1# $Header: /home/jesse/DBIx-SearchBuilder/history/SearchBuilder/Handle.pm,v 1.21 2002/01/28 06:11:37 jesse Exp $ 2package DBIx::SearchBuilder::Handle; 3 4use strict; 5use warnings; 6 7use Carp qw(croak cluck); 8use DBI; 9use Class::ReturnValue; 10use Encode qw(); 11 12use DBIx::SearchBuilder::Util qw/ sorted_values /; 13 14use vars qw(@ISA %DBIHandle $PrevHandle $DEBUG %TRANSDEPTH %TRANSROLLBACK %FIELDS_IN_TABLE); 15 16 17=head1 NAME 18 19DBIx::SearchBuilder::Handle - Perl extension which is a generic DBI handle 20 21=head1 SYNOPSIS 22 23 use DBIx::SearchBuilder::Handle; 24 25 my $handle = DBIx::SearchBuilder::Handle->new(); 26 $handle->Connect( Driver => 'mysql', 27 Database => 'dbname', 28 Host => 'hostname', 29 User => 'dbuser', 30 Password => 'dbpassword'); 31 # now $handle isa DBIx::SearchBuilder::Handle::mysql 32 33=head1 DESCRIPTION 34 35This class provides a wrapper for DBI handles that can also perform a number of additional functions. 36 37=cut 38 39 40 41=head2 new 42 43Generic constructor 44 45=cut 46 47sub new { 48 my $proto = shift; 49 my $class = ref($proto) || $proto; 50 my $self = {}; 51 bless ($self, $class); 52 53 @{$self->{'StatementLog'}} = (); 54 return $self; 55} 56 57 58 59=head2 Connect PARAMHASH: Driver, Database, Host, User, Password 60 61Takes a paramhash and connects to your DBI datasource. 62 63You should _always_ set 64 65 DisconnectHandleOnDestroy => 1 66 67unless you have a legacy app like RT2 or RT 3.0.{0,1,2} that depends on the broken behaviour. 68 69If you created the handle with 70 DBIx::SearchBuilder::Handle->new 71and there is a DBIx::SearchBuilder::Handle::(Driver) subclass for the driver you have chosen, 72the handle will be automatically "upgraded" into that subclass. 73 74=cut 75 76sub Connect { 77 my $self = shift; 78 my %args = ( 79 Driver => undef, 80 Database => undef, 81 Host => undef, 82 SID => undef, 83 Port => undef, 84 User => undef, 85 Password => undef, 86 RequireSSL => undef, 87 DisconnectHandleOnDestroy => undef, 88 @_ 89 ); 90 91 if ( $args{'Driver'} && !$self->isa( __PACKAGE__ .'::'. $args{'Driver'} ) ) { 92 return $self->Connect( %args ) if $self->_UpgradeHandle( $args{'Driver'} ); 93 } 94 95 # Setting this actually breaks old RT versions in subtle ways. 96 # So we need to explicitly call it 97 $self->{'DisconnectHandleOnDestroy'} = $args{'DisconnectHandleOnDestroy'}; 98 99 my $old_dsn = $self->DSN || ''; 100 my $new_dsn = $self->BuildDSN( %args ); 101 102 # Only connect if we're not connected to this source already 103 return undef if $self->dbh && $self->dbh->ping && $new_dsn eq $old_dsn; 104 105 my $handle = DBI->connect( 106 $new_dsn, $args{'User'}, $args{'Password'} 107 ) or croak "Connect Failed $DBI::errstr\n"; 108 109 # databases do case conversion on the name of columns returned. 110 # actually, some databases just ignore case. this smashes it to something consistent 111 $handle->{FetchHashKeyName} ='NAME_lc'; 112 113 # Set the handle 114 $self->dbh($handle); 115 116 # Cache version info 117 $self->DatabaseVersion; 118 119 return 1; 120} 121 122 123=head2 _UpgradeHandle DRIVER 124 125This private internal method turns a plain DBIx::SearchBuilder::Handle into one 126of the standard driver-specific subclasses. 127 128=cut 129 130sub _UpgradeHandle { 131 my $self = shift; 132 133 my $driver = shift; 134 my $class = 'DBIx::SearchBuilder::Handle::' . $driver; 135 local $@; 136 eval "require $class"; 137 return if $@; 138 139 bless $self, $class; 140 return 1; 141} 142 143 144=head2 BuildDSN PARAMHASH 145 146Takes a bunch of parameters: 147 148Required: Driver, Database, 149Optional: Host, Port and RequireSSL 150 151Builds a DSN suitable for a DBI connection 152 153=cut 154 155sub BuildDSN { 156 my $self = shift; 157 my %args = ( 158 Driver => undef, 159 Database => undef, 160 Host => undef, 161 Port => undef, 162 SID => undef, 163 RequireSSL => undef, 164 @_ 165 ); 166 167 my $dsn = "dbi:$args{'Driver'}:dbname=$args{'Database'}"; 168 $dsn .= ";sid=$args{'SID'}" if $args{'SID'}; 169 $dsn .= ";host=$args{'Host'}" if $args{'Host'}; 170 $dsn .= ";port=$args{'Port'}" if $args{'Port'}; 171 $dsn .= ";requiressl=1" if $args{'RequireSSL'}; 172 173 return $self->{'dsn'} = $dsn; 174} 175 176 177=head2 DSN 178 179Returns the DSN for this database connection. 180 181=cut 182 183sub DSN { 184 return shift->{'dsn'}; 185} 186 187 188 189=head2 RaiseError [MODE] 190 191Turns on the Database Handle's RaiseError attribute. 192 193=cut 194 195sub RaiseError { 196 my $self = shift; 197 198 my $mode = 1; 199 $mode = shift if (@_); 200 201 $self->dbh->{RaiseError}=$mode; 202} 203 204 205 206 207=head2 PrintError [MODE] 208 209Turns on the Database Handle's PrintError attribute. 210 211=cut 212 213sub PrintError { 214 my $self = shift; 215 216 my $mode = 1; 217 $mode = shift if (@_); 218 219 $self->dbh->{PrintError}=$mode; 220} 221 222 223 224=head2 LogSQLStatements BOOL 225 226Takes a boolean argument. If the boolean is true, SearchBuilder will log all SQL 227statements, as well as their invocation times and execution times. 228 229Returns whether we're currently logging or not as a boolean 230 231=cut 232 233sub LogSQLStatements { 234 my $self = shift; 235 if (@_) { 236 require Time::HiRes; 237 $self->{'_DoLogSQL'} = shift; 238 } 239 return ($self->{'_DoLogSQL'}); 240} 241 242=head2 _LogSQLStatement STATEMENT DURATION 243 244Add an SQL statement to our query log 245 246=cut 247 248sub _LogSQLStatement { 249 my $self = shift; 250 my $statement = shift; 251 my $duration = shift; 252 my @bind = @_; 253 push @{$self->{'StatementLog'}} , ([Time::HiRes::time(), $statement, [@bind], $duration, Carp::longmess("Executed SQL query")]); 254 255} 256 257=head2 ClearSQLStatementLog 258 259Clears out the SQL statement log. 260 261 262=cut 263 264sub ClearSQLStatementLog { 265 my $self = shift; 266 @{$self->{'StatementLog'}} = (); 267} 268 269 270=head2 SQLStatementLog 271 272Returns the current SQL statement log as an array of arrays. Each entry is a triple of 273 274(Time, Statement, Duration) 275 276=cut 277 278sub SQLStatementLog { 279 my $self = shift; 280 return (@{$self->{'StatementLog'}}); 281 282} 283 284 285 286=head2 AutoCommit [MODE] 287 288Turns on the Database Handle's AutoCommit attribute. 289 290=cut 291 292sub AutoCommit { 293 my $self = shift; 294 295 my $mode = 1; 296 $mode = shift if (@_); 297 298 $self->dbh->{AutoCommit}=$mode; 299} 300 301 302 303 304=head2 Disconnect 305 306Disconnect from your DBI datasource 307 308=cut 309 310sub Disconnect { 311 my $self = shift; 312 my $dbh = $self->dbh; 313 return unless $dbh; 314 $self->Rollback(1); 315 316 my $ret = $dbh->disconnect; 317 318 # DBD::mysql with MariaDB 10.2+ could cause segment faults when 319 # interacting with a disconnected handle, here we unset 320 # dbh to inform other code that there is no connection any more. 321 # See also https://github.com/perl5-dbi/DBD-mysql/issues/306 322 323 if ( $self->isa('DBIx::SearchBuilder::Handle::mysql') 324 && $self->{'database_version'} =~ /mariadb/i 325 && $self->{'database_version'} ge '10.2' ) 326 { 327 $self->dbh(undef); 328 } 329 330 return $ret; 331} 332 333 334=head2 dbh [HANDLE] 335 336Return the current DBI handle. If we're handed a parameter, make the database handle that. 337 338=cut 339 340# allow use of Handle as a synonym for DBH 341*Handle=\&dbh; 342 343sub dbh { 344 my $self=shift; 345 346 #If we are setting the database handle, set it. 347 if ( @_ ) { 348 $DBIHandle{$self} = $PrevHandle = shift; 349 %FIELDS_IN_TABLE = (); 350 } 351 352 return($DBIHandle{$self} ||= $PrevHandle); 353} 354 355 356=head2 Insert $TABLE_NAME @KEY_VALUE_PAIRS 357 358Takes a table name and a set of key-value pairs in an array. 359Splits the key value pairs, constructs an INSERT statement 360and performs the insert. 361 362Base class return statement handle object, while DB specific 363subclass should return row id. 364 365=cut 366 367sub Insert { 368 my $self = shift; 369 return $self->SimpleQuery( $self->InsertQueryString(@_) ); 370} 371 372=head2 InsertQueryString $TABLE_NAME @KEY_VALUE_PAIRS 373 374Takes a table name and a set of key-value pairs in an array. 375Splits the key value pairs, constructs an INSERT statement 376and returns query string and set of bind values. 377 378This method is more useful for subclassing in DB specific 379handles. L</Insert> method is preferred for end users. 380 381=cut 382 383sub InsertQueryString { 384 my($self, $table, @pairs) = @_; 385 my(@cols, @vals, @bind); 386 387 while ( my $key = shift @pairs ) { 388 push @cols, $key; 389 push @vals, '?'; 390 push @bind, shift @pairs; 391 } 392 393 my $QueryString = "INSERT INTO $table"; 394 $QueryString .= " (". join(", ", @cols) .")"; 395 $QueryString .= " VALUES (". join(", ", @vals). ")"; 396 return ($QueryString, @bind); 397} 398 399=head2 InsertFromSelect 400 401Takes table name, array reference with columns, select query 402and list of bind values. Inserts data select by the query 403into the table. 404 405To make sure call is portable every column in result of 406the query should have unique name or should be aliased. 407See L<DBIx::SearchBuilder::Handle::Oracle/InsertFromSelect> for 408details. 409 410=cut 411 412sub InsertFromSelect { 413 my ($self, $table, $columns, $query, @binds) = @_; 414 415 $columns = join ', ', @$columns 416 if $columns; 417 418 my $full_query = "INSERT INTO $table"; 419 $full_query .= " ($columns)" if $columns; 420 $full_query .= ' '. $query; 421 my $sth = $self->SimpleQuery( $full_query, @binds ); 422 return $sth unless $sth; 423 424 my $rows = $sth->rows; 425 return $rows == 0? '0E0' : $rows; 426} 427 428=head2 UpdateRecordValue 429 430Takes a hash with fields: Table, Column, Value PrimaryKeys, and 431IsSQLFunction. Table, and Column should be obvious, Value is where you 432set the new value you want the column to have. The primary_keys field should 433be the lvalue of DBIx::SearchBuilder::Record::PrimaryKeys(). Finally 434IsSQLFunction is set when the Value is a SQL function. For example, you 435might have ('Value'=>'PASSWORD(string)'), by setting IsSQLFunction that 436string will be inserted into the query directly rather then as a binding. 437 438=cut 439 440sub UpdateRecordValue { 441 my $self = shift; 442 my %args = ( Table => undef, 443 Column => undef, 444 IsSQLFunction => undef, 445 PrimaryKeys => undef, 446 @_ ); 447 448 my @bind = (); 449 my $query = 'UPDATE ' . $args{'Table'} . ' '; 450 $query .= 'SET ' . $args{'Column'} . '='; 451 452 ## Look and see if the field is being updated via a SQL function. 453 if ($args{'IsSQLFunction'}) { 454 $query .= $args{'Value'} . ' '; 455 } 456 else { 457 $query .= '? '; 458 push (@bind, $args{'Value'}); 459 } 460 461 ## Constructs the where clause. 462 my $where = 'WHERE '; 463 foreach my $key (sort keys %{$args{'PrimaryKeys'}}) { 464 $where .= $key . "=?" . " AND "; 465 push (@bind, $args{'PrimaryKeys'}{$key}); 466 } 467 $where =~ s/AND\s$//; 468 469 my $query_str = $query . $where; 470 return ($self->SimpleQuery($query_str, @bind)); 471} 472 473 474 475 476=head2 UpdateTableValue TABLE COLUMN NEW_VALUE RECORD_ID IS_SQL 477 478Update column COLUMN of table TABLE where the record id = RECORD_ID. if IS_SQL is set, 479don\'t quote the NEW_VALUE 480 481=cut 482 483sub UpdateTableValue { 484 my $self = shift; 485 486 ## This is just a wrapper to UpdateRecordValue(). 487 my %args = (); 488 $args{'Table'} = shift; 489 $args{'Column'} = shift; 490 $args{'Value'} = shift; 491 $args{'PrimaryKeys'} = shift; 492 $args{'IsSQLFunction'} = shift; 493 494 return $self->UpdateRecordValue(%args) 495} 496 497=head1 SimpleUpdateFromSelect 498 499Takes table name, hash reference with (column, value) pairs, 500select query and list of bind values. 501 502Updates the table, but only records with IDs returned by the 503selected query, eg: 504 505 UPDATE $table SET %values WHERE id IN ( $query ) 506 507It's simple as values are static and search only allowed 508by id. 509 510=cut 511 512sub SimpleUpdateFromSelect { 513 my ($self, $table, $values, $query, @query_binds) = @_; 514 515 my @columns; my @binds; 516 for my $k (sort keys %$values) { 517 push @columns, $k; 518 push @binds, $values->{$k}; 519 } 520 521 my $full_query = "UPDATE $table SET "; 522 $full_query .= join ', ', map "$_ = ?", @columns; 523 $full_query .= ' WHERE id IN ('. $query .')'; 524 my $sth = $self->SimpleQuery( $full_query, @binds, @query_binds ); 525 return $sth unless $sth; 526 527 my $rows = $sth->rows; 528 return $rows == 0? '0E0' : $rows; 529} 530 531=head1 DeleteFromSelect 532 533Takes table name, select query and list of bind values. 534 535Deletes from the table, but only records with IDs returned by the 536select query, eg: 537 538 DELETE FROM $table WHERE id IN ($query) 539 540=cut 541 542sub DeleteFromSelect { 543 my ($self, $table, $query, @binds) = @_; 544 my $sth = $self->SimpleQuery( 545 "DELETE FROM $table WHERE id IN ($query)", 546 @binds 547 ); 548 return $sth unless $sth; 549 550 my $rows = $sth->rows; 551 return $rows == 0? '0E0' : $rows; 552} 553 554=head2 SimpleQuery QUERY_STRING, [ BIND_VALUE, ... ] 555 556Execute the SQL string specified in QUERY_STRING 557 558=cut 559 560sub SimpleQuery { 561 my $self = shift; 562 my $QueryString = shift; 563 my @bind_values; 564 @bind_values = (@_) if (@_); 565 566 my $sth = $self->dbh->prepare($QueryString); 567 unless ($sth) { 568 if ($DEBUG) { 569 die "$self couldn't prepare the query '$QueryString'" 570 . $self->dbh->errstr . "\n"; 571 } 572 else { 573 warn "$self couldn't prepare the query '$QueryString'" 574 . $self->dbh->errstr . "\n"; 575 my $ret = Class::ReturnValue->new(); 576 $ret->as_error( 577 errno => '-1', 578 message => "Couldn't prepare the query '$QueryString'." 579 . $self->dbh->errstr, 580 do_backtrace => undef 581 ); 582 return ( $ret->return_value ); 583 } 584 } 585 586 # Check @bind_values for HASH refs 587 for ( my $bind_idx = 0 ; $bind_idx < scalar @bind_values ; $bind_idx++ ) { 588 if ( ref( $bind_values[$bind_idx] ) eq "HASH" ) { 589 my $bhash = $bind_values[$bind_idx]; 590 $bind_values[$bind_idx] = $bhash->{'value'}; 591 delete $bhash->{'value'}; 592 $sth->bind_param( $bind_idx + 1, undef, $bhash ); 593 } 594 } 595 596 my $basetime; 597 if ( $self->LogSQLStatements ) { 598 $basetime = Time::HiRes::time(); 599 } 600 my $executed; 601 { 602 no warnings 'uninitialized' ; # undef in bind_values makes DBI sad 603 eval { $executed = $sth->execute(@bind_values) }; 604 } 605 if ( $self->LogSQLStatements ) { 606 $self->_LogSQLStatement( $QueryString, Time::HiRes::time() - $basetime, @bind_values ); 607 } 608 609 if ( $@ or !$executed ) { 610 if ($DEBUG) { 611 die "$self couldn't execute the query '$QueryString'" 612 . $self->dbh->errstr . "\n"; 613 614 } 615 else { 616 cluck "$self couldn't execute the query '$QueryString'"; 617 618 my $ret = Class::ReturnValue->new(); 619 $ret->as_error( 620 errno => '-1', 621 message => "Couldn't execute the query '$QueryString'" 622 . $self->dbh->errstr, 623 do_backtrace => undef 624 ); 625 return ( $ret->return_value ); 626 } 627 628 } 629 return ($sth); 630 631} 632 633 634 635=head2 FetchResult QUERY, [ BIND_VALUE, ... ] 636 637Takes a SELECT query as a string, along with an array of BIND_VALUEs 638If the select succeeds, returns the first row as an array. 639Otherwise, returns a Class::ResturnValue object with the failure loaded 640up. 641 642=cut 643 644sub FetchResult { 645 my $self = shift; 646 my $query = shift; 647 my @bind_values = @_; 648 my $sth = $self->SimpleQuery($query, @bind_values); 649 if ($sth) { 650 return ($sth->fetchrow); 651 } 652 else { 653 return($sth); 654 } 655} 656 657 658=head2 BinarySafeBLOBs 659 660Returns 1 if the current database supports BLOBs with embedded nulls. 661Returns undef if the current database doesn't support BLOBs with embedded nulls 662 663=cut 664 665sub BinarySafeBLOBs { 666 my $self = shift; 667 return(1); 668} 669 670 671 672=head2 KnowsBLOBs 673 674Returns 1 if the current database supports inserts of BLOBs automatically. 675Returns undef if the current database must be informed of BLOBs for inserts. 676 677=cut 678 679sub KnowsBLOBs { 680 my $self = shift; 681 return(1); 682} 683 684 685 686=head2 BLOBParams FIELD_NAME FIELD_TYPE 687 688Returns a hash ref for the bind_param call to identify BLOB types used by 689the current database for a particular column type. 690 691=cut 692 693sub BLOBParams { 694 my $self = shift; 695 # Don't assign to key 'value' as it is defined later. 696 return ( {} ); 697} 698 699 700 701=head2 DatabaseVersion [Short => 1] 702 703Returns the database's version. 704 705If argument C<Short> is true returns short variant, in other 706case returns whatever database handle/driver returns. By default 707returns short version, e.g. '4.1.23' or '8.0-rc4'. 708 709Returns empty string on error or if database couldn't return version. 710 711The base implementation uses a C<SELECT VERSION()> 712 713=cut 714 715sub DatabaseVersion { 716 my $self = shift; 717 my %args = ( Short => 1, @_ ); 718 719 unless ( defined $self->{'database_version'} ) { 720 721 # turn off error handling, store old values to restore later 722 my $re = $self->RaiseError; 723 $self->RaiseError(0); 724 my $pe = $self->PrintError; 725 $self->PrintError(0); 726 727 my $statement = "SELECT VERSION()"; 728 my $sth = $self->SimpleQuery($statement); 729 730 my $ver = ''; 731 $ver = ( $sth->fetchrow_arrayref->[0] || '' ) if $sth; 732 $ver =~ /(\d+(?:\.\d+)*(?:-[a-z0-9]+)?)/i; 733 $self->{'database_version'} = $ver; 734 $self->{'database_version_short'} = $1 || $ver; 735 736 $self->RaiseError($re); 737 $self->PrintError($pe); 738 } 739 740 return $self->{'database_version_short'} if $args{'Short'}; 741 return $self->{'database_version'}; 742} 743 744=head2 CaseSensitive 745 746Returns 1 if the current database's searches are case sensitive by default 747Returns undef otherwise 748 749=cut 750 751sub CaseSensitive { 752 my $self = shift; 753 return(1); 754} 755 756 757 758 759 760=head2 _MakeClauseCaseInsensitive FIELD OPERATOR VALUE 761 762Takes a field, operator and value. performs the magic necessary to make 763your database treat this clause as case insensitive. 764 765Returns a FIELD OPERATOR VALUE triple. 766 767=cut 768 769our $RE_CASE_INSENSITIVE_CHARS = qr/[-'"\d: ]/; 770 771sub _MakeClauseCaseInsensitive { 772 my $self = shift; 773 my $field = shift; 774 my $operator = shift; 775 my $value = shift; 776 777 # don't downcase integer values and things that looks like dates 778 if ($value !~ /^$RE_CASE_INSENSITIVE_CHARS+$/o) { 779 $field = "lower($field)"; 780 $value = lc($value); 781 } 782 return ($field, $operator, $value,undef); 783} 784 785=head2 Transactions 786 787L<DBIx::SearchBuilder::Handle> emulates nested transactions, 788by keeping a transaction stack depth. 789 790B<NOTE:> In nested transactions you shouldn't mix rollbacks and commits, 791because only last action really do commit/rollback. For example next code 792would produce desired results: 793 794 $handle->BeginTransaction; 795 $handle->BeginTransaction; 796 ... 797 $handle->Rollback; 798 $handle->BeginTransaction; 799 ... 800 $handle->Commit; 801 $handle->Commit; 802 803Only last action(Commit in example) finilize transaction in DB. 804 805=head3 BeginTransaction 806 807Tells DBIx::SearchBuilder to begin a new SQL transaction. 808This will temporarily suspend Autocommit mode. 809 810=cut 811 812sub BeginTransaction { 813 my $self = shift; 814 815 my $depth = $self->TransactionDepth; 816 return unless defined $depth; 817 818 $self->TransactionDepth(++$depth); 819 return 1 if $depth > 1; 820 821 return $self->dbh->begin_work; 822} 823 824=head3 EndTransaction [Action => 'commit'] [Force => 0] 825 826Tells to end the current transaction. Takes C<Action> argument 827that could be C<commit> or C<rollback>, the default value 828is C<commit>. 829 830If C<Force> argument is true then all nested transactions 831would be committed or rolled back. 832 833If there is no transaction in progress then method throw 834warning unless action is forced. 835 836Method returns true on success or false if an error occurred. 837 838=cut 839 840sub EndTransaction { 841 my $self = shift; 842 my %args = ( Action => 'commit', Force => 0, @_ ); 843 my $action = lc $args{'Action'} eq 'commit'? 'commit': 'rollback'; 844 845 my $depth = $self->TransactionDepth || 0; 846 unless ( $depth ) { 847 unless( $args{'Force'} ) { 848 Carp::cluck( "Attempted to $action a transaction with none in progress" ); 849 return 0; 850 } 851 return 1; 852 } else { 853 $depth--; 854 } 855 $depth = 0 if $args{'Force'}; 856 857 $self->TransactionDepth( $depth ); 858 859 my $dbh = $self->dbh; 860 $TRANSROLLBACK{ $dbh }{ $action }++; 861 if ( $TRANSROLLBACK{ $dbh }{ $action eq 'commit'? 'rollback' : 'commit' } ) { 862 warn "Rollback and commit are mixed while escaping nested transaction"; 863 } 864 return 1 if $depth; 865 866 delete $TRANSROLLBACK{ $dbh }; 867 868 if ($action eq 'commit') { 869 return $dbh->commit; 870 } 871 else { 872 DBIx::SearchBuilder::Record::Cachable->FlushCache 873 if DBIx::SearchBuilder::Record::Cachable->can('FlushCache'); 874 return $dbh->rollback; 875 } 876} 877 878=head3 Commit [FORCE] 879 880Tells to commit the current SQL transaction. 881 882Method uses C<EndTransaction> method, read its 883L<description|DBIx::SearchBuilder::Handle/EndTransaction>. 884 885=cut 886 887sub Commit { 888 my $self = shift; 889 $self->EndTransaction( Action => 'commit', Force => shift ); 890} 891 892 893=head3 Rollback [FORCE] 894 895Tells to abort the current SQL transaction. 896 897Method uses C<EndTransaction> method, read its 898L<description|DBIx::SearchBuilder::Handle/EndTransaction>. 899 900=cut 901 902sub Rollback { 903 my $self = shift; 904 $self->EndTransaction( Action => 'rollback', Force => shift ); 905} 906 907 908=head3 ForceRollback 909 910Force the handle to rollback. 911Whether or not we're deep in nested transactions. 912 913=cut 914 915sub ForceRollback { 916 my $self = shift; 917 $self->Rollback(1); 918} 919 920 921=head3 TransactionDepth 922 923Returns the current depth of the nested transaction stack. 924Returns C<undef> if there is no connection to database. 925 926=cut 927 928sub TransactionDepth { 929 my $self = shift; 930 931 my $dbh = $self->dbh; 932 return undef unless $dbh && $dbh->ping; 933 934 if ( @_ ) { 935 my $depth = shift; 936 if ( $depth ) { 937 $TRANSDEPTH{ $dbh } = $depth; 938 } else { 939 delete $TRANSDEPTH{ $dbh }; 940 } 941 } 942 return $TRANSDEPTH{ $dbh } || 0; 943} 944 945 946=head2 ApplyLimits STATEMENTREF ROWS_PER_PAGE FIRST_ROW 947 948takes an SQL SELECT statement and massages it to return ROWS_PER_PAGE starting with FIRST_ROW; 949 950=cut 951 952sub ApplyLimits { 953 my $self = shift; 954 my $statementref = shift; 955 my $per_page = shift; 956 my $first = shift; 957 958 my $limit_clause = ''; 959 960 if ( $per_page) { 961 $limit_clause = " LIMIT "; 962 if ( $first ) { 963 $limit_clause .= $first . ", "; 964 } 965 $limit_clause .= $per_page; 966 } 967 968 $$statementref .= $limit_clause; 969 970} 971 972 973 974 975 976=head2 Join { Paramhash } 977 978Takes a paramhash of everything Searchbuildler::Record does 979plus a parameter called 'SearchBuilder' that contains a ref 980to a SearchBuilder object'. 981 982This performs the join. 983 984 985=cut 986 987 988sub Join { 989 990 my $self = shift; 991 my %args = ( 992 SearchBuilder => undef, 993 TYPE => 'normal', 994 ALIAS1 => 'main', 995 FIELD1 => undef, 996 TABLE2 => undef, 997 COLLECTION2 => undef, 998 FIELD2 => undef, 999 ALIAS2 => undef, 1000 EXPRESSION => undef, 1001 @_ 1002 ); 1003 1004 1005 my $alias; 1006 1007#If we're handed in an ALIAS2, we need to go remove it from the Aliases array. 1008# Basically, if anyone generates an alias and then tries to use it in a join later, we want to be smart about 1009# creating joins, so we need to go rip it out of the old aliases table and drop it in as an explicit join 1010 if ( $args{'ALIAS2'} ) { 1011 1012 # this code is slow and wasteful, but it's clear. 1013 my @aliases = @{ $args{'SearchBuilder'}->{'aliases'} }; 1014 my @new_aliases; 1015 foreach my $old_alias (@aliases) { 1016 if ( $old_alias =~ /^(.*?) (\Q$args{'ALIAS2'}\E)$/ ) { 1017 $args{'TABLE2'} = $1; 1018 $alias = $2; 1019 } 1020 else { 1021 push @new_aliases, $old_alias; 1022 } 1023 } 1024 1025# If we found an alias, great. let's just pull out the table and alias for the other item 1026 unless ($alias) { 1027 1028 # if we can't do that, can we reverse the join and have it work? 1029 my $a1 = $args{'ALIAS1'}; 1030 my $f1 = $args{'FIELD1'}; 1031 $args{'ALIAS1'} = $args{'ALIAS2'}; 1032 $args{'FIELD1'} = $args{'FIELD2'}; 1033 $args{'ALIAS2'} = $a1; 1034 $args{'FIELD2'} = $f1; 1035 1036 @aliases = @{ $args{'SearchBuilder'}->{'aliases'} }; 1037 @new_aliases = (); 1038 foreach my $old_alias (@aliases) { 1039 if ( $old_alias =~ /^(.*?) ($args{'ALIAS2'})$/ ) { 1040 $args{'TABLE2'} = $1; 1041 $alias = $2; 1042 1043 } 1044 else { 1045 push @new_aliases, $old_alias; 1046 } 1047 } 1048 1049 } else { 1050 # we found alias, so NewAlias should take care of distinctness 1051 $args{'DISTINCT'} = 1 unless exists $args{'DISTINCT'}; 1052 } 1053 1054 unless ( $alias ) { 1055 # XXX: this situation is really bug in the caller!!! 1056 return ( $self->_NormalJoin(%args) ); 1057 } 1058 $args{'SearchBuilder'}->{'aliases'} = \@new_aliases; 1059 } elsif ( $args{'COLLECTION2'} ) { 1060 # We're joining to a pre-limited collection. We need to take 1061 # all clauses in the other collection, munge 'main.' to a new 1062 # alias, apply them locally, then proceed as usual. 1063 my $collection = delete $args{'COLLECTION2'}; 1064 $alias = $args{ALIAS2} = $args{'SearchBuilder'}->_GetAlias( $collection->Table ); 1065 $args{TABLE2} = $collection->Table; 1066 1067 eval {$collection->_ProcessRestrictions}; # RT hate 1068 1069 # Move over unused aliases 1070 push @{$args{SearchBuilder}{aliases}}, @{$collection->{aliases}}; 1071 1072 # Move over joins, as well 1073 for my $join (sort keys %{$collection->{left_joins}}) { 1074 my %alias = %{$collection->{left_joins}{$join}}; 1075 $alias{depends_on} = $alias if $alias{depends_on} eq "main"; 1076 $alias{criteria} = $self->_RenameRestriction( 1077 RESTRICTIONS => $alias{criteria}, 1078 NEW => $alias 1079 ); 1080 $args{SearchBuilder}{left_joins}{$join} = \%alias; 1081 } 1082 1083 my $restrictions = $self->_RenameRestriction( 1084 RESTRICTIONS => $collection->{restrictions}, 1085 NEW => $alias 1086 ); 1087 $args{SearchBuilder}{restrictions}{$_} = $restrictions->{$_} for keys %{$restrictions}; 1088 } else { 1089 $alias = $args{'SearchBuilder'}->_GetAlias( $args{'TABLE2'} ); 1090 } 1091 1092 my $meta = $args{'SearchBuilder'}->{'left_joins'}{"$alias"} ||= {}; 1093 if ( $args{'TYPE'} =~ /LEFT/i ) { 1094 $meta->{'alias_string'} = " LEFT JOIN " . $args{'TABLE2'} . " $alias "; 1095 $meta->{'type'} = 'LEFT'; 1096 } 1097 else { 1098 $meta->{'alias_string'} = " JOIN " . $args{'TABLE2'} . " $alias "; 1099 $meta->{'type'} = 'NORMAL'; 1100 } 1101 $meta->{'depends_on'} = $args{'ALIAS1'}; 1102 1103 my $criterion = $args{'EXPRESSION'} || $args{'ALIAS1'}.".".$args{'FIELD1'}; 1104 $meta->{'criteria'}{'base_criterion'} = 1105 [ { field => "$alias.$args{'FIELD2'}", op => '=', value => $criterion } ]; 1106 1107 if ( $args{'DISTINCT'} && !defined $args{'SearchBuilder'}{'joins_are_distinct'} ) { 1108 $args{SearchBuilder}{joins_are_distinct} = 1; 1109 } elsif ( !$args{'DISTINCT'} ) { 1110 $args{SearchBuilder}{joins_are_distinct} = 0; 1111 } 1112 1113 return ($alias); 1114} 1115 1116sub _RenameRestriction { 1117 my $self = shift; 1118 my %args = ( 1119 RESTRICTIONS => undef, 1120 OLD => "main", 1121 NEW => undef, 1122 @_, 1123 ); 1124 1125 my %return; 1126 for my $key ( keys %{$args{RESTRICTIONS}} ) { 1127 my $newkey = $key; 1128 $newkey =~ s/^\Q$args{OLD}\E\./$args{NEW}./; 1129 my @parts; 1130 for my $part ( @{ $args{RESTRICTIONS}{$key} } ) { 1131 if ( ref $part ) { 1132 my %part = %{$part}; 1133 $part{field} =~ s/^\Q$args{OLD}\E\./$args{NEW}./; 1134 $part{value} =~ s/^\Q$args{OLD}\E\./$args{NEW}./; 1135 push @parts, \%part; 1136 } else { 1137 push @parts, $part; 1138 } 1139 } 1140 $return{$newkey} = \@parts; 1141 } 1142 return \%return; 1143} 1144 1145sub _NormalJoin { 1146 1147 my $self = shift; 1148 my %args = ( 1149 SearchBuilder => undef, 1150 TYPE => 'normal', 1151 FIELD1 => undef, 1152 ALIAS1 => undef, 1153 TABLE2 => undef, 1154 FIELD2 => undef, 1155 ALIAS2 => undef, 1156 @_ 1157 ); 1158 1159 my $sb = $args{'SearchBuilder'}; 1160 1161 if ( $args{'TYPE'} =~ /LEFT/i ) { 1162 my $alias = $sb->_GetAlias( $args{'TABLE2'} ); 1163 my $meta = $sb->{'left_joins'}{"$alias"} ||= {}; 1164 $meta->{'alias_string'} = " LEFT JOIN $args{'TABLE2'} $alias "; 1165 $meta->{'depends_on'} = $args{'ALIAS1'}; 1166 $meta->{'type'} = 'LEFT'; 1167 $meta->{'criteria'}{'base_criterion'} = [ { 1168 field => "$args{'ALIAS1'}.$args{'FIELD1'}", 1169 op => '=', 1170 value => "$alias.$args{'FIELD2'}", 1171 } ]; 1172 1173 return ($alias); 1174 } 1175 else { 1176 $sb->DBIx::SearchBuilder::Limit( 1177 ENTRYAGGREGATOR => 'AND', 1178 QUOTEVALUE => 0, 1179 ALIAS => $args{'ALIAS1'}, 1180 FIELD => $args{'FIELD1'}, 1181 VALUE => $args{'ALIAS2'} . "." . $args{'FIELD2'}, 1182 @_ 1183 ); 1184 } 1185} 1186 1187# this code is all hacky and evil. but people desperately want _something_ and I'm 1188# super tired. refactoring gratefully appreciated. 1189 1190sub _BuildJoins { 1191 my $self = shift; 1192 my $sb = shift; 1193 1194 $self->OptimizeJoins( SearchBuilder => $sb ); 1195 1196 my $join_clause = join " CROSS JOIN ", ($sb->Table ." main"), @{ $sb->{'aliases'} }; 1197 my %processed = map { /^\S+\s+(\S+)$/; $1 => 1 } @{ $sb->{'aliases'} }; 1198 $processed{'main'} = 1; 1199 1200 # get a @list of joins that have not been processed yet, but depend on processed join 1201 my $joins = $sb->{'left_joins'}; 1202 while ( my @list = 1203 grep !$processed{ $_ } 1204 && (!$joins->{ $_ }{'depends_on'} || $processed{ $joins->{ $_ }{'depends_on'} }), 1205 sort keys %$joins 1206 ) { 1207 foreach my $join ( @list ) { 1208 $processed{ $join }++; 1209 1210 my $meta = $joins->{ $join }; 1211 my $aggregator = $meta->{'entry_aggregator'} || 'AND'; 1212 1213 $join_clause .= $meta->{'alias_string'} . " ON "; 1214 my @tmp = map { 1215 ref($_)? 1216 $_->{'field'} .' '. $_->{'op'} .' '. $_->{'value'}: 1217 $_ 1218 } 1219 map { ('(', @$_, ')', $aggregator) } sorted_values($meta->{'criteria'}); 1220 pop @tmp; 1221 $join_clause .= join ' ', @tmp; 1222 } 1223 } 1224 1225 # here we could check if there is recursion in joins by checking that all joins 1226 # are processed 1227 if ( my @not_processed = grep !$processed{ $_ }, keys %$joins ) { 1228 die "Unsatisfied dependency chain in joins @not_processed"; 1229 } 1230 return $join_clause; 1231} 1232 1233sub OptimizeJoins { 1234 my $self = shift; 1235 my %args = (SearchBuilder => undef, @_); 1236 my $joins = $args{'SearchBuilder'}->{'left_joins'}; 1237 1238 my %processed = map { /^\S+\s+(\S+)$/; $1 => 1 } @{ $args{'SearchBuilder'}->{'aliases'} }; 1239 $processed{ $_ }++ foreach grep $joins->{ $_ }{'type'} ne 'LEFT', keys %$joins; 1240 $processed{'main'}++; 1241 1242 my @ordered; 1243 # get a @list of joins that have not been processed yet, but depend on processed join 1244 # if we are talking about forest then we'll get the second level of the forest, 1245 # but we should process nodes on this level at the end, so we build FILO ordered list. 1246 # finally we'll get ordered list with leafes in the beginning and top most nodes at 1247 # the end. 1248 while ( my @list = grep !$processed{ $_ } 1249 && $processed{ $joins->{ $_ }{'depends_on'} }, sort keys %$joins ) 1250 { 1251 unshift @ordered, @list; 1252 $processed{ $_ }++ foreach @list; 1253 } 1254 1255 foreach my $join ( @ordered ) { 1256 next if $self->MayBeNull( SearchBuilder => $args{'SearchBuilder'}, ALIAS => $join ); 1257 1258 $joins->{ $join }{'alias_string'} =~ s/^\s*LEFT\s+/ /; 1259 $joins->{ $join }{'type'} = 'NORMAL'; 1260 } 1261 1262 # here we could check if there is recursion in joins by checking that all joins 1263 # are processed 1264 1265} 1266 1267=head2 MayBeNull 1268 1269Takes a C<SearchBuilder> and C<ALIAS> in a hash and resturns 1270true if restrictions of the query allow NULLs in a table joined with 1271the ALIAS, otherwise returns false value which means that you can 1272use normal join instead of left for the aliased table. 1273 1274Works only for queries have been built with L<DBIx::SearchBuilder/Join> and 1275L<DBIx::SearchBuilder/Limit> methods, for other cases return true value to 1276avoid fault optimizations. 1277 1278=cut 1279 1280sub MayBeNull { 1281 my $self = shift; 1282 my %args = (SearchBuilder => undef, ALIAS => undef, @_); 1283 # if we have at least one subclause that is not generic then we should get out 1284 # of here as we can't parse subclauses 1285 return 1 if grep $_ ne 'generic_restrictions', keys %{ $args{'SearchBuilder'}->{'subclauses'} }; 1286 1287 # build full list of generic conditions 1288 my @conditions; 1289 foreach ( grep @$_, sorted_values($args{'SearchBuilder'}->{'restrictions'}) ) { 1290 push @conditions, 'AND' if @conditions; 1291 push @conditions, '(', @$_, ')'; 1292 } 1293 1294 # find tables that depends on this alias and add their join conditions 1295 foreach my $join ( sorted_values($args{'SearchBuilder'}->{'left_joins'}) ) { 1296 # left joins on the left side so later we'll get 1 AND x expression 1297 # which equal to x, so we just skip it 1298 next if $join->{'type'} eq 'LEFT'; 1299 next unless $join->{'depends_on'} eq $args{'ALIAS'}; 1300 1301 my @tmp = map { ('(', @$_, ')', $join->{'entry_aggregator'}) } sorted_values($join->{'criteria'}); 1302 pop @tmp; 1303 1304 @conditions = ('(', @conditions, ')', 'AND', '(', @tmp ,')'); 1305 1306 } 1307 return 1 unless @conditions; 1308 1309 # replace conditions with boolean result: 1 - allows nulls, 0 - not 1310 # all restrictions on that don't act on required alias allow nulls 1311 # otherwise only IS NULL operator 1312 foreach ( splice @conditions ) { 1313 unless ( ref $_ ) { 1314 push @conditions, $_; 1315 } elsif ( rindex( $_->{'field'}, "$args{'ALIAS'}.", 0 ) == 0 ) { 1316 # field is alias.xxx op ... and only IS op allows NULLs 1317 push @conditions, lc $_->{op} eq 'is'; 1318 } elsif ( $_->{'value'} && rindex( $_->{'value'}, "$args{'ALIAS'}.", 0 ) == 0 ) { 1319 # value is alias.xxx so it can not be IS op 1320 push @conditions, 0; 1321 } elsif ( $_->{'field'} =~ /^(?i:lower)\(\s*\Q$args{'ALIAS'}\./ ) { 1322 # handle 'LOWER(alias.xxx) OP VALUE' we use for case insensetive 1323 push @conditions, lc $_->{op} eq 'is'; 1324 } else { 1325 push @conditions, 1; 1326 } 1327 } 1328 1329 # resturns index of closing paren by index of openning paren 1330 my $closing_paren = sub { 1331 my $i = shift; 1332 my $count = 0; 1333 for ( ; $i < @conditions; $i++ ) { 1334 if ( $conditions[$i] eq '(' ) { 1335 $count++; 1336 } 1337 elsif ( $conditions[$i] eq ')' ) { 1338 $count--; 1339 } 1340 return $i unless $count; 1341 } 1342 die "lost in parens"; 1343 }; 1344 1345 # solve boolean expression we have, an answer is our result 1346 my $parens_count = 0; 1347 my @tmp = (); 1348 while ( defined ( my $e = shift @conditions ) ) { 1349 #print "@tmp >>>$e<<< @conditions\n"; 1350 return $e if !@conditions && !@tmp; 1351 1352 unless ( $e ) { 1353 if ( $conditions[0] eq ')' ) { 1354 push @tmp, $e; 1355 next; 1356 } 1357 1358 my $aggreg = uc shift @conditions; 1359 if ( $aggreg eq 'OR' ) { 1360 # 0 OR x == x 1361 next; 1362 } elsif ( $aggreg eq 'AND' ) { 1363 # 0 AND x == 0 1364 my $close_p = $closing_paren->(0); 1365 splice @conditions, 0, $close_p + 1, (0); 1366 } else { 1367 die "unknown aggregator: @tmp $e >>>$aggreg<<< @conditions"; 1368 } 1369 } elsif ( $e eq '1' ) { 1370 if ( $conditions[0] eq ')' ) { 1371 push @tmp, $e; 1372 next; 1373 } 1374 1375 my $aggreg = uc shift @conditions; 1376 if ( $aggreg eq 'OR' ) { 1377 # 1 OR x == 1 1378 my $close_p = $closing_paren->(0); 1379 splice @conditions, 0, $close_p + 1, (1); 1380 } elsif ( $aggreg eq 'AND' ) { 1381 # 1 AND x == x 1382 next; 1383 } else { 1384 die "unknown aggregator: @tmp $e >>>$aggreg<<< @conditions"; 1385 } 1386 } elsif ( $e eq '(' ) { 1387 if ( $conditions[1] eq ')' ) { 1388 splice @conditions, 1, 1; 1389 } else { 1390 $parens_count++; 1391 push @tmp, $e; 1392 } 1393 } elsif ( $e eq ')' ) { 1394 die "extra closing paren: @tmp >>>$e<<< @conditions" 1395 if --$parens_count < 0; 1396 1397 unshift @conditions, @tmp, $e; 1398 @tmp = (); 1399 } else { 1400 die "lost: @tmp >>>$e<<< @conditions"; 1401 } 1402 } 1403 return 1; 1404} 1405 1406=head2 DistinctQuery STATEMENTREF 1407 1408takes an incomplete SQL SELECT statement and massages it to return a DISTINCT result set. 1409 1410 1411=cut 1412 1413sub DistinctQuery { 1414 my $self = shift; 1415 my $statementref = shift; 1416 my $sb = shift; 1417 1418 my $QueryHint = $sb->QueryHint; 1419 $QueryHint = $QueryHint ? " /* $QueryHint */ " : " "; 1420 1421 # Prepend select query for DBs which allow DISTINCT on all column types. 1422 $$statementref = "SELECT" . $QueryHint . "DISTINCT main.* FROM $$statementref"; 1423 $$statementref .= $sb->_GroupClause; 1424 $$statementref .= $sb->_OrderClause; 1425} 1426 1427 1428 1429 1430=head2 DistinctCount STATEMENTREF 1431 1432takes an incomplete SQL SELECT statement and massages it to return a DISTINCT result set. 1433 1434 1435=cut 1436 1437sub DistinctCount { 1438 my $self = shift; 1439 my $statementref = shift; 1440 my $sb = shift; 1441 1442 my $QueryHint = $sb->QueryHint; 1443 $QueryHint = $QueryHint ? " /* $QueryHint */ " : " "; 1444 1445 # Prepend select query for DBs which allow DISTINCT on all column types. 1446 $$statementref = "SELECT" . $QueryHint . "COUNT(DISTINCT main.id) FROM $$statementref"; 1447 1448} 1449 1450sub Fields { 1451 my $self = shift; 1452 my $table = lc shift; 1453 1454 unless ( $FIELDS_IN_TABLE{$table} ) { 1455 $FIELDS_IN_TABLE{ $table } = []; 1456 my $sth = $self->dbh->column_info( undef, '', $table, '%' ) 1457 or return (); 1458 my $info = $sth->fetchall_arrayref({}); 1459 foreach my $e ( @$info ) { 1460 push @{ $FIELDS_IN_TABLE{ $table } }, $e->{'COLUMN_NAME'}; 1461 } 1462 } 1463 1464 return @{ $FIELDS_IN_TABLE{ $table } }; 1465} 1466 1467 1468=head2 Log MESSAGE 1469 1470Takes a single argument, a message to log. 1471 1472Currently prints that message to STDERR 1473 1474=cut 1475 1476sub Log { 1477 my $self = shift; 1478 my $msg = shift; 1479 warn $msg."\n"; 1480 1481} 1482 1483=head2 SimpleDateTimeFunctions 1484 1485See L</DateTimeFunction> for details on supported functions. 1486This method is for implementers of custom DB connectors. 1487 1488Returns hash reference with (function name, sql template) pairs. 1489 1490=cut 1491 1492sub SimpleDateTimeFunctions { 1493 my $self = shift; 1494 return { 1495 datetime => 'SUBSTR(?, 1, 19)', 1496 time => 'SUBSTR(?, 12, 8)', 1497 1498 hourly => 'SUBSTR(?, 1, 13)', 1499 hour => 'SUBSTR(?, 12, 2 )', 1500 1501 date => 'SUBSTR(?, 1, 10)', 1502 daily => 'SUBSTR(?, 1, 10)', 1503 1504 day => 'SUBSTR(?, 9, 2 )', 1505 dayofmonth => 'SUBSTR(?, 9, 2 )', 1506 1507 monthly => 'SUBSTR(?, 1, 7 )', 1508 month => 'SUBSTR(?, 6, 2 )', 1509 1510 annually => 'SUBSTR(?, 1, 4 )', 1511 year => 'SUBSTR(?, 1, 4 )', 1512 }; 1513} 1514 1515=head2 DateTimeFunction 1516 1517Takes named arguments: 1518 1519=over 4 1520 1521=item * Field - SQL expression date/time function should be applied 1522to. Note that this argument is used as is without any kind of quoting. 1523 1524=item * Type - name of the function, see supported values below. 1525 1526=item * Timezone - optional hash reference with From and To values, 1527see L</ConvertTimezoneFunction> for details. 1528 1529=back 1530 1531Returns SQL statement. Returns NULL if function is not supported. 1532 1533=head3 Supported functions 1534 1535Type value in L</DateTimeFunction> is case insesitive. Spaces, 1536underscores and dashes are ignored. So 'date time', 'DateTime' 1537and 'date_time' are all synonyms. The following functions are 1538supported: 1539 1540=over 4 1541 1542=item * date time - as is, no conversion, except applying timezone 1543conversion if it's provided. 1544 1545=item * time - time only 1546 1547=item * hourly - datetime prefix up to the hours, e.g. '2010-03-25 16' 1548 1549=item * hour - hour, 0 - 23 1550 1551=item * date - date only 1552 1553=item * daily - synonym for date 1554 1555=item * day of week - 0 - 6, 0 - Sunday 1556 1557=item * day - day of month, 1 - 31 1558 1559=item * day of month - synonym for day 1560 1561=item * day of year - 1 - 366, support is database dependent 1562 1563=item * month - 1 - 12 1564 1565=item * monthly - year and month prefix, e.g. '2010-11' 1566 1567=item * year - e.g. '2023' 1568 1569=item * annually - synonym for year 1570 1571=item * week of year - 0-53, presence of zero week, 1st week meaning 1572and whether week starts on Monday or Sunday heavily depends on database. 1573 1574=back 1575 1576=cut 1577 1578sub DateTimeFunction { 1579 my $self = shift; 1580 my %args = ( 1581 Field => undef, 1582 Type => '', 1583 Timezone => undef, 1584 @_ 1585 ); 1586 1587 my $res = $args{'Field'} || '?'; 1588 if ( $args{'Timezone'} ) { 1589 $res = $self->ConvertTimezoneFunction( 1590 %{ $args{'Timezone'} }, 1591 Field => $res, 1592 ); 1593 } 1594 1595 my $norm_type = lc $args{'Type'}; 1596 $norm_type =~ s/[ _-]//g; 1597 if ( my $template = $self->SimpleDateTimeFunctions->{ $norm_type } ) { 1598 $template =~ s/\?/$res/; 1599 $res = $template; 1600 } 1601 else { 1602 return 'NULL'; 1603 } 1604 return $res; 1605} 1606 1607=head2 ConvertTimezoneFunction 1608 1609Generates a function applied to Field argument that converts timezone. 1610By default converts from UTC. Examples: 1611 1612 # UTC => Moscow 1613 $handle->ConvertTimezoneFunction( Field => '?', To => 'Europe/Moscow'); 1614 1615If there is problem with arguments or timezones are equal 1616then Field returned without any function applied. Field argument 1617is not escaped in any way, it's your job. 1618 1619Implementation is very database specific. To be portable convert 1620from UTC or to UTC. Some databases have internal storage for 1621information about timezones that should be kept up to date. 1622Read documentation for your DB. 1623 1624=cut 1625 1626sub ConvertTimezoneFunction { 1627 my $self = shift; 1628 my %args = ( 1629 From => 'UTC', 1630 To => undef, 1631 Field => '', 1632 @_ 1633 ); 1634 return $args{'Field'}; 1635} 1636 1637=head2 DateTimeIntervalFunction 1638 1639Generates a function to calculate interval in seconds between two 1640dates. Takes From and To arguments which can be either scalar or 1641a hash. Hash is processed with L<DBIx::SearchBuilder/CombineFunctionWithField>. 1642 1643Arguments are not quoted or escaped in any way. It's caller's job. 1644 1645=cut 1646 1647sub DateTimeIntervalFunction { 1648 my $self = shift; 1649 my %args = ( From => undef, To => undef, @_ ); 1650 1651 $_ = DBIx::SearchBuilder->CombineFunctionWithField(%$_) 1652 for grep ref, @args{'From', 'To'}; 1653 1654 return $self->_DateTimeIntervalFunction( %args ); 1655} 1656 1657sub _DateTimeIntervalFunction { return 'NULL' } 1658 1659=head2 NullsOrder 1660 1661Sets order of NULLs when sorting columns when called with mode, 1662but only if DB supports it. Modes: 1663 1664=over 4 1665 1666=item * small 1667 1668NULLs are smaller then anything else, so come first when order 1669is ASC and last otherwise. 1670 1671=item * large 1672 1673NULLs are larger then anything else. 1674 1675=item * first 1676 1677NULLs are always first. 1678 1679=item * last 1680 1681NULLs are always last. 1682 1683=item * default 1684 1685Return back to DB's default behaviour. 1686 1687=back 1688 1689When called without argument returns metadata required to generate 1690SQL. 1691 1692=cut 1693 1694sub NullsOrder { 1695 my $self = shift; 1696 1697 unless ($self->HasSupportForNullsOrder) { 1698 warn "No support for changing NULLs order" if @_; 1699 return undef; 1700 } 1701 1702 if ( @_ ) { 1703 my $mode = shift || 'default'; 1704 if ( $mode eq 'default' ) { 1705 delete $self->{'nulls_order'}; 1706 } 1707 elsif ( $mode eq 'small' ) { 1708 $self->{'nulls_order'} = { ASC => 'NULLS FIRST', DESC => 'NULLS LAST' }; 1709 } 1710 elsif ( $mode eq 'large' ) { 1711 $self->{'nulls_order'} = { ASC => 'NULLS LAST', DESC => 'NULLS FIRST' }; 1712 } 1713 elsif ( $mode eq 'first' ) { 1714 $self->{'nulls_order'} = { ASC => 'NULLS FIRST', DESC => 'NULLS FIRST' }; 1715 } 1716 elsif ( $mode eq 'last' ) { 1717 $self->{'nulls_order'} = { ASC => 'NULLS LAST', DESC => 'NULLS LAST' }; 1718 } 1719 else { 1720 warn "'$mode' is not supported NULLs ordering mode"; 1721 delete $self->{'nulls_order'}; 1722 } 1723 } 1724 1725 return undef unless $self->{'nulls_order'}; 1726 return $self->{'nulls_order'}; 1727} 1728 1729=head2 HasSupportForNullsOrder 1730 1731Returns true value if DB supports adjusting NULLs order while sorting 1732a column, for example C<ORDER BY Value ASC NULLS FIRST>. 1733 1734=cut 1735 1736sub HasSupportForNullsOrder { 1737 return 0; 1738} 1739 1740 1741=head2 DESTROY 1742 1743When we get rid of the Searchbuilder::Handle, we need to disconnect from the database 1744 1745=cut 1746 1747sub DESTROY { 1748 my $self = shift; 1749 $self->Disconnect if $self->{'DisconnectHandleOnDestroy'}; 1750 delete $DBIHandle{$self}; 1751} 1752 1753 17541; 1755__END__ 1756 1757 1758=head1 AUTHOR 1759 1760Jesse Vincent, jesse@fsck.com 1761 1762=head1 SEE ALSO 1763 1764perl(1), L<DBIx::SearchBuilder> 1765 1766=cut 1767 1768