Writing Rules from Templates

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 

       Expand source

      /*
      @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 

       Expand source

      /*
      @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 

       Expand source

      /*
      @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 

       Expand source

      /*
      @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