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

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

Reformatting this file ahead of some changes
File size: 9.8 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 {
43 }
44
45 public DerbyWrapper(String dbpath)
46 {
47 connectDatabase(dbpath, false);
48 }
49
50 public void connectDatabase(String dbpath, boolean create_database)
51 {
52 try
53 {
54 Class.forName(DRIVER).newInstance();
55 //System.out.println("Loaded the embedded driver.");
56 protocol_str = PROTOCOL + dbpath;
57 if (create_database)
58 {
59 conn = DriverManager.getConnection(protocol_str + ";create=true");
60 }
61 else
62 {
63 conn = DriverManager.getConnection(protocol_str);
64 }
65 state = conn.createStatement();
66 }
67 catch (Throwable e)
68 {
69 System.out.println("exception thrown:");
70 if (e instanceof SQLException)
71 {
72 printSQLError((SQLException) e);
73 }
74 else
75 {
76 e.printStackTrace();
77 }
78 }
79 }
80
81 public void closeDatabase()
82 {
83 state = null;
84 conn = null;
85 boolean gotSQLExc = false;
86 try
87 {
88 DriverManager.getConnection(protocol_str + ";shutdown=true");
89 }
90 catch (SQLException se)
91 {
92 gotSQLExc = true;
93 }
94 if (!gotSQLExc)
95 {
96 System.out.println("Database did not shut down normally");
97 }
98 }
99
100 public void createDatabase() throws SQLException
101 {
102 conn.setAutoCommit(false);
103 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))");
104 //ystem.out.println("table users created successfully!");
105 state.execute("insert into " + USERS + " values ('admin', 'admin', 'administrator,all-collections-editor', 'true', 'change the password for this account as soon as possible')");
106 conn.commit();
107 }
108
109 public UserQueryResult listAllUser() throws SQLException
110 {
111 UserQueryResult userQueryResult = new UserQueryResult();
112 String sql_list_all_user = "select username, password, groups, accountstatus, comment from " + USERS;
113
114 ResultSet rs = state.executeQuery(sql_list_all_user);
115 while (rs.next())
116 {
117 String returned_username = rs.getString("username");
118 String returned_password = rs.getString("password");
119 String returned_groups = rs.getString("groups");
120 String returned_accountstatus = rs.getString("accountstatus");
121 String returned_comment = rs.getString("comment");
122 userQueryResult.addUserTerm(returned_username, returned_password, returned_groups, returned_accountstatus, returned_comment);
123 }
124 if (userQueryResult.getSize() == 0)
125 {
126 System.out.println("couldn't find any users");
127 return null;
128 }
129 else
130 {
131 return userQueryResult;
132 }
133 }
134
135 public String addUser(String username, String password, String groups, String accountstatus, String comment) throws SQLException
136 {
137 conn.setAutoCommit(false);
138 String sql_insert_user = "insert into " + USERS + " values ('" + username + "', '" + password + "', '" + groups + "', '" + accountstatus + "', '" + comment + "')";
139 try
140 {
141 state.execute(sql_insert_user);
142 conn.commit();
143 }
144 catch (Throwable e)
145 {
146 System.out.println("exception thrown:");
147 if (e instanceof SQLException)
148 {
149 printSQLError((SQLException) e);
150 }
151 else
152 {
153 e.printStackTrace();
154 }
155 closeDatabase();
156 System.out.println("Error:" + e.getMessage());
157 return "Error:" + e.getMessage();
158 }
159 return "succeed";
160 }
161
162 public String deleteUser(String del_username) throws SQLException
163 {
164 conn.setAutoCommit(false);
165 String sql_delete_user = "delete from " + USERS + " where username='" + del_username + "'";
166 try
167 {
168 state.execute(sql_delete_user);
169 conn.commit();
170 }
171 catch (Throwable e)
172 {
173 System.out.println("exception thrown:");
174 if (e instanceof SQLException)
175 {
176 printSQLError((SQLException) e);
177 }
178 else
179 {
180 e.printStackTrace();
181 }
182 closeDatabase();
183 return "Error:" + e.getMessage();
184 }
185 return "succeed";
186 }
187
188 public boolean deleteAllUser() throws SQLException
189 {
190 conn.setAutoCommit(false);
191 try
192 {
193 state.execute("delete from " + USERS);
194 conn.commit();
195 }
196 catch (Throwable e)
197 {
198 System.out.println("exception thrown:");
199 if (e instanceof SQLException)
200 {
201 printSQLError((SQLException) e);
202 }
203 else
204 {
205 e.printStackTrace();
206 }
207 closeDatabase();
208 return false;
209 }
210 return true;
211 }
212
213 public UserQueryResult findUser(String username, String password) throws SQLException
214 {
215 UserQueryResult userQueryResult = new UserQueryResult();
216
217 conn.setAutoCommit(false);
218 String sql_find_user = "SELECT username, password, groups, accountstatus, comment FROM " + USERS;
219 String append_sql = "";
220
221 if (username != null)
222 {
223 append_sql = " WHERE username = '" + username + "'";
224 }
225 if (password != null)
226 {
227 if (append_sql.equals(""))
228 {
229 append_sql = " WHERE password = '" + password + "'";
230 }
231 else
232 {
233 append_sql += " and password = '" + password + "'";
234 }
235 }
236 if (!append_sql.equals(""))
237 {
238 sql_find_user += append_sql;
239 }
240 ResultSet rs = state.executeQuery(sql_find_user);
241 while (rs.next())
242 {
243 String returned_username = rs.getString("username");
244 //System.out.println("returned_username :" + returned_username);
245 String returned_password = rs.getString("password");
246 //System.out.println("returned_password :" + returned_password);
247 String returned_groups = rs.getString("groups");
248 //System.out.println("returned_groups :" + returned_groups);
249 String returned_accountstatus = rs.getString("accountstatus");
250 //System.out.println("returned_accountstatus :" + returned_accountstatus);
251 String returned_comment = rs.getString("comment");
252 //System.out.println("returned_comment :" + returned_comment);
253 userQueryResult.addUserTerm(returned_username, returned_password, returned_groups, returned_accountstatus, returned_comment);
254 //System.out.println(userQueryResult.toString());
255 }
256 conn.commit();
257 if (userQueryResult.getSize() > 0)
258 {
259 return userQueryResult;
260 }
261 else
262 {
263 System.out.println("couldn't find the user");
264 return null;
265 }
266 }
267
268 public String modifyUserInfo(String username, String new_password, String groups, String accountstatus, String comment) throws SQLException
269 {
270 conn.setAutoCommit(false);
271 String sql_modify_user_info = "update " + USERS + " set ";
272 if (new_password != null && !new_password.equals(""))
273 {
274 sql_modify_user_info += "password='" + new_password + "'";
275 }
276
277 if (groups != null && accountstatus != null && comment != null)
278 {
279 sql_modify_user_info += ", groups='" + groups + "'" + ", accountstatus='" + accountstatus + "'" + ", comment='" + comment + "'";
280 }
281 sql_modify_user_info += " where username='" + username + "'";
282
283 try
284 {
285 System.out.println(sql_modify_user_info);
286 state.execute(sql_modify_user_info);
287 conn.commit();
288 }
289 catch (Throwable e)
290 {
291 System.out.println("exception thrown:");
292 if (e instanceof SQLException)
293 {
294 printSQLError((SQLException) e);
295 }
296 else
297 {
298 e.printStackTrace();
299 }
300 closeDatabase();
301 return "Error:" + e.getMessage();
302 }
303 return "succeed";
304 }
305
306 public void db2txt() throws SQLException
307 {
308 UserQueryResult userQueryResult = new UserQueryResult();
309 String sql_list_all_user = "select username, password, groups, accountstatus, comment from " + USERS;
310 ResultSet rs = state.executeQuery(sql_list_all_user);
311
312 while (rs.next())
313 {
314 String returned_username = rs.getString("username");
315 System.out.println("[" + returned_username + "]");
316 String returned_comment = rs.getString("comment");
317 System.out.println("<comment>" + returned_comment);
318 String returned_accountstatus = rs.getString("accountstatus");
319 System.out.println("<enabled>" + returned_accountstatus);
320 String returned_groups = rs.getString("groups");
321 System.out.println("<groups>" + returned_groups);
322 String returned_password = rot13(rs.getString("password"));
323 System.out.println("<password>" + returned_password);
324 System.out.println("<username>" + returned_username);
325 System.out.println("");
326 System.out.println("----------------------------------------------------------------------");
327 }
328 conn.commit();
329 closeDatabase();
330 }
331
332 static void printSQLError(SQLException e)
333 {
334 while (e != null)
335 {
336 System.out.println(e.toString());
337 e = e.getNextException();
338 }
339 }
340
341 //Simply use rot-13 to encrypt and decrypt the password
342 public String rot13(String password)
343 {
344 String out_password = "";
345 for (int i = 0; i < password.length(); i++)
346 {
347 char c = password.charAt(i);
348 if (c >= 'a' && c <= 'm')
349 c += 13;
350 else if (c >= 'n' && c <= 'z')
351 c -= 13;
352 else if (c >= 'A' && c <= 'M')
353 c += 13;
354 else if (c >= 'A' && c <= 'Z')
355 c -= 13;
356 out_password += c;
357 }
358 return out_password;
359 }
360}
Note: See TracBrowser for help on using the repository browser.