Liquibase Enterprise was formerly known as Datical DB.

Writing Rules from Templates

Owned by Former user (Deleted)
Last updated: Jan 02, 2020 by Former user (Deleted)
4 min read

Getting Started

  1. Create a simple Datical project
    1. Create 2 empty databases on your desired DB platform
    2. Create project with 2 steps pointing to these databases
  2. Identify a new rule that needs to be created
  3. Look for the Rule in the Rules Library below
    • Types of rules (so far)
      • Standards - Naming
      • Standards - Datatype
      • Security
      • Performance
      • Structure
  4. Creating a Rule - either copy a rule from the library or create a new rule from the following templates (create your rule file using a meaningful name - see examples in library below)

    1. SqlRule Rule Template 

      /*
@author <add your name here>
@version 1.0
@date <add current date here>
@description <add the description of the rule here>
*/

// Setting the rule type ensures that people using the rule will know where to place it in the directory structure
package com.datical.hammer.core.sqlrules

dialect "mvel"

// Java Utilities
import java.util.List;
import java.util.Arrays;
import java.util.ArrayList;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.collections.ListUtils;

// Rules Engine Utility Methods (used by all rules)
import com.datical.db.project.DatabaseDef;
import com.datical.db.project.Project;
import com.datical.hammer.core.rules.ClientSystemInfo;
import com.datical.hammer.core.rules.ProjectResources;
import com.datical.hammer.core.rules.Response;
import com.datical.hammer.core.rules.Response.ResponseType;
import com.datical.hammer.core.forecast.rules.RuleFunctions;


// Rules Engine Utility Methods (for SQLRules Only)
import com.datical.hammer.core.rules.WithComments;
import com.datical.hammer.core.rules.WithoutComments;

// Development tip: Use regex creation and debugging site: https://regex101.com

// Rule(s)
rule "CreateIndex: Parallel attribute is missing and needs to be specified"
    salience 1
    when
        wc : WithoutComments(getText().toLowerCase() matches ".*(create)(\\s+)(index)(?!.*parallel.*)([^;]+)[;].*" )
    then
		String errorMessage = 
			"The parallel options is missing and needs to be specified in the Create Index SQL script: " +
           wc.getSqlFile().getName();
        insert(new Response(ResponseType.FAIL, errorMessage, drools.getRule().getName()));
end

    2. PreForecast Rule Template 

      /*
@author <add your name here>
@version 1.0
@date <add current date here>
@description <add the description of the rule here>
*/

// Setting the rule type ensures that people using the rule will know where to place it in the directory structure
package com.datical.hammer.core.preforecast

dialect "mvel"

// Java Utilities
import java.util.List;
import java.util.Arrays;
import java.util.ArrayList;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.collections.ListUtils;

// Rules Engine Utility Methods (used by all rules)
import com.datical.db.project.DatabaseDef;
import com.datical.db.project.Project;
import com.datical.hammer.core.forecast.rules.RuleFunctions;
import com.datical.hammer.core.rules.ClientSystemInfo;
import com.datical.hammer.core.rules.GeneratedSQL;
import com.datical.hammer.core.rules.ProjectResources;
import com.datical.hammer.core.rules.Response;
import com.datical.hammer.core.rules.Response.ResponseType;
import com.datical.hammer.core.status.ChangeMetaDataContainer;

// Import Models for Objects (the following are the commonly used models)
// Models: Liquibase Base Object Models
import org.liquibase.xml.ns.dbchangelog.AddColumnType;
import org.liquibase.xml.ns.dbchangelog.AddForeignKeyConstraintType;
import org.liquibase.xml.ns.dbchangelog.AddPrimaryKeyType;
import org.liquibase.xml.ns.dbchangelog.ChangeSetType;
import org.liquibase.xml.ns.dbchangelog.ColumnAddColumnType;
import org.liquibase.xml.ns.dbchangelog.ColumnBaseType;
import org.liquibase.xml.ns.dbchangelog.ColumnExistsType;
import org.liquibase.xml.ns.dbchangelog.ColumnType;
import org.liquibase.xml.ns.dbchangelog.CreateIndexType;
import org.liquibase.xml.ns.dbchangelog.CreateProcedureType;
import org.liquibase.xml.ns.dbchangelog.CreateSequenceType;
import org.liquibase.xml.ns.dbchangelog.CreateTableType;
import org.liquibase.xml.ns.dbchangelog.CreateViewType;
import org.liquibase.xml.ns.dbchangelog.DropTableType;
import org.liquibase.xml.ns.dbchangelog.ForeignKeyConstraintExistsType;
import org.liquibase.xml.ns.dbchangelog.LoadDataType;
import org.liquibase.xml.ns.dbchangelog.LoadUpdateDataType;
import org.liquibase.xml.ns.dbchangelog.RenameColumnType;
import org.liquibase.xml.ns.dbchangelog.RenameTableType;
import org.liquibase.xml.ns.dbchangelog.RenameViewType;
import org.liquibase.xml.ns.dbchangelog.SqlType;

// Models: Datical Stored Logic Extension Models
import com.datical.xml.ns.storedlogic.CreateTriggerType;
import com.datical.xml.ns.storedlogic.CreateFunctionType;
import com.datical.xml.ns.storedlogic.CreatePackageBodyType;
import com.datical.xml.ns.storedlogic.CreatePackageType;
import com.datical.xml.ns.storedlogic.RenameTriggerType;

// Models: Datical AppDBA Extension Models
import com.datical.xml.ns.appdba.CreateSynonymType;

// Rule(s)
rule "Change Set Must Have a Comment"
	salience 10000
	when
		$changeSet : ChangeSetType(  )
		$comments : List( size == 0 )
							from collect(CommentType()
								from $changeSet.comments)
	then
		String errorMessage = "Change Set ID " + $changeSet.getId() + " has no comment.";
		insert(new Response(ResponseType.FAIL, errorMessage, drools.getRule().getName(), $changeSet.getId()));
end

    3. Forecast Rule Template 

      /*
@author <add your name here>
@version 1.0
@date <add current date here>
@description <add the description of the rule here>
*/

// Setting the rule type ensures that people using the rule will know where to place it in the directory structure
package com.datical.hammer.core.forecast

dialect "mvel"

// Java Utilities
import java.util.List;
import java.util.Arrays;
import java.util.ArrayList;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.collections.ListUtils;

// Rules Engine Utility Methods (used by all rules)
import com.datical.db.project.DatabaseDef;
import com.datical.db.project.Project;
import com.datical.hammer.core.rules.ClientSystemInfo;
import com.datical.hammer.core.rules.ProjectResources;
import com.datical.hammer.core.rules.Response;
import com.datical.hammer.core.rules.Response.ResponseType;
import com.datical.hammer.core.forecast.rules.RuleFunctions;

// Import Models for Objects (the following are the commonly used models)
// Models: Liquibase Base Object Models
import org.liquibase.xml.ns.dbchangelog.AddColumnType;
import org.liquibase.xml.ns.dbchangelog.AddForeignKeyConstraintType;
import org.liquibase.xml.ns.dbchangelog.AddPrimaryKeyType;
import org.liquibase.xml.ns.dbchangelog.ChangeSetType;
import org.liquibase.xml.ns.dbchangelog.ColumnAddColumnType;
import org.liquibase.xml.ns.dbchangelog.ColumnBaseType;
import org.liquibase.xml.ns.dbchangelog.ColumnExistsType;
import org.liquibase.xml.ns.dbchangelog.ColumnType;
import org.liquibase.xml.ns.dbchangelog.CreateIndexType;
import org.liquibase.xml.ns.dbchangelog.CreateProcedureType;
import org.liquibase.xml.ns.dbchangelog.CreateSequenceType;
import org.liquibase.xml.ns.dbchangelog.CreateTableType;
import org.liquibase.xml.ns.dbchangelog.CreateViewType;
import org.liquibase.xml.ns.dbchangelog.DropTableType;
import org.liquibase.xml.ns.dbchangelog.ForeignKeyConstraintExistsType;
import org.liquibase.xml.ns.dbchangelog.LoadDataType;
import org.liquibase.xml.ns.dbchangelog.LoadUpdateDataType;
import org.liquibase.xml.ns.dbchangelog.RenameColumnType;
import org.liquibase.xml.ns.dbchangelog.RenameTableType;
import org.liquibase.xml.ns.dbchangelog.RenameViewType;
import org.liquibase.xml.ns.dbchangelog.SqlType;

// Models: Datical Stored Logic Extension Models
import com.datical.xml.ns.storedlogic.CreateTriggerType;
import com.datical.xml.ns.storedlogic.CreateFunctionType;
import com.datical.xml.ns.storedlogic.CreatePackageBodyType;
import com.datical.xml.ns.storedlogic.CreatePackageType;
import com.datical.xml.ns.storedlogic.RenameTriggerType;

// Models: Datical AppDBA Extension Models
import com.datical.xml.ns.appdba.CreateSynonymType;

// Models: Datical Database Models
import com.datical.dbsim.model.Column;
import com.datical.dbsim.model.DbModel;
import com.datical.dbsim.model.FkConstraint;
import com.datical.dbsim.model.Function;
import com.datical.dbsim.model.Index;
import com.datical.dbsim.model.PackageBody;
import com.datical.dbsim.model.Procedure;
import com.datical.dbsim.model.Schema;
import com.datical.dbsim.model.Table;
import com.datical.dbsim.model.Trigger;
import com.datical.dbsim.model.View;


// Rule(s)
rule "Columns added to new tables should not have defaultValue set"
	when
		$createTable : CreateTableType( )
		$defaultValues : List( size > 0 )
							from collect(ColumnType ( defaultValue not matches null
															|| defaultValueNumeric not matches null
															|| defaultValueBoolean not matches null
															|| defaultValueDate not matches null
															|| defaultValueComputed not matches null)
								from $createTable.getColumn())
	then
		String errorMessage = drools.getRule().getName();
		insert(new Response(ResponseType.FAIL, errorMessage, drools.getRule().getName()));
end

    4. PostForecast Rule Template 

      /*
@author <add your name here>
@version 1.0
@date <add current date here>
@description <add the description of the rule here>
*/

// Setting the rule type ensures that people using the rule will know where to place it in the directory structure
package com.datical.hammer.core.postforecast

dialect "mvel"

// Java Utilities
import java.util.List;
import java.util.Arrays;
import java.util.ArrayList;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.collections.ListUtils;

// Rules Engine Utility Methods (used by all rules)
import com.datical.db.project.DatabaseDef;
import com.datical.db.project.Project;
import com.datical.hammer.core.rules.ClientSystemInfo;
import com.datical.hammer.core.rules.ProjectResources;
import com.datical.hammer.core.rules.Response;
import com.datical.hammer.core.rules.Response.ResponseType;
import com.datical.hammer.core.forecast.rules.RuleFunctions;

// Import Models for Objects (the following are the commonly used models)
// Models: Liquibase Base Object Models
import org.liquibase.xml.ns.dbchangelog.AddColumnType;
import org.liquibase.xml.ns.dbchangelog.AddForeignKeyConstraintType;
import org.liquibase.xml.ns.dbchangelog.AddPrimaryKeyType;
import org.liquibase.xml.ns.dbchangelog.ChangeSetType;
import org.liquibase.xml.ns.dbchangelog.ColumnAddColumnType;
import org.liquibase.xml.ns.dbchangelog.ColumnBaseType;
import org.liquibase.xml.ns.dbchangelog.ColumnExistsType;
import org.liquibase.xml.ns.dbchangelog.ColumnType;
import org.liquibase.xml.ns.dbchangelog.CreateIndexType;
import org.liquibase.xml.ns.dbchangelog.CreateProcedureType;
import org.liquibase.xml.ns.dbchangelog.CreateSequenceType;
import org.liquibase.xml.ns.dbchangelog.CreateTableType;
import org.liquibase.xml.ns.dbchangelog.CreateViewType;
import org.liquibase.xml.ns.dbchangelog.DropTableType;
import org.liquibase.xml.ns.dbchangelog.ForeignKeyConstraintExistsType;
import org.liquibase.xml.ns.dbchangelog.LoadDataType;
import org.liquibase.xml.ns.dbchangelog.LoadUpdateDataType;
import org.liquibase.xml.ns.dbchangelog.RenameColumnType;
import org.liquibase.xml.ns.dbchangelog.RenameTableType;
import org.liquibase.xml.ns.dbchangelog.RenameViewType;
import org.liquibase.xml.ns.dbchangelog.SqlType;

// Models: Datical Stored Logic Extension Models
import com.datical.xml.ns.storedlogic.CreateTriggerType;
import com.datical.xml.ns.storedlogic.CreateFunctionType;
import com.datical.xml.ns.storedlogic.CreatePackageBodyType;
import com.datical.xml.ns.storedlogic.CreatePackageType;
import com.datical.xml.ns.storedlogic.RenameTriggerType;

// Models: Datical AppDBA Extension Models
import com.datical.xml.ns.appdba.CreateSynonymType;

// Models: Datical Database Models
import com.datical.dbsim.model.Column;
import com.datical.dbsim.model.DbModel;
import com.datical.dbsim.model.FkConstraint;
import com.datical.dbsim.model.Function;
import com.datical.dbsim.model.Index;
import com.datical.dbsim.model.PackageBody;
import com.datical.dbsim.model.Procedure;
import com.datical.dbsim.model.Schema;
import com.datical.dbsim.model.Table;
import com.datical.dbsim.model.Trigger;
import com.datical.dbsim.model.View;


// Rule(s)
rule "All tables should have a primary key or unique constraint"
	when
		$db_model_container : ModelContainer( )
	then
		String errorMessage = "";
		for (Schema schema : $db_model_container.getNewModel().getSchemas()) {
			for (Table table : schema.getTables()) {
				if (!table.getName().toUpperCase().equals("DATABASECHANGELOGLOCK") && !table.getName().toUpperCase().equals("DATABASECHANGELOG")) {
					if ((table.getPkConstraint() == null) && table.getUniqueConstraints().isEmpty()) {
						errorMessage += "Table (" + table.getName() + ") needs to have either a primary key or unique constraint.<br/>\n";
					}
				}
			}
		}
		
		if (errorMessage != "") {
			insert(new Response(ResponseType.FAIL, errorMessage, drools.getRule().getName()));
		}
end
  5. Testing a Rule
    1. Copy your rule into the appropriate Rules subfolder
    2. In the GUI, right-click on the Rules folder and select "Check Rules" (this will detect any syntax errors)
      1. Note, this does not check SqlRules
    3. Create one or more SQL scripts named the same as your rule with a .sql ending that
      1. exercises a positive case 
      2. exercises a negative case
      3. exercises one or more edge cases

    4. If this is a SQLFile rule, run the Datical command line as shown below or run the SQL script through the Packager (preview mode should be okay)

      cd <datical project workspace>
hammer runRules [SQL file | SQL files folder]
    5. If this is a Pre-Forecast, Forecast or PostForecast rule, then
      1. Run the SQL script through convert SQL to get it converted into change sets (save the relevant change sets in a file named the same as your rule with a .xml ending). The QA team will thank you.
      2. Run Forecast on the second step in your project to test the rules on the undeployed changes now in your changelog

Copyright © Liquibase 2012-2022 - Proprietary and Confidential