summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rwxr-xr-xmigrate.pl79
1 files changed, 79 insertions, 0 deletions
diff --git a/migrate.pl b/migrate.pl
new file mode 100755
index 0000000..84964bb
--- /dev/null
+++ b/migrate.pl
@@ -0,0 +1,79 @@
+#!/usr/bin/env perl
+
+use strict;
+use warnings;
+use 5.020;
+
+use DBI;
+
+my $dbname = $ENV{TRAVELYNX_DB_FILE} // 'travelynx.sqlite';
+my $dbh = DBI->connect( "dbi:SQLite:dbname=${dbname}", q{}, q{} );
+
+my $has_version_query = $dbh->prepare(qq{
+ select name from sqlite_master
+ where type = 'table' and name = 'schema_version';
+});
+
+sub get_schema_version {
+ $has_version_query->execute();
+ my $rows = $has_version_query->fetchall_arrayref;
+ if (@{$rows} == 1) {
+ my $get_version_query = $dbh->prepare(qq{
+ select version from schema_version;
+ });
+ $get_version_query->execute();
+ my $rows = $get_version_query->fetchall_arrayref;
+ if (@{$rows} == 0) {
+ return -1;
+ }
+ return $rows->[0][0];
+ }
+ return 0;
+}
+
+my @migrations = (
+ # v0 -> v1
+ sub {
+ $dbh->do(qq{
+ begin transaction;
+ });
+ $dbh->do(qq{
+ create table schema_version (
+ version integer primary key
+ );
+ });
+ $dbh->do(qq{
+ insert into schema_version (version) values (1);
+ });
+ $dbh->do(qq{
+ create table new_users (
+ id integer primary key,
+ name char(64) not null unique,
+ status int not null,
+ email char(256),
+ password text,
+ registered_at datetime,
+ last_login datetime
+ );
+ });
+ my $get_users_query = $dbh->prepare(qq{
+ select * from users;
+ });
+ $dbh->do(qq{
+ commit;
+ });
+ },
+);
+
+my $schema_version = get_schema_version();
+
+say "Found travelynx schema v${schema_version}";
+
+if ($schema_version == @migrations) {
+ say "Database schema is up-to-date";
+}
+
+for my $i ($schema_version .. $#migrations) {
+ printf("Updating to v%d\n", $i + 1);
+ $migrations[$i]();
+}