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