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

filter: Statementstmt 0.0% 0 / 343 Branchbran 0.0% 0 / 136 Conditioncond 0.0% 0 / 120 MC/DCmcdc 0.0% 0 / 67 Subroutinesub 0.0% 0 / 32 Podpod 0.0% 0 / 25 totaltotal 0.0% 0 / 723 CC 62 SCAR 34.9
CC
62
Cov
0%
create_db
90.0
DESTROY
72.0
stat
42.0
CRAP 32.9
1 # -*- perl -*-
2 ##----------------------------------------------------------------------------
3 ## Database Object Interface - ~/lib//mnt/src/perl/DB-Object/lib/DB/Object/Mysql.pm
4 ## Version v1.6.0
5 ## Copyright(c) 2026 DEGUEST Pte. Ltd.
6 ## Author: Jacques Deguest <jack@deguest.jp>
7 ## Created 2017/07/19
8 ## Modified 2026/09/23
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 is the subclassable module for driver specific ones.
15 package DB::Object::Mysql;
16 BEGIN
17 {
18 use strict;
19 use warnings;
20 warnings::register_categories( 'DB::Object' );
21 use parent qw( DB::Object );
22 use vars qw(
23 $VERSION $CACHE_SIZE
24 $DEBUG $ERROR $MOD_PERL $USE_BIND $USE_CACHE $PLACEHOLDER_REGEXP
25 $DATATYPES_DICT $EXCEPTION_CLASS
26 );
27 eval{ require DBD::mysql; };
28 die( $@ ) if( $@ );
29 # <https://dev.mysql.com/doc/refman/8.0/en/data-types.html>
30 # the 'constant' property in the dictionary hash is added in structure()
31 # See also: SELECT DISTINCT(data_type) FROM information_schema.columns ORDER by data_type
32 # but this does not provide a complete list of datatype
33 our $DATATYPES_DICT =
34 {
35 bigint => {
36 constant => '',
37 name => 'SQL_BIGINT',
38 re => qr/^(bigint)/,
39 type => 'bigint',
40 },
41 binary => {
42 constant => '',
43 name => 'SQL_BINARY',
44 re => qr/^binary/,
45 type => 'binary',
46 },
47 bit => {
48 constant => '',
49 name => 'SQL_BIT',
50 re => qr/^(bit)/,
51 type => 'bit',
52 },
53 blob => {
54 constant => '',
55 name => 'SQL_BLOB',
56 re => qr/^blob/,
57 type => 'blob',
58 },
59 curve => {
60 constant => '',
61 name => 'SQL_UNKNOWN_TYPE',
62 re => qr/^(curve)\b/,
63 type => 'curve',
64 },
65 date => {
66 constant => '',
67 name => 'SQL_DATE',
68 re => qr/^(date)\b/,
69 type => 'date',
70 },
71 datetime => {
72 constant => '',
73 name => 'SQL_DATETIME',
74 re => qr/^(datetime)/,
75 type => 'datetime',
76 },
77 decimal => {
78 alias => [qw( dec )],
79 constant => '',
80 name => 'SQL_DECIMAL',
81 re => qr/^(dec|decimal)/,
82 type => 'decimal',
83 },
84 double => {
85 alias => [ 'double precision' ],
86 constant => '',
87 name => 'SQL_DOUBLE',
88 re => qr/^(double\s+precision|double)/,
89 type => 'double',
90 },
91 enum => {
92 constant => '',
93 name => 'SQL_UNKNOWN_TYPE',
94 re => qr/^(enum)\b/,
95 type => 'enum',
96 },
97 float => {
98 constant => '',
99 name => 'SQL_FLOAT',
100 re => qr/^(float)/,
101 type => 'float',
102 },
103 geometry => {
104 constant => '',
105 name => 'SQL_UNKNOWN_TYPE',
106 re => qr/^(geometry)\b/,
107 type => 'geometry',
108 },
109 geometrycollection => {
110 constant => '',
111 name => 'SQL_UNKNOWN_TYPE',
112 re => qr/^(geometrycollection)\b/,
113 type => 'geometrycollection',
114 },
115 int => {
116 alias => [qw( integer )],
117 constant => '',
118 name => 'SQL_INTEGER',
119 re => qr/^(integer|int)/,
120 type => 'int',
121 },
122 json => {
123 constant => '',
124 name => 'SQL_UNKNOWN_TYPE',
125 re => qr/^(json)\b/,
126 type => 'json',
127 },
128 linestring => {
129 constant => '',
130 name => 'SQL_UNKNOWN_TYPE',
131 re => qr/^(linestring)\b/,
132 type => 'linestring',
133 },
134 longlob => {
135 constant => '',
136 name => 'SQL_BLOB',
137 re => qr/^(longlob)\b/,
138 type => 'longlob',
139 },
140 longtext => {
141 constant => '',
142 name => 'SQL_LONGVARCHAR',
143 re => qr/^(longtext)\b/,
144 type => 'longtext',
145 },
146 mediumblob => {
147 constant => '',
148 name => 'SQL_BLOB',
149 re => qr/^(mediumblob)\b/,
150 type => 'mediumblob',
151 },
152 mediumint => {
153 constant => '',
154 name => 'SQL_BIT',
155 re => qr/^(mediumint)/,
156 type => 'mediumint',
157 },
158 mediumtext => {
159 constant => '',
160 name => 'SQL_LONGVARCHAR',
161 re => qr/^(mediumtext)\b/,
162 type => 'mediumtext',
163 },
164 multicurve => {
165 constant => '',
166 name => 'SQL_UNKNOWN_TYPE',
167 re => qr/^(multicurve)\b/,
168 type => 'multicurve',
169 },
170 multilinestring => {
171 constant => '',
172 name => 'SQL_UNKNOWN_TYPE',
173 re => qr/^(multilinestring)\b/ ,
174 type => 'multilinestring',
175 },
176 multipoint => {
177 constant => '',
178 name => 'SQL_UNKNOWN_TYPE',
179 re => qr/^(multipoint)\b/,
180 type => 'multipoint',
181 },
182 multipolygon => {
183 constant => '',
184 name => 'SQL_UNKNOWN_TYPE',
185 re => qr/^(multipolygon)\b/,
186 type => 'multipolygon',
187 },
188 point => {
189 constant => '',
190 name => 'SQL_UNKNOWN_TYPE',
191 re => qr/^(point)\b/,
192 type => 'point',
193 },
194 polygon => {
195 constant => '',
196 name => 'SQL_UNKNOWN_TYPE',
197 re => qr/^(polygon)\b/,
198 type => 'polygon',
199 },
200 set => {
201 constant => '',
202 name => 'SQL_UNKNOWN_TYPE',
203 re => qr/^(set)\b/,
204 type => 'set',
205 },
206 smallint => {
207 constant => '',
208 name => 'SQL_SMALLINT',
209 re => qr/^(smallint)/,
210 type => 'smallint',
211 },
212 text => {
213 constant => '',
214 name => 'SQL_LONGVARCHAR',
215 re => qr/^text/,
216 type => 'text',
217 },
218 timestamp => {
219 constant => '',
220 name => 'SQL_TIMESTAMP',
221 re => qr/^(timestamp)/,
222 type => 'timestamp',
223 },
224 tinyblob => {
225 constant => '',
226 name => 'SQL_BLOB',
227 re => qr/^(tinyblob)\b/,
228 type => 'tinyblob',
229 },
230 tinyint => {
231 constant => '',
232 name => 'SQL_TINYINT',
233 re => qr/^(tinyint)/,
234 type => 'tinyint',
235 },
236 tinytext => {
237 constant => '',
238 name => 'SQL_LONGVARCHAR',
239 re => qr/^(tinytext)\b/,
240 type => 'tinytext',
241 },
242 varbinary => {
243 constant => '',
244 name => 'SQL_VARBINARY',
245 re => qr/^varbinary/,
246 type => 'varbinary',
247 },
248 varchar => {
249 alias => [qw( char character ), 'character varying' ],
250 constant => '',
251 name => 'SQL_VARCHAR',
252 re => qr/^((?:character varying|varchar)|(?:(?:character|char)\b(?![[:blank:]]+varying)))/,
253 type => 'varchar',
254 },
255 year => {
256 constant => '',
257 name => 'SQL_INTERVAL_YEAR',
258 re => qr/^(year)/,
259 type => 'year',
260 },
261 };
262 # DBI->trace(5);
263 our $PLACEHOLDER_REGEXP = qr/(?<![?\w])\?(?![?\w])/;
264 our $EXCEPTION_CLASS = $DB::Object::EXCEPTION_CLASS;
265 our $VERSION = 'v1.6.0';
266 };
267  
268 use strict;
269 use warnings;
270 our $DEBUG = 0;
271 our $CACHE_SIZE = 10;
272 our $USE_BIND = 0;
273 our $USE_CACHE = 0;
274 our $MOD_PERL = 0;
275 if( $INC{ 'Apache/DBI.pm' } &&
276 substr( $ENV{GATEWAY_INTERFACE}|| '', 0, 8 ) eq 'CGI-Perl' )
277 {
278 $MOD_PERL++;
279 }
280 # Actually the one in DB::Object is used, because DBD::mysql has no datatype constants of its own
281 # our $DATATYPES = {};
282 foreach my $type ( keys( %$DATATYPES_DICT ) )
283 {
284 if( CORE::exists( $DATATYPES_DICT->{ $type }->{alias} ) &&
285 ref( $DATATYPES_DICT->{ $type }->{alias} ) eq 'ARRAY' &&
286 scalar( @{$DATATYPES_DICT->{ $type }->{alias}} ) )
287 {
288 foreach my $alias ( @{$DATATYPES_DICT->{ $type }->{alias}} )
289 {
290 next if( CORE::exists( $DATATYPES_DICT->{ $alias } ) );
291 $DATATYPES_DICT->{ $alias } = $DATATYPES_DICT->{ $type };
292 }
293 }
294 }
295  
296 my $keys = $DBI::EXPORT_TAGS{sql_types};
297 my @constants_to_ignore = qw(
298 SQL_GUID SQL_WLONGVARCHAR SQL_WVARCHAR SQL_WCHAR SQL_LONGVARBINARY SQL_LONGVARCHAR
299 SQL_UNKNOWN_TYPE SQL_ALL_TYPES SQL_NUMERIC SQL_REAL SQL_INTERVAL SQL_TIME SQL_BOOLEAN
300 SQL_UDT SQL_UDT_LOCATOR SQL_ROW SQL_REF SQL_BLOB_LOCATOR SQL_CLOB SQL_CLOB_LOCATOR
301 SQL_ARRAY SQL_ARRAY_LOCATOR SQL_MULTISET SQL_MULTISET_LOCATOR SQL_TYPE_DATE
302 SQL_TYPE_TIME SQL_TYPE_TIMESTAMP SQL_TYPE_TIME_WITH_TIMEZONE SQL_TYPE_TIMESTAMP_WITH_TIMEZONE
303 SQL_INTERVAL_YEAR SQL_INTERVAL_MONTH SQL_INTERVAL_DAY SQL_INTERVAL_HOUR SQL_INTERVAL_MINUTE
304 SQL_INTERVAL_SECOND SQL_INTERVAL_YEAR_TO_MONTH SQL_INTERVAL_DAY_TO_HOUR
305 SQL_INTERVAL_DAY_TO_MINUTE SQL_INTERVAL_DAY_TO_SECOND SQL_INTERVAL_HOUR_TO_MINUTE
306 SQL_INTERVAL_HOUR_TO_SECOND SQL_INTERVAL_MINUTE_TO_SECOND
307 );
308 foreach my $c ( @$keys )
309 {
310 next if( scalar( grep( /^$c$/, @constants_to_ignore ) ) );
311 if( $c =~ /^SQL_(\w+)$/ )
312 {
313 my $type = lc( $1 );
314 my $code = \&{"DBI::$c"};
315 my $val = $code->();
316 if( !CORE::exists( $DATATYPES_DICT->{ $type } ) )
317 {
318 warn( "Unknown MySQL constant DBI::${c}" ) if( warnings::enabled( 'DB::Object' ) );
319 next;
320 }
321 $DATATYPES_DICT->{ $type }->{constant} = $val;
322 }
323 }
324  
325 sub init
326 {
327 my $self = shift( @_ );
328 $self->{_exception_class} = $EXCEPTION_CLASS;
329 $self->SUPER::init( @_ ) || return( $self->pass_error );
330 $self->{driver} = 'mysql';
331 return( $self );
332 }
333 # End of generic routines
334  
335 # ROUTINES PROPRIETAIRE
336 sub attribute($;$@)
337 {
338 my $self = shift( @_ );
339 # $h->{AttributeName} = ...; # set/write
340 # ... = $h->{AttributeName}; # get/read
341 # 1 means that the attribute may be modified
342 # 0 mneas that the attribute may only be read
343 my $name = shift( @_ ) if( @_ == 1 );
344 my %arg = ( @_ );
345 my %attr =
346 (
347 Warn => 1,
348 Active => 0,
349 Kids => 0,
350 ActiveKids => 0,
351 CachedKids => 0,
352 InactiveDestroy => 1,
353 PrintError => 1,
354 RaiseError => 1,
355 ChopBlanks => 1,
356 LongReadLen => 1,
357 LongTruncOk => 1,
358 AutoCommit => 1,
359 Name => 0,
360 RowCacheSize => 0,
361 NUM_OF_FIELDS => 0,
362 NUM_OF_PARAMS => 0,
363 NAME => 0,
364 TYPE => 0,
365 PRECISION => 0,
366 SCALE => 0,
367 NULLABLE => 0,
368 CursorName => 0,
369 Statement => 0,
370 RowsInCache => 0,
371 );
372 # Only those attribute exist
373 # Using an a non existing attribute produce an exception, so we better avoid
374 if( $name )
375 {
376 return( $self->{dbh}->{ $name } ) if( exists( $attr{ $name } ) );
377 }
378 else
379 {
380 my $value;
381 while( ( $name, $value ) = each( %arg ) )
382 {
383 # We intend to modifiy the value of an attribute
384 # we are allowed to modify this value if it is true
385 if( exists( $attr{ $name } ) &&
386 defined( $value ) &&
387 $attr{ $name } )
388 {
389 $self->{dbh}->{ $name } = $value;
390 }
391 }
392 }
393 }
394  
395 sub begin_work($;$@)
396 {
397 my $self = shift( @_ );
398 my $autocommit_previous = $self->{dbh}->{AutoCommit};
399 local $@;
400 my $rv = eval
401 {
402 return( $self->{dbh}->begin_work( @_ ) );
403 };
404 if( $@ )
405 {
406 return( $self->error( "Error calling begin_work(). Have you forgotten to turn on AutoCommit?: $@" ) );
407 }
408 elsif( !$rv && $self->{dbh}->err )
409 {
410 return( $self->error({ code => $self->{dbh}->err, message => $self->{dbh}->errstr }) );
411 }
412  
413 if( defined( $rv ) )
414 {
415 $self->{AutoCommit_previous} = $autocommit_previous;
416 $self->{dbh}->{AutoCommit} = 0;
417 $self->_transaction_started;
418 return( $rv || 1 );
419 }
420 return;
421 }
422  
423 sub commit($;$@)
424 {
425 my $self = shift( @_ );
426 local $@;
427 my $rv = eval
428 {
429 return( $self->{dbh}->commit( @_ ) );
430 };
431 if( $@ )
432 {
433 return( $self->error( "Error calling commit(): $@" ) );
434 }
435 elsif( !$rv && $self->{dbh}->err )
436 {
437 return( $self->error({ code => $self->{dbh}->err, message => $self->{dbh}->errstr }) );
438 }
439  
440 if( defined( $rv ) )
441 {
442 $self->{dbh}->{AutoCommit} = $self->{AutoCommit_previous} if( defined( $self->{AutoCommit_previous} ) );
443 $self->_transaction_finished;
444 return( $rv || 1 );
445 }
446 return;
447 }
448  
449 # NOTE: sub connect is inherited
450  
451 # NOTE: sub constant_to_datatype is inherited
452  
453 sub create_db
454 {
455 my $self = shift( @_ );
456 my $name = shift( @_ ) || return( $self->error( "No database name to create was provided." ) );
457 my $opts = $self->_get_args_as_hash( @_ );
458 my $params = [];
459 # https://dev.mysql.com/doc/refman/5.6/en/create-database.html
460 push( @$params, sprintf( 'CHARACTER SET = %s', $opts->{charset} ) ) if( $opts->{charset} );
461 push( @$params, sprintf( 'COLLATE = %s', $opts->{collate} ) ) if( $opts->{collate} );
462 my $sql = "CREATE DATABASE " . ( $opts->{if_not_exists} ? 'IF NOT EXISTS ' : '' ) . $name;
463 if( scalar( @$params ) )
464 {
465 $sql .= ' ' . join( ' ', @$params );
466 }
467 my $dbh = $self->{dbh} || return( $self->error( "Could not find database handler." ) );
468 my( $sth, $rc );
469 # try-catch
470 local $@;
471 $sth = eval
472 {
473 $dbh->prepare( $sql );
474 };
475 if( $@ )
476 {
477 return( $self->error( "An error occured while prepareing SQL query to create database: ", $@ ) );
478 }
479 $sth or return( $self->error( "An error occured while prepareing SQL query to create database: ", $dbh->errstr ) );
480  
481 $rc = eval
482 {
483 $dbh->prepare( $sql );
484 };
485 if( $@ )
486 {
487 return( $self->error( "An error occured while prepareing SQL query to create database: ", $@ ) );
488 }
489 $rc or return( $self->error( "An error occured while prepareing SQL query to create database: ", $dbh->errstr ) );
490  
491 # try-catch
492 eval
493 {
494 $sth->finish;
495 };
496 if( $@ )
497 {
498 return( $self->error( "An unexpected error occurred while trying to finish the SQL query to create database: ", $@, "\n$sql" ) );
499 }
500  
501 my $ref = {};
502 # my @keys = qw( host port login passwd opt debug );
503 my $ok_params = $self->_connection_parameters;
504 foreach my $key ( @$ok_params )
505 {
506 if( my $coderef = $self->can( $key ) )
507 {
508 $ref->{ $key } = $coderef->( $self );
509 }
510 }
511 $ref->{database} = $name;
512 $dbh = $self->connect( $ref ) || return( $self->error( "I could create the database \"$name\" but oddly enough, I could not connect to it with user \"$ref->{login}\" on host \"$ref->{host}\" with port \"$ref->{port}\"." ) );
513 return( $dbh );
514 }
515  
516 sub databases
517 {
518 my $self = shift( @_ );
519 # return( $self->error( "Not connected to PostgreSQL server yet. Issue $dbh->connect first." ) ) if( !$self->{ 'dbh' } );
520 my $dbh;
521 # If there is no connection yet, then create one using the postgres login.
522 # There should not be a live user and database just to check what databases there are.
523 if( !$self->{dbh} )
524 {
525 my $con =
526 {
527 'database' => 'mysql',
528 };
529 $con->{mysql_read_default_file} = '/etc/my.cnf' if( -f( '/etc/my.cnf' ) );
530 if( CORE::exists( $ENV{ 'DB_MYSQL_CON' } ) )
531 {
532 @$con{ qw( host login passwd ) } = split( /;/, $ENV{ 'DB_MYSQL_CON' } );
533 }
534 else
535 {
536 @$con{ qw( host login passwd ) } = @_;
537 }
538  
539 # try-catch
540 local $@;
541 $dbh = eval
542 {
543 $self->connect( $con );
544 };
545 if( $@ )
546 {
547 return( $self->error( "Error trying to connect to the MySQL server: $@" ) );
548 }
549 $dbh or return( $self->pass_error );
550 }
551 else
552 {
553 $dbh = $self;
554 }
555 my $temp = $dbh->do( "SHOW DATABASES" )->fetchall_arrayref;
556 my @dbases = map( $_->[0], @$temp );
557 return( @dbases );
558 }
559  
560 # NOTE: sub datatype_dict is inherited
561  
562 # NOTE: sub datatype_to_constant is inherited
563  
564 # NOTE: sub datatypes is in inherited
565  
566 sub get_sql_type
567 {
568 my $self = shift( @_ );
569 my $type = shift( @_ ) || return( $self->error( "No sql type was provided to get its constant." ) );
570 $type = lc( $type );
571 if( CORE::exists( $DATATYPES_DICT->{ $type } ) &&
572 $type ne $DATATYPES_DICT->{ $type }->{type} )
573 {
574 $type = $DATATYPES_DICT->{ $type }->{type};
575 }
576 my $const;
577 if( substr( $type, 0, 4 ) eq 'sql_' )
578 {
579 $const = $self->{dbh}->can( "DBI::\U${type}\E" );
580 }
581 else
582 {
583 $const = $self->{dbh}->can( "DBI::SQL_\U${type}\E" );
584 }
585 return( '' ) if( !defined( $const ) );
586 return( $const->() );
587 }
588  
589 # Specific to Mysql (Postgres also uses it)
590 sub having
591 {
592 my $self = shift( @_ );
593 my $q = $self->_reset_query;
594 return( $q->having( @_ ) );
595 }
596  
597 sub last_insert_id
598 {
599 my $self = shift( @_ );
600 my $query = $self->{dbh}->prepare( "SELECT LAST_INSERT_ID()" ) || return;
601 $query->execute();
602 my $val = $query->fetchrow();
603 $query->finish();
604 return( $val );
605 }
606  
607 sub lock
608 {
609 my $self = shift( @_ );
610 my $str = shift( @_ );
611 my $timeout = shift( @_ ) || 2;
612 # my $res = $self->select( "GET_LOCK( '$str', $timeout )" )->fetchrow();
613 my $sth = $self->{dbh}->prepare( "SELECT GET_LOCK( '$str', $timeout )" ) ||
614 return( $self->error( "Error while preparing query to get lock '$str': ", $self->{dbh}->errstr() ) );
615 $sth->execute() ||
616 return( $self->error( "Error while executing query to get lock '$str': ", $sth->errstr() ) );
617 my $res = $sth->fetchrow;
618 $sth->finish();
619 $self->{ '_locks' } ||= [];
620 push( @{ $self->{_locks} }, $str ) if( $res && $res ne 'NULL' );
621 return( $res eq 'NULL' ? undef() : $res );
622 }
623  
624 sub query_object { return( shift->_set_get_object( 'query_object', 'DB::Object::Mysql::Query', @_ ) ); }
625  
626 sub rollback
627 {
628 my $self = shift( @_ );
629 local $@;
630 my $rv = eval
631 {
632 return( $self->{dbh}->rollback( @_ ) );
633 };
634 if( $@ )
635 {
636 return( $self->error( "Error calling rollback: $@" ) );
637 }
638 elsif( !defined( $rv ) && $self->{dbh}->err )
639 {
640 return( $self->error({ code => $self->{dbh}->err, message => $self->{dbh}->errstr }) );
641 }
642  
643 if( defined( $rv ) )
644 {
645 $self->{dbh}->{AutoCommit} = $self->{AutoCommit_previous} if( defined( $self->{AutoCommit_previous} ) );
646 $self->_transaction_finished;
647 return( $rv || 1 );
648 }
649 return;
650 }
651  
652 sub stat
653 {
654 my $self = shift( @_ );
655 my $type = lc( shift( @_ ) );
656 my $sth = $self->{dbh}->prepare( "SHOW STATUS" );
657 $sth->execute();
658 my @data = ();
659 my $ref = {};
660 while( @data = $sth->fetchrow() )
661 {
662 $ref->{ lc( $data[ 0 ] ) } = $data[ 1 ];
663 }
664 $sth->finish();
665 if( $type )
666 {
667 return( exists( $ref->{ $type } ) ? $ref->{ $type } : undef() );
668 }
669 else
670 {
671 return( wantarray() ? () : undef() ) if( !%$ref );
672 return( wantarray() ? %$ref : $ref );
673 }
674 }
675  
676 # sub table_exists inherited from DB::Object
677  
678 sub table_info
679 {
680 my $self = shift( @_ );
681 my $table = shift( @_ ) ||
682 return( $self->error( "You must provide a table name to access the table methods." ) );
683 my $opts = $self->_get_args_as_hash( @_ );
684 my $db = $opts->{database} || $self->database;
685 # my $sth = $self->{dbh}->table_info( undef(), undef(), $table );
686 my $sql = <<EOT;
687 SELECT
688 table_schema AS "schema"
689 ,table_name AS "name"
690 ,table_type AS "type"
691 FROM information_schema.tables
692 WHERE table_name=?
693 EOT
694 my $dbh = $self->{dbh} || return( $self->error( "Could not find database handler." ) );
695 my $sth = $dbh->prepare_cached( $sql ) || return( $self->error( "An error occured while preparing query to check if table \"$table\" exists in our database: ", $dbh->errstr ) );
696 $sth->execute( $table ) || return( $self->error( "An error occured while executing query to check if table \"$table\" exists in our database: ", $sth->errstr ) );
697 my $all = $sth->fetchall_arrayref( {} );
698 $sth->finish;
699 return( [] ) if( !scalar( @$all ) );
700 foreach my $ref ( @$all )
701 {
702 # example: BASE TABLE
703 if( $ref->{type} =~ /^(?:(?:\S+)[[:blank:]]+)?(.*?)$/ )
704 {
705 $ref->{type} = $2;
706 }
707 }
708 return( $all ) if( $opts->{anywhere} );
709 foreach my $ref ( @$all )
710 {
711 if( $ref->{schema} eq $db )
712 {
713 return( $ref );
714 }
715 }
716 return( [] );
717 }
718  
719 sub tables
720 {
721 my $self = shift( @_ );
722 my $db = shift( @_ ) || $self->{ 'database' };
723 my $sth = $self->{dbh}->prepare( "SHOW TABLES FROM $db" ) ||
724 return( $self->error( "Error while preparing query to get all tables from database '$db'." ) );
725 $sth->execute();
726 my $ref = $sth->fetchall_arrayref();
727 $sth->finish;
728 my @tables = map{ $_->[0] } @$ref;
729 return( \@tables );
730 }
731  
732 sub tables_info
733 {
734 my $self = shift( @_ );
735 my $db = shift( @_ ) || $self->{database};
736 my @tables = ();
737 my $query = <<EOT;
738 SELECT
739 *
740 ,table_schema AS "database"
741 ,table_name AS "name"
742 ,table_type AS "type"
743 FROM information_schema.tables
744 WHERE table_schema=?
745 EOT
746 my $sth = $self->{dbh}->prepare_cached( $query ) || return( $self->error( sprintf( "Error while preparing query $query: %s", $self->{dbh}->errstr ) ) );
747 $sth->execute( $db ) || return( $self->error( sprintf( "Error while executing query $query: %s", $sth->errstr ) ) );
748 my $all = $sth->fetchall_arrayref( {} );
749 return( $all );
750 }
751  
752 sub unlock
753 {
754 my $self = shift( @_ );
755 my $str = shift( @_ ) ||
756 return( $self->error( "No lock string identifier provided." ) );
757 # my $res = $self->select( "RELEASE_LOCK( '$str' )" )->fetchrow();
758 my $sth = $self->{dbh}->prepare( "SELECT RELEASE_LOCK( '$str' )" ) ||
759 return( $self->error( "Error while preparing query to release lock '$str': ", $self->errstr() ) );
760 $sth->execute() ||
761 return( $self->error( "Error while executing query to release lock '$str': ", $sth->errstr() ) );
762 my $res = $sth->fetchrow;
763 $sth->finish();
764 # Take out the lock from the saved locks pile (used by DESTROY)
765 my $locks = $self->{ '_locks' } ||= [];
766 my @new = grep{ !/$str/ } @$locks;
767 $locks = [ @new ];
768 return( $res eq 'NULL' ? undef() : $res );
769 }
770  
771 sub variables
772 {
773 my $self = shift( @_ );
774 my $type = shift( @_ );
775 $self->error( "Variable '$type' is a read-only value." ) if( @_ );
776 my $vars = $self->{ 'variables' } ||= {};
777 if( !%$vars )
778 {
779 my $sth = $self->{dbh}->prepare( "SHOW VARIABLES" ) ||
780 return( $self->error( "SHOW VARIABLES is not supported." ) );
781 $sth->execute();
782 my $ref = $self->fetchall_arrayref();
783 my %vars = map{ lc( $_->[ 0 ] ) => $_->[ 1 ] } @$ref;
784 $vars = \%vars if( %vars );
785 $sth->finish();
786 }
787 my @found = grep{ /$type/i } keys( %$vars );
788 return( '' ) if( !scalar( @found ) );
789 return( $vars->{ $found[ 0 ] } );
790 }
791  
792 # https://dev.mysql.com/doc/refman/8.0/en/show-variables.html
793 sub version
794 {
795 my $self = shift( @_ );
796 # If we already have the information, let's use our cache instead of making a query
797 return( $self->{_db_version} ) if( length( $self->{_db_version} ) );
798 my $sql = 'SELECT @@innodb_version';
799 my $sth = $self->do( $sql ) || return( $self->error( "Unable to issue the sql statement '$sql' to get the server version: ", $self->errstr ) );
800 my $ver = $sth->fetchrow;
801 $sth->finish;
802 # We cache it
803 $self->{_db_version} = version->parse( $ver );
804 return( $ver );
805 }
806  
807 sub _connection_options
808 {
809 my $self = shift( @_ );
810 my $param = shift( @_ );
811 # This should really not be an option. This decode utf8 in database
812 $param->{mysql_enable_utf8} = 1 if( !CORE::exists( $param->{mysql_enable_utf8} ) );
813 my @mysql_params = grep( /^mysql_/, keys( %$param ) );
814 my $opt = $self->SUPER::_connection_options( $param );
815 @$opt{ @mysql_params } = @$param{ @mysql_params };
816 return( $opt );
817 }
818  
819 # NOTE: sub _connection_params2hash_driver is not necessary here. We use our parent's one.
820  
821 sub _connection_parameters
822 {
823 my $self = shift( @_ );
824 my $param = shift( @_ );
825 my $core = $self->new_array( [qw( db login passwd host port driver database server opt uri debug cache_connections cache_dir cache_query cache_table unknown_field use_cache id )] );
826 my @mysql_params = grep( /^mysql_/, keys( %$param ) );
827 # See DBD::mysql for the list of valid parameters
828 # E.g.: mysql_client_found_rows, mysql_compression mysql_connect_timeout mysql_write_timeout mysql_read_timeout mysql_init_command mysql_skip_secure_auth mysql_read_default_file mysql_read_default_group mysql_socket mysql_ssl mysql_ssl_client_key mysql_ssl_client_cert mysql_ssl_ca_file mysql_ssl_ca_path mysql_ssl_cipher mysql_local_infile mysql_multi_statements mysql_server_prepare mysql_server_prepare_disable_fallback mysql_embedded_options mysql_embedded_groups mysql_conn_attrs
829 push( @$core, @mysql_params );
830 my $core_fields = $self->{_core_fields} || [];
831 $core->push( @$core_fields );
832 return( $core->unique(1) );
833 }
834  
835 sub _dsn
836 {
837 my $self = shift( @_ );
838 # "DBI:mysql:database=$sql_db;host=$sql_host;port=$sql_port;mysql_read_default_file=/etc/my.cnf"
839 my @params = ( sprintf( 'dbi:%s:database=%s', @$self{ qw( driver database ) } ) );
840 if( $self->{host} )
841 {
842 $self->_load_class( 'Net::IP' ) || return( $self->pass_error );
843 my $ip = Net::IP->new( $self->{host} );
844 if( $ip )
845 {
846 if( $ip->version == 6 )
847 {
848 push( @params, sprintf( 'host=[%s]', $ip->ip ) );
849 }
850 else
851 {
852 push( @params, sprintf( 'host=%s', $ip->ip ) );
853 }
854 }
855 else
856 {
857 push( @params, sprintf( 'host=%s', $self->{host} ) );
858 }
859 }
860 # my @params = sprintf( "dbi:%s:database=%s;host=%s", @$self{ qw( driver database server ) } );
861 push( @params, sprintf( 'port=%d', $self->{port} ) ) if( $self->{port} );
862 push( @params, sprintf( 'mysql_read_default_file=%s', $self->{mysql_read_default_file} ) ) if( $self->{mysql_read_default_file} );
863 return( join( ';', @params ) );
864 }
865  
866 sub _placeholder_regexp { return( $PLACEHOLDER_REGEXP ) }
867  
868 # NOTE: DESTROY
869 DESTROY
870 {
871 # <https://perldoc.perl.org/perlobj#Destructors>
872 CORE::local( $., $@, $!, $^E, $? );
873 CORE::return if( ${^GLOBAL_PHASE} eq 'DESTRUCT' );
874 my $self = CORE::shift( @_ );
875 CORE::return if( !CORE::defined( $self ) );
876 my $class = ref( $self ) || $self;
877 if( $self->{sth} )
878 {
879 print( STDERR "DESTROY(): Terminating sth '$self' for query:\n$self->{query}\n" ) if( $DEBUG || $self->{debug} );
880 $self->{sth}->finish();
881 }
882 elsif( $self->{dbh} && $class eq 'DB::Object' )
883 {
884 local( $SIG{__WARN__} ) = sub { };
885 # $self->{ 'dbh' }->disconnect();
886 if( $DEBUG || $self->{debug} )
887 {
888 my( $pack, $file, $line, $sub ) = ( caller(0) )[0, 1, 2, 3];
889 my( $pack2, $file2, $line2, $sub2 ) = ( caller(1) ) [0, 1, 2, 3];
890 print( STDERR "DESTROY database handle ($self) [$self->{query}]\ncalled within sub '$sub' ($sub2) from package '$pack' ($pack2) in file '$file' ($file2) at line '$line' ($line2).\n" );
891 }
892 $self->disconnect();
893 }
894 my $locks = $self->{_locks};
895 if( $locks && $self->_is_array( $locks ) )
896 {
897 foreach my $name ( @$locks )
898 {
899 $self->unlock( $name );
900 }
901 }
902 }
903  
904 1;
905 # NOTE: POD
906 __END__