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

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

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