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