SHARYANTO::SQL::Schema - Routine and convention to create/update your application's DB schema
version 0.03
This module has Rinci metadata.
None are exported by default, but they are exportable.
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').
meta
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.
spec
install
There should also be zero or more upgrade_to_v$VER keys, the value of each is a series of SQL statements to upgrade from ($VER-1) to $VER. So there could be upgrade_to_v2, upgrade_to_v3, and so on up the latest version.
upgrade_to_v$VER
upgrade_to_v2
upgrade_to_v3
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$VER will be executed to bring the schema to the latest version.
Currently only tested on MySQL, Postgres, and SQLite.
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.
Steven Haryanto <stevenharyanto@gmail.com>
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.
To install SHARYANTO::SQL::Schema, copy and paste the appropriate command in to your terminal.
cpanm
cpanm SHARYANTO::SQL::Schema
CPAN shell
perl -MCPAN -e shell install SHARYANTO::SQL::Schema
For more information on module installation, please visit the detailed CPAN module installation guide.