If the database does not contain the player name for certain blocks, cancel event

Hi!

Thanks to CubieX, i finally managed to log blocks and such with SQLite. What i need to do now is how i could check if a block contains the player name. If it does not, then cancel the block place/break event.

I am using the Sync API for this, if that affects anything.

http://dev.bukkit.org/bukkit-plugins/sync/

 

Do you have any code? What do you mean check if a block contains a players name? Like the SQL entry?

Try a SELECT * WHERE statement. It's very hard to help someone when you have no idea what they're doing.

 

jimbo8 What does your DB look like? Maybe even an ER model if it is complicated?

 

Nateb1121

Yeah, sorry, should have given some more info

The only code i have is to save and see which person placed and broke which blocks.

Logging class:

Code:java

package me.Thomas.MySQL.BlockLog;
 
import java.sql.SQLException;
 
import me.Thomas.mindwork.Mindwork;
 
import org.bukkit.Location;
import org.bukkit.World;
import org.bukkit.entity.Player;
import org.bukkit.event.EventHandler;
import org.bukkit.event.Listener;
import org.bukkit.event.block.BlockBreakEvent;
import org.bukkit.event.block.BlockPlaceEvent;
 
public class Blocklog implements Listener{
Mindwork plugin;
 
public Blocklog(Mindwork instance){
plugin = instance;
}
@EventHandler
public void onPlayerBreak(BlockBreakEvent event){
int x = event.getBlock().getX();
int y = event.getBlock().getY();
int z = event.getBlock().getZ();
Player player = event.getPlayer();
World world = player.getWorld();
plugin.sql.initialise();
Location block = event.getBlock().getLocation();
try {
plugin.sql.standardQuery("INSERT INTO blockbreak(brukernavn,blokk,x,y,z,verden) VALUES ('" + event.getPlayer().getName() + "', '" + block + "', '" + x +"', '" + y + "', '" + z + "', '"+ world.getName() + "');");
player.sendMessage("Lagret!"); //debug melding
} catch (SQLException e) {
e.printStackTrace();
}
}
@EventHandler
public void onPlayerBreak(BlockPlaceEvent event){
int x = event.getBlock().getX();
int y = event.getBlock().getY();
int z = event.getBlock().getZ();
Player player = event.getPlayer();
World world = player.getWorld();
plugin.sql.initialise();
Location block = event.getBlock().getLocation();
try {
plugin.sql.standardQuery("INSERT INTO blockplace(brukernavn,blokk,x,y,z,verden) VALUES ('" + event.getPlayer().getName() + "', '" + block + "', '" + x +"', '" + y + "', '" + z + "', '"+ world.getName() + "');");
player.sendMessage("Lagret!"); //debug melding
} catch (SQLException e) {
e.printStackTrace();
}
}
 
 
 
}
 

..and the checking

Code:java

package me.Thomas.MySQL.BlockLog;
 
import java.sql.ResultSet;
import java.sql.SQLException;
 
import me.Thomas.mindwork.Mindwork;
 
import org.bukkit.ChatColor;
import org.bukkit.Material;
import org.bukkit.World;
import org.bukkit.entity.Player;
import org.bukkit.event.EventHandler;
import org.bukkit.event.Listener;
import org.bukkit.event.block.Action;
import org.bukkit.event.player.PlayerInteractEvent;
 
public class SjekkBlokkLogg implements Listener{
Mindwork plugin;
 
public SjekkBlokkLogg(Mindwork instance){
plugin = instance;
}
 
@EventHandler
public void sjekkLogg(PlayerInteractEvent event){
Player player = event.getPlayer();
if(event.getAction() == Action.RIGHT_CLICK_BLOCK){
if (player.getItemInHand().getType() == Material.WATCH) {
int x = event.getClickedBlock().getLocation().getBlockX();
int y = event.getClickedBlock().getLocation().getBlockY();
int z = event.getClickedBlock().getLocation().getBlockZ();
World world = player.getWorld();
plugin.sql.initialise();
try {
ResultSet coordset = plugin.sql.sqlQuery("SELECT * FROM blockbreak WHERE verden='" + world.getName() + "' AND x=" + x + " AND y=" + y + " AND z=" + z + ";");
ResultSet playerset = plugin.sql.sqlQuery("SELECT brukernavn FROM blockbreak;");
while(coordset.next()){
player.sendMessage(ChatColor.RED + "Denne blokken ble fjernet av " + playerset.getString("brukernavn") + ". Koordinater: " + "\nX: " + coordset.getString("x") + ", Y: " + coordset.getString("y") + ", Z: " + coordset.getString("z") + "\nVerden: " + coordset.getString("verden"));
 
}
 
coordset.close();
playerset.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
if(event.getAction() == Action.LEFT_CLICK_BLOCK){
if (player.getItemInHand().getType() == Material.WATCH) {
event.setCancelled(true);
int x = event.getClickedBlock().getLocation().getBlockX();
int y = event.getClickedBlock().getLocation().getBlockY();
int z = event.getClickedBlock().getLocation().getBlockZ();
World world = player.getWorld();
plugin.sql.initialise();
try {
ResultSet coordset2 = plugin.sql.sqlQuery("SELECT * FROM blockplace WHERE verden='" + world.getName() + "' AND x=" + x + " AND y=" + y + " AND z=" + z + ";");
ResultSet playerset2 = plugin.sql.sqlQuery("SELECT brukernavn FROM blockplace;");
while(coordset2.next()){
player.sendMessage(ChatColor.RED + "Denne blokken ble plassert av " + playerset2.getString("brukernavn") + ". Koordinater: " + "\nX:" + coordset2.getString("x") + ", Y: " + coordset2.getString("y") + ", Z: " + coordset2.getString("z") + "\nVerden: " + coordset2.getString("verden"));
}
coordset2.close();
playerset2.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
 
}
}
}
 

Would maybe something like this work?

Code:java

ResultSet playerset = plugin.sql.sqlQuery("SELECT * FROM blockplace WHERE '" + player.getName() + "'=brukernavn;");
 

Also, this is in norwegian.

Short translation;

Brukernavn means username, verden means world and that is basically it in this code

Sgt_Tailor

My database looks like this;
http://i.imgur.com/Iz7uIcC.png

 

jimbo8 one note for the database. You don't need the blokk column, as that is stored using the x y and z of the location as well, which is faster

For the checking you want to do this:

Code:java

 
String name = player.getName();
int x = ...
int y = ...
int z = ...
ResultSet rs = plugin.sql.sqlQuery("SELECT * FROM blockplace WHERE brukernavn = 'name' AND x = "+x+" AND y = "+y+" AND z = "+z);
if(rs.hasNext){
//the player placed a block at that location.
}
/syntax]
But taking into account that a block can be broken and placed by multiple people and you only want the latest person to be able to break the block you can try this query: "SELECT brukernavn FROM blockplace WHERE x = "+x+" AND y = "+y+" AND z = "+z+" ORDER BY id DESC" and then use the following code to check if the player that is trying to break the block is in fact the owner
 
[syntax=java]ResultSet rs = plugin.sql.sqlQuery("SELECT brukernavn FROM blockplace WHERE x = "+x+" AND y = "+y+" AND z = "+z+" ORDER BY id DESC LIMIT 1");
while(rs.hasNext()){
    String owner = rs.getString(1);
    if(owner.equals(player.getName()){
        //he is the lastes placer
    }
    else{
        //he is not
    }
}
[/syntax]

 

Thanks, this works!

Just one more thing, do you know how i could add a date?

Let's say I'm checking a grief in one of my cities, and is using my clock to see who broke the blocks, but i also want to see the time it happened. How would i do that?

Sgt_Tailor

 

jimbo8 add a timestamp column to the table, but instead of sorting by id you can then sort by time

 

Sgt_Tailor

I will see what i can do, i guess the SQL docs says something about it

 

Code:

"CREATE TABLE blockplace(id INT AUTO_INCREMENT PRIMARY, brukernavn VARCHAR(16) NOT NULL, x INT NOT NULL, y INT NOT NULL, z INT NOT NULL, verden VARCHAR NOT NULL, time TIMESTAMP)"

you can use that query to create the table. I wasn't sure what blokk is for (I guess the id of the block), but you can add that if you need it

 

Sgt_Tailor

Okay, so i figured out that SQLite got locked every time i did something and i had to close the connection every time i did something with the connection, if i forget to clsoe the connection, the database locks :s

So i tried using MySQL instead and got this error;

Code:

[15:12:31 WARN]: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You
have an error in your SQL syntax; check the manual that corresponds to your MySQ
L server version for the right syntax to use near ' brukernavn VARCHAR(16) NOT N
ULL, x INT NOT NULL, y INT NOT NULL, z INT NOT NULL' at line 1

This is the line(s);

Code:

sql.standardQuery("CREATE TABLE blockbreak(ID INT AUTO_INCREMENT PRIMARY, brukernavn VARCHAR(16) NOT NULL, x INT NOT NULL, y INT NOT NULL, z INT NOT NULL, verden VARCHAR NOT NULL, tid TIMESTAMP);");

Code:

sql.standardQuery("CREATE TABLE blockplace(ID INT AUTO_INCREMENT PRIMARY, brukernavn VARCHAR(16) NOT NULL, x INT NOT NULL, y INT NOT NULL, z INT NOT NULL, verden VARCHAR NOT NULL, tid TIMESTAMP);");

Btw, i managed to add a timestamp, it works perfectly fine

 

jimbo8 I will get on my main pc to run the query, it is most likely a stupid mistake. Let me get a coffee to wake me up a bit

 

Sgt_Tailor

*gives coffee*

 

jimbo8 I'm at my pc. Starting up my server to do some queries

 

Sgt_Tailor

Tell me when you figured out something

 

jimbo8 This works:

Code:

CREATE TABLE IF NOT EXISTS blockplace(
 ID INT AUTO_INCREMENT PRIMARY KEY,
brukernavn VARCHAR(16) NOT NULL,
x INT NOT NULL,
y INT NOT NULL,
z INT NOT NULL,
verden VARCHAR(30) NOT NULL,
tid TIMESTAMP)

the world name can not be bigger than 30 characters, feel free to change that

 

Sgt_Tailor

Thanks, it works now

Only problem is that i got this error when i use my clock to check the block protection, and i don't really understand what it means

Code:

[16:20:11 WARN]: java.sql.SQLException: Before start of result set
[16:20:11 WARN]:        at com.mysql.jdbc.SQLError.createSQLException(SQLError.j
ava:1073)
[16:20:11 WARN]:        at com.mysql.jdbc.SQLError.createSQLException(SQLError.j
ava:987)
[16:20:11 WARN]:        at com.mysql.jdbc.SQLError.createSQLException(SQLError.j
ava:982)
[16:20:11 WARN]:        at com.mysql.jdbc.SQLError.createSQLException(SQLError.j
ava:927)
[16:20:11 WARN]:        at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImp
l.java:841)
[16:20:11 WARN]:        at com.mysql.jdbc.ResultSetImpl.getStringInternal(Result
SetImpl.java:5656)
[16:20:11 WARN]:        at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.
java:5576)
[16:20:11 WARN]:        at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.
java:5616)
[16:20:11 WARN]:        at me.Thomas.MySQL.BlockLog.SjekkBlokkLogg.sjekkLogg(Sje
kkBlokkLogg.java:58)
[16:20:11 WARN]:        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native M
ethod)
[16:20:11 WARN]:        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown S
ource)
[16:20:11 WARN]:        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unkno
wn Source)
[16:20:11 WARN]:        at java.lang.reflect.Method.invoke(Unknown Source)
[16:20:11 WARN]:        at org.bukkit.plugin.java.JavaPluginLoader$1.execute(Jav
aPluginLoader.java:425)
[16:20:11 WARN]:        at org.bukkit.plugin.RegisteredListener.callEvent(Regist
eredListener.java:62)
[16:20:11 WARN]:        at org.bukkit.plugin.TimedRegisteredListener.callEvent(T
imedRegisteredListener.java:30)
[16:20:11 WARN]:        at org.bukkit.plugin.SimplePluginManager.fireEvent(Simpl
ePluginManager.java:482)
[16:20:11 WARN]:        at org.bukkit.plugin.SimplePluginManager.callEvent(Simpl
ePluginManager.java:467)
[16:20:11 WARN]:        at org.bukkit.craftbukkit.v1_7_R1.event.CraftEventFactor
y.callPlayerInteractEvent(CraftEventFactory.java:208)
[16:20:11 WARN]:        at net.minecraft.server.v1_7_R1.PlayerInteractManager.di
g(PlayerInteractManager.java:103)
[16:20:11 WARN]:        at net.minecraft.server.v1_7_R1.PlayerConnection.a(Playe
rConnection.java:536)
[16:20:11 WARN]:        at net.minecraft.server.v1_7_R1.PacketPlayInBlockDig.a(S
ourceFile:53)
[16:20:11 WARN]:        at net.minecraft.server.v1_7_R1.PacketPlayInBlockDig.han
dle(SourceFile:8)
[16:20:11 WARN]:        at net.minecraft.server.v1_7_R1.NetworkManager.a(Network
Manager.java:147)
[16:20:11 WARN]:        at net.minecraft.server.v1_7_R1.ServerConnection.c(Sourc
eFile:134)
[16:20:11 WARN]:        at net.minecraft.server.v1_7_R1.MinecraftServer.u(Minecr
aftServer.java:657)
[16:20:11 WARN]:        at net.minecraft.server.v1_7_R1.DedicatedServer.u(Dedica
tedServer.java:273)
[16:20:11 WARN]:        at net.minecraft.server.v1_7_R1.MinecraftServer.t(Minecr
aftServer.java:540)
[16:20:11 WARN]:        at net.minecraft.server.v1_7_R1.MinecraftServer.run(Mine
craftServer.java:446)
[16:20:11 WARN]:        at net.minecraft.server.v1_7_R1.ThreadServerApplication.
run(SourceFile:617)

"Before start of resultset"

What? I don't really understand

Here is the code it is pointing to;

Code:java

package me.Thomas.MySQL.BlockLog;
 
import java.sql.ResultSet;
import java.sql.SQLException;
 
import me.Thomas.mindwork.Mindwork;
 
import org.bukkit.ChatColor;
import org.bukkit.Material;
import org.bukkit.World;
import org.bukkit.entity.Player;
import org.bukkit.event.EventHandler;
import org.bukkit.event.Listener;
import org.bukkit.event.block.Action;
import org.bukkit.event.player.PlayerInteractEvent;
 
public class SjekkBlokkLogg implements Listener{
    Mindwork plugin;
 
    public SjekkBlokkLogg(Mindwork instance){
        plugin = instance;
    }
 
    @EventHandler
    public void sjekkLogg(PlayerInteractEvent event){
        Player player = event.getPlayer();
        if(event.getAction() == Action.RIGHT_CLICK_BLOCK){
            if (player.getItemInHand().getType() == Material.WATCH) {
                int x = event.getClickedBlock().getLocation().getBlockX();
                int y = event.getClickedBlock().getLocation().getBlockY();
                int z = event.getClickedBlock().getLocation().getBlockZ();
                World world = player.getWorld();
                plugin.sql.initialise();
                try {
                    ResultSet coordset = plugin.sql.sqlQuery("SELECT * FROM blockbreak WHERE verden='" + world.getName() + "' AND x=" + x + " AND y=" + y + " AND z=" + z + ";");
                    ResultSet playerset = plugin.sql.sqlQuery("SELECT brukernavn FROM blockbreak;");
                    while(coordset.next()){
                    player.sendMessage(ChatColor.GRAY + "Denne blokken ble fjernet av " + playerset.getString("brukernavn") + ". Koordinater: " + ChatColor.GREEN + "\nX: " + coordset.getString("x") + ChatColor.DARK_GRAY + "," + ChatColor.RED + " Y: " + coordset.getString("y") + ChatColor.DARK_GRAY +"," + ChatColor.BLUE + " Z: " + coordset.getString("z") + ChatColor.GRAY + "\nVerden: " + ChatColor.AQUA + coordset.getString("verden") + ChatColor.GRAY + "\nTid: " + ChatColor.GOLD + coordset.getString("tid"));
 
                    }
                }
                    catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        if(event.getAction() == Action.LEFT_CLICK_BLOCK){
            if (player.getItemInHand().getType() == Material.WATCH) {
                event.setCancelled(true);
                int x = event.getClickedBlock().getLocation().getBlockX();
                int y = event.getClickedBlock().getLocation().getBlockY();
                int z = event.getClickedBlock().getLocation().getBlockZ();
                World world = player.getWorld();
                try {
                    ResultSet coordset2 = plugin.sql.sqlQuery("SELECT * FROM blockplace WHERE verden='" + world.getName() + "' AND x=" + x + " AND y=" + y + " AND z=" + z + ";");
                    ResultSet playerset2 = plugin.sql.sqlQuery("SELECT brukernavn FROM blockplace;");
                    while(coordset2.next()){
                        player.sendMessage(ChatColor.GRAY + "Denne blokken ble plassert av " + ChatColor.WHITE + playerset2.getString("brukernavn") + ChatColor.GRAY + ". Koordinater: " + ChatColor.GREEN + "\nX: " + coordset2.getString("x") + ChatColor.DARK_GRAY + "," + ChatColor.RED + " Y: " + coordset2.getString("y") + ChatColor.DARK_GRAY +"," + ChatColor.BLUE + " Z: " + coordset2.getString("z") + ChatColor.GRAY + "\nVerden: " + ChatColor.AQUA + coordset2.getString("verden") + ChatColor.GRAY + "\nTid: " + ChatColor.GOLD + coordset2.getString("tid"));
                    }
                }
                    catch (SQLException e) {
                    e.printStackTrace();
 
                }
 
            }
 
        }
    }
}
 

where line 58 is here:

Code:java

player.sendMessage(ChatColor.GRAY + "Denne blokken ble plassert av " + ChatColor.WHITE + playerset2.getString("brukernavn") + ChatColor.GRAY + ". Koordinater: " + ChatColor.GREEN + "\nX: " + coordset2.getString("x") + ChatColor.DARK_GRAY + "," + ChatColor.RED + " Y: " + coordset2.getString("y") + ChatColor.DARK_GRAY +"," + ChatColor.BLUE + " Z: " + coordset2.getString("z") + ChatColor.GRAY + "\nVerden: " + ChatColor.AQUA + coordset2.getString("verden") + ChatColor.GRAY + "\nTid: " + ChatColor.GOLD + coordset2.getString("tid"));
 

 

jimbo8 try playerset2.getString(1), as column count is used, not the names

 

Sgt_Tailor

Hmm, does still not work.

Exactly the same error.

 

jimbo8 you need to make a new while loop: while(playerset2.next())

 

Ah, of course. Thanks.

Do you know how i can get the last placer of the block?

As i have removed the block a lot of times, it spams me with every playername that broke the block at that location.

[EDIT]

Actually, it gets every single block broken/placed on the whole server.

Nevermind, forgot to sort by ID.

 

jimbo8 If this solved the issue then you can mark this as solved. If I helped you could leave me a like

 

Sgt_Tailor

Hi

I think this will be the last question, but do you know what i should do if i want to change the block protection to another person? I know i can get the target block, get the name of the owner of the targeted block and then change the owner name, but what if i want to change regions?

Like, let's say i mark a 10x10 area with a wooden axe, how could i then change every block within that radius to a chosen player?

Like this:

/blockprotection <playername>

..Will change every protected block to the chosen user.

Also, how could i protect a marked area? I've built my spawn a few weeks ago, so it does not contain the block protection, how would i save every block? Kinda the same way as changing block protection?

[EDIT]

Also.. I need to remove the person from that ID after the block has been broken by the same person, because if i place a block, and then you place one on the same coordinates, it will be locked on me

Nevermind. But i got a lot of bugs, though.

I'm using a clock to check who placed the block, but it only tells the last person who placed a block on the whole server, not on those coordinates. I don't see what's wrong:

Code:java

                try {
                    ResultSet coordset2 = plugin.sql.sqlQuery("SELECT * FROM blockplace WHERE verden='" + world.getName() + "' AND x=" + x + " AND y=" + y + " AND z=" + z + " ORDER BY id DESC LIMIT 1;");
                    ResultSet playerset2 = plugin.sql.sqlQuery("SELECT brukernavn FROM blockplace ORDER BY id DESC LIMIT 1;");
                    while(coordset2.next()){
                        while(playerset2.next()){
                        player.sendMessage(ChatColor.GRAY + "This block was placed by " + ChatColor.WHITE + playerset2.getString(1) + ChatColor.GRAY + ". Koordinater: " + ChatColor.GREEN + "\nX: " + coordset2.getString("x") + ChatColor.DARK_GRAY + "," + ChatColor.RED + " Y: " + coordset2.getString("y") + ChatColor.DARK_GRAY +"," + ChatColor.BLUE + " Z: " + coordset2.getString("z") + ChatColor.GRAY + "\World: " + ChatColor.AQUA + coordset2.getString("verden") + ChatColor.GRAY + "\nTime: " + ChatColor.GOLD + coordset2.getString("tid"));
                    }
                    }
                }catch (SQLException e) {
                    e.printStackTrace();
 
                }

 

jimbo8 why do you need playerset2? you can just do coordset.getString(1)

 

I tried to remove it, but then i just get this "end of resultset" error.

Nevermind, fixed it.

Still trying to find out how i can change protection in areas
Sgt_Tailor

 

jimbo8 how would you define an area?

 

Sgt_Tailor

Let's say i have a wooden axe.

I mark two areas, mark one and two is 30 blocks away from eachother. Now, if i type /changebp sgt_tailor(or something like that), every single block in the marked area will be protected for you, only you can break it now.

 

you can have two options, hava a table with areas (id, blockpos1, blockpos2) and when someone breaks a block iterate though all the areas and check if the block is inside it. Or for every block in the area, set that the latest placer is playerA. You could alternatively use blockMetaData to store this as well and only change that on block break and place. That would prevent you from connection to the database every time someone breaks a block.