view release on metacpan or  search on metacpan

lib/App/RPi/EnvUI/DB.pm  view on Meta::CPAN

sub env {
    my ($self) = @_;

    my $id = $self->last_id;

    if (! $self->{env_sth}) {
        $self->{env_sth} = $self->db->prepare(
            'SELECT * FROM stats WHERE id=?'
        );
    }
    my $sth = $self->{env_sth};
    $sth->execute($id);
    return $sth->fetchrow_hashref;
}
sub db {
    return $_[0]->{db};
}
sub delete {
    my ($self, $table) = @_;

    my $sth = $self->db->prepare(
        "DELETE FROM $table;"
    );

    $sth->execute;
}
sub graph_data {
    my ($self) = @_;

    if (! $self->{graph_sth}){
        $self->{graph_sth} = $self->db->prepare(
            "select * from (
                select * from stats order by id DESC limit 5760
            ) sub
                order by id asc;"
        );
    }

    my $sth = $self->{graph_sth};
    $sth->execute;
    return $sth->fetchall_arrayref;
}
sub insert_env {
    my ($self, $temp, $hum) = @_;

    if (! $self->{insert_env_sth}){
        $self->{insert_env_sth} = $self->db->prepare(
            'INSERT INTO stats VALUES (?, CURRENT_TIMESTAMP, ?, ?)'
        );
    }
    $self->{insert_env_sth}->execute(undef, $temp, $hum);
}
sub last_id {
    my $self = shift;
    my $id_list = $self->db->selectrow_arrayref(
        "select seq from sqlite_sequence where name='stats';"
    );

    return defined $id_list ? $id_list->[0] : 0;
}
sub update {
    my ($self, $table, $col, $value, $where_col, $where_val) = @_;

    if (! defined $where_col) {
        my $sth = $self->db->prepare("UPDATE $table SET $col=?");
        $sth->execute($value);
    }
    else {
        my $sth = $self->db->prepare(
            "UPDATE $table SET $col=? WHERE $where_col=?"
        );
        $sth->execute($value, $where_val);
    }
}
sub update_bulk {
    my ($self, $table, $col, $where_col, $data) = @_;

    my $sth = $self->db->prepare(
        "UPDATE $table SET $col=? WHERE $where_col=?"
    );

    for (@$data){
        $sth->execute(@$_);
    }
}
sub update_bulk_all {
    my ($self, $table, $col, $data) = @_;

    my $sth = $self->db->prepare(
        "UPDATE $table SET $col=?;"
    );
    $sth->execute(@$data);
}
sub begin {
    $_[0]->{db}->begin_work;
}
sub commit {
    $_[0]->{db}->commit;
}

true;
__END__

=head1 NAME

App::RPi::EnvUI::DB - Database manager for App::RPi::EnvUI environment control
sysytem

=head1 SYNOPSIS
    use App::RPi::EnvUI::DB;

    my $db = App::RPi::EnvUI::DB->new;

    $db->method(@args);

=head1 DESCRIPTION

This is the database abstraction class for L<App::RPi::EnvUI>. It abstracts
away the database work from the API and the webapp itself.

=head1 METHODS

=head2 new(%args)

Returns a new L<App::RPi::EnvUI::DB> object. All parameters are sent in as a
hash structure.

Parameters:

    testing

Optional, Bool. C<1> to enable testing mode, C<0> to disable.

Default: C<0> (off)

=head2 aux($aux_id)

Fetches and returns a hash reference containing the details of an auxillary
channel.

Parameters:

    $aux_id

Mandatory, String. The string name of the auxillary channel (eg: C<aux1>)