CustomSqlChange: how access database metadata ?

General Discussion
1

Hi,

For version 2 of liquibase, I wrote a CustomSqlChange accessing java.sql.DatabaseMetaData in order to get the list of constraints :

Set constraints = SnapshotGeneratorFactory.getInstance()
.createSnapshot(null, database).getDatabaseMetaData().getUniqueConstraints();


But with version 3, this is not possible anymore and so I’m unable to upgrade.

How could I get access to the constraint list with the new API?

Thanks

2

Edit: I was accessing liquibase.snapshot.DatabaseSnapshot and not java.sql.DatabaseMetaData.

3

In both, CustomSqlChange is passed a Database object. Were you creating a snapshot from that before and are wondering how to create a snapshot now in 3.0?

Nathan

4

Actually I want to get the name of a unique constraint by specifying the table and the column name.

With table and column information my CustomSqlChange was able to drop an unique constraint without specifying its name.
Useful when you have generated names that are diffrernt from an instance to another.


5

Implementation that was wiorking witth liquibase 2 :

  1. import liquibase.change.core.DropUniqueConstraintChange;
    import liquibase.change.custom.CustomSqlChange;
    import liquibase.database.Database;
    import liquibase.database.structure.UniqueConstraint;
    import liquibase.exception.CustomChangeException;
    import liquibase.exception.DatabaseException;
    import liquibase.exception.SetupException;
    import liquibase.exception.ValidationErrors;
    import liquibase.resource.ResourceAccessor;
    import liquibase.snapshot.DatabaseSnapshotGeneratorFactory;
    import liquibase.statement.SqlStatement;

    import java.util.Arrays;
    import java.util.HashSet;
    import java.util.Set;

    public class DropUnknownUniqueConstraint implements CustomSqlChange {
        private final DropUniqueConstraintChange change = new DropUniqueConstraintChange();

        private Set columnNames;

        public void setTableName(String tableName) {
            change.setTableName(tableName);
        }

        public void setUniqueColumns(String uniqueColumns) {
            this.change.setUniqueColumns(uniqueColumns);
            columnNames = new HashSet<>(Arrays.asList(uniqueColumns.split(",")));
        }

        @Override
        public String getConfirmationMessage() {
            return change.getConfirmationMessage();
        }

        @Override
        public void setUp() throws SetupException {
        }

        @Override
        public void setFileOpener(ResourceAccessor resourceAccessor) {
        }

        @Override
        public ValidationErrors validate(Database database) {
            return new ValidationErrors();
        }

        @Override
        public SqlStatement[] generateStatements(Database database) throws CustomChangeException {
            Set constraints;
            try {
                constraints = DatabaseSnapshotGeneratorFactory.getInstance()
                        .createSnapshot(database, null, null).getUniqueConstraints();
            } catch (DatabaseException e) {
                throw new CustomChangeException(e.getMessage(), e);
            }
            UniqueConstraint constraintToRemove = null;
            for (UniqueConstraint constraint : constraints) {
                if (constraint.getTable().getName().equals(change.getTableName())) {
                    Set columns = new HashSet<>(constraint.getColumns());
                    if (columns.equals(columnNames)) {
                        constraintToRemove = constraint;
                        break;
                    }
                }
            }
            if (constraintToRemove == null) {
                throw new CustomChangeException(“Constraint not found for table '” + change.getTableName()
                        + “’ and columns '” + change.getUniqueColumns() + “’.”);
            }

            change.setConstraintName(constraintToRemove.getName());
            return change.generateStatements(database);
        }
    }

6

Thanks for the example code.


With 3.0, DatabaseSnapshotGeneratorFactory changed to SnapshotGeneratorFactory. The larger change is that the createSnapshot() method now take different parameters. The one you probably want is Nathan




7

Thanks, I tried this but tyhe problem is I d’ont know the table column list neither the catalog and schema names.

8

And if I create a table snaphost with only the table name, it doesn’t match and all I get is a null reference.

9

I tried something else:

 

  1. public static Collection getUniqueConstraints(Database database, Table table) throws DatabaseException,
                InvalidExampleException {
  2.   SnapshotControl control = new SnapshotControl(database, UniqueConstraint.class);
      CatalogAndSchema catalogAndSchema = new CatalogAndSchema(null, null);
      DatabaseSnapshot snapshot = SnapshotGeneratorFactory.getInstance().createSnapshot(catalogAndSchema, database, control);
      return snapshot.get(UniqueConstraint.class);
  3. }
    but I get an empty set of constraints.

10

Sorry for the slow response. Did you find a solution or should I write up an example?

Nathan