- Timestamp:
- 2012-03-23T14:23:55+13:00 (12 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
main/trunk/greenstone3/src/java/org/greenstone/gsdl3/util/DerbyWrapper.java
r25125 r25261 20 20 21 21 import javax.swing.*; 22 23 import org.greenstone.gsdl3.service.Authentication; 24 22 25 import java.io.File; 23 26 import java.sql.Connection; … … 106 109 { 107 110 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))");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))"); 109 112 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')"); 113 state.execute("insert into " + USERS + " values ('admin', '" + Authentication.hashPassword("admin") + "', 'true', 'change the password for this account as soon as possible', '')"); 112 114 state.execute("insert into " + ROLES + " values ('admin', 'administrator')"); 113 115 state.execute("insert into " + ROLES + " values ('admin', 'all-collections-editor')"); … … 123 125 { 124 126 UserQueryResult userQueryResult = new UserQueryResult(); 125 String sql_list_all_user = "SELECT username, password, accountstatus, comment FROM " + USERS;127 String sql_list_all_user = "SELECT username, password, accountstatus, email, comment FROM " + USERS; 126 128 127 129 ArrayList<HashMap<String, String>> users = new ArrayList<HashMap<String, String>>(); … … 134 136 user.put("as", rs.getString("accountstatus")); 135 137 user.put("comment", rs.getString("comment")); 138 user.put("email", rs.getString("email")); 136 139 137 140 users.add(user); … … 150 153 group += gs.getString("role"); 151 154 } 152 userQueryResult.addUserTerm(user.get("username"), user.get("password"), group, user.get("as"), user.get("comment") );155 userQueryResult.addUserTerm(user.get("username"), user.get("password"), group, user.get("as"), user.get("comment"), user.get("email")); 153 156 } 154 157 … … 164 167 } 165 168 166 public String addUser(String username, String password, String groups, String accountstatus, String comment)169 public boolean addUser(String username, String password, String groups, String accountstatus, String comment, String email) 167 170 { 168 171 try 169 172 { 170 173 conn.setAutoCommit(false); 171 String sql_insert_user = "insert into " + USERS + " values ('" + username + "', '" + password + "', '" + accountstatus + "', '" + comment + "' )";174 String sql_insert_user = "insert into " + USERS + " values ('" + username + "', '" + password + "', '" + accountstatus + "', '" + comment + "', '" + email + "')"; 172 175 state.execute(sql_insert_user); 173 176 … … 194 197 closeDatabase(); 195 198 System.out.println("Error:" + e.getMessage()); 196 return "Error:" + e.getMessage();197 } 198 199 return "succeed";200 } 201 202 public StringdeleteUser(String del_username)199 return false; 200 } 201 202 return true; 203 } 204 205 public boolean deleteUser(String del_username) 203 206 { 204 207 try … … 223 226 } 224 227 closeDatabase(); 225 return "Error:" + e.getMessage();226 } 227 return "succeed";228 return false; 229 } 230 return true; 228 231 } 229 232 … … 254 257 } 255 258 256 public UserQueryResult findUser(String username, String password) throws SQLException259 public UserQueryResult findUser(String username, String password) 257 260 { 258 261 UserQueryResult userQueryResult = new UserQueryResult(); 259 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 260 351 conn.setAutoCommit(false); 261 String sql_find_user = "SELECT username, password, accountstatus, comment FROM " + USERS;352 String sql_find_user = "SELECT username, password, accountstatus, comment, email FROM " + USERS; 262 353 String append_sql = ""; 263 354 … … 265 356 { 266 357 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 else275 {276 append_sql += " and password = '" + password + "'";277 }278 358 } 279 359 if (!append_sql.equals("")) … … 291 371 user.put("as", rs.getString("accountstatus")); 292 372 user.put("comment", rs.getString("comment")); 373 user.put("email", rs.getString("email")); 293 374 294 375 users.add(user); … … 310 391 } 311 392 312 userQueryResult.addUserTerm(user.get("username"), user.get("password"), group, user.get("as"), user.get("comment") );393 userQueryResult.addUserTerm(user.get("username"), user.get("password"), group, user.get("as"), user.get("comment"), user.get("email")); 313 394 } 314 395 … … 324 405 } 325 406 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) 407 public String modifyUserInfo(String username, String new_password, String groups, String accountstatus, String comment, String email) 386 408 { 387 409 try … … 389 411 conn.setAutoCommit(false); 390 412 String sql_modify_user_info = "update " + USERS + " set "; 413 414 boolean needComma = false; 391 415 if (new_password != null && !new_password.equals("")) 392 416 { 393 417 sql_modify_user_info += "password='" + new_password + "'"; 418 needComma = true; 394 419 } 395 420 396 421 if (accountstatus != null && comment != null) 397 422 { 398 sql_modify_user_info += ", accountstatus='" + accountstatus + "'" + ", comment='" + comment + "'"; 399 } 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 400 432 sql_modify_user_info += " where username='" + username + "'"; 401 System.out.println(sql_modify_user_info);402 433 state.execute(sql_modify_user_info); 403 434 … … 431 462 } 432 463 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("")) 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()) 452 491 { 453 returned_groups += ","; 492 if (!returnedGroups.equals("")) 493 { 494 returnedGroups += ","; 495 } 496 returnedGroups += groupsSet.getString("role"); 454 497 } 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(); 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 } 466 517 } 467 518 … … 474 525 } 475 526 } 476 477 //Simply use rot-13 to encrypt and decrypt the password478 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 527 }
Note:
See TracChangeset
for help on using the changeset viewer.
