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

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

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