Phrasebook/SQL.pm

filter: Statementstmt 82.2% 51 / 62 Branchbran 73.0% 19 / 26 Conditioncond 50.0% 3 / 6 MC/DCmcdc 50.0% 2 / 4 Subroutinesub 100% 8 / 8 Podpod 100% 2 / 2 totaltotal 78.7% 85 / 108 CC 17 SCAR 27.5
CC
17
Cov
78%
query
22.9
dbh
2.0
BEGIN
1.0
CRAP 15.7
↑ Summary Phrasebook/Loader/Text.pm »
1 package Data::Phrasebook::SQL;
2 6 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
3 6 ▶use warnings FATAL => 'all';
Subroutine BEGIN: called CC=1 SCAR=0.0
4 6 ▶use base qw( Data::Phrasebook::Generic Data::Phrasebook::Debug );
Subroutine BEGIN: called CC=1 SCAR=0.0
5 6 ▶use Carp qw( croak );
Subroutine BEGIN: called CC=1 SCAR=0.0
6  
7 6 ▶use Data::Phrasebook::SQL::Query;
Subroutine BEGIN: called CC=1 SCAR=0.0
8  
9 6 ▶use vars qw($VERSION);
Subroutine BEGIN: called CC=1 SCAR=0.0
10 6 $VERSION = '0.35';
11  
12 =head1 NAME
13  
14 Data::Phrasebook::SQL - The SQL/DBI Phrasebook Model.
15  
16 =head1 SYNOPSIS
17  
18 use Data::Phrasebook;
19 use DBI;
20  
21 my $dbh = DBI->connect(...);
22  
23 my $book = Data::Phrasebook->new(
24 class => 'SQL',
25 dbh => $dbh,
26 file => 'queries.txt',
27 );
28 my $q = $book->query( 'find_author', {
29 author => "Lance Parkin"
30 });
31 while ( my $row = $q->fetchrow_hashref ) {
32 print "He wrote $row->{title}\n";
33 }
34 $q->finish;
35  
36 F<queries.txt>:
37  
38 find_author=select title,author from books where author = :author
39  
40 =head1 DESCRIPTION
41  
42 In order to make use of features like placeholders in DBI in conjunction
43 with phrasebooks, it's helpful to have a phrasebook be somewhat more aware
44 of how DBI operates. Thus, you get C<Data::Phrasebook::SQL>.
45  
46 C<Data::Phrasebook::SQL> has knowledge of how DBI works and creates and
47 executes your queries appropriately.
48  
49 =head1 CONSTRUCTOR
50  
51 =head2 new
52  
53 Not to be accessed directly, but via the parent L<Data::Phrasebook>, by
54 specifying the class as SQL.
55  
56 Additional arguments to those described in L<Data::Phrasebook::Generic> are:
57  
58 =over 4
59  
60 =item *
61  
62 C<dbh> - a DBI database handle.
63  
64 =back
65  
66 =head1 METHODS
67  
68 =head2 dbh
69  
70 Set, or get, the current DBI handle.
71  
72 =cut
73  
74 12 ▶sub dbh {
Subroutine dbh: called CC=2 SCAR=6.9
Pod documented
75 12 my $self = shift;
76 12 ▶ return @_ ? $self->{dbh} = shift : $self->{dbh};
Branch: 1 branch
BranchTrueFalseTotal
@_ ? : 6 6 12
77 }
78  
79 =head2 query
80  
81 Constructs a L<Data::Phrasebook::SQL::Query> object from a template. Takes at
82 least one argument, this being the identifier for the query. The identifier is
83 used as a key into the phrasebook C<file>. A second argument can be provided,
84 which is an optional hashref of key to value mappings.
85  
86 If phrasebook has a YAML source looking much like the following:
87  
88 ---
89 find_author:
90 sql: select class,title,author from books where author = :author
91  
92 You could write:
93  
94 my $q = $book->query( 'find_author' );
95  
96 OR
97  
98 my $q = $book->query( 'find_author', {
99 author => 'Lance Parkin'
100 } );
101  
102 OR
103  
104 my $author = 'Lance Parkin';
105 my $q = $book->query( 'find_author', {
106 author => \$author,
107 } );
108  
109 # sql = select class,title,author from books where author = ?
110 # args = 'Lance Parkin'
111  
112 In the above examples, the parameters are bound to the SQL using the bind
113 parameters functionality. This is more efficient in most cases where the
114 same SQL is reused with different values for fields.
115  
116 However, not all SQL statements just need to bind parameters, some may require
117 the ability to replace parameters, such as a field list.
118  
119 ---
120 find_author:
121 sql: select :fields from books where author = :author
122  
123 my $q = $book->query( 'find_author',
124 replace => { fields => 'class,title,author' },
125 bind => { author => 'Lance Parkin' }
126 );
127  
128 # sql = select class,title,author from books where author = ?
129 # args = 'Lance Parkin'
130  
131 In all instances, if the SQL template requested does not exist or has no
132 definition, then an error will be thrown.
133  
134 Consult L<Data::Phrasebook::SQL::Query> for what you can then do with your
135 returned object.
136  
137 For reference: the bind hashref argument, if it is given, is given to the
138 query object's C<order_args> and then C<args> methods.
139  
140 =cut
141  
142 7 ▶sub query {
Subroutine query: called CC=16 SCAR=31.3
Pod documented
143 7 my ($self,$id,@args) = @_;
144  
145 7 ▶ $self->store(3,"->query IN") if($self->debug);
Branch: 1 branch
BranchTrueFalseTotal
if $self->debug 0 7 7
146  
147 7 my $map = $self->data($id);
148 7 ▶ croak "No mapping for '$id'" unless($map);
Branch: 1 branch
BranchTrueFalseTotal
unless $map 1 6 7
149 6 my $sql;
150  
151 6 ▶ if($self->debug) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->debug) 0 6 6
152 0 $self->store(4,"->query id=[$id]");
153 0 $self->store(4,"->query map=[$map]");
154 }
155  
156 6 ▶ if(ref $map eq 'HASH') {
Branch: 1 branch
BranchTrueFalseTotal
if (ref $map eq 'HASH') { } 0 6 6
157 croak "No SQL content for '$id'." unless exists $map->{sql}
158 0 ▶ and defined $map->{sql};
Branch: 1 branch
BranchTrueFalseTotal
unless exists $map->{"sql"} and defined $map->{"sql"} 0 0 0
Condition0%0/3
exists $map->{"sql"} and defined $map->{"sql"}
!ll&&!rl&&r
000
MC/DC0%0/2
exists $map->{"sql"} and defined $map->{"sql"}
exists $map->{"sql"} defined $map->{"sql"}
Truth table0%0/3
A and B
A= exists $map->{"sql"}B= defined $map->{"sql"}
ABresult
0X0
100
111
159 0 $sql = $map->{sql};
160 } else {
161 6 $sql = $map; # we assume sql string only
162 }
163  
164 6 ▶ unshift @args, 'bind' if(scalar(@args) == 1); # default is to bind parameters
Branch: 1 branch
BranchTrueFalseTotal
if scalar @args == 1 2 4 6
165  
166 6 ▶ if($self->debug) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->debug) 0 6 6
167 0 $self->store(4,"->query BEFORE methods");
168 0 $self->store(4,"->query sql=[$sql]");
169 0 $self->store(4,"->query args=[".$self->dumper(\@args)."]");
170 }
171  
172 6 my (%args,$params,@order);
173 6 ▶ while(@args) {
Branch: 1 branch
BranchTrueFalseTotal
if (@args) 6 6 12
174 # go backwards in case there are duplicate keys
175 6 my $args = pop @args;
176 6 my $method = pop @args;
177  
178 6 for(keys %$args) {
179 6 $args{$_}->{method} = $method;
180 6 $args{$_}->{value} = $args->{$_};
181 }
182  
183 6 ▶ $params = $args if($method eq 'bind');
Branch: 1 branch
BranchTrueFalseTotal
if $method eq "bind" 4 2 6
184 }
185  
186 6 my $delim_RE = $self->delimiters();
187 6 $sql =~ s{$delim_RE}[
188 {
189 8 ▶ if(defined $args{$1} && $args{$1}->{method} eq 'replace') {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $args{$1} and $args{$1}{'method'} eq 'replace') { } 2 6 8
Condition100%3/3
defined $args{$1} and $args{$1}{"method"} eq "replace"
!ll&&!rl&&r
242
MC/DC100%2/2
defined $args{$1} and $args{$1}{"method"} eq "replace"
defined $args{$1} $args{$1}{"method"} eq "replace"
Truth table100%3/3
A and B
A= defined $args{$1}B= $args{$1}{"method"} eq "replace"
ABresult
0X0
100
111
190 2 $args{$1}->{value};
191 } else {
192 6 push @order, $1;
193 6 "?"
194 }
195 };
196 ]egx;
197  
198 6 ▶ if($self->debug) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->debug) 0 6 6
199 0 $self->store(4,'->query AFTER methods');
200 0 $self->store(4,"->query sql=[$sql]");
201 0 $self->store(4,'->query order=['.join(',',@order).']');
202 0 $self->store(4,'->query params=['.$self->dumper($params).']');
203 }
204  
205 6 my $q = Data::Phrasebook::SQL::Query->new(
206 sql => $sql,
207 order => \@order,
208 dbh => $self->dbh,
209 );
210 6 ▶ $q->args( $q->order_args( $params ) ) if($params);
Branch: 1 branch
BranchTrueFalseTotal
if $params 4 2 6
211 6 return $q;
212 }
213  
214 6 1;
215  
216 __END__
↑ Summary Phrasebook/Loader/Text.pm »