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

Last change on this file since 16869 was 16869, checked in by kjdon, 16 years ago

added license message
File size: 9.7 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.Properties;
30
31public 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 }
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
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");
190 //System.out.println("returned_username :" + returned_username);
191 String returned_password=rs.getString("password");
192 //System.out.println("returned_password :" + returned_password);
193 String returned_groups=rs.getString("groups");
194 //System.out.println("returned_groups :" + returned_groups);
195 String returned_accountstatus=rs.getString("accountstatus");
196 //System.out.println("returned_accountstatus :" + returned_accountstatus);
197 String returned_comment=rs.getString("comment");
198 //System.out.println("returned_comment :" + returned_comment);
199 userQueryResult.addUserTerm(returned_username,returned_password,returned_groups,returned_accountstatus,returned_comment);
200 //System.out.println(userQueryResult.toString());
201 }
202 conn.commit();
203 if (userQueryResult.getSize()>0){
204 return userQueryResult;
205 }else{
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("")){
215 sql_modify_user_info += "password='" + new_password +"'";
216 }
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+"'";
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
240 public void db2txt() throws SQLException{
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);
254 String returned_password=rot13(rs.getString("password"));
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 }
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 }
286}
Note: See TracBrowser for help on using the repository browser.