lib/DB/Object/Postgres/Statement.pm

filter: Statementstmt 86.6% 13 / 15 Branchbran n/a Conditioncond n/a MC/DCmcdc n/a Subroutinesub 100% 5 / 5 Podpod n/a totaltotal 90.0% 18 / 20 CC 1 SCAR 0.6
CC
1
Cov
87%
BEGIN
1.3
BEGIN
1.0
BEGIN
1.0
CRAP 1.1
« lib/DB/Object/Fields/Overloaded.pm ↑ Summary lib/DB/Object/Exception.pm »
1 # -*- perl -*-
2 ##----------------------------------------------------------------------------
3 ## SQL API Abstraction - ~/lib/DB/Object/Postgres/Statement.pm
4 ## Version v0.302.2
5 ## Copyright(c) 2026 DEGUEST Pte. Ltd.
6 ## Author: Jacques Deguest <jack@deguest.jp>
7 ## Created 2017/07/19
8 ## Modified 2026/10/01
9 ## All rights reserved
10 ##
11 ## This program is free software; you can redistribute it and/or modify it
12 ## under the same terms as Perl itself.
13 ##----------------------------------------------------------------------------
14 # This package's purpose is to automatically terminate the statement object and
15 # separate them from the connection object (DB::Object).
16 # Connection object last longer than statement objects
17 ##----------------------------------------------------------------------------
18 package DB::Object::Postgres::Statement;
19 BEGIN
20 {
21 1 ▶ use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
22 1 ▶ use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
23 warnings::register_categories( 'DB::Object' );
24 1 ▶ use parent qw( DB::Object::Statement );
Subroutine BEGIN: called CC=1 SCAR=0.0
25 1 ▶ use vars qw( $VERSION $DEBUG );
Subroutine BEGIN: called CC=1 SCAR=0.0
26 0 ▶ use DBD::Pg ':pg_types';
Subroutine BEGIN: called CC=1 SCAR=2.6
27 our $DEBUG = 0;
28 our $VERSION = 'v0.302.2';
29 };
30  
31 use strict;
32 use warnings;
33  
34 # NOTE: sub bind_param is inherited from DB::Object::Statement
35 # sub bind_param
36  
37 # sub commit is called by dbh, so it is in DB::Object::Postgres
38  
39 sub disable_trigger
40 {
41 my $self = shift( @_ );
42 my $opts = $self->_get_args_as_hash( @_ );
43 $opts->{all} //= 0;
44 $opts->{name} //= '';
45 my $query = $self->{query} ||
46 return( $self->error( "No query found to temporarily disable trigger." ) );
47 my $q = $self->query_object;
48 my $tables = $q->join_tables->length ? $q->join_tables : $self->new_array( $q->table_object );
49 my( $before, $after );
50 if( $tables->length > 1 )
51 {
52 $before = $tables->map(sub
53 {
54 my $tbl = shift( @_ );
55 return( 'ALTER TABLE ' . $tbl->name . ' DISABLE TRIGGER ' . ( $opts->{all} ? 'ALL' : 'USER' ) . ';' );
56 })->join( ' ' );
57 $after = $tables->map(sub
58 {
59 my $tbl = shift( @_ );
60 return( 'ALTER TABLE ' . $tbl->name . ' ENABLE TRIGGER ' . ( $opts->{all} ? 'ALL' : 'USER' ) . ';' );
61 })->join( ' ' );
62 }
63 else
64 {
65 my $tbl = $q->table_object;
66 $before = 'ALTER TABLE ' . $tbl->name . ' DISABLE TRIGGER ' . ( $opts->{name} ? $opts->{name} : $opts->{all} ? 'ALL' : 'USER' ) . ';';
67 $after = 'ALTER TABLE ' . $tbl->name . ' ENABLE TRIGGER ' . ( $opts->{name} ? $opts->{name} : $opts->{all} ? 'ALL' : 'USER' ) . ';';
68 }
69 my $new = "${before} ${query}; ${after}";
70 $q->query( $new );
71 my $sth = $self->table_object->_cache_this( $q ) ||
72 return( $self->error( "Error while preparing new query temporarily disabling triggers:\n$new" ) );
73 if( !defined( wantarray() ) )
74 {
75 $sth->execute() ||
76 return( $self->error( "Error while executing new query temporarily disabling triggers:\n$new" ) );
77 }
78 return( $sth );
79 }
80  
81 # Customised for Postgres
82 sub distinct
83 {
84 my $self = shift( @_ );
85 my $what = @_ > 1 ? join( ', ', @_ ) : shift( @_ );
86 my $query = $self->{query} ||
87 return( $self->error( "No query to set as to be ignored." ) );
88  
89 my $type = uc( ( $query =~ /^\s*(\S+)\s+/ )[ 0 ] );
90 # ALTER for table alteration statements (DB::Object::Tables
91 my @allowed = qw( SELECT );
92 my $allowed = CORE::join( '|', @allowed );
93 if( !scalar( grep{ /^$type$/i } @allowed ) )
94 {
95 return( $self->error( "You may not flag statement of type \U$type\E to be distinct:\n$query" ) );
96 }
97 # Incompatible. Do not bother going further
98 return( $self ) if( $query =~ /^[[:blank:]]*(?:$allowed)[[:blank:]]+(?:DISTINCT|ALL)[[:blank:]]+/i );
99 my $clause = defined( $what ) ? "DISTINCT ON ($what)" : "DISTINCT";
100  
101 $query =~ s/^([[:blank:]]*)($allowed)([[blank:]]+)/$1$2 $clause /;
102 # my $sth = $self->prepare( $query ) ||
103 # $self->{ 'query' } = $query;
104 # saving parameters to bind later on must have been done previously
105 my $sth = $self->_cache_this( $query ) ||
106 return( $self->error( "Error while preparing new ignored query:\n$query" ) );
107 if( !defined( wantarray() ) )
108 {
109 $sth->execute ||
110 return( $self->error( "Error while executing new ignored query:\n$query" ) );
111 }
112 return( $sth );
113 }
114  
115 # Customised for Postgres
116 sub dump
117 {
118 my $self = shift( @_ );
119 my $args = $self->_get_args_as_hash( @_ );
120 my $vsep = ",";
121 my $hsep = "\n";
122 my $width = 35;
123 $self->_load_class( 'IO::File' ) || return( $self->pass_error );
124 my $fh = IO::File->new;
125 $fh->fdopen( fileno( STDOUT ), "w" );
126 $vsep = $args->{vsep} if( exists( $args->{vsep} ) );
127 $hsep = $args->{hsep} if( exists( $args->{hsep} ) );
128 $width = $args->{width} if( exists( $args->{width} ) );
129 my @fields = ();
130 # my $fields_ref = $self->{sth}->FETCH( 'NAME' );
131 my $fields_ref = $self->{sth}->{NAME};
132 if( defined( $fields_ref ) && ref( $fields_ref ) eq 'ARRAY' )
133 {
134 @fields = @$fields_ref;
135 }
136 else
137 {
138 return( $self->error( "No array reference of fields could be retrieved from statement '$self->{sth}'." ) );
139 }
140 return( $self->error( "No query to dump." ) ) if( !exists( $self->{sth} ) );
141 if( exists( $args->{file} ) )
142 {
143 $self->_load_class( 'DateTime::Lite' ) || return( $self->pass_error );
144 # new_file is inherited from Module::Generic and calls Module::Generic::File
145 my $file = $self->new_file( $args->{file} );
146 $fh = $file->open( '>', { binmode => 'utf8' }) ||
147 return( $self->error( "Unable to open file $file in write mode: ", $file->error ) );
148 # my @header = sort{ $fields->{ $a } <=> $fields->{ $b } } keys( %$fields );
149 my @header = sort{ $a <=> $b } @fields;
150 my $date = DateTime::Lite->now;
151 my $table = $self->{table};
152 $fh->printf( "# Generated on %s for table $table\n", $date->strftime( '%c' ) );
153 $fh->print( "# ", CORE::join( "\t", @header ), "\n" );
154 my @data = ();
155 while( @data = $self->fetchrow() )
156 {
157 $fh->print( CORE::join( "\t", @data ), "\n" );
158 }
159 $fh->close();
160 $self->finish();
161 return( $self );
162 }
163 elsif( exists( $args->{fh} ) )
164 {
165 if( !fileno( $args->{fh} ) )
166 {
167 return( $self->error( "The file descriptor provided does not seem to be valid (not open)" ) );
168 }
169 $fh = IO::File->new_from_fd( $args->{fh}, 'w' ) || return( $self->error( $! ) );
170 }
171 my $max = 0;
172 # foreach my $field ( keys( %$fields ) )
173 foreach my $field ( @fields )
174 {
175 $max = length( $field ) if( length( $field ) > $max );
176 }
177 my $template = '';
178 # foreach my $field ( sort{ $fields->{ $a } <=> $fields->{ $b } } keys( %$fields ) )
179 foreach my $field ( @fields )
180 {
181 $template .= "$field" . ( '.' x ( $max - length( $field ) ) ) . ": %s\n";
182 }
183 $template .= "\n";
184 my @data = ();
185 while( @data = $self->fetchrow )
186 {
187 $fh->printf( $template, @data );
188 }
189 $self->finish;
190 return( $self );
191 }
192  
193 # NOTE: sub execute is inherited from DB::Object::Statement
194 # sub execute
195  
196 # NOTE: sub executed is inherited from DB::Object::Statement
197 # sub executed
198  
199 # NOTE: sub fetchall_arrayref is inherited from DB::Object::Statement
200 # sub fetchall_arrayref($@)
201  
202 # NOTE: sub fetchcol is inherited from DB::Object::Statement
203 # sub fetchcol($;$)
204  
205 # NOTE: sub fetchhash is inherited from DB::Object::Statement
206 # sub fetchhash(@)
207  
208 # NOTE: sub fetchrow is inherited from DB::Object::Statement
209 # sub fetchrow(@)
210  
211 # NOTE: field_types -> TYPE
212 # <https://metacpan.org/pod/DBI#TYPE>
213 sub field_types { return( shift->_get_statement_attribute( 'pg_type' ) ); }
214  
215 # NOTE: sub finish is inherited from DB::Object::Statement
216 # sub finish
217  
218 sub ignore
219 {
220 return( shift->error( "INSERT | UPDATE | ALTER IGNORE is not supported by Postgres." ) );
221 }
222  
223 # NOTE: sub join is inherited from DB::Object::Statement
224 # sub join
225  
226 # NOTE: sub object is inherited from DB::Object::Statement
227 # sub object
228  
229 sub last_insert_id
230 {
231 my $self = shift( @_ );
232 my $dbo = $self->database_object ||
233 return( $self->error( "No database handle could be found." ) );
234 return( $dbo->last_insert_id( undef(), undef(), $self->table_object->name, undef() ) );
235 }
236  
237 sub name
238 {
239 my $self = shift( @_ );
240 if( @_ )
241 {
242 $self->{sth}->{pg_prepare_name} = shift( @_ );
243 }
244 return( $self->{sth}->{pg_prepare_name} );
245 }
246  
247 sub only
248 {
249 my $self = shift( @_ );
250 my $q = $self->query_object || return( $self->error( "No query formatter object was set" ) );
251 my $table = $self->{table} || $q->table_object->name ||
252 return( $self->error( "No table provided to perform select statement." ) );
253 my $tbl_o = $q->table_object || $self->{table_object} || return( $self->error( "No table object is set." ) );
254 my $db = $tbl_o->database();
255 my $multi_db = $tbl_o->param( 'multi_db' );
256 my $type = uc( ( $self->{query} =~ /^(SELECT|DELETE|INSERT|UPDATE)\b/i )[0] );
257 my @query = ();
258 if( $type eq 'SELECT' )
259 {
260 my $fields = $q->selected_fields;
261 @query = $multi_db ? ( "SELECT $fields FROM ONLY $db.$table" ) : ( "SELECT $fields FROM ONLY $table" );
262 }
263 elsif( $type eq 'DELETE' )
264 {
265 @query = $multi_db ? ( "DELETE FROM ONLY $db.$table" ) : ( "DELETE FROM ONLY $table" );
266 }
267 elsif( $type eq 'UPDATE' )
268 {
269 my $qv = $q->query_values ||
270 return( $self->error( "Something went wrong. No query values found. Please investigate." ) );
271 return( $self->error( "I was expecting a scalar reference for uery values, but got instead '$qv'." ) ) if( !$self->_is_scalar( $qv ) );
272 my $values = $$qv;
273 @query = ( "UPDATE ONLY $table SET $values" );
274 }
275 # Other type such as INSERT or TRUNCATE are not applicable, so we just ignore if we were ever called by mistake.
276 else
277 {
278 return( $self );
279 }
280 my $clauses = $q->_query_components;
281 push( @query, @$clauses ) if( @$clauses );
282 my $query = $q->{query} = CORE::join( ' ', @query );
283 my $sth = $tbl_o->_cache_this( $q );
284 if( !defined( $sth ) )
285 {
286 return( $self->error( "Error while preparing query to select on table '$self->{table}':\n$query", $self->errstr() ) );
287 }
288 if( !defined( wantarray() ) )
289 {
290 $sth->execute ||
291 return( $self->error( "Error while executing query to select:\n", $self->as_string(), $sth->errstr() ) );
292 }
293 return( $sth );
294 }
295  
296 sub priority
297 {
298 return( shift->error( "Priority is not supported in Postgres." ) );
299 }
300  
301 # rollback is called using the dbh handler and is located in DB::Object::Postgres
302  
303 # NOTE: sub rows is inherited from DB::Object::Statement
304 # sub rows(@)
305  
306 # NOTE: sub undo is inherited from DB::Object::Statement
307 # sub undo
308  
309 # Does nothing in Postgres. This is a Mysql feature
310 sub wait
311 {
312 return( shift( @_ ) );
313 }
314  
315 sub _convert_datetime2object
316 {
317 my $self = shift( @_ );
318 my $opts = $self->_get_args_as_hash( @_ );
319 my $sth = $opts->{statement} || return( $self->error( "No statement handler was provided to convert data from json to perl." ) );
320 # my $data = $opts->{data} || return( $self->error( "No data was provided to convert from json to perl." ) );
321 return( $opts->{data} ) if( !defined( $opts->{data} ) || !CORE::length( $opts->{data} ) );
322 return( $opts->{data} ) if( !$sth->rows );
323 my $data = $opts->{data};
324 # my $names = $sth->FETCH('NAME');
325 # my $types = $sth->FETCH('pg_type');
326 # Get the cached field names and types that we stored after executing the query, but before we finished reading the statement
327 my $names = $self->_cache_field_names;
328 my $types = $self->_cache_field_types;
329 my $mode = ref( $data );
330  
331 for( my $i = 0; $i < scalar( @$names ); $i++ )
332 {
333 if( $types->[$i] eq PG_DATE ||
334 $types->[$i] eq PG_TIMESTAMP ||
335 $types->[$i] eq 'date' ||
336 $types->[$i] eq 'timestamp' )
337 {
338 if( $mode eq 'ARRAY' )
339 {
340 for( my $j = 0; $j < scalar( @$data ); $j++ )
341 {
342 next if( !$data->[ $j ]->{ $names->[ $i ] } );
343 my $dt = $self->_convert_string2datetime( $data->[ $j ]->{ $names->[ $i ] } );
344 if( !defined( $dt ) )
345 {
346 warn( $self->error );
347 }
348 $data->[ $j ]->{ $names->[ $i ] } = $dt;
349 }
350 }
351 elsif( $mode eq 'HASH' )
352 {
353 next if( !$data->{ $names->[ $i ] } );
354 my $dt = $self->_convert_string2datetime( $data->{ $names->[ $i ] } );
355 if( !defined( $dt ) )
356 {
357 warn( $self->error );
358 }
359 $data->{ $names->[ $i ] } = $dt;
360 }
361 }
362 }
363 return( $data );
364 }
365  
366 sub _convert_json2hash
367 {
368 my $self = shift( @_ );
369 my $opts = $self->_get_args_as_hash( @_ );
370 # $self->debug( 3 );
371 # my( $pack, $file, $line ) = caller( 1 );
372 # my $sub = ( caller( 2 ) )[3];
373 # $data can be either hash pr array
374 my $sth = $opts->{statement} || return( $self->error( "No statement handler was provided to convert data from json to perl." ) );
375 # my $data = $opts->{data} || return( $self->error( "No data was provided to convert from json to perl." ) );
376 return( $opts->{data} ) if( !defined( $opts->{data} ) || !CORE::length( $opts->{data} ) );
377 my $data = $opts->{data};
378 # my $names = $sth->FETCH('NAME');
379 # my $types = $sth->FETCH('pg_type');
380 my $names = $self->_cache_field_names;
381 my $types = $self->_cache_field_types;
382 my $mode = ref( $data );
383 for( my $i = 0; $i < scalar( @$names ); $i++ )
384 {
385 if( $types->[$i] eq PG_JSON || $types->[$i] eq PG_JSONB || $types->[$i] eq 'json' || $types->[$i] eq 'jsonb' )
386 {
387 if( $self->_is_array( $data ) )
388 {
389 for( my $j = 0; $j < scalar( @$data ); $j++ )
390 {
391 next if( !$data->[ $j ]->{ $names->[ $i ] } );
392 my $ref = $self->_decode_json( $data->[ $j ]->{ $names->[ $i ] } );
393 $data->[ $j ]->{ $names->[ $i ] } = $ref if( $ref );
394 }
395 }
396 elsif( $self->_is_hash( $data => 'strict' ) )
397 {
398 my $ref = $self->_decode_json( $data->{ $names->[ $i ] } );
399 $data->{ $names->[ $i ] } = $ref if( $ref );
400 }
401 }
402 }
403 return( $data );
404 }
405  
406 # NOTE: DESTROY
407 DESTROY
408 {
409 # Do nothing but existing so it is handled by this package
410 # print( STDERR "DESTROY'ing statement $self ($self->{ 'query' })\n" );
411 };
412  
413 1;
414 # NOTE: POD
415 __END__
« lib/DB/Object/Fields/Overloaded.pm ↑ Summary lib/DB/Object/Exception.pm »