KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > opensubsystems > core > persist > db > driver > sybase > SybaseDatabaseTestSchema


1 /*
2  * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenSubsystems
5  *
6  * $Id: SybaseDatabaseTestSchema.java,v 1.19 2007/01/07 06:15:02 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.sybase;
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 Sybase database specific
41  *
42  * @version $Id: SybaseDatabaseTestSchema.java,v 1.19 2007/01/07 06:15:02 bastafidli Exp $
43  * @author Julo Legeny
44  * @code.reviewer Miro Halas
45  * @code.reviewed 1.16 2005/07/29 08:49:48 bastafidli
46  */

47 public class SybaseDatabaseTestSchema extends DatabaseTestSchema
48 {
49    /*
50       These tables are database specific
51       
52       CREATE TABLE GENERATEDKEY_TEST
53       (
54          TEST_KEY NUMERIC(10, 0) IDENTITY 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 TRANSACTION_TEST
79       (
80          TEST_ID INTEGER NOT NULL,
81          TEST_VALUE VARCHAR(50) NOT NULL,
82          CONSTRAINT TEST_ID_PK PRIMARY KEY (TEST_ID)
83       )
84
85       CREATE TABLE TRANSACTION_RELATED_TEST
86       (
87          TEST_REL_ID INTEGER NOT NULL,
88          TEST_ID INTEGER NOT NULL,
89          CONSTRAINT TEST_ID_FK FOREIGN KEY (TEST_ID)
90          REFERENCES TRANSACTION_TEST (TEST_ID)
91       )
92
93       CREATE TRIGGER TRAN_DELCASCADE_TRG
94          ON TRANSACTION_TEST
95       FOR DELETE
96       AS
97          DELETE TRANSACTION_RELATED_TEST
98             FROM TRANSACTION_RELATED_TEST, deleted
99             WHERE TRANSACTION_RELATED_TEST.TEST_ID = deleted.TEST_ID
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)
115       )
116
117       CREATE TRIGGER DELETE_DELCASCADE_TRG
118          ON DELETE_TEST
119       FOR DELETE
120       AS
121          DELETE DELETE_RELATED_TEST
122             FROM DELETE_RELATED_TEST, deleted
123             WHERE DELETE_RELATED_TEST.TEST_ID = deleted.TEST_ID
124
125       CREATE TABLE OWN_FK_TEST
126       (
127          TEST_ID INTEGER NOT NULL,
128          FK_ID INTEGER,
129          CONSTRAINT OWN_FK_TEST_PK PRIMARY KEY (TEST_ID),
130          CONSTRAINT OWN_FK_TEST_FK FOREIGN KEY (FK_ID)
131          REFERENCES OWN_FK_TEST (TEST_ID)
132       )
133
134       CREATE TABLE GROUP_BASE_TEST
135       (
136          TEST_BASE_ID INTEGER NOT NULL,
137          TEST_BASE_VALUE INTEGER,
138          CONSTRAINT GROUP_BASE_TEST_PK PRIMARY KEY (TEST_BASE_ID)
139       )
140
141       CREATE TABLE GROUP_CHILD_TEST
142       (
143          TEST_CHILD_ID INTEGER NOT NULL,
144          TEST_BASE_FK_ID INTEGER NOT NULL,
145          TEST_CHILD_VALUE INTEGER,
146          CONSTRAINT GROUP_CHILD_TEST_PK PRIMARY KEY (TEST_CHILD_ID),
147          CONSTRAINT TEST_BASE_ID_FK FOREIGN KEY (TEST_BASE_FK_ID)
148          REFERENCES GROUP_BASE_TEST (TEST_BASE_ID)
149       )
150
151       CREATE TRIGGER GROUP_DELCASCADE_TRG
152          ON GROUP_BASE_TEST
153       FOR DELETE
154       AS
155          DELETE GROUP_CHILD_TEST
156             FROM GROUP_CHILD_TEST, deleted
157             WHERE GROUP_CHILD_TEST.TEST_BASE_FK_ID = deleted.TEST_BASE_ID
158
159       CREATE TABLE SAME_TEST1
160       (
161          ID INTEGER NOT NULL
162       )
163
164       CREATE TABLE SAME_TEST2
165       (
166          ID INTEGER NOT NULL
167       )
168
169       CREATE TABLE POOL_TEST
170       (
171          TEST_VALUE INTEGER NOT NULL,
172          CONSTRAINT TEST_VALUE_PK PRIMARY KEY (TEST_VALUE)
173       )
174       
175       CREATE TABLE POOL_TEST2
176       (
177          TEST_VALUE INTEGER NOT NULL,
178          CONSTRAINT TEST_VALUE_PK PRIMARY KEY (TEST_VALUE)
179       )
180
181       CREATE TABLE QUERY_TEST
182       (
183          VALUE_1 INTEGER NOT NULL,
184          VALUE_2 INTEGER NOT NULL
185       )
186
187       CREATE table QUERY_TEST_EXCEPT (
188          VALUE_1 INTEGER NOT NULL
189       )
190
191       CREATE TABLE UNIQUE_COLUMN_TEST
192       (
193          TEST_ID INTEGER,
194          CONSTRAINT TEST_ID_UQ UNIQUE (TEST_ID)
195       )
196
197       CREATE TABLE NULL_COLUMN_TEST (
198          NAME VARCHAR(50)
199       )
200
201       CREATE PROCEDURE INSERT_GENERATEDKEY_TEST2
202          @IN_VALUE VARCHAR(50),
203          @OUT_KEY INTEGER OUTPUT
204       AS
205          INSERT INTO GENERATEDKEY_TEST(TEST_VALUE) VALUES (@IN_VALUE)
206          SET @OUT_KEY = @@IDENTITY
207          RETURN @OUT_KEY
208
209       CREATE PROCEDURE INSERT_ROW_COUNT_TEST
210          @IN_VALUE VARCHAR(50),
211          @OUT_ROWS INTEGER OUTPUT
212       AS
213          INSERT INTO GENERATEDKEY_TEST(TEST_VALUE) VALUES (@IN_VALUE)
214          SET @OUT_ROWS = @@ROWCOUNT
215       RETURN @OUT_ROWS
216    
217       CREATE PROCEDURE UPDATE_TRANSACTION_TEST_VALUE
218          @IN_OLD_TEST_VALUE VARCHAR(50),
219          @IN_NEW_TEST_VALUE VARCHAR(50)
220       AS
221          UPDATE TRANSACTION_TEST SET
222                 TEST_VALUE = @IN_NEW_TEST_VALUE
223                 WHERE TEST_VALUE = @IN_OLD_TEST_VALUE
224          RETURN @@ROWCOUNT
225    */

226    
227    // Cached values ////////////////////////////////////////////////////////////
228

229    /**
230     * Logger for this class
231     */

232    private static Logger JavaDoc s_logger = Log.getInstance(SybaseDatabaseTestSchema.class);
233
234    // Constructors /////////////////////////////////////////////////////////////
235

236    /**
237     * Default constructor.
238     *
239     * @throws OSSException - error occured.
240     */

241    public SybaseDatabaseTestSchema(
242    ) throws OSSException
243    {
244       super();
245    }
246
247    // Lifecycle events /////////////////////////////////////////////////////////
248

249    /**
250     * {@inheritDoc}
251     */

252    public void create(
253       Connection JavaDoc cntDBConnection,
254       String JavaDoc strUserName
255    ) throws SQLException JavaDoc
256    {
257       // There will be not called super for creating generic tables, because
258
// MS SQL Server uses own specific data types
259
// super.create(cntDBConnection, strUserName);
260

261       // Now try to create any database specific tables
262
Statement JavaDoc stmQuery = null;
263       try
264       {
265          stmQuery = cntDBConnection.createStatement();
266
267          ///////////////////////////////////////////////////////////////////////
268

269          if (stmQuery.execute("CREATE TABLE GENERATEDKEY_TEST" + NL +
270                               "(" + NL +
271                               " TEST_KEY NUMERIC(10, 0) IDENTITY NOT NULL PRIMARY KEY," + NL +
272                               " TEST_VALUE VARCHAR(50) NOT NULL" + NL +
273                               ")"))
274          {
275             // Close any results
276
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
277          }
278          s_logger.log(Level.FINEST, "Table GENERATEDKEY_TEST created.");
279          /*
280          if (stmQuery.execute("grant all on GENERATEDKEY_TEST to " + strUserName))
281          {
282             // Close any results
283             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
284          }
285          s_logger.log(Level.FINEST,
286                       "Access for table GENERATEDKEY_TEST set for user "
287                       + strUserName);
288          */

289
290          ///////////////////////////////////////////////////////////////////////
291

292          if (stmQuery.execute("CREATE TABLE RESULTSET_TEST" + NL +
293                               "(" + NL +
294                               " RESULTSET_TEST VARCHAR(20) NOT NULL" + NL +
295                               ")"))
296          {
297             // Close any results
298
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
299          }
300          s_logger.log(Level.FINEST, "Table RESULTSET_TEST created.");
301          /*
302          if (stmQuery.execute("grant all on RESULTSET_TEST to " + strUserName))
303          {
304             // Close any results
305             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
306          }
307          Log.getLogger().log(Level.FINEST,
308                              "Access for table RESULTSET_TEST set for user "
309                              + strUserName);
310          */

311
312          ///////////////////////////////////////////////////////////////////////
313

314          if (stmQuery.execute("CREATE TABLE DATE_TEST" + NL +
315                               "(" + NL +
316                               " DATE_TEST SMALLDATETIME NOT NULL" + NL +
317                               ")"))
318          {
319             // Close any results
320
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
321          }
322          s_logger.log(Level.FINEST, "Table DATE_TEST created.");
323          /*
324          if (stmQuery.execute("grant all on DATE_TEST to " + strUserName))
325          {
326             // Close any results
327             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
328          }
329          Log.getLogger().log(Level.FINEST,
330                              "Access for table DATE_TEST set for user "
331                              + strUserName);
332          */

333
334          ///////////////////////////////////////////////////////////////////////
335

336          if (stmQuery.execute("CREATE TABLE TIME_TEST" + NL +
337                               "(" + NL +
338                               " TIME_TEST SMALLDATETIME NOT NULL" + NL +
339                               ")"))
340          {
341             // Close any results
342
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
343          }
344          s_logger.log(Level.FINEST, "Table TIME_TEST created.");
345          /*
346          if (stmQuery.execute("grant all on TIME_TEST to " + strUserName))
347          {
348             // Close any results
349             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
350          }
351          Log.getLogger().log(Level.FINEST,
352                              "Access for table TIME_TEST set for user "
353                              + strUserName);
354          */

355
356          ///////////////////////////////////////////////////////////////////////
357

358          if (stmQuery.execute("CREATE TABLE TIMESTAMP_TEST" + NL +
359                               "(" + NL +
360                               " TIMESTAMP_TEST DATETIME NOT NULL" + NL +
361                               ")"))
362          {
363             // Close any results
364
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
365          }
366          s_logger.log(Level.FINEST, "Table TIMESTAMP_TEST created.");
367          /*
368          if (stmQuery.execute("grant all on TIMESTAMP_TEST to " + strUserName))
369          {
370             // Close any results
371             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
372          }
373          Log.getLogger().log(Level.FINEST,
374                              "Access for table TIMESTAMP_TEST set for user "
375                              + strUserName);
376          */

377
378          ///////////////////////////////////////////////////////////////////////
379

380          if (stmQuery.execute("CREATE TABLE TRANSACTION_TEST" + NL +
381                               "(" + NL +
382                               " TEST_ID INTEGER NOT NULL," + NL +
383                               " TEST_VALUE VARCHAR(50) NOT NULL," + NL +
384                               " CONSTRAINT TEST_ID_PK PRIMARY KEY (TEST_ID)" + NL +
385                               ")"))
386          {
387             // Close any results
388
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
389          }
390          s_logger.log(Level.FINEST, "Table TRANSACTION_TEST created.");
391          /*
392          if (stmQuery.execute("grant all on TRANSACTION_TEST to " + strUserName))
393          {
394             // Close any results
395             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
396          }
397          Log.getLogger().log(Level.FINEST,
398                              "Access for table TRANSACTION_TEST set for user "
399                              + strUserName);
400          */

401
402          ///////////////////////////////////////////////////////////////////////
403

404          if (stmQuery.execute("CREATE TABLE TRANSACTION_RELATED_TEST" + NL +
405                               "(" + NL +
406                               " TEST_REL_ID INTEGER NOT NULL," + NL +
407                               " TEST_ID INTEGER NOT NULL," + NL +
408                               " TEST_VALUE VARCHAR(50)," + NL +
409                               " CONSTRAINT TEST_ID_FK FOREIGN KEY (TEST_ID)" + NL +
410                               " REFERENCES TRANSACTION_TEST (TEST_ID)" + NL +
411                               ")"))
412          {
413             // Close any results
414
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
415          }
416          s_logger.log(Level.FINEST, "Table TRANSACTION_RELATED_TEST created.");
417          /*
418          if (stmQuery.execute("grant all on TRANSACTION_RELATED_TEST to " + strUserName))
419          {
420             // Close any results
421             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
422          }
423          Log.getLogger().log(Level.FINEST,
424                              "Access for table TRANSACTION_RELATED_TEST set for user "
425                              + strUserName);
426          */

427
428          ///////////////////////////////////////////////////////////////////////
429

430          if (stmQuery.execute("CREATE TRIGGER TRAN_DELCASCADE_TRG " + NL +
431                               "ON TRANSACTION_TEST " + NL +
432                               "FOR DELETE " + NL +
433                               "AS " + NL +
434                               " DELETE TRANSACTION_RELATED_TEST " + NL +
435                               " FROM TRANSACTION_RELATED_TEST, deleted " + NL +
436                               " WHERE TRANSACTION_RELATED_TEST.TEST_ID = deleted.TEST_ID"))
437          {
438             // Close any results
439
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
440          }
441          s_logger.log(Level.FINEST, "Trigger TRAN_DELCASCADE_TRG created.");
442          /*
443          if (stmQuery.execute("grant all on TRAN_DELCASCADE_TRG to " + strUserName))
444          {
445             // Close any results
446             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
447          }
448          Log.getLogger().log(Level.FINEST,
449                              "Access for trigger TRAN_DELCASCADE_TRG set for user "
450                              + strUserName);
451          */

452
453          ///////////////////////////////////////////////////////////////////////
454

455          if (stmQuery.execute("CREATE TABLE DELETE_TEST" + NL +
456                               "(" +
457                               " TEST_ID INTEGER NOT NULL," + NL +
458                               " TEST_VALUE VARCHAR(50) NOT NULL," + NL +
459                               " CONSTRAINT DTEST_ID_PK PRIMARY KEY (TEST_ID)" + NL +
460                               ")"))
461          {
462             // Close any results
463
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
464          }
465          s_logger.log(Level.FINEST, "Table DELETE_TEST created.");
466          /*
467          if (stmQuery.execute("grant all on DELETE_TEST to " + strUserName))
468          {
469             // Close any results
470             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
471          }
472          Log.getLogger().log(Level.FINEST,
473                              "Access for table DELETE_TEST set for user "
474                              + strUserName);
475          */

476
477          ///////////////////////////////////////////////////////////////////////
478

479          if (stmQuery.execute("CREATE TABLE DELETE_RELATED_TEST" + NL +
480                               "(" + NL +
481                               " TEST_REL_ID INTEGER NOT NULL," + NL +
482                               " TEST_ID INTEGER NOT NULL," + NL +
483                               " TEST_VALUE VARCHAR(50)," + NL +
484                               " CONSTRAINT DTEST_ID_FK FOREIGN KEY (TEST_ID)" + NL +
485                               " REFERENCES DELETE_TEST (TEST_ID)" + NL +
486                               ")"))
487          {
488             // Close any results
489
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
490          }
491          s_logger.log(Level.FINEST, "Table DELETE_RELATED_TEST created.");
492          /*
493          if (stmQuery.execute("grant all on DELETE_RELATED_TEST to " + strUserName))
494          {
495             // Close any results
496             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
497          }
498          Log.getLogger().log(Level.FINEST,
499                              "Access for table DELETE_RELATED_TEST set for user "
500                              + strUserName);
501          */

502
503          ///////////////////////////////////////////////////////////////////////
504

505          if (stmQuery.execute("CREATE TRIGGER DELETE_DELCASCADE_TRG " + NL +
506                               "ON DELETE_TEST " + NL +
507                               "FOR DELETE " + NL +
508                               "AS " + NL +
509                               " DELETE DELETE_RELATED_TEST " + NL +
510                               " FROM DELETE_RELATED_TEST, deleted " + NL +
511                               " WHERE DELETE_RELATED_TEST.TEST_ID = deleted.TEST_ID"))
512          {
513             // Close any results
514
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
515          }
516          s_logger.log(Level.FINEST, "Trigger DELETE_DELCASCADE_TRG created.");
517          /*
518          if (stmQuery.execute("grant all on DELETE_DELCASCADE_TRG to " + strUserName))
519          {
520             // Close any results
521             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
522          }
523          Log.getLogger().log(Level.FINEST,
524                              "Access for trigger DELETE_DELCASCADE_TRG set for user "
525                              + strUserName);
526          */

527
528          ///////////////////////////////////////////////////////////////////////
529

530          if (stmQuery.execute("CREATE TABLE ROLLBACK_TEST" + NL +
531                               "(" + NL +
532                               " TEST_COLUMN VARCHAR(50) NOT NULL," + NL +
533                               " CONSTRAINT TEST_COLUMN_UQ UNIQUE (TEST_COLUMN)" + NL +
534                               ")"))
535          {
536             // Close any results
537
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
538          }
539          s_logger.log(Level.FINEST, "Table TRANSACTION_TEST created.");
540          /*
541          if (stmQuery.execute("grant all on TRANSACTION_TEST to " + strUserName))
542          {
543             // Close any results
544             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
545          }
546          Log.getLogger().log(Level.FINEST,
547                              "Access for table TRANSACTION_TEST set for user "
548                              + strUserName);
549          */

550
551          ///////////////////////////////////////////////////////////////////////
552

553          if (stmQuery.execute("CREATE TABLE OWN_FK_TEST " + NL +
554                               "( " + NL +
555                               " TEST_ID INTEGER NOT NULL, " + NL +
556                               " FK_ID INTEGER," + NL +
557                               " CONSTRAINT OWN_FK_TEST_PK PRIMARY KEY (TEST_ID), " + NL +
558                               " CONSTRAINT OWN_FK_TEST_FK FOREIGN KEY (FK_ID) " + NL +
559                               " REFERENCES OWN_FK_TEST (TEST_ID)" + NL +
560                               ")"))
561          {
562             // Close any results
563
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
564          }
565          s_logger.log(Level.FINEST, "Table OWN_FK_TEST created.");
566          /*
567          if (stmQuery.execute("grant all on TRANSACTION_TEST to " + strUserName))
568          {
569             // Close any results
570             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
571          }
572          Log.getLogger().log(Level.FINEST,
573                              "Access for table TRANSACTION_TEST set for user "
574                              + strUserName);
575          */

576          
577          ///////////////////////////////////////////////////////////////////////
578

579          if (stmQuery.execute("CREATE TABLE GROUP_BASE_TEST" + NL +
580                               "(" + NL +
581                               " TEST_BASE_ID INTEGER NOT NULL, " + NL +
582                               " TEST_BASE_VALUE INTEGER," + NL +
583                               " CONSTRAINT GROUP_BASE_TEST_PK PRIMARY KEY (TEST_BASE_ID)" + NL +
584                               ")"))
585          {
586             // Close any results
587
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
588          }
589          s_logger.log(Level.FINEST, "Table GROUP_BASE_TEST created.");
590          /*
591          if (stmQuery.execute("grant all on GROUP_BASE_TEST to " + strUserName))
592          {
593             // Close any results
594             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
595          }
596          Log.getLogger().log(Level.FINEST,
597                              "Access for table GROUP_BASE_TEST set for user "
598                              + strUserName);
599          */

600
601          ///////////////////////////////////////////////////////////////////////
602

603          if (stmQuery.execute(
604                 "CREATE TABLE GROUP_CHILD_TEST" + NL +
605                 "(" + NL +
606                 " TEST_CHILD_ID INTEGER NOT NULL, " + NL +
607                 " TEST_BASE_FK_ID INTEGER NOT NULL, " + NL +
608                 " TEST_CHILD_VALUE INTEGER," + NL +
609                 " CONSTRAINT GROUP_CHILD_TEST_PK PRIMARY KEY (TEST_CHILD_ID)," + NL +
610                 " CONSTRAINT TEST_BASE_ID_FK FOREIGN KEY (TEST_BASE_FK_ID) " + NL +
611                 " REFERENCES GROUP_BASE_TEST (TEST_BASE_ID)" + NL +
612                 ")"))
613          {
614             // Close any results
615
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
616          }
617          s_logger.log(Level.FINEST, "Table GROUP_CHILD_TEST created.");
618          /*
619          if (stmQuery.execute("grant all on GROUP_CHILD_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 GROUP_CHILD_TEST set for user "
626                              + strUserName);
627          */

628
629          ///////////////////////////////////////////////////////////////////////
630

631          if (stmQuery.execute("CREATE TRIGGER GROUP_DELCASCADE_TRG " + NL +
632                               "ON GROUP_BASE_TEST " + NL +
633                               "FOR DELETE " + NL +
634                               "AS " + NL +
635                               " DELETE GROUP_CHILD_TEST " + NL +
636                               " FROM GROUP_CHILD_TEST, deleted " + NL +
637                               " WHERE GROUP_CHILD_TEST.TEST_BASE_FK_ID = deleted.TEST_BASE_ID"))
638          {
639             // Close any results
640
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
641          }
642          s_logger.log(Level.FINEST, "Trigger GROUP_DELCASCADE_TRG created.");
643          /*
644          if (stmQuery.execute("grant all on GROUP_DELCASCADE_TRG to " + strUserName))
645          {
646             // Close any results
647             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
648          }
649          Log.getLogger().log(Level.FINEST,
650                              "Access for trigger GROUP_DELCASCADE_TRG set for user "
651                              + strUserName);
652          */

653
654          ///////////////////////////////////////////////////////////////////////
655

656          if (stmQuery.execute("CREATE TABLE SAME_TEST1" + NL +
657                               "(" + NL +
658                               " ID INTEGER NOT NULL" + NL +
659                               ")"))
660          {
661             // Close any results
662
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
663          }
664          s_logger.log(Level.FINEST, "Table SAME_TEST1 created.");
665          /*
666          if (stmQuery.execute("grant all on SAME_TEST1 to " + strUserName))
667          {
668             // Close any results
669             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
670          }
671          Log.getLogger().log(Level.FINEST,
672                              "Access for table SAME_TEST1 set for user "
673                              + strUserName);
674          */

675
676          ///////////////////////////////////////////////////////////////////////
677

678          if (stmQuery.execute("CREATE TABLE SAME_TEST2 " + NL +
679                               "(" + NL +
680                               " ID 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 SAME_TEST2 created.");
687          /*
688          if (stmQuery.execute("grant all on SAME_TEST2 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 SAME_TEST2 set for user "
695                              + strUserName);
696          */

697
698          ///////////////////////////////////////////////////////////////////////
699

700          if (stmQuery.execute("CREATE TABLE POOL_TEST " + NL +
701                               "(" + NL +
702                               " TEST_VALUE INTEGER NOT NULL," + NL +
703                               " CONSTRAINT TEST_VALUE1_PK PRIMARY KEY (TEST_VALUE)" + NL +
704                               ")"))
705          {
706             // Close any results
707
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
708          }
709          s_logger.log(Level.FINEST, "Table POOL_TEST created.");
710          /*
711          if (stmQuery.execute("grant all on POOL_TEST to " + strUserName))
712          {
713             // Close any results
714             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
715          }
716          Log.getLogger().log(Level.FINEST,
717                              "Access for table POOL_TEST set for user "
718                              + strUserName);
719          */

720
721          ///////////////////////////////////////////////////////////////////////
722

723          if (stmQuery.execute("CREATE TABLE POOL_TEST2 " + NL +
724                               "(" + NL +
725                               " TEST_VALUE INTEGER NOT NULL," + NL +
726                               " CONSTRAINT TEST_VALUE2_PK PRIMARY KEY (TEST_VALUE)" + NL +
727                               ")"))
728          {
729             // Close any results
730
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
731          }
732          s_logger.log(Level.FINEST, "Table POOL_TEST2 created.");
733          /*
734          if (stmQuery.execute("grant all on POOL_TEST2 to " + strUserName))
735          {
736             // Close any results
737             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
738          }
739          Log.getLogger().log(Level.FINEST,
740                              "Access for table POOL_TEST set for user "
741                              + strUserName);
742          */

743          
744          ///////////////////////////////////////////////////////////////////////
745

746          if (stmQuery.execute("CREATE TABLE QUERY_TEST " + NL +
747                               "(" + NL +
748                               " VALUE_1 INTEGER NOT NULL," + NL +
749                               " VALUE_2 INTEGER NOT NULL" + NL +
750                               ")"))
751          {
752             // Close any results
753
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
754          }
755          s_logger.log(Level.FINEST, "Table QUERY_TEST created.");
756          /*
757          if (stmQuery.execute("grant all on QUERY_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 table QUERY_TEST set for user "
764                              + strUserName);
765          */

766
767          ///////////////////////////////////////////////////////////////////////
768

769          if (stmQuery.execute("CREATE TABLE QUERY_TEST_EXCEPT " + NL +
770                               "(" + NL +
771                               " VALUE_1 INTEGER NOT NULL" + NL +
772                               ")"))
773          {
774             // Close any results
775
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
776          }
777          s_logger.log(Level.FINEST, "Table QUERY_TEST_EXCEPT created.");
778          /*
779          if (stmQuery.execute("grant all on QUERY_TEST to " + strUserName))
780          {
781             // Close any results
782             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
783          }
784          Log.getLogger().log(Level.FINEST,
785                              "Access for table QUERY_TEST set for user "
786                              + strUserName);
787          */

788
789          ///////////////////////////////////////////////////////////////////////
790

791          if (stmQuery.execute("CREATE TABLE UNIQUE_COLUMN_TEST " + NL +
792                               "(" + NL +
793                               " TEST_ID INTEGER, " + NL +
794                               " CONSTRAINT TEST_ID_UQ UNIQUE (TEST_ID) " + NL +
795                               ")"))
796          {
797             // Close any results
798
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
799          }
800          s_logger.log(Level.FINEST, "Table UNIQUE_COLUMN_TEST created.");
801          /*
802          if (stmQuery.execute("grant all on UNIQUE_COLUMN_TEST to " + strUserName))
803          {
804             // Close any results
805             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
806          }
807          Log.getLogger().log(Level.FINEST,
808                              "Access for table UNIQUE_COLUMN_TEST set for user "
809                              + strUserName);
810          */

811
812          ///////////////////////////////////////////////////////////////////////
813

814          if (stmQuery.execute("CREATE TABLE NULL_COLUMN_TEST " + NL +
815                               "(" + NL +
816                               " NAME VARCHAR(50)" + NL +
817                               ")"))
818          {
819             // Close any results
820
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
821          }
822          s_logger.log(Level.FINEST, "Table NULL_COLUMN_TEST created.");
823          /*
824          if (stmQuery.execute("grant all on NULL_COLUMN_TEST to " + strUserName))
825          {
826             // Close any results
827             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
828          }
829          Log.getLogger().log(Level.FINEST,
830                              "Access for table NULL_COLUMN_TEST set for user "
831                              + strUserName);
832          */

833
834          ///////////////////////////////////////////////////////////////////////
835

836          if (stmQuery.execute("CREATE PROCEDURE INSERT_GENERATEDKEY_TEST " + NL +
837                   "@IN_VALUE VARCHAR(50), " + NL +
838                   "@OUT_KEY INTEGER OUTPUT " + NL +
839                   "AS " + NL +
840                   " INSERT INTO GENERATEDKEY_TEST(TEST_VALUE) VALUES (@IN_VALUE) " + NL +
841                   " SET @OUT_KEY = @@IDENTITY " + NL +
842                   " RETURN @OUT_KEY"))
843          {
844             // Close any results
845
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
846          }
847          s_logger.log(Level.FINEST, "Procedure INSERT_GENERATEDKEY_TEST created.");
848          /*
849          if (stmQuery.execute("grant execute on INSERT_GENERATEDKEY_TEST to " + strUserName))
850          {
851             // Close any results
852             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
853          }
854          Log.getLogger().log(Level.FINEST,
855                           "Access for procedure INSERT_GENERATEDKEY_TEST set for user "
856                           + strUserName);
857          */

858
859          ///////////////////////////////////////////////////////////////////////
860

861          if (stmQuery.execute("CREATE PROCEDURE INSERT_ROW_COUNT_TEST " + NL +
862                   "@IN_VALUE VARCHAR(50), " + NL +
863                   "@OUT_ROWS INTEGER OUTPUT " + NL +
864                   "AS " + NL +
865                   " INSERT INTO GENERATEDKEY_TEST(TEST_VALUE) VALUES (@IN_VALUE) " + NL +
866                   " SET @OUT_ROWS = @@ROWCOUNT " + NL +
867                   " RETURN @OUT_ROWS"))
868          {
869             // Close any results
870
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
871          }
872          s_logger.log(Level.FINEST, "Procedure INSERT_GENERATEDKEY_TEST created.");
873          /*
874          if (stmQuery.execute("grant execute on INSERT_GENERATEDKEY_TEST to " + strUserName))
875          {
876             // Close any results
877             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
878          }
879          Log.getLogger().log(Level.FINEST,
880                           "Access for procedure INSERT_GENERATEDKEY_TEST set for user "
881                           + strUserName);
882          */

883
884          ///////////////////////////////////////////////////////////////////////
885

886          if (stmQuery.execute("CREATE PROCEDURE UPDATE_TRANSACTION_TEST_VALUE " + NL +
887                               "@IN_OLD_TEST_VALUE VARCHAR(50), " + NL +
888                               "@IN_NEW_TEST_VALUE VARCHAR(50) " + NL +
889                               "AS " + NL +
890                               " UPDATE " + strUserName + ".TRANSACTION_TEST SET " + NL +
891                               " TEST_VALUE = @IN_NEW_TEST_VALUE " + NL +
892                               " WHERE TEST_VALUE = @IN_OLD_TEST_VALUE " + NL +
893                               " RETURN @@ROWCOUNT"))
894
895          {
896             // Close any results
897
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
898          }
899          s_logger.log(Level.FINEST, "Procedure UPDATE_TRANSACTION_TEST_VALUE created.");
900          /*
901          if (stmQuery.execute("grant all on UPDATE_TRANSACTION_TEST_VALUE to " + strUserName))
902          {
903             // Close any results
904             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
905          }
906          Log.getLogger().log(Level.FINEST,
907                              "Access for procedure UPDATE_TRANSACTION_TEST_VALUE set for user "
908                              + strUserName);
909          */

910
911       }
912       catch (SQLException JavaDoc sqleExc)
913       {
914          s_logger.log(Level.WARNING, "Failed to create database test schema.",
915                              sqleExc);
916          throw sqleExc;
917       }
918       finally
919       {
920          DatabaseUtils.closeStatement(stmQuery);
921       }
922    }
923
924    /**
925     * {@inheritDoc}
926     */

927    public String JavaDoc getInsertGeneratedKey(
928    )
929    {
930       return "INSERT INTO GENERATEDKEY_TEST(TEST_VALUE) " +
931               "VALUES (?)";
932    }
933
934    /**
935     * {@inheritDoc}
936     */

937    public int[] executeInsertGeneratedKey2(
938       Connection JavaDoc dbConnection,
939       String JavaDoc strValue
940    ) throws SQLException JavaDoc
941    {
942       CallableStatement JavaDoc insertStatement = null;
943       int iInsertCount = 0;
944       int iGeneratedKey = 0;
945       int[] returnValues = null;
946       
947       try
948       {
949          insertStatement = dbConnection.prepareCall(
950                               "EXEC INSERT_GENERATEDKEY_TEST ?, ?");
951          insertStatement.setString(1, strValue);
952          insertStatement.registerOutParameter(2, Types.INTEGER);
953
954          iInsertCount = insertStatement.executeUpdate();
955          iGeneratedKey = insertStatement.getInt(2);
956
957          returnValues = new int[2];
958          returnValues[0] = iInsertCount;
959          returnValues[1] = iGeneratedKey;
960       }
961       finally
962       {
963          DatabaseUtils.closeStatement(insertStatement);
964       }
965       
966       return returnValues;
967    }
968
969    /**
970     * {@inheritDoc}
971     */

972    public int executeUpdateTestValue(
973       Connection JavaDoc dbConnection,
974       String JavaDoc strOldValue,
975       String JavaDoc strNewValue
976    ) throws SQLException JavaDoc
977    {
978       CallableStatement JavaDoc updateStatement = null;
979       int iUpdateCount = 0;
980
981       try
982       {
983          updateStatement = dbConnection.prepareCall("EXEC UPDATE_TRANSACTION_TEST_VALUE ?, ?");
984          updateStatement.setString(1, strOldValue);
985          updateStatement.setString(2, strNewValue);
986             
987          // here is the bug in SAP DB, if there is called stored procedure without
988
// output parameters, there is not returned number of updated records
989
iUpdateCount = updateStatement.executeUpdate();
990       }
991       finally
992       {
993          DatabaseUtils.closeStatement(updateStatement);
994       }
995       
996       return iUpdateCount;
997    }
998
999    /**
1000    * {@inheritDoc}
1001    */

1002   public int[] executeInsertRow(
1003      Connection JavaDoc dbConnection,
1004      String JavaDoc strValue)
1005   throws SQLException JavaDoc
1006   {
1007      CallableStatement JavaDoc insertStatement = null;
1008      int iInsertCount = 0;
1009      int iInsertCountReturnedFromSP = 0;
1010      int[] returnValues = null;
1011      
1012      try
1013      {
1014         insertStatement = dbConnection.prepareCall("{call INSERT_ROW_COUNT_TEST (?, ?)}");
1015         insertStatement.setString(1, strValue);
1016         insertStatement.registerOutParameter(2, Types.INTEGER);
1017
1018         iInsertCount = insertStatement.executeUpdate();
1019         iInsertCountReturnedFromSP = insertStatement.getInt(2);
1020
1021         returnValues = new int[2];
1022
1023         // value (number of affected rows) returned from insertStatement.executeUpdate();
1024
returnValues[0] = iInsertCount;
1025
1026         // value (number of inserted rows) returned from stored procedure.
1027
returnValues[1] = iInsertCountReturnedFromSP;
1028      }
1029      finally
1030      {
1031         DatabaseUtils.closeStatement(insertStatement);
1032      }
1033      
1034      return returnValues;
1035   }
1036
1037   /**
1038    * {@inheritDoc}
1039    */

1040   public void createTestUser(
1041      Connection JavaDoc cntAdminDBConnection,
1042      String JavaDoc strDatabaseURL,
1043      String JavaDoc strUserName,
1044      String JavaDoc strUserPassword
1045   ) throws SQLException JavaDoc
1046   {
1047      Statement JavaDoc stmQuery = null;
1048      try
1049      {
1050         // Parse database name from URL.
1051
String JavaDoc strDatabaseName = "";
1052         // There can be used 2 various JDBC drivers: original Sybase driver, open source jTDS
1053
// driver, BEA WebLogic and DataDirect driver. URL is different for each driver so we
1054
// have to retrieve database name by 2 diffrent ways. For original Sybase and open source
1055
// jTDS driver we will use the same method for database name identifycation and another
1056
// method for identification both DataDirect and BEA WebLogic.
1057
if (strDatabaseURL.indexOf(":Tds:") > 0
1058             || strDatabaseURL.indexOf(":jtds:") > 0)
1059         {
1060            // There is used original Sybase or open source jTDS driver. Parse DB name.
1061
strDatabaseName = strDatabaseURL.substring(
1062                                        strDatabaseURL.lastIndexOf("/") + 1,
1063                                        strDatabaseURL.length());
1064         }
1065         else if (strDatabaseURL.indexOf(":bea:") > 0
1066                  || strDatabaseURL.indexOf(":datadirect:") > 0)
1067         {
1068            // There is used BEA WebLogic or DataDirect driver. Parse DB name.
1069
strDatabaseName = strDatabaseURL.substring(
1070                                 strDatabaseURL.lastIndexOf("atabaseName=") +
1071                                 "atabaseName=".length(), strDatabaseURL.length());
1072         }
1073         else
1074         {
1075            throw new SQLException JavaDoc("Unsupported URL: " + strDatabaseURL);
1076         }
1077
1078         
1079         String JavaDoc strCreateUserQuery = "USE " + strDatabaseName + " EXEC sp_addlogin '" +
1080                                     strUserName + "', '" + strUserPassword +
1081                                     "', @defdb='" + strDatabaseName +
1082                                     "', @deflanguage='us_english', @auth_mech = 'ASE'" +
1083                                     " EXEC sp_locklogin '" + strUserName + "', 'unlock'" +
1084                                     " EXEC sp_adduser '" + strUserName + "', '" + strUserName +
1085                                     "', 'public' GRANT CREATE TABLE TO " + strUserName;
1086
1087         stmQuery = cntAdminDBConnection.createStatement();
1088
1089         // there has to be here autocommit = true, because there is problem to execute
1090
// some stored procedure while transaction
1091
boolean bOriginalAutoCommit = cntAdminDBConnection.getAutoCommit();
1092
1093         try
1094         {
1095            if (!bOriginalAutoCommit)
1096            {
1097               cntAdminDBConnection.setAutoCommit(true);
1098            }
1099   
1100            if (stmQuery.execute(strCreateUserQuery))
1101            {
1102               // Close any results
1103
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
1104            }
1105         }
1106         finally
1107         {
1108            if (!bOriginalAutoCommit)
1109            {
1110               cntAdminDBConnection.setAutoCommit(bOriginalAutoCommit);
1111            }
1112         }
1113      }
1114      finally
1115      {
1116         DatabaseUtils.closeStatement(stmQuery);
1117      }
1118   }
1119
1120   /**
1121    * {@inheritDoc}
1122    */

1123   public void dropTestUser(
1124      Connection JavaDoc cntAdminDBConnection,
1125      String JavaDoc strDatabaseURL,
1126      String JavaDoc strUserName
1127   ) throws SQLException JavaDoc
1128   {
1129      Statement JavaDoc stmQuery = null;
1130
1131      try
1132      {
1133         // Parse database name from URL.
1134
String JavaDoc strDatabaseName = strDatabaseURL.substring(
1135                                     strDatabaseURL.lastIndexOf("/") + 1,
1136                                     strDatabaseURL.length());
1137
1138         String JavaDoc strDropUserQuery = "USE " + strDatabaseName + " EXEC sp_dropuser " + strUserName +
1139                                   " EXEC sp_droplogin " + strUserName;
1140
1141         stmQuery = cntAdminDBConnection.createStatement();
1142
1143         if (stmQuery.execute(strDropUserQuery))
1144         {
1145            // Close any results
1146
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
1147         }
1148      }
1149      finally
1150      {
1151         DatabaseUtils.closeStatement(stmQuery);
1152      }
1153   }
1154}
1155
Popular Tags