Java > Open Source Codes > com > versant > core > jdbc > sql > PostgresSqlDriver


1
2 /*
3  * Copyright (c) 1998 - 2005 Versant Corporation
4  * All rights reserved. This program and the accompanying materials
5  * are made available under the terms of the Eclipse Public License v1.0
6  * which accompanies this distribution, and is available at
7  * http://www.eclipse.org/legal/epl-v10.html
8  *
9  * Contributors:
10  * Versant Corporation - initial API and implementation
11  */
12 package com.versant.core.jdbc.sql;
13
14 import com.versant.core.jdbc.metadata.*;
15 import com.versant.core.jdbc.sql.conv.BooleanConverter;
16 import com.versant.core.jdbc.sql.conv.DateTimestampConverter;
17 import com.versant.core.jdbc.sql.conv.NoMinCharConverter;
18 import com.versant.core.jdbc.sql.diff.ColumnDiff;
19 import com.versant.core.jdbc.sql.diff.ControlParams;
20 import com.versant.core.jdbc.sql.diff.TableDiff;
21 import com.versant.core.jdbc.sql.exp.SqlExp;
22 import com.versant.core.util.CharBuf;
23
24 import java.io.PrintWriter ;
25 import java.sql.*;
26 import java.util.*;
27 import java.util.Date ;
28
29 /**
30  * A driver for Postgres.
31  */
32 public class PostgresSqlDriver extends SqlDriver {
33     public static final String SQLPARAM_NUMERIC_CAST = "CAST(? as numeric)";
34     public static final String SQLPARAM_REAL_CAST = "CAST(? as real)";
35
36     public final static char[] CHAR_SQLPARAM_NUMERIC_CAST = SQLPARAM_NUMERIC_CAST.toCharArray();
37     public final static char[] CHAR_SQLPARAM_REAL_CAST = SQLPARAM_REAL_CAST.toCharArray();
38
39     private boolean dropTableRequiresCascade;
40     private String version = null;
41     private HashMap sqlTypeCache;
42     private HashMap pgTypeCache;
43
44     private static final String jdbc1Types[] = {
45         "int2", "int4", "oid", "int8", "cash", "money", "numeric", "float4", "float8", "bpchar",
46         "char", "char2", "char4", "char8", "char16", "varchar", "text", "name", "filename", "bytea",
47         "bool", "date", "time", "abstime", "timestamp", "timestamptz"
48     };
49     private static final int jdbc1Typei[] = {
50         5, 4, 4, -5, 8, 8, 2, 7, 8, 1,
51         1, 1, 1, 1, 1, 12, 2005, 12, 12, 2004,
52         -7, 91, 92, 93, 93, 93
53     };
54
55     private static char[] FOR_UPDATE = " FOR UPDATE OF ".toCharArray();
56
57     /**
58      * Get the name of this driver.
59      */
60     public String getName() {
61         return "postgres";
62     }
63
64     public boolean isCustomizeForServerRequired() {
65         return true;
66     }
67
68     /**
69      * Find out what version of Postgres con is for and adapt.
70      */
71     public void customizeForServer(Connection con) throws SQLException {
72         String s = getVersion(con);
73         int i = s.indexOf(' ') + 1;
74         int j = s.indexOf('.');
75         int k = j;
76         for (;;) {
77             char c = s.charAt(++k);
78             if ((c < '0') || (c > '9')) break;
79         }
80         int major = Integer.parseInt(s.substring(i, j));
81         int minor = Integer.parseInt(s.substring(j + 1, k));
82         dropTableRequiresCascade = (major >= 7 && minor >= 3) || major >= 8;
83     }
84
85     private String parseVersion(String version){
86         int i = version.indexOf(' ') + 1;
87         int j = version.indexOf('.');
88         int k = j;
89         for (; ;) {
90             char c = version.charAt(++k);
91             if ((c < '0') || (c > '9')) break;
92         }
93         int major = Integer.parseInt(version.substring(i, j));
94         int minor = Integer.parseInt(version.substring(j + 1, k));
95         return major + "." + minor+".";
96
97     }
98
99     private String getVersion(Connection con) throws SQLException {
100         if (version != null){
101             return version;
102         } else {
103             Statement stat = null;
104             ResultSet rs = null;
105             try {
106                 stat = con.createStatement();
107                 rs = stat.executeQuery("SELECT version()");
108                 rs.next();
109                 String ver = rs.getString(1);
110                 con.commit();
111                 version = parseVersion(ver);
112                 return version;
113             } finally {
114                 if (rs != null) {
115                     try {
116                         rs.close();
117                     } catch (SQLException e) {
118                         // ignore
119 }
120                 }
121                 if (stat != null) {
122                     try {
123                         stat.close();
124                     } catch (SQLException e) {
125                         // ignore
126 }
127                 }
128             }
129         }
130     }
131
132     /**
133      * Get the default type mapping for the supplied JDBC type code from
134      * java.sql.Types or null if the type is not supported. There is no
135      * need to set the database or jdbcType on the mapping as this is done
136      * after this call returns. Subclasses should override this and to
137      * customize type mappings.
138      */
139     protected JdbcTypeMapping getTypeMapping(int jdbcType) {
140         switch (jdbcType) {
141             case Types.BIT:
142             case Types.TINYINT:
143                 return new JdbcTypeMapping("SMALLINT",
144                     0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE, null);
145             case Types.DOUBLE:
146                 return new JdbcTypeMapping("DOUBLE PRECISION",
147                     0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE, null);
148             case Types.CLOB:
149             case Types.LONGVARCHAR:
150                 return new JdbcTypeMapping("TEXT",
151                     0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE, null);
152             case Types.VARBINARY:
153             case Types.LONGVARBINARY:
154             case Types.BLOB:
155                 return new JdbcTypeMapping("BYTEA",
156                     0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
157                     bytesConverterFactory);
158         }
159         return super.getTypeMapping(jdbcType);
160     }
161
162     /**
163      * Get the default field mappings for this driver. These map java classes
164      * to column properties. Subclasses should override this, call super() and
165      * replace mappings as needed.
166      */
167     public HashMap getJavaTypeMappings() {
168         HashMap ans = super.getJavaTypeMappings();
169
170         BooleanConverter.Factory bcf = new BooleanConverter.Factory();
171         ((JdbcJavaTypeMapping)ans.get(Boolean.TYPE)).setConverterFactory(bcf);
172         ((JdbcJavaTypeMapping)ans.get(Boolean .class)).setConverterFactory(bcf);
173
174         DateTimestampConverter.Factory dtcf = new DateTimestampConverter.Factory();
175         ((JdbcJavaTypeMapping)ans.get(Date .class)).setConverterFactory(dtcf);
176
177         NoMinCharConverter.Factory f = new NoMinCharConverter.Factory();
178         add(ans, new JdbcJavaTypeMapping(Character .class, Types.CHAR, 1, 0,
179             JdbcJavaTypeMapping.TRUE, f));
180         add(ans, new JdbcJavaTypeMapping(Character.TYPE, Types.CHAR, 1, 0,
181             JdbcJavaTypeMapping.FALSE, f));
182
183         return ans;
184     }
185
186     public String getAliasPrepend() {
187         return " as ";
188     }
189
190     /**
191      * Does the JDBC driver support statement batching?
192      */
193     public boolean isInsertBatchingSupported() {
194         return true;
195     }
196
197     /**
198      * Does the JDBC driver support statement batching for updates?
199      */
200     public boolean isUpdateBatchingSupported() {
201         return true;
202     }
203
204     /**
205      * Does the JDBC driver support scrollable result sets?
206      */
207     public boolean isScrollableResultSetSupported() {
208         return true;
209     }
210
211     public boolean isFetchSizeSupported() {
212         return false;
213     }
214
215     /**
216      * Does this driver use the ANSI join syntax (i.e. the join clauses appear
217      * in the from list e.g. postgres)?
218      */
219     public boolean isAnsiJoinSyntax() {
220         return true;
221     }
222
223     /**
224      * Is null a valid value for a column with a foreign key constraint?
225      */
226     public boolean isNullForeignKeyOk() {
227         return true;
228     }
229
230     public boolean isAutoIncSupported() {
231         return true;
232     }
233
234     public Object getAutoIncColumnValue(JdbcTable classTable, Connection con,
235             Statement stat) throws SQLException {
236         String sql = "SELECT currval('" + classTable.name + "_" +
237             classTable.pk[0].name + "_seq')";
238         Statement s = null;
239         ResultSet rs = null;
240         try {
241             s = con.createStatement();
242             rs = s.executeQuery(sql.toString());
243             rs.next();
244             if (classTable.pk[0].jdbcType == Types.BIGINT) {
245                 return new Long (rs.getLong(1));
246             } else {
247                 return new Integer (rs.getInt(1));
248             }
249         } finally {
250             if (rs != null) {
251                 try {
252                     rs.close();
253                 } catch (SQLException e) {
254                     // ignore
255 }
256             }
257             if (s != null) {
258                 try {
259                     s.close();
260                 } catch (SQLException e) {
261                     // ignore
262 }
263             }
264         }
265     }
266
267     /**
268      * Create a default name generator instance for JdbcStore's using this
269      * driver.
270      */
271     public JdbcNameGenerator createJdbcNameGenerator() {
272         DefaultJdbcNameGenerator n = createDefaultJdbcNameGenerator();
273         n.setMaxColumnNameLength(31);
274         n.setMaxTableNameLength(31);
275         n.setMaxConstraintNameLength(31);
276         n.setMaxIndexNameLength(31);
277         return n;
278     }
279
280     /**
281      * Append the part of a create table statement for a column.
282      */
283     protected void appendCreateColumn(JdbcTable t, JdbcColumn c, CharBuf s,
284             boolean comments) {
285         if (c.autoinc) {
286             int si = s.size();
287             s.append(c.name);
288             if (c.jdbcType == Types.BIGINT) {
289                 s.append(" SERIAL8");
290             } else {
291                 s.append(" SERIAL");
292             }
293             s.append(',');
294             if (comments && c.comment != null) {
295                 s.append(' ');
296                 si += COMMENT_COL;
297                 for (; s.size() < si; s.append(' '));
298                 s.append(comment(c.comment));
299             }
300         } else {
301             super.appendCreateColumn(t, c, s, comments);
302         }
303     }
304
305     /**
306      * Append the allow nulls part of the definition for a column in a
307      * create table statement.
308      */
309     protected void appendCreateColumnNulls(JdbcTable t, JdbcColumn c,
310             CharBuf s) {
311         if (!c.nulls) s.append(" NOT NULL");
312     }
313
314     /**
315      * Add the primary key constraint part of a create table statement to s.
316      */
317     protected void appendPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
318         s.append("CONSTRAINT ");
319         s.append(t.pkConstraintName);
320         s.append(" PRIMARY KEY (");
321         appendColumnNameList(t.pk, s);
322         s.append(')');
323     }
324
325     /**
326      * Append an 'add constraint' statement for c.
327      */
328     protected void appendRefConstraint(CharBuf s, JdbcConstraint c) {
329         s.append("ALTER TABLE ");
330         s.append(c.src.name);
331         s.append(" ADD CONSTRAINT ");
332         s.append(c.name);
333         s.append(" FOREIGN KEY (");
334         appendColumnNameList(c.srcCols, s);
335         s.append(") REFERENCES ");
336         s.append(c.dest.name);
337         s.append('(');
338         appendColumnNameList(c.dest.pk, s);
339         s.append(")");
340     }
341
342     /**
343      * Write an SQL statement to a script with appropriate separator.
344      */
345     protected void print(PrintWriter out, String sql) {
346         out.print(sql);
347         out.println(";");
348         out.println();
349     }
350
351     /**
352      * Append the from list entry for a table.
353      */
354     public void appendSqlFrom(JdbcTable table, String alias,
355             CharBuf s) {
356         s.append(table.name);
357         if (alias != null) {
358             s.append(" AS ");
359             s.append(alias);
360         }
361     }
362
363     /**
364      * Append the from list entry for a table that is the right hand table
365      * in a join i.e. it is being joined to.
366      * @param exp This is the expression that joins the tables
367      * @param outer If true then this is an outer join
368      */
369     public void appendSqlFromJoin(JdbcTable table, String alias, SqlExp exp,
370             boolean outer, CharBuf s) {
371         if (exp == null) {
372             s.append(" CROSS JOIN ");
373         } else if (outer) {
374             s.append(" LEFT JOIN ");
375         } else {
376             s.append(" JOIN ");
377         }
378         s.append(table.name);
379         if (alias != null) {
380             s.append(" AS ");
381             s.append(alias);
382         }
383         if (exp != null) {
384             s.append(" ON (");
385             exp.appendSQL(this, s, null);
386             s.append(')');
387         }
388     }
389
390     /**
391      * Append a replacable parameter part of a where clause for the column.
392      * This gives the driver a chance to embed type conversions and so on
393      * for types not handled well by the JDBC driver (e.g. BigDecimals for
394      * the postgres JDBC driver).
395      */
396     public void appendWhereParam(CharBuf s, JdbcColumn c) {
397         switch (c.jdbcType) {
398             case Types.NUMERIC:
399                 s.append("CAST(? as numeric)");
400                 break;
401             case Types.REAL:
402                 s.append("CAST(? as real)");
403                 break;
404             default:
405                 super.appendWhereParam(s, c);
406         };
407     }
408
409     /**
410      * Get a String for a replacable parameter. This gives the driver a
411      * chance to embed type conversions and so on for types not handled well
412      * by the JDBC driver (e.g. BigDecimals and the postgres JDBC driver).
413      */
414     public String getSqlParamString(int jdbcType) {
415         switch (jdbcType) {
416             case Types.NUMERIC:
417                 return SQLPARAM_NUMERIC_CAST;
418             case Types.REAL:
419                 return SQLPARAM_REAL_CAST;
420             default:
421                 return "?";
422         }
423     }
424
425     public char[] getSqlParamStringChars(int jdbcType) {
426         switch (jdbcType) {
427             case Types.NUMERIC:
428                 return CHAR_SQLPARAM_NUMERIC_CAST;
429             case Types.REAL:
430                 return CHAR_SQLPARAM_REAL_CAST;
431             default:
432                 return DEFAULT_PARAM_CHARS;
433         }
434     }
435
436     /**
437      * Get default SQL to test a connection or null if none available. This
438      * must be a query that returns at least one row.
439      */
440     public String getConnectionValidateSQL() {
441         return "SELECT datname FROM pg_database";
442     }
443
444     /**
445      * Drop the table and all its constraints etc. This must remove
446      * constraints to this table from other tables so it can be dropped.
447      */
448     public void dropTable(Connection con, String table, Statement stat) throws SQLException {
449         CharBuf s = new CharBuf(64);
450         s.append("DROP TABLE ");
451         s.append(table);
452         if (dropTableRequiresCascade) s.append(" CASCADE");
453         stat.execute(s.toString());
454     }
455
456     /**
457      * Get whatever needs to be appended to a SELECT statement to lock the
458      * rows if this makes sense for the database. This must have a leading
459      * space if not empty.
460      */
461     public char[] getSelectForUpdate() {
462         return FOR_UPDATE;
463     }
464
465     /**
466      * Does 'SELECT FOR UPDATE' require the main table of the query to be
467      * appended (ala postgres)?
468      */
469     public boolean isSelectForUpdateAppendTable() {
470         return true;
471     }
472
473     /**
474      * Can 'SELECT FOR UPDATE' be used with a DISTINCT?
475      */
476     public boolean isSelectForUpdateWithDistinctOk() {
477         return false;
478     }
479
480     protected boolean isValidSchemaTable(String tableName) {
481         String [] sysNames = new String []{"pga_forms",
482                                          "pga_queries",
483                                          "pga_reports",
484                                          "pga_schema",
485                                          "pga_scripts"};
486
487         for (int i = 0; i < sysNames.length; i++) {
488             if (sysNames[i].equals(tableName)) {
489                 return false;
490             }
491         }
492         return true;
493     }
494
495
496
497
498     /**
499      * Get the JdbcTables from the database for the given database con.
500      * @param con
501      * @return HashMap of tablename.toLowerCase() as key and JdbcTable as value
502      * @throws SQLException on DB errors
503      */
504     public HashMap getDBSchema(Connection con, ControlParams params) throws SQLException {
505         HashMap jdbcTableMap = new HashMap(); // main map of jdbc tables
506 // now we do columns
507 params.setColumnsOnly(false);
508         String tableName = null;
509         initTypeCache(con);
510         String columnSql = null;
511         if (haveMinimumServerVersion("7.3",con)) {
512             columnSql =
513                     "SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description " +
514                     " FROM pg_catalog.pg_namespace n " +
515                     " JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) " +
516                     " JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) " +
517                     " LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) " +
518                     " LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid) " +
519                     " LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND dc.relname='pg_class') " +
520                     " LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog') " +
521                     " WHERE a.attnum > 0 " +
522                     " AND NOT a.attisdropped "+
523                     " AND n.nspname LIKE 'public'";
524
525         } else if (haveMinimumServerVersion("7.1", con)) {
526             columnSql =
527                     "SELECT NULL::text AS nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description " +
528                     " FROM pg_class c " +
529                     " JOIN pg_attribute a ON (a.attrelid=c.oid) " +
530                     " LEFT JOIN pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) " +
531                     " LEFT JOIN pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid) " +
532                     " LEFT JOIN pg_class dc ON (dc.oid=dsc.classoid AND dc.relname='pg_class') " +
533                     " WHERE a.attnum > 0 "+
534                     " AND c.relname NOT LIKE('pg_%')";
535         } else {
536             columnSql =
537                     "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 " +
538                     " FROM pg_class c, " +
539                     " pg_attribute a " +
540                     " WHERE a.attrelid=c.oid AND a.attnum > 0 ";
541         }
542         columnSql = columnSql + " ORDER BY nspname,relname,attnum ";
543
544         Statement statCol = con.createStatement();
545         ResultSet rsColumn = statCol.executeQuery(columnSql);
546         ArrayList columns = null;
547
548         while (rsColumn.next()) {
549
550             String temptableName = rsColumn.getString(2);
551
552             if (!isValidSchemaTable(temptableName)) {
553                 continue;
554             }
555
556             if (tableName == null) { // this is the first one
557 tableName = temptableName;
558                 columns = new ArrayList();
559                 JdbcTable jdbcTable = new JdbcTable();
560                 jdbcTable.name = tableName;
561                 jdbcTableMap.put(tableName, jdbcTable);
562             }
563
564             if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
565 JdbcColumn[] jdbcColumns = new JdbcColumn[columns.size()];
566                 columns.toArray(jdbcColumns);
567                 JdbcTable jdbcTable0 = (JdbcTable) jdbcTableMap.get(tableName);
568                 jdbcTable0.cols = jdbcColumns;
569
570
571                 tableName = temptableName;
572                 columns.clear();
573                 JdbcTable jdbcTable1 = new JdbcTable();
574                 jdbcTable1.name = tableName;
575                 jdbcTableMap.put(tableName, jdbcTable1);
576             }
577
578             JdbcColumn col = new JdbcColumn();
579             int typeOid = rsColumn.getInt("atttypid");
580             col.name = rsColumn.getString(3);
581             String pgType = getPGType(typeOid);
582             col.sqlType = pgType;
583             col.jdbcType = getSQLType(typeOid);
584             col.scale = 0;
585             if (pgType.equals("bpchar") || pgType.equals("varchar")) {
586                 int atttypmod = rsColumn.getInt("atttypmod");
587                 col.length = atttypmod == -1 ? 0 : atttypmod - 4;
588             } else if (pgType.equals("numeric") || pgType.equals("decimal")) {
589                 int attypmod = rsColumn.getInt("atttypmod") - 4; // was index 8
590 col.length = attypmod >> 16 & 65535;
591                 col.scale = attypmod & 65535;
592             } else if (pgType.equals("bit") || pgType.equals("varbit")) {
593                 col.length = rsColumn.getInt("atttypmod");
594             } else {
595                 col.length = rsColumn.getInt("attlen");
596             }
597             col.nulls = !rsColumn.getBoolean("attnotnull");
598
599             switch (col.jdbcType) {
600                 case java.sql.Types.BIT:
601                 case java.sql.Types.TINYINT:
602                 case java.sql.Types.SMALLINT:
603                 case java.sql.Types.BIGINT:
604                 case java.sql.Types.INTEGER:
605                 case java.sql.Types.DATE:
606                 case java.sql.Types.TIME:
607                 case java.sql.Types.TIMESTAMP:
608                     col.length = 0;
609                     col.scale = 0;
610                 default:
611             }
612
613             columns.add(col);
614         }
615         // we fin last table
616 if (columns != null){
617             JdbcColumn[] jdbcColumns = new JdbcColumn[columns.size()];
618             columns.toArray(jdbcColumns);
619             JdbcTable colJdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
620             if (colJdbcTable != null){
621                 colJdbcTable.cols = jdbcColumns;
622             }
623             columns.clear();
624         }
625         tableName = null;
626
627
628         // clean up
629 if (rsColumn != null) {
630             try {
631                 rsColumn.close();
632             } catch (SQLException e) {
633             }
634         }
635         if (statCol != null) {
636             try {
637                 statCol.close();
638             } catch (SQLException e) {
639             }
640         }
641         if (!params.checkColumnsOnly()) {
642             if (params.isCheckPK()) {
643                 // now we do primaryKeys
644 HashMap pkMap = null;
645
646                 String where = "AND ct.relname NOT LIKE('pg_%')";
647                 String from;
648                 if (haveMinimumServerVersion("7.3",con)) {
649                     from = " FROM pg_catalog.pg_namespace n, " +
650                             "pg_catalog.pg_class ct, " +
651                             "pg_catalog.pg_class ci, " +
652                             "pg_catalog.pg_attribute a, " +
653                             "pg_catalog.pg_index i ";
654                     where = " AND ct.relnamespace = n.oid " +
655                             " AND n.nspname = 'public' ";
656                 } else {
657                     from = " FROM pg_class ct, " +
658                             " pg_class ci, " +
659                             " pg_attribute a, " +
660                             " pg_index i ";
661                 }
662                 String pkSql =
663                         " SELECT ct.relname AS TABLE_NAME, " +
664                         " a.attname AS COLUMN_NAME, " +
665                         " a.attnum AS KEY_SEQ, " +
666                         " ci.relname AS PK_NAME " +
667                         from +
668                         " WHERE ct.oid=i.indrelid " +
669                         " AND ci.oid=i.indexrelid " +
670                         " AND a.attrelid=ci.oid " +
671                         " AND i.indisprimary " +
672                         where +
673                         " ORDER BY table_name, pk_name, key_seq";
674
675                 Statement statPK = con.createStatement();
676                 ResultSet rsPKs = statPK.executeQuery(pkSql);
677                 int pkCount = 0;
678                 String pkName = null;
679                 while (rsPKs.next()) {
680                     String temptableName = rsPKs.getString(1);
681
682                     if (!jdbcTableMap.containsKey(temptableName)) {
683                         continue;
684                     }
685
686                     if (tableName == null) { // this is the first one
687 tableName = temptableName;
688                         pkMap = new HashMap();
689                     }
690
691                     if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
692 JdbcColumn[] pkColumns = new JdbcColumn[pkCount];
693                         int indexOfPKCount = 0;
694                         JdbcTable jdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
695                         for (int i = 0; i < jdbcTable.cols.length; i++) {
696                             JdbcColumn jdbcColumn = jdbcTable.cols[i];
697                             if (pkMap.containsKey(jdbcColumn.name)) {
698                                 pkColumns[indexOfPKCount] = jdbcColumn;
699                                 jdbcColumn.pk = true;
700                                 indexOfPKCount++;
701                             }
702                         }
703                         jdbcTable.pk = pkColumns;
704                         jdbcTable.pkConstraintName = pkName;
705
706                         // PK's become tables, remove them
707 jdbcTableMap.remove(pkName);
708
709                         tableName = temptableName;
710                         pkMap.clear();
711                         pkCount = 0;
712                     }
713                     pkCount++;
714                     pkMap.put(rsPKs.getString(2), null);
715                     pkName = rsPKs.getString(4);
716                 }
717                 JdbcColumn[] pkColumns = new JdbcColumn[pkCount];
718                 int indexOfPKCount = 0;
719                 JdbcTable pkJdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
720                 if (pkJdbcTable != null) {
721                     for (int i = 0; i < pkJdbcTable.cols.length; i++) {
722                         JdbcColumn jdbcColumn = pkJdbcTable.cols[i];
723                         if (pkMap.containsKey(jdbcColumn.name)) {
724                             pkColumns[indexOfPKCount] = jdbcColumn;
725                             jdbcColumn.pk = true;
726                             indexOfPKCount++;
727                         }
728                     }
729                     pkJdbcTable.pk = pkColumns;
730                     pkJdbcTable.pkConstraintName = pkName;
731
732                     // PK's become tables, remove them
733 jdbcTableMap.remove(pkName);
734                 }
735                 tableName = null;
736                 // clean up
737 if (rsPKs != null) {
738                     try {
739                         rsPKs.close();
740                     } catch (SQLException e) {
741                     }
742                 }
743                 if (statPK != null) {
744                     try {
745                         statPK.close();
746                     } catch (SQLException e) {
747                     }
748                 }
749             }
750             if (params.isCheckIndex()) {
751                 // now we do index
752 String where = "AND ct.relname NOT LIKE('pg_%') ";
753                 String from;
754                 if (haveMinimumServerVersion("7.3",con)) {
755                     from = " FROM pg_catalog.pg_namespace n, \n" +
756                             " pg_catalog.pg_class ct, \n" +
757                             " pg_catalog.pg_class ci, \n" +
758                             " pg_catalog.pg_index i, \n" +
759                             " pg_catalog.pg_attribute a, \n" +
760                             " pg_catalog.pg_am am \n";
761                     where = " AND n.oid = ct.relnamespace \n" +
762                             " AND n.nspname = 'public' \n";
763                 } else {
764                     from = " FROM pg_class ct, \n" +
765                             " pg_class ci, \n" +
766                             " pg_index i, \n" +
767                             " pg_attribute a, \n" +
768                             " pg_am am \n";
769                 }
770                 String indexSql =
771                         "SELECT ct.relname AS TABLE_NAME, \n" +
772                         " a.attname AS COLUMN_NAME, \n" +
773                         " ci.relname AS INDEX_NAME, \n" +
774                         " NOT i.indisunique AS NON_UNIQUE, \n" +
775                         " CASE i.indisclustered \n" +
776                         " WHEN true THEN " + 1 + "\n" +
777                         " ELSE CASE am.amname \n" +
778                         " WHEN 'hash' THEN " + 2 + "\n" +
779                         " ELSE " + 3 + "\n" +
780                         " END \n" +
781                         " END AS TYPE, \n" +
782                         " a.attnum AS ORDINAL_POSITION \n" +
783                         from +
784                         " WHERE ct.oid = i.indrelid \n" +
785                         " AND ci.oid = i.indexrelid \n" +
786                         " AND a.attrelid = ci.oid \n" +
787                         " AND ci.relam=am.oid \n" +
788                         where +
789                         " ORDER BY TABLE_NAME, INDEX_NAME, ORDINAL_POSITION \n";
790                 Statement statIndex = con.createStatement();
791 // System.out.println("\n\n\n\n");
792 // System.out.println("--"+getVersion(con));
793 // System.out.println(indexSql);
794 // System.out.println("\n\n\n\n");
795 ResultSet rsIndex = statIndex.executeQuery(indexSql);
796
797                 HashMap indexNameMap = null;
798                 ArrayList indexes = null;
799                 while (rsIndex.next()) {
800                     String temptableName = rsIndex.getString(1);
801                     if (tableName == null) { // this is the first one
802 tableName = temptableName;
803                         indexNameMap = new HashMap();
804                         indexes = new ArrayList();
805                     }
806
807                     String indexName = rsIndex.getString(3);
808                     JdbcTable tempJdbcTable = (JdbcTable) jdbcTableMap.get(temptableName);
809
810
811                     if (indexName != null && !indexName.equals(tempJdbcTable.pkConstraintName)) {
812                         if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
813 JdbcTable jdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
814                             JdbcIndex[] jdbcIndexes = new JdbcIndex[indexes.size()];
815                             indexes.toArray(jdbcIndexes);
816                             jdbcTable.indexes = jdbcIndexes;
817
818
819                             tableName = temptableName;
820                             indexes.clear();
821                             indexNameMap.clear();
822
823                         }
824                         JdbcTable jdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
825                         if (indexNameMap.containsKey(indexName)) {
826                             JdbcIndex index = null;
827                             for (Iterator iter = indexes.iterator(); iter.hasNext();) {
828                                 JdbcIndex jdbcIndex = (JdbcIndex) iter.next();
829                                 if (jdbcIndex.name.equals(indexName)) {
830                                     index = jdbcIndex;
831                                 }
832                             }
833
834                             JdbcColumn[] tempIndexColumns = index.cols;
835                             JdbcColumn[] indexColumns = new JdbcColumn[tempIndexColumns.length + 1];
836                             System.arraycopy(tempIndexColumns, 0, indexColumns, 0, tempIndexColumns.length);
837                             String colName = rsIndex.getString(2);
838                             for (int i = 0; i < jdbcTable.cols.length; i++) {
839                                 JdbcColumn jdbcColumn = jdbcTable.cols[i];
840                                 if (colName.equals(jdbcColumn.name)) {
841                                     indexColumns[tempIndexColumns.length] = jdbcColumn;
842                                     jdbcColumn.partOfIndex = true;
843                                 }
844                             }
845                             index.setCols(indexColumns);
846                         } else {
847                             indexNameMap.put(indexName, null);
848                             JdbcIndex index = new JdbcIndex();
849                             index.name = indexName;
850                             // index's become tables, remove them
851 jdbcTableMap.remove(indexName);
852                             index.unique = !rsIndex.getBoolean(4);
853                             short indexType = rsIndex.getShort(5);
854                             switch (indexType) {
855                                 case DatabaseMetaData.tableIndexClustered:
856                                     index.clustered = true;
857                                     break;
858                             }
859                             String colName = rsIndex.getString(2);
860                             JdbcColumn[] indexColumns = new JdbcColumn[1];
861                             for (int i = 0; i < jdbcTable.cols.length; i++) {
862                                 JdbcColumn jdbcColumn = jdbcTable.cols[i];
863                                 if (colName.equals(jdbcColumn.name)) {
864                                     indexColumns[0] = jdbcColumn;
865                                     jdbcColumn.partOfIndex = true;
866                                 }
867                             }
868                             index.setCols(indexColumns);
869                             indexes.add(index);
870                         }
871                     }
872                 }
873                 JdbcTable indexJdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
874                 if (indexJdbcTable != null) {
875                     JdbcIndex[] jdbcIndexes = new JdbcIndex[indexes.size()];
876                     indexes.toArray(jdbcIndexes);
877                     indexJdbcTable.indexes = jdbcIndexes;
878                     indexes.clear();
879                     indexNameMap.clear();
880                 }
881                 tableName = null;
882                 // clean up
883 if (rsIndex != null) {
884                     try {
885                         rsIndex.close();
886                     } catch (SQLException e) {
887                     }
888                 }
889                 if (statIndex != null) {
890                     try {
891                         statIndex.close();
892                     } catch (SQLException e) {
893                     }
894                 }
895             }
896             if (params.isCheckConstraint()) {
897                 // now we do forign keys
898 String where = "";
899
900                 String from;
901                 if (haveMinimumServerVersion("7.3",con)) {
902
903                     from =
904                             " FROM pg_catalog.pg_namespace n1 " +
905                             " JOIN pg_catalog.pg_class c1 ON (c1.relnamespace = n1.oid) " +
906                             " JOIN pg_catalog.pg_index i ON (c1.oid = i.indrelid) " +
907                             " JOIN pg_catalog.pg_class ic ON (i.indexrelid = ic.oid) " +
908                             " JOIN pg_catalog.pg_attribute a ON (ic.oid = a.attrelid), " +
909                             " pg_catalog.pg_namespace n2 JOIN pg_catalog.pg_class c2 ON (c2.relnamespace = n2.oid), " +
910                             " pg_catalog.pg_trigger t1 JOIN pg_catalog.pg_proc p1 ON (t1.tgfoid = p1.oid), " +
911                             " pg_catalog.pg_trigger t2 JOIN pg_catalog.pg_proc p2 ON (t2.tgfoid = p2.oid) ";
912                     where = " AND n2.nspname = 'public' ";
913
914                 } else {
915
916                     from = " FROM pg_class c1 " +
917                             " JOIN pg_index i ON (c1.oid = i.indrelid) " +
918                             " JOIN pg_class ic ON (i.indexrelid = ic.oid) " +
919                             " JOIN pg_attribute a ON (ic.oid = a.attrelid), " +
920                             " pg_class c2, " +
921                             " pg_trigger t1 JOIN pg_proc p1 ON (t1.tgfoid = p1.oid), " +
922                             " pg_trigger t2 JOIN pg_proc p2 ON (t2.tgfoid = p2.oid) ";
923                 }
924                 String fkSql =
925                             "SELECT c1.relname as PKTABLE_NAME, " + //PKTABLE_NAME
926 " c2.relname as FKTABLE_NAME, " + //FKTABLE_NAME
927 " t1.tgconstrname, " +
928                             " a.attnum as keyseq, " +
929                             " ic.relname as fkeyname, " +
930                             " t1.tgdeferrable, " +
931                             " t1.tginitdeferred, " +
932                             " t1.tgnargs," +
933                             " t1.tgargs " +
934                             from +
935                             "WHERE (t1.tgrelid=c1.oid " +
936                             " AND t1.tgisconstraint " +
937                             " AND t1.tgconstrrelid=c2.oid " +
938                             " AND p1.proname LIKE 'RI\\\\_FKey\\\\_%\\\\_upd') " +
939                             " AND (t2.tgrelid=c1.oid " +
940                             " AND t2.tgisconstraint " +
941                             " AND t2.tgconstrrelid=c2.oid " +
942                             " AND p2.proname LIKE 'RI\\\\_FKey\\\\_%\\\\_del') " +
943                             " AND i.indisprimary " +
944                             where +
945                             "ORDER BY FKTABLE_NAME , tgconstrname, keyseq";
946                 Statement statFK = con.createStatement();
947                 ResultSet rsFKs = statFK.executeQuery(fkSql);
948
949                 HashMap constraintNameMap = new HashMap();
950                 HashMap doneMap = new HashMap();
951
952
953                 while (rsFKs.next()) {
954                     String targs = rsFKs.getString(9);
955                     StringTokenizer st = new StringTokenizer(targs, "\\000");
956
957                     String constName = null;
958                     String srcTableName = null;
959                     String destTableName = null;
960                     ArrayList srcColNames = new ArrayList();
961
962                     if (st.hasMoreTokens()){ //0
963 constName = st.nextToken();
964                     }
965                     if (st.hasMoreTokens()) { //1
966 srcTableName = st.nextToken();
967                     }
968                     if (st.hasMoreTokens()) { //2
969 destTableName = st.nextToken();
970                         st.nextToken();//3 UNSPECIFIED
971 }
972                     while (st.hasMoreTokens()){
973                         srcColNames.add(st.nextToken());
974                         if (st.hasMoreTokens()){
975                             st.nextToken();
976                         }
977                     }
978                     JdbcTable srcJdbcTable = (JdbcTable) jdbcTableMap.get(srcTableName);
979
980
981                     String doneName = srcTableName + constName;
982                     if (srcJdbcTable == null){
983                         doneMap.put(doneName, null);
984                         continue;
985                     } else if (doneMap.containsKey(doneName)){
986                         continue;
987                     } else {
988                         doneMap.put(doneName,null);
989                     }
990
991
992
993                     JdbcTable destJdbcTable = (JdbcTable) jdbcTableMap.get(destTableName);
994                     JdbcConstraint jdbcConstraint = new JdbcConstraint();
995                     jdbcConstraint.name = constName;
996                     jdbcConstraint.src = srcJdbcTable;
997                     jdbcConstraint.dest = destJdbcTable;
998                     JdbcColumn[] constraintColumns = new JdbcColumn[srcColNames.size()];
999                     int j = 0;
1000                    for (Iterator iter = srcColNames.iterator(); iter.hasNext();j++) {
1001                        String colName = (String ) iter.next();
1002                        for (int i = 0; i < srcJdbcTable.cols.length; i++) {
1003                            JdbcColumn jdbcColumn = srcJdbcTable.cols[i];
1004                            if (colName.equals(jdbcColumn.name)) {
1005                                constraintColumns[j] = jdbcColumn;
1006                                jdbcColumn.foreignKey = true;
1007                            }
1008                        }
1009                    }
1010                    jdbcConstraint.srcCols = constraintColumns;
1011                    if (constraintNameMap.containsKey(srcJdbcTable)){
1012                        ArrayList list = (ArrayList)constraintNameMap.get(srcJdbcTable);
1013                        list.add(jdbcConstraint);
1014                    } else {
1015                        ArrayList list = new ArrayList();
1016                        list.add(jdbcConstraint);
1017                        constraintNameMap.put(srcJdbcTable, list);
1018                    }
1019                }
1020                for (Iterator iter = constraintNameMap.keySet().iterator(); iter.hasNext();) {
1021                    JdbcTable jdbcTable = (JdbcTable) iter.next();
1022                    if (jdbcTable != null) {
1023                        ArrayList list = (ArrayList) constraintNameMap.get(jdbcTable);
1024                        JdbcConstraint[] jdbcConstraints = new JdbcConstraint[list.size()];
1025                        list.toArray(jdbcConstraints);
1026                        jdbcTable.constraints = jdbcConstraints;
1027                    }
1028                }
1029
1030                if (rsFKs != null) {
1031                    try {
1032                        rsFKs.close();
1033                    } catch (SQLException e) {
1034                    }
1035                }
1036                if (statFK != null) {
1037                    try {
1038                        statFK.close();
1039                    } catch (SQLException e) {
1040                    }
1041                }
1042            }
1043        }
1044
1045
1046        HashMap returnMap = new HashMap();
1047        Collection col = jdbcTableMap.values();
1048        for (Iterator iterator = col.iterator(); iterator.hasNext();) {
1049            JdbcTable table = (JdbcTable) iterator.next();
1050            returnMap.put(table.name.toLowerCase(), table);
1051        }
1052        fixAllNames(returnMap);
1053        return returnMap;
1054    }
1055
1056    public boolean haveMinimumServerVersion(String ver, Connection con) throws SQLException {
1057        return getVersion(con).compareTo(ver) >= 0;
1058    }
1059
1060
1061    public int getSQLType(String pgTypeName) {
1062        int sqlType = 1111;
1063        for (int i = 0; i < jdbc1Types.length; i++) {
1064            if (!pgTypeName.equals(jdbc1Types[i])) {
1065                continue;
1066            }
1067            sqlType = jdbc1Typei[i];
1068            break;
1069        }
1070
1071        return sqlType;
1072    }
1073
1074    /**
1075     * remember to init the type cash first.
1076     * @param oid
1077     * @return
1078     * @throws SQLException
1079     */
1080    public String getPGType(int oid) throws SQLException {
1081        String pgType = (String ) pgTypeCache.get(new Integer (oid));
1082        return pgType;
1083    }
1084
1085    /**
1086     * remember to init the type cash first.
1087     * @param oid
1088     * @return
1089     * @throws SQLException
1090     */
1091    public int getSQLType(int oid) throws SQLException {
1092        Integer sqlType = (Integer ) sqlTypeCache.get(new Integer (oid));
1093        return sqlType.intValue();
1094    }
1095
1096    private void initTypeCache(Connection con) throws SQLException {
1097        sqlTypeCache = new HashMap();
1098        pgTypeCache = new HashMap();
1099
1100        String sql;
1101        if (haveMinimumServerVersion("7.3", con)) {
1102            sql = "SELECT typname,oid FROM pg_catalog.pg_type ";
1103        } else {
1104            sql = "SELECT typname,oid FROM pg_type ";
1105        }
1106        Statement stat = con.createStatement();
1107        ResultSet rs = stat.executeQuery(sql);
1108        String pgType;
1109        while (rs.next()) {
1110            pgType = rs.getString(1);
1111            Integer iOid = new Integer (rs.getInt(2));
1112            Integer sqlType = new Integer (getSQLType(pgType));
1113            sqlTypeCache.put(iOid, sqlType);
1114            pgTypeCache.put(iOid, pgType);
1115        }
1116        // do last type
1117 pgType = "opaque";
1118        Integer iOid = new Integer (0);
1119        Integer sqlType = new Integer (getSQLType(pgType));
1120        sqlTypeCache.put(iOid, sqlType);
1121        pgTypeCache.put(iOid, pgType);
1122        if (rs != null) {
1123            try {
1124                rs.close();
1125            } catch (SQLException e) {
1126            }
1127        }
1128        if (stat != null) {
1129            try {
1130                stat.close();
1131            } catch (SQLException e) {
1132            }
1133        }
1134    }
1135
1136
1137
1138    /**
1139     * Append a column that needs to be added.
1140     */
1141    protected void appendAddNewColumn(JdbcTable t, JdbcColumn c,
1142                                      CharBuf s, boolean comments) {
1143        if (comments && isCommentSupported() && c.comment != null) {
1144            s.append(comment("add column for field " + c.comment));
1145        }
1146
1147        s.append("\n");
1148        if (isAddSequenceColumn(c)) {
1149            addSequenceColumn(t, c, s, comments);
1150        } else {
1151            s.append("ALTER TABLE ");
1152            s.append(t.name);
1153            s.append(" ADD ");
1154            s.append(c.name);
1155            s.append(' ');
1156            appendColumnType(c, s);
1157            s.append(getRunCommand());
1158            if (!c.nulls) {
1159                s.append("UPDATE ");
1160                s.append(t.name);
1161                s.append(" SET ");
1162                s.append(c.name);
1163                s.append(" = ");
1164                s.append(getDefaultForType(c));
1165                s.append(getRunCommand());
1166
1167                s.append("ALTER TABLE ");
1168                s.append(t.name);
1169                s.append(" ALTER ");
1170                s.append(c.name);
1171                s.append(" SET NOT NULL");
1172                s.append(getRunCommand());
1173            }
1174        }
1175    }
1176
1177
1178    /**
1179     * Append a column that needs to be added.
1180     */
1181    protected void appendModifyColumn(TableDiff tableDiff, ColumnDiff diff, CharBuf s,
1182                                      boolean comments) {
1183        JdbcTable t = tableDiff.getOurTable();
1184        JdbcColumn c = diff.getOurCol();
1185        boolean length = diff.isLenghtDiff();
1186        boolean scale = diff.isScaleDiff();
1187        boolean nulls = diff.isNullDiff();
1188        boolean type = diff.isTypeDiff();
1189        if (comments && isCommentSupported() && c.comment != null) {
1190            s.append(comment("modify column for field " + c.comment));
1191        }
1192        if (comments && isCommentSupported() && c.comment == null) {
1193            s.append(comment("modify column " + c.name));
1194        }
1195
1196        s.append("\n");
1197        if (length || scale || type){
1198            String tempcolumn = getTempColumnName(t);
1199            s.append("ALTER TABLE ");
1200            s.append(t.name);
1201            s.append(" RENAME ");
1202            s.append(c.name);
1203            s.append(" TO ");
1204            s.append(tempcolumn);
1205            s.append(getRunCommand());
1206
1207            s.append("ALTER TABLE ");
1208            s.append(t.name);
1209            s.append(" ADD ");
1210            s.append(c.name);
1211            s.append(' ');
1212            appendColumnType(c, s);
1213            s.append(getRunCommand());
1214
1215            s.append("UPDATE ");
1216            s.append(t.name);
1217            s.append(" SET ");
1218            s.append(c.name);
1219            s.append(" = ");
1220            s.append(tempcolumn);
1221            s.append("::");
1222            appendColumnType(c, s);
1223            s.append(getRunCommand());
1224
1225
1226            s.append("ALTER TABLE ");
1227            s.append(t.name);
1228            s.append(" DROP COLUMN ");
1229            s.append(tempcolumn);
1230
1231            if (!c.nulls) {
1232                s.append(getRunCommand());
1233                s.append("ALTER TABLE ");
1234                s.append(t.name);
1235                s.append(" ALTER COLUMN ");
1236                s.append(c.name);
1237                s.append(" SET NOT NULL");
1238            }
1239
1240        } else if (nulls){
1241            s.append("ALTER TABLE ");
1242            s.append(t.name);
1243            s.append(" ALTER COLUMN ");
1244            s.append(c.name);
1245            if (!c.nulls) {
1246                s.append(" SET NOT NULL");
1247            } else {
1248                s.append(" DROP NOT NULL");
1249            }
1250        }
1251
1252    }
1253
1254
1255    /**
1256     * Append a column that needs to be added.
1257     */
1258    protected void appendDropColumn(TableDiff tableDiff, JdbcColumn c,
1259                                    CharBuf s, boolean comments) {
1260        if (comments && isCommentSupported()) {
1261            s.append(comment("dropping unknown column " + c.name));
1262        }
1263        s.append("\n");
1264        if (isDropSequenceColumn(tableDiff, c)) {
1265            dropSequenceColumn(tableDiff.getOurTable(), c, s, comments);
1266        } else {
1267            s.append("ALTER TABLE ");
1268            s.append(tableDiff.getOurTable().name);
1269            s.append(" DROP COLUMN ");
1270            s.append(c.name);
1271        }
1272    }
1273
1274
1275    /**
1276     * Append an 'drop constraint' statement for c.
1277     */
1278    protected void appendRefDropConstraint(CharBuf s, JdbcConstraint c, boolean comments) {
1279// if (comments && isCommentSupported()) {
1280// s.append(comment("dropping unknown constraint " + c.name));
1281// s.append('\n');
1282// }
1283 s.append("ALTER TABLE ");
1284        s.append(c.src.name);
1285        s.append(" DROP CONSTRAINT ");
1286        s.append(c.name);
1287        if (dropTableRequiresCascade){
1288            s.append(" CASCADE");
1289        }
1290    }
1291
1292    /**
1293     * Generate a 'drop index' statement for idx.
1294     */
1295    protected void appendDropIndex(CharBuf s, JdbcTable t, JdbcIndex idx,
1296                                   boolean comments) {
1297// if (comments && isCommentSupported()) {
1298// s.append(comment("dropping unknown index "+ idx.name));
1299// s.append('\n');
1300// }
1301 s.append("DROP INDEX ");
1302        s.append(idx.name);
1303        if (dropTableRequiresCascade) {
1304            s.append(" CASCADE");
1305        }
1306    }
1307
1308    /**
1309     * Add the primary key constraint in isolation.
1310     */
1311    protected void addPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
1312        s.append("ALTER TABLE ");
1313        s.append(t.name);
1314        s.append(" ADD ");
1315        appendPrimaryKeyConstraint(t, s);
1316    }
1317
1318    /**
1319     * Drop the primary key constraint in isolation.
1320     */
1321    protected void dropPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
1322        s.append("ALTER TABLE ");
1323        s.append(t.name);
1324        s.append(" DROP CONSTRAINT ");
1325        s.append(t.pkConstraintName);
1326        if (dropTableRequiresCascade) {
1327            s.append(" CASCADE");
1328        }
1329    }
1330
1331    /**
1332     * Drop a Sequence column to implement a Set
1333     */
1334    protected void dropSequenceColumn(JdbcTable t, JdbcColumn c, CharBuf s, boolean comments) {
1335        String tempTableName = getTempTableName(t, 31);
1336
1337        s.append(comment("create a temp table to store old table values."));
1338        s.append("\n");
1339        s.append("CREATE TABLE ");
1340        s.append(tempTableName);
1341        s.append(" (\n");
1342        JdbcColumn[] cols = t.getColsForCreateTable();
1343        int nc = cols.length;
1344        boolean first = true;
1345        for (int i = 0; i < nc; i++) {
1346            if (first)
1347                first = false;
1348            else
1349                s.append("\n");
1350            s.append(" ");
1351            appendCreateColumn(t, cols[i], s, comments);
1352        }
1353        s.append("\n ");
1354        appendPrimaryKeyConstraint(t, s);
1355        s.append("\n)");
1356        s.append(getRunCommand());
1357
1358
1359        s.append(comment("insert a distinct list into the temp table."));
1360        s.append("\n");
1361        s.append("INSERT INTO ");
1362        s.append(tempTableName);
1363        s.append("(");
1364        for (int i = 0; i < nc; i++) {
1365            s.append(cols[i].name);
1366            if ((i + 1) != nc) {
1367                s.append(", ");
1368            }
1369        }
1370        s.append(")");
1371        s.append("\nSELECT DISTINCT ");
1372        for (int i = 0; i < nc; i++) {
1373            if (i != 0) {
1374                s.append("\n ");
1375            }
1376            s.append(cols[i].name);
1377            if ((i + 1) != nc) {
1378                s.append(", ");
1379            }
1380        }
1381        s.append("\n FROM ");
1382        s.append(t.name);
1383
1384        s.append(getRunCommand());
1385
1386
1387        s.append(comment("drop main table."));
1388        s.append("\n");
1389        s.append("DROP TABLE ");
1390        s.append(t.name);
1391        if (dropTableRequiresCascade) s.append(" CASCADE");
1392        s.append(getRunCommand());
1393
1394        s.append(comment("rename temp table to main table."));
1395        s.append("\n");
1396        s.append("ALTER TABLE ");
1397        s.append(tempTableName);
1398        s.append(" RENAME TO ");
1399        s.append(t.name);
1400
1401    }
1402
1403    /**
1404     * Add a Sequence column to implement a list
1405     */
1406    protected void addSequenceColumn(JdbcTable t, JdbcColumn c, CharBuf s, boolean comments) {
1407
1408        String mainTempTableName = getTempTableName(t, 31);
1409        String minTempTableName = getTempTableName(t, 31);
1410        String identityColumnName = getTempColumnName(t);
1411
1412
1413        JdbcColumn indexColumn = null;
1414        JdbcColumn sequenceColumn = null;
1415        JdbcColumn[] cols = t.getColsForCreateTable();
1416        int nc = cols.length;
1417        for (int i = 0; i < nc; i++) {
1418            if (isAddSequenceColumn(cols[i])) {
1419                sequenceColumn = cols[i];
1420            } else if (t.isInPrimaryKey(cols[i].name)) {
1421                indexColumn = cols[i];
1422            }
1423        }
1424
1425
1426        s.append(comment("Generate a sequence number so that we can implement a List."));
1427        s.append("\n");
1428        s.append(comment("create a temp table with a extra identity column."));
1429        s.append("\n");
1430        s.append("CREATE TABLE ");
1431        s.append(mainTempTableName);
1432        s.append(" (\n ");
1433        // create identity column
1434 s.append(identityColumnName);
1435        s.append(" SERIAL,");
1436        for (int i = 0; i < nc; i++) {
1437            s.append("\n ");
1438            appendCreateColumn(t, cols[i], s, comments);
1439        }
1440        int lastIndex = s.toString().lastIndexOf(',');
1441        s.replace(lastIndex, lastIndex + 1, ' ');// we take the last ',' out.
1442 s.append("\n)");
1443
1444
1445        s.append(getRunCommand());
1446
1447
1448        s.append(comment("insert a '0' in the sequence column and copy the rest of the old table into the temp table."));
1449        s.append("\n");
1450        s.append("INSERT INTO ");
1451        s.append(mainTempTableName);
1452        s.append("(");
1453        for (int i = 0; i < nc; i++) {
1454            s.append(cols[i].name);
1455            if ((i + 1) != nc) {
1456                s.append(", ");
1457            }
1458        }
1459        s.append(")");
1460        s.append("\nSELECT ");
1461        for (int i = 0; i < nc; i++) {
1462            if (i != 0) {
1463                s.append("\n ");
1464            }
1465            if (isAddSequenceColumn(cols[i])) {
1466                s.append('0');
1467            } else {
1468                s.append(cols[i].name);
1469            }
1470            if ((i + 1) != nc) {
1471                s.append(", ");
1472            }
1473        }
1474        s.append("\n FROM ");
1475        s.append(t.name);
1476        s.append("\n ORDER BY ");
1477        s.append(indexColumn.name);
1478
1479
1480        s.append(getRunCommand());
1481
1482
1483        s.append(comment("create a temp table to store the minimum id."));
1484        s.append("\n");
1485        s.append("CREATE TABLE ");
1486        s.append(minTempTableName);
1487        s.append(" (\n ");
1488        s.append(indexColumn.name);
1489        s.append(' ');
1490        appendColumnType(indexColumn, s);
1491        appendCreateColumnNulls(t, indexColumn, s);
1492        s.append(",\n ");
1493        s.append("min_id");
1494        s.append(" INTEGER\n)");
1495
1496
1497        s.append(getRunCommand());
1498
1499
1500        s.append(comment("store the minimum id."));
1501        s.append("\n");
1502        s.append("INSERT INTO ");
1503        s.append(minTempTableName);
1504        s.append(" (");
1505        s.append(indexColumn.name);
1506        s.append(", ");
1507        s.append("min_id");
1508        s.append(")\n");
1509        s.append("SELECT ");
1510        s.append(indexColumn.name);
1511        s.append(",\n ");
1512        s.append("MIN(");
1513        s.append(identityColumnName);
1514        s.append(")\n");
1515        s.append(" FROM ");
1516        s.append(mainTempTableName);
1517        s.append("\n");
1518        s.append(" GROUP BY ");
1519        s.append(indexColumn.name);
1520
1521
1522        s.append(getRunCommand());
1523
1524
1525        s.append(comment("drop main table " + t.name + "."));
1526        s.append("\n");
1527        s.append("DROP TABLE ");
1528        s.append(t.name);
1529        if (dropTableRequiresCascade) s.append(" CASCADE");
1530
1531        s.append(getRunCommand());
1532
1533
1534        s.append(comment("recreate table " + t.name + "."));
1535        s.append("\n");
1536        s.append("CREATE TABLE ");
1537        s.append(t.name);
1538        s.append(" (\n");
1539        boolean first = true;
1540        for (int i = 0; i < nc; i++) {
1541            if (first)
1542                first = false;
1543            else
1544                s.append("\n");
1545            s.append(" ");
1546            appendCreateColumn(t, cols[i], s, comments);
1547        }
1548        s.append("\n ");
1549        appendPrimaryKeyConstraint(t, s);
1550        s.append("\n)");
1551        appendTableType(t, s);
1552
1553
1554        s.append(getRunCommand());
1555
1556        s.append(comment("populate table " + t.name + " with the new sequence column."));
1557        s.append("\n");
1558        s.append("INSERT INTO ");
1559        s.append(t.name);
1560        s.append("(");
1561        for (int i = 0; i < nc; i++) {
1562            s.append(cols[i].name);
1563            if ((i + 1) != nc) {
1564                s.append(", ");
1565            }
1566        }
1567        s.append(")");
1568        s.append("\nSELECT ");
1569        for (int i = 0; i < nc; i++) {
1570            if (i != 0) {
1571                s.append("\n ");
1572            }
1573
1574            if (isAddSequenceColumn(cols[i])) {
1575                s.append("(a.");
1576                s.append(identityColumnName);
1577                s.append(" - b.min_id)");
1578            } else {
1579                s.append("a.");
1580                s.append(cols[i].name);
1581            }
1582
1583            if ((i + 1) != nc) {
1584                s.append(", ");
1585            }
1586        }
1587        s.append("\n FROM ");
1588        s.append(mainTempTableName);
1589        s.append(" a,\n ");
1590        s.append(minTempTableName);
1591        s.append(" b\n WHERE a.");
1592        s.append(indexColumn.name);
1593        s.append(" = b.");
1594        s.append(indexColumn.name);
1595
1596
1597        s.append(getRunCommand());
1598
1599
1600        s.append(comment("drop temp tables."));
1601        s.append("\n");
1602        s.append("DROP TABLE ");
1603        s.append(mainTempTableName);
1604        if (dropTableRequiresCascade) s.append(" CASCADE");
1605        s.append(getRunCommand());
1606
1607        if (!dropTableRequiresCascade) {
1608            s.append(comment("drop sequence."));
1609            s.append("\n");
1610            s.append("DROP SEQUENCE ");
1611            s.append(mainTempTableName);
1612            s.append("_");
1613            s.append(identityColumnName);
1614            s.append("_seq");
1615            s.append(getRunCommand());
1616        }
1617
1618        s.append("DROP TABLE ");
1619        s.append(minTempTableName);
1620        if (dropTableRequiresCascade) s.append(" CASCADE");
1621        s.append(getRunCommand());
1622    }
1623
1624
1625        /*
1626CREATE TABLE temp AS SELECT * FROM distributors;
1627DROP TABLE distributors;
1628CREATE TABLE distributors AS SELECT * FROM temp;
1629DROP TABLE temp;*/
1630// protected void fixColumnsNonDirect (TableDiff tableDiff, PrintWriter out) {
1631//
1632// JdbcTable ourTable = tableDiff.getOurTable();
1633// String tempTableName = getTempTableName(ourTable, 128);
1634//
1635//
1636// CharBuf s = new CharBuf();
1637// s.append("CREATE TABLE ");
1638// s.append(tempTableName); //ourTable.name
1639// s.append(" (");
1640// JdbcColumn[] cols = ourTable.getColsForCreateTable();
1641// int nc = cols.length;
1642// for (int i = 0; i < nc; i++) {
1643// appendCreateColumn(ourTable, cols[i], s, false);
1644// s.append(' ');
1645// }
1646// appendPrimaryKeyConstraint(ourTable, s);
1647// s.append(")");
1648// s.append(getRunCommand());
1649//
1650//
1651// s.append("INSERT INTO ");
1652// s.append(tempTableName); //ourTable.name
1653// s.append(" (");
1654// for (int i = 0; i < nc; i++) {
1655// s.append(cols[i].name);
1656// if ((i + 1) != nc) {
1657// s.append(", ");
1658// }
1659// }
1660// s.append(") ");
1661//
1662// s.append("\n");//new line
1663//
1664// s.append("SELECT ");
1665// for (int i = 0; i < nc; i++) {
1666// ColumnDiff diff = getColumnDiffForName(tableDiff, cols[i].name);
1667// if (diff == null) {
1668// if (i != 0) {
1669// s.append(" ");
1670// }
1671// s.append(cols[i].name);
1672// } else {
1673// if (diff.isMissingCol()) {
1674// if (diff.getOurCol().nulls) {
1675// if (i != 0) {
1676// s.append(" ");
1677// }
1678// s.append("CAST(NULL");
1679// s.append(" AS ");
1680// appendColumnType(cols[i], s);
1681// s.append(")");
1682//
1683// } else {
1684// if (i != 0) {
1685// s.append(" ");
1686// }
1687// s.append(getDefaultForType(diff.getOurCol()));
1688// }
1689//
1690// } else if ((diff.isLenghtDiff() || diff.isScaleDiff() || diff.isTypeDiff()) && diff.isNullDiff()) {
1691// if (cols[i].nulls) {
1692// if (i != 0) {
1693// s.append(" ");
1694// }
1695// s.append("CAST(");
1696// s.append(cols[i].name);
1697// s.append(" AS ");
1698// appendColumnType(cols[i], s);
1699// s.append(")");
1700// } else {
1701// if (i != 0) {
1702// s.append(" ");
1703// }
1704// s.append("CASE ");
1705// s.append("\n");//new line
1706// s.append(" WHEN ");
1707// s.append(cols[i].name);
1708// s.append(" IS NOT NULL THEN CAST(");
1709// s.append(cols[i].name);
1710// s.append(" AS ");
1711// appendColumnType(cols[i], s);
1712// s.append(")");
1713// s.append("\n");//new line
1714// s.append(" ELSE CAST(");
1715// s.append(getDefaultForType(diff.getOurCol()));
1716// s.append(" AS ");
1717// appendColumnType(cols[i], s);
1718// s.append(")");
1719// s.append("\n");//new line
1720// s.append(" END CASE");
1721// }
1722//
1723// } else if ((diff.isLenghtDiff() || diff.isScaleDiff() || diff.isTypeDiff()) && !diff.isNullDiff()) {
1724// if (i != 0) {
1725// s.append(" ");
1726// }
1727// s.append("CAST(");
1728// s.append(cols[i].name);
1729// s.append(" AS ");
1730// appendColumnType(cols[i], s);
1731// s.append(")");
1732// } else if (diff.isNullDiff()) {
1733// if (cols[i].nulls) {
1734// if (i != 0) {
1735// s.append(" ");
1736// }
1737// s.append(cols[i].name);
1738// } else {
1739// if (i != 0) {
1740// s.append(" ");
1741// }
1742// s.append("CASE ");
1743// s.append("\n");//new line
1744// s.append(" WHEN ");
1745// s.append(cols[i].name);
1746// s.append(" IS NOT NULL THEN ");
1747// s.append(cols[i].name);
1748// s.append("\n");//new line
1749// s.append(" ELSE ");
1750// s.append(getDefaultForType(diff.getOurCol()));
1751// s.append("\n");//new line
1752// s.append(" END CASE");
1753// }
1754// }
1755// }
1756//
1757//
1758// if ((i + 1) != nc) {
1759// s.append(", ");
1760// s.append("\n");//new line
1761// }
1762// }
1763// s.append("\n");//new line
1764// s.append(" FROM ");
1765// s.append(ourTable.name);
1766// s.append(getRunCommand());
1767//
1768//
1769// s.append("DROP TABLE ");
1770// s.append(ourTable.name);
1771// s.append(getRunCommand());
1772//
1773// s.append("RENAME TABLE ");
1774// s.append(tempTableName);
1775// s.append(" TO ");
1776// s.append(ourTable.name);
1777// s.append(getRunCommand());
1778//
1779// out.println(s.toString());
1780//
1781//
1782// }
1783
1784}
1785

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags