summaryrefslogtreecommitdiff
path: root/lib/Travelynx/Command/database.pm
diff options
context:
space:
mode:
authorDaniel Friesel <derf@finalrewind.org>2019-04-14 08:46:00 +0200
committerDaniel Friesel <derf@finalrewind.org>2019-04-14 08:46:00 +0200
commit17e8b659571d18f3df4de597ad5674b2eed1ae33 (patch)
tree6076120fd2cee28b4de4c21da6a6cb4ac131ab52 /lib/Travelynx/Command/database.pm
parentd7d6f8f89151ed46bf6be4f7d9b3d576a1d9f69d (diff)
parentc19f239d1d582b5580fb8effaace97285edb06c8 (diff)
Merge branch 'feuerrot-db_setup'
Diffstat (limited to 'lib/Travelynx/Command/database.pm')
-rw-r--r--lib/Travelynx/Command/database.pm108
1 files changed, 68 insertions, 40 deletions
diff --git a/lib/Travelynx/Command/database.pm b/lib/Travelynx/Command/database.pm
index 5e1b38a..190063b 100644
--- a/lib/Travelynx/Command/database.pm
+++ b/lib/Travelynx/Command/database.pm
@@ -9,10 +9,28 @@ has usage => sub { shift->extract_usage };
sub get_schema_version {
my ($dbh) = @_;
- for my $entry (
- $dbh->selectall_array(qq{select version from schema_version}) )
- {
- return $entry->[0];
+
+ # We do not want DBD to print an SQL error if schema_version does not
+ # exist, as this is not an error in this case. Setting $dbh->{PrintError} =
+ # 0 would disable error printing for all subsequent SQL operations, however,
+ # we only want to disable it for this specific query. Hence we use a
+ # prepared statement and only disable error printing only there.
+ my $sth = $dbh->prepare(qq{select version from schema_version});
+ $sth->{PrintError} = 0;
+ my $success = $sth->execute;
+
+ if ( not defined $success ) {
+ return undef;
+ }
+
+ my $rows = $sth->fetchall_arrayref;
+
+ if ( @{$rows} == 1 ) {
+ return $rows->[0][0];
+ }
+ else {
+ printf( "Found multiple schema versions: %s", @{$rows} );
+ exit(1);
}
}
@@ -46,7 +64,6 @@ sub initialize_db {
action_id smallint not null,
station_id int references stations (id),
action_time timestamptz not null,
- edited smallint not null,
train_type varchar(16),
train_line varchar(16),
train_no varchar(16),
@@ -67,7 +84,7 @@ sub initialize_db {
token varchar(80) not null,
primary key (user_id, type)
);
- insert into schema_version values (2);
+ insert into schema_version values (0);
}
);
}
@@ -108,41 +125,55 @@ my @migrations = (
},
);
+sub setup_db {
+ my ($dbh) = @_;
+ $dbh->begin_work;
+ if ( initialize_db($dbh) ) {
+ $dbh->commit;
+ }
+ else {
+ $dbh->rollback;
+ printf( "Database initialization was not successful: %s",
+ $DBI::errstr );
+ }
+}
+
+sub migrate_db {
+ my ($dbh) = @_;
+ $dbh->begin_work;
+ my $schema_version = get_schema_version($dbh);
+ say "Found travelynx schema v${schema_version}";
+ if ( $schema_version == @migrations ) {
+ say "Database layout is up-to-date";
+ }
+ for my $i ( $schema_version .. $#migrations ) {
+ printf( "Updating to v%d ...\n", $i + 1 );
+ if ( not $migrations[$i]($dbh) ) {
+ say "Aborting migration; rollback to v${schema_version}";
+ $dbh->rollback;
+ exit(1);
+ }
+ }
+ if ( get_schema_version($dbh) == @migrations ) {
+ $dbh->commit;
+ }
+}
+
sub run {
my ( $self, $command ) = @_;
- my $exit_status = 0;
my $dbh = $self->app->dbh;
- if ( $command eq 'setup' ) {
- $dbh->begin_work;
- if ( initialize_db($dbh) ) {
- $dbh->commit;
- }
- else {
- $dbh->rollback;
- $exit_status = 1;
- }
+ if ( not defined $dbh ) {
+ printf( "Can't connect to the database: %s\n", $DBI::errstr );
+ exit(1);
}
- elsif ( $command eq 'migrate' ) {
- $dbh->begin_work;
- my $schema_version = get_schema_version($dbh);
- say "Found travelynx schema v${schema_version}";
- if ( $schema_version == @migrations ) {
- say "Database layout is up-to-date";
- }
- for my $i ( $schema_version .. $#migrations ) {
- printf( "Updating to v%d ...\n", $i + 1 );
- if ( not $migrations[$i]($dbh) ) {
- say "Aborting migration; rollback to v${schema_version}";
- $dbh->rollback;
- $exit_status = 1;
- last;
- }
- }
- if ( get_schema_version($dbh) == @migrations ) {
- $dbh->commit;
+
+ if ( $command eq 'migrate' ) {
+ if ( not defined get_schema_version($dbh) ) {
+ setup_db($dbh);
}
+ migrate_db($dbh);
}
elsif ( $command eq 'has-current-schema' ) {
if ( get_schema_version($dbh) == @migrations ) {
@@ -150,7 +181,7 @@ sub run {
}
else {
say "no";
- $exit_status = 1;
+ exit(1);
}
}
else {
@@ -158,8 +189,6 @@ sub run {
}
$dbh->disconnect;
-
- exit($exit_status);
}
1;
@@ -168,12 +197,11 @@ __END__
=head1 SYNOPSIS
- Usage: index.pl database <setup|migrate|has-current-schema>
+ Usage: index.pl database <migrate|has-current-schema>
Upgrades the database layout to the latest schema.
Recommended workflow:
> systemctl stop travelynx
- > TRAVELYNX_DB_HOST=... TRAVELYNX_DB_NAME=... TRAVELYNX_DB_USER=... \
- TRAVELYNX_DB_PASSWORD=... perl index.pl migrate
+ > perl index.pl migrate
> systemctl start travelynx