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

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

Added a function that gets a user by username
File size: 12.9 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.*;
22import java.io.File;
23import java.sql.Connection;
24import java.sql.DriverManager;
25import java.sql.ResultSet;
26import java.sql.SQLException;
27import java.sql.SQLWarning;
28import java.sql.Statement;
29import java.util.ArrayList;
30import java.util.HashMap;
31import java.util.Properties;
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 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 DriverManager.getConnection(protocol_str + ";shutdown=true");
92 }
93 catch (SQLException se)
94 {
95 gotSQLExc = true;
96 }
97 if (!gotSQLExc)
98 {
99 System.out.println("Database did not shut down normally");
100 }
101 }
102
103 public void createDatabase()
104 {
105 try
106 {
107 conn.setAutoCommit(false);
108 state.execute("create table users (username varchar(40) not null, password varchar(40) not null, accountstatus varchar(10), comment varchar(100), primary key(username))");
109 state.execute("create table roles (username varchar(40) not null, role varchar(40) not null, primary key (username, role))");
110 //ystem.out.println("table users created successfully!");
111 state.execute("insert into " + USERS + " values ('admin', 'admin', 'true', 'change the password for this account as soon as possible')");
112 state.execute("insert into " + ROLES + " values ('admin', 'administrator')");
113 state.execute("insert into " + ROLES + " values ('admin', 'all-collections-editor')");
114 conn.commit();
115 }
116 catch (Exception ex)
117 {
118 ex.printStackTrace();
119 }
120 }
121
122 public UserQueryResult listAllUser() throws SQLException
123 {
124 UserQueryResult userQueryResult = new UserQueryResult();
125 String sql_list_all_user = "SELECT username, password, accountstatus, comment FROM " + USERS;
126
127 ArrayList<HashMap<String, String>> users = new ArrayList<HashMap<String, String>>();
128 ResultSet rs = state.executeQuery(sql_list_all_user);
129 while (rs.next())
130 {
131 HashMap<String, String> user = new HashMap<String, String>();
132 user.put("username", rs.getString("username"));
133 user.put("password", rs.getString("password"));
134 user.put("as", rs.getString("accountstatus"));
135 user.put("comment", rs.getString("comment"));
136
137 users.add(user);
138 }
139
140 for (HashMap<String, String> user : users)
141 {
142 ResultSet gs = state.executeQuery("SELECT role FROM roles WHERE username = '" + user.get("username") + "'");
143 String group = "";
144 while (gs.next())
145 {
146 if (!group.equals(""))
147 {
148 group += ",";
149 }
150 group += gs.getString("role");
151 }
152 userQueryResult.addUserTerm(user.get("username"), user.get("password"), group, user.get("as"), user.get("comment"));
153 }
154
155 if (userQueryResult.getSize() == 0)
156 {
157 System.out.println("couldn't find any users");
158 return null;
159 }
160 else
161 {
162 return userQueryResult;
163 }
164 }
165
166 public String addUser(String username, String password, String groups, String accountstatus, String comment)
167 {
168 try
169 {
170 conn.setAutoCommit(false);
171 String sql_insert_user = "insert into " + USERS + " values ('" + username + "', '" + password + "', '" + accountstatus + "', '" + comment + "')";
172 state.execute(sql_insert_user);
173
174 String[] groupArray = groups.split(",");
175 for (String g : groupArray)
176 {
177 String sql_insert_group = "insert into " + ROLES + " values ('" + username + "', '" + g + "')";
178 state.execute(sql_insert_group);
179 }
180
181 conn.commit();
182 }
183 catch (Throwable e)
184 {
185 System.out.println("exception thrown:");
186 if (e instanceof SQLException)
187 {
188 printSQLError((SQLException) e);
189 }
190 else
191 {
192 e.printStackTrace();
193 }
194 closeDatabase();
195 System.out.println("Error:" + e.getMessage());
196 return "Error:" + e.getMessage();
197 }
198
199 return "succeed";
200 }
201
202 public String deleteUser(String del_username)
203 {
204 try
205 {
206 conn.setAutoCommit(false);
207 String sql_delete_user = "delete from " + USERS + " where username='" + del_username + "'";
208 String sql_delete_groups = "delete from " + ROLES + " where username='" + del_username + "'";
209 state.execute(sql_delete_user);
210 state.execute(sql_delete_groups);
211 conn.commit();
212 }
213 catch (Throwable e)
214 {
215 System.out.println("exception thrown:");
216 if (e instanceof SQLException)
217 {
218 printSQLError((SQLException) e);
219 }
220 else
221 {
222 e.printStackTrace();
223 }
224 closeDatabase();
225 return "Error:" + e.getMessage();
226 }
227 return "succeed";
228 }
229
230 public boolean deleteAllUser() throws SQLException
231 {
232 conn.setAutoCommit(false);
233 try
234 {
235 state.execute("delete from " + USERS);
236 state.execute("delete from " + ROLES);
237 conn.commit();
238 }
239 catch (Throwable e)
240 {
241 System.out.println("exception thrown:");
242 if (e instanceof SQLException)
243 {
244 printSQLError((SQLException) e);
245 }
246 else
247 {
248 e.printStackTrace();
249 }
250 closeDatabase();
251 return false;
252 }
253 return true;
254 }
255
256 public UserQueryResult findUser(String username, String password) throws SQLException
257 {
258 UserQueryResult userQueryResult = new UserQueryResult();
259
260 conn.setAutoCommit(false);
261 String sql_find_user = "SELECT username, password, accountstatus, comment FROM " + USERS;
262 String append_sql = "";
263
264 if (username != null)
265 {
266 append_sql = " WHERE username = '" + username + "'";
267 }
268 if (password != null)
269 {
270 if (append_sql.equals(""))
271 {
272 append_sql = " WHERE password = '" + password + "'";
273 }
274 else
275 {
276 append_sql += " and password = '" + password + "'";
277 }
278 }
279 if (!append_sql.equals(""))
280 {
281 sql_find_user += append_sql;
282 }
283
284 ArrayList<HashMap<String, String>> users = new ArrayList<HashMap<String, String>>();
285 ResultSet rs = state.executeQuery(sql_find_user);
286 while (rs.next())
287 {
288 HashMap<String, String> user = new HashMap<String, String>();
289 user.put("username", rs.getString("username"));
290 user.put("password", rs.getString("password"));
291 user.put("as", rs.getString("accountstatus"));
292 user.put("comment", rs.getString("comment"));
293
294 users.add(user);
295 }
296 conn.commit();
297
298 for (HashMap<String, String> user : users)
299 {
300 ResultSet gs = state.executeQuery("SELECT role FROM " + ROLES + " WHERE username = '" + user.get("username") + "'");
301
302 String group = "";
303 while (gs.next())
304 {
305 if (!group.equals(""))
306 {
307 group += ",";
308 }
309 group += gs.getString("role");
310 }
311
312 userQueryResult.addUserTerm(user.get("username"), user.get("password"), group, user.get("as"), user.get("comment"));
313 }
314
315 if (userQueryResult.getSize() > 0)
316 {
317 return userQueryResult;
318 }
319 else
320 {
321 System.out.println("couldn't find the user");
322 return null;
323 }
324 }
325
326 public UserQueryResult findUser(String username) throws SQLException
327 {
328 UserQueryResult userQueryResult = new UserQueryResult();
329
330 conn.setAutoCommit(false);
331 String sql_find_user = "SELECT username, password, accountstatus, comment FROM " + USERS;
332 String append_sql = "";
333
334 if (username != null)
335 {
336 append_sql = " WHERE username = '" + username + "'";
337 }
338 if (!append_sql.equals(""))
339 {
340 sql_find_user += append_sql;
341 }
342
343 ArrayList<HashMap<String, String>> users = new ArrayList<HashMap<String, String>>();
344 ResultSet rs = state.executeQuery(sql_find_user);
345 while (rs.next())
346 {
347 HashMap<String, String> user = new HashMap<String, String>();
348 user.put("username", rs.getString("username"));
349 user.put("password", rs.getString("password"));
350 user.put("as", rs.getString("accountstatus"));
351 user.put("comment", rs.getString("comment"));
352
353 users.add(user);
354 }
355 conn.commit();
356
357 for (HashMap<String, String> user : users)
358 {
359 ResultSet gs = state.executeQuery("SELECT role FROM " + ROLES + " WHERE username = '" + user.get("username") + "'");
360
361 String group = "";
362 while (gs.next())
363 {
364 if (!group.equals(""))
365 {
366 group += ",";
367 }
368 group += gs.getString("role");
369 }
370
371 userQueryResult.addUserTerm(user.get("username"), user.get("password"), group, user.get("as"), user.get("comment"));
372 }
373
374 if (userQueryResult.getSize() > 0)
375 {
376 return userQueryResult;
377 }
378 else
379 {
380 System.out.println("couldn't find the user");
381 return null;
382 }
383 }
384
385 public String modifyUserInfo(String username, String new_password, String groups, String accountstatus, String comment)
386 {
387 try
388 {
389 conn.setAutoCommit(false);
390 String sql_modify_user_info = "update " + USERS + " set ";
391 if (new_password != null && !new_password.equals(""))
392 {
393 sql_modify_user_info += "password='" + new_password + "'";
394 }
395
396 if (accountstatus != null && comment != null)
397 {
398 sql_modify_user_info += ", accountstatus='" + accountstatus + "'" + ", comment='" + comment + "'";
399 }
400 sql_modify_user_info += " where username='" + username + "'";
401 System.out.println(sql_modify_user_info);
402 state.execute(sql_modify_user_info);
403
404 String sql_delete_groups = "delete from " + ROLES + " where username='" + username + "'";
405 state.execute(sql_delete_groups);
406
407 String[] groupsArray = groups.split(",");
408 for (String g : groupsArray)
409 {
410 String sql_insert_group = "insert into " + ROLES + " values ('" + username + "', '" + g + "')";
411 state.execute(sql_insert_group);
412 }
413
414 conn.commit();
415 }
416 catch (Throwable e)
417 {
418 System.out.println("exception thrown:");
419 if (e instanceof SQLException)
420 {
421 printSQLError((SQLException) e);
422 }
423 else
424 {
425 e.printStackTrace();
426 }
427 closeDatabase();
428 return "Error:" + e.getMessage();
429 }
430 return "succeed";
431 }
432
433 public void db2txt() throws SQLException
434 {
435 UserQueryResult userQueryResult = new UserQueryResult();
436 String sql_list_all_user = "select username, password, accountstatus, comment from " + USERS;
437 ResultSet rs = state.executeQuery(sql_list_all_user);
438
439 while (rs.next())
440 {
441 String returned_username = rs.getString("username");
442 System.out.println("[" + returned_username + "]");
443 String returned_comment = rs.getString("comment");
444 System.out.println("<comment>" + returned_comment);
445 String returned_accountstatus = rs.getString("accountstatus");
446 System.out.println("<enabled>" + returned_accountstatus);
447 ResultSet groupsSet = state.executeQuery("SELECT role FROM " + ROLES + " WHERE username = '" + returned_username + "'");
448 String returned_groups = "";
449 while (groupsSet.next())
450 {
451 if (!returned_groups.equals(""))
452 {
453 returned_groups += ",";
454 }
455 returned_groups += groupsSet.getString("role");
456 }
457 System.out.println("<groups>" + returned_groups);
458 String returned_password = rot13(rs.getString("password"));
459 System.out.println("<password>" + returned_password);
460 System.out.println("<username>" + returned_username);
461 System.out.println("");
462 System.out.println("----------------------------------------------------------------------");
463 }
464 conn.commit();
465 closeDatabase();
466 }
467
468 static void printSQLError(SQLException e)
469 {
470 while (e != null)
471 {
472 System.out.println(e.toString());
473 e = e.getNextException();
474 }
475 }
476
477 //Simply use rot-13 to encrypt and decrypt the password
478 public String rot13(String password)
479 {
480 String out_password = "";
481 for (int i = 0; i < password.length(); i++)
482 {
483 char c = password.charAt(i);
484 if (c >= 'a' && c <= 'm')
485 c += 13;
486 else if (c >= 'n' && c <= 'z')
487 c -= 13;
488 else if (c >= 'A' && c <= 'M')
489 c += 13;
490 else if (c >= 'A' && c <= 'Z')
491 c -= 13;
492 out_password += c;
493 }
494 return out_password;
495 }
496}
Note: See TracBrowser for help on using the repository browser.