ORLite::Migrate(3pm)

💖 ORLite::Migrate 💖

okay so bestie, this is the manual page for ORLite::Migrate (that's the 3pm one, from User Contributed Perl Documentation), and I'm gonna walk you through it like we're texting at 2am. no cap. ✨

---

🌈 NAME 🌈

ORLite::Migrate — the most extremely light weight, SQLite-specific schema migration situation you've ever met. it's giving tiny but powerful. 🦋

---

✨ SYNOPSIS ✨

so first you build your ORM class using a patch timeline that lives in the shared files directory. like this:

# Build your ORM class using a patch timeline
# stored in the shared files directory.
use ORLite::Migrate {
    create       => 1,
    file         => 'sqlite.db',
    timeline     => File::Spec->catdir(
        File::ShareDir::module_dir('My::Module'), 'patches',
    ),
    user_version => 8,
};

and then here's migrate-1.pl, a trivial schema patch, a little baby one, adorable:

# migrate-1.pl - A trivial schema patch
#!/usr/bin/perl

use strict;
use DBI ();

# Locate the SQLite database
my $file = <STDIN>;
chomp($file);
unless ( -f $file and -w $file ) {
    die "SQLite file $file does not exist";
}

# Connect to the SQLite database
my $dbh = DBI->connect("dbi:SQLite(RaiseError=>1):$file");
unless ( $dbh ) {
  die "Failed to connect to $file";
}

$dbh->do( <<'END_SQL' );
create table foo (
    id integer not null primary key,
    name varchar(32) not null
)
END_SQL

periodt. 💅

---

🎀 DESCRIPTION 🎀

SQLite is a light weight single file SQL database that provides an excellent platform for embedded storage of structured data. iconic behaviour honestly.

ORLite is a light weight single class Object-Relational Mapper (ORM) system specifically designed for (and limited to only) working with SQLite. it does one thing and it slays at that one thing.

ORLite::Migrate is a light weight single class Database Schema Migration enhancement for ORLite. main character energy, tiny footprint.

it provides a simple implementation of schema versioning inside the SQLite database using the built-in user_version pragma (which is set to zero by default, fyi 🌸).

when you're setting up the ORM class, you hand it an extra timeline parameter. that should be either a monolithic timeline class, or a directory containing standalone migration scripts. pick your fighter.

a timeline is a set of revisioned schema changes, applied in order, representing the evolution of the database schema over time. the end of the timeline — represented by the highest revision number — represents the "current" anticipated schema for the application. it's the glow-up arc of your database, fr fr.

and because the patch sequence can be calculated from any arbitrary starting version, keeping the historical set of changes in your application as schema patches means a user on any older application version can install the most current version and have their database upgraded smoothly and safely. no drama, no tears, just vibes. 🫶

the recommended place to store the migration timeline is a shared files directory, locatable using one of the functions from File::ShareDir.

so the timeline for your application can be specified in two different forms, and each has its own advantages and disadvantages. let's get into it 👑

🌸 Timeline Directories 🌸

a Timeline Directory is a directory on the filesystem containing a set of Perl scripts named in a consistent pattern.

these patch scripts are named in the form migrate-$version.pl, where $version is the schema version to migrate to. a typical timeline directory will look something like the following.

migrate-01.pl
migrate-02.pl
migrate-03.pl
migrate-04.pl
migrate-05.pl
migrate-06.pl
migrate-07.pl
migrate-08.pl
migrate-09.pl
migrate-10.pl

look at them all lined up. organized. disciplined. 💅

ORLite::Migrate formulates a migration plan that starts at the current database user_version pragma value, executing the migration script that has the version user_version + 1, then executing user_version + 2 and so on.

it will continue stepping forwards until it runs out of patches to execute. then it just... stops. done. ✨

the main advantage of a timeline directory is that each patch is run in its own process and interpreter. hundreds of patches can be produced by many different authors, with certainty that the changes described in each will be executed as intended. collaborative slay.

the main disadvantage of using a timeline directory is that your application must be able to identify the Perl interpreter it is run in so that it can execute a sub-process. this may be difficult or impossible in cases such as PAR-packaged applications and Perl interpreters embedded inside .exe wrappers or larger non-Perl applications. 😭

in general, it is recommended that you use the timeline directory approach unless you encounter a situation in which sub-process execution (or locating the patch files) is difficult.

🦋 Timeline Classes 🦋

a timeline class places all of the schema patches into a single Perl module, with each patch represented as a method name. one file, many methods, very demure.

the following is an example of a trivial timeline class.

package t::lib::MyTimeline;

use strict;
use base 'ORLite::Migrate::Timeline';

my $UPGRADE1 = <<'END_SQL';

create table foo (
    id integer not null primary key,
    name varchar(32) not null
);

insert into foo values ( 1, 'foo' )

END_SQL

sub upgrade1 {
    my $self = shift;
    foreach ( split /;\s+/, $UPGRADE1 ) {
        $self->do($_);
    }
    return 1;
}

sub upgrade2 {
    $_[0]->do("insert into foo values ( 2, 'bar' )");
}

sub upgrade3 {
    $_[0]->do("insert into foo values ( 3, 'baz' )");
}

1;

as with the patch files, the current state of the user_version pragma will be examined, and each upgradeN method will be called to advance the schema forwards.

the main advantage of a timeline class is that you will not need to execute sub-processes, and so a timeline class will continue to function even in unusual or exotic process contexts such as PAR packaging or .exe wrappers. resilient queen. 👑

the main disadvantage of a timeline class is that the entire timeline code must be loaded into memory no matter how many patch steps are needed (and stay in memory after the migration has completed), and all patches share a common interpreter and thus can potentially pollute or corrupt each other. ⚠️ like, they're roommates who don't respect boundaries.

---

💜 SUPPORT 💜

bugs should be reported via the CPAN bug tracker at

<http://rt.cpan.org/NoAuth/ReportBug.html?Queue=ORLite-Migrate>

for other issues, contact the author.

---

👑 AUTHOR 👑

Adam Kennedy <adamk@cpan.org>

---

🌈 COPYRIGHT 🌈

Copyright 2009 - 2012 Adam Kennedy.

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

The full text of the license can be found in the LICENSE file included with this module.

---

and that's the whole page, bestie!! this was ORLite::Migrate(3pm), shipped as part of perl v5.36.0 and dated 2023-07-01. thanks for reading, ily, go migrate something safely 🎀✨💖