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