Add a custom object to your Liquibase diff
Adding a custom object to your liquibase diff is a pretty simple two step process.
- Create an implementation of DatabaseObject
- 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
