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.DateTimestampConverter;
0015: import com.versant.core.jdbc.sql.diff.ColumnDiff;
0016: import com.versant.core.jdbc.sql.diff.ControlParams;
0017: import com.versant.core.jdbc.sql.diff.TableDiff;
0018: import com.versant.core.jdbc.sql.exp.SqlExp;
0019: import com.versant.core.util.CharBuf;
0020:
0021: import java.io.PrintWriter;
0022: import java.sql.*;
0023: import java.util.*;
0024: import java.util.Date;
0025:
0026: import org.polepos.teams.jdo.*;
0027:
0028: import com.versant.core.common.BindingSupportImpl;
0029:
0030: /**
0031: * A driver for Pointbase.
0032: */
0033: public class PointbaseSqlDriver extends SqlDriver {
0034:
0035: public PointbaseSqlDriver() {
0036: VoaEdited.exception();
0037: }
0038:
0039: /**
0040: * Get the name of this driver.
0041: */
0042: public String getName() {
0043: return "pointbase";
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: return new JdbcTypeMapping("BOOLEAN", 0, 0,
0057: JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE, null);
0058: case Types.TINYINT:
0059: return new JdbcTypeMapping("TINYINT", 0, 0,
0060: JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE, null);
0061: case Types.BIGINT:
0062: return new JdbcTypeMapping("BIGINT", 0, 0,
0063: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE, null);
0064: case Types.LONGVARCHAR:
0065: return new JdbcTypeMapping("LONG VARCHAR", 0, 0,
0066: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE, null);
0067: case Types.CLOB:
0068: return new JdbcTypeMapping("CLOB", 1024, 0,
0069: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE, null);
0070: case Types.VARBINARY:
0071: case Types.BLOB:
0072: case Types.LONGVARBINARY:
0073: return new JdbcTypeMapping("BLOB", 1024, 0,
0074: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
0075: bytesConverterFactory);
0076: }
0077: return super .getTypeMapping(jdbcType);
0078: }
0079:
0080: /**
0081: * Get the default field mappings for this driver. These map java classes
0082: * to column properties. Subclasses should override this, call super() and
0083: * replace mappings as needed.
0084: */
0085: public HashMap getJavaTypeMappings() {
0086: HashMap ans = super .getJavaTypeMappings();
0087:
0088: DateTimestampConverter.Factory dtcf = new DateTimestampConverter.Factory();
0089: ((JdbcJavaTypeMapping) ans.get(Date.class))
0090: .setConverterFactory(dtcf);
0091:
0092: return ans;
0093: }
0094:
0095: public boolean isClearBatchRequired() {
0096: return true;
0097: }
0098:
0099: /**
0100: * Does the JDBC driver support statement batching?
0101: */
0102: public boolean isInsertBatchingSupported() {
0103: return true;
0104: }
0105:
0106: /**
0107: * Does the JDBC driver support statement batching for updates?
0108: */
0109: public boolean isUpdateBatchingSupported() {
0110: return true;
0111: }
0112:
0113: /**
0114: * Does the JDBC driver support scrollable result sets?
0115: */
0116: public boolean isScrollableResultSetSupported() {
0117: return true;
0118: }
0119:
0120: public boolean isPreparedStatementPoolingOK() {
0121: return false;
0122: }
0123:
0124: /**
0125: * Does this driver use the ANSI join syntax (i.e. the join clauses appear
0126: * in the from list e.g. postgres)?
0127: */
0128: public boolean isAnsiJoinSyntax() {
0129: return true;
0130: }
0131:
0132: /**
0133: * Is null a valid value for a column with a foreign key constraint?
0134: */
0135: public boolean isNullForeignKeyOk() {
0136: return true;
0137: }
0138:
0139: /**
0140: * Should indexes be used for columns in the order by list that are
0141: * also in the select list? This is used for databases that will not
0142: * order by a column that is duplicated in the select list (e.g. Oracle).
0143: */
0144: public boolean isUseIndexesForOrderCols() {
0145: return true;
0146: }
0147:
0148: /**
0149: * Create a default name generator instance for JdbcStore's using this
0150: * driver.
0151: */
0152: public JdbcNameGenerator createJdbcNameGenerator() {
0153: DefaultJdbcNameGenerator n = createDefaultJdbcNameGenerator();
0154: n.setMaxColumnNameLength(31);
0155: n.setMaxTableNameLength(31);
0156: n.setMaxConstraintNameLength(31);
0157: n.setMaxIndexNameLength(31);
0158: return n;
0159: }
0160:
0161: /**
0162: * Append the allow nulls part of the definition for a column in a
0163: * create table statement.
0164: */
0165: protected void appendCreateColumnNulls(JdbcTable t, JdbcColumn c,
0166: CharBuf s) {
0167: if (!c.nulls)
0168: s.append(" NOT NULL");
0169: }
0170:
0171: /**
0172: * Add the primary key constraint part of a create table statement to s.
0173: */
0174: protected void appendPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
0175: s.append("CONSTRAINT ");
0176: s.append(t.pkConstraintName);
0177: s.append(" PRIMARY KEY (");
0178: appendColumnNameList(t.pk, s);
0179: s.append(')');
0180: }
0181:
0182: /**
0183: * Append an 'add constraint' statement for c.
0184: */
0185: protected void appendRefConstraint(CharBuf s, JdbcConstraint c) {
0186: s.append("ALTER TABLE ");
0187: s.append(c.src.name);
0188: s.append(" ADD CONSTRAINT ");
0189: s.append(c.name);
0190: s.append(" FOREIGN KEY (");
0191: appendColumnNameList(c.srcCols, s);
0192: s.append(") REFERENCES ");
0193: s.append(c.dest.name);
0194: s.append('(');
0195: appendColumnNameList(c.dest.pk, s);
0196: s.append(") MATCH FULL");
0197: }
0198:
0199: /**
0200: * Write an SQL statement to a script with appropriate separator.
0201: */
0202: protected void print(PrintWriter out, String sql) {
0203: out.print(sql);
0204: out.println(";");
0205: out.println();
0206: }
0207:
0208: /**
0209: * Append the from list entry for a table.
0210: */
0211: public void appendSqlFrom(JdbcTable table, String alias, CharBuf s) {
0212: s.append(table.name);
0213: if (alias != null) {
0214: s.append(" AS ");
0215: s.append(alias);
0216: }
0217: }
0218:
0219: /**
0220: * Append the from list entry for a table that is the right hand table
0221: * in a join i.e. it is being joined to.
0222: *
0223: * @param exp This is the expression that joins the tables
0224: * @param outer If true then this is an outer join
0225: */
0226: public void appendSqlFromJoin(JdbcTable table, String alias,
0227: SqlExp exp, boolean outer, CharBuf s) {
0228: if (outer) {
0229: s.append(" LEFT OUTER JOIN ");
0230: } else {
0231: s.append(" JOIN ");
0232: }
0233: s.append(table.name);
0234: if (alias != null) {
0235: s.append(" AS ");
0236: s.append(alias);
0237: }
0238: if (exp != null) {
0239: s.append(" ON (");
0240: exp.appendSQL(this , s, null);
0241: s.append(')');
0242: }
0243: }
0244:
0245: /**
0246: * Append the column type part of a create table statement for a column.
0247: */
0248: protected void appendColumnType(JdbcColumn c, CharBuf s,
0249: boolean useZeroScale) {
0250: if (c.sqlType == null) {
0251: throw BindingSupportImpl.getInstance().internal(
0252: "sqlType is null: " + c);
0253: }
0254: s.append(c.sqlType);
0255: if (c.sqlType.equals("BLOB") || c.sqlType.equals("CLOB")) {
0256: if (c.length == 0) {
0257: s.append('(');
0258: s.append(1024);
0259: s.append(" K)");
0260: } else {
0261: s.append('(');
0262: s.append(c.length);
0263: s.append(" K)");
0264: }
0265: } else if (c.length != 0 || c.scale != 0) {
0266: s.append('(');
0267: s.append(c.length);
0268: if (c.scale != 0) {
0269: s.append(',');
0270: s.append(c.scale);
0271: }
0272: s.append(')');
0273: }
0274: }
0275:
0276: public String getConnectionValidateSQL() {
0277: return "SELECT databasename FROM sysdatabases";
0278: }
0279:
0280: /**
0281: * Get con ready for a getQueryPlan call. Example: On Sybase this will
0282: * do a 'set showplan 1' and 'set noexec 1'. Also make whatever changes
0283: * are necessary to sql to prepare it for a getQueryPlan call. Example:
0284: * On Oracle this will prepend 'explain '. The cleanupForGetQueryPlan
0285: * method must be called in a finally block if this method is called.
0286: *
0287: * @see #cleanupForGetQueryPlan
0288: * @see #getQueryPlan
0289: */
0290: public String prepareForGetQueryPlan(Connection con, String sql) {
0291: try {
0292: Statement statement = con.createStatement();
0293: statement.execute("SET PLANONLY ON");
0294: } catch (SQLException sqle) {
0295: sqle.printStackTrace();
0296: }
0297: return sql;
0298: }
0299:
0300: /**
0301: * Get the query plan for ps and cleanup anything done in
0302: * prepareForGetQueryPlan. Return null if this is not supported.
0303: *
0304: * @see #prepareForGetQueryPlan
0305: * @see #cleanupForGetQueryPlan
0306: */
0307: public String getQueryPlan(Connection con, PreparedStatement ps) {
0308: StringBuffer buff = new StringBuffer();
0309: Statement stat = null;
0310: ResultSet rs = null;
0311: try {
0312:
0313: ps.execute();
0314: stat = con.createStatement();
0315: stat.execute("SET PLANONLY OFF");
0316: rs = stat.executeQuery("select * from PLAN_TABLE");
0317:
0318: double totalCost = 0;
0319: int block = -1;
0320: while (rs != null && rs.next()) {
0321: int blockNum = rs.getInt("BLOCK");// BLOCK
0322: int stepNum = rs.getInt("STEP");// STEP
0323: String operation = rs.getString("OPERATION");//OPERATION
0324: String accessMethod = rs.getString("ACCESS_METHOD");//ACCESS_METHOD
0325: String tablename = rs.getString("TABLENAME");//TABLENAME
0326: String indexname = rs.getString("INDEXNAME");//INDEXNAME
0327: double cost = rs.getDouble("COST");// COST
0328: double outputRows = rs.getDouble("OUTPUTROWS");// OUTPUTROWS
0329: String expression = rs.getString("EXPRESSIONS");//EXPRESSIONS
0330:
0331: if (block != blockNum) {
0332: if (block != -1) {
0333: buff.append("\n");
0334: }
0335: buff.append("Block " + blockNum);
0336: block = blockNum;
0337: }
0338: buff.append("\n Step " + stepNum);
0339:
0340: buff.append("\n " + operation);
0341: if (accessMethod != null) {
0342: buff.append(" using " + accessMethod);
0343: }
0344: if (tablename != null) {
0345: buff.append("\n on table " + tablename);
0346: if (indexname != null) {
0347: buff.append(" using index " + indexname);
0348: }
0349: }
0350: buff
0351: .append("\n output rows : "
0352: + outputRows);
0353: if (expression != null) {
0354: buff.append("\n expression : "
0355: + expression);
0356: }
0357: buff.append("\n cost = " + cost);
0358: totalCost += cost;
0359: }
0360:
0361: buff.append("\n\nTOTAL COST = " + totalCost);
0362: } catch (Exception sqle) {
0363: // sqle.printStackTrace();
0364: } finally {
0365: try {
0366: rs.close();
0367: stat.close();
0368: } catch (Exception e) {
0369: }
0370: }
0371: return buff.toString();
0372: }
0373:
0374: /**
0375: * Cleanup anything done in prepareForGetQueryPlan. Example: On Sybase this
0376: * will do a 'set showplan 0' and 'set noexec 0'.
0377: *
0378: * @see #prepareForGetQueryPlan
0379: * @see #getQueryPlan
0380: */
0381: public void cleanupForGetQueryPlan(Connection con) {
0382: try {
0383: Statement statement = con.createStatement();
0384: statement.execute("SET PLANONLY OFF");
0385: statement.execute("DELETE FROM PLAN_TABLE");
0386: statement.execute("DELETE FROM PLAN_QUERIES");
0387:
0388: } catch (SQLException sqle) {
0389: sqle.printStackTrace();
0390: }
0391: }
0392:
0393: /**
0394: * Retrieve the value of the autoinc or serial column for a row just
0395: * inserted using stat on con.
0396: */
0397: public Object getAutoIncColumnValue(JdbcTable classTable,
0398: Connection con, Statement stat) throws SQLException {
0399:
0400: VoaEdited.exception();
0401: return null;
0402:
0403: // ResultSet rs = null;
0404: // try {
0405: // // must do the cast so that this works on JDK 1.3
0406: // if (stat instanceof com.pointbase.net.netJDBCStatement) {
0407: // // client server version
0408: // rs = ((com.pointbase.net.netJDBCStatement)stat).getGeneratedKeys();
0409: // } else {
0410: // // embedded version
0411: // rs = ((com.pointbase.jdbc.jdbcStatement)stat).getGeneratedKeys();
0412: // }
0413: // if (!rs.next()) {
0414: // throw BindingSupportImpl.getInstance().datastore("No row returned for " +
0415: // "stat.getGeneratedKeys() after insert for identity column: " +
0416: // classTable.name + "." + classTable.pk[0].name);
0417: // }
0418: // return classTable.pk[0].get(rs, 1);
0419: // } finally {
0420: // try {
0421: // if (rs != null) rs.close();
0422: // } catch (SQLException e) {
0423: // // ignore
0424: // }
0425: // }
0426: }
0427:
0428: /**
0429: * Format a comment.
0430: */
0431: public String comment(String msg) {
0432: return "/* " + msg + " */";
0433: }
0434:
0435: /**
0436: * Get whatever needs to be appended to a SELECT statement to lock the
0437: * rows if this makes sense for the database. This must have a leading
0438: * space if not empty.
0439: */
0440: public char[] getSelectForUpdate() {
0441: return null;
0442: }
0443:
0444: public boolean checkDDL(ArrayList tables, Connection con,
0445: PrintWriter errors, PrintWriter fix, ControlParams params)
0446: throws SQLException {
0447: params.setCheckIndex(false);
0448: return super .checkDDL(tables, con, errors, fix, params);
0449: }
0450:
0451: /**
0452: * Get the JdbcTable from the database for the given database connection and table name.
0453: */
0454: public HashMap getDBSchema(Connection con, ControlParams params)
0455: throws SQLException {
0456:
0457: HashMap jdbcTableMap = new HashMap(); // main map of jdbc tables
0458:
0459: // now we do columns
0460: String tableName = null;
0461: String colSql = " SELECT TABLENAME AS TABLE_NAME,\n"
0462: + " COLUMNNAME AS COLUMN_NAME,\n"
0463: + " CAST(COLUMNTYPE AS SMALLINT) AS DATA_TYPE,\n"
0464: + " SYSSQLDATATYPES.NAME AS TYPE_NAME,\n"
0465: + " COLUMNLENGTH AS COLUMN_SIZE,\n"
0466: + " COLUMNSCALE AS DECIMAL_DIGITS,\n"
0467: + " ISNULLABLE AS NULLABLE,\n"
0468: + " ORDINALPOSITION + 1 AS ORDINAL_POSITION\n"
0469: + " FROM POINTBASE.SYSTABLES, \n"
0470: + " POINTBASE.SYSCOLUMNS, \n"
0471: + " POINTBASE.SYSSCHEMATA,\n"
0472: + " POINTBASE.SYSSQLDATATYPES\n"
0473: + " WHERE SYSTABLES.TABLEID = SYSCOLUMNS.TABLEID\n"
0474: + " AND SYSTABLES.SCHEMAID = SYSSCHEMATA.SCHEMAID\n"
0475: + " AND SYSSQLDATATYPES.SQLTYPE = COLUMNTYPE\n"
0476: + " AND SYSSCHEMATA.SCHEMAID <> 4\n"
0477: + " ORDER BY TABLE_NAME, ORDINAL_POSITION";
0478: Statement statCol = con.createStatement();
0479: ResultSet rsColumn = statCol.executeQuery(colSql);
0480: ArrayList currentColumns = null;
0481:
0482: while (rsColumn.next()) {
0483:
0484: String temptableName = rsColumn.getString("TABLE_NAME");
0485:
0486: if (!isValidSchemaTable(temptableName)) {
0487: continue;
0488: }
0489:
0490: if (tableName == null) { // this is the first one
0491: tableName = temptableName;
0492: currentColumns = new ArrayList();
0493: JdbcTable jdbcTable = new JdbcTable();
0494: jdbcTable.name = tableName;
0495: jdbcTableMap.put(tableName, jdbcTable);
0496: }
0497:
0498: if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
0499: JdbcColumn[] jdbcColumns = new JdbcColumn[currentColumns
0500: .size()];
0501: currentColumns.toArray(jdbcColumns);
0502: JdbcTable jdbcTable0 = (JdbcTable) jdbcTableMap
0503: .get(tableName);
0504: jdbcTable0.cols = jdbcColumns;
0505:
0506: tableName = temptableName;
0507: currentColumns.clear();
0508: JdbcTable jdbcTable1 = new JdbcTable();
0509: jdbcTable1.name = tableName;
0510: jdbcTableMap.put(tableName, jdbcTable1);
0511: }
0512:
0513: JdbcColumn col = new JdbcColumn();
0514:
0515: col.name = rsColumn.getString("COLUMN_NAME");
0516: col.sqlType = rsColumn.getString("TYPE_NAME");
0517: col.jdbcType = rsColumn.getInt("DATA_TYPE");
0518: col.length = rsColumn.getInt("COLUMN_SIZE");
0519: col.scale = rsColumn.getInt("DECIMAL_DIGITS");
0520: col.nulls = rsColumn.getBoolean("NULLABLE");
0521:
0522: if (col.jdbcType == 16) {
0523: col.jdbcType = java.sql.Types.BIT;
0524: } else if (col.jdbcType == 9) {
0525: col.jdbcType = java.sql.Types.BIGINT;
0526: } else if (col.jdbcType == 40) {
0527: col.jdbcType = java.sql.Types.CLOB;
0528: } else if (col.jdbcType == 30) {
0529: col.jdbcType = java.sql.Types.BLOB;
0530: }
0531:
0532: switch (col.jdbcType) {
0533: case java.sql.Types.BIT:
0534: case java.sql.Types.TINYINT:
0535: case java.sql.Types.SMALLINT:
0536: case java.sql.Types.INTEGER:
0537: case java.sql.Types.BIGINT:
0538: case java.sql.Types.DATE:
0539: case java.sql.Types.TIME:
0540: case java.sql.Types.TIMESTAMP:
0541: col.length = 0;
0542: col.scale = 0;
0543: default:
0544: }
0545:
0546: currentColumns.add(col);
0547: }
0548: // we fin last table
0549: if (currentColumns != null) {
0550: JdbcColumn[] lastJdbcColumns = new JdbcColumn[currentColumns
0551: .size()];
0552: currentColumns.toArray(lastJdbcColumns);
0553: JdbcTable colJdbcTable = (JdbcTable) jdbcTableMap
0554: .get(tableName);
0555: colJdbcTable.cols = lastJdbcColumns;
0556: currentColumns.clear();
0557:
0558: }
0559: tableName = null;
0560: // clean up
0561: if (rsColumn != null) {
0562: try {
0563: rsColumn.close();
0564: } catch (SQLException e) {
0565: }
0566: }
0567: if (statCol != null) {
0568: try {
0569: statCol.close();
0570: } catch (SQLException e) {
0571: }
0572: }
0573:
0574: if (!params.checkColumnsOnly()) {
0575: if (params.isCheckPK()) {
0576: // now we do primaryKeys
0577: HashMap pkMap = null;
0578:
0579: String pkSql = "SELECT TABLENAME AS TABLE_NAME, \n"
0580: + " COLUMNNAME AS COLUMN_NAME,\n"
0581: + " SYSINDEXKEYS.ORDINALPOSITION+1 AS KEY_SEQ, \n"
0582: + " INDEXNAME AS PK_NAME \n"
0583: + " FROM POINTBASE.SYSTABLES, \n"
0584: + " POINTBASE.SYSINDEXES, \n"
0585: + " POINTBASE.SYSINDEXKEYS, \n"
0586: + " POINTBASE.SYSCOLUMNS, \n"
0587: + " POINTBASE.SYSSCHEMATA\n"
0588: + " WHERE SYSTABLES.TABLEID = SYSINDEXES.TABLEID\n"
0589: + " AND SYSINDEXES.INDEXID = SYSINDEXKEYS.INDEXID\n"
0590: + " AND SYSCOLUMNS.COLUMNID = SYSINDEXKEYS.COLUMNID\n"
0591: + " AND SYSTABLES.TABLEID = SYSCOLUMNS.TABLEID\n"
0592: + " AND SYSTABLES.SCHEMAID = SYSSCHEMATA.SCHEMAID\n"
0593: + " AND SYSINDEXES.INDEXTYPE = 1\n"
0594: + " AND SYSSCHEMATA.SCHEMAID <> 4\n"
0595: + " ORDER BY 1,4,3";
0596:
0597: Statement statPK = con.createStatement();
0598: ResultSet rsPKs = statPK.executeQuery(pkSql);
0599: int pkCount = 0;
0600: String pkName = null;
0601: while (rsPKs.next()) {
0602: String temptableName = rsPKs.getString(1);
0603:
0604: if (!jdbcTableMap.containsKey(temptableName)) {
0605: continue;
0606: }
0607:
0608: if (tableName == null) { // this is the first one
0609: tableName = temptableName;
0610: pkMap = new HashMap();
0611: }
0612:
0613: if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
0614: JdbcColumn[] pkColumns = new JdbcColumn[pkCount];
0615: int indexOfPKCount = 0;
0616: JdbcTable jdbcTable = (JdbcTable) jdbcTableMap
0617: .get(tableName);
0618: for (int i = 0; i < jdbcTable.cols.length; i++) {
0619: JdbcColumn jdbcColumn = jdbcTable.cols[i];
0620: if (pkMap.containsKey(jdbcColumn.name)) {
0621: pkColumns[indexOfPKCount] = jdbcColumn;
0622: jdbcColumn.pk = true;
0623: indexOfPKCount++;
0624: }
0625: }
0626: jdbcTable.pk = pkColumns;
0627: jdbcTable.pkConstraintName = pkName;
0628:
0629: tableName = temptableName;
0630: pkMap.clear();
0631: pkCount = 0;
0632: }
0633: pkCount++;
0634: pkMap.put(rsPKs.getString(2), null);
0635: pkName = rsPKs.getString(4);
0636: }
0637: JdbcColumn[] pkColumns = new JdbcColumn[pkCount];
0638: int indexOfPKCount = 0;
0639: JdbcTable pkJdbcTable = (JdbcTable) jdbcTableMap
0640: .get(tableName);
0641: if (pkJdbcTable != null) {
0642: for (int i = 0; i < pkJdbcTable.cols.length; i++) {
0643: JdbcColumn jdbcColumn = pkJdbcTable.cols[i];
0644: if (pkMap.containsKey(jdbcColumn.name)) {
0645: pkColumns[indexOfPKCount] = jdbcColumn;
0646: jdbcColumn.pk = true;
0647: indexOfPKCount++;
0648: }
0649: }
0650: pkJdbcTable.pk = pkColumns;
0651: pkJdbcTable.pkConstraintName = pkName;
0652: }
0653: tableName = null;
0654: // clean up
0655: if (rsPKs != null) {
0656: try {
0657: rsPKs.close();
0658: } catch (SQLException e) {
0659: }
0660: }
0661: if (statPK != null) {
0662: try {
0663: statPK.close();
0664: } catch (SQLException e) {
0665: }
0666: }
0667: }
0668: if (params.isCheckIndex()) {
0669: // now we do index /////////////////////////////////////////////////////////////////////////
0670: String indexSql =
0671:
0672: "SELECT TABLENAME AS TABLE_NAME, \n"
0673: + " COLUMNNAME AS COLUMN_NAME,\n"
0674: + " INDEXNAME AS INDEX_NAME, \n"
0675: + " INDEXTYPE AS NON_UNIQUE, \n"
0676: + " '3' AS TYPE, \n"
0677: + " SYSINDEXKEYS.ORDINALPOSITION + 1 AS ORDINAL_POSITION \n"
0678: + " FROM POINTBASE.SYSTABLES, \n"
0679: + " POINTBASE.SYSINDEXES, \n"
0680: + " POINTBASE.SYSINDEXKEYS, \n"
0681: + " POINTBASE.SYSCOLUMNS\n"
0682: + " WHERE SYSTABLES.TABLEID = SYSINDEXES.TABLEID\n"
0683: + " AND SYSINDEXES.INDEXID = SYSINDEXKEYS.INDEXID\n"
0684: + " AND SYSCOLUMNS.COLUMNID = SYSINDEXKEYS.COLUMNID\n"
0685: + " AND SYSTABLES.TABLEID = SYSCOLUMNS.TABLEID\n"
0686: + " AND SYSINDEXES.INDEXTYPE <> 1 \n"
0687: + " AND NOT SYSINDEXES.INDEXID IN (\n"
0688: + " SELECT SYSREFERENTIALCONSTRAINTS.CONSTRAINTINDEXID \n"
0689: + " FROM SYSREFERENTIALCONSTRAINTS) \n"
0690: + " ORDER BY TABLE_NAME,INDEX_NAME,ORDINAL_POSITION";
0691: Statement statIndex = con.createStatement();
0692: ResultSet rsIndex = statIndex.executeQuery(indexSql);
0693:
0694: HashMap indexNameMap = null;
0695: ArrayList indexes = null;
0696: while (rsIndex.next()) {
0697: String temptableName = rsIndex.getString(1);
0698: if (tableName == null) { // this is the first one
0699: tableName = temptableName;
0700: indexNameMap = new HashMap();
0701: indexes = new ArrayList();
0702: }
0703:
0704: String indexName = rsIndex.getString(3);
0705: JdbcTable tempJdbcTable = (JdbcTable) jdbcTableMap
0706: .get(temptableName);
0707:
0708: if (indexName != null
0709: && !indexName
0710: .equals(tempJdbcTable.pkConstraintName)) {
0711: if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
0712: JdbcTable jdbcTable = (JdbcTable) jdbcTableMap
0713: .get(tableName);
0714: JdbcIndex[] jdbcIndexes = new JdbcIndex[indexes
0715: .size()];
0716: indexes.toArray(jdbcIndexes);
0717: jdbcTable.indexes = jdbcIndexes;
0718:
0719: tableName = temptableName;
0720: indexes.clear();
0721: indexNameMap.clear();
0722:
0723: }
0724: JdbcTable jdbcTable = (JdbcTable) jdbcTableMap
0725: .get(tableName);
0726: if (indexNameMap.containsKey(indexName)) {
0727: JdbcIndex index = null;
0728: for (Iterator iter = indexes.iterator(); iter
0729: .hasNext();) {
0730: JdbcIndex jdbcIndex = (JdbcIndex) iter
0731: .next();
0732: if (jdbcIndex.name.equals(indexName)) {
0733: index = jdbcIndex;
0734: }
0735: }
0736:
0737: JdbcColumn[] tempIndexColumns = index.cols;
0738: JdbcColumn[] indexColumns = new JdbcColumn[tempIndexColumns.length + 1];
0739: System.arraycopy(tempIndexColumns, 0,
0740: indexColumns, 0,
0741: tempIndexColumns.length);
0742: String colName = rsIndex.getString(2);
0743: for (int i = 0; i < jdbcTable.cols.length; i++) {
0744: JdbcColumn jdbcColumn = jdbcTable.cols[i];
0745: if (colName.equals(jdbcColumn.name)) {
0746: indexColumns[tempIndexColumns.length] = jdbcColumn;
0747: jdbcColumn.partOfIndex = true;
0748: }
0749: }
0750: index.setCols(indexColumns);
0751: } else {
0752: indexNameMap.put(indexName, null);
0753: JdbcIndex index = new JdbcIndex();
0754: index.name = indexName;
0755: index.unique = !rsIndex.getBoolean(4);
0756: short indexType = rsIndex.getShort(5);
0757: switch (indexType) {
0758: case DatabaseMetaData.tableIndexClustered:
0759: index.clustered = true;
0760: break;
0761: }
0762: String colName = rsIndex.getString(2);
0763: JdbcColumn[] indexColumns = new JdbcColumn[1];
0764: for (int i = 0; i < jdbcTable.cols.length; i++) {
0765: JdbcColumn jdbcColumn = jdbcTable.cols[i];
0766: if (colName.equals(jdbcColumn.name)) {
0767: indexColumns[0] = jdbcColumn;
0768: jdbcColumn.partOfIndex = true;
0769: }
0770: }
0771: index.setCols(indexColumns);
0772: indexes.add(index);
0773: }
0774: }
0775: }
0776: JdbcTable indexJdbcTable = (JdbcTable) jdbcTableMap
0777: .get(tableName);
0778: if (indexJdbcTable != null) {
0779: JdbcIndex[] jdbcIndexes = new JdbcIndex[indexes
0780: .size()];
0781: indexes.toArray(jdbcIndexes);
0782: indexJdbcTable.indexes = jdbcIndexes;
0783: indexes.clear();
0784: indexNameMap.clear();
0785: }
0786: tableName = null;
0787:
0788: // clean up
0789: if (rsIndex != null) {
0790: try {
0791: rsIndex.close();
0792: } catch (SQLException e) {
0793: }
0794: }
0795: if (statIndex != null) {
0796: try {
0797: statIndex.close();
0798: } catch (SQLException e) {
0799: }
0800: }
0801: }
0802: // end of index ///////////////////////////////////////////////////////////////////////
0803:
0804: if (params.isCheckConstraint()) {
0805:
0806: // now we do forign keys
0807:
0808: String fkSql = "select pt.TABLENAME as PKTABLE_NAME, \n"
0809: + " ft.TABLENAME as FKTABLE_NAME,\n"
0810: + " i.INDEXNAME as FK_NAME,\n"
0811: + " c.COLUMNNAME as FKCOLUMN_NAME,\n"
0812: + " ik.ORDINALPOSITION\n"
0813: + " from SYSREFERENTIALCONSTRAINTS as rc,\n"
0814: + " SYSTABLES as pt, \n"
0815: + " SYSTABLES as ft,\n"
0816: + " SYSINDEXES as i,\n"
0817: + " SYSINDEXKEYS as ik,\n"
0818: + " SYSCOLUMNS as c\n"
0819: + " where rc.CONSTRAINTTABLEID = ft.TABLEID\n"
0820: + " and rc.REFERENCETABLEID = pt.TABLEID\n"
0821: + " and rc.CONSTRAINTINDEXID = i.INDEXID\n"
0822: + " and rc.CONSTRAINTINDEXID = ik.INDEXID\n"
0823: + " and ik.COLUMNID = c.COLUMNID\n"
0824: + " and c.TABLEID = ft.TABLEID\n"
0825: + " ORDER BY 2,3,5";
0826: Statement statFK = con.createStatement();
0827: ResultSet rsFKs = statFK.executeQuery(fkSql);
0828:
0829: HashMap constraintNameMap = null;
0830: ArrayList constraints = null;
0831: while (rsFKs.next()) {
0832: String temptableName = rsFKs.getString(2);
0833: if (tableName == null) { // this is the first one
0834: tableName = temptableName;
0835: constraintNameMap = new HashMap();
0836: constraints = new ArrayList();
0837: }
0838:
0839: if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
0840: JdbcTable jdbcTable = (JdbcTable) jdbcTableMap
0841: .get(tableName);
0842: JdbcConstraint[] jdbcConstraints = new JdbcConstraint[constraints
0843: .size()];
0844: constraints.toArray(jdbcConstraints);
0845: jdbcTable.constraints = jdbcConstraints;
0846:
0847: tableName = temptableName;
0848: constraintNameMap.clear();
0849: constraints.clear();
0850: }
0851:
0852: String fkName = rsFKs.getString(3);
0853: JdbcTable jdbcTable = (JdbcTable) jdbcTableMap
0854: .get(tableName);
0855: if (constraintNameMap.containsKey(fkName)) {
0856: JdbcConstraint constraint = null;
0857: for (Iterator iter = constraints.iterator(); iter
0858: .hasNext();) {
0859: JdbcConstraint jdbcConstraint = (JdbcConstraint) iter
0860: .next();
0861: if (jdbcConstraint.name.equals(fkName)) {
0862: constraint = jdbcConstraint;
0863: }
0864: }
0865:
0866: JdbcColumn[] tempConstraintColumns = constraint.srcCols;
0867: JdbcColumn[] constraintColumns = new JdbcColumn[tempConstraintColumns.length + 1];
0868: System.arraycopy(tempConstraintColumns, 0,
0869: constraintColumns, 0,
0870: tempConstraintColumns.length);
0871: String colName = rsFKs.getString(4);
0872: for (int i = 0; i < jdbcTable.cols.length; i++) {
0873: JdbcColumn jdbcColumn = jdbcTable.cols[i];
0874: if (colName.equals(jdbcColumn.name)) {
0875: constraintColumns[tempConstraintColumns.length] = jdbcColumn;
0876: jdbcColumn.foreignKey = true;
0877: }
0878: }
0879: constraint.srcCols = constraintColumns;
0880: } else {
0881: constraintNameMap.put(fkName, null);
0882: JdbcConstraint constraint = new JdbcConstraint();
0883: constraint.name = fkName;
0884: constraint.src = jdbcTable;
0885: String colName = rsFKs.getString(4);
0886: JdbcColumn[] constraintColumns = new JdbcColumn[1];
0887: for (int i = 0; i < jdbcTable.cols.length; i++) {
0888: JdbcColumn jdbcColumn = jdbcTable.cols[i];
0889: if (colName.equals(jdbcColumn.name)) {
0890: constraintColumns[0] = jdbcColumn;
0891: jdbcColumn.foreignKey = true;
0892: }
0893: }
0894: constraint.srcCols = constraintColumns;
0895: constraint.dest = (JdbcTable) jdbcTableMap
0896: .get(rsFKs.getString(1));
0897: constraints.add(constraint);
0898: }
0899: }
0900: JdbcTable constraintsjdbcTable = (JdbcTable) jdbcTableMap
0901: .get(tableName);
0902: if (constraintsjdbcTable != null) {
0903: JdbcConstraint[] jdbcConstraints = new JdbcConstraint[constraints
0904: .size()];
0905: constraints.toArray(jdbcConstraints);
0906: constraintsjdbcTable.constraints = jdbcConstraints;
0907: }
0908:
0909: if (rsFKs != null) {
0910: try {
0911: rsFKs.close();
0912: } catch (SQLException e) {
0913: }
0914: }
0915: if (statFK != null) {
0916: try {
0917: statFK.close();
0918: } catch (SQLException e) {
0919: }
0920: }
0921:
0922: }
0923: }
0924: HashMap returnMap = new HashMap();
0925: Collection col = jdbcTableMap.values();
0926: for (Iterator iterator = col.iterator(); iterator.hasNext();) {
0927: JdbcTable table = (JdbcTable) iterator.next();
0928: returnMap.put(table.name.toLowerCase(), table);
0929: }
0930: fixAllNames(returnMap);
0931: return returnMap;
0932: }
0933:
0934: public boolean checkScale(JdbcColumn ourCol, JdbcColumn dbCol) {
0935: switch (ourCol.jdbcType) {
0936: case Types.FLOAT:
0937: case Types.REAL:
0938: case Types.DOUBLE:
0939: return true;
0940: default:
0941: return super .checkScale(ourCol, dbCol);
0942: }
0943: }
0944:
0945: public boolean checkLenght(JdbcColumn ourCol, JdbcColumn dbCol) {
0946: switch (ourCol.jdbcType) {
0947: case Types.BLOB:
0948: case Types.BINARY:
0949: case Types.CLOB:
0950: case Types.VARBINARY:
0951: case Types.LONGVARBINARY:
0952: return true;
0953: default:
0954: return super .checkLenght(ourCol, dbCol);
0955: }
0956: }
0957:
0958: /**
0959: * Append a column that needs to be added.
0960: */
0961: protected void appendAddNewColumn(JdbcTable t, JdbcColumn c,
0962: CharBuf s, boolean comments) {
0963: if (comments && isCommentSupported() && c.comment != null) {
0964: s.append(comment("add column for field " + c.comment));
0965: }
0966:
0967: s.append("\n");
0968: if (isAddSequenceColumn(c)) {
0969: addSequenceColumn(t, c, s, comments);
0970: } else {
0971: s.append("ALTER TABLE ");
0972: s.append(t.name);
0973: s.append(" ADD ");
0974: s.append(c.name);
0975: s.append(' ');
0976: appendColumnType(c, s);
0977: appendCreateColumnNulls(t, c, s);
0978: if (!c.nulls) {
0979: s.append(" DEFAULT ");
0980: s.append(getDefaultForType(c));
0981: s.append(getRunCommand());
0982:
0983: s.append("UPDATE ");
0984: s.append(t.name);
0985: s.append(" SET ");
0986: s.append(c.name);
0987: s.append(" = ");
0988: s.append(getDefaultForType(c));
0989: s.append(getRunCommand());
0990: } else {
0991: s.append(getRunCommand());
0992: }
0993: }
0994: }
0995:
0996: /**
0997: * Append a column that needs to be added.
0998: */
0999: protected void appendModifyColumn(TableDiff tableDiff,
1000: ColumnDiff diff, CharBuf s, boolean comments) {
1001: JdbcTable t = tableDiff.getOurTable();
1002: JdbcColumn c = diff.getOurCol();
1003: boolean length = diff.isLenghtDiff();
1004: boolean scale = diff.isScaleDiff();
1005: boolean nulls = diff.isNullDiff();
1006: boolean type = diff.isTypeDiff();
1007: if (comments && isCommentSupported() && c.comment != null) {
1008: s.append(comment("modify column for field " + c.comment));
1009: }
1010: if (comments && isCommentSupported() && c.comment == null) {
1011: s.append(comment("modify column " + c.name));
1012: }
1013:
1014: if (length || scale || type || nulls) {
1015: s.append("\n");
1016: s
1017: .append(comment("################################ WARNING ###################################\n"));
1018:
1019: if (length) {
1020: s
1021: .append(comment("# Altering the lenght of a column for Pointbase, is not yet supported. #\n"));
1022: }
1023: if (scale) {
1024: s
1025: .append(comment("# Altering the scale of a column for Pointbase, is not yet supported. #\n"));
1026: }
1027: if (type) {
1028: s
1029: .append(comment("# Altering the data type of a column for Pointbase, is not yet supported. #\n"));
1030: }
1031: if (nulls) {
1032: s
1033: .append(comment("# Altering the null value of a column for Pointbase, is not yet supported. #\n"));
1034: }
1035: s
1036: .append(comment("############################################################################"));
1037: }
1038: }
1039:
1040: /**
1041: * Append a column that needs to be added.
1042: */
1043: protected void appendDropColumn(TableDiff tableDiff, JdbcColumn c,
1044: CharBuf s, boolean comments) {
1045: if (comments && isCommentSupported()) {
1046: s.append(comment("dropping unknown column " + c.name));
1047: }
1048:
1049: s.append("\n");
1050: if (isDropSequenceColumn(tableDiff, c)) {
1051: dropSequenceColumn(tableDiff.getOurTable(), c, s, comments);
1052: } else {
1053: s.append("ALTER TABLE ");
1054: s.append(tableDiff.getOurTable().name);
1055: s.append(" DROP COLUMN ");
1056: s.append(c.name);
1057: s.append(" CASCADE");
1058: }
1059: }
1060:
1061: /**
1062: * Append an 'drop constraint' statement for c.
1063: */
1064: protected void appendRefDropConstraint(CharBuf s, JdbcConstraint c,
1065: boolean comments) {
1066: // if (comments && isCommentSupported()) {
1067: // s.append(comment("dropping unknown constraint " + c.name));
1068: // s.append('\n');
1069: // }
1070: s.append("ALTER TABLE ");
1071: s.append(c.src.name);
1072: s.append(" DROP CONSTRAINT ");
1073: s.append(c.name);
1074: s.append(" CASCADE");
1075: }
1076:
1077: /**
1078: * Generate a 'drop index' statement for idx.
1079: */
1080: protected void appendDropIndex(CharBuf s, JdbcTable t,
1081: JdbcIndex idx, boolean comments) {
1082: // if (comments && isCommentSupported()) {
1083: // s.append(comment("dropping unknown index "+ idx.name));
1084: // s.append('\n');
1085: // }
1086:
1087: s.append("DROP INDEX ");
1088: s.append(t.name);
1089: s.append('.');
1090: s.append(idx.name);
1091: }
1092:
1093: /**
1094: * Add the primary key constraint in isolation.
1095: */
1096: protected void addPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
1097: s.append("ALTER TABLE ");
1098: s.append(t.name);
1099: s.append(" ADD ");
1100: appendPrimaryKeyConstraint(t, s);
1101: }
1102:
1103: /**
1104: * Drop the primary key constraint in isolation.
1105: */
1106: protected void dropPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
1107: s.append("ALTER TABLE ");
1108: s.append(t.name);
1109: s.append(" DROP CONSTRAINT ");
1110: s.append(t.pkConstraintName);
1111: }
1112:
1113: boolean isDirectTypeColumnChangesSupported(JdbcColumn ourCol,
1114: JdbcColumn dbCol) {
1115: return false;
1116: }
1117:
1118: boolean isDirectNullColumnChangesSupported() {
1119: return false;
1120: }
1121:
1122: boolean isDirectScaleColumnChangesSupported(JdbcColumn ourCol,
1123: JdbcColumn dbCol) {
1124: return false;
1125: }
1126:
1127: boolean isDirectLenghtColumnChangesSupported(JdbcColumn ourCol,
1128: JdbcColumn dbCol) {
1129: return false;
1130: }
1131:
1132: protected void fixColumnsNonDirect(TableDiff tableDiff,
1133: PrintWriter out) {
1134:
1135: JdbcTable ourTable = tableDiff.getOurTable();
1136: String tempTableName = getTempTableName(ourTable, 31);
1137: CharBuf s = new CharBuf();
1138:
1139: s.append("CREATE TABLE ");
1140: s.append(tempTableName);
1141: s.append(" (\n");
1142: JdbcColumn[] cols = ourTable.getColsForCreateTable();
1143: int nc = cols.length;
1144: boolean first = true;
1145: for (int i = 0; i < nc; i++) {
1146: if (first) {
1147: first = false;
1148: } else {
1149: s.append("\n");
1150: }
1151: s.append(" ");
1152: appendCreateColumn(ourTable, cols[i], s, true);
1153: }
1154: s.append("\n ");
1155: appendPrimaryKeyConstraint(ourTable, s);
1156: s.append("\n)");
1157: s.append(getRunCommand());
1158:
1159: // we have to make sure that the table does not have nulls when we export it to a not null column.
1160: for (int i = 0; i < nc; i++) {
1161: ColumnDiff diff = getColumnDiffForName(tableDiff,
1162: cols[i].name);
1163: if (diff != null && diff.isNullDiff()) {
1164: if (!diff.getOurCol().nulls) {
1165: s.append("UPDATE ");
1166: s.append(ourTable.name);
1167: s.append("\n SET ");
1168: s.append(diff.getDbCol().name);
1169: s.append(" = ");
1170: s.append(getDefaultForType(diff.getDbCol()));
1171: s.append("\n WHERE ");
1172: s.append(diff.getDbCol().name);
1173: s.append(" = NULL");
1174: s.append(getRunCommand());
1175: }
1176: }
1177: }
1178:
1179: s.append("INSERT INTO ");
1180: s.append(tempTableName); //ourTable.name
1181: s.append(" (");
1182: for (int i = 0; i < nc; i++) {
1183: s.append(cols[i].name);
1184: if ((i + 1) != nc) {
1185: s.append(", ");
1186: }
1187: }
1188: s.append(") ");
1189:
1190: s.append("\n");//new line
1191:
1192: s.append("SELECT ");
1193: for (int i = 0; i < nc; i++) {
1194: ColumnDiff diff = getColumnDiffForName(tableDiff,
1195: cols[i].name);
1196: if (diff == null) {
1197: if (i != 0) {
1198: s.append(" ");
1199: }
1200: s.append(cols[i].name);
1201: } else {
1202: if (diff.isMissingCol()) {
1203: if (diff.getOurCol().nulls) {
1204: if (i != 0) {
1205: s.append(" ");
1206: }
1207: s.append("NULL");
1208: } else {
1209: if (i != 0) {
1210: s.append(" ");
1211: }
1212: s.append(getDefaultForType(diff.getOurCol()));
1213: }
1214:
1215: } else if (diff.isLenghtDiff() || diff.isScaleDiff()
1216: || diff.isTypeDiff()) {
1217: if (i != 0) {
1218: s.append(" ");
1219: }
1220: s.append("CAST(");
1221: s.append(cols[i].name);
1222: s.append(" AS ");
1223: appendColumnType(cols[i], s);
1224: s.append(")");
1225:
1226: } else if (diff.isNullDiff()) {
1227: if (i != 0) {
1228: s.append(" ");
1229: }
1230: s.append(cols[i].name);
1231: }
1232: }
1233:
1234: if ((i + 1) != nc) {
1235: s.append(", ");
1236: s.append("\n");//new line
1237: }
1238: }
1239: s.append("\n");//new line
1240: s.append(" FROM ");
1241: s.append(ourTable.name);
1242: s.append(getRunCommand());
1243:
1244: s.append("DROP TABLE ");
1245: s.append(ourTable.name);
1246: s.append(getRunCommand());
1247:
1248: s.append("ALTER TABLE ");
1249: s.append(tempTableName);
1250: s.append(" RENAME TO ");
1251: s.append(ourTable.name);
1252: s.append(getRunCommand());
1253:
1254: out.println(s.toString());
1255: }
1256:
1257: /**
1258: * Drop a Sequence column to implement a Set
1259: */
1260: protected void dropSequenceColumn(JdbcTable t, JdbcColumn c,
1261: CharBuf s, boolean comments) {
1262: String tempTableName = getTempTableName(t, 31);
1263:
1264: s
1265: .append(comment("create a temp table to store old table values."));
1266: s.append("\n");
1267: s.append("CREATE TABLE ");
1268: s.append(tempTableName);
1269: s.append(" (\n");
1270: JdbcColumn[] cols = t.getColsForCreateTable();
1271: int nc = cols.length;
1272: boolean first = true;
1273: for (int i = 0; i < nc; i++) {
1274: if (first) {
1275: first = false;
1276: } else {
1277: s.append("\n");
1278: }
1279: s.append(" ");
1280: appendCreateColumn(t, cols[i], s, comments);
1281: }
1282: s.append("\n ");
1283: appendPrimaryKeyConstraint(t, s);
1284: s.append("\n)");
1285: s.append(getRunCommand());
1286:
1287: s
1288: .append(comment("insert a distinct list into the temp table."));
1289: s.append("\n");
1290: s.append("INSERT INTO ");
1291: s.append(tempTableName);
1292: s.append("(");
1293: for (int i = 0; i < nc; i++) {
1294: s.append(cols[i].name);
1295: if ((i + 1) != nc) {
1296: s.append(", ");
1297: }
1298: }
1299: s.append(")");
1300: s.append("\nSELECT DISTINCT ");
1301: for (int i = 0; i < nc; i++) {
1302: if (i != 0) {
1303: s.append("\n ");
1304: }
1305: s.append(cols[i].name);
1306: if ((i + 1) != nc) {
1307: s.append(", ");
1308: }
1309: }
1310: s.append("\n FROM ");
1311: s.append(t.name);
1312:
1313: s.append(getRunCommand());
1314:
1315: s.append(comment("drop main table."));
1316: s.append("\n");
1317: s.append("DROP TABLE ");
1318: s.append(t.name);
1319: s.append(getRunCommand());
1320:
1321: s.append(comment("rename temp table to main table."));
1322: s.append("\n");
1323: s.append("ALTER TABLE ");
1324: s.append(tempTableName);
1325: s.append(" RENAME TO ");
1326: s.append(t.name);
1327:
1328: }
1329:
1330: /**
1331: * Add a Sequence column to implement a list
1332: */
1333: protected void addSequenceColumn(JdbcTable t, JdbcColumn c,
1334: CharBuf s, boolean comments) {
1335:
1336: String mainTempTableName = getTempTableName(t, 31);
1337: String minTempTableName = getTempTableName(t, 31);
1338: String identityColumnName = getTempColumnName(t);
1339:
1340: JdbcColumn indexColumn = null;
1341: JdbcColumn sequenceColumn = null;
1342: JdbcColumn[] cols = t.getColsForCreateTable();
1343: int nc = cols.length;
1344: for (int i = 0; i < nc; i++) {
1345: if (isAddSequenceColumn(cols[i])) {
1346: sequenceColumn = cols[i];
1347: } else if (t.isInPrimaryKey(cols[i].name)) {
1348: indexColumn = cols[i];
1349: }
1350: }
1351:
1352: s
1353: .append(comment("Generate a sequence number so that we can implement a List."));
1354: s.append("\n");
1355: s
1356: .append(comment("create a temp table with a extra identity column."));
1357: s.append("\n");
1358: s.append("CREATE TABLE ");
1359: s.append(mainTempTableName);
1360: s.append(" (\n ");
1361: // create identity column
1362: s.append(identityColumnName);
1363: s.append(" INTEGER IDENTITY,");
1364: for (int i = 0; i < nc; i++) {
1365: s.append("\n ");
1366: appendCreateColumn(t, cols[i], s, comments);
1367: }
1368: int lastIndex = s.toString().lastIndexOf(',');
1369: s.replace(lastIndex, lastIndex + 1, ' ');// we take the last ',' out.
1370: s.append("\n)");
1371:
1372: s.append(getRunCommand());
1373:
1374: s
1375: .append(comment("insert a '0' in the sequence column and copy the rest of the old table into the temp table."));
1376: s.append("\n");
1377: s.append("INSERT INTO ");
1378: s.append(mainTempTableName);
1379: s.append("(");
1380: for (int i = 0; i < nc; i++) {
1381: s.append(cols[i].name);
1382: if ((i + 1) != nc) {
1383: s.append(", ");
1384: }
1385: }
1386: s.append(")");
1387: s.append("\nSELECT ");
1388: for (int i = 0; i < nc; i++) {
1389: if (i != 0) {
1390: s.append("\n ");
1391: }
1392: if (isAddSequenceColumn(cols[i])) {
1393: s.append('0');
1394: } else {
1395: s.append(cols[i].name);
1396: }
1397: if ((i + 1) != nc) {
1398: s.append(", ");
1399: }
1400: }
1401: s.append("\n FROM ");
1402: s.append(t.name);
1403: s.append("\n GROUP BY ");
1404: s.append(indexColumn.name);
1405: s.append(',');
1406: for (int i = 0; i < nc; i++) {
1407: if (!isAddSequenceColumn(cols[i])
1408: && !t.isInPrimaryKey(cols[i].name)) {
1409: s.append(cols[i].name);
1410: }
1411: }
1412:
1413: s.append(getRunCommand());
1414:
1415: s
1416: .append(comment("create a temp table to store the minimum id."));
1417: s.append("\n");
1418: s.append("CREATE TABLE ");
1419: s.append(minTempTableName);
1420: s.append(" (\n ");
1421: s.append(indexColumn.name);
1422: s.append(' ');
1423: appendColumnType(indexColumn, s);
1424: appendCreateColumnNulls(t, indexColumn, s);
1425: s.append(",\n ");
1426: s.append("min_id");
1427: s.append(" INTEGER\n)");
1428:
1429: s.append(getRunCommand());
1430:
1431: s.append(comment("store the minimum id."));
1432: s.append("\n");
1433: s.append("INSERT INTO ");
1434: s.append(minTempTableName);
1435: s.append(" (");
1436: s.append(indexColumn.name);
1437: s.append(", ");
1438: s.append("min_id");
1439: s.append(")\n");
1440: s.append("SELECT ");
1441: s.append(indexColumn.name);
1442: s.append(",\n ");
1443: s.append("MIN(");
1444: s.append(identityColumnName);
1445: s.append(")\n");
1446: s.append(" FROM ");
1447: s.append(mainTempTableName);
1448: s.append("\n");
1449: s.append(" GROUP BY ");
1450: s.append(indexColumn.name);
1451:
1452: s.append(getRunCommand());
1453:
1454: s.append(comment("drop main table " + t.name + "."));
1455: s.append("\n");
1456: s.append("DROP TABLE ");
1457: s.append(t.name);
1458:
1459: s.append(getRunCommand());
1460:
1461: s.append(comment("recreate table " + t.name + "."));
1462: s.append("\n");
1463: s.append("CREATE TABLE ");
1464: s.append(t.name);
1465: s.append(" (\n");
1466: boolean first = true;
1467: for (int i = 0; i < nc; i++) {
1468: if (first) {
1469: first = false;
1470: } else {
1471: s.append("\n");
1472: }
1473: s.append(" ");
1474: appendCreateColumn(t, cols[i], s, comments);
1475: }
1476: s.append("\n ");
1477: appendPrimaryKeyConstraint(t, s);
1478: s.append("\n)");
1479: appendTableType(t, s);
1480:
1481: s.append(getRunCommand());
1482:
1483: s.append(comment("populate table " + t.name
1484: + " with the new sequence column."));
1485: s.append("\n");
1486: s.append("INSERT INTO ");
1487: s.append(t.name);
1488: s.append("(");
1489: for (int i = 0; i < nc; i++) {
1490: s.append(cols[i].name);
1491: if ((i + 1) != nc) {
1492: s.append(", ");
1493: }
1494: }
1495: s.append(")");
1496: s.append("\nSELECT ");
1497: for (int i = 0; i < nc; i++) {
1498: if (i != 0) {
1499: s.append("\n ");
1500: }
1501:
1502: if (isAddSequenceColumn(cols[i])) {
1503: s.append("(a.");
1504: s.append(identityColumnName);
1505: s.append(" - b.min_id)");
1506: } else {
1507: s.append("a.");
1508: s.append(cols[i].name);
1509: }
1510:
1511: if ((i + 1) != nc) {
1512: s.append(", ");
1513: }
1514: }
1515: s.append("\n FROM ");
1516: s.append(mainTempTableName);
1517: s.append(" a,\n ");
1518: s.append(minTempTableName);
1519: s.append(" b\n WHERE a.");
1520: s.append(indexColumn.name);
1521: s.append(" = b.");
1522: s.append(indexColumn.name);
1523:
1524: s.append(getRunCommand());
1525:
1526: s.append(comment("drop temp tables."));
1527: s.append("\n");
1528: s.append("DROP TABLE ");
1529: s.append(mainTempTableName);
1530:
1531: s.append(getRunCommand());
1532:
1533: s.append("DROP TABLE ");
1534: s.append(minTempTableName);
1535: s.append(getRunCommand());
1536: }
1537:
1538: }
|