1package OpenXPKI::Server::Database; 2use Moose; 3use utf8; 4=head1 Name 5 6OpenXPKI::Server::Database - Handles database connections and encapsulates DB 7specific drivers/functions. 8 9=cut 10 11use OpenXPKI::Debug; 12use OpenXPKI::Exception; 13use OpenXPKI::MooseParams; 14use OpenXPKI::Server::Database::Role::Driver; 15use OpenXPKI::Server::Database::QueryBuilder; 16use OpenXPKI::Server::Database::Query; 17use Data::Dumper; 18use DBIx::Handler; 19use DBI::Const::GetInfoType; # provides %GetInfoType hash 20use Math::BigInt; 21use SQL::Abstract::More; 22use Moose::Exporter; 23 24# Export AUTO_ID 25Moose::Exporter->setup_import_methods(with_meta => [ 'AUTO_ID' ]); 26sub AUTO_ID { return bless {}, "OpenXPKI::Server::Database::AUTOINCREMENT" } 27 28## TODO special handling for SQLite databases from OpenXPKI::Server::Init->get_dbi() 29# if ($params{TYPE} eq "SQLite") { 30# if (defined $args->{PURPOSE} && ($args->{PURPOSE} ne "")) { 31# $params{NAME} .= "._" . $args->{PURPOSE} . "_"; 32# ##! 16: 'SQLite, name: ' . $params{NAME} 33# } 34# } 35 36################################################################################ 37# Attributes 38# 39 40has 'log' => ( 41 is => 'ro', 42 isa => 'Object', 43 required => 1, 44); 45 46# Parameters to construct DSN, mostly from config: system.database.[main|log] 47has 'db_params' => ( 48 is => 'ro', 49 isa => 'HashRef', 50 required => 1, 51); 52 53has 'autocommit' => ( 54 is => 'ro', 55 isa => 'Bool', 56 default => 0, 57); 58 59has 'driver' => ( 60 is => 'ro', 61 does => 'OpenXPKI::Server::Database::Role::Driver', 62 lazy => 1, 63 builder => '_build_driver', 64); 65 66has 'query_builder' => ( 67 is => 'ro', 68 isa => 'OpenXPKI::Server::Database::QueryBuilder', 69 lazy => 1, 70 default => sub { 71 my $self = shift; 72 return OpenXPKI::Server::Database::QueryBuilder->new( 73 driver => $self->driver, 74 sqlam => $self->sqlam, 75 $self->driver->namespace ? (namespace => $self->driver->namespace) : (), 76 ); 77 }, 78); 79 80has 'sqlam' => ( # SQL query builder 81 is => 'rw', 82 isa => 'SQL::Abstract::More', 83 lazy => 1, 84 default => sub { 85 my $self = shift; 86 my @return = $self->driver->sqlam_params; # use array to get list context 87 # tolerate different return values: undef, list, HashRef 88 return SQL::Abstract::More->new( 89 $self->_driver_return_val_to_hash( 90 \@return, 91 ref($self->driver)."::sqlam_params", 92 ) 93 ); 94 }, 95 # TODO Support Oracle 12c LIMIT syntax: OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY 96 # TODO Support LIMIT for other DBs by giving a custom sub to "limit_offset" 97); 98 99has 'db_version' => ( 100 is => 'rw', 101 isa => 'Str', 102 lazy => 1, 103 default => sub { 104 my $self = shift; 105 return $self->dbh->get_info($GetInfoType{SQL_DBMS_VER}); 106 }, 107); 108 109has '_dbix_handler' => ( 110 is => 'rw', 111 isa => 'DBIx::Handler', 112 lazy => 1, 113 builder => '_build_dbix_handler', 114 predicate => '_dbix_handler_initialized', # for test cases 115 handles => { 116 disconnect => 'disconnect', 117 }, 118); 119 120# stores the caller() information about the code that started a transaction 121has '_txn_starter' => ( 122 is => 'rw', 123 isa => 'Any', 124 clearer => '_clear_txn_starter', 125 predicate => 'in_txn', 126); 127 128################################################################################ 129# Builders 130# 131 132sub _build_driver { 133 my $self = shift; 134 my %args = %{$self->db_params}; # copy hash 135 136 # Remove undefined value (= empty option in originating config file) 137 for (keys %args) { 138 delete $args{$_} unless defined $args{$_}; 139 } 140 141 my $driver = $args{type}; 142 OpenXPKI::Exception->throw ( 143 message => "Parameter 'type' missing: it must equal the last part of a package in the OpenXPKI::Server::Database::Driver::* namespace.", 144 ) unless $driver; 145 delete $args{type}; 146 147 my $class = "OpenXPKI::Server::Database::Driver::".$driver; 148 ##! 32: "Trying to load driver class " . $class; 149 150 eval { use Module::Load 0.32; autoload($class) }; 151 OpenXPKI::Exception->throw ( 152 message => "Unable to require() database driver package", 153 params => { class_name => $class, message => $@ } 154 ) if $@; 155 156 my $instance; 157 eval { $instance = $class->new(%args) }; 158 OpenXPKI::Exception->throw ( 159 message => "Unable to instantiate database driver class", 160 params => { class_name => $class, message => $@ } 161 ) if $@; 162 163 OpenXPKI::Exception->throw ( 164 message => "Database driver class does not seem to be a Moose class", 165 params => { class_name => $class } 166 ) unless $instance->can('does'); 167 168 OpenXPKI::Exception->throw ( 169 message => "Database driver class does not consume role OpenXPKI::Server::Database::Role::Driver", 170 params => { class_name => $class } 171 ) unless $instance->does('OpenXPKI::Server::Database::Role::Driver'); 172 173 return $instance; 174} 175 176# Converts DBI errors into OpenXPKI exceptions 177sub _dbi_error_handler { 178 my ($self, $msg, $dbh, $more_details) = @_; 179 180 my $details = { 181 source => "?", 182 dbi_error => $dbh->errstr, 183 dsn => $self->driver->dbi_dsn, 184 user => $self->driver->user, 185 ref $more_details ? %$more_details : (), 186 }; 187 188 my $method = ""; 189 my $our_msg; 190 191 # original message is like: [class] [method] failed: [message] 192 if ($msg =~ m/^(?<class>[a-z:_]+)\s+(?<method>[^\(\s]+)/i) { 193 $details->{source} = sprintf("%s::%s", $+{class}, $+{method}); 194 $method = $+{method}; 195 } 196 197 $our_msg = "connection failed" if "connect" eq $method; 198 $our_msg = "preparing SQL query failed" if "prepare" eq $method; 199 $our_msg = "binding parameters to SQL statement failed" if "bind_params" eq $method; 200 $our_msg = "execution of SQL query failed" if "execute" eq $method; 201 202 OpenXPKI::Exception->throw( 203 message => "Database error" . ($our_msg ? ": $our_msg" : ""), 204 params => $details, 205 ); 206}; 207 208sub _build_dbix_handler { 209 my $self = shift; 210 ##! 4: "DSN: ".$self->driver->dbi_dsn 211 ##! 4: "User: ".($self->driver->user // '(none)') 212 my %params = $self->_driver_return_val_to_hash( 213 [ $self->driver->dbi_connect_params ], # driver might return a list so we enforce list context 214 ref($self->driver)."::dbi_connect_params", 215 ); 216 ##! 4: "Additional connect() attributes: " . join " | ", map { $_." = ".$params{$_} } keys %params 217 218 my %params_from_config; 219 if ($self->db_params->{driver} && ref $self->db_params->{driver} eq 'HASH') { 220 %params_from_config = %{$self->db_params->{driver}}; 221 } 222 223 my $dbix = DBIx::Handler->new( 224 $self->driver->dbi_dsn, 225 $self->driver->user, 226 $self->driver->passwd, 227 { 228 AutoCommit => $self->autocommit, 229 LongReadLen => 10_000_000, 230 RaiseError => 0, 231 PrintError => 0, 232 HandleError => sub { 233 my ($msg, $dbh, $retval) = @_; 234 $self->_dbi_error_handler($msg, $dbh); 235 }, 236 %params, 237 %params_from_config, 238 }, 239 { 240 on_connect_do => sub { 241 my $dbh = shift; 242 ##! 32: 'DBMS version: ' . $dbh->get_info($GetInfoType{SQL_DBMS_VER}); 243 # custom on_connect actions 244 $self->driver->on_connect($dbh); 245 # on_connect_do is (also) called after fork(): 246 # then we get a new DBI handle and a previous transaction is invalid 247 $self->_clear_txn_starter; 248 }, 249 } 250 ); 251 252 ##! 32: 'DBIx Handle ' . Dumper $dbix 253 254 return $dbix; 255} 256 257################################################################################ 258# Methods 259# 260 261sub _driver_return_val_to_hash { 262 my ($self, $params, $method) = @_; 263 my $normalized; 264 if (scalar @$params == 0) { # undef 265 $normalized = {}; 266 } 267 elsif (scalar @$params > 1) { # list 268 $normalized = { @$params }; 269 } 270 elsif (ref $params->[0] eq 'HASH') { # HashRef 271 $normalized = $params->[0]; 272 } 273 else { 274 OpenXPKI::Exception->throw ( 275 message => "Faulty driver implementation: '$method' did not return undef, a HashRef or a plain hash (list)", 276 ); 277 } 278 return %$normalized; 279} 280 281sub _driver_return_val_to_list { 282 my ($self, $params, $method) = @_; 283 my $normalized; 284 if (scalar @$params == 0) { # undef 285 $normalized = []; 286 } 287 elsif (ref $params->[0] eq 'ARRAY') { # ArrayRef 288 $normalized = $params->[0]; 289 } 290 elsif (scalar(grep { /.+/ } map { ref } @$params) > 0) { # some elements are not scalars 291 OpenXPKI::Exception->throw ( 292 message => "Faulty driver implementation: '$method' did not return undef, an ArrayRef or a plain list", 293 ); 294 } 295 else { # list of scalars (or single scalar) 296 $normalized = [ @$params ]; 297 } 298 return @$normalized; 299} 300 301sub dbh { 302 my $self = shift; 303 # If this is too slow due to DB pings, we could pass "no_ping" attribute to 304 # DBIx::Handler and copy the "fixup" code from DBIx::Connector::_fixup_run() 305 my $dbh = $self->_dbix_handler->dbh; # fork-safe DBI handle 306 $dbh->{FetchHashKeyName} = 'NAME_lc'; # enforce lowercase names 307 return $dbh; 308} 309 310sub ping { 311 my $self = shift; 312 return $self->_dbix_handler->dbh->ping(); 313} 314 315# Execute given query 316sub run { 317 my ($self, $query, $return_rownum) = positional_args(\@_, 318 { isa => 'OpenXPKI::Server::Database::Query|Str' }, 319 { isa => 'Bool', optional => 1, default => 0 }, 320 ); 321 my $query_string; 322 my $query_params; 323 if (ref $query) { 324 $query_string = $query->string; 325 $query_params = $query->params; 326 } 327 else { 328 $query_string = $query; 329 } 330 331 # pass extra info about $query_string to our error handler 332 local $self->dbh->{HandleError} = sub { 333 my ($msg, $dbh, $retval) = @_; 334 $self->_dbi_error_handler($msg, $dbh, { query => $query_string }); 335 }; 336 337 ##! 16: "Query: " . $query_string; 338 my $sth = $self->dbh->prepare($query_string); 339 # bind parameters via SQL::Abstract::More to do some magic 340 $self->sqlam->bind_params($sth, @{$query_params}) if $query_params; 341 342 $self->log->trace(sprintf "DB query: %s", $query_string) if $self->log->is_trace; 343 344 my $rownum = $sth->execute; 345 ##! 16: "$rownum rows affected" 346 347 return $return_rownum ? $rownum : $sth; 348} 349 350# SELECT 351# Returns: DBI statement handle 352sub select { 353 my $self = shift; 354 my $query = $self->query_builder->select(@_); 355 return $self->run($query); 356} 357 358# SELECT - return first row 359# Returns: DBI statement handle 360sub select_one { 361 my $self = shift; 362 return $self->select(@_)->fetchrow_hashref; 363} 364 365# SELECT - return all rows as list of arrays 366# Returns: ArrayRef[ArrayRef] 367sub select_arrays { 368 my $self = shift; 369 return $self->select(@_)->fetchall_arrayref([]); 370} 371 372# SELECT - return all rows as list of hashes 373# Returns: ArrayRef[HashRef] 374sub select_hashes { 375 my $self = shift; 376 return $self->select(@_)->fetchall_arrayref({}); 377} 378 379# SUB SELECT 380# Returns: reference (!) to an ArrayRef that has to be included into the query 381sub subselect { 382 my $self = shift; 383 return $self->query_builder->subselect(@_); 384} 385 386sub count { 387 my $self = shift; 388 my %query_param = @_; 389 390 for (qw(order_by limit offset)) { 391 delete $query_param{$_} if defined $query_param{$_}; 392 } 393 394 return $self->driver->count_rows($self, $self->query_builder->select(%query_param) ); 395} 396 397# INSERT 398# Returns: DBI statement handle 399sub insert { 400 my ($self, %params) = named_args(\@_, # OpenXPKI::MooseParams 401 into => { isa => 'Str' }, 402 values => { isa => 'HashRef' }, 403 ); 404 405 # Replace AUTO_ID with value of next_id() 406 for (keys %{ $params{values} }) { 407 $params{values}->{$_} = $self->next_id($params{into}) 408 if (ref $params{values}->{$_} eq "OpenXPKI::Server::Database::AUTOINCREMENT"); 409 } 410 411 my $query = $self->query_builder->insert(%params); 412 return $self->run($query, 1); # 1 = return number of affected rows 413} 414 415# UPDATE 416# Returns: DBI statement handle 417sub update { 418 my $self = shift; 419 my $query = $self->query_builder->update(@_); 420 return $self->run($query, 1); # 1 = return number of affected rows 421} 422 423# MERGE 424# Returns: DBI statement handle 425sub merge { 426 my ($self, %args) = named_args(\@_, # OpenXPKI::MooseParams 427 into => { isa => 'Str' }, 428 set => { isa => 'HashRef' }, 429 set_once => { isa => 'HashRef', optional => 1, default => {} }, 430 # The WHERE specification contains the primary key columns. 431 # In case of an INSERT these will be used as normal values. Therefore 432 # we only allow scalars as hash values (which are translated to AND 433 # connected "equals" conditions by SQL::Abstract::More). 434 where => { isa => 'HashRef[Value]' }, 435 ); 436 my $query = $self->driver->merge_query( 437 $self, 438 $self->query_builder->_add_namespace_to($args{into}), 439 $args{set}, 440 $args{set_once}, 441 $args{where}, 442 ); 443 return $self->run($query, 1); # 1 = return number of affected rows 444} 445 446# DELETE 447# Returns: DBI statement handle 448sub delete { 449 my $self = shift; 450 my $query = $self->query_builder->delete(@_); 451 return $self->run($query, 1); # 1 = return number of affected rows 452} 453 454# 455sub _run_and_commit { 456 my ($self, $method, @args) = @_; 457 $self->start_txn unless $self->autocommit; 458 my $result = $self->$method(@args); 459 $self->commit unless $self->autocommit; 460 return $result; 461} 462 463# 464sub insert_and_commit { shift->_run_and_commit("insert", @_); } 465sub update_and_commit { shift->_run_and_commit("update", @_); } 466sub merge_and_commit { shift->_run_and_commit("merge", @_); } 467sub delete_and_commit { shift->_run_and_commit("delete", @_); } 468 469# Create a new insert ID ("serial") 470sub next_id { 471 my ($self, $table) = @_; 472 473 # get new serial number from DBMS (SQL sequence or emulation via table) 474 475 my $seq_table = $self->query_builder->_add_namespace_to("seq_$table"); 476 my $id_int = $self->driver->next_id($self, $seq_table ); 477 my $id = Math::BigInt->new($id_int); 478 ##! 32: 'Next ID: ' . $id->bstr() 479 480 # shift bitwise left and add server id (default: 255) 481 my $nodeid_bits = $self->db_params->{server_shift} // 8; 482 my $nodeid = $self->db_params->{server_id} // 2 ** $nodeid_bits - 1; 483 $id->blsft($nodeid_bits); 484 $id->bior(Math::BigInt->new($nodeid)); 485 486 ##! 32: 'Next ID - after bitshift: ' . $id->bstr() 487 return $id->bstr(); 488} 489 490# Create a new sequence 491sub create_sequence { 492 my ($self, $table) = @_; 493 my $seq_table = $self->query_builder->_add_namespace_to("seq_$table"); 494 my $query = $self->driver->sequence_create_query($self, $seq_table); 495 return $self->run($query, 0); 496} 497 498# Drop a sequence 499sub drop_sequence { 500 my ($self, $table) = @_; 501 my $seq_table = $self->query_builder->_add_namespace_to("seq_$table"); 502 my $query = $self->driver->sequence_drop_query($self, $seq_table); 503 return $self->run($query, 0); 504} 505 506# Drop a table 507sub drop_table { 508 my ($self, $table) = @_; 509 my $query = $self->driver->table_drop_query($self, $self->query_builder->_add_namespace_to($table)); 510 return $self->run($query, 0); 511} 512 513sub start_txn { 514 my $self = shift; 515 return $self->log->warn("AutoCommit is on, start_txn() is useless") 516 if $self->autocommit; 517 518 # we have to enforce the actual DB connection which clears 519 # $self->_txn_starter at this point as otherwise the very first commit() 520 # $self->_txn_starter and think there is no running transaction. 521 $self->ping; 522 523 my $caller = [ caller ]; 524 if ($self->in_txn) { 525 $self->log->debug( 526 sprintf "transaction start requested during a running transaction (started in %s:%i) in %s:%i", 527 $caller->[0], 528 $caller->[2], 529 $self->_txn_starter->[0], 530 $self->_txn_starter->[2], 531 ) if $self->log->is_debug; 532 } 533 ##! 16: "Transaction start" 534 $self->_txn_starter($caller); 535 536 $self->log->trace(sprintf "transaction start in %s:%i", $caller->[0], $caller->[2]) 537 if $self->log->is_trace; 538} 539 540sub commit { 541 my $self = shift; 542 return $self->log->warn("AutoCommit is on, commit() is useless") 543 if $self->autocommit; 544 545 my $caller = [ caller ]; 546 if ($self->in_txn) { 547 $self->log->trace( 548 sprintf "commit for txn (started at %s:%i) in %s:%i", 549 $self->_txn_starter->[0], $self->_txn_starter->[2], 550 $caller->[0], $caller->[2] 551 ) if $self->log->is_trace; 552 } 553 else { 554 $self->log->debug( 555 sprintf "commit was requested without prior transaction start in %s:%i", 556 $caller->[0], $caller->[2] 557 ) if $self->log->is_debug; 558 } 559 560 ##! 16: "Commit" 561 $self->dbh->commit; 562 $self->_clear_txn_starter; 563} 564 565sub rollback { 566 my $self = shift; 567 return $self->log->warn("AutoCommit is on, rollback() is useless") if $self->autocommit; 568 569 my $caller = [ caller ]; 570 571 if ($self->in_txn) { 572 if ($self->log->is_trace) { 573 $self->log->trace( 574 sprintf "rollback for txn (started at %s:%i) in %s:%i", 575 $self->_txn_starter->[0], $self->_txn_starter->[2], 576 $caller->[0], $caller->[2] 577 ) if $self->log->is_trace; 578 } 579 } 580 else { 581 $self->log->debug( 582 sprintf "rollback was requested without prior transaction start in %s:%i", 583 $caller->[0], $caller->[2] 584 ) if $self->log->is_debug; 585 } 586 587 ##! 16: "Rollback of changes" 588 $self->dbh->rollback; 589 $self->_clear_txn_starter; 590} 591 592__PACKAGE__->meta->make_immutable; 593 594=head1 Description 595 596This class contains the API to interact with the configured OpenXPKI database. 597 598=head2 Database drivers 599 600While OpenXPKI supports several database types out of the box it still allows 601you to include new DBMS specific drivers without the need to change existing 602code. 603 604For more details see L<OpenXPKI::Server::Database::Role::Driver>. 605 606=head2 Class structure 607 608=cut 609 610# The diagram was drawn using App::Asciio 611 612=pod 613 614 .----------------------------. 615 .----| OpenXPKI::Server::Database |---.--------------------. 616 | '----------------------------' | | 617 | | | | 618 | | v v 619 | | .---------------------. .---------------. 620 | .-----' | SQL::Abstract::More | | DBIx::Handler | 621 | | '---------------------' '---------------' 622 | | . 623 | v injected 624 | .---------------------. . 625 | | O:S:D::QueryBuilder |<...........' 626 | '---------------------' 627 | | .--------------. 628 | '---->| O:S:D::Query | 629 | '--------------' 630 | 631 | .------------------. 632 '->| O:S:D::Driver::* | 633 '------------------' 634 . 635 consumes 636 . .---------------------. 637 ....>| O:S:D::Role::Driver | 638 . '---------------------' 639 . .------------------------------. .--------------------------------. 640 ....>| O:S:D::Role::SequenceSupport | or | O:S:D::Role::SequenceEmulation | 641 . '------------------------------' '--------------------------------' 642 . .------------------------------. .--------------------------------. 643 '...>| O:S:D::Role::MergeSupport | or | O:S:D::Role::MergeEmulation | 644 '------------------------------' '--------------------------------' 645 646=head1 Attributes 647 648=head2 Constructor parameters 649 650=over 651 652=item * B<log> - Log object (I<OpenXPKI::Server::Log>, required) 653 654=item * B<db_params> - I<HashRef> with parameters for the DBI data source name 655string (required). 656 657Required keys in this hash: 658 659=over 660 661=item * B<type> - last part of a package in the C<OpenXPKI::Server::Database::Driver::*> namespace. (I<Str>, required) 662 663=item * Any of the L<OpenXPKI::Server::Database::Role::Driver/Constructor parameters> 664 665=item * Additional parameters required by the specific driver 666 667=back 668 669=item * B<autocommit> - I<Bool> to switch on L<DBI/AutoCommit> (optional, default: 0) 670 671=back 672 673=head2 Others 674 675=over 676 677=item * B<driver> - database specific driver instance (consumer of L<OpenXPKI::Server::Database::Role::Driver>) 678 679=item * B<query_builder> - OpenXPKI query builder to create abstract SQL queries (L<OpenXPKI::Server::Database::QueryBuilder>) 680 681Usage: 682 683 # returns an OpenXPKI::Server::Database::Query object 684 my $query = $db->query_builder->select( 685 from => 'certificate', 686 columns => [ 'identifier' ], 687 where => { pki_realm => 'democa' }, 688 ); 689 690=item * B<db_version> - database version, equals the result of C<$dbh-E<gt>get_version(...)> (I<Str>) 691 692=item * B<sqlam> - low level SQL query builder (internal work horse, an instance of L<SQL::Abstract::More>) 693 694=back 695 696=head1 Methods 697 698Note: all methods might throw an L<OpenXPKI::Exception> if there are errors in the query or during it's execution. 699 700=head2 new 701 702Constructor. 703 704Named parameters: see L<attributes section above|/"Constructor parameters">. 705 706 707 708=head2 select 709 710Selects rows from the database and returns the results as a I<DBI::st> statement 711handle. 712 713Please note that C<NULL> values will be converted to Perl C<undef>. 714 715Subqueries can be realized using L</subselect>. 716 717Named parameters: 718 719=over 720 721=item * B<columns> - List of column names (I<ArrayRef[Str]>, required) 722 723=item * B<from> - Table name (or list of) (I<Str | ArrayRef[Str]>, required) 724 725=item * B<from_join> - A B<string> to describe table relations for FROM .. JOIN following the spec in L<SQL::Abstract::More/join> (I<Str>) 726 727 from_join => "certificate req_key=req_key csr" 728 729Please note that you cannot specify C<from> and C<from_join> at the same time. 730 731=item * B<where> - WHERE clause following the spec in L<SQL::Abstract/WHERE-CLAUSES> (I<Str | ArrayRef | HashRef>) 732 733=item * B<group_by> - GROUP BY column (or list of) (I<Str | ArrayRef>) 734 735=item * B<having> - HAVING clause following the spec in L<SQL::Abstract/WHERE-CLAUSES> (I<Str | ArrayRef | HashRef>) 736 737=item * B<order_by> - Plain ORDER BY string or list of columns. Each column name can be preceded by a "-" for descending sort (I<Str | ArrayRef>) 738 739=item * B<limit> - (I<Int>) 740 741=item * B<offset> - (I<Int>) 742 743=back 744 745 746 747=head2 subselect 748 749Builds a subquery to be used within another query and returns a reference to an I<ArrayRef>. 750 751The returned structure is understood by L<SQL::Abstract|SQL::Abstract/Literal_SQL_with_placeholders_and_bind_values_(subqueries)> which is used internally. 752 753E.g. to create the following query: 754 755 SELECT title FROM books 756 WHERE ( 757 author_id IN ( 758 SELECT id FROM authors 759 WHERE ( legs > 2 ) 760 ) 761 ) 762 763you can use C<subselect()> as follows: 764 765 CTX('dbi')->select( 766 from => "books", 767 columns => [ "title" ], 768 where => { 769 author_id => CTX('dbi')->subselect("IN" => { 770 from => "authors", 771 columns => [ "id" ], 772 where => { legs => { '>' => 2 } }, 773 }), 774 }, 775 ); 776 777Positional parameters: 778 779=over 780 781=item * B<$operator> - SQL operator between column and subquery (I<Str>, required). 782 783Operators can be e.g. C<'IN'>, C<'NOT IN'>, C<'E<gt> MAX'> or C<'E<lt> ALL'>. 784 785=item * B<$query> - The query parameters in a I<HashRef> as they would be given to L</select> (I<HashRef>, required) 786 787=back 788 789 790 791=head2 select_one 792 793Selects one row from the database and returns the results as a I<HashRef> 794(column name => value) by calling C<$sth-E<gt>fetchrow_hashref>. 795 796For parameters see L</select>. 797 798Returns C<undef> if the query had no results. 799 800Please note that C<NULL> values will be converted to Perl C<undef>. 801 802 803 804=head2 select_arrays 805 806Selects all rows from the database and returns them as an I<ArrayRef[ArrayRef]>. 807This is a shortcut to C<$dbi-E<gt>select(...)-E<gt>fetchall_arrayref([])>. 808 809For parameters see L</select>. 810 811Please note that C<NULL> values will be converted to Perl C<undef>. 812 813 814 815=head2 select_hashes 816 817Selects all rows from the database and returns them as an I<ArrayRef[HashRef]>. 818This is a shortcut to C<$dbi-E<gt>select(...)-E<gt>fetchall_arrayref({})>. 819 820For parameters see L</select>. 821 822Please note that C<NULL> values will be converted to Perl C<undef>. 823 824 825 826=head2 count 827 828Takes the same arguments as L</select>, wraps them into a subquery and 829return the number of rows the select would return. The parameters 830C<order_by>, C<limit> and C<offset> are ignored. 831 832 833 834=head2 insert 835 836Inserts rows into the database and returns the number of affected rows. 837 838 $db->insert( 839 into => "certificate", 840 values => { 841 identifier => AUTO_ID, # use the sequence associated with this table 842 cert_key => $key, 843 ... 844 } 845 ); 846 847To automatically set a primary key to the next serial number (i.e. sequence 848associated with this table) set it to C<AUTO_ID>. You need to C<use OpenXPKI::Server::Database;> 849to be able to use C<AUTO_ID>. 850 851Named parameters: 852 853=over 854 855=item * B<into> - Table name (I<Str>, required) 856 857=item * B<values> - Hash with column name / value pairs. Please note that 858C<undef> is interpreted as C<NULL> (I<HashRef>, required). 859 860=back 861 862 863 864=head2 update 865 866Updates rows in the database and returns the number of affected rows. 867 868A WHERE clause is required to prevent accidential updates of all rows in a table. 869 870Please note that C<NULL> values will be converted to Perl C<undef>. 871 872Named parameters: 873 874=over 875 876=item * B<table> - Table name (I<Str>, required) 877 878=item * B<set> - Hash with column name / value pairs. Please note that C<undef> is interpreted as C<NULL> (I<HashRef>, required) 879 880=item * B<where> - WHERE clause following the spec in L<SQL::Abstract/WHERE-CLAUSES> (I<Str | ArrayRef | HashRef>) 881 882=back 883 884 885 886=head2 merge 887 888Either directly executes or emulates an SQL MERGE (you could also call it 889REPLACE) function and returns the number of affected rows. 890 891Please note that e.g. MySQL returns 2 (not 1) if an update was performed. So 892you should only use the return value to test for 0 / FALSE. 893 894Named parameters: 895 896=over 897 898=item * B<into> - Table name (I<Str>, required) 899 900=item * B<set> - Columns that are always set (INSERT or UPDATE). Hash with 901column name / value pairs. 902 903Please note that C<undef> is interpreted as C<NULL> (I<HashRef>, required) 904 905=item * B<set_once> - Columns that are only set on INSERT (additional to those 906in the C<where> parameter. Hash with column name / value pairs. 907 908Please note that C<undef> is interpreted as C<NULL> (I<HashRef>, required) 909 910=item * B<where> - WHERE clause specification that must contain the PRIMARY KEY 911columns and only allows "AND" and "equal" operators: 912C<<{ col1 => val1, col2 => val2 }>> (I<HashRef>) 913 914The values from the WHERE clause are also inserted if the row does not exist 915(together with those from C<set_once>)! 916 917=back 918 919 920 921=head2 delete 922 923Deletes rows in the database and returns the results as a I<DBI::st> statement 924handle. 925 926To prevent accidential deletion of all rows of a table you must specify 927parameter C<all> if you want to do that: 928 929 CTX('dbi')->delete( 930 from => "mytab", 931 all => 1, 932 ); 933 934Named parameters: 935 936=over 937 938=item * B<from> - Table name (I<Str>, required) 939 940=item * B<where> - WHERE clause following the spec in L<SQL::Abstract/WHERE-CLAUSES> (I<Str | ArrayRef | HashRef>) 941 942=item * B<all> - Set this to 1 instead of specifying C<where> to delete all rows (I<Bool>) 943 944=back 945 946 947 948=head2 start_txn 949 950Records the start of a new transaction (i.e. sets a flag) without database 951interaction. 952 953If the flag was already set (= another transaction is running), a C<ROLLBACK> is 954performed first and an error message is logged. 955 956Please note that after a C<fork()> the flag is be reset as the C<DBI> handle 957is also reset (so there cannot be a running transaction). 958 959=head2 in_txn 960 961Returns C<true> if a transaction is currently running, i.e. after L</start_txn> 962was called but before L</commit> or L</rollback> where called. 963 964=head2 commit 965 966Commits a transaction. 967 968Logs an error if L</start_txn> was not called first. 969 970=head2 rollback 971 972Rolls back a transaction. 973 974Logs an error if L</start_txn> was not called first. 975 976=cut 977 978=head2 insert_and_commit 979 980Calling this method is the same as: 981 982 $db->start_txn; 983 $db->insert(...); 984 $db->commit; 985 986For more informations see L<OpenXPKI::Server::Database/insert>. 987 988=head2 update_and_commit 989 990Calling this method is the same as: 991 992 $db->start_txn; 993 $db->update(...); 994 $db->commit; 995 996For more informations see L<OpenXPKI::Server::Database/update>. 997 998=head2 merge_and_commit 999 1000Calling this method is the same as: 1001 1002 $db->start_txn; 1003 $db->merge(...); 1004 $db->commit; 1005 1006For more informations see L<OpenXPKI::Server::Database/merge>. 1007 1008=head2 delete_and_commit 1009 1010Calling this method is the same as: 1011 1012 $db->start_txn; 1013 $db->delete(...); 1014 $db->commit; 1015 1016For more informations see L<OpenXPKI::Server::Database/delete>. 1017 1018################################################################################ 1019 1020=head1 Low level methods 1021 1022The following methods allow more fine grained control over the query processing. 1023 1024=head2 dbh 1025 1026Returns a fork safe DBI handle. Connects to the database if neccessary. 1027 1028To remain fork safe DO NOT CACHE this (also do not convert into a lazy attribute). 1029 1030=head2 run 1031 1032Executes the given query and returns a DBI statement handle. Throws an exception 1033in case of errors. 1034 1035 my $sth; 1036 eval { 1037 $sth = $db->run($query); 1038 }; 1039 if (my $e = OpenXPKI::Exception->caught) { 1040 die "OpenXPKI exception executing query: $e"; 1041 } 1042 elsif ($@) { 1043 die "Unknown error: $e"; 1044 }; 1045 1046Parameters: 1047 1048=over 1049 1050=item * B<$query> - query to run (either a I<OpenXPKI::Server::Database::Query> 1051or a literal SQL string) 1052 1053=item * B<$return_rownum> - return number of affected rows instead of DBI 1054statement handle (optional, default: 0). 1055 1056If no rows were affected, then "0E0" is returned which Perl will treat as 0 but 1057will regard as true. 1058 1059=back 1060 1061=head2 disconnect 1062 1063Disconnects from the database. Might be useful to e.g. remove file locks when 1064using SQLite. 1065 1066=cut 1067