Rules are used to enforce policies for changes to databases. 

Use the three-stage forecasting process to determine if changesets comply with your requirements. If the changesets fail the rules during forecasting, you can fix them before you deploy the changesets to your databases.    

Some rules are provided with Datical DB software. They are described in Rules Library. You can also write your own rules, either by copying and editing the provided rules or by writing them yourself. 

Each project includes a Rules directory where you place the rules that you want to apply to that project. 

The rules engine is implemented using the open-source Drools Business Rules Management System.

The rules engine contains models of objects for Datical DB and the database you are working with. Rules consist of expressions that evaluate selected objects and specify a response.

Rules Location and Naming

Location

To use rules in a project, you place rules files (.drl) in the Rules directory hierarchy under the project. 

<project>/
   Rules/
      Forecast/
      PostForecast/
      PreForecast/
      SqlRules/

• Files that contain one or more rules
• Sub folders containing individual or bundled rules

Rules File Naming Conventions

Give each rule a short, meaningful name. All rules files must have a .drl file extension. Rule files without the extension are not picked up for forecasting. 

<rule_name>.drl

Where Rules Are Applied

Rules are available to be applied during packing and during each phase of the forecasting process. 

Script File Extension Requirements

Rules only check SQL scripts with the following extensions by default: .sql, .dml, .ddl

To optionally add other SQL script extensions for other file types to be checked by SqlRules, change the sqlRules.supportedScriptExtensions property in the daticaldb.properties file, which is located in the root directory for the project (<project>). 

Example:

sqlRules.supportedScriptExtensions=.pkg,.trg,.prc,.proc,.ddl,.sql,.dml

Packaging Rules

Rules in the SqlRules directory are applied by Deployment Packager when packaging SQL scripts into changesets. They are not part of the forecasting process. 

Use these rules to test the SQL statements in all SQL scripts before they are packaged. Typically they are used to test for disallowed statements, such as grants. 

• SqlRules 

Forecasting Rules

The forecasting process allows to test the impact of changesets on the target database. You can apply rules during each stage of forecasting. Each stage of forecasting has its own directory of rules. 

• PreForecast - applied immediately before a forecast
• Forecast - applied to produce a forecast of the impact of changes on the database schema
• PostForecast - applied to assess the results of a forecast and to perform further tests

The available object data models for forecasting rules vary by stage. 

Rules Types

SqlRules

These rules are executed by the Deployment Packager at the beginning of the packaging process. When the script deployPackager.groovy is run, tests all of the SQL scripts being packaged against the rules located in the SqlRules folder.

The Deployment Packager runs the runRules command to run rules on the SQL scripts slated for packaging.  This is run in both preview and active mode.  You can run the command outside of Deployment Packager as follows.

hammer runRules [SQL file | List of SQL files | SQL files folder]

Rules are read from the SqlRules folder.

The rules engine is fired once for each script.

Use commas or semicolons to separate lists of files passed to the command.  If semicolons are used, the list should surrounded by double quotes.

PreForecast

PreForecast rules run before forecasting starts. They can be used to test conditions of Datical DB objects and client objects that are required in order for forecasting to be performed. 

This is the best place to perform validation to ensure:

• Changelog Creator, timestamps, and comments exist
• Developers and DBAs follow best practices for changeset management

Rules are read from the PreForecast folder.

In this phase, the rules engine is fired once.

Forecast

Forecast rules are run during the forecast process. During forecasting, Datical DB builds a model of the database as it currently exists, and then simulates what would happen to that model as each change is applied to the database. The changes are not applied to the database. The results of a forecast help you determine if there would be undesired effects caused by the changes before you run them. 

Most rules are written to run during the forecast. These rules validate the change model as well and determine how the changes affect the target database schema.

Some of the most common classes of rules:

• Verify or Enforce Corporate DDL Standards
• Assess the validity of DDL changes
• Protect against DDL changes that could have a negative impact on data
• Limit ability to deploy certain types of DDL (especially, grants)

Processing proceeds in two steps:

1. Run built-in forecast checks. These automatic tests basic semantic integrity. For example, when a script renames a column in a database, a built-in rule checks that both the table and the column exist. 
2. Run rules in the Forecast folder. 

Rules are read from the Forecast folder.

In this phase, the rules engine is fired once FOR EACH CHANGE to be deployed.

PostForecast

PostForecast rules are run after the forecasting process finishes. You can use the rules to examine the final forecasted state of the database or parts of the Forecast object itself. For example, you could test whether a forecast threw more than a specified number of warnings.

Rules are written to execute after forecast but before deployment.

Rules in this stage typically:

• Validate rules on the database using the forecasted database model
• Look for DDL drift before applying additional changes

Rules are read from the PostForecast folder.

In this phase, the rules engine is fired once.

Organizing Rules

Directory Structure

Rules are executed according to phase: sqlrules, pre-forecast, forecast, and post-forecast. Rules are applied to the appropriate phase based on their location in the rules subfolder:

<Datical DB Project>/Rules/
                           SQLRules
                           PreForecast
                           Forecast
                           PostForecast

In addition to these standard folders, you can also specify sub-folders to better organize your rules.

Minimally, you will want to specify company specific directories so you can keep your rules separate from the Datical supplied rules.

As you have more rules, consider adding additional sub-folders where needed to keep the rules from getting overwhelming. Some ideal categories are:

• <rule goal>: Security, Access Control, Dependency Validation, Data Safety, Corporate Standards, Audit and Compliance, etc.
• <rule db object>: group the rules by the primary object type we're writing the rule about (Table, Column, Index, StoredProcedure, Trigger, etc)

An example of this directory structure looks as follows:

<Datical DB Project>/
Rules/
   Forecast/
      com.datical/
      com.my_company/
         Security/
         Table/
         Stored Logic/
         Index/
   PostForecast/
      com.datical/
      com.my_company/
   PreForecast/
      com.datical/
      com.my_company/
   SqlRules
      com.datical/
      com.my_company

Centralized Rules

If you have many projects that all use the same set of rules, you can optionally centralize the rules by creating a rules repository in source control.

See this page for more details: How to Create a Common Rules Repository