This repository contains test cases for Custom Policy Checks.

Links

Description	Source
Latest Liquibase build (Download `liquibase-<version>.zip`)	GitHub.com
Documentation	doc.liquibase.com
Checks jar file	Maven Repository
Python code reference	W3Schools.com
SQL parse module reference	ReadTheDocs.io
Python helper scripts (included with build)	GitHub.com
GraalPy (required for local virtual environment)	GitHub.com
GraalPy reference	Medium.com
venv reference	Python.org

Pre-Execution Steps

Clone the repo to ensure you have all available updated files.

git clone git@github.com:adeelmalik78/python_policy_checks.git

Java 17 or higher is required.
Download and extract the liquibase-<version>.zip file.
Ensure this property (liquibase-checks-enabled) is set to enable custom policy checks.

Environment variable: LIQUIBASE_COMMAND_CHECKS_RUN_CHECKS_SCRIPTS_ENABLED='true'
liquibase.properties: checks-scripts-enabled=true
CLI: --checks-scripts-enabled=true

Tutorials

There are two tutorials provided in this repo. These are a great starting point to learn from a helloworld example:

Observations

Recommend all non-custom policy checks be disabled for testing.
SQL changelogs are available in the changelog.sql file.
[IMPORTANT] Python scripts are called ONCE FOR EACH CHANGESET (changelog scope) or ONCE FOR EACH DATABASE OBJECT (database scope). Changesets may contain multiple SQL statements.
The print() function can be used to display debugging messages (instead of just liquibase_logger). This works regardless of log_level. Additionally, f strings automatically convert variables for printing and remove the need for concatenation to build a string of static and dynamic text.
```
my_int = 123
my_str = "Hello World!"
print(f"{my_str} My variable is: {my_int}")
```
f strings can also be used wherever strings are expected.
```
my_int = 123
liquibase_status.message = f"My variable is: {my_int}"
```
Arguments defined at check creation/modification can be passed in to scripts. See varchar_max_size.py for an example.
Error messages can be customized by adding a string to be replaced when defining the custom policy check. See table_names_uppercase.py for an example.
Values can be saved/retrieved between check runs using a cache. See create_index_count.py for an example.
The latest Python helper scripts can be imported into your main python file to access available functions. Note you may need to ask #devops for access to the repository to view them.
```
import liquibase_utilities
import liquibase_changesets

print(liquibase_changesets.get_labels(liquibase_utilities.get_changeset()))
```
LoadData change types are not supported. DAT-17893
Having the commercial Mongo extension in the lib directory will cause some relational change types to behave incorrectly (e.g., createIndex). DAT-17901

Environment variables can be accessed using the os module.

import os

print(os.environ.get("LIQUIBASE_COMMAND_URL"))

Running Local Python Environment

To use a local Python environment, versus the built-in one, follow these steps.

Download and extract GraalPy.
Add <install dir>/bin (or <install dir>\bin for Windows) to your path.
Create a Python virtual environment and directory structure. Once created the environment can be moved to a different folder (provided the Liquibase environment variable is also updated). Use GitBash for Windows to execute these commands.
```
graalpy -m venv <virtual env path>
```
Activate the environment to install modules to the local virtual environment.
Linux
```
source <virtual env path>/bin/activate
```
Windows
```
source <virtual env path>/Scripts/activate
```
Install Python modules. Sqlpare is required.
```
graalpy -m pip install sqlparse
```
Deactive the environment (exit or run bin/deactivate). Deactivate on Windows may throw an error (safe to close the GitBash window).

Configure Liquibase to point to the new virtual environment.
Linux

LIQUIBASE_SCRIPT_PYTHON_EXECUTABLE_PATH="<virtual env path>/bin/python"

Windows

LIQUIBASE_SCRIPT_PYTHON_EXECUTABLE_PATH='<virtual env path>\Scripts\python.exe'

Run checks as normal. To revert back to the built-in environment, unset the environment variable.

Configuration Steps

Note: script path includes name of script file

Changelog Checks

DisallowSchemaInChangelog

% liquibase checks customize --check-name=CustomCheckTemplate

Prompt	Response
Short Name	DisallowSchemaInChangelog
Severity	0-4
Description
Scope	changelog
Message
Type	python
Path	Scripts/disallow_schema_changelog.py
Args
Snapshot	false

TableNamesMustBeUppercase

% liquibase checks customize --check-name=CustomCheckTemplate

Prompt	Response
Short Name	TableNamesMustBeUppercase
Severity	0-4
Description	TableNamesMustBeUppercase
Scope	changelog
Message	Table __TABLE_NAME__ must be UPPERCASE.
Type	python
Path	Scripts/table_names_uppercase.py
Args
Snapshot	false

TableNameNoSpecialCharactersChangelog

% liquibase checks customize --check-name=CustomCheckTemplate

Prompt	Response
Short Name	TableNameNoSpecialCharactersChangelog
Severity	0-4
Description	Table name
Scope	changelog
Message
Type	python
Path	Scripts/tableName_noSpecialCharacters_changelog.py
Args
Snapshot	false

Database Checks

VarcharMaxSize

% liquibase checks customize --check-name=CustomCheckTemplate

Prompt	Response
Short Name	VarcharMaxSize
Severity	0-4
Description	VarcharMaxSize
Scope	database
Message	Column __COLUMN_NAME__ exceeds __COLUMN_SIZE__.
Type	python
Path	Scripts/varchar_max_size.py
Args	VARCHAR_MAX=255
Snapshot	false

PKNamingConvention

% liquibase checks customize --check-name=CustomCheckTemplate

Prompt	Response
Short Name	PKNamingConvention
Severity	0-4
Description	PKNamingConvention
Scope	database
Message	Primary key name __CURRENT_NAME__ must include table name (__NAME_STANDARD__).
Type	python
Path	Scripts/pk_names.py
Args
Snapshot	false

UCNamingConvention

% liquibase checks customize --check-name=CustomCheckTemplate

Prompt	Response
Short Name	UCNamingConvention
Severity	0-4
Description	Unique constraint name must be in the form of xak_tablename
Scope	DATABASE
Message
Type	PYTHON
Path	Scripts/uc_names.py
Args
Snapshot	false

PKNamingConventionPG

% liquibase checks customize --check-name=CustomCheckTemplate

Prompt	Response
Short Name	PKNamingPostgreSQL
Severity	0-4
Description	Name must be in the form of tablename_pkey
Scope	DATABASE
Message	Primary key name __CURRENT_NAME__ must include table name. Please use (__NAME_STANDARD__) instead.
Type	PYTHON
Path	Scripts/pk_names_pg.py
Args	STANDARD=pkey
Snapshot	false

CreateIndexCount

% liquibase checks customize --check-name=CustomCheckTemplate

Prompt	Response
Short Name	CreateIndexCount
Severity	0-4
Description	CreateIndexCount
Scope	changelog
Message	Table __TABLE_NAME__ would have __INDEX_COUNT__ indexes.
Type	python
Path	Scripts/create_index_count.py
Args	MAX_INDEX=2
Snapshot	true

FKNamingConvention

% liquibase checks customize --check-name=CustomCheckTemplate

Prompt	Response
Short Name	FKNamingConvention
Severity	0-4
Description	FKNamingConvention
Scope	changelog
Message	Foreign key name __NAME_CURRENT__ must include parent and child table names (__NAME_STANDARD__).
Type	python
Path	Scripts/fk_names.py
Args
Snapshot	false

PKTablespace

% liquibase checks customize --check-name=CustomCheckTemplate

Prompt	Response
Short Name	PKTablespace
Severity	0-4
Description	PKTablespace
Scope	changelog
Message	Primary key name __PK_NAME__ must include explicit tablespace definition.
Type	python
Path	Scripts/pk_tablespace.py
Args
Snapshot	false

adeelmalik78/python_policy_checks