· 4 years ago · Aug 26, 2021, 08:28 PM
1public abstract class Database {
2
3 private final File chestStorage = ChestStorage.getData();
4 protected Connection connection;
5 private boolean batchUpdateGoingOn = false;
6 private Map<String, List<String>> plainColums = new HashMap<>();
7 private Map<String, List<String>> columns = new HashMap<>();
8 private List<String> addNewColumns;
9 boolean hasStartWriteToDb = false;
10
11 private boolean loadDataStartup = true;
12
13 private final List<String> columListContainerdata = new ArrayList<>(Arrays.asList("Location", "Player_UUID", "FilterWhiteBlack", "Chest_Type"
14 , "Next_Upgrade", "Chest_contents", "SettingsName", "AmountOfPages",
15 "FilterItems", "Link_location", "AmountOfItems"));
16 private final List<String> columListUserdata = new ArrayList<>(Arrays.asList("Player_UUID", "Show_Border_Suction_Reach",
17 "Show_Effects_When_Place_Container", "Show_Holograms_When_Place_Container", "Partylist_Players"));
18
19 protected final String tableNameContainer;
20 protected final String tableNameForUserSettings;
21
22 public Database(String tableNameContainer, String tableNameForUserSettings) {
23 this.tableNameContainer = tableNameContainer;
24 this.tableNameForUserSettings = tableNameForUserSettings;
25 }
26
27 public abstract Connection connect();
28
29 public void createTables() throws SQLException {
30 boolean hasCreateTabels = false;
31 Map<String, String> tabels = new HashMap<>();
32 tabels.put(tableNameContainer, "CREATE TABLE IF NOT EXISTS " + tableNameContainer + " (" + prepereColums(tableNameContainer) + ");");
33 tabels.put(tableNameForUserSettings, "CREATE TABLE IF NOT EXISTS " + tableNameForUserSettings + " (" + prepereColums(tableNameForUserSettings) + ");");
34
35 for (Map.Entry<String, String> entityTables : tabels.entrySet()) {
36 if (load(entityTables.getValue()))
37 hasCreateTabels = true;
38 }
39 if (hasCreateTabels) {
40 if (checkColumExist())
41 dropColumn(tableNameContainer, tableNameContainer, new String[]{"Suction_Range", "Link_Range", "Link_Amount"});
42 }
43 }
44
45 protected String prepereColums(final String tableName) {
46 List<String> transform = new ArrayList<>();
47
48 if (tableName.equals(tableNameForUserSettings))
49 for (String colums : columListUserdata) {
50 if (colums.equals("Player_UUID"))
51 transform.add(colums + " VARCHAR(64) PRIMARY KEY");
52 else if (colums.equals("Partylist_Players"))
53 transform.add(colums + " VARCHAR(1000)");
54 else
55 transform.add(colums + " VARCHAR(120)");
56 }
57 else if (tableName.equals(tableNameContainer))
58 for (String colums : columListContainerdata) {
59 switch (colums) {
60 case "Location":
61 transform.add(colums + " VARCHAR(64) PRIMARY KEY");
62 break;
63 case "Chest_contents":
64 transform.add(colums + " VARCHAR(4000)");
65 break;
66 case "AmountOfItems":
67 transform.add(colums + " VARCHAR(500)");
68 break;
69 default:
70 transform.add(colums + " VARCHAR(120)");
71 break;
72 }
73 }
74
75 return TextUtils(transform);
76 }
77
78 protected void getTableColumns(String... tableNames) throws SQLException {
79
80 this.connection = connect();
81
82 for (String tabel : tableNames) {
83 List<String> column = new ArrayList<>();
84 List<String> columnss = new ArrayList<>();
85 PreparedStatement statement = this.connection.prepareStatement("SELECT * FROM " + tabel);
86 ResultSet rs = statement.executeQuery();
87 ResultSetMetaData rsmd = rs.getMetaData();
88 int columnCount = rsmd.getColumnCount();
89
90 for (int i = 1; i <= columnCount; i++) {
91
92 if (!rsmd.getColumnTypeName(i).equals("NUMERIC") && !(rsmd.getPrecision(i) == 0)) {
93 columnss.add(rsmd.getColumnLabel(i) + " " + rsmd.getColumnTypeName(i) + "(" + rsmd.getPrecision(i) + ")" +
94 ((tabel.equals(tableNameContainer) && rsmd.getColumnLabel(i).equals("Location")) ||
95 (tabel.equals(tableNameForUserSettings) && rsmd.getColumnLabel(i).equals("Player_UUID")) ? "PRIMARY KEY" : ""));
96 }
97 column.add(rsmd.getColumnName(i));
98 }
99 if (this.addNewColumns != null && !this.addNewColumns.toString().equals("[]")) {
100 columnss.addAll(this.addNewColumns);
101 }
102
103 close(statement, rs);
104 plainColums.put(tabel, column);
105 columns.put(tabel, columnss);
106 }
107
108 }
109
110 public void addNewColumns(String... addColumns) {
111 this.addNewColumns = Arrays.asList(addColumns);
112 }
113
114 protected void dropColumn(String createTableCmd, String tableName, String[] colsToRemove) throws java.sql.SQLException {
115 PreparedStatement movedata;
116 getTableColumns(tableName);
117 List<String> updatedTableColumns = columns.get(tableName);
118
119 if (updatedTableColumns == null || updatedTableColumns.toString().equals("[]")) return;
120 // Remove the columns we don't want anymore from the table's list of columns
121
122 if (colsToRemove != null)
123 updatedTableColumns.removeAll(Arrays.asList(colsToRemove));
124
125 String columnsSeperated = TextUtils(updatedTableColumns);//StringUtils.join(updatedTableColumns, ",");
126
127 connection = connect();
128
129 // Rename the old table, so we can remove old name and rename colums.
130 PreparedStatement alterTable = connection.prepareStatement("ALTER TABLE " + tableName + " RENAME TO " + tableName + "_old;");
131 // Creating the table on its new format (no redundant columns)
132 PreparedStatement createTable = connection.prepareStatement("CREATE TABLE IF NOT EXISTS " + tableName + " (" + columnsSeperated + ");");
133
134 alterTable.execute();
135 createTable.execute();
136
137 // Populating the table with the data
138 movedata = connection.prepareStatement("INSERT INTO " + tableName + "(" + TextUtils(this.plainColums.get(tableName)) + ") SELECT "
139 + TextUtils(this.plainColums.get(tableName)) + " FROM " + tableName + "_old;");
140 movedata.execute();
141
142 PreparedStatement removeOldtable = connection.prepareStatement("DROP TABLE " + tableName + "_old;");
143 removeOldtable.execute();
144
145 close(movedata, alterTable, createTable, removeOldtable);
146 }
147
148 protected boolean checkColumExist() throws SQLException {
149 Map<String, List<String>> tables = new HashMap<>();
150 getTableColumns(tableNameContainer, tableNameForUserSettings);
151 tables.put(tableNameContainer, plainColums.get(tableNameContainer));
152 tables.put(tableNameForUserSettings, plainColums.get(tableNameForUserSettings));
153
154 try {
155 this.connection = connect();
156 for (Map.Entry<String, List<String>> entrytabels : tables.entrySet()) {
157 for (String tablecolums : entrytabels.getValue())
158 for (String currentTabels : tabledata(entrytabels.getKey()))
159 if (tablecolums.equals(currentTabels)) return true;
160 else {
161 PreparedStatement statement = connection.prepareStatement("ALTER TABLE '" + entrytabels.getKey() +
162 "' ADD COLUMN '" + tablecolums + "';");
163 System.out.println("add new column on key " + entrytabels.getKey() + " column " + tablecolums);
164 statement.execute();
165 close(statement);
166 return false;
167 }
168 }
169 } catch (SQLException throwables) {
170 throwables.printStackTrace();
171 }
172 return true;
173 }
174
175 protected List<String> tabledata(String tableName) {
176 if (tableName.equals(tableNameForUserSettings)) {
177 return columListUserdata;
178 } else if (tableName.equals(tableNameContainer))
179 return columListContainerdata;
180
181 return null;
182 }
183
184 protected String TextUtils(List<String> colums) {
185 return colums.toString().replace("[", "").replace("]", "");
186 }
187
188 public boolean load(String sql) {
189 this.connection = connect();
190 try {
191 PreparedStatement statement = this.connection.prepareStatement(sql);
192
193 statement.executeUpdate();
194 close(statement);
195 return true;
196 } catch (SQLException e) {
197 e.printStackTrace();
198 }
199 initialize();
200 return true;
201 }
202
203 protected final void batchUpdate(@NonNull List<String> batchupdate) {
204 ArrayList<String> sqls = new ArrayList<>(batchupdate);
205 this.connection = connect();
206
207 if (sqls.size() == 0)
208 return;
209
210 if (!hasStartWriteToDb)
211 try {
212 hasStartWriteToDb = true;
213 final Statement batchStatement = this.connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
214 final int processedCount = sqls.size();
215
216 // Prevent automatically sending db instructions
217 this.connection.setAutoCommit(false);
218
219
220 for (final String sql : sqls)
221 batchStatement.addBatch(sql);
222 if (processedCount > 10_000)
223 Common.log("Updating your database (" + processedCount + " entries)... PLEASE BE PATIENT THIS WILL TAKE "
224 + (processedCount > 50_000 ? "10-20 MINUTES" : "5-10 MINUTES") + " - If server will print a crash report, ignore it, update will proceed.");
225
226 // Set the flag to start time notifications timer
227 batchUpdateGoingOn = true;
228
229 // Notify console that progress still is being made
230 new Timer().scheduleAtFixedRate(new TimerTask() {
231
232 @Override
233 public void run() {
234 if (batchUpdateGoingOn)
235 Common.log("Still executing, DO NOT SHUTDOWN YOUR SERVER.");
236 else
237 cancel();
238 }
239 }, 1000 * 30, 1000 * 30);
240
241 // Execute
242 batchStatement.executeBatch();
243
244 // This will block the thread
245 this.connection.commit();
246
247 Common.log("Updated " + processedCount + " database entries.");
248
249 } catch (final Throwable t) {
250 t.printStackTrace();
251
252 } finally {
253 try {
254 this.connection.setAutoCommit(true);
255
256 } catch (final SQLException ex) {
257 ex.printStackTrace();
258 }
259 hasStartWriteToDb = false;
260 // Even in case of failure, cancel
261 batchUpdateGoingOn = false;
262 }
263 }
264
265
266 public void disconnect() {
267 try {
268 if (connection != null) {
269 connection.close();
270 }
271 } catch (SQLException exception) {
272 exception.printStackTrace();
273 }
274 }
275
276 public void initialize() {
277 try {
278 PreparedStatement preparedStatement = this.connection.prepareStatement("SELECT * FROM " + tableNameContainer + " WHERE " + columListContainerdata.get(0) + " = ?");
279 ResultSet resultSet = preparedStatement.executeQuery();
280 close(preparedStatement, resultSet);
281
282 } catch (SQLException ex) {
283 ChestStorage.getInstance().getLogger().log(Level.SEVERE, "Unable to retreive connection", ex);
284 }
285 }
286
287 public void close(PreparedStatement... preparedStatement) {
288 for (PreparedStatement statement : preparedStatement)
289 close(statement, null);
290 }
291
292 public void close(PreparedStatement preparedStatement, ResultSet resultSet) {
293 try {
294 if (preparedStatement != null)
295 preparedStatement.close();
296 if (resultSet != null)
297 resultSet.close();
298 } catch (SQLException ex) {
299 ex.printStackTrace();
300 /*Error.close(ChestStorage.getInstance(), ex);*/
301 }
302 }
303
304 public void saveToSQlitesUserSettings(Map<UUID, PreferenceSettingsRegistery.SettingsData> preferenceSettingsRegistery) {
305 }
306
307 public void saveToSQlites(Map<Location, ContainerRegistry.ChestData> chests) {
308
309 }
310
311 public void loadFromSQliteAllPlayerSettings() {
312
313 }
314
315 public void loadFromSQliteAllContainerdata() {
316
317 }
318
319 public void removeChestfromSQlites(Location location) {
320
321 }
322
323 public void getContainerfromSQlites(Location location) {
324 }
325
326}
327