PLSQLHandler.pm

filter: Statementstmt 32.1% 36 / 112 Branchbran 0.0% 0 / 24 Conditioncond 0.0% 0 / 9 MC/DCmcdc 0.0% 0 / 6 Subroutinesub 37.5% 9 / 24 Podpod 100% 15 / 15 totaltotal 31.5% 60 / 190 CC 19 SCAR 29.6
CC
19
Cov
25%
initialise_bind_variables
72.0
set_binding_order
20.0
plsql_block_declaration
12.0
CRAP 19.4
« Connection.pm ↑ Summary SQLHandler.pm »
1 package DBIx::PLSQLHandler;
2  
3 2 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
4 2 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
5  
6 2 ▶use Abstract::Meta::Class ':has';
Subroutine BEGIN: called CC=1 SCAR=0.0
7 2 ▶use Carp 'confess';
Subroutine BEGIN: called CC=1 SCAR=0.0
8 2 ▶use base 'DBIx::SQLHandler';
Subroutine BEGIN: called CC=1 SCAR=0.0
9 2 ▶use Data::Dumper;
Subroutine BEGIN: called CC=1 SCAR=0.0
10 2 ▶use vars qw($VERSION);
Subroutine BEGIN: called CC=1 SCAR=0.0
11  
12 2 $VERSION = 0.02;
13  
14 2 ▶use constant DEFAULT_TYPE => 'SQL_VARCHAR';
Subroutine BEGIN: called CC=1 SCAR=0.0
15 2 ▶use constant DEFAULT_WIDTH => 32000;
Subroutine BEGIN: called CC=1 SCAR=0.0
16  
17  
18 =head1 NAME
19  
20 DBIx::PLSQLHandler - PL/SQL procedural language handler.
21  
22 =head1 SYNOPSIS
23  
24 use DBIx::PLSQLHandler;
25 my $plsql = new DBIx::PLSQLHandler(
26 connection => $connection,
27 plsql => "
28 DECLARE
29 debit_amt CONSTANT NUMBER(5,2) := 500.00;
30 BEGIN
31 SELECT a.bal INTO :acct_balance FROM accounts a
32 WHERE a.account_id = :acct AND a.debit > debit_amt;
33 :extra_info := 'debit_amt: ' || debit_amt;
34 END;"
35 );
36  
37 my $result_set = $plsql->execute(acct => 000212);
38 # $result_set->{acct_balance}; $result_set->{extra_info}
39 ... do some stuff
40  
41 or
42  
43 use DBIx::Connection;
44  
45 ...
46  
47 my $plsql = $connection->plsql_handler(
48 plsql => "
49 DECLARE
50 debit_amt CONSTANT NUMBER(5,2) := 500.00;
51 BEGIN
52 SELECT a.bal INTO :acct_balance FROM accounts a
53 WHERE a.account_id = :acct AND a.debit > debit_amt;
54 :extra_info := 'debit_amt: ' || debit_amt;
55 END;"
56 );
57  
58  
59 =head1 DESCRIPTION
60  
61 Base class for PLSQL blocks hyandler(SQL Procedural Language).
62 It allows use independetly specyfig Procedural Language SQL dialect like PL/SQL (Oracle, mySQL), PL/pgSQL (PostgreSQL)
63 It uses ":" placeholers to bind variables in or out or inout.
64  
65 By default it bind variable is defined as varchar,
66 however you can change it by specyfing your types in bind_variables parameter.
67  
68  
69 my $plsql_handler = new DBIx::PLSQLHandler(
70 name => 'int_test',
71 connection => $connection,
72 plsql => "BEGIN
73 :var1 := :var2 + :var3;
74 :var4 := 'long text';
75 END;",
76 bind_variables => {
77 var1 => {type => 'SQL_INTEGER'},
78 var4 => {type => 'SQL_VARCHAR', width => 30}
79 }
80 );
81  
82 In Oracle database it uses an anonymous PLSQL block,
83 In mysql procedure wraps the plsql block.
84 In postgresql function wraps the plsql block.
85 Name for the procedure/function wrapper is created as 'anonymous_' + $self->name
86  
87 =head2 ATTRIBUTES
88  
89 =over
90  
91 =item plsql
92  
93 Plsql block
94  
95 =cut
96  
97
98 2 has '$.plsql';
99  
100  
101 =item bind_variables
102  
103 Keeps information about binds variables and its types.
104  
105 =cut
106  
107 2 has '%.bind_variables' => (item_accessor => 'bind_variable');
108  
109  
110 =item bind_in_variales
111  
112 Ordered list for binding in variables
113  
114 =cut
115  
116 2 has '@.bind_in_variables';
117  
118  
119 =item bind_inout_variales
120  
121 Ordered list for binding in out variables
122  
123 =cut
124  
125 2 has '@.bind_inout_variables';
126  
127  
128 =item bind_out_variales
129  
130 Ordered list for binding out variables
131  
132 =cut
133  
134 2 has '@.bind_out_variables';
135  
136  
137 =item default_type
138  
139 default type binding
140  
141 =cut
142  
143 2 has '$.default_type' => (default => DEFAULT_TYPE);
144  
145  
146 =item default_width
147  
148 default width binding
149  
150 =cut
151  
152 2 has '$.default_width' => (default => DEFAULT_WIDTH);
153  
154 =back
155  
156 =head2 METHODS
157  
158 =over
159  
160 =item new
161  
162 =cut
163  
164 0 ▶sub new {
Subroutine new: not called CC=1 SCAR=6.9
Pod documented
165 0 my ($class, %args) = @_;
166 0 my $specialisation_module = $args{connection}->load_module('PLSQL');
167 0 my $self = $specialisation_module->new(%args);
168 0 return $self;
169 }
170  
171  
172 =item initialise
173  
174 Initialises handler.
175  
176 =cut
177  
178 0 ▶sub initialise {
Subroutine initialise: not called CC=1 SCAR=6.9
Pod documented
179 0 my ($self) = @_;
180 0 $self->initialise_bind_variables();
181 0 $self->SUPER::initialise();
182 }
183  
184  
185 =item initialise_bind_variables
186  
187 Parses plsql for binding variables.
188 TODO replace this naive implementations.
189  
190 =cut
191  
192 0 ▶sub initialise_bind_variables {
Subroutine initialise_bind_variables: not called CC=8 SCAR=42.8
Pod documented
193 0 my ($self) = @_;
194 0 my $plsql = $self->plsql;
195 0 my $bind_variables = $self->bind_variables;
196 0 $plsql =~ s/\'[^\']*\'//g;
197 0 ▶ while ($plsql =~ s/:(\w+)\s*(:*)//) {
Branch: 1 branch
BranchTrueFalseTotal
if ($plsql =~ s/:(\w+)\s*(:*)//) 0 0 0
198 0 my $bind_variable = $1;
199 0 my $out_flag = $2;
200 0 my $variable = $bind_variables->{$bind_variable};
201 0 ▶ if ($variable && $variable->{binding}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($variable and $variable->{'binding'}) { } 0 0 0
Condition0%0/3
$variable and $variable->{"binding"}
!ll&&!rl&&r
000
MC/DC0%0/2
$variable and $variable->{"binding"}
$variable $variable->{"binding"}
Truth table0%0/3
A and B
A= $variableB= $variable->{"binding"}
ABresult
0X0
100
111
202 0 ▶ $variable->{binding} = 'inout' if ($out_flag && $variable->{binding} eq 'in');
Branch: 1 branch
BranchTrueFalseTotal
if $out_flag and $variable->{"binding"} eq "in" 0 0 0
Condition0%0/3
$out_flag and $variable->{"binding"} eq "in"
!ll&&!rl&&r
000
MC/DC0%0/2
$out_flag and $variable->{"binding"} eq "in"
$out_flag $variable->{"binding"} eq "in"
Truth table0%0/3
A and B
A= $out_flagB= $variable->{"binding"} eq "in"
ABresult
0X0
100
111
203
204 } else {
205 0 ▶ $variable = $bind_variables->{$bind_variable} = $self->default_variable_info
Branch: 1 branch
BranchTrueFalseTotal
unless $variable 0 0 0
206 unless $variable;
207 0 ▶ $variable->{binding} = $out_flag ? 'out' : 'in';
Branch: 1 branch
BranchTrueFalseTotal
$out_flag ? : 0 0 0
208 }
209 }
210 0 $self->set_binding_order();
211 }
212  
213  
214 =item set_binding_order
215  
216 =cut
217  
218 0 ▶sub set_binding_order {
Subroutine set_binding_order: not called CC=4 SCAR=30.0
Pod documented
219 0 my ($self) = @_;
220 0 my $bind_variables = $self->bind_variables;
221 0 my $bind_in_variables = $self->bind_in_variables;
222 0 my $bind_inout_variables = $self->bind_inout_variables;
223 0 my $bind_out_variables = $self->bind_out_variables;
224
225 0 foreach my $k (sort keys %$bind_variables) {
226 0 my $variable = $bind_variables->{$k};
227 0 ▶ if ($variable->{binding} eq 'in') {
Branch: 2 branches
BranchTrueFalseTotal
if ($variable->{'binding'} eq 'in') { } 0 0 0
elsif ($variable->{'binding'} eq 'out') { } 0 0 0
228 0 push @$bind_in_variables, $k;
229
230 } elsif ($variable->{binding} eq 'out') {
231 0 push @$bind_out_variables, $k;
232
233 } else {
234 0 push @$bind_inout_variables, $k;
235 }
236 }
237 }
238  
239  
240 =item default_variable_info
241  
242 Adds default variable meta data.
243  
244 =cut
245  
246 0 ▶sub default_variable_info {
Subroutine default_variable_info: not called CC=1 SCAR=6.9
Pod documented
247 0 my $self = shift;
248 0 {type => $self->default_type, width => $self->default_width, @_};
249 }
250  
251  
252 =item plsql_block_name
253  
254 Returns plsql block name (used to create plsql block procedure or function wrapper)
255  
256 =cut
257  
258 0 ▶sub plsql_block_name {
Subroutine plsql_block_name: not called CC=2 SCAR=17.9
Pod documented
259 0 my ($self) = @_;
260 0 my $result = "anonymous_";
261 0 ▶ if ($self->name =~ m/\s+/) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->name =~ /\s+/) { } 0 0 0
262 0 $result .= unpack("%32C*",$self->name);
263 } else {
264 0 $result .= $self->name;
265 }
266 0 substr($result, 0, 30);
267 }
268  
269  
270 =item plsql_block_declaration
271  
272 =cut
273  
274 0 ▶sub plsql_block_declaration {
Subroutine plsql_block_declaration: not called CC=3 SCAR=24.8
Pod documented
275 0 my ($self) = @_;
276 0 my $result = '';
277 0 foreach my $k($self->bind_variable_order) {
278 0 ▶ $result .= ($result ? ', ' : '') . $self->variable_declaration($k);
Branch: 1 branch
BranchTrueFalseTotal
$result ? : 0 0 0
279 }
280 0 $result;
281 }
282  
283  
284 =item bind_variable_order
285  
286 Return bind variable order
287  
288 =cut
289  
290 0 ▶sub bind_variable_order {
Subroutine bind_variable_order: not called CC=1 SCAR=6.9
Pod documented
291 0 my ($self) = @_;
292 0 ($self->bind_in_variables, $self->bind_inout_variables, $self->bind_out_variables);
293 }
294  
295  
296 =item binded_in_variables
297  
298 Returns bind_in_variables + bind_inout_variables
299  
300 =cut
301  
302 0 ▶sub binded_in_variables {
Subroutine binded_in_variables: not called CC=1 SCAR=6.9
Pod documented
303 0 my ($self) = @_;
304 0 ($self->bind_in_variables, $self->bind_inout_variables);
305 }
306  
307  
308 =item binded_out_variables
309  
310 Returns bind_inout_variables + bind_out_variables
311  
312 =cut
313  
314 0 ▶sub binded_out_variables {
Subroutine binded_out_variables: not called CC=1 SCAR=6.9
Pod documented
315 0 my ($self) = @_;
316 0 ($self->bind_inout_variables, $self->bind_out_variables);
317 }
318  
319  
320 =item variable_declaration
321  
322 Returns variable definition for plsql block stub
323  
324 =cut
325  
326 0 ▶sub variable_declaration {
Subroutine variable_declaration: not called CC=1 SCAR=6.9
Pod documented
327 0 my ($self, $variable_name) = @_;
328 0 my $variable = $self->bind_variable($variable_name);
329 0 my $type = $variable->{type};
330 0 uc($variable->{binding}) .' ' . $variable_name . ' ' . $self->get_type($type) . $self->type_precision($variable_name);
331 }
332  
333  
334 =item type_precision
335  
336 Returns variable type precision, takes bind variable name.
337  
338 =cut
339  
340 0 ▶sub type_precision {
Subroutine type_precision: not called CC=3 SCAR=24.8
Pod documented
341 0 my ($self, $variable_name) = @_;
342 0 my $variable = $self->bind_variable($variable_name);
343 0 ▶ ($variable->{type} && $variable->{type} =~ /CHAR/ ? '(' . $variable->{width} . ')' : '')
Branch: 1 branch
BranchTrueFalseTotal
$variable->{'type'} && $variable->{'type'} =~ /CHAR/ ? : 0 0 0
Condition0%0/3
$variable->{"type"} and $variable->{"type"} =~ /CHAR/
!ll&&!rl&&r
000
MC/DC0%0/2
$variable->{"type"} and $variable->{"type"} =~ /CHAR/
$variable->{"type"} $variable->{"type"} =~ /CHAR/
Truth table0%0/3
A and B
A= $variable->{"type"}B= $variable->{"type"} =~ /CHAR/
ABresult
0X0
100
111
344 }
345  
346  
347 =item block_source
348  
349 Block source, used for comparision against database wrapper source.
350  
351 =cut
352  
353 0 ▶sub block_source {
Subroutine block_source: not called CC=1 SCAR=6.9
Pod documented
354 0 my ($self) = @_;
355 0 "BEGIN\n"
356 . $self->parsed_plsql
357 ."\nEND;";
358 }
359  
360  
361 =item parsed_plsql
362  
363 Parses plsql code and replaces :var to var
364  
365 =cut
366  
367 0 ▶sub parsed_plsql {
Subroutine parsed_plsql: not called CC=2 SCAR=17.9
Pod documented
368 0 my ($self) = @_;
369 0 my $plsql = $self->plsql;
370 0 my $bind_variables = $self->bind_variables;
371 0 foreach my $variable (sort keys %$bind_variables) {
372 0 $plsql =~ s/:$variable/$variable/g;
373 }
374 0 $plsql;
375 }
376  
377  
378 =item is_block_changed
379  
380 Checks if plsql_block has been changed and return true otherwise false.
381  
382 =cut
383  
384 0 ▶sub is_block_changed {
Subroutine is_block_changed: not called CC=3 SCAR=24.8
Pod documented
385 0 my ($self, @bind_param) = @_;
386 0 my $connection = $self->connection;
387 0 my $record = $connection->record($self->sql_defintion('find_function'), @bind_param);
388 0 ▶ my $routine_definition = $record->{routine_definition} or return 1;
Branch: 1 branch
BranchTrueFalseTotal
my $routine_definition = $record->{"routine_definition"} or return 1 0 0 0
389 0 $routine_definition =~ s/[\n\r\s\t;]//g;
390 0 my $block_source = $self->block_source;
391 0 $block_source =~ s/[\n\r\s\t;]//g;
392 0 ▶ if ($block_source ne $routine_definition) {
Branch: 1 branch
BranchTrueFalseTotal
if ($block_source ne $routine_definition) 0 0 0
393 0 $self->drop_plsql_block;
394 0 return 1
395 };
396 0 !! undef;
397 }
398  
399  
400 2 1;
401  
402 __END__
« Connection.pm ↑ Summary SQLHandler.pm »