view release on metacpan or  search on metacpan

lib/CallBackery/Database.pm  view on Meta::CPAN

use CallBackery::Exception qw(mkerror);

=head1 NAME

CallBackery::Database - database access helpers

=head1 SYNOPSIS

 use CallBackery::Database;
 my $db = CallBackery::Database->new(app=>$self->config);
 my ($fields,$values) = $self->map2sql(table,data);
 my $selWhere = $self->map2where(table,data);
 my $rowHash = $self->fetchRow(table,{field=>val,field=>val},selExpression?);
 my $value = $self->fetchValue(table,{field=>val,field=>val},column);
 my $id = $self->matchData(table,{field=>val,field=>val});
 my $id = $self->lookUp(table,field,value);
 my $id = $self->updateOrInsertData(table,{dataField=>val,...},{matchField=>val,...}?);
 my $id = $self->insertIfNew(table,{field=>val,field=>val});

=head1 DESCRIPTION

Database access helpers.

=head2 config

object needs access to the system config to get access to the database

=cut

has app => sub {
    croak "app property is required";
}, weak => 1;

has userName => sub {
    return "* no user *";
};

has config => sub {
    shift->app->config;
}, weak => 1;


=head2 dhb

a dbi database handle

=cut

my $lastFlush = time;

has sql => sub {
    my $self = shift;
    require Mojo::SQLite;
    my $sql = Mojo::SQLite->new($self->config->cfgHash->{BACKEND}{cfg_db});

    $sql->options({
        RaiseError => 1,
        PrintError => 0,
        AutoCommit => 1,
        ShowErrorStatement => 1,
        sqlite_unicode => 1,
        FetchHashKeyName=>'NAME_lc',
    });

    $sql->on(connection => sub ($sql, $dbh) {
      $dbh->do('PRAGMA foreign_keys = ON;');
    });

    $sql->migrations
        ->name('cbmig')
        ->from_data(__PACKAGE__,'dbsetup.sql')
        ->migrate;

    return $sql;
};

# this must be fresh ... always!
sub mojoSqlDb {
    my $self = shift;
    return $self->sql->db;
};

=over 4

=item my($fields,$values) = $self->C<map2sql(table,data)>;

Provide two hash pointers and quote the field names for inclusion into an
SQL expression. Build field names according to the table_field rule.

=cut

sub map2sql {
    my $self = shift;
    my $table = shift;
    my $data = shift;
    my @values;
    my @fields;
    while (my($field, $value) = each %$data) {
        push @fields,$self->mojoSqlDb->dbh->quote_identifier($table."_".$field);
        push @values,$value;
    }
    return (\@fields,\@values);
}

=item my $sqlWhere = $self->C<map2where(table,data)>;

build a where statement Find a record matching the given data in a table the
data is a map. Quote field names and values.  Build field names according to
the table_field rule.

=cut

sub map2where {
    my $self = shift;
    my $table = shift;
    my $data = shift;
    my $db = $self->mojoSqlDb;
    my @expression;
    while (my($field, $value) = each %$data) {
        my $field = $db->dbh->quote_identifier($table."_".$field);
        my $expr;