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

Last change on this file since 29903 was 29903, checked in by ak19, 9 years ago

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