README.md

Liquibase Pro Python Policy Checks

This repository is a collection of Liquibase Pro Python Policy checks. These checks have been created by the Liquibase community, including our customers and field engineers. You are encouraged to use these rules in your own Liquibase Pro pipelines. If you need any help with these rules, please contact [email protected] and our team will be happy to assist you.

Useful Links

Description	Source
Documentation	Liquibase
Checks jar file (latest is latest version number)	Maven
Python code reference	W3Schools.com
SQL parse module reference	ReadTheDocs.io
GraalPy (required for local virtual environment)	GitHub.com
GraalPy reference	Medium.com
venv reference	Python.org

Pre-Execution Steps

1. Java 17 or higher is required.
2. Download latest version of Liquibase and the latest version of the checks jar file.
3. Ensure this Environment variable or setting is set to enable custom policy checks.
   Environment variable

   LIQUIBASE_COMMAND_CHECKS_RUN_CHECKS_SCRIPTS_ENABLED='true'
   

   liquibase.properties

   checks-scripts-enabled=true
   

Notes

1. To aid in debugging, it's recommended all non-custom policy checks be disabled.
2. Relational and NoSQL changelogs are available in the Changesets folder.
3. Scripts are called once for each changeset (changelog scope) or once for each database object (database scope). Changesets may contain multiple SQL statements.
4. 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}"
   

5. Arguments defined at check creation/modification can be passed in to scripts. See VarcharMaxSize for an example.
6. Error messages can be customized by adding a string to be replaced when defining the custom policy check. See TableNamesMustBeUppercase for an example.
7. Values can be saved/retrieved between check runs using a cache. See CreateIndexCount for an example.
8. The latest Python helper scripts can be imported into your main python file to access available functions.

   import liquibase_utilities
   import liquibase_changesets
   
   print(liquibase_changesets.get_labels(liquibase_utilities.get_changeset()))
   

9. LoadData change types are not supported.
10. Having the commercial Mongo extension in the lib directory will cause some relational change types to behave incorrectly (e.g., createIndex).
11. Environment variables can be accessed using the os module.

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

Local Python Environment

Not required to use or develop custom policy checks

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

1. Download and extract GraalPy.
2. Add <install dir>/bin (or <install dir>\bin for Windows) to your path.
3. 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>
   

4. 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
   

5. Install Python modules. Sqlpare is required.

   graalpy -m pip install sqlparse
   

6. Deactive the environment (exit or run bin/deactivate). Deactivate on Windows may throw an error (safe to close the GitBash window).
7. 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'
   

8. 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

1. NoDeleteWithoutWhere

   Key	Value
   Database	Any

   liquibase checks customize --check-name=CustomCheckTemplate
   

   Prompt	Response
   Short Name	NoDeleteWithoutWhere
   Severity	0-4
   Description	DELETE statements must have a WHERE clause.
   Scope	changelog
   Message	All DELETE statements must have a WHERE clause.
   Type	python
   Path	Scripts/delete_without_where.py
   Args
   Snapshot	false

2. TableNamesMustBeUppercase

   Key	Value
   Database	Relational

   liquibase checks customize --check-name=CustomCheckTemplate
   

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

3. VarcharMaxSize

   Key	Value
   Database	Oracle

   liquibase checks customize --check-name=CustomCheckTemplate
   

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

4. PKNamingConvention

   Key	Value
   Database	Relational

   liquibase checks customize --check-name=CustomCheckTemplate
   

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

5. VarcharDataIntegrity

   Key	Value
   Database	Relational

   liquibase checks customize --check-name=CustomCheckTemplate
   

   Prompt	Response
   Short Name	VarcharDataIntegrity
   Severity	0-4
   Description	VARCHAR columns cannot accept numeric data.
   Scope	changelog
   Message	Inserting numeric data into column __COLUMN_NAME__ is not allowed.
   Type	python
   Path	Scripts/varchar_data_integrity.py
   Args
   Snapshot	true

6. CollectionMustHaveValidator

   Key	Value
   Database	MongoDB

   liquibase checks customize --check-name=CustomCheckTemplate
   

   Prompt	Response
   Short Name	CollectionMustHaveValidator
   Severity	0-4
   Description	New collections must include a validator.
   Scope	changelog
   Message	New collections must include a validator.
   Type	python
   Path	Scripts/collection_without_validator.py
   Args
   Snapshot	false

7. PKNamingConvention

   Key	Value
   Database	Relational

   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

8. CreateIndexCount

   Key	Value
   Database	Relational

   liquibase checks customize --check-name=CustomCheckTemplate
   

   Prompt	Response
   Short Name	CreateIndexCount
   Severity	0-4
   Description	Tables can have a maximum of MAX_INDEX indexes.
   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

9. TableColumnDisallow

   Key	Value
   Database	Oracle

   liquibase checks customize --check-name=CustomCheckTemplate
   

   Prompt	Response
   Short Name	TableColumnDisallow
   Severity	0-4
   Description	Warn if DATA_TYPE columns are used.
   Scope	changelog
   Message	Datatype __COLUMN_TYPE__ is discouraged for column __COLUMN_NAME__.
   Type	python
   Path	Scripts/table_column_disallow.py
   Args	DATA_TYPE=CLOB
   Snapshot	false

10. TableColumnNameSize

    Key	Value
    Database	Relational

    liquibase checks customize --check-name=CustomCheckTemplate
    

    Prompt	Response
    Short Name	TableColumnNameSize
    Severity	0-4
    Description	Column names must be MAX_SIZE or lower in length.
    Scope	database
    Message	Name of __OBJECT_TYPE__ __OBJECT_NAME__ is __CURRENT_SIZE__ characters.
    Type	python
    Path	Scripts/table_column_name_size.py
    Args	MAX_SIZE=10
    Snapshot	false

11. CurrentSchemaOnly

    Key	Value
    Database	Oracle

    liquibase checks customize --check-name=CustomCheckTemplate
    

    Prompt	Response
    Short Name	CurrentSchemaOnly
    Severity	0-4
    Description	Only changes in current schema are allowed.
    Scope	changelog
    Message	Only changes to schema __SCHEMA_NAME__ are allowed.
    Type	python
    Path	Scripts/current_schema_only.py
    Args
    Snapshot	true

12. FKNamingConvention

    Key	Value
    Database	Relational

    liquibase checks customize --check-name=CustomCheckTemplate
    

    Prompt	Response
    Short Name	FKNamingConvention
    Severity	0-4
    Description	Foreign keys must include parent and child table names.
    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

13. PKTablespace

    Key	Value
    Database	Oracle

    liquibase checks customize --check-name=CustomCheckTemplate
    

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

14. ColumnDefaultValue

    Key	Value
    Database	Oracle

    liquibase checks customize --check-name=CustomCheckTemplate
    

    Prompt	Response
    Short Name	ColumnDefaultValue
    Severity	0-4
    Description	New columns must not have a default value assigned.
    Scope	changelog
    Message	Column __COLUMN_NAME__ in table __TABLE_NAME__ should not have a default value.
    Type	python
    Path	Scripts/column_default_value.py
    Args
    Snapshot	false

15. Varchar2MustUseChar

    Key	Value
    Database	Oracle

    liquibase checks customize --check-name=CustomCheckTemplate
    

    Prompt	Response
    Short Name	Varchar2MustUseChar
    Severity	0-4
    Description	Varchar2 column Must Define Char instead of Bytes
    Scope	changelog
    Message	VARCHAR2 column __COLUMN_NAME__ must use CHAR instead of default BYTES
    Type	python
    Path	Scripts/varchar2_must_use_char.py
    Args
    Snapshot	false

16. VarcharPreferred

    Key	Value
    Database	Oracle

    liquibase checks customize --check-name=CustomCheckTemplate
    

    Prompt	Response
    Short Name	VarcharPreferred
    Severity	0-4
    Description	Warn if CHAR data type is used.
    Scope	database
    Message	Column __COLUMN_NAME__ has type CHAR, VARCHAR preferred.
    Type	python
    Path	Scripts/varchar_preferred.py
    Args
    Snapshot	false

17. CreateTableTablespace

    Key	Value
    Database	Oracle

    liquibase checks customize --check-name=CustomCheckTemplate
    

    Prompt	Response
    Short Name	CreateTableTablespace
    Severity	0-4
    Description	New tables must include a tablespace definition.
    Scope	changelog
    Message	Table __TABLE_NAME__ must include explicit tablespace definition.
    Type	python
    Path	Scripts/create_table_tablespace.py
    Args
    Snapshot	false

18. IdentifiersWithoutQuotes

    Key	Value
    Database	Relational

    liquibase checks customize --check-name=CustomCheckTemplate
    

    Prompt	Response
    Short Name	IdentifiersWithoutQuotes
    Severity	0-4
    Description	Identifiers should not include quotes.
    Scope	changelog
    Message	Identifier __ID_NAME__ should not include quotes.
    Type	python
    Path	Scripts/identifiers_without_quotes.py
    Args
    Snapshot	false

19. IndexMustUseDifferentTablespace

    Key	Value
    Database	Oracle
    Example Changesets	createindex.sql

    liquibase checks customize --check-name=CustomCheckTemplate
    

    Prompt	Response
    Short Name	IndexMustUseDifferentTablespace
    Severity	0-4
    Description	Index of table must be in a different tablespace than table.
    Scope	changelog
    Message	Index __INDEX_NAME__ must be in a different tablespace than __TABLE_NAME__ tablespace __TABLE_SPACE__
    Type	python
    Path	Scripts/index_in_different_tablespace.py
    Args
    Snapshot	true

20. TestFormattedSQL

    Key	Value
    Changelog	Formatted SQL only

    liquibase checks customize --check-name=CustomCheckTemplate
    

    Prompt	Response
    Short Name	TestFormattedSQL
    Severity	0-4
    Description	SQL files must include Liquibase meta data.
    Scope	changelog
    Message	SQL files must include Liquibase meta data.
    Type	python
    Path	Scripts/test_formatted_sql.py
    Args
    Snapshot	false

Contact Liquibase

Liquibase sales: https://www.liquibase.com/contact

Liquibase support: https://support.liquibase.com