0001: /*
0002: * Copyright (c) 1998 - 2005 Versant Corporation
0003: * All rights reserved. This program and the accompanying materials
0004: * are made available under the terms of the Eclipse Public License v1.0
0005: * which accompanies this distribution, and is available at
0006: * http://www.eclipse.org/legal/epl-v10.html
0007: *
0008: * Contributors:
0009: * Versant Corporation - initial API and implementation
0010: */
0011: package com.versant.core.jdbc.sql;
0012:
0013: import com.versant.core.jdbc.metadata.*;
0014: import com.versant.core.jdbc.sql.conv.BooleanConverter;
0015: import com.versant.core.jdbc.sql.conv.CharacterStreamConverter;
0016: import com.versant.core.jdbc.sql.conv.DateTimestampConverter;
0017: import com.versant.core.jdbc.sql.diff.ColumnDiff;
0018: import com.versant.core.jdbc.sql.diff.ControlParams;
0019: import com.versant.core.jdbc.sql.diff.TableDiff;
0020: import com.versant.core.jdbc.sql.exp.SqlExp;
0021: import com.versant.core.util.CharBuf;
0022:
0023: import java.io.PrintWriter;
0024: import java.sql.*;
0025: import java.util.*;
0026: import java.util.Date;
0027:
0028: /**
0029: * Support for DB2.
0030: */
0031: public class DB2SqlDriver extends SqlDriver {
0032:
0033: private CharacterStreamConverter.Factory characterStreamConverterFactory = new CharacterStreamConverter.Factory();
0034: private boolean isAS400;
0035:
0036: /**
0037: * Get the name of this driver.
0038: */
0039: public String getName() {
0040: return "db2";
0041: }
0042:
0043: /**
0044: * Get the default type mapping for the supplied JDBC type code from
0045: * java.sql.Types or null if the type is not supported. There is no
0046: * need to set the database or jdbcType on the mapping as this is done
0047: * after this call returns. Subclasses should override this and to
0048: * customize type mappings.
0049: */
0050: protected JdbcTypeMapping getTypeMapping(int jdbcType) {
0051: switch (jdbcType) {
0052: case Types.BIT:
0053: case Types.TINYINT:
0054: return new JdbcTypeMapping("SMALLINT", 0, 0,
0055: JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE, null);
0056: case Types.BIGINT:
0057: return new JdbcTypeMapping("BIGINT", 0, 0,
0058: JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE, null);
0059: case Types.DATE:
0060: case Types.TIME:
0061: case Types.TIMESTAMP:
0062: return new JdbcTypeMapping("TIMESTAMP", 0, 0,
0063: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE, null);
0064: case Types.DOUBLE:
0065: return new JdbcTypeMapping("DOUBLE", 0, 0,
0066: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE, null);
0067: case Types.CLOB:
0068: case Types.LONGVARCHAR:
0069: return new JdbcTypeMapping("CLOB", 0, 0,
0070: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
0071: characterStreamConverterFactory);
0072: case Types.VARBINARY:
0073: case Types.LONGVARBINARY:
0074: case Types.BLOB:
0075: return new JdbcTypeMapping("BLOB", 0, 0,
0076: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
0077: bytesConverterFactory);
0078: }
0079: return super .getTypeMapping(jdbcType);
0080: }
0081:
0082: /**
0083: * Get the default field mappings for this driver. These map java classes
0084: * to column properties. Subclasses should override this, call super() and
0085: * replace mappings as needed.
0086: */
0087: public HashMap getJavaTypeMappings() {
0088: HashMap ans = super .getJavaTypeMappings();
0089:
0090: BooleanConverter.Factory bcf = new BooleanConverter.Factory();
0091: ((JdbcJavaTypeMapping) ans.get(Boolean.TYPE))
0092: .setConverterFactory(bcf);
0093:
0094: DateTimestampConverter.Factory dtcf = new DateTimestampConverter.Factory();
0095: ((JdbcJavaTypeMapping) ans.get(Date.class))
0096: .setConverterFactory(dtcf);
0097:
0098: return ans;
0099: }
0100:
0101: /**
0102: * Create a default name generator instance for JdbcStore's using this
0103: * driver.
0104: */
0105: public JdbcNameGenerator createJdbcNameGenerator() {
0106: DefaultJdbcNameGenerator n = createDefaultJdbcNameGenerator();
0107: n.setMaxColumnNameLength(30);
0108: n.setMaxTableNameLength(128);
0109: n.setMaxConstraintNameLength(18);
0110: n.setMaxIndexNameLength(18);
0111: return n;
0112: }
0113:
0114: /**
0115: * Should PreparedStatement batching be used for this database and
0116: * JDBC driver?
0117: */
0118: public boolean isPreparedStatementPoolingOK() {
0119: return false;
0120: }
0121:
0122: /**
0123: * Does the JDBC driver support statement batching?
0124: */
0125: public boolean isInsertBatchingSupported() {
0126: return true;
0127: }
0128:
0129: /**
0130: * Does the JDBC driver support statement batching for updates?
0131: */
0132: public boolean isUpdateBatchingSupported() {
0133: return true;
0134: }
0135:
0136: protected void init(Driver jdbcDriver) {
0137: String n = jdbcDriver.getClass().getName();
0138: if (n.indexOf("as400") >= 0) {
0139: isAS400 = true;
0140: }
0141: }
0142:
0143: /**
0144: * Can batching be used if the statement contains a column with the
0145: * given JDBC type?
0146: */
0147: public boolean isBatchingSupportedForJdbcType(int jdbcType) {
0148: switch (jdbcType) {
0149: case Types.CLOB:
0150: case Types.LONGVARCHAR:
0151: case Types.VARBINARY:
0152: case Types.LONGVARBINARY:
0153: case Types.BLOB:
0154: return false;
0155: }
0156: return true;
0157: }
0158:
0159: /**
0160: * Does the JDBC driver support scrollable result sets?
0161: */
0162: public boolean isScrollableResultSetSupported() {
0163: return true;
0164: }
0165:
0166: /**
0167: * Is it ok to convert simple 'exists (select ...)' clauses under an
0168: * 'or' into outer joins?
0169: */
0170: public boolean isOptimizeExistsUnderOrToOuterJoin() {
0171: return true;
0172: }
0173:
0174: /**
0175: * Does this driver use the ANSI join syntax (i.e. the join clauses appear
0176: * in the from list e.g. postgres)?
0177: */
0178: public boolean isAnsiJoinSyntax() {
0179: return true;
0180: }
0181:
0182: /**
0183: * May the ON clauses for joins in a subquery reference columns from the
0184: * enclosing query? DB2 does not allow this.
0185: */
0186: public boolean isSubQueryJoinMayUseOuterQueryCols() {
0187: return false;
0188: }
0189:
0190: /**
0191: * Does this database support comments embedded in SQL?
0192: */
0193: public boolean isCommentSupported() {
0194: return false;
0195: }
0196:
0197: /**
0198: * Drop the table and all its constraints etc. This must remove
0199: * constraints to this table from other tables so it can be dropped.
0200: */
0201: public void dropTable(Connection con, String table, Statement stat)
0202: throws SQLException {
0203: stat.execute("DROP TABLE " + table);
0204: }
0205:
0206: /**
0207: * Append the allow nulls part of the definition for a column in a
0208: * create table statement.
0209: */
0210: protected void appendCreateColumnNulls(JdbcTable t, JdbcColumn c,
0211: CharBuf s) {
0212: if (!c.nulls)
0213: s.append(" NOT NULL");
0214: }
0215:
0216: /**
0217: * Add the primary key constraint part of a create table statement to s.
0218: */
0219: protected void appendPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
0220: s.append("CONSTRAINT ");
0221: s.append(t.pkConstraintName);
0222: s.append(" PRIMARY KEY (");
0223: appendColumnNameList(t.pk, s);
0224: s.append(')');
0225: }
0226:
0227: /**
0228: * Append an 'add constraint' statement for c.
0229: */
0230: protected void appendRefConstraint(CharBuf s, JdbcConstraint c) {
0231: s.append("ALTER TABLE ");
0232: s.append(c.src.name);
0233: s.append(" FOREIGN KEY ");
0234: s.append(c.name);
0235: s.append('(');
0236: appendColumnNameList(c.srcCols, s);
0237: s.append(") REFERENCES ");
0238: s.append(c.dest.name);
0239: s.append('(');
0240: appendColumnNameList(c.dest.pk, s);
0241: s.append(')');
0242: }
0243:
0244: /**
0245: * Generate a 'create table' statement for t.
0246: */
0247: public void generateCreateTable(JdbcTable t, Statement stat,
0248: PrintWriter out, boolean comments) throws SQLException {
0249: CharBuf s = new CharBuf();
0250: s.append("CREATE TABLE ");
0251: s.append(t.name);
0252: s.append(" (");
0253: JdbcColumn[] cols = t.getColsForCreateTable();
0254: int nc = cols.length;
0255: for (int i = 0; i < nc; i++) {
0256: appendCreateColumn(t, cols[i], s, comments);
0257: s.append(' ');
0258: }
0259: appendPrimaryKeyConstraint(t, s);
0260: s.append(")");
0261: String sql = s.toString();
0262: if (out != null)
0263: print(out, sql);
0264: if (stat != null)
0265: stat.execute(sql);
0266: }
0267:
0268: /**
0269: * Write an SQL statement to a script with appropriate separator.
0270: */
0271: protected void print(PrintWriter out, String sql) {
0272: out.print(sql);
0273: out.println(";");
0274: out.println();
0275: }
0276:
0277: /**
0278: * Append the from list entry for a table.
0279: */
0280: public void appendSqlFrom(JdbcTable table, String alias, CharBuf s) {
0281: s.append(table.name);
0282: if (alias != null) {
0283: s.append(" AS ");
0284: s.append(alias);
0285: }
0286: }
0287:
0288: /**
0289: * Append the from list entry for a table that is the right hand table
0290: * in a join i.e. it is being joined to.
0291: *
0292: * @param exp This is the expression that joins the tables
0293: * @param outer If true then this is an outer join
0294: */
0295: public void appendSqlFromJoin(JdbcTable table, String alias,
0296: SqlExp exp, boolean outer, CharBuf s) {
0297: if (outer)
0298: s.append(" LEFT JOIN ");
0299: else
0300: s.append(" JOIN ");
0301: s.append(table.name);
0302: if (alias != null) {
0303: s.append(" AS ");
0304: s.append(alias);
0305: }
0306: if (exp != null) {
0307: s.append(" ON (");
0308: exp.appendSQL(this , s, null);
0309: s.append(')');
0310: }
0311: }
0312:
0313: /**
0314: * Get default SQL to test a connection or null if none available. This
0315: * must be a query that returns at least one row.
0316: */
0317: public String getConnectionValidateSQL() {
0318: return "SELECT * FROM SYSIBM.SYSDUMMY1";
0319: }
0320:
0321: /**
0322: * Gets the current user's schema
0323: */
0324: protected String getSchema(Connection con) {
0325: String schema = null;
0326: String sql = "SELECT CURRENT SCHEMA FROM SYSIBM.SYSDUMMY1";
0327: try {
0328: Statement statement = con.createStatement();
0329: ResultSet rs = statement.executeQuery(sql);
0330: if (rs.next()) {
0331: schema = rs.getString(1);
0332: }
0333: try {
0334: statement.close();
0335: } catch (SQLException e) {
0336: }
0337: } catch (SQLException sqle) {
0338: //hide
0339: }
0340: return schema;
0341: }
0342:
0343: /**
0344: * Get whatever needs to be appended to a SELECT statement to lock the
0345: * rows if this makes sense for the database. This must have a leading
0346: * space if not empty.
0347: */
0348: public char[] getSelectForUpdate() {
0349: return null;
0350: }
0351:
0352: private String[] typesNames = new String[] { "BIGINT",
0353: "LONG VARCHAR FOR BIT DATA", "VARCHAR() FOR BIT DATA",
0354: "CHAR() FOR BIT DATA", "ROWID", "LONG VARCHAR", "CHAR",
0355: "CHARACTER", "NUMERIC", "DECIMAL", "INTEGER", "SMALLINT",
0356: "FLOAT", "REAL", "DOUBLE", "VARG", "VARCHAR", "DATE",
0357: "TIME", "TIMESTAMP", "TIMESTMP", "BLOB", "CLOB", "DBCLOB" };
0358:
0359: private int[] typesValues = new int[] { -5, -4, -3, -2, -2, -1, 1,
0360: 1, 2, 3, 4, 5, 6, 7, 8, 12, 12, 91, 92, 93, 93, 2004, 2005,
0361: 2005 };
0362:
0363: private int getJdbcType(String type) {
0364: for (int i = 0; i < typesNames.length; i++) {
0365: if (typesNames[i].equals(type)) {
0366: return typesValues[i];
0367: }
0368: }
0369: return Types.OTHER;
0370: }
0371:
0372: /**
0373: * Get the JdbcTables from the database for the given database con.
0374: *
0375: * @param con
0376: * @return HashMap of tablename.toLowerCase() as key and JdbcTable as value
0377: * @throws SQLException on DB errors
0378: */
0379: public HashMap getDBSchema(Connection con, ControlParams params)
0380: throws SQLException {
0381: HashMap jdbcTableMap = new HashMap(); // main map of jdbc tables
0382:
0383: HashMap synonymMap = new HashMap();
0384: String schemaName = getSchema(con);
0385:
0386: try {
0387: String synonymSql = null;
0388: String tmpSynonymDB2 = "SELECT BASE_NAME, "
0389: + " NAME " + " FROM SYSIBM.SYSTABLES "
0390: + " WHERE TYPE = 'A'";
0391: String tmpSynonymAS400 = "SELECT BASE_TABLE_NAME,"
0392: + " TABLE_NAME "
0393: + " FROM SYSTABLES "
0394: + " WHERE TABLE_TYPE = 'A'"
0395: + (schemaName == null ? ""
0396: : " AND TABLE_SCHEMA = '" + schemaName
0397: + "'");
0398: if (isAS400) {
0399: synonymSql = tmpSynonymAS400;
0400: } else {
0401: synonymSql = tmpSynonymDB2;
0402: }
0403:
0404: Statement statSynonym = con.createStatement();
0405: ResultSet rsSynonym = statSynonym.executeQuery(synonymSql);
0406: while (rsSynonym.next()) {
0407: synonymMap.put(rsSynonym.getString(1).toLowerCase(),
0408: rsSynonym.getString(2).toLowerCase());
0409: }
0410: // clean up
0411: if (rsSynonym != null) {
0412: try {
0413: rsSynonym.close();
0414: } catch (SQLException e) {
0415: }
0416: }
0417: if (statSynonym != null) {
0418: try {
0419: statSynonym.close();
0420: } catch (SQLException e) {
0421: }
0422: }
0423: } catch (SQLException e) {
0424: //hide it all, we do not want throw exeptions if
0425: }
0426:
0427: // now we do columns
0428: String tableName = null;
0429:
0430: String columnSql = null;
0431: String tmpColumnDB2 = " SELECT TABNAME, COLNAME, TYPENAME, LENGTH, SCALE, COLNO, 'Y' "
0432: + " FROM SYSCAT.COLUMNS "
0433: + " WHERE NULLS LIKE '%Y%' "
0434: + " AND NOT TABSCHEMA IN ('SYSIBM','SYSCAT','SYSSTAT') "
0435: + "UNION ALL "
0436: + " SELECT TABNAME, COLNAME, TYPENAME, LENGTH, SCALE, COLNO, 'N' "
0437: + " FROM SYSCAT.COLUMNS "
0438: + " WHERE NULLS LIKE '%N%' "
0439: + " AND NOT TABSCHEMA IN ('SYSIBM','SYSCAT','SYSSTAT') "
0440: + "UNION ALL "
0441: + " SELECT B.NAME, A.COLNAME, A.TYPENAME, A.LENGTH, A.SCALE, A.COLNO, 'Y' "
0442: + " FROM SYSCAT.COLUMNS A, "
0443: + " SYSIBM.SYSTABLES B "
0444: + " WHERE NULLS LIKE '%Y%' "
0445: + " AND B.BASE_NAME = A.TABNAME "
0446: + " AND A.TABSCHEMA = B.CREATOR "
0447: + " AND NOT A.TABSCHEMA in ('SYSIBM','SYSCAT','SYSSTAT') "
0448: + "UNION ALL "
0449: + " SELECT B.NAME, A.COLNAME, A.TYPENAME, A.LENGTH, A.SCALE, A.COLNO, 'N' "
0450: + " FROM SYSCAT.COLUMNS A, "
0451: + " SYSIBM.SYSTABLES B "
0452: + " WHERE NULLS LIKE '%N%' "
0453: + " AND B.BASE_NAME = A.TABNAME "
0454: + " AND A.TABSCHEMA = B.CREATOR "
0455: + " AND NOT A.TABSCHEMA in ('SYSIBM','SYSCAT','SYSSTAT') "
0456: + " ORDER BY 1, 6 FOR FETCH ONLY";
0457: String tmpColumnAS400 = "SELECT c.TABLE_NAME , "
0458: + " c.COLUMN_NAME , "
0459: + " c.DATA_TYPE, "
0460: + " c.LENGTH , "
0461: + " c.NUMERIC_SCALE, "
0462: + " c.ORDINAL_POSITION, "
0463: + " c.IS_NULLABLE "
0464: + " FROM SYSCOLUMNS c, "
0465: + " SYSTABLES t "
0466: + " WHERE c.TABLE_NAME = t.TABLE_NAME "
0467: + (schemaName == null ? "" : " AND t.TABLE_SCHEMA = '"
0468: + schemaName + "' AND c.TABLE_SCHEMA = '"
0469: + schemaName + "'")
0470: + " AND t.SYSTEM_TABLE = 'N' "
0471: + " AND t.TABLE_TYPE = 'T' " + " ORDER BY 1,6 "
0472: + " FOR FETCH ONLY ";
0473:
0474: if (isAS400) {
0475: columnSql = tmpColumnAS400;
0476: } else {
0477: columnSql = tmpColumnDB2;
0478: }
0479: Statement statCol = con.createStatement();
0480: ResultSet rsColumn = statCol.executeQuery(columnSql);
0481: ArrayList columns = null;
0482:
0483: while (rsColumn.next()) {
0484:
0485: String temptableName = rsColumn.getString(1);
0486:
0487: if (tableName == null) { // this is the first one
0488: tableName = temptableName;
0489: columns = new ArrayList();
0490: JdbcTable jdbcTable = new JdbcTable();
0491: jdbcTable.name = tableName;
0492: jdbcTableMap.put(tableName, jdbcTable);
0493: }
0494:
0495: if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
0496: JdbcColumn[] jdbcColumns = new JdbcColumn[columns
0497: .size()];
0498: columns.toArray(jdbcColumns);
0499: JdbcTable jdbcTable0 = (JdbcTable) jdbcTableMap
0500: .get(tableName);
0501: jdbcTable0.cols = jdbcColumns;
0502:
0503: tableName = temptableName;
0504: columns.clear();
0505: JdbcTable jdbcTable1 = new JdbcTable();
0506: jdbcTable1.name = tableName;
0507: jdbcTableMap.put(tableName, jdbcTable1);
0508: }
0509:
0510: JdbcColumn col = new JdbcColumn();
0511:
0512: col.name = rsColumn.getString(2).trim();
0513: col.sqlType = rsColumn.getString(3).trim();
0514: col.jdbcType = getJdbcType(col.sqlType);
0515: col.length = rsColumn.getInt(4);
0516: col.scale = rsColumn.getInt(5);
0517: col.nulls = ("Y".equals(rsColumn.getString(7).trim()) ? true
0518: : false);
0519:
0520: switch (col.jdbcType) {
0521: case java.sql.Types.BIT:
0522: case java.sql.Types.TINYINT:
0523: case java.sql.Types.SMALLINT:
0524: case java.sql.Types.INTEGER:
0525: case java.sql.Types.BIGINT:
0526: case java.sql.Types.CLOB:
0527: case java.sql.Types.BLOB:
0528: case java.sql.Types.DATE:
0529: case java.sql.Types.TIME:
0530: case java.sql.Types.TIMESTAMP:
0531: col.length = 0;
0532: col.scale = 0;
0533: default:
0534: }
0535: columns.add(col);
0536: }
0537: // we fin last table
0538: if (columns != null) {
0539: JdbcColumn[] jdbcColumns = new JdbcColumn[columns.size()];
0540: if (jdbcColumns != null) {
0541: columns.toArray(jdbcColumns);
0542: JdbcTable colJdbcTable = (JdbcTable) jdbcTableMap
0543: .get(tableName);
0544: colJdbcTable.cols = jdbcColumns;
0545: columns.clear();
0546: }
0547: }
0548: tableName = null;
0549:
0550: // clean up
0551: if (rsColumn != null) {
0552: try {
0553: rsColumn.close();
0554: } catch (SQLException e) {
0555: }
0556: }
0557: if (statCol != null) {
0558: try {
0559: statCol.close();
0560: } catch (SQLException e) {
0561: }
0562: }
0563: if (!params.checkColumnsOnly()) {
0564: if (params.isCheckPK()) {
0565: // now we do primaryKeys
0566: HashMap pkMap = null;
0567:
0568: String pkSql = null;
0569: String tmpPkDB2 = "SELECT DISTINCT IT.TABNAME as TABLE_NAME, "
0570: + " KT.COLNAME as COLUMN_NAME, "
0571: + " KT.COLSEQ as KEY_SEQ, "
0572: + " IT.INDNAME as PK_NAME "
0573: + " FROM SYSCAT.INDEXCOLUSE KT, "
0574: + " SYSCAT.INDEXES IT "
0575: + " WHERE IT.UNIQUERULE = 'P' "
0576: + " AND IT.INDSCHEMA = KT.INDSCHEMA "
0577: + " AND KT.INDNAME = IT.INDNAME "
0578: + " ORDER BY TABLE_NAME,PK_NAME,KEY_SEQ FOR FETCH ONLY";
0579: String tmpPkAS400 = " SELECT DISTINCT T1.TABLE_NAME, "
0580: + " T1.COLUMN_NAME, "
0581: + " T1.ORDINAL_POSITION as KEY_SEQ, "
0582: + " T1.CONSTRAINT_NAME as PK_NAME "
0583: + " FROM SYSKEYCST T1, "
0584: + " SYSCST T2 "
0585: + " WHERE T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAME "
0586: + " AND T1.CONSTRAINT_SCHEMA = T2.CONSTRAINT_SCHEMA "
0587: + " AND T2.CONSTRAINT_TYPE = 'PRIMARY KEY' "
0588: + (schemaName == null ? ""
0589: : "AND T1.TABLE_SCHEMA = '"
0590: + schemaName + "'")
0591: + " ORDER BY 1,4,3 " + " FOR FETCH ONLY ";
0592: if (isAS400) {
0593: pkSql = tmpPkAS400;
0594: } else {
0595: pkSql = tmpPkDB2;
0596: }
0597:
0598: Statement statPK = con.createStatement();
0599: ResultSet rsPKs = statPK.executeQuery(pkSql);
0600: int pkCount = 0;
0601: String pkName = null;
0602: while (rsPKs.next()) {
0603: String temptableName = rsPKs.getString(1);
0604:
0605: if (!jdbcTableMap.containsKey(temptableName)) {
0606: continue;
0607: }
0608:
0609: if (tableName == null) { // this is the first one
0610: tableName = temptableName;
0611: pkMap = new HashMap();
0612: }
0613:
0614: if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
0615: JdbcColumn[] pkColumns = new JdbcColumn[pkCount];
0616: int indexOfPKCount = 0;
0617: JdbcTable jdbcTable = (JdbcTable) jdbcTableMap
0618: .get(tableName);
0619: for (int i = 0; i < jdbcTable.cols.length; i++) {
0620: JdbcColumn jdbcColumn = jdbcTable.cols[i];
0621: if (pkMap.containsKey(jdbcColumn.name)) {
0622: pkColumns[indexOfPKCount] = jdbcColumn;
0623: jdbcColumn.pk = true;
0624: indexOfPKCount++;
0625: }
0626: }
0627: jdbcTable.pk = pkColumns;
0628: jdbcTable.pkConstraintName = pkName;
0629:
0630: tableName = temptableName;
0631: pkMap.clear();
0632: pkCount = 0;
0633: }
0634: pkCount++;
0635: pkMap.put(rsPKs.getString(2), null);
0636: pkName = rsPKs.getString(4);
0637: }
0638: JdbcColumn[] pkColumns = new JdbcColumn[pkCount];
0639: int indexOfPKCount = 0;
0640: JdbcTable pkJdbcTable = (JdbcTable) jdbcTableMap
0641: .get(tableName);
0642: if (pkJdbcTable != null) {
0643: for (int i = 0; i < pkJdbcTable.cols.length; i++) {
0644: JdbcColumn jdbcColumn = pkJdbcTable.cols[i];
0645: if (pkMap.containsKey(jdbcColumn.name)) {
0646: pkColumns[indexOfPKCount] = jdbcColumn;
0647: jdbcColumn.pk = true;
0648: indexOfPKCount++;
0649: }
0650: }
0651: pkJdbcTable.pk = pkColumns;
0652: pkJdbcTable.pkConstraintName = pkName;
0653: }
0654:
0655: tableName = null;
0656: // clean up
0657: if (rsPKs != null) {
0658: try {
0659: rsPKs.close();
0660: } catch (SQLException e) {
0661: }
0662: }
0663: if (statPK != null) {
0664: try {
0665: statPK.close();
0666: } catch (SQLException e) {
0667: }
0668: }
0669: }
0670: if (params.isCheckIndex()) {
0671: // now we do index
0672: String indexSql = null;
0673: String tmpIndexDB2 = "SELECT IT.TABNAME as TABLE_NAME, "
0674: + " IU.COLNAME as COLUMN_NAME, "
0675: + " IT.INDNAME as INDEX_NAME, "
0676: + " 1 as TYPE, "
0677: + " IU.COLSEQ as ORDINAL_POSITION,"
0678: + " 0 as UNIQUE "
0679: + " FROM SYSCAT.INDEXCOLUSE IU, "
0680: + " SYSCAT.INDEXES IT "
0681: + " WHERE IU.INDNAME = IT.INDNAME "
0682: + " AND IU.INDSCHEMA = IT.INDSCHEMA "
0683: + " AND IT.INDEXTYPE = 'CLUS' "
0684: + " AND IT.TABSCHEMA <> 'SYSIBM' "
0685: + " AND IT.UNIQUE_COLCOUNT = -1 "
0686: + "UNION ALL "
0687: + "SELECT IT.TABNAME as TABLE_NAME, "
0688: + " IU.COLNAME as COLUMN_NAME, "
0689: + " IT.INDNAME as INDEX_NAME, "
0690: + " 3 as TYPE, "
0691: + " IU.COLSEQ as ORDINAL_POSITION,"
0692: + " 1 as UNIQUE"
0693: + " FROM SYSCAT.INDEXCOLUSE IU, "
0694: + " SYSCAT.INDEXES IT "
0695: + " WHERE IU.INDNAME = IT.INDNAME "
0696: + " AND IU.INDSCHEMA = IT.INDSCHEMA "
0697: + " AND IT.INDEXTYPE = 'CLUS' "
0698: + " AND IT.TABSCHEMA <> 'SYSIBM' "
0699: + " AND IT.UNIQUE_COLCOUNT <> -1 "
0700: + " AND IT.UNIQUERULE = 'U'"
0701: + "UNION ALL "
0702: + "SELECT IT.TABNAME as TABLE_NAME, "
0703: + " IU.COLNAME as COLUMN_NAME, "
0704: + " IT.INDNAME as INDEX_NAME, "
0705: + " 3 as TYPE, "
0706: + " IU.COLSEQ as ORDINAL_POSITION,"
0707: + " 0 as UNIQUE"
0708: + " FROM SYSCAT.INDEXCOLUSE IU, "
0709: + " SYSCAT.INDEXES IT "
0710: + " WHERE IU.INDNAME = IT.INDNAME "
0711: + " AND IU.INDSCHEMA = IT.INDSCHEMA "
0712: + " AND IT.INDEXTYPE = 'REG' "
0713: + " AND IT.TABSCHEMA <> 'SYSIBM' "
0714: + " AND IT.UNIQUE_COLCOUNT = -1 "
0715: + "UNION ALL "
0716: + "SELECT IT.TABNAME as TABLE_NAME, "
0717: + " IU.COLNAME as COLUMN_NAME, "
0718: + " IT.INDNAME as INDEX_NAME, "
0719: + " 3 as TYPE, "
0720: + " IU.COLSEQ as ORDINAL_POSITION,"
0721: + " 1 as UNIQUE"
0722: + " FROM SYSCAT.INDEXCOLUSE IU, "
0723: + " SYSCAT.INDEXES IT "
0724: + " WHERE IU.INDNAME = IT.INDNAME "
0725: + " AND IU.INDSCHEMA = IT.INDSCHEMA "
0726: + " AND IT.INDEXTYPE = 'REG' "
0727: + " AND IT.TABSCHEMA <> 'SYSIBM' "
0728: + " AND IT.UNIQUE_COLCOUNT <> -1 "
0729: + " AND IT.UNIQUERULE = 'U'"
0730: + " ORDER BY TABLE_NAME, INDEX_NAME FOR FETCH ONLY";
0731: String tmpIndexAS400 = "SELECT i.TBNAME AS TABLE_NAME , "
0732: + " k.COLNAME AS COLUMN_NAME , "
0733: + " i.NAME AS INDEX_NAME, "
0734: + " 3 AS TYPE , "
0735: + " k.COLSEQ AS ORDINAL_POSITION, "
0736: + " CASE UNIQUERULE "
0737: + " WHEN 'D' THEN 0 else 1 "
0738: + " END AS UNIQUE "
0739: + " FROM SYSINDEXES i, "
0740: + " SYSKEYS k "
0741: + " WHERE CREATOR = IXCREATOR "
0742: + " AND NAME = IXNAME "
0743: + (schemaName == null ? ""
0744: : " AND TABLE_SCHEMA = '"
0745: + schemaName + "'")
0746: + " ORDER BY 1, 3, 5 FOR FETCH ONLY";
0747:
0748: if (isAS400) {
0749: indexSql = tmpIndexAS400;
0750: } else {
0751: indexSql = tmpIndexDB2;
0752: }
0753:
0754: Statement statIndex = con.createStatement();
0755: ResultSet rsIndex = statIndex.executeQuery(indexSql);
0756:
0757: HashMap indexNameMap = null;
0758: ArrayList indexes = null;
0759: while (rsIndex.next()) {
0760: String temptableName = rsIndex.getString(1);
0761: if (tableName == null) { // this is the first one
0762: tableName = temptableName;
0763: indexNameMap = new HashMap();
0764: indexes = new ArrayList();
0765: }
0766:
0767: String indexName = rsIndex.getString(3);
0768: JdbcTable tempJdbcTable = (JdbcTable) jdbcTableMap
0769: .get(temptableName);
0770:
0771: if (indexName != null
0772: && !indexName
0773: .equals(tempJdbcTable.pkConstraintName)) {
0774: if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
0775: JdbcTable jdbcTable = (JdbcTable) jdbcTableMap
0776: .get(tableName);
0777: JdbcIndex[] jdbcIndexes = new JdbcIndex[indexes
0778: .size()];
0779: indexes.toArray(jdbcIndexes);
0780: jdbcTable.indexes = jdbcIndexes;
0781:
0782: tableName = temptableName;
0783: indexes.clear();
0784: indexNameMap.clear();
0785:
0786: }
0787: JdbcTable jdbcTable = (JdbcTable) jdbcTableMap
0788: .get(tableName);
0789: if (indexNameMap.containsKey(indexName)) {
0790: JdbcIndex index = null;
0791: for (Iterator iter = indexes.iterator(); iter
0792: .hasNext();) {
0793: JdbcIndex jdbcIndex = (JdbcIndex) iter
0794: .next();
0795: if (jdbcIndex.name.equals(indexName)) {
0796: index = jdbcIndex;
0797: }
0798: }
0799:
0800: JdbcColumn[] tempIndexColumns = index.cols;
0801: JdbcColumn[] indexColumns = new JdbcColumn[tempIndexColumns.length + 1];
0802: System.arraycopy(tempIndexColumns, 0,
0803: indexColumns, 0,
0804: tempIndexColumns.length);
0805: String colName = rsIndex.getString(2);
0806: for (int i = 0; i < jdbcTable.cols.length; i++) {
0807: JdbcColumn jdbcColumn = jdbcTable.cols[i];
0808: if (colName.equals(jdbcColumn.name)) {
0809: indexColumns[tempIndexColumns.length] = jdbcColumn;
0810: jdbcColumn.partOfIndex = true;
0811: }
0812: }
0813: index.setCols(indexColumns);
0814: } else {
0815: indexNameMap.put(indexName, null);
0816: JdbcIndex index = new JdbcIndex();
0817: index.name = indexName;
0818: index.unique = rsIndex.getBoolean(6);
0819: short indexType = rsIndex.getShort(4);
0820: switch (indexType) {
0821: case DatabaseMetaData.tableIndexClustered:
0822: index.clustered = true;
0823: break;
0824: }
0825: String colName = rsIndex.getString(2);
0826: JdbcColumn[] indexColumns = new JdbcColumn[1];
0827: for (int i = 0; i < jdbcTable.cols.length; i++) {
0828: JdbcColumn jdbcColumn = jdbcTable.cols[i];
0829: if (colName.equals(jdbcColumn.name)) {
0830: indexColumns[0] = jdbcColumn;
0831: jdbcColumn.partOfIndex = true;
0832: }
0833: }
0834: index.setCols(indexColumns);
0835: indexes.add(index);
0836: }
0837: }
0838: }
0839: JdbcTable indexJdbcTable = (JdbcTable) jdbcTableMap
0840: .get(tableName);
0841: if (indexJdbcTable != null && indexes != null) {
0842: JdbcIndex[] jdbcIndexes = new JdbcIndex[indexes
0843: .size()];
0844: indexes.toArray(jdbcIndexes);
0845: indexJdbcTable.indexes = jdbcIndexes;
0846: indexes.clear();
0847: indexNameMap.clear();
0848: }
0849: tableName = null;
0850: // clean up
0851: if (rsIndex != null) {
0852: try {
0853: rsIndex.close();
0854: } catch (SQLException e) {
0855: }
0856: }
0857: if (statIndex != null) {
0858: try {
0859: statIndex.close();
0860: } catch (SQLException e) {
0861: }
0862: }
0863: }
0864: if (params.isCheckConstraint()) {
0865: // now we do forign keys
0866: if (isAS400) {
0867: String fkSql = "SELECT DISTINCT PK.TABLE_NAME as PKTABLE_NAM, "
0868: + //1
0869: " PK.COLUMN_NAME as PKCOLUMN_NAME, "
0870: + //2
0871: " FK.TABLE_NAME as FKTABLE_NAME, "
0872: + //3
0873: " FK.COLUMN_NAME as FKCOLUMN_NAME, "
0874: + //4
0875: " FK.ORDINAL_POSITION as COL_NUM, "
0876: + //5
0877: " FK.CONSTRAINT_NAME as FK_NAME , "
0878: + //6
0879: " PK.CONSTRAINT_NAME as PK_NAME "
0880: + //7
0881: " FROM SYSCST C, "
0882: + " SYSKEYCST PK, "
0883: + " SYSREFCST R, "
0884: + " SYSKEYCST FK "
0885: + " WHERE C.CONSTRAINT_NAME = PK.CONSTRAINT_NAME "
0886: + " AND C.CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA "
0887: + " AND C.CONSTRAINT_NAME = R.UNIQUE_CONSTRAINT_NAME "
0888: + " AND C.CONSTRAINT_SCHEMA = R.UNIQUE_CONSTRAINT_SCHEMA "
0889: + " AND R.CONSTRAINT_NAME = FK.CONSTRAINT_NAME "
0890: + " AND R.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA "
0891: + " AND PK.ORDINAL_POSITION = FK.ORDINAL_POSITION "
0892: + (schemaName == null ? ""
0893: : " AND FK.TABLE_SCHEMA = '"
0894: + schemaName + "'")
0895: + " ORDER BY 3,6,5 " + " FOR FETCH ONLY ";
0896: Statement statFK = con.createStatement();
0897: ResultSet rsFKs = statFK.executeQuery(fkSql);
0898:
0899: HashMap constraintNameMap = null;
0900: ArrayList constraints = null;
0901: while (rsFKs.next()) {
0902: String temptableName = rsFKs.getString(3);
0903: if (tableName == null) { // this is the first one
0904: tableName = temptableName;
0905: constraintNameMap = new HashMap();
0906: constraints = new ArrayList();
0907: }
0908:
0909: if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
0910: JdbcTable jdbcTable = (JdbcTable) jdbcTableMap
0911: .get(tableName);
0912: JdbcConstraint[] jdbcConstraints = new JdbcConstraint[constraints
0913: .size()];
0914: constraints.toArray(jdbcConstraints);
0915: jdbcTable.constraints = jdbcConstraints;
0916:
0917: tableName = temptableName;
0918: constraintNameMap.clear();
0919: constraints.clear();
0920: }
0921:
0922: String fkName = rsFKs.getString(6);
0923: JdbcTable jdbcTable = (JdbcTable) jdbcTableMap
0924: .get(tableName);
0925:
0926: if (jdbcTable == null)
0927: continue;
0928:
0929: if (constraintNameMap.containsKey(fkName)) {
0930: JdbcConstraint constraint = null;
0931: for (Iterator iter = constraints.iterator(); iter
0932: .hasNext();) {
0933: JdbcConstraint jdbcConstraint = (JdbcConstraint) iter
0934: .next();
0935: if (jdbcConstraint.name.equals(fkName)) {
0936: constraint = jdbcConstraint;
0937: }
0938: }
0939:
0940: JdbcColumn[] tempConstraintColumns = constraint.srcCols;
0941: JdbcColumn[] constraintColumns = new JdbcColumn[tempConstraintColumns.length + 1];
0942: System.arraycopy(tempConstraintColumns, 0,
0943: constraintColumns, 0,
0944: tempConstraintColumns.length);
0945: String colName = rsFKs.getString(4);
0946: for (int i = 0; i < jdbcTable.cols.length; i++) {
0947: JdbcColumn jdbcColumn = jdbcTable.cols[i];
0948: if (colName.equals(jdbcColumn.name)) {
0949: constraintColumns[tempConstraintColumns.length] = jdbcColumn;
0950: jdbcColumn.foreignKey = true;
0951: }
0952: }
0953: constraint.srcCols = constraintColumns;
0954: } else {
0955: constraintNameMap.put(fkName, null);
0956: JdbcConstraint constraint = new JdbcConstraint();
0957: constraint.name = fkName;
0958: constraint.src = jdbcTable;
0959: String colName = rsFKs.getString(4);
0960: JdbcColumn[] constraintColumns = new JdbcColumn[1];
0961: for (int i = 0; i < jdbcTable.cols.length; i++) {
0962: JdbcColumn jdbcColumn = jdbcTable.cols[i];
0963: if (colName.equals(jdbcColumn.name)) {
0964: constraintColumns[0] = jdbcColumn;
0965: jdbcColumn.foreignKey = true;
0966: }
0967: }
0968: constraint.srcCols = constraintColumns;
0969: constraint.dest = (JdbcTable) jdbcTableMap
0970: .get(rsFKs.getString(1));
0971: constraints.add(constraint);
0972: }
0973: }
0974:
0975: JdbcTable constraintsjdbcTable = (JdbcTable) jdbcTableMap
0976: .get(tableName);
0977: if (constraintsjdbcTable != null) {
0978: JdbcConstraint[] jdbcConstraints = new JdbcConstraint[constraints
0979: .size()];
0980: constraints.toArray(jdbcConstraints);
0981: constraintsjdbcTable.constraints = jdbcConstraints;
0982: }
0983:
0984: if (rsFKs != null) {
0985: try {
0986: rsFKs.close();
0987: } catch (SQLException e) {
0988: }
0989: }
0990: if (statFK != null) {
0991: try {
0992: statFK.close();
0993: } catch (SQLException e) {
0994: }
0995: }
0996: } else { //jenifer 8514360 vinny
0997: String fkSql = "SELECT RT.REFTABNAME as PKTABLE_NAME, "
0998: + //1
0999: " RT.PK_COLNAMES as PKCOLUMN_NAME, "
1000: + //2
1001: " RT.TABNAME as FKTABLE_NAME, "
1002: + //3
1003: " RT.FK_COLNAMES as FKCOLUMN_NAME, "
1004: + //4
1005: " RT.COLCOUNT as COLCOUNT, "
1006: + //5
1007: " RT.CONSTNAME as FK_NAME, "
1008: + //6
1009: " RT.REFKEYNAME as PK_NAME "
1010: + //7
1011: " FROM SYSCAT.REFERENCES RT "
1012: + " ORDER BY FKTABLE_NAME, FK_NAME";
1013:
1014: Statement statFK = con.createStatement();
1015: ResultSet rsFKs = statFK.executeQuery(fkSql);
1016:
1017: HashMap constraintNameMap = new HashMap();
1018:
1019: while (rsFKs.next()) {
1020: String constName = rsFKs.getString("FK_NAME");
1021: String srcTableName = rsFKs
1022: .getString("FKTABLE_NAME");
1023: String destTableName = rsFKs
1024: .getString("PKTABLE_NAME");
1025: ArrayList srcColNames = new ArrayList();
1026:
1027: if (rsFKs.getInt("COLCOUNT") == 1) {
1028: srcColNames.add(rsFKs.getString(
1029: "FKCOLUMN_NAME").trim());
1030: } else {
1031: StringTokenizer st = new StringTokenizer(
1032: rsFKs.getString("FKCOLUMN_NAME")
1033: .trim(), " ");
1034: while (st.hasMoreTokens()) {
1035: srcColNames.add(st.nextToken().trim());
1036: }
1037: }
1038: JdbcTable srcJdbcTable = (JdbcTable) jdbcTableMap
1039: .get(srcTableName);
1040: if (srcJdbcTable == null) {
1041: continue;
1042: }
1043: JdbcTable destJdbcTable = (JdbcTable) jdbcTableMap
1044: .get(destTableName);
1045: if (destJdbcTable == null) {
1046: continue;
1047: }
1048:
1049: JdbcConstraint jdbcConstraint = new JdbcConstraint();
1050: jdbcConstraint.name = constName;
1051: jdbcConstraint.src = srcJdbcTable;
1052: jdbcConstraint.dest = destJdbcTable;
1053: JdbcColumn[] constraintColumns = new JdbcColumn[srcColNames
1054: .size()];
1055: int j = 0;
1056: for (Iterator iter = srcColNames.iterator(); iter
1057: .hasNext(); j++) {
1058: String colName = (String) iter.next();
1059: for (int i = 0; i < srcJdbcTable.cols.length; i++) {
1060: JdbcColumn jdbcColumn = srcJdbcTable.cols[i];
1061: if (colName.equals(jdbcColumn.name)) {
1062: constraintColumns[j] = jdbcColumn;
1063: jdbcColumn.foreignKey = true;
1064: }
1065: }
1066: }
1067: jdbcConstraint.srcCols = constraintColumns;
1068: if (constraintNameMap.containsKey(srcJdbcTable)) {
1069: ArrayList list = (ArrayList) constraintNameMap
1070: .get(srcJdbcTable);
1071: list.add(jdbcConstraint);
1072: } else {
1073: ArrayList list = new ArrayList();
1074: list.add(jdbcConstraint);
1075: constraintNameMap.put(srcJdbcTable, list);
1076: }
1077: }
1078: for (Iterator iter = constraintNameMap.keySet()
1079: .iterator(); iter.hasNext();) {
1080: JdbcTable jdbcTable = (JdbcTable) iter.next();
1081: ArrayList list = (ArrayList) constraintNameMap
1082: .get(jdbcTable);
1083: if (list != null) {
1084: JdbcConstraint[] jdbcConstraints = new JdbcConstraint[list
1085: .size()];
1086: list.toArray(jdbcConstraints);
1087: jdbcTable.constraints = jdbcConstraints;
1088: }
1089: }
1090:
1091: if (rsFKs != null) {
1092: try {
1093: rsFKs.close();
1094: } catch (SQLException e) {
1095: }
1096: }
1097: if (statFK != null) {
1098: try {
1099: statFK.close();
1100: } catch (SQLException e) {
1101: }
1102: }
1103: }
1104: }
1105: }
1106:
1107: HashMap returnMap = new HashMap();
1108: Collection col = jdbcTableMap.values();
1109: String name = null;
1110: for (Iterator iterator = col.iterator(); iterator.hasNext();) {
1111: JdbcTable table = (JdbcTable) iterator.next();
1112: name = table.name.toLowerCase();
1113: returnMap.put(name, table);
1114: if (synonymMap.containsKey(name)) {
1115: returnMap.put(synonymMap.get(name), table);
1116: }
1117: }
1118: fixAllNames(returnMap);
1119: return returnMap;
1120: }
1121:
1122: /**
1123: * Append a column that needs to be added.
1124: */
1125: protected void appendAddNewColumn(JdbcTable t, JdbcColumn c,
1126: CharBuf s, boolean comments) {
1127: if (comments && isCommentSupported() && c.comment != null) {
1128: s.append(comment("add column for field " + c.comment));
1129: }
1130:
1131: s.append("\n");
1132: if (isAddSequenceColumn(c)) {
1133: addSequenceColumn(t, c, s, comments);
1134: } else {
1135: s.append("ALTER TABLE ");
1136: s.append(t.name);
1137: s.append(" ADD COLUMN ");
1138: s.append(c.name);
1139: s.append(' ');
1140: appendColumnType(c, s);
1141: if (c.nulls) {
1142: s.append(getRunCommand());
1143: } else {
1144: appendCreateColumnNulls(t, c, s);
1145: s.append(" DEFAULT");
1146: s.append(getRunCommand());
1147:
1148: s.append("UPDATE ");
1149: s.append(t.name);
1150: s.append(" SET ");
1151: s.append(c.name);
1152: s.append(" = ");
1153: s.append(getDefaultForType(c));
1154: s.append(getRunCommand());
1155: }
1156: }
1157: }
1158:
1159: /**
1160: * Append a column that needs to be added.
1161: */
1162: protected void appendDropColumn(TableDiff tableDiff, JdbcColumn c,
1163: CharBuf s, boolean comments) {
1164: if (comments && isCommentSupported()) {
1165: s.append(comment("dropping unknown column " + c.name));
1166: }
1167: s.append("\n");
1168: if (isDropSequenceColumn(tableDiff, c)) {
1169: dropSequenceColumn(tableDiff.getOurTable(), c, s, comments);
1170: } else {
1171: s.append("ALTER TABLE ");
1172: s.append(tableDiff.getOurTable().name);
1173: s.append(" DROP COLUMN ");
1174: s.append(c.name);
1175: }
1176: }
1177:
1178: /**
1179: * Append a column that needs to be added.
1180: */
1181: protected void appendModifyColumn(TableDiff tableDiff,
1182: ColumnDiff diff, CharBuf s, boolean comments) {
1183: JdbcTable t = tableDiff.getOurTable();
1184: JdbcColumn c = diff.getOurCol();
1185:
1186: if (comments && isCommentSupported() && c.comment != null) {
1187: s.append(comment("modify column for field " + c.comment));
1188: }
1189: if (comments && isCommentSupported() && c.comment == null) {
1190: s.append(comment("modify column " + c.name));
1191: }
1192: s.append("\n");
1193: s.append("ALTER TABLE ");
1194: s.append(t.name);
1195: s.append(" ALTER ");
1196: s.append(c.name);
1197: s.append(" SET DATA TYPE ");
1198: appendColumnType(c, s);
1199:
1200: }
1201:
1202: /**
1203: * Append an 'drop constraint' statement for c.
1204: */
1205: protected void appendRefDropConstraint(CharBuf s, JdbcConstraint c,
1206: boolean comments) {
1207: // if (comments && isCommentSupported()) {
1208: // s.append(comment("dropping unknown constraint " + c.name));
1209: // s.append('\n');
1210: // }
1211: s.append("ALTER TABLE ");
1212: s.append(c.src.name);
1213: s.append(" DROP FOREIGN KEY ");
1214: s.append(c.name);
1215: }
1216:
1217: /**
1218: * Generate a 'drop index' statement for idx.
1219: */
1220: protected void appendDropIndex(CharBuf s, JdbcTable t,
1221: JdbcIndex idx, boolean comments) {
1222: // if (comments && isCommentSupported()) {
1223: // s.append(comment("dropping unknown index "+ idx.name));
1224: // s.append('\n');
1225: // }
1226: s.append("DROP INDEX ");
1227: s.append(idx.name);
1228: }
1229:
1230: /**
1231: * Add the primary key constraint in isolation.
1232: */
1233: protected void addPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
1234: s.append("ALTER TABLE ");
1235: s.append(t.name);
1236: s.append(" ADD ");
1237: appendPrimaryKeyConstraint(t, s);
1238: }
1239:
1240: /**
1241: * Drop the primary key constraint in isolation.
1242: */
1243: protected void dropPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
1244: s.append("ALTER TABLE ");
1245: s.append(t.name);
1246: s.append(" DROP PRIMARY KEY");
1247: }
1248:
1249: boolean isDirectDropColumnSupported() {
1250: return false;
1251: }
1252:
1253: boolean isDirectTypeColumnChangesSupported(JdbcColumn ourCol,
1254: JdbcColumn dbCol) {
1255: return false;
1256: }
1257:
1258: boolean isDirectNullColumnChangesSupported() {
1259: return false;
1260: }
1261:
1262: boolean isDirectScaleColumnChangesSupported(JdbcColumn ourCol,
1263: JdbcColumn dbCol) {
1264: return false;
1265: }
1266:
1267: boolean isDirectLenghtColumnChangesSupported(JdbcColumn ourCol,
1268: JdbcColumn dbCol) {
1269: if (ourCol.jdbcType == java.sql.Types.VARCHAR
1270: && dbCol.jdbcType == java.sql.Types.VARCHAR) {
1271: if (dbCol.length < ourCol.length) {
1272: return true;
1273: }
1274: }
1275: return false;
1276: }
1277:
1278: protected void fixColumnsNonDirect(TableDiff tableDiff,
1279: PrintWriter out) {
1280:
1281: JdbcTable ourTable = tableDiff.getOurTable();
1282: String tempTableName = getTempTableName(ourTable, 128);
1283:
1284: CharBuf s = new CharBuf();
1285: s.append("CREATE TABLE ");
1286: s.append(tempTableName); //ourTable.name
1287: s.append(" (");
1288: JdbcColumn[] cols = ourTable.getColsForCreateTable();
1289: int nc = cols.length;
1290: for (int i = 0; i < nc; i++) {
1291: appendCreateColumn(ourTable, cols[i], s, false);
1292: s.append(' ');
1293: }
1294: appendPrimaryKeyConstraint(ourTable, s);
1295: s.append(")");
1296: s.append(getRunCommand());
1297:
1298: s.append("INSERT INTO ");
1299: s.append(tempTableName); //ourTable.name
1300: s.append(" (");
1301: for (int i = 0; i < nc; i++) {
1302: s.append(cols[i].name);
1303: if ((i + 1) != nc) {
1304: s.append(", ");
1305: }
1306: }
1307: s.append(") ");
1308:
1309: s.append("\n");//new line
1310:
1311: s.append("SELECT ");
1312: for (int i = 0; i < nc; i++) {
1313: ColumnDiff diff = getColumnDiffForName(tableDiff,
1314: cols[i].name);
1315: if (diff == null) {
1316: if (i != 0) {
1317: s.append(" ");
1318: }
1319: s.append(cols[i].name);
1320: } else {
1321: if (diff.isMissingCol()) {
1322: if (diff.getOurCol().nulls) {
1323: if (i != 0) {
1324: s.append(" ");
1325: }
1326: s.append("CAST(NULL");
1327: s.append(" AS ");
1328: appendColumnType(cols[i], s);
1329: s.append(")");
1330:
1331: } else {
1332: if (i != 0) {
1333: s.append(" ");
1334: }
1335: s.append(getDefaultForType(diff.getOurCol()));
1336: }
1337:
1338: } else if ((diff.isLenghtDiff() || diff.isScaleDiff() || diff
1339: .isTypeDiff())
1340: && diff.isNullDiff()) {
1341: if (cols[i].nulls) {
1342: if (i != 0) {
1343: s.append(" ");
1344: }
1345: s.append("CAST(");
1346: s.append(cols[i].name);
1347: s.append(" AS ");
1348: appendColumnType(cols[i], s);
1349: s.append(")");
1350: } else {
1351: if (i != 0) {
1352: s.append(" ");
1353: }
1354: s.append("CASE ");
1355: s.append("\n");//new line
1356: s.append(" WHEN ");
1357: s.append(cols[i].name);
1358: s.append(" IS NOT NULL THEN CAST(");
1359: s.append(cols[i].name);
1360: s.append(" AS ");
1361: appendColumnType(cols[i], s);
1362: s.append(")");
1363: s.append("\n");//new line
1364: s.append(" ELSE CAST(");
1365: s.append(getDefaultForType(diff.getOurCol()));
1366: s.append(" AS ");
1367: appendColumnType(cols[i], s);
1368: s.append(")");
1369: s.append("\n");//new line
1370: s.append(" END CASE");
1371: }
1372:
1373: } else if ((diff.isLenghtDiff() || diff.isScaleDiff() || diff
1374: .isTypeDiff())
1375: && !diff.isNullDiff()) {
1376: if (i != 0) {
1377: s.append(" ");
1378: }
1379: s.append("CAST(");
1380: s.append(cols[i].name);
1381: s.append(" AS ");
1382: appendColumnType(cols[i], s);
1383: s.append(")");
1384: } else if (diff.isNullDiff()) {
1385: if (cols[i].nulls) {
1386: if (i != 0) {
1387: s.append(" ");
1388: }
1389: s.append(cols[i].name);
1390: } else {
1391: if (i != 0) {
1392: s.append(" ");
1393: }
1394: s.append("CASE ");
1395: s.append("\n");//new line
1396: s.append(" WHEN ");
1397: s.append(cols[i].name);
1398: s.append(" IS NOT NULL THEN ");
1399: s.append(cols[i].name);
1400: s.append("\n");//new line
1401: s.append(" ELSE ");
1402: s.append(getDefaultForType(diff.getOurCol()));
1403: s.append("\n");//new line
1404: s.append(" END CASE");
1405: }
1406: }
1407: }
1408:
1409: if ((i + 1) != nc) {
1410: s.append(", ");
1411: s.append("\n");//new line
1412: }
1413: }
1414: s.append("\n");//new line
1415: s.append(" FROM ");
1416: s.append(ourTable.name);
1417: s.append(getRunCommand());
1418:
1419: s.append("DROP TABLE ");
1420: s.append(ourTable.name);
1421: s.append(getRunCommand());
1422:
1423: s.append("RENAME TABLE ");
1424: s.append(tempTableName);
1425: s.append(" TO ");
1426: s.append(ourTable.name);
1427: s.append(getRunCommand());
1428:
1429: out.println(s.toString());
1430:
1431: }
1432:
1433: /**
1434: * Drop a Sequence column to implement a Set
1435: */
1436: protected void dropSequenceColumn(JdbcTable t, JdbcColumn c,
1437: CharBuf s, boolean comments) {
1438: String tempTableName = getTempTableName(t, 128);
1439:
1440: // s.append(comment("create a temp table to store old table values."));
1441: // s.append("\n");
1442: s.append("CREATE TABLE ");
1443: s.append(tempTableName);
1444: s.append(" (\n");
1445: JdbcColumn[] cols = t.getColsForCreateTable();
1446: int nc = cols.length;
1447: boolean first = true;
1448: for (int i = 0; i < nc; i++) {
1449: if (first)
1450: first = false;
1451: else
1452: s.append("\n");
1453: s.append(" ");
1454: appendCreateColumn(t, cols[i], s, comments);
1455: }
1456: s.append("\n ");
1457: appendPrimaryKeyConstraint(t, s);
1458: s.append("\n)");
1459: s.append(getRunCommand());
1460:
1461: // s.append(comment("insert a distinct list into the temp table."));
1462: // s.append("\n");
1463: s.append("INSERT INTO ");
1464: s.append(tempTableName);
1465: s.append("(");
1466: for (int i = 0; i < nc; i++) {
1467: s.append(cols[i].name);
1468: if ((i + 1) != nc) {
1469: s.append(", ");
1470: }
1471: }
1472: s.append(")");
1473: s.append("\nSELECT DISTINCT ");
1474: for (int i = 0; i < nc; i++) {
1475: if (i != 0) {
1476: s.append("\n ");
1477: }
1478: s.append(cols[i].name);
1479: if ((i + 1) != nc) {
1480: s.append(", ");
1481: }
1482: }
1483: s.append("\n FROM ");
1484: s.append(t.name);
1485:
1486: s.append(getRunCommand());
1487:
1488: // s.append(comment("drop main table."));
1489: // s.append("\n");
1490: s.append("DROP TABLE ");
1491: s.append(t.name);
1492: s.append(getRunCommand());
1493:
1494: // s.append(comment("rename temp table to main table."));
1495: // s.append("\n");
1496: s.append("RENAME TABLE ");
1497: s.append(tempTableName);
1498: s.append(" TO ");
1499: s.append(t.name);
1500:
1501: }
1502:
1503: /**
1504: * Add a Sequence column to implement a list
1505: */
1506: protected void addSequenceColumn(JdbcTable t, JdbcColumn c,
1507: CharBuf s, boolean comments) {
1508:
1509: String mainTempTableName = getTempTableName(t, 128);
1510: String minTempTableName = getTempTableName(t, 128);
1511: String identityColumnName = getTempColumnName(t);
1512:
1513: JdbcColumn indexColumn = null;
1514: JdbcColumn sequenceColumn = null;
1515: JdbcColumn[] cols = t.getColsForCreateTable();
1516: int nc = cols.length;
1517: for (int i = 0; i < nc; i++) {
1518: if (isAddSequenceColumn(cols[i])) {
1519: } else if (t.isInPrimaryKey(cols[i].name)) {
1520: indexColumn = cols[i];
1521: }
1522: }
1523:
1524: // s.append(comment("Generate a sequence number so that we can implement a List."));
1525: // s.append("\n");
1526: // s.append(comment("create a temp table with a extra identity column."));
1527: // s.append("\n");
1528: s.append("CREATE TABLE ");
1529: s.append(mainTempTableName);
1530: s.append(" (\n ");
1531: // create identity column
1532: s.append(identityColumnName);
1533: s.append(" INTEGER GENERATED ALWAYS AS IDENTITY,");
1534: for (int i = 0; i < nc; i++) {
1535: s.append("\n ");
1536: appendCreateColumn(t, cols[i], s, comments);
1537: }
1538: int lastIndex = s.toString().lastIndexOf(',');
1539: s.replace(lastIndex, lastIndex + 1, ' ');// we take the last ',' out.
1540: s.append("\n)");
1541:
1542: s.append(getRunCommand());
1543:
1544: // s.append(comment("insert a '0' in the sequence column and copy the rest of the old table into the temp table."));
1545: // s.append("\n");
1546: s.append("INSERT INTO ");
1547: s.append(mainTempTableName);
1548: s.append("(");
1549: for (int i = 0; i < nc; i++) {
1550: s.append(cols[i].name);
1551: if ((i + 1) != nc) {
1552: s.append(", ");
1553: }
1554: }
1555: s.append(")");
1556: s.append("\nSELECT ");
1557: for (int i = 0; i < nc; i++) {
1558: if (i != 0) {
1559: s.append("\n ");
1560: }
1561: if (isAddSequenceColumn(cols[i])) {
1562: s.append('0');
1563: } else {
1564: s.append(cols[i].name);
1565: }
1566: if ((i + 1) != nc) {
1567: s.append(", ");
1568: }
1569: }
1570: s.append("\n FROM ");
1571: s.append(t.name);
1572: s.append("\n ORDER BY ");
1573: s.append(indexColumn.name);
1574:
1575: s.append(getRunCommand());
1576:
1577: // s.append(comment("create a temp table to store the minimum id."));
1578: // s.append("\n");
1579: s.append("CREATE TABLE ");
1580: s.append(minTempTableName);
1581: s.append(" (\n ");
1582: s.append(indexColumn.name);
1583: s.append(' ');
1584: appendColumnType(indexColumn, s);
1585: appendCreateColumnNulls(t, indexColumn, s);
1586: s.append(",\n ");
1587: s.append("min_id");
1588: s.append(" INTEGER\n)");
1589:
1590: s.append(getRunCommand());
1591:
1592: // s.append(comment("store the minimum id."));
1593: // s.append("\n");
1594: s.append("INSERT INTO ");
1595: s.append(minTempTableName);
1596: s.append(" (");
1597: s.append(indexColumn.name);
1598: s.append(", ");
1599: s.append("min_id");
1600: s.append(")\n");
1601: s.append("SELECT ");
1602: s.append(indexColumn.name);
1603: s.append(",\n ");
1604: s.append("MIN(");
1605: s.append(identityColumnName);
1606: s.append(")\n");
1607: s.append(" FROM ");
1608: s.append(mainTempTableName);
1609: s.append("\n");
1610: s.append(" GROUP BY ");
1611: s.append(indexColumn.name);
1612:
1613: s.append(getRunCommand());
1614:
1615: // s.append(comment("drop main table " + t.name + "."));
1616: // s.append("\n");
1617: s.append("DROP TABLE ");
1618: s.append(t.name);
1619:
1620: s.append(getRunCommand());
1621:
1622: // s.append(comment("recreate table " + t.name + "."));
1623: // s.append("\n");
1624: s.append("CREATE TABLE ");
1625: s.append(t.name);
1626: s.append(" (\n");
1627: boolean first = true;
1628: for (int i = 0; i < nc; i++) {
1629: if (first)
1630: first = false;
1631: else
1632: s.append("\n");
1633: s.append(" ");
1634: appendCreateColumn(t, cols[i], s, comments);
1635: }
1636: s.append("\n ");
1637: appendPrimaryKeyConstraint(t, s);
1638: s.append("\n)");
1639: appendTableType(t, s);
1640:
1641: s.append(getRunCommand());
1642:
1643: // s.append(comment("populate table " + t.name + " with the new sequence column."));
1644: // s.append("\n");
1645: s.append("INSERT INTO ");
1646: s.append(t.name);
1647: s.append("(");
1648: for (int i = 0; i < nc; i++) {
1649: s.append(cols[i].name);
1650: if ((i + 1) != nc) {
1651: s.append(", ");
1652: }
1653: }
1654: s.append(")");
1655: s.append("\nSELECT ");
1656: for (int i = 0; i < nc; i++) {
1657: if (i != 0) {
1658: s.append("\n ");
1659: }
1660:
1661: if (isAddSequenceColumn(cols[i])) {
1662: s.append("(a.");
1663: s.append(identityColumnName);
1664: s.append(" - b.min_id)");
1665: } else {
1666: s.append("a.");
1667: s.append(cols[i].name);
1668: }
1669:
1670: if ((i + 1) != nc) {
1671: s.append(", ");
1672: }
1673: }
1674: s.append("\n FROM ");
1675: s.append(mainTempTableName);
1676: s.append(" a,\n ");
1677: s.append(minTempTableName);
1678: s.append(" b\n WHERE a.");
1679: s.append(indexColumn.name);
1680: s.append(" = b.");
1681: s.append(indexColumn.name);
1682:
1683: s.append(getRunCommand());
1684:
1685: // s.append(comment("drop temp tables."));
1686: // s.append("\n");
1687: s.append("DROP TABLE ");
1688: s.append(mainTempTableName);
1689: s.append(getRunCommand());
1690:
1691: s.append("DROP TABLE ");
1692: s.append(minTempTableName);
1693: s.append(getRunCommand());
1694: }
1695: }
|