view release on metacpan or  search on metacpan

lib/Bot/BasicBot/Pluggable/Module/Notes/Store/SQLite.pm  view on Meta::CPAN

Stores the given information in the database.  Croaks on error.

=cut

sub store {
    my ($self, %args) = @_;
    my $dbh = $self->{dbh};

    my $sth = $dbh->prepare( qq{
        INSERT INTO } . TABLENAME() . q{ (timestamp, name, channel, notes)
               VALUES (?, ?, ?, ?)
    }) or croak "Error: can't prepare db query for insert: " . $dbh->errstr;

    $sth->execute( @args{ qw( timestamp name channel notes ) } )
      or croak "Error: can't insert into database: " . $dbh->errstr;

    return 1;
}

sub get_notes {
    my ($self, %args) = @_;

#    warn Data::Dumper::Dumper(\%args);
    my $dbh = $self->{dbh};

    my %allowed = map { ($_ => 1) } ( qw/datetime channel name notes/ );
    my @select_vals = ();
    my @select_keys = ();

    foreach my $arg (keys %args) {
        if ($arg && defined $args{$arg} && exists $allowed{$arg}) {
            push @select_keys, "$arg LIKE ?"; 
            push @select_vals, $args{$arg}; 
        }
    }
    my $where_extra = " channel <> 'msg' " if(!$args{private});
    my $where = join(' AND ', @select_keys, $where_extra);

    my $page = $args{page} || 1;
    my $limit = $args{rows} || 10;
    
    my $sql = qq{
        SELECT id, timestamp, channel, name, notes FROM } 
        . TABLENAME() 
        . ($where ? " WHERE $where" : '') 
        . q{ ORDER BY } . ($args{order_ind} || 'channel') . " " . ($args{sort_order} || 'DESC')
# channel, timestamp desc}
        . q{ LIMIT } . ($limit * $page - $limit) . ', ' . $limit;
    #warn "SQLite: <<$sql>>" ;
    my $sth = $dbh->prepare($sql
    ) or croak "Error: can't prepare db query for select: " . $dbh->errstr;

    $sth->execute( @select_vals );

    my $notes = $sth->fetchall_arrayref({});

#   warn "Notes: ", Dumper($notes);

    ## hack to fake date/time fields:
    my $dt_formatter = DateTime::Format::Strptime->new( pattern => '%F %T',
                                                        locale => 'en_GB',
                                                        time_zone => 'UTC'
        );
    foreach my $row (@$notes){
        my $dt = $dt_formatter->parse_datetime( $row->{timestamp} )
            or die "Badly formatted timestamp: $row->{timestamp}";
        $row->{date} = $dt->ymd;
        $row->{time} = $dt->hms;
    }

#    warn "Notes: ", Dumper($notes);
    return $notes;
}

=head1 BUGS

No retrieval methods yet.

=head1 SEE ALSO

=over 4

=item * L<Bot::BasicBot::Pluggable::Module::Notes>

=back

=head1 AUTHOR

Jess Robinson <castaway@desert-island.me.uk>

=head1 COPYRIGHT

This module is free software; you can redistribute it and/or modify it
under the same terms as Perl itself.

=cut

1;