|
1
64 package com.jcorporate.expresso.core.utility;
65
66 import com.jcorporate.expresso.core.ExpressoSchema;
67 import com.jcorporate.expresso.core.controller.Controller;
68 import com.jcorporate.expresso.core.dataobjects.Securable;
69 import com.jcorporate.expresso.core.db.DBConnection;
70 import com.jcorporate.expresso.core.db.DBConnectionPool;
71 import com.jcorporate.expresso.core.db.DBException;
72 import com.jcorporate.expresso.core.db.TableCreator;
73 import com.jcorporate.expresso.core.db.TypeMapper;
74 import com.jcorporate.expresso.core.dbobj.DBObject;
75 import com.jcorporate.expresso.core.dbobj.Schema;
76 import com.jcorporate.expresso.core.dbobj.SchemaFactory;
77 import com.jcorporate.expresso.core.dbobj.SecuredDBObject;
78 import com.jcorporate.expresso.core.i18n.Messages;
79 import com.jcorporate.expresso.core.job.Job;
80 import com.jcorporate.expresso.core.job.ServerException;
81 import com.jcorporate.expresso.core.misc.ConfigManager;
82 import com.jcorporate.expresso.core.misc.ConfigurationException;
83 import com.jcorporate.expresso.core.misc.DateTime;
84 import com.jcorporate.expresso.core.misc.StringUtil;
85 import com.jcorporate.expresso.core.registry.MutableRequestRegistry;
86 import com.jcorporate.expresso.core.security.SuperUser;
87 import com.jcorporate.expresso.core.security.User;
88 import com.jcorporate.expresso.core.servlet.StdServlet;
89 import com.jcorporate.expresso.kernel.InstallLog;
90 import com.jcorporate.expresso.kernel.LogManager;
91 import com.jcorporate.expresso.services.dbobj.ControllerSecurity;
92 import com.jcorporate.expresso.services.dbobj.DBObjLimit;
93 import com.jcorporate.expresso.services.dbobj.DBObjSecurity;
94 import com.jcorporate.expresso.services.dbobj.DefaultUserInfo;
95 import com.jcorporate.expresso.services.dbobj.Event;
96 import com.jcorporate.expresso.services.dbobj.GroupMembers;
97 import com.jcorporate.expresso.services.dbobj.JobSecurity;
98 import com.jcorporate.expresso.services.dbobj.RegistrationDomain;
99 import com.jcorporate.expresso.services.dbobj.RegistrationObjectMap;
100 import com.jcorporate.expresso.services.dbobj.SchemaList;
101 import com.jcorporate.expresso.services.dbobj.Setup;
102 import com.jcorporate.expresso.services.dbobj.UserGroup;
103 import com.jcorporate.expresso.services.html.HtmlException;
104 import com.jcorporate.expresso.services.html.Page;
105 import com.jcorporate.expresso.services.html.Paragraph;
106 import com.jcorporate.expresso.services.html.Text;
107 import org.apache.log4j.Logger;
108
109 import java.io.BufferedInputStream  ;
110 import java.io.BufferedOutputStream ;
111 import java.io.BufferedReader ;
112 import java.io.File ;
113 import java.io.FileInputStream ;
114 import java.io.FileOutputStream ;
115 import java.io.IOException ;
116 import java.io.InputStreamReader ;
117 import java.io.PrintStream ;
118 import java.sql.Connection ;
119 import java.sql.DatabaseMetaData ;
120 import java.sql.DriverManager ;
121 import java.sql.ResultSet ;
122 import java.sql.SQLException ;
123 import java.util.ArrayList ;
124 import java.util.Date ;
125 import java.util.Enumeration ;
126 import java.util.Hashtable ;
127 import java.util.Iterator ;
128 import java.util.List ;
129 import java.util.StringTokenizer ;
130 import java.util.Vector ;
131
132
133
172 public class DBTool
173 extends Thread {
174 private static final String thisClass = DBTool.class.getName();
175 private static String dbName = null;
176 private static Logger log = Logger.getLogger(DBTool.class);
177
178
181 public DBTool() {
182 }
183
184
189 private static void chooseConfig(String dbName)
190 throws DBException, IOException {
191 System.out.println("Choose Schema: (Database " + dbName + ")");
192
193 Hashtable choices = new Hashtable ();
194 choices.put("1",
195 "Expresso|com.jcorporate.expresso.core.ExpressoSchema");
196
197 int nextNum = 1;
198 SchemaList sl = new SchemaList();
199 sl.setDataContext(dbName);
200
201 String nextKey = null;
202
203 List list = sl.searchAndRetrieveList();
204 for (Iterator iterator = list.iterator(); iterator.hasNext();) {
205 SchemaList oneSchemaList = (SchemaList) iterator.next();
206 nextNum++;
207 nextKey = "" + nextNum;
208 choices.put(nextKey,
209 oneSchemaList.getField("Descrip") + "|" +
210 oneSchemaList.getField("SchemaClass"));
211 }
212
213
214 String oneNumber = null;
215 String oneDescrip = null;
216
217 for (Enumeration ex = choices.keys(); ex.hasMoreElements();) {
218 oneNumber = (String ) ex.nextElement();
219
220 StringTokenizer stk = new StringTokenizer ((String ) choices.get(oneNumber), "|");
221 oneDescrip = stk.nextToken();
222 System.out.println(oneNumber + ". " + oneDescrip);
223 }
224
225 System.out.println("");
226 System.out.print("Enter the number corresponding to the schema that " +
227 "you wish to enter/edit Setup values for:");
228
229 BufferedReader br = new BufferedReader (new InputStreamReader (System.in));
230 String command = StringUtil.notNull(br.readLine());
231
232 if (command.equals("")) {
233 return;
234 } else {
235 StringTokenizer stk2 = new StringTokenizer ((String ) choices.get(command), "|");
236 stk2.nextToken();
237 enterConfig(dbName, stk2.nextToken());
238 }
239 }
240
241
242
245 private static void doBenchMarks()
246 throws DBException, SQLException ,
247 ConfigurationException {
248 System.out.println("Benchmarks begin at " +
249 DateTime.getDateTimeString());
250
251 int retryCount = 5000;
252 System.out.println("This benchmark tests the performance of the " +
253 "connection pool in simple \n" +
254 " allocations of connections - it makes & releases " +
255 retryCount + " connections, 5 at a time.\n");
256 System.out.println("Begin connection pool tests:" +
257 DateTime.getDateTimeString());
258
259 DBConnectionPool myPool = DBConnectionPool.getInstance("default");
260
261 for (int i = 0; i <= retryCount; i++) {
262 DBConnection conn1 = myPool.getConnection("Test " + (i + 1));
263 DBConnection conn2 = myPool.getConnection("Test " + (i + 2));
264 DBConnection conn3 = myPool.getConnection("Test " + (i + 4));
265 DBConnection conn4 = myPool.getConnection("Test " + (i + 5));
266 DBConnection conn5 = myPool.getConnection("Test " + (i + 6));
267 myPool.release(conn1);
268 myPool.release(conn2);
269 myPool.release(conn3);
270 myPool.release(conn4);
271 myPool.release(conn5);
272 }
273
274 System.out.println("End connection pool tests:" +
275 DateTime.getDateTimeString());
276 System.out.println("Compare this to making & breaking the connection " +
277 "to the DB the same number\n" + " of times.");
278
279 String dbURL = ConfigManager.getJdbcRequired("default").getUrl();
280 String dbLogin = ConfigManager.getJdbcRequired("default").getLogin();
281 String dbPassword = ConfigManager.getJdbcRequired("default").getPassword();
282 System.out.println("Begin direct connection tests:" +
283 DateTime.getDateTimeString());
284
285 for (int i = 0; i <= retryCount; i++) {
286 Connection conn1 = DriverManager.getConnection(dbURL, dbLogin,
287 dbPassword);
288 conn1.createStatement();
289 conn1.close();
290
291 Connection conn2 = DriverManager.getConnection(dbURL, dbLogin,
292 dbPassword);
293 conn2.createStatement();
294 conn2.close();
295
296 Connection conn3 = DriverManager.getConnection(dbURL, dbLogin,
297 dbPassword);
298 conn3.createStatement();
299 conn3.close();
300
301 Connection conn4 = DriverManager.getConnection(dbURL, dbLogin,
302 dbPassword);
303 conn4.createStatement();
304 conn4.close();
305
306 Connection conn5 = DriverManager.getConnection(dbURL, dbLogin,
307 dbPassword);
308 conn5.createStatement();
309 conn5.close();
310 }
311
312 System.out.println("End direct connection tests:" +
313 DateTime.getDateTimeString());
314 System.out.println("Direct SQL tests - the above test is repeated, " +
315 "but with a series of simple \n" +
316 " SQL queries executed in each of the connections.");
317 System.out.println("Begin direct SQL tests:" +
318 DateTime.getDateTimeString());
319
320 for (int i = 0; i <= retryCount; i++) {
321 DBConnection conn1 = myPool.getConnection("Test " + (i + 1));
322 conn1.execute("SELECT * FROM USERLOGIN WHERE UserName = 'Admin'");
323
324 DBConnection conn2 = myPool.getConnection("Test " + (i + 2));
325 conn2.execute("SELECT * FROM GROUPMEMBERS WHERE UserName = 'Admin'");
326
327 DBConnection conn3 = myPool.getConnection("Test " + (i + 4));
328 conn3.execute("SELECT * FROM SETUP WHERE SetupCode = 'ServletEvent'");
329
330 DBConnection conn4 = myPool.getConnection("Test " + (i + 5));
331 conn4.execute("SELECT * FROM DBOBJSECURITY WHERE GroupName = 'Admin'");
332
333 DBConnection conn5 = myPool.getConnection("Test " + (i + 6));
334 conn5.execute("SELECT * FROM EVENT WHERE Event = 'SYSERROR'");
335 myPool.release(conn1);
336 myPool.release(conn2);
337 myPool.release(conn3);
338 myPool.release(conn4);
339 myPool.release(conn5);
340 }
341
342 System.out.println("End direct SQL tests:" +
343 DateTime.getDateTimeString());
344 System.out.println("DB Object tests - the above test is repeated, " +
345 "but with db objects \n" +
346 " doing the queries rather than direct sql");
347 System.out.println("Begin db object tests:" +
348 DateTime.getDateTimeString());
349
350 GroupMembers gm = new GroupMembers();
351 DefaultUserInfo oneUser = new DefaultUserInfo();
352 Setup s1 = new Setup();
353 DBObjSecurity dbo = new DBObjSecurity();
354 Event e = new Event();
355
356 for (int i = 0; i <= retryCount; i++) {
357 DBConnection conn1 = myPool.getConnection("Test " + (i + 1));
358 oneUser.setConnection(conn1);
359 oneUser.setField("UserName", User.ADMIN_USER);
360 oneUser.find();
361
362 DBConnection conn2 = myPool.getConnection("Test " + (i + 2));
363 gm.setConnection(conn2);
364 gm.setField("UserName", User.ADMIN_USER);
365 gm.find();
366
367 DBConnection conn3 = myPool.getConnection("Test " + (i + 4));
368 s1.setConnection(conn3);
369 s1.setField("SetupCode", "ServletEvent");
370 s1.find();
371
372 DBConnection conn4 = myPool.getConnection("Test " + (i + 5));
373 dbo.setConnection(conn4);
374 dbo.setField("GroupName", User.ADMIN_USER);
375 dbo.find();
376
377 DBConnection conn5 = myPool.getConnection("Test " + (i + 6));
378 e.setConnection(conn5);
379 e.setField("Event", "SYSERROR");
380 e.find();
381 System.out.println("Benchmarks begin at " +
382 DateTime.getDateTimeString());
383 myPool.release(conn1);
384 myPool.release(conn2);
385 myPool.release(conn3);
386 myPool.release(conn4);
387 myPool.release(conn5);
388 }
389
390 retryCount = 5000;
391 System.out.println("End dbobject tests:" +
392 DateTime.getDateTimeString());
393 System.out.println("This benchmark tests the performance of the " +
394 "connection pool in simple \n" +
395 " allocations of connections - it makes & releases " +
396 retryCount + " connections, 5 at a time.\n");
397 System.out.println("Begin connection pool tests:" +
398 DateTime.getDateTimeString());
399 myPool = DBConnectionPool.getInstance("default");
400
401 for (int i = 0; i <= retryCount; i++) {
402 DBConnection conn1 = myPool.getConnection("Test " + (i + 1));
403 DBConnection conn2 = myPool.getConnection("Test " + (i + 2));
404 DBConnection conn3 = myPool.getConnection("Test " + (i + 4));
405 DBConnection conn4 = myPool.getConnection("Test " + (i + 5));
406 DBConnection conn5 = myPool.getConnection("Test " + (i + 6));
407 myPool.release(conn1);
408 myPool.release(conn2);
409 myPool.release(conn3);
410 myPool.release(conn4);
411 myPool.release(conn5);
412 }
413
414 System.out.println("End connection pool tests:" +
415 DateTime.getDateTimeString());
416 System.out.println("Compare this to making & breaking the connection " +
417 "to the DB the same number\n" + " of times.");
418 dbURL = ConfigManager.getJdbcRequired("default").getUrl();
419 dbLogin = ConfigManager.getJdbcRequired("default").getLogin();
420 dbPassword = ConfigManager.getJdbcRequired("default").getPassword();
421 System.out.println("Begin direct connection tests:" +
422 DateTime.getDateTimeString());
423
424 for (int i = 0; i <= retryCount; i++) {
425 Connection conn1 = DriverManager.getConnection(dbURL, dbLogin,
426 dbPassword);
427 conn1.createStatement();
428 conn1.close();
429
430 Connection conn2 = DriverManager.getConnection(dbURL, dbLogin,
431 dbPassword);
432 conn2.createStatement();
433 conn2.close();
434
435 Connection conn3 = DriverManager.getConnection(dbURL, dbLogin,
436 dbPassword);
437 conn3.createStatement();
438 conn3.close();
439
440 Connection conn4 = DriverManager.getConnection(dbURL, dbLogin,
441 dbPassword);
442 conn4.createStatement();
443 conn4.close();
444
445 Connection conn5 = DriverManager.getConnection(dbURL, dbLogin,
446 dbPassword);
447 conn5.createStatement();
448 conn5.close();
449 }
450
451 System.out.println("End direct connection tests:" +
452 DateTime.getDateTimeString());
453 System.out.println("Direct SQL tests - the above test is repeated, " +
454 "but with a series of simple \n" +
455 " SQL queries executed in each of the connections.");
456 System.out.println("Begin direct SQL tests:" +
457 DateTime.getDateTimeString());
458
459 for (int i = 0; i <= retryCount; i++) {
460 DBConnection conn1 = myPool.getConnection("Test " + (i + 1));
461 conn1.execute("SELECT * FROM USERLOGIN WHERE UserName = 'Admin'");
462
463 DBConnection conn2 = myPool.getConnection("Test " + (i + 2));
464 conn2.execute("SELECT * FROM GROUPMEMBERS WHERE UserName = 'Admin'");
465
466 DBConnection conn3 = myPool.getConnection("Test " + (i + 4));
467 conn3.execute("SELECT * FROM SETUP WHERE SetupCode = 'ServletEvent'");
468
469 DBConnection conn4 = myPool.getConnection("Test " + (i + 5));
470 conn4.execute("SELECT * FROM DBOBJSECURITY WHERE GroupName = 'Admin'");
471
472 DBConnection conn5 = myPool.getConnection("Test " + (i + 6));
473 conn5.execute("SELECT * FROM EVENT WHERE Event = 'SYSERROR'");
474 myPool.release(conn1);
475 myPool.release(conn2);
476 myPool.release(conn3);
477 myPool.release(conn4);
478 myPool.release(conn5);
479 }
480
481 System.out.println("End direct SQL tests:" +
482 DateTime.getDateTimeString());
483 System.out.println("DB Object tests - the above test is repeated, " +
484 "but with db objects \n" +
485 " doing the queries rather than direct sql");
486 System.out.println("Begin db object tests:" +
487 DateTime.getDateTimeString());
488 gm = new GroupMembers();
489 oneUser = new DefaultUserInfo();
490 s1 = new Setup();
491 dbo = new DBObjSecurity();
492 e = new Event();
493
494 for (int i = 0; i <= retryCount; i++) {
495 DBConnection conn1 = myPool.getConnection("Test " + (i + 1));
496
497 oneUser.setField("UserName", User.ADMIN_USER);
501 oneUser.find();
502
503 DBConnection conn2 = myPool.getConnection("Test " + (i + 2));
504 gm.setConnection(conn2);
505 gm.setField("UserName", User.ADMIN_USER);
506 gm.find();
507
508 DBConnection conn3 = myPool.getConnection("Test " + (i + 4));
509 s1.setConnection(conn3);
510 s1.setField("SetupCode", "ServletEvent");
511 s1.find();
512
513 DBConnection conn4 = myPool.getConnection("Test " + (i + 5));
514 dbo.setConnection(conn4);
515 dbo.setField("GroupName", User.ADMIN_USER);
516 dbo.find();
517
518 DBConnection conn5 = myPool.getConnection("Test " + (i + 6));
519 e.setConnection(conn5);
520 e.setField("Event", "SYSERROR");
521 e.find();
522 myPool.release(conn1);
523 myPool.release(conn2);
524 myPool.release(conn3);
525 myPool.release(conn4);
526 myPool.release(conn5);
527 }
528
529 System.out.println("End dbobject tests:" +
530 DateTime.getDateTimeString());
531 System.out.println("Benchmarks end at " +
532 DateTime.getDateTimeString());
533 }
534
535
536
542 public static void deleteTable(String dbName, String tableName)
543 throws DBException {
544 DBConnectionPool pool = DBConnectionPool.getInstance(dbName);
545 String stmnt = "DROP TABLE " + tableName;
546 pool.executeExclusiveUpdate(stmnt);
547 }
548
549
557 public static synchronized void deleteSchema(Page myPage, String dbName,
558 Vector schemas)
559 throws DBException, HtmlException,
560 IllegalArgumentException {
561 log.info("Removing " + Integer.toString(schemas.size()) + " schemas");
562
563 for (Enumeration e = schemas.elements(); e.hasMoreElements();) {
564 String oneName = (e.nextElement()).getClass().getName();
565 deleteSchema(myPage, dbName, oneName);
566 }
567
568 log.info("Schema Deletion Complete");
569 }
570
571
587 public static synchronized void deleteSchema(Page myPage, String dbName,
588 String schemaName)
589 throws DBException, HtmlException,
590 IllegalArgumentException {
591 final String myName = (thisClass +
592 "deleteSchema(Page,String,String)");
593 boolean expressoDelete = false;
594 log.info(myName + ": removing schema: " + schemaName + " from db " +
595 dbName);
596
597 if (schemaName.equals("com.jcorporate.expresso.core.ExpressoSchema")) {
598 expressoDelete = true;
599 }
600
601 Schema oneSchema = null;
602
603 try {
604 oneSchema = (Schema) Class.forName(schemaName).newInstance();
605 } catch (Throwable e) {
606 throw new IllegalArgumentException ("Unable to load class " +
607 schemaName + " " +
608 e.getMessage());
609 }
610
611 DBObject oneObject;
612
613 try {
614
615 DBConnectionPool.reInitialize();
618 } catch (DBException e) {
619 }
620 for (Enumeration e = oneSchema.getMembers(); e.hasMoreElements();) {
621 oneObject = (DBObject) e.nextElement();
622
623 if (myPage != null) {
624 myPage.add(new Paragraph(new Text("Removing Table" + " " +
625 oneObject.getJDBCMetaData().getTargetSQLTable(oneObject.getDataContext()))));
626 }
627 try {
628 deleteTable(dbName, oneObject.getJDBCMetaData().getTargetSQLTable(oneObject.getDataContext()));
629 } catch (DBException dbe) {
630 log.warn("Unable To Delete Table: " +
631 oneObject.getJDBCMetaData().getTargetSQLTable(oneObject.getDataContext()) + " DB Message: " +
632 dbe.getDBMessage());
633 }
634 }
635 if (expressoDelete == false) {
638 try {
639
640 SchemaList schemaEntry = new SchemaList(SecuredDBObject.SYSTEM_ACCOUNT);
642 schemaEntry.setDataContext(dbName);
643 schemaEntry.setField("SchemaClass", schemaName);
644 schemaEntry.delete();
645 } catch (DBException e) {
646 log.info("Unable to delete schema entry.", e);
647 }
648
649 Setup setupList = new Setup(SecuredDBObject.SYSTEM_ACCOUNT);
651 setupList.setDataContext(dbName);
652 setupList.setField("SchemaClass", schemaName);
653
654 List list = setupList.searchAndRetrieveList();
655 for (Iterator iterator = list.iterator(); iterator.hasNext();) {
656 Setup s = (Setup) iterator.next();
657 try {
658 s.setDataContext(dbName);
659 s.delete();
660 } catch (DBException e) {
661 if (s != null) {
662 log.info("Unable to delete setup value: " +
663 s.getField("SetupCode"), e);
664 } else {
665 log.info("Unable to delete setup value: (s=null)", e);
666 }
667 }
668 }
669 }
670 if (myPage != null) {
671 myPage.add(new Paragraph(new Text("All Tables Removed")));
672 }
673 }
674
675
676
680 private static void enterConfig(String dbName, String schemaClass)
681 throws DBException, IOException {
682 System.out.println("Setup/Configuration values: (Database " + dbName +
683 ", Schema " + schemaClass + ")");
684
685 Setup setupList = new Setup();
686 setupList.setDataContext(dbName);
687
688 String command = ("");
689 setupList.setField("SchemaClass", schemaClass);
690
691 List list = setupList.searchAndRetrieveList();
692 if (list.size() == 0) {
693 System.out.println("There were no default setup values in database/context '" +
694 dbName +
695 "'. You must run initial setup to create the defaults " +
696 "first.");
697 }
698
699 for (Iterator iterator = list.iterator(); iterator.hasNext();) {
700 Setup oneSetup = (Setup) iterator.next();
701 System.out.println("---------------------------------");
702 System.out.println("Setup Code :" +
703 oneSetup.getField("SetupCode"));
704 System.out.println("Description :" +
705 oneSetup.getField("Descrip"));
706 System.out.println("Current Value:" +
707 oneSetup.getField("SetupValue"));
708 System.out.println("");
709 System.out.print("Press enter to leave current value, or enter new value:");
710
711 BufferedReader br = new BufferedReader (new InputStreamReader (System.in));
712 command = StringUtil.notNull(br.readLine());
713
714 if (!command.equals("")) {
715 System.out.println("Value for code '" +
716 oneSetup.getField("SetupCode") + "' " +
717 " now '" + command + "'");
718 oneSetup.setField("SetupValue", command);
719 oneSetup.update();
720 System.out.println("Database updated");
721 } else {
722 System.out.println("Current value retained.");
723 &nbs
|
Java API By Example, From Geeks To Geeks.