Java > Open Source Codes > org > opensubsystems > core > persist > db > driver > DatabaseTestSchema


1 /*
2  * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenSubsystems
5  *
6  * $Id: DatabaseTestSchema.java,v 1.23 2007/01/10 05:16:59 bastafidli Exp $
7  *
8  * This program is free software; you can redistribute it and/or modify
9  * it under the terms of the GNU General Public License as published by
10  * the Free Software Foundation; version 2 of the License.
11  *
12  * This program is distributed in the hope that it will be useful,
13  * but WITHOUT ANY WARRANTY; without even the implied warranty of
14  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15  * GNU General Public License for more details.
16  *
17  * You should have received a copy of the GNU General Public License
18  * along with this program; if not, write to the Free Software
19  * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
20  */
21
22 package org.opensubsystems.core.persist.db.driver;
23
24 import java.sql.Connection ;
25 import java.sql.SQLException ;
26 import java.sql.Statement ;
27 import java.util.HashMap ;
28 import java.util.Map ;
29 import java.util.logging.Level ;
30 import java.util.logging.Logger ;
31
32 import org.opensubsystems.core.error.OSSDatabaseAccessException;
33 import org.opensubsystems.core.error.OSSException;
34 import org.opensubsystems.core.persist.db.DatabaseConnectionFactory;
35 import org.opensubsystems.core.persist.db.ModifiableDatabaseSchemaImpl;
36 import org.opensubsystems.core.util.DatabaseUtils;
37 import org.opensubsystems.core.util.Log;
38
39 /**
40  * This class encapsulates details about creation and upgrade
41  * of database schema required to test database driver functionality.
42  *
43  * This database schema already contains some database specific tables and
44  * thats why it was declared abstract.
45  *
46  * @version $Id: DatabaseTestSchema.java,v 1.23 2007/01/10 05:16:59 bastafidli Exp $
47  * @author Miro Halas
48  * @code.reviewer Miro Halas
49  * @code.reviewed Initial revision
50  */
51 public abstract class DatabaseTestSchema extends ModifiableDatabaseSchemaImpl
52 {
53    /*
54       CREATE TABLE RESULTSET_TEST
55       (
56          RESULTSET_TEST VARCHAR(20) NOT NULL
57       )
58       
59       CREATE TABLE DATE_TEST
60       (
61          DATE_TEST DATE NOT NULL
62       )
63       
64       CREATE TABLE TIME_TEST
65       (
66          TIME_TEST TIME NOT NULL
67       )
68       
69       CREATE TABLE TIMESTAMP_TEST
70       (
71          TIMESTAMP_TEST TIMESTAMP NOT NULL
72       )
73
74       CREATE TABLE TRANSACTION_TEST
75       (
76          TEST_ID INTEGER NOT NULL,
77          TEST_VALUE VARCHAR(50) NOT NULL,
78          CONSTRAINT TEST_ID_PK PRIMARY KEY (TEST_ID)
79       )
80
81       CREATE TABLE TRANSACTION_RELATED_TEST
82       (
83          TEST_REL_ID INTEGER NOT NULL,
84          TEST_ID INTEGER NOT NULL,
85          CONSTRAINT TEST_ID_FK FOREIGN KEY (TEST_ID)
86          REFERENCES TRANSACTION_TEST (TEST_ID) ON DELETE CASCADE
87       )
88       
89       CREATE TABLE DELETE_TEST
90       (
91          TEST_ID INTEGER NOT NULL,
92          TEST_VALUE VARCHAR(50) NOT NULL,
93          CONSTRAINT DTEST_ID_PK PRIMARY KEY (TEST_ID)
94       )
95
96       CREATE TABLE DELETE_RELATED_TEST
97       (
98          TEST_REL_ID INTEGER NOT NULL,
99          TEST_ID INTEGER NOT NULL,
100          TEST_VALUE VARCHAR(50),
101          CONSTRAINT DTEST_ID_FK FOREIGN KEY (TEST_ID)
102          REFERENCES DELETE_TEST (TEST_ID) ON DELETE CASCADE
103       )
104
105       CREATE TABLE ROLLBACK_TEST
106       (
107          TEST_COLUMN VARCHAR(50) NOT NULL,
108          CONSTRAINT TEST_COLUMN_UQ UNIQUE (TEST_COLUMN)
109       )
110
111       CREATE TABLE OWN_FK_TEST
112       (
113          TEST_ID INTEGER NOT NULL,
114          FK_ID INTEGER,
115          CONSTRAINT OWN_FK_TEST_PK PRIMARY KEY (TEST_ID),
116          CONSTRAINT OWN_FK_TEST_FK FOREIGN KEY (FK_ID)
117          REFERENCES OWN_FK_TEST (TEST_ID) ON DELETE CASCADE
118       )
119
120       CREATE TABLE GROUP_BASE_TEST
121       (
122          TEST_BASE_ID INTEGER NOT NULL,
123          TEST_BASE_VALUE INTEGER,
124          CONSTRAINT GROUP_BASE_TEST_PK PRIMARY KEY (TEST_BASE_ID)
125       )
126
127       CREATE TABLE GROUP_CHILD_TEST
128       (
129          TEST_CHILD_ID INTEGER NOT NULL,
130          TEST_BASE_FK_ID INTEGER NOT NULL,
131          TEST_CHILD_VALUE INTEGER,
132          CONSTRAINT GROUP_CHILD_TEST_PK PRIMARY KEY (TEST_CHILD_ID),
133          CONSTRAINT TEST_BASE_ID_FK FOREIGN KEY (TEST_BASE_FK_ID)
134          REFERENCES GROUP_BASE_TEST (TEST_BASE_ID) ON DELETE CASCADE
135       )
136
137       CREATE TABLE SAME_TEST1
138       (
139          ID INTEGER NOT NULL
140       )
141
142       CREATE TABLE SAME_TEST2
143       (
144          ID INTEGER NOT NULL
145       )
146
147       CREATE TABLE POOL_TEST
148       (
149          TEST_VALUE INTEGER NOT NULL,
150          CONSTRAINT TEST_VALUE_PK PRIMARY KEY (TEST_VALUE)
151       )
152       
153       CREATE TABLE POOL_TEST2
154       (
155          TEST_VALUE INTEGER NOT NULL,
156          CONSTRAINT TEST_VALUE_PK PRIMARY KEY (TEST_VALUE)
157       )
158
159       CREATE TABLE QUERY_TEST
160       (
161          VALUE_1 INTEGER NOT NULL,
162          VALUE_2 INTEGER NOT NULL
163       )
164
165       CREATE table QUERY_TEST_EXCEPT (
166          VALUE_1 INTEGER NOT NULL
167       )
168       
169       CREATE TABLE UNIQUE_COLUMN_TEST (
170          TEST_ID INTEGER,
171          CONSTRAINT TEST_ID_UQ UNIQUE (TEST_ID)
172       )
173
174       CREATE TABLE NULL_COLUMN_TEST (
175          NAME VARCHAR(50)
176       )
177
178     */
179    
180    // Constants ////////////////////////////////////////////////////////////////
181
182    /**
183     * Name identifies this schema in the database.
184     */
185    public static final String DBTEST_SCHEMA_NAME = "DBTEST";
186    
187    /**
188     * Version of this schema in the database.
189     */
190    public static final int DBTEST_SCHEMA_VERSION = 1;
191
192    /**
193     * List of table names belonging to this schema.
194     */
195    public static final Map TABLE_NAMES;
196
197    // Cached variables /////////////////////////////////////////////////////////
198
199    /**
200     * Logger for this class
201     */
202    private static Logger s_logger = Log.getInstance(DatabaseTestSchema.class);
203
204    // Constructors /////////////////////////////////////////////////////////////
205
206    /**
207     * Static initializer
208     */
209    static
210    {
211       // Create map that stores table names. The key is object data type
212 // and value is table name
213 TABLE_NAMES = new HashMap ();
214       TABLE_NAMES.put(new Integer (1), "RESULTSET_TEST");
215       TABLE_NAMES.put(new Integer (2), "DATE_TEST");
216       TABLE_NAMES.put(new Integer (3), "TIME_TEST");
217       TABLE_NAMES.put(new Integer (4), "TIMESTAMP_TEST");
218       TABLE_NAMES.put(new Integer (5), "TRANSACTION_TEST");
219       TABLE_NAMES.put(new Integer (6), "TRANSACTION_RELATED_TEST");
220       TABLE_NAMES.put(new Integer (7), "DELETE_TEST");
221       TABLE_NAMES.put(new Integer (8), "DELETE_RELATED_TEST");
222       TABLE_NAMES.put(new Integer (9), "OWN_FK_TEST");
223       TABLE_NAMES.put(new Integer (10), "GROUP_BASE_TEST");
224       TABLE_NAMES.put(new Integer (11), "GROUP_CHILD_TEST");
225       TABLE_NAMES.put(new Integer (12), "SAME_TEST1");
226       TABLE_NAMES.put(new Integer (13), "SAME_TEST2");
227       TABLE_NAMES.put(new Integer (14), "POOL_TEST");
228       TABLE_NAMES.put(new Integer (15), "POOL_TEST2");
229       TABLE_NAMES.put(new Integer (16), "QUERY_TEST");
230       TABLE_NAMES.put(new Integer (17), "QUERY_TEST_EXCEPT");
231       TABLE_NAMES.put(new Integer (18), "UNIQUE_COLUMN_TEST");
232       TABLE_NAMES.put(new Integer (19), "NULL_COLUMN_TEST");
233       TABLE_NAMES.put(new Integer (20), "ROLLBACK_TEST");
234    }
235
236    /**
237     * Default constructor.
238     *
239     * @throws OSSException - error occured.
240     */
241    public DatabaseTestSchema(
242    ) throws OSSException
243    {
244       super(null, DBTEST_SCHEMA_NAME, DBTEST_SCHEMA_VERSION, true, TABLE_NAMES);
245    }
246
247    // Lifecycle events /////////////////////////////////////////////////////////
248
249    /**
250     * {@inheritDoc}
251     */
252    public void create(
253       Connection cntDBConnection,
254       String strUserName
255    ) throws SQLException
256    {
257       Statement stmQuery = null;
258       try
259       {
260          stmQuery = cntDBConnection.createStatement();
261          if (stmQuery.execute("CREATE TABLE RESULTSET_TEST" + NL +
262                               "(" + NL +
263                               " RESULTSET_TEST VARCHAR(20) NOT NULL" + NL +
264                               ")"))
265          {
266             // Close any results
267 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
268          }
269          s_logger.log(Level.FINEST, "Table RESULTSET_TEST created.");
270          /*
271          if (stmQuery.execute("grant all on RESULTSET_TEST to " + strUserName))
272          {
273             // Close any results
274             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
275          }
276          Log.getLogger().log(Level.FINEST,
277                              "Access for table RESULTSET_TEST set for user "
278                              + strUserName);
279          */
280
281          ///////////////////////////////////////////////////////////////////////
282
283          if (stmQuery.execute("CREATE TABLE DATE_TEST" + NL +
284                               "(" + NL +
285                               " DATE_TEST DATE NOT NULL" + NL +
286                               ")"))
287          {
288             // Close any results
289 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
290          }
291          s_logger.log(Level.FINEST, "Table DATE_TEST created.");
292          /*
293          if (stmQuery.execute("grant all on DATE_TEST to " + strUserName))
294          {
295             // Close any results
296             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
297          }
298          Log.getLogger().log(Level.FINEST,
299                              "Access for table DATE_TEST set for user "
300                              + strUserName);
301          */
302
303          ///////////////////////////////////////////////////////////////////////
304
305          if (stmQuery.execute("CREATE TABLE TIME_TEST" + NL +
306                               "(" + NL +
307                               " TIME_TEST TIME NOT NULL" + NL +
308                               ")"))
309          {
310             // Close any results
311 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
312          }
313          s_logger.log(Level.FINEST, "Table TIME_TEST created.");
314          /*
315          if (stmQuery.execute("grant all on TIME_TEST to " + strUserName))
316          {
317             // Close any results
318             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
319          }
320          Log.getLogger().log(Level.FINEST,
321                              "Access for table TIME_TEST set for user "
322                              + strUserName);
323          */
324
325          ///////////////////////////////////////////////////////////////////////
326
327          if (stmQuery.execute("CREATE TABLE TIMESTAMP_TEST" + NL +
328                               "(" + NL +
329                               " TIMESTAMP_TEST TIMESTAMP NOT NULL" + NL +
330                               ")"))
331          {
332             // Close any results
333 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
334          }
335          s_logger.log(Level.FINEST, "Table TIMESTAMP_TEST created.");
336          /*
337          if (stmQuery.execute("grant all on TIMESTAMP_TEST to " + strUserName))
338          {
339             // Close any results
340             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
341          }
342          Log.getLogger().log(Level.FINEST,
343                              "Access for table TIMESTAMP_TEST set for user "
344                              + strUserName);
345          */
346          ///////////////////////////////////////////////////////////////////////
347 if (stmQuery.execute("CREATE TABLE TRANSACTION_TEST" + NL +
348                               "(" + NL +
349                               " TEST_ID INTEGER NOT NULL," + NL +
350                               " TEST_VALUE VARCHAR(50) NOT NULL," + NL +
351                               " CONSTRAINT TEST_ID_PK PRIMARY KEY (TEST_ID)" + NL +
352                               ")"))
353          {
354             // Close any results
355 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
356          }
357          s_logger.log(Level.FINEST, "Table TRANSACTION_TEST created.");
358          /*
359          if (stmQuery.execute("grant all on TRANSACTION_TEST to " + strUserName))
360          {
361             // Close any results
362             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
363          }
364          Log.getLogger().log(Level.FINEST,
365                              "Access for table TRANSACTION_TEST set for user "
366                              + strUserName);
367          */
368
369          ///////////////////////////////////////////////////////////////////////
370 if (stmQuery.execute("CREATE TABLE TRANSACTION_RELATED_TEST" + NL +
371                               "(" + NL +
372                               " TEST_REL_ID INTEGER NOT NULL," + NL +
373                               " TEST_ID INTEGER NOT NULL," + NL +
374                               " TEST_VALUE VARCHAR(50)," + NL +
375                               " CONSTRAINT TEST_ID_FK FOREIGN KEY (TEST_ID)" + NL +
376                               " REFERENCES TRANSACTION_TEST (TEST_ID) ON DELETE CASCADE" + NL +
377                               ")"))
378          {
379             // Close any results
380 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
381          }
382          s_logger.log(Level.FINEST, "Table TRANSACTION_RELATED_TEST created.");
383          /*
384          if (stmQuery.execute("grant all on TRANSACTION_RELATED_TEST to " + strUserName))
385          {
386             // Close any results
387             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
388          }
389          Log.getLogger().log(Level.FINEST,
390                              "Access for table TRANSACTION_RELATED_TEST set for user "
391                              + strUserName);
392          */
393          
394          ///////////////////////////////////////////////////////////////////////
395 if (stmQuery.execute("CREATE TABLE DELETE_TEST" + NL +
396                               "(" +
397                               " TEST_ID INTEGER NOT NULL," + NL +
398                               " TEST_VALUE VARCHAR(50) NOT NULL," + NL +
399                               " CONSTRAINT DTEST_ID_PK PRIMARY KEY (TEST_ID)" + NL +
400                               ")"))
401          {
402             // Close any results
403 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
404          }
405          s_logger.log(Level.FINEST, "Table DELETE_TEST created.");
406          /*
407          if (stmQuery.execute("grant all on DELETE_TEST to " + strUserName))
408          {
409             // Close any results
410             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
411          }
412          Log.getLogger().log(Level.FINEST,
413                              "Access for table DELETE_TEST set for user "
414                              + strUserName);
415          */
416          ///////////////////////////////////////////////////////////////////////
417 if (stmQuery.execute("CREATE TABLE DELETE_RELATED_TEST" + NL +
418                               "(" + NL +
419                               " TEST_REL_ID INTEGER NOT NULL," + NL +
420                               " TEST_ID INTEGER NOT NULL," + NL +
421                               " TEST_VALUE VARCHAR(50)," + NL +
422                               " CONSTRAINT DTEST_ID_FK FOREIGN KEY (TEST_ID)" + NL +
423                               " REFERENCES DELETE_TEST (TEST_ID) ON DELETE CASCADE" + NL +
424                               ")"))
425          {
426             // Close any results
427 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
428          }
429          s_logger.log(Level.FINEST, "Table DELETE_RELATED_TEST created.");
430          /*
431          if (stmQuery.execute("grant all on DELETE_RELATED_TEST to " + strUserName))
432          {
433             // Close any results
434             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
435          }
436          Log.getLogger().log(Level.FINEST,
437                              "Access for table DELETE_RELATED_TEST set for user "
438                              + strUserName);
439          */
440          ///////////////////////////////////////////////////////////////////////
441
442          if (stmQuery.execute("CREATE TABLE ROLLBACK_TEST" + NL +
443                               "(" + NL +
444                               " TEST_COLUMN VARCHAR(50) NOT NULL," + NL +
445                               " CONSTRAINT TEST_COLUMN_UQ UNIQUE (TEST_COLUMN)" + NL +
446                               ")"))
447          {
448             // Close any results
449 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
450          }
451          s_logger.log(Level.FINEST, "Table TRANSACTION_TEST created.");
452          /*
453          if (stmQuery.execute("grant all on TRANSACTION_TEST to " + strUserName))
454          {
455             // Close any results
456             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
457          }
458          Log.getLogger().log(Level.FINEST,
459                              "Access for table TRANSACTION_TEST set for user "
460                              + strUserName);
461          */
462          ///////////////////////////////////////////////////////////////////////
463 if (stmQuery.execute("CREATE TABLE OWN_FK_TEST " + NL +
464                               "( " + NL +
465                               " TEST_ID INTEGER NOT NULL, " + NL +
466                               " FK_ID INTEGER," + NL +
467                               " CONSTRAINT OWN_FK_TEST_PK PRIMARY KEY (TEST_ID), " + NL +
468                               " CONSTRAINT OWN_FK_TEST_FK FOREIGN KEY (FK_ID) " + NL +
469                               " REFERENCES OWN_FK_TEST (TEST_ID) ON DELETE CASCADE" + NL +
470                               ")"))
471          {
472             // Close any results
473 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
474          }
475          s_logger.log(Level.FINEST, "Table OWN_FK_TEST created.");
476          /*
477          if (stmQuery.execute("grant all on TRANSACTION_TEST to " + strUserName))
478          {
479             // Close any results
480             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
481          }
482          Log.getLogger().log(Level.FINEST,
483                              "Access for table TRANSACTION_TEST set for user "
484                              + strUserName);
485          */
486          
487          ///////////////////////////////////////////////////////////////////////
488 if (stmQuery.execute("CREATE TABLE GROUP_BASE_TEST" + NL +
489                               "(" + NL +
490                               " TEST_BASE_ID INTEGER NOT NULL, " + NL +
491                               " TEST_BASE_VALUE INTEGER," + NL +
492                               " CONSTRAINT GROUP_BASE_TEST_PK PRIMARY KEY (TEST_BASE_ID)" + NL +
493                               ")"))
494          {
495             // Close any results
496 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
497          }
498          s_logger.log(Level.FINEST, "Table GROUP_BASE_TEST created.");
499          /*
500          if (stmQuery.execute("grant all on GROUP_BASE_TEST to " + strUserName))
501          {
502             // Close any results
503             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
504          }
505          Log.getLogger().log(Level.FINEST,
506                              "Access for table GROUP_BASE_TEST set for user "
507                              + strUserName);
508          */
509          ///////////////////////////////////////////////////////////////////////
510 if (stmQuery.execute(
511                 "CREATE TABLE GROUP_CHILD_TEST" + NL +
512                 "(" + NL +
513                 " TEST_CHILD_ID INTEGER NOT NULL, " + NL +
514                 " TEST_BASE_FK_ID INTEGER NOT NULL, " + NL +
515                 " TEST_CHILD_VALUE INTEGER," + NL +
516                 " CONSTRAINT GROUP_CHILD_TEST_PK PRIMARY KEY (TEST_CHILD_ID)," + NL +
517                 " CONSTRAINT TEST_BASE_ID_FK FOREIGN KEY (TEST_BASE_FK_ID) " + NL +
518                 " REFERENCES GROUP_BASE_TEST (TEST_BASE_ID) ON DELETE CASCADE" + NL +
519                 ")"))
520          {
521             // Close any results
522 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
523          }
524          s_logger.log(Level.FINEST, "Table GROUP_CHILD_TEST created.");
525          /*
526          if (stmQuery.execute("grant all on GROUP_CHILD_TEST to " + strUserName))
527          {
528             // Close any results
529             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
530          }
531          Log.getLogger().log(Level.FINEST,
532                              "Access for table GROUP_CHILD_TEST set for user "
533                              + strUserName);
534          */
535          ///////////////////////////////////////////////////////////////////////
536 if (stmQuery.execute("CREATE TABLE SAME_TEST1" + NL +
537                               "(" + NL +
538                               " ID INTEGER NOT NULL" + NL +
539                               ")"))
540          {
541             // Close any results
542 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
543          }
544          s_logger.log(Level.FINEST, "Table SAME_TEST1 created.");
545          /*
546          if (stmQuery.execute("grant all on SAME_TEST1 to " + strUserName))
547          {
548             // Close any results
549             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
550          }
551          Log.getLogger().log(Level.FINEST,
552                              "Access for table SAME_TEST1 set for user "
553                              + strUserName);
554          */
555          ///////////////////////////////////////////////////////////////////////
556 if (stmQuery.execute("CREATE TABLE SAME_TEST2 " + NL +
557                               "(" + NL +
558                               " ID INTEGER NOT NULL" + NL +
559                               ")"))
560          {
561             // Close any results
562 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
563          }
564          s_logger.log(Level.FINEST, "Table SAME_TEST2 created.");
565          /*
566          if (stmQuery.execute("grant all on SAME_TEST2 to " + strUserName))
567          {
568             // Close any results
569             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
570          }
571          Log.getLogger().log(Level.FINEST,
572                              "Access for table SAME_TEST2 set for user "
573                              + strUserName);
574          */
575          ///////////////////////////////////////////////////////////////////////
576 if (stmQuery.execute("CREATE TABLE POOL_TEST " + NL +
577                               "(" + NL +
578                               " TEST_VALUE INTEGER NOT NULL," + NL +
579                               " CONSTRAINT TEST_VALUE1_PK PRIMARY KEY (TEST_VALUE)" + NL +
580                               ")"))
581          {
582             // Close any results
583 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
584          }
585          s_logger.log(Level.FINEST, "Table POOL_TEST created.");
586          /*
587          if (stmQuery.execute("grant all on POOL_TEST to " + strUserName))
588          {
589             // Close any results
590             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
591          }
592          Log.getLogger().log(Level.FINEST,
593                              "Access for table POOL_TEST set for user "
594                              + strUserName);
595          */
596          ///////////////////////////////////////////////////////////////////////
597 if (stmQuery.execute("CREATE TABLE POOL_TEST2 " + NL +
598                               "(" + NL +
599                               " TEST_VALUE INTEGER NOT NULL," + NL +
600                               " CONSTRAINT TEST_VALUE2_PK PRIMARY KEY (TEST_VALUE)" + NL +
601                               ")"))
602          {
603             // Close any results
604 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
605          }
606          s_logger.log(Level.FINEST, "Table POOL_TEST2 created.");
607          /*
608          if (stmQuery.execute("grant all on POOL_TEST2 to " + strUserName))
609          {
610             // Close any results
611             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
612          }
613          Log.getLogger().log(Level.FINEST,
614                              "Access for table POOL_TEST set for user "
615                              + strUserName);
616          */
617          
618          ///////////////////////////////////////////////////////////////////////
619 if (stmQuery.execute("CREATE TABLE QUERY_TEST " + NL +
620                               "(" + NL +
621                               " VALUE_1 INTEGER NOT NULL," + NL +
622                               " VALUE_2 INTEGER NOT NULL" + NL +
623                               ")"))
624          {
625             // Close any results
626 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
627          }
628          s_logger.log(Level.FINEST, "Table QUERY_TEST created.");
629          /*
630          if (stmQuery.execute("grant all on QUERY_TEST to " + strUserName))
631          {
632             // Close any results
633             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
634          }
635          Log.getLogger().log(Level.FINEST,
636                              "Access for table QUERY_TEST set for user "
637                              + strUserName);
638          */
639          ///////////////////////////////////////////////////////////////////////
640 if (stmQuery.execute("CREATE TABLE QUERY_TEST_EXCEPT " + NL +
641                               "(" + NL +
642                               " VALUE_1 INTEGER NOT NULL" + NL +
643                               ")"))
644          {
645             // Close any results
646 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
647          }
648          s_logger.log(Level.FINEST, "Table QUERY_TEST_EXCEPT created.");
649          /*
650          if (stmQuery.execute("grant all on QUERY_TEST to " + strUserName))
651          {
652             // Close any results
653             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
654          }
655          Log.getLogger().log(Level.FINEST,
656                              "Access for table QUERY_TEST set for user "
657                              + strUserName);
658          */
659          ///////////////////////////////////////////////////////////////////////
660 if (stmQuery.execute("CREATE TABLE UNIQUE_COLUMN_TEST " + NL +
661                               "(" + NL +
662                               " TEST_ID INTEGER, " + NL +
663                               " CONSTRAINT TEST_ID_UQ UNIQUE (TEST_ID) " + NL +
664                               ")"))
665          {
666             // Close any results
667 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
668          }
669          s_logger.log(Level.FINEST, "Table UNIQUE_COLUMN_TEST created.");
670          /*
671          if (stmQuery.execute("grant all on UNIQUE_COLUMN_TEST to " + strUserName))
672          {
673             // Close any results
674             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
675          }
676          Log.getLogger().log(Level.FINEST,
677                              "Access for table UNIQUE_COLUMN_TEST set for user "
678                              + strUserName);
679          */
680          ///////////////////////////////////////////////////////////////////////
681 if (stmQuery.execute("CREATE TABLE NULL_COLUMN_TEST " + NL +
682                               "(" + NL +
683                               " NAME VARCHAR(50)" + NL +
684                               ")"))
685          {
686             // Close any results
687 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
688          }
689          s_logger.log(Level.FINEST, "Table NULL_COLUMN_TEST created.");
690          /*
691          if (stmQuery.execute("grant all on NULL_COLUMN_TEST to " + strUserName))
692          {
693             // Close any results
694             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
695          }
696          Log.getLogger().log(Level.FINEST,
697                              "Access for table NULL_COLUMN_TEST set for user "
698                              + strUserName);
699          */
700       }
701       catch (SQLException sqleExc)
702       {
703          s_logger.log(Level.WARNING, "Failed to create database test schema.",
704                              sqleExc);
705          throw sqleExc;
706       }
707       finally
708       {
709          DatabaseUtils.closeStatement(stmQuery);
710       }
711    }
712
713    /**
714     * Returns insert-select query
715     *
716     * @return String - SQL query for insert-select .
717     */
718    public String getInsertSelectQuery()
719    {
720       return "insert into QUERY_TEST (VALUE_1, VALUE_2) " +
721              "select ?, VALUE_2 from QUERY_TEST where VALUE_2=1";
722    }
723
724    /**
725     * Returns select except query
726     *
727     * @return String - SQL query for select-except.
728     */
729    public String getSelectExceptQuery()
730    {
731       return "select value_1 from query_test_except where value_1 in (?, ?, ?) " +
732              "except select value_1 from query_test_except where value_1 in (?, ?) " +
733              "except select value_1 from query_test_except where value_1 in (?, ?)";
734    }
735
736    /**
737     * Returns select-except-union query
738     *
739     * @return String - SQL query for select-except-union.
740     */
741    public String getSelectExceptUnionQuery()
742    {
743       return "select value_1 from query_test_except where value_1 in (?, ?, ?) " +
744              "except select value_1 from query_test_except where value_1 in (?, ?) " +
745              "union select value_1 from query_test_except where value_1 in (?, ?)";
746    }
747
748    /**
749     * Returns select-exist query
750     *
751     * @return String - SQL query for select-exist.
752     */
753    public String getSelectExistQuery()
754    {
755       return "select VALUE_1 from QUERY_TEST AS QT where exists " +
756              "(select 1 from QUERY_TEST AS QT1 where QT1.VALUE_2 = QT.VALUE_1)";
757    }
758
759    /**
760     * Query to insert to table with generated keys is database specific.
761     *
762     * @return String - SQL query to insert to table with generated keys.
763     */
764    public abstract String getInsertGeneratedKey(
765    );
766
767   /**
768    * Execute another query to insert to table with generated keys
769    * which guarantees return
770    *
771    * @param dbConnection - database connection
772    * @param strValue - value to insert
773    * @return int[] - index 0 - how many records were inserted
774    * - index 1 - the value of generated key
775    * @throws SQLException - error during insert execute
776    */
777   public abstract int[] executeInsertGeneratedKey2(
778      Connection dbConnection,
779      String strValue
780   ) throws SQLException ;
781
782   /**
783    * Execute another query to insert to table. There will be test if calling of stored procedure
784    * will return number of inserted rows.
785    *
786    * @param dbConnection - database connection
787    * @param strValue - value to insert
788    * @return int[] - index 0 - how many records were inserted
789    * - index 1 - the value of inserted records retrieved from stored procedure
790    * @throws SQLException - error during insert execute
791    */
792   public abstract int[] executeInsertRow(
793      Connection dbConnection,
794      String strValue
795   ) throws SQLException ;
796
797   /**
798    * Execute query to update test value using stored procedure without parameters
799    *
800    * @param dbConnection - database connection
801    * @param strOldValue - old value to be updated
802    * @param strNewValue - new value for update
803    * @return int - number of updated records
804    * @throws SQLException - error during insert execute
805    */
806   public abstract int executeUpdateTestValue(
807      Connection dbConnection,
808      String strOldValue,
809      String strNewValue
810   ) throws SQLException ;
811
812   /**
813    * Create datasource with specified parameters.
814    *
815    * @param dbConnectionFactory - database connection factory
816    * @param strDataSourceName - name of the data source
817    * @param strDatabaseDriver - database driver
818    * @param strDatabaseURL - database URL
819    * @param strUserName - name of the user that will be created
820    * @param strUserPassword - user password
821    * @throws OSSDatabaseAccessException - error during data source creation
822    */
823   public void createDataSource(
824      DatabaseConnectionFactory dbConnectionFactory,
825      String strDataSourceName,
826      String strDatabaseDriver,
827      String strDatabaseURL,
828      String strUserName,
829      String strUserPassword
830   ) throws OSSDatabaseAccessException
831   {
832       dbConnectionFactory.addDataSource(strDataSourceName,
833                                         strDatabaseDriver,
834                                         strDatabaseURL,
835                                         strUserName,
836                                         strUserPassword);
837   }
838
839   /**
840    * Create test user in the specified database.
841    *
842    * @param cntAdminDBConnection - database connection
843    * @param strDatabaseURL - database URL
844    * @param strUserName - name of the user that will be created
845    * @param strUserPassword - user password
846    * @throws SQLException - error during user creation
847    */
848   public abstract void createTestUser(
849      Connection cntAdminDBConnection,
850      String strDatabaseURL,
851      String strUserName,
852      String strUserPassword
853   ) throws SQLException ;
854
855   /**
856    * Drop test user in the specified database.
857    *
858    * @param cntAdminDBConnection - database connection
859    * @param strDatabaseURL - database URL
860    * @param strUserName - name of the user that will be created
861    * @throws SQLException - error during user deletion
862    */
863   public abstract void dropTestUser(
864      Connection cntAdminDBConnection,
865      String strDatabaseURL,
866      String strUserName
867   ) throws SQLException ;
868 }
869

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags