From ed397093c264e5a9c47e417be66585da96b8a2ee Mon Sep 17 00:00:00 2001 From: Markus Witt Date: Sat, 13 Apr 2019 23:36:58 +0200 Subject: database setup: merge setup and migration code paths --- lib/Travelynx/Command/database.pm | 98 +++++++++++++++++++++++---------------- 1 file changed, 58 insertions(+), 40 deletions(-) (limited to 'lib') diff --git a/lib/Travelynx/Command/database.pm b/lib/Travelynx/Command/database.pm index 5e1b38a..1c97cce 100644 --- a/lib/Travelynx/Command/database.pm +++ b/lib/Travelynx/Command/database.pm @@ -9,10 +9,18 @@ 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]; + my $schema_version + = $dbh->selectall_arrayref(qq{select version from schema_version}); + + if ( not defined $schema_version ) { + return undef; + } + elsif ( @{$schema_version} == 1 ) { + return $schema_version->[0][0]; + } + else { + printf( "Found multiple schema versions: %s", @{$schema_version} ); + exit(1); } } @@ -46,7 +54,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 +74,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 +115,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 +171,7 @@ sub run { } else { say "no"; - $exit_status = 1; + exit(1); } } else { @@ -158,8 +179,6 @@ sub run { } $dbh->disconnect; - - exit($exit_status); } 1; @@ -168,12 +187,11 @@ __END__ =head1 SYNOPSIS - Usage: index.pl database + Usage: index.pl database 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 -- cgit v1.2.3 From c19f239d1d582b5580fb8effaace97285edb06c8 Mon Sep 17 00:00:00 2001 From: Daniel Friesel Date: Sun, 14 Apr 2019 08:44:27 +0200 Subject: Do not print error when encountering an empty database --- lib/Travelynx/Command/database.pm | 22 ++++++++++++++++------ 1 file changed, 16 insertions(+), 6 deletions(-) (limited to 'lib') diff --git a/lib/Travelynx/Command/database.pm b/lib/Travelynx/Command/database.pm index 1c97cce..190063b 100644 --- a/lib/Travelynx/Command/database.pm +++ b/lib/Travelynx/Command/database.pm @@ -9,17 +9,27 @@ has usage => sub { shift->extract_usage }; sub get_schema_version { my ($dbh) = @_; - my $schema_version - = $dbh->selectall_arrayref(qq{select version from schema_version}); - if ( not defined $schema_version ) { + # 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; } - elsif ( @{$schema_version} == 1 ) { - return $schema_version->[0][0]; + + my $rows = $sth->fetchall_arrayref; + + if ( @{$rows} == 1 ) { + return $rows->[0][0]; } else { - printf( "Found multiple schema versions: %s", @{$schema_version} ); + printf( "Found multiple schema versions: %s", @{$rows} ); exit(1); } } -- cgit v1.2.3