Connection/PostgreSQL/SQL.pm

filter: Statementstmt 16.8% 18 / 107 Branchbran 0.0% 0 / 48 Conditioncond 0.0% 0 / 8 MC/DCmcdc 0.0% 0 / 5 Subroutinesub 31.2% 5 / 16 Podpod 100% 6 / 6 totaltotal 15.2% 29 / 190 CC 30 SCAR 35.2
CC
30
Cov
11%
_create_lob
72.0
update_lob
56.0
_read_lob
42.0
CRAP 33.8
« Connection/MySQL/SQL.pm ↑ Summary Connection/Oracle/SQL.pm »
1 package DBIx::Connection::PostgreSQL::SQL;
2  
3 1 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
4 1 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
5 1 ▶use vars qw($VERSION $LOB_MAX_SIZE);
Subroutine BEGIN: called CC=1 SCAR=0.0
6  
7 1 ▶use Abstract::Meta::Class ':all';
Subroutine BEGIN: called CC=1 SCAR=0.0
8 1 ▶use Carp 'confess';
Subroutine BEGIN: called CC=1 SCAR=0.0
9  
10 1 $VERSION = 0.03;
11 1 $LOB_MAX_SIZE = (1024 * 1024 * 1024);
12  
13 =head1 NAME
14  
15 DBIx::Connection::PostgreSQL::SQL - PostgreSQL catalog sql abstractaction layer.
16  
17 =cut
18  
19 =head1 SYNOPSIS
20
21 use DBIx::Connection::PostgreSQL::SQL;
22  
23 =head1 DESCRIPTION
24  
25 Represents sql abstractaction layer
26  
27 =head1 EXPORT
28  
29 None
30  
31 =head2 METHODS
32  
33 =over
34  
35 =item sequence_value
36  
37 Returns sql statement that returns next sequence value
38  
39 =cut
40  
41 0 ▶sub sequence_value {
Subroutine sequence_value: not called CC=1 SCAR=6.9
Pod documented
42 0 my ($class, $sequence_name) = @_;
43 0 "SELECT nextval('${sequence_name}') AS val"
44 }
45  
46  
47 =item reset_sequence
48  
49 Returns sql statement that restarts sequence.
50  
51 =cut
52  
53 0 ▶sub reset_sequence {
Subroutine reset_sequence: not called CC=2 SCAR=17.9
Pod documented
54 0 my ($class, $sequence_name, $restart_with, $increment_by) = @_;
55 0 ▶ ("ALTER SEQUENCE ${sequence_name} RESTART WITH ${restart_with}",
Branch: 1 branch
BranchTrueFalseTotal
$increment_by ? : 0 0 0
56 ($increment_by ? "ALTER SEQUENCE ${sequence_name} INCREMENT ${increment_by}" : ()));
57 }
58  
59  
60 =item has_sequence
61  
62 Returns sql statement that check is sequence exists in database schema
63  
64 =cut
65  
66 0 ▶sub has_sequence {
Subroutine has_sequence: not called CC=1 SCAR=6.9
Pod documented
67 0 my ($class, $schema) = @_;
68 0 "SELECT pc.relname AS sequence_name
69 FROM pg_class pc
70 JOIN pg_authid pa ON pa.oid = pc.relowner AND pc.relkind = 'S' AND pc.relname = lower(?) AND rolname = '". $schema ."' ";
71 }
72  
73  
74 =item set_session_variables
75  
76 Sets session variables
77  
78 It uses the following sql command pattern:
79  
80 SET variable TO value;
81  
82 DBIx::Connection::PostgreSQL::SQL->set_session_variables($connection, {DateStyle => 'US'});
83  
84 =cut
85  
86 0 ▶sub set_session_variables {
Subroutine set_session_variables: not called CC=2 SCAR=17.9
Pod documented
87 0 my ($class, $connection, $db_session_variables) = @_;
88 0 my $sql = "";
89 $sql .= "SET " . $_ . " TO " . $db_session_variables->{$_} . ";"
90 0 for keys %$db_session_variables;
91 0 $connection->do($sql);
92 }
93  
94  
95 =item update_lob
96  
97 Updates lob. (Large Object)
98 Takes connection object, table name, lob column_name, lob conetent, hash_ref to primary key values. optionally lob size column name.
99  
100 =cut
101  
102 0 ▶sub update_lob {
Subroutine update_lob: not called CC=7 SCAR=40.3
Pod documented
103 0 my ($class, $connection, $table_name, $lob_column_name, $lob, $primary_key_values, $lob_size_column_name) = @_;
104 0 ▶ confess "missing primary key for lob update on ${table_name}.${lob_column_name}"
Branch: 1 branch
BranchTrueFalseTotal
unless $primary_key_values and %$primary_key_values 0 0 0
Condition0%0/3
$primary_key_values and %$primary_key_values
!ll&&!rl&&r
000
MC/DC0%0/2
$primary_key_values and %$primary_key_values
$primary_key_values %$primary_key_values
Truth table0%0/3
A and B
A= $primary_key_valuesB= %$primary_key_values
ABresult
0X0
100
111
105 if (!$primary_key_values || ! (%$primary_key_values));
106 0 ▶ confess "missing lob size column name" unless $lob_size_column_name;
Branch: 1 branch
BranchTrueFalseTotal
unless $lob_size_column_name 0 0 0
107 0 my $sql = "UPDATE ${table_name} SET ${lob_column_name} = ? ";
108 0 ▶ $sql .= ($lob_size_column_name ? ", ${lob_size_column_name} = ? " : '')
Branch: 1 branch
BranchTrueFalseTotal
$lob_size_column_name ? : 0 0 0
109 . $connection->_where_clause($primary_key_values);
110
111 0 $class->_unlink_lob($connection, $class->_get_lob_id($connection, $table_name, $primary_key_values, $lob_column_name));
112 0 my $lob_id = $class->_create_lob($connection, $lob);
113 0 my $bind_counter = 1;
114 0 my $sth = $connection->dbh->prepare($sql);
115 0 $sth->bind_param($bind_counter++ ,$lob_id);
116 0 ▶ $sth->bind_param($bind_counter++ , length($lob)) if $lob_size_column_name;
Branch: 1 branch
BranchTrueFalseTotal
if $lob_size_column_name 0 0 0
117 0 for my $k (sort keys %$primary_key_values) {
118 0 $sth->bind_param($bind_counter++ , $primary_key_values->{$k});
119 }
120 0 $sth->execute();
121 }
122  
123  
124  
125 =item fetch_lob
126  
127 Retrieve lobs.
128 Takes connection object, table name, lob column_name, hash_ref to primary key values. optionally lob size column name.
129 By default max lob size is set to 1 GB
130 DBIx::Connection::Oracle::SQL::LOB_MAX_SIZE = (1024 * 1024 * 1024);
131  
132 =cut
133  
134 0 ▶sub fetch_lob {
Subroutine fetch_lob: not called CC=4 SCAR=30.0
Pod documented
135 0 my ($class, $connection, $table_name, $lob_column_name, $primary_key_values, $lob_size_column_name) = @_;
136 0 ▶ confess "missing primary key for lob update on ${table_name}.${lob_column_name}"
Branch: 1 branch
BranchTrueFalseTotal
unless $primary_key_values and %$primary_key_values 0 0 0
Condition0%0/3
$primary_key_values and %$primary_key_values
!ll&&!rl&&r
000
MC/DC0%0/2
$primary_key_values and %$primary_key_values
$primary_key_values %$primary_key_values
Truth table0%0/3
A and B
A= $primary_key_valuesB= %$primary_key_values
ABresult
0X0
100
111
137 if (! $primary_key_values || ! (%$primary_key_values));
138 0 ▶ confess "missing lob size column name" unless $lob_size_column_name;
Branch: 1 branch
BranchTrueFalseTotal
unless $lob_size_column_name 0 0 0
139 0 my $lob_id = $class->_get_lob_id($connection, $table_name, $primary_key_values, $lob_column_name);
140 0 my $lob_size = $class->_get_lob_size($connection, $table_name, $primary_key_values, $lob_size_column_name);
141 0 $class->_read_lob($connection, $lob_id, $lob_size);
142 }
143  
144  
145  
146 =item _create_lob
147  
148 Creates lob
149  
150 =cut
151  
152 0 ▶sub _create_lob {
Subroutine _create_lob: not called CC=8 SCAR=42.8
153 0 my ($class, $connection, $lob) = @_;
154 0 my $dbh = $connection->dbh;
155 0 my $autocomit_mode = $connection->has_autocomit_mode;
156 0 ▶ $connection->begin_work if $autocomit_mode;
Branch: 1 branch
BranchTrueFalseTotal
if $autocomit_mode 0 0 0
157 0 my $mode = $dbh->{pg_INV_WRITE};
158 0 ▶ my $lob_id = $dbh->func($mode, 'lo_creat')
Branch: 1 branch
BranchTrueFalseTotal
my $lob_id = $dbh->func($mode, "lo_creat") or confess(q[can't create lob ] . $!) 0 0 0
159 or confess "can't create lob ". $!;
160 0 my $lobj_fd = $dbh->func($lob_id, $mode, 'lo_open');
161 0 ▶ confess "can't open lob ${lob_id}". $!
Branch: 1 branch
BranchTrueFalseTotal
unless defined $lobj_fd 0 0 0
162 unless defined $lobj_fd;
163 0 ▶ my $length = length($lob || '');
Condition0%0/2
$lob || ''
l!l
00
MC/DC0%0/1
$lob || ''
$lob
Truth table0%0/2
A
A= $lob
Aresult
11
00
164 0 my $offset = 0;
165 0 ▶ while($length > 0) {
Branch: 1 branch
BranchTrueFalseTotal
if ($length > 0) 0 0 0
166 0 $dbh->func($lobj_fd, $offset, 0, 'lo_lseek');
167 0 ▶ my $nbytes = $dbh->func($lobj_fd, substr($lob, $offset, $length), $length, 'lo_write')
Branch: 1 branch
BranchTrueFalseTotal
my $nbytes = $dbh->func($lobj_fd, substr($lob, $offset, $length), $length, "lo_write") or confess(q[can't write lob ] . $!) 0 0 0
168 or confess "can't write lob " . $!;
169 0 $offset += $nbytes;
170 0 $length -= $nbytes;
171 }
172 0 $dbh->func($lobj_fd, 'lo_close');
173 0 ▶ $connection->commit if ($autocomit_mode);
Branch: 1 branch
BranchTrueFalseTotal
if $autocomit_mode 0 0 0
174 0 $lob_id;
175 }
176
177  
178  
179 =item _read_lob
180  
181 Reads lob
182  
183 =cut
184  
185 0 ▶sub _read_lob {
Subroutine _read_lob: not called CC=6 SCAR=37.4
186 0 my ($class, $connection, $lob_id, $length) = @_;
187 0 my $dbh = $connection->dbh;
188 0 my $autocomit_mode = $connection->has_autocomit_mode;
189 0 ▶ $connection->begin_work if $autocomit_mode;
Branch: 1 branch
BranchTrueFalseTotal
if $autocomit_mode 0 0 0
190 0 my $mode = $dbh->{pg_INV_READ};
191 0 my $lobj_fd = $dbh->func($lob_id, $mode, 'lo_open');
192 0 ▶ confess "can't open lob ${lob_id}". $!
Branch: 1 branch
BranchTrueFalseTotal
unless defined $lobj_fd 0 0 0
193 unless defined $lobj_fd;
194 0 my $result = '';
195 0 my $buff = '';
196 0 my $offset = 0;
197 0 while(1) {
198 0 $dbh->func($lobj_fd, $offset, 0, 'lo_lseek');
199 0 my $nbytes = $dbh->func($lobj_fd, $buff, $length, 'lo_read');
200 0 ▶ confess "can't read lob ${lob_id}" . $!
Branch: 1 branch
BranchTrueFalseTotal
unless defined $nbytes 0 0 0
201 unless defined $nbytes;
202 0 $result .= $buff;
203 0 $offset += $nbytes;
204 0 ▶ last if ($offset == $length);
Branch: 1 branch
BranchTrueFalseTotal
if $offset == $length 0 0 0
205 }
206 0 $dbh->func($lobj_fd, 'lo_close');
207 0 ▶ $connection->commit if ($autocomit_mode);
Branch: 1 branch
BranchTrueFalseTotal
if $autocomit_mode 0 0 0
208 0 $result
209 }
210
211  
212 =item _unlnik_lob
213  
214 Removes lob.
215  
216 =cut
217  
218 0 ▶sub _unlink_lob {
Subroutine _unlink_lob: not called CC=5 SCAR=34.0
219 0 my ($class, $connection, $lob_id) = @_;
220 0 ▶ return unless $lob_id;
Branch: 1 branch
BranchTrueFalseTotal
unless $lob_id 0 0 0
221 0 my $dbh = $connection->dbh;
222 0 my $autocomit_mode = $connection->has_autocomit_mode;
223 0 ▶ $connection->begin_work if $autocomit_mode;
Branch: 1 branch
BranchTrueFalseTotal
if $autocomit_mode 0 0 0
224 0 ▶ $dbh->func($lob_id, 'lo_unlink')
Branch: 1 branch
BranchTrueFalseTotal
$dbh->func($lob_id, "lo_unlink") or confess("can't unlink lob $lob_id" . $!) 0 0 0
225 or confess "can't unlink lob ${lob_id}". $!;
226 0 ▶ $connection->commit if ($autocomit_mode);
Branch: 1 branch
BranchTrueFalseTotal
if $autocomit_mode 0 0 0
227 }
228  
229  
230 =item _get_lob_size
231  
232 Returns lob size.
233  
234 =cut
235  
236 0 ▶sub _get_lob_size {
Subroutine _get_lob_size: not called CC=3 SCAR=24.8
237 0 my ($class, $connection, $table_name, $primary_key_values, $lob_size_column_name) = @_;
238 0 my $resut;
239 0 ▶ if($lob_size_column_name) {
Branch: 1 branch
BranchTrueFalseTotal
if ($lob_size_column_name) 0 0 0
240 0 my $sql = "SELECT ${lob_size_column_name} as lob_size FROM ${table_name} " . $connection->_where_clause($primary_key_values);
241 0 my ($record) = $connection->record($sql, map { $primary_key_values->{$_}} sort keys %$primary_key_values);
242 0 $resut = $record->{lob_size};
243 }
244 0 ▶ $resut || $LOB_MAX_SIZE;
Branch: 1 branch
BranchTrueFalseTotal
$resut or $DBIx::Connection::PostgreSQL::SQL::LOB_MAX_SIZE 0 0 0
245 }
246  
247  
248 =item _get_lob_id
249  
250 Returns lob oid.
251  
252 =cut
253  
254 0 ▶sub _get_lob_id {
Subroutine _get_lob_id: not called CC=1 SCAR=6.9
255 0 my ($class, $connection, $table_name, $primary_key_values, $lob_column_name) = @_;
256 0 my $resut;
257 0 my $sql = "SELECT ${lob_column_name} as lob_id FROM ${table_name} " . $connection->_where_clause($primary_key_values);
258 0 my ($record) = $connection->record($sql, map { $primary_key_values->{$_}} sort keys %$primary_key_values);
259 0 $resut = $record->{lob_id};
260 }
261  
262 1 1;
263  
264 __END__
« Connection/MySQL/SQL.pm ↑ Summary Connection/Oracle/SQL.pm »