Java > Open Source Codes > org > opensubsystems > core > persist > db > driver > oracle > OracleDatabaseTestSchema


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

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags