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.*;
0015: import com.versant.core.jdbc.sql.exp.SqlExp;
0016: import com.versant.core.jdbc.sql.diff.TableDiff;
0017: import com.versant.core.jdbc.sql.diff.ColumnDiff;
0018: import com.versant.core.jdbc.sql.diff.ControlParams;
0019: import com.versant.core.util.CharBuf;
0020:
0021: import java.sql.*;
0022: import java.util.*;
0023: import java.util.Date;
0024: import java.io.PrintWriter;
0025:
0026: /**
0027: * A driver for Cache object database.
0028: */
0029: public class CacheSqlDriver extends SqlDriver {
0030:
0031: private InputStreamConverter.Factory blobConverterFactory = new InputStreamConverter.Factory();
0032: private CharacterStreamConverter.Factory clobConverterFactory = new CharacterStreamConverter.Factory();
0033:
0034: /**
0035: * Get the name of this driver.
0036: */
0037: public String getName() {
0038: return "cache";
0039: }
0040:
0041: /**
0042: * Get the default type mapping for the supplied JDBC type code from
0043: * java.sql.Types or null if the type is not supported. There is no
0044: * need to set the database or jdbcType on the mapping as this is done
0045: * after this call returns. Subclasses should override this and to
0046: * customize type mappings.
0047: */
0048: protected JdbcTypeMapping getTypeMapping(int jdbcType) {
0049: switch (jdbcType) {
0050: case Types.DOUBLE:
0051: return new JdbcTypeMapping("DOUBLE", 0, 0,
0052: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE, null);
0053: case Types.BIGINT:
0054: return new JdbcTypeMapping("NUMERIC", 18, 0,
0055: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE, null);
0056: case Types.BLOB:
0057: case Types.LONGVARBINARY:
0058: return new JdbcTypeMapping("LONGVARBINARY", 0, 0,
0059: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
0060: blobConverterFactory);
0061: case Types.CLOB:
0062: case Types.LONGVARCHAR:
0063: return new JdbcTypeMapping("LONGVARCHAR", 0, 0,
0064: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
0065: clobConverterFactory);
0066: }
0067: return super .getTypeMapping(jdbcType);
0068: }
0069:
0070: /**
0071: * Get the default field mappings for this driver. These map java classes
0072: * to column properties. Subclasses should override this, call super() and
0073: * replace mappings as needed.
0074: */
0075: public HashMap getJavaTypeMappings() {
0076: HashMap ans = super .getJavaTypeMappings();
0077:
0078: BooleanConverter.Factory bcf = new BooleanConverter.Factory();
0079: ((JdbcJavaTypeMapping) ans.get(Boolean.TYPE))
0080: .setConverterFactory(bcf);
0081: ((JdbcJavaTypeMapping) ans.get(Boolean.class))
0082: .setConverterFactory(bcf);
0083:
0084: DateTimestampConverter.Factory dtcf = new DateTimestampConverter.Factory();
0085: ((JdbcJavaTypeMapping) ans.get(Date.class))
0086: .setConverterFactory(dtcf);
0087:
0088: return ans;
0089: }
0090:
0091: /**
0092: * Drop the table and all its constraints etc. This must remove
0093: * constraints to this table from other tables so it can be dropped.
0094: */
0095: public void dropTable(Connection con, String table, Statement stat)
0096: throws SQLException {
0097: DatabaseMetaData metaData = con.getMetaData();
0098: ResultSet rsFKs = metaData.getExportedKeys(null, null, table);
0099:
0100: ArrayList a = new ArrayList();
0101: while (rsFKs.next()) {
0102: String tableName = rsFKs.getString("FKTABLE_NAME");
0103: String conName = rsFKs.getString("FK_NAME");
0104: a.add("ALTER TABLE " + tableName + " DROP CONSTRAINT "
0105: + conName);
0106: }
0107: try {
0108: rsFKs.close();
0109: } catch (SQLException e1) {
0110: // hide
0111: }
0112: for (Iterator i = a.iterator(); i.hasNext();) {
0113: String sql = (String) i.next();
0114: try {
0115: stat.execute(sql);
0116: } catch (SQLException x) {
0117: /*some times it's a bit slow to update it's system tables and we get a exeption*/
0118: }
0119: }
0120: stat.execute("DROP TABLE " + table + " CASCADE");
0121: }
0122:
0123: /**
0124: * Generate a 'create table' statement for t.
0125: */
0126: public void generateCreateTable(JdbcTable t, Statement stat,
0127: PrintWriter out, boolean comments) throws SQLException {
0128: CharBuf s = new CharBuf();
0129: if (comments && isCommentSupported() && t.comment != null) {
0130: s.append(comment(t.comment));
0131: s.append('\n');
0132: }
0133: s.append("CREATE TABLE ");
0134: s.append(t.name);
0135: s.append(" (\n");
0136: JdbcColumn[] cols = t.getColsForCreateTable();
0137: int nc = cols.length;
0138: boolean first = true;
0139: for (int i = 0; i < nc; i++) {
0140: if (first)
0141: first = false;
0142: else
0143: s.append("\n");
0144: s.append(" ");
0145: appendCreateColumn(t, cols[i], s, comments);
0146: }
0147: s.append("\n ");
0148: appendPrimaryKeyConstraint(t, s);
0149: appendIndexesInCreateTable(t, s);
0150: s.append("\n)");
0151: appendTableType(t, s);
0152: String sql = s.toString();
0153: if (out != null)
0154: print(out, sql);
0155: if (stat != null)
0156: stat.execute(sql);
0157: }
0158:
0159: /**
0160: * Can the tx isolation level be set on this database?
0161: */
0162: public boolean isSetTransactionIsolationLevelSupported() {
0163: return false;
0164: }
0165:
0166: /**
0167: * Does the JDBC driver support scrollable result sets?
0168: */
0169: public boolean isScrollableResultSetSupported() {
0170: return true;
0171: }
0172:
0173: /**
0174: * Does this driver use the ANSI join syntax (i.e. the join clauses appear
0175: * in the from list e.g. postgres)?
0176: */
0177: public boolean isAnsiJoinSyntax() {
0178: return true;
0179: }
0180:
0181: /**
0182: * Is null a valid value for a column with a foreign key constraint?
0183: */
0184: public boolean isNullForeignKeyOk() {
0185: return true;
0186: }
0187:
0188: /**
0189: * Get whatever needs to be appended to a SELECT statement to lock the
0190: * rows if this makes sense for the database. This must have a leading
0191: * space if not empty.
0192: */
0193: public char[] getSelectForUpdate() {
0194: return null;
0195: }
0196:
0197: /**
0198: * Does the JDBC driver support Statement.setFetchSize()?
0199: */
0200: public boolean isFetchSizeSupported() {
0201: return true;
0202: }
0203:
0204: /**
0205: * Should indexes be used for columns in the order by list that are
0206: * also in the select list? This is used for databases that will not
0207: * order by a column that is duplicated in the select list (e.g. Oracle).
0208: */
0209: public boolean isUseIndexesForOrderCols() {
0210: return true;
0211: }
0212:
0213: /**
0214: * Does the LIKE operator only support literal string and column
0215: * arguments (e.g. Informix)?
0216: */
0217: public boolean isLikeStupid() {
0218: return true;
0219: }
0220:
0221: /**
0222: * Should PreparedStatement pooling be used for this database and
0223: * JDBC driver?
0224: */
0225: public boolean isPreparedStatementPoolingOK() {
0226: return true;
0227: }
0228:
0229: /**
0230: * Does the JDBC driver support statement batching for inserts?
0231: */
0232: public boolean isInsertBatchingSupported() {
0233: return true;
0234: }
0235:
0236: /**
0237: * Does the JDBC driver support statement batching for updates?
0238: */
0239: public boolean isUpdateBatchingSupported() {
0240: return true;
0241: }
0242:
0243: /**
0244: * Create a default name generator instance for JdbcStore's using this
0245: * driver.
0246: */
0247: public JdbcNameGenerator createJdbcNameGenerator() {
0248: DefaultJdbcNameGenerator n = createDefaultJdbcNameGenerator();
0249: n.setMaxColumnNameLength(40);
0250: n.setMaxTableNameLength(40);
0251: n.setMaxConstraintNameLength(40);
0252: n.setMaxIndexNameLength(40);
0253: return n;
0254: }
0255:
0256: /**
0257: * Append the allow nulls part of the definition for a column in a
0258: * create table statement.
0259: */
0260: protected void appendCreateColumnNulls(JdbcTable t, JdbcColumn c,
0261: CharBuf s) {
0262: if (c.nulls) {
0263: s.append(" NULL");
0264: } else {
0265: s.append(" NOT NULL");
0266: }
0267: }
0268:
0269: /**
0270: * Add the primary key constraint part of a create table statement to s.
0271: */
0272: protected void appendPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
0273: s.append("CONSTRAINT ");
0274: s.append(t.pkConstraintName);
0275: s.append(" PRIMARY KEY (");
0276: appendColumnNameList(t.pk, s);
0277: s.append(')');
0278: }
0279:
0280: /**
0281: * Append an 'add constraint' statement for c.
0282: */
0283: protected void appendRefConstraint(CharBuf s, JdbcConstraint c) {
0284: s.append("ALTER TABLE ");
0285: s.append(c.src.name);
0286: s.append(" ADD CONSTRAINT ");
0287: s.append(c.name);
0288: s.append(" FOREIGN KEY (");
0289: appendColumnNameList(c.srcCols, s);
0290: s.append(") REFERENCES ");
0291: s.append(c.dest.name);
0292: s.append('(');
0293: appendColumnNameList(c.dest.pk, s);
0294: s.append(')');
0295: }
0296:
0297: /**
0298: * Write an SQL statement to a script with appropriate separator.
0299: */
0300: protected void print(PrintWriter out, String sql) {
0301: out.print(sql);
0302: out.println(";");
0303: out.println();
0304: }
0305:
0306: /**
0307: * Append the from list entry for a table.
0308: */
0309: public void appendSqlFrom(JdbcTable table, String alias, CharBuf s) {
0310: s.append(table.name);
0311: if (alias != null) {
0312: s.append(" AS ");
0313: s.append(alias);
0314: }
0315: }
0316:
0317: protected boolean isValidSchemaTable(String tableName) {
0318: return true;
0319: }
0320:
0321: /**
0322: * Append the from list entry for a table that is the right hand table
0323: * in a join i.e. it is being joined to.
0324: *
0325: * @param exp This is the expression that joins the tables
0326: * @param outer If true then this is an outer join
0327: */
0328: public void appendSqlFromJoin(JdbcTable table, String alias,
0329: SqlExp exp, boolean outer, CharBuf s) {
0330: if (outer)
0331: s.append(" LEFT JOIN ");
0332: else
0333: s.append(" JOIN ");
0334: s.append(table.name);
0335: if (alias != null) {
0336: s.append(" AS ");
0337: s.append(alias);
0338: }
0339: if (exp != null) {
0340: s.append(" ON (");
0341: exp.appendSQL(this , s, null);
0342: s.append(')');
0343: }
0344: }
0345:
0346: protected String getSchema(Connection con) {
0347: /*This is very messy, and if anybody knows how to do this better
0348: please tell me (carl)!!!!*/
0349: Statement stat = null;
0350: String schema = null;
0351: try {
0352: stat = con.createStatement();
0353: stat.execute("SELECT * FROM XXX_XXX_XXX_XXX_XXX");
0354: } catch (SQLException e) {
0355: try {
0356: String msg = e.getMessage();
0357: int start = msg.indexOf("SQL ERROR #30");
0358: int end = msg.indexOf(".XXX_XXX_XXX_XXX_XXX'");
0359: String iffy = msg.substring(start, end);
0360: start = iffy.indexOf('`');
0361: schema = iffy.substring(start + 1);
0362: } catch (Exception x) {
0363: // now we try someting else
0364: ResultSet rs = null;
0365: try {
0366: ArrayList list = new ArrayList();
0367: DatabaseMetaData metaData = con.getMetaData();
0368: rs = metaData.getSchemas();
0369: while (rs.next()) {
0370: list.add(rs.getString(1));
0371: }
0372: if (list.contains("SQLUser")) {
0373: return "SQLUser";
0374: }
0375: } catch (SQLException e1) {
0376: return null;
0377: } finally {
0378: if (rs != null) {
0379: try {
0380: rs.close();
0381: } catch (SQLException e2) {
0382: //hide
0383: }
0384: }
0385: }
0386: return null;
0387: }
0388: } finally {
0389: if (stat != null) {
0390: try {
0391: stat.close();
0392: } catch (SQLException e) {
0393: //hide
0394: }
0395: }
0396: }
0397: return schema;
0398: }
0399:
0400: /**
0401: * Get the JdbcTable from the database for the given database connection and table name.
0402: */
0403: public HashMap getDBSchema(Connection con, ControlParams params)
0404: throws SQLException {
0405: DatabaseMetaData meta = con.getMetaData();
0406:
0407: HashMap jdbcTableMap = new HashMap(); // main map of jdbc tables
0408:
0409: String catalog = getCatalog(con);
0410: String schema = getSchema(con);
0411:
0412: // now we do columns
0413: String tableName = null;
0414: ResultSet rsColumn = meta.getColumns(catalog, schema, null,
0415: null);
0416: ArrayList currentColumns = null;
0417:
0418: while (rsColumn.next()) {
0419:
0420: String temptableName = rsColumn.getString("TABLE_NAME");
0421:
0422: if (!isValidSchemaTable(temptableName)) {
0423: continue;
0424: }
0425:
0426: if (tableName == null) { // this is the first one
0427: tableName = temptableName;
0428: currentColumns = new ArrayList();
0429: JdbcTable jdbcTable = new JdbcTable();
0430: jdbcTable.name = tableName;
0431: jdbcTableMap.put(tableName, jdbcTable);
0432: }
0433:
0434: if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
0435: JdbcColumn[] jdbcColumns = new JdbcColumn[currentColumns
0436: .size()];
0437: currentColumns.toArray(jdbcColumns);
0438: JdbcTable jdbcTable0 = (JdbcTable) jdbcTableMap
0439: .get(tableName);
0440: jdbcTable0.cols = jdbcColumns;
0441:
0442: tableName = temptableName;
0443: currentColumns.clear();
0444: JdbcTable jdbcTable1 = new JdbcTable();
0445: jdbcTable1.name = tableName;
0446: jdbcTableMap.put(tableName, jdbcTable1);
0447: }
0448:
0449: JdbcColumn col = new JdbcColumn();
0450:
0451: col.name = rsColumn.getString("COLUMN_NAME");
0452: col.sqlType = rsColumn.getString("TYPE_NAME");
0453: col.jdbcType = rsColumn.getInt("DATA_TYPE");
0454: col.length = rsColumn.getInt("COLUMN_SIZE");
0455: col.scale = rsColumn.getInt("DECIMAL_DIGITS");
0456: col.nulls = "YES".equals(rsColumn.getString("IS_NULLABLE"));
0457: if (col.jdbcType == 2 && col.scale == 0) {
0458: col.length = col.length + 2;
0459: }
0460: switch (col.jdbcType) {
0461: case java.sql.Types.BIT:
0462: case java.sql.Types.TINYINT:
0463: case java.sql.Types.SMALLINT:
0464: case java.sql.Types.INTEGER:
0465: case java.sql.Types.DATE:
0466: case java.sql.Types.TIME:
0467: case java.sql.Types.TIMESTAMP:
0468: col.length = 0;
0469: col.scale = 0;
0470: default:
0471: }
0472: currentColumns.add(col);
0473: }
0474: // we fin last table
0475: if (currentColumns != null) {
0476: JdbcColumn[] lastJdbcColumns = new JdbcColumn[currentColumns
0477: .size()];
0478: if (lastJdbcColumns != null) {
0479: currentColumns.toArray(lastJdbcColumns);
0480: JdbcTable colJdbcTable = (JdbcTable) jdbcTableMap
0481: .get(tableName);
0482: colJdbcTable.cols = lastJdbcColumns;
0483: tableName = null;
0484: currentColumns.clear();
0485: }
0486: }
0487:
0488: if (rsColumn != null) {
0489: try {
0490: rsColumn.close();
0491: } catch (SQLException e) {
0492: }
0493: }
0494:
0495: if (!params.checkColumnsOnly()) {
0496: Set mainTableNames = jdbcTableMap.keySet();
0497: if (params.isCheckPK()) {
0498: // now we do primaryKeys ///////////////////////////////////////////////////////////////////////
0499: for (Iterator iterator = mainTableNames.iterator(); iterator
0500: .hasNext();) {
0501: tableName = (String) iterator.next();
0502: JdbcTable jdbcTable = (JdbcTable) jdbcTableMap
0503: .get(tableName);
0504: HashMap pkMap = new HashMap();
0505: HashMap pkNames = new HashMap();
0506: ResultSet rsPKs = meta.getPrimaryKeys(catalog,
0507: schema, tableName);
0508: int pkCount = 0;
0509: while (rsPKs.next()) {
0510: pkCount++;
0511: pkMap.put(rsPKs.getString("COLUMN_NAME"), null);
0512: String pkName = rsPKs.getString("PK_NAME");
0513: jdbcTable.pkConstraintName = pkName;
0514: pkNames.put(pkName, null);
0515: }
0516: rsPKs.close();
0517: JdbcColumn[] pkColumns = new JdbcColumn[pkCount];
0518: if (pkColumns != null) {
0519: int indexOfPKCount = 0;
0520: for (int i = 0; i < jdbcTable.cols.length; i++) {
0521: JdbcColumn jdbcColumn = jdbcTable.cols[i];
0522: if (pkMap.containsKey(jdbcColumn.name)) {
0523: pkColumns[indexOfPKCount] = jdbcColumn;
0524: jdbcColumn.pk = true;
0525: indexOfPKCount++;
0526: }
0527: }
0528: jdbcTable.pk = pkColumns;
0529: }
0530:
0531: }
0532:
0533: // end of primaryKeys ///////////////////////////////////////////////////////////////////////
0534: }
0535: if (params.isCheckIndex()) {
0536: // now we do index /////////////////////////////////////////////////////////////////////////
0537: for (Iterator iterator = mainTableNames.iterator(); iterator
0538: .hasNext();) {
0539: tableName = (String) iterator.next();
0540: JdbcTable jdbcTable = (JdbcTable) jdbcTableMap
0541: .get(tableName);
0542: ResultSet rsIndex = null;
0543: try {
0544: rsIndex = meta.getIndexInfo(catalog, schema,
0545: tableName, false, false);
0546: } catch (SQLException e) {
0547: iterator.remove();
0548: continue;
0549: }
0550:
0551: HashMap indexNameMap = new HashMap();
0552: ArrayList indexes = new ArrayList();
0553: while (rsIndex.next()) {
0554:
0555: String indexName = rsIndex
0556: .getString("INDEX_NAME");
0557: if (indexName != null
0558: && !indexName
0559: .equals(jdbcTable.pkConstraintName)
0560: && !indexName.startsWith("SYS_IDX_")) {
0561: if (indexNameMap.containsKey(indexName)) {
0562: JdbcIndex index = null;
0563: for (Iterator iter = indexes.iterator(); iter
0564: .hasNext();) {
0565: JdbcIndex jdbcIndex = (JdbcIndex) iter
0566: .next();
0567: if (jdbcIndex.name
0568: .equals(indexName)) {
0569: index = jdbcIndex;
0570: }
0571: }
0572: if (index != null) {
0573: JdbcColumn[] tempIndexColumns = index.cols;
0574: JdbcColumn[] indexColumns = new JdbcColumn[tempIndexColumns.length + 1];
0575: System.arraycopy(tempIndexColumns,
0576: 0, indexColumns, 0,
0577: tempIndexColumns.length);
0578: String colName = rsIndex
0579: .getString("COLUMN_NAME");
0580: for (int i = 0; i < jdbcTable.cols.length; i++) {
0581: JdbcColumn jdbcColumn = jdbcTable.cols[i];
0582: if (colName
0583: .equals(jdbcColumn.name)) {
0584: indexColumns[tempIndexColumns.length] = jdbcColumn;
0585: jdbcColumn.partOfIndex = true;
0586: }
0587: }
0588: index.setCols(indexColumns);
0589: }
0590: } else {
0591: indexNameMap.put(indexName, null);
0592: JdbcIndex index = new JdbcIndex();
0593: index.name = indexName;
0594: index.unique = !rsIndex
0595: .getBoolean("NON_UNIQUE");
0596: short indexType = rsIndex
0597: .getShort("TYPE");
0598: switch (indexType) {
0599: case DatabaseMetaData.tableIndexClustered:
0600: index.clustered = true;
0601: break;
0602: }
0603: String colName = rsIndex
0604: .getString("COLUMN_NAME");
0605: JdbcColumn[] indexColumns = new JdbcColumn[1];
0606: for (int i = 0; i < jdbcTable.cols.length; i++) {
0607: JdbcColumn jdbcColumn = jdbcTable.cols[i];
0608: if (colName.equals(jdbcColumn.name)) {
0609: indexColumns[0] = jdbcColumn;
0610: jdbcColumn.partOfIndex = true;
0611: }
0612: }
0613: if (indexColumns[0] != null) {
0614: index.setCols(indexColumns);
0615: indexes.add(index);
0616: }
0617: }
0618: }
0619: }
0620: if (indexes != null) {
0621: JdbcIndex[] jdbcIndexes = new JdbcIndex[indexes
0622: .size()];
0623: if (jdbcIndexes != null) {
0624: indexes.toArray(jdbcIndexes);
0625: jdbcTable.indexes = jdbcIndexes;
0626: }
0627: }
0628: if (rsIndex != null) {
0629: try {
0630: rsIndex.close();
0631: } catch (SQLException e) {
0632: }
0633: }
0634: }
0635:
0636: // end of index ///////////////////////////////////////////////////////////////////////
0637: }
0638: if (params.isCheckConstraint()) {
0639: // now we do forign keys /////////////////////////////////////////////////////////////
0640: for (Iterator iterator = mainTableNames.iterator(); iterator
0641: .hasNext();) {
0642: tableName = (String) iterator.next();
0643: JdbcTable jdbcTable = (JdbcTable) jdbcTableMap
0644: .get(tableName);
0645: ResultSet rsFKs = null;
0646: try {
0647: rsFKs = meta.getImportedKeys(catalog, schema,
0648: tableName);
0649: } catch (SQLException e) {
0650: iterator.remove();
0651: continue;
0652: }
0653: HashMap constraintNameMap = new HashMap();
0654: ArrayList constraints = new ArrayList();
0655: while (rsFKs.next()) {
0656:
0657: String fkName = rsFKs.getString("FK_NAME");
0658:
0659: if (constraintNameMap.containsKey(fkName)) {
0660: JdbcConstraint constraint = null;
0661: for (Iterator iter = constraints.iterator(); iter
0662: .hasNext();) {
0663: JdbcConstraint jdbcConstraint = (JdbcConstraint) iter
0664: .next();
0665: if (jdbcConstraint.name.equals(fkName)) {
0666: constraint = jdbcConstraint;
0667: }
0668: }
0669:
0670: JdbcColumn[] tempConstraintColumns = constraint.srcCols;
0671: JdbcColumn[] constraintColumns = new JdbcColumn[tempConstraintColumns.length + 1];
0672: System.arraycopy(tempConstraintColumns, 0,
0673: constraintColumns, 0,
0674: tempConstraintColumns.length);
0675: String colName = rsFKs
0676: .getString("FKCOLUMN_NAME");
0677: for (int i = 0; i < jdbcTable.cols.length; i++) {
0678: JdbcColumn jdbcColumn = jdbcTable.cols[i];
0679: if (colName.equals(jdbcColumn.name)) {
0680: constraintColumns[tempConstraintColumns.length] = jdbcColumn;
0681: jdbcColumn.foreignKey = true;
0682: }
0683: }
0684: constraint.srcCols = constraintColumns;
0685: } else {
0686: constraintNameMap.put(fkName, null);
0687: JdbcConstraint constraint = new JdbcConstraint();
0688: constraint.name = fkName;
0689: constraint.src = jdbcTable;
0690: String colName = rsFKs
0691: .getString("FKCOLUMN_NAME");
0692: JdbcColumn[] constraintColumns = new JdbcColumn[1];
0693: for (int i = 0; i < jdbcTable.cols.length; i++) {
0694: JdbcColumn jdbcColumn = jdbcTable.cols[i];
0695: if (colName.equals(jdbcColumn.name)) {
0696: constraintColumns[0] = jdbcColumn;
0697: jdbcColumn.foreignKey = true;
0698: }
0699: }
0700: constraint.srcCols = constraintColumns;
0701: constraint.dest = (JdbcTable) jdbcTableMap
0702: .get(rsFKs
0703: .getString("PKTABLE_NAME"));
0704: constraints.add(constraint);
0705: }
0706:
0707: }
0708: if (constraints != null) {
0709: JdbcConstraint[] jdbcConstraints = new JdbcConstraint[constraints
0710: .size()];
0711: if (jdbcConstraints != null) {
0712: constraints.toArray(jdbcConstraints);
0713: jdbcTable.constraints = jdbcConstraints;
0714: }
0715: }
0716: if (rsFKs != null) {
0717: try {
0718: rsFKs.close();
0719: } catch (SQLException e) {
0720: }
0721: }
0722: }
0723: // end of forign keys /////////////////////////////////////////////////////////////
0724: }
0725: }
0726:
0727: HashMap returnMap = new HashMap();
0728: Collection col = jdbcTableMap.values();
0729: for (Iterator iterator = col.iterator(); iterator.hasNext();) {
0730: JdbcTable table = (JdbcTable) iterator.next();
0731: returnMap.put(table.name.toLowerCase(), table);
0732: }
0733: fixAllNames(returnMap);
0734: return returnMap;
0735: }
0736:
0737: /**
0738: * Append a column that needs to be added.
0739: */
0740: protected void appendAddNewColumn(JdbcTable t, JdbcColumn c,
0741: CharBuf s, boolean comments) {
0742: if (comments && isCommentSupported() && c.comment != null) {
0743: s.append(comment("add column for field " + c.comment));
0744: }
0745:
0746: s.append("\n");
0747: if (isAddSequenceColumn(c)) {
0748: addSequenceColumn(t, c, s, comments);
0749: } else {
0750: s.append("ALTER TABLE ");
0751: s.append(t.name);
0752: s.append(" ADD ");
0753: s.append(c.name);
0754: s.append(' ');
0755: appendColumnType(c, s);
0756: s.append(getRunCommand());
0757: if (!c.nulls) {
0758: s.append("UPDATE ");
0759: s.append(t.name);
0760: s.append(" SET ");
0761: s.append(c.name);
0762: s.append(" = ");
0763: s.append(getDefaultForType(c));
0764: s.append(getRunCommand());
0765:
0766: s.append("ALTER TABLE ");
0767: s.append(t.name);
0768: s.append(" ALTER ");
0769: s.append(c.name);
0770: s.append(" SET NOT NULL");
0771: s.append(getRunCommand());
0772: }
0773: }
0774: }
0775:
0776: /**
0777: * Append a column that needs to be added.
0778: */
0779: protected void appendModifyColumn(TableDiff tableDiff,
0780: ColumnDiff diff, CharBuf s, boolean comments) {
0781: JdbcTable t = tableDiff.getOurTable();
0782: JdbcColumn ourCol = diff.getOurCol();
0783: boolean nulls = diff.isNullDiff();
0784:
0785: if (comments && isCommentSupported() && ourCol.comment != null) {
0786: s.append(comment("modify column for field "
0787: + ourCol.comment));
0788: }
0789: if (comments && isCommentSupported() && ourCol.comment == null) {
0790: s.append(comment("modify column " + ourCol.name));
0791: }
0792:
0793: s.append("\n");
0794:
0795: if (nulls) {
0796: if (!ourCol.nulls) {
0797: s.append("UPDATE ");
0798: s.append(t.name);
0799: s.append("\n");
0800: s.append(" SET ");
0801: s.append(ourCol.name);
0802: s.append(" = ");
0803: s.append(getDefaultForType(ourCol));
0804: s.append("\n");
0805: s.append(" WHERE ");
0806: s.append(ourCol.name);
0807: s.append(" = NULL");
0808:
0809: s.append(getRunCommand());
0810:
0811: }
0812:
0813: }
0814:
0815: s.append("ALTER TABLE ");
0816: s.append(t.name);
0817: s.append(" MODIFY ");
0818: s.append(ourCol.name);
0819: s.append(' ');
0820: appendColumnType(ourCol, s);
0821: if (nulls) {
0822: appendCreateColumnNulls(t, ourCol, s);
0823: }
0824:
0825: }
0826:
0827: /**
0828: * Append a column that needs to be added.
0829: */
0830: protected void appendDropColumn(TableDiff tableDiff, JdbcColumn c,
0831: CharBuf s, boolean comments) {
0832: if (comments && isCommentSupported()) {
0833: s.append(comment("dropping unknown column " + c.name));
0834: }
0835: s.append("\n");
0836: if (isDropSequenceColumn(tableDiff, c)) {
0837: dropSequenceColumn(tableDiff.getOurTable(), c, s, comments);
0838: } else {
0839: s.append("ALTER TABLE ");
0840: s.append(tableDiff.getOurTable().name);
0841: s.append(" DROP COLUMN ");
0842: s.append(c.name);
0843: }
0844: }
0845:
0846: /**
0847: * Append an 'drop constraint' statement for c.
0848: */
0849: protected void appendRefDropConstraint(CharBuf s, JdbcConstraint c,
0850: boolean comments) {
0851: // if (comments && isCommentSupported()) {
0852: // s.append(comment("dropping unknown constraint " + c.name));
0853: // s.append('\n');
0854: // }
0855: s.append("ALTER TABLE ");
0856: s.append(c.src.name);
0857: s.append(" DROP CONSTRAINT ");
0858: s.append(c.name);
0859: }
0860:
0861: /**
0862: * Generate a 'drop index' statement for idx.
0863: */
0864: protected void appendDropIndex(CharBuf s, JdbcTable t,
0865: JdbcIndex idx, boolean comments) {
0866: // if (comments && isCommentSupported()) {
0867: // s.append(comment("dropping unknown index "+ idx.name));
0868: // s.append('\n');
0869: // }
0870: s.append("DROP INDEX ");
0871: s.append(idx.name);
0872: }
0873:
0874: /**
0875: * Add the primary key constraint in isolation.
0876: */
0877: protected void addPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
0878: s.append("ALTER TABLE ");
0879: s.append(t.name);
0880: s.append(" ADD ");
0881: appendPrimaryKeyConstraint(t, s);
0882: }
0883:
0884: /**
0885: * Drop the primary key constraint in isolation.
0886: */
0887: protected void dropPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
0888: s.append("ALTER TABLE ");
0889: s.append(t.name);
0890: s.append(" DROP PRIMARY KEY");
0891: // s.append(t.pkConstraintName);
0892: }
0893:
0894: /**
0895: * Drop a Sequence column to implement a Set
0896: */
0897: protected void dropSequenceColumn(JdbcTable t, JdbcColumn c,
0898: CharBuf s, boolean comments) {
0899: String tempTableName = getTempTableName(t, 40);
0900:
0901: s
0902: .append(comment("create a temp table to store old table values."));
0903: s.append("\n");
0904: s.append("CREATE TABLE ");
0905: s.append(tempTableName);
0906: s.append(" (\n");
0907: JdbcColumn[] cols = t.getColsForCreateTable();
0908: int nc = cols.length;
0909: boolean first = true;
0910: for (int i = 0; i < nc; i++) {
0911: if (first)
0912: first = false;
0913: else
0914: s.append("\n");
0915: s.append(" ");
0916: appendCreateColumn(t, cols[i], s, comments);
0917: }
0918: s.append("\n ");
0919: appendPrimaryKeyConstraint(t, s);
0920: s.append("\n)");
0921: s.append(getRunCommand());
0922:
0923: s
0924: .append(comment("insert a distinct list into the temp table."));
0925: s.append("\n");
0926: s.append("INSERT INTO ");
0927: s.append(tempTableName);
0928: s.append("(");
0929: for (int i = 0; i < nc; i++) {
0930: s.append(cols[i].name);
0931: if ((i + 1) != nc) {
0932: s.append(", ");
0933: }
0934: }
0935: s.append(")");
0936: s.append("\nSELECT DISTINCT ");
0937: for (int i = 0; i < nc; i++) {
0938: if (i != 0) {
0939: s.append("\n ");
0940: }
0941: s.append(cols[i].name);
0942: if ((i + 1) != nc) {
0943: s.append(", ");
0944: }
0945: }
0946: s.append("\n FROM ");
0947: s.append(t.name);
0948:
0949: s.append(getRunCommand());
0950:
0951: s.append(comment("drop main table."));
0952: s.append("\n");
0953: s.append("DROP TABLE ");
0954: s.append(t.name);
0955: s.append(" CASCADE");
0956: s.append(getRunCommand());
0957:
0958: s.append(comment("create a the original table again."));
0959: s.append("\n");
0960: s.append("CREATE TABLE ");
0961: s.append(t.name);
0962: s.append(" (\n");
0963: first = true;
0964: for (int i = 0; i < nc; i++) {
0965: if (first)
0966: first = false;
0967: else
0968: s.append("\n");
0969: s.append(" ");
0970: appendCreateColumn(t, cols[i], s, comments);
0971: }
0972: s.append("\n ");
0973: appendPrimaryKeyConstraint(t, s);
0974: s.append("\n)");
0975: s.append(getRunCommand());
0976:
0977: s.append(comment("insert the list back into the main table."));
0978: s.append("\n");
0979: s.append("INSERT INTO ");
0980: s.append(t.name);
0981: s.append("(");
0982: for (int i = 0; i < nc; i++) {
0983: s.append(cols[i].name);
0984: if ((i + 1) != nc) {
0985: s.append(", ");
0986: }
0987: }
0988: s.append(")");
0989: s.append("\nSELECT ");
0990: for (int i = 0; i < nc; i++) {
0991: if (i != 0) {
0992: s.append("\n ");
0993: }
0994: s.append(cols[i].name);
0995: if ((i + 1) != nc) {
0996: s.append(", ");
0997: }
0998: }
0999: s.append("\n FROM ");
1000: s.append(tempTableName);
1001: s.append(getRunCommand());
1002:
1003: s.append(comment("drop temp table."));
1004: s.append("\n");
1005: s.append("DROP TABLE ");
1006: s.append(tempTableName);
1007:
1008: }
1009:
1010: /**
1011: * Add a Sequence column to implement a list
1012: */
1013: protected void addSequenceColumn(JdbcTable t, JdbcColumn c,
1014: CharBuf s, boolean comments) {
1015:
1016: String mainTempTableName = getTempTableName(t, 40);
1017: String minTempTableName = getTempTableName(t, 40);
1018:
1019: JdbcColumn indexColumn = null;
1020: JdbcColumn sequenceColumn = null;
1021: JdbcColumn[] cols = t.getColsForCreateTable();
1022: int nc = cols.length;
1023: for (int i = 0; i < nc; i++) {
1024: if (isAddSequenceColumn(cols[i])) {
1025: sequenceColumn = cols[i];
1026: } else if (t.isInPrimaryKey(cols[i].name)) {
1027: indexColumn = cols[i];
1028: }
1029: }
1030:
1031: s
1032: .append(comment("Generate a sequence number so that we can implement a List."));
1033: s.append("\n");
1034: s.append(comment("create a temp table."));
1035: s.append("\n");
1036: s.append("CREATE TABLE ");
1037: s.append(mainTempTableName);
1038: s.append(" (");
1039: for (int i = 0; i < nc; i++) {
1040: s.append("\n ");
1041: appendCreateColumn(t, cols[i], s, comments);
1042: }
1043: int lastIndex = s.toString().lastIndexOf(',');
1044: s.replace(lastIndex, lastIndex + 1, ' ');// we take the last ',' out.
1045: s.append("\n)");
1046:
1047: s.append(getRunCommand());
1048:
1049: s
1050: .append(comment("insert a '0' in the sequence column and copy the rest of the old table into the temp table."));
1051: s.append("\n");
1052: s.append("INSERT INTO ");
1053: s.append(mainTempTableName);
1054: s.append("(");
1055: for (int i = 0; i < nc; i++) {
1056: s.append(cols[i].name);
1057: if ((i + 1) != nc) {
1058: s.append(", ");
1059: }
1060: }
1061: s.append(")");
1062: s.append("\nSELECT ");
1063: for (int i = 0; i < nc; i++) {
1064: if (i != 0) {
1065: s.append("\n ");
1066: }
1067: if (isAddSequenceColumn(cols[i])) {
1068: s.append('0');
1069: } else {
1070: s.append(cols[i].name);
1071: }
1072: if ((i + 1) != nc) {
1073: s.append(", ");
1074: }
1075: }
1076: s.append("\n FROM ");
1077: s.append(t.name);
1078: s.append("\n GROUP BY ");
1079: s.append(indexColumn.name);
1080: s.append(',');
1081: for (int i = 0; i < nc; i++) {
1082: if (!isAddSequenceColumn(cols[i])
1083: && !t.isInPrimaryKey(cols[i].name)) {
1084: s.append(cols[i].name);
1085: }
1086: }
1087:
1088: s.append(getRunCommand());
1089:
1090: s
1091: .append(comment("create a temp table to store the minimum id."));
1092: s.append("\n");
1093: s.append("CREATE TABLE ");
1094: s.append(minTempTableName);
1095: s.append(" (\n ");
1096: s.append(indexColumn.name);
1097: s.append(' ');
1098: appendColumnType(indexColumn, s);
1099: appendCreateColumnNulls(t, indexColumn, s);
1100: s.append(",\n ");
1101: s.append("min_id");
1102: s.append(" INTEGER\n)");
1103:
1104: s.append(getRunCommand());
1105:
1106: s.append(comment("store the minimum id."));
1107: s.append("\n");
1108: s.append("INSERT INTO ");
1109: s.append(minTempTableName);
1110: s.append(" (");
1111: s.append(indexColumn.name);
1112: s.append(", ");
1113: s.append("min_id");
1114: s.append(")\n");
1115: s.append("SELECT ");
1116: s.append(indexColumn.name);
1117: s.append(",\n ");
1118: s.append("MIN(ID)\n");
1119: s.append(" FROM ");
1120: s.append(mainTempTableName);
1121: s.append("\n");
1122: s.append(" GROUP BY ");
1123: s.append(indexColumn.name);
1124:
1125: s.append(getRunCommand());
1126:
1127: s.append(comment("drop main table " + t.name + "."));
1128: s.append("\n");
1129: s.append("DROP TABLE ");
1130: s.append(t.name);
1131: s.append(" CASCADE");
1132:
1133: s.append(getRunCommand());
1134:
1135: s.append(comment("recreate table " + t.name + "."));
1136: s.append("\n");
1137: s.append("CREATE TABLE ");
1138: s.append(t.name);
1139: s.append(" (\n");
1140: boolean first = true;
1141: for (int i = 0; i < nc; i++) {
1142: if (first)
1143: first = false;
1144: else
1145: s.append("\n");
1146: s.append(" ");
1147: appendCreateColumn(t, cols[i], s, comments);
1148: }
1149: s.append("\n ");
1150: appendPrimaryKeyConstraint(t, s);
1151: s.append("\n)");
1152: appendTableType(t, s);
1153:
1154: s.append(getRunCommand());
1155:
1156: s.append(comment("populate table " + t.name
1157: + " with the new sequence column."));
1158: s.append("\n");
1159: s.append("INSERT INTO ");
1160: s.append(t.name);
1161: s.append("(");
1162: for (int i = 0; i < nc; i++) {
1163: s.append(cols[i].name);
1164: if ((i + 1) != nc) {
1165: s.append(", ");
1166: }
1167: }
1168: s.append(")");
1169: s.append("\nSELECT ");
1170: for (int i = 0; i < nc; i++) {
1171: if (i != 0) {
1172: s.append("\n ");
1173: }
1174:
1175: if (isAddSequenceColumn(cols[i])) {
1176: s.append("(a.ID - b.min_id)");
1177: } else {
1178: s.append("a.");
1179: s.append(cols[i].name);
1180: }
1181:
1182: if ((i + 1) != nc) {
1183: s.append(", ");
1184: }
1185: }
1186: s.append("\n FROM ");
1187: s.append(mainTempTableName);
1188: s.append(" a,\n ");
1189: s.append(minTempTableName);
1190: s.append(" b\n WHERE a.");
1191: s.append(indexColumn.name);
1192: s.append(" = b.");
1193: s.append(indexColumn.name);
1194:
1195: s.append(getRunCommand());
1196:
1197: s.append(comment("drop temp tables."));
1198: s.append("\n");
1199: s.append("DROP TABLE ");
1200: s.append(mainTempTableName);
1201: s.append(" CASCADE");
1202: s.append(getRunCommand());
1203:
1204: s.append("DROP TABLE ");
1205: s.append(minTempTableName);
1206: s.append(" CASCADE");
1207: s.append(getRunCommand());
1208: }
1209: }
|