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.ColumnDiff;
0016: import com.versant.core.jdbc.sql.diff.ControlParams;
0017: import com.versant.core.jdbc.sql.diff.TableDiff;
0018: import com.versant.core.jdbc.sql.exp.SqlExp;
0019: import com.versant.core.util.CharBuf;
0020: import com.versant.core.common.BindingSupportImpl;
0021:
0022: import java.io.PrintWriter;
0023: import java.sql.*;
0024: import java.util.*;
0025: import java.util.Date;
0026:
0027: /**
0028: * A driver for Oracle.
0029: */
0030: public class OracleSqlDriver extends SqlDriver {
0031:
0032: protected CharacterStreamConverter.Factory characterStreamConverterFactory = new CharacterStreamConverter.Factory();
0033: protected InputStreamConverter.Factory inputStreamConverterFactory = new InputStreamConverter.Factory();
0034: protected OracleBlobConverter.Factory blobConverterFactory = new OracleBlobConverter.Factory();
0035:
0036: protected OracleClobConverter.Factory clobConverterFactory = new OracleClobConverter.Factory();
0037:
0038: private HashMap typeDiffMap = null;
0039:
0040: /**
0041: * Get the name of this driver.
0042: */
0043: public String getName() {
0044: return "oracle";
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("NUMBER", 19, 0,
0062: JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE, null);
0063:
0064: case Types.DECIMAL:
0065:
0066: case Types.NUMERIC:
0067: return new JdbcTypeMapping("NUMBER", 20, 10,
0068: JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE, null);
0069: case Types.DATE:
0070: case Types.TIME:
0071: case Types.TIMESTAMP:
0072: return new JdbcTypeMapping("DATE", 0, 0,
0073: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE, null);
0074: case Types.DOUBLE:
0075: return new JdbcTypeMapping("DOUBLE PRECISION", 0, 0,
0076: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE, null);
0077: case Types.CLOB:
0078: return new JdbcTypeMapping("CLOB", 0, 0,
0079: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
0080: clobConverterFactory);
0081: case Types.LONGVARCHAR:
0082: return new JdbcTypeMapping("LONG", 0, 0,
0083: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
0084: characterStreamConverterFactory);
0085: case Types.VARCHAR:
0086: return new JdbcTypeMapping("VARCHAR2", 255, 0,
0087: JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE, null);
0088: case Types.LONGVARBINARY:
0089: return new JdbcTypeMapping("LONG RAW", 0, 0,
0090: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
0091: inputStreamConverterFactory);
0092:
0093: case Types.VARBINARY:
0094: case Types.BLOB:
0095: return new JdbcTypeMapping("BLOB", 0, 0,
0096: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
0097: blobConverterFactory);
0098:
0099: }
0100: return super .getTypeMapping(jdbcType);
0101: }
0102:
0103: /**
0104: * Get the default field mappings for this driver. These map java classes
0105: * to column properties. Subclasses should override this, call super() and
0106: * replace mappings as needed.
0107: */
0108: public HashMap getJavaTypeMappings() {
0109: HashMap ans = super .getJavaTypeMappings();
0110:
0111: BooleanConverter.Factory bcf = new BooleanConverter.Factory();
0112: ((JdbcJavaTypeMapping) ans.get(Boolean.TYPE))
0113: .setConverterFactory(bcf);
0114: ((JdbcJavaTypeMapping) ans.get(Boolean.class))
0115: .setConverterFactory(bcf);
0116:
0117: DateTimestampConverter.Factory dtcf = new DateTimestampConverter.Factory();
0118: ((JdbcJavaTypeMapping) ans.get(Date.class))
0119: .setConverterFactory(dtcf);
0120:
0121: return ans;
0122: }
0123:
0124: /**
0125: * Does the JDBC driver support statement batching?
0126: */
0127: public boolean isInsertBatchingSupported() {
0128: return true;
0129: }
0130:
0131: /**
0132: * Can batching be used if the statement contains a column with the
0133: * given JDBC type?
0134: */
0135: public boolean isBatchingSupportedForJdbcType(int jdbcType) {
0136: return jdbcType != Types.LONGVARCHAR
0137: && jdbcType != Types.LONGVARBINARY;
0138: }
0139:
0140: /**
0141: * Does the JDBC driver support statement batching for updates?
0142: */
0143: public boolean isUpdateBatchingSupported() {
0144: return false;
0145: }
0146:
0147: /**
0148: * Does the JDBC driver support scrollable result sets?
0149: */
0150: public boolean isScrollableResultSetSupported() {
0151: return true;
0152: }
0153:
0154: /**
0155: * Should indexes be used for columns in the order by list that are
0156: * also in the select list? This is used for databases that will not
0157: * order by a column that is duplicated in the select list (e.g. Oracle).
0158: */
0159: public boolean isUseIndexesForOrderCols() {
0160: return true;
0161: }
0162:
0163: /**
0164: * Is null a valid value for a column with a foreign key constraint?
0165: */
0166: public boolean isNullForeignKeyOk() {
0167: return true;
0168: }
0169:
0170: /**
0171: * How many PreparedStatement's should the pool be limited to by default
0172: * (0 for unlimited) ?
0173: */
0174: public int getDefaultPsCacheMax() {
0175: return 30;
0176: }
0177:
0178: /**
0179: * Can 'SELECT FOR UPDATE' be used with a DISTINCT?
0180: */
0181: public boolean isSelectForUpdateWithDistinctOk() {
0182: return false;
0183: }
0184:
0185: /**
0186: * Create a default name generator instance for JdbcStore's using this
0187: * driver.
0188: */
0189: public JdbcNameGenerator createJdbcNameGenerator() {
0190: DefaultJdbcNameGenerator n = createDefaultJdbcNameGenerator();
0191: n.setMaxColumnNameLength(30);
0192: n.setMaxTableNameLength(30);
0193: n.setMaxConstraintNameLength(30);
0194: n.setMaxIndexNameLength(30);
0195: return n;
0196: }
0197:
0198: /**
0199: * Drop the table and all its constraints etc. This must remove
0200: * constraints to this table from other tables so it can be dropped.
0201: */
0202: public void dropTable(Connection con, String table, Statement stat)
0203: throws SQLException {
0204: stat.execute("DROP TABLE " + table + " CASCADE CONSTRAINTS");
0205: }
0206:
0207: /**
0208: * Append the allow nulls part of the definition for a column in a
0209: * create table statement.
0210: */
0211: protected void appendCreateColumnNulls(JdbcTable t, JdbcColumn c,
0212: CharBuf s) {
0213: if (!c.nulls) {
0214: s.append(" NOT NULL");
0215: } else {
0216: s.append(" NULL");
0217: }
0218: }
0219:
0220: /**
0221: * Add the primary key constraint part of a create table statement to s.
0222: */
0223: protected void appendPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
0224: s.append("CONSTRAINT ");
0225: s.append(t.pkConstraintName);
0226: s.append(" PRIMARY KEY (");
0227: appendColumnNameList(t.pk, s);
0228: s.append(')');
0229: }
0230:
0231: /**
0232: * Append an 'add constraint' statement for c.
0233: */
0234: protected void appendRefConstraint(CharBuf s, JdbcConstraint c) {
0235: s.append("ALTER TABLE ");
0236: s.append(c.src.name);
0237: s.append(" ADD CONSTRAINT ");
0238: s.append(c.name);
0239: s.append(" FOREIGN KEY (");
0240: appendColumnNameList(c.srcCols, s);
0241: s.append(") REFERENCES ");
0242: s.append(c.dest.name);
0243: s.append('(');
0244: appendColumnNameList(c.dest.pk, s);
0245: s.append(')');
0246: }
0247:
0248: /**
0249: * Append an 'drop constraint' statement for c.
0250: */
0251: protected void dropRefConstraint(CharBuf s, JdbcConstraint c) {
0252: s.append("ALTER TABLE ");
0253: s.append(c.src.name);
0254: s.append(" DROP CONSTRAINT ");
0255: s.append(c.name);
0256: }
0257:
0258: /**
0259: * Write an SQL statement to a script with appropriate separator.
0260: */
0261: protected void print(PrintWriter out, String sql) {
0262: out.print(sql);
0263: out.println(";");
0264: out.println();
0265: }
0266:
0267: /**
0268: * Append the from list entry for a table that is the right hand table
0269: * in a join i.e. it is being joined to.
0270: * @param exp This is the expression that joins the tables
0271: * @param outer If true then this is an outer join
0272: */
0273: public void appendSqlFromJoin(JdbcTable table, String alias,
0274: SqlExp exp, boolean outer, CharBuf s) {
0275: s.append(',');
0276: s.append(' ');
0277: s.append(table.name);
0278: if (alias != null) {
0279: s.append(' ');
0280: s.append(alias);
0281: }
0282: }
0283:
0284: /**
0285: * Append a join expression.
0286: */
0287: public void appendSqlJoin(String leftAlias, JdbcColumn left,
0288: String rightAlias, JdbcColumn right, boolean outer,
0289: CharBuf s) {
0290: s.append(leftAlias);
0291: s.append('.');
0292: s.append(left.name);
0293: s.append(' ');
0294: s.append('=');
0295: s.append(' ');
0296: s.append(rightAlias);
0297: s.append('.');
0298: s.append(right.name);
0299: if (outer) {
0300: s.append(' ');
0301: s.append('(');
0302: s.append('+');
0303: s.append(')');
0304: }
0305: }
0306:
0307: /**
0308: * Get default SQL to test a connection or null if none available. This
0309: * must be a query that returns at least one row.
0310: */
0311: public String getConnectionValidateSQL() {
0312: return "SELECT sysdate FROM dual";
0313: }
0314:
0315: /**
0316: * Gets the current user's schema
0317: */
0318: protected String getSchema(Connection con) {
0319: String schema = null;
0320: String sql = "SELECT sys_context('USERENV','CURRENT_SCHEMA') FROM dual";
0321: try {
0322: Statement statement = con.createStatement();
0323: ResultSet rs = statement.executeQuery(sql);
0324: if (rs.next()) {
0325: schema = rs.getString(1);
0326: }
0327: try {
0328: statement.close();
0329: } catch (SQLException e) {
0330: }
0331: } catch (SQLException sqle) {
0332: //hide
0333: }
0334: return schema;
0335: }
0336:
0337: /**
0338: * Get con ready for a getQueryPlan call. Example: On Sybase this will
0339: * do a 'set showplan 1' and 'set noexec 1'. Also make whatever changes
0340: * are necessary to sql to prepare it for a getQueryPlan call. Example:
0341: * On Oracle this will prepend 'explain '. The cleanupForGetQueryPlan
0342: * method must be called in a finally block if this method is called.
0343: * @see #cleanupForGetQueryPlan
0344: * @see #getQueryPlan
0345: */
0346: public String prepareForGetQueryPlan(Connection con, String sql) {
0347: try {
0348: createPlan(con);
0349: } catch (SQLException sqle) {
0350: sqle.printStackTrace();
0351: }
0352: return "EXPLAIN PLAN SET statement_id = 'JDO_PLAN' INTO jdo_plan_table FOR "
0353: + sql;
0354: }
0355:
0356: private void createPlan(Connection con) throws SQLException {
0357: String exists = "SELECT TABLE_NAME FROM SYS.ALL_TABLES WHERE table_name = 'JDO_PLAN_TABLE'";
0358: Statement existsStat = con.createStatement();
0359: ResultSet rs = existsStat.executeQuery(exists);
0360: if (rs.next()) {
0361: try {
0362: rs.close();
0363: existsStat.close();
0364: } catch (SQLException e) {
0365: }
0366:
0367: } else {
0368: Statement statement = con.createStatement();
0369: String create = "CREATE TABLE jdo_plan_table ("
0370: + " STATEMENT_ID VARCHAR2(30),"
0371: + " TIMESTAMP DATE,"
0372: + " REMARKS VARCHAR2(80),"
0373: + " OPERATION VARCHAR2(30),"
0374: + " OPTIONS VARCHAR2(30),"
0375: + " OBJECT_NODE VARCHAR2(128),"
0376: + " OBJECT_OWNER VARCHAR2(30),"
0377: + " OBJECT_NAME VARCHAR2(30),"
0378: + " OBJECT_INSTANCE NUMBER(38),"
0379: + " OBJECT_TYPE VARCHAR2(30),"
0380: + " OPTIMIZER VARCHAR2(255),"
0381: + " SEARCH_COLUMNS NUMBER,"
0382: + " ID NUMBER(38),"
0383: + " PARENT_ID NUMBER(38),"
0384: + " POSITION NUMBER(38),"
0385: + " COST NUMBER(38),"
0386: + " CARDINALITY NUMBER(38),"
0387: + " BYTES NUMBER(38),"
0388: + " OTHER_TAG VARCHAR2(255),"
0389: + " PARTITION_START VARCHAR2(255),"
0390: + " PARTITION_STOP VARCHAR2(255),"
0391: + " PARTITION_ID NUMBER(38),"
0392: + " OTHER LONG,"
0393: + " DISTRIBUTION VARCHAR2(30) )";
0394:
0395: statement.execute(create);
0396:
0397: try {
0398: rs.close();
0399: existsStat.close();
0400: statement.close();
0401: } catch (SQLException e) {
0402: }
0403:
0404: }
0405: }
0406:
0407: /**
0408: * Get the query plan for ps and cleanup anything done in
0409: * prepareForGetQueryPlan. Return null if this is not supported.
0410: * @see #prepareForGetQueryPlan
0411: * @see #cleanupForGetQueryPlan
0412: */
0413: public String getQueryPlan(Connection con, PreparedStatement ps) {
0414: StringBuffer buff = new StringBuffer();
0415: Statement stat = null;
0416: ResultSet rs = null;
0417: try {
0418:
0419: ps.execute();
0420:
0421: stat = con.createStatement();
0422:
0423: String select = "select lpad(' ',4*(level-1))||operation||' ('||options||') '|| object_name||' ' "
0424: + " ||decode(object_type,'','','('||object_type||') ') "
0425: + " ||decode(object_node,'','','['||object_node||'] ') "
0426: + " ||decode(OPTIMIZER,'','','['||OPTIMIZER||'] ') "
0427: + " ||decode(id,0,'Cost='||position, "
0428: + " decode(COST,'','',' Cost='||COST||' ' "
0429: + " ||decode(id,0,'','Card='||CARDINALITY||' ') "
0430: + " ||decode(id,0,'','Bytes='||BYTES) "
0431: + " ) ) query "
0432: + "from jdo_plan_table "
0433: + " where statement_id = 'JDO_PLAN' "
0434: + "start with id = 0 "
0435: + "connect by prior id = parent_id "
0436: + "ORDER BY id";
0437: rs = stat.executeQuery(select);
0438:
0439: int count = -1;
0440: while (rs != null && rs.next()) {
0441: if (count == -1) {
0442: count = 0;
0443: } else {
0444: buff.append('\n');
0445: }
0446: buff.append(rs.getString(1));
0447: }
0448: } catch (Exception sqle) {
0449: sqle.printStackTrace();
0450: } finally {
0451: try {
0452: rs.close();
0453: stat.close();
0454: } catch (Exception e) {
0455: }
0456: }
0457: return buff.toString();
0458: }
0459:
0460: /**
0461: * Cleanup anything done in prepareForGetQueryPlan. Example: On Sybase this
0462: * will do a 'set showplan 0' and 'set noexec 0'.
0463: * @see #prepareForGetQueryPlan
0464: * @see #getQueryPlan
0465: */
0466: public void cleanupForGetQueryPlan(Connection con) {
0467: try {
0468: Statement statement2 = con.createStatement();
0469: statement2.execute("DROP TABLE jdo_plan_table");
0470: try {
0471: statement2.close();
0472: } catch (SQLException e) {
0473: }
0474: } catch (SQLException sqle) {
0475: sqle.printStackTrace();
0476: }
0477: }
0478:
0479: /**
0480: * Get the JdbcTables from the database for the given database con.
0481: * @param con
0482: * @return HashMap of tablename.toLowerCase() as key and JdbcTable as value
0483: * @throws SQLException on DB errors
0484: */
0485: public HashMap getDBSchema(Connection con, ControlParams params)
0486: throws SQLException {
0487: HashMap jdbcTableMap = new HashMap(); // main map of jdbc tables
0488:
0489: HashMap synonymMap = new HashMap();
0490: try {
0491: String synonymSql = "SELECT TABLE_NAME,\n"
0492: + " SYNONYM_NAME \n"
0493: + " FROM ALL_SYNONYMS \n"
0494: + " WHERE SYNONYM_NAME NOT LIKE TABLE_NAME ";
0495: Statement statSynonym = con.createStatement();
0496: ResultSet rsSynonym = statSynonym.executeQuery(synonymSql);
0497: while (rsSynonym.next()) {
0498: synonymMap.put(rsSynonym.getString(1).toLowerCase(),
0499: rsSynonym.getString(2).toLowerCase());
0500: }
0501: // clean up
0502: if (rsSynonym != null) {
0503: try {
0504: rsSynonym.close();
0505: } catch (SQLException e) {
0506: }
0507: }
0508: if (statSynonym != null) {
0509: try {
0510: statSynonym.close();
0511: } catch (SQLException e) {
0512: }
0513: }
0514: } catch (SQLException e) {
0515: // hide all
0516: }
0517:
0518: // now we do columns
0519: String tableName = null;
0520:
0521: String columnSql = "SELECT DISTINCT t.table_name AS table_name,\n"
0522: + " t.column_name AS column_name,\n"
0523: + " DECODE (t.data_type, 'CHAR', 1,'NCHAR', 1,'NVARCHAR2', 12, 'VARCHAR2', 12,\n"
0524: + " 'NUMBER', 3, 'LONG', -1, 'DATE', 91, 'RAW', -3, 'LONG RAW', -4,\n"
0525: + " 'FLOAT', DECODE (DECODE (t.data_precision, null, t.data_length, t.data_precision),126,8,6),\n"
0526: + " 'BLOB', 2004,'BFILE', 2004,'CLOB',2005,'NCLOB', 2005, 'TIMESTAMP(6)', 93,'TIMESTAMP', 93, \n"
0527: + " 'TIMESTAMP WITH LOCAL TIME ZONE' , 93 ,'TIMESTAMP WITH TIME ZONE',12,\n"
0528: + " 'XMLTYPE',2005, 1111) AS data_type, \n"
0529: + " t.data_type AS type_name,\n"
0530: + " decode(t.data_type, 'CLOB', 2147483647, 'NCLOB', 2147483647, 'LONG', 2147483647,\n"
0531: + " 'BLOB', 2147483647, 'LONG RAW', 2147483647, 'BFILE', 2147483647, 'DATE', 19,\n"
0532: + " 'ROWID', 18, DECODE (t.data_precision, null, t.data_length, t.data_precision)) as column_size,\n"
0533: + " t.data_scale AS decimal_digits,\n"
0534: + " DECODE (t.nullable, 'N', 0, 1) AS nullable,\n"
0535: + " t.column_id AS ordinal_position\n"
0536: + " FROM user_tab_columns t,\n"
0537: + " user_tables u\n"
0538: + " WHERE u.table_name = t.table_name\n"
0539: + " AND u.table_name NOT LIKE('AQ$_%')\n"
0540: + " AND u.table_name NOT LIKE('DEF$_%')\n"
0541: + " AND u.table_name NOT LIKE('LOGMNR_%')\n"
0542: + " AND u.table_name NOT LIKE('LOGSTDBY$%')\n"
0543: + " AND u.table_name NOT LIKE('MVIEW$_%')\n"
0544: + " AND u.table_name NOT LIKE('REPCAT$_%')\n"
0545: + " AND u.table_name NOT LIKE('SQLPLUS_PRODUCT_PROFILE')\n"
0546: + " AND u.table_name NOT LIKE('HELP')\n"
0547: + " ORDER BY table_name, ordinal_position ";
0548: Statement statCol = con.createStatement();
0549: ResultSet rsColumn = statCol.executeQuery(columnSql);
0550: ArrayList columns = null;
0551:
0552: while (rsColumn.next()) {
0553:
0554: String temptableName = rsColumn.getString(1);
0555:
0556: if (tableName == null) { // this is the first one
0557: tableName = temptableName;
0558: columns = new ArrayList();
0559: JdbcTable jdbcTable = new JdbcTable();
0560: jdbcTable.name = tableName;
0561: jdbcTableMap.put(tableName, jdbcTable);
0562: }
0563:
0564: if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
0565: JdbcColumn[] jdbcColumns = new JdbcColumn[columns
0566: .size()];
0567: columns.toArray(jdbcColumns);
0568: JdbcTable jdbcTable0 = (JdbcTable) jdbcTableMap
0569: .get(tableName);
0570: jdbcTable0.cols = jdbcColumns;
0571:
0572: tableName = temptableName;
0573: columns.clear();
0574: JdbcTable jdbcTable1 = new JdbcTable();
0575: jdbcTable1.name = tableName;
0576: jdbcTableMap.put(tableName, jdbcTable1);
0577: }
0578:
0579: JdbcColumn col = new JdbcColumn();
0580:
0581: col.name = rsColumn.getString(2);
0582: col.sqlType = rsColumn.getString(4);
0583: col.jdbcType = rsColumn.getInt(3);
0584: col.length = rsColumn.getInt(5);
0585: col.scale = rsColumn.getInt(6);
0586: // if (col.sqlType.equals("NUMBER") &&
0587: // col.jdbcType == 3
0588: // && col.scale == 0){
0589: // col.jdbcType = java.sql.Types.INTEGER;
0590: // }
0591: col.nulls = rsColumn.getBoolean(7);
0592:
0593: switch (col.jdbcType) {
0594: case java.sql.Types.DATE:
0595: case java.sql.Types.TIME:
0596: case java.sql.Types.TIMESTAMP:
0597: col.length = 0;
0598: col.scale = 0;
0599: default:
0600: }
0601: columns.add(col);
0602: }
0603: // we fin last table
0604: if (columns != null) {
0605: JdbcColumn[] jdbcColumns = new JdbcColumn[columns.size()];
0606: columns.toArray(jdbcColumns);
0607: JdbcTable colJdbcTable = (JdbcTable) jdbcTableMap
0608: .get(tableName);
0609: if (colJdbcTable != null) {
0610: colJdbcTable.cols = jdbcColumns;
0611: }
0612: columns.clear();
0613: }
0614: tableName = null;
0615:
0616: // clean up
0617: if (rsColumn != null) {
0618: try {
0619: rsColumn.close();
0620: } catch (SQLException e) {
0621: }
0622: }
0623: if (statCol != null) {
0624: try {
0625: statCol.close();
0626: } catch (SQLException e) {
0627: }
0628: }
0629: if (!params.checkColumnsOnly()) {
0630: if (params.isCheckPK()) {
0631: // now we do primaryKeys
0632: HashMap pkMap = null;
0633:
0634: String pkSql = "SELECT c.table_name,\n"
0635: + " c.column_name,\n"
0636: + " c.position ,\n"
0637: + " c.constraint_name \n"
0638: + "FROM user_cons_columns c,\n"
0639: + " user_constraints k\n"
0640: + "WHERE k.constraint_type = 'P'\n"
0641: + " AND k.constraint_name = c.constraint_name\n"
0642: + " AND k.table_name = c.table_name \n"
0643: + " AND k.table_name NOT LIKE('AQ$_%')\n"
0644: + " AND k.table_name NOT LIKE('DEF$_%')\n"
0645: + " AND k.table_name NOT LIKE('LOGMNR_%')\n"
0646: + " AND k.table_name NOT LIKE('LOGSTDBY$%')\n"
0647: + " AND k.table_name NOT LIKE('MVIEW$_%')\n"
0648: + " AND k.table_name NOT LIKE('REPCAT$_%')\n"
0649: + " AND k.table_name NOT LIKE('SQLPLUS_PRODUCT_PROFILE')\n"
0650: + " AND k.table_name NOT LIKE('HELP')\n"
0651: + " AND k.owner = c.owner \n"
0652: + "ORDER BY c.table_name,c.constraint_name,c.position";
0653:
0654: Statement statPK = con.createStatement();
0655: ResultSet rsPKs = statPK.executeQuery(pkSql);
0656: int pkCount = 0;
0657: String pkName = null;
0658: while (rsPKs.next()) {
0659: String temptableName = rsPKs.getString(1);
0660:
0661: if (!jdbcTableMap.containsKey(temptableName)) {
0662: continue;
0663: }
0664:
0665: if (tableName == null) { // this is the first one
0666: tableName = temptableName;
0667: pkMap = new HashMap();
0668: }
0669:
0670: if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
0671: JdbcColumn[] pkColumns = new JdbcColumn[pkCount];
0672: int indexOfPKCount = 0;
0673: JdbcTable jdbcTable = (JdbcTable) jdbcTableMap
0674: .get(tableName);
0675: for (int i = 0; i < jdbcTable.cols.length; i++) {
0676: JdbcColumn jdbcColumn = jdbcTable.cols[i];
0677: if (pkMap.containsKey(jdbcColumn.name)) {
0678: pkColumns[indexOfPKCount] = jdbcColumn;
0679: jdbcColumn.pk = true;
0680: indexOfPKCount++;
0681: }
0682: }
0683: jdbcTable.pk = pkColumns;
0684: jdbcTable.pkConstraintName = pkName;
0685:
0686: tableName = temptableName;
0687: pkMap.clear();
0688: pkCount = 0;
0689: }
0690: pkCount++;
0691: pkMap.put(rsPKs.getString(2), null);
0692: pkName = rsPKs.getString(4);
0693: }
0694: JdbcColumn[] pkColumns = new JdbcColumn[pkCount];
0695: int indexOfPKCount = 0;
0696: JdbcTable pkJdbcTable = (JdbcTable) jdbcTableMap
0697: .get(tableName);
0698: if (pkJdbcTable != null) {
0699: for (int i = 0; i < pkJdbcTable.cols.length; i++) {
0700: JdbcColumn jdbcColumn = pkJdbcTable.cols[i];
0701: if (pkMap.containsKey(jdbcColumn.name)) {
0702: pkColumns[indexOfPKCount] = jdbcColumn;
0703: jdbcColumn.pk = true;
0704: indexOfPKCount++;
0705: }
0706: }
0707: pkJdbcTable.pk = pkColumns;
0708: pkJdbcTable.pkConstraintName = pkName;
0709: }
0710: tableName = null;
0711: // clean up
0712: if (rsPKs != null) {
0713: try {
0714: rsPKs.close();
0715: } catch (SQLException e) {
0716: }
0717: }
0718: if (statPK != null) {
0719: try {
0720: statPK.close();
0721: } catch (SQLException e) {
0722: }
0723: }
0724: }
0725: if (params.isCheckIndex()) {
0726: // now we do index
0727: String indexSql = "select i.table_name,\n"
0728: + " c.column_name,\n"
0729: + " i.index_name,\n"
0730: + " decode (i.uniqueness, 'UNIQUE', 0, 1) as NON_UNIQUE,\n"
0731: + " 1 as type,\n"
0732: + " c.column_position as ordinal_position\n"
0733: + " from user_indexes i,\n"
0734: + " user_ind_columns c \n"
0735: + " where i.index_name = c.index_name\n"
0736: + " AND i.table_name = c.table_name\n"
0737: + " AND i.table_name NOT LIKE('AQ$_%')\n"
0738: + " AND i.table_name NOT LIKE('DEF$_%')\n"
0739: + " AND i.table_name NOT LIKE('LOGMNR_%')\n"
0740: + " AND i.table_name NOT LIKE('LOGSTDBY$%')\n"
0741: + " AND i.table_name NOT LIKE('MVIEW$_%')\n"
0742: + " AND i.table_name NOT LIKE('REPCAT$_%')\n"
0743: + " AND i.table_name NOT LIKE('SQLPLUS_PRODUCT_PROFILE')\n"
0744: + " AND i.table_name NOT LIKE('HELP')"
0745: + " order by i.table_name,index_name, ordinal_position";
0746: Statement statIndex = con.createStatement();
0747: ResultSet rsIndex = statIndex.executeQuery(indexSql);
0748:
0749: HashMap indexNameMap = null;
0750: ArrayList indexes = null;
0751: while (rsIndex.next()) {
0752: String temptableName = rsIndex.getString(1);
0753: if (tableName == null) { // this is the first one
0754: tableName = temptableName;
0755: indexNameMap = new HashMap();
0756: indexes = new ArrayList();
0757: }
0758:
0759: String indexName = rsIndex.getString(3);
0760: JdbcTable tempJdbcTable = (JdbcTable) jdbcTableMap
0761: .get(temptableName);
0762:
0763: if (tempJdbcTable == null) {
0764: continue;
0765: }
0766:
0767: if (indexName != null
0768: && !indexName
0769: .equals(tempJdbcTable.pkConstraintName)) {
0770: if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
0771: JdbcTable jdbcTable = (JdbcTable) jdbcTableMap
0772: .get(tableName);
0773: JdbcIndex[] jdbcIndexes = new JdbcIndex[indexes
0774: .size()];
0775: indexes.toArray(jdbcIndexes);
0776: jdbcTable.indexes = jdbcIndexes;
0777:
0778: tableName = temptableName;
0779: indexes.clear();
0780: indexNameMap.clear();
0781:
0782: }
0783: JdbcTable jdbcTable = (JdbcTable) jdbcTableMap
0784: .get(tableName);
0785: if (indexNameMap.containsKey(indexName)) {
0786: JdbcIndex index = null;
0787: for (Iterator iter = indexes.iterator(); iter
0788: .hasNext();) {
0789: JdbcIndex jdbcIndex = (JdbcIndex) iter
0790: .next();
0791: if (jdbcIndex.name.equals(indexName)) {
0792: index = jdbcIndex;
0793: }
0794: }
0795:
0796: JdbcColumn[] tempIndexColumns = index.cols;
0797: JdbcColumn[] indexColumns = new JdbcColumn[tempIndexColumns.length + 1];
0798: System.arraycopy(tempIndexColumns, 0,
0799: indexColumns, 0,
0800: tempIndexColumns.length);
0801: String colName = rsIndex.getString(2);
0802: boolean foundCol = false;
0803: for (int i = 0; i < jdbcTable.cols.length; i++) {
0804: JdbcColumn jdbcColumn = jdbcTable.cols[i];
0805: if (colName
0806: .equalsIgnoreCase(jdbcColumn.name)) {
0807: indexColumns[tempIndexColumns.length] = jdbcColumn;
0808: jdbcColumn.partOfIndex = true;
0809: foundCol = true;
0810: }
0811: }
0812:
0813: if (foundCol) {
0814: index.setCols(indexColumns);
0815: }
0816: } else {
0817: indexNameMap.put(indexName, null);
0818: JdbcIndex index = new JdbcIndex();
0819: index.name = indexName;
0820: index.unique = !rsIndex.getBoolean(4);
0821: short indexType = rsIndex.getShort(5);
0822: switch (indexType) {
0823: case DatabaseMetaData.tableIndexClustered:
0824: index.clustered = true;
0825: break;
0826: }
0827: String colName = rsIndex.getString(2);
0828: JdbcColumn[] indexColumns = new JdbcColumn[1];
0829: boolean foundCol = false;
0830: for (int i = 0; i < jdbcTable.cols.length; i++) {
0831: JdbcColumn jdbcColumn = jdbcTable.cols[i];
0832: if (colName
0833: .equalsIgnoreCase(jdbcColumn.name)) {
0834: indexColumns[0] = jdbcColumn;
0835: jdbcColumn.partOfIndex = true;
0836: foundCol = true;
0837: }
0838: }
0839: if (foundCol) {
0840: index.setCols(indexColumns);
0841: indexes.add(index);
0842: }
0843: }
0844: }
0845: }
0846: JdbcTable indexJdbcTable = (JdbcTable) jdbcTableMap
0847: .get(tableName);
0848: if (indexJdbcTable != null) {
0849: if (indexJdbcTable != null) {
0850: JdbcIndex[] jdbcIndexes = new JdbcIndex[indexes
0851: .size()];
0852: indexes.toArray(jdbcIndexes);
0853: indexJdbcTable.indexes = jdbcIndexes;
0854: }
0855: indexes.clear();
0856: indexNameMap.clear();
0857: }
0858: tableName = null;
0859: // clean up
0860: if (rsIndex != null) {
0861: try {
0862: rsIndex.close();
0863: } catch (SQLException e) {
0864: }
0865: }
0866: if (statIndex != null) {
0867: try {
0868: statIndex.close();
0869: } catch (SQLException e) {
0870: }
0871: }
0872: }
0873: if (params.isCheckConstraint()) {
0874: // now we do forign keys
0875:
0876: String fkSql = "SELECT p.table_name as pktable_name,\n"
0877: + " pc.column_name as pkcolumn_name,\n"
0878: + " f.table_name as fktable_name,\n"
0879: + " fc.column_name as fkcolumn_name,\n"
0880: + " fc.position as key_seq,\n"
0881: + " f.constraint_name as fk_name,\n"
0882: + " p.constraint_name as pk_name\n"
0883: + "FROM user_cons_columns pc, \n"
0884: + " user_constraints p,\n"
0885: + " user_cons_columns fc,\n"
0886: + " user_constraints f\n"
0887: + "WHERE f.constraint_type = 'R'\n"
0888: + " AND p.owner = f.r_owner \n"
0889: + " AND p.constraint_name = f.r_constraint_name\n"
0890: + " AND p.constraint_type = 'P'\n"
0891: + " AND pc.owner = p.owner\n"
0892: + " AND pc.constraint_name = p.constraint_name\n"
0893: + " AND pc.table_name = p.table_name\n"
0894: + " AND fc.owner = f.owner\n"
0895: + " AND fc.constraint_name = f.constraint_name\n"
0896: + " AND fc.table_name = f.table_name\n"
0897: + " AND f.table_name NOT LIKE('AQ$_%')\n"
0898: + " AND f.table_name NOT LIKE('DEF$_%')\n"
0899: + " AND f.table_name NOT LIKE('LOGMNR_%')\n"
0900: + " AND f.table_name NOT LIKE('LOGSTDBY$%')\n"
0901: + " AND f.table_name NOT LIKE('MVIEW$_%')\n"
0902: + " AND f.table_name NOT LIKE('REPCAT$_%')\n"
0903: + " AND f.table_name NOT LIKE('SQLPLUS_PRODUCT_PROFILE')\n"
0904: + " AND f.table_name NOT LIKE('HELP')\n"
0905: + " AND fc.position = pc.position\n"
0906: + "ORDER BY fktable_name, fk_name,key_seq ";
0907: Statement statFK = con.createStatement();
0908: ResultSet rsFKs = statFK.executeQuery(fkSql);
0909:
0910: HashMap constraintNameMap = null;
0911: ArrayList constraints = null;
0912: while (rsFKs.next()) {
0913: String temptableName = rsFKs.getString(3);
0914: if (tableName == null) { // this is the first one
0915: tableName = temptableName;
0916: constraintNameMap = new HashMap();
0917: constraints = new ArrayList();
0918: }
0919:
0920: if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
0921: JdbcTable jdbcTable = (JdbcTable) jdbcTableMap
0922: .get(tableName);
0923: JdbcConstraint[] jdbcConstraints = new JdbcConstraint[constraints
0924: .size()];
0925: constraints.toArray(jdbcConstraints);
0926: jdbcTable.constraints = jdbcConstraints;
0927:
0928: tableName = temptableName;
0929: constraintNameMap.clear();
0930: constraints.clear();
0931: }
0932:
0933: String fkName = rsFKs.getString(6);
0934: JdbcTable jdbcTable = (JdbcTable) jdbcTableMap
0935: .get(tableName);
0936:
0937: if (jdbcTable == null)
0938: continue;
0939:
0940: if (constraintNameMap.containsKey(fkName)) {
0941: JdbcConstraint constraint = null;
0942: for (Iterator iter = constraints.iterator(); iter
0943: .hasNext();) {
0944: JdbcConstraint jdbcConstraint = (JdbcConstraint) iter
0945: .next();
0946: if (jdbcConstraint.name.equals(fkName)) {
0947: constraint = jdbcConstraint;
0948: }
0949: }
0950:
0951: JdbcColumn[] tempConstraintColumns = constraint.srcCols;
0952: JdbcColumn[] constraintColumns = new JdbcColumn[tempConstraintColumns.length + 1];
0953: System.arraycopy(tempConstraintColumns, 0,
0954: constraintColumns, 0,
0955: tempConstraintColumns.length);
0956: String colName = rsFKs.getString(4);
0957: for (int i = 0; i < jdbcTable.cols.length; i++) {
0958: JdbcColumn jdbcColumn = jdbcTable.cols[i];
0959: if (colName.equals(jdbcColumn.name)) {
0960: constraintColumns[tempConstraintColumns.length] = jdbcColumn;
0961: jdbcColumn.foreignKey = true;
0962: }
0963: }
0964: constraint.srcCols = constraintColumns;
0965: } else {
0966: constraintNameMap.put(fkName, null);
0967: JdbcConstraint constraint = new JdbcConstraint();
0968: constraint.name = fkName;
0969: constraint.src = jdbcTable;
0970: String colName = rsFKs.getString(4);
0971: JdbcColumn[] constraintColumns = new JdbcColumn[1];
0972: for (int i = 0; i < jdbcTable.cols.length; i++) {
0973: JdbcColumn jdbcColumn = jdbcTable.cols[i];
0974: if (colName.equals(jdbcColumn.name)) {
0975: constraintColumns[0] = jdbcColumn;
0976: jdbcColumn.foreignKey = true;
0977: }
0978: }
0979: constraint.srcCols = constraintColumns;
0980: constraint.dest = (JdbcTable) jdbcTableMap
0981: .get(rsFKs.getString(1));
0982: constraints.add(constraint);
0983: }
0984: }
0985:
0986: JdbcTable constraintsjdbcTable = (JdbcTable) jdbcTableMap
0987: .get(tableName);
0988: if (constraintsjdbcTable != null) {
0989: JdbcConstraint[] jdbcConstraints = new JdbcConstraint[constraints
0990: .size()];
0991: constraints.toArray(jdbcConstraints);
0992: constraintsjdbcTable.constraints = jdbcConstraints;
0993: }
0994:
0995: if (rsFKs != null) {
0996: try {
0997: rsFKs.close();
0998: } catch (SQLException e) {
0999: }
1000: }
1001: if (statFK != null) {
1002: try {
1003: statFK.close();
1004: } catch (SQLException e) {
1005: }
1006: }
1007: }
1008: }
1009:
1010: HashMap returnMap = new HashMap();
1011: Collection col = jdbcTableMap.values();
1012: String name = null;
1013: for (Iterator iterator = col.iterator(); iterator.hasNext();) {
1014: JdbcTable table = (JdbcTable) iterator.next();
1015: name = table.name.toLowerCase();
1016: returnMap.put(name, table);
1017: if (synonymMap.containsKey(name)) {
1018: returnMap.put(synonymMap.get(name), table);
1019: }
1020: }
1021: fixAllNames(returnMap);
1022: return returnMap;
1023: }
1024:
1025: public boolean checkType(JdbcColumn ourCol, JdbcColumn dbCol) {
1026: String ourSqlType = ourCol.sqlType.toUpperCase();
1027: String dbSqlType = dbCol.sqlType.toUpperCase();
1028: if (ourCol.jdbcType == dbCol.jdbcType) {
1029: return true;
1030: } else if (ourSqlType.startsWith(dbSqlType)) {
1031: return true;
1032: } else {
1033: switch (ourCol.jdbcType) {
1034: case Types.SMALLINT:
1035: case Types.BIT:
1036: case Types.TINYINT:
1037: switch (dbCol.jdbcType) {
1038: case Types.BIT:
1039: case Types.TINYINT:
1040: case Types.DECIMAL:
1041: return true;
1042: default:
1043: return false;
1044: }
1045: case Types.INTEGER:
1046: switch (dbCol.jdbcType) {
1047: case Types.NUMERIC:
1048: case Types.DECIMAL:
1049: return true;
1050: default:
1051: return false;
1052: }
1053:
1054: default:
1055: return super .checkType(ourCol, dbCol);
1056: }
1057: }
1058: }
1059:
1060: /**
1061: * Append a column that needs to be added.
1062: */
1063: protected void appendAddNewColumn(JdbcTable t, JdbcColumn c,
1064: CharBuf s, boolean comments) {
1065: if (comments && isCommentSupported() && c.comment != null) {
1066: s.append(comment("add column for field " + c.comment));
1067: }
1068: s.append("\n");
1069: if (isAddSequenceColumn(c)) {
1070: addSequenceColumn(t, c, s, comments);
1071: } else {
1072:
1073: s.append("ALTER TABLE ");
1074: s.append(t.name);
1075: s.append(" ADD (");
1076: s.append(c.name);
1077: s.append(' ');
1078: appendColumnType(c, s);
1079: s.append(" NULL)");
1080: s.append(getRunCommand());
1081: if (!c.nulls) {
1082: s.append("UPDATE ");
1083: s.append(t.name);
1084: s.append(" SET ");
1085: s.append(c.name);
1086: s.append(" = ");
1087: s.append(getDefaultForType(c));
1088: s.append(getRunCommand());
1089:
1090: s.append("ALTER TABLE ");
1091: s.append(t.name);
1092: s.append(" MODIFY ");
1093: s.append(c.name);
1094: s.append(' ');
1095: appendColumnType(c, s);
1096: appendCreateColumnNulls(t, c, s);
1097: s.append(getRunCommand());
1098: }
1099: }
1100: }
1101:
1102: /**
1103: * Append a column that needs to be added.
1104: */
1105: protected void appendModifyColumn(TableDiff tableDiff,
1106: ColumnDiff diff, CharBuf s, boolean comments) {
1107: JdbcTable t = tableDiff.getOurTable();
1108: JdbcColumn ourCol = diff.getOurCol();
1109: boolean nulls = diff.isNullDiff();
1110:
1111: if (comments && isCommentSupported() && ourCol.comment != null) {
1112: s.append(comment("modify column for field "
1113: + ourCol.comment));
1114: }
1115: if (comments && isCommentSupported() && ourCol.comment == null) {
1116: s.append(comment("modify column " + ourCol.name));
1117: }
1118:
1119: s.append("\n");
1120:
1121: if (nulls) {
1122: if (!ourCol.nulls) {
1123: s.append("UPDATE ");
1124: s.append(t.name);
1125: s.append("\n");
1126: s.append(" SET ");
1127: s.append(ourCol.name);
1128: s.append(" = ");
1129: s.append(getDefaultForType(ourCol));
1130: s.append("\n");
1131: s.append(" WHERE ");
1132: s.append(ourCol.name);
1133: s.append(" = NULL");
1134:
1135: s.append(getRunCommand());
1136:
1137: }
1138:
1139: }
1140:
1141: s.append("ALTER TABLE ");
1142: s.append(t.name);
1143: s.append(" MODIFY ");
1144: s.append(ourCol.name);
1145: s.append(' ');
1146: appendColumnType(ourCol, s);
1147: if (nulls) {
1148: appendCreateColumnNulls(t, ourCol, s);
1149: }
1150:
1151: }
1152:
1153: /**
1154: * Must this column be recreated?
1155: * @param diff
1156: * @return
1157: */
1158: private boolean mustRecreate(ColumnDiff diff) {
1159: JdbcColumn ourCol = diff.getOurCol();
1160: JdbcColumn dbCol = diff.getDbCol();
1161: boolean recreateColumn = false;
1162: if (diff.isLenghtDiff()) {
1163: if (dbCol.length > ourCol.length) {
1164: recreateColumn = true;
1165: }
1166: }
1167: if (diff.isScaleDiff()) {
1168: if (dbCol.scale > ourCol.scale) {
1169: recreateColumn = true;
1170: }
1171: }
1172: return recreateColumn;
1173: }
1174:
1175: /**
1176: * Append a column that needs to be added.
1177: */
1178: protected void appendDropColumn(TableDiff tableDiff, JdbcColumn c,
1179: CharBuf s, boolean comments) {
1180: if (comments && isCommentSupported()) {
1181: s.append(comment("dropping unknown column " + c.name));
1182: }
1183: s.append("\n");
1184: if (isDropSequenceColumn(tableDiff, c)) {
1185: dropSequenceColumn(tableDiff.getOurTable(), c, s, comments);
1186: } else {
1187: s.append("ALTER TABLE ");
1188: s.append(tableDiff.getOurTable().name);
1189: s.append(" DROP COLUMN ");
1190: s.append(c.name);
1191: }
1192:
1193: }
1194:
1195: public boolean isOracleStoreProcs() {
1196: return true;
1197: }
1198:
1199: boolean isDirectTypeColumnChangesSupported(JdbcColumn toCol,
1200: JdbcColumn fromCol) {
1201: switch (fromCol.jdbcType) {
1202: case Types.BIT:
1203: switch (toCol.jdbcType) {
1204: case Types.BIT:
1205: case Types.TINYINT:
1206: case Types.SMALLINT:
1207: case Types.INTEGER:
1208: case Types.BIGINT:
1209: case Types.FLOAT:
1210: case Types.REAL:
1211: case Types.DOUBLE:
1212: case Types.NUMERIC:
1213: case Types.DECIMAL:
1214: return true;
1215: default:
1216: return false;
1217: }
1218: case Types.TINYINT:
1219: switch (toCol.jdbcType) {
1220: case Types.BIT:
1221: case Types.TINYINT:
1222: case Types.SMALLINT:
1223: case Types.INTEGER:
1224: case Types.BIGINT:
1225: case Types.FLOAT:
1226: case Types.REAL:
1227: case Types.DOUBLE:
1228: case Types.NUMERIC:
1229: case Types.DECIMAL:
1230: return true;
1231: default:
1232: return false;
1233: }
1234: case Types.SMALLINT:
1235: switch (toCol.jdbcType) {
1236: case Types.BIT:
1237: case Types.TINYINT:
1238: case Types.SMALLINT:
1239: case Types.INTEGER:
1240: case Types.BIGINT:
1241: case Types.FLOAT:
1242: case Types.REAL:
1243: case Types.DOUBLE:
1244: case Types.NUMERIC:
1245: case Types.DECIMAL:
1246: return true;
1247: default:
1248: return false;
1249: }
1250: case Types.INTEGER:
1251: switch (toCol.jdbcType) {
1252: case Types.BIT:
1253: case Types.TINYINT:
1254: case Types.SMALLINT:
1255: case Types.INTEGER:
1256: case Types.BIGINT:
1257: case Types.FLOAT:
1258: case Types.REAL:
1259: case Types.DOUBLE:
1260: case Types.NUMERIC:
1261: case Types.DECIMAL:
1262: return true;
1263: default:
1264: return false;
1265: }
1266: case Types.BIGINT:
1267: switch (toCol.jdbcType) {
1268: case Types.BIT:
1269: case Types.TINYINT:
1270: case Types.SMALLINT:
1271: case Types.INTEGER:
1272: case Types.BIGINT:
1273: case Types.FLOAT:
1274: case Types.REAL:
1275: case Types.DOUBLE:
1276: case Types.NUMERIC:
1277: case Types.DECIMAL:
1278: return true;
1279: default:
1280: return false;
1281: }
1282: case Types.FLOAT:
1283: switch (toCol.jdbcType) {
1284: case Types.BIT:
1285: case Types.TINYINT:
1286: case Types.SMALLINT:
1287: case Types.INTEGER:
1288: case Types.BIGINT:
1289: case Types.FLOAT:
1290: case Types.REAL:
1291: case Types.DOUBLE:
1292: case Types.NUMERIC:
1293: case Types.DECIMAL:
1294: return true;
1295: default:
1296: return false;
1297: }
1298: case Types.REAL:
1299: switch (toCol.jdbcType) {
1300: case Types.BIT:
1301: case Types.TINYINT:
1302: case Types.SMALLINT:
1303: case Types.INTEGER:
1304: case Types.BIGINT:
1305: case Types.FLOAT:
1306: case Types.REAL:
1307: case Types.DOUBLE:
1308: case Types.NUMERIC:
1309: case Types.DECIMAL:
1310: return true;
1311: default:
1312: return false;
1313: }
1314: case Types.DOUBLE:
1315: switch (toCol.jdbcType) {
1316: case Types.BIT:
1317: case Types.TINYINT:
1318: case Types.SMALLINT:
1319: case Types.INTEGER:
1320: case Types.BIGINT:
1321: case Types.FLOAT:
1322: case Types.REAL:
1323: case Types.DOUBLE:
1324: case Types.NUMERIC:
1325: case Types.DECIMAL:
1326: return true;
1327: default:
1328: return false;
1329: }
1330: case Types.NUMERIC:
1331: switch (toCol.jdbcType) {
1332: case Types.BIT:
1333: case Types.TINYINT:
1334: case Types.SMALLINT:
1335: case Types.INTEGER:
1336: case Types.BIGINT:
1337: case Types.FLOAT:
1338: case Types.REAL:
1339: case Types.DOUBLE:
1340: case Types.NUMERIC:
1341: case Types.DECIMAL:
1342: return true;
1343: default:
1344: return false;
1345: }
1346: case Types.DECIMAL:
1347: switch (toCol.jdbcType) {
1348: case Types.BIT:
1349: case Types.TINYINT:
1350: case Types.SMALLINT:
1351: case Types.INTEGER:
1352: case Types.BIGINT:
1353: case Types.FLOAT:
1354: case Types.REAL:
1355: case Types.DOUBLE:
1356: case Types.NUMERIC:
1357: case Types.DECIMAL:
1358: return true;
1359: default:
1360: return false;
1361: }
1362: case Types.CHAR:
1363: switch (toCol.jdbcType) {
1364: case Types.VARCHAR:
1365: case Types.CHAR:
1366: return true;
1367: default:
1368: return false;
1369: }
1370: case Types.VARCHAR:
1371: switch (toCol.jdbcType) {
1372: case Types.VARCHAR:
1373: case Types.CHAR:
1374: return true;
1375: default:
1376: return false;
1377: }
1378: case Types.DATE:
1379: switch (toCol.jdbcType) {
1380: case Types.TIMESTAMP:
1381: case Types.DATE:
1382: case Types.TIME:
1383: return true;
1384: default:
1385: return false;
1386: }
1387: case Types.TIME:
1388: switch (toCol.jdbcType) {
1389: case Types.TIMESTAMP:
1390: case Types.DATE:
1391: case Types.TIME:
1392: return true;
1393: default:
1394: return false;
1395: }
1396: case Types.TIMESTAMP:
1397: switch (toCol.jdbcType) {
1398: case Types.TIMESTAMP:
1399: case Types.DATE:
1400: case Types.TIME:
1401: return true;
1402: default:
1403: return false;
1404: }
1405: case Types.CLOB:
1406: case Types.LONGVARCHAR:
1407: case Types.BLOB:
1408: case Types.LONGVARBINARY:
1409: case Types.VARBINARY:
1410: /*
1411: todo we need to throw a exception if this happens, because we cannot support this changes.
1412: */
1413:
1414: }
1415: return false;
1416: }
1417:
1418: boolean isDirectScaleColumnChangesSupported(JdbcColumn ourCol,
1419: JdbcColumn dbCol) {
1420: if (dbCol.scale < ourCol.scale) {
1421: return true;
1422: }
1423: return false;
1424: }
1425:
1426: boolean isDirectLenghtColumnChangesSupported(JdbcColumn ourCol,
1427: JdbcColumn dbCol) {
1428: if (dbCol.length < ourCol.length) {
1429: return true;
1430: }
1431: return false;
1432: }
1433:
1434: /**
1435: * Append an 'drop constraint' statement for c.
1436: */
1437: protected void appendRefDropConstraint(CharBuf s, JdbcConstraint c,
1438: boolean comments) {
1439: s.append("ALTER TABLE ");
1440: s.append(c.src.name);
1441: s.append(" DROP CONSTRAINT ");
1442: s.append(c.name);
1443: }
1444:
1445: /**
1446: * Generate a 'drop index' statement for idx.
1447: */
1448: protected void appendDropIndex(CharBuf s, JdbcTable t,
1449: JdbcIndex idx, boolean comments) {
1450: s.append("DROP INDEX ");
1451: s.append(idx.name);
1452: }
1453:
1454: /**
1455: * Add the primary key constraint in isolation.
1456: */
1457: protected void addPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
1458: s.append("ALTER TABLE ");
1459: s.append(t.name);
1460: s.append(" ADD ");
1461: appendPrimaryKeyConstraint(t, s);
1462: }
1463:
1464: /**
1465: * Drop the primary key constraint in isolation.
1466: */
1467: protected void dropPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
1468: s.append("ALTER TABLE ");
1469: s.append(t.name);
1470: s.append(" DROP CONSTRAINT ");
1471: s.append(t.pkConstraintName);
1472: }
1473:
1474: protected void fixColumnsNonDirect(TableDiff tableDiff,
1475: PrintWriter out) {
1476:
1477: JdbcTable ourTable = tableDiff.getOurTable();
1478: String tempTableName = getTempTableName(ourTable, 30);
1479:
1480: CharBuf s = new CharBuf();
1481:
1482: JdbcTable dbTable = tableDiff.getDbTable();
1483: if (dbTable.pkConstraintName != null) {
1484: if (tableDiff.getPkDiffs().isEmpty()) {
1485: dropPrimaryKeyConstraint(tableDiff.getDbTable(), s);
1486: s.append(getRunCommand());
1487: }
1488: }
1489:
1490: s.append("CREATE TABLE ");
1491: s.append(tempTableName);
1492: s.append(" (\n");
1493: JdbcColumn[] cols = ourTable.getColsForCreateTable();
1494: int nc = cols.length;
1495: boolean first = true;
1496: for (int i = 0; i < nc; i++) {
1497: if (first) {
1498: first = false;
1499: } else {
1500: s.append("\n");
1501: }
1502: s.append(" ");
1503: appendCreateColumn(ourTable, cols[i], s, true);
1504: }
1505: s.append("\n ");
1506: appendPrimaryKeyConstraint(ourTable, s);
1507: s.append("\n)");
1508:
1509: s.append(getRunCommand());
1510:
1511: s.append("INSERT INTO ");
1512: s.append(tempTableName); //
1513: s.append(" (");
1514: for (int i = 0; i < nc; i++) {
1515: s.append(cols[i].name);
1516: if ((i + 1) != nc) {
1517: s.append(", ");
1518: }
1519: }
1520: s.append(") ");
1521:
1522: s.append("\n");
1523:
1524: s.append("SELECT ");
1525: for (int i = 0; i < nc; i++) {
1526: JdbcColumn ourCol = cols[i];
1527: ColumnDiff diff = getColumnDiffForName(tableDiff,
1528: ourCol.name);
1529: if (diff == null) {
1530: if (i != 0) {
1531: s.append(" ");
1532: }
1533: s.append(ourCol.name);
1534: } else {
1535: JdbcColumn dbCol = diff.getDbCol();
1536: if (diff.isMissingCol()) {
1537: if (diff.getOurCol().nulls) {
1538: if (i != 0) {
1539: s.append(" ");
1540: }
1541: s.append("NULL");
1542:
1543: } else {
1544: if (i != 0) {
1545: s.append(" ");
1546: }
1547: s.append(getDefaultForType(diff.getOurCol()));
1548: }
1549:
1550: } else if ((diff.isLenghtDiff() || diff.isScaleDiff() || diff
1551: .isTypeDiff())
1552: && diff.isNullDiff()) {
1553:
1554: if (ourCol.nulls) {
1555: if (i != 0) {
1556: s.append(" ");
1557: }
1558: appendCast(ourCol, dbCol, s, false);
1559: } else {
1560: if (i != 0) {
1561: s.append(" ");
1562: }
1563: s.append("CASE ");
1564: s.append("\n");//new line
1565: s.append(" WHEN ");
1566: s.append(ourCol.name);
1567: s.append(" IS NOT NULL THEN ");
1568: appendCast(ourCol, dbCol, s, false);
1569: s.append("\n");//new line
1570: s.append(" ELSE ");
1571: appendCast(ourCol, dbCol, s, true);
1572: s.append("\n");//new line
1573: s.append(" END");
1574: }
1575:
1576: } else if ((diff.isLenghtDiff() || diff.isScaleDiff() || diff
1577: .isTypeDiff())
1578: && !diff.isNullDiff()) {
1579: if (i != 0) {
1580: s.append(" ");
1581: }
1582: appendCast(ourCol, dbCol, s, true);
1583: } else if (diff.isNullDiff()) {
1584: if (ourCol.nulls) {
1585: if (i != 0) {
1586: s.append(" ");
1587: }
1588: s.append(ourCol.name);
1589: } else {
1590: if (i != 0) {
1591: s.append(" ");
1592: }
1593: s.append("CASE ");
1594: s.append("\n");//new line
1595: s.append(" WHEN ");
1596: s.append(ourCol.name);
1597: s.append(" IS NOT NULL THEN ");
1598: s.append(ourCol.name);
1599: s.append("\n");//new line
1600: s.append(" ELSE ");
1601: s.append(getDefaultForType(ourCol));
1602: s.append("\n");//new line
1603: s.append(" END");
1604: }
1605: }
1606: }
1607:
1608: if ((i + 1) != nc) {
1609: s.append(", ");
1610: s.append("\n");//new line
1611: }
1612: }
1613: s.append("\n");//new line
1614: s.append(" FROM ");
1615: s.append(ourTable.name);
1616: s.append(getRunCommand());
1617:
1618: s.append("DROP TABLE ");
1619: s.append(ourTable.name);
1620: s.append(" CASCADE CONSTRAINTS");
1621: s.append(getRunCommand());
1622:
1623: s.append("ALTER TABLE ");
1624: s.append(tempTableName);
1625: s.append(" RENAME TO ");
1626: s.append(ourTable.name);
1627: s.append(getRunCommand());
1628:
1629: out.println(s.toString());
1630:
1631: }
1632:
1633: private void appendCast(JdbcColumn ourCol, JdbcColumn dbCol,
1634: CharBuf s, boolean defaultValue) {
1635: String ourType = ourCol.sqlType.toUpperCase().trim();
1636: String dbType = dbCol.sqlType.toUpperCase().trim();
1637:
1638: if ((ourType.startsWith("VARCHAR2") || ourType
1639: .startsWith("CHAR"))
1640: && (dbType.startsWith("VARCHAR2")
1641: || dbType.startsWith("CHAR") || dbType
1642: .startsWith("NCHAR"))) {
1643: s.append("CAST(TRANSLATE(");
1644: if (defaultValue) {
1645: s.append(getDefaultForType(ourCol));
1646: } else {
1647: s.append(ourCol.name);
1648: }
1649: s.append(" USING CHAR_CS) AS ");
1650: appendColumnType(ourCol, s);
1651: s.append(")");
1652: } else if (ourType.startsWith("NCHAR")
1653: && (dbType.startsWith("VARCHAR2")
1654: || dbType.startsWith("CHAR") || dbType
1655: .startsWith("NCHAR"))) {
1656:
1657: s.append("CAST(TRANSLATE(");
1658: if (defaultValue) {
1659: s.append(getDefaultForType(ourCol));
1660: } else {
1661: s.append(ourCol.name);
1662: }
1663: s.append(" USING NCHAR_CS) AS ");
1664: appendColumnType(ourCol, s);
1665: s.append(")");
1666: } else if ((ourType.startsWith("CLOB") || (ourType
1667: .startsWith("BLOB")))
1668: && (dbType.startsWith("LONG"))) {
1669: if (defaultValue) {
1670: s.append(getDefaultForType(ourCol));
1671: } else {
1672: s.append("TO_LOB(");
1673: s.append(ourCol.name);
1674: s.append(")");
1675: }
1676: } else {
1677: s.append("CAST(");
1678: if (defaultValue) {
1679: s.append(getDefaultForType(ourCol));
1680: } else {
1681: s.append(ourCol.name);
1682: }
1683: s.append(" AS ");
1684: appendColumnType(ourCol, s);
1685: s.append(")");
1686: }
1687: }
1688:
1689: /**
1690: * Drop a Sequence column to implement a Set
1691: */
1692: protected void dropSequenceColumn(JdbcTable t, JdbcColumn c,
1693: CharBuf s, boolean comments) {
1694: String tempTableName = getTempTableName(t, 30);
1695:
1696: s
1697: .append(comment("create a temp table to store old table values."));
1698: s.append("\n");
1699: s.append("CREATE TABLE ");
1700: s.append(tempTableName);
1701: s.append(" (\n");
1702: JdbcColumn[] cols = t.getColsForCreateTable();
1703: int nc = cols.length;
1704: boolean first = true;
1705: for (int i = 0; i < nc; i++) {
1706: if (first)
1707: first = false;
1708: else
1709: s.append("\n");
1710: s.append(" ");
1711: appendCreateColumn(t, cols[i], s, comments);
1712: }
1713: s.append("\n ");
1714: appendPrimaryKeyConstraint(t, s);
1715: s.append("\n)");
1716: s.append(getRunCommand());
1717:
1718: s
1719: .append(comment("insert a distinct list into the temp table."));
1720: s.append("\n");
1721: s.append("INSERT INTO ");
1722: s.append(tempTableName);
1723: s.append("(");
1724: for (int i = 0; i < nc; i++) {
1725: s.append(cols[i].name);
1726: if ((i + 1) != nc) {
1727: s.append(", ");
1728: }
1729: }
1730: s.append(")");
1731: s.append("\nSELECT DISTINCT ");
1732: for (int i = 0; i < nc; i++) {
1733: if (i != 0) {
1734: s.append("\n ");
1735: }
1736: s.append(cols[i].name);
1737: if ((i + 1) != nc) {
1738: s.append(", ");
1739: }
1740: }
1741: s.append("\n FROM ");
1742: s.append(t.name);
1743:
1744: s.append(getRunCommand());
1745:
1746: s.append(comment("drop main table."));
1747: s.append("\n");
1748: s.append("DROP TABLE ");
1749: s.append(t.name);
1750: s.append(getRunCommand());
1751:
1752: s.append(comment("rename temp table to main table."));
1753: s.append("\n");
1754: s.append("ALTER TABLE ");
1755: s.append(tempTableName);
1756: s.append(" RENAME TO ");
1757: s.append(t.name);
1758:
1759: }
1760:
1761: /**
1762: * Add a Sequence column to implement a List
1763: */
1764: protected void addSequenceColumn(JdbcTable t, JdbcColumn c,
1765: CharBuf s, boolean comments) {
1766: String tempTableName = getTempTableName(t, 30);
1767: String minTempTableName = getTempTableName(t, 30);
1768:
1769: JdbcColumn indexColumn = null;
1770: JdbcColumn sequenceColumn = null;
1771: JdbcColumn[] cols = t.getColsForCreateTable();
1772: int nc = cols.length;
1773: for (int i = 0; i < nc; i++) {
1774: if (isAddSequenceColumn(cols[i])) {
1775: sequenceColumn = cols[i];
1776: } else if (t.isInPrimaryKey(cols[i].name)) {
1777: indexColumn = cols[i];
1778: }
1779: }
1780:
1781: s
1782: .append(comment("create a temp table to store old table values."));
1783: s.append("\n");
1784: s.append("CREATE TABLE ");
1785: s.append(tempTableName);
1786: s.append(" (\n");
1787: boolean first = true;
1788: for (int i = 0; i < nc; i++) {
1789: if (first) {
1790: first = false;
1791: } else {
1792: s.append("\n");
1793: }
1794: s.append(" ");
1795: appendCreateColumn(t, cols[i], s, true);
1796: }
1797: int lastIndex = s.toString().lastIndexOf(',');
1798: s.replace(lastIndex, lastIndex + 1, ' ');// we take the last ',' out.
1799: s.append("\n)");
1800: s.append(getRunCommand());
1801:
1802: s
1803: .append(comment("create a temp table to store the minimum id."));
1804: s.append("\n");
1805: s.append("CREATE TABLE ");
1806: s.append(minTempTableName);
1807: s.append(" (\n ");
1808: s.append(indexColumn.name);
1809: s.append(' ');
1810: appendColumnType(indexColumn, s);
1811: appendCreateColumnNulls(t, indexColumn, s);
1812: s.append(",\n ");
1813: s.append("min_id");
1814: s.append(" INTEGER\n)");
1815:
1816: s.append(getRunCommand());
1817:
1818: s
1819: .append(comment("insert a sequence, and copy the rest of the old table into the temp table."));
1820: s.append("\n");
1821: s.append("INSERT INTO ");
1822: s.append(tempTableName);
1823: s.append("(");
1824: for (int i = 0; i < nc; i++) {
1825: s.append(cols[i].name);
1826: if ((i + 1) != nc) {
1827: s.append(", ");
1828: }
1829: }
1830: s.append(")");
1831: s.append("\nSELECT ");
1832: for (int i = 0; i < nc; i++) {
1833: if (i != 0) {
1834: s.append("\n ");
1835: }
1836: if (isAddSequenceColumn(cols[i])) {
1837: s.append('0');
1838: } else {
1839: s.append(cols[i].name);
1840: }
1841: if ((i + 1) != nc) {
1842: s.append(", ");
1843: }
1844: }
1845: s.append("\n FROM ");
1846: s.append(t.name);
1847: s.append("\n ORDER BY ");
1848: s.append(indexColumn.name);
1849:
1850: s.append(getRunCommand());
1851:
1852: s.append(comment("store the minimum id."));
1853: s.append("\n");
1854: s.append("UPDATE ");
1855: s.append(tempTableName);
1856: s.append("\n SET ");
1857: s.append(c.name);
1858: s.append(" = ROWNUM");
1859: s.append(getRunCommand());
1860:
1861: s.append(comment("store the minimum id."));
1862: s.append("\n");
1863: s.append("INSERT INTO ");
1864: s.append(minTempTableName);
1865: s.append(" (");
1866: s.append(indexColumn.name);
1867: s.append(", ");
1868: s.append("min_id");
1869: s.append(")\n");
1870: s.append("SELECT ");
1871: s.append(indexColumn.name);
1872: s.append(",\n ");
1873: s.append("MIN(" + c.name + //"ROWNUM" +
1874: ")\n");
1875: s.append(" FROM ");
1876: s.append(tempTableName);
1877: s.append("\n");
1878: s.append(" GROUP BY ");
1879: s.append(indexColumn.name);
1880:
1881: s.append(getRunCommand());
1882:
1883: s.append(comment("update the sequence column."));
1884: s.append("\n");
1885: s.append("UPDATE ");
1886: s.append(tempTableName);
1887: s.append(" a\n SET ");
1888: s.append(c.name);
1889: s.append(" = (SELECT a.");
1890: s.append(c.name);
1891: s.append(" - b.min_id\n");
1892: s.append(pad(13 + c.name.length()));
1893: s.append("FROM ");
1894: s.append(minTempTableName);
1895: s.append(" b\n");
1896: s.append(pad(12 + c.name.length()));
1897: s.append("WHERE a.");
1898: s.append(indexColumn.name);
1899: s.append(" = b.");
1900: s.append(indexColumn.name);
1901: s.append(')');
1902:
1903: s.append(getRunCommand());
1904:
1905: s.append(comment("drop temp table."));
1906: s.append("\n");
1907: s.append("DROP TABLE ");
1908: s.append(minTempTableName);
1909: s.append(getRunCommand());
1910:
1911: s.append(comment("drop main table."));
1912: s.append("\n");
1913: s.append("DROP TABLE ");
1914: s.append(t.name);
1915: s.append(getRunCommand());
1916:
1917: s.append(comment("rename temp table to main table."));
1918: s.append("\n");
1919: s.append("ALTER TABLE ");
1920: s.append(tempTableName);
1921: s.append(" RENAME TO ");
1922: s.append(t.name);
1923: s.append(getRunCommand());
1924:
1925: s.append(comment("Add the primary key back."));
1926: s.append("\n");
1927: addPrimaryKeyConstraint(t, s);
1928: s.append(getRunCommand());
1929:
1930: }
1931:
1932: }
|