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

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

Some fixes
File size: 20.8 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 state.close();
341 if (rs.next())
342 {
343 found = true;
344 }
345 else
346 {
347 found = false;
348 }
349 }
350 catch (Exception ex)
351 {
352 System.out.println("exception thrown:");
353 if (ex instanceof SQLException)
354 {
355 printSQLError((SQLException) ex);
356 }
357 else
358 {
359 ex.printStackTrace();
360 }
361
362 System.out.println("Error:" + ex.getMessage());
363 return false;
364 }
365
366 try
367 {
368 PreparedStatement stmt = null;
369 if (!found)
370 {
371 stmt = conn.prepareStatement("INSERT INTO " + DATA + " VALUES (?, ?, ?)");
372 stmt.setString(1, username);
373 stmt.setString(2, name);
374 stmt.setString(3, value);
375 stmt.executeUpdate();
376 }
377 else
378 {
379 stmt = conn.prepareStatement("UPDATE " + DATA + " SET value=? WHERE username=? AND name=?");
380 stmt.setString(1, value);
381 stmt.setString(2, username);
382 stmt.setString(3, name);
383 stmt.executeUpdate();
384 }
385 conn.commit();
386 stmt.close();
387 }
388 catch (Exception ex)
389 {
390 System.out.println("exception thrown:");
391 if (ex instanceof SQLException)
392 {
393 printSQLError((SQLException) ex);
394 }
395 else
396 {
397 ex.printStackTrace();
398 }
399
400 System.out.println("Error:" + ex.getMessage());
401 return false;
402 }
403 return true;
404 }
405
406 public String getUserData(String username, String name)
407 {
408 try
409 {
410 Statement state = conn.createStatement();
411 ResultSet rs = state.executeQuery("SELECT * FROM " + DATA + " WHERE username='" + username + "' AND name='" + name + "'");
412 conn.commit();
413 state.close();
414 if (rs.next())
415 {
416 return rs.getString("value");
417 }
418 }
419 catch (Exception ex)
420 {
421 System.out.println("exception thrown:");
422 if (ex instanceof SQLException)
423 {
424 printSQLError((SQLException) ex);
425 }
426 else
427 {
428 ex.printStackTrace();
429 }
430
431 System.out.println("Error:" + ex.getMessage());
432 }
433 return null;
434 }
435
436 public boolean addUser(String username, String password, String groups, String accountstatus, String comment, String email)
437 {
438 try
439 {
440 Statement state = conn.createStatement();
441 String sql_insert_user = "insert into " + USERS + " values ('" + username + "', '" + password + "', '" + accountstatus + "', '" + comment + "', '" + email + "')";
442 state.execute(sql_insert_user);
443
444 String[] groupArray = groups.split(",");
445 for (String g : groupArray)
446 {
447 String sql_insert_group = "insert into " + ROLES + " values ('" + username + "', '" + g + "')";
448 state.execute(sql_insert_group);
449 }
450
451 conn.commit();
452 state.close();
453 }
454 catch (Throwable e)
455 {
456 System.out.println("exception thrown:");
457 if (e instanceof SQLException)
458 {
459 printSQLError((SQLException) e);
460 }
461 else
462 {
463 e.printStackTrace();
464 }
465
466 System.out.println("Error:" + e.getMessage());
467 return false;
468 }
469
470 return true;
471 }
472
473 public boolean deleteUser(String del_username)
474 {
475 try
476 {
477 String sql_delete_user = "delete from " + USERS + " where username='" + del_username + "'";
478 String sql_delete_groups = "delete from " + ROLES + " where username='" + del_username + "'";
479 Statement state = conn.createStatement();
480 state.execute(sql_delete_user);
481 state.execute(sql_delete_groups);
482 conn.commit();
483 state.close();
484 }
485 catch (Throwable e)
486 {
487 System.out.println("exception thrown:");
488 if (e instanceof SQLException)
489 {
490 printSQLError((SQLException) e);
491 }
492 else
493 {
494 e.printStackTrace();
495 }
496 return false;
497 }
498 return true;
499 }
500
501 public boolean deleteAllUser() throws SQLException
502 {
503 try
504 {
505 Statement state = conn.createStatement();
506 state.execute("delete from " + USERS);
507 state.execute("delete from " + ROLES);
508 conn.commit();
509 state.close();
510 }
511 catch (Throwable e)
512 {
513 System.out.println("exception thrown:");
514 if (e instanceof SQLException)
515 {
516 printSQLError((SQLException) e);
517 }
518 else
519 {
520 e.printStackTrace();
521 }
522
523 return false;
524 }
525 return true;
526 }
527
528 public UserQueryResult findUser(String username, String password)
529 {
530 UserQueryResult userQueryResult = new UserQueryResult();
531
532 String sql_find_user = "SELECT username, password, accountstatus, comment, email FROM " + USERS;
533 String append_sql = "";
534
535 if (username != null)
536 {
537 append_sql = " WHERE username = '" + username + "'";
538 }
539 if (password != null)
540 {
541 if (append_sql.equals(""))
542 {
543 append_sql = " WHERE password = '" + password + "'";
544 }
545 else
546 {
547 append_sql += " and password = '" + password + "'";
548 }
549 }
550 if (!append_sql.equals(""))
551 {
552 sql_find_user += append_sql;
553 }
554
555 try
556 {
557 ArrayList<HashMap<String, String>> users = new ArrayList<HashMap<String, String>>();
558 Statement state = conn.createStatement();
559 ResultSet rs = state.executeQuery(sql_find_user);
560 while (rs.next())
561 {
562 HashMap<String, String> user = new HashMap<String, String>();
563 user.put("username", rs.getString("username"));
564 user.put("password", rs.getString("password"));
565 user.put("as", rs.getString("accountstatus"));
566 user.put("comment", rs.getString("comment"));
567 user.put("email", rs.getString("email"));
568
569 users.add(user);
570 }
571 conn.commit();
572
573 for (HashMap<String, String> user : users)
574 {
575 ResultSet gs = state.executeQuery("SELECT role FROM " + ROLES + " WHERE username = '" + user.get("username") + "'");
576
577 String group = "";
578 while (gs.next())
579 {
580 if (!group.equals(""))
581 {
582 group += ",";
583 }
584 group += gs.getString("role");
585 }
586
587 userQueryResult.addUserTerm(user.get("username"), user.get("password"), group, user.get("as"), user.get("comment"), user.get("email"));
588 }
589 state.close();
590 }
591 catch (Exception ex)
592 {
593 ex.printStackTrace();
594 return null;
595 }
596
597 if (userQueryResult.getSize() > 0)
598 {
599 return userQueryResult;
600 }
601 else
602 {
603 return null;
604 }
605 }
606
607 // findUser(null) will return all users, which is why a UserQueryResult
608 // (a vector of UserTermInfo) is returned
609 public UserQueryResult findUser(String username) throws SQLException
610 {
611 UserQueryResult userQueryResult = new UserQueryResult();
612
613 String sql_find_user = "SELECT username, password, accountstatus, comment, email FROM " + USERS;
614 String append_sql = "";
615
616 if (username != null)
617 {
618 append_sql = " WHERE username = '" + username + "'";
619 }
620 if (!append_sql.equals(""))
621 {
622 sql_find_user += append_sql;
623 }
624
625 ArrayList<HashMap<String, String>> users = new ArrayList<HashMap<String, String>>();
626
627 Statement state = conn.createStatement();
628 ResultSet rs = state.executeQuery(sql_find_user);
629 conn.commit();
630 while (rs.next())
631 {
632 HashMap<String, String> user = new HashMap<String, String>();
633 user.put("username", rs.getString("username"));
634 user.put("password", rs.getString("password"));
635 user.put("as", rs.getString("accountstatus"));
636 user.put("comment", rs.getString("comment"));
637 user.put("email", rs.getString("email"));
638
639 users.add(user);
640 }
641 state.close();
642
643 state = conn.createStatement();
644 for (HashMap<String, String> user : users)
645 {
646 ResultSet gs = state.executeQuery("SELECT role FROM " + ROLES + " WHERE username = '" + user.get("username") + "'");
647 conn.commit();
648
649 String group = "";
650 while (gs.next())
651 {
652 if (!group.equals(""))
653 {
654 group += ",";
655 }
656 group += gs.getString("role");
657 }
658
659 userQueryResult.addUserTerm(user.get("username"), user.get("password"), group, user.get("as"), user.get("comment"), user.get("email"));
660 }
661 state.close();
662
663 if (userQueryResult.getSize() > 0)
664 {
665 return userQueryResult;
666 }
667 else
668 {
669 System.out.println("couldn't find the user");
670 return null;
671 }
672 }
673
674 public String modifyUserInfo(String username, String new_password, String groups, String accountstatus, String comment, String email)
675 {
676 try
677 {
678 String sql_modify_user_info = "update " + USERS + " set ";
679
680 boolean needComma = false;
681 if (new_password != null && !new_password.equals(""))
682 {
683 sql_modify_user_info += "password='" + new_password + "'";
684 needComma = true;
685 }
686
687 if (accountstatus != null && comment != null)
688 {
689 sql_modify_user_info += (needComma ? "," : "") + " accountstatus='" + accountstatus + "'" + ", comment='" + comment + "'";
690 needComma = true;
691 }
692
693 if (email != null)
694 {
695 sql_modify_user_info += (needComma ? "," : "") + " email='" + email + "'";
696 }
697
698 sql_modify_user_info += " where username='" + username + "'";
699 Statement state = conn.createStatement();
700 state.execute(sql_modify_user_info);
701
702 String sql_delete_groups = "delete from " + ROLES + " where username='" + username + "'";
703 state.execute(sql_delete_groups);
704
705 String[] groupsArray = groups.split(",");
706 for (String g : groupsArray)
707 {
708 String sql_insert_group = "insert into " + ROLES + " values ('" + username + "', '" + g + "')";
709 state.execute(sql_insert_group);
710 }
711
712 conn.commit();
713 state.close();
714 }
715 catch (Throwable e)
716 {
717 System.out.println("exception thrown:");
718 if (e instanceof SQLException)
719 {
720 printSQLError((SQLException) e);
721 }
722 else
723 {
724 e.printStackTrace();
725 }
726
727 return "Error:" + e.getMessage();
728 }
729 return "succeed";
730 }
731
732 public void db2txt()
733 {
734 System.err.println(db2txtString());
735 }
736
737 public String db2txtString()
738 {
739 //String db2txt = "Error in converting db2txt string.";
740 String db2txt = "";
741 try
742 {
743 String sql_list_all_user = "select username, password, accountstatus, comment, email from " + USERS;
744
745 Statement state = conn.createStatement();
746 ResultSet rs = state.executeQuery(sql_list_all_user);
747
748 ArrayList<HashMap<String, String>> infoMap = new ArrayList<HashMap<String, String>>();
749
750 while (rs.next())
751 {
752 HashMap<String, String> userMap = new HashMap<String, String>();
753 userMap.put("username", rs.getString("username"));
754 userMap.put("password", rs.getString("password"));
755 userMap.put("status", rs.getString("accountstatus"));
756 userMap.put("comment", rs.getString("comment"));
757 userMap.put("email", rs.getString("email"));
758 infoMap.add(userMap);
759 }
760 conn.commit();
761
762 StringBuffer buffer = new StringBuffer();//("-------------------------------------");
763 for (HashMap<String, String> user : infoMap)
764 {
765 ResultSet groupsSet = state.executeQuery("SELECT role FROM " + ROLES + " WHERE username = '" + user.get("username") + "'");
766 String returnedGroups = "";
767 while (groupsSet.next())
768 {
769 if (!returnedGroups.equals(""))
770 {
771 returnedGroups += ",";
772 }
773 returnedGroups += groupsSet.getString("role");
774 }
775 conn.commit();
776
777 buffer.append("USERNAME = " + user.get("username"));
778 buffer.append("\nPASSWORD = " + user.get("password"));
779 buffer.append("\nGROUPS = " + returnedGroups);
780 buffer.append("\nSTATUS = " + user.get("status"));
781 buffer.append("\nCOMMENT = " + user.get("comment"));
782 buffer.append("\nEMAIL = " + user.get("email"));
783 buffer.append("\n-------------------------------------\n");
784 }
785 db2txt = buffer.toString();
786
787 conn.commit();
788 state.close();
789 }
790 catch (Exception ex)
791 {
792 ex.printStackTrace();
793 }
794 finally
795 {
796 return db2txt;
797 }
798 }
799
800 static void printSQLError(SQLException e)
801 {
802 while (e != null)
803 {
804 System.out.println(e.toString());
805 e = e.getNextException();
806 }
807 }
808
809 public void clearUserDataWithPrefix(String username, String prefix)
810 {
811 try
812 {
813 Statement state = conn.createStatement();
814 state.execute("DELETE FROM data WHERE username = '" + username + "' AND SUBSTR(name, 1, " + prefix.length() + ") = '" + prefix + "'");
815 conn.commit();
816 state.close();
817 }
818 catch (Exception ex)
819 {
820 ex.printStackTrace();
821 }
822 }
823}
Note: See TracBrowser for help on using the repository browser.