From 61885d0dba3c8e06146aabb40031609ef1eb5b2e Mon Sep 17 00:00:00 2001 From: Dirk Koopman Date: Mon, 12 Mar 2012 23:26:55 +0000 Subject: [PATCH] breath on database storage engines Check that Mysql / sqlite databases work Add ipaddr column to spot table if required from now on add ip addresses to table if present (NULL otherwise) --- Changes | 4 ++++ perl/DXSql.pm | 16 +++++++++++----- perl/DXSql/SQLite.pm | 30 ++++++++++++++++++++++++++++-- perl/DXSql/mysql.pm | 26 +++++++++++++++++++++++++- perl/Spot.pm | 19 +++++++++++-------- 5 files changed, 79 insertions(+), 16 deletions(-) diff --git a/Changes b/Changes index 32a1306a..26a4bd0e 100644 --- a/Changes +++ b/Changes @@ -1,3 +1,7 @@ +12Mar12======================================================================= +1. Check database operations on MySQL/SQlite3. +2. Add ipaddr field to spot table if not present. +3. Add ipaddr field from PC61 to database stored spots. 09Mar12======================================================================= 1. Try to trap unblessed references on receipt of PC34s 15Feb12======================================================================= diff --git a/perl/DXSql.pm b/perl/DXSql.pm index 437f3bbf..d125cc59 100644 --- a/perl/DXSql.pm +++ b/perl/DXSql.pm @@ -62,7 +62,7 @@ sub connect my $dbh; eval { no strict 'refs'; - $dbh = DBI->connect($dsn, $user, $passwd, {AutoCommit => 0}); + $dbh = DBI->connect($dsn, $user, $passwd); }; unless ($dbh) { $active = 0; @@ -86,16 +86,19 @@ sub do eval { $self->{dbh}->do($s); }; } +sub begin_work +{ + $_[0]->{dbh}->begin_work; +} + sub commit { $_[0]->{dbh}->commit; - $_[0]->{dbh}->{AutoCommit} = 0; } sub rollback { $_[0]->{dbh}->rollback; - $_[0]->{dbh}->{AutoCommit} = 0; } sub quote @@ -111,7 +114,7 @@ sub prepare sub spot_insert_prepare { my $self = shift; - return $self->prepare('insert into spot values(?' . ',?' x 14 . ')'); + return $self->prepare('insert into spot values(?' . ',?' x 15 . ')'); } sub spot_insert @@ -121,6 +124,8 @@ sub spot_insert my $sth = shift; if ($sth) { + push @$spot, undef while @$spot < 15; + pop @$spot while @$spot > 15; eval {$sth->execute(undef, @$spot)}; } else { my $s = "insert into spot values(NULL,"; @@ -137,7 +142,8 @@ sub spot_insert $s .= $spot->[10] . ','; $s .= $spot->[11] . ','; $s .= (length $spot->[12] ? $self->quote($spot->[12]) : 'NULL') . ','; - $s .= (length $spot->[13] ? $self->quote($spot->[13]) : 'NULL') . ')'; + $s .= (length $spot->[13] ? $self->quote($spot->[13]) : 'NULL') . ','; + $s .= (length $spot->[14] ? $self->quote($spot->[14]) : 'NULL') . ')'; eval {$self->do($s)}; } } diff --git a/perl/DXSql/SQLite.pm b/perl/DXSql/SQLite.pm index d151f82e..8cb1606d 100644 --- a/perl/DXSql/SQLite.pm +++ b/perl/DXSql/SQLite.pm @@ -22,11 +22,36 @@ sub show_tables my $sth = $self->prepare($s); $sth->execute; my @out; - push @out, $sth->fetchrow_array; + while (my @t = $sth->fetchrow_array) { + push @out, @t; + } $sth->finish; return @out; } +sub has_ipaddr +{ + my $self = shift; + my $s = q(PRAGMA table_info(spot)); + my $sth = $self->prepare($s); + $sth->execute; + while (my @t = $sth->fetchrow_array) { + if ($t[0] eq 'ipaddr') { + $sth->finish; + return 1; + } + } + $sth->finish; + return undef; +} + +sub add_ipaddr +{ + my $self = shift; + my $s = q(alter table spot add column ipaddr varchar(40)); + $self->do($s); +} + sub spot_create_table { my $self = shift; @@ -45,7 +70,8 @@ spotcq int, spotteritu int, spottercq int, spotstate text, -spotterstate text +spotterstate text, +ipaddr text )}; $self->do($s); } diff --git a/perl/DXSql/mysql.pm b/perl/DXSql/mysql.pm index ff708da6..dee586f7 100644 --- a/perl/DXSql/mysql.pm +++ b/perl/DXSql/mysql.pm @@ -29,6 +29,29 @@ sub show_tables return @out; } +sub has_ipaddr +{ + my $self = shift; + my $s = q(describe spot); + my $sth = $self->prepare($s); + $sth->execute; + while (my @t = $sth->fetchrow_array) { + if ($t[0] eq 'ipaddr') { + $sth->finish; + return 1; + } + } + $sth->finish; + return undef; +} + +sub add_ipaddr +{ + my $self = shift; + my $s = q(alter table spot add column ipaddr varchar(40)); + $self->do($s); +} + sub spot_create_table { my $self = shift; @@ -47,7 +70,8 @@ spotcq tinyint, spotteritu tinyint, spottercq tinyint, spotstate char(2), -spotterstate char(2) +spotterstate char(2), +ipaddr varchar(40) )}; $self->do($s); } diff --git a/perl/Spot.pm b/perl/Spot.pm index b8efd3d1..7fcba6d1 100644 --- a/perl/Spot.pm +++ b/perl/Spot.pm @@ -111,10 +111,11 @@ sub init my $now = Julian::Day->alloc(1995, 0); my $today = Julian::Day->new(time); my $sth = $main::dbh->spot_insert_prepare; - $main::dbh->{RaiseError} = 0; while ($now->cmp($today) <= 0) { my $fh = $fp->open($now); if ($fh) { +# $main::dbh->{RaiseError} = 0; + $main::dbh->begin_work; my $count = 0; while (<$fh>) { chomp; @@ -125,31 +126,32 @@ sub init push @s, $b[1] if @s < 7; push @s, '' if @s < 8; push @s, @a[0,1], @b[0,1] if @s < 12; - push @s, $a[2], $a[2] if @s < 14; + push @s, $a[2], $a[2] if @s < 14; } - push @s, undef while @s < 14; - pop @s while @s > 14; - $main::dbh->spot_insert(\@s, $sth); $count++; } - $main::dbh->commit if $count; - $main::dbh->{RaiseError} = 0; + $main::dbh->commit; dbg("inserted $count spots from $now->[0] $now->[1]"); $fh->close; $total += $count; } $now = $now->add(1); } + $main::dbh->begin_work; $main::dbh->spot_add_indexes; $main::dbh->commit; - $main::dbh->{RaiseError} = 1; +# $main::dbh->{RaiseError} = 1; $t = time - $t; my $min = int($t / 60); my $sec = $t % 60; dbg("$total spots converted in $min:$sec"); } + unless ($main::dbh->has_ipaddr) { + $main::dbh->add_ipaddr; + dbg("added ipaddr field to spot table"); + } } } @@ -190,6 +192,7 @@ sub add my $buf = join('^', @_); $fp->writeunix($_[2], $buf); if ($main::dbh) { + $main::dbh->begin_work; $main::dbh->spot_insert(\@_); $main::dbh->commit; } -- 2.43.0