1x-eng/sql_idempotency_doctor

More of a CLI utility to help confirm idempotency of SQL scripts in a namespace that's init, deployed &/ managed by `sqitch`.

sql_idempotency_doctor

A CLI tool to check/verify the idempotency of PLPGSQL scripts.

sql_idempotency_doctor is a user-friendly command-line interface that verifies if the PLPGSQL scripts in the specified deploy and revert directories (usually created & maintained by sqitch) are idempotent.

This tool is built using the Cobra framework.

Table of Contents

• Installation
• Usage
• Examples
• Getting Started

Installation

1. Download the latest binary for your platform from the releases page.
2. Extract the archive and place the sql_idempotency_doctor / sid executable in a directory included in your system's PATH.

Alternatively, you can build the project from source:

1. Ensure you have Go installed on your system.
2. Clone the repository and navigate to the project root folder:

git clone https://github.com/your_username/sql_idempotency_doctor.git
cd sql_idempotency_doctor

3. Build the executable:

go build -o sid

4. Move the sid executable to a directory included in your system's PATH.

Usage

To check the idempotency of SQL scripts, run the following command:

sid check -p /path/to/directory -n namespace

Replace /path/to/directory with the actual path to the directory containing the deploy and revert folders, and replace namespace as you see fit.

Examples

Consider the following directory structure:

/path/to/directory
├── deploy
│   └── namespace.sql
└── revert
    └── namespace.sql

To check the idempotency of the SQL scripts in the deploy and revert directories, run:

sid check -p /path/to/directory -n namespace

where scripts in <namespace.sql> that require assessment must be decorated with:

--@ddl:start
CREATE OR REPLACE FUNCTION a_function()
RETURNS BOOLEAN AS $$
    SELECT true
$$ LANGUAGE sql STABLE; 
--@ddl:end

For more samples, refer examples.sql

GettingStarted

To set up the development environment, follow these steps:

1. Clone the repository:

git clone https://github.com/your_username/sql_idempotency_doctor.git

2. Navigate to the project root folder:

cd sql_idempotency_doctor

3. Install dependencies:

go mod download

4. Make your changes and build the executable:

go build -o sid

5. Test your changes:

./sid check -p /path/to/directory -n namespace