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

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

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