| 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__ | ||