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.jdbc.metadata.*;
014: import com.versant.core.jdbc.sql.exp.SqlExp;
015: import com.versant.core.jdbc.sql.conv.DateTimestampConverter;
016: import com.versant.core.jdbc.sql.diff.TableDiff;
017: import com.versant.core.jdbc.sql.diff.ColumnDiff;
018: import com.versant.core.jdbc.sql.diff.ControlParams;
019: import com.versant.core.util.CharBuf;
020:
021: import java.io.PrintWriter;
022: import java.sql.*;
023: import java.util.HashMap;
024: import java.util.Date;
025: import java.util.ArrayList;
026: import java.util.Iterator;
027:
028: import com.versant.core.util.CharBuf;
029:
030: /**
031: * A driver for Hypersonic SQL.
032: */
033: public class HypersonicSqlDriver extends SqlDriver {
034:
035: /**
036: * Get the name of this driver.
037: */
038: public String getName() {
039: return "hypersonic";
040: }
041:
042: /**
043: * Get the default type mapping for the supplied JDBC type code from
044: * java.sql.Types or null if the type is not supported. There is no
045: * need to set the database or jdbcType on the mapping as this is done
046: * after this call returns. Subclasses should override this and to
047: * customize type mappings.
048: */
049: protected JdbcTypeMapping getTypeMapping(int jdbcType) {
050: switch (jdbcType) {
051: case Types.LONGVARBINARY:
052: case Types.BLOB:
053: return new JdbcTypeMapping("LONGVARBINARY", 0, 0,
054: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
055: bytesConverterFactory);
056: case Types.CLOB:
057: case Types.LONGVARCHAR:
058: return new JdbcTypeMapping("LONGVARCHAR", 0, 0,
059: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE, null);
060: }
061: return super .getTypeMapping(jdbcType);
062: }
063:
064: /**
065: * Get the default field mappings for this driver. These map java classes
066: * to column properties. Subclasses should override this, call super() and
067: * replace mappings as needed.
068: */
069: public HashMap getJavaTypeMappings() {
070: HashMap ans = super .getJavaTypeMappings();
071:
072: DateTimestampConverter.Factory dtcf = new DateTimestampConverter.Factory();
073: ((JdbcJavaTypeMapping) ans.get(Date.class))
074: .setConverterFactory(dtcf);
075:
076: return ans;
077: }
078:
079: /**
080: * Generate a 'create table' statement for t.
081: */
082: public void generateCreateTable(JdbcTable t, Statement stat,
083: PrintWriter out, boolean comments) throws SQLException {
084: CharBuf s = new CharBuf();
085: if (comments && isCommentSupported() && t.comment != null) {
086: s.append(comment(t.comment));
087: s.append('\n');
088: }
089: s.append("CREATE CACHED TABLE ");
090: s.append(t.name);
091: s.append(" (\n");
092: JdbcColumn[] cols = t.getColsForCreateTable();
093: int nc = cols.length;
094: boolean first = true;
095: for (int i = 0; i < nc; i++) {
096: if (first) {
097: first = false;
098: } else {
099: s.append("\n");
100: }
101: s.append(" ");
102: appendCreateColumn(t, cols[i], s, comments);
103: }
104: s.append("\n ");
105: appendPrimaryKeyConstraint(t, s);
106: appendIndexesInCreateTable(t, s);
107: s.append("\n)");
108: appendTableType(t, s);
109: String sql = s.toString();
110: if (out != null)
111: print(out, sql);
112: if (stat != null)
113: stat.execute(sql);
114: }
115:
116: /**
117: * Can the tx isolation level be set on this database?
118: */
119: public boolean isSetTransactionIsolationLevelSupported() {
120: return false;
121: }
122:
123: /**
124: * Does this driver use the ANSI join syntax (i.e. the join clauses appear
125: * in the from list e.g. postgres)?
126: */
127: public boolean isAnsiJoinSyntax() {
128: return true;
129: }
130:
131: /**
132: * May the ON clauses for joins in a subquery reference columns from the
133: * enclosing query? DB2 does not allow this.
134: */
135: public boolean isSubQueryJoinMayUseOuterQueryCols() {
136: return false;
137: }
138:
139: /**
140: * Is null a valid value for a column with a foreign key constraint?
141: */
142: public boolean isNullForeignKeyOk() {
143: return true;
144: }
145:
146: /**
147: * Get whatever needs to be appended to a SELECT statement to lock the
148: * rows if this makes sense for the database. This must have a leading
149: * space if not empty.
150: */
151: public char[] getSelectForUpdate() {
152: return null;
153: }
154:
155: /**
156: * Does the JDBC driver support Statement.setFetchSize()?
157: */
158: public boolean isFetchSizeSupported() {
159: return false;
160: }
161:
162: /**
163: * Should indexes be used for columns in the order by list that are
164: * also in the select list? This is used for databases that will not
165: * order by a column that is duplicated in the select list (e.g. Oracle).
166: */
167: public boolean isUseIndexesForOrderCols() {
168: return true;
169: }
170:
171: /**
172: * Does the LIKE operator only support literal string and column
173: * arguments (e.g. Informix)?
174: */
175: public boolean isLikeStupid() {
176: return true;
177: }
178:
179: /**
180: * Should PreparedStatement pooling be used for this database and
181: * JDBC driver?
182: */
183: public boolean isPreparedStatementPoolingOK() {
184: return false;
185: }
186:
187: /**
188: * Does the JDBC driver support statement batching for inserts?
189: */
190: public boolean isInsertBatchingSupported() {
191: return true;
192: }
193:
194: /**
195: * Does the JDBC driver support statement batching for updates?
196: */
197: public boolean isUpdateBatchingSupported() {
198: return true;
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(31);
208: n.setMaxTableNameLength(31);
209: n.setMaxConstraintNameLength(31);
210: n.setMaxIndexNameLength(31);
211: return n;
212: }
213:
214: /**
215: * Append the allow nulls part of the definition for a column in a
216: * create table statement.
217: */
218: protected void appendCreateColumnNulls(JdbcTable t, JdbcColumn c,
219: CharBuf s) {
220: if (c.nulls) {
221: s.append(" NULL");
222: } else {
223: s.append(" NOT NULL");
224: }
225: }
226:
227: /**
228: * Add the primary key constraint part of a create table statement to s.
229: */
230: protected void appendPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
231: s.append("CONSTRAINT ");
232: s.append(t.pkConstraintName);
233: s.append(" PRIMARY KEY (");
234: appendColumnNameList(t.pk, s);
235: s.append(')');
236: }
237:
238: public void generateConstraints(JdbcTable t, Statement stat,
239: PrintWriter out, boolean comments) throws SQLException {
240: // current hypersonic release corrupts the database if there are
241: // ref constraints
242: }
243:
244: public boolean checkDDL(ArrayList tables, Connection con,
245: PrintWriter errors, PrintWriter fix, ControlParams params)
246: throws SQLException {
247: // current hypersonic release corrupts the database if there are
248: // ref constraints
249: params.setCheckConstraint(false);
250: return super .checkDDL(tables, con, errors, fix, params);
251: }
252:
253: /**
254: * Append an 'add constraint' statement for c.
255: // current hypersonic release corrupts the database if there are
256: // ref constraints
257: protected void appendRefConstraint(CharBuf s, JdbcConstraint c) {
258: s.append("ALTER TABLE ");
259: s.append(c.src.name);
260: s.append(" ADD CONSTRAINT ");
261: s.append(c.name);
262: s.append(" FOREIGN KEY (");
263: appendColumnNameList(c.srcCols, s);
264: s.append(") REFERENCES ");
265: s.append(c.dest.name);
266: s.append('(');
267: appendColumnNameList(c.dest.pk, s);
268: s.append(')');
269: }
270: */
271:
272: /**
273: * Write an SQL statement to a script with appropriate separator.
274: */
275: protected void print(PrintWriter out, String sql) {
276: out.print(sql);
277: out.println(";");
278: out.println();
279: }
280:
281: /**
282: * Append the from list entry for a table.
283: */
284: public void appendSqlFrom(JdbcTable table, String alias, CharBuf s) {
285: s.append(table.name);
286: if (alias != null) {
287: s.append(" AS ");
288: s.append(alias);
289: }
290: }
291:
292: protected boolean isValidSchemaTable(String tableName) {
293: if (tableName.startsWith("SYSTEM_")) {
294: return false;
295: }
296: return true;
297: }
298:
299: /**
300: * Append the from list entry for a table that is the right hand table
301: * in a join i.e. it is being joined to.
302: *
303: * @param exp This is the expression that joins the tables
304: * @param outer If true then this is an outer join
305: */
306: public void appendSqlFromJoin(JdbcTable table, String alias,
307: SqlExp exp, boolean outer, CharBuf s) {
308: if (outer) {
309: s.append(" LEFT JOIN ");
310: } else {
311: s.append(" JOIN ");
312: }
313: s.append(table.name);
314: if (alias != null) {
315: s.append(" AS ");
316: s.append(alias);
317: }
318: if (exp != null) {
319: s.append(" ON (");
320: exp.appendSQL(this , s, null);
321: s.append(')');
322: }
323: }
324:
325: /**
326: * Drop the table and all its constraints etc. This must remove
327: * constraints to this table from other tables so it can be dropped.
328: */
329: public void dropTable(Connection con, String table, Statement stat)
330: throws SQLException {
331: ResultSet rs = null;
332: try {
333: stat = con.createStatement();
334: rs = stat.executeQuery("SELECT FKTABLE_NAME,FK_NAME\n"
335: + " FROM SYSTEM_CROSSREFERENCE\n"
336: + " WHERE PKTABLE_NAME = '"
337: + table.toUpperCase().trim() + "'");
338: ArrayList a = new ArrayList();
339: for (; rs.next();) {
340: String tableName = rs.getString(1);
341: String conName = rs.getString(2);
342: a.add("ALTER TABLE " + tableName + " DROP CONSTRAINT "
343: + conName);
344:
345: }
346: rs.close();
347: for (Iterator i = a.iterator(); i.hasNext();) {
348: String sql = (String) i.next();
349: try {
350: stat.execute(sql);
351: } catch (SQLException e) {
352: /*some times it's a bit slow to update it's system tables and we get a exeption*/
353: }
354: }
355: stat.execute("DROP TABLE " + table);
356: } finally {
357: if (rs != null) {
358: try {
359: rs.close();
360: } catch (SQLException x) {
361: // ignore
362: }
363: }
364: }
365: }
366:
367: boolean isDirectTypeColumnChangesSupported(JdbcColumn ourCol,
368: JdbcColumn dbCol) {
369: return false;
370: }
371:
372: boolean isDirectNullColumnChangesSupported() {
373: return false;
374: }
375:
376: boolean isDirectScaleColumnChangesSupported(JdbcColumn ourCol,
377: JdbcColumn dbCol) {
378: return false;
379: }
380:
381: boolean isDirectLenghtColumnChangesSupported(JdbcColumn ourCol,
382: JdbcColumn dbCol) {
383: return false;
384: }
385:
386: boolean isDropPrimaryKeySupported() {
387: return false;
388: }
389:
390: boolean isDropConstraintsForDropTableSupported() {
391: return false;
392: }
393:
394: /**
395: * Append a column that needs to be added.
396: */
397: protected void appendAddNewColumn(JdbcTable t, JdbcColumn c,
398: CharBuf s, boolean comments) {
399: if (comments && isCommentSupported() && c.comment != null) {
400: s.append(comment("add column for field " + c.comment));
401: }
402:
403: s.append("\n");
404: if (isAddSequenceColumn(c)) {
405: addSequenceColumn(t, c, s, comments);
406: } else {
407: s.append("ALTER TABLE ");
408: s.append(t.name);
409: s.append(" ADD COLUMN ");
410: s.append(c.name);
411: s.append(' ');
412: appendColumnType(c, s);
413: if (!c.nulls) {
414: s.append(" DEFAULT ");
415: String _default = getDefaultForType(c);
416: if (_default.startsWith("'")) {
417: s.append(_default);
418: } else {
419: s.append("'");
420: s.append(_default);
421: s.append("'");
422: }
423: s.append(" NOT NULL");
424: }
425: s.append(getRunCommand());
426: }
427: }
428:
429: /**
430: * Append a column that needs to be added.
431: */
432: protected void appendDropColumn(TableDiff tableDiff, JdbcColumn c,
433: CharBuf s, boolean comments) {
434: if (comments && isCommentSupported()) {
435: s.append(comment("dropping unknown column " + c.name));
436: }
437: s.append("\n");
438: if (isDropSequenceColumn(tableDiff, c)) {
439: dropSequenceColumn(tableDiff.getOurTable(), c, s, comments);
440: } else {
441: s.append("ALTER TABLE ");
442: s.append(tableDiff.getOurTable().name);
443: s.append(" DROP COLUMN ");
444: s.append(c.name);
445: }
446: }
447:
448: /**
449: * Append an 'drop constraint' statement for c.
450: */
451: protected void appendRefDropConstraint(CharBuf s, JdbcConstraint c,
452: boolean comments) {
453: s.append("ALTER TABLE ");
454: s.append(c.src.name);
455: s.append(" DROP CONSTRAINT ");
456: s.append(c.name);
457: }
458:
459: /**
460: * Generate a 'drop index' statement for idx.
461: */
462: protected void appendDropIndex(CharBuf s, JdbcTable t,
463: JdbcIndex idx, boolean comments) {
464: s.append("DROP INDEX ");
465: s.append(idx.name);
466: }
467:
468: protected void fixColumnsNonDirect(TableDiff tableDiff,
469: PrintWriter out) {
470: JdbcTable ourTable = tableDiff.getOurTable();
471: String tempTableName = getTempTableName(ourTable, 31);
472:
473: CharBuf s = new CharBuf();
474: s.append("CREATE CACHED TABLE ");
475: s.append(tempTableName); //ourTable.name
476: s.append(" (");
477: JdbcColumn[] cols = ourTable.getColsForCreateTable();
478: int nc = cols.length;
479: for (int i = 0; i < nc; i++) {
480: s.append("\n ");
481: appendCreateColumn(ourTable, cols[i], s, false);
482: }
483: s.append("\n ");
484: appendPrimaryKeyConstraint(ourTable, s);
485: s.append(")");
486: s.append(getRunCommand());
487:
488: s.append("INSERT INTO ");
489: s.append(tempTableName); //ourTable.name
490: s.append(" (");
491: for (int i = 0; i < nc; i++) {
492: s.append(cols[i].name);
493: if ((i + 1) != nc) {
494: s.append(", ");
495: }
496: }
497: s.append(") ");
498:
499: s.append("\n");//new line
500:
501: s.append("SELECT ");
502: for (int i = 0; i < nc; i++) {
503: ColumnDiff diff = getColumnDiffForName(tableDiff,
504: cols[i].name);
505: if (diff == null) {
506: if (i != 0) {
507: s.append(" ");
508: }
509: s.append(cols[i].name);
510: } else {
511: if (diff.isMissingCol()) {
512: if (diff.getOurCol().nulls) {
513: if (i != 0) {
514: s.append(" ");
515: }
516: s.append("CAST( NULL AS ");
517: appendColumnType(cols[i], s);
518: s.append(")");
519:
520: } else {
521: if (i != 0) {
522: s.append(" ");
523: }
524: s.append(getDefaultForType(diff.getOurCol()));
525: }
526:
527: } else if ((diff.isLenghtDiff() || diff.isScaleDiff() || diff
528: .isTypeDiff())
529: && diff.isNullDiff()) {
530: if (cols[i].nulls) {
531: if (i != 0) {
532: s.append(" ");
533: }
534: s.append("CAST(");
535: s.append(cols[i].name);
536: s.append(" AS ");
537: appendColumnType(cols[i], s);
538: s.append(")");
539: } else {
540: if (i != 0) {
541: s.append(" ");
542: }
543: /* IFNULL(exp,value) (if exp is null, value is returned else exp) */
544:
545: s.append("CAST( ");
546: s.append("IFNULL(");
547: s.append(cols[i].name);
548: s.append(",");
549: s.append(getDefaultForType(diff.getOurCol()));
550: s.append(") AS ");
551: appendColumnType(cols[i], s);
552: s.append(")");
553: }
554:
555: } else if ((diff.isLenghtDiff() || diff.isScaleDiff() || diff
556: .isTypeDiff())
557: && !diff.isNullDiff()) {
558: if (i != 0) {
559: s.append(" ");
560: }
561: s.append("CAST(");
562: s.append(cols[i].name);
563: s.append(" AS ");
564: appendColumnType(cols[i], s);
565: s.append(")");
566: } else if (diff.isNullDiff()) {
567: if (cols[i].nulls) {
568: if (i != 0) {
569: s.append(" ");
570: }
571: s.append(cols[i].name);
572: } else {
573: if (i != 0) {
574: s.append(" ");
575: }
576: s.append("IFNULL(");
577: s.append(cols[i].name);
578: s.append(",");
579: s.append(getDefaultForType(diff.getOurCol()));
580: s.append(")");
581: }
582: }
583: }
584:
585: if ((i + 1) != nc) {
586: s.append(", ");
587: s.append("\n");//new line
588: }
589: }
590: s.append("\n");//new line
591: s.append(" FROM ");
592: s.append(ourTable.name);
593: s.append(getRunCommand());
594:
595: s.append("DROP TABLE ");
596: s.append(ourTable.name);
597: s.append(getRunCommand());
598:
599: s.append("ALTER TABLE ");
600: s.append(tempTableName);
601: s.append(" RENAME TO ");
602: s.append(ourTable.name);
603: s.append(getRunCommand());
604:
605: out.println(s.toString());
606: }
607:
608: /**
609: * Drop a Sequence column to implement a Set
610: */
611: protected void dropSequenceColumn(JdbcTable t, JdbcColumn c,
612: CharBuf s, boolean comments) {
613: String tempTableName = getTempTableName(t, 31);
614:
615: s
616: .append(comment("create a temp table to store old table values."));
617: s.append("\n");
618:
619: s.append("CREATE CACHED TABLE ");
620: s.append(tempTableName);
621: s.append(" (\n");
622: JdbcColumn[] cols = t.getColsForCreateTable();
623: int nc = cols.length;
624: boolean first = true;
625: for (int i = 0; i < nc; i++) {
626: if (first) {
627: first = false;
628: } else {
629: s.append("\n");
630: }
631: s.append(" ");
632: appendCreateColumn(t, cols[i], s, comments);
633: }
634: s.append("\n ");
635: appendPrimaryKeyConstraint(t, s);
636: s.append("\n)");
637: s.append(getRunCommand());
638:
639: s
640: .append(comment("insert a distinct list into the temp table."));
641: s.append("\n");
642: s.append("INSERT INTO ");
643: s.append(tempTableName);
644: s.append("(");
645: for (int i = 0; i < nc; i++) {
646: s.append(cols[i].name);
647: if ((i + 1) != nc) {
648: s.append(", ");
649: }
650: }
651: s.append(")");
652: s.append("\nSELECT DISTINCT ");
653: for (int i = 0; i < nc; i++) {
654: if (i != 0) {
655: s.append("\n ");
656: }
657: s.append(cols[i].name);
658: if ((i + 1) != nc) {
659: s.append(", ");
660: }
661: }
662: s.append("\n FROM ");
663: s.append(t.name);
664:
665: s.append(getRunCommand());
666:
667: s.append(comment("drop main table."));
668: s.append("\n");
669: s.append("DROP TABLE ");
670: s.append(t.name);
671: s.append(getRunCommand());
672:
673: s.append(comment("rename temp table to main table."));
674: s.append("\n");
675: s.append("ALTER TABLE ");
676: s.append(tempTableName);
677: s.append(" RENAME TO ");
678: s.append(t.name);
679:
680: }
681:
682: /**
683: * Add a Sequence column to implement a list
684: */
685: protected void addSequenceColumn(JdbcTable t, JdbcColumn c,
686: CharBuf s, boolean comments) {
687:
688: String mainTempTableName = getTempTableName(t, 31);
689: String minTempTableName = getTempTableName(t, 31);
690: String identityColumnName = getTempColumnName(t);
691:
692: JdbcColumn indexColumn = null;
693: JdbcColumn sequenceColumn = null;
694: JdbcColumn[] cols = t.getColsForCreateTable();
695: int nc = cols.length;
696: for (int i = 0; i < nc; i++) {
697: if (isAddSequenceColumn(cols[i])) {
698: sequenceColumn = cols[i];
699: } else if (t.isInPrimaryKey(cols[i].name)) {
700: indexColumn = cols[i];
701: }
702: }
703:
704: s
705: .append(comment("Generate a sequence number so that we can implement a List."));
706: s.append("\n");
707: s
708: .append(comment("create a temp table with a extra identity column."));
709: s.append("\n");
710: s.append("CREATE CACHED TABLE ");
711: s.append(mainTempTableName);
712: s.append(" (\n ");
713: // create identity column
714: s.append(identityColumnName);
715: s.append(" IDENTITY,");
716: for (int i = 0; i < nc; i++) {
717: s.append("\n ");
718: appendCreateColumn(t, cols[i], s, comments);
719: }
720: int lastIndex = s.toString().lastIndexOf(',');
721: s.replace(lastIndex, lastIndex + 1, ' ');// we take the last ',' out.
722: s.append("\n)");
723:
724: s.append(getRunCommand());
725:
726: s
727: .append(comment("insert a '0' in the sequence column and copy the rest of the old table into the temp table."));
728: s.append("\n");
729: s.append("INSERT INTO ");
730: s.append(mainTempTableName);
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 ");
740: for (int i = 0; i < nc; i++) {
741: if (i != 0) {
742: s.append("\n ");
743: }
744: if (isAddSequenceColumn(cols[i])) {
745: s.append('0');
746: } else {
747: s.append(cols[i].name);
748: }
749: if ((i + 1) != nc) {
750: s.append(", ");
751: }
752: }
753: s.append("\n FROM ");
754: s.append(t.name);
755: s.append("\n ORDER BY ");
756: s.append(indexColumn.name);
757:
758: s.append(getRunCommand());
759:
760: s
761: .append(comment("create a temp table to store the minimum id."));
762: s.append("\n");
763: s.append("CREATE CACHED TABLE ");
764: s.append(minTempTableName);
765: s.append(" (\n ");
766: s.append(indexColumn.name);
767: s.append(' ');
768: appendColumnType(indexColumn, s);
769: appendCreateColumnNulls(t, indexColumn, s);
770: s.append(",\n ");
771: s.append("min_id");
772: s.append(" INTEGER\n)");
773:
774: s.append(getRunCommand());
775:
776: s.append(comment("store the minimum id."));
777: s.append("\n");
778: s.append("INSERT INTO ");
779: s.append(minTempTableName);
780: s.append(" (");
781: s.append(indexColumn.name);
782: s.append(", ");
783: s.append("min_id");
784: s.append(")\n");
785: s.append("SELECT ");
786: s.append(indexColumn.name);
787: s.append(",\n ");
788: s.append("MIN(");
789: s.append(identityColumnName);
790: s.append(")\n");
791: s.append(" FROM ");
792: s.append(mainTempTableName);
793: s.append("\n");
794: s.append(" GROUP BY ");
795: s.append(indexColumn.name);
796:
797: s.append(getRunCommand());
798:
799: s.append(comment("drop main table " + t.name + "."));
800: s.append("\n");
801: s.append("DROP TABLE ");
802: s.append(t.name);
803:
804: s.append(getRunCommand());
805:
806: s.append(comment("recreate table " + t.name + "."));
807: s.append("\n");
808: s.append("CREATE TABLE ");
809: s.append(t.name);
810: s.append(" (\n");
811: boolean first = true;
812: for (int i = 0; i < nc; i++) {
813: if (first) {
814: first = false;
815: } else {
816: s.append("\n");
817: }
818: s.append(" ");
819: appendCreateColumn(t, cols[i], s, comments);
820: }
821: s.append("\n ");
822: appendPrimaryKeyConstraint(t, s);
823: s.append("\n)");
824: appendTableType(t, s);
825:
826: s.append(getRunCommand());
827:
828: s.append(comment("populate table " + t.name
829: + " with the new sequence column."));
830: s.append("\n");
831: s.append("INSERT INTO ");
832: s.append(t.name);
833: s.append("(");
834: for (int i = 0; i < nc; i++) {
835: s.append(cols[i].name);
836: if ((i + 1) != nc) {
837: s.append(", ");
838: }
839: }
840: s.append(")");
841: s.append("\nSELECT ");
842: for (int i = 0; i < nc; i++) {
843: if (i != 0) {
844: s.append("\n ");
845: }
846:
847: if (isAddSequenceColumn(cols[i])) {
848: s.append("(a.");
849: s.append(identityColumnName);
850: s.append(" - b.min_id)");
851: } else {
852: s.append("a.");
853: s.append(cols[i].name);
854: }
855:
856: if ((i + 1) != nc) {
857: s.append(", ");
858: }
859: }
860: s.append("\n FROM ");
861: s.append(mainTempTableName);
862: s.append(" a,\n ");
863: s.append(minTempTableName);
864: s.append(" b\n WHERE a.");
865: s.append(indexColumn.name);
866: s.append(" = b.");
867: s.append(indexColumn.name);
868:
869: s.append(getRunCommand());
870:
871: s.append(comment("drop temp tables."));
872: s.append("\n");
873: s.append("DROP TABLE ");
874: s.append(mainTempTableName);
875: s.append(getRunCommand());
876:
877: s.append("DROP TABLE ");
878: s.append(minTempTableName);
879:
880: s.append(getRunCommand());
881: }
882:
883: }
|