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