Changeset 28201 for main/trunk/greenstone3/src/java/org/greenstone
- Timestamp:
- 2013-09-03T12:29:09+12:00 (11 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
main/trunk/greenstone3/src/java/org/greenstone/gsdl3/util/DerbyWrapper.java
r27908 r28201 27 27 import java.util.ArrayList; 28 28 import java.util.HashMap; 29 import java.util.HashSet; 29 30 30 31 import org.greenstone.gsdl3.service.Authentication; … … 39 40 static final String DATA = "data"; 40 41 private Connection conn = null; 41 private Statement state = null;42 42 private String protocol_str; 43 43 44 public DerbyWrapper()45 {46 }47 48 44 public DerbyWrapper(String dbpath) 49 45 { … … 55 51 try 56 52 { 53 if (conn != null) 54 { 55 System.err.println("Connection already established, close the database first"); 56 return; 57 } 58 57 59 Class.forName(DRIVER).newInstance(); 58 //System.out.println("Loaded the embedded driver.");59 60 protocol_str = PROTOCOL + dbpath; 60 61 if (create_database) … … 66 67 conn = DriverManager.getConnection(protocol_str); 67 68 } 68 state = conn.createStatement();69 conn.setAutoCommit(false); 69 70 } 70 71 catch (Throwable e) … … 84 85 public void closeDatabase() 85 86 { 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) 87 try 88 { 89 conn.commit(); 90 conn.close(); 91 conn = null; 92 } 93 catch (SQLException e) 101 94 { 102 95 e.printStackTrace(); 103 }104 105 if (!gotSQLExc)106 {107 System.err.println("Warning: Derby Database did not shut down normally");108 96 } 109 97 } … … 117 105 // shutdown the whole server 118 106 DriverManager.getConnection(PROTOCOL + ";shutdown=true"); 119 120 107 } 121 108 catch (SQLException se) … … 127 114 catch (Exception e) 128 115 { 129 130 116 e.printStackTrace(); 131 117 } … … 140 126 try 141 127 { 142 conn.setAutoCommit(false);128 Statement state = conn.createStatement(); 143 129 state.execute("drop table data"); 144 130 state.execute("create table data (username varchar(40) not null, name varchar(128) not null, value clob, primary key (username, name))"); 145 131 conn.commit(); 132 state.close(); 146 133 } 147 134 catch (SQLException e) … … 151 138 } 152 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 153 156 public void createDatabase() 154 157 { 155 158 try 156 159 { 157 conn.setAutoCommit(false);160 Statement state = conn.createStatement(); 158 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))"); 159 162 state.execute("create table roles (username varchar(40) not null, role varchar(40) not null, primary key (username, role))"); 160 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 161 166 state.execute("insert into " + USERS + " values ('admin', '" + Authentication.hashPassword("admin") + "', 'true', 'change the password for this account as soon as possible', '')"); 162 167 state.execute("insert into " + ROLES + " values ('admin', 'administrator')"); 163 168 state.execute("insert into " + ROLES + " values ('admin', 'all-collections-editor')"); 164 169 conn.commit(); 170 state.close(); 165 171 } 166 172 catch (Exception ex) … … 170 176 } 171 177 172 public UserQueryResult listAllUser() throws SQLException 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 173 228 { 174 229 UserQueryResult userQueryResult = new UserQueryResult(); … … 176 231 177 232 ArrayList<HashMap<String, String>> users = new ArrayList<HashMap<String, String>>(); 233 Statement state = conn.createStatement(); 178 234 ResultSet rs = state.executeQuery(sql_list_all_user); 235 conn.commit(); 236 state.close(); 237 179 238 while (rs.next()) 180 239 { … … 221 280 try 222 281 { 282 Statement state = conn.createStatement(); 223 283 ResultSet rs = state.executeQuery("SELECT * FROM " + DATA + " WHERE username='" + username + "' AND name='" + name + "'"); 284 conn.commit(); 285 state.close(); 224 286 if (rs.next()) 225 287 { … … 242 304 ex.printStackTrace(); 243 305 } 244 closeDatabase(); 306 245 307 System.out.println("Error:" + ex.getMessage()); 246 308 return false; … … 249 311 try 250 312 { 313 PreparedStatement stmt = null; 251 314 if (!found) 252 315 { 253 PreparedStatement stmt = null;254 316 stmt = conn.prepareStatement("INSERT INTO " + DATA + " VALUES (?, ?, ?)"); 255 317 stmt.setString(1, username); … … 260 322 else 261 323 { 262 PreparedStatement stmt = null;263 324 stmt = conn.prepareStatement("UPDATE " + DATA + " SET value=? WHERE username=? AND name=?"); 264 325 stmt.setString(1, value); … … 267 328 stmt.executeUpdate(); 268 329 } 330 conn.commit(); 331 stmt.close(); 269 332 } 270 333 catch (Exception ex) … … 279 342 ex.printStackTrace(); 280 343 } 281 closeDatabase(); 344 282 345 System.out.println("Error:" + ex.getMessage()); 283 346 return false; … … 290 353 try 291 354 { 355 Statement state = conn.createStatement(); 292 356 ResultSet rs = state.executeQuery("SELECT * FROM " + DATA + " WHERE username='" + username + "' AND name='" + name + "'"); 357 conn.commit(); 358 state.close(); 293 359 if (rs.next()) 294 360 { … … 307 373 ex.printStackTrace(); 308 374 } 309 closeDatabase(); 375 310 376 System.out.println("Error:" + ex.getMessage()); 311 377 } … … 317 383 try 318 384 { 319 conn.setAutoCommit(false);385 Statement state = conn.createStatement(); 320 386 String sql_insert_user = "insert into " + USERS + " values ('" + username + "', '" + password + "', '" + accountstatus + "', '" + comment + "', '" + email + "')"; 321 387 state.execute(sql_insert_user); … … 329 395 330 396 conn.commit(); 397 state.close(); 331 398 } 332 399 catch (Throwable e) … … 341 408 e.printStackTrace(); 342 409 } 343 closeDatabase(); 410 344 411 System.out.println("Error:" + e.getMessage()); 345 412 return false; … … 353 420 try 354 421 { 355 conn.setAutoCommit(false);356 422 String sql_delete_user = "delete from " + USERS + " where username='" + del_username + "'"; 357 423 String sql_delete_groups = "delete from " + ROLES + " where username='" + del_username + "'"; 424 Statement state = conn.createStatement(); 358 425 state.execute(sql_delete_user); 359 426 state.execute(sql_delete_groups); 360 427 conn.commit(); 428 state.close(); 361 429 } 362 430 catch (Throwable e) … … 371 439 e.printStackTrace(); 372 440 } 373 closeDatabase();374 441 return false; 375 442 } … … 379 446 public boolean deleteAllUser() throws SQLException 380 447 { 381 conn.setAutoCommit(false);382 try383 {448 try 449 { 450 Statement state = conn.createStatement(); 384 451 state.execute("delete from " + USERS); 385 452 state.execute("delete from " + ROLES); 386 453 conn.commit(); 454 state.close(); 387 455 } 388 456 catch (Throwable e) … … 397 465 e.printStackTrace(); 398 466 } 399 closeDatabase(); 467 400 468 return false; 401 469 } … … 406 474 { 407 475 UserQueryResult userQueryResult = new UserQueryResult(); 408 409 try410 {411 conn.setAutoCommit(false);412 }413 catch (Exception ex)414 {415 ex.printStackTrace();416 return null;417 }418 476 419 477 String sql_find_user = "SELECT username, password, accountstatus, comment, email FROM " + USERS; … … 443 501 { 444 502 ArrayList<HashMap<String, String>> users = new ArrayList<HashMap<String, String>>(); 503 Statement state = conn.createStatement(); 445 504 ResultSet rs = state.executeQuery(sql_find_user); 446 505 while (rs.next()) … … 473 532 userQueryResult.addUserTerm(user.get("username"), user.get("password"), group, user.get("as"), user.get("comment"), user.get("email")); 474 533 } 534 state.close(); 475 535 } 476 536 catch (Exception ex) … … 496 556 UserQueryResult userQueryResult = new UserQueryResult(); 497 557 498 conn.setAutoCommit(false);499 558 String sql_find_user = "SELECT username, password, accountstatus, comment, email FROM " + USERS; 500 559 String append_sql = ""; … … 510 569 511 570 ArrayList<HashMap<String, String>> users = new ArrayList<HashMap<String, String>>(); 571 572 Statement state = conn.createStatement(); 512 573 ResultSet rs = state.executeQuery(sql_find_user); 513 574 while (rs.next()) … … 540 601 userQueryResult.addUserTerm(user.get("username"), user.get("password"), group, user.get("as"), user.get("comment"), user.get("email")); 541 602 } 603 state.close(); 542 604 543 605 if (userQueryResult.getSize() > 0) … … 556 618 try 557 619 { 558 conn.setAutoCommit(false);559 620 String sql_modify_user_info = "update " + USERS + " set "; 560 621 … … 578 639 579 640 sql_modify_user_info += " where username='" + username + "'"; 641 Statement state = conn.createStatement(); 580 642 state.execute(sql_modify_user_info); 581 643 … … 591 653 592 654 conn.commit(); 655 state.close(); 593 656 } 594 657 catch (Throwable e) … … 603 666 e.printStackTrace(); 604 667 } 605 closeDatabase(); 668 606 669 return "Error:" + e.getMessage(); 607 670 } … … 620 683 try 621 684 { 622 conn.setAutoCommit(false); // An exception at this line can happen when the GS3 tomcat server is already running623 // and GS3 is already accessing the usersDB when this function independently tries to624 // connect to it (via usersDB2txt.java's main(). For an explanation of the possible625 // reasons, see http://db.apache.org/derby/papers/DerbyTut/embedded_intro.html626 // section "Embedded Derby supports multiple users in one JVM".627 685 String sql_list_all_user = "select username, password, accountstatus, comment, email from " + USERS; 686 687 Statement state = conn.createStatement(); 628 688 ResultSet rs = state.executeQuery(sql_list_all_user); 629 689 … … 668 728 669 729 conn.commit(); 670 closeDatabase();730 state.close(); 671 731 } 672 732 catch (Exception ex) … … 693 753 try 694 754 { 695 conn.setAutoCommit(false);755 Statement state = conn.createStatement(); 696 756 state.execute("DELETE FROM data WHERE username = '" + username + "' AND SUBSTR(name, 1, " + prefix.length() + ") = '" + prefix + "'"); 697 757 conn.commit(); 758 state.close(); 698 759 } 699 760 catch (Exception ex)
Note:
See TracChangeset
for help on using the changeset viewer.