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

Last change on this file since 25261 was 25261, checked in by sjm84, 12 years ago

Some major changes to the derby wrapper
File size: 13.5 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 javax.swing.*;
22
23import org.greenstone.gsdl3.service.Authentication;
24
25import java.io.File;
26import java.sql.Connection;
27import java.sql.DriverManager;
28import java.sql.ResultSet;
29import java.sql.SQLException;
30import java.sql.SQLWarning;
31import java.sql.Statement;
32import java.util.ArrayList;
33import java.util.HashMap;
34import java.util.Properties;
35
36public class DerbyWrapper
37{
38 static final String PROTOCOL = "jdbc:derby:";
39 static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";
40 static final String USERSDB = "usersDB";
41 static final String USERS = "users";
42 static final String ROLES = "roles";
43 private Connection conn = null;
44 private Statement state = null;
45 private String protocol_str;
46
47 public DerbyWrapper()
48 {
49 }
50
51 public DerbyWrapper(String dbpath)
52 {
53 connectDatabase(dbpath, false);
54 }
55
56 public void connectDatabase(String dbpath, boolean create_database)
57 {
58 try
59 {
60 Class.forName(DRIVER).newInstance();
61 //System.out.println("Loaded the embedded driver.");
62 protocol_str = PROTOCOL + dbpath;
63 if (create_database)
64 {
65 conn = DriverManager.getConnection(protocol_str + ";create=true");
66 }
67 else
68 {
69 conn = DriverManager.getConnection(protocol_str);
70 }
71 state = conn.createStatement();
72 }
73 catch (Throwable e)
74 {
75 System.out.println("exception thrown:");
76 if (e instanceof SQLException)
77 {
78 printSQLError((SQLException) e);
79 }
80 else
81 {
82 e.printStackTrace();
83 }
84 }
85 }
86
87 public void closeDatabase()
88 {
89 state = null;
90 conn = null;
91 boolean gotSQLExc = false;
92 try
93 {
94 DriverManager.getConnection(protocol_str + ";shutdown=true");
95 }
96 catch (SQLException se)
97 {
98 gotSQLExc = true;
99 }
100 if (!gotSQLExc)
101 {
102 System.out.println("Database did not shut down normally");
103 }
104 }
105
106 public void createDatabase()
107 {
108 try
109 {
110 conn.setAutoCommit(false);
111 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))");
112 state.execute("create table roles (username varchar(40) not null, role varchar(40) not null, primary key (username, role))");
113 state.execute("insert into " + USERS + " values ('admin', '" + Authentication.hashPassword("admin") + "', 'true', 'change the password for this account as soon as possible', '')");
114 state.execute("insert into " + ROLES + " values ('admin', 'administrator')");
115 state.execute("insert into " + ROLES + " values ('admin', 'all-collections-editor')");
116 conn.commit();
117 }
118 catch (Exception ex)
119 {
120 ex.printStackTrace();
121 }
122 }
123
124 public UserQueryResult listAllUser() throws SQLException
125 {
126 UserQueryResult userQueryResult = new UserQueryResult();
127 String sql_list_all_user = "SELECT username, password, accountstatus, email, comment FROM " + USERS;
128
129 ArrayList<HashMap<String, String>> users = new ArrayList<HashMap<String, String>>();
130 ResultSet rs = state.executeQuery(sql_list_all_user);
131 while (rs.next())
132 {
133 HashMap<String, String> user = new HashMap<String, String>();
134 user.put("username", rs.getString("username"));
135 user.put("password", rs.getString("password"));
136 user.put("as", rs.getString("accountstatus"));
137 user.put("comment", rs.getString("comment"));
138 user.put("email", rs.getString("email"));
139
140 users.add(user);
141 }
142
143 for (HashMap<String, String> user : users)
144 {
145 ResultSet gs = state.executeQuery("SELECT role FROM roles WHERE username = '" + user.get("username") + "'");
146 String group = "";
147 while (gs.next())
148 {
149 if (!group.equals(""))
150 {
151 group += ",";
152 }
153 group += gs.getString("role");
154 }
155 userQueryResult.addUserTerm(user.get("username"), user.get("password"), group, user.get("as"), user.get("comment"), user.get("email"));
156 }
157
158 if (userQueryResult.getSize() == 0)
159 {
160 System.out.println("couldn't find any users");
161 return null;
162 }
163 else
164 {
165 return userQueryResult;
166 }
167 }
168
169 public boolean addUser(String username, String password, String groups, String accountstatus, String comment, String email)
170 {
171 try
172 {
173 conn.setAutoCommit(false);
174 String sql_insert_user = "insert into " + USERS + " values ('" + username + "', '" + password + "', '" + accountstatus + "', '" + comment + "', '" + email + "')";
175 state.execute(sql_insert_user);
176
177 String[] groupArray = groups.split(",");
178 for (String g : groupArray)
179 {
180 String sql_insert_group = "insert into " + ROLES + " values ('" + username + "', '" + g + "')";
181 state.execute(sql_insert_group);
182 }
183
184 conn.commit();
185 }
186 catch (Throwable e)
187 {
188 System.out.println("exception thrown:");
189 if (e instanceof SQLException)
190 {
191 printSQLError((SQLException) e);
192 }
193 else
194 {
195 e.printStackTrace();
196 }
197 closeDatabase();
198 System.out.println("Error:" + e.getMessage());
199 return false;
200 }
201
202 return true;
203 }
204
205 public boolean deleteUser(String del_username)
206 {
207 try
208 {
209 conn.setAutoCommit(false);
210 String sql_delete_user = "delete from " + USERS + " where username='" + del_username + "'";
211 String sql_delete_groups = "delete from " + ROLES + " where username='" + del_username + "'";
212 state.execute(sql_delete_user);
213 state.execute(sql_delete_groups);
214 conn.commit();
215 }
216 catch (Throwable e)
217 {
218 System.out.println("exception thrown:");
219 if (e instanceof SQLException)
220 {
221 printSQLError((SQLException) e);
222 }
223 else
224 {
225 e.printStackTrace();
226 }
227 closeDatabase();
228 return false;
229 }
230 return true;
231 }
232
233 public boolean deleteAllUser() throws SQLException
234 {
235 conn.setAutoCommit(false);
236 try
237 {
238 state.execute("delete from " + USERS);
239 state.execute("delete from " + ROLES);
240 conn.commit();
241 }
242 catch (Throwable e)
243 {
244 System.out.println("exception thrown:");
245 if (e instanceof SQLException)
246 {
247 printSQLError((SQLException) e);
248 }
249 else
250 {
251 e.printStackTrace();
252 }
253 closeDatabase();
254 return false;
255 }
256 return true;
257 }
258
259 public UserQueryResult findUser(String username, String password)
260 {
261 UserQueryResult userQueryResult = new UserQueryResult();
262
263 try
264 {
265 conn.setAutoCommit(false);
266 }
267 catch (Exception ex)
268 {
269 ex.printStackTrace();
270 return null;
271 }
272
273 String sql_find_user = "SELECT username, password, accountstatus, comment, email FROM " + USERS;
274 String append_sql = "";
275
276 if (username != null)
277 {
278 append_sql = " WHERE username = '" + username + "'";
279 }
280 if (password != null)
281 {
282 if (append_sql.equals(""))
283 {
284 append_sql = " WHERE password = '" + password + "'";
285 }
286 else
287 {
288 append_sql += " and password = '" + password + "'";
289 }
290 }
291 if (!append_sql.equals(""))
292 {
293 sql_find_user += append_sql;
294 }
295
296 try
297 {
298 ArrayList<HashMap<String, String>> users = new ArrayList<HashMap<String, String>>();
299 ResultSet rs = state.executeQuery(sql_find_user);
300 while (rs.next())
301 {
302 HashMap<String, String> user = new HashMap<String, String>();
303 user.put("username", rs.getString("username"));
304 user.put("password", rs.getString("password"));
305 user.put("as", rs.getString("accountstatus"));
306 user.put("comment", rs.getString("comment"));
307 user.put("email", rs.getString("email"));
308
309 users.add(user);
310 }
311 conn.commit();
312
313 for (HashMap<String, String> user : users)
314 {
315 ResultSet gs = state.executeQuery("SELECT role FROM " + ROLES + " WHERE username = '" + user.get("username") + "'");
316
317 String group = "";
318 while (gs.next())
319 {
320 if (!group.equals(""))
321 {
322 group += ",";
323 }
324 group += gs.getString("role");
325 }
326
327 userQueryResult.addUserTerm(user.get("username"), user.get("password"), group, user.get("as"), user.get("comment"), user.get("email"));
328 }
329 }
330 catch (Exception ex)
331 {
332 ex.printStackTrace();
333 return null;
334 }
335
336 if (userQueryResult.getSize() > 0)
337 {
338 return userQueryResult;
339 }
340 else
341 {
342 System.out.println("couldn't find the user");
343 return null;
344 }
345 }
346
347 public UserQueryResult findUser(String username) throws SQLException
348 {
349 UserQueryResult userQueryResult = new UserQueryResult();
350
351 conn.setAutoCommit(false);
352 String sql_find_user = "SELECT username, password, accountstatus, comment, email FROM " + USERS;
353 String append_sql = "";
354
355 if (username != null)
356 {
357 append_sql = " WHERE username = '" + username + "'";
358 }
359 if (!append_sql.equals(""))
360 {
361 sql_find_user += append_sql;
362 }
363
364 ArrayList<HashMap<String, String>> users = new ArrayList<HashMap<String, String>>();
365 ResultSet rs = state.executeQuery(sql_find_user);
366 while (rs.next())
367 {
368 HashMap<String, String> user = new HashMap<String, String>();
369 user.put("username", rs.getString("username"));
370 user.put("password", rs.getString("password"));
371 user.put("as", rs.getString("accountstatus"));
372 user.put("comment", rs.getString("comment"));
373 user.put("email", rs.getString("email"));
374
375 users.add(user);
376 }
377 conn.commit();
378
379 for (HashMap<String, String> user : users)
380 {
381 ResultSet gs = state.executeQuery("SELECT role FROM " + ROLES + " WHERE username = '" + user.get("username") + "'");
382
383 String group = "";
384 while (gs.next())
385 {
386 if (!group.equals(""))
387 {
388 group += ",";
389 }
390 group += gs.getString("role");
391 }
392
393 userQueryResult.addUserTerm(user.get("username"), user.get("password"), group, user.get("as"), user.get("comment"), user.get("email"));
394 }
395
396 if (userQueryResult.getSize() > 0)
397 {
398 return userQueryResult;
399 }
400 else
401 {
402 System.out.println("couldn't find the user");
403 return null;
404 }
405 }
406
407 public String modifyUserInfo(String username, String new_password, String groups, String accountstatus, String comment, String email)
408 {
409 try
410 {
411 conn.setAutoCommit(false);
412 String sql_modify_user_info = "update " + USERS + " set ";
413
414 boolean needComma = false;
415 if (new_password != null && !new_password.equals(""))
416 {
417 sql_modify_user_info += "password='" + new_password + "'";
418 needComma = true;
419 }
420
421 if (accountstatus != null && comment != null)
422 {
423 sql_modify_user_info += (needComma ? "," : "") + " accountstatus='" + accountstatus + "'" + ", comment='" + comment + "'";
424 needComma = true;
425 }
426
427 if(email != null)
428 {
429 sql_modify_user_info += (needComma ? "," : "") + " email='" + email + "'";
430 }
431
432 sql_modify_user_info += " where username='" + username + "'";
433 state.execute(sql_modify_user_info);
434
435 String sql_delete_groups = "delete from " + ROLES + " where username='" + username + "'";
436 state.execute(sql_delete_groups);
437
438 String[] groupsArray = groups.split(",");
439 for (String g : groupsArray)
440 {
441 String sql_insert_group = "insert into " + ROLES + " values ('" + username + "', '" + g + "')";
442 state.execute(sql_insert_group);
443 }
444
445 conn.commit();
446 }
447 catch (Throwable e)
448 {
449 System.out.println("exception thrown:");
450 if (e instanceof SQLException)
451 {
452 printSQLError((SQLException) e);
453 }
454 else
455 {
456 e.printStackTrace();
457 }
458 closeDatabase();
459 return "Error:" + e.getMessage();
460 }
461 return "succeed";
462 }
463
464 public void db2txt()
465 {
466 try
467 {
468 conn.setAutoCommit(false);
469 String sql_list_all_user = "select username, password, accountstatus, comment, email from " + USERS;
470 ResultSet rs = state.executeQuery(sql_list_all_user);
471
472 ArrayList<HashMap<String, String>> infoMap = new ArrayList<HashMap<String, String>>();
473
474 while (rs.next())
475 {
476 HashMap<String, String> userMap = new HashMap<String, String>();
477 userMap.put("username", rs.getString("username"));
478 userMap.put("password", rs.getString("password"));
479 userMap.put("status", rs.getString("accountstatus"));
480 userMap.put("comment", rs.getString("comment"));
481 userMap.put("email", rs.getString("email"));
482 infoMap.add(userMap);
483 }
484 conn.commit();
485
486 for (HashMap<String, String> user : infoMap)
487 {
488 ResultSet groupsSet = state.executeQuery("SELECT role FROM " + ROLES + " WHERE username = '" + user.get("username") + "'");
489 String returnedGroups = "";
490 while (groupsSet.next())
491 {
492 if (!returnedGroups.equals(""))
493 {
494 returnedGroups += ",";
495 }
496 returnedGroups += groupsSet.getString("role");
497 }
498 conn.commit();
499
500 System.err.println("-------------------------------------");
501 System.err.println("USERNAME = " + user.get("username"));
502 System.err.println("PASSWORD = " + user.get("password"));
503 System.err.println("GROUPS = " + returnedGroups);
504 System.err.println("STATUS = " + user.get("status"));
505 System.err.println("COMMENT = " + user.get("comment"));
506 System.err.println("EMAIL = " + user.get("email"));
507 System.err.println("-------------------------------------");
508 }
509
510 conn.commit();
511 closeDatabase();
512 }
513 catch (Exception ex)
514 {
515 ex.printStackTrace();
516 }
517 }
518
519 static void printSQLError(SQLException e)
520 {
521 while (e != null)
522 {
523 System.out.println(e.toString());
524 e = e.getNextException();
525 }
526 }
527}
Note: See TracBrowser for help on using the repository browser.