001: package liquibase.database.structure;
002:
003: import liquibase.database.AbstractDatabase;
004: import liquibase.database.Database;
005: import liquibase.database.OracleDatabase;
006: import liquibase.diff.DiffStatusListener;
007: import liquibase.exception.JDBCException;
008: import liquibase.log.LogFactory;
009: import liquibase.util.StringUtils;
010:
011: import java.sql.DatabaseMetaData;
012: import java.sql.ResultSet;
013: import java.sql.SQLException;
014: import java.sql.Statement;
015: import java.text.ParseException;
016: import java.util.*;
017: import java.util.logging.Logger;
018:
019: public class DatabaseSnapshot {
020:
021: private DatabaseMetaData databaseMetaData;
022: private Database database;
023:
024: private Set<Table> tables = new HashSet<Table>();
025: private Set<View> views = new HashSet<View>();
026: private Set<Column> columns = new HashSet<Column>();
027: private Set<ForeignKey> foreignKeys = new HashSet<ForeignKey>();
028: private Set<Index> indexes = new HashSet<Index>();
029: private Set<PrimaryKey> primaryKeys = new HashSet<PrimaryKey>();
030: private Set<Sequence> sequences = new HashSet<Sequence>();
031:
032: private Map<String, Table> tablesMap = new HashMap<String, Table>();
033: private Map<String, View> viewsMap = new HashMap<String, View>();
034: private Map<String, Column> columnsMap = new HashMap<String, Column>();
035: private Set<DiffStatusListener> statusListeners;
036:
037: private static final Logger log = LogFactory.getLogger();
038:
039: /**
040: * Creates an empty database snapshot
041: */
042: public DatabaseSnapshot() {
043: }
044:
045: /**
046: * Creates a snapshot of the given database with no status listeners
047: */
048: public DatabaseSnapshot(Database database) throws JDBCException {
049: this (database, null, null);
050: }
051:
052: /**
053: * Creates a snapshot of the given database with no status listeners
054: */
055: public DatabaseSnapshot(Database database, String schema)
056: throws JDBCException {
057: this (database, null, schema);
058: }
059:
060: /**
061: * Creates a snapshot of the given database.
062: */
063: public DatabaseSnapshot(Database database,
064: Set<DiffStatusListener> statusListeners)
065: throws JDBCException {
066: this (database, statusListeners, database.getDefaultSchemaName());
067: }
068:
069: /**
070: * Creates a snapshot of the given database.
071: */
072: public DatabaseSnapshot(Database database,
073: Set<DiffStatusListener> statusListeners,
074: String requestedSchema) throws JDBCException {
075: try {
076: this .database = database;
077: this .databaseMetaData = database.getConnection()
078: .getMetaData();
079: this .statusListeners = statusListeners;
080:
081: readTablesAndViews(requestedSchema);
082: readForeignKeyInformation(requestedSchema);
083: readPrimaryKeys(requestedSchema);
084: readColumns(requestedSchema);
085: // readUniqueConstraints(catalog, schema);
086: readIndexes(requestedSchema);
087: readSequences(requestedSchema);
088:
089: this .tables = new HashSet<Table>(tablesMap.values());
090: this .views = new HashSet<View>(viewsMap.values());
091: this .columns = new HashSet<Column>(columnsMap.values());
092: } catch (SQLException e) {
093: throw new JDBCException(e);
094: }
095: }
096:
097: public Database getDatabase() {
098: return database;
099: }
100:
101: public Set<Table> getTables() {
102: return tables;
103: }
104:
105: public Set<View> getViews() {
106: return views;
107: }
108:
109: public Column getColumn(Column column) {
110: if (column.getTable() == null) {
111: return columnsMap.get(column.getView().getName() + "."
112: + column.getName());
113: } else {
114: return columnsMap.get(column.getTable().getName() + "."
115: + column.getName());
116: }
117: }
118:
119: public Column getColumn(String tableName, String columnName) {
120: return columnsMap.get(tableName + "." + columnName);
121: }
122:
123: public Set<Column> getColumns() {
124: return columns;
125: }
126:
127: public Set<ForeignKey> getForeignKeys() {
128: return foreignKeys;
129: }
130:
131: public Set<Index> getIndexes() {
132: return indexes;
133: }
134:
135: public Set<PrimaryKey> getPrimaryKeys() {
136: return primaryKeys;
137: }
138:
139: public Set<Sequence> getSequences() {
140: return sequences;
141: }
142:
143: private void readTablesAndViews(String schema) throws SQLException,
144: JDBCException {
145: updateListeners("Reading tables for " + database.toString()
146: + " ...");
147: ResultSet rs = databaseMetaData.getTables(database
148: .convertRequestedSchemaToCatalog(schema), database
149: .convertRequestedSchemaToSchema(schema), null,
150: new String[] { "TABLE", "VIEW" });
151: while (rs.next()) {
152: String type = rs.getString("TABLE_TYPE");
153: String name = rs.getString("TABLE_NAME");
154: String schemaName = rs.getString("TABLE_SCHEM");
155: String catalogName = rs.getString("TABLE_CAT");
156: String remarks = rs.getString("REMARKS");
157:
158: if (database.isSystemTable(catalogName, schemaName, name)
159: || database.isLiquibaseTable(name)
160: || database.isSystemView(catalogName, schemaName,
161: name)) {
162: continue;
163: }
164:
165: if ("TABLE".equals(type)) {
166: Table table = new Table(name);
167: table.setRemarks(StringUtils.trimToNull(remarks));
168: table.setDatabase(database);
169: tablesMap.put(name, table);
170: } else if ("VIEW".equals(type)) {
171: View view = new View();
172: view.setName(name);
173: try {
174: view.setDefinition(database.getViewDefinition(
175: schema, name));
176: } catch (JDBCException e) {
177: System.out
178: .println("Error getting view with "
179: + ((AbstractDatabase) database)
180: .getViewDefinitionSql(
181: schema, name));
182: throw e;
183: }
184:
185: viewsMap.put(name, view);
186:
187: }
188: }
189: rs.close();
190:
191: /* useful for troubleshooting table reading */
192: // if (tablesMap.size() == 0) {
193: // System.out.println("No tables found, all tables:");
194: //
195: // String convertedCatalog = database.convertRequestedSchemaToCatalog(schema);
196: // String convertedSchema = database.convertRequestedSchemaToSchema(schema);
197: //
198: // System.out.println("Tried: "+convertedCatalog+"."+convertedSchema);
199: // convertedCatalog = null;
200: // convertedSchema = null;
201: //
202: // rs = databaseMetaData.getTables(convertedCatalog, convertedSchema, null, new String[]{"TABLE", "VIEW"});
203: // while (rs.next()) {
204: // String type = rs.getString("TABLE_TYPE");
205: // String name = rs.getString("TABLE_NAME");
206: // String schemaName = rs.getString("TABLE_SCHEM");
207: // String catalogName = rs.getString("TABLE_CAT");
208: //
209: // if (database.isSystemTable(catalogName, schemaName, name) || database.isLiquibaseTable(name) || database.isSystemView(catalogName, schemaName, name)) {
210: // continue;
211: // }
212: //
213: // System.out.println(catalogName+"."+schemaName+"."+name+":"+type);
214: //
215: // }
216: // rs.close();
217: // }
218: }
219:
220: private void readColumns(String schema) throws SQLException,
221: JDBCException {
222: updateListeners("Reading columns for " + database.toString()
223: + " ...");
224:
225: Statement selectStatement = database.getConnection()
226: .createStatement();
227: ResultSet rs = databaseMetaData.getColumns(database
228: .convertRequestedSchemaToCatalog(schema), database
229: .convertRequestedSchemaToSchema(schema), null, null);
230: while (rs.next()) {
231: Column columnInfo = new Column();
232:
233: String tableName = rs.getString("TABLE_NAME");
234: String columnName = rs.getString("COLUMN_NAME");
235: String schemaName = rs.getString("TABLE_SCHEM");
236: String catalogName = rs.getString("TABLE_CAT");
237:
238: if (database.isSystemTable(catalogName, schemaName,
239: tableName)
240: || database.isLiquibaseTable(tableName)) {
241: continue;
242: }
243:
244: Table table = tablesMap.get(tableName);
245: if (table == null) {
246: View view = viewsMap.get(tableName);
247: if (view == null) {
248: log.info("Could not find table or view "
249: + tableName + " for column " + columnName);
250: continue;
251: } else {
252: columnInfo.setView(view);
253: view.getColumns().add(columnInfo);
254: }
255: } else {
256: columnInfo.setTable(table);
257: table.getColumns().add(columnInfo);
258: }
259:
260: columnInfo.setName(columnName);
261: columnInfo.setDataType(rs.getInt("DATA_TYPE"));
262: columnInfo.setColumnSize(rs.getInt("COLUMN_SIZE"));
263: columnInfo.setDecimalDigits(rs.getInt("DECIMAL_DIGITS"));
264: Object defaultValue = rs.getObject("COLUMN_DEF");
265: try {
266: columnInfo.setDefaultValue(database
267: .convertDatabaseValueToJavaObject(defaultValue,
268: columnInfo.getDataType(), columnInfo
269: .getColumnSize(), columnInfo
270: .getDecimalDigits()));
271: } catch (ParseException e) {
272: throw new JDBCException(e);
273: }
274:
275: int nullable = rs.getInt("NULLABLE");
276: if (nullable == DatabaseMetaData.columnNoNulls) {
277: columnInfo.setNullable(false);
278: } else if (nullable == DatabaseMetaData.columnNullable) {
279: columnInfo.setNullable(true);
280: }
281:
282: columnInfo.setPrimaryKey(isPrimaryKey(columnInfo));
283:
284: columnInfo.setAutoIncrement(database.isColumnAutoIncrement(
285: schema, tableName, columnName));
286:
287: columnInfo.setTypeName(database.getColumnType(rs
288: .getString("TYPE_NAME"), columnInfo
289: .isAutoIncrement()));
290:
291: columnsMap.put(tableName + "." + columnName, columnInfo);
292: }
293: rs.close();
294: selectStatement.close();
295: }
296:
297: private boolean isPrimaryKey(Column columnInfo) {
298: for (PrimaryKey pk : getPrimaryKeys()) {
299: if (columnInfo.getTable() == null) {
300: continue;
301: }
302: if (pk.getTable().getName().equalsIgnoreCase(
303: columnInfo.getTable().getName())) {
304: if (pk.getColumnNamesAsList().contains(
305: columnInfo.getName())) {
306: return true;
307: }
308: }
309: }
310:
311: return false;
312: }
313:
314: private void readForeignKeyInformation(String schema)
315: throws JDBCException, SQLException {
316: updateListeners("Reading foreign keys for "
317: + database.toString() + " ...");
318:
319: for (Table table : tablesMap.values()) {
320: String dbCatalog = database
321: .convertRequestedSchemaToCatalog(schema);
322: String dbSchema = database
323: .convertRequestedSchemaToSchema(schema);
324: ResultSet rs = databaseMetaData.getExportedKeys(dbCatalog,
325: dbSchema, table.getName());
326: ForeignKey fkInfo = null;
327: while (rs.next()) {
328: String pkTableName = rs.getString("PKTABLE_NAME");
329: String pkColumn = rs.getString("PKCOLUMN_NAME");
330: Table pkTable = tablesMap.get(pkTableName);
331: if (pkTable == null) {
332: throw new JDBCException("Could not find table "
333: + pkTableName + " for column " + pkColumn);
334: }
335: int keySeq = rs.getInt("KEY_SEQ");
336: //Simple (non-composite) keys have KEY_SEQ=1, so create the ForeignKey.
337: //In case of subsequent parts of composite keys (KEY_SEQ>1) don't create new instance, just reuse the one from previous call.
338: //According to #getExportedKeys() contract, the result set rows are properly sorted, so the reuse of previous FK instance is safe.
339: if (keySeq == 1) {
340: fkInfo = new ForeignKey();
341: }
342:
343: fkInfo.setPrimaryKeyTable(pkTable);
344: fkInfo.addPrimaryKeyColumn(pkColumn);
345:
346: String fkTableName = rs.getString("FKTABLE_NAME");
347: String fkColumn = rs.getString("FKCOLUMN_NAME");
348: Table fkTable = tablesMap.get(fkTableName);
349: if (fkTable == null) {
350: throw new JDBCException("Could not find table "
351: + fkTableName + " for column " + fkColumn);
352: }
353: fkInfo.setForeignKeyTable(fkTable);
354: fkInfo.addForeignKeyColumn(fkColumn);
355:
356: fkInfo.setName(rs.getString("FK_NAME"));
357:
358: if (database.supportsInitiallyDeferrableColumns()) {
359: short deferrablility = rs.getShort("DEFERRABILITY");
360: if (deferrablility == DatabaseMetaData.importedKeyInitiallyDeferred) {
361: fkInfo.setDeferrable(Boolean.TRUE);
362: fkInfo.setInitiallyDeferred(Boolean.TRUE);
363: } else if (deferrablility == DatabaseMetaData.importedKeyInitiallyImmediate) {
364: fkInfo.setDeferrable(Boolean.TRUE);
365: fkInfo.setInitiallyDeferred(Boolean.FALSE);
366: } else if (deferrablility == DatabaseMetaData.importedKeyNotDeferrable) {
367: fkInfo.setDeferrable(Boolean.FALSE);
368: fkInfo.setInitiallyDeferred(Boolean.FALSE);
369: }
370: }
371:
372: //Add only if the key was created in this iteration (updating the instance values changes hashCode so it cannot be re-inserted into set)
373: if (keySeq == 1) {
374: foreignKeys.add(fkInfo);
375: }
376: }
377:
378: rs.close();
379: }
380: }
381:
382: private void readIndexes(String schema) throws JDBCException,
383: SQLException {
384: updateListeners("Reading indexes for " + database.toString()
385: + " ...");
386:
387: for (Table table : tablesMap.values()) {
388: ResultSet rs;
389: Statement statement = null;
390: if (database instanceof OracleDatabase) {
391: //oracle getIndexInfo is buggy and slow. See Issue 1824548 and http://forums.oracle.com/forums/thread.jspa?messageID=578383򍍏
392: statement = database.getConnection().createStatement();
393: String sql = "SELECT INDEX_NAME, 3 AS TYPE, TABLE_NAME, COLUMN_NAME, COLUMN_POSITION AS ORDINAL_POSITION, null AS FILTER_CONDITION FROM ALL_IND_COLUMNS WHERE TABLE_OWNER='"
394: + database
395: .convertRequestedSchemaToSchema(schema)
396: + "' AND TABLE_NAME='"
397: + table.getName()
398: + "' ORDER BY INDEX_NAME, ORDINAL_POSITION";
399: rs = statement.executeQuery(sql);
400: } else {
401: rs = databaseMetaData
402: .getIndexInfo(
403: database
404: .convertRequestedSchemaToCatalog(schema),
405: database
406: .convertRequestedSchemaToSchema(schema),
407: table.getName(), false, true);
408: }
409: Map<String, Index> indexMap = new HashMap<String, Index>();
410: while (rs.next()) {
411: String indexName = rs.getString("INDEX_NAME");
412: short type = rs.getShort("TYPE");
413: // String tableName = rs.getString("TABLE_NAME");
414: String columnName = rs.getString("COLUMN_NAME");
415: short position = rs.getShort("ORDINAL_POSITION");
416: String filterCondition = rs
417: .getString("FILTER_CONDITION");
418:
419: if (type == DatabaseMetaData.tableIndexStatistic) {
420: continue;
421: }
422:
423: if (columnName == null) {
424: //nothing to index, not sure why these come through sometimes
425: continue;
426: }
427: Index indexInformation;
428: if (indexMap.containsKey(indexName)) {
429: indexInformation = indexMap.get(indexName);
430: } else {
431: indexInformation = new Index();
432: indexInformation.setTable(table);
433: indexInformation.setName(indexName);
434: indexInformation
435: .setFilterCondition(filterCondition);
436: indexMap.put(indexName, indexInformation);
437: }
438: indexInformation.getColumns().add(position - 1,
439: columnName);
440: }
441: for (Map.Entry<String, Index> entry : indexMap.entrySet()) {
442: indexes.add(entry.getValue());
443: }
444: rs.close();
445: if (statement != null) {
446: statement.close();
447: }
448: }
449:
450: Set<Index> indexesToRemove = new HashSet<Index>();
451: //remove PK indexes
452: for (Index index : indexes) {
453: for (PrimaryKey pk : primaryKeys) {
454: if (index.getTable().getName().equalsIgnoreCase(
455: pk.getTable().getName())
456: && index.getColumnNames().equals(
457: pk.getColumnNames())) {
458: indexesToRemove.add(index);
459: }
460: }
461: }
462: indexes.removeAll(indexesToRemove);
463: }
464:
465: private void readPrimaryKeys(String schema) throws JDBCException,
466: SQLException {
467: updateListeners("Reading primary keys for "
468: + database.toString() + " ...");
469:
470: //we can't add directly to the this.primaryKeys hashSet because adding columns to an exising PK changes the hashCode and .contains() fails
471: List<PrimaryKey> foundPKs = new ArrayList<PrimaryKey>();
472:
473: for (Table table : tablesMap.values()) {
474: ResultSet rs = databaseMetaData.getPrimaryKeys(database
475: .convertRequestedSchemaToCatalog(schema), database
476: .convertRequestedSchemaToSchema(schema), table
477: .getName());
478:
479: while (rs.next()) {
480: String tableName = rs.getString("TABLE_NAME");
481: String columnName = rs.getString("COLUMN_NAME");
482: short position = rs.getShort("KEY_SEQ");
483:
484: boolean foundExistingPK = false;
485: for (PrimaryKey pk : foundPKs) {
486: if (pk.getTable().getName().equals(tableName)) {
487: pk.addColumnName(position - 1, columnName);
488:
489: foundExistingPK = true;
490: }
491: }
492:
493: if (!foundExistingPK) {
494: PrimaryKey primaryKey = new PrimaryKey();
495: primaryKey.setTable(table);
496: primaryKey.addColumnName(position - 1, columnName);
497: primaryKey.setName(rs.getString("PK_NAME"));
498:
499: foundPKs.add(primaryKey);
500: }
501: }
502:
503: rs.close();
504: }
505:
506: this .primaryKeys.addAll(foundPKs);
507: }
508:
509: // private void readUniqueConstraints(String catalog, String schema) throws JDBCException, SQLException {
510: // updateListeners("Reading unique constraints for " + database.toString() + " ...");
511: //
512: // //noinspection unchecked
513: // List<String> sequenceNamess = (List<String>) new JdbcTemplate(database).queryForList(database.findUniqueConstraints(schema), String.class);
514: //
515: // for (String sequenceName : sequenceNamess) {
516: // Sequence seq = new Sequence();
517: // seq.setName(sequenceName);
518: //
519: // sequences.add(seq);
520: // }
521: // }
522:
523: private void readSequences(String schema) throws JDBCException {
524: updateListeners("Reading sequences for " + database.toString()
525: + " ...");
526:
527: if (database.supportsSequences()) {
528: //noinspection unchecked
529: List<String> sequenceNamess = (List<String>) database
530: .getJdbcTemplate().queryForList(
531: database.createFindSequencesSQL(schema),
532: String.class);
533:
534: for (String sequenceName : sequenceNamess) {
535: Sequence seq = new Sequence();
536: seq.setName(sequenceName.trim());
537:
538: sequences.add(seq);
539: }
540: }
541: }
542:
543: private void updateListeners(String message) {
544: if (this .statusListeners == null) {
545: return;
546: }
547: log.finest(message);
548: for (DiffStatusListener listener : this .statusListeners) {
549: listener.statusUpdate(message);
550: }
551: }
552:
553: /**
554: * Returns the table object for the given tableName. If table does not exist, returns null
555: */
556: public Table getTable(String tableName) {
557: for (Table table : getTables()) {
558: if (table.getName().equalsIgnoreCase(tableName)) {
559: return table;
560: }
561: }
562: return null;
563: }
564:
565: public ForeignKey getForeignKey(String foreignKeyName) {
566: for (ForeignKey fk : getForeignKeys()) {
567: if (fk.getName().equalsIgnoreCase(foreignKeyName)) {
568: return fk;
569: }
570: }
571: return null;
572: }
573:
574: public Sequence getSequence(String sequenceName) {
575: for (Sequence sequence : getSequences()) {
576: if (sequence.getName().equalsIgnoreCase(sequenceName)) {
577: return sequence;
578: }
579: }
580: return null;
581: }
582:
583: public Index getIndex(String indexName) {
584: for (Index index : getIndexes()) {
585: if (index.getName().equalsIgnoreCase(indexName)) {
586: return index;
587: }
588: }
589: return null;
590: }
591:
592: public View getView(String viewName) {
593: for (View view : getViews()) {
594: if (view.getName().equalsIgnoreCase(viewName)) {
595: return view;
596: }
597: }
598: return null;
599: }
600:
601: public PrimaryKey getPrimaryKey(String pkName) {
602: for (PrimaryKey pk : getPrimaryKeys()) {
603: if (pk.getName().equalsIgnoreCase(pkName)) {
604: return pk;
605: }
606: }
607: return null;
608: }
609: }
|