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.

About the Author

Scott Bock profile.

Scott Bock

Principal Technologist

Scott is a Senior Software Engineer with over 12 years of experience using Java, and 5 years experience in technical leadership positions. His strengths include troubleshooting and problem solving abilities, excellent repertoire with customers and management, and verbal and written communication. He develops code across the entire technology stack including database, application, and user interface.

Leave a Reply

Your email address will not be published.

Related Blog Posts
A security model for developers
Software security is more important than ever, but developing secure applications is more confusing than ever. TLS, mTLS, RBAC, SAML, OAUTH, OWASP, GDPR, SASL, RSA, JWT, cookie, attack vector, DDoS, firewall, VPN, security groups, exploit, […]
Building Better Data Visualization Experiences: Part 1 of 2
Through direct experience with data scientists, business analysts, lab technicians, as well as other UX professionals, I have found that we need a better understanding of the people who will be using our data visualization products in order to build them. Creating a product utilizing data with the goal of providing insight is fundamentally different from a typical user-centric web experience, although traditional UX process methods can help.
Kafka Schema Evolution With Java Spring Boot and Protobuf
In this blog I will be demonstrating Kafka schema evolution with Java, Spring Boot and Protobuf.  This app is for tutorial purposes, so there will be instances where a refactor could happen. I tried to […]
Redis Bitmaps: Storing state in small places
Redis is a popular open source in-memory data store that supports all kinds of abstract data structures. In this post and in an accompanying example Java project, I am going to explore two great use […]