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

Last change on this file since 28234 was 28234, checked in by sjm84, 11 years ago

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