view release on metacpan or  search on metacpan

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

Find a record matching the given data in a table and returns the value in column.

=cut

sub fetchValue {
    my $self = shift;
    my $table = shift;
    my $where = shift;
    my $column = shift;
    my $db = $self->mojoSqlDb;
    my $row = $self->fetchRow($table,$where,$db->dbh->quote_identifier($table.'_'.$column));
    if ($row){
        return $row->{$table.'_'.$column};
    }
    else {
        return undef;
    }
}


=item $id = $self->C<matchData(table,data)>;

Find a record matching the given data in a table
the data is a map.

=cut

sub matchData {
    my $self = shift;
    my $table = shift;
    my $data = shift;
    my $found = $self->fetchValue($table,$data,"id");
    return $found;

}

=item $id = $self->C<lookUp(table,column,value)>

Lookup the value in table in table_column and return table_id.
Throw an exception if this fails. Use matchData if you are just looking.

=cut

sub lookUp {
    my $self = shift;
    my $table = shift;
    my $column = shift;
    my $value = shift;
    my $id = $self->matchData($table,{$column => $value})
        or die mkerror(1349,"Lookup for $column = $value in $table faild");
    return $id;
}

=item $id = $self->C<updateOrInsertData(table,data,match?)>

Insert the given data into the table. If a match map is given, try an update first
with the given match only insert when update has 0 hits.

=cut

sub updateOrInsertData {
    my $self = shift;
    my $table = shift;
    my $data = shift;
    my $match = shift;
    my $db = $self->mojoSqlDb;
    my ($colNames,$colValues) = $self->map2sql($table,$data);
    my $sqlTable = $db->dbh->quote_identifier($table);
    my $sqlIdCol = $db->dbh->quote_identifier($table."_id");
    my $sqlColumns = join ', ', @$colNames;
    my $sqlSet = join ', ', map { "$_ = ?" } @$colNames;
    my $sqlData = join ', ', map { '?' } @$colValues;
    if ($match){ # try update first if we have an id
        my $matchWhere = $self->map2where($table,$match);
        my $SQL = <<"SQL";
        UPDATE $sqlTable SET $sqlSet
        WHERE $matchWhere
SQL
        my $count =  $db->dbh->do($SQL,{},@$colValues);
        if ($count > 0){
            return ( $data->{id} // $match->{id} );
        }
    }
    my $SQL = <<"SQL";
        INSERT INTO $sqlTable ( $sqlColumns )
        VALUES ( $sqlData )
SQL
    $db->dbh->do($SQL,{},@$colValues);

    # non serial primary key, id defined by user
    if (exists $data->{'id'}){
        return $data->{'id'};
    }
    # serial primary key
    else{
        return $db->dbh->last_insert_id(undef,undef,$table,$table."_id");
    }
}

=item $id = $self->C<insertIfNew(table,data)>

Lookup the given data. If it is new, insert a record. Returns the matching Id.

=cut

sub insertIfNew {
    my $self = shift;
    my $table = shift;
    my $data = shift;
    return ( $self->matchData($table,$data)
           // $self->updateOrInsertData($table,$data));
}

=item $id = $self->C<deleteData(table,id)>

Delete data from table. Given the record id.
Returns true if the record was deleted.

=cut

sub deleteData {