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