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.BooleanConverter;
0015: import com.versant.core.jdbc.sql.conv.DateTimestampConverter;
0016: import com.versant.core.jdbc.sql.conv.NoMinCharConverter;
0017: import com.versant.core.jdbc.sql.diff.ColumnDiff;
0018: import com.versant.core.jdbc.sql.diff.ControlParams;
0019: import com.versant.core.jdbc.sql.diff.TableDiff;
0020: import com.versant.core.jdbc.sql.exp.SqlExp;
0021: import com.versant.core.util.CharBuf;
0022:
0023: import java.io.PrintWriter;
0024: import java.sql.*;
0025: import java.util.*;
0026: import java.util.Date;
0027:
0028: /**
0029: * A driver for Postgres.
0030: */
0031: public class PostgresSqlDriver extends SqlDriver {
0032: public static final String SQLPARAM_NUMERIC_CAST = "CAST(? as numeric)";
0033: public static final String SQLPARAM_REAL_CAST = "CAST(? as real)";
0034:
0035: public final static char[] CHAR_SQLPARAM_NUMERIC_CAST = SQLPARAM_NUMERIC_CAST
0036: .toCharArray();
0037: public final static char[] CHAR_SQLPARAM_REAL_CAST = SQLPARAM_REAL_CAST
0038: .toCharArray();
0039:
0040: private boolean dropTableRequiresCascade;
0041: private String version = null;
0042: private HashMap sqlTypeCache;
0043: private HashMap pgTypeCache;
0044:
0045: private static final String jdbc1Types[] = { "int2", "int4", "oid",
0046: "int8", "cash", "money", "numeric", "float4", "float8",
0047: "bpchar", "char", "char2", "char4", "char8", "char16",
0048: "varchar", "text", "name", "filename", "bytea", "bool",
0049: "date", "time", "abstime", "timestamp", "timestamptz" };
0050: private static final int jdbc1Typei[] = { 5, 4, 4, -5, 8, 8, 2, 7,
0051: 8, 1, 1, 1, 1, 1, 1, 12, 2005, 12, 12, 2004, -7, 91, 92,
0052: 93, 93, 93 };
0053:
0054: private static char[] FOR_UPDATE = " FOR UPDATE OF ".toCharArray();
0055:
0056: /**
0057: * Get the name of this driver.
0058: */
0059: public String getName() {
0060: return "postgres";
0061: }
0062:
0063: public boolean isCustomizeForServerRequired() {
0064: return true;
0065: }
0066:
0067: /**
0068: * Find out what version of Postgres con is for and adapt.
0069: */
0070: public void customizeForServer(Connection con) throws SQLException {
0071: String s = getVersion(con);
0072: int i = s.indexOf(' ') + 1;
0073: int j = s.indexOf('.');
0074: int k = j;
0075: for (;;) {
0076: char c = s.charAt(++k);
0077: if ((c < '0') || (c > '9'))
0078: break;
0079: }
0080: int major = Integer.parseInt(s.substring(i, j));
0081: int minor = Integer.parseInt(s.substring(j + 1, k));
0082: dropTableRequiresCascade = (major >= 7 && minor >= 3)
0083: || major >= 8;
0084: }
0085:
0086: private String parseVersion(String version) {
0087: int i = version.indexOf(' ') + 1;
0088: int j = version.indexOf('.');
0089: int k = j;
0090: for (;;) {
0091: char c = version.charAt(++k);
0092: if ((c < '0') || (c > '9'))
0093: break;
0094: }
0095: int major = Integer.parseInt(version.substring(i, j));
0096: int minor = Integer.parseInt(version.substring(j + 1, k));
0097: return major + "." + minor + ".";
0098:
0099: }
0100:
0101: private String getVersion(Connection con) throws SQLException {
0102: if (version != null) {
0103: return version;
0104: } else {
0105: Statement stat = null;
0106: ResultSet rs = null;
0107: try {
0108: stat = con.createStatement();
0109: rs = stat.executeQuery("SELECT version()");
0110: rs.next();
0111: String ver = rs.getString(1);
0112: con.commit();
0113: version = parseVersion(ver);
0114: return version;
0115: } finally {
0116: if (rs != null) {
0117: try {
0118: rs.close();
0119: } catch (SQLException e) {
0120: // ignore
0121: }
0122: }
0123: if (stat != null) {
0124: try {
0125: stat.close();
0126: } catch (SQLException e) {
0127: // ignore
0128: }
0129: }
0130: }
0131: }
0132: }
0133:
0134: /**
0135: * Get the default type mapping for the supplied JDBC type code from
0136: * java.sql.Types or null if the type is not supported. There is no
0137: * need to set the database or jdbcType on the mapping as this is done
0138: * after this call returns. Subclasses should override this and to
0139: * customize type mappings.
0140: */
0141: protected JdbcTypeMapping getTypeMapping(int jdbcType) {
0142: switch (jdbcType) {
0143: case Types.BIT:
0144: case Types.TINYINT:
0145: return new JdbcTypeMapping("SMALLINT", 0, 0,
0146: JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE, null);
0147: case Types.DOUBLE:
0148: return new JdbcTypeMapping("DOUBLE PRECISION", 0, 0,
0149: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE, null);
0150: case Types.CLOB:
0151: case Types.LONGVARCHAR:
0152: return new JdbcTypeMapping("TEXT", 0, 0,
0153: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE, null);
0154: case Types.VARBINARY:
0155: case Types.LONGVARBINARY:
0156: case Types.BLOB:
0157: return new JdbcTypeMapping("BYTEA", 0, 0,
0158: JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
0159: bytesConverterFactory);
0160: }
0161: return super .getTypeMapping(jdbcType);
0162: }
0163:
0164: /**
0165: * Get the default field mappings for this driver. These map java classes
0166: * to column properties. Subclasses should override this, call super() and
0167: * replace mappings as needed.
0168: */
0169: public HashMap getJavaTypeMappings() {
0170: HashMap ans = super .getJavaTypeMappings();
0171:
0172: BooleanConverter.Factory bcf = new BooleanConverter.Factory();
0173: ((JdbcJavaTypeMapping) ans.get(Boolean.TYPE))
0174: .setConverterFactory(bcf);
0175: ((JdbcJavaTypeMapping) ans.get(Boolean.class))
0176: .setConverterFactory(bcf);
0177:
0178: DateTimestampConverter.Factory dtcf = new DateTimestampConverter.Factory();
0179: ((JdbcJavaTypeMapping) ans.get(Date.class))
0180: .setConverterFactory(dtcf);
0181:
0182: NoMinCharConverter.Factory f = new NoMinCharConverter.Factory();
0183: add(ans, new JdbcJavaTypeMapping(Character.class, Types.CHAR,
0184: 1, 0, JdbcJavaTypeMapping.TRUE, f));
0185: add(ans, new JdbcJavaTypeMapping(Character.TYPE, Types.CHAR, 1,
0186: 0, JdbcJavaTypeMapping.FALSE, f));
0187:
0188: return ans;
0189: }
0190:
0191: public String getAliasPrepend() {
0192: return " as ";
0193: }
0194:
0195: /**
0196: * Does the JDBC driver support statement batching?
0197: */
0198: public boolean isInsertBatchingSupported() {
0199: return true;
0200: }
0201:
0202: /**
0203: * Does the JDBC driver support statement batching for updates?
0204: */
0205: public boolean isUpdateBatchingSupported() {
0206: return true;
0207: }
0208:
0209: /**
0210: * Does the JDBC driver support scrollable result sets?
0211: */
0212: public boolean isScrollableResultSetSupported() {
0213: return true;
0214: }
0215:
0216: public boolean isFetchSizeSupported() {
0217: return false;
0218: }
0219:
0220: /**
0221: * Does this driver use the ANSI join syntax (i.e. the join clauses appear
0222: * in the from list e.g. postgres)?
0223: */
0224: public boolean isAnsiJoinSyntax() {
0225: return true;
0226: }
0227:
0228: /**
0229: * Is null a valid value for a column with a foreign key constraint?
0230: */
0231: public boolean isNullForeignKeyOk() {
0232: return true;
0233: }
0234:
0235: public boolean isAutoIncSupported() {
0236: return true;
0237: }
0238:
0239: public Object getAutoIncColumnValue(JdbcTable classTable,
0240: Connection con, Statement stat) throws SQLException {
0241: String sql = "SELECT currval('" + classTable.name + "_"
0242: + classTable.pk[0].name + "_seq')";
0243: Statement s = null;
0244: ResultSet rs = null;
0245: try {
0246: s = con.createStatement();
0247: rs = s.executeQuery(sql.toString());
0248: rs.next();
0249: if (classTable.pk[0].jdbcType == Types.BIGINT) {
0250: return new Long(rs.getLong(1));
0251: } else {
0252: return new Integer(rs.getInt(1));
0253: }
0254: } finally {
0255: if (rs != null) {
0256: try {
0257: rs.close();
0258: } catch (SQLException e) {
0259: // ignore
0260: }
0261: }
0262: if (s != null) {
0263: try {
0264: s.close();
0265: } catch (SQLException e) {
0266: // ignore
0267: }
0268: }
0269: }
0270: }
0271:
0272: /**
0273: * Create a default name generator instance for JdbcStore's using this
0274: * driver.
0275: */
0276: public JdbcNameGenerator createJdbcNameGenerator() {
0277: DefaultJdbcNameGenerator n = createDefaultJdbcNameGenerator();
0278: n.setMaxColumnNameLength(31);
0279: n.setMaxTableNameLength(31);
0280: n.setMaxConstraintNameLength(31);
0281: n.setMaxIndexNameLength(31);
0282: return n;
0283: }
0284:
0285: /**
0286: * Append the part of a create table statement for a column.
0287: */
0288: protected void appendCreateColumn(JdbcTable t, JdbcColumn c,
0289: CharBuf s, boolean comments) {
0290: if (c.autoinc) {
0291: int si = s.size();
0292: s.append(c.name);
0293: if (c.jdbcType == Types.BIGINT) {
0294: s.append(" SERIAL8");
0295: } else {
0296: s.append(" SERIAL");
0297: }
0298: s.append(',');
0299: if (comments && c.comment != null) {
0300: s.append(' ');
0301: si += COMMENT_COL;
0302: for (; s.size() < si; s.append(' '))
0303: ;
0304: s.append(comment(c.comment));
0305: }
0306: } else {
0307: super .appendCreateColumn(t, c, s, comments);
0308: }
0309: }
0310:
0311: /**
0312: * Append the allow nulls part of the definition for a column in a
0313: * create table statement.
0314: */
0315: protected void appendCreateColumnNulls(JdbcTable t, JdbcColumn c,
0316: CharBuf s) {
0317: if (!c.nulls)
0318: s.append(" NOT NULL");
0319: }
0320:
0321: /**
0322: * Add the primary key constraint part of a create table statement to s.
0323: */
0324: protected void appendPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
0325: s.append("CONSTRAINT ");
0326: s.append(t.pkConstraintName);
0327: s.append(" PRIMARY KEY (");
0328: appendColumnNameList(t.pk, s);
0329: s.append(')');
0330: }
0331:
0332: /**
0333: * Append an 'add constraint' statement for c.
0334: */
0335: protected void appendRefConstraint(CharBuf s, JdbcConstraint c) {
0336: s.append("ALTER TABLE ");
0337: s.append(c.src.name);
0338: s.append(" ADD CONSTRAINT ");
0339: s.append(c.name);
0340: s.append(" FOREIGN KEY (");
0341: appendColumnNameList(c.srcCols, s);
0342: s.append(") REFERENCES ");
0343: s.append(c.dest.name);
0344: s.append('(');
0345: appendColumnNameList(c.dest.pk, s);
0346: s.append(")");
0347: }
0348:
0349: /**
0350: * Write an SQL statement to a script with appropriate separator.
0351: */
0352: protected void print(PrintWriter out, String sql) {
0353: out.print(sql);
0354: out.println(";");
0355: out.println();
0356: }
0357:
0358: /**
0359: * Append the from list entry for a table.
0360: */
0361: public void appendSqlFrom(JdbcTable table, String alias, CharBuf s) {
0362: s.append(table.name);
0363: if (alias != null) {
0364: s.append(" AS ");
0365: s.append(alias);
0366: }
0367: }
0368:
0369: /**
0370: * Append the from list entry for a table that is the right hand table
0371: * in a join i.e. it is being joined to.
0372: * @param exp This is the expression that joins the tables
0373: * @param outer If true then this is an outer join
0374: */
0375: public void appendSqlFromJoin(JdbcTable table, String alias,
0376: SqlExp exp, boolean outer, CharBuf s) {
0377: if (exp == null) {
0378: s.append(" CROSS JOIN ");
0379: } else if (outer) {
0380: s.append(" LEFT JOIN ");
0381: } else {
0382: s.append(" JOIN ");
0383: }
0384: s.append(table.name);
0385: if (alias != null) {
0386: s.append(" AS ");
0387: s.append(alias);
0388: }
0389: if (exp != null) {
0390: s.append(" ON (");
0391: exp.appendSQL(this , s, null);
0392: s.append(')');
0393: }
0394: }
0395:
0396: /**
0397: * Append a replacable parameter part of a where clause for the column.
0398: * This gives the driver a chance to embed type conversions and so on
0399: * for types not handled well by the JDBC driver (e.g. BigDecimals for
0400: * the postgres JDBC driver).
0401: */
0402: public void appendWhereParam(CharBuf s, JdbcColumn c) {
0403: switch (c.jdbcType) {
0404: case Types.NUMERIC:
0405: s.append("CAST(? as numeric)");
0406: break;
0407: case Types.REAL:
0408: s.append("CAST(? as real)");
0409: break;
0410: default:
0411: super .appendWhereParam(s, c);
0412: }
0413: ;
0414: }
0415:
0416: /**
0417: * Get a String for a replacable parameter. This gives the driver a
0418: * chance to embed type conversions and so on for types not handled well
0419: * by the JDBC driver (e.g. BigDecimals and the postgres JDBC driver).
0420: */
0421: public String getSqlParamString(int jdbcType) {
0422: switch (jdbcType) {
0423: case Types.NUMERIC:
0424: return SQLPARAM_NUMERIC_CAST;
0425: case Types.REAL:
0426: return SQLPARAM_REAL_CAST;
0427: default:
0428: return "?";
0429: }
0430: }
0431:
0432: public char[] getSqlParamStringChars(int jdbcType) {
0433: switch (jdbcType) {
0434: case Types.NUMERIC:
0435: return CHAR_SQLPARAM_NUMERIC_CAST;
0436: case Types.REAL:
0437: return CHAR_SQLPARAM_REAL_CAST;
0438: default:
0439: return DEFAULT_PARAM_CHARS;
0440: }
0441: }
0442:
0443: /**
0444: * Get default SQL to test a connection or null if none available. This
0445: * must be a query that returns at least one row.
0446: */
0447: public String getConnectionValidateSQL() {
0448: return "SELECT datname FROM pg_database";
0449: }
0450:
0451: /**
0452: * Drop the table and all its constraints etc. This must remove
0453: * constraints to this table from other tables so it can be dropped.
0454: */
0455: public void dropTable(Connection con, String table, Statement stat)
0456: throws SQLException {
0457: CharBuf s = new CharBuf(64);
0458: s.append("DROP TABLE ");
0459: s.append(table);
0460: if (dropTableRequiresCascade)
0461: s.append(" CASCADE");
0462: stat.execute(s.toString());
0463: }
0464:
0465: /**
0466: * Get whatever needs to be appended to a SELECT statement to lock the
0467: * rows if this makes sense for the database. This must have a leading
0468: * space if not empty.
0469: */
0470: public char[] getSelectForUpdate() {
0471: return FOR_UPDATE;
0472: }
0473:
0474: /**
0475: * Does 'SELECT FOR UPDATE' require the main table of the query to be
0476: * appended (ala postgres)?
0477: */
0478: public boolean isSelectForUpdateAppendTable() {
0479: return true;
0480: }
0481:
0482: /**
0483: * Can 'SELECT FOR UPDATE' be used with a DISTINCT?
0484: */
0485: public boolean isSelectForUpdateWithDistinctOk() {
0486: return false;
0487: }
0488:
0489: protected boolean isValidSchemaTable(String tableName) {
0490: String[] sysNames = new String[] { "pga_forms", "pga_queries",
0491: "pga_reports", "pga_schema", "pga_scripts" };
0492:
0493: for (int i = 0; i < sysNames.length; i++) {
0494: if (sysNames[i].equals(tableName)) {
0495: return false;
0496: }
0497: }
0498: return true;
0499: }
0500:
0501: /**
0502: * Get the JdbcTables from the database for the given database con.
0503: * @param con
0504: * @return HashMap of tablename.toLowerCase() as key and JdbcTable as value
0505: * @throws SQLException on DB errors
0506: */
0507: public HashMap getDBSchema(Connection con, ControlParams params)
0508: throws SQLException {
0509: HashMap jdbcTableMap = new HashMap(); // main map of jdbc tables
0510: // now we do columns
0511: params.setColumnsOnly(false);
0512: String tableName = null;
0513: initTypeCache(con);
0514: String columnSql = null;
0515: if (haveMinimumServerVersion("7.3", con)) {
0516: columnSql = "SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description "
0517: + " FROM pg_catalog.pg_namespace n "
0518: + " JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) "
0519: + " JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) "
0520: + " LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) "
0521: + " LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid) "
0522: + " LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND dc.relname='pg_class') "
0523: + " LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog') "
0524: + " WHERE a.attnum > 0 "
0525: + " AND NOT a.attisdropped "
0526: + " AND n.nspname LIKE 'public'";
0527:
0528: } else if (haveMinimumServerVersion("7.1", con)) {
0529: columnSql = "SELECT NULL::text AS nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description "
0530: + " FROM pg_class c "
0531: + " JOIN pg_attribute a ON (a.attrelid=c.oid) "
0532: + " LEFT JOIN pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) "
0533: + " LEFT JOIN pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid) "
0534: + " LEFT JOIN pg_class dc ON (dc.oid=dsc.classoid AND dc.relname='pg_class') "
0535: + " WHERE a.attnum > 0 "
0536: + " AND c.relname NOT LIKE('pg_%')";
0537: } else {
0538: columnSql = "SELECT NULL::text AS nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,NULL AS adsrc,NULL AS description "
0539: + " FROM pg_class c, "
0540: + " pg_attribute a "
0541: + " WHERE a.attrelid=c.oid AND a.attnum > 0 ";
0542: }
0543: columnSql = columnSql + " ORDER BY nspname,relname,attnum ";
0544:
0545: Statement statCol = con.createStatement();
0546: ResultSet rsColumn = statCol.executeQuery(columnSql);
0547: ArrayList columns = null;
0548:
0549: while (rsColumn.next()) {
0550:
0551: String temptableName = rsColumn.getString(2);
0552:
0553: if (!isValidSchemaTable(temptableName)) {
0554: continue;
0555: }
0556:
0557: if (tableName == null) { // this is the first one
0558: tableName = temptableName;
0559: columns = new ArrayList();
0560: JdbcTable jdbcTable = new JdbcTable();
0561: jdbcTable.name = tableName;
0562: jdbcTableMap.put(tableName, jdbcTable);
0563: }
0564:
0565: if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
0566: JdbcColumn[] jdbcColumns = new JdbcColumn[columns
0567: .size()];
0568: columns.toArray(jdbcColumns);
0569: JdbcTable jdbcTable0 = (JdbcTable) jdbcTableMap
0570: .get(tableName);
0571: jdbcTable0.cols = jdbcColumns;
0572:
0573: tableName = temptableName;
0574: columns.clear();
0575: JdbcTable jdbcTable1 = new JdbcTable();
0576: jdbcTable1.name = tableName;
0577: jdbcTableMap.put(tableName, jdbcTable1);
0578: }
0579:
0580: JdbcColumn col = new JdbcColumn();
0581: int typeOid = rsColumn.getInt("atttypid");
0582: col.name = rsColumn.getString(3);
0583: String pgType = getPGType(typeOid);
0584: col.sqlType = pgType;
0585: col.jdbcType = getSQLType(typeOid);
0586: col.scale = 0;
0587: if (pgType.equals("bpchar") || pgType.equals("varchar")) {
0588: int atttypmod = rsColumn.getInt("atttypmod");
0589: col.length = atttypmod == -1 ? 0 : atttypmod - 4;
0590: } else if (pgType.equals("numeric")
0591: || pgType.equals("decimal")) {
0592: int attypmod = rsColumn.getInt("atttypmod") - 4; // was index 8
0593: col.length = attypmod >> 16 & 65535;
0594: col.scale = attypmod & 65535;
0595: } else if (pgType.equals("bit") || pgType.equals("varbit")) {
0596: col.length = rsColumn.getInt("atttypmod");
0597: } else {
0598: col.length = rsColumn.getInt("attlen");
0599: }
0600: col.nulls = !rsColumn.getBoolean("attnotnull");
0601:
0602: switch (col.jdbcType) {
0603: case java.sql.Types.BIT:
0604: case java.sql.Types.TINYINT:
0605: case java.sql.Types.SMALLINT:
0606: case java.sql.Types.BIGINT:
0607: case java.sql.Types.INTEGER:
0608: case java.sql.Types.DATE:
0609: case java.sql.Types.TIME:
0610: case java.sql.Types.TIMESTAMP:
0611: col.length = 0;
0612: col.scale = 0;
0613: default:
0614: }
0615:
0616: columns.add(col);
0617: }
0618: // we fin last table
0619: if (columns != null) {
0620: JdbcColumn[] jdbcColumns = new JdbcColumn[columns.size()];
0621: columns.toArray(jdbcColumns);
0622: JdbcTable colJdbcTable = (JdbcTable) jdbcTableMap
0623: .get(tableName);
0624: if (colJdbcTable != null) {
0625: colJdbcTable.cols = jdbcColumns;
0626: }
0627: columns.clear();
0628: }
0629: tableName = null;
0630:
0631: // clean up
0632: if (rsColumn != null) {
0633: try {
0634: rsColumn.close();
0635: } catch (SQLException e) {
0636: }
0637: }
0638: if (statCol != null) {
0639: try {
0640: statCol.close();
0641: } catch (SQLException e) {
0642: }
0643: }
0644: if (!params.checkColumnsOnly()) {
0645: if (params.isCheckPK()) {
0646: // now we do primaryKeys
0647: HashMap pkMap = null;
0648:
0649: String where = "AND ct.relname NOT LIKE('pg_%')";
0650: String from;
0651: if (haveMinimumServerVersion("7.3", con)) {
0652: from = " FROM pg_catalog.pg_namespace n, "
0653: + "pg_catalog.pg_class ct, "
0654: + "pg_catalog.pg_class ci, "
0655: + "pg_catalog.pg_attribute a, "
0656: + "pg_catalog.pg_index i ";
0657: where = " AND ct.relnamespace = n.oid "
0658: + " AND n.nspname = 'public' ";
0659: } else {
0660: from = " FROM pg_class ct, "
0661: + " pg_class ci, "
0662: + " pg_attribute a, "
0663: + " pg_index i ";
0664: }
0665: String pkSql = " SELECT ct.relname AS TABLE_NAME, "
0666: + " a.attname AS COLUMN_NAME, "
0667: + " a.attnum AS KEY_SEQ, "
0668: + " ci.relname AS PK_NAME " + from
0669: + " WHERE ct.oid=i.indrelid "
0670: + " AND ci.oid=i.indexrelid "
0671: + " AND a.attrelid=ci.oid "
0672: + " AND i.indisprimary " + where
0673: + " ORDER BY table_name, pk_name, key_seq";
0674:
0675: Statement statPK = con.createStatement();
0676: ResultSet rsPKs = statPK.executeQuery(pkSql);
0677: int pkCount = 0;
0678: String pkName = null;
0679: while (rsPKs.next()) {
0680: String temptableName = rsPKs.getString(1);
0681:
0682: if (!jdbcTableMap.containsKey(temptableName)) {
0683: continue;
0684: }
0685:
0686: if (tableName == null) { // this is the first one
0687: tableName = temptableName;
0688: pkMap = new HashMap();
0689: }
0690:
0691: if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
0692: JdbcColumn[] pkColumns = new JdbcColumn[pkCount];
0693: int indexOfPKCount = 0;
0694: JdbcTable jdbcTable = (JdbcTable) jdbcTableMap
0695: .get(tableName);
0696: for (int i = 0; i < jdbcTable.cols.length; i++) {
0697: JdbcColumn jdbcColumn = jdbcTable.cols[i];
0698: if (pkMap.containsKey(jdbcColumn.name)) {
0699: pkColumns[indexOfPKCount] = jdbcColumn;
0700: jdbcColumn.pk = true;
0701: indexOfPKCount++;
0702: }
0703: }
0704: jdbcTable.pk = pkColumns;
0705: jdbcTable.pkConstraintName = pkName;
0706:
0707: // PK's become tables, remove them
0708: jdbcTableMap.remove(pkName);
0709:
0710: tableName = temptableName;
0711: pkMap.clear();
0712: pkCount = 0;
0713: }
0714: pkCount++;
0715: pkMap.put(rsPKs.getString(2), null);
0716: pkName = rsPKs.getString(4);
0717: }
0718: JdbcColumn[] pkColumns = new JdbcColumn[pkCount];
0719: int indexOfPKCount = 0;
0720: JdbcTable pkJdbcTable = (JdbcTable) jdbcTableMap
0721: .get(tableName);
0722: if (pkJdbcTable != null) {
0723: for (int i = 0; i < pkJdbcTable.cols.length; i++) {
0724: JdbcColumn jdbcColumn = pkJdbcTable.cols[i];
0725: if (pkMap.containsKey(jdbcColumn.name)) {
0726: pkColumns[indexOfPKCount] = jdbcColumn;
0727: jdbcColumn.pk = true;
0728: indexOfPKCount++;
0729: }
0730: }
0731: pkJdbcTable.pk = pkColumns;
0732: pkJdbcTable.pkConstraintName = pkName;
0733:
0734: // PK's become tables, remove them
0735: jdbcTableMap.remove(pkName);
0736: }
0737: tableName = null;
0738: // clean up
0739: if (rsPKs != null) {
0740: try {
0741: rsPKs.close();
0742: } catch (SQLException e) {
0743: }
0744: }
0745: if (statPK != null) {
0746: try {
0747: statPK.close();
0748: } catch (SQLException e) {
0749: }
0750: }
0751: }
0752: if (params.isCheckIndex()) {
0753: // now we do index
0754: String where = "AND ct.relname NOT LIKE('pg_%') ";
0755: String from;
0756: if (haveMinimumServerVersion("7.3", con)) {
0757: from = " FROM pg_catalog.pg_namespace n, \n"
0758: + " pg_catalog.pg_class ct, \n"
0759: + " pg_catalog.pg_class ci, \n"
0760: + " pg_catalog.pg_index i, \n"
0761: + " pg_catalog.pg_attribute a, \n"
0762: + " pg_catalog.pg_am am \n";
0763: where = " AND n.oid = ct.relnamespace \n"
0764: + " AND n.nspname = 'public' \n";
0765: } else {
0766: from = " FROM pg_class ct, \n"
0767: + " pg_class ci, \n"
0768: + " pg_index i, \n"
0769: + " pg_attribute a, \n"
0770: + " pg_am am \n";
0771: }
0772: String indexSql = "SELECT ct.relname AS TABLE_NAME, \n"
0773: + " a.attname AS COLUMN_NAME, \n"
0774: + " ci.relname AS INDEX_NAME, \n"
0775: + " NOT i.indisunique AS NON_UNIQUE, \n"
0776: + " CASE i.indisclustered \n"
0777: + " WHEN true THEN "
0778: + 1
0779: + "\n"
0780: + " ELSE CASE am.amname \n"
0781: + " WHEN 'hash' THEN "
0782: + 2
0783: + "\n"
0784: + " ELSE "
0785: + 3
0786: + "\n"
0787: + " END \n"
0788: + " END AS TYPE, \n"
0789: + " a.attnum AS ORDINAL_POSITION \n"
0790: + from
0791: + " WHERE ct.oid = i.indrelid \n"
0792: + " AND ci.oid = i.indexrelid \n"
0793: + " AND a.attrelid = ci.oid \n"
0794: + " AND ci.relam=am.oid \n"
0795: + where
0796: + " ORDER BY TABLE_NAME, INDEX_NAME, ORDINAL_POSITION \n";
0797: Statement statIndex = con.createStatement();
0798: // System.out.println("\n\n\n\n");
0799: // System.out.println("--"+getVersion(con));
0800: // System.out.println(indexSql);
0801: // System.out.println("\n\n\n\n");
0802: ResultSet rsIndex = statIndex.executeQuery(indexSql);
0803:
0804: HashMap indexNameMap = null;
0805: ArrayList indexes = null;
0806: while (rsIndex.next()) {
0807: String temptableName = rsIndex.getString(1);
0808: if (tableName == null) { // this is the first one
0809: tableName = temptableName;
0810: indexNameMap = new HashMap();
0811: indexes = new ArrayList();
0812: }
0813:
0814: String indexName = rsIndex.getString(3);
0815: JdbcTable tempJdbcTable = (JdbcTable) jdbcTableMap
0816: .get(temptableName);
0817:
0818: if (indexName != null
0819: && !indexName
0820: .equals(tempJdbcTable.pkConstraintName)) {
0821: if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
0822: JdbcTable jdbcTable = (JdbcTable) jdbcTableMap
0823: .get(tableName);
0824: JdbcIndex[] jdbcIndexes = new JdbcIndex[indexes
0825: .size()];
0826: indexes.toArray(jdbcIndexes);
0827: jdbcTable.indexes = jdbcIndexes;
0828:
0829: tableName = temptableName;
0830: indexes.clear();
0831: indexNameMap.clear();
0832:
0833: }
0834: JdbcTable jdbcTable = (JdbcTable) jdbcTableMap
0835: .get(tableName);
0836: if (indexNameMap.containsKey(indexName)) {
0837: JdbcIndex index = null;
0838: for (Iterator iter = indexes.iterator(); iter
0839: .hasNext();) {
0840: JdbcIndex jdbcIndex = (JdbcIndex) iter
0841: .next();
0842: if (jdbcIndex.name.equals(indexName)) {
0843: index = jdbcIndex;
0844: }
0845: }
0846:
0847: JdbcColumn[] tempIndexColumns = index.cols;
0848: JdbcColumn[] indexColumns = new JdbcColumn[tempIndexColumns.length + 1];
0849: System.arraycopy(tempIndexColumns, 0,
0850: indexColumns, 0,
0851: tempIndexColumns.length);
0852: String colName = rsIndex.getString(2);
0853: for (int i = 0; i < jdbcTable.cols.length; i++) {
0854: JdbcColumn jdbcColumn = jdbcTable.cols[i];
0855: if (colName.equals(jdbcColumn.name)) {
0856: indexColumns[tempIndexColumns.length] = jdbcColumn;
0857: jdbcColumn.partOfIndex = true;
0858: }
0859: }
0860: index.setCols(indexColumns);
0861: } else {
0862: indexNameMap.put(indexName, null);
0863: JdbcIndex index = new JdbcIndex();
0864: index.name = indexName;
0865: // index's become tables, remove them
0866: jdbcTableMap.remove(indexName);
0867: index.unique = !rsIndex.getBoolean(4);
0868: short indexType = rsIndex.getShort(5);
0869: switch (indexType) {
0870: case DatabaseMetaData.tableIndexClustered:
0871: index.clustered = true;
0872: break;
0873: }
0874: String colName = rsIndex.getString(2);
0875: JdbcColumn[] indexColumns = new JdbcColumn[1];
0876: for (int i = 0; i < jdbcTable.cols.length; i++) {
0877: JdbcColumn jdbcColumn = jdbcTable.cols[i];
0878: if (colName.equals(jdbcColumn.name)) {
0879: indexColumns[0] = jdbcColumn;
0880: jdbcColumn.partOfIndex = true;
0881: }
0882: }
0883: index.setCols(indexColumns);
0884: indexes.add(index);
0885: }
0886: }
0887: }
0888: JdbcTable indexJdbcTable = (JdbcTable) jdbcTableMap
0889: .get(tableName);
0890: if (indexJdbcTable != null) {
0891: JdbcIndex[] jdbcIndexes = new JdbcIndex[indexes
0892: .size()];
0893: indexes.toArray(jdbcIndexes);
0894: indexJdbcTable.indexes = jdbcIndexes;
0895: indexes.clear();
0896: indexNameMap.clear();
0897: }
0898: tableName = null;
0899: // clean up
0900: if (rsIndex != null) {
0901: try {
0902: rsIndex.close();
0903: } catch (SQLException e) {
0904: }
0905: }
0906: if (statIndex != null) {
0907: try {
0908: statIndex.close();
0909: } catch (SQLException e) {
0910: }
0911: }
0912: }
0913: if (params.isCheckConstraint()) {
0914: // now we do forign keys
0915: String where = "";
0916:
0917: String from;
0918: if (haveMinimumServerVersion("7.3", con)) {
0919:
0920: from = " FROM pg_catalog.pg_namespace n1 "
0921: + " JOIN pg_catalog.pg_class c1 ON (c1.relnamespace = n1.oid) "
0922: + " JOIN pg_catalog.pg_index i ON (c1.oid = i.indrelid) "
0923: + " JOIN pg_catalog.pg_class ic ON (i.indexrelid = ic.oid) "
0924: + " JOIN pg_catalog.pg_attribute a ON (ic.oid = a.attrelid), "
0925: + " pg_catalog.pg_namespace n2 JOIN pg_catalog.pg_class c2 ON (c2.relnamespace = n2.oid), "
0926: + " pg_catalog.pg_trigger t1 JOIN pg_catalog.pg_proc p1 ON (t1.tgfoid = p1.oid), "
0927: + " pg_catalog.pg_trigger t2 JOIN pg_catalog.pg_proc p2 ON (t2.tgfoid = p2.oid) ";
0928: where = " AND n2.nspname = 'public' ";
0929:
0930: } else {
0931:
0932: from = " FROM pg_class c1 "
0933: + " JOIN pg_index i ON (c1.oid = i.indrelid) "
0934: + " JOIN pg_class ic ON (i.indexrelid = ic.oid) "
0935: + " JOIN pg_attribute a ON (ic.oid = a.attrelid), "
0936: + " pg_class c2, "
0937: + " pg_trigger t1 JOIN pg_proc p1 ON (t1.tgfoid = p1.oid), "
0938: + " pg_trigger t2 JOIN pg_proc p2 ON (t2.tgfoid = p2.oid) ";
0939: }
0940: String fkSql = "SELECT c1.relname as PKTABLE_NAME, "
0941: + //PKTABLE_NAME
0942: " c2.relname as FKTABLE_NAME, "
0943: + //FKTABLE_NAME
0944: " t1.tgconstrname, "
0945: + " a.attnum as keyseq, "
0946: + " ic.relname as fkeyname, "
0947: + " t1.tgdeferrable, "
0948: + " t1.tginitdeferred, "
0949: + " t1.tgnargs,"
0950: + " t1.tgargs "
0951: + from
0952: + "WHERE (t1.tgrelid=c1.oid "
0953: + " AND t1.tgisconstraint "
0954: + " AND t1.tgconstrrelid=c2.oid "
0955: + " AND p1.proname LIKE 'RI\\\\_FKey\\\\_%\\\\_upd') "
0956: + " AND (t2.tgrelid=c1.oid "
0957: + " AND t2.tgisconstraint "
0958: + " AND t2.tgconstrrelid=c2.oid "
0959: + " AND p2.proname LIKE 'RI\\\\_FKey\\\\_%\\\\_del') "
0960: + " AND i.indisprimary "
0961: + where
0962: + "ORDER BY FKTABLE_NAME , tgconstrname, keyseq";
0963: Statement statFK = con.createStatement();
0964: ResultSet rsFKs = statFK.executeQuery(fkSql);
0965:
0966: HashMap constraintNameMap = new HashMap();
0967: HashMap doneMap = new HashMap();
0968:
0969: while (rsFKs.next()) {
0970: String targs = rsFKs.getString(9);
0971: StringTokenizer st = new StringTokenizer(targs,
0972: "\\000");
0973:
0974: String constName = null;
0975: String srcTableName = null;
0976: String destTableName = null;
0977: ArrayList srcColNames = new ArrayList();
0978:
0979: if (st.hasMoreTokens()) { //0
0980: constName = st.nextToken();
0981: }
0982: if (st.hasMoreTokens()) { //1
0983: srcTableName = st.nextToken();
0984: }
0985: if (st.hasMoreTokens()) { //2
0986: destTableName = st.nextToken();
0987: st.nextToken();//3 UNSPECIFIED
0988: }
0989: while (st.hasMoreTokens()) {
0990: srcColNames.add(st.nextToken());
0991: if (st.hasMoreTokens()) {
0992: st.nextToken();
0993: }
0994: }
0995: JdbcTable srcJdbcTable = (JdbcTable) jdbcTableMap
0996: .get(srcTableName);
0997:
0998: String doneName = srcTableName + constName;
0999: if (srcJdbcTable == null) {
1000: doneMap.put(doneName, null);
1001: continue;
1002: } else if (doneMap.containsKey(doneName)) {
1003: continue;
1004: } else {
1005: doneMap.put(doneName, null);
1006: }
1007:
1008: JdbcTable destJdbcTable = (JdbcTable) jdbcTableMap
1009: .get(destTableName);
1010: JdbcConstraint jdbcConstraint = new JdbcConstraint();
1011: jdbcConstraint.name = constName;
1012: jdbcConstraint.src = srcJdbcTable;
1013: jdbcConstraint.dest = destJdbcTable;
1014: JdbcColumn[] constraintColumns = new JdbcColumn[srcColNames
1015: .size()];
1016: int j = 0;
1017: for (Iterator iter = srcColNames.iterator(); iter
1018: .hasNext(); j++) {
1019: String colName = (String) iter.next();
1020: for (int i = 0; i < srcJdbcTable.cols.length; i++) {
1021: JdbcColumn jdbcColumn = srcJdbcTable.cols[i];
1022: if (colName.equals(jdbcColumn.name)) {
1023: constraintColumns[j] = jdbcColumn;
1024: jdbcColumn.foreignKey = true;
1025: }
1026: }
1027: }
1028: jdbcConstraint.srcCols = constraintColumns;
1029: if (constraintNameMap.containsKey(srcJdbcTable)) {
1030: ArrayList list = (ArrayList) constraintNameMap
1031: .get(srcJdbcTable);
1032: list.add(jdbcConstraint);
1033: } else {
1034: ArrayList list = new ArrayList();
1035: list.add(jdbcConstraint);
1036: constraintNameMap.put(srcJdbcTable, list);
1037: }
1038: }
1039: for (Iterator iter = constraintNameMap.keySet()
1040: .iterator(); iter.hasNext();) {
1041: JdbcTable jdbcTable = (JdbcTable) iter.next();
1042: if (jdbcTable != null) {
1043: ArrayList list = (ArrayList) constraintNameMap
1044: .get(jdbcTable);
1045: JdbcConstraint[] jdbcConstraints = new JdbcConstraint[list
1046: .size()];
1047: list.toArray(jdbcConstraints);
1048: jdbcTable.constraints = jdbcConstraints;
1049: }
1050: }
1051:
1052: if (rsFKs != null) {
1053: try {
1054: rsFKs.close();
1055: } catch (SQLException e) {
1056: }
1057: }
1058: if (statFK != null) {
1059: try {
1060: statFK.close();
1061: } catch (SQLException e) {
1062: }
1063: }
1064: }
1065: }
1066:
1067: HashMap returnMap = new HashMap();
1068: Collection col = jdbcTableMap.values();
1069: for (Iterator iterator = col.iterator(); iterator.hasNext();) {
1070: JdbcTable table = (JdbcTable) iterator.next();
1071: returnMap.put(table.name.toLowerCase(), table);
1072: }
1073: fixAllNames(returnMap);
1074: return returnMap;
1075: }
1076:
1077: public boolean haveMinimumServerVersion(String ver, Connection con)
1078: throws SQLException {
1079: return getVersion(con).compareTo(ver) >= 0;
1080: }
1081:
1082: public int getSQLType(String pgTypeName) {
1083: int sqlType = 1111;
1084: for (int i = 0; i < jdbc1Types.length; i++) {
1085: if (!pgTypeName.equals(jdbc1Types[i])) {
1086: continue;
1087: }
1088: sqlType = jdbc1Typei[i];
1089: break;
1090: }
1091:
1092: return sqlType;
1093: }
1094:
1095: /**
1096: * remember to init the type cash first.
1097: * @param oid
1098: * @return
1099: * @throws SQLException
1100: */
1101: public String getPGType(int oid) throws SQLException {
1102: String pgType = (String) pgTypeCache.get(new Integer(oid));
1103: return pgType;
1104: }
1105:
1106: /**
1107: * remember to init the type cash first.
1108: * @param oid
1109: * @return
1110: * @throws SQLException
1111: */
1112: public int getSQLType(int oid) throws SQLException {
1113: Integer sqlType = (Integer) sqlTypeCache.get(new Integer(oid));
1114: return sqlType.intValue();
1115: }
1116:
1117: private void initTypeCache(Connection con) throws SQLException {
1118: sqlTypeCache = new HashMap();
1119: pgTypeCache = new HashMap();
1120:
1121: String sql;
1122: if (haveMinimumServerVersion("7.3", con)) {
1123: sql = "SELECT typname,oid FROM pg_catalog.pg_type ";
1124: } else {
1125: sql = "SELECT typname,oid FROM pg_type ";
1126: }
1127: Statement stat = con.createStatement();
1128: ResultSet rs = stat.executeQuery(sql);
1129: String pgType;
1130: while (rs.next()) {
1131: pgType = rs.getString(1);
1132: Integer iOid = new Integer(rs.getInt(2));
1133: Integer sqlType = new Integer(getSQLType(pgType));
1134: sqlTypeCache.put(iOid, sqlType);
1135: pgTypeCache.put(iOid, pgType);
1136: }
1137: // do last type
1138: pgType = "opaque";
1139: Integer iOid = new Integer(0);
1140: Integer sqlType = new Integer(getSQLType(pgType));
1141: sqlTypeCache.put(iOid, sqlType);
1142: pgTypeCache.put(iOid, pgType);
1143: if (rs != null) {
1144: try {
1145: rs.close();
1146: } catch (SQLException e) {
1147: }
1148: }
1149: if (stat != null) {
1150: try {
1151: stat.close();
1152: } catch (SQLException e) {
1153: }
1154: }
1155: }
1156:
1157: /**
1158: * Append a column that needs to be added.
1159: */
1160: protected void appendAddNewColumn(JdbcTable t, JdbcColumn c,
1161: CharBuf s, boolean comments) {
1162: if (comments && isCommentSupported() && c.comment != null) {
1163: s.append(comment("add column for field " + c.comment));
1164: }
1165:
1166: s.append("\n");
1167: if (isAddSequenceColumn(c)) {
1168: addSequenceColumn(t, c, s, comments);
1169: } else {
1170: s.append("ALTER TABLE ");
1171: s.append(t.name);
1172: s.append(" ADD ");
1173: s.append(c.name);
1174: s.append(' ');
1175: appendColumnType(c, s);
1176: s.append(getRunCommand());
1177: if (!c.nulls) {
1178: s.append("UPDATE ");
1179: s.append(t.name);
1180: s.append(" SET ");
1181: s.append(c.name);
1182: s.append(" = ");
1183: s.append(getDefaultForType(c));
1184: s.append(getRunCommand());
1185:
1186: s.append("ALTER TABLE ");
1187: s.append(t.name);
1188: s.append(" ALTER ");
1189: s.append(c.name);
1190: s.append(" SET NOT NULL");
1191: s.append(getRunCommand());
1192: }
1193: }
1194: }
1195:
1196: /**
1197: * Append a column that needs to be added.
1198: */
1199: protected void appendModifyColumn(TableDiff tableDiff,
1200: ColumnDiff diff, CharBuf s, boolean comments) {
1201: JdbcTable t = tableDiff.getOurTable();
1202: JdbcColumn c = diff.getOurCol();
1203: boolean length = diff.isLenghtDiff();
1204: boolean scale = diff.isScaleDiff();
1205: boolean nulls = diff.isNullDiff();
1206: boolean type = diff.isTypeDiff();
1207: if (comments && isCommentSupported() && c.comment != null) {
1208: s.append(comment("modify column for field " + c.comment));
1209: }
1210: if (comments && isCommentSupported() && c.comment == null) {
1211: s.append(comment("modify column " + c.name));
1212: }
1213:
1214: s.append("\n");
1215: if (length || scale || type) {
1216: String tempcolumn = getTempColumnName(t);
1217: s.append("ALTER TABLE ");
1218: s.append(t.name);
1219: s.append(" RENAME ");
1220: s.append(c.name);
1221: s.append(" TO ");
1222: s.append(tempcolumn);
1223: s.append(getRunCommand());
1224:
1225: s.append("ALTER TABLE ");
1226: s.append(t.name);
1227: s.append(" ADD ");
1228: s.append(c.name);
1229: s.append(' ');
1230: appendColumnType(c, s);
1231: s.append(getRunCommand());
1232:
1233: s.append("UPDATE ");
1234: s.append(t.name);
1235: s.append(" SET ");
1236: s.append(c.name);
1237: s.append(" = ");
1238: s.append(tempcolumn);
1239: s.append("::");
1240: appendColumnType(c, s);
1241: s.append(getRunCommand());
1242:
1243: s.append("ALTER TABLE ");
1244: s.append(t.name);
1245: s.append(" DROP COLUMN ");
1246: s.append(tempcolumn);
1247:
1248: if (!c.nulls) {
1249: s.append(getRunCommand());
1250: s.append("ALTER TABLE ");
1251: s.append(t.name);
1252: s.append(" ALTER COLUMN ");
1253: s.append(c.name);
1254: s.append(" SET NOT NULL");
1255: }
1256:
1257: } else if (nulls) {
1258: s.append("ALTER TABLE ");
1259: s.append(t.name);
1260: s.append(" ALTER COLUMN ");
1261: s.append(c.name);
1262: if (!c.nulls) {
1263: s.append(" SET NOT NULL");
1264: } else {
1265: s.append(" DROP NOT NULL");
1266: }
1267: }
1268:
1269: }
1270:
1271: /**
1272: * Append a column that needs to be added.
1273: */
1274: protected void appendDropColumn(TableDiff tableDiff, JdbcColumn c,
1275: CharBuf s, boolean comments) {
1276: if (comments && isCommentSupported()) {
1277: s.append(comment("dropping unknown column " + c.name));
1278: }
1279: s.append("\n");
1280: if (isDropSequenceColumn(tableDiff, c)) {
1281: dropSequenceColumn(tableDiff.getOurTable(), c, s, comments);
1282: } else {
1283: s.append("ALTER TABLE ");
1284: s.append(tableDiff.getOurTable().name);
1285: s.append(" DROP COLUMN ");
1286: s.append(c.name);
1287: }
1288: }
1289:
1290: /**
1291: * Append an 'drop constraint' statement for c.
1292: */
1293: protected void appendRefDropConstraint(CharBuf s, JdbcConstraint c,
1294: boolean comments) {
1295: // if (comments && isCommentSupported()) {
1296: // s.append(comment("dropping unknown constraint " + c.name));
1297: // s.append('\n');
1298: // }
1299: s.append("ALTER TABLE ");
1300: s.append(c.src.name);
1301: s.append(" DROP CONSTRAINT ");
1302: s.append(c.name);
1303: if (dropTableRequiresCascade) {
1304: s.append(" CASCADE");
1305: }
1306: }
1307:
1308: /**
1309: * Generate a 'drop index' statement for idx.
1310: */
1311: protected void appendDropIndex(CharBuf s, JdbcTable t,
1312: JdbcIndex idx, boolean comments) {
1313: // if (comments && isCommentSupported()) {
1314: // s.append(comment("dropping unknown index "+ idx.name));
1315: // s.append('\n');
1316: // }
1317: s.append("DROP INDEX ");
1318: s.append(idx.name);
1319: if (dropTableRequiresCascade) {
1320: s.append(" CASCADE");
1321: }
1322: }
1323:
1324: /**
1325: * Add the primary key constraint in isolation.
1326: */
1327: protected void addPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
1328: s.append("ALTER TABLE ");
1329: s.append(t.name);
1330: s.append(" ADD ");
1331: appendPrimaryKeyConstraint(t, s);
1332: }
1333:
1334: /**
1335: * Drop the primary key constraint in isolation.
1336: */
1337: protected void dropPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
1338: s.append("ALTER TABLE ");
1339: s.append(t.name);
1340: s.append(" DROP CONSTRAINT ");
1341: s.append(t.pkConstraintName);
1342: if (dropTableRequiresCascade) {
1343: s.append(" CASCADE");
1344: }
1345: }
1346:
1347: /**
1348: * Drop a Sequence column to implement a Set
1349: */
1350: protected void dropSequenceColumn(JdbcTable t, JdbcColumn c,
1351: CharBuf s, boolean comments) {
1352: String tempTableName = getTempTableName(t, 31);
1353:
1354: s
1355: .append(comment("create a temp table to store old table values."));
1356: s.append("\n");
1357: s.append("CREATE TABLE ");
1358: s.append(tempTableName);
1359: s.append(" (\n");
1360: JdbcColumn[] cols = t.getColsForCreateTable();
1361: int nc = cols.length;
1362: boolean first = true;
1363: for (int i = 0; i < nc; i++) {
1364: if (first)
1365: first = false;
1366: else
1367: s.append("\n");
1368: s.append(" ");
1369: appendCreateColumn(t, cols[i], s, comments);
1370: }
1371: s.append("\n ");
1372: appendPrimaryKeyConstraint(t, s);
1373: s.append("\n)");
1374: s.append(getRunCommand());
1375:
1376: s
1377: .append(comment("insert a distinct list into the temp table."));
1378: s.append("\n");
1379: s.append("INSERT INTO ");
1380: s.append(tempTableName);
1381: s.append("(");
1382: for (int i = 0; i < nc; i++) {
1383: s.append(cols[i].name);
1384: if ((i + 1) != nc) {
1385: s.append(", ");
1386: }
1387: }
1388: s.append(")");
1389: s.append("\nSELECT DISTINCT ");
1390: for (int i = 0; i < nc; i++) {
1391: if (i != 0) {
1392: s.append("\n ");
1393: }
1394: s.append(cols[i].name);
1395: if ((i + 1) != nc) {
1396: s.append(", ");
1397: }
1398: }
1399: s.append("\n FROM ");
1400: s.append(t.name);
1401:
1402: s.append(getRunCommand());
1403:
1404: s.append(comment("drop main table."));
1405: s.append("\n");
1406: s.append("DROP TABLE ");
1407: s.append(t.name);
1408: if (dropTableRequiresCascade)
1409: s.append(" CASCADE");
1410: s.append(getRunCommand());
1411:
1412: s.append(comment("rename temp table to main table."));
1413: s.append("\n");
1414: s.append("ALTER TABLE ");
1415: s.append(tempTableName);
1416: s.append(" RENAME TO ");
1417: s.append(t.name);
1418:
1419: }
1420:
1421: /**
1422: * Add a Sequence column to implement a list
1423: */
1424: protected void addSequenceColumn(JdbcTable t, JdbcColumn c,
1425: CharBuf s, boolean comments) {
1426:
1427: String mainTempTableName = getTempTableName(t, 31);
1428: String minTempTableName = getTempTableName(t, 31);
1429: String identityColumnName = getTempColumnName(t);
1430:
1431: JdbcColumn indexColumn = null;
1432: JdbcColumn sequenceColumn = null;
1433: JdbcColumn[] cols = t.getColsForCreateTable();
1434: int nc = cols.length;
1435: for (int i = 0; i < nc; i++) {
1436: if (isAddSequenceColumn(cols[i])) {
1437: sequenceColumn = cols[i];
1438: } else if (t.isInPrimaryKey(cols[i].name)) {
1439: indexColumn = cols[i];
1440: }
1441: }
1442:
1443: s
1444: .append(comment("Generate a sequence number so that we can implement a List."));
1445: s.append("\n");
1446: s
1447: .append(comment("create a temp table with a extra identity column."));
1448: s.append("\n");
1449: s.append("CREATE TABLE ");
1450: s.append(mainTempTableName);
1451: s.append(" (\n ");
1452: // create identity column
1453: s.append(identityColumnName);
1454: s.append(" SERIAL,");
1455: for (int i = 0; i < nc; i++) {
1456: s.append("\n ");
1457: appendCreateColumn(t, cols[i], s, comments);
1458: }
1459: int lastIndex = s.toString().lastIndexOf(',');
1460: s.replace(lastIndex, lastIndex + 1, ' ');// we take the last ',' out.
1461: s.append("\n)");
1462:
1463: s.append(getRunCommand());
1464:
1465: s
1466: .append(comment("insert a '0' in the sequence column and copy the rest of the old table into the temp table."));
1467: s.append("\n");
1468: s.append("INSERT INTO ");
1469: s.append(mainTempTableName);
1470: s.append("(");
1471: for (int i = 0; i < nc; i++) {
1472: s.append(cols[i].name);
1473: if ((i + 1) != nc) {
1474: s.append(", ");
1475: }
1476: }
1477: s.append(")");
1478: s.append("\nSELECT ");
1479: for (int i = 0; i < nc; i++) {
1480: if (i != 0) {
1481: s.append("\n ");
1482: }
1483: if (isAddSequenceColumn(cols[i])) {
1484: s.append('0');
1485: } else {
1486: s.append(cols[i].name);
1487: }
1488: if ((i + 1) != nc) {
1489: s.append(", ");
1490: }
1491: }
1492: s.append("\n FROM ");
1493: s.append(t.name);
1494: s.append("\n ORDER BY ");
1495: s.append(indexColumn.name);
1496:
1497: s.append(getRunCommand());
1498:
1499: s
1500: .append(comment("create a temp table to store the minimum id."));
1501: s.append("\n");
1502: s.append("CREATE TABLE ");
1503: s.append(minTempTableName);
1504: s.append(" (\n ");
1505: s.append(indexColumn.name);
1506: s.append(' ');
1507: appendColumnType(indexColumn, s);
1508: appendCreateColumnNulls(t, indexColumn, s);
1509: s.append(",\n ");
1510: s.append("min_id");
1511: s.append(" INTEGER\n)");
1512:
1513: s.append(getRunCommand());
1514:
1515: s.append(comment("store the minimum id."));
1516: s.append("\n");
1517: s.append("INSERT INTO ");
1518: s.append(minTempTableName);
1519: s.append(" (");
1520: s.append(indexColumn.name);
1521: s.append(", ");
1522: s.append("min_id");
1523: s.append(")\n");
1524: s.append("SELECT ");
1525: s.append(indexColumn.name);
1526: s.append(",\n ");
1527: s.append("MIN(");
1528: s.append(identityColumnName);
1529: s.append(")\n");
1530: s.append(" FROM ");
1531: s.append(mainTempTableName);
1532: s.append("\n");
1533: s.append(" GROUP BY ");
1534: s.append(indexColumn.name);
1535:
1536: s.append(getRunCommand());
1537:
1538: s.append(comment("drop main table " + t.name + "."));
1539: s.append("\n");
1540: s.append("DROP TABLE ");
1541: s.append(t.name);
1542: if (dropTableRequiresCascade)
1543: s.append(" CASCADE");
1544:
1545: s.append(getRunCommand());
1546:
1547: s.append(comment("recreate table " + t.name + "."));
1548: s.append("\n");
1549: s.append("CREATE TABLE ");
1550: s.append(t.name);
1551: s.append(" (\n");
1552: boolean first = true;
1553: for (int i = 0; i < nc; i++) {
1554: if (first)
1555: first = false;
1556: else
1557: s.append("\n");
1558: s.append(" ");
1559: appendCreateColumn(t, cols[i], s, comments);
1560: }
1561: s.append("\n ");
1562: appendPrimaryKeyConstraint(t, s);
1563: s.append("\n)");
1564: appendTableType(t, s);
1565:
1566: s.append(getRunCommand());
1567:
1568: s.append(comment("populate table " + t.name
1569: + " with the new sequence column."));
1570: s.append("\n");
1571: s.append("INSERT INTO ");
1572: s.append(t.name);
1573: s.append("(");
1574: for (int i = 0; i < nc; i++) {
1575: s.append(cols[i].name);
1576: if ((i + 1) != nc) {
1577: s.append(", ");
1578: }
1579: }
1580: s.append(")");
1581: s.append("\nSELECT ");
1582: for (int i = 0; i < nc; i++) {
1583: if (i != 0) {
1584: s.append("\n ");
1585: }
1586:
1587: if (isAddSequenceColumn(cols[i])) {
1588: s.append("(a.");
1589: s.append(identityColumnName);
1590: s.append(" - b.min_id)");
1591: } else {
1592: s.append("a.");
1593: s.append(cols[i].name);
1594: }
1595:
1596: if ((i + 1) != nc) {
1597: s.append(", ");
1598: }
1599: }
1600: s.append("\n FROM ");
1601: s.append(mainTempTableName);
1602: s.append(" a,\n ");
1603: s.append(minTempTableName);
1604: s.append(" b\n WHERE a.");
1605: s.append(indexColumn.name);
1606: s.append(" = b.");
1607: s.append(indexColumn.name);
1608:
1609: s.append(getRunCommand());
1610:
1611: s.append(comment("drop temp tables."));
1612: s.append("\n");
1613: s.append("DROP TABLE ");
1614: s.append(mainTempTableName);
1615: if (dropTableRequiresCascade)
1616: s.append(" CASCADE");
1617: s.append(getRunCommand());
1618:
1619: if (!dropTableRequiresCascade) {
1620: s.append(comment("drop sequence."));
1621: s.append("\n");
1622: s.append("DROP SEQUENCE ");
1623: s.append(mainTempTableName);
1624: s.append("_");
1625: s.append(identityColumnName);
1626: s.append("_seq");
1627: s.append(getRunCommand());
1628: }
1629:
1630: s.append("DROP TABLE ");
1631: s.append(minTempTableName);
1632: if (dropTableRequiresCascade)
1633: s.append(" CASCADE");
1634: s.append(getRunCommand());
1635: }
1636:
1637: /*
1638: CREATE TABLE temp AS SELECT * FROM distributors;
1639: DROP TABLE distributors;
1640: CREATE TABLE distributors AS SELECT * FROM temp;
1641: DROP TABLE temp;*/
1642: // protected void fixColumnsNonDirect (TableDiff tableDiff, PrintWriter out) {
1643: //
1644: // JdbcTable ourTable = tableDiff.getOurTable();
1645: // String tempTableName = getTempTableName(ourTable, 128);
1646: //
1647: //
1648: // CharBuf s = new CharBuf();
1649: // s.append("CREATE TABLE ");
1650: // s.append(tempTableName); //ourTable.name
1651: // s.append(" (");
1652: // JdbcColumn[] cols = ourTable.getColsForCreateTable();
1653: // int nc = cols.length;
1654: // for (int i = 0; i < nc; i++) {
1655: // appendCreateColumn(ourTable, cols[i], s, false);
1656: // s.append(' ');
1657: // }
1658: // appendPrimaryKeyConstraint(ourTable, s);
1659: // s.append(")");
1660: // s.append(getRunCommand());
1661: //
1662: //
1663: // s.append("INSERT INTO ");
1664: // s.append(tempTableName); //ourTable.name
1665: // s.append(" (");
1666: // for (int i = 0; i < nc; i++) {
1667: // s.append(cols[i].name);
1668: // if ((i + 1) != nc) {
1669: // s.append(", ");
1670: // }
1671: // }
1672: // s.append(") ");
1673: //
1674: // s.append("\n");//new line
1675: //
1676: // s.append("SELECT ");
1677: // for (int i = 0; i < nc; i++) {
1678: // ColumnDiff diff = getColumnDiffForName(tableDiff, cols[i].name);
1679: // if (diff == null) {
1680: // if (i != 0) {
1681: // s.append(" ");
1682: // }
1683: // s.append(cols[i].name);
1684: // } else {
1685: // if (diff.isMissingCol()) {
1686: // if (diff.getOurCol().nulls) {
1687: // if (i != 0) {
1688: // s.append(" ");
1689: // }
1690: // s.append("CAST(NULL");
1691: // s.append(" AS ");
1692: // appendColumnType(cols[i], s);
1693: // s.append(")");
1694: //
1695: // } else {
1696: // if (i != 0) {
1697: // s.append(" ");
1698: // }
1699: // s.append(getDefaultForType(diff.getOurCol()));
1700: // }
1701: //
1702: // } else if ((diff.isLenghtDiff() || diff.isScaleDiff() || diff.isTypeDiff()) && diff.isNullDiff()) {
1703: // if (cols[i].nulls) {
1704: // if (i != 0) {
1705: // s.append(" ");
1706: // }
1707: // s.append("CAST(");
1708: // s.append(cols[i].name);
1709: // s.append(" AS ");
1710: // appendColumnType(cols[i], s);
1711: // s.append(")");
1712: // } else {
1713: // if (i != 0) {
1714: // s.append(" ");
1715: // }
1716: // s.append("CASE ");
1717: // s.append("\n");//new line
1718: // s.append(" WHEN ");
1719: // s.append(cols[i].name);
1720: // s.append(" IS NOT NULL THEN CAST(");
1721: // s.append(cols[i].name);
1722: // s.append(" AS ");
1723: // appendColumnType(cols[i], s);
1724: // s.append(")");
1725: // s.append("\n");//new line
1726: // s.append(" ELSE CAST(");
1727: // s.append(getDefaultForType(diff.getOurCol()));
1728: // s.append(" AS ");
1729: // appendColumnType(cols[i], s);
1730: // s.append(")");
1731: // s.append("\n");//new line
1732: // s.append(" END CASE");
1733: // }
1734: //
1735: // } else if ((diff.isLenghtDiff() || diff.isScaleDiff() || diff.isTypeDiff()) && !diff.isNullDiff()) {
1736: // if (i != 0) {
1737: // s.append(" ");
1738: // }
1739: // s.append("CAST(");
1740: // s.append(cols[i].name);
1741: // s.append(" AS ");
1742: // appendColumnType(cols[i], s);
1743: // s.append(")");
1744: // } else if (diff.isNullDiff()) {
1745: // if (cols[i].nulls) {
1746: // if (i != 0) {
1747: // s.append(" ");
1748: // }
1749: // s.append(cols[i].name);
1750: // } else {
1751: // if (i != 0) {
1752: // s.append(" ");
1753: // }
1754: // s.append("CASE ");
1755: // s.append("\n");//new line
1756: // s.append(" WHEN ");
1757: // s.append(cols[i].name);
1758: // s.append(" IS NOT NULL THEN ");
1759: // s.append(cols[i].name);
1760: // s.append("\n");//new line
1761: // s.append(" ELSE ");
1762: // s.append(getDefaultForType(diff.getOurCol()));
1763: // s.append("\n");//new line
1764: // s.append(" END CASE");
1765: // }
1766: // }
1767: // }
1768: //
1769: //
1770: // if ((i + 1) != nc) {
1771: // s.append(", ");
1772: // s.append("\n");//new line
1773: // }
1774: // }
1775: // s.append("\n");//new line
1776: // s.append(" FROM ");
1777: // s.append(ourTable.name);
1778: // s.append(getRunCommand());
1779: //
1780: //
1781: // s.append("DROP TABLE ");
1782: // s.append(ourTable.name);
1783: // s.append(getRunCommand());
1784: //
1785: // s.append("RENAME TABLE ");
1786: // s.append(tempTableName);
1787: // s.append(" TO ");
1788: // s.append(ourTable.name);
1789: // s.append(getRunCommand());
1790: //
1791: // out.println(s.toString());
1792: //
1793: //
1794: // }
1795: }
|