Java > Open Source Codes > org > opensubsystems > core > persist > db > driver > mysql > MySQLDatabaseTestSchema


1 /*
2  * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenSubsystems
5  *
6  * $Id: MySQLDatabaseTestSchema.java,v 1.18 2007/01/07 06:14:52 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.mysql;
23
24 import java.sql.Connection ;
25 import java.sql.PreparedStatement ;
26 import java.sql.ResultSet ;
27 import java.sql.SQLException ;
28 import java.sql.Statement ;
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 MySQL database specific
41  *
42  * @version $Id: MySQLDatabaseTestSchema.java,v 1.18 2007/01/07 06:14:52 bastafidli Exp $
43  * @author Julo Legeny
44  * @code.reviewer Miro Halas
45  * @code.reviewed Initial revision
46  */
47 public class MySQLDatabaseTestSchema extends DatabaseTestSchema
48 {
49    /*
50    These tables are database specific
51    
52       CREATE TABLE GENERATEDKEY_TEST
53       (
54          TEST_KEY INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
55          TEST_VALUE VARCHAR(50) NOT NULL
56       ) TYPE=INNODB
57
58       CREATE TABLE RESULTSET_TEST
59       (
60          RESULTSET_TEST VARCHAR(20) NOT NULL
61       ) TYPE=INNODB
62       
63       CREATE TABLE DATE_TEST
64       (
65          DATE_TEST DATE NOT NULL
66       ) TYPE=INNODB
67       
68       CREATE TABLE TIME_TEST
69       (
70          TIME_TEST TIME NOT NULL
71       ) TYPE=INNODB
72       
73       CREATE TABLE TIMESTAMP_TEST
74       (
75          TIMESTAMP_TEST TIMESTAMP NOT NULL
76       ) TYPE=INNODB
77
78      
79       CREATE TABLE TRANSACTION_TEST
80       (
81          TEST_ID INTEGER NOT NULL,
82          TEST_VALUE VARCHAR(50) NOT NULL,
83          CONSTRAINT TEST_ID_PK PRIMARY KEY (TEST_ID)
84       ) TYPE=INNODB
85
86       CREATE TABLE TRANSACTION_RELATED_TEST
87       (
88          TEST_REL_ID INTEGER NOT NULL,
89          TEST_ID INTEGER NOT NULL,
90          TEST_VALUE VARCHAR(50),
91          INDEX IND_TRAN_TEST_ID (TEST_ID),
92          CONSTRAINT TEST_ID_FK FOREIGN KEY (TEST_ID)
93          REFERENCES TRANSACTION_TEST (TEST_ID) ON DELETE CASCADE
94       ) TYPE=INNODB
95       
96       CREATE TABLE DELETE_TEST
97       (
98          TEST_ID INTEGER NOT NULL,
99          TEST_VALUE VARCHAR(50) NOT NULL,
100          CONSTRAINT DTEST_ID_PK PRIMARY KEY (TEST_ID)
101       ) TYPE=INNODB
102
103       CREATE TABLE DELETE_RELATED_TEST
104       (
105          TEST_REL_ID INTEGER NOT NULL,
106          TEST_ID INTEGER NOT NULL,
107          TEST_VALUE VARCHAR(50),
108          INDEX IND_DEL_TEST_ID (TEST_ID),
109          CONSTRAINT DTEST_ID_FK FOREIGN KEY (TEST_ID)
110          REFERENCES DELETE_TEST (TEST_ID) ON DELETE CASCADE
111       ) TYPE=INNODB
112
113       CREATE TABLE OWN_FK_TEST
114       (
115          TEST_ID INTEGER NOT NULL,
116          FK_ID INTEGER,
117          INDEX IND_OWN_FK_ID (FK_ID),
118          CONSTRAINT OWN_FK_TEST_PK PRIMARY KEY (TEST_ID),
119          CONSTRAINT OWN_FK_TEST_FK FOREIGN KEY (FK_ID)
120          REFERENCES OWN_FK_TEST (TEST_ID) ON DELETE CASCADE
121       ) TYPE=INNODB
122
123       CREATE TABLE GROUP_BASE_TEST
124       (
125          TEST_BASE_ID INTEGER NOT NULL,
126          TEST_BASE_VALUE INTEGER,
127          CONSTRAINT GROUP_BASE_TEST_PK PRIMARY KEY (TEST_BASE_ID)
128       ) TYPE=INNODB
129
130       CREATE TABLE GROUP_CHILD_TEST
131       (
132          TEST_CHILD_ID INTEGER NOT NULL,
133          TEST_BASE_FK_ID INTEGER NOT NULL,
134          TEST_CHILD_VALUE INTEGER,
135          INDEX IND_CHILD_TEST_BASE_FK_ID (TEST_CHILD_ID),
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       ) TYPE=INNODB
140
141       CREATE TABLE SAME_TEST1
142       (
143          ID INTEGER NOT NULL
144       ) TYPE=INNODB
145
146       CREATE TABLE SAME_TEST2
147       (
148          ID INTEGER NOT NULL
149       ) TYPE=INNODB
150
151       CREATE TABLE POOL_TEST
152       (
153          TEST_VALUE INTEGER NOT NULL,
154          CONSTRAINT TEST_VALUE_PK PRIMARY KEY (TEST_VALUE)
155       ) TYPE=INNODB
156       
157       CREATE TABLE POOL_TEST2
158       (
159          TEST_VALUE INTEGER NOT NULL,
160          CONSTRAINT TEST_VALUE_PK PRIMARY KEY (TEST_VALUE)
161       ) TYPE=INNODB
162
163       CREATE TABLE QUERY_TEST
164       (
165          VALUE_1 INTEGER NOT NULL,
166          VALUE_2 INTEGER NOT NULL
167       ) TYPE=INNODB
168
169       CREATE table QUERY_TEST_EXCEPT (
170          VALUE_1 INTEGER NOT NULL
171       ) TYPE=INNODB
172
173       CREATE TABLE UNIQUE_COLUMN_TEST
174       (
175          TEST_ID INTEGER,
176          CONSTRAINT TEST_ID_UQ UNIQUE (TEST_ID)
177       ) TYPE=INNODB
178
179       CREATE TABLE NULL_COLUMN_TEST (
180          NAME VARCHAR(50)
181       ) TYPE=INNODB
182
183    */
184    
185    // Cached values ////////////////////////////////////////////////////////////
186
187    /**
188     * Logger for this class
189     */
190    private static Logger s_logger = Log.getInstance(MySQLDatabaseTestSchema.class);
191
192    // Constructors /////////////////////////////////////////////////////////////
193
194    /**
195     * Default constructor.
196     *
197     * @throws OSSException - error occured.
198     */
199    public MySQLDatabaseTestSchema(
200    ) throws OSSException
201    {
202       super();
203    }
204
205    // Lifecycle events /////////////////////////////////////////////////////////
206
207    /**
208     * {@inheritDoc}
209     */
210    public void create(
211       Connection cntDBConnection,
212       String strUserName
213    ) throws SQLException
214    {
215       // There will be not called super for creating generic tables, because
216 // MySQL has to have created tables using TYPE=INNODB
217 // super.create(cntDBConnection, strUserName);
218
219       // Now try to create any database specific tables
220 Statement stmQuery = null;
221       try
222       {
223          stmQuery = cntDBConnection.createStatement();
224
225          ///////////////////////////////////////////////////////////////////////
226
227          if (stmQuery.execute("CREATE TABLE GENERATEDKEY_TEST" + NL +
228                               "(" + NL +
229                               " TEST_KEY INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY," + NL +
230                               " TEST_VALUE VARCHAR(50) NOT NULL" + NL +
231                               ") TYPE=INNODB"))
232          {
233             // Close any results
234 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
235          }
236          s_logger.log(Level.FINEST, "Table GENERATEDKEY_TEST created.");
237          /*
238          if (stmQuery.execute("grant all on GENERATEDKEY_TEST to " + strUserName))
239          {
240             // Close any results
241             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
242          }
243          s_logger.log(Level.FINEST,
244                       "Access for table GENERATEDKEY_TEST set for user "
245                       + strUserName);
246          */
247
248          ///////////////////////////////////////////////////////////////////////
249
250          if (stmQuery.execute("CREATE TABLE RESULTSET_TEST" + NL +
251                               "(" + NL +
252                               " RESULTSET_TEST VARCHAR(20) NOT NULL" + NL +
253                               ") TYPE=INNODB"))
254          {
255             // Close any results
256 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
257          }
258          s_logger.log(Level.FINEST, "Table RESULTSET_TEST created.");
259          /*
260          if (stmQuery.execute("grant all on RESULTSET_TEST to " + strUserName))
261          {
262             // Close any results
263             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
264          }
265          Log.getLogger().log(Level.FINEST,
266                              "Access for table RESULTSET_TEST set for user "
267                              + strUserName);
268          */
269
270          ///////////////////////////////////////////////////////////////////////
271
272          if (stmQuery.execute("CREATE TABLE DATE_TEST" + NL +
273                               "(" + NL +
274                               " DATE_TEST DATE NOT NULL" + NL +
275                               ") TYPE=INNODB"))
276          {
277             // Close any results
278 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
279          }
280          s_logger.log(Level.FINEST, "Table DATE_TEST created.");
281          /*
282          if (stmQuery.execute("grant all on DATE_TEST to " + strUserName))
283          {
284             // Close any results
285             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
286          }
287          Log.getLogger().log(Level.FINEST,
288                              "Access for table DATE_TEST set for user "
289                              + strUserName);
290          */
291
292          ///////////////////////////////////////////////////////////////////////
293
294          if (stmQuery.execute("CREATE TABLE TIME_TEST" + NL +
295                               "(" + NL +
296                               " TIME_TEST TIME NOT NULL" + NL +
297                               ") TYPE=INNODB"))
298          {
299             // Close any results
300 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
301          }
302          s_logger.log(Level.FINEST, "Table TIME_TEST created.");
303          /*
304          if (stmQuery.execute("grant all on TIME_TEST to " + strUserName))
305          {
306             // Close any results
307             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
308          }
309          Log.getLogger().log(Level.FINEST,
310                              "Access for table TIME_TEST set for user "
311                              + strUserName);
312          */
313
314          ///////////////////////////////////////////////////////////////////////
315
316          if (stmQuery.execute("CREATE TABLE TIMESTAMP_TEST" + NL +
317                               "(" + NL +
318                               " TIMESTAMP_TEST TIMESTAMP NOT NULL" + NL +
319                               ") TYPE=INNODB"))
320          {
321             // Close any results
322 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
323          }
324          s_logger.log(Level.FINEST, "Table TIMESTAMP_TEST created.");
325          /*
326          if (stmQuery.execute("grant all on TIMESTAMP_TEST to " + strUserName))
327          {
328             // Close any results
329             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
330          }
331          Log.getLogger().log(Level.FINEST,
332                              "Access for table TIMESTAMP_TEST set for user "
333                              + strUserName);
334          */
335
336          ///////////////////////////////////////////////////////////////////////
337
338          if (stmQuery.execute("CREATE TABLE TRANSACTION_TEST" + NL +
339                               "(" + NL +
340                               " TEST_ID INTEGER UNSIGNED UNIQUE NOT NULL," + NL +
341                               " TEST_VALUE VARCHAR(50) NOT NULL," + NL +
342                               " CONSTRAINT TEST_ID_PK PRIMARY KEY (TEST_ID)" + NL +
343                               ") TYPE=INNODB"))
344          {
345             // Close any results
346 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
347          }
348          s_logger.log(Level.FINEST, "Table TRANSACTION_TEST created.");
349          /*
350          if (stmQuery.execute("grant all on TRANSACTION_TEST to " + strUserName))
351          {
352             // Close any results
353             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
354          }
355          Log.getLogger().log(Level.FINEST,
356                              "Access for table TRANSACTION_TEST set for user "
357                              + strUserName);
358          */
359
360          ///////////////////////////////////////////////////////////////////////
361
362          if (stmQuery.execute("CREATE TABLE TRANSACTION_RELATED_TEST" + NL +
363                               "(" + NL +
364                               " TEST_REL_ID INTEGER NOT NULL," + NL +
365                               " TEST_ID INTEGER UNSIGNED NOT NULL," + NL +
366                               " TEST_VALUE VARCHAR(50)," + NL +
367                               " INDEX IND_TRAN_TEST_ID (TEST_ID)," + NL +
368                               " CONSTRAINT TEST_ID_FK FOREIGN KEY (TEST_ID)" + NL +
369                               " REFERENCES TRANSACTION_TEST (TEST_ID) ON DELETE CASCADE" + NL +
370                               ") TYPE=INNODB"))
371          {
372             // Close any results
373 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
374          }
375          s_logger.log(Level.FINEST, "Table TRANSACTION_RELATED_TEST created.");
376          /*
377          if (stmQuery.execute("grant all on TRANSACTION_RELATED_TEST to " + strUserName))
378          {
379             // Close any results
380             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
381          }
382          Log.getLogger().log(Level.FINEST,
383                              "Access for table TRANSACTION_RELATED_TEST set for user "
384                              + strUserName);
385          */
386          
387          ///////////////////////////////////////////////////////////////////////
388
389          if (stmQuery.execute("CREATE TABLE DELETE_TEST" + NL +
390                               "(" + NL +
391                               " TEST_ID INTEGER NOT NULL," + NL +
392                               " TEST_VALUE VARCHAR(50) NOT NULL," + NL +
393                               " CONSTRAINT DTEST_ID_PK PRIMARY KEY (TEST_ID)" + NL +
394                               ") TYPE=INNODB"))
395          {
396             // Close any results
397 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
398          }
399          s_logger.log(Level.FINEST, "Table DELETE_TEST created.");
400          /*
401          if (stmQuery.execute("grant all on DELETE_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 DELETE_TEST set for user "
408                              + strUserName);
409          */
410
411          ///////////////////////////////////////////////////////////////////////
412
413          if (stmQuery.execute("CREATE TABLE DELETE_RELATED_TEST" + NL +
414                               "(" + NL +
415                               " TEST_REL_ID INTEGER NOT NULL," + NL +
416                               " TEST_ID INTEGER NOT NULL," + NL +
417                               " TEST_VALUE VARCHAR(50)," + NL +
418                               " INDEX IND_DEL_TEST_ID (TEST_ID)," + NL +
419                               " CONSTRAINT DTEST_ID_FK FOREIGN KEY (TEST_ID)" + NL +
420                               " REFERENCES DELETE_TEST (TEST_ID) ON DELETE CASCADE" + NL +
421                               ") TYPE=INNODB"))
422          {
423             // Close any results
424 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
425          }
426          s_logger.log(Level.FINEST, "Table DELETE_RELATED_TEST created.");
427          /*
428          if (stmQuery.execute("grant all on DELETE_RELATED_TEST to " + strUserName))
429          {
430             // Close any results
431             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
432          }
433          Log.getLogger().log(Level.FINEST,
434                              "Access for table DELETE_RELATED_TEST set for user "
435                              + strUserName);
436          */
437          ///////////////////////////////////////////////////////////////////////
438
439          if (stmQuery.execute("CREATE TABLE ROLLBACK_TEST" + NL +
440                               "(" + NL +
441                               " TEST_COLUMN VARCHAR(50) NOT NULL," + NL +
442                               " CONSTRAINT TEST_COLUMN_UQ UNIQUE (TEST_COLUMN)" + NL +
443                               ") TYPE=INNODB"))
444          {
445             // Close any results
446 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
447          }
448          s_logger.log(Level.FINEST, "Table TRANSACTION_TEST created.");
449          /*
450          if (stmQuery.execute("grant all on TRANSACTION_TEST to " + strUserName))
451          {
452             // Close any results
453             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
454          }
455          Log.getLogger().log(Level.FINEST,
456                              "Access for table TRANSACTION_TEST set for user "
457                              + strUserName);
458          */
459
460          ///////////////////////////////////////////////////////////////////////
461
462          if (stmQuery.execute("CREATE TABLE OWN_FK_TEST " + NL +
463                               "( " + NL +
464                               " TEST_ID INTEGER NOT NULL, " + NL +
465                               " FK_ID INTEGER," + NL +
466                               " INDEX IND_OWN_FK_ID (FK_ID)," + NL +
467                               " CONSTRAINT OWN_FK_TEST_PK PRIMARY KEY (TEST_ID), " + NL +
468                               " CONSTRAINT OWN_FK_TEST_FK FOREIGN KEY (FK_ID) " + NL +
469                               " REFERENCES OWN_FK_TEST (TEST_ID) ON DELETE CASCADE" + NL +
470                               ") TYPE=INNODB"))
471          {
472             // Close any results
473 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
474          }
475          s_logger.log(Level.FINEST, "Table OWN_FK_TEST created.");
476          /*
477          if (stmQuery.execute("grant all on TRANSACTION_TEST to " + strUserName))
478          {
479             // Close any results
480             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
481          }
482          Log.getLogger().log(Level.FINEST,
483                              "Access for table TRANSACTION_TEST set for user "
484                              + strUserName);
485          */
486          
487          ///////////////////////////////////////////////////////////////////////
488
489          if (stmQuery.execute("CREATE TABLE GROUP_BASE_TEST" + NL +
490                               "(" + NL +
491                               " TEST_BASE_ID INTEGER NOT NULL, " + NL +
492                               " TEST_BASE_VALUE INTEGER," + NL +
493                               " CONSTRAINT GROUP_BASE_TEST_PK PRIMARY KEY (TEST_BASE_ID)" + NL +
494                               ") TYPE=INNODB"))
495          {
496             // Close any results
497 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
498          }
499          s_logger.log(Level.FINEST, "Table GROUP_BASE_TEST created.");
500          /*
501          if (stmQuery.execute("grant all on GROUP_BASE_TEST to " + strUserName))
502          {
503             // Close any results
504             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
505          }
506          Log.getLogger().log(Level.FINEST,
507                              "Access for table GROUP_BASE_TEST set for user "
508                              + strUserName);
509          */
510
511          ///////////////////////////////////////////////////////////////////////
512
513          if (stmQuery.execute(
514                 "CREATE TABLE GROUP_CHILD_TEST" + NL +
515                 "(" + NL +
516                 " TEST_CHILD_ID INTEGER NOT NULL, " + NL +
517                 " TEST_BASE_FK_ID INTEGER NOT NULL, " + NL +
518                 " TEST_CHILD_VALUE INTEGER," + NL +
519                 " INDEX IND_CHILD_TEST_BASE_FK_ID (TEST_BASE_FK_ID)," + NL +
520                 " CONSTRAINT GROUP_CHILD_TEST_PK PRIMARY KEY (TEST_CHILD_ID)," + NL +
521                 " CONSTRAINT TEST_BASE_ID_FK FOREIGN KEY (TEST_BASE_FK_ID) " + NL +
522                 " REFERENCES GROUP_BASE_TEST (TEST_BASE_ID) ON DELETE CASCADE" + NL +
523                 ") TYPE=INNODB"))
524          {
525             // Close any results
526 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
527          }
528          s_logger.log(Level.FINEST, "Table GROUP_CHILD_TEST created.");
529          /*
530          if (stmQuery.execute("grant all on GROUP_CHILD_TEST to " + strUserName))
531          {
532             // Close any results
533             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
534          }
535          Log.getLogger().log(Level.FINEST,
536                              "Access for table GROUP_CHILD_TEST set for user "
537                              + strUserName);
538          */
539
540          ///////////////////////////////////////////////////////////////////////
541
542          if (stmQuery.execute("CREATE TABLE SAME_TEST1" + NL +
543                               "(" + NL +
544                               " ID INTEGER NOT NULL" + NL +
545                               ") TYPE=INNODB"))
546          {
547             // Close any results
548 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
549          }
550          s_logger.log(Level.FINEST, "Table SAME_TEST1 created.");
551          /*
552          if (stmQuery.execute("grant all on SAME_TEST1 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 SAME_TEST1 set for user "
559                              + strUserName);
560          */
561
562          ///////////////////////////////////////////////////////////////////////
563
564          if (stmQuery.execute("CREATE TABLE SAME_TEST2 " + NL +
565                               "(" + NL +
566                               " ID INTEGER NOT NULL" + NL +
567                               ") TYPE=INNODB"))
568          {
569             // Close any results
570 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
571          }
572          s_logger.log(Level.FINEST, "Table SAME_TEST2 created.");
573          /*
574          if (stmQuery.execute("grant all on SAME_TEST2 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_TEST2 set for user "
581                              + strUserName);
582          */
583
584          ///////////////////////////////////////////////////////////////////////
585
586          if (stmQuery.execute("CREATE TABLE POOL_TEST " + NL +
587                               "(" + NL +
588                               " TEST_VALUE INTEGER NOT NULL," + NL +
589                               " CONSTRAINT TEST_VALUE1_PK PRIMARY KEY (TEST_VALUE)" + NL +
590                               ") TYPE=INNODB"))
591          {
592             // Close any results
593 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
594          }
595          s_logger.log(Level.FINEST, "Table POOL_TEST created.");
596          /*
597          if (stmQuery.execute("grant all on POOL_TEST to " + strUserName))
598          {
599             // Close any results
600             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
601          }
602          Log.getLogger().log(Level.FINEST,
603                              "Access for table POOL_TEST set for user "
604                              + strUserName);
605          */
606
607          ///////////////////////////////////////////////////////////////////////
608
609          if (stmQuery.execute("CREATE TABLE POOL_TEST2 " + NL +
610                               "(" + NL +
611                               " TEST_VALUE INTEGER NOT NULL," + NL +
612                               " CONSTRAINT TEST_VALUE2_PK PRIMARY KEY (TEST_VALUE)" + NL +
613                               ") TYPE=INNODB"))
614          {
615             // Close any results
616 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
617          }
618          s_logger.log(Level.FINEST, "Table POOL_TEST2 created.");
619          /*
620          if (stmQuery.execute("grant all on POOL_TEST2 to " + strUserName))
621          {
622             // Close any results
623             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
624          }
625          Log.getLogger().log(Level.FINEST,
626                              "Access for table POOL_TEST set for user "
627                              + strUserName);
628          */
629          
630          ///////////////////////////////////////////////////////////////////////
631
632          if (stmQuery.execute("CREATE TABLE QUERY_TEST " + NL +
633                               "(" + NL +
634                               " VALUE_1 INTEGER NOT NULL," + NL +
635                               " VALUE_2 INTEGER NOT NULL" + NL +
636                               ") TYPE=INNODB"))
637          {
638             // Close any results
639 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
640          }
641          s_logger.log(Level.FINEST, "Table QUERY_TEST created.");
642          /*
643          if (stmQuery.execute("grant all on QUERY_TEST to " + strUserName))
644          {
645             // Close any results
646             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
647          }
648          Log.getLogger().log(Level.FINEST,
649                              "Access for table QUERY_TEST set for user "
650                              + strUserName);
651          */
652
653          ///////////////////////////////////////////////////////////////////////
654
655          if (stmQuery.execute("CREATE TABLE QUERY_TEST_EXCEPT " + NL +
656                               "(" + NL +
657                               " VALUE_1 INTEGER NOT NULL" + NL +
658                               ") TYPE=INNODB"))
659          {
660             // Close any results
661 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
662          }
663          s_logger.log(Level.FINEST, "Table QUERY_TEST_EXCEPT 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 UNIQUE_COLUMN_TEST " + NL +
678                               "(" + NL +
679                               " TEST_ID INTEGER, " + NL +
680                               " CONSTRAINT TEST_ID_UQ UNIQUE (TEST_ID) " + NL +
681                               ") TYPE=INNODB"))
682          {
683             // Close any results
684 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
685          }
686          s_logger.log(Level.FINEST, "Table UNIQUE_COLUMN_TEST created.");
687          /*
688          if (stmQuery.execute("grant all on UNIQUE_COLUMN_TEST to " + strUserName))
689          {
690             // Close any results
691             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
692          }
693          Log.getLogger().log(Level.FINEST,
694                              "Access for table UNIQUE_COLUMN_TEST set for user "
695                              + strUserName);
696          */
697
698          ///////////////////////////////////////////////////////////////////////
699
700          if (stmQuery.execute("CREATE TABLE NULL_COLUMN_TEST " + NL +
701                               "(" + NL +
702                               " NAME VARCHAR(50)" + NL +
703                               ") TYPE=INNODB"))
704          {
705             // Close any results
706 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
707          }
708          s_logger.log(Level.FINEST, "Table NULL_COLUMN_TEST created.");
709          /*
710          if (stmQuery.execute("grant all on NULL_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 NULL_COLUMN_TEST set for user "
717                              + strUserName);
718          */
719
720
721       }
722       catch (SQLException sqleExc)
723       {
724          s_logger.log(Level.WARNING, "Failed to create database test schema.",
725                              sqleExc);
726          throw sqleExc;
727       }
728       finally
729       {
730          DatabaseUtils.closeStatement(stmQuery);
731       }
732    }
733
734    /**
735     * {@inheritDoc}
736     */
737    public String getInsertGeneratedKey(
738    )
739    {
740       return "INSERT INTO generatedkey_test(test_key, test_value) " +
741               "VALUES (null, ?)";
742    }
743
744    /**
745     * {@inheritDoc}
746     */
747    public int[] executeInsertGeneratedKey2(
748       Connection dbConnection,
749       String strValue
750    ) throws SQLException
751    {
752       PreparedStatement insertStatement = null;
753       Statement selectStatement = null;
754       // CallableStatement callStatement = null;
755 ResultSet rsResults = null;
756       int iInsertCount = 0;
757       int iGeneratedKey = 0;
758       int[] returnValues = null;
759       
760       try
761       {
762          insertStatement = dbConnection.prepareStatement(getInsertGeneratedKey());
763          insertStatement.setString(1, strValue);
764          iInsertCount = insertStatement.executeUpdate();
765
766          selectStatement = dbConnection.createStatement();
767
768          rsResults = selectStatement.executeQuery("SELECT LAST_INSERT_ID() FROM generatedkey_test");
769          if (rsResults.next())
770          {
771             iGeneratedKey = rsResults.getInt(1);
772
773             returnValues = new int[2];
774             returnValues[0] = iInsertCount;
775             returnValues[1] = iGeneratedKey;
776          }
777       }
778       finally
779       {
780          DatabaseUtils.closeStatement(insertStatement);
781          DatabaseUtils.closeResultSetAndStatement(rsResults, selectStatement);
782       }
783       
784       return returnValues;
785    }
786
787
788    /**
789     * {@inheritDoc}
790     */
791    public int executeUpdateTestValue(
792       Connection dbConnection,
793       String strOldValue,
794       String strNewValue
795    ) throws SQLException
796    {
797       PreparedStatement updateStatement = null;
798       int iUpdateCount = 0;
799
800       try
801       {
802          updateStatement = dbConnection.prepareStatement(
803               "update TRANSACTION_TEST set TEST_VALUE = ? where TEST_VALUE = ?");
804          updateStatement.setString(1, strNewValue);
805          updateStatement.setString(2, strOldValue);
806             
807          // here is the bug in SAP DB which is not seen in HSQLDB, if there is
808 // called stored procedure without output parameters, there is not
809 // returned number of updated records
810 iUpdateCount = updateStatement.executeUpdate();
811       }
812       finally
813       {
814          DatabaseUtils.closeStatement(updateStatement);
815       }
816       
817       return iUpdateCount;
818    }
819
820    /**
821     * {@inheritDoc}
822     */
823    public int[] executeInsertRow(
824       Connection dbConnection,
825       String strValue)
826    throws SQLException
827    {
828       PreparedStatement insertStatement = null;
829       Statement selectStatement = null;
830       ResultSet rsResults = null;
831       int iInsertCount = 0;
832       int iInsertCountReturnedFromSP = 0;
833       int[] returnValues = null;
834       
835       try
836       {
837          insertStatement = dbConnection.prepareStatement(getInsertGeneratedKey());
838          insertStatement.setString(1, strValue);
839          iInsertCount = insertStatement.executeUpdate();
840
841          selectStatement = dbConnection.createStatement();
842
843          // Returning numer of affected rows is supported from MySQL version 5
844 // rsResults = selectStatement.executeQuery("SELECT ROW_COUNT();");
845 // if (rsResults.next())
846 // {
847 // iGeneratedKey = rsResults.getInt(1);
848
849             returnValues = new int[2];
850
851             // value (number of affected rows) returned from insertStatement.executeUpdate();
852 returnValues[0] = iInsertCount;
853
854             // value (number of inserted rows) returned from stored procedure. It will be always 0
855 // here, because MySQL doesn't support stored procedures.
856 returnValues[1] = iInsertCountReturnedFromSP;
857          // }
858 }
859       finally
860       {
861          DatabaseUtils.closeStatement(insertStatement);
862          DatabaseUtils.closeResultSetAndStatement(rsResults, selectStatement);
863       }
864       // MySQL doesn't support stored procedures
865 return returnValues;
866    }
867
868    /**
869     * {@inheritDoc}
870     */
871    public void createTestUser(
872       Connection cntAdminDBConnection,
873       String strDatabaseURL,
874       String strUserName,
875       String strUserPassword
876    ) throws SQLException
877    {
878       Statement stmQuery = null;
879       try
880       {
881          // Parse database name from URL.
882 String strDatabaseName = strDatabaseURL.substring(
883                                      strDatabaseURL.lastIndexOf("/") + 1,
884                                      strDatabaseURL.length());
885
886          String strCreateUserQuery = "GRANT Select, Insert, Update, Delete, Index, Alter, " +
887                                      "Create, Drop, References ON " + strDatabaseName + ".* TO '" +
888                                      strUserName + "'@'localhost' IDENTIFIED BY '"
889                                      + strUserPassword + "'";
890
891          stmQuery = cntAdminDBConnection.createStatement();
892
893          if (stmQuery.execute(strCreateUserQuery))
894          {
895             // Close any results
896 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
897          }
898       }
899       finally
900       {
901          DatabaseUtils.closeStatement(stmQuery);
902       }
903    }
904
905    /**
906     * {@inheritDoc}
907     */
908    public void dropTestUser(
909       Connection cntAdminDBConnection,
910       String strDatabaseURL,
911       String strUserName
912    ) throws SQLException
913    {
914       Statement stmQuery = null;
915       try
916       {
917          // Parse host name from URL
918 String strHost = strDatabaseURL.substring(
919                              strDatabaseURL.indexOf("://") + 3,
920                              strDatabaseURL.lastIndexOf("/"));
921
922          String strDropUserQuery = "DELETE FROM mysql.user WHERE User='" + strUserName + "' " +
923                                    "AND Host='" + strHost + "'";
924
925          stmQuery = cntAdminDBConnection.createStatement();
926
927          if (stmQuery.execute(strDropUserQuery))
928          {
929             // Close any results
930 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
931          }
932       }
933       finally
934       {
935          DatabaseUtils.closeStatement(stmQuery);
936       }
937    }
938 }
939

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags