Java > Open Source Codes > com > versant > core > jdbc > sql > MySqlSqlDriver


1
2 /*
3  * Copyright (c) 1998 - 2005 Versant Corporation
4  * All rights reserved. This program and the accompanying materials
5  * are made available under the terms of the Eclipse Public License v1.0
6  * which accompanies this distribution, and is available at
7  * http://www.eclipse.org/legal/epl-v10.html
8  *
9  * Contributors:
10  * Versant Corporation - initial API and implementation
11  */
12 package com.versant.core.jdbc.sql;
13
14 import com.versant.core.common.Debug;
15 import com.versant.core.metadata.MDStatics;
16 import com.versant.core.jdbc.metadata.*;
17 import com.versant.core.jdbc.sql.conv.AsciiStreamConverter;
18 import com.versant.core.jdbc.sql.conv.BooleanConverter;
19 import com.versant.core.jdbc.sql.conv.DateTimestampConverter;
20 import com.versant.core.jdbc.sql.conv.InputStreamConverter;
21 import com.versant.core.jdbc.sql.diff.ColumnDiff;
22 import com.versant.core.jdbc.sql.diff.ControlParams;
23 import com.versant.core.jdbc.sql.diff.TableDiff;
24 import com.versant.core.jdbc.sql.exp.SqlExp;
25 import com.versant.core.util.CharBuf;
26
27 import java.io.PrintWriter ;
28 import java.math.BigDecimal ;
29 import java.math.BigInteger ;
30 import java.sql.*;
31 import java.util.ArrayList ;
32 import java.util.Date ;
33 import java.util.HashMap ;
34
35 /**
36  * Driver for MySQL.
37  */
38 public final class MySqlSqlDriver extends SqlDriver {
39
40     private AsciiStreamConverter.Factory asciiStreamConverterFactory
41             = new AsciiStreamConverter.Factory();
42     private InputStreamConverter.Factory inputStreamConverterFactory
43             = new InputStreamConverter.Factory();
44
45     private boolean refConstraintsNotSupported = true;
46     private int major;
47     private int minor;
48     private String minorPatchLevel;
49     private String rawVersion;
50
51     /**
52      * Get the name of this driver.
53      */
54     public String getName() {
55         return "mysql";
56     }
57
58     public int getMajorVersion() {
59         return major;
60     }
61
62     public int getMinorVersion() {
63         return minor;
64     }
65
66     public String getMinorVersionPatchLevel() {
67         return minorPatchLevel;
68     }
69
70     public String getVersion() {
71         return rawVersion;
72     }
73
74     /**
75      * Get the default type mapping for the supplied JDBC type code from
76      * java.sql.Types or null if the type is not supported. There is no
77      * need to set the database or jdbcType on the mapping as this is done
78      * after this call returns. Subclasses should override this and to
79      * customize type mappings.
80      */
81     protected JdbcTypeMapping getTypeMapping(int jdbcType) {
82         switch (jdbcType) {
83             case Types.FLOAT:
84             case Types.REAL:
85                 return new JdbcTypeMapping("FLOAT",
86                         0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
87                         null);
88             case Types.DATE:
89             case Types.TIME:
90             case Types.TIMESTAMP:
91                 return new JdbcTypeMapping("DATETIME",
92                         0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
93                         null);
94             case Types.CLOB:
95             case Types.LONGVARCHAR:
96                 return new JdbcTypeMapping("LONGTEXT",
97                         0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
98                         asciiStreamConverterFactory);
99             case Types.LONGVARBINARY:
100             case Types.BLOB:
101                 return new JdbcTypeMapping("LONGBLOB",
102                         0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
103                         inputStreamConverterFactory);
104             case Types.VARBINARY:
105                 return new JdbcTypeMapping("TINYBLOB",
106                         0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
107                         bytesConverterFactory);
108         }
109         return super.getTypeMapping(jdbcType);
110     }
111
112     /**
113      * Get the default field mappings for this driver. These map java classes
114      * to column properties. Subclasses should override this, call super() and
115      * replace mappings as needed.
116      */
117     public HashMap getJavaTypeMappings() {
118         HashMap ans = super.getJavaTypeMappings();
119
120         BooleanConverter.Factory bcf = new BooleanConverter.Factory();
121         ((JdbcJavaTypeMapping) ans.get(Boolean.TYPE)).setConverterFactory(bcf);
122         ((JdbcJavaTypeMapping) ans.get(Boolean .class)).setConverterFactory(bcf);
123
124         DateTimestampConverter.Factory dtcf = new DateTimestampConverter.Factory();
125         ((JdbcJavaTypeMapping) ans.get(Date .class)).setConverterFactory(dtcf);
126
127         return ans;
128     }
129
130     /**
131      * Use the index of the column in the 'group by' expression.
132      */
133     public boolean useColumnIndexForGroupBy() {
134         return true;
135     }
136
137     public boolean isCustomizeForServerRequired() {
138         return true;
139     }
140
141     /**
142      * Find out what version of MySQL con is for and adapt.
143      */
144     public void customizeForServer(Connection con) throws SQLException {
145         try {
146             extractVersionInfo(rawVersion = getVersion(con));
147         } catch (NumberFormatException e) {
148             if (Debug.DEBUG) e.printStackTrace(System.out);
149         }
150     }
151
152     /**
153      * Extract version info from a String. Expected format 'major.minor.minorPatchLevel'
154      * where both major and minor will be interpreted as int and minorPatchLevel
155      * as a String.
156      */
157     private void extractVersionInfo(String s) {
158         if (Debug.DEBUG) System.out.println("s = " + s);
159         int i = s.indexOf('.');
160         major = Integer.parseInt(s.substring(0, i));
161         if (Debug.DEBUG) System.out.println("major = " + major);
162         int j = s.indexOf('.', i + 1);
163         minor = Integer.parseInt(s.substring(i + 1, j));
164         if (Debug.DEBUG) System.out.println("minor = " + minor);
165         minorPatchLevel = s.substring(j + 1);
166         if (Debug.DEBUG) {
167             System.out.println("minorPatchLevel = " + minorPatchLevel);
168         }
169     }
170
171     private String getVersion(Connection con) throws SQLException {
172         Statement stat = null;
173         ResultSet rs = null;
174         try {
175             stat = con.createStatement();
176             rs = stat.executeQuery("SELECT version()");
177             rs.next();
178             String ver = rs.getString(1);
179             con.commit();
180             return ver;
181         } finally {
182             if (rs != null) {
183                 try {
184                     rs.close();
185                 } catch (SQLException e) {
186                     // ignore
187 }
188             }
189             if (stat != null) {
190                 try {
191                     stat.close();
192                 } catch (SQLException e) {
193                     // ignore
194 }
195             }
196         }
197     }
198
199     /**
200      * Create a default name generator instance for JdbcStore's using this
201      * driver.
202      */
203     public JdbcNameGenerator createJdbcNameGenerator() {
204         DefaultJdbcNameGenerator n = createDefaultJdbcNameGenerator();
205         n.setMaxColumnNameLength(64);
206         n.setMaxTableNameLength(64);
207         n.setMaxConstraintNameLength(64);
208         n.setMaxIndexNameLength(64);
209         return n;
210     }
211
212     /**
213      * Add the primary key constraint part of a create table statement to s.
214      */
215     protected void appendPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
216         s.append("CONSTRAINT ");
217         s.append(t.pkConstraintName);
218         s.append(" PRIMARY KEY (");
219         appendColumnNameList(t.pk, s);
220         s.append(')');
221     }
222
223     /**
224      * Hook for drivers that have to append a table type to the create table
225      * statement (e.g. MySQL).
226      */
227     protected void appendTableType(JdbcTable t, CharBuf s) {
228         s.append(" TYPE = InnoDB");
229     }
230
231     /**
232      * Hook for drivers that must create indexes in the create table
233      * statement (e.g. MySQL).
234      */
235     protected void appendIndexesInCreateTable(JdbcTable t, CharBuf s) {
236 // if (t.indexes == null) return;
237 // for (int i = 0; i < t.indexes.length; i++) {
238 // JdbcIndex idx = t.indexes[i];
239 // s.append(",\n ");
240 // if (idx.unique) {
241 // s.append("UNIQUE ");
242 // }else {
243 // s.append("INDEX ");
244 // }
245 // s.append(idx.name);
246 // s.append(' ');
247 // s.append('(');
248 // s.append(idx.cols[0].name);
249 // int n = idx.cols.length;
250 // for (int j = 1; j < n; j++) {
251 // s.append(',');
252 // s.append(' ');
253 // s.append(idx.cols[j].name);
254 // }
255 // s.append(')');
256 // }
257 }
258
259     /**
260      * Generate a 'create index' statement for idx.
261      */
262     protected void appendCreateIndex(CharBuf s, JdbcTable t, JdbcIndex idx,
263                                      boolean comments) {
264         if (comments && isCommentSupported() && idx.comment != null) {
265             s.append(comment(idx.comment));
266             s.append('\n');
267         }
268         s.append("ALTER TABLE ");
269         s.append(t.name);
270         if (idx.unique) {
271             s.append(" ADD UNIQUE ");
272         } else {
273             s.append(" ADD INDEX ");
274         }
275         s.append(idx.name);
276         s.append('(');
277         s.append(idx.cols[0].name);
278         int n = idx.cols.length;
279         for (int i = 1; i < n; i++) {
280             s.append(',');
281             s.append(' ');
282             s.append(idx.cols[i].name);
283         }
284         s.append(')');
285     }
286
287     /**
288      * Generate the 'add constraint' statements for t.
289      */
290     public void generateConstraints(JdbcTable t, Statement stat,
291                                     PrintWriter out, boolean comments)
292             throws SQLException {
293         if (!refConstraintsNotSupported) {
294             super.generateConstraints(t, stat, out, comments);
295         }
296     }
297
298     /**
299      * Append an 'add constraint' statement for c.
300      */
301     protected void appendRefConstraint(CharBuf s, JdbcConstraint c) {
302         s.append("ALTER TABLE ");
303         s.append(c.src.name);
304         s.append(" ADD CONSTRAINT ");
305         s.append(c.name);
306         s.append(" FOREIGN KEY (");
307         appendColumnNameList(c.srcCols, s);
308         s.append(") REFERENCES ");
309         s.append(c.dest.name);
310         s.append('(');
311         appendColumnNameList(c.dest.pk, s);
312         s.append(')');
313     }
314
315     /**
316      * Append the from list entry for a table that is the right hand table
317      * in a join i.e. it is being joined to.
318      *
319      * @param exp This is the expression that joins the tables
320      * @param outer If true then this is an outer join
321      */
322     public void appendSqlFromJoin(JdbcTable table, String alias, SqlExp exp,
323                                   boolean outer, CharBuf s) {
324         if (exp == null) {
325             s.append(" CROSS JOIN ");
326         } else if (outer) {
327             s.append(" LEFT JOIN ");
328         } else {
329             s.append(" INNER JOIN ");
330         }
331         s.append(table.name);
332         if (alias != null) {
333             s.append(" AS ");
334             s.append(alias);
335         }
336         if (exp != null) {
337             s.append(" ON (");
338             exp.appendSQL(this, s, null);
339             s.append(')');
340         }
341     }
342
343     /**
344      * Append a join expression.
345      */
346     public void appendSqlJoin(String leftAlias, JdbcColumn left,
347                               String rightAlias, JdbcColumn right, boolean outer,
348                               CharBuf s) {
349         s.append(leftAlias);
350         s.append('.');
351         s.append(left.name);
352         s.append(' ');
353         s.append('=');
354         s.append(' ');
355         s.append(rightAlias);
356         s.append('.');
357         s.append(right.name);
358     }
359
360     /**
361      * Does the JDBC driver support statement batching?
362      */
363     public boolean isInsertBatchingSupported() {
364         return true;
365     }
366
367     /**
368      * Does the JDBC driver support statement batching for updates?
369      */
370     public boolean isUpdateBatchingSupported() {
371         return true;
372     }
373
374     /**
375      * Does the JDBC driver support scrollable result sets?
376      */
377     public boolean isScrollableResultSetSupported() {
378         return true;
379     }
380
381     /**
382      * Does this driver use the ANSI join syntax (i.e. the join clauses appear
383      * in the from list e.g. postgres)?
384      */
385     public boolean isAnsiJoinSyntax() {
386         return true;
387     }
388
389     /**
390      * Must 'exists (select ...)' clauses be converted into a join and
391      * distinct be added to the select (e.g. MySQL) ?
392      */
393     public boolean isConvertExistsToDistinctJoin() {
394         return true;
395     }
396
397     /**
398      * Does the LIKE operator only support literal string and column
399      * arguments (e.g. Informix)?
400      */
401     public boolean isLikeStupid() {
402         return true;
403     }
404
405     /**
406      * Must add expressions (+, -, string concat) be wrapped in brackets?
407      */
408     public boolean isExtraParens() {
409         return true;
410     }
411
412     /**
413      * Append the allow nulls part of the definition for a column in a
414      * create table statement.
415      */
416     protected void appendCreateColumnNulls(JdbcTable t, JdbcColumn c,
417                                            CharBuf s) {
418         if (c.nulls) {
419             s.append(" NULL");
420         } else {
421             s.append(" NOT NULL");
422         }
423     }
424
425     /**
426      * Get default SQL to test a connection or null if none available. This
427      * must be a query that returns at least one row.
428      */
429     public String getConnectionValidateSQL() {
430         return "SELECT version()";
431     }
432
433     /**
434      * Does this database support autoincrement or serial columns?
435      */
436     public boolean isAutoIncSupported() {
437         return true;
438     }
439
440     /**
441      * Append the column auto increment part of a create table statement for a
442      * column.
443      */
444     protected void appendCreateColumnAutoInc(JdbcTable t, JdbcColumn c,
445                                              CharBuf s) {
446         s.append(" AUTO_INCREMENT");
447     }
448
449     /**
450      * Retrieve the value of the autoinc or serial column for a row just
451      * inserted using stat on con.
452      */
453     public Object getAutoIncColumnValue(JdbcTable classTable,
454             Connection con, Statement stat) throws SQLException {
455         long id = ((com.mysql.jdbc.Statement)stat).getLastInsertID();
456         switch (classTable.pk[0].javaTypeCode) {
457             case MDStatics.BYTE:
458             case MDStatics.BYTEW:
459                 return new Byte ((byte) id);
460             case MDStatics.SHORT:
461             case MDStatics.SHORTW:
462                 return new Short ((short) id);
463             case MDStatics.LONG:
464             case MDStatics.LONGW:
465                 return new Long (id);
466             case MDStatics.BIGDECIMAL:
467                 return new BigDecimal (id);
468             case MDStatics.BIGINTEGER:
469                 return new BigInteger (Long.toString(id));
470         }
471         return new Integer ((int) id);
472     }
473
474     public boolean checkDDL(ArrayList tables, Connection con,
475                             PrintWriter errors, PrintWriter fix, ControlParams params)
476             throws SQLException {
477         if (refConstraintsNotSupported) {
478             params.setCheckConstraint(false);
479         }
480         return super.checkDDL(tables, con, errors, fix, params);
481     }
482
483     protected String getCatalog(Connection con) throws SQLException {
484         String catalog = null;
485         Statement stat = null;
486         ResultSet rs = null;
487
488         try {
489             stat = con.createStatement();
490             rs = stat.executeQuery("SELECT DATABASE()");
491             if (rs.next()) {
492                 catalog = rs.getString(1);
493             }
494         } finally {
495             if (rs != null) {
496                 try {
497                     rs.close();
498                 } catch (SQLException e) {
499                 }
500             }
501             if (stat != null) {
502                 try {
503                     stat.close();
504                 } catch (SQLException e) {
505                 }
506             }
507         }
508
509         return catalog;
510     } /*
511
512
513 ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification ...]
514
515 alter_specification:
516 ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
517 | ADD [COLUMN] (create_definition, create_definition,...)
518 | ADD INDEX [index_name] (index_col_name,...)
519 | ADD PRIMARY KEY (index_col_name,...)
520 | ADD UNIQUE [index_name] (index_col_name,...)
521 | ADD FULLTEXT [index_name] (index_col_name,...)
522 | ADD [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
523 [reference_definition]
524 | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
525 | CHANGE [COLUMN] old_col_name create_definition
526 [FIRST | AFTER column_name]
527 | MODIFY [COLUMN] create_definition [FIRST | AFTER column_name]
528 | DROP [COLUMN] col_name
529 | DROP PRIMARY KEY
530 | DROP INDEX index_name
531 | DISABLE KEYS
532 | ENABLE KEYS
533 | RENAME [TO] new_tbl_name
534 | ORDER BY col
535 | table_options
536
537     */
538
539     /**
540      * Add a Sequence column to implement a list
541      * <p/>
542      * <p/>
543      * <p/>
544      * /**
545      * Append a column that needs to be added.
546      */
547     protected void appendAddNewColumn(JdbcTable t, JdbcColumn c,
548                                       CharBuf s, boolean comments) {
549         if (comments && isCommentSupported() && c.comment != null) {
550             s.append(comment("add column for field " + c.comment));
551         }
552
553         s.append("\n");
554         if (isAddSequenceColumn(c)) {
555             addSequenceColumn(t, c, s, comments);
556         } else {
557             s.append("ALTER TABLE ");
558             s.append(t.name);
559             s.append(" ADD COLUMN ");
560             s.append(c.name);
561             s.append(' ');
562             appendColumnType(c, s);
563             s.append(" NULL");
564             if (c.autoinc) {
565                 appendCreateColumnAutoInc(t, c, s);
566             }
567             s.append(getRunCommand());
568             if (!c.nulls) {
569                 s.append("UPDATE ");
570                 s.append(t.name);
571                 s.append(" SET ");
572                 s.append(c.name);
573                 s.append(" = ");
574                 s.append(getDefaultForType(c));
575                 s.append(getRunCommand());
576
577                 s.append("ALTER TABLE ");
578                 s.append(t.name);
579                 s.append(" CHANGE COLUMN ");
580                 s.append(c.name);
581                 s.append(' ');
582                 s.append(c.name);
583                 s.append(' ');
584                 appendColumnType(c, s);
585                 appendCreateColumnNulls(t, c, s);
586                 if (c.autoinc) {
587                     appendCreateColumnAutoInc(t, c, s);
588                 }
589                 s.append(getRunCommand());
590             }
591         }
592
593     }
594
595     /**
596      * Append a column that needs to be added.
597      */
598     protected void appendModifyColumn(TableDiff tableDiff, ColumnDiff diff,
599                                       CharBuf s, boolean comments) {
600         JdbcTable t = tableDiff.getOurTable();
601         JdbcColumn c = diff.getOurCol();
602         if (comments && isCommentSupported() && c.comment != null) {
603             s.append(comment("modify column for field " + c.comment));
604         }
605         if (comments && isCommentSupported() && c.comment == null) {
606             s.append(comment("modify column " + c.name));
607         }
608
609         s.append("\n");
610         s.append("ALTER TABLE ");
611         s.append(t.name);
612         s.append(" CHANGE COLUMN ");
613         s.append(c.name);
614         s.append(' ');
615         s.append(c.name);
616         s.append(' ');
617         appendColumnType(c, s);
618         appendCreateColumnNulls(t, c, s);
619         if (c.autoinc) {
620             appendCreateColumnAutoInc(t, c, s);
621         }
622
623     }
624
625     /**
626      * Append a column that needs to be added.
627      */
628     protected void appendDropColumn(TableDiff tableDiff, JdbcColumn c,
629                                     CharBuf s, boolean comments) {
630         if (comments && isCommentSupported()) {
631             s.append(comment("dropping unknown column " + c.name));
632         }
633         s.append("\n");
634         if (isDropSequenceColumn(tableDiff, c)) {
635             dropSequenceColumn(tableDiff.getOurTable(), c, s, comments);
636         } else {
637             s.append("ALTER TABLE ");
638             s.append(tableDiff.getOurTable().name);
639             s.append(" DROP COLUMN ");
640             s.append(c.name);
641         }
642     }
643
644     /**
645      * Append an 'drop constraint' statement for c.
646      */
647     protected void appendRefDropConstraint(CharBuf s, JdbcConstraint c,
648                                            boolean comments) {
649 // if (comments && isCommentSupported()) {
650 // s.append(comment("dropping unknown constraint " + c.name));
651 // s.append('\n');
652 // }
653 s.append("ALTER TABLE ");
654         s.append(c.src.name);
655         s.append(" DROP CONSTRAINT ");
656         s.append(c.name);
657     }
658
659     /**
660      * Generate a 'drop index' statement for idx.
661      */
662     protected void appendDropIndex(CharBuf s, JdbcTable t, JdbcIndex idx,
663                                    boolean comments) {
664 // if (comments && isCommentSupported()) {
665 // s.append(comment("dropping unknown index "+ idx.name));
666 // s.append('\n');
667 // }
668 s.append("ALTER TABLE ");
669         s.append(t.name);
670         s.append(" DROP INDEX ");
671         s.append(idx.name);
672     }
673
674     /**
675      * Add the primary key constraint in isolation.
676      */
677     protected void addPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
678         s.append("ALTER TABLE ");
679         s.append(t.name);
680         s.append(" ADD PRIMARY KEY (");
681         appendColumnNameList(t.pk, s);
682         s.append(')');
683     }
684
685     /**
686      * Drop the primary key constraint in isolation.
687      */
688     protected void dropPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
689         s.append("ALTER TABLE ");
690         s.append(t.name);
691         s.append(" DROP PRIMARY KEY");
692     }
693
694     /**
695      * Drop a Sequence column to implement a Set
696      */
697     protected void dropSequenceColumn(JdbcTable t, JdbcColumn c, CharBuf s,
698                                       boolean comments) {
699         String tempTableName = getTempTableName(t, 64);
700
701         s.append(comment("create a temp table to store old table values."));
702         s.append("\n");
703         s.append("CREATE TABLE ");
704         s.append(tempTableName);
705         s.append(" (\n");
706         JdbcColumn[] cols = t.getColsForCreateTable();
707         int nc = cols.length;
708         boolean first = true;
709         for (int i = 0; i < nc; i++) {
710             if (first) {
711                 first = false;
712             } else {
713                 s.append("\n");
714             }
715             s.append(" ");
716             appendCreateColumn(t, cols[i], s, comments);
717         }
718         s.append("\n ");
719         appendPrimaryKeyConstraint(t, s);
720         s.append("\n)");
721         appendTableType(t, s);
722         s.append(getRunCommand());
723
724         s.append(comment("insert a distinct list into the temp table."));
725         s.append("\n");
726         s.append("INSERT INTO ");
727         s.append(tempTableName);
728         s.append("(");
729         for (int i = 0; i < nc; i++) {
730             s.append(cols[i].name);
731             if ((i + 1) != nc) {
732                 s.append(", ");
733             }
734         }
735         s.append(")");
736         s.append("\nSELECT DISTINCT ");
737         for (int i = 0; i < nc; i++) {
738             if (i != 0) {
739                 s.append("\n ");
740             }
741             s.append(cols[i].name);
742             if ((i + 1) != nc) {
743                 s.append(", ");
744             }
745         }
746         s.append("\n FROM ");
747         s.append(t.name);
748
749         s.append(getRunCommand());
750
751         s.append(comment("drop main table."));
752         s.append("\n");
753         s.append("DROP TABLE ");
754         s.append(t.name);
755         s.append(getRunCommand());
756
757         s.append(comment("rename temp table to main table."));
758         s.append("\n");
759         s.append("ALTER TABLE ");
760         s.append(tempTableName);
761         s.append(" RENAME TO ");
762         s.append(t.name);
763
764     }
765
766     /**
767      * Add a Sequence column to implement a list
768      */
769     protected void addSequenceColumn(JdbcTable t, JdbcColumn c, CharBuf s,
770                                      boolean comments) {
771
772         String mainTempTableName = getTempTableName(t, 64);
773         String minTempTableName = getTempTableName(t, 64);
774         String identityColumnName = getTempColumnName(t);
775
776         JdbcColumn indexColumn = null;
777         JdbcColumn sequenceColumn = null;
778         JdbcColumn[] cols = t.getColsForCreateTable();
779         int nc = cols.length;
780         for (int i = 0; i < nc; i++) {
781             if (isAddSequenceColumn(cols[i])) {
782                 sequenceColumn = cols[i];
783             } else if (t.isInPrimaryKey(cols[i].name)) {
784                 indexColumn = cols[i];
785             }
786         }
787
788         s.append(comment("Generate a sequence number so that " +
789                 "we can implement a List."));
790         s.append("\n");
791         s.append(comment("create a temp table with a extra " +
792                 "identity column."));
793         s.append("\n");
794         s.append("CREATE TABLE ");
795         s.append(mainTempTableName);
796         s.append(" (\n ");
797         // create identity column
798 s.append(identityColumnName);
799         s.append(" BIGINT NOT NULL AUTO_INCREMENT,");
800         for (int i = 0; i < nc; i++) {
801             s.append("\n ");
802             appendCreateColumn(t, cols[i], s, comments);
803         }
804         s.append("\n CONSTRAINT ");
805         s.append(t.pkConstraintName);
806         s.append(" PRIMARY KEY (");
807         s.append(identityColumnName);
808         s.append(")\n)");
809
810         s.append(getRunCommand());
811
812         s.append(comment("insert a '0' in the sequence " +
813                 "column and copy the rest of the old table " +
814                 "into the temp table."));
815         s.append("\n");
816         s.append("INSERT INTO ");
817         s.append(mainTempTableName);
818         s.append("(");
819         for (int i = 0; i < nc; i++) {
820             s.append(cols[i].name);
821             if ((i + 1) != nc) {
822                 s.append(", ");
823             }
824         }
825         s.append(")");
826         s.append("\nSELECT ");
827         for (int i = 0; i < nc; i++) {
828             if (i != 0) {
829                 s.append("\n ");
830             }
831             if (isAddSequenceColumn(cols[i])) {
832                 s.append('0');
833             } else {
834                 s.append(cols[i].name);
835             }
836             if ((i + 1) != nc) {
837                 s.append(", ");
838             }
839         }
840         s.append("\n FROM ");
841         s.append(t.name);
842         s.append("\n ORDER BY ");
843         s.append(indexColumn.name);
844
845         s.append(getRunCommand());
846
847         s.append(comment("create a temp table to store the minimum id."));
848         s.append("\n");
849         s.append("CREATE TABLE ");
850         s.append(minTempTableName);
851         s.append(" (\n ");
852         s.append(indexColumn.name);
853         s.append(' ');
854         appendColumnType(indexColumn, s);
855         appendCreateColumnNulls(t, indexColumn, s);
856         s.append(",\n ");
857         s.append("min_id");
858         s.append(" INTEGER\n)");
859
860         s.append(getRunCommand());
861
862         s.append(comment("store the minimum id."));
863         s.append("\n");
864         s.append("INSERT INTO ");
865         s.append(minTempTableName);
866         s.append(" (");
867         s.append(indexColumn.name);
868         s.append(", ");
869         s.append("min_id");
870         s.append(")\n");
871         s.append("SELECT ");
872         s.append(indexColumn.name);
873         s.append(",\n ");
874         s.append("MIN(");
875         s.append(identityColumnName);
876         s.append(")\n");
877         s.append(" FROM ");
878         s.append(mainTempTableName);
879         s.append("\n");
880         s.append(" GROUP BY ");
881         s.append(indexColumn.name);
882
883         s.append(getRunCommand());
884
885         s.append(comment("drop main table " + t.name + "."));
886         s.append("\n");
887         s.append("DROP TABLE ");
888         s.append(t.name);
889
890         s.append(getRunCommand());
891
892         s.append(comment("recreate table " + t.name + "."));
893         s.append("\n");
894         s.append("CREATE TABLE ");
895         s.append(t.name);
896         s.append(" (\n");
897         boolean first = true;
898         for (int i = 0; i < nc; i++) {
899             if (first) {
900                 first = false;
901             } else {
902                 s.append("\n");
903             }
904             s.append(" ");
905             appendCreateColumn(t, cols[i], s, comments);
906         }
907         s.append("\n ");
908         appendPrimaryKeyConstraint(t, s);
909         s.append("\n)");
910         appendTableType(t, s);
911
912         s.append(getRunCommand());
913
914         s.append(comment("populate table " + t.name +
915                 " with the new sequence column."));
916         s.append("\n");
917         s.append("INSERT INTO ");
918         s.append(t.name);
919         s.append("(");
920         for (int i = 0; i < nc; i++) {
921             s.append(cols[i].name);
922             if ((i + 1) != nc) {
923                 s.append(", ");
924             }
925         }
926         s.append(")");
927         s.append("\nSELECT ");
928         for (int i = 0; i < nc; i++) {
929             if (i != 0) {
930                 s.append("\n ");
931             }
932
933             if (isAddSequenceColumn(cols[i])) {
934                 s.append("(a.");
935                 s.append(identityColumnName);
936                 s.append(" - b.min_id)");
937             } else {
938                 s.append("a.");
939                 s.append(cols[i].name);
940             }
941
942             if ((i + 1) != nc) {
943                 s.append(", ");
944             }
945         }
946         s.append("\n FROM ");
947         s.append(mainTempTableName);
948         s.append(" a,\n ");
949         s.append(minTempTableName);
950         s.append(" b\n WHERE a.");
951         s.append(indexColumn.name);
952         s.append(" = b.");
953         s.append(indexColumn.name);
954
955         s.append(getRunCommand());
956
957         s.append(comment("drop temp tables."));
958         s.append("\n");
959         s.append("DROP TABLE ");
960         s.append(mainTempTableName);
961         s.append(getRunCommand());
962
963         s.append("DROP TABLE ");
964         s.append(minTempTableName);
965         s.append(getRunCommand());
966     }
967
968 }
969

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags