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