source: main/trunk/greenstone3/src/java/org/greenstone/gsdl3/util/DerbyWrapper.java@ 36067

Last change on this file since 36067 was 35362, checked in by kjdon, 3 years ago

Class.newInstance() is deprecated. Use getDeclaredConstructor().newInstance() instead
File size: 22.5 KB
Line 
1/*
2 * DerbyWrapper.java
3 * Copyright (C) 2008 New Zealand Digital Library, http://www.nzdl.org
4 *
5 * This program is free software; you can redistribute it and/or modify
6 * it under the terms of the GNU General Public License as published by
7 * the Free Software Foundation; either version 2 of the License, or
8 * (at your option) any later version.
9 *
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
14 *
15 * You should have received a copy of the GNU General Public License
16 * along with this program; if not, write to the Free Software
17 * Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
18 */
19package org.greenstone.gsdl3.util;
20
21import java.io.File;
22import java.sql.Connection;
23import java.sql.DriverManager;
24import java.sql.PreparedStatement;
25import java.sql.ResultSet;
26import java.sql.SQLException;
27import java.sql.Statement;
28import java.util.ArrayList;
29import java.util.HashMap;
30import java.util.HashSet;
31
32import org.greenstone.gsdl3.service.Authentication;
33import org.greenstone.util.GlobalProperties;
34
35/**
36 * The UserDB stores the groups in the "roles" table in expanded form, i.e. expandedGroups.
37 * This is what is retrieved as well. Call UserTermInfo.compactGroup(expandedGroups)
38 * to get the compacted form closer to what the user may have entered.
39*/
40public class DerbyWrapper
41{
42 static final String PORT;
43 static final String DERBYSERVER;
44 static final String PROTOCOL;
45
46 // static code block to initialise the above
47 static {
48 // GlobalProperties won't be loaded at this point if running ant config-admin or ant config-user
49 // from the command line (both of which call ant update-userdb which in turn calls ModifyUsersDB.java)
50 // In such a case, the ant command will have set the system property (-Dgsdl3_writablehome)
51 // and passed this to ModifyUsersDB.java. Use that to load the GlobalProperties at this point
52
53 if(GlobalProperties.getGSDL3Home() == null) { // testing whether GlobalProperties is already loaded
54 String gsdl3_writablehome = System.getProperty("gsdl3.writablehome"); // set by 'ant update-userdb' cmd
55
56 //System.err.println("@@@@@ writablehome: " + gsdl3_writablehome);
57 GlobalProperties.loadGlobalProperties(gsdl3_writablehome);
58 }
59
60 //System.err.println("@@@@@ GlobalProperties.getGSDL3Home(): " + GlobalProperties.getGSDL3Home()); //test
61
62 // No more fallback values, use exactly what's propagated into global.properties from build.properties
63 String port = GlobalProperties.getProperty("derby.server.port");//, "1527");
64 if(port.indexOf("@") == -1) { // if there are still @placeholders@ in global.properties, then use fallback values
65 PORT = port;
66 DERBYSERVER = GlobalProperties.getProperty("derby.server");//, "localhost");
67 } else {
68 PORT = "1527";
69 DERBYSERVER = "localhost";
70 }
71 PROTOCOL = "jdbc:derby://"+DERBYSERVER+":"+PORT+"/"; // "jdbc:derby://localhost:1527"; // by default
72 //System.out.println("@@@ PROTOCOL:" + PROTOCOL); //check in installer
73 }
74
75
76 static final String DRIVER = "org.apache.derby.jdbc.ClientDriver"; //"org.apache.derby.jdbc.EmbeddedDriver";
77 static final String USERSDB = "usersDB";
78 static final String USERS = "users";
79 static final String ROLES = "roles";
80 static final String DATA = "data";
81 private Connection conn = null;
82 private static String protocol_str;
83
84 public DerbyWrapper(String dbpath)
85 {
86 connectDatabase(dbpath, false);
87 }
88
89 public static void createDatabaseIfNeeded()
90 {
91 String usersDB_dir = GlobalProperties.getGSDL3Home() + File.separatorChar + "etc" + File.separatorChar + "usersDB";
92 protocol_str = PROTOCOL + usersDB_dir;
93 File usersDB_file = new File(usersDB_dir);
94 if (!usersDB_file.exists())
95 {
96 String etc_dir = GlobalProperties.getGSDL3Home() + File.separatorChar + "etc";
97 File etc_file = new File(etc_dir);
98 if (!etc_file.exists())
99 {
100 boolean success = etc_file.mkdir();
101 if (!success)
102 {
103 System.err.println("Couldn't create the etc dir under " + GlobalProperties.getGSDL3Home() + ".");
104 }
105 }
106 try
107 {
108 DerbyWrapper.createDatabase(DriverManager.getConnection(protocol_str + ";create=true"));
109 }
110 catch (Exception ex)
111 {
112 ex.printStackTrace();
113 }
114 }
115 }
116
117 public void connectDatabase(String dbpath, boolean create_database)
118 {
119 try
120 {
121 if (conn != null)
122 {
123 System.err.println("Connection already established, close the database first");
124 return;
125 }
126
127 Class.forName(DRIVER).getDeclaredConstructor().newInstance();
128 protocol_str = PROTOCOL + dbpath;
129 if (create_database)
130 {
131 conn = DriverManager.getConnection(protocol_str + ";create=true");
132 }
133 else
134 {
135 conn = DriverManager.getConnection(protocol_str);
136 }
137 conn.setAutoCommit(false);
138 }
139 catch (Throwable e)
140 {
141 System.out.println("exception thrown:");
142 if (e instanceof SQLException)
143 {
144 printSQLError((SQLException) e);
145 }
146 else
147 {
148 e.printStackTrace();
149 }
150 }
151 }
152
153 public void closeDatabase()
154 {
155 try
156 {
157 conn.commit();
158 conn.close();
159 conn = null;
160 }
161 catch (SQLException e)
162 {
163 e.printStackTrace();
164 }
165 }
166
167 public static void shutdownDatabaseServer()
168 {
169
170 // shutdown the server if we're using an embedded derby
171 // if we're a derby client using the derby network server
172
173 if(!DRIVER.equals("org.apache.derby.jdbc.EmbeddedDriver")) {
174 return;
175 }
176
177 boolean gotSQLExc = false;
178 try
179 {
180 DriverManager.getConnection(PROTOCOL + ";shutdown=true");
181 }
182 catch (SQLException se)
183 {
184 // this is good (i.e. what Derby is designed to do on a successful shutdown)
185 gotSQLExc = true;
186 //System.out.println("Shutdown returned: " + se);
187 }
188 catch (Exception e)
189 {
190 e.printStackTrace();
191 }
192 if (!gotSQLExc)
193 {
194 System.err.println("Warning: Derby did not shut down normally");
195 }
196 }
197
198 public void clearUserData()
199 {
200 try
201 {
202 Statement state = conn.createStatement();
203 state.execute("drop table data");
204 state.execute("create table data (username varchar(40) not null, name varchar(128) not null, value clob, primary key (username, name))");
205 conn.commit();
206 state.close();
207 }
208 catch (SQLException e)
209 {
210 e.printStackTrace();
211 }
212 }
213
214 public void clearTrackerData()
215 {
216 try
217 {
218 Statement state = conn.createStatement();
219 state.execute("drop table usertracker");
220 state.execute("create table usertracker (username varchar(40) not null, collection varchar(128) not null, site varchar(128) not null, oid varchar(128) not null, time varchar(128) not null, action varchar(128) not null, primary key (username, time))");
221 conn.commit();
222 state.close();
223 }
224 catch (SQLException e)
225 {
226 e.printStackTrace();
227 }
228 }
229
230 public static void createDatabase(Connection conn)
231 {
232 try
233 {
234 Statement state = conn.createStatement();
235 state.execute("create table users (username varchar(40) not null, password varchar(40) not null, accountstatus varchar(10), comment varchar(100), email varchar(40), primary key(username))");
236 state.execute("create table roles (username varchar(40) not null, role varchar(40) not null, primary key (username, role))");
237 state.execute("create table data (username varchar(40) not null, name varchar(128) not null, value clob, primary key (username, name))");
238 state.execute("create table usertracker (username varchar(40) not null, collection varchar(128) not null, site varchar(128) not null, oid varchar(128) not null, time varchar(128) not null, action varchar(128) not null, primary key (username, time))");
239
240 state.execute("insert into " + USERS + " values ('admin', '" + Authentication.hashPassword("admin") + "', 'true', 'change the password for this account as soon as possible', '')");
241 state.execute("insert into " + ROLES + " values ('admin', 'administrator')");
242 state.execute("insert into " + ROLES + " values ('admin', 'all-collections-editor')");
243 conn.commit();
244 state.close();
245 conn.close();
246 }
247 catch (Exception ex)
248 {
249 ex.printStackTrace();
250 }
251 }
252
253 public void addUserAction(String username, String site, String collection, String oid, String action)
254 {
255 try
256 {
257 Statement state = conn.createStatement();
258 state.execute("INSERT INTO usertracker VALUES ('" + username + "', '" + collection + "', '" + site + "', '" + oid + "', '" + System.currentTimeMillis() + "', '" + action + "')");
259 conn.commit();
260 state.close();
261 }
262 catch (Exception ex)
263 {
264 ex.printStackTrace();
265 }
266 }
267
268 public ArrayList<HashMap<String, String>> getMostRecentUserActions(String site, String collection, String oid)
269 {
270 ArrayList<HashMap<String, String>> actions = new ArrayList<HashMap<String, String>>();
271
272 try
273 {
274 String query = "SELECT username, action, time FROM usertracker WHERE site = '" + site + "' and collection = '" + collection + "' and oid = '" + oid + "' ORDER BY time";
275 Statement state = conn.createStatement();
276 ResultSet rs = state.executeQuery(query);
277 conn.commit();
278
279 HashSet<String> usernamesSeen = new HashSet<String>();
280 while (rs.next())
281 {
282 String timeStr = rs.getString("time");
283 long time = Long.parseLong(timeStr);
284
285 if (System.currentTimeMillis() - time > 6000)
286 {
287 continue;
288 }
289
290 HashMap<String, String> action = new HashMap<String, String>();
291 if (!usernamesSeen.contains(rs.getString("username")))
292 {
293 action.put("username", rs.getString("username"));
294 action.put("action", rs.getString("action"));
295 actions.add(action);
296
297 usernamesSeen.add(rs.getString("username"));
298 }
299 }
300 state.close();
301
302 clearOldUserActions();
303 }
304 catch (Exception ex)
305 {
306 ex.printStackTrace();
307 }
308 return actions;
309 }
310
311 public void clearOldUserActions()
312 {
313 try
314 {
315 Statement state = conn.createStatement();
316 state.execute("DELETE FROM usertracker WHERE (CAST (time AS BIGINT)) < " + (System.currentTimeMillis() - 20000));
317 conn.commit();
318 state.close();
319 }
320 catch (Exception ex)
321 {
322 ex.printStackTrace();
323 }
324 }
325
326 public UserQueryResult listAllUsers() throws SQLException
327 {
328 UserQueryResult userQueryResult = new UserQueryResult();
329 String sql_list_all_user = "SELECT username, password, accountstatus, email, comment FROM " + USERS;
330
331 ArrayList<HashMap<String, String>> users = new ArrayList<HashMap<String, String>>();
332 Statement state = conn.createStatement();
333 ResultSet rs = state.executeQuery(sql_list_all_user);
334 conn.commit();
335 state.close();
336
337 while (rs.next())
338 {
339 HashMap<String, String> user = new HashMap<String, String>();
340 user.put("username", rs.getString("username"));
341 user.put("password", rs.getString("password"));
342 user.put("as", rs.getString("accountstatus"));
343 user.put("comment", rs.getString("comment"));
344 user.put("email", rs.getString("email"));
345
346 users.add(user);
347 }
348
349 for (HashMap<String, String> user : users)
350 {
351 ResultSet gs = state.executeQuery("SELECT role FROM " + ROLES + " WHERE username = '" + user.get("username") + "'");
352 String group = "";
353 while (gs.next())
354 {
355 if (!group.equals(""))
356 {
357 group += ",";
358 }
359 group += gs.getString("role");
360 }
361 userQueryResult.addUserTerm(user.get("username"), user.get("password"), group, user.get("as"), user.get("comment"), user.get("email"));
362 }
363
364 if (userQueryResult.getSize() == 0)
365 {
366 System.out.println("couldn't find any users");
367 return null;
368 }
369 else
370 {
371 return userQueryResult;
372 }
373 }
374
375 public boolean addUserData(String username, String name, String value)
376 {
377 //Check if we already have a value under this name
378 boolean found = false;
379 try
380 {
381 Statement state = conn.createStatement();
382 ResultSet rs = state.executeQuery("SELECT * FROM " + DATA + " WHERE username='" + username + "' AND name='" + name + "'");
383 conn.commit();
384 if (rs.next())
385 {
386 found = true;
387 }
388 else
389 {
390 found = false;
391 }
392 state.close();
393 }
394 catch (Exception ex)
395 {
396 System.out.println("exception thrown:");
397 if (ex instanceof SQLException)
398 {
399 printSQLError((SQLException) ex);
400 ex.printStackTrace();
401 }
402 else
403 {
404 ex.printStackTrace();
405 }
406
407 System.out.println("Error:" + ex.getMessage());
408 return false;
409 }
410
411 try
412 {
413 PreparedStatement stmt = null;
414 if (!found)
415 {
416 stmt = conn.prepareStatement("INSERT INTO " + DATA + " VALUES (?, ?, ?)");
417 stmt.setString(1, username);
418 stmt.setString(2, name);
419 stmt.setString(3, value);
420 stmt.executeUpdate();
421 }
422 else
423 {
424 stmt = conn.prepareStatement("UPDATE " + DATA + " SET value=? WHERE username=? AND name=?");
425 stmt.setString(1, value);
426 stmt.setString(2, username);
427 stmt.setString(3, name);
428 stmt.executeUpdate();
429 }
430 conn.commit();
431 stmt.close();
432 }
433 catch (Exception ex)
434 {
435 System.out.println("exception thrown:");
436 if (ex instanceof SQLException)
437 {
438 printSQLError((SQLException) ex);
439 }
440 else
441 {
442 ex.printStackTrace();
443 }
444
445 System.out.println("Error:" + ex.getMessage());
446 return false;
447 }
448 return true;
449 }
450
451 public String getUserData(String username, String name)
452 {
453 try
454 {
455 Statement state = conn.createStatement();
456 ResultSet rs = state.executeQuery("SELECT * FROM " + DATA + " WHERE username='" + username + "' AND name='" + name + "'");
457 conn.commit();
458 if (rs.next())
459 {
460 return rs.getString("value");
461 }
462 state.close();
463 }
464 catch (Exception ex)
465 {
466 System.out.println("exception thrown:");
467 if (ex instanceof SQLException)
468 {
469 printSQLError((SQLException) ex);
470 }
471 else
472 {
473 ex.printStackTrace();
474 }
475
476 System.out.println("Error:" + ex.getMessage());
477 }
478 return null;
479 }
480
481 public boolean addUser(String username, String password, String expandedGroups, String accountstatus, String comment, String email)
482 {
483 try
484 {
485 Statement state = conn.createStatement();
486 String sql_insert_user = "insert into " + USERS + " values ('" + username + "', '" + password + "', '" + accountstatus + "', '" + comment + "', '" + email + "')";
487 state.execute(sql_insert_user);
488
489 String[] groupArray = expandedGroups.split(",");
490 for (String g : groupArray)
491 {
492 String sql_insert_group = "insert into " + ROLES + " values ('" + username + "', '" + g + "')";
493 state.execute(sql_insert_group);
494 }
495
496 conn.commit();
497 state.close();
498 }
499 catch (Throwable e)
500 {
501 System.out.println("exception thrown:");
502 if (e instanceof SQLException)
503 {
504 printSQLError((SQLException) e);
505 }
506 else
507 {
508 e.printStackTrace();
509 }
510
511 System.out.println("Error:" + e.getMessage());
512 return false;
513 }
514
515 return true;
516 }
517
518 public boolean deleteUser(String del_username)
519 {
520 try
521 {
522 String sql_delete_user = "delete from " + USERS + " where username='" + del_username + "'";
523 String sql_delete_groups = "delete from " + ROLES + " where username='" + del_username + "'";
524 Statement state = conn.createStatement();
525 state.execute(sql_delete_user);
526 state.execute(sql_delete_groups);
527 conn.commit();
528 state.close();
529 }
530 catch (Throwable e)
531 {
532 System.out.println("exception thrown:");
533 if (e instanceof SQLException)
534 {
535 printSQLError((SQLException) e);
536 }
537 else
538 {
539 e.printStackTrace();
540 }
541 return false;
542 }
543 return true;
544 }
545
546 public boolean deleteAllUser() throws SQLException
547 {
548 try
549 {
550 Statement state = conn.createStatement();
551 state.execute("delete from " + USERS);
552 state.execute("delete from " + ROLES);
553 conn.commit();
554 state.close();
555 }
556 catch (Throwable e)
557 {
558 System.out.println("exception thrown:");
559 if (e instanceof SQLException)
560 {
561 printSQLError((SQLException) e);
562 }
563 else
564 {
565 e.printStackTrace();
566 }
567
568 return false;
569 }
570 return true;
571 }
572
573 // single arg method for convenience
574 public UserQueryResult findUser(String username) {
575 return findUser(username, null);
576 }
577 public UserQueryResult findUser(String username, String password)
578 {
579 UserQueryResult userQueryResult = new UserQueryResult();
580
581 String sql_find_user = "SELECT username, password, accountstatus, comment, email FROM " + USERS;
582 String append_sql = "";
583
584 if (username != null)
585 {
586 append_sql = " WHERE username = '" + username + "'";
587 }
588 if (password != null)
589 {
590 if (append_sql.equals(""))
591 {
592 append_sql = " WHERE password = '" + password + "'";
593 }
594 else
595 {
596 append_sql += " and password = '" + password + "'";
597 }
598 }
599 if (!append_sql.equals(""))
600 {
601 sql_find_user += append_sql;
602 }
603
604 try
605 {
606 ArrayList<HashMap<String, String>> users = new ArrayList<HashMap<String, String>>();
607 Statement state = conn.createStatement();
608 ResultSet rs = state.executeQuery(sql_find_user);
609 while (rs.next())
610 {
611 HashMap<String, String> user = new HashMap<String, String>();
612 user.put("username", rs.getString("username"));
613 user.put("password", rs.getString("password"));
614 user.put("as", rs.getString("accountstatus"));
615 user.put("comment", rs.getString("comment"));
616 user.put("email", rs.getString("email"));
617
618 users.add(user);
619 }
620 conn.commit();
621
622 for (HashMap<String, String> user : users)
623 {
624 ResultSet gs = state.executeQuery("SELECT role FROM " + ROLES + " WHERE username = '" + user.get("username") + "'");
625 conn.commit();
626
627 String group = "";
628 while (gs.next())
629 {
630 if (!group.equals(""))
631 {
632 group += ",";
633 }
634 group += gs.getString("role");
635 }
636
637 userQueryResult.addUserTerm(user.get("username"), user.get("password"), group, user.get("as"), user.get("comment"), user.get("email"));
638 }
639 state.close();
640 }
641 catch (Exception ex)
642 {
643 ex.printStackTrace();
644 return null;
645 }
646
647 if (userQueryResult.getSize() > 0)
648 {
649 return userQueryResult;
650 }
651 else
652 {
653 return null;
654 }
655 }
656
657 public String modifyUserInfo(String username, String new_password, String expandedGroups, String accountstatus, String comment, String email)
658 {
659 try
660 {
661 String sql_modify_user_info = "update " + USERS + " set ";
662
663 boolean needComma = false;
664 if (new_password != null && !new_password.equals(""))
665 {
666 sql_modify_user_info += "password='" + new_password + "'";
667 needComma = true;
668 }
669
670 if (accountstatus != null) {
671 sql_modify_user_info += (needComma ? "," : "") + " accountstatus='" + accountstatus + "'";
672 needComma = true;
673 }
674 if (comment != null)
675 {
676 sql_modify_user_info += (needComma ? "," : "") + " comment='" + comment + "'";
677 needComma = true;
678 }
679
680 if (email != null)
681 {
682 sql_modify_user_info += (needComma ? "," : "") + " email='" + email + "'";
683 needComma = true;
684 }
685 Statement state = conn.createStatement();
686 if (needComma) {
687 // it is possible that we are only modifying groups, so use needComma to
688 // see if we actually need to run this step
689 sql_modify_user_info += " where username='" + username + "'";
690 //System.err.println("about to execute sql: "+sql_modify_user_info);
691
692 state.execute(sql_modify_user_info);
693 }
694 if (expandedGroups != null) {
695 // delete the groups we have currently
696
697 String sql_delete_groups = "delete from " + ROLES + " where username='" + username + "'";
698 state.execute(sql_delete_groups);
699
700 // add the new groups
701 String[] groupsArray = expandedGroups.split(",");
702 for (String g : groupsArray)
703 {
704 String sql_insert_group = "insert into " + ROLES + " values ('" + username + "', '" + g + "')";
705 state.execute(sql_insert_group);
706 }
707
708 conn.commit();
709 state.close();
710 }
711 }
712 catch (Throwable e)
713 {
714 System.out.println("exception thrown:");
715 if (e instanceof SQLException)
716 {
717 printSQLError((SQLException) e);
718 }
719 else
720 {
721 e.printStackTrace();
722 }
723
724 return "Error:" + e.getMessage();
725 }
726 return "succeed";
727 }
728
729 public void db2txt()
730 {
731 System.err.println(db2txtString());
732 }
733
734 public String db2txtString()
735 {
736 //String db2txt = "Error in converting db2txt string.";
737 String db2txt = "";
738 try
739 {
740 String sql_list_all_user = "select username, password, accountstatus, comment, email from " + USERS;
741
742 Statement state = conn.createStatement();
743 ResultSet rs = state.executeQuery(sql_list_all_user);
744
745 ArrayList<HashMap<String, String>> infoMap = new ArrayList<HashMap<String, String>>();
746
747 while (rs.next())
748 {
749 HashMap<String, String> userMap = new HashMap<String, String>();
750 userMap.put("username", rs.getString("username"));
751 userMap.put("password", rs.getString("password"));
752 userMap.put("status", rs.getString("accountstatus"));
753 userMap.put("comment", rs.getString("comment"));
754 userMap.put("email", rs.getString("email"));
755 infoMap.add(userMap);
756 }
757 conn.commit();
758
759 StringBuffer buffer = new StringBuffer();//("-------------------------------------");
760 for (HashMap<String, String> user : infoMap)
761 {
762 ResultSet groupsSet = state.executeQuery("SELECT role FROM " + ROLES + " WHERE username = '" + user.get("username") + "'");
763 String returnedGroups = "";
764 while (groupsSet.next())
765 {
766 if (!returnedGroups.equals(""))
767 {
768 returnedGroups += ",";
769 }
770 returnedGroups += groupsSet.getString("role");
771 }
772 conn.commit();
773
774 buffer.append("USERNAME = " + user.get("username"));
775 buffer.append("\nPASSWORD = " + user.get("password"));
776 buffer.append("\nGROUPS = " + returnedGroups);
777 buffer.append("\nSTATUS = " + user.get("status"));
778 buffer.append("\nCOMMENT = " + user.get("comment"));
779 buffer.append("\nEMAIL = " + user.get("email"));
780 buffer.append("\n-------------------------------------\n");
781 }
782 db2txt = buffer.toString();
783
784 conn.commit();
785 state.close();
786 }
787 catch (Exception ex)
788 {
789 ex.printStackTrace();
790 }
791 finally
792 {
793 return db2txt;
794 }
795 }
796
797 static void printSQLError(SQLException e)
798 {
799 while (e != null)
800 {
801 System.out.println(e.toString());
802 e = e.getNextException();
803 }
804 }
805
806 public void clearUserDataWithPrefix(String username, String prefix)
807 {
808 try
809 {
810 Statement state = conn.createStatement();
811 state.execute("DELETE FROM data WHERE username = '" + username + "' AND SUBSTR(name, 1, " + prefix.length() + ") = '" + prefix + "'");
812 conn.commit();
813 state.close();
814 }
815 catch (Exception ex)
816 {
817 ex.printStackTrace();
818 }
819 }
820}
Note: See TracBrowser for help on using the repository browser.