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