NAME
    SHARYANTO::SQL::Schema - Routine and convention to create/update your
    application's DB schema

VERSION
    version 0.06

DESCRIPTION
    This module uses Log::Any for logging.

    To use this module, you typically run the create_or_update_db_schema()
    routine at the start of your program/script, e.g.:

     use DBI;
     use SHARYANTO::SQL::Schema qw(create_or_update_db_schema);
     my $spec = {...}; # the schema specification
     my $dbh = DBI->connect(...);
     my $res = create_or_update_db_schema(dbh=>$dbh, spec=>$spec);
     die "Cannot run the application: cannot create/upgrade database schema: $res->[1]"
         unless $res->[0] == 200;

    This way, your program automatically creates/updates database schema
    when run. Users need not know anything.

SEE ALSO
    Some other database migration tools that directly uses SQL:

    *   Database::Migrator

        Pretty much similar, albeit more fully-fledged/involved. You have to
        use OO style. You put each version's SQL in a separate file and
        subdirectory. Perl scripts can also be executed for each version
        upgrade. Meta table is configurable (default recommended is
        'AppliedMigrations').

DESCRIPTION
    This module has Rinci metadata.

FUNCTIONS
    None are exported by default, but they are exportable.

  create_or_update_db_schema(%args) -> [status, msg, result, meta]
    Routine and convention to create/update your application's DB schema.

    With this routine (and some convention) you can easily create and update
    database schema for your application in a simple (and boring a.k.a.
    using plain SQL) way.

    *Version*: version is an integer and starts from 1. Each software
    release with schema change will bump the version number to 1. Version
    information is stored in a special table called "meta" (SELECT value
    FROM meta WHERE name='schema_version').

    You supply the SQL statements in "spec". "spec" is a hash which contains
    the key "install" (the value of which is a series of SQL statements to
    create the schema from nothing). It should be the SQL statements to
    create the latest version of the schema.

    There should also be zero or more "upgrade_to_v$VERSION" keys, the value
    of each is a series of SQL statements to upgrade from ($VERSION-1) to
    $VERSION. So there could be "upgrade_to_v2", "upgrade_to_v3", and so on
    up the latest version.

    This routine will connect to database and check the current schema
    version. If "meta" table does not exist yet, the SQL statements in
    "install" will be executed. The "meta" table will also be created and a
    row ('schema_version', 1) is added.

    If "meta" table already exists, schema version will be read from it and
    one or more series of SQL statements from "upgrade_to_v$VERSION" will be
    executed to bring the schema to the latest version.

    Currently only tested on MySQL, Postgres, and SQLite. Postgres is
    recommended because it can do transactional DDL (a failed upgrade in the
    middle will not cause the database schema state to be inconsistent, e.g.
    in-between two versions).

    Arguments ('*' denotes required arguments):

    *   dbh* => *obj*

        DBI database handle.

    *   spec* => *hash*

        SQL statements to create and update schema.

        Example:

            {
                install => [
                    'CREATE TABLE IF NOT EXISTS t1 (...)',
                    'CREATE TABLE IF NOT EXISTS t2 (...)',
                ],
    
                upgrade_to_v2 => [
                    'ALTER TABLE t1 ADD COLUMN c5 INT NOT NULL',
                    'CREATE UNIQUE INDEX i1 ON t2(c1)',
                ],
    
                upgrade_to_v3 => [
                    'ALTER TABLE t2 DROP COLUMN c2',
                ],
            }

    Return value:

    Returns an enveloped result (an array). First element (status) is an
    integer containing HTTP status code (200 means OK, 4xx caller error, 5xx
    function error). Second element (msg) is a string containing error
    message, or 'OK' if status is 200. Third element (result) is optional,
    the actual result. Fourth element (meta) is called result metadata and
    is optional, a hash that contains extra information.

AUTHOR
    Steven Haryanto <stevenharyanto@gmail.com>

COPYRIGHT AND LICENSE
    This software is copyright (c) 2013 by Steven Haryanto.

    This is free software; you can redistribute it and/or modify it under
    the same terms as the Perl 5 programming language system itself.