view release on metacpan or  search on metacpan

README.md  view on Meta::CPAN

# NAME

App::JESP - Just Enough SQL Patches

# SYNOPSIS

Use the command line utility:

    jesp

Or use from your own program (in Perl):

    my $jesp = App::JESP->new({
        interactive => 0, # No ANSI color
        home        => 'path/to/jesphome',
        dsn         => ...,
        username    => ...,
        password    => ...
    });

    $jesp->install();
    $jesp->deploy();

# CONFIGURATION

All JESP configuration must live in a JESP home directory.

This home directory must contain a plan.json file, containing the patching
plan for your DB. See plan.json section below for the format of this file.

## plan.json

This file MUST live in your JESP home directory. It has to contain
a json datastructure like this:

    {
      "patches": [
          { "id":"foobar_sql",        "sql":  "CREATE TABLE foobar(id INT PRIMARY KEY)"},
          { "id":"foobar_rel",        "file": "patches/morefoobar.sql" }
          { "id":"foobar_abs",        "file": "/absolute/path/to/patches/evenmore.sql" },
          { "id":"a_backfill_script", "file": "path/to/executable/file.sh" },
      ],
    }

Patches MUST have a unique `id` in all the plan, and they can either contain raw SQL
(`sql` key), or point to a `file` (absolute, or relative to the JESP home) containing
the SQL.

The `id` is a VARCHAR(512). While it doesn't indicate any ordering, a simple and useful
way to keep the IDs unique is to provide a date/timestamp (of when the patch was
_authored_) plus a free form description of the change.

The [JSON](https://metacpan.org/pod/JSON) file is parsed with the relaxed flag, which means it can contain trailing
commas (and # comments). The trailing commas are particularly useful, since commit diffs
and merge conflicts will be contained to the new line that was added.

You are encouraged to look in [https://github.com/jeteve/App-JESP/tree/master/t](https://github.com/jeteve/App-JESP/tree/master/t) for examples.

# PATCH TYPES

# sql

Simply add the SQL statement to execute in your patch structure:

    {
      "patches": [
          ...
          { "id":"2017-11-02: create table foobar", "sql": "CREATE TABLE foobar(id INT PRIMARY KEY)"}
          ...
    }

This is convenient for very short SQL statment.

# sql files

Point to a file that contains SQL statement(s) to be executed. The filename can be either absolute
or relative to the directory that contains the plan.