NAME ==== DB::Migration::Simple - Simple DB migrations. Go up and down in versions. SYNOPSIS ======== use v6; use DB::Migration::Simple; use DBIish; my $dbh = DBIish.connect('SQLite', :database); my $m = DB::Migration::Simple.new(:$dbh :migration-file); # optional parameter: :$verbose # optional parameter: :$migration-table-name say $m.current-version(); $m.migrate(version<42>, :verbose); # go to version 42 and be informative $m.migrate(); # migrate to latest version DESCRIPTION =========== DB::Migration::Simple is a Perl 6 module to help with up- and downgrading a database schema between versions. Write an SQL-file that specifies actions for up and down migrations. DB::Migration::Simple does not depend on certain databases or versions thereof. It takes a dabatabase handle and trusts that the SQL you write will work with that handle. Lines starting with an # are comments Empty lines are ignored. Lines starting with "-- x up" denote the next version. Versions are integers. Comments are also allowed at the end of lines starting with "--": `-- 31 # Version 31 has a comment` The other lines are SQL that get sent to your database. Separate SQL statements with semicolons. Don't use SQL comments after a semicolon. Only one SQL statement per line. In fact, the semicolon has to be the last non-whitespace character on a line. (That is because we don't parse the SQL but just split at the semicolons and anchor the semicolon at the end of a line to avoid splitting inside SQL. Example: `SELECT .. FROM .. WHERE foo = ";"`. The splitting in turn is done before sending the statements to the DB because it looks like DBIish only supports one statement at a time. Might be wrong.) NOK: `CREATE ...; INSERT ...;` NOK: `CREATE ...(...); # comment` NOK: `CREATE ...; /* comment */` Example ------- -- 1 up # comment CREATE TABLE table_version_1( /* comment */ # comment /* or, SQL style comment */ id INTEGER PRIMARY KEY AUTOINCREMENT, msg TEXT ); INSERT INTO table_version_1 (msg) VALUES("This is version 1"); # comment --1 down DROP TABLE table_version_1; -- 2 up CREATE TABLE table_version_2 (msg TEXT); INSERT INTO table_version_2 VALUES ("This is version 2"); -- 2 down DROP TABLE table_version_2; -- 3 up CREATE TABLE table_version_3 (msg TEXT); -- 3 down DROP TABLE table_version_3; Migrating up from version 0 (empty database) to version 2 will do the following. - Migrate to version 1, using the commands under "-- 1 up". - Migrate to version 2, using the commands under "-- 2 up". Migrating down from version 2 to version 1 will use the commands under "-- 2 down". The migrations are wrapped in a transacation. In case of failure, the commands executed are rolled back, and you are left at the version before you called `$m.migrate()`. Verbose Mode ------------ For debugging or other reasons of interest, supply the :verbose flag my $m = DB::Migration::Simple.new(:$dbh, :verbose); Metadata -------- The migration meta information is stored in your database, in a table named "db-migration-simple-meta". You can choose a different table name: $migration-table.name = 'my-own-migration-meta-table-that-suits-me-better'; $m = DB::Migration::Simple.new(:$dbh :$migration-file :$migration-table-name); AUTHOR ====== Matthias Bloch matthias.bloch@puffin.ch This module was inspired by the Perl 5 Mojo::(DB-name-here)::Migrations modules. COPYRIGHT AND LICENSE ===================== Copyright © Matthias Bloch matthias.bloch@puffin.ch This library is free software; you can redistribute it and/or modify it under the Artistic License 2.0.