· 4 years ago · Jul 17, 2021, 05:42 PM
1import lombok.Getter;
2import lombok.NonNull;
3import lombok.SneakyThrows;
4import org.broken.cheststorage.ChestStorage;
5import org.broken.cheststorage.data.ChestRegistry;
6import org.broken.cheststorage.data.ChestRegistry.ChestData;
7import org.broken.cheststorage.data.ChestRegistry.TypeOfContainer;
8import org.broken.cheststorage.database.sqlite.Errors;
9import org.bukkit.Location;
10import org.bukkit.Material;
11import org.bukkit.inventory.ItemStack;
12import org.mineacademy.fo.Common;
13import org.mineacademy.fo.SerializeUtil;
14
15import java.io.File;
16import java.io.IOException;
17import java.math.BigInteger;
18import java.sql.*;
19import java.util.*;
20import java.util.logging.Level;
21
22public class SQLite {
23 @Getter
24 private static final SQLite Instance = new SQLite();
25 private final File chestStorage = ChestStorage.getData();// A reference to Main Class
26
27 private Connection connection = connect();
28
29 private static final ConvertJson converting = new ConvertJson();
30
31 private boolean loadDataStartup = true;
32
33 private final String TableNameChest = "ContainerData", TableOneFirstRow = "Location", TableOnesecondRow = "Player_UUID",
34 TableOneThirdRow = "FilterWhiteBlack", TableOneFourthRow = "Chest_Type", TableOneFifthRow = "Suction_Range",
35 TableOneSixthRow = "Link_Range", TableOneSeventhRow = "Link_Amount", TableOneEighthRow = "Next_Upgrade",
36 TableOneNineRow = "Chest_contents", TableOneTenRow = "SettingsName", TableOneElevenRow = "AmountOfPages",
37 TableOneTwelveRow = "FilterItems", TableOneThirteenRow = "Link_location", TableOneFourteenRow = "AmountOfItems";
38 private final String TableTwoUsers = "Users", TableTwoFirstRow = "PLAYER_NAME", TableTwosecondRow = "UUID";
39
40 private boolean batchUpdateGoingOn = false;
41
42
43 public Connection connect() {
44
45
46 try {
47 File dbFile = new File(chestStorage, "database.db");
48 if (!dbFile.exists()) {
49 try {
50 dbFile.createNewFile();
51
52 //String url = "jdbc:sqlite:" + dbFile.getPath();
53 } catch (IOException ex) {
54 ChestStorage.getInstance().getLogger().log(Level.SEVERE, "File write error: " + dbFile + ".db", ex);
55 }
56 }
57
58 if (this.connection != null && !this.connection.isClosed()) {
59 return this.connection;
60 }
61 Class.forName("org.sqlite.JDBC");
62 this.connection = DriverManager.getConnection("jdbc:sqlite:" + dbFile);
63 return this.connection;
64
65
66 } catch (SQLException ex) {
67 ChestStorage.getInstance().getLogger().log(Level.SEVERE, "SQLite exception on initialize", ex);
68 } catch (ClassNotFoundException ex) {
69 ChestStorage.getInstance().getLogger().log(Level.SEVERE, "You need the SQLite JBDC library. Google it. Put it in /lib folder.");
70 }
71 return null;
72 }
73
74
75 public void load(String sql) {
76 this.connection = connect();
77 try {
78 Statement s = this.connection.createStatement();
79 s.executeUpdate(sql);
80 s.close();
81 } catch (SQLException e) {
82 e.printStackTrace();
83 }
84 initialize();
85 }
86/*
87 public Statement Statements() {
88 try {
89 connect().createStatement();
90 } catch (SQLException throwables) {
91 throwables.printStackTrace();
92 }
93 return statement;
94 }*/
95
96 public void disconnect() {
97 try {
98 if (connection != null) {
99 connection.close();
100 }
101 } catch (SQLException exception) {
102 exception.printStackTrace();
103 }
104 }
105
106
107 /*private void update(String sql) {
108 try {
109 connect().executeupdate(sql);
110 } catch (SQLException exception) {
111 exception.printStackTrace();
112 }
113 }
114
115 public ResultSet getResult(String sql) {
116 try {
117 return connect().executeQuery(sql);
118 } catch (SQLException exception) {
119 exception.printStackTrace();
120 }
121 return null;
122 }*/
123
124 public void createTables() {
125 String test;
126 String tableOne = "CREATE TABLE IF NOT EXISTS " + TableNameChest + " (" + TableOneFirstRow + " VARCHAR(64) PRIMARY KEY, " +
127 TableOnesecondRow + " VARCHAR(64) , " + TableOneThirdRow + " VARCHAR(64), " + TableOneFourthRow + " VARCHAR(64), " +
128 TableOneFifthRow + " VARCHAR(64), " + TableOneSixthRow + " VARCHAR(64), " + TableOneSeventhRow + " VARCHAR(64), " +
129 TableOneEighthRow + " VARCHAR(64), " + TableOneNineRow + " VARCHAR(200)," + TableOneTenRow + " VARCHAR(64)," +
130 TableOneElevenRow + " VARCHAR(64)," + TableOneTwelveRow + " VARCHAR(64)," + TableOneThirteenRow + " VARCHAR(200)," + TableOneFourteenRow + " VARCHAR(120));";
131 String tableTwo = "CREATE TABLE IF NOT EXISTS " + TableTwoUsers + " (" + TableTwoFirstRow + " VARCHAR(64) , " + TableTwosecondRow + " VARCHAR(64) PRIMARY KEY);";
132 //Common.log(tableOne);
133
134
135 String[] tabels = {tableOne, tableTwo};
136
137 for (int i = 0; i < tabels.length; i++) {
138 test = tabels[i];
139 //System.out.println(test);
140 load(test);
141 }
142 }
143
144 //TODO Fix more of load code and cleanup/fixes?
145 public void LoadFromSQlite() {
146 PreparedStatement ps = null;
147 this.connection = connect();
148 ResultSet rs;
149
150 try {
151 String sql = "SELECT * FROM " + TableNameChest;
152 ps = this.connection.prepareStatement(sql);
153 rs = ps.executeQuery();
154
155 while (rs.next()) {
156
157 Location location = SerializeUtil.deserializeLocation(rs.getObject(TableOneFirstRow));
158 UUID playerUUID = UUID.fromString(rs.getString(TableOnesecondRow));
159 boolean filterWhiteBlack = rs.getBoolean(TableOneThirdRow);
160 TypeOfContainer typeOfContainer = TypeOfContainer.valueOf(rs.getString(TableOneFourthRow));
161 int suctionRange = rs.getInt(TableOneFifthRow);
162 int linkRange = rs.getInt(TableOneSixthRow);
163 int linkAmount = rs.getInt(TableOneSeventhRow);
164 String nextUpgrade = rs.getString(TableOneEighthRow);
165 ItemStack[] chestItems = SerializDeserialzItemStackFromToDatabase.itemStackArrayFromBase64(rs.getString(TableOneNineRow));
166 String containerSettingsName = rs.getString(TableOneTenRow);
167 int numberOfPages = rs.getInt(TableOneElevenRow);
168 ItemStack[] filterItems = rs.getString(TableOneTwelveRow) != null ?
169 (SerializDeserialzItemStackFromToDatabase.itemStackArrayFromBase64(rs.getString(TableOneTwelveRow))) : (new ItemStack[]{(new ItemStack(Material.AIR))});
170 String linkedLocations = rs.getString(TableOneThirteenRow);
171 String amountOfItems = rs.getString(TableOneFourteenRow);
172 BigInteger convertedToBigInteger = new BigInteger(amountOfItems);
173
174 ArrayList<Location> linkedlocations = converting.convertFromJsonList(linkedLocations);
175
176 ChestRegistry.getInstance().addChestToCacheFromSQL(location, new ChestData(playerUUID,
177 nextUpgrade, typeOfContainer, containerSettingsName, numberOfPages,
178 filterWhiteBlack, suctionRange, linkRange, linkAmount, filterItems,
179 chestItems, linkedlocations, convertedToBigInteger));
180
181 }
182 } catch (SQLException throwables) {
183 throwables.printStackTrace();
184 } finally {
185 try {
186 if (ps != null)
187 ps.close();
188 if (this.connection != null)
189 this.connection.close();
190 } catch (SQLException ex) {
191 ChestStorage.getInstance().getLogger().log(Level.SEVERE, Errors.sqlConnectionClose(), ex);
192 }
193 }
194 }
195
196 public void removeChestfromSQlites(Location location) {
197 List<String> sqls = new ArrayList<>();
198 String sql = "DELETE FROM " + TableNameChest + " WHERE " + TableOneFirstRow + " = '" + SerializeUtil.serializeLoc(location) + "'";
199
200 sqls.add(sql);
201 this.batchUpdate(sqls);
202 }
203
204 public void saveItemsToSQlites(Location location, ItemStack[] itemStack) {
205 List<String> sqls = new ArrayList<>();
206 String sql = "UPDATE " + TableNameChest + " SET " + TableOneNineRow + " = '" + SerializDeserialzItemStackFromToDatabase.itemStackArrayToBase64(itemStack) + "' WHERE " + TableOneFirstRow + " = '" + SerializeUtil.serializeLoc(location) + "'";
207
208 sqls.add(sql);
209 this.batchUpdate(sqls);
210 }
211
212 public void saveFilterListToSQlites(Location location, ItemStack[] itemStack) {
213 List<String> sqls = new ArrayList<>();
214 String sql = "UPDATE " + TableNameChest + " SET " + TableOneTwelveRow + " = '" + SerializDeserialzItemStackFromToDatabase.itemStackArrayToBase64(itemStack) + "' WHERE " + TableOneFirstRow + " = '" + SerializeUtil.serializeLoc(location) + "'";
215
216 sqls.add(sql);
217 this.batchUpdate(sqls);
218 }
219
220 @SneakyThrows
221 public void saveDataOnBlockPlace(ChestData chestData, Location location) {
222 List<String> sqls = new ArrayList<>();
223
224 String sql = "REPLACE INTO " + TableNameChest + " (" + TableOneFirstRow
225 + "," + TableOnesecondRow + "," + TableOneThirdRow
226 + "," + TableOneFourthRow + "," + TableOneFifthRow + "," + TableOneSixthRow
227 + "," + TableOneSeventhRow + "," + TableOneEighthRow + "," + TableOneNineRow
228 + "," + TableOneTenRow + "," + TableOneElevenRow
229 + ") VALUES('" + SerializeUtil.serializeLoc(location)
230 + "','" + chestData.getPlayerId().toString() + "','" + chestData.isFilterWhiteBlack()
231 + "','" + chestData.getTypeOfContainer() + "','" + chestData.getSuctionRange()
232 + "','" + chestData.getLinkRange() + "','" + chestData.getLinkAmount()
233 + "','" + chestData.getNextUpgrade()
234 + "','" + SerializDeserialzItemStackFromToDatabase.itemStackArrayToBase64(chestData.getChestContents())
235 + "','" + chestData.getContainerFileName() + "','" + chestData.getNumberOfPages() + "')";
236 sqls.add(sql);
237
238
239 sqls.add(sql);
240
241 //System.out.println(sqls);
242 this.batchUpdate(sqls);
243 }
244
245 //TODO Make data values more cleaner.
246 @SneakyThrows
247 public void saveToSQlites(Map<Location, ChestData> chests) {
248 List<String> sqls = new ArrayList<>();
249 for (Map.Entry<Location, ChestData> entry : chests.entrySet()) {
250 Location location = entry.getKey();
251 ChestData chestData = entry.getValue();
252
253 String sql = "REPLACE INTO " + TableNameChest + " (" + TableOneFirstRow
254 + "," + TableOnesecondRow + "," + TableOneThirdRow
255 + "," + TableOneFourthRow + "," + TableOneFifthRow + "," + TableOneSixthRow
256 + "," + TableOneSeventhRow + "," + TableOneEighthRow + "," + TableOneNineRow
257 + "," + TableOneTenRow + "," + TableOneElevenRow + "," + TableOneTwelveRow
258 + "," + TableOneThirteenRow + "," + TableOneFourteenRow
259 + ") VALUES('" + SerializeUtil.serializeLoc(location)
260 + "','" + chestData.getPlayerId().toString() + "','" + chestData.isFilterWhiteBlack()
261 + "','" + chestData.getTypeOfContainer() + "','" + chestData.getSuctionRange()
262 + "','" + chestData.getLinkRange() + "','" + chestData.getLinkAmount()
263 + "','" + chestData.getNextUpgrade()
264 + "','" + SerializDeserialzItemStackFromToDatabase.itemStackArrayToBase64(chestData.getChestContents())
265 + "','" + chestData.getContainerFileName() + "','" + chestData.getNumberOfPages()
266 + "','" + SerializDeserialzItemStackFromToDatabase.itemStackArrayToBase64(chestData.getFilterItems() != null ? chestData.getFilterItems() : new ItemStack[]{(new ItemStack(Material.AIR))})
267 + "','" + converting.convertToJsonList("Locations", chestData.getLocations()) + "','" + chestData.getAmountOfItems().toString() + "')";
268
269
270 sqls.add(sql);
271 }
272 //System.out.println(sqls);
273 this.batchUpdate(sqls);
274 }
275
276 protected final void batchUpdate(@NonNull List<String> sqls) {
277 this.connection = connect();
278
279 if (sqls.size() == 0)
280 return;
281 try {
282
283
284 final Statement batchStatement = this.connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
285 final int processedCount = sqls.size();
286
287 // Prevent automatically sending db instructions
288 this.connection.setAutoCommit(false);
289
290
291 for (final String sql : sqls)
292 batchStatement.addBatch(sql);
293 if (processedCount > 10_000)
294 Common.log("Updating your database (" + processedCount + " entries)... PLEASE BE PATIENT THIS WILL TAKE "
295 + (processedCount > 50_000 ? "10-20 MINUTES" : "5-10 MINUTES") + " - If server will print a crash report, ignore it, update will proceed.");
296
297 // Set the flag to start time notifications timer
298 batchUpdateGoingOn = true;
299
300 // Notify console that progress still is being made
301 new Timer().scheduleAtFixedRate(new TimerTask() {
302
303 @Override
304 public void run() {
305 if (batchUpdateGoingOn)
306 Common.log("Still executing, DO NOT SHUTDOWN YOUR SERVER.");
307 else
308 cancel();
309 }
310 }, 1000 * 30, 1000 * 30);
311
312 // Execute
313 batchStatement.executeBatch();
314
315 // This will block the thread
316 this.connection.commit();
317
318 //Common.log("Updated " + processedCount + " database entries.");
319
320 } catch (final Throwable t) {
321 t.printStackTrace();
322
323 } finally {
324 try {
325 this.connection.setAutoCommit(true);
326
327 } catch (final SQLException ex) {
328 ex.printStackTrace();
329 }
330
331 // Even in case of failure, cancel
332 batchUpdateGoingOn = false;
333 }
334 }
335
336
337 public void initialize() {
338 try {
339 PreparedStatement preparedStatement = this.connection.prepareStatement("SELECT * FROM " + TableNameChest + " WHERE " + TableOnesecondRow + " = ?");
340 ResultSet resultSet = preparedStatement.executeQuery();
341 close(preparedStatement, resultSet);
342
343 } catch (SQLException ex) {
344 ChestStorage.getInstance().getLogger().log(Level.SEVERE, "Unable to retreive connection", ex);
345 }
346 }
347
348 public void close(PreparedStatement preparedStatement, ResultSet resultSet) {
349 try {
350 if (preparedStatement != null)
351 preparedStatement.close();
352 if (resultSet != null)
353 resultSet.close();
354 } catch (SQLException ex) {
355 ex.printStackTrace();
356 /*Error.close(ChestStorage.getInstance(), ex);*/
357 }
358 }
359
360}
361