DB.pm

filter: Statementstmt 12.2% 17 / 139 Branchbran 0.0% 0 / 100 Conditioncond 0.0% 0 / 23 MC/DCmcdc 0.0% 0 / 13 Subroutinesub 19.2% 5 / 26 Podpod 40.0% 8 / 20 totaltotal 9.3% 30 / 321 CC 60 SCAR 48.8
CC
60
Cov
6%
init_db
420.0
insert
110.0
sql_select
56.0
CRAP 131.4
↑ Summary DB/CRUD.pm »
1 # BW::DB.pm
2 # Normalized database routines
3 # with support for MySQL and SQLite
4 #
5 # by Bill Weinman - http://bw.org/
6 # Copyright (c) 1995-2010 The BearHeart Group, LLC
7 #
8 # See HISTORY file.
9 #
10  
11 package BW::DB;
12 1 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
13 1 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
14  
15 1 ▶use base qw( BW::Base );
Subroutine BEGIN: called CC=1 SCAR=0.0
16 1 ▶use BW::Constants;
Subroutine BEGIN: called CC=1 SCAR=0.0
17 1 ▶use DBI;
Subroutine BEGIN: called CC=1 SCAR=0.0
18  
19 1 our $VERSION = "1.1.1";
20  
21 sub _init
22 0 ▶{
Subroutine _init: not called CC=5 SCAR=34.0
23 0 my $self = shift;
24 0 $self->SUPER::_init(@_);
25  
26 0 ▶ if ( $self->{connect} or $self->{dsn} or $self->{database} or $self->{dbengine} ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"connect"} or $self->{"dsn"} or $self->{"database"} or $self->{"dbengine"}) 0 0 0
Condition0%0/9
$self->{"connect"} or $self->{"dsn"} or $self->{"database"} or $self->{"dbengine"}
l!l&&r!l&&!r
000
$self->{"connect"} or $self->{"dsn"} or $self->{"database"}
l!l&&r!l&&!r
000
$self->{"connect"} or $self->{"dsn"}
l!l&&r!l&&!r
000
MC/DC0%0/4
$self->{"connect"} or $self->{"dsn"} or $self->{"database"} or $self->{"dbengine"}
$self->{"connect"} $self->{"dsn"} $self->{"database"} $self->{"dbengine"}
Truth table0%0/5
A or B or C or D
A= $self->{"connect"}B= $self->{"dsn"}C= $self->{"database"}D= $self->{"dbengine"}
ABCDresult
1XXX1
01XX1
001X1
00011
00000
27 0 $self->init_db;
28 }
29  
30 0 return SUCCESS;
31 }
32  
33 # _setter_getter entry points
34 0 ▶sub connect { BW::Base::_setter_getter(@_); }
Subroutine connect: not called CC=1 SCAR=6.9
Pod documented
35 0 ▶sub database { BW::Base::_setter_getter(@_); }
Subroutine database: not called CC=1 SCAR=6.9
Pod undocumented
36 0 ▶sub dbname { BW::Base::_setter_getter(@_); }
Subroutine dbname: not called CC=1 SCAR=6.9
Pod undocumented
37 0 ▶sub host { BW::Base::_setter_getter(@_); }
Subroutine host: not called CC=1 SCAR=6.9
Pod undocumented
38 0 ▶sub port { BW::Base::_setter_getter(@_); }
Subroutine port: not called CC=1 SCAR=6.9
Pod undocumented
39 0 ▶sub socket { BW::Base::_setter_getter(@_); }
Subroutine socket: not called CC=1 SCAR=6.9
Pod undocumented
40 0 ▶sub user { BW::Base::_setter_getter(@_); }
Subroutine user: not called CC=1 SCAR=6.9
Pod undocumented
41 0 ▶sub password { BW::Base::_setter_getter(@_); }
Subroutine password: not called CC=1 SCAR=6.9
Pod undocumented
42 0 ▶sub dsn { BW::Base::_setter_getter(@_); }
Subroutine dsn: not called CC=1 SCAR=6.9
Pod undocumented
43 0 ▶sub dbh { BW::Base::_setter_getter(@_); }
Subroutine dbh: not called CC=1 SCAR=6.9
Pod undocumented
44 0 ▶sub dbengine { BW::Base::_setter_getter(@_); } # may be 'mysql' or 'SQLite'
Subroutine dbengine: not called CC=1 SCAR=6.9
Pod undocumented
45  
46 # setup the database connection
47 sub init_db
48 0 ▶{
Subroutine init_db: not called CC=20 SCAR=60.4
Pod undocumented
49 0 my $sn = 'init_db';
50 0 my $self = shift;
51  
52 0 my ( $dsn, $dbengine, $dbname, $database, $host, $port, $socket, $user, $password );
53 0 ▶ $dbengine = $self->{dbengine} || 'mysql'; # default to mysql for backward compatibility
Condition0%0/2
$self->{'dbengine'} || 'mysql'
l!l
00
MC/DC0%0/1
$self->{'dbengine'} || 'mysql'
$self->{'dbengine'}
Truth table0%0/2
A
A= $self->{'dbengine'}
Aresult
11
00
54  
55 0 ▶ if ( $self->connect ) {
Branch: 4 branches
BranchTrueFalseTotal
if ($self->connect) { } 0 0 0
elsif ($self->{'dsn'}) { } 0 0 0
elsif ($self->{'dbname'}) { } 0 0 0
elsif ($self->{'database'}) { } 0 0 0
56 0 my @dsnarray = split( /:/, $self->{connect} );
57 0 ▶ if( scalar @dsnarray == 2 ) {
Branch: 1 branch
BranchTrueFalseTotal
if (scalar @dsnarray == 2) { } 0 0 0
58 0 ( $dbengine, $dbname ) = @dsnarray;
59 } else {
60 0 ( $database, $host, $port, $user, $password ) = split( /:/, $self->{connect} );
61 }
62 } elsif ( $self->{dsn} ) {
63 0 $dsn = $self->{dsn};
64 } elsif ( $self->{dbname} ) {
65 0 $dbengine = 'SQLite';
66 0 $dbname = $self->{dbname};
67 } elsif ( $self->{database} ) {
68 0 $database = $self->{database};
69 0 $host = $self->{host};
70 0 $port = $self->{port};
71 0 $socket = $self->{socket};
72 }
73  
74 0 ▶ if(lc $dbengine eq 'sqlite') { $dbengine = 'SQLite' }; # correct any miscapitalization
Branch: 1 branch
BranchTrueFalseTotal
if (lc $dbengine eq "sqlite") 0 0 0
75  
76 0 ▶ $self->dbengine($dbengine) if $dbengine; # use the setters
Branch: 1 branch
BranchTrueFalseTotal
if $dbengine 0 0 0
77 0 ▶ $self->dbname($dbname) if $dbname;
Branch: 1 branch
BranchTrueFalseTotal
if $dbname 0 0 0
78  
79 0 ▶ $user = $self->{user} if $self->{user};
Branch: 1 branch
BranchTrueFalseTotal
if $self->{"user"} 0 0 0
80 0 ▶ $password = $self->{password} if $self->{password};
Branch: 1 branch
BranchTrueFalseTotal
if $self->{"password"} 0 0 0
81 0 ▶ $dsn = "DBI:${dbengine}" unless $dsn;
Branch: 1 branch
BranchTrueFalseTotal
unless $dsn 0 0 0
82 0 ▶ $dsn .= ":database=$database" if $database;
Branch: 1 branch
BranchTrueFalseTotal
if $database 0 0 0
83 0 ▶ $dsn .= ":dbname=$dbname" if $dbname;
Branch: 1 branch
BranchTrueFalseTotal
if $dbname 0 0 0
84 0 ▶ $dsn .= ";host=$host" if $host;
Branch: 1 branch
BranchTrueFalseTotal
if $host 0 0 0
85 0 ▶ $dsn .= ";port=$port" if $port;
Branch: 1 branch
BranchTrueFalseTotal
if $port 0 0 0
86 0 ▶ $dsn .= ";mysql_socket=$socket" if $socket;
Branch: 1 branch
BranchTrueFalseTotal
if $socket 0 0 0
87  
88 0 $self->{dbh} = DBI->connect( $dsn, $user, $password, { PrintError => 0 } );
89 0 ▶ return $self->_error("$sn: DBI connect error: $DBI::errstr") if $DBI::errstr;
Branch: 1 branch
BranchTrueFalseTotal
if $DBI::errstr 0 0 0
90 0 ▶ return $self->_error("$sn: database not connected") unless $self->{dbh};
Branch: 1 branch
BranchTrueFalseTotal
unless $self->{"dbh"} 0 0 0
91 }
92  
93 # sql_do ( $query, @bind_values )
94 # returns number of rows affected
95 # use for non-SELECT ad-hoc queries
96 sub sql_do
97 0 ▶{
Subroutine sql_do: not called CC=5 SCAR=34.0
Pod documented
98 0 my $sn = 'sql_do';
99 0 my $self = shift;
100 0 ▶ my $query = shift or return VOID;
Branch: 1 branch
BranchTrueFalseTotal
my $query = shift() or return undef 0 0 0
101 0 my @bind_values = @_;
102  
103 0 ▶ $self->init_db() unless $self->{dbh};
Branch: 1 branch
BranchTrueFalseTotal
unless $self->{"dbh"} 0 0 0
104 0 ▶ return $self->_error("Database not connected.") unless $self->{dbh};
Branch: 1 branch
BranchTrueFalseTotal
unless $self->{"dbh"} 0 0 0
105  
106 0 my $rc = $self->{dbh}->do( $query, undef, @bind_values );
107 0 ▶ if ($DBI::err) {
Branch: 1 branch
BranchTrueFalseTotal
if ($DBI::err) { } 0 0 0
108 0 return $self->_error("$sn: DBI: $DBI::errstr");
109 } else {
110 0 return $rc;
111 }
112 }
113  
114 # sql_select ( $query, @bind_values )
115 # returns arrayref of hashrefs or FAILURE
116 # hash keys are equiv of column (or query) names
117 sub sql_select
118 0 ▶{
Subroutine sql_select: not called CC=7 SCAR=40.3
Pod documented
119 0 my $sn = 'sql_select';
120 0 my $self = shift;
121 0 ▶ my $query = shift or return VOID;
Branch: 1 branch
BranchTrueFalseTotal
my $query = shift() or return undef 0 0 0
122 0 my @bind_values = @_;
123  
124 0 ▶ $self->init_db() unless $self->{dbh};
Branch: 1 branch
BranchTrueFalseTotal
unless $self->{"dbh"} 0 0 0
125 0 ▶ return $self->_error("$sn: Database not connected.") unless $self->{dbh};
Branch: 1 branch
BranchTrueFalseTotal
unless $self->{"dbh"} 0 0 0
126  
127 0 my $rc = $self->{dbh}->selectall_arrayref( $query, { Slice => {} }, @bind_values );
128 0 ▶ if ($DBI::err) {
Branch: 2 branches
BranchTrueFalseTotal
if ($DBI::err) { } 0 0 0
elsif ($rc and ref $rc eq 'ARRAY') { } 0 0 0
129 0 return $self->_error("$sn: DBI: $DBI::errstr");
130 ▶ } elsif ( $rc && ref($rc) eq 'ARRAY' ) {
Condition0%0/3
$rc and ref $rc eq "ARRAY"
!ll&&!rl&&r
000
MC/DC0%0/2
$rc and ref $rc eq "ARRAY"
$rc ref $rc eq "ARRAY"
Truth table0%0/3
A and B
A= $rcB= ref $rc eq "ARRAY"
ABresult
0X0
100
111
131 0 return $rc;
132 } else {
133 0 return FAILURE;
134 }
135 }
136  
137 # sql_select_column ( $query, @bind_values )
138 # returns arrayref of values (scalars) or FAILURE
139 sub sql_select_column
140 0 ▶{
Subroutine sql_select_column: not called CC=7 SCAR=40.3
Pod documented
141 0 my $sn = 'sql_select_column';
142 0 my $self = shift;
143 0 ▶ my $query = shift or return VOID;
Branch: 1 branch
BranchTrueFalseTotal
my $query = shift() or return undef 0 0 0
144 0 my @bind_values = @_;
145  
146 0 ▶ $self->init_db() unless $self->{dbh};
Branch: 1 branch
BranchTrueFalseTotal
unless $self->{"dbh"} 0 0 0
147 0 ▶ return $self->_error("$sn: Database not connected.") unless $self->{dbh};
Branch: 1 branch
BranchTrueFalseTotal
unless $self->{"dbh"} 0 0 0
148  
149 0 my $rc = $self->{dbh}->selectcol_arrayref( $query, undef, @bind_values );
150 0 ▶ if ($DBI::err) {
Branch: 2 branches
BranchTrueFalseTotal
if ($DBI::err) { } 0 0 0
elsif ($rc and ref $rc eq 'ARRAY') { } 0 0 0
151 0 return $self->_error("$sn: DBI: $DBI::errstr");
152 ▶ } elsif ( $rc && ref($rc) eq 'ARRAY' ) {
Condition0%0/3
$rc and ref $rc eq "ARRAY"
!ll&&!rl&&r
000
MC/DC0%0/2
$rc and ref $rc eq "ARRAY"
$rc ref $rc eq "ARRAY"
Truth table0%0/3
A and B
A= $rcB= ref $rc eq "ARRAY"
ABresult
0X0
100
111
153 0 return $rc;
154 } else {
155 0 return FAILURE;
156 }
157 }
158  
159 # sql_select_value ( $query, @bind_values )
160 # returns scalar value or FAILURE
161 sub sql_select_value
162 0 ▶{
Subroutine sql_select_value: not called CC=7 SCAR=40.3
Pod documented
163 0 my $sn = 'sql_select_value';
164 0 my $self = shift;
165 0 ▶ my $query = shift or return VOID;
Branch: 1 branch
BranchTrueFalseTotal
my $query = shift() or return undef 0 0 0
166 0 my @bind_values = @_;
167  
168 0 ▶ $self->init_db() unless $self->{dbh};
Branch: 1 branch
BranchTrueFalseTotal
unless $self->{"dbh"} 0 0 0
169 0 ▶ return $self->_error("$sn: Database not connected.") unless $self->{dbh};
Branch: 1 branch
BranchTrueFalseTotal
unless $self->{"dbh"} 0 0 0
170  
171 0 my $rc = $self->{dbh}->selectcol_arrayref( $query, { MaxRows => 1 }, @bind_values );
172 0 ▶ if ($DBI::err) {
Branch: 2 branches
BranchTrueFalseTotal
if ($DBI::err) { } 0 0 0
elsif ($rc and ref $rc eq 'ARRAY') { } 0 0 0
173 0 return $self->_error("$sn: DBI: $DBI::errstr");
174 ▶ } elsif ( $rc && ref($rc) eq 'ARRAY' ) {
Condition0%0/3
$rc and ref $rc eq "ARRAY"
!ll&&!rl&&r
000
MC/DC0%0/2
$rc and ref $rc eq "ARRAY"
$rc ref $rc eq "ARRAY"
Truth table0%0/3
A and B
A= $rcB= ref $rc eq "ARRAY"
ABresult
0X0
100
111
175 0 return $rc->[0];
176 } else {
177 0 return FAILURE;
178 }
179 }
180  
181 # insert( table, { name => value, ... } )
182 # returns SUCCESS or FAILURE
183 sub insert
184 0 ▶{
Subroutine insert: not called CC=10 SCAR=47.0
Pod documented
185 0 my $sn = 'insert';
186 0 ▶ my $self = shift or return undef;
Branch: 1 branch
BranchTrueFalseTotal
my $self = shift() or return undef 0 0 0
187 0 ▶ my $table = shift or return undef;
Branch: 1 branch
BranchTrueFalseTotal
my $table = shift() or return undef 0 0 0
188 0 ▶ my $nvpairs = shift or return undef;
Branch: 1 branch
BranchTrueFalseTotal
my $nvpairs = shift() or return undef 0 0 0
189 0 my @cols;
190 my @vals;
191  
192 0 ▶ $self->init_db() unless $self->{dbh};
Branch: 1 branch
BranchTrueFalseTotal
unless $self->{"dbh"} 0 0 0
193 0 ▶ return $self->_error("$sn: Database not connected.") unless $self->{dbh};
Branch: 1 branch
BranchTrueFalseTotal
unless $self->{"dbh"} 0 0 0
194  
195 0 foreach my $k ( keys %{$nvpairs} ) {
196 0 push @cols, $k;
197 0 push @vals, $nvpairs->{$k};
198 }
199 0 ▶ return FAILURE unless ( @cols and @vals );
Branch: 1 branch
BranchTrueFalseTotal
unless @cols and @vals 0 0 0
Condition0%0/3
@cols and @vals
!ll&&!rl&&r
000
MC/DC0%0/2
@cols and @vals
@cols @vals
Truth table0%0/3
A and B
A= @colsB= @vals
ABresult
0X0
100
111
200  
201 0 my $query =
202 "INSERT INTO $table (" .
203 join( ', ', @cols ) .
204 ") VALUES (" .
205 join( ', ', ('?') x @vals ) .
206 ")";
207  
208 0 ▶ $self->{dbh}->do( $query, undef, @vals ) or return $self->_error("$sn: $DBI::errstr");
Branch: 1 branch
BranchTrueFalseTotal
$self->{"dbh"}->do($query, undef, @vals) or return $self->_error("${sn}: $DBI::errstr") 0 0 0
209 0 return SUCCESS;
210 }
211  
212 # insert_id
213 # returns the insert id from the last insert operation
214 sub insert_id
215 0 ▶{
Subroutine insert_id: not called CC=1 SCAR=6.9
Pod documented
216 0 my $self = shift;
217 0 return $self->{dbh}->last_insert_id( '', '', '', '' ); # use the DBI function
218 }
219  
220 sub table_exists
221 0 ▶{
Subroutine table_exists: not called CC=5 SCAR=34.0
Pod documented
222 0 my $sn = 'table_exists';
223 0 my $self = shift;
224 0 ▶ my $table_name = shift or return $self->_error("no table name");
Branch: 1 branch
BranchTrueFalseTotal
my $table_name = shift() or return $self->_error("no table name") 0 0 0
225  
226 0 ▶ if($self->{dbengine} eq 'SQLite') {
Branch: 2 branches
BranchTrueFalseTotal
if ($self->{'dbengine'} eq 'SQLite') { } 0 0 0
elsif ($self->{'dbengine'} eq 'mysql') { } 0 0 0
227 0 my $rc = $self->sql_select( "pragma table_info($table_name)" );
228 0 return scalar @$rc; # pragma returns no rows if table doesn't exist
229 } elsif($self->{dbengine} eq 'mysql') {
230 0 my $rc = $self->sql_select("describe $table_name");
231 0 ▶ if ( $self->error ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->error) { } 0 0 0
232 0 return FALSE;
233 } else {
234 0 return TRUE;
235 }
236 }
237 }
238  
239 # create an sql date from a unix epoch date
240 sub sql_date
241 0 ▶{
Subroutine sql_date: not called CC=2 SCAR=17.9
Pod undocumented
242 0 my ( $self, $t ) = @_;
243 0 ▶ $t = time unless defined $t;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $t 0 0 0
244  
245 0 my ( $sec, $min, $hour, $mday, $mon, $year, $wday, $yday ) = gmtime($t);
246 0 my $tstr = sprintf( "%04d-%02d-%02d %02d:%02d:%02d", $year + 1900, $mon + 1, $mday, $hour, $min, $sec );
247 0 return $tstr;
248 }
249  
250 1 1;
251  
252 __END__
↑ Summary DB/CRUD.pm »