| 1 | # -*- perl -*- | ||
| 2 | ##---------------------------------------------------------------------------- | ||
| 3 | ## Database Object Interface - ~/lib/DB/Object/Mysql/Query.pm | ||
| 4 | ## Version v0.5.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::Mysql::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 $DEBUG = 0; | ||
| 24 | our $EXCEPTION_CLASS = $DB::Object::EXCEPTION_CLASS; | ||
| 25 | our $VERSION = 'v0.5.2'; | ||
| 26 | }; | ||
| 27 | |||
| 28 | use strict; | ||
| 29 | use warnings; | ||
| 30 | |||
| 31 | sub init | ||
| 32 | { | ||
| 33 | my $self = shift( @_ ); | ||
| 34 | $self->{having} = ''; | ||
| 35 | $self->{_init_strict_use_sub} = 1; | ||
| 36 | $self->{_exception_class} = $EXCEPTION_CLASS; | ||
| 37 | $self->SUPER::init( @_ ) || return( $self->pass_error ); | ||
| 38 | $self->{binded_having} = []; | ||
| 39 | $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 )]; | ||
| 40 | return( $self ); | ||
| 41 | } | ||
| 42 | |||
| 43 | sub binded_having { return( shift->_set_get( 'binded_having', @_ ) ); } | ||
| 44 | |||
| 45 | sub format_from_epoch | ||
| 46 | { | ||
| 47 | my $self = shift( @_ ); | ||
| 48 | my $opts = {}; | ||
| 49 | $opts = shift( @_ ) if( scalar( @_ ) == 1 && $self->_is_hash( $_[0] => 'strict' ) ); | ||
| 50 | if( $opts->{bind} ) | ||
| 51 | { | ||
| 52 | return( "FROM_UNIXTIME(?)" ); | ||
| 53 | } | ||
| 54 | else | ||
| 55 | { | ||
| 56 | return( sprintf( 'FROM_UNIXTIME(%s)', $opts->{value} ) ); | ||
| 57 | } | ||
| 58 | } | ||
| 59 | |||
| 60 | sub format_to_epoch | ||
| 61 | { | ||
| 62 | my $self = shift( @_ ); | ||
| 63 | my $opts = {}; | ||
| 64 | $opts = shift( @_ ) if( scalar( @_ ) == 1 && $self->_is_hash( $_[0] => 'strict' ) ); | ||
| 65 | if( $opts->{bind} ) | ||
| 66 | { | ||
| 67 | return( 'UNIX_TIMESTAMP(?)' ); | ||
| 68 | } | ||
| 69 | else | ||
| 70 | { | ||
| 71 | return( sprintf( "UNIX_TIMESTAMP(%s)", $opts->{quote} ? "'" . $opts->{value} . "'" : $opts->{value} ) ); | ||
| 72 | } | ||
| 73 | } | ||
| 74 | |||
| 75 | # _having is in DB::Object::Query | ||
| 76 | sub having { return( shift->_where_having( 'having', 'having', @_ ) ); } | ||
| 77 | |||
| 78 | # <https://dev.mysql.com/doc/refman/8.0/en/select.html> | ||
| 79 | sub limit | ||
| 80 | { | ||
| 81 | my $self = shift( @_ ); | ||
| 82 | my $limit = $self->{limit}; | ||
| 83 | if( @_ ) | ||
| 84 | { | ||
| 85 | # Returns a DB::Object::Query::Clause | ||
| 86 | $limit = $self->_process_limit( @_ ) || | ||
| 87 | return( $self->pass_error ); | ||
| 88 | |||
| 89 | if( CORE::length( $limit->metadata->limit // '' ) ) | ||
| 90 | { | ||
| 91 | $limit->generic( CORE::length( $limit->metadata->offset // '' ) ? 'LIMIT ?, ?' : 'LIMIT ?' ); | ||
| 92 | # %s works for integer, and also for numbered placeholders like $1 or ?1, or regular placeholder like ? | ||
| 93 | $limit->value( | ||
| 94 | CORE::length( $limit->metadata->offset // '' ) | ||
| 95 | ? CORE::sprintf( 'LIMIT %s, %s', $limit->metadata->offset, $limit->metadata->limit ) | ||
| 96 | : CORE::sprintf( 'LIMIT %s', $limit->metadata->limit ) | ||
| 97 | ); | ||
| 98 | } | ||
| 99 | } | ||
| 100 | |||
| 101 | if( !$limit && want( 'OBJECT' ) ) | ||
| 102 | { | ||
| 103 | return( $self->new_null( type => 'object' ) ); | ||
| 104 | } | ||
| 105 | return( $limit ); | ||
| 106 | } | ||
| 107 | |||
| 108 | # 4.1.0 | ||
| 109 | # $q->on_conflict({ | ||
| 110 | # target => 'id', # Optional: implies a specific key, but MySQL uses any duplicate key | ||
| 111 | # action => 'nothing', # No-op (ignore conflict) | ||
| 112 | # action => 'update', # Perform update on duplicate key | ||
| 113 | # fields => { a => 'some value', b => 'some other' }, # Fields to update | ||
| 114 | # }); | ||
| 115 | sub on_conflict | ||
| 116 | { | ||
| 117 | my $self = shift( @_ ); | ||
| 118 | my $opts = {}; | ||
| 119 | $self->{_on_conflict} = {} if( ref( $self->{_on_conflict} ) ne 'HASH' ); | ||
| 120 | |||
| 121 | if( @_ ) | ||
| 122 | { | ||
| 123 | my $tbl_o = $self->{table_object} || return( $self->error( "No table object is set." ) ); | ||
| 124 | # No version check needed-ON DUPLICATE KEY UPDATE is in MySQL since 4.1.0 (2004) | ||
| 125 | my $ver = $tbl_o->database_object->version; | ||
| 126 | if( version->parse( $ver ) < version->parse( '4.1.0' ) ) | ||
| 127 | { | ||
| 128 | return( $self->error( "MySQL version is $ver, but version 4.1.0 or higher is required to use this on duplicate clause." ) ); | ||
| 129 | } | ||
| 130 | |||
| 131 | $opts = $self->_get_args_as_hash( @_ ); | ||
| 132 | my $hash = {}; | ||
| 133 | my @comp = ('ON DUPLICATE KEY UPDATE'); | ||
| 134 | |||
| 135 | # Target is optional in MySQL-implicitly uses any duplicate key (primary or unique) | ||
| 136 | if( $opts->{target} ) | ||
| 137 | { | ||
| 138 | $hash->{target} = $opts->{target}; | ||
| 139 | # MySQL doesn’t support explicit constraint targeting (unlike PostgreSQL/SQLite), | ||
| 140 | # so we store it for reference but don’t alter the query syntax. | ||
| 141 | warn( "MySQL does not support explicit constraint targeting, so the target specified '$opts->{target}' will not be applied." ) if( $self->_is_warnings_enabled( 'DB::Object' ) ); | ||
| 142 | } | ||
| 143 | |||
| 144 | # Action handling | ||
| 145 | if( $opts->{action} ) | ||
| 146 | { | ||
| 147 | if( $opts->{action} eq 'update' ) | ||
| 148 | { | ||
| 149 | $hash->{action} = $opts->{action}; | ||
| 150 | |||
| 151 | # No fields provided-defer to callback using INSERT data | ||
| 152 | if( !$opts->{fields} ) | ||
| 153 | { | ||
| 154 | $self->{_on_conflict_callback} = sub | ||
| 155 | { | ||
| 156 | my $f_ref = $self->{_args}; | ||
| 157 | $self->is_upsert(1); | ||
| 158 | # Assuming this returns a formatted SET clause | ||
| 159 | my $elems = $self->format_update($f_ref); | ||
| 160 | push( @comp, $elems->formats->join( ', ' ) ); | ||
| 161 | $hash->{query} = join(' ', @comp); | ||
| 162 | $self->{_on_conflict} = $hash; | ||
| 163 | $self->{on_conflict} = join( ' ', @comp ); | ||
| 164 | $self->elements->push( $elems->elements->list ) if( $elems->elements ); | ||
| 165 | CORE::delete( $self->{_on_conflict_callback} ); | ||
| 166 | }; | ||
| 167 | # Empty string signals deferred processing | ||
| 168 | return( '' ); | ||
| 169 | } | ||
| 170 | |||
| 171 | # Validate fields | ||
| 172 | return( $self->error( "Fields property for on_conflict update must be a hash or array." ) ) | ||
| 173 | if( !$self->_is_hash( $opts->{fields} => 'strict' ) && !$self->_is_array( $opts->{fields} ) && !$self->{_on_conflict_callback} ); | ||
| 174 | if( $self->_is_hash( $opts->{fields} => 'strict' ) ) | ||
| 175 | { | ||
| 176 | return( $self->error( "Fields property for on_conflict update contains no fields!" ) ) | ||
| 177 | if( !scalar( keys( %{$opts->{fields}} ) ) ); | ||
| 178 | } | ||
| 179 | elsif( $self->_is_array( $opts->{fields} ) ) | ||
| 180 | { | ||
| 181 | return( $self->error( "Fields property for on_conflict update contains no fields!" ) ) | ||
| 182 | if( !scalar( @{$opts->{fields}} ) ); | ||
| 183 | } | ||
| 184 | |||
| 185 | # Convert array fields to hash with VALUES(column) | ||
| 186 | if( $self->_is_array( $opts->{fields} ) ) | ||
| 187 | { | ||
| 188 | $opts->{fields} = +{ map{ $_ => \"VALUES($_)" } @{$opts->{fields}} }; | ||
| 189 | } | ||
| 190 | $hash->{fields} = $opts->{fields}; | ||
| 191 | |||
| 192 | # Build the SET clause | ||
| 193 | my $q = []; | ||
| 194 | foreach my $k ( sort( keys( %{$opts->{fields}} ) ) ) | ||
| 195 | { | ||
| 196 | my $val = $opts->{fields}->{ $k }; | ||
| 197 | push( @$q, sprintf( '%s = %s', $k, ref( $val ) eq 'SCALAR' ? $$val : $tbl_o->database_object->quote( $val ) ) ); | ||
| 198 | } | ||
| 199 | if( scalar( @$q ) ) | ||
| 200 | { | ||
| 201 | push( @comp, join( ', ', @$q ) ); | ||
| 202 | } | ||
| 203 | else | ||
| 204 | { | ||
| 205 | return( $self->error( "ON DUPLICATE KEY UPDATE specified, but no fields to update." ) ); | ||
| 206 | } | ||
| 207 | } | ||
| 208 | elsif( $opts->{action} eq 'nothing' || | ||
| 209 | $opts->{action} eq 'ignore' ) | ||
| 210 | { | ||
| 211 | # MySQL doesn’t have a native "do nothing" for ON DUPLICATE KEY UPDATE, | ||
| 212 | # so we set a no-op (e.g., id = id) | ||
| 213 | $hash->{action} = $opts->{action}; | ||
| 214 | # Fallback to 'id' if no PK defined | ||
| 215 | my $pk = $tbl_o->primary_key || 'id'; | ||
| 216 | push( @comp, "$pk = $pk" ); | ||
| 217 | } | ||
| 218 | else | ||
| 219 | { | ||
| 220 | return( $self->error( "Unknown action '$opts->{action}' for on_conflict clause." ) ); | ||
| 221 | } | ||
| 222 | } | ||
| 223 | else | ||
| 224 | { | ||
| 225 | return( $self->error( "No action specified for the on_conflict clause." ) ); | ||
| 226 | } | ||
| 227 | |||
| 228 | $hash->{query} = join( ' ', @comp ); | ||
| 229 | $self->{_on_conflict} = $hash; | ||
| 230 | $self->{on_conflict} = $self->new_clause({ value => join( ' ', @comp ) }); | ||
| 231 | } | ||
| 232 | |||
| 233 | # Execute callback if called by _query_components | ||
| 234 | if( $self->{_on_conflict_callback} && !scalar( @_ ) ) | ||
| 235 | { | ||
| 236 | $self->{_on_conflict_callback}->(); | ||
| 237 | } | ||
| 238 | return $self->{on_conflict}; | ||
| 239 | } | ||
| 240 | |||
| 241 | # I know I could alias one on the other, but I want it to show up in the stack trace, so ti can be corrected. | ||
| 242 | sub on_update { return( shift->on_conflict( @_ ) ); } | ||
| 243 | |||
| 244 | sub replace | ||
| 245 | { | ||
| 246 | my $self = shift( @_ ); | ||
| 247 | my $data = shift( @_ ) if( @_ == 1 && ref( $_[ 0 ] ) ); | ||
| 248 | my @arg = @_; | ||
| 249 | my %arg = (); | ||
| 250 | my $select = ''; | ||
| 251 | if( !%arg && $data && $self->_is_hash( $data => 'strict' ) ) | ||
| 252 | { | ||
| 253 | %arg = %$data; | ||
| 254 | } | ||
| 255 | elsif( $data && $self->_is_object( $data ) && $data->isa( 'DB::Object::Statement' ) ) | ||
| 256 | { | ||
| 257 | $select = $data->as_string; | ||
| 258 | } | ||
| 259 | %arg = @arg if( @arg ); | ||
| 260 | my $tbl_o = $self->table_object || return( $self->error( "No table object is set." ) ); | ||
| 261 | my $table = $tbl_o->name || | ||
| 262 | return( $self->error( "No table was provided to replace data." ) ); | ||
| 263 | my $structure = $tbl_o->structure || return( $self->pass_error( $tbl_o->error ) ); | ||
| 264 | my $null = $tbl_o->null; | ||
| 265 | my @avoid = (); | ||
| 266 | foreach my $field ( keys( %$structure ) ) | ||
| 267 | { | ||
| 268 | ## It is useless to insert a blank data in a field whose default value is NULL. | ||
| 269 | ## Especially since a test on a NULL field may be made specifically. | ||
| 270 | push( @avoid, $field ) if( !exists( $arg{ $field } ) && $null->{ $field } ); | ||
| 271 | } | ||
| 272 | $self->getdefault({ | ||
| 273 | table => $table, | ||
| 274 | arg => \@arg, | ||
| 275 | avoid => \@avoid | ||
| 276 | }); | ||
| 277 | my( $fields, $values ) = $self->format_statement; | ||
| 278 | ## $self->{ 'binded_values' } = $db_data->{ 'binded_values' }; | ||
| 279 | my $query = $self->{query} = $select ? "REPLACE INTO $table $select" : "REPLACE INTO $table ($fields) VALUES($values)"; | ||
| 280 | ## Everything meaningfull lies within the object | ||
| 281 | ## If no bind should be done _save_bind does nothing | ||
| 282 | $self->_save_bind(); | ||
| 283 | ## Query string should lie within the object | ||
| 284 | ## _cache_this sends back an object no matter what or unde() if an error occurs | ||
| 285 | my $sth = $tbl_o->_cache_this( $self ); | ||
| 286 | ## STOP! No need to go further | ||
| 287 | if( !defined( $sth ) ) | ||
| 288 | { | ||
| 289 | return( $self->error( "Error while preparing query to replace data into table '$table':\n$query", $self->errstr() ) ); | ||
| 290 | } | ||
| 291 | if( !defined( wantarray() ) ) | ||
| 292 | { | ||
| 293 | $sth->execute() || | ||
| 294 | return( $self->error( "Error while executing query to replace data to table '$table':\n$query" ) ); | ||
| 295 | } | ||
| 296 | return( $sth ); | ||
| 297 | } | ||
| 298 | |||
| 299 | sub reset | ||
| 300 | { | ||
| 301 | my $self = shift( @_ ); | ||
| 302 | if( !$self->{query_reset} ) | ||
| 303 | { | ||
| 304 | 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 )]; | ||
| 305 | CORE::delete( @$self{ @$keys } ); | ||
| 306 | $self->{query_reset}++; | ||
| 307 | # $self->{enhance} = 1; | ||
| 308 | } | ||
| 309 | return( $self ); | ||
| 310 | } | ||
| 311 | |||
| 312 | sub reset_bind | ||
| 313 | { | ||
| 314 | my $self = shift( @_ ); | ||
| 315 | my @f = qw( binded binded_where binded_group binded_having binded_order binded_limit ); | ||
| 316 | foreach my $field ( @f ) | ||
| 317 | { | ||
| 318 | $self->{ $field } = []; | ||
| 319 | } | ||
| 320 | return( $self ); | ||
| 321 | } | ||
| 322 | |||
| 323 | # Not supported in MySQL | ||
| 324 | # sub returning | ||
| 325 | |||
| 326 | # Inherited from DB::Object::Query | ||
| 327 | # sub update | ||
| 328 | |||
| 329 | sub _query_components | ||
| 330 | { | ||
| 331 | my $self = shift( @_ ); | ||
| 332 | my $type = ( @_ > 0 && lc( shift( @_ ) ) ) || $self->_query_type() || return( $self->error( "You must specify a query type: select, insert, update or delete" ) ); | ||
| 333 | my $opts = $self->_get_args_as_hash( @_ ); | ||
| 334 | # ok options: | ||
| 335 | # 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. | ||
| 336 | $opts->{no_bind_copy} //= 0; | ||
| 337 | my( $where, $group, $having, $sort, $order, $limit, $on_conflict ); | ||
| 338 | |||
| 339 | $where = $self->where(); | ||
| 340 | if( $type eq 'select' ) | ||
| 341 | { | ||
| 342 | $group = $self->group; | ||
| 343 | $having = $self->having; | ||
| 344 | $sort = $self->reverse ? 'DESC' : $self->sort ? 'ASC' : ''; | ||
| 345 | $order = $self->order; | ||
| 346 | } | ||
| 347 | $limit = $self->limit; | ||
| 348 | $on_conflict = $self->on_conflict; | ||
| 349 | my @query = (); | ||
| 350 | push( @query, "WHERE $where" ) if( $where && $type ne 'insert' ); | ||
| 351 | if( $where && $where->elements->length ) | ||
| 352 | { | ||
| 353 | $self->elements->push( $where ) unless( $opts->{no_bind_copy} ); | ||
| 354 | } | ||
| 355 | push( @query, "GROUP BY $group" ) if( $group && $type eq 'select' ); | ||
| 356 | push( @query, "HAVING $having" ) if( $having && $type eq 'select' ); | ||
| 357 | push( @query, "ORDER BY $order" ) if( $order && $type eq 'select' ); | ||
| 358 | push( @query, $sort ) if( $sort && $order && $type eq 'select' ); | ||
| 359 | if( $limit && $type eq 'select' ) | ||
| 360 | { | ||
| 361 | push( @query, "$limit" ); | ||
| 362 | if( $limit->elements->length ) | ||
| 363 | { | ||
| 364 | $self->elements->push( $limit ) unless( $opts->{no_bind_copy} ); | ||
| 365 | } | ||
| 366 | } | ||
| 367 | if( $on_conflict ) | ||
| 368 | { | ||
| 369 | if( $type eq 'insert' ) | ||
| 370 | { | ||
| 371 | push( @query, $on_conflict ); | ||
| 372 | } | ||
| 373 | else | ||
| 374 | { | ||
| 375 | warn( "Warning only: the MySQL ON CONFLICT clause is only supported for INSERT queries. Your query was of type \"$type\"." ) if( $self->_is_warnings_enabled( 'DB::Object' ) ); | ||
| 376 | } | ||
| 377 | } | ||
| 378 | return( \@query ); | ||
| 379 | } | ||
| 380 | |||
| 381 | 1; | ||
| 382 | # NOTE: POD | ||
| 383 | __END__ |