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

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

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