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