Add a custom object to your Liquibase diff

Adding a custom object to your liquibase diff is a pretty simple two step process.

  1. Create an implementation of DatabaseObject
  2. Create an implementation of SnapshotGenerator

In my case I wanted to add tracking of Stored Procedures on my Snowflake Database.

First we need to implement the DatabaseObject in this case we’ll extend the AbstractDatabaseObject.
We need to:

  • override setName
  • override getName
  • override getSchema
  • add methods to get/set any other attributes
import liquibase.structure.AbstractDatabaseObject;
import liquibase.structure.DatabaseObject;
import liquibase.structure.core.Schema;
 
public class Procedure extends AbstractDatabaseObject {
 
    public Procedure() {
    }
 
    public Procedure(String catalogName, String schemaName, String functionName) {
        this.setSchema(new Schema(catalogName, schemaName));
        this.setName(functionName);
    }
 
    @Override
    public DatabaseObject[] getContainingObjects() {
        return null;
    }
 
    @Override
    public String getName() {
        return getAttribute("name", String.class);
    }
 
    @Override
    public Procedure setName(String name) {
        this.setAttribute("name", name);
        return this;
    }
 
    public String getArgumentSignature() {
        return getAttribute("argumentSignature", String.class);
    }
 
    public Procedure setArgumentSignature(String argumentSignature) {
        this.setAttribute("argumentSignature", argumentSignature);
        return this;
    }
 
    public String getDataType() {
        return getAttribute("dataType", String.class);
    }
 
    public Procedure setDataType(String dataType) {
        this.setAttribute("dataType", dataType);
        return this;
    }
 
    public String getProcedureDefinition() {
        return getAttribute("procedureDefinition", String.class);
    }
 
    public Procedure setProcedureDefinition(String procedureDefinition) {
        this.setAttribute("procedureDefinition", procedureDefinition);
        return this;
    }
 
 
    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if ((o == null) || (getClass() != o.getClass())) return false;
 
        Procedure function = (Procedure) o;
 
        if ((this.getSchema() != null) && (function.getSchema() != null)) {
            boolean schemasEqual = this.getSchema().equals(function.getSchema());
            if (!schemasEqual) {
                return false;
            }
        }
 
        return !((getName() != null) ? !getName().equalsIgnoreCase(function.getName()) : (function.getName() != null));
 
    }
 
    @Override
    public int hashCode() {
        return ((getName() != null) ? getName().toUpperCase().hashCode() : 0);
    }
 
 
    @Override
    public String toString() {
        return getName();
    }
 
    /**
     * @return Returns the schema.
     */
    @Override
    public Schema getSchema() {
        return getAttribute("schema", Schema.class);
    }
 
    /**
     * @param schema The schema to set.
     */
    public Procedure setSchema(Schema schema) {
        this.setAttribute("schema", schema);
        return this;
    }
 
    public Procedure setSchema(String catalog, String schema) {
        return setSchema(new Schema(catalog, schema));
    }
 
}

Next we need to implement the SnapshotGenerator in this case we’ll extend the JdbcSnapshotGenerator. (Note: I used liquibase’s SequenceSnapshotGenerator as a general guide).
We need to:

  • call the super constructor referencing our new DatabaseObject class and that it “addsTo” Schema.class
  • override addsTo
  • override snapshotObject
import liquibase.database.Database;
import liquibase.exception.DatabaseException;
import liquibase.executor.ExecutorService;
import liquibase.ext.snowflake.structure.Procedure;
import liquibase.snapshot.DatabaseSnapshot;
import liquibase.snapshot.InvalidExampleException;
import liquibase.snapshot.SnapshotIdService;
import liquibase.snapshot.jvm.JdbcSnapshotGenerator;
import liquibase.statement.core.RawSqlStatement;
import liquibase.structure.DatabaseObject;
import liquibase.structure.core.Schema;
 
import java.util.List;
import java.util.Map;
 
public class ProcedureSnapshotGenerator extends JdbcSnapshotGenerator {
 
    public ProcedureSnapshotGenerator() {
        super(Procedure.class, new Class[]{Schema.class});
    }
 
    @Override
    protected void addTo(DatabaseObject foundObject, DatabaseSnapshot snapshot) throws DatabaseException, InvalidExampleException {
 
        if (foundObject instanceof Schema) {
            Schema schema = (Schema) foundObject;
            Database database = snapshot.getDatabase();
 
            //noinspection unchecked
            List<Map> procedures = ExecutorService.getInstance().getExecutor(database).queryForList(new RawSqlStatement(getProceduresSQL(schema)));
 
            if (procedures != null) {
                for (Map procedure : procedures) {
                    schema.addDatabaseObject(mapToProcedure(procedure, (Schema) foundObject));
                }
            }
        }
    }
 
    @Override
    protected DatabaseObject snapshotObject(DatabaseObject example, DatabaseSnapshot snapshot) throws DatabaseException {
        if (example.getSnapshotId() != null) {
            return example;
        }
        Database database = snapshot.getDatabase();
        List<Map> procedures;
 
        if (example.getAttribute("liquibase-complete", false)) { //need to go through "snapshotting" the object even if it was previously populated in addTo. Use the "liquibase-complete" attribute to track that it doesn't need to be fully snapshotted
            example.setSnapshotId(SnapshotIdService.getInstance().generateId());
            example.setAttribute("liquibase-complete", null);
            return example;
        }
 
        procedures = ExecutorService.getInstance()
                .getExecutor(database)
                .queryForList(new RawSqlStatement(getProceduresSQL(example.getSchema())));
        DatabaseObject sequenceRow = getProcedures(example, procedures);
        if (sequenceRow != null) return sequenceRow;
 
        return null;
    }
 
    private DatabaseObject getProcedures(DatabaseObject example, List<Map> procedures) {
        for (Map procedureRow : procedures) {
            String name = (String) procedureRow.get("PROCEDURE_NAME");
            if (name.equals(example.getName())) {
                return mapToProcedure(procedureRow, example.getSchema());
            }
        }
 
        return null;
    }
 
    private Procedure mapToProcedure(Map procedureRow, Schema schema) {
        Procedure procedure = new Procedure();
        procedure.setName((String) procedureRow.get("PROCEDURE_NAME"));
        procedure.setArgumentSignature((String) procedureRow.get("ARGUMENT_SIGNATURE"));
        procedure.setDataType((String) procedureRow.get("DATA_TYPE"));
        procedure.setProcedureDefinition((String) procedureRow.get("PROCEDURE_DEFINITION"));
        procedure.setSchema(schema);
        procedure.setAttribute("liquibase-complete", true);
 
        return procedure;
    }
 
    protected String getProceduresSQL(Schema schema) {
        return
                "SELECT " +
                        "   PROCEDURE_NAME, " +
                        "   ARGUMENT_SIGNATURE, " +
                        "   DATA_TYPE, " +
                        "   PROCEDURE_DEFINITION " +
                        "FROM " +
                        "   INFORMATION_SCHEMA.PROCEDURES " +
                        "   WHERE " +
                        "PROCEDURE_SCHEMA = '" + schema.getName() + "'";
    }
}

That’s it! Now when your code is included in the liquibase classpath. Liquibase with scan for implementation of DatabaseObject and SnapshotGenerator and pick up your code to be used as part of a diff command.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

*