Java > Open Source Codes > org > opensubsystems > core > persist > db > driver > mssql > MSSQLDatabaseTestSchema


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

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags