RCOM Schema Checker rules syntax and usage

Use cases
Installation and running
Type of rules currently supported
Folder names, File names and syntax
Examples and receipts

Use cases (i.e. let me try to convince you to use this)

If you do not need convincing, jump ahead to the Installation and Running section

The audit table

Depends on who your systems serves, it is very possible you would like to keep track of any changes to the data of any/some entities in your system. For example, your user’s data might change over time, Last name can get changed, First name can get changed, sometime username can get changed, maybe some other data stored in the users table will be changed. To track those changes you would create a table, which is an exact copy (with some exceptions, see later). Into that table you will insert a users record before it is about to change. That way you can have a “paper” trail of everything that happened to any specific user’s record.

Here are the exceptions for this example. While the data definition (field type and size) must match exactly between both tables, the audit table obviously can’t have the same unique/pk index as the users table. You would also, probably, want to add some meta-data fields to the audit table, like who did the change (the user, the system, your help desk?), when was the change done and a reason. All that means three or more extra fields in the audit table.

The users table will change over time, fields will change in definition, fields will be added and other will be removed. HOW? would you keep both tables synced in structure, but only in the fields they are supposed to be mathced?

The audit database(s)

Similar example to the above scenario, but this time an entire databases, possibly with hundreds of tables. HOW? would you keep all those audit tables and app tables structure in sync with each other?

The metadata fields

Most mature systems I have seen, their architects got to the same conclusion about adding specific metadat fields to each table in their systems. Those fields usually help debug and track things that happens, like, when was the last time a specific record was updated, by whom, when was it created, by whom, which process last touched it etc.

There might be other conventions in your company as to fields names and maybe that all tables should have a PK called id. HOW? would you easilly test/enforce those rules over time?

The legacy reasons

For some obscure or Legacy reasons, you might have some tables that their structure must match, you might have some other rules you need to enforce on the strcuture of tables and fields, We can do it!

Installation and running

I assume you have installed Rahl Commander and configured it. Besides that, there is no specific installation/configuration needed for this functionality to work.

Type of rules currently supported

The list has the rules the Schema Checker can enforce.

Rules targeting a single databse

field_exists: Makes sure all tables in a specific DB have all the fields specified
primary_key: Makes sure all/some tables have a primary key, and what would be it’s field name. For example, enforce all tables to have id SERIAL as primary key.
no_primary_key: Makes sure all/some tables don’t have a primary key
ignore_tables: Auxilliary – in conjunctions with any rule, makes the Schema Cheker skip the tables specified in this rule when checking for the main rule (I give examples in the Syntax section).
field_exists: Makes sure all/some tables have all the fields specified in this rule

Rules comparing two databases

exists: Makes sure table found in db 1 exists in db 2
same: Makes sure a table structure is the same between two different databases
ignore_fields: Auxilliary – ignores fields when doing comparisons, like exists
same_if_exists: A combination of the previous rules. All/some tables must match, If they exists in both databases.
prefix: Auxilliary – When comparing a set of tables between two different databases, where all the difference in te tables names is a prefix. For example all tables in db1 start with logan_ and all tables in db2 start with sabertooth_ but besides that, names are equal.
postfix: Same as above, just with postfix. The two rules can be combined.

Folder names, File names and syntax

Filenames and folder structure

Under the schema folder, the sub-folder names must match the database names you wish to write rules for. There can be only subfolders under the schema main folder. And those folders must match an existing database. Anything else and the Schema Checker will error out (S.
For example, If you have two databases in your db server, “db1” and “db2”.
The folder structure will be

schema _____
            |_db1
            |_db2

A set of rules that will apply only to db1 database (for example: all tables in db1 must have a field name “id”) will be in a file with a postfix of .schk The name of the file is not important, I chose tables.schk.

schema _____
            |_db1_
            |     |_tables.schk
            |
            |_db2

A filename that will host a set of rules that will compare the tables between two databases, For example: db2 will be compared to database db1. will be written in a file with postfix of .rchk and the file name will be db2.rchk
An example of a rule you might find in such a file would be: “all tables in db2, must have a counterpart matching table in db1, with a prefix of back_).
The folder structure for this scenario will look like that:

schema______
            |__db1
            |     |_____db2.rchk
            |
            |__db2

You can have as many files as you need under each folder. You could have the files from both examples above under the db1 folder.
To summerize:

schema main folder has only one level of folders below it.
Folders below schema must match an existing databse name.
Under the databases named folders we can have only two types of files:
1. [any name].schk for rules that are applied on current db tables.
2. [compared to db name].rchk for rules to compare current db to the db that the file is named after.

Rules syntax – general

Empty lines are ignored
# is used for comments. Any line which start with an # will be ignored.
A rule sentence can span as many lines as needed, The Schema Cheker will know when a rule sentence starts and ends.
You can apply several rules to each table/collection of tables in one rule sentence. Each rule has to be separated by one or more spaces.

Anatomy of a rule sentence

The following shows the parts of a rule sentence. NOTICE! I could have written it all in one line, as lone as there is a space between each rule.

table_name:rule_A 
           rule_B[fine_tuning_rule_b_1,fine_tuning_rule_b_2] 
           rule_C[fine_tune(some_exception|someotherexception)]

Each rule sentence has two parts separated by a colon :
The left side of a rule sentence specifies the tables to apply the rules to. It can be a specific table, or with a use of a wild card a group (or all) of the tables in the current db.
The right side of a rule sentence is where the actual rules are. There can be one or many rules.
In the right side, first must come table group modifiers rules. For example: The ignore rules, which tells the Schema Cheker to ignore some tables. It is usefull when you apply rules for the entire DB except 1 or 2 tables. Check the end section for concrete examples.
Rules can be further fine tuned with additional params, and those params might need to be further fine tuned with additional params or exceptions. I am making use of different level of parenteces. There should be no spcaes between parenteces and their rule name.
Parenteces order: [()]. Separator of params [p1,p2,p3,] and for inner params (p1|p2|p3|p4)

Examples and receipts

All tables in db1 must have a field named id

The rule will be written in a file called tables.schk.
The folder structure is:

schema _____
            |_db1_
            |     |_tables.schk
            |
            |_db2

The content of tables.schk (In bold the rule for this receipt).

all:field_exists[id]

All tables in db1 must have the following fields: id,date_created,last_date_modified

The rule will be written in a file called tables.schk.
The folder structure is:

schema _____
            |_db1_
            |     |_tables.schk
            |
            |_db2

The content of tables.schk.

all:field_exists[id,date_created,last_date_modified]

All tables in db1, Except table monitor, must have the following fields: id,date_created,last_date_modified.
Table monitor should have the field id.

The rule will be written in a file called tables.schk.
The folder structure is:

schema _____
            |_db1_
            |     |_tables.schk
            |
            |_db2

The content of tables.schk.

all:ignore_tables[monitor] field_exists[id,date_created,last_date_modified]
monitor:field_exists[id]