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

filter: Statementstmt 0.0% 0 / 190 Branchbran 0.0% 0 / 108 Conditioncond 0.0% 0 / 95 MC/DCmcdc 0.0% 0 / 54 Subroutinesub 0.0% 0 / 20 Podpod 0.0% 0 / 13 totaltotal 0.0% 0 / 480 CC 55 SCAR 53.0
CC
55
Cov
0%
on_conflict
420.0
_query_components
306.0
replace
90.0
CRAP 200.2
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__