| 1 |
NAME |
| 2 |
ORLite::Migrate - Extremely light weight SQLite-specific schema |
| 3 |
migration |
| 4 |
|
| 5 |
SYNOPSIS |
| 6 |
# Build your ORM class using a patch timeline |
| 7 |
# stored in the shared files directory. |
| 8 |
use ORLite::Migrate { |
| 9 |
create => 1, |
| 10 |
file => 'sqlite.db', |
| 11 |
timeline => File::Spec->catdir( |
| 12 |
File::ShareDir::module_dir('My::Module'), 'patches', |
| 13 |
), |
| 14 |
user_version => 8, |
| 15 |
}; |
| 16 |
|
| 17 |
# migrate-1.pl - A trivial schema patch |
| 18 |
#!/usr/bin/perl |
| 19 |
|
| 20 |
use strict; |
| 21 |
use DBI (); |
| 22 |
|
| 23 |
# Locate the SQLite database |
| 24 |
my $file = <STDIN>; |
| 25 |
chomp($file); |
| 26 |
unless ( -f $file and -w $file ) { |
| 27 |
die "SQLite file $file does not exist"; |
| 28 |
} |
| 29 |
|
| 30 |
# Connect to the SQLite database |
| 31 |
my $dbh = DBI->connect("dbi:SQLite(RaiseError=>1):$file"); |
| 32 |
unless ( $dbh ) { |
| 33 |
die "Failed to connect to $file"; |
| 34 |
} |
| 35 |
|
| 36 |
$dbh->do( <<'END_SQL' ); |
| 37 |
create table foo ( |
| 38 |
id integer not null primary key, |
| 39 |
name varchar(32) not null |
| 40 |
) |
| 41 |
END_SQL |
| 42 |
|
| 43 |
DESCRIPTION |
| 44 |
SQLite is a light weight single file SQL database that provides an |
| 45 |
excellent platform for embedded storage of structured data. |
| 46 |
|
| 47 |
ORLite is a light weight single class Object-Relational Mapper (ORM) |
| 48 |
system specifically designed for (and limited to only) work with SQLite. |
| 49 |
|
| 50 |
ORLite::Migrate is a light weight single class Database Schema Migration |
| 51 |
enhancement for ORLite. |
| 52 |
|
| 53 |
It provides a simple implementation of schema versioning within the |
| 54 |
SQLite database using the built-in "user_version" pragma (which is set |
| 55 |
to zero by default). |
| 56 |
|
| 57 |
When setting up the ORM class, an additional "timeline" parameter is |
| 58 |
provided, which should be either a monolithic timeline class, or a |
| 59 |
directory containing standalone migration scripts. |
| 60 |
|
| 61 |
A "timeline" is a set of revisioned schema changed, to be applied in |
| 62 |
order and representing the evolution of the database schema over time. |
| 63 |
The end of the timeline, representing by the highest revision number, |
| 64 |
represents the "current" anticipated schema for the application. |
| 65 |
|
| 66 |
Because the patch sequence can be calculated from any arbitrary starting |
| 67 |
version, by keeping the historical set of changes in your application as |
| 68 |
schema patches it is possible for the user of any older application |
| 69 |
version to install the most current version of an application and have |
| 70 |
their database upgraded smoothly and safely. |
| 71 |
|
| 72 |
The recommended location to store the migration timeline is a shared |
| 73 |
files directory, locatable using one of the functions from |
| 74 |
File::ShareDir. |
| 75 |
|
| 76 |
The timeline for your application can be specified in two different |
| 77 |
forms, with different advantages and disadvantages. |
| 78 |
|
| 79 |
Timeline Directories |
| 80 |
A Timeline Directory is a directory on the filesystem containing a set |
| 81 |
of Perl scripts named in a consistent pattern. |
| 82 |
|
| 83 |
These patch scripts are named in the form migrate-$version.pl, where |
| 84 |
$version is the schema version to migrate to. A typical timeline |
| 85 |
directory will look something like the following. |
| 86 |
|
| 87 |
migrate-01.pl |
| 88 |
migrate-02.pl |
| 89 |
migrate-03.pl |
| 90 |
migrate-04.pl |
| 91 |
migrate-05.pl |
| 92 |
migrate-06.pl |
| 93 |
migrate-07.pl |
| 94 |
migrate-08.pl |
| 95 |
migrate-09.pl |
| 96 |
migrate-10.pl |
| 97 |
|
| 98 |
ORLite::Migrate formulates a migration plan that starts at the current |
| 99 |
database "user_version" pragma value, executing the migration script |
| 100 |
that has the version "user_version + 1", then executing "user_version + |
| 101 |
2" and so on. |
| 102 |
|
| 103 |
It will continue stepping forwards until it runs out of patches to |
| 104 |
execute. |
| 105 |
|
| 106 |
The main advantage of a timeline directory is that each patch is run in |
| 107 |
its own process and interpreter. Hundreds of patches can be produced by |
| 108 |
many different authors, with certainty that the changes described in |
| 109 |
each will be executed as intended. |
| 110 |
|
| 111 |
The main disadvantage of using a timeline directory is that your |
| 112 |
application must be able to identify the Perl interpreter it is run in |
| 113 |
so that it can execute a sub-process. This may be difficult or |
| 114 |
impossible for cases such as PAR-packaged applications and Perl |
| 115 |
interpreters embedded inside .exe wrappers or larger non-Perl |
| 116 |
applications. |
| 117 |
|
| 118 |
In general, it is recommended that you use the timeline directory |
| 119 |
approach unless you encounter a situation in which sub-process execution |
| 120 |
(or locating the patch files) is difficult. |
| 121 |
|
| 122 |
Timeline Classes |
| 123 |
A timeline class places all of the schema patches into a single Perl |
| 124 |
module, with each patch represented as a method name. |
| 125 |
|
| 126 |
The following is an example of a trivial timeline class. |
| 127 |
|
| 128 |
package t::lib::MyTimeline; |
| 129 |
|
| 130 |
use strict; |
| 131 |
use base 'ORLite::Migrate::Timeline'; |
| 132 |
|
| 133 |
my $UPGRADE1 = <<'END_SQL'; |
| 134 |
|
| 135 |
create table foo ( |
| 136 |
id integer not null primary key, |
| 137 |
name varchar(32) not null |
| 138 |
); |
| 139 |
|
| 140 |
insert into foo values ( 1, 'foo' ) |
| 141 |
|
| 142 |
END_SQL |
| 143 |
|
| 144 |
sub upgrade1 { |
| 145 |
my $self = shift; |
| 146 |
foreach ( split /;\s+/, $UPGRADE1 ) { |
| 147 |
$self->do($_); |
| 148 |
} |
| 149 |
return 1; |
| 150 |
} |
| 151 |
|
| 152 |
sub upgrade2 { |
| 153 |
$_[0]->do("insert into foo values ( 2, 'bar' )"); |
| 154 |
} |
| 155 |
|
| 156 |
sub upgrade3 { |
| 157 |
$_[0]->do("insert into foo values ( 3, 'baz' )"); |
| 158 |
} |
| 159 |
|
| 160 |
1; |
| 161 |
|
| 162 |
As with the patch files, the current state of the "user_version" pragma |
| 163 |
will be examined, and each "upgradeN" method will be called to advance |
| 164 |
the schema forwards. |
| 165 |
|
| 166 |
The main advantage of a timeline class is that you will not need to |
| 167 |
execute sub-processes, and so a timeline class will continue to function |
| 168 |
even in unusual or exotic process contents such as PAR packaging or .exe |
| 169 |
wrappers. |
| 170 |
|
| 171 |
The main disadvantage of a timeline class is that the entire timeline |
| 172 |
code must be loaded into memory no matter how many patch steps are |
| 173 |
needed (and stay in memory after the migration has completed), and all |
| 174 |
patches share a common interpreter and thus can potentially pollute or |
| 175 |
corrupt each other. |
| 176 |
|
| 177 |
SUPPORT |
| 178 |
Bugs should be reported via the CPAN bug tracker at |
| 179 |
|
| 180 |
<http://rt.cpan.org/NoAuth/ReportBug.html?Queue=ORLite-Migrate> |
| 181 |
|
| 182 |
For other issues, contact the author. |
| 183 |
|
| 184 |
AUTHOR |
| 185 |
Adam Kennedy <[email protected]> |
| 186 |
|
| 187 |
COPYRIGHT |
| 188 |
Copyright 2009 - 2012 Adam Kennedy. |
| 189 |
|
| 190 |
This program is free software; you can redistribute it and/or modify it |
| 191 |
under the same terms as Perl itself. |
| 192 |
|
| 193 |
The full text of the license can be found in the LICENSE file included |
| 194 |
with this module. |
| 195 |
|