From fae2ae463d8871c09e77391db1e24dff0e31f53b Mon Sep 17 00:00:00 2001 From: miker Date: Fri, 18 Feb 2005 18:26:43 +0000 Subject: [PATCH] ARG... sqlite is pain git-svn-id: svn://svn.open-ils.org/OpenSRF/trunk@90 9efc2488-bf62-4759-914b-345cdb29e865 --- src/perlmods/OpenSRF/Application/Persist.pm | 123 +++++++++++++------- 1 file changed, 79 insertions(+), 44 deletions(-) diff --git a/src/perlmods/OpenSRF/Application/Persist.pm b/src/perlmods/OpenSRF/Application/Persist.pm index ced6bd0..a0d1278 100644 --- a/src/perlmods/OpenSRF/Application/Persist.pm +++ b/src/perlmods/OpenSRF/Application/Persist.pm @@ -12,6 +12,33 @@ use vars qw/$dbh $sc $log/; sub initialize { $log = 'OpenSRF::Utils::Logger'; + + $sc = OpenSRF::Utils::SettingsClient->new; + + my $dbfile = $sc->config_value( apps => persist => app_settings => 'dbfile'); + unless ($dbfile) { + throw OpenSRF::EX::PANIC ("Can't find my dbfile for SQLite!"); + } + + my $init_dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","",""); + $init_dbh->{AutoCommit} = 1; + $init_dbh->{RaiseError} = 0; + + $init_dbh->do( <<" SQL" ); + CREATE TABLE storage ( + id INTEGER PRIMARY KEY, + name_id INTEGER, + value TEXT + ); + SQL + + $init_dbh->do( <<" SQL" ); + CREATE TABLE store_name ( + id INTEGER PRIMARY KEY, + name TEXT UNIQUE + ); + SQL + } sub child_init { @@ -24,24 +51,8 @@ sub child_init { $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","",""); $dbh->{AutoCommit} = 1; - $dbh->{RaiseError} = 1; - - eval { - $dbh->do( <<" SQL" ); - CREATE TABLE storage ( - id INT PRIMARY KEY, - name_id INT, - value TEXT - ); - SQL - - $dbh->do( <<" SQL" ); - CREATE TABLE store_name ( - id INT PRIMARY KEY, - name TEXT UNIQUE - ); - SQL - }; + $dbh->{RaiseError} = 0; + } sub create_store { @@ -50,17 +61,24 @@ sub create_store { my $name = shift || ''; - eval { - my $sth = $dbh->prepare("INSERT INTO store_name (name) VALUES (?)"); - $sth->execute($name); - $sth->finish; + + my $continue = 0; + try { + _get_name_id($name); + + } catch Error with { + $continue++; }; - if ($@) { - throw OpenSRF::EX::WARN ("Duplicate key: object name [$name] already exists! " . $dbh->errstr); - } + + throw OpenSRF::EX::WARN ("Duplicate key: object name [$name] already exists! " . $dbh->errstr) + unless ($continue); + + my $sth = $dbh->prepare("INSERT INTO store_name (name) VALUES (?);"); + $sth->execute($name); + $sth->finish; unless ($name) { - my $last_id = $dbh->last_insert_id(); + my $last_id = $dbh->last_insert_id(undef, undef, 'store_name', 'id'); $name = 'AUTOGENERATED!!'.$last_id; $dbh->do("UPDATE store_name SET name = '$name' WHERE id = '$last_id';"); } @@ -79,13 +97,15 @@ sub add_item { my $self = shift; my $client = shift; - my $name = shift or throw OpenSRF::EX::WARN ("No queue name specified!"); + my $name = shift or do { + throw OpenSRF::EX::WARN ("No name specified!"); + }; my $value = shift || ''; my $name_id = _get_name_id($name); if ($self->api_name =~ /object/) { - $dbh->do('DELETE FROM storage WHERE name_id = ?', {}, $name_id); + $dbh->do('DELETE FROM storage WHERE name_id = ?;', {}, $name_id); } $dbh->do('INSERT INTO storage (name_id,value) VALUES (?,?);', {}, $name_id, JSON->perl2JSON($value)); @@ -110,15 +130,18 @@ __PACKAGE__->register_method( ); sub _get_name_id { - my $name = shift or throw OpenSRF::EX::WARN ("No queue name specified!"); + my $name = shift or do { + throw OpenSRF::EX::WARN ("No queue name specified!"); + }; - my $name_id = $dbh->selectcol_arrayref("SELECT id FROM store_name WHERE name = ?", {}, $name)->[0]; - unless ($name_id) { + my $name_id = $dbh->selectrow_arrayref("SELECT id FROM store_name WHERE name = ?;", {}, $name); + + if (!ref($name_id) || !defined($name_id->[0])) { throw OpenSRF::EX::WARN ("Object name [$name] does not exist!"); } - return $name_id; + return $name_id->[0]; } sub destroy_store { @@ -129,8 +152,9 @@ sub destroy_store { my $name_id = _get_name_id($name); - $dbh->do("DELETE FROM storage WHERE name_id = ?", {}, $name_id); - $dbh->do("DELETE FROM store_name WHERE id = ?", {}, $name_id); + $dbh->do("DELETE FROM storage WHERE name_id = ?;", {}, $name_id); + $dbh->do("DELETE FROM store_name WHERE id = ?;", {}, $name_id); + } __PACKAGE__->register_method( api_name => 'opensrf.persist.slot.destroy', @@ -141,9 +165,10 @@ __PACKAGE__->register_method( sub _flush_by_name { my $name = shift; if ($name =~ /^AUTOGENERATED!!/) { - my $count = $dbh->selectrow_arrayref("SELECT COUNT(*) FROM storage WHERE name = ?", {}, $name); + my $name_id = _get_name_id($name); + my $count = $dbh->selectrow_arrayref("SELECT COUNT(*) FROM storage WHERE name_id = ?;", {}, $name_id); if (!ref($count) || $$count[0] == 0) { - $dbh->do("DELETE FROM store_name WHERE name = ?", {}, $name); + $dbh->do("DELETE FROM store_name WHERE name = ?;", {}, $name); } } } @@ -152,13 +177,16 @@ sub pop_queue { my $self = shift; my $client = shift; - my $name = shift or throw OpenSRF::EX::WARN ("No queue name specified!"); + my $name = shift or do { + throw OpenSRF::EX::WARN ("No queue name specified!"); + }; my $name_id = _get_name_id($name); - my $value = $dbh->selectrow_arrayref('SELECT id, value FROM storage WHERE name_id = ? ORDER BY id ASC LIMIT 1', {}, $name_id); - $dbh->do('DELETE FROM storage WHERE id = ?',{}, $value->[0]); + my $value = $dbh->selectrow_arrayref('SELECT id, value FROM storage WHERE name_id = ? ORDER BY id ASC LIMIT 1;', {}, $name_id); + $dbh->do('DELETE FROM storage WHERE id = ?;',{}, $value->[0]); _flush_by_name($name); + return JSON->JSON2perl( $value->[1] ); } __PACKAGE__->register_method( @@ -172,13 +200,16 @@ sub shift_stack { my $self = shift; my $client = shift; - my $name = shift or throw OpenSRF::EX::WARN ("No queue name specified!"); + my $name = shift or do { + throw OpenSRF::EX::WARN ("No queue name specified!"); + }; my $name_id = _get_name_id($name); - my $value = $dbh->selectrow_arrayref('SELECT id, value FROM storage WHERE name_id = ? ORDER BY id DESC LIMIT 1', {}, $name_id); - $dbh->do('DELETE FROM storage WHERE id = ?',{}, $value->[0]); + my $value = $dbh->selectrow_arrayref('SELECT id, value FROM storage WHERE name_id = ? ORDER BY id DESC LIMIT 1;', {}, $name_id); + $dbh->do('DELETE FROM storage WHERE id = ?;',{}, $value->[0]); _flush_by_name($name); + return JSON->JSON2perl( $value->[1] ); } __PACKAGE__->register_method( @@ -191,13 +222,17 @@ sub get_object { my $self = shift; my $client = shift; - my $name = shift or throw OpenSRF::EX::WARN ("No queue name specified!"); + my $name = shift or do { + throw OpenSRF::EX::WARN ("No object name specified!"); + }; + my $name_id = _get_name_id($name); - my $value = $dbh->selectrow_arrayref('SELECT name_id, value FROM storage WHERE name_id = ? ORDER BY id DESC LIMIT 1', {}, $name_id); + my $value = $dbh->selectrow_arrayref('SELECT name_id, value FROM storage WHERE name_id = ? ORDER BY id DESC LIMIT 1;', {}, $name_id); $dbh->do('DELETE FROM storage WHERE name_id = ?',{}, $value->[0]); _flush_by_name($name); + return JSON->JSON2perl( $value->[1] ); } __PACKAGE__->register_method( -- 2.43.2