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.BooleanConverter;
0016: import com.versant.core.jdbc.sql.conv.ClobStringConverter;
0017: import com.versant.core.jdbc.sql.exp.SqlExp;
0018: import com.versant.core.jdbc.sql.exp.BinaryOpExp;
0019: import com.versant.core.jdbc.sql.diff.ControlParams;
0020: import com.versant.core.jdbc.sql.diff.TableDiff;
0021: import com.versant.core.jdbc.sql.diff.ColumnDiff;
0022: import com.versant.core.util.CharBuf;
0023: import com.versant.core.metadata.ClassMetaData;
0024:
0025: import java.sql.*;
0026: import java.util.*;
0027: import java.util.Date;
0028: import java.io.PrintWriter;
0029:
0030: import com.versant.core.common.BindingSupportImpl;
0031:
0032: /**
0033: * A driver for Sybase using jconnect JDBC driver.
0034: */
0035: public class SybaseSqlDriver extends SqlDriver {
0036:
0037: private ClobStringConverter.Factory clobStringConverterFactory = new ClobStringConverter.Factory();
0038:
0039: private boolean batchingSupported = false;
0040: private boolean scrollableResultSetSupported = false;
0041: private boolean optimizeExistsUnderOrToOuterJoin = false;
0042: private boolean ansiJoinSyntax = false;
0043: private boolean supportDirectColumnChange = false;
0044: private HashMap sysMap = null;
0045:
0046: /**
0047: * Get the name of this driver.
0048: */
0049: public String getName() {
0050: return "sybase";
0051: }
0052:
0053: /**
0054: * Get the default type mapping for the supplied JDBC type code from
0055: * java.sql.Types or null if the type is not supported. There is no
0056: * need to set the database or jdbcType on the mapping as this is done
0057: * after this call returns. Subclasses should override this and to
0058: * customize type mappings.
0059: */
0060: protected JdbcTypeMapping getTypeMapping(int jdbcType) {
0061: switch (jdbcType) {
0062: case Types.BIT:
0063: return new JdbcTypeMapping("TINYINT", 0, 0,
0064: JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE, null);
0065: case Types.BIGINT:
0066: return new JdbcTypeMapping("NUMERIC", 19, 0,
0067: JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE, null);
0068: case Types.DATE:
0069: case Types.TIME:
0070: case Types.TIMESTAMP:
0071: return new JdbcTypeMapping("DATETIME", 0, 0,
0072: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE, null);
0073: case Types.DOUBLE:
0074: return new JdbcTypeMapping("DOUBLE PRECISION", 0, 0,
0075: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE, null);
0076: case Types.CLOB:
0077: // the converter is only required here as CLOB is not the
0078: // correct JDBC type when setting null on ps (must be
0079: // LONGVARCHAR)
0080: return new JdbcTypeMapping("TEXT", 0, 0,
0081: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
0082: clobStringConverterFactory);
0083: case Types.LONGVARCHAR:
0084: // unlike CLOB this does not need the clobStringConverterFactory
0085: // as LONGVARCHAR is the correct JDBC type for Sybase when
0086: // setting null on ps
0087: return new JdbcTypeMapping("TEXT", 0, 0,
0088: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE, null);
0089: case Types.VARBINARY:
0090: return new JdbcTypeMapping("VARBINARY", 255, 0,
0091: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
0092: nullBytesAsBinaryConverterFactory);
0093: case Types.LONGVARBINARY:
0094: case Types.BLOB:
0095: return new JdbcTypeMapping("IMAGE", 0, 0,
0096: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
0097: nullBytesAsBinaryConverterFactory);
0098: }
0099: return super .getTypeMapping(jdbcType);
0100: }
0101:
0102: /**
0103: * Get the default field mappings for this driver. These map java classes
0104: * to column properties. Subclasses should override this, call super() and
0105: * replace mappings as needed.
0106: */
0107: public HashMap getJavaTypeMappings() {
0108: HashMap ans = super .getJavaTypeMappings();
0109:
0110: BooleanConverter.Factory bcf = new BooleanConverter.Factory();
0111: ((JdbcJavaTypeMapping) ans.get(Boolean.TYPE))
0112: .setConverterFactory(bcf);
0113: ((JdbcJavaTypeMapping) ans.get(Boolean.class))
0114: .setConverterFactory(bcf);
0115:
0116: ((JdbcJavaTypeMapping) ans.get(Byte.TYPE))
0117: .setJdbcType(Types.SMALLINT);
0118: ((JdbcJavaTypeMapping) ans.get(Byte.class))
0119: .setJdbcType(Types.SMALLINT);
0120:
0121: DateTimestampConverter.Factory dtcf = new DateTimestampConverter.Factory();
0122: ((JdbcJavaTypeMapping) ans.get(Date.class))
0123: .setConverterFactory(dtcf);
0124:
0125: return ans;
0126: }
0127:
0128: /**
0129: * Perform any driver specific customization. This can be used to control
0130: * funcionality depending on the version of JDBC driver in use etc.
0131: */
0132: protected void init(Driver jdbcDriver) {
0133: String n = jdbcDriver.getClass().getName();
0134: if (n.indexOf("jdbc2") >= 0) {
0135: batchingSupported = true;
0136: scrollableResultSetSupported = true;
0137: }
0138: }
0139:
0140: public boolean isCustomizeForServerRequired() {
0141: return true;
0142: }
0143:
0144: /**
0145: * Perform any specific configuration appropriate for the database server
0146: * in use. If any SQL is done on con call con.commit() before returning.
0147: */
0148: public void customizeForServer(Connection con) throws SQLException {
0149: // get Sybase major and minor version numbers
0150: int major = 0;
0151: //int minor = 0;
0152: try {
0153: String ver = getSybaseVersion(con);
0154: int i = ver.indexOf('/') + 1;
0155: String no = ver.substring(i, ver.indexOf('/', i));
0156: i = no.indexOf('.');
0157: major = Integer.parseInt(no.substring(0, i));
0158: //int j = no.indexOf('.', i + 1);
0159: //if (j >= 0) minor = Integer.parseInt(no.substring(i + 1, j));
0160: //else minor = Integer.parseInt(no.substring(i + 1));
0161: } catch (NumberFormatException e) {
0162: // ignore - must be old Sybase
0163: }
0164: // optimize settings to match
0165: if (major >= 12) {
0166: ansiJoinSyntax = true;
0167: optimizeExistsUnderOrToOuterJoin = true;
0168: supportDirectColumnChange = true;
0169: }
0170: }
0171:
0172: /**
0173: * Get the version of Sybase on con.
0174: */
0175: private String getSybaseVersion(Connection con) throws SQLException {
0176: String ver;
0177: Statement stat = null;
0178: ResultSet rs = null;
0179: try {
0180: stat = con.createStatement();
0181: rs = stat.executeQuery("select @@version");
0182: rs.next();
0183: ver = rs.getString(1);
0184: con.commit();
0185: } finally {
0186: if (rs != null) {
0187: try {
0188: rs.close();
0189: } catch (SQLException e) {
0190: // ignore
0191: }
0192: }
0193: if (stat != null) {
0194: try {
0195: stat.close();
0196: } catch (SQLException e) {
0197: // ignore
0198: }
0199: }
0200: }
0201: return ver;
0202: }
0203:
0204: /**
0205: * Does the JDBC driver support statement batching?
0206: */
0207: public boolean isInsertBatchingSupported() {
0208: return false; //batchingSupported;
0209: }
0210:
0211: /**
0212: * Does the JDBC driver support statement batching for updates?
0213: */
0214: public boolean isUpdateBatchingSupported() {
0215: return false; //batchingSupported;
0216: }
0217:
0218: /**
0219: * Does the JDBC driver support scrollable result sets?
0220: */
0221: public boolean isScrollableResultSetSupported() {
0222: return scrollableResultSetSupported;
0223: }
0224:
0225: /**
0226: * Is it ok to convert simple 'exists (select ...)' clauses under an
0227: * 'or' into outer joins?
0228: */
0229: public boolean isOptimizeExistsUnderOrToOuterJoin() {
0230: return optimizeExistsUnderOrToOuterJoin;
0231: }
0232:
0233: /**
0234: * Does this driver use the ANSI join syntax (i.e. the join clauses appear
0235: * in the from list e.g. postgres)?
0236: */
0237: public boolean isAnsiJoinSyntax() {
0238: return ansiJoinSyntax;
0239: }
0240:
0241: /**
0242: * Create a default name generator instance for JdbcStore's using this
0243: * driver.
0244: */
0245: public JdbcNameGenerator createJdbcNameGenerator() {
0246: DefaultJdbcNameGenerator n = createDefaultJdbcNameGenerator();
0247: n.setMaxColumnNameLength(30);
0248: n.setMaxTableNameLength(30);
0249: n.setMaxConstraintNameLength(30);
0250: n.setMaxIndexNameLength(30);
0251: return n;
0252: }
0253:
0254: /**
0255: * Append the allow nulls part of the definition for a column in a
0256: * create table statement.
0257: */
0258: protected void appendCreateColumnNulls(JdbcTable t, JdbcColumn c,
0259: CharBuf s) {
0260: if (c.nulls)
0261: s.append(" NULL");
0262: }
0263:
0264: /**
0265: * Add the primary key constraint part of a create table statement to s.
0266: */
0267: protected void appendPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
0268: s.append("CONSTRAINT ");
0269: s.append(t.pkConstraintName);
0270: s.append(" PRIMARY KEY (");
0271: appendColumnNameList(t.pk, s);
0272: s.append(')');
0273: }
0274:
0275: /**
0276: * Append an 'add constraint' statement for c.
0277: */
0278: protected void appendRefConstraint(CharBuf s, JdbcConstraint c) {
0279: s.append("ALTER TABLE ");
0280: s.append(c.src.name);
0281: s.append(" ADD CONSTRAINT ");
0282: s.append(c.name);
0283: s.append(" FOREIGN KEY (");
0284: appendColumnNameList(c.srcCols, s);
0285: s.append(") REFERENCES ");
0286: s.append(c.dest.name);
0287: s.append('(');
0288: appendColumnNameList(c.dest.pk, s);
0289: s.append(')');
0290: }
0291:
0292: /**
0293: * Drop the table and all its constraints etc. This must remove
0294: * constraints to this table from other tables so it can be dropped.
0295: */
0296: public void dropTable(Connection con, String table, Statement stat)
0297: throws SQLException {
0298: ResultSet rs = null;
0299: try {
0300: stat = con.createStatement();
0301: try {
0302: rs = stat.executeQuery("sp_helpconstraint " + table);
0303: ArrayList a = new ArrayList();
0304: for (; rs.next();) {
0305: String cname = rs.getString(1);
0306: String info = rs.getString(2);
0307: if (info.indexOf("FOREIGN KEY") >= 0) {
0308: String tn = info
0309: .substring(0, info.indexOf(' '));
0310: a.add("ALTER TABLE " + tn + " DROP CONSTRAINT "
0311: + cname);
0312: }
0313: }
0314: rs.close();
0315: for (Iterator i = a.iterator(); i.hasNext();) {
0316: String sql = (String) i.next();
0317: stat.execute(sql);
0318: }
0319: } catch (SQLException e) {
0320: // ignore
0321: // when there are no constraints, sybase 12 throws an exception ?
0322: }
0323: stat.execute("DROP TABLE " + table);
0324: } finally {
0325: if (rs != null) {
0326: try {
0327: rs.close();
0328: } catch (SQLException x) {
0329: // ignore
0330: }
0331: }
0332: }
0333: }
0334:
0335: public void updateClassForPostInsertKeyGen(ClassMetaData cmd,
0336: JdbcMappingResolver mappingResolver) {
0337: JdbcTypeMapping tm = mappingResolver
0338: .getTypeMapping(Types.NUMERIC);
0339: JdbcTable table = ((JdbcClass) cmd.storeClass).table;
0340: table.pk[0].jdbcType = tm.getJdbcType();
0341: table.pk[0].sqlType = tm.getSqlType();
0342: updateSubClassPkCols(cmd, tm);
0343: }
0344:
0345: private void updateSubClassPkCols(ClassMetaData cmd,
0346: JdbcTypeMapping tm) {
0347: ClassMetaData[] subs = cmd.pcSubclasses;
0348: if (subs != null) {
0349: for (int j = 0; j < subs.length; j++) {
0350: ClassMetaData sub = subs[j];
0351: JdbcClass jdbcClass = (JdbcClass) sub.storeClass;
0352: if (jdbcClass.inheritance == JdbcClass.INHERITANCE_VERTICAL) {
0353: jdbcClass.table.pk[0].jdbcType = tm.getJdbcType();
0354: jdbcClass.table.pk[0].sqlType = tm.getSqlType();
0355: }
0356: updateSubClassPkCols(sub, tm);
0357: }
0358: }
0359: }
0360:
0361: /**
0362: * Write an SQL statement to a script with appropriate separator.
0363: */
0364: protected void print(PrintWriter out, String sql) {
0365: out.println(sql);
0366: out.println("go");
0367: out.println();
0368: }
0369:
0370: /**
0371: * Append the from list entry for a table that is the right hand table
0372: * in a join i.e. it is being joined to.
0373: * @param exp This is the expression that joins the tables
0374: * @param outer If true then this is an outer join
0375: */
0376: public void appendSqlFromJoin(JdbcTable table, String alias,
0377: SqlExp exp, boolean outer, CharBuf s) {
0378: if (ansiJoinSyntax) {
0379: if (outer)
0380: s.append(" LEFT JOIN ");
0381: else
0382: s.append(" JOIN ");
0383: s.append(table.name);
0384: if (alias != null) {
0385: s.append(" AS ");
0386: s.append(alias);
0387: }
0388: if (exp != null) {
0389: s.append(" ON (");
0390: exp.appendSQL(this , s, null);
0391: s.append(')');
0392: }
0393: } else {
0394: s.append(',');
0395: s.append(' ');
0396: s.append(table.name);
0397: if (alias != null) {
0398: s.append(' ');
0399: s.append(alias);
0400: }
0401: }
0402: }
0403:
0404: /**
0405: * Append a join expression.
0406: */
0407: public void appendSqlJoin(String leftAlias, JdbcColumn left,
0408: String rightAlias, JdbcColumn right, boolean outer,
0409: CharBuf s) {
0410: s.append(leftAlias);
0411: s.append('.');
0412: s.append(left.name);
0413: s.append(' ');
0414: if (outer && !ansiJoinSyntax)
0415: s.append('*');
0416: s.append('=');
0417: s.append(' ');
0418: s.append(rightAlias);
0419: s.append('.');
0420: s.append(right.name);
0421: }
0422:
0423: /**
0424: * Get the string form of a binary operator.
0425: * @see BinaryOpExp
0426: */
0427: public String getSqlBinaryOp(int op) {
0428: switch (op) {
0429: case BinaryOpExp.CONCAT:
0430: return "+";
0431: }
0432: return super .getSqlBinaryOp(op);
0433: }
0434:
0435: /**
0436: * Get default SQL to test a connection or null if none available. This
0437: * must be a query that returns at least one row.
0438: */
0439: public String getConnectionValidateSQL() {
0440: return "SELECT db_name()";
0441: }
0442:
0443: /**
0444: * Get con ready for a getQueryPlan call. Example: On Sybase this will
0445: * do a 'set showplan 1' and 'set noexec 1'. Also make whatever changes
0446: * are necessary to sql to prepare it for a getQueryPlan call. Example:
0447: * On Oracle this will prepend 'explain '. The cleanupForGetQueryPlan
0448: * method must be called in a finally block if this method is called.
0449: * @see #cleanupForGetQueryPlan
0450: * @see #getQueryPlan
0451: */
0452: public String prepareForGetQueryPlan(Connection con, String sql) {
0453: try {
0454: Statement statement = con.createStatement();
0455: statement.execute("SET showplan ON");
0456: statement.execute("SET noexec ON");
0457: } catch (SQLException sqle) {
0458: sqle.printStackTrace();
0459: }
0460: return sql;
0461: }
0462:
0463: /**
0464: * Get the query plan for ps and cleanup anything done in
0465: * prepareForGetQueryPlan. Return null if this is not supported.
0466: * @see #prepareForGetQueryPlan
0467: * @see #cleanupForGetQueryPlan
0468: */
0469: public String getQueryPlan(Connection con, PreparedStatement ps) {
0470: StringBuffer buff = new StringBuffer();
0471: try {
0472: ps.execute();
0473: SQLWarning warning = ps.getWarnings();
0474: while (warning != null) {
0475: buff.append(warning.getLocalizedMessage());
0476: warning = warning.getNextWarning();
0477: }
0478:
0479: } catch (Exception sqle) {
0480: sqle.printStackTrace();
0481: }
0482: return buff.toString();
0483: }
0484:
0485: /**
0486: * Cleanup anything done in prepareForGetQueryPlan. Example: On Sybase this
0487: * will do a 'set showplan 0' and 'set noexec 0'.
0488: * @see #prepareForGetQueryPlan
0489: * @see #getQueryPlan
0490: */
0491: public void cleanupForGetQueryPlan(Connection con) {
0492: try {
0493: Statement statement = con.createStatement();
0494: statement.execute("SET noexec OFF");
0495: statement.execute("SET showplan OFF");
0496:
0497: } catch (SQLException sqle) {
0498: sqle.printStackTrace();
0499: }
0500: }
0501:
0502: /**
0503: * Does this database support autoincrement or serial columns?
0504: */
0505: public boolean isAutoIncSupported() {
0506: return true;
0507: }
0508:
0509: /**
0510: * Append the part of a create table statement for a column.
0511: */
0512: protected void appendCreateColumn(JdbcTable t, JdbcColumn c,
0513: CharBuf s, boolean comments) {
0514: if (c.autoinc) {
0515: int si = s.size();
0516: s.append(c.name);
0517: s.append(" NUMERIC");
0518: if (c.length != 0) {
0519: s.append('(');
0520: s.append(c.length);
0521: s.append(')');
0522: }
0523: s.append(" IDENTITY,");
0524: if (comments && c.comment != null) {
0525: s.append(' ');
0526: si += COMMENT_COL;
0527: for (; s.size() < si; s.append(' '))
0528: ;
0529: s.append(comment(c.comment));
0530: }
0531: } else {
0532: super .appendCreateColumn(t, c, s, comments);
0533: }
0534: }
0535:
0536: /**
0537: * Retrieve the value of the autoinc or serial column for a row just
0538: * inserted using stat on con.
0539: */
0540: public Object getAutoIncColumnValue(JdbcTable classTable,
0541: Connection con, Statement stat) throws SQLException {
0542: Statement es = null;
0543: try {
0544: es = con.createStatement();
0545: ResultSet rs = es.executeQuery("SELECT @@identity");
0546: if (!rs.next()) {
0547: throw BindingSupportImpl
0548: .getInstance()
0549: .datastore(
0550: "Unable to get identity column "
0551: + "value - 'select @@identity' returned no row");
0552: }
0553: return classTable.pk[0].get(rs, 1);
0554: } finally {
0555: if (es != null) {
0556: try {
0557: es.close();
0558: } catch (SQLException e) {
0559: // ignore
0560: }
0561: }
0562: }
0563: }
0564:
0565: /**
0566: * Enable or disable identity insert for the given table if this is
0567: * required to insert a value into an identity column.
0568: */
0569: public void enableIdentityInsert(Connection con, String table,
0570: boolean on) throws SQLException {
0571: Statement stat = con.createStatement();
0572: try {
0573: stat.execute("SET identity_insert " + table
0574: + (on ? " ON" : " OFF"));
0575: } finally {
0576: try {
0577: stat.close();
0578: } catch (SQLException e) {
0579: // ignore
0580: }
0581: }
0582: }
0583:
0584: /**
0585: * Get whatever needs to be appended to a SELECT statement to lock the
0586: * rows if this makes sense for the database. This must have a leading
0587: * space if not empty.
0588: */
0589: public char[] getSelectForUpdate() {
0590: return null;
0591: }
0592:
0593: protected String getCatalog(Connection con) throws SQLException {
0594: String catalog = null;
0595: Statement stat = null;
0596: ResultSet rs = null;
0597:
0598: try {
0599: stat = con.createStatement();
0600: rs = stat.executeQuery("SELECT db_name()");
0601: if (rs.next()) {
0602: catalog = rs.getString(1);
0603: }
0604: } finally {
0605: if (rs != null) {
0606: try {
0607: rs.close();
0608: } catch (SQLException e) {
0609: }
0610: }
0611: if (stat != null) {
0612: try {
0613: stat.close();
0614: } catch (SQLException e) {
0615: }
0616: }
0617: }
0618: return catalog;
0619: }
0620:
0621: protected boolean isValidSchemaTable(String tableName) {
0622: if (sysMap == null) {
0623: sysMap = new HashMap();
0624: String[] sysNames = new String[] { "sysalternates",
0625: "sysattributes", "syscolumns", "syscomments",
0626: "sysconstraints", "sysdepends", "sysindexes",
0627: "syskeys", "syslogs", "sysobjects",
0628: "syspartitions", "sysprocedures", "sysprotects",
0629: "sysreferences", "sysroles", "syssegments",
0630: "sysstatistics", "systabstats", "systhresholds",
0631: "systypes", "sysusermessages", "sysusers",
0632: "sysobjects", "sysindexes", "syscolumns",
0633: "systypes", "sysprocedures", "syscomments",
0634: "syssegments", "syslogs", "sysprotects",
0635: "sysusers", "sysalternates", "sysdepends",
0636: "syskeys", "sysgams", "sysusermessages",
0637: "sysreferences", "sysconstraints", "systhresholds",
0638: "sysroles", "sysattributes", "syspartitions",
0639: "systabstats", "sysstatistics", "sysxtypes",
0640: "sysjars", "sysqueryplans", "sysdatabases",
0641: "sysusages", "sysprocesses", "syslogins",
0642: "syslocks", "sysdevices", "sysmessages",
0643: "sysconfigures", "syscurconfigs", "syssecmechs",
0644: "sysservers", "sysremotelogins", "sysmonitors",
0645: "sysengines", "syslanguages", "syscharsets",
0646: "systestlog", "syslisteners", "syssrvroles",
0647: "sysloginroles", "syslogshold", "systimeranges",
0648: "sysresourcelimits", "systransactions",
0649: "syssessions", "syscertificates", "spt_values",
0650: "spt_monitor", "spt_limit_types", "syblicenseslog",
0651: "spt_ijdbc_table_types", "spt_ijdbc_mda",
0652: "spt_ijdbc_conversion", "ijdbc_function_escapes",
0653: "sp_procxmode", "sp_validlang", "sp_getmessage",
0654: "sp_aux_getsize", "sp_configure", "sp_dboption",
0655: "sp_dropdevice", "sp_dbupgrade",
0656: "sp_loaddbupgrade", "sp_prtsybsysmsgs",
0657: "spt_jdbc_table_types", "spt_mda", "spt_jtext",
0658: "spt_jdbc_conversion", "jdbc_function_escapes" };
0659: for (int i = 0; i < sysNames.length; i++) {
0660: String sysName = sysNames[i];
0661: sysMap.put(sysName, null);
0662: }
0663: }
0664:
0665: if (sysMap.containsKey(tableName)) {
0666: return false;
0667: }
0668: return true;
0669: }
0670:
0671: /**
0672: * Get the JdbcTable from the database for the given database connection and table name.
0673: */
0674: public HashMap getDBSchema(Connection con, ControlParams params)
0675: throws SQLException {
0676:
0677: HashMap jdbcTableMap = new HashMap(); // main map of jdbc tables
0678:
0679: String catalog = getCatalog(con);
0680:
0681: // now we do columns
0682: String tableName = null;
0683: ResultSet rs = null;
0684: HashMap tableNameMap = new HashMap();
0685: try {
0686: rs = con.getMetaData().getTables(null, getSchema(con),
0687: null, null);
0688: for (; rs.next();) {
0689: if (rs.getString(4).trim().equals("TABLE")) {
0690: String name = rs.getString(3).trim();
0691: tableNameMap.put(name, name);
0692: }
0693: }
0694: } finally {
0695: if (rs != null) {
0696: try {
0697: rs.close();
0698: } catch (SQLException x) {
0699: // ignore
0700: }
0701: }
0702: }
0703: String colSql = "sp_columns null,null,'" + catalog + "',null";
0704: Statement statCol = con.createStatement();
0705: ResultSet rsColumn = statCol.executeQuery(colSql);
0706: ArrayList currentColumns = null;
0707:
0708: while (rsColumn.next()) {
0709:
0710: String temptableName = rsColumn.getString("TABLE_NAME");
0711:
0712: if (!isValidSchemaTable(temptableName)) {
0713: continue;
0714: }
0715: if (!tableNameMap.containsKey(temptableName)) {
0716: continue;
0717: }
0718:
0719: if (tableName == null) { // this is the first one
0720: tableName = temptableName;
0721: currentColumns = new ArrayList();
0722: JdbcTable jdbcTable = new JdbcTable();
0723: jdbcTable.name = tableName;
0724: jdbcTableMap.put(tableName, jdbcTable);
0725: }
0726:
0727: if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
0728: JdbcColumn[] jdbcColumns = new JdbcColumn[currentColumns
0729: .size()];
0730: currentColumns.toArray(jdbcColumns);
0731: JdbcTable jdbcTable0 = (JdbcTable) jdbcTableMap
0732: .get(tableName);
0733: jdbcTable0.cols = jdbcColumns;
0734:
0735: tableName = temptableName;
0736: currentColumns.clear();
0737: JdbcTable jdbcTable1 = new JdbcTable();
0738: jdbcTable1.name = tableName;
0739: jdbcTableMap.put(tableName, jdbcTable1);
0740: }
0741:
0742: JdbcColumn col = new JdbcColumn();
0743:
0744: col.name = rsColumn.getString("COLUMN_NAME");
0745: col.sqlType = rsColumn.getString("TYPE_NAME");
0746: col.jdbcType = rsColumn.getInt("DATA_TYPE");
0747: col.length = rsColumn.getInt("PRECISION");
0748: col.scale = rsColumn.getInt("SCALE");
0749: col.nulls = rsColumn.getBoolean("NULLABLE");
0750:
0751: if (col.jdbcType == 11) {
0752: col.jdbcType = java.sql.Types.TIMESTAMP;
0753: }
0754:
0755: switch (col.jdbcType) {
0756: case java.sql.Types.BIT:
0757: case java.sql.Types.TINYINT:
0758: case java.sql.Types.SMALLINT:
0759: case java.sql.Types.INTEGER:
0760: case java.sql.Types.DATE:
0761: case java.sql.Types.TIME:
0762: case java.sql.Types.TIMESTAMP:
0763: col.length = 0;
0764: col.scale = 0;
0765: default:
0766: }
0767:
0768: currentColumns.add(col);
0769: }
0770: // we fin last table
0771: JdbcColumn[] lastJdbcColumns = new JdbcColumn[currentColumns
0772: .size()];
0773: currentColumns.toArray(lastJdbcColumns);
0774: JdbcTable colJdbcTable = (JdbcTable) jdbcTableMap
0775: .get(tableName);
0776: colJdbcTable.cols = lastJdbcColumns;
0777: tableName = null;
0778: currentColumns.clear();
0779:
0780: rsColumn.close();
0781: statCol.close();
0782:
0783: if (!params.checkColumnsOnly()) {
0784: Set mainTableNames = jdbcTableMap.keySet();
0785: if (params.isCheckPK()) {
0786: // now we do primaryKeys
0787: HashMap pkMap = null;
0788:
0789: String pkSql = "select TABLE_NAME = o.name, \n"
0790: + " COLUMN_NAME = c.name, \n"
0791: + " KEY_SEQ = \n"
0792: + " case \n"
0793: + " when c.name = index_col(o.name, i.indid, 1) then convert (smallint,1) \n"
0794: + " when c.name = index_col(o.name, i.indid, 2) then convert (smallint,2) \n"
0795: + " when c.name = index_col(o.name, i.indid, 3) then convert (smallint,3) \n"
0796: + " when c.name = index_col(o.name, i.indid, 4) then convert (smallint,4) \n"
0797: + " when c.name = index_col(o.name, i.indid, 5) then convert (smallint,5) \n"
0798: + " when c.name = index_col(o.name, i.indid, 6) then convert (smallint,6) \n"
0799: + " when c.name = index_col(o.name, i.indid, 7) then convert (smallint,7) \n"
0800: + " when c.name = index_col(o.name, i.indid, 8) then convert (smallint,8) \n"
0801: + " when c.name = index_col(o.name, i.indid, 9) then convert (smallint,9) \n"
0802: + " when c.name = index_col(o.name, i.indid, 10) then convert (smallint,10) \n"
0803: + " when c.name = index_col(o.name, i.indid, 11) then convert (smallint,11) \n"
0804: + " when c.name = index_col(o.name, i.indid, 12) then convert (smallint,12) \n"
0805: + " when c.name = index_col(o.name, i.indid, 13) then convert (smallint,13) \n"
0806: + " when c.name = index_col(o.name, i.indid, 14) then convert (smallint,14) \n"
0807: + " when c.name = index_col(o.name, i.indid, 15) then convert (smallint,15) \n"
0808: + " when c.name = index_col(o.name, i.indid, 16) then convert (smallint,16) \n"
0809: + " end, \n"
0810: + " PK_NAME = convert(sysname,i.name) \n"
0811: + " from sysindexes i, syscolumns c, sysobjects o\n"
0812: + " where o.id = c.id \n"
0813: + " and o.id = i.id \n"
0814: + " and i.status2 & 2 = 2\n"
0815: + " and i.status & 2048 = 2048\n"
0816: + " and (c.name = index_col (o.name, i.indid, 1) or \n"
0817: + " c.name = index_col (o.name, i.indid, 2) or \n"
0818: + " c.name = index_col (o.name, i.indid, 3) or \n"
0819: + " c.name = index_col (o.name, i.indid, 4) or \n"
0820: + " c.name = index_col (o.name, i.indid, 5) or \n"
0821: + " c.name = index_col (o.name, i.indid, 6) or \n"
0822: + " c.name = index_col (o.name, i.indid, 7) or \n"
0823: + " c.name = index_col (o.name, i.indid, 8) or \n"
0824: + " c.name = index_col (o.name, i.indid, 9) or \n"
0825: + " c.name = index_col (o.name, i.indid, 10) or \n"
0826: + " c.name = index_col (o.name, i.indid, 11) or \n"
0827: + " c.name = index_col (o.name, i.indid, 12) or \n"
0828: + " c.name = index_col (o.name, i.indid, 13) or \n"
0829: + " c.name = index_col (o.name, i.indid, 14) or \n"
0830: + " c.name = index_col (o.name, i.indid, 15) or \n"
0831: + " c.name = index_col (o.name, i.indid, 16) \n"
0832: + " ) \n" + " ORDER BY 1, 3";
0833:
0834: Statement statPK = con.createStatement();
0835: ResultSet rsPKs = statPK.executeQuery(pkSql);
0836: int pkCount = 0;
0837: String pkName = null;
0838: while (rsPKs.next()) {
0839: String temptableName = rsPKs.getString(1);
0840:
0841: if (!jdbcTableMap.containsKey(temptableName)) {
0842: continue;
0843: }
0844:
0845: if (tableName == null) { // this is the first one
0846: tableName = temptableName;
0847: pkMap = new HashMap();
0848: }
0849:
0850: if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
0851: JdbcColumn[] pkColumns = new JdbcColumn[pkCount];
0852: int indexOfPKCount = 0;
0853: JdbcTable jdbcTable = (JdbcTable) jdbcTableMap
0854: .get(tableName);
0855: for (int i = 0; i < jdbcTable.cols.length; i++) {
0856: JdbcColumn jdbcColumn = jdbcTable.cols[i];
0857: if (pkMap.containsKey(jdbcColumn.name)) {
0858: pkColumns[indexOfPKCount] = jdbcColumn;
0859: jdbcColumn.pk = true;
0860: indexOfPKCount++;
0861: }
0862: }
0863: jdbcTable.pk = pkColumns;
0864: jdbcTable.pkConstraintName = pkName;
0865:
0866: tableName = temptableName;
0867: pkMap.clear();
0868: pkCount = 0;
0869: }
0870: pkCount++;
0871: pkMap.put(rsPKs.getString(2), null);
0872: pkName = rsPKs.getString(4);
0873: }
0874: JdbcColumn[] pkColumns = new JdbcColumn[pkCount];
0875: int indexOfPKCount = 0;
0876: JdbcTable pkJdbcTable = (JdbcTable) jdbcTableMap
0877: .get(tableName);
0878: if (pkJdbcTable != null) {
0879: for (int i = 0; i < pkJdbcTable.cols.length; i++) {
0880: JdbcColumn jdbcColumn = pkJdbcTable.cols[i];
0881: if (pkMap.containsKey(jdbcColumn.name)) {
0882: pkColumns[indexOfPKCount] = jdbcColumn;
0883: jdbcColumn.pk = true;
0884: indexOfPKCount++;
0885: }
0886: }
0887: pkJdbcTable.pk = pkColumns;
0888: pkJdbcTable.pkConstraintName = pkName;
0889: }
0890: tableName = null;
0891: // clean up
0892: if (rsPKs != null) {
0893: try {
0894: rsPKs.close();
0895: } catch (SQLException e) {
0896: }
0897: }
0898: if (statPK != null) {
0899: try {
0900: statPK.close();
0901: } catch (SQLException e) {
0902: }
0903: }
0904: }
0905: if (params.isCheckIndex()) {
0906: // now we do index /////////////////////////////////////////////////////////////////////////
0907: String indexSql = "select 'TABLE_NAME' = o.name, \n"
0908: + " 'COLUMN_NAME' = INDEX_COL(o.name,indid,colid), \n"
0909: + " 'INDEX_NAME' = x.name, \n"
0910: + " 'NON_UNIQUE' =\n"
0911: + " case\n"
0912: + " when x.status & 2 != 2 then convert (smallint,1)\n"
0913: + " else convert (smallint,0)\n"
0914: + " end,\n"
0915: + " 'TYPE' = \n"
0916: + " case\n"
0917: + " when x.indid > 1 then convert (smallint,3)\n"
0918: + " when x.status2 & 512 = 512 then convert (smallint,1)\n"
0919: + " else convert (smallint,1)\n"
0920: + " end,\n"
0921: + " 'ORDINAL_POSITION' = colid \n"
0922: + " from sysindexes x, syscolumns c, sysobjects o \n"
0923: + " where x.id = object_id(o.name) \n"
0924: + " and x.id = o.id \n"
0925: + " and o.type = 'U' \n"
0926: + " and x.status = 0 \n"
0927: + " and x.id = c.id \n"
0928: + " and c.colid < keycnt + (x.status & 16) / 16\n"
0929: + " ORDER BY TABLE_NAME,INDEX_NAME,ORDINAL_POSITION";
0930: Statement statIndex = con.createStatement();
0931: ResultSet rsIndex = statIndex.executeQuery(indexSql);
0932:
0933: HashMap indexNameMap = null;
0934: ArrayList indexes = null;
0935: while (rsIndex.next()) {
0936: String temptableName = rsIndex.getString(1);
0937: if (tableName == null) { // this is the first one
0938: tableName = temptableName;
0939: indexNameMap = new HashMap();
0940: indexes = new ArrayList();
0941: }
0942:
0943: String indexName = rsIndex.getString(3);
0944: JdbcTable tempJdbcTable = (JdbcTable) jdbcTableMap
0945: .get(temptableName);
0946:
0947: if (indexName != null
0948: && !indexName
0949: .equals(tempJdbcTable.pkConstraintName)) {
0950: if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
0951: JdbcTable jdbcTable = (JdbcTable) jdbcTableMap
0952: .get(tableName);
0953: JdbcIndex[] jdbcIndexes = new JdbcIndex[indexes
0954: .size()];
0955: indexes.toArray(jdbcIndexes);
0956: jdbcTable.indexes = jdbcIndexes;
0957:
0958: tableName = temptableName;
0959: indexes.clear();
0960: indexNameMap.clear();
0961:
0962: }
0963: JdbcTable jdbcTable = (JdbcTable) jdbcTableMap
0964: .get(tableName);
0965: if (indexNameMap.containsKey(indexName)) {
0966: JdbcIndex index = null;
0967: for (Iterator iter = indexes.iterator(); iter
0968: .hasNext();) {
0969: JdbcIndex jdbcIndex = (JdbcIndex) iter
0970: .next();
0971: if (jdbcIndex.name.equals(indexName)) {
0972: index = jdbcIndex;
0973: }
0974: }
0975:
0976: JdbcColumn[] tempIndexColumns = index.cols;
0977: JdbcColumn[] indexColumns = new JdbcColumn[tempIndexColumns.length + 1];
0978: System.arraycopy(tempIndexColumns, 0,
0979: indexColumns, 0,
0980: tempIndexColumns.length);
0981: String colName = rsIndex.getString(2);
0982: for (int i = 0; i < jdbcTable.cols.length; i++) {
0983: JdbcColumn jdbcColumn = jdbcTable.cols[i];
0984: if (colName.equals(jdbcColumn.name)) {
0985: indexColumns[tempIndexColumns.length] = jdbcColumn;
0986: jdbcColumn.partOfIndex = true;
0987: }
0988: }
0989: index.setCols(indexColumns);
0990: } else {
0991: indexNameMap.put(indexName, null);
0992: JdbcIndex index = new JdbcIndex();
0993: index.name = indexName;
0994: index.unique = !rsIndex.getBoolean(4);
0995: short indexType = rsIndex.getShort(5);
0996: switch (indexType) {
0997: case DatabaseMetaData.tableIndexClustered:
0998: index.clustered = true;
0999: break;
1000: }
1001: String colName = rsIndex.getString(2);
1002: JdbcColumn[] indexColumns = new JdbcColumn[1];
1003: for (int i = 0; i < jdbcTable.cols.length; i++) {
1004: JdbcColumn jdbcColumn = jdbcTable.cols[i];
1005: if (colName.equals(jdbcColumn.name)) {
1006: indexColumns[0] = jdbcColumn;
1007: jdbcColumn.partOfIndex = true;
1008: }
1009: }
1010: index.setCols(indexColumns);
1011: indexes.add(index);
1012: }
1013: }
1014: }
1015: JdbcTable indexJdbcTable = (JdbcTable) jdbcTableMap
1016: .get(tableName);
1017: if (indexJdbcTable != null) {
1018: JdbcIndex[] jdbcIndexes = new JdbcIndex[indexes
1019: .size()];
1020: indexes.toArray(jdbcIndexes);
1021: indexJdbcTable.indexes = jdbcIndexes;
1022: indexes.clear();
1023: indexNameMap.clear();
1024: }
1025: tableName = null;
1026:
1027: // clean up
1028: if (rsIndex != null) {
1029: try {
1030: rsIndex.close();
1031: } catch (SQLException e) {
1032: }
1033: }
1034: if (statIndex != null) {
1035: try {
1036: statIndex.close();
1037: } catch (SQLException e) {
1038: }
1039: }
1040: }
1041: // end of index ///////////////////////////////////////////////////////////////////////
1042:
1043: if (params.isCheckConstraint()) {
1044:
1045: // now we do forign keys
1046: String fkSql1 = " create table #fkeysall( \n"
1047: + " rkeyid int NOT NULL, \n"
1048: + " rkey1 int NOT NULL,\n"
1049: + " rkey2 int NOT NULL, \n"
1050: + " rkey3 int NOT NULL, \n"
1051: + " rkey4 int NOT NULL, \n"
1052: + " rkey5 int NOT NULL, \n"
1053: + " rkey6 int NOT NULL, \n"
1054: + " rkey7 int NOT NULL, \n"
1055: + " rkey8 int NOT NULL, \n"
1056: + " rkey9 int NOT NULL, \n"
1057: + " rkey10 int NOT NULL, \n"
1058: + " rkey11 int NOT NULL, \n"
1059: + " rkey12 int NOT NULL, \n"
1060: + " rkey13 int NOT NULL, \n"
1061: + " rkey14 int NOT NULL, \n"
1062: + " rkey15 int NOT NULL, \n"
1063: + " rkey16 int NOT NULL, \n"
1064: + " fkeyid int NOT NULL, \n"
1065: + " fkey1 int NOT NULL, \n"
1066: + " fkey2 int NOT NULL, \n"
1067: + " fkey3 int NOT NULL, \n"
1068: + " fkey4 int NOT NULL, \n"
1069: + " fkey5 int NOT NULL, \n"
1070: + " fkey6 int NOT NULL, \n"
1071: + " fkey7 int NOT NULL, \n"
1072: + " fkey8 int NOT NULL, \n"
1073: + " fkey9 int NOT NULL, \n"
1074: + " fkey10 int NOT NULL, \n"
1075: + " fkey11 int NOT NULL, \n"
1076: + " fkey12 int NOT NULL, \n"
1077: + " fkey13 int NOT NULL, \n"
1078: + " fkey14 int NOT NULL, \n"
1079: + " fkey15 int NOT NULL, \n"
1080: + " fkey16 int NOT NULL, \n"
1081: + " constid int NOT NULL, \n"
1082: + " name varchar(32) NOT NULL) ";
1083: Statement statFK1 = con.createStatement();
1084: statFK1.execute(fkSql1);
1085: statFK1.close();
1086: String fkSql2 = "insert into #fkeysall\n"
1087: + " select\n"
1088: + " r.reftabid,\n"
1089: + " r.refkey1, r.refkey2, r.refkey3, r.refkey4,\n"
1090: + " r.refkey5, r.refkey6, r.refkey7, r.refkey8,\n"
1091: + " r.refkey9, r.refkey10, r.refkey11, r.refkey12,\n"
1092: + " r.refkey13, r.refkey14, r.refkey15, r.refkey16,\n"
1093: + " r.tableid,\n"
1094: + " r.fokey1, r.fokey2, r.fokey3, r.fokey4,\n"
1095: + " r.fokey5, r.fokey6, r.fokey7, r.fokey8,\n"
1096: + " r.fokey9, r.fokey10, r.fokey11, r.fokey12,\n"
1097: + " r.fokey13, r.fokey14, r.fokey15, r.fokey16,\n"
1098: + " r.constrid,\n"
1099: + " i.name\n"
1100: + " from sysreferences r, sysobjects o, sysindexes i\n"
1101: + " where r.constrid = o.id\n"
1102: + " AND o.type = 'RI'\n"
1103: + " AND r.indexid = i.indid\n"
1104: + " AND r.reftabid = i.id";
1105: Statement statFK2 = con.createStatement();
1106: statFK2.execute(fkSql2);
1107: statFK2.close();
1108: String fkSql3 = "create table #fkeys( \n"
1109: + " pktable_id int NOT NULL, \n"
1110: + " pkcolid int NOT NULL, \n"
1111: + " fktable_id int NOT NULL, \n"
1112: + " fkcolid int NOT NULL, \n"
1113: + " KEY_SEQ smallint NOT NULL, \n"
1114: + " fk_id int NOT NULL, \n"
1115: + " PK_NAME varchar(32) NOT NULL) ";
1116: Statement statFK3 = con.createStatement();
1117: statFK3.execute(fkSql3);
1118: statFK3.close();
1119: String fkSql4 = "insert into #fkeys\n"
1120: + " select rkeyid, rkey1, fkeyid, fkey1, 1, constid, name\n"
1121: + " from #fkeysall\n"
1122: + " union all\n"
1123: + " select rkeyid, rkey2, fkeyid, fkey2, 2, constid, name\n"
1124: + " from #fkeysall\n"
1125: + " union all\n"
1126: + " select rkeyid, rkey3, fkeyid, fkey3, 3, constid, name\n"
1127: + " from #fkeysall\n"
1128: + " union all\n"
1129: + " select rkeyid, rkey4, fkeyid, fkey4, 4, constid, name\n"
1130: + " from #fkeysall\n"
1131: + " union all\n"
1132: + " select rkeyid, rkey5, fkeyid, fkey5, 5, constid, name\n"
1133: + " from #fkeysall\n"
1134: + " union all\n"
1135: + " select rkeyid, rkey6, fkeyid, fkey6, 6, constid, name\n"
1136: + " from #fkeysall\n"
1137: + " union all\n"
1138: + " select rkeyid, rkey7, fkeyid, fkey7, 7, constid, name\n"
1139: + " from #fkeysall\n"
1140: + " union all\n"
1141: + " select rkeyid, rkey8, fkeyid, fkey8, 8, constid, name\n"
1142: + " from #fkeysall\n"
1143: + " union all\n"
1144: + " select rkeyid, rkey9, fkeyid, fkey9, 9, constid, name\n"
1145: + " from #fkeysall\n"
1146: + " union all\n"
1147: + " select rkeyid, rkey10, fkeyid, fkey10, 10, constid, name\n"
1148: + " from #fkeysall\n"
1149: + " union all\n"
1150: + " select rkeyid, rkey11, fkeyid, fkey11, 11, constid, name\n"
1151: + " from #fkeysall\n"
1152: + " union all\n"
1153: + " select rkeyid, rkey12, fkeyid, fkey12, 12, constid, name\n"
1154: + " from #fkeysall\n"
1155: + " union all\n"
1156: + " select rkeyid, rkey13, fkeyid, fkey13, 13, constid, name\n"
1157: + " from #fkeysall\n"
1158: + " union all\n"
1159: + " select rkeyid, rkey14, fkeyid, fkey14, 14, constid, name\n"
1160: + " from #fkeysall\n"
1161: + " union all\n"
1162: + " select rkeyid, rkey15, fkeyid, fkey15, 15, constid, name\n"
1163: + " from #fkeysall\n"
1164: + " union all\n"
1165: + " select rkeyid, rkey16, fkeyid, fkey16, 16, constid, name\n"
1166: + " from #fkeysall";
1167: Statement statFK4 = con.createStatement();
1168: statFK4.execute(fkSql4);
1169: statFK4.close();
1170:
1171: String fkSql = "select PKTABLE_NAME = convert(sysname,o1.name),\n"
1172: + " PKCOLUMN_NAME = convert(sysname,c1.name),\n"
1173: + " FKTABLE_NAME = convert(sysname,o2.name),\n"
1174: + " FKCOLUMN_NAME = convert(sysname,c2.name),\n"
1175: + " KEY_SEQ,\n"
1176: + " FK_NAME = convert(sysname,OBJECT_NAME(fk_id)),\n"
1177: + " PK_NAME\n"
1178: + " from #fkeys f,\n"
1179: + " sysobjects o1, sysobjects o2,\n"
1180: + " syscolumns c1, syscolumns c2\n"
1181: + " where o1.id = f.pktable_id\n"
1182: + " AND o2.id = f.fktable_id\n"
1183: + " AND c1.id = f.pktable_id\n"
1184: + " AND c2.id = f.fktable_id\n"
1185: + " AND c1.colid = f.pkcolid\n"
1186: + " AND c2.colid = f.fkcolid\n"
1187: + " ORDER BY 3,6,5";
1188: Statement statFK = con.createStatement();
1189: ResultSet rsFKs = statFK.executeQuery(fkSql);
1190:
1191: HashMap constraintNameMap = null;
1192: ArrayList constraints = null;
1193: while (rsFKs.next()) {
1194: String temptableName = rsFKs.getString(3);
1195: if (tableName == null) { // this is the first one
1196: tableName = temptableName;
1197: constraintNameMap = new HashMap();
1198: constraints = new ArrayList();
1199: }
1200:
1201: if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
1202: JdbcTable jdbcTable = (JdbcTable) jdbcTableMap
1203: .get(tableName);
1204: JdbcConstraint[] jdbcConstraints = new JdbcConstraint[constraints
1205: .size()];
1206: constraints.toArray(jdbcConstraints);
1207: jdbcTable.constraints = jdbcConstraints;
1208:
1209: tableName = temptableName;
1210: constraintNameMap.clear();
1211: constraints.clear();
1212: }
1213:
1214: String fkName = rsFKs.getString(6);
1215: JdbcTable jdbcTable = (JdbcTable) jdbcTableMap
1216: .get(tableName);
1217: if (constraintNameMap.containsKey(fkName)) {
1218: JdbcConstraint constraint = null;
1219: for (Iterator iter = constraints.iterator(); iter
1220: .hasNext();) {
1221: JdbcConstraint jdbcConstraint = (JdbcConstraint) iter
1222: .next();
1223: if (jdbcConstraint.name.equals(fkName)) {
1224: constraint = jdbcConstraint;
1225: }
1226: }
1227:
1228: JdbcColumn[] tempConstraintColumns = constraint.srcCols;
1229: JdbcColumn[] constraintColumns = new JdbcColumn[tempConstraintColumns.length + 1];
1230: System.arraycopy(tempConstraintColumns, 0,
1231: constraintColumns, 0,
1232: tempConstraintColumns.length);
1233: String colName = rsFKs.getString(4);
1234: for (int i = 0; i < jdbcTable.cols.length; i++) {
1235: JdbcColumn jdbcColumn = jdbcTable.cols[i];
1236: if (colName.equals(jdbcColumn.name)) {
1237: constraintColumns[tempConstraintColumns.length] = jdbcColumn;
1238: jdbcColumn.foreignKey = true;
1239: }
1240: }
1241: constraint.srcCols = constraintColumns;
1242: } else {
1243: constraintNameMap.put(fkName, null);
1244: JdbcConstraint constraint = new JdbcConstraint();
1245: constraint.name = fkName;
1246: constraint.src = jdbcTable;
1247: String colName = rsFKs.getString(4);
1248: JdbcColumn[] constraintColumns = new JdbcColumn[1];
1249: for (int i = 0; i < jdbcTable.cols.length; i++) {
1250: JdbcColumn jdbcColumn = jdbcTable.cols[i];
1251: if (colName.equals(jdbcColumn.name)) {
1252: constraintColumns[0] = jdbcColumn;
1253: jdbcColumn.foreignKey = true;
1254: }
1255: }
1256: constraint.srcCols = constraintColumns;
1257: constraint.dest = (JdbcTable) jdbcTableMap
1258: .get(rsFKs.getString(1));
1259: constraints.add(constraint);
1260: }
1261: }
1262: JdbcTable constraintsjdbcTable = (JdbcTable) jdbcTableMap
1263: .get(tableName);
1264: if (constraintsjdbcTable != null) {
1265: JdbcConstraint[] jdbcConstraints = new JdbcConstraint[constraints
1266: .size()];
1267: constraints.toArray(jdbcConstraints);
1268: constraintsjdbcTable.constraints = jdbcConstraints;
1269: }
1270:
1271: if (rsFKs != null) {
1272: try {
1273: rsFKs.close();
1274: } catch (SQLException e) {
1275: }
1276: }
1277: if (statFK != null) {
1278: try {
1279: statFK.close();
1280: } catch (SQLException e) {
1281: }
1282: }
1283:
1284: Statement statCleanUp = con.createStatement();
1285: statCleanUp.execute("DROP TABLE #fkeysall, #fkeys");
1286: if (statCleanUp != null) {
1287: try {
1288: statCleanUp.close();
1289: } catch (SQLException e) {
1290: }
1291: }
1292:
1293: }
1294: }
1295: HashMap returnMap = new HashMap();
1296: Collection col = jdbcTableMap.values();
1297: for (Iterator iterator = col.iterator(); iterator.hasNext();) {
1298: JdbcTable table = (JdbcTable) iterator.next();
1299: returnMap.put(table.name.toLowerCase(), table);
1300: }
1301: fixAllNames(returnMap);
1302: return returnMap;
1303: }
1304:
1305: public String getRunCommand() {
1306: return "\ngo\n";
1307: }
1308:
1309: /**
1310: * Append a column that needs to be added.
1311: */
1312: protected void appendAddNewColumn(JdbcTable t, JdbcColumn c,
1313: CharBuf s, boolean comments) {
1314: if (comments && isCommentSupported() && c.comment != null) {
1315: s.append(comment("add column for field " + c.comment));
1316: }
1317:
1318: s.append("\n");
1319: if (isAddSequenceColumn(c)) {
1320: addSequenceColumn(t, c, s, comments);
1321: } else {
1322: s.append("ALTER TABLE ");
1323: s.append(t.name);
1324: s.append(" ADD ");
1325: s.append(c.name);
1326: s.append(' ');
1327: appendColumnType(c, s);
1328: if (c.autoinc) {
1329: appendCreateColumnAutoInc(t, c, s);
1330: s.append(getRunCommand());
1331: } else if (c.nulls) {
1332: appendCreateColumnNulls(t, c, s);
1333: s.append(getRunCommand());
1334: } else {
1335: s.append(" NULL");
1336: s.append(getRunCommand());
1337:
1338: s.append("UPDATE ");
1339: s.append(t.name);
1340: s.append(" SET ");
1341: s.append(c.name);
1342: s.append(" = ");
1343: s.append(getDefaultForType(c));
1344: s.append(getRunCommand());
1345:
1346: s.append("ALTER TABLE ");
1347: s.append(t.name);
1348: s.append(" MODIFY ");
1349: s.append(c.name);
1350: s.append(' ');
1351: appendColumnType(c, s);
1352: s.append(" NOT NULL");
1353: s.append(getRunCommand());
1354: }
1355: }
1356: }
1357:
1358: /**
1359: * Add a Sequence column to implement a list
1360: */
1361: protected void addSequenceColumn(JdbcTable t, JdbcColumn c,
1362: CharBuf s, boolean comments) {
1363:
1364: String mainTempTableName = getTempTableName(t, 30);
1365: String minTempTableName = getTempTableName(t, 30);
1366: String identityColumnName = getTempColumnName(t);
1367:
1368: JdbcColumn indexColumn = null;
1369: JdbcColumn sequenceColumn = null;
1370: JdbcColumn[] cols = t.getColsForCreateTable();
1371: int nc = cols.length;
1372: for (int i = 0; i < nc; i++) {
1373: if (isAddSequenceColumn(cols[i])) {
1374: sequenceColumn = cols[i];
1375: } else if (t.isInPrimaryKey(cols[i].name)) {
1376: indexColumn = cols[i];
1377: }
1378: }
1379:
1380: s
1381: .append(comment("Generate a sequence number so that we can implement a List."));
1382: s.append("\n");
1383: s
1384: .append(comment("create a temp table with a extra identity column."));
1385: s.append("\n");
1386: s.append("CREATE TABLE ");
1387: s.append(mainTempTableName);
1388: s.append(" (\n ");
1389: // create identity column
1390: s.append(identityColumnName);
1391: s.append(" NUMERIC(6) IDENTITY,");
1392: for (int i = 0; i < nc; i++) {
1393: s.append("\n ");
1394: appendCreateColumn(t, cols[i], s, comments);
1395: }
1396: int lastIndex = s.toString().lastIndexOf(',');
1397: s.replace(lastIndex, lastIndex + 1, ' ');// we take the last ',' out.
1398: s.append("\n)");
1399:
1400: s.append(getRunCommand());
1401:
1402: s
1403: .append(comment("insert a '0' in the sequence column and copy the rest of the old table into the temp table."));
1404: s.append("\n");
1405: s.append("INSERT INTO ");
1406: s.append(mainTempTableName);
1407: s.append("(");
1408: for (int i = 0; i < nc; i++) {
1409: s.append(cols[i].name);
1410: if ((i + 1) != nc) {
1411: s.append(", ");
1412: }
1413: }
1414: s.append(")");
1415: s.append("\nSELECT ");
1416: for (int i = 0; i < nc; i++) {
1417: if (i != 0) {
1418: s.append("\n ");
1419: }
1420: if (isAddSequenceColumn(cols[i])) {
1421: s.append('0');
1422: } else {
1423: s.append(cols[i].name);
1424: }
1425: if ((i + 1) != nc) {
1426: s.append(", ");
1427: }
1428: }
1429: s.append("\n FROM ");
1430: s.append(t.name);
1431: s.append("\n ORDER BY ");
1432: s.append(indexColumn.name);
1433:
1434: s.append(getRunCommand());
1435:
1436: s
1437: .append(comment("create a temp table to store the minimum id."));
1438: s.append("\n");
1439: s.append("CREATE TABLE ");
1440: s.append(minTempTableName);
1441: s.append(" (\n ");
1442: s.append(indexColumn.name);
1443: s.append(' ');
1444: appendColumnType(indexColumn, s);
1445: appendCreateColumnNulls(t, indexColumn, s);
1446: s.append(",\n ");
1447: s.append("min_id");
1448: s.append(" INTEGER\n)");
1449:
1450: s.append(getRunCommand());
1451:
1452: s.append(comment("store the minimum id."));
1453: s.append("\n");
1454: s.append("INSERT INTO ");
1455: s.append(minTempTableName);
1456: s.append(" (");
1457: s.append(indexColumn.name);
1458: s.append(", ");
1459: s.append("min_id");
1460: s.append(")\n");
1461: s.append("SELECT ");
1462: s.append(indexColumn.name);
1463: s.append(",\n ");
1464: s.append("MIN(");
1465: s.append(identityColumnName);
1466: s.append(")\n");
1467: s.append(" FROM ");
1468: s.append(mainTempTableName);
1469: s.append("\n");
1470: s.append(" GROUP BY ");
1471: s.append(indexColumn.name);
1472:
1473: s.append(getRunCommand());
1474:
1475: s.append(comment("update the temp table's sequence column."));
1476: s.append("\n");
1477: s.append("UPDATE ");
1478: s.append(mainTempTableName);
1479: s.append("\n SET ");
1480: s.append(sequenceColumn.name);
1481: s.append(" = (");
1482: s.append(identityColumnName);
1483: s.append(" - ");
1484: s.append("b.");
1485: s.append("min_id");
1486: s.append(")\n");
1487: s.append(" FROM ");
1488: s.append(mainTempTableName);
1489: s.append(" a,\n");
1490: s.append(" ");
1491: s.append(minTempTableName);
1492: s.append(" b\n");
1493: s.append(" WHERE a.");
1494: s.append(indexColumn.name);
1495: s.append(" = b.");
1496: s.append(indexColumn.name);
1497:
1498: s.append(getRunCommand());
1499:
1500: s.append(comment("drop main table " + t.name + "."));
1501: s.append("\n");
1502: s.append("DROP TABLE ");
1503: s.append(t.name);
1504:
1505: s.append(getRunCommand());
1506:
1507: s.append(comment("recreate table " + t.name + "."));
1508: s.append("\n");
1509: s.append("CREATE TABLE ");
1510: s.append(t.name);
1511: s.append(" (\n");
1512: boolean first = true;
1513: for (int i = 0; i < nc; i++) {
1514: if (first)
1515: first = false;
1516: else
1517: s.append("\n");
1518: s.append(" ");
1519: appendCreateColumn(t, cols[i], s, comments);
1520: }
1521: s.append("\n ");
1522: appendPrimaryKeyConstraint(t, s);
1523: s.append("\n)");
1524:
1525: s.append(getRunCommand());
1526:
1527: s.append(comment("populate table " + t.name
1528: + " with the new sequence column."));
1529: s.append("\n");
1530: s.append("INSERT INTO ");
1531: s.append(t.name);
1532: s.append("(");
1533: for (int i = 0; i < nc; i++) {
1534: s.append(cols[i].name);
1535: if ((i + 1) != nc) {
1536: s.append(", ");
1537: }
1538: }
1539: s.append(")");
1540: s.append("\nSELECT ");
1541: for (int i = 0; i < nc; i++) {
1542: if (i != 0) {
1543: s.append("\n ");
1544: }
1545: s.append(cols[i].name);
1546:
1547: if ((i + 1) != nc) {
1548: s.append(", ");
1549: }
1550: }
1551: s.append("\n FROM ");
1552: s.append(mainTempTableName);
1553:
1554: s.append(getRunCommand());
1555:
1556: s.append(comment("drop temp tables."));
1557: s.append("\n");
1558: s.append("DROP TABLE ");
1559: s.append(mainTempTableName);
1560: s.append(getRunCommand());
1561:
1562: s.append("DROP TABLE ");
1563: s.append(minTempTableName);
1564: s.append(getRunCommand());
1565:
1566: }
1567:
1568: /**
1569: * Append a column that needs to be added.
1570: */
1571: protected void appendModifyColumn(TableDiff tableDiff,
1572: ColumnDiff diff, CharBuf s, boolean comments) {
1573: JdbcTable t = tableDiff.getOurTable();
1574: JdbcColumn c = diff.getOurCol();
1575: boolean nulls = diff.isNullDiff();
1576: if (comments && isCommentSupported() && c.comment != null) {
1577: s.append(comment("modify column for field " + c.comment));
1578: }
1579: if (comments && isCommentSupported() && c.comment == null) {
1580: s.append(comment("modify column " + c.name));
1581: }
1582: s.append("\n");
1583: s.append("ALTER TABLE ");
1584: s.append(t.name);
1585: s.append(" MODIFY ");
1586: s.append(c.name);
1587: s.append(' ');
1588: appendColumnType(c, s);
1589: if (nulls) {
1590: if (c.nulls) {
1591: s.append(" NULL");
1592: } else {
1593: s.append(" NOT NULL");
1594: }
1595: }
1596: }
1597:
1598: /**
1599: * Append a column that needs to be added.
1600: */
1601: protected void appendDropColumn(TableDiff tableDiff, JdbcColumn c,
1602: CharBuf s, boolean comments) {
1603: if (comments && isCommentSupported()) {
1604: s.append(comment("dropping unknown column " + c.name));
1605: }
1606: s.append("\n");
1607:
1608: if (isDropSequenceColumn(tableDiff, c)) {
1609: dropSequenceColumn(tableDiff.getOurTable(), c, s, comments);
1610: } else {
1611: s.append("ALTER TABLE ");
1612: s.append(tableDiff.getOurTable().name);
1613: s.append(" DROP ");
1614: s.append(c.name);
1615: }
1616: }
1617:
1618: /**
1619: * Append an 'drop constraint' statement for c.
1620: */
1621: protected void appendRefDropConstraint(CharBuf s, JdbcConstraint c,
1622: boolean comments) {
1623: // if (comments && isCommentSupported()) {
1624: // s.append(comment("dropping unknown constraint " + c.name));
1625: // s.append('\n');
1626: // }
1627: s.append("ALTER TABLE ");
1628: s.append(c.src.name);
1629: s.append(" DROP CONSTRAINT ");
1630: s.append(c.name);
1631: }
1632:
1633: /**
1634: * Generate a 'drop index' statement for idx.
1635: */
1636: protected void appendDropIndex(CharBuf s, JdbcTable t,
1637: JdbcIndex idx, boolean comments) {
1638: // if (comments && isCommentSupported()) {
1639: // s.append(comment("dropping unknown index "+ idx.name));
1640: // s.append('\n');
1641: // }
1642: s.append("DROP INDEX ");
1643: s.append(t.name);
1644: s.append('.');
1645: s.append(idx.name);
1646: }
1647:
1648: /**
1649: * Add the primary key constraint in isolation.
1650: */
1651: protected void addPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
1652: s.append("ALTER TABLE ");
1653: s.append(t.name);
1654: s.append(" ADD ");
1655: appendPrimaryKeyConstraint(t, s);
1656: }
1657:
1658: /**
1659: * Drop the primary key constraint in isolation.
1660: */
1661: protected void dropPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
1662: s.append("ALTER TABLE ");
1663: s.append(t.name);
1664: s.append(" DROP CONSTRAINT ");
1665: s.append(t.pkConstraintName);
1666: }
1667:
1668: /**
1669: * Drop a Sequence column to implement a Set
1670: */
1671: protected void dropSequenceColumn(JdbcTable t, JdbcColumn c,
1672: CharBuf s, boolean comments) {
1673: String tempTableName = getTempTableName(t, 30);
1674:
1675: s
1676: .append(comment("create a temp table to store old table values."));
1677: s.append("\n");
1678: s.append("CREATE TABLE ");
1679: s.append(tempTableName);
1680: s.append(" (\n");
1681: JdbcColumn[] cols = t.getColsForCreateTable();
1682: int nc = cols.length;
1683: boolean first = true;
1684: for (int i = 0; i < nc; i++) {
1685: if (first)
1686: first = false;
1687: else
1688: s.append("\n");
1689: s.append(" ");
1690: appendCreateColumn(t, cols[i], s, comments);
1691: }
1692: s.append("\n ");
1693: appendPrimaryKeyConstraint(t, s);
1694: s.append("\n)");
1695: s.append(getRunCommand());
1696:
1697: s
1698: .append(comment("insert a distinct list into the temp table."));
1699: s.append("\n");
1700: s.append("INSERT INTO ");
1701: s.append(tempTableName);
1702: s.append("(");
1703: for (int i = 0; i < nc; i++) {
1704: s.append(cols[i].name);
1705: if ((i + 1) != nc) {
1706: s.append(", ");
1707: }
1708: }
1709: s.append(")");
1710: s.append("\nSELECT DISTINCT ");
1711: for (int i = 0; i < nc; i++) {
1712: if (i != 0) {
1713: s.append("\n ");
1714: }
1715: s.append(cols[i].name);
1716: if ((i + 1) != nc) {
1717: s.append(", ");
1718: }
1719: }
1720: s.append("\n FROM ");
1721: s.append(t.name);
1722:
1723: s.append(getRunCommand());
1724:
1725: s.append(comment("drop main table."));
1726: s.append("\n");
1727: s.append("DROP TABLE ");
1728: s.append(t.name);
1729: s.append(getRunCommand());
1730:
1731: s.append(comment("rename temp table to main table."));
1732: s.append("\n");
1733: s.append("sp_rename ");
1734: s.append(tempTableName);
1735: s.append(", ");
1736: s.append(t.name);
1737:
1738: }
1739:
1740: protected void fixColumnsNonDirect(TableDiff tableDiff,
1741: PrintWriter out) {
1742:
1743: JdbcTable ourTable = tableDiff.getOurTable();
1744: String tempTableName = getTempTableName(ourTable, 30);
1745: CharBuf s = new CharBuf();
1746: s.append("CREATE TABLE ");
1747: s.append(tempTableName);
1748: s.append(" (\n");
1749: JdbcColumn[] cols = ourTable.getColsForCreateTable();
1750: int nc = cols.length;
1751: boolean first = true;
1752: for (int i = 0; i < nc; i++) {
1753: if (first) {
1754: first = false;
1755: } else {
1756: s.append("\n");
1757: }
1758: s.append(" ");
1759: appendCreateColumn(ourTable, cols[i], s, true);
1760: }
1761: s.append("\n ");
1762: appendPrimaryKeyConstraint(ourTable, s);
1763: s.append("\n)");
1764:
1765: s.append(getRunCommand());
1766:
1767: s.append("INSERT INTO ");
1768: s.append(tempTableName); //ourTable.name
1769: s.append(" (");
1770: for (int i = 0; i < nc; i++) {
1771: s.append(cols[i].name);
1772: if ((i + 1) != nc) {
1773: s.append(", ");
1774: }
1775: }
1776: s.append(") ");
1777:
1778: s.append("\n");//new line
1779:
1780: s.append("SELECT ");
1781: for (int i = 0; i < nc; i++) {
1782: ColumnDiff diff = getColumnDiffForName(tableDiff,
1783: cols[i].name);
1784: if (diff == null) {
1785: if (i != 0) {
1786: s.append(" ");
1787: }
1788: s.append(cols[i].name);
1789: } else {
1790: if (diff.isMissingCol()) {
1791: if (diff.getOurCol().nulls) {
1792: if (i != 0) {
1793: s.append(" ");
1794: }
1795: s.append("NULL");
1796: } else {
1797: if (i != 0) {
1798: s.append(" ");
1799: }
1800: s.append(getDefaultForType(diff.getOurCol()));
1801: }
1802:
1803: } else if ((diff.isLenghtDiff() || diff.isScaleDiff() || diff
1804: .isTypeDiff())
1805: && diff.isNullDiff()) {
1806: if (cols[i].nulls) {
1807: if (i != 0) {
1808: s.append(" ");
1809: }
1810: //select convert(char(25),col1) from test1a
1811: s.append("CONVERT(");
1812: appendColumnType(cols[i], s);
1813: s.append(", ");
1814: s.append(cols[i].name);
1815: s.append(")");
1816: } else {
1817: if (i != 0) {
1818: s.append(" ");
1819: }
1820: s.append("CASE ");
1821: s.append("\n");//new line
1822: s.append(" WHEN ");
1823: s.append(cols[i].name);
1824: s.append(" IS NOT NULL THEN CONVERT(");
1825: appendColumnType(cols[i], s);
1826: s.append(", ");
1827: s.append(cols[i].name);
1828: s.append(")");
1829: s.append("\n");//new line
1830: s.append(" ELSE ");
1831: s.append(getDefaultForType(diff.getOurCol()));
1832: s.append("\n");//new line
1833: s.append(" END");
1834: }
1835:
1836: } else if ((diff.isLenghtDiff() || diff.isScaleDiff() || diff
1837: .isTypeDiff())
1838: && !diff.isNullDiff()) {
1839: if (i != 0) {
1840: s.append(" ");
1841: }
1842: s.append("CONVERT(");
1843: appendColumnType(cols[i], s);
1844: s.append(", ");
1845: s.append(cols[i].name);
1846: s.append(")");
1847: } else if (diff.isNullDiff()) {
1848: if (cols[i].nulls) {
1849: if (i != 0) {
1850: s.append(" ");
1851: }
1852: s.append(cols[i].name);
1853: } else {
1854: if (i != 0) {
1855: s.append(" ");
1856: }
1857: s.append("CASE ");
1858: s.append("\n");//new line
1859: s.append(" WHEN ");
1860: s.append(cols[i].name);
1861: s.append(" IS NOT NULL THEN ");
1862: s.append(cols[i].name);
1863: s.append("\n");//new line
1864: s.append(" ELSE ");
1865: s.append(getDefaultForType(diff.getOurCol()));
1866: s.append("\n");//new line
1867: s.append(" END");
1868: }
1869: }
1870: }
1871:
1872: if ((i + 1) != nc) {
1873: s.append(", ");
1874: s.append("\n");//new line
1875: }
1876: }
1877: s.append("\n");//new line
1878: s.append(" FROM ");
1879: s.append(ourTable.name);
1880:
1881: s.append(getRunCommand());
1882:
1883: s.append("DROP TABLE ");
1884: s.append(ourTable.name);
1885:
1886: s.append(getRunCommand());
1887:
1888: s.append("sp_rename ");
1889: s.append(tempTableName);
1890: s.append(", ");
1891: s.append(ourTable.name);
1892:
1893: s.append(getRunCommand());
1894: out.println(s.toString());
1895:
1896: }
1897:
1898: boolean isDirectDropColumnSupported() {
1899: return supportDirectColumnChange;
1900: }
1901:
1902: boolean isDirectAddColumnSupported(JdbcColumn ourCol) {
1903: return supportDirectColumnChange;
1904: }
1905:
1906: boolean isDirectNullColumnChangesSupported() {
1907: return supportDirectColumnChange;
1908: }
1909:
1910: boolean isDirectScaleColumnChangesSupported(JdbcColumn ourCol,
1911: JdbcColumn dbCol) {
1912: return supportDirectColumnChange;
1913: }
1914:
1915: boolean isDirectLenghtColumnChangesSupported(JdbcColumn ourCol,
1916: JdbcColumn dbCol) {
1917: return supportDirectColumnChange;
1918: }
1919:
1920: boolean isDirectTypeColumnChangesSupported(JdbcColumn ourCol,
1921: JdbcColumn dbCol) {
1922: return supportDirectColumnChange;
1923: }
1924:
1925: boolean isDropConstraintsForDropTableSupported() {
1926: return false;
1927: }
1928:
1929: }
|