[16869] | 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 | */
|
---|
[14289] | 19 | package org.greenstone.gsdl3.util;
|
---|
| 20 |
|
---|
| 21 | import javax.swing.*;
|
---|
| 22 | import java.io.File;
|
---|
| 23 | import java.sql.Connection;
|
---|
| 24 | import java.sql.DriverManager;
|
---|
| 25 | import java.sql.ResultSet;
|
---|
| 26 | import java.sql.SQLException;
|
---|
| 27 | import java.sql.SQLWarning;
|
---|
| 28 | import java.sql.Statement;
|
---|
| 29 | import java.util.Properties;
|
---|
| 30 |
|
---|
| 31 | public class DerbyWrapper
|
---|
| 32 | {
|
---|
| 33 | static final String PROTOCOL = "jdbc:derby:";
|
---|
| 34 | static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";
|
---|
| 35 | static final String USERSDB = "usersDB";
|
---|
| 36 | static final String USERS = "users";
|
---|
| 37 | private Connection conn=null;
|
---|
| 38 | private Statement state=null;
|
---|
| 39 | private String protocol_str;
|
---|
| 40 |
|
---|
| 41 | public DerbyWrapper(){}
|
---|
| 42 | public DerbyWrapper(String dbpath){
|
---|
| 43 | connectDatabase(dbpath,false);
|
---|
| 44 | }
|
---|
| 45 |
|
---|
| 46 | public void connectDatabase(String dbpath, boolean create_database){
|
---|
| 47 | try{
|
---|
| 48 | Class.forName(DRIVER).newInstance();
|
---|
| 49 | //System.out.println("Loaded the embedded driver.");
|
---|
| 50 | protocol_str = PROTOCOL + dbpath;
|
---|
| 51 | if (create_database){
|
---|
| 52 | conn = DriverManager.getConnection(protocol_str + ";create=true");
|
---|
| 53 | }else{
|
---|
| 54 | conn = DriverManager.getConnection(protocol_str);
|
---|
| 55 | }
|
---|
| 56 | state = conn.createStatement();
|
---|
| 57 | }catch (Throwable e){
|
---|
| 58 | System.out.println("exception thrown:");
|
---|
| 59 | if (e instanceof SQLException){
|
---|
| 60 | printSQLError((SQLException) e);
|
---|
| 61 | }else{
|
---|
| 62 | e.printStackTrace();
|
---|
| 63 | }
|
---|
| 64 | }
|
---|
| 65 | }
|
---|
| 66 |
|
---|
| 67 | public void closeDatabase() {
|
---|
| 68 | state = null;
|
---|
| 69 | conn = null;
|
---|
| 70 | boolean gotSQLExc = false;
|
---|
| 71 | try{
|
---|
| 72 | DriverManager.getConnection(protocol_str + ";shutdown=true");
|
---|
| 73 | }catch (SQLException se){
|
---|
| 74 | gotSQLExc = true;
|
---|
| 75 | }
|
---|
| 76 | if (!gotSQLExc){
|
---|
| 77 | System.out.println("Database did not shut down normally");
|
---|
| 78 | }
|
---|
| 79 | }
|
---|
| 80 |
|
---|
| 81 | public void createDatabase()throws SQLException{
|
---|
| 82 | conn.setAutoCommit(false);
|
---|
| 83 | state.execute("create table users (username varchar(40) not null, password varchar(40) not null, groups varchar(500), accountstatus varchar(10), comment varchar(100), primary key(username))");
|
---|
| 84 | //ystem.out.println("table users created successfully!");
|
---|
| 85 | state.execute("insert into "+USERS+" values ('admin', 'admin', 'administrator,all-collections-editor', 'true', 'change the password for this account as soon as possible')");
|
---|
| 86 | conn.commit();
|
---|
| 87 | }
|
---|
| 88 |
|
---|
| 89 | public UserQueryResult listAllUser()throws SQLException{
|
---|
| 90 | UserQueryResult userQueryResult=new UserQueryResult();
|
---|
| 91 | String sql_list_all_user="select username, password, groups, accountstatus, comment from " + USERS ;
|
---|
| 92 |
|
---|
| 93 | ResultSet rs = state.executeQuery(sql_list_all_user);
|
---|
| 94 | while (rs.next()){
|
---|
| 95 | String returned_username= rs.getString("username");
|
---|
| 96 | String returned_password=rs.getString("password");
|
---|
| 97 | String returned_groups=rs.getString("groups");
|
---|
| 98 | String returned_accountstatus=rs.getString("accountstatus");
|
---|
| 99 | String returned_comment=rs.getString("comment");
|
---|
| 100 | userQueryResult.addUserTerm(returned_username,returned_password,returned_groups,returned_accountstatus,returned_comment);
|
---|
| 101 | }
|
---|
| 102 | if (userQueryResult.getSize()==0){
|
---|
| 103 | System.out.println("couldn't find any users");
|
---|
| 104 | return null;
|
---|
| 105 | }else{
|
---|
| 106 | return userQueryResult;
|
---|
| 107 | }
|
---|
| 108 | }
|
---|
| 109 |
|
---|
| 110 | public String addUser(String username, String password, String groups, String accountstatus, String comment)throws SQLException{
|
---|
| 111 | conn.setAutoCommit(false);
|
---|
| 112 | String sql_insert_user="insert into " + USERS + " values ('"+username +"', '"+password+"', '"+groups+"', '"+accountstatus+"', '"+comment+"')";
|
---|
| 113 | try{
|
---|
| 114 | state.execute(sql_insert_user);
|
---|
| 115 | conn.commit();
|
---|
| 116 | }catch(Throwable e){
|
---|
| 117 | System.out.println("exception thrown:");
|
---|
| 118 | if (e instanceof SQLException){
|
---|
| 119 | printSQLError((SQLException) e);
|
---|
| 120 | }else{
|
---|
| 121 | e.printStackTrace();
|
---|
| 122 | }
|
---|
| 123 | closeDatabase();
|
---|
| 124 | System.out.println("Error:"+e.getMessage());
|
---|
| 125 | return "Error:"+e.getMessage();
|
---|
| 126 | }
|
---|
| 127 | return "succeed";
|
---|
| 128 | }
|
---|
| 129 |
|
---|
| 130 | public String deleteUser(String del_username)throws SQLException{
|
---|
| 131 | conn.setAutoCommit(false);
|
---|
| 132 | String sql_delete_user="delete from " + USERS + " where username='"+del_username+"'";
|
---|
| 133 | try{
|
---|
| 134 | state.execute(sql_delete_user);
|
---|
| 135 | conn.commit();
|
---|
| 136 | }catch(Throwable e){
|
---|
| 137 | System.out.println("exception thrown:");
|
---|
| 138 | if (e instanceof SQLException){
|
---|
| 139 | printSQLError((SQLException) e);
|
---|
| 140 | }else{
|
---|
| 141 | e.printStackTrace();
|
---|
| 142 | }
|
---|
| 143 | closeDatabase();
|
---|
| 144 | return "Error:"+e.getMessage();
|
---|
| 145 | }
|
---|
| 146 | return "succeed";
|
---|
| 147 | }
|
---|
[14335] | 148 |
|
---|
| 149 | public boolean deleteAllUser() throws SQLException{
|
---|
| 150 | conn.setAutoCommit(false);
|
---|
| 151 | try{
|
---|
| 152 | state.execute("delete from " + USERS);
|
---|
| 153 | conn.commit();
|
---|
| 154 | }catch(Throwable e){
|
---|
| 155 | System.out.println("exception thrown:");
|
---|
| 156 | if (e instanceof SQLException){
|
---|
| 157 | printSQLError((SQLException) e);
|
---|
| 158 | }else{
|
---|
| 159 | e.printStackTrace();
|
---|
| 160 | }
|
---|
| 161 | closeDatabase();
|
---|
| 162 | return false;
|
---|
| 163 | }
|
---|
| 164 | return true;
|
---|
| 165 | }
|
---|
| 166 |
|
---|
[14289] | 167 | public UserQueryResult findUser(String username, String password)throws SQLException{
|
---|
| 168 | UserQueryResult userQueryResult=new UserQueryResult();
|
---|
| 169 |
|
---|
| 170 | conn.setAutoCommit(false);
|
---|
| 171 | String sql_find_user="SELECT username, password, groups, accountstatus, comment FROM "+USERS;
|
---|
| 172 | String append_sql="";
|
---|
| 173 |
|
---|
| 174 | if (username!=null){
|
---|
| 175 | append_sql=" WHERE username = '" + username+ "'";
|
---|
| 176 | }
|
---|
| 177 | if (password!=null){
|
---|
| 178 | if (append_sql.equals("")){
|
---|
| 179 | append_sql=" WHERE password = '"+password+"'";
|
---|
| 180 | }else{
|
---|
| 181 | append_sql+=" and password = '"+password+"'";
|
---|
| 182 | }
|
---|
| 183 | }
|
---|
| 184 | if (!append_sql.equals("")){
|
---|
| 185 | sql_find_user+=append_sql;
|
---|
| 186 | }
|
---|
| 187 | ResultSet rs = state.executeQuery(sql_find_user);
|
---|
| 188 | while (rs.next()){
|
---|
| 189 | String returned_username= rs.getString("username");
|
---|
[14530] | 190 | //System.out.println("returned_username :" + returned_username);
|
---|
[14289] | 191 | String returned_password=rs.getString("password");
|
---|
[14530] | 192 | //System.out.println("returned_password :" + returned_password);
|
---|
[14289] | 193 | String returned_groups=rs.getString("groups");
|
---|
[14530] | 194 | //System.out.println("returned_groups :" + returned_groups);
|
---|
[14289] | 195 | String returned_accountstatus=rs.getString("accountstatus");
|
---|
[14530] | 196 | //System.out.println("returned_accountstatus :" + returned_accountstatus);
|
---|
[14289] | 197 | String returned_comment=rs.getString("comment");
|
---|
[14530] | 198 | //System.out.println("returned_comment :" + returned_comment);
|
---|
[14289] | 199 | userQueryResult.addUserTerm(returned_username,returned_password,returned_groups,returned_accountstatus,returned_comment);
|
---|
[14530] | 200 | //System.out.println(userQueryResult.toString());
|
---|
[14289] | 201 | }
|
---|
| 202 | conn.commit();
|
---|
[14338] | 203 | if (userQueryResult.getSize()>0){
|
---|
| 204 | return userQueryResult;
|
---|
| 205 | }else{
|
---|
[14289] | 206 | System.out.println("couldn't find the user");
|
---|
| 207 | return null;
|
---|
| 208 | }
|
---|
| 209 | }
|
---|
| 210 |
|
---|
| 211 | public String modifyUserInfo(String username, String new_password,String groups, String accountstatus, String comment)throws SQLException{
|
---|
| 212 | conn.setAutoCommit(false);
|
---|
| 213 | String sql_modify_user_info="update " + USERS +" set ";
|
---|
| 214 | if (new_password!=null && !new_password.equals("")){
|
---|
[14341] | 215 | sql_modify_user_info += "password='" + new_password +"'";
|
---|
[14289] | 216 | }
|
---|
[14341] | 217 |
|
---|
| 218 | if (groups!=null && accountstatus!=null && comment!=null){
|
---|
| 219 | sql_modify_user_info += ", groups='" + groups + "'" + ", accountstatus='" + accountstatus + "'" + ", comment='" + comment + "'";
|
---|
| 220 | }
|
---|
| 221 | sql_modify_user_info +=" where username='" + username+"'";
|
---|
[14289] | 222 |
|
---|
| 223 | try{
|
---|
| 224 | System.out.println(sql_modify_user_info);
|
---|
| 225 | state.execute(sql_modify_user_info);
|
---|
| 226 | conn.commit();
|
---|
| 227 | }catch(Throwable e){
|
---|
| 228 | System.out.println("exception thrown:");
|
---|
| 229 | if (e instanceof SQLException){
|
---|
| 230 | printSQLError((SQLException) e);
|
---|
| 231 | }else{
|
---|
| 232 | e.printStackTrace();
|
---|
| 233 | }
|
---|
| 234 | closeDatabase();
|
---|
| 235 | return "Error:"+e.getMessage();
|
---|
| 236 | }
|
---|
| 237 | return "succeed";
|
---|
| 238 | }
|
---|
| 239 |
|
---|
[14327] | 240 | public void db2txt() throws SQLException{
|
---|
[14289] | 241 | UserQueryResult userQueryResult=new UserQueryResult();
|
---|
| 242 | String sql_list_all_user="select username, password, groups, accountstatus, comment from " + USERS ;
|
---|
| 243 | ResultSet rs = state.executeQuery(sql_list_all_user);
|
---|
| 244 |
|
---|
| 245 | while (rs.next()){
|
---|
| 246 | String returned_username= rs.getString("username");
|
---|
| 247 | System.out.println("[" + returned_username+"]");
|
---|
| 248 | String returned_comment=rs.getString("comment");
|
---|
| 249 | System.out.println("<comment>" + returned_comment);
|
---|
| 250 | String returned_accountstatus=rs.getString("accountstatus");
|
---|
| 251 | System.out.println("<enabled>" + returned_accountstatus);
|
---|
| 252 | String returned_groups=rs.getString("groups");
|
---|
| 253 | System.out.println("<groups>" + returned_groups);
|
---|
[14366] | 254 | String returned_password=rot13(rs.getString("password"));
|
---|
[14289] | 255 | System.out.println("<password>" + returned_password);
|
---|
| 256 | System.out.println("<username>" + returned_username);
|
---|
| 257 | System.out.println("");
|
---|
| 258 | System.out.println("----------------------------------------------------------------------");
|
---|
| 259 | }
|
---|
| 260 | conn.commit();
|
---|
| 261 | closeDatabase();
|
---|
| 262 | }
|
---|
| 263 |
|
---|
| 264 | static void printSQLError(SQLException e)
|
---|
| 265 | {
|
---|
| 266 | while (e != null)
|
---|
| 267 | {
|
---|
| 268 | System.out.println(e.toString());
|
---|
| 269 | e = e.getNextException();
|
---|
| 270 | }
|
---|
| 271 | }
|
---|
[14366] | 272 |
|
---|
| 273 | //Simply use rot-13 to encrypt and decrypt the password
|
---|
| 274 | public String rot13(String password){
|
---|
| 275 | String out_password="";
|
---|
| 276 | for (int i = 0; i < password.length(); i++) {
|
---|
| 277 | char c = password.charAt(i);
|
---|
| 278 | if (c >= 'a' && c <= 'm') c += 13;
|
---|
| 279 | else if (c >= 'n' && c <= 'z') c -= 13;
|
---|
| 280 | else if (c >= 'A' && c <= 'M') c += 13;
|
---|
| 281 | else if (c >= 'A' && c <= 'Z') c -= 13;
|
---|
| 282 | out_password+=c;
|
---|
| 283 | }
|
---|
| 284 | return out_password;
|
---|
| 285 | }
|
---|
[14289] | 286 | }
|
---|