1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
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();
}
}
}
|