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


line stmt bran cond sub pod time code
1             package CGI::Ex::Recipes::DBIx;
2              
3 1     1   7 use strict;
  1         2  
  1         48  
4 1     1   6 use warnings;
  1         2  
  1         33  
5 1     1   5 use utf8;
  1         3  
  1         7  
6 1     1   692427 use DBI;
  1         22431  
  1         80  
7 1     1   1485 use DBD::SQLite;
  1         11434  
  1         34  
8 1     1   1275 use SQL::Abstract;
  1         12750  
  1         47  
9 1     1   13 use Carp qw(carp cluck croak);
  1         2  
  1         91  
10 1     1   8 use vars qw(@EXPORT_OK);
  1         2  
  1         59  
11             require Exporter;
12 1     1   7 use base qw(Exporter);
  1         4  
  1         771  
13             @EXPORT_OK = qw(
14             dbh
15             sql
16             create_tables
17             categories
18             recipes
19            
20             );
21             our $VERSION = '0.02';
22              
23             sub dbh {
24 0     0 1   my $self = shift;
25 0 0         if (! $self->{'dbh'}) {
26            
27 0   0       my $file = ($ENV{SITE_ROOT} || $self->base_dir_abs->[0] ) . '/' . $self->conf->{'db_file'}
28             || './data/recipes.sqlite';
29 0           my $exists = -e $file;
30 0   0       my $package = $self->{'_package'} || 'somethingsligthlylessborring';
31 0           $self->{'dbh'} = DBI->connect(
32             "dbi:SQLite:dbname=$file", '', '',
33             {
34             #'private_'. $package => $package ,
35             RaiseError => 1,
36             }
37             );
38 0 0         $self->create_tables if !$exists;
39 0           warn 'New db connetion initiated!';
40             }
41 0           return $self->{'dbh'};
42             }
43              
44              
45             sub create_tables {
46 0     0 1   my $self = shift;
47             #TODO:move SQL in a file
48 0           $self->dbh->do("CREATE TABLE recipes (
49             id INTEGER PRIMARY KEY AUTOINCREMENT,
50             -- pid: id of the category in which this row will be placed
51             pid INTEGER NOT NULL,
52             -- is_category: is this recipe a category or not
53             is_category INTEGER NOT NULL DEFAULT 0,
54             -- sortorder: id of the field after which this field will be showed
55             sortorder INTEGER NOT NULL,
56             -- title: title of the recipe
57             title VARCHAR(100) NOT NULL,
58             -- problem: short description of the problem which this recipe solves
59             problem VARCHAR(255) NOT NULL,
60             -- analysis: analysis of the problem (why it occured etc.)
61             analysis TEXT NOT NULL,
62             -- solution: provide one or several solutions
63             solution TEXT NOT NULL,
64             -- tstamp: last modification in unix timestamp format
65             tstamp INTEGER NOT NULL,
66             -- date_added: creation date in unix timestamp format
67             date_added INTEGER NOT NULL
68             )");
69              
70 0           $self->dbh->do("CREATE TABLE cache (
71             id VARCHAR(32) PRIMARY KEY,
72             value TEXT NOT NULL,
73             tstamp INTEGER NOT NULL,
74             expires INTEGER NOT NULL
75             )");
76             }
77              
78             sub sql {
79 0     0 0   my $self = shift;
80 0 0         if (! $self->{'sql'}) {
81 0           $self->{'sql'} = SQL::Abstract->new;
82             }
83 0           return $self->{'sql'};
84             }
85              
86             #Suitable for preparing a left menu in some view/template
87             sub categories {
88 0     0 1   my $self = shift;
89 0   0       my $fields = shift || [qw(id pid is_category title)];
90 0   0       my $where = shift || {pid=>0};
91             #make shure we want categories
92 0   0       $where->{is_category} ||= 1;
93 0   0       my $order = shift || ['sortorder'];
94 0           my ($s, @bind) = $self->sql->select('recipes',$fields,$where,$order);
95 0           return $self->dbh->selectall_arrayref($s,{Slice => {} ,MaxRows=>1000,},@bind);
96             }
97              
98             #note: this method is more general than categories. returns all recipes with given pid||0
99             sub recipes {
100 0     0 1   my $self = shift;
101 0   0       my $fields = shift || '*';
102 0   0       my $where = shift || { pid => 0, id => { '!=', 0 } };
103 0   0       my $order = shift || ['sortorder'];
104 0           my ($s, @bind) = $self->sql->select('recipes',$fields,$where,$order);
105 0           return $self->dbh->selectall_arrayref($s,{Slice => {} ,MaxRows=>1000,},@bind);
106             }
107              
108             1; # End of CGI::Ex::Recipes::DBIx
109              
110             __END__