KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > opensubsystems > core > persist > db > driver > db2 > DB2DatabaseTestSchema


1 /*
2  * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenSubsystems
5  *
6  * $Id: DB2DatabaseTestSchema.java,v 1.15 2007/01/07 06:14:53 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.db2;
23
24 import java.sql.CallableStatement JavaDoc;
25 import java.sql.Connection JavaDoc;
26 import java.sql.SQLException JavaDoc;
27 import java.sql.Statement JavaDoc;
28 import java.sql.Types JavaDoc;
29 import java.util.logging.Level JavaDoc;
30 import java.util.logging.Logger JavaDoc;
31
32 import org.opensubsystems.core.error.OSSException;
33 import org.opensubsystems.core.persist.db.driver.DatabaseTestSchema;
34 import org.opensubsystems.core.util.DatabaseUtils;
35 import org.opensubsystems.core.util.Log;
36
37 /**
38  * This class encapsulates details about creation and upgrade
39  * of database schema required to test database driver functionality
40  * for tables which are IBM DB2 database specific
41  *
42  * @version $Id: DB2DatabaseTestSchema.java,v 1.15 2007/01/07 06:14:53 bastafidli Exp $
43  * @author Julo Legeny
44  * @code.reviewer Miro Halas
45  * @code.reviewed 1.12 2005/02/26 21:13:43 bastafidli
46  */
47 public class DB2DatabaseTestSchema extends DatabaseTestSchema
48 {
49    /*
50       These tables are database specific
51       
52       CREATE SEQUENCE GENERATEDKEY_TEST_SEQ INCREMENT BY 1 START WITH 1 NO CYCLE
53       
54       CREATE TABLE GENERATEDKEY_TEST
55       (
56          TEST_KEY INTEGER NOT NULL PRIMARY KEY,
57          TEST_VALUE VARCHAR(50) NOT NULL
58       )
59
60       CREATE TABLE RESULTSET_TEST
61       (
62          RESULTSET_TEST VARCHAR(20) NOT NULL
63       )
64       
65       CREATE TABLE DATE_TEST
66       (
67          DATE_TEST DATE NOT NULL
68       )
69       
70       CREATE TABLE TIME_TEST
71       (
72          TIME_TEST TIME NOT NULL
73       )
74       
75       CREATE TABLE TIMESTAMP_TEST
76       (
77          TIMESTAMP_TEST TIMESTAMP NOT NULL
78       )
79       
80       CREATE TABLE TRANSACTION_TEST
81       (
82          TEST_ID INTEGER NOT NULL,
83          TEST_VALUE VARCHAR(50) NOT NULL,
84          CONSTRAINT TEST_ID_PK PRIMARY KEY (TEST_ID)
85       )
86       
87       CREATE TABLE TRANSACTION_RELATED_TEST
88       (
89          TEST_REL_ID INTEGER NOT NULL,
90          TEST_ID INTEGER NOT NULL,
91          TEST_VALUE VARCHAR(50),
92          CONSTRAINT TEST_ID_FK FOREIGN KEY (TEST_ID)
93          REFERENCES TRANSACTION_TEST (TEST_ID) ON DELETE CASCADE
94       )
95       
96       CREATE TABLE DELETE_TEST
97       (
98          TEST_ID INTEGER NOT NULL,
99          TEST_VALUE VARCHAR(50) NOT NULL,
100          CONSTRAINT DTEST_ID_PK PRIMARY KEY (TEST_ID)
101       )
102       
103       CREATE TABLE DELETE_RELATED_TEST
104       (
105          TEST_REL_ID INTEGER NOT NULL,
106          TEST_ID INTEGER NOT NULL,
107          TEST_VALUE VARCHAR(50),
108          CONSTRAINT DTEST_ID_FK FOREIGN KEY (TEST_ID)
109          REFERENCES DELETE_TEST (TEST_ID) ON DELETE CASCADE
110       )
111       
112       CREATE TABLE ROLLBACK_TEST
113       (
114          TEST_COLUMN VARCHAR(50) NOT NULL,
115          CONSTRAINT TEST_COLUMN_UQ UNIQUE (TEST_COLUMN)
116       )
117       
118       CREATE TABLE OWN_FK_TEST
119       (
120          TEST_ID INTEGER NOT NULL,
121          FK_ID INTEGER,
122          CONSTRAINT OWN_FK_TEST_PK PRIMARY KEY (TEST_ID),
123          CONSTRAINT OWN_FK_TEST_FK FOREIGN KEY (FK_ID)
124          REFERENCES OWN_FK_TEST (TEST_ID) ON DELETE CASCADE
125       )
126       
127       CREATE TABLE GROUP_BASE_TEST
128       (
129          TEST_BASE_ID INTEGER NOT NULL,
130          TEST_BASE_VALUE INTEGER,
131          CONSTRAINT GROUP_BASE_TEST_PK PRIMARY KEY (TEST_BASE_ID)
132       )
133       
134       
135       CREATE TABLE GROUP_CHILD_TEST
136       (
137          TEST_CHILD_ID INTEGER NOT NULL,
138          TEST_BASE_FK_ID INTEGER NOT NULL,
139          TEST_CHILD_VALUE INTEGER,
140          CONSTRAINT GROUP_CH_TEST_PK PRIMARY KEY (TEST_CHILD_ID),
141          CONSTRAINT TEST_BASE_ID_FK FOREIGN KEY (TEST_BASE_FK_ID)
142          REFERENCES GROUP_BASE_TEST (TEST_BASE_ID) ON DELETE CASCADE
143       )
144       
145       CREATE TABLE SAME_TEST1
146       (
147          ID INTEGER NOT NULL
148       )
149       
150       CREATE TABLE SAME_TEST2
151       (
152          ID INTEGER NOT NULL
153       )
154       
155       CREATE TABLE POOL_TEST
156       (
157          TEST_VALUE INTEGER NOT NULL,
158          CONSTRAINT TEST_VALUE1_PK PRIMARY KEY (TEST_VALUE)
159       )
160       
161       CREATE TABLE POOL_TEST2
162       (
163          TEST_VALUE INTEGER NOT NULL,
164          CONSTRAINT TEST_VALUE2_PK PRIMARY KEY (TEST_VALUE)
165       )
166       
167       CREATE TABLE QUERY_TEST
168       (
169          VALUE_1 INTEGER NOT NULL,
170          VALUE_2 INTEGER NOT NULL
171       )
172       
173       CREATE TABLE QUERY_TEST_EXCEPT
174       (
175          VALUE_1 INTEGER NOT NULL
176       )
177
178       CREATE TABLE UNIQUE_COLUMN_TEST
179       (
180          TEST_ID INTEGER NOT NULL,
181          CONSTRAINT TEST_ID_UQ UNIQUE (TEST_ID)
182       )
183
184       CREATE TABLE NULL_COLUMN_TEST (
185          NAME VARCHAR(50)
186       )
187
188       CREATE PROCEDURE insert_generatedkey_test (
189          IN in_value VARCHAR(50),
190          OUT out_key INTEGER,
191          OUT out_rows INTEGER
192       )
193
194       LANGUAGE SQL SPECIFIC insert_genkey_test
195       BEGIN
196          DECLARE new_out_key INTEGER DEFAULT -1;
197          DECLARE rows_inserted INTEGER DEFAULT 0;
198          SET new_out_key = NEXT VALUE FOR generatedkey_test_seq;
199          INSERT INTO GENERATEDKEY_TEST(test_key, test_value) VALUES (new_out_key, in_value);
200          GET DIAGNOSTICS rows_inserted = ROW_COUNT;
201          SET out_key = new_out_key;
202          SET out_rows = rows_inserted;
203       END
204
205       CREATE PROCEDURE insert_row_count_test (
206          IN in_value VARCHAR(50),
207          OUT out_rows INTEGER)
208       LANGUAGE SQL SPECIFIC insert_rowc_test
209       BEGIN
210          DECLARE rows_inserted INTEGER DEFAULT 0;
211          INSERT INTO GENERATEDKEY_TEST(test_key, test_value) VALUES
212             (NEXT VALUE FOR generatedkey_test_seq, in_value);
213          GET DIAGNOSTICS rows_inserted = ROW_COUNT;
214          SET out_rows = rows_inserted;
215       END
216
217       CREATE PROCEDURE update_transaction_test_value (
218          IN IN_OLD_TEST_VALUE VARCHAR(50),
219          IN IN_NEW_TEST_VALUE VARCHAR(50)
220       )
221       LANGUAGE SQL SPECIFIC update_tran_value
222       BEGIN
223          UPDATE transaction_test SET test_value = IN_NEW_TEST_VALUE
224                 WHERE test_value = IN_OLD_TEST_VALUE;
225       END
226    */
227    
228    // Cached values ////////////////////////////////////////////////////////////
229
230    /**
231     * Logger for this class
232     */
233    private static Logger JavaDoc s_logger = Log.getInstance(DB2DatabaseTestSchema.class);
234
235    // Constructors /////////////////////////////////////////////////////////////
236
237    /**
238     * Default constructor.
239     *
240     * @throws OSSException - error occured.
241     */
242    public DB2DatabaseTestSchema(
243    ) throws OSSException
244    {
245       super();
246    }
247
248    // Lifecycle events /////////////////////////////////////////////////////////
249
250    /**
251     * {@inheritDoc}
252     */
253    public String JavaDoc getInsertSelectQuery()
254    {
255       return "insert into QUERY_TEST (VALUE_1, VALUE_2) " +
256              "select CAST(? AS INTEGER), VALUE_2 from QUERY_TEST where VALUE_2=1";
257    }
258
259    /**
260     * {@inheritDoc}
261     */
262    public void create(
263       Connection JavaDoc cntDBConnection,
264       String JavaDoc strUserName
265    ) throws SQLException JavaDoc
266    {
267       // There will be not called super for creating generic tables, because
268 // IBM DB2 has restriction for constraint name length = 18 characters.
269 // We have create all whole schema directly in this method.
270 // super.create(cntDBConnection, strUserName);
271
272       // Now try to create any database specific tables
273 Statement JavaDoc stmQuery = null;
274       try
275       {
276          stmQuery = cntDBConnection.createStatement();
277
278          if (stmQuery.execute("CREATE SEQUENCE generatedkey_test_seq " +
279                               "INCREMENT BY 1 START WITH 1 NO CYCLE"))
280          {
281             // Close any results
282 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
283          }
284          s_logger.log(Level.FINEST, "Sequence GENERATEDKEY_TEST_SEQ created.");
285          /*
286          if (stmQuery.execute("grant all on GENERATEDKEY_TEST_SEQ to " + strUserName))
287          {
288             // Close any results
289             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
290          }
291          Log.getLogger().log(Level.FINEST,
292                              "Access for sequence GENERATEDKEY_TEST_SEQ set for user "
293                              + strUserName);
294          */
295
296          ///////////////////////////////////////////////////////////////////////
297
298          if (stmQuery.execute("CREATE TABLE RESULTSET_TEST" + NL +
299                               "(" + NL +
300                               " RESULTSET_TEST VARCHAR(20) NOT NULL" + NL +
301                               ")"))
302          {
303             // Close any results
304 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
305          }
306          s_logger.log(Level.FINEST, "Table RESULTSET_TEST created.");
307          /*
308          if (stmQuery.execute("grant all on RESULTSET_TEST to " + strUserName))
309          {
310             // Close any results
311             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
312          }
313          Log.getLogger().log(Level.FINEST,
314                              "Access for table RESULTSET_TEST set for user "
315                              + strUserName);
316          */
317
318          ///////////////////////////////////////////////////////////////////////
319
320          if (stmQuery.execute("CREATE TABLE DATE_TEST" + NL +
321                               "(" + NL +
322                               " DATE_TEST DATE NOT NULL" + NL +
323                               ")"))
324          {
325             // Close any results
326 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
327          }
328          s_logger.log(Level.FINEST, "Table DATE_TEST created.");
329          /*
330          if (stmQuery.execute("grant all on DATE_TEST to " + strUserName))
331          {
332             // Close any results
333             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
334          }
335          Log.getLogger().log(Level.FINEST,
336                              "Access for table DATE_TEST set for user "
337                              + strUserName);
338          */
339
340          ///////////////////////////////////////////////////////////////////////
341
342          if (stmQuery.execute("CREATE TABLE TIME_TEST" + NL +
343                               "(" + NL +
344                               " TIME_TEST TIME NOT NULL" + NL +
345                               ")"))
346          {
347             // Close any results
348 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
349          }
350          s_logger.log(Level.FINEST, "Table TIME_TEST created.");
351          /*
352          if (stmQuery.execute("grant all on TIME_TEST to " + strUserName))
353          {
354             // Close any results
355             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
356          }
357          Log.getLogger().log(Level.FINEST,
358                              "Access for table TIME_TEST set for user "
359                              + strUserName);
360          */
361
362          ///////////////////////////////////////////////////////////////////////
363
364          if (stmQuery.execute("CREATE TABLE TIMESTAMP_TEST" + NL +
365                               "(" + NL +
366                               " TIMESTAMP_TEST TIMESTAMP NOT NULL" + NL +
367                               ")"))
368          {
369             // Close any results
370 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
371          }
372          s_logger.log(Level.FINEST, "Table TIMESTAMP_TEST created.");
373          /*
374          if (stmQuery.execute("grant all on TIMESTAMP_TEST to " + strUserName))
375          {
376             // Close any results
377             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
378          }
379          Log.getLogger().log(Level.FINEST,
380                              "Access for table TIMESTAMP_TEST set for user "
381                              + strUserName);
382          */
383
384          ///////////////////////////////////////////////////////////////////////
385
386          if (stmQuery.execute("CREATE TABLE TRANSACTION_TEST" + NL +
387                               "(" + NL +
388                               " TEST_ID INTEGER NOT NULL," + NL +
389                               " TEST_VALUE VARCHAR(50) NOT NULL," + NL +
390                               " CONSTRAINT TEST_ID_PK PRIMARY KEY (TEST_ID)" + NL +
391                               ")"))
392          {
393             // Close any results
394 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
395          }
396          s_logger.log(Level.FINEST, "Table TRANSACTION_TEST created.");
397          /*
398          if (stmQuery.execute("grant all on TRANSACTION_TEST to " + strUserName))
399          {
400             // Close any results
401             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
402          }
403          Log.getLogger().log(Level.FINEST,
404                              "Access for table TRANSACTION_TEST set for user "
405                              + strUserName);
406          */
407
408          ///////////////////////////////////////////////////////////////////////
409
410          if (stmQuery.execute("CREATE TABLE TRANSACTION_RELATED_TEST" + NL +
411                               "(" + NL +
412                               " TEST_REL_ID INTEGER NOT NULL," + NL +
413                               " TEST_ID INTEGER NOT NULL," + NL +
414                               " TEST_VALUE VARCHAR(50)," + NL +
415                               " CONSTRAINT TEST_ID_FK FOREIGN KEY (TEST_ID)" + NL +
416                               " REFERENCES TRANSACTION_TEST (TEST_ID) ON DELETE CASCADE" + NL +
417                               ")"))
418          {
419             // Close any results
420 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
421          }
422          s_logger.log(Level.FINEST, "Table TRANSACTION_RELATED_TEST created.");
423          /*
424          if (stmQuery.execute("grant all on TRANSACTION_RELATED_TEST to " + strUserName))
425          {
426             // Close any results
427             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
428          }
429          Log.getLogger().log(Level.FINEST,
430                              "Access for table TRANSACTION_RELATED_TEST set for user "
431                              + strUserName);
432          */
433
434          ///////////////////////////////////////////////////////////////////////
435
436          if (stmQuery.execute("CREATE TABLE DELETE_TEST" + NL +
437                               "(" +
438                               " TEST_ID INTEGER NOT NULL," + NL +
439                               " TEST_VALUE VARCHAR(50) NOT NULL," + NL +
440                               " CONSTRAINT DTEST_ID_PK PRIMARY KEY (TEST_ID)" + NL +
441                               ")"))
442          {
443             // Close any results
444 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
445          }
446          s_logger.log(Level.FINEST, "Table DELETE_TEST created.");
447          /*
448          if (stmQuery.execute("grant all on DELETE_TEST to " + strUserName))
449          {
450             // Close any results
451             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
452          }
453          Log.getLogger().log(Level.FINEST,
454                              "Access for table DELETE_TEST set for user "
455                              + strUserName);
456          */
457
458          ///////////////////////////////////////////////////////////////////////
459
460          if (stmQuery.execute("CREATE TABLE DELETE_RELATED_TEST" + NL +
461                               "(" + NL +
462                               " TEST_REL_ID INTEGER NOT NULL," + NL +
463                               " TEST_ID INTEGER NOT NULL," + NL +
464                               " TEST_VALUE VARCHAR(50)," + NL +
465                               " CONSTRAINT DTEST_ID_FK FOREIGN KEY (TEST_ID)" + NL +
466                               " REFERENCES DELETE_TEST (TEST_ID) ON DELETE CASCADE" + NL +
467                               ")"))
468          {
469             // Close any results
470 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
471          }
472          s_logger.log(Level.FINEST, "Table DELETE_RELATED_TEST created.");
473          /*
474          if (stmQuery.execute("grant all on DELETE_RELATED_TEST to " + strUserName))
475          {
476             // Close any results
477             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
478          }
479          Log.getLogger().log(Level.FINEST,
480                              "Access for table DELETE_RELATED_TEST set for user "
481                              + strUserName);
482          */
483
484          ///////////////////////////////////////////////////////////////////////
485
486          if (stmQuery.execute("CREATE TABLE ROLLBACK_TEST" + NL +
487                               "(" + NL +
488                               " TEST_COLUMN VARCHAR(50) NOT NULL," + NL +
489                               " CONSTRAINT TEST_COLUMN_UQ UNIQUE (TEST_COLUMN)" + NL +
490                               ")"))
491          {
492             // Close any results
493 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
494          }
495          s_logger.log(Level.FINEST, "Table TRANSACTION_TEST created.");
496          /*
497          if (stmQuery.execute("grant all on TRANSACTION_TEST to " + strUserName))
498          {
499             // Close any results
500             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
501          }
502          Log.getLogger().log(Level.FINEST,
503                              "Access for table TRANSACTION_TEST set for user "
504                              + strUserName);
505          */
506
507          ///////////////////////////////////////////////////////////////////////
508
509          if (stmQuery.execute("CREATE TABLE OWN_FK_TEST " + NL +
510                               "( " + NL +
511                               " TEST_ID INTEGER NOT NULL, " + NL +
512                               " FK_ID INTEGER," + NL +
513                               " CONSTRAINT OWN_FK_TEST_PK PRIMARY KEY (TEST_ID), " + NL +
514                               " CONSTRAINT OWN_FK_TEST_FK FOREIGN KEY (FK_ID) " + NL +
515                               " REFERENCES OWN_FK_TEST (TEST_ID) ON DELETE CASCADE" + NL +
516                               ")"))
517          {
518             // Close any results
519 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
520          }
521          s_logger.log(Level.FINEST, "Table OWN_FK_TEST created.");
522          /*
523          if (stmQuery.execute("grant all on TRANSACTION_TEST to " + strUserName))
524          {
525             // Close any results
526             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
527          }
528          Log.getLogger().log(Level.FINEST,
529                              "Access for table TRANSACTION_TEST set for user "
530                              + strUserName);
531          */
532
533          ///////////////////////////////////////////////////////////////////////
534
535          if (stmQuery.execute("CREATE TABLE GROUP_BASE_TEST" + NL +
536                               "(" + NL +
537                               " TEST_BASE_ID INTEGER NOT NULL, " + NL +
538                               " TEST_BASE_VALUE INTEGER," + NL +
539                               " CONSTRAINT GROUP_BASE_TEST_PK PRIMARY KEY (TEST_BASE_ID)" + NL +
540                               ")"))
541          {
542             // Close any results
543 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
544          }
545          s_logger.log(Level.FINEST, "Table GROUP_BASE_TEST created.");
546          /*
547          if (stmQuery.execute("grant all on GROUP_BASE_TEST to " + strUserName))
548          {
549             // Close any results
550             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
551          }
552          Log.getLogger().log(Level.FINEST,
553                              "Access for table GROUP_BASE_TEST set for user "
554                              + strUserName);
555          */
556
557          ///////////////////////////////////////////////////////////////////////
558
559          if (stmQuery.execute(
560                 "CREATE TABLE GROUP_CHILD_TEST" + NL +
561                 "(" + NL +
562                 " TEST_CHILD_ID INTEGER NOT NULL, " + NL +
563                 " TEST_BASE_FK_ID INTEGER NOT NULL, " + NL +
564                 " TEST_CHILD_VALUE INTEGER," + NL +
565                 " CONSTRAINT GROUP_CH_TEST_PK PRIMARY KEY (TEST_CHILD_ID)," + NL +
566                 " CONSTRAINT TEST_BASE_ID_FK FOREIGN KEY (TEST_BASE_FK_ID) " + NL +
567                 " REFERENCES GROUP_BASE_TEST (TEST_BASE_ID) ON DELETE CASCADE" + NL +
568                 ")"))
569          {
570             // Close any results
571 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
572          }
573          s_logger.log(Level.FINEST, "Table GROUP_CHILD_TEST created.");
574          /*
575          if (stmQuery.execute("grant all on GROUP_CHILD_TEST to " + strUserName))
576          {
577             // Close any results
578             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
579          }
580          Log.getLogger().log(Level.FINEST,
581                              "Access for table GROUP_CHILD_TEST set for user "
582                              + strUserName);
583          */
584
585          ///////////////////////////////////////////////////////////////////////
586
587          if (stmQuery.execute("CREATE TABLE SAME_TEST1" + NL +
588                               "(" + NL +
589                               " ID INTEGER NOT NULL" + NL +
590                               ")"))
591          {
592             // Close any results
593 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
594          }
595          s_logger.log(Level.FINEST, "Table SAME_TEST1 created.");
596          /*
597          if (stmQuery.execute("grant all on SAME_TEST1 to " + strUserName))
598          {
599             // Close any results
600             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
601          }
602          Log.getLogger().log(Level.FINEST,
603                              "Access for table SAME_TEST1 set for user "
604                              + strUserName);
605          */
606
607          ///////////////////////////////////////////////////////////////////////
608
609          if (stmQuery.execute("CREATE TABLE SAME_TEST2 " + NL +
610                               "(" + NL +
611                               " ID INTEGER NOT NULL" + NL +
612                               ")"))
613          {
614             // Close any results
615 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
616          }
617          s_logger.log(Level.FINEST, "Table SAME_TEST2 created.");
618          /*
619          if (stmQuery.execute("grant all on SAME_TEST2 to " + strUserName))
620          {
621             // Close any results
622             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
623          }
624          Log.getLogger().log(Level.FINEST,
625                              "Access for table SAME_TEST2 set for user "
626                              + strUserName);
627          */
628
629          ///////////////////////////////////////////////////////////////////////
630
631          if (stmQuery.execute("CREATE TABLE POOL_TEST " + NL +
632                               "(" + NL +
633                               " TEST_VALUE INTEGER NOT NULL," + NL +
634                               " CONSTRAINT TEST_VALUE1_PK PRIMARY KEY (TEST_VALUE)" + NL +
635                               ")"))
636          {
637             // Close any results
638 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
639          }
640          s_logger.log(Level.FINEST, "Table POOL_TEST created.");
641          /*
642          if (stmQuery.execute("grant all on POOL_TEST to " + strUserName))
643          {
644             // Close any results
645             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
646          }
647          Log.getLogger().log(Level.FINEST,
648                              "Access for table POOL_TEST set for user "
649                              + strUserName);
650          */
651
652          ///////////////////////////////////////////////////////////////////////
653
654          if (stmQuery.execute("CREATE TABLE POOL_TEST2 " + NL +
655                               "(" + NL +
656                               " TEST_VALUE INTEGER NOT NULL," + NL +
657                               " CONSTRAINT TEST_VALUE2_PK PRIMARY KEY (TEST_VALUE)" + NL +
658                               ")"))
659          {
660             // Close any results
661 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
662          }
663          s_logger.log(Level.FINEST, "Table POOL_TEST2 created.");
664          /*
665          if (stmQuery.execute("grant all on POOL_TEST2 to " + strUserName))
666          {
667             // Close any results
668             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
669          }
670          Log.getLogger().log(Level.FINEST,
671                              "Access for table POOL_TEST set for user "
672                              + strUserName);
673          */
674
675          ///////////////////////////////////////////////////////////////////////
676
677          if (stmQuery.execute("CREATE TABLE QUERY_TEST " + NL +
678                               "(" + NL +
679                               " VALUE_1 INTEGER NOT NULL," + NL +
680                               " VALUE_2 INTEGER NOT NULL" + NL +
681                               ")"))
682          {
683             // Close any results
684 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
685          }
686          s_logger.log(Level.FINEST, "Table QUERY_TEST created.");
687          /*
688          if (stmQuery.execute("grant all on QUERY_TEST to " + strUserName))
689          {
690             // Close any results
691             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
692          }
693          Log.getLogger().log(Level.FINEST,
694                              "Access for table QUERY_TEST set for user "
695                              + strUserName);
696          */
697
698          ///////////////////////////////////////////////////////////////////////
699
700          if (stmQuery.execute("CREATE TABLE QUERY_TEST_EXCEPT " + NL +
701                               "(" + NL +
702                               " VALUE_1 INTEGER NOT NULL" + NL +
703                               ")"))
704          {
705             // Close any results
706 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
707          }
708          s_logger.log(Level.FINEST, "Table QUERY_TEST_EXCEPT created.");
709          /*
710          if (stmQuery.execute("grant all on QUERY_TEST to " + strUserName))
711          {
712             // Close any results
713             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
714          }
715          Log.getLogger().log(Level.FINEST,
716                              "Access for table QUERY_TEST set for user "
717                              + strUserName);
718          */
719
720          ///////////////////////////////////////////////////////////////////////
721
722          if (stmQuery.execute("CREATE TABLE GENERATEDKEY_TEST" + NL +
723                               "(" + NL +
724                               " TEST_KEY INTEGER NOT NULL PRIMARY KEY," + NL +
725                               " TEST_VALUE VARCHAR(50) NOT NULL" + NL +
726                               ")"))
727          {
728             // Close any results
729 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
730          }
731          s_logger.log(Level.FINEST, "Table GENERATEDKEY_TEST created.");
732          /*
733          if (stmQuery.execute("grant all on GENERATEDKEY_TEST to " + strUserName))
734          {
735             // Close any results
736             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
737          }
738          s_logger.log(Level.FINEST,
739                       "Access for table GENERATEDKEY_TEST set for user "
740                       + strUserName);
741          */
742          ///////////////////////////////////////////////////////////////////////
743 if (stmQuery.execute("CREATE TABLE UNIQUE_COLUMN_TEST " + NL +
744                               "(" + NL +
745                               " TEST_ID INTEGER NOT NULL, " + NL +
746                               " CONSTRAINT TEST_ID_UQ UNIQUE (TEST_ID) " + NL +
747                               ")"))
748          {
749             // Close any results
750 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
751          }
752          s_logger.log(Level.FINEST, "Table UNIQUE_COLUMN_TEST created.");
753          /*
754          if (stmQuery.execute("grant all on UNIQUE_COLUMN_TEST to " + strUserName))
755          {
756             // Close any results
757             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
758          }
759          Log.getLogger().log(Level.FINEST,
760                              "Access for table UNIQUE_COLUMN_TEST set for user "
761                              + strUserName);
762          */
763
764          ///////////////////////////////////////////////////////////////////////
765
766          if (stmQuery.execute("CREATE TABLE NULL_COLUMN_TEST " + NL +
767                               "(" + NL +
768                               " NAME VARCHAR(50)" + NL +
769                               ")"))
770          {
771             // Close any results
772 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
773          }
774          s_logger.log(Level.FINEST, "Table NULL_COLUMN_TEST created.");
775          /*
776          if (stmQuery.execute("grant all on NULL_COLUMN_TEST to " + strUserName))
777          {
778             // Close any results
779             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
780          }
781          Log.getLogger().log(Level.FINEST,
782                              "Access for table NULL_COLUMN_TEST set for user "
783                              + strUserName);
784          */
785
786          ///////////////////////////////////////////////////////////////////////
787
788
789          if (stmQuery.execute("CREATE PROCEDURE insert_generatedkey_test (" +
790                               " IN in_value VARCHAR(50), " +
791                               " OUT out_key INTEGER, " +
792                               " OUT out_rows INTEGER) " +
793                               "LANGUAGE SQL SPECIFIC insert_genkey_test " +
794                               "BEGIN " +
795                               " DECLARE new_out_key INTEGER DEFAULT -1; " +
796                               " DECLARE rows_inserted INTEGER DEFAULT 0; " +
797                               " SET new_out_key = NEXT VALUE FOR generatedkey_test_seq; " +
798                               " INSERT INTO GENERATEDKEY_TEST(test_key, test_value) " +
799                               " VALUES (new_out_key, in_value); " +
800                               " GET DIAGNOSTICS rows_inserted = ROW_COUNT; " +
801                               " SET out_key = new_out_key; " +
802                               " SET out_rows = rows_inserted; " +
803                               "END"))
804          {
805             // Close any results
806 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
807          }
808          s_logger.log(Level.FINEST, "Procedure INSERT_GENERATEDKEY_TEST created.");
809          /*
810          if (stmQuery.execute("grant execute on INSERT_GENERATEDKEY_TEST to " + strUserName))
811          {
812             // Close any results
813             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
814          }
815          Log.getLogger().log(Level.FINEST,
816                              "Access for procedure INSERT_GENERATEDKEY_TEST set for user "
817                              + strUserName);
818          */
819
820          ///////////////////////////////////////////////////////////////////////
821
822          if (stmQuery.execute("CREATE PROCEDURE insert_row_count_test (" +
823                               " IN in_value VARCHAR(50), " +
824                               " OUT out_rows INTEGER) " +
825                               "LANGUAGE SQL SPECIFIC insert_rowc_test " +
826                               "BEGIN " +
827                               " DECLARE rows_inserted INTEGER DEFAULT 0; " +
828                               " INSERT INTO GENERATEDKEY_TEST(test_key, test_value) " +
829                               " VALUES (NEXT VALUE FOR generatedkey_test_seq, in_value); " +
830                               " GET DIAGNOSTICS rows_inserted = ROW_COUNT; " +
831                               " SET out_rows = rows_inserted; " +
832                               "END"))
833          {
834             // Close any results
835 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
836          }
837          s_logger.log(Level.FINEST, "Procedure INSERT_ROW_COUNT_TEST created.");
838          /*
839          if (stmQuery.execute("grant execute on INSERT_ROW_COUNT_TEST to " + strUserName))
840          {
841             // Close any results
842             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
843          }
844          Log.getLogger().log(Level.FINEST,
845                              "Access for procedure INSERT_ROW_COUNT_TEST set for user "
846                              + strUserName);
847          */
848
849          ///////////////////////////////////////////////////////////////////////
850
851          if (stmQuery.execute("CREATE PROCEDURE update_transaction_test_value (" +
852                               " IN IN_OLD_TEST_VALUE VARCHAR(50), " +
853                               " IN IN_NEW_TEST_VALUE VARCHAR(50)) " +
854                               "LANGUAGE SQL SPECIFIC update_tran_value " +
855                               "BEGIN " +
856                               " UPDATE transaction_test SET test_value = IN_NEW_TEST_VALUE " +
857                               " WHERE test_value = IN_OLD_TEST_VALUE; " +
858                               "END"))
859          {
860             // Close any results
861 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
862          }
863          s_logger.log(Level.FINEST, "Procedure UPDATE_TRANSACTION_TEST_VALUE created.");
864          /*
865          if (stmQuery.execute("grant all on UPDATE_TRANSACTION_TEST_VALUE to " + strUserName))
866          {
867             // Close any results
868             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
869          }
870          Log.getLogger().log(Level.FINEST,
871                              "Access for procedure UPDATE_TRANSACTION_TEST_VALUE set for user "
872                              + strUserName);
873          */
874       }
875       catch (SQLException JavaDoc sqleExc)
876       {
877          s_logger.log(Level.WARNING, "Failed to create database test schema.",
878                              sqleExc);
879          throw sqleExc;
880       }
881       finally
882       {
883          DatabaseUtils.closeStatement(stmQuery);
884       }
885    }
886
887    /**
888     * {@inheritDoc}
889     */
890    public String JavaDoc getInsertGeneratedKey(
891    )
892    {
893       return "INSERT INTO generatedkey_test(test_key, test_value) " +
894               "VALUES (NEXTVAL FOR generatedkey_test_seq, ?)";
895    }
896
897    /**
898     * {@inheritDoc}
899     */
900    public int[] executeInsertGeneratedKey2(
901       Connection JavaDoc dbConnection,
902       String JavaDoc strValue
903    ) throws SQLException JavaDoc
904    {
905       CallableStatement JavaDoc insertStatement = null;
906       int iInsertCount;
907       int iGeneratedKey = 0;
908       int[] returnValues = null;
909       
910       try
911       {
912          insertStatement = dbConnection.prepareCall(
913                               "call INSERT_GENERATEDKEY_TEST(?, ?, ?)");
914          insertStatement.setString(1, strValue);
915          insertStatement.registerOutParameter(2, Types.INTEGER);
916          insertStatement.registerOutParameter(3, Types.INTEGER);
917
918          // number of updated rows is returned by stored procedure
919 insertStatement.executeUpdate();
920          iGeneratedKey = insertStatement.getInt(2);
921          iInsertCount = insertStatement.getInt(3);
922
923          returnValues = new int[2];
924          returnValues[0] = iInsertCount;
925          returnValues[1] = iGeneratedKey;
926       }
927       finally
928       {
929          DatabaseUtils.closeStatement(insertStatement);
930       }
931       
932       return returnValues;
933    }
934
935    /**
936     * {@inheritDoc}
937     */
938    public int executeUpdateTestValue(
939       Connection JavaDoc dbConnection,
940       String JavaDoc strOldValue,
941       String JavaDoc strNewValue
942    ) throws SQLException JavaDoc
943    {
944       CallableStatement JavaDoc updateStatement = null;
945       int iUpdateCount = 0;
946
947       try
948       {
949          updateStatement = dbConnection.prepareCall("call UPDATE_TRANSACTION_TEST_VALUE (?, ?)");
950          updateStatement.setString(1, strOldValue);
951          updateStatement.setString(2, strNewValue);
952             
953          // number of updated rows is returned by stored procedure
954 // output parameters, there is not returned number of updated records
955 updateStatement.executeUpdate();
956          
957       }
958       finally
959       {
960          DatabaseUtils.closeStatement(updateStatement);
961       }
962       
963       return iUpdateCount;
964    }
965
966    /**
967     * {@inheritDoc}
968     */
969    public int[] executeInsertRow(
970       Connection JavaDoc dbConnection,
971       String JavaDoc strValue
972    ) throws SQLException JavaDoc
973    {
974       CallableStatement JavaDoc insertStatement = null;
975       int iInsertCount = 0;
976       int iInsertCountReturnedFromSP = 0;
977       int[] returnValues = null;
978       
979       try
980       {
981          insertStatement = dbConnection.prepareCall(
982                               "call INSERT_ROW_COUNT_TEST(?, ?)");
983          insertStatement.setString(1, strValue);
984          insertStatement.registerOutParameter(2, Types.INTEGER);
985
986          // number of updated rows is returned by stored procedure
987 iInsertCount = insertStatement.executeUpdate();
988          iInsertCountReturnedFromSP = insertStatement.getInt(2);
989
990          returnValues = new int[2];
991
992          // value (number of affected rows) returned from insertStatement.executeUpdate();
993 returnValues[0] = iInsertCount;
994
995          // value (number of inserted rows) returned from stored procedure.
996 returnValues[1] = iInsertCountReturnedFromSP;
997       }
998       finally
999       {
1000         DatabaseUtils.closeStatement(insertStatement);
1001      }
1002      
1003      return returnValues;
1004      }
1005
1006   /**
1007    * {@inheritDoc}
1008    */
1009   public void createTestUser(
1010      Connection JavaDoc cntAdminDBConnection,
1011      String JavaDoc strDatabaseURL,
1012      String JavaDoc strUserName,
1013      String JavaDoc strUserPassword
1014   ) throws SQLException JavaDoc
1015   {
1016      Statement JavaDoc stmQuery = null;
1017      try
1018      {
1019         String JavaDoc strCreateUserQuery = "GRANT CONNECT ON DATABASE TO USER " + strUserName;
1020
1021         stmQuery = cntAdminDBConnection.createStatement();
1022
1023         if (stmQuery.execute(strCreateUserQuery))
1024         {
1025            // Close any results
1026 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
1027         }
1028      }
1029      finally
1030      {
1031         DatabaseUtils.closeStatement(stmQuery);
1032      }
1033   }
1034
1035   /**
1036    * {@inheritDoc}
1037    */
1038   public void dropTestUser(
1039      Connection JavaDoc cntAdminDBConnection,
1040      String JavaDoc strDatabaseURL,
1041      String JavaDoc strUserName
1042   ) throws SQLException JavaDoc
1043   {
1044      Statement JavaDoc stmQuery = null;
1045      try
1046      {
1047         String JavaDoc strDropUserQuery = "REVOKE CONNECT ON DATABASE FROM USER " + strUserName;
1048
1049         stmQuery = cntAdminDBConnection.createStatement();
1050
1051         if (stmQuery.execute(strDropUserQuery))
1052         {
1053            // Close any results
1054 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
1055         }
1056      }
1057      finally
1058      {
1059         DatabaseUtils.closeStatement(stmQuery);
1060      }
1061   }
1062}
1063
Popular Tags