package Dwimmer::Feed::DB;
use Moose;
use Carp ();
use Data::Dumper qw(Dumper);
use DateTime;
use DBI;
our $VERSION = '0.32';
has 'store' => (is => 'ro', isa => 'Str', required => 1);
has 'dbh' => (is => 'rw', isa => 'DBI::db');
sub connect {
my ($self) = @_;
if (not $self->dbh) {
my $dbh = DBI->connect("dbi:SQLite:dbname=" . $self->store, "", "", {
FetchHashKeyName => 'NAME_lc',
RaiseError => 1,
PrintError => 0,
});
$self->dbh( $dbh );
}
return $self->dbh;
}
sub add_source {
my ($self, $e) = @_;
my @fields = qw(title url feed comment status twitter site_id);
my $fields = join ', ', @fields;
my $placeholders = join ', ', (('?') x scalar @fields);
$self->dbh->do("INSERT INTO sources ($fields) VALUES($placeholders)",
{},
@$e{@fields});
return $self->dbh->last_insert_id('', '', '', '');
}
sub get_all_entries {
my ($self) = @_;
my $sth = $self->dbh->prepare('SELECT * FROM entries ORDER BY issued DESC');
$sth->execute;
my @results;
while (my $h = $sth->fetchrow_hashref) {
push @results, $h;
}
return \@results;
}
sub find {
my ($self, %args) = @_;
my $ref = $self->dbh->selectrow_hashref('SELECT * FROM entries WHERE link LIKE ?', {}, $args{link});
return $ref;
}
sub add_entry {
my ($self, %args) = @_;
my @fields = grep {defined $args{$_}} qw(id source_id link author issued title summary content tags site_id);
my $f = join ',', @fields;
my $p = join ',', (('?') x scalar @fields);
my $issued = $args{issued};
$args{issued} = $issued->ymd . ' ' . $issued->hms;
my $sql = "INSERT INTO entries ($f) VALUES($p)";
#main::LOG("SQL: $sql");
$self->dbh->do($sql, {}, @args{@fields});
my $id = $self->dbh->last_insert_id('', '', '', '');
main::LOG(" ID: $id");
# only deliver new things
my $NOT_TOO_OLD = 60*60*24;
if ($issued->epoch > time - $NOT_TOO_OLD) {
$self->dbh->do(q{INSERT INTO delivery_queue (channel, entry, site_id) VALUES ('mail', ?, ?)},
{}, $id, $args{site_id});
}
return;
}
sub get_queue {
my ($self, $channel) = @_;
my $sth = $self->dbh->prepare('SELECT * FROM entries, delivery_queue WHERE entries.id=delivery_queue.entry AND channel = ?');
$sth->execute($channel);
my @results;
while (my $h = $sth->fetchrow_hashref) {
push @results, $h;
}
return \@results;
}
sub delete_from_queue {
my ($self, $channel, $id) = @_;
$self->dbh->do('DELETE FROM delivery_queue WHERE channel=? AND entry=?', {}, $channel, $id);
return;
}
sub get_sources {
my ( $self, %opt ) = @_;
my $sql = 'SELECT * FROM sources';
my @fields = sort keys %opt;
if (%opt) {
$sql .= ' WHERE ';
$sql .= join ' AND ', map { "$_=?" } @fields;
}
my $sth = $self->dbh->prepare($sql);
$sth->execute(@opt{@fields});
my @r;
while (my $h = $sth->fetchrow_hashref) {
push @r, $h;
}
return \@r;
}
sub get_source_by_id {
my ( $self, $id ) = @_;
my $sources = $self->get_sources;
my ($s) = grep { $_->{id} eq $id } @$sources;
return $s;
}
sub update_last_fetch {
my ($self, $source_id, $status, $error) = @_;
my $sql = qq{UPDATE sources SET last_fetch_time=?, last_fetch_status=?, last_fetch_error=? WHERE id=?};
$self->dbh->do($sql, undef, time(), $status, $error, $source_id);
return;
}
sub update {
my ($self, $id, $field, $value) = @_;
Carp::croak("Invalid field '$field'")
if $field !~ m{^(feed|comment|twitter|status|title|url)$};
Carp::croak("Invalid value for status '$value'")
if $field eq 'status' and $value !~ m{^(enabled|disabled)$};
my $sql = qq{UPDATE sources SET $field = ? WHERE id=?};
$self->dbh->do($sql, undef, $value, $id);
}
sub set_config {
my ($self, %args) = @_;
foreach my $field (qw(key value site_id)) {
die "Missing $field" if not defined $args{$field};
}
$self->delete_config( %args );
$self->dbh->do('INSERT INTO config (key, value, site_id) VALUES (?, ?, ?)',
undef,
$args{key}, $args{value}, $args{site_id});
return;
}
sub delete_config {
my ($self, %args) = @_;
foreach my $field (qw(key site_id)) {
die "Missing $field" if not defined $args{$field};
}
$self->dbh->do('DELETE FROM config WHERE key=? AND site_id=?', undef, $args{key}, $args{site_id});
return;
}
sub get_config {
my ($self, %args) = @_;
my $sql = 'SELECT * FROM config ';
if (defined $args{site_id}) {
$sql .= 'WHERE site_id=?';
}
$sql .= ' ORDER BY key DESC';
my $sth = $self->dbh->prepare($sql);
defined $args{site_id} ? $sth->execute($args{site_id}) : $sth->execute();;
my @results;
while (my $h = $sth->fetchrow_hashref) {
push @results, $h;
}
return \@results;
}
sub get_config_hash {
my ($self, %args) = @_;
my $sql = 'SELECT * FROM config ';
if (defined $args{site_id}) {
$sql .= 'WHERE site_id=?';
}
$sql .= ' ORDER BY key DESC';
my $sth = $self->dbh->prepare($sql);
defined $args{site_id} ? $sth->execute($args{site_id}) : $sth->execute();
my %config;
while (my $h = $sth->fetchrow_hashref) {
$config{ $h->{key} } = $h->{value};
}
return \%config;
}
sub addsite {
my ($self, %args) = @_;
return $self->dbh->do(q{INSERT INTO sites (name) VALUES (?)}, {}, $args{name});
}
sub get_site_id {
my ($self, $name) = @_;
my $ref = $self->dbh->selectrow_hashref('SELECT id FROM sites WHERE name = ?', {}, $name);
return $ref->{id};
}
sub get_site_by_id {
my ($self, $id) = @_;
my $ref = $self->dbh->selectrow_hashref('SELECT * FROM sites WHERE id = ?', {}, $id);
return $ref;
}
sub get_sites {
my ($self) = @_;
my $sql = 'SELECT * FROM sites';
my $sth = $self->dbh->prepare($sql);
$sth->execute;
my @r;
while (my $h = $sth->fetchrow_hashref) {
push @r, $h;
}
return \@r;
}
1;