Solved Save block breaking in SQLite database

Hi!

I finally managed to learn SQL, yay!

So now, I'm going to start LOGGING EVERONE AND EVERYTHING! Mwuahahah! > : )
Nah, just kidding. But I'm going to start logging every block broken by who, and the coordinates.

But how do i load the coordinates and blocks properly?

This doesn't work for me;

Code:java

package me.Thomas.MySQLTestPlugin.KommandoerOgSlikt;
 
import java.sql.ResultSet;
import java.sql.SQLException;
 
import me.Thomas.MySQLTestPlugin.Main;
 
import org.bukkit.ChatColor;
import org.bukkit.Location;
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{
Main plugin;
 
public SjekkBlokkLogg(Main 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();
Location loc = new Location(world, x, y, z);
try {
plugin.sql.initialise();
ResultSet coordset = plugin.sql.sqlQuery("SELECT verden,x,y,z FROM blockbreak");
ResultSet set = plugin.sql.sqlQuery("SELECT blokk FROM blockbreak ");
ResultSet playerset = plugin.sql.sqlQuery("SELECT brukernavn FROM blockbreak");
if(coordset.equals(loc)){
while(coordset.next() || set.next() || playerset.next()){
player.sendMessage(ChatColor.RED + "Denne blokken ble fjernet av " + playerset.getString("brukernavn") + ". Koordinater: " + "\nX: " + coordset.getString("x") + "\nY: " + coordset.getString("y") + "\nZ: " + coordset.getString("z") + "\nVerden: " + coordset.getString("verden"));
}
}else{
player.sendMessage("Denne blokken har ingen blokklogg.");
}
set.close();
}
catch (SQLException e) {
e.printStackTrace();
}
 
}
}
}
}
 

The way i save it:

Code:java

package me.Thomas.MySQLTestPlugin.KommandoerOgSlikt;
 
import java.sql.SQLException;
 
import me.Thomas.MySQLTestPlugin.Main;
 
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;
 
public class Blocklog implements Listener{
Main plugin;
 
public Blocklog(Main 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();
try {
plugin.sql.standardQuery("INSERT INTO blockbreak(brukernavn,x,y,z,verden) VALUES ('" + event.getPlayer().getName() + "', '" + x +"', '" + y + "', '" + z + "', '"+ world + "') ;");
player.sendMessage("Lagret!"); //Debug message to make sure it get's saved
} catch (SQLException e) {
e.printStackTrace();
}
}
 
 
 
}
 

Btw, it is on norwegian
I'm also using the Sync API to make my life a whole lot easier.

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

I have no problems with changing API and/or use a library/anything else.

Thanks!

 

Code:

if(coordset.equals(loc)){

This will not work. You need to create a new Location object from the data read from database and then you can compare the coordinates of those locations.
Also, you can use a program like "SQLiteSpy" or similar to have a look at your database.
This way you can find errors faster.

But keep in mind that logging frequent actions like block breaking, placing and more is better done with MySQL (best with InnoDB engine) than SQLite.
Because SQLite has only table-level-locking. Means, the whole table will be locked for every INSERT or UPDATE or DELETE query you issue until the query has been fully executed.
This may lead to a performance problem at some point.
MySQL with InnoDB engine has row-level-locking. Means, only the currently accessed row will be locked, while other rows can still be accessed simultaneously.
Just as a thought.
Make also sure to connect, write and read data asynchronously to/from DB within a separate thread, to avoid stalling the main thread. (=lag)
What LogBlock for example also does, is using a queue to write data asynchronously and bulk-wise for better performance.

If your plugin stays small, the used database engine is not that much of a concern.
But if it eventually does nearly all of that stuff LogBlock for example does, then you will have to worry about this.

 

CubieX
I'm using Navicat SQLite to check and edit the file.

I will stick with SQLite for now, until i need a better way to store the data.

How would i do that?

 

I don't speak norwegian. So I don't know exactly what this output is used for:

Code:

ResultSet coordset = plugin.sql.sqlQuery("SELECT verden,x,y,z FROM blockbreak");
ResultSet set = plugin.sql.sqlQuery("SELECT blokk FROM blockbreak ");
ResultSet playerset = plugin.sql.sqlQuery("SELECT brukernavn FROM blockbreak");

But if you want to output who broke the clicked block and when,
then you need to extend your SELECT queries with a WHERE statement to only get the block at the clicked location.
You do not need to compare the locations then. because your ResultSet will now only hold the history of this clicked block.

 

CubieX

Okay, i guess i will translate my code before sending it

Yeah, something like this then?

Code:

ResultSet coordset = plugin.sql.sqlQuery("SELECT * FROM blockbreak WHERE x,y,z='something inside here';");

Code translated:

Code:

ResultSet coordset = plugin.sql.sqlQuery("SELECT world,x,y,z FROM blockbreak");
ResultSet set = plugin.sql.sqlQuery("SELECT block FROM blockbreak ");
ResultSet playerset = plugin.sql.sqlQuery("SELECT username FROM blockbreak");

Yeah.. you'll just have to be patient with me I'm still a beginner, got a lot of notes written down.

SQL is so much easier when not working with Java.

 

SQL is SQL. I can't see why it should be more complicated when using it with Java.

Your WHERE statement should look something like this:

Code:

WHERE world='" + world.getName() + "' AND x=" + x + " AND y=" + y + " AND z=" + z + ";";

And you will surely add a timestamp later?
If you have one, add an "ORDER BY timestamp DESC" statement to your queries to get your result set ordered with the newest entry at first position.
It's easier to get a nice output list this way.

 

CubieX

Thanks for your help, i really appreciate it!
It works perfectly fine now.
Once again, thank you.

[EDIT]
Didn't see your edit before now.

A time stamp would be great, i guess the MySQL docs says something about how to set it up?

Misunderstood. Yeah, that would be great too

Do you know how i should log the time the block was broken?

Like

Code:

Calendar time = Calendar.getInstance();
            SimpleDateFormat formatter = new SimpleDateFormat(
                    "dd/MMM HH:mm:ss");
            String tid = formatter.format(dato.getTime()).toString();