blib/lib/DB/Object/Postgres/Query.pm untested

filter: Statementstmt 0.0% 0 / 349 Branchbran 0.0% 0 / 202 Conditioncond 0.0% 0 / 133 MC/DCmcdc 0.0% 0 / 78 Subroutinesub 0.0% 0 / 25 Podpod 0.0% 0 / 17 totaltotal 0.0% 0 / 804 CC 102 SCAR 65.9
CC
102
Cov
0%
format_statement
1722.0
on_conflict
552.0
_query_components
380.0
CRAP 724.2
1 # -*- perl -*-
2 ##----------------------------------------------------------------------------
3 ## Database Object Interface - ~/lib/DB/Object/Postgres/Query.pm
4 ## Version v0.4.2
5 ## Copyright(c) 2026 DEGUEST Pte. Ltd.
6 ## Author: Jacques Deguest <jack@deguest.jp>
7 ## Created 2017/07/19
8 ## Modified 2026/08/05
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 package DB::Object::Postgres::Query;
15 BEGIN
16 {
17 use strict;
18 use warnings;
19 warnings::register_categories( 'DB::Object' );
20 use parent qw( DB::Object::Query );
21 use vars qw( $VERSION $DEBUG $EXCEPTION_CLASS );
22 use Wanted;
23 our $EXCEPTION_CLASS = $DB::Object::EXCEPTION_CLASS;
24 our $VERSION = 'v0.4.2';
25 };
26  
27 use strict;
28 use warnings;
29  
30 sub init
31 {
32 my $self = shift( @_ );
33 $self->{having} = '';
34 $self->{_init_strict_use_sub} = 1;
35 $self->{_exception_class} = $EXCEPTION_CLASS;
36 $self->SUPER::init( @_ ) || return( $self->pass_error );
37 $self->{binded_having} = [];
38 $self->{query_reset_keys} = [qw( alias binded binded_values binded_where binded_limit binded_group binded_having binded_order from_unixtime group_by limit local _on_conflict on_conflict order_by reverse sorted unix_timestamp where )];
39 return( $self );
40 }
41  
42 sub binded_having { return( shift->_set_get_array_as_object( 'binded_having', @_ ) ); }
43  
44 # sub binded_types_as_param
45 # {
46 # my $self = shift( @_ );
47 # my $types = $self->binded_types;
48 # my $params = $self->new_array;
49 # foreach my $t ( @$types )
50 # {
51 # if( CORE::length( $t ) )
52 # {
53 # $params->push( { pg_type => $t } );
54 # }
55 # else
56 # {
57 # $params->push( '' );
58 # }
59 # }
60 # return( $params );
61 # }
62  
63 sub binded_types_as_param
64 {
65 my $self = shift( @_ );
66 my $params = $self->new_array;
67 $self->elements->foreach(sub
68 {
69 my $elem = shift( @_ );
70 my $type;
71 if( $elem && $elem->as_is )
72 {
73 return;
74 }
75 elsif( $elem && defined( $type = $elem->type ) )
76 {
77 $params->push( { pg_type => $type } );
78 }
79 else
80 {
81 $params->push( '' );
82 }
83 });
84 return( $params );
85 }
86  
87 sub dollar_placeholder
88 {
89 my $self = shift( @_ );
90 if( @_ )
91 {
92 $self->prepare_options->set( 'pg_placeholder_dollaronly' => shift( @_ ) );
93 }
94 return( $self->prepare_options->get( 'pg_placeholder_dollaronly' ) );
95 }
96  
97 sub format_from_epoch
98 {
99 my $self = shift( @_ );
100 my $opts = $self->_get_args_as_hash( @_ );
101 if( $opts->{bind} )
102 {
103 return( "TO_TIMESTAMP(?)" );
104 }
105 else
106 {
107 return( sprintf( "TO_TIMESTAMP(%s)", $opts->{value} ) );
108 }
109 }
110  
111 sub format_to_epoch
112 {
113 my $self = shift( @_ );
114 my $opts = $self->_get_args_as_hash( @_ );
115 if( $opts->{bind} )
116 {
117 # 2020-10-11: ABSTIME is deprecated in PostgreSQL 12
118 # https://www.postgresql.org/docs/12/release-12.html
119 # return( "'?'::ABSTIME::INTEGER" );
120 # We use instead the more standard way which works back from PostgreSQL 7.1
121 return( "EXTRACT( EPOCH FROM '?'::TIMESTAMP )::INTEGER" );
122 }
123 else
124 {
125 # return( sprintf( "%s::ABSTIME::INTEGER", $opts->{quote} ? "'" . $opts->{value} . "'" : $opts->{value} ) );
126 return( sprintf( "EXTRACT( EPOCH FROM %s::TIMESTAMP )::INTEGER", $opts->{quote} ? "'" . $opts->{value} . "'" : $opts->{value} ) );
127 }
128 }
129  
130 # NOTE: For select or insert queries
131 sub format_statement
132 {
133 my $self = shift( @_ );
134 my $opts = $self->_get_args_as_hash( @_ );
135 my $dbo = $self->database_object ||
136 return( $self->error( "No database handle could be found." ) );
137 my $tbl_o = $self->table_object || return( $self->error( "No table object is set." ) );
138 # Should we use bind statement?
139 my $bind = $tbl_o->use_bind;
140 $opts->{data} = $self->{_default} if( !$opts->{data} );
141 $opts->{order} = $self->{_fields} if( !$opts->{order} );
142 $opts->{table} = $tbl_o->name if( !$opts->{table} );
143 local $_;
144 my $data = $opts->{data};
145 my $order = $opts->{order};
146 my $table = $opts->{table};
147 my $from_unix = {};
148 my $unixtime = {};
149 my $args = $self->{_args};
150 my $fields = '';
151 my $values = '';
152 my $base_class = $self->base_class;
153 $from_unix = $self->{_from_unix};
154 if( !%$from_unix )
155 {
156 my $times = $self->from_unixtime();
157 map{ $from_unix->{ $_ }++ } @$times;
158 }
159  
160 if( $self->_is_array( $unixtime ) )
161 {
162 my %hash = map{ $_ => 1 } @$unixtime;
163 $unixtime = \%hash;
164 }
165 my @format_fields = ();
166 my @format_values = ();
167 my $binded = $self->{binded_values} = [];
168 my $multi_db = $tbl_o->prefix_database;
169 my $prefix = $tbl_o->prefix;
170 my $db = $tbl_o->database;
171 my $field_prefix = $tbl_o->query_object->table_alias ? $tbl_o->query_object->table_alias : $prefix;
172 my $fields_ref = $tbl_o->fields;
173 my $ok_list = CORE::join( '|', keys( %$fields_ref ) );
174 my $tables = CORE::join( '|', @{$tbl_o->database_object->tables} );
175 my $struct = $tbl_o->structure || return( $self->pass_error( $tbl_o->error ) );
176 my $types = $tbl_o->types;
177 my $types_const = $tbl_o->types_const;
178 my $query_type = $self->{query_type};
179 my @sorted = ();
180 my @types = ();
181 if( $self->query_type eq 'insert' &&
182 @$args &&
183 !( @$args % 2 ) )
184 {
185 for( my $i = 0; $i < @$args; $i += 2 )
186 {
187 push( @sorted, $args->[ $i ] ) if( exists( $order->{ $args->[ $i ] } ) );
188 $data->{ $args->[ $i ] } = $args->[ $i + 1 ] if( !exists( $data->{ $args->[ $i ] } ) );
189 }
190 }
191 @sorted = sort{ $order->{ $a }->pos <=> $order->{ $b }->pos } keys( %$order ) if( !@sorted );
192 # Used for insert or update so that execute can take a hash of key => value pair and we would bind the values in the right order
193 # But or that we need to know the order of the fields.
194 $self->{sorted} = \@sorted;
195 my $placeholder_re = $tbl_o->database_object->_placeholder_regexp;
196 my $elems = $self->new_elements( debug => $self->debug );
197  
198 foreach my $field ( @sorted )
199 {
200 next if( defined( $struct->{ $field } ) && $struct->{ $field } =~ /\bSERIAL\b/i );
201 my $elem = $self->new_element;
202 if( exists( $data->{ $field } ) )
203 {
204 my $value = $data->{ $field };
205 if( $self->_is_a( $value, "${base_class}::Statement" ) )
206 {
207 # $elem->value( '(' . $value->as_string . ')' );
208 $elem->format( '(' . $value->as_string . ')' );
209 # push( @format_values, '(' . $value->as_string . ')' );
210 # push( @$binded, $value->query_object->binded_values->list ) if( $value->query_object->binded_values->length );
211 # # $self->binded_types->push( $value->query_object->binded_types_as_param );
212 # push( @types, $value->query_object->binded_types->list ) if( $value->query_object->binded_types->length );
213 $elem->elements( $value->query_object->elements );
214 }
215 # This is for insert or update statement types
216 elsif( exists( $from_unix->{ $field } ) )
217 {
218 # push( @format_values, sprintf( "FROM_UNIXTIME('%s') AS $field", $data->{ $field } ) );
219 if( $bind )
220 {
221 # push( @$binded, $value );
222 # push( @format_values, $self->format_from_epoch({ value => $value, bind => 1 }) );
223 if( CORE::exists( $types_const->{ $field } ) )
224 {
225 # CORE::push( @types, $types_const->{ $field }->{constant} );
226 # PG_INT4
227 # CORE::push( @types, $dbo->get_sql_type( 'int4' ) );
228 $elem->type( $dbo->get_sql_type( 'int4' ) );
229 }
230 # else
231 # {
232 # CORE::push( @types, '' );
233 # }
234 if( $value =~ /($placeholder_re)/ )
235 {
236 $elem->placeholder( $1 );
237 if( defined( $+{index} ) )
238 {
239 $elem->index( $+{index} );
240 }
241 $elem->format( $value );
242 }
243 else
244 {
245 $elem->value( $value );
246 }
247 $elem->format( $self->format_from_epoch({ value => $value, bind => 1 }) );
248 }
249 else
250 {
251 if( $value =~ /^$placeholder_re$/ )
252 {
253 $elem->format( $self->format_from_epoch({ value => $value, bind => 1 }) );
254 if( CORE::exists( $types_const->{ $field } ) )
255 {
256 # CORE::push( @types, $types_const->{ $field }->{constant} );
257 # PG_INT4
258 # CORE::push( @types, $dbo->get_sql_type( 'int4' ) );
259 $elem->type( $dbo->get_sql_type( 'int4' ) );
260 }
261 # else
262 # {
263 # CORE::push( @types, '' );
264 # }
265 }
266 else
267 {
268 $elem->format( $self->format_from_epoch({ value => $value, bind => 0 }) );
269 }
270 }
271 }
272 elsif( ref( $value ) eq 'SCALAR' )
273 {
274 push( @format_values, $$value );
275 }
276 elsif( $value =~ /($placeholder_re)/ )
277 {
278 $elem->placeholder( $1 );
279 $elem->format( $value );
280 if( defined( $+{index} ) )
281 {
282 $elem->index( $+{index} );
283 }
284 # push( @format_values, $1 );
285 # CORE::push( @types, $types_const->{ $field } ? $types_const->{ $field }->{constant} : '' );
286 if( CORE::exists( $types_const->{ $field } ) )
287 {
288 # CORE::push( @types, $types_const->{ $field }->{constant} );
289 $elem->type( $types_const->{ $field }->{constant} );
290 }
291 # else
292 # {
293 # CORE::push( @types, '' );
294 # }
295 }
296 elsif( $struct->{ $field } =~ /^\s*\bBLOB\b/i )
297 {
298 # push( @format_values, '?' );
299 # push( @$binded, $value );
300 $elem->placeholder( '?' );
301 $elem->format( '?' );
302 $elem->value( $value );
303 my $const;
304 if( lc( $types->{ $field } ) eq 'bytea' && ( $const = $dbo->get_sql_type( 'bytea' ) ) )
305 {
306 # CORE::push( @types, DBD::Pg::PG_BYTEA );
307 # CORE::push( @types, $const );
308 $elem->type( $const );
309 }
310 # else
311 # {
312 # CORE::push( @types, '' );
313 # }
314 }
315 # If the value itself looks like a field name or like a SQL function
316 # or simply if bind option is inactive
317 # This stinks too much. It is way too complex to parse or guess a sql query
318 # use a scalar reference instead to pass value as is
319 # elsif( $value =~ /(?:\.|\A)(?:$ok_list)\b/ ||
320 # $value =~ /[a-zA-Z_]{3,}\([^\)]*\)/ ||
321 # $value eq '?' )
322 # {
323 # push( @format_values, $value );
324 # }
325 elsif( !$bind )
326 {
327 my $const;
328 $elem->value( $value );
329 if( lc( $types->{ $field } ) eq 'bytea' && ( $const = $dbo->get_sql_type( 'bytea' ) ) )
330 {
331 # push( @format_values, $tbl_o->database_object->quote( $value, DBD::Pg::PG_BYTEA ) );
332 # push( @format_values, $tbl_o->database_object->quote( $value, { pg_type => $const } ) );
333 $elem->format( $tbl_o->database_object->quote( $value, { pg_type => $const } ) );
334 }
335 # Value is a hash and the data type is json, so we transform this value into a json data
336 elsif( $self->_is_hash( $value => 'strict' ) && ( lc( $types->{ $field } ) eq 'jsonb' || lc( $types->{ $field } ) eq 'json' ) )
337 {
338 my $this_json = $self->_encode_json( $value );
339 # push( @format_values, $tbl_o->database_object->quote( $this_json, ( lc( $types->{ $field } ) eq 'jsonb' ? DBD::Pg::PG_JSONB : DBD::Pg::PG_JSON ) ) );
340 # push( @format_values, $tbl_o->database_object->quote( $this_json, { pg_type => $dbo->get_sql_type( $types->{ $field } ) } ) );
341 $elem->format( $tbl_o->database_object->quote( $this_json, { pg_type => $dbo->get_sql_type( $types->{ $field } ) } ) );
342 }
343 else
344 {
345 # push( @format_values, sprintf( "'%s'", quotemeta( $value ) ) );
346 # push( @format_values, sprintf( "%s", $tbl_o->database_object->quote( $value ) ) );
347 $elem->format( sprintf( "%s", $tbl_o->database_object->quote( $value ) ) );
348 }
349 }
350 # We do this before testing for param binding because DBI puts quotes around SET number :-(
351 elsif( $value =~ /^\d+$/ && $struct->{ $field } =~ /\bSET\(/i )
352 {
353 # push( @format_values, $value );
354 $elem->format( $value );
355 }
356 elsif( $value =~ /^\d+$/ &&
357 $struct->{ $field } =~ /\bENUM\(/i &&
358 ( $query_type eq 'insert' || $query_type eq 'update' ) )
359 {
360 # push( @format_values, "'$value'" );
361 $elem->format( "'$value'" );
362 }
363 # Otherwise, bind option is enabled, we bind parameter
364 elsif( $bind )
365 {
366 # push( @format_values, '?' );
367 # push( @$binded, $value );
368 $elem->placeholder( '?' );
369 $elem->format( '?' );
370 $elem->value( $value );
371 my $const;
372 if( lc( $types->{ $field } ) eq 'bytea' && ( $const = $dbo->get_sql_type( 'bytea' ) ) )
373 {
374 # CORE::push( @types, $const );
375 $elem->type( $const );
376 }
377 # else
378 # {
379 # CORE::push( @types, '' );
380 # }
381 }
382 # In last resort, we handle the formatting ourself
383 else
384 {
385 # push( @format_values, "'" . quotemeta( $value ) . "'" );
386 my $const;
387 if( lc( $types->{ $field } ) eq 'bytea' && ( $const = $dbo->get_sql_type( 'bytea' ) ) )
388 {
389 # push( @format_values, $tbl_o->database_object->quote( $value, DBD::Pg::PG_BYTEA ) );
390 # push( @format_values, $tbl_o->database_object->quote( $value, { pg_type => $const } ) );
391 $elem->format( $tbl_o->database_object->quote( $value, { pg_type => $const } ) );
392 }
393 else
394 {
395 # push( @format_values, $tbl_o->database_object->quote( $value ) );
396 $elem->format( $tbl_o->database_object->quote( $value ) );
397 }
398 }
399 }
400  
401 if( $field_prefix )
402 {
403 $field =~ s{
404 (?<![\.\"])\b($ok_list)\b(\s*)?(?!\.)
405 }
406 {
407 my( $field, $spc ) = ( $1, $2 );
408 if( $` =~ /\s+(?:AS|FROM)\s+$/i )
409 {
410 "${field}${spc}";
411 }
412 elsif( $query_type eq 'select' && $prefix )
413 {
414 "${field_prefix}.${field}${spc}";
415 }
416 else
417 {
418 "${field}${spc}";
419 }
420 }gex;
421 $field =~ s/(?<!\.)($tables)(?:\.)/$db\.$1\./g if( $multi_db );
422 # push( @format_fields, $field );
423 }
424 # else
425 # {
426 # push( @format_fields, $field );
427 # }
428 # If the field provided is just a string, we trade it for a table field object, because it will provide us with more information to format the query in DB::Object::Statement, such as to turn a hash reference into JSON, or a DateTime::Lite object into a suitable format
429 $elem->field( $self->_is_a( $field => 'DB::Object::Fields::Field' )
430 ? $field
431 : exists( $fields_ref->{ $field } )
432 ? $tbl_o->fields( $field )
433 : $field
434 );
435 $elems->push( $elem );
436 }
437 # TODO: Remove the following line as it is obsolete as of 2023-07-23
438 $self->binded_types->push( @types ) if( scalar( @types ) );
439 if( !wantarray() && scalar( @{$self->{_extra}} ) )
440 {
441 foreach my $this ( @{$self->{_extra}} )
442 {
443 $elems->push({
444 field => $this,
445 debug => $self->debug,
446 });
447 }
448 }
449 return( $elems );
450 }
451  
452 # _having is in DB::Object::Query
453 # sub having { return( shift->_having( @_ ) ); }
454 sub having { return( shift->_where_having( 'having', 'having', @_ ) ); }
455  
456 # <http://www.postgresql.org/docs/9.3/interactive/queries-limit.html>
457 sub limit
458 {
459 my $self = shift( @_ );
460 my $limit = $self->{limit};
461 if( @_ )
462 {
463 # Returns a DB::Object::Query::Clause
464 $limit = $self->_process_limit( @_ ) ||
465 return( $self->pass_error );
466 if( CORE::length( $limit->metadata->limit // '' ) )
467 {
468 $limit->generic( CORE::length( $limit->metadata->offset // '' ) ? 'OFFSET ? LIMIT ?' : 'LIMIT ?' );
469 # %s works for integer, and also for numbered placeholders like $1 or ?1, or regular placeholder like ?
470 $limit->value(
471 CORE::length( $limit->metadata->offset // '' )
472 ? sprintf( "OFFSET %s LIMIT %s", $limit->metadata->offset, $limit->metadata->limit )
473 : sprintf( "LIMIT %s", $limit->metadata->limit )
474 );
475 }
476 }
477  
478 if( !$limit && want( 'OBJECT' ) )
479 {
480 return( $self->new_null( type => 'object' ) );
481 }
482 return( $limit );
483 }
484  
485 # FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE
486 # This feature is available since PostgreSQL version 9.3
487 # <https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE>
488 sub lock
489 {
490 my $self = shift( @_ );
491 my $dbo = $self->database_object ||
492 return( $self->error( "No database handle could be found." ) );
493 my $lock = $self->{lock};
494 if( @_ )
495 {
496 require version;
497 if( $dbo->version < version->parse( '9.3' ) )
498 {
499 warn( "This feature is only available in PostgreSQL version 9.3 and above." ) if( $self->_warnings_enabled( 'DB::Object' ) );
500 }
501 # Supported, case insensitive:
502 # For update
503 # For no key update
504 # For share
505 # For key share
506 # update
507 # no key update
508 # share
509 # key share
510 my $str = shift( @_ );
511 if( $str !~ /^(?:For[[:blank:]\h]+)?(update|no[[:blank:]\h]+key[[:blank:]\h]+update|share|key[[:blank:]\h]+share)/i )
512 {
513 return( $self->error( "Unsupported argument for locking. See https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE" ) );
514 }
515 unless( $str =~ /^For[[:blank:]\h]+/i )
516 {
517 $str = 'FOR ' . $str;
518 }
519 $lock = $self->{lock} = uc( $str );
520 }
521 return( $lock );
522 }
523  
524 # https://www.postgresql.org/docs/10/sql-insert.html
525 # $q->on_conflict({
526 # target => 'id',
527 # action => 'nothing',
528 # action => 'update',
529 # fields => { a => 'some value', b => 'some other' },
530 # });
531 sub on_conflict
532 {
533 my $self = shift( @_ );
534 my $opts = {};
535 $self->{_on_conflict} = {} if( ref( $self->{_on_conflict} ) ne 'HASH' );
536 if( @_ )
537 {
538 my $tbl_o = $self->{table_object} || return( $self->error( "No table object is set." ) );
539 my $ver = $tbl_o->database_object->version;
540 if( version->parse( $ver ) < version->parse( '9.4' ) )
541 {
542 return( $self->error( "PostgreSQL version is $ver, but version 9.5 or higher is required to use this on conflict clause." ) );
543 }
544 $opts = $self->_get_args_as_hash( @_ );
545 my $hash = {};
546 my @comp = ( 'ON CONFLICT' );
547 if( $opts->{target} )
548 {
549 $hash->{target} = $opts->{target};
550 # Example: ON CONFLICT ON CONSTRAINT customers_name_key DO NOTHING;
551 if( $hash->{target} =~ /^(on[[:blank:]]+constraint)(.*?)$/i )
552 {
553 $hash->{target} = "\U$1\E$2";
554 push( @comp, $hash->{target} );
555 }
556 # a reference to a scalar was provided, so we set the value as is
557 elsif( ref( $hash->{target} ) eq 'SCALAR' )
558 {
559 push( @comp, $$hash->{target} );
560 }
561 elsif( $self->_is_array( $hash->{target} ) )
562 {
563 push( @comp, sprintf( '(%s)', join( ',', @{$hash->{target}} ) ) );
564 }
565 else
566 {
567 push( @comp, sprintf( '(%s)', $hash->{target} ) );
568 }
569 }
570 # https://www.postgresql.org/docs/10/sql-insert.html#SQL-ON-CONFLICT
571 elsif( $opts->{action} ne 'nothing' )
572 {
573 return( $self->error( "No target was specified for the on conflict clause." ) );
574 }
575  
576 if( $opts->{where} )
577 {
578 $hash->{where} = $opts->{where};
579 push( @comp, 'WHERE ' . $opts->{where} );
580 }
581  
582 # action => update
583 if( $opts->{action} )
584 {
585 if( $opts->{action} eq 'update' )
586 {
587 $hash->{action} = $opts->{action};
588 # return( $self->error( "No fields to update was provided for on conflict do update" ) ) if( !$opts->{fields} );
589 # No fields provided, so we take it from the initial insert and build the update list instead
590 if( !$opts->{fields} )
591 {
592 $self->{_on_conflict_callback} = sub
593 {
594 my $f_ref = $self->{_args};
595 # Need to account for placeholders
596 # Let's check values only
597 $self->is_upsert(1);
598 my $elems = $self->format_update( $f_ref );
599 my $inherited_fields = $elems->formats->join( ', ' );
600 push( @comp, 'DO UPDATE SET' );
601 push( @comp, $inherited_fields );
602 $hash->{query} = join( ' ', @comp );
603 $self->{_on_conflict} = $hash;
604 $self->{on_conflict} = join( ' ', @comp );
605 $self->elements->push( $elems->elements->list );
606 # Usable only once
607 CORE::delete( $self->{_on_conflict_callback} );
608 };
609 # Return empty, not undef; undef is error
610 return( '' );
611 }
612 return( $self->error( "Fields property to update for on conflict do update clause is not a hash reference nor an array of fields." ) ) if( !$self->_is_hash( $opts->{fields} => 'strict' ) && !$self->_is_array( $opts->{fields} ) && !$self->{_on_conflict_callback} );
613 if( $self->_is_hash( $opts->{fields} => 'strict' ) )
614 {
615 return( $self->error( "Fields property to update for on conflict do update clause contains no fields!" ) ) if( !scalar( keys( %{$opts->{fields}} ) ) );
616 }
617 elsif( $self->_is_array( $opts->{fields} ) )
618 {
619 return( $self->error( "Fields property to update for on conflict do update clause contains no fields!" ) ) if( !scalar( @{$opts->{fields}} ) );
620 }
621  
622 if( $self->_is_array( $opts->{fields} ) )
623 {
624 my $this = $opts->{fields};
625 my $new = {};
626 foreach my $f ( @$this )
627 {
628 $new->{ $f } = \( 'EXCLUDED.' . $f );
629 }
630 $opts->{fields} = $new;
631 }
632 # Here the user will use the special table 'excluded'
633 $hash->{fields} = $opts->{fields};
634  
635 my $q = [];
636  
637 foreach my $k ( sort( keys( %{$opts->{fields}} ) ) )
638 {
639 push( @$q, sprintf( '%s = %s', $k, ref( $opts->{fields}->{ $k } ) eq 'SCALAR' ? ${$opts->{fields}->{ $k }} : $tbl_o->database_object->quote( $opts->{fields}->{ $k } ) ) );
640 }
641 if( scalar( @$q ) )
642 {
643 push( @comp, 'DO UPDATE SET' );
644 push( @comp, join( ", ", @$q ) );
645 }
646 else
647 {
648 return( $self->error( "An on conflict do update clause was specified, but I could not get a list of fields to update." ) );
649 }
650 }
651 elsif( $opts->{action} eq 'nothing' || $opts->{action} eq 'ignore' )
652 {
653 $hash->{action} = $opts->{action};
654 push( @comp, 'DO NOTHING' );
655 }
656 else
657 {
658 return( $self->error( "Unknown action '$opts->{action}' for on conflict clause." ) );
659 }
660 }
661 else
662 {
663 return( $self->error( "No action was specified for the on conflict clause." ) );
664 }
665 $hash->{query} = join( ' ', @comp );
666 $self->{_on_conflict} = $hash;
667 $self->{on_conflict} = $self->new_clause({ value => join( ' ', @comp ) });
668 }
669 # We are being called possibly by _query_components
670 # If we have a callback, we execute it
671 if( $self->{_on_conflict_callback} && !scalar( @_ ) )
672 {
673 # This will use the insert components set up to format our on conflict clause properly
674 # The callback is needed, because the query formatting occurs after the calling of our method on_conflict()
675 $self->{_on_conflict_callback}->();
676 }
677 return( $self->{on_conflict} );
678 }
679  
680 sub reset
681 {
682 my $self = shift( @_ );
683 if( !$self->{query_reset} )
684 {
685 my $keys = [qw( alias binded binded_values binded_where binded_limit binded_group binded_having binded_order from_unixtime group_by limit local _on_conflict on_conflict order_by reverse sorted unix_timestamp where )];
686 CORE::delete( @$self{ @$keys } );
687 $self->{query_reset}++;
688 # $self->{enhance} = 1;
689 }
690 return( $self );
691 }
692  
693 sub reset_bind
694 {
695 my $self = shift( @_ );
696 my @f = qw( binded binded_where binded_group binded_having binded_order binded_limit );
697 foreach my $field ( @f )
698 {
699 $self->{ $field } = [];
700 }
701 return( $self );
702 }
703  
704 sub returning
705 {
706 my $self = shift( @_ );
707 my $dbo = $self->database_object ||
708 return( $self->error( "No database handle could be found." ) );
709 my $tbl_o = $self->{table_object} || return( $self->error( "No table object is set." ) );
710 if( @_ )
711 {
712 my $pg_version = $dbo->version;
713 return( $self->error( "Cannot use returning for PostgreSQL version lower than 8.2. This server version is: $pg_version" ) ) if( $pg_version < '8.2' );
714 # It could be a field name or a wildcard
715 return( $self->error( "A reference was provided (", ref( $_[0] ), "), but I was expecting a string, which could be a field name or even a star (*) indicating all fields." ) ) if( ref( $_[0] ) );
716 $self->{returning} = $self->new_clause( value => shift( @_ ) );
717 }
718 return( $self->{returning} );
719 }
720  
721 sub server_prepare
722 {
723 my $self = shift( @_ );
724 if( @_ )
725 {
726 $self->prepare_options->set( 'pg_server_prepare' => shift( @_ ) );
727 }
728 return( $self->prepare_options->get( 'pg_server_prepare' ) );
729 }
730  
731 sub _query_components
732 {
733 my $self = shift( @_ );
734 my $type = ( @_ > 0 && lc( shift( @_ ) ) ) || $self->_query_type() || return( $self->error( "You must specify a query type: select, insert, update or delete" ) );
735 my $opts = $self->_get_args_as_hash( @_ );
736 # ok options:
737 # no_bind_copy: because join for example does it already and this would duplicate the binded types, so we use this option to tell this method to set an exception. Kind of a hack that needs clean-up in the future from a design point of view.
738 $opts->{no_bind_copy} //= 0;
739 my( $where, $group, $having, $sort, $order, $limit, $returning, $on_conflict, $lock );
740  
741 $where = $self->where();
742 if( $type eq 'select' )
743 {
744 $group = $self->group;
745 $having = $self->having;
746 $sort = $self->reverse ? 'DESC' : $self->sort ? 'ASC' : '';
747 $order = $self->order;
748 # <https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE>
749 $lock = $self->lock;
750 }
751 $limit = $self->limit;
752 $returning = $self->returning;
753 $on_conflict = $self->on_conflict;
754 my @query = ();
755 push( @query, "WHERE $where" ) if( $where && $type ne 'insert' );
756 if( $where && $where->elements->length )
757 {
758 # $self->binded_types->push( $where->bind->types->list ) unless( $opts->{no_bind_copy} );
759 $self->elements->push( $where ) unless( $opts->{no_bind_copy} );
760 }
761 push( @query, "GROUP BY $group" ) if( $group && $type eq 'select' );
762 push( @query, "HAVING $having" ) if( $having && $type eq 'select' );
763 push( @query, "ORDER BY $order" ) if( $order && $type eq 'select' );
764 push( @query, $sort ) if( $sort && $order && $type eq 'select' );
765 if( $limit && $type eq 'select' )
766 {
767 push( @query, "$limit" );
768 # if( $limit->bind->types->length )
769 if( $limit->elements->length )
770 {
771 # $self->binded_types->push( $limit->bind->types->list ) unless( $opts->{no_bind_copy} );
772 $self->elements->push( $limit ) unless( $opts->{no_bind_copy} );
773 }
774 }
775 if( $on_conflict )
776 {
777 if( $type eq 'insert' )
778 {
779 push( @query, $on_conflict );
780 }
781 else
782 {
783 warn( "Warning only: the PostgreSQL ON CONFLICT clause is only supported for INSERT queries. Your query was of type \"$type\"." ) if( $self->_is_warnings_enabled( 'DB::Object' ) );
784 }
785 }
786  
787 if( $returning && ( $type eq 'insert' || $type eq 'update' || $type eq 'delete' ) )
788 {
789 push( @query, "RETURNING $returning" );
790 }
791 elsif( $lock && $type eq 'select' )
792 {
793 push( @query, $lock );
794 }
795 return( \@query );
796 }
797  
798 1;
799 # NOTE: POD
800 __END__