Source code: openfuture/bugbase/domain/update/CreateDBUsersUpdate.java
1 package openfuture.bugbase.domain.update;
2 /*
3 * This library is free software; you can redistribute it and/or
4 * modify it under the terms of the GNU Lesser General Public
5 * License as published by the Free Software Foundation; either
6 * version 2 of the License, or (at your option) any later version.<p>
7 *
8 * This library is distributed in the hope that it will be useful,
9 * but WITHOUT ANY WARRANTY; without even the implied warranty of
10 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
11 * Lesser General Public License for more details.<p>
12 *
13 * You should have received a copy of the GNU Lesser General Public
14 * License along with this library; if not, write to the Free Software
15 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA<br>
16 * http://www.gnu.org/copyleft/lesser.html
17 */
18
19 import java.sql.Connection;
20 import java.sql.SQLException;
21 import java.sql.Statement;
22 import java.text.ParseException;
23 import java.text.SimpleDateFormat;
24 import java.util.Iterator;
25 import java.util.LinkedList;
26 import openfuture.bugbase.domain.Persistency;
27 import openfuture.bugbase.domain.User;
28 import openfuture.bugbase.model.TransactionResult;
29 import openfuture.bugbase.model.Version;
30 import openfuture.bugbase.model.VersionUpdate;
31 import openfuture.util.database.DBServer;
32
33 // Configuration Management Information:
34 // -------------------------------------
35 // $Id: CreateDBUsersUpdate.java,v 1.3 2001/07/01 06:46:30 wreissen Exp $
36 //
37 // Version History:
38 // ----------------
39 // $Log: CreateDBUsersUpdate.java,v $
40 // Revision 1.3 2001/07/01 06:46:30 wreissen
41 // fixed errors in DOS/Unix-translation.
42 //
43 // Revision 1.3 2001/06/26 12:09:01 wreissen
44 // table names in lowercase.
45 //
46 // Revision 1.2 2001/03/26 15:44:15 wreissen
47 // tableExists moved from Persistency to DBServer
48 //
49 // Revision 1.1 2000/09/27 15:53:24 wreissen
50 // moved to openfuture.
51 //
52 // Revision 1.3 2000/08/28 10:20:40 wreissen
53 // the Persistency holds the database connection statically.
54 //
55 // Revision 1.2 2000/07/12 08:18:15 wreissen
56 // All connection relevant methods obtain the connection as argument,
57 // since user sessions are introduced.
58 //
59 // Revision 1.1 2000/06/15 04:59:34 wreissen
60 // initial version
61 //
62 //
63 // ***********************************************************************************
64 /**
65 * Create all existing users as database users and replace the email address
66 * in the bug reports by the user IDs.
67 *
68 *
69 * Created: Wed Jun 14 12:27:16 2000
70 *
71 * @author Wolfgang Reissenberger
72 * @version $Revision: 1.3 $
73 */
74
75 public class CreateDBUsersUpdate implements VersionUpdate {
76
77 private Version version;
78 private Connection connection;
79 private Persistency persistency;
80 private LinkedList bugreportColumns;
81 private LinkedList bugreportColtypes;
82 private LinkedList doctorColumns;
83 private LinkedList doctorColtypes;
84 private LinkedList doctorConstraints;
85 private String bugreportBackupTable;
86 private String doctorBackupTable;
87
88 public CreateDBUsersUpdate(Persistency persistency) {
89 SimpleDateFormat df = new SimpleDateFormat("y-M-d HH:mm");
90 try {
91 version = new Version("0.4.6", df.parse("2000-06-14 12:30"));
92 } catch (ParseException e) {
93 version = new Version("0.4.6", null);
94 }
95 this.persistency = persistency;
96 bugreportBackupTable = "bugreports_backup";
97 doctorBackupTable = "doctors_backup";
98 }
99
100 /**
101 * Setup before the transaction core is executed.
102 * <ul>
103 * <li> Copy the table bugreports to bugreports_backup.
104 * <li> Copy the table doctors to doctors_backup.
105 * </ul>
106 *
107 *
108 * @return result of the task
109 */
110 public TransactionResult setup() {
111 LinkedList logging = new LinkedList();
112 LinkedList warning = new LinkedList();
113 int result = TransactionResult.SUCCESS;
114
115 Statement statement = null;
116 try {
117 connection = persistency.getConnection();
118 statement = connection.createStatement();
119
120 // first we insure, that the backup table for bugreports
121 // does not exist.
122 if (persistency.getSqlServer().tableExists(connection, bugreportBackupTable)) {
123 statement.executeUpdate("drop table " + bugreportBackupTable);
124 logging.add("table " + bugreportBackupTable + " exists ... dropped.");
125 }
126
127 // create bugreports_backup
128 bugreportColumns = new LinkedList();
129 bugreportColumns.add("id");
130 bugreportColumns.add("project");
131 bugreportColumns.add("groupid");
132 bugreportColumns.add("title");
133 bugreportColumns.add("description");
134 bugreportColumns.add("packageName");
135 bugreportColumns.add("errorLevel");
136 bugreportColumns.add("dateReported");
137 bugreportColumns.add("dateStarted");
138 bugreportColumns.add("dateFixed");
139 bugreportColumns.add("dateRejected");
140 bugreportColumns.add("emailReporter");
141 bugreportColumns.add("emailDoctor");
142
143 bugreportColtypes = new LinkedList();
144 bugreportColtypes.add("integer " +
145 persistency.getSqlServer().sqlTranslate(DBServer.AUTO_INCREMENT)
146 + " not null");
147 bugreportColtypes.add(persistency.getSqlServer().sqlTranslate("varchar(50)"));
148 bugreportColtypes.add(persistency.getSqlServer().sqlTranslate("varchar(20)"));
149 bugreportColtypes.add(persistency.getSqlServer().sqlTranslate("varchar(100)"));
150 bugreportColtypes.add(persistency.getSqlServer().sqlTranslate(DBServer.BLOB));
151 bugreportColtypes.add(persistency.getSqlServer().sqlTranslate("varchar(50)"));
152 bugreportColtypes.add("integer");
153 bugreportColtypes.add("datetime");
154 bugreportColtypes.add("datetime");
155 bugreportColtypes.add("datetime");
156 bugreportColtypes.add("datetime");
157 bugreportColtypes.add(persistency.getSqlServer().sqlTranslate("varchar(100)"));
158 bugreportColtypes.add(persistency.getSqlServer().sqlTranslate("varchar(100)"));
159
160 persistency.createTable(bugreportBackupTable,
161 bugreportColumns, bugreportColtypes, null);
162 logging.add("table " + bugreportBackupTable + " created.");
163 // copy table bugreports
164 statement.executeUpdate("insert into " + bugreportBackupTable +
165 " select * from bugreports");
166 logging.add("table bugreports copied to " + bugreportBackupTable);
167
168 // create the doctors_backup table
169 doctorColumns = new LinkedList();
170 doctorColumns.add("project");
171 doctorColumns.add("doctor");
172
173 doctorColtypes = new LinkedList();
174 doctorColtypes.add("varchar");
175 doctorColtypes.add("varchar");
176
177 doctorConstraints = new LinkedList();
178 doctorConstraints.add("constraint c primary key (project, doctor)");
179
180 persistency.createTable(doctorBackupTable,
181 doctorColumns, doctorColtypes, null);
182
183 // copy table doctors
184 statement.executeUpdate("insert into " + doctorBackupTable +
185 " select * from doctors");
186 logging.add("table doctors copied to " + doctorBackupTable);
187 } catch (SQLException e) {
188 e.printStackTrace();
189 warning.add("setup failed. Reason: " + e.getMessage());
190 result = TransactionResult.FAILURE;
191 } finally {
192 try {
193 if (statement != null) statement.close();
194 } catch (SQLException e) {
195 e.printStackTrace();
196 warning.add("statement.close() failed. Reason: "
197 + e.getMessage());
198 result = TransactionResult.FAILURE;
199 }
200 }
201 return(new TransactionResult(result, logging, warning));
202 }
203
204 /**
205 * Core execution of the transaction.
206 * <ul>
207 * <li> drop table 'bugreports'
208 * <li> create it new with columns 'emailReporter' and
209 * 'emailDoctor' renamed to 'reporter' and 'doctor'
210 * <li> copy entries from 'bugreports_backup' to 'bugreports'.
211 * <li> replace email addresses of reporter and doctor byte
212 * their userid
213 * <li> create database users for each user entry whith
214 * password equals to the userid.
215 * </ul>
216 *
217 * @return result of the task
218 */
219 public TransactionResult execute() {
220 LinkedList logging = new LinkedList();
221 LinkedList warning = new LinkedList();
222 int result = TransactionResult.SUCCESS;
223
224 Statement statement = null;
225 try {
226 statement = connection.createStatement();
227
228 // drop table 'bugreports'
229 statement.executeUpdate("drop table bugreports");
230 logging.add("table bugreports dropped.");
231
232 // create it new with renamed column names
233 LinkedList newBugreportColumns = new LinkedList(bugreportColumns);
234 LinkedList newBugreportColtypes = new LinkedList(bugreportColtypes);
235
236 newBugreportColumns.set(bugreportColumns.indexOf("emailReporter"), "reporter");
237 newBugreportColumns.set(bugreportColumns.indexOf("emailDoctor"), "doctor");
238
239 persistency.createTable("BUGREPORTS",
240 newBugreportColumns, newBugreportColtypes,
241 null);
242 logging.add("table bugreports created with columns 'emailReporter' and 'emailDoctor' renamed to 'reporter' and 'doctor'.");
243
244 // copy 'bugreports_backup' to 'bugreports'
245 String theColumns = join(", ", bugreportColumns);
246 String theNewColumns = join(", ", newBugreportColumns);
247 String sqlString = "insert into bugreports (" + theNewColumns + ")";
248 sqlString += " select " + theColumns + " from " + bugreportBackupTable;
249 statement.executeUpdate(sqlString);
250 logging.add("table 'bugreports_backup' copied back to 'bugreports'.");
251
252 // create users. We ignore the exceptions
253 User user = null;
254 LinkedList userData = persistency.getUserData();
255 Iterator it = userData.iterator();
256
257 while (it.hasNext()) {
258 user = (User) it.next();
259 statement.executeUpdate("update bugreports set reporter = '" +
260 user.getUserid() + "' where reporter = '" +
261 user.getEmail() + "'");
262 statement.executeUpdate("update bugreports set doctor = '" +
263 user.getUserid() + "' where doctor = '" +
264 user.getEmail() + "'");
265 statement.executeUpdate("update doctors set doctor = '" +
266 user.getUserid() + "' where doctor = '" +
267 user.getEmail() + "'");
268
269 try {
270 statement.executeUpdate("create user '" + user +
271 "' password '" + user + "'");
272 } catch (SQLException e) {
273 e.printStackTrace();
274 warning.add("create user '" + user + "' password '" +
275 user + "' failed. Reason: " + e.getMessage());
276 }
277 logging.add("bugreports table: reporter email address replaced by userid.");
278 logging.add("bugreports table: doctor email address replaced by userid.");
279 logging.add("doctors table: mail address replaced by userid.");
280 logging.add("users created with their userid as password.");
281 }
282
283
284
285 } catch (SQLException e) {
286 e.printStackTrace();
287 warning.add(" failed. Reason: " + e.getMessage());
288 result = TransactionResult.FAILURE;
289 } finally {
290 try {
291 if (statement != null) statement.close();
292 } catch (SQLException e) {
293 e.printStackTrace();
294 warning.add("statement.close() failed. Reason: "
295 + e.getMessage());
296 result = TransactionResult.FAILURE;
297 }
298 }
299 return(new TransactionResult(result, logging, warning));
300 }
301
302 /**
303 * This method will be executed after a successful execution of
304 * {@link #execute}.
305 * <ul>
306 * <li> drop table 'bugreports_backup'
307 * </ul>
308 *
309 * @return result of the task
310 */
311 public TransactionResult cleanup() {
312 LinkedList logging = new LinkedList();
313 LinkedList warning = new LinkedList();
314 int result = TransactionResult.SUCCESS;
315
316 Statement statement = null;
317 try {
318 statement = connection.createStatement();
319 statement.executeUpdate("drop table " + bugreportBackupTable);
320 logging.add("table " + bugreportBackupTable + " dropped.");
321 statement.executeUpdate("drop table " + doctorBackupTable);
322 logging.add("table " + doctorBackupTable + " dropped.");
323
324 } catch (SQLException e) {
325 e.printStackTrace();
326 warning.add("cleanup failed. Reason: " + e.getMessage());
327 result = TransactionResult.FAILURE;
328 } finally {
329 try {
330 if (statement != null) statement.close();
331 } catch (SQLException e) {
332 e.printStackTrace();
333 warning.add("statement.close() failed. Reason: "
334 + e.getMessage());
335 result = TransactionResult.FAILURE;
336 }
337 }
338
339 persistency.setPersistencyVersion(version);
340
341 return(new TransactionResult(result, logging, warning));
342 }
343
344 /**
345 * This method will be executed after a unsuccessful execution of
346 * {@link #execute}.
347 * <ul>
348 * <li> copy table 'bugreports_backup' back to table 'bugreports'
349 * <li> drop table 'bugreports_backup'
350 * </ul>
351 *
352 * @return result of the task
353 */
354 public TransactionResult rollback() {
355 LinkedList logging = new LinkedList();
356 LinkedList warning = new LinkedList();
357 int result = TransactionResult.SUCCESS;
358
359 Statement statement = null;
360 try {
361 statement = connection.createStatement();
362
363 if (persistency.getSqlServer().tableExists(connection, bugreportBackupTable)) {
364 statement.executeUpdate("drop table bugreports");
365 logging.add("table bugreports dropped.");
366
367 persistency.createTable("BUGREPORTS",
368 bugreportColumns, bugreportColtypes, null);
369 logging.add("table bugreports created with old structure.");
370
371 statement.executeUpdate("insert into bugreports select * from "
372 + bugreportBackupTable);
373 logging.add("table " + bugreportBackupTable + " copied to 'bugreports'");
374
375 statement.executeUpdate("drop table " + bugreportBackupTable);
376 logging.add("table " + bugreportBackupTable + " dropped.");
377 }
378
379 if (persistency.getSqlServer().tableExists(connection,
380 doctorBackupTable)) {
381 statement.executeUpdate("drop table doctors");
382 logging.add("table doctors dropped.");
383
384 persistency.createTable("DOCTORS",
385 doctorColumns, doctorColtypes,
386 doctorConstraints);
387 logging.add("table doctors created with old structure.");
388
389 statement.executeUpdate("insert into doctors select * from "
390 + doctorBackupTable);
391 logging.add("table " + doctorBackupTable + " copied to 'doctors'");
392
393 statement.executeUpdate("drop table " + doctorBackupTable);
394 logging.add("table " + doctorBackupTable + " dropped.");
395 }
396
397 } catch (SQLException e) {
398 e.printStackTrace();
399 warning.add("rollback failed. Reason: " + e.getMessage());
400 result = TransactionResult.FAILURE;
401 } finally {
402 try {
403 if (statement != null) statement.close();
404 } catch (SQLException e) {
405 e.printStackTrace();
406 warning.add("statement.close() failed. Reason: "
407 + e.getMessage());
408 result = TransactionResult.FAILURE;
409 }
410 }
411 return(new TransactionResult(result, logging, warning));
412 }
413
414 /**
415 *
416 * @return <description>
417 */
418 public String getDescription() {
419 return "Create all existing users as database users and replace the email address in the bug reports by the user IDs.";
420 }
421
422 /**
423 * No predecessor available.
424 * @return null
425 */
426 public final VersionUpdate predecessor() {
427 return null;
428 }
429
430 /**
431 * Persistency version after this update.
432 * @return version 0.4.6
433 */
434 public final Version getVersion() {
435 return this.version;
436 }
437
438
439 private String join (String separator, LinkedList strings) {
440
441 Iterator it = strings.iterator();
442
443 String result = "";
444 while (it.hasNext()) {
445 result += it.next().toString();
446 if (it.hasNext()) {
447 result += separator;
448 }
449 }
450 return result;
451 }
452 } // CreateDBUsersUpdate