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

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

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