File Coverage

erecipes/perl/lib/CGI/Ex/Recipes/DBIx.pm

Criterion	Covered	Total	%
statement	27	56	48.2
branch	0	6	0.0
condition	0	18	0.0
subroutine	9	14	64.2
pod	4	5	80.0
total	40	99	40.4

package CGI::Ex::Recipes::DBIx;

use strict;
use warnings;
use utf8;
use DBI;
use DBD::SQLite;
use SQL::Abstract;
use Carp qw(carp cluck croak);
use vars qw(@EXPORT_OK);
require Exporter;
use base qw(Exporter);
@EXPORT_OK = qw(
    dbh
    sql
    create_tables
    categories
    recipes

);
our $VERSION = '0.02';

sub dbh {
    my $self = shift;
    if (! $self->{'dbh'}) {

        my $file   = ($ENV{SITE_ROOT} || $self->base_dir_abs->[0] ) . '/' . $self->conf->{'db_file'}
                        || './data/recipes.sqlite';
        my $exists = -e $file;
        my $package =  $self->{'_package'} || 'somethingsligthlylessborring';
        $self->{'dbh'} = DBI->connect(
            "dbi:SQLite:dbname=$file", '', '',
            {
                #'private_'. $package => $package ,
                RaiseError => 1,
            }
        );
        $self->create_tables if !$exists;
        warn 'New db connetion initiated!';
    }
    return $self->{'dbh'};
}


sub create_tables {
    my $self = shift;
    #TODO:move SQL in a file
    $self->dbh->do("CREATE TABLE recipes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
            -- pid: id of the category in which this row will be placed
        pid INTEGER NOT NULL,
            -- is_category: is this recipe a category or not
        is_category INTEGER NOT NULL DEFAULT 0,
            -- sortorder: id of the field after which this field will be showed
        sortorder INTEGER NOT NULL,
            -- title: title of the recipe
        title VARCHAR(100) NOT NULL,
            -- problem: short description of the problem which this recipe solves
        problem VARCHAR(255) NOT NULL,
            -- analysis: analysis of the problem (why it occured etc.)
        analysis TEXT NOT NULL,
            -- solution: provide one or several solutions
        solution  TEXT NOT NULL,
            -- tstamp: last modification in unix timestamp format
        tstamp  INTEGER NOT NULL,
            -- date_added: creation date in unix timestamp format
        date_added INTEGER NOT NULL
    )");

$self->dbh->do("CREATE TABLE cache (
        id VARCHAR(32) PRIMARY KEY,
        value TEXT NOT NULL,
        tstamp  INTEGER NOT NULL,
        expires  INTEGER NOT NULL
    )");
}

sub sql {
    my $self = shift;
    if (! $self->{'sql'}) {
        $self->{'sql'} = SQL::Abstract->new;
    }
    return $self->{'sql'};
}

#Suitable for preparing a left menu in some view/template
sub categories {
    my $self   = shift;
    my $fields = shift || [qw(id pid is_category title)];
    my $where  = shift || {pid=>0};
    #make shure we want categories
    $where->{is_category} ||= 1;
    my $order  = shift || ['sortorder'];
    my ($s, @bind) = $self->sql->select('recipes',$fields,$where,$order);
    return $self->dbh->selectall_arrayref($s,{Slice => {} ,MaxRows=>1000,},@bind);
}

#note: this method is more general than categories. returns all recipes with given pid||0
sub recipes {
    my $self   = shift;
    my $fields = shift || '*';
    my $where  = shift || { pid => 0, id => { '!=', 0 } };
    my $order  = shift || ['sortorder'];
    my ($s, @bind) = $self->sql->select('recipes',$fields,$where,$order);
    return $self->dbh->selectall_arrayref($s,{Slice => {} ,MaxRows=>1000,},@bind);
}

1; # End of CGI::Ex::Recipes::DBIx

__END__