1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 package Utilities;
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39 import Exceptions.AccessException;
40 import Exceptions.CustomerNotFoundException;
41 import Exceptions.VersionException;
42 import Launchers.Settings;
43 import com.mysql.jdbc.exceptions.jdbc4.CommunicationsException;
44 import com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException;
45 import com.zaxxer.hikari.HikariConfig;
46 import com.zaxxer.hikari.HikariDataSource;
47 import com.zaxxer.hikari.pool.HikariPool;
48 import javafx.scene.control.Alert;
49 import javafx.scene.control.ButtonBar;
50 import javafx.scene.control.ButtonType;
51 import javafx.util.Pair;
52 import org.flywaydb.core.Flyway;
53
54 import java.sql.*;
55 import java.sql.Date;
56 import java.text.SimpleDateFormat;
57 import java.util.*;
58
59
60
61
62 @SuppressWarnings("unused")
63 public class DbInt {
64 public static String prefix = "ABOS-Test-";
65
66 private static HashMap<String, HikariDataSource> connectionPools = new HashMap<>();
67 private static boolean isConfigured = false;
68 private static Utilities.Settable<String> currentUserName = new Utilities.Settable<>("", "");
69 private static Utilities.Settable<Version> databaseVersion = new Utilities.Settable<>(new Version(-1, -1, -1), new Version(-1, -1, -1));
70 private static String username;
71 private static String password;
72 private static boolean isAdmin;
73
74 public static void setPrefix(String newPrefix) {
75 prefix = newPrefix;
76 }
77
78
79
80
81
82
83
84 public static String getCustInf(String yearL, int id, String info) {
85 return getCustInf(yearL, id, info, "");
86 }
87
88
89
90
91
92
93
94
95
96 public static String getCustInf(String yearL, Integer ID, String info, String defaultVal) {
97 String ret = defaultVal;
98
99 try (Connection con = DbInt.getConnection(yearL);
100 PreparedStatement prep = con.prepareStatement("SELECT * FROM customerview WHERE idCustomers=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
101 prep.setInt(1, ID);
102 try (ResultSet rs = prep.executeQuery()) {
103
104 while (rs.next()) {
105
106 ret = rs.getString(info);
107
108 }
109 }
110
111
112 } catch (SQLException e) {
113 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
114 }
115
116
117 return ret;
118 }
119
120 @Nonnull
121 public static Connection getConnection(String Db) throws SQLException {
122
123 try {
124 Class.forName("com.mysql.jdbc.Driver");
125 } catch (ClassNotFoundException e) {
126
127 LogToFile.log(e, Severity.SEVERE, "Error loading database library. Please try reinstalling or contacting support.");
128 }
129
130 String url = String.format("jdbc:mysql://%s/%s?useSSL=%s", Config.getDbLoc(), prefix + Db, Config.getSSL());
131 try {
132 if (connectionPools.containsKey(url)) {
133 Connection con = connectionPools.get(url).getConnection();
134 if (con == null) {
135 throw new SQLException("Unable to acquire connection", "08001");
136 }
137 return con;
138 } else {
139 HikariConfig config = new HikariConfig();
140 config.setJdbcUrl(url);
141 config.setUsername(username);
142 config.setPassword(password);
143 config.addDataSourceProperty("cachePrepStmts", "true");
144 config.addDataSourceProperty("prepStmtCacheSize", "250");
145 config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
146 config.addDataSourceProperty("useServerPrepStmts", "true");
147 config.addDataSourceProperty("useLocalSessionState", "true");
148 config.addDataSourceProperty("useLocalTransactionState", "true");
149 config.addDataSourceProperty("rewriteBatchedStatements", "true");
150 config.addDataSourceProperty("cacheResultSetMetadata", "true");
151 config.addDataSourceProperty("cacheServerConfiguration", "true");
152 config.addDataSourceProperty("elideSetAutoCommits", "true");
153 config.addDataSourceProperty("maintainTimeStats", "false");
154 HikariDataSource ds = new HikariDataSource(config);
155 connectionPools.put(url, ds);
156 Connection con = ds.getConnection();
157 if (con == null) {
158 throw new SQLException("Unable to acquire connection", "08001");
159 }
160 databaseVersion.setIfNot(new Version(con.getMetaData().getDatabaseProductVersion()));
161
162 return con;
163
164 }
165
166
167
168 } catch (CommunicationsException e) {
169 promptConfig();
170 } catch (SQLException ex) {
171
172 if (((ex.getErrorCode() == 50000)
173 && ("XJ015".equals(ex.getSQLState())))) {
174
175 LogToFile.log(ex, Severity.FINER, "Derby shut down normally");
176
177 } else {
178 if (Objects.equals(ex.getSQLState(), "42000")) {
179 Alert alert = new Alert(Alert.AlertType.CONFIRMATION);
180 alert.setTitle("ERROR!");
181 alert.setHeaderText("The program cannot find the specified database");
182 alert.setContentText("Would you like to open the settings Dialog to create it?");
183
184
185 Optional<ButtonType> result = alert.showAndWait();
186 if (result.get() == ButtonType.OK) {
187 new Settings(null);
188 return getConnection(Db);
189 } else {
190 Alert closingWarning = new Alert(Alert.AlertType.WARNING);
191 closingWarning.setTitle("Warning!");
192 closingWarning.setHeaderText("The program cannot run without the database");
193 closingWarning.setContentText("Application is closing. Please restart application and create the database in the setting dialog.");
194
195
196 closingWarning.showAndWait();
197 System.exit(0);
198 }
199 LogToFile.log(ex, Severity.SEVERE, "");
200 } else {
201 LogToFile.log(ex, Severity.WARNING, CommonErrors.returnSqlMessage(ex));
202 }
203 }
204
205 } catch (HikariPool.PoolInitializationException ex) {
206 if (ex.getCause() instanceof MySQLSyntaxErrorException) {
207 if (Objects.equals(((MySQLSyntaxErrorException) ex.getCause()).getSQLState(), "42000")) {
208 Alert alert = new Alert(Alert.AlertType.CONFIRMATION);
209 alert.setTitle("ERROR!");
210 alert.setHeaderText("The program cannot find the specified database");
211 alert.setContentText("Would you like to open the settings Dialog to create it?");
212
213
214 Optional<ButtonType> result = alert.showAndWait();
215 if (result.get() == ButtonType.OK) {
216 new Settings(null);
217 return getConnection(Db);
218 } else {
219 Alert closingWarning = new Alert(Alert.AlertType.WARNING);
220 closingWarning.setTitle("Warning!");
221 closingWarning.setHeaderText("The program cannot run without the database");
222 closingWarning.setContentText("Application is closing. Please restart application and create the database in the setting dialog.");
223
224
225 closingWarning.showAndWait();
226 System.exit(0);
227 }
228 LogToFile.log(ex, Severity.SEVERE, "");
229 } else {
230 LogToFile.log(ex, Severity.WARNING, CommonErrors.returnSqlMessage(((MySQLSyntaxErrorException) ex.getCause())));
231 }
232 } else {
233 LogToFile.log(ex, Severity.SEVERE, "");
234
235 }
236
237 }
238 throw new SQLException("Unable to acquire connection", "08001");
239 }
240
241 public static boolean testConnection() {
242
243 try {
244 Class.forName("com.mysql.jdbc.Driver");
245 } catch (ClassNotFoundException e) {
246
247 LogToFile.log(e, Severity.SEVERE, "Error loading database library. Please try reinstalling or contacting support.");
248 }
249
250
251 String url = String.format("jdbc:mysql://%s/%s?useSSL=%s", Config.getDbLoc(), prefix + "Commons", Config.getSSL());
252
253 try {
254 if (connectionPools.containsKey(url)) {
255 Connection con = connectionPools.get(url).getConnection();
256 if (con == null) {
257 throw new SQLException("Unable to acquire connection", "08001");
258 }
259 return con.isValid(15);
260 } else {
261 HikariConfig config = new HikariConfig();
262 config.setJdbcUrl(url);
263 config.setUsername(username);
264 config.setPassword(password);
265 config.addDataSourceProperty("cachePrepStmts", "true");
266 config.addDataSourceProperty("prepStmtCacheSize", "250");
267 config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
268 config.addDataSourceProperty("useServerPrepStmts", "true");
269 config.addDataSourceProperty("useLocalSessionState", "true");
270 config.addDataSourceProperty("useLocalTransactionState", "true");
271 config.addDataSourceProperty("rewriteBatchedStatements", "true");
272 config.addDataSourceProperty("cacheResultSetMetadata", "true");
273 config.addDataSourceProperty("cacheServerConfiguration", "true");
274 config.addDataSourceProperty("elideSetAutoCommits", "true");
275 config.addDataSourceProperty("maintainTimeStats", "false");
276 HikariDataSource ds = new HikariDataSource(config);
277 connectionPools.put(url, ds);
278 Connection con = ds.getConnection();
279 if (con == null) {
280 throw new SQLException("Unable to acquire connection", "08001");
281 }
282 return con.isValid(15);
283
284 }
285
286
287
288
289
290
291 } catch (Exception e) {
292 return false;
293 }
294 }
295
296
297
298
299
300
301 @Nonnull
302 public static Connection getConnection() throws SQLException {
303 try {
304 Class.forName("com.mysql.jdbc.Driver");
305 } catch (ClassNotFoundException e) {
306
307 LogToFile.log(e, Severity.SEVERE, "Error loading database library. Please try reinstalling or contacting support.");
308 }
309 String url = String.format("jdbc:mysql://%s/?useSSL=%s", Config.getDbLoc(), Config.getSSL());
310
311 try {
312 if (connectionPools.containsKey(url)) {
313 Connection con = connectionPools.get(url).getConnection();
314 if (con == null) {
315 throw new SQLException("Unable to acquire connection", "08001");
316 }
317 return con;
318 } else {
319 HikariConfig config = new HikariConfig();
320 config.setJdbcUrl(url);
321 config.setUsername(username);
322 config.setPassword(password);
323 config.addDataSourceProperty("cachePrepStmts", "true");
324 config.addDataSourceProperty("prepStmtCacheSize", "250");
325 config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
326 config.addDataSourceProperty("useServerPrepStmts", "true");
327 config.addDataSourceProperty("useLocalSessionState", "true");
328 config.addDataSourceProperty("useLocalTransactionState", "true");
329 config.addDataSourceProperty("rewriteBatchedStatements", "true");
330 config.addDataSourceProperty("cacheResultSetMetadata", "true");
331 config.addDataSourceProperty("cacheServerConfiguration", "true");
332 config.addDataSourceProperty("elideSetAutoCommits", "true");
333 config.addDataSourceProperty("maintainTimeStats", "false");
334 HikariDataSource ds = new HikariDataSource(config);
335 connectionPools.put(url, ds);
336 Connection con = ds.getConnection();
337
338 if (con == null) {
339 throw new SQLException("Unable to acquire connection", "08001");
340 }
341 databaseVersion.setIfNot(new Version(con.getMetaData().getDatabaseProductVersion()));
342 return con;
343
344 }
345
346 } catch (CommunicationsException e) {
347 promptConfig();
348 } catch (SQLException ex) {
349
350
351 if (((ex.getErrorCode() == 50000)
352 && ("XJ015".equals(ex.getSQLState())))) {
353
354 LogToFile.log(ex, Severity.FINER, "Derby shut down normally");
355
356 } else {
357
358
359 LogToFile.log(ex, Severity.WARNING, CommonErrors.returnSqlMessage(ex));
360
361 }
362
363 }
364 throw new SQLException("Unable to acquire connection", "08001");
365 }
366
367
368
369
370
371
372
373
374 public static Boolean createDb(String DB) {
375
376
377
378
379
380 try {
381 Class.forName("com.mysql.jdbc.Driver");
382 } catch (ClassNotFoundException e) {
383
384 LogToFile.log(e, Severity.SEVERE, "Error loading database library. Please try reinstalling or contacting support.");
385 }
386
387
388 String url = String.format("jdbc:mysql://%s/?useSSL=%s", Config.getDbLoc(), Config.getSSL());
389
390 try (Connection con = DriverManager.getConnection(url, username, password);
391 Statement st = con.createStatement()) {
392 int Result = st.executeUpdate("CREATE DATABASE `" + prefix + DB + "`");
393
394 } catch (CommunicationsException e) {
395 promptConfig();
396 } catch (SQLException ex) {
397
398
399 if (((ex.getErrorCode() == 50000)
400 && ("XJ015".equals(ex.getSQLState())))) {
401
402 LogToFile.log(ex, Severity.FINER, "Derby shut down normally");
403
404 } else if (ex.getErrorCode() == 1007) {
405 return false;
406 } else {
407
408 LogToFile.log(ex, Severity.SEVERE, ex.getMessage());
409 }
410
411 }
412 Flyway flyway = new Flyway();
413 flyway.setDataSource(url, username, password);
414 flyway.baseline();
415
416 return true;
417 }
418
419 public static void deleteAllDB() {
420 getYears().forEach(year -> {
421 String createAndGrantCommand = "DROP USER IF EXISTS'" + year + "'@'localhost'";
422 try (Connection con = DbInt.getConnection();
423 PreparedStatement prep = con.prepareStatement("", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
424
425 prep.addBatch(createAndGrantCommand);
426 prep.executeBatch();
427 } catch (SQLException e) {
428 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
429 }
430 deleteDb(year);
431 });
432 DbInt.deleteDb("Commons");
433
434 }
435
436 public static void createSetAndTables() {
437
438
439
440
441
442
443 DbInt.createDb("Commons");
444
445 try (Connection con = DbInt.getConnection("Commons");
446 PreparedStatement prep = con.prepareStatement("CREATE TABLE `Users` (\n" +
447 " `idUsers` int(11) NOT NULL AUTO_INCREMENT,\n" +
448 " `userName` varchar(255) NOT NULL,\n" +
449 " `fullName` varchar(255) NOT NULL,\n" +
450 " `Admin` int(11) NOT NULL,\n" +
451 " `Years` varchar(255) NOT NULL,\n" +
452 " PRIMARY KEY (`idUsers`)\n" +
453 ")", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
454 prep.execute();
455 } catch (SQLException e) {
456 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
457 }
458 try (Connection con = DbInt.getConnection("Commons");
459 PreparedStatement prep = con.prepareStatement("CREATE TABLE `Settings` (\n" +
460 " `key` VARCHAR(45) NOT NULL,\n" +
461 " `Value` VARCHAR(255) NULL,\n" +
462 " PRIMARY KEY (`key`));\n", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
463 prep.execute();
464 } catch (SQLException e) {
465 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
466 }
467 try (Connection con = DbInt.getConnection("Commons");
468 PreparedStatement prep = con.prepareStatement("CREATE TABLE `Years` (\n" +
469 " `idYear` int(11) NOT NULL AUTO_INCREMENT,\n" +
470 " `Year` varchar(4) NOT NULL,\n" +
471 " PRIMARY KEY (`idYear`));",
472 ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
473 prep.execute();
474 } catch (SQLException e) {
475 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
476 }
477 try (Connection con = DbInt.getConnection("Commons");
478 PreparedStatement prep = con.prepareStatement("CREATE\n" +
479 " ALGORITHM = UNDEFINED\n" +
480 " DEFINER = `admin`@`localhost`\n" +
481 " SQL SECURITY DEFINER\n" +
482 "VIEW `" + prefix + "Commons`.`userView` AS\n" +
483 " SELECT\n" +
484 " `" + prefix + "Commons`.`Users`.`idUsers` AS `idUsers`,\n" +
485 " `" + prefix + "Commons`.`Users`.`userName` AS `userName`,\n" +
486 " `" + prefix + "Commons`.`Users`.`fullName` AS `fullName`,\n" +
487 " `" + prefix + "Commons`.`Users`.`Admin` AS `Admin`,\n" +
488
489 " `" + prefix + "Commons`.`Users`.`Years` AS `Years`\n" +
490 " FROM\n" +
491 " `" + prefix + "Commons`.`Users`\n" +
492 " WHERE\n" +
493 " (`" + prefix + "Commons`.`Users`.`userName` = LEFT(USER(), (LOCATE('@', USER()) - 1))) WITH CASCADED CHECK OPTION", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
494 prep.execute();
495 } catch (SQLException e) {
496 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
497 }
498 try (Connection con = DbInt.getConnection("Commons");
499 PreparedStatement prep = con.prepareStatement("INSERT INTO Users(userName, fullName, Admin, Years) Values (?, ?, 1, '')", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
500 prep.setString(1, username);
501 prep.setString(2, username);
502
503 prep.execute();
504 } catch (SQLException e) {
505 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
506 }
507 try (Connection con = DbInt.getConnection("Commons");
508 PreparedStatement prep = con.prepareStatement("INSERT INTO Settings(key, Value) Values (?, ?)", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
509 prep.setString(1, "Version");
510 prep.setString(2, Config.getProgramVersion().toString());
511
512 prep.execute();
513 } catch (SQLException e) {
514 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
515 }
516 }
517
518 public static ArrayList<User> getUsers() throws AccessException {
519 if (isAdmin) {
520 ArrayList<User> ret = new ArrayList<>();
521 try (Connection con = DbInt.getConnection("Commons");
522 PreparedStatement prep = con.prepareStatement("SELECT userName, Years, fullName, Admin FROM Users", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
523 ResultSet rs = prep.executeQuery()) {
524 while (rs.next()) {
525
526 ret.add(new User(rs.getString("userName"), rs.getString("fullName"), rs.getString("Years"), rs.getInt("Admin") == 1));
527
528 }
529
530
531 } catch (SQLException e) {
532 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
533 }
534 return ret;
535 } else {
536 throw new AccessException("You are not an Admin");
537 }
538 }
539
540 public static void deleteDb(String DB) {
541
542 try {
543 Class.forName("com.mysql.jdbc.Driver");
544 } catch (ClassNotFoundException e) {
545
546 LogToFile.log(e, Severity.SEVERE, "Error loading database library. Please try reinstalling or contacting support.");
547 }
548
549
550 String url = String.format("jdbc:mysql://%s/?useSSL=%s", Config.getDbLoc(), Config.getSSL());
551
552 try (Connection con = DriverManager.getConnection(url, username, password);
553 Statement st = con.createStatement()) {
554 int Result = st.executeUpdate("DROP DATABASE `" + prefix + DB + "`");
555
556 } catch (CommunicationsException e) {
557 promptConfig();
558 } catch (SQLException ex) {
559
560
561 if (((ex.getErrorCode() == 50000)
562 && ("XJ015".equals(ex.getSQLState())))) {
563
564 LogToFile.log(ex, Severity.FINER, "Derby shut down normally");
565
566 } else {
567
568 LogToFile.log(ex, Severity.SEVERE, CommonErrors.returnSqlMessage(ex));
569 }
570
571 }
572
573
574 }
575
576 public static Iterable<String> getAllCustomerNames() {
577 Collection<String> ret = new ArrayList<>();
578 Iterable<String> years = getUserYears();
579 for (String year : years) {
580
581 try (Connection con = DbInt.getConnection(year);
582 PreparedStatement prep = con.prepareStatement("SELECT Name FROM customerview", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
583 ResultSet rs = prep.executeQuery()
584 ) {
585 while (rs.next()) {
586 String name = rs.getString("Name");
587 if (!ret.contains(name)) {
588 ret.add(name);
589 }
590
591 }
592
593
594 } catch (SQLException e) {
595 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
596 }
597 }
598
599
600 return ret;
601 }
602
603 public static Iterable<Customer> getAllCustomers() {
604
605 Collection<Customer> ret = new ArrayList<>();
606 Iterable<String> years = getUserYears();
607 for (String year : years) {
608
609 try (Connection con = DbInt.getConnection(year);
610 PreparedStatement prep = con.prepareStatement("SELECT Name,idCustomers FROM customerview", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
611 ResultSet rs = prep.executeQuery()) {
612 while (rs.next()) {
613 String name = rs.getString("Name");
614 ret.add(new Customer(rs.getInt("idCustomers"), year));
615
616
617 }
618
619
620 } catch (SQLException e) {
621 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
622 } catch (CustomerNotFoundException ignored) {
623 }
624 }
625
626
627 return ret;
628 }
629
630 public static String getYearsForUser(String uName) {
631 String csvRet = "";
632
633 try (Connection con = DbInt.getConnection("Commons");
634 PreparedStatement prep = con.prepareStatement("SELECT Years FROM Users WHERE userName=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
635 prep.setString(1, uName);
636 try (ResultSet rs = prep.executeQuery()) {
637 while (rs.next()) {
638
639 csvRet = (rs.getString("Years"));
640
641 }
642 }
643
644
645 } catch (SQLException e) {
646 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
647 }
648 return csvRet;
649 }
650
651 public static ArrayList<String> getUserYears() {
652 String csvRet = "";
653 ArrayList<String> ret = new ArrayList<>();
654
655 try (Connection con = DbInt.getConnection("Commons");
656 PreparedStatement prep = con.prepareStatement("SELECT YEARS FROM userView", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
657 ResultSet rs = prep.executeQuery()) {
658 while (rs.next()) {
659
660 csvRet = (rs.getString("YEARS"));
661
662 }
663
664
665 } catch (SQLException e) {
666 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
667 }
668
669 List<String> retL = new ArrayList<String>(Arrays.asList(csvRet.split("\\s*,\\s*")));
670 retL.forEach(year -> {
671 if (!year.isEmpty()) {
672 ret.add(year);
673 }
674 });
675 return ret;
676 }
677
678 public static ArrayList<String> getYears() {
679 String csvRet = "";
680 ArrayList<String> ret = new ArrayList<>();
681
682 try (Connection con = DbInt.getConnection("Commons");
683 PreparedStatement prep = con.prepareStatement("SELECT Year FROM Years", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
684 ResultSet rs = prep.executeQuery()) {
685 while (rs.next()) {
686
687 ret.add(rs.getString(1));
688
689 }
690
691
692 } catch (SQLException e) {
693 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
694 }
695
696
697 return ret;
698 }
699
700
701 public static String getCategoryDate(String catName, String year) {
702 Date ret = null;
703 try (Connection con = DbInt.getConnection(year);
704 PreparedStatement prep = con.prepareStatement("SELECT CatDate FROM categories WHERE catName=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
705 prep.setString(1, catName);
706
707 try (ResultSet rs = prep.executeQuery()) {
708
709 while (rs.next()) {
710
711 ret = rs.getDate(1);
712
713 }
714 }
715
716
717 } catch (SQLException e) {
718 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
719 }
720 String output;
721 SimpleDateFormat formatter;
722 formatter = new SimpleDateFormat("MM/dd/yyyy");
723 output = formatter.format(ret);
724 return output;
725 }
726
727 public static String getUserName() {
728 return currentUserName.orElseGetAndSet(() -> {
729 String ret = "";
730
731 try (Connection con = DbInt.getConnection();
732 PreparedStatement prep = con.prepareStatement("SELECT LEFT(USER(), (LOCATE('@', USER()) - 1)) as 'uName'", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
733
734
735 try (ResultSet rs = prep.executeQuery()) {
736
737 while (rs.next()) {
738
739 ret = rs.getString("uName");
740
741 }
742 }
743
744
745 } catch (SQLException e) {
746 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
747
748 }
749
750
751 return ret;
752 });
753
754 }
755
756 public static User getUser(String year) {
757 return new User(year);
758 }
759
760 public static User getCurrentUser() throws SQLException {
761 User curUser = null;
762 try (Connection con = DbInt.getConnection("Commons");
763 PreparedStatement prep = con.prepareStatement("SELECT Years, fullName, Admin FROM userView", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
764 ResultSet rs = prep.executeQuery()) {
765 while (rs.next()) {
766
767 curUser = new User(getUserName(), rs.getString("fullName"), rs.getString("Years"), rs.getInt("Admin") == 1);
768
769 }
770
771
772 } catch (SQLException e) {
773 if (Objects.equals(e.getSQLState(), "42000")) {
774 throw e;
775 } else {
776 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
777
778 }
779 }
780 return curUser;
781 }
782
783 public static ArrayList<String> getDatabses() {
784 ArrayList ret = new ArrayList();
785 ret.add("Commons");
786 ret.addAll(getYears());
787 return ret;
788 }
789
790 public static void migrateDatabase(String database, String location) throws AccessException {
791 if (!isAdmin()) {
792 throw new AccessException("Admin Access Required");
793 }
794
795 String url = String.format("jdbc:mysql://%s/%s?useSSL=%s", Config.getDbLoc(), prefix + database, Config.getSSL());
796 Flyway flyway = new Flyway();
797 flyway.setLocations(location);
798 flyway.setDataSource(url, username, password);
799 flyway.migrate();
800 }
801
802 public static void baselineDatabse(String database) throws AccessException {
803 if (!isAdmin()) {
804 throw new AccessException("Admin Access Required");
805 }
806
807 String url = String.format("jdbc:mysql://%s/%s?useSSL=%s", Config.getDbLoc(), prefix + database, Config.getSSL());
808 Flyway flyway = new Flyway();
809 flyway.setDataSource(url, username, password);
810 if (database.equals("Commons")) {
811 flyway.setLocations("db.migration/commons");
812 } else {
813 flyway.setLocations("db.migration/year");
814
815 }
816 flyway.baseline();
817 }
818
819 public static Boolean verifyLoginAndUser(Pair<String, String> userPass) {
820 username = userPass.getKey();
821 password = userPass.getValue();
822 Boolean successful = false;
823 try {
824 Class.forName("com.mysql.jdbc.Driver");
825 } catch (ClassNotFoundException e) {
826
827 LogToFile.log(e, Severity.SEVERE, "Error loading database library. Please try reinstalling or contacting support.");
828 }
829 Statement st = null;
830 ResultSet rs = null;
831 Connection pCon;
832
833 String url = String.format("jdbc:mysql://%s/?useSSL=%s", Config.getDbLoc(), Config.getSSL());
834
835 try {
836
837
838 pCon = DriverManager.getConnection(url, username, password);
839 if (pCon.isValid(2)) {
840 User curUser = getCurrentUser();
841 if (curUser != null) {
842 successful = true;
843 isAdmin = curUser.isAdmin();
844 Version localVersion = Config.getProgramVersion();
845 Version remoteVersion = getStoredProgramVersion("Commons");
846 if (localVersion.greaterThan(remoteVersion)) {
847 if (isAdmin) {
848 Alert alert = new Alert(Alert.AlertType.CONFIRMATION);
849 alert.setTitle("Version Mismatch");
850 alert.setHeaderText("Your software's version is greater than the remote.");
851 alert.setContentText("Would you like to update the remote or run in compatibility mode?");
852
853 ButtonType buttonTypeOne = new ButtonType("Update");
854 ButtonType buttonTypeTwo = new ButtonType("Run in compatibility mode", ButtonBar.ButtonData.CANCEL_CLOSE);
855
856 alert.getButtonTypes().setAll(buttonTypeOne, buttonTypeTwo);
857
858 Optional<ButtonType> result = alert.showAndWait();
859 if (result.get() == buttonTypeOne) {
860 getDatabses().forEach((db) -> {
861 try {
862 if (db.equals("Commons")) {
863 migrateDatabase(db, "db.migration/commons");
864
865 } else {
866 migrateDatabase(db, "db.migration/year");
867
868 }
869 } catch (AccessException ignored) {
870
871 }
872 });
873 }
874 } else {
875 LogToFile.log(new VersionException(), Severity.WARNING, "Your software's version is greater than the remote. The application will be running in compatibility mode.");
876
877 }
878 } else if (localVersion.equals(remoteVersion)) {
879 LogToFile.log(null, Severity.FINEST, "Remote and Local are running on same version: " + localVersion.toString());
880 } else {
881 LogToFile.log(new VersionException(), Severity.SEVERE, "Remote version is greater than local. You MUST update your software to continue.");
882 System.exit(0);
883 }
884 databaseVersion.setIfNot(new Version(pCon.getMetaData().getDatabaseProductVersion()));
885
886 }
887 }
888
889
890
891 } catch (CommunicationsException e) {
892 promptConfig();
893 LogToFile.log(e, Severity.FINEST, "Error contacting Database");
894 } catch (SQLException e) {
895
896 if (Objects.equals(e.getSQLState(), "28000")) {
897 successful = false;
898 } else if (Objects.equals(e.getSQLState(), "42000")) {
899 successful = true;
900 } else {
901 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
902
903
904 }
905 }
906
907 return successful;
908 }
909
910 public static Boolean verifyLogin(Pair<String, String> userPass) {
911 username = userPass.getKey();
912 password = userPass.getValue();
913 Boolean successful = false;
914 try {
915 Class.forName("com.mysql.jdbc.Driver");
916 } catch (ClassNotFoundException e) {
917
918 LogToFile.log(e, Severity.SEVERE, "Error loading database library. Please try reinstalling or contacting support.");
919 }
920 Statement st = null;
921 ResultSet rs = null;
922 Connection pCon;
923
924 String url = String.format("jdbc:mysql://%s/?useSSL=%s", Config.getDbLoc(), Config.getSSL());
925
926 try {
927
928
929 pCon = DriverManager.getConnection(url, username, password);
930 if (pCon.isValid(2)) {
931 databaseVersion.setIfNot(new Version(pCon.getMetaData().getDatabaseProductVersion()));
932
933 successful = true;
934
935 }
936
937
938
939 } catch (CommunicationsException e) {
940 promptConfig();
941 LogToFile.log(e, Severity.FINEST, "Error contacting Database");
942 } catch (SQLException e) {
943
944 if (Objects.equals(e.getSQLState(), "28000")) {
945 successful = false;
946 } else if (Objects.equals(e.getSQLState(), "42000")) {
947 successful = true;
948 } else {
949 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
950
951
952 }
953 }
954
955 return successful;
956 }
957
958 private static void promptConfig() {
959 Alert alert = new Alert(Alert.AlertType.CONFIRMATION);
960 alert.setTitle("Verify Databse?");
961 alert.setHeaderText("Failed to connect to the databasse");
962 alert.setContentText("Would you like to open the settings window to verify the connection?");
963
964 ButtonType buttonTypeOne = new ButtonType("Open");
965 ButtonType buttonTypeTwo = new ButtonType("Cancel", ButtonBar.ButtonData.CANCEL_CLOSE);
966
967 alert.getButtonTypes().setAll(buttonTypeOne, buttonTypeTwo);
968
969 Optional<ButtonType> result = alert.showAndWait();
970 if (result.get() == buttonTypeOne) {
971 new Settings();
972
973 }
974 }
975
976 public static boolean isAdmin() {
977 return isAdmin;
978 }
979
980 public static Version getDatabaseVersion() {
981 return databaseVersion.get();
982 }
983
984 public static Version getStoredProgramVersion(String Database) {
985 try (Connection con = DbInt.getConnection("Commons");
986 PreparedStatement prep = con.prepareStatement("SELECT Value FROM `Settings` WHERE `key`='Version'", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
987 ResultSet rs = prep.executeQuery()) {
988 rs.first();
989 return new Version(rs.getString(1));
990
991
992
993
994 } catch (SQLException e) {
995 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
996 }
997 return new Version("0.0");
998 }
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013 }