First commit

1 files changed, 125 insertions, 0 deletions

diff --git a/src/main/java/dev/deeve/containeraudit/Database.java b/src/main/java/dev/deeve/containeraudit/Database.java
new file mode 100644
index 0000000..ad409f4
--- /dev/null
+++ b/src/main/java/dev/deeve/containeraudit/Database.java
@@ -0,0 +1,125 @@
+package dev.deeve.containeraudit;
+
+import org.bukkit.entity.Player;
+import org.bukkit.plugin.java.JavaPlugin;
+import org.bukkit.inventory.ItemStack;
+import org.bukkit.Location;
+import org.bukkit.Bukkit;
+
+import java.sql.PreparedStatement;
+import java.sql.DriverManager;
+import java.sql.SQLException;
+import java.text.SimpleDateFormat;
+import java.time.Instant;
+import java.time.LocalDateTime;
+import java.util.ArrayList;
+import java.util.Date;
+import java.util.logging.Level;
+import java.util.logging.Logger;
+import java.sql.Connection;
+import java.sql.ResultSet;
+
+public class Database {
+  static Connection connection;
+  static Logger logger;
+
+  public static void connect(String path, Logger logger) throws SQLException {
+    connection = DriverManager.getConnection("jdbc:sqlite:" + path);
+    Database.logger = logger;
+  }
+
+  static String playerID(Player player) {
+    return player.getUniqueId().toString().replaceAll("-", "");
+  }
+
+  public static void createTables() throws SQLException {
+    PreparedStatement chestEventCreate = connection.prepareStatement(
+      "CREATE TABLE IF NOT EXISTS chest_events (" +
+        "id         INTEGER PRIMARY KEY AUTOINCREMENT, " +
+        "player     TEXT NOT NULL, " +
+        "x          REAL NOT NULL, " +
+        "y          REAL NOT NULL, " +
+        "z          REAL NOT NULL, " +
+        "block      TEXT NOT NULL," +
+        "count      INTEGER NOT NULL," +
+        "event_type TEXT NOT NULL," +
+        "timestamp  INTEGER NOT NULL" +
+      ")"
+    );
+    chestEventCreate.executeUpdate();
+    
+    PreparedStatement chestCreate = connection.prepareStatement(
+      "CREATE TABLE IF NOT EXISTS chests (" +
+        "id         INTEGER PRIMARY KEY AUTOINCREMENT, " +
+        "actor      TEXT NOT NULL, " +
+        "action     TEXT NOT NULL," +
+        "x          REAL NOT NULL, " +
+        "y          REAL NOT NULL, " +
+        "z          REAL NOT NULL," +
+        "timestamp  INTEGER NOT NULL" +
+      ")"
+    );
+    
+    chestCreate.executeUpdate();
+    
+    PreparedStatement viewCreate = connection.prepareStatement(
+    		"CREATE VIEW IF NOT EXISTS chest_block_events AS " +
+    		"SELECT * FROM chests AS placed_chest FULL OUTER JOIN chests AS broke_chest ON " +
+    		"broke_chest.action = \"BREAK\" AND placed_chest.action = \"PLACE\" AND " +
+    		" placed_chest.x = broke_chest.x AND placed_chest.y = broke_chest.y AND placed_chest.z = broke_chest.z AND "
+    		+ "broke_chest.timestamp = (SELECT MIN(timestamp) FROM chests WHERE timestamp > placed_chest.timestamp) WHERE " +
+    		" placed_chest.action = \"PLACE\" OR broke_chest.action = \"BREAK\";"
+    );
+    
+    viewCreate.executeUpdate();
+    
+    // software engineering is dead
+    PreparedStatement shmiewShmeate = connection.prepareStatement("CREATE VIEW IF NOT EXISTS chest_event_owned AS SELECT chest_events.id, chest_events.player, chest_events.x, chest_events.y, chest_events.z, chest_events.timestamp AS event_timestamp, action, block, `count`, chests.timestamp AS chest_placed_timestamp, chests.actor AS chest_owner FROM chest_events LEFT JOIN chests  ON chests.timestamp = (SELECT MAX(timestamp) FROM chests WHERE chest_events.x = x AND chest_events.y = y AND chest_events.z = z AND chests.action = \"PLACE\" AND chest_events.timestamp > timestamp)");
+    shmiewShmeate.executeUpdate();
+    
+    		
+  }
+  
+  public static void logInventoryEvent(String player, boolean pickup, Location loc, ItemStack stack) {
+	  try {
+		  PreparedStatement statement = connection.prepareStatement(
+				  "INSERT INTO chest_events (player, x, y, z, block, count, event_type, timestamp) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
+		  
+		  statement.setString(1, player);
+		  statement.setInt(2, loc.getBlockX());
+		  statement.setInt(3, loc.getBlockY());
+		  statement.setInt(4, loc.getBlockZ());
+		  statement.setString(5, stack.getType().getKey().toString());
+		  statement.setInt(6, stack.getAmount());
+		  statement.setString(7, pickup ? "PICKUP" : "PLACE");
+		  statement.setLong(8, Instant.now().getEpochSecond());
+		  
+		  statement.executeUpdate();
+		  
+	  } catch (SQLException ex) {
+		  logger.log(Level.SEVERE, "Error occured when inserting into chest_events");
+	      ex.printStackTrace();
+	  }
+	  
+  }
+  
+  public static void logBlockEvent(String player, String type, Location loc) {
+	  try {
+		  PreparedStatement statement = connection.prepareStatement(
+				  "INSERT INTO chests (actor, action, x, y, z, timestamp) VALUES (?, ?, ?, ?, ?, ?)");
+		  
+		  statement.setString(1, player);
+		  statement.setString(2, type);
+		  statement.setInt(3, loc.getBlockX());
+		  statement.setInt(4, loc.getBlockY());
+		  statement.setInt(5, loc.getBlockZ());
+		  statement.setLong(6, Instant.now().getEpochSecond());
+		  
+		  statement.executeUpdate();
+		  
+	  } catch (SQLException ex) {
+		  logger.log(Level.SEVERE, "Error occured when inserting into chests");
+		  ex.printStackTrace();
+	  }
+  }
+}
\ No newline at end of file