관리-도구
편집 파일: SQLiteStore.pm
package PUC::Module::WebApp::SQLiteStore; use strict; use warnings; use 5.010_001; use parent 'Class::Accessor'; use DBD::SQLite; use DBI; use JSON (); use Log::Log4perl; Log::Log4perl->init_once('/opt/PUC/log4perl.conf'); my $log = Log::Log4perl->get_logger(); __PACKAGE__->mk_ro_accessors(qw/dsn/); sub new { my ( $class, %options ) = @_; _build_dsn( \%options ); return bless \%options, $class; } sub _build_dsn { my ($self) = @_; return $self->{dsn} //= 'dbi:SQLite:dbname=' . $self->{dbfile}; } sub create_local_store { my ($self) = @_; my $dbh = DBI->connect_cached( $self->dsn ); $dbh->do( <<'CREATE_SQL' ); CREATE TABLE IF NOT EXISTS main.webapps ( -- v1.0 server VARCHAR(255) NOT NULL, username VARCHAR(31) NOT NULL, domain TEXT NOT NULL, data TEXT NOT NULL, updated CHAR(20) NOT NULL, -- ISO-8601 modified PRIMARY KEY (server, username, domain) ON CONFLICT FAIL ) CREATE_SQL return $self; } sub diff_and_save { my ( $self, $data ) = @_; my $json = JSON->new->utf8->canonical->encode($data); ## no critic (ProhibitLongChains) my $dbh = DBI->connect_cached( $self->dsn ); ## no critic (RequireCarping, ProhibitLocal) local $dbh->{RaiseError} = 1; my $success = eval { # try $dbh->begin_work; my $sth = $dbh->prepare( <<'SQL' ); SELECT * FROM webapps WHERE server = ? AND username = ? AND domain = ? ORDER BY updated DESC -- PRIMARY KEY should prevent multiple records SQL $sth->execute( $data->{server}->{hostname}, $data->{username}, $data->{domain}->{name}, ) or die 'Query failed without raising exception!'; my $result = $sth->fetchrow_hashref(); die 'Fetch failed without raising exception! - ' . $sth->err if $sth->err; if ( $result && $sth->fetchrow_hashref() ) { die 'Foreign key constraint failed - multiple records!'; } if ( !$result || $json ne $result->{data} ) { my $sth_u = $dbh->prepare( $result ? <<'SQL_UPDATE' : <<'SQL_INSERT' ); UPDATE webapps SET data = ?, updated = datetime('now') WHERE server = ? AND username = ? AND domain = ? SQL_UPDATE INSERT INTO webapps ( data, server, username, domain, updated ) VALUES ( ?, ?, ?, ?, datetime('now') ) SQL_INSERT $sth_u->execute( $json, $data->{server}->{hostname}, $data->{username}, $data->{domain}->{name}, ) or die 'INSERT or UPDATE failed without raising exception!'; $result = { data => $json }; } else { $result = {}; } $dbh->commit; return $result; }; if ( !$success ) { # catch ## no critic (BracedFileHandle CheckedSyscalls) my $err = $@; eval { $dbh->rollback }; ## no critic (CheckingReturnValue) $log->error(__PACKAGE__ . ": Failed to query database:".$err, action => "PUC::Module::WebApp::SQLiteStore->diff_and_save"); print STDERR __PACKAGE__ . ": Failed to query database: $err\n"; # don't report if there is a database error return; } return $success->{data}; # json string or undef } 1;