X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FStorage%2FDBI.pm;h=037673ee822e52f70108edc733491e82bf4146f4;hb=893403c81741764c51e7cfff69a24d427d083c33;hp=d458e546b916050aa48a675288e7e740d26e17ec;hpb=e8e971f256b0989484ac29ece5843cf2c9427429;p=dbsrgits%2FDBIx-Class.git diff --git a/lib/DBIx/Class/Storage/DBI.pm b/lib/DBIx/Class/Storage/DBI.pm index d458e54..037673e 100644 --- a/lib/DBIx/Class/Storage/DBI.pm +++ b/lib/DBIx/Class/Storage/DBI.pm @@ -3,382 +3,214 @@ package DBIx::Class::Storage::DBI; use base 'DBIx::Class::Storage'; -use strict; +use strict; use warnings; +use Carp::Clan qw/^DBIx::Class/; use DBI; -use SQL::Abstract::Limit; use DBIx::Class::Storage::DBI::Cursor; use DBIx::Class::Storage::Statistics; -use Scalar::Util qw/blessed weaken/; +use Scalar::Util(); +use List::Util(); __PACKAGE__->mk_group_accessors('simple' => qw/_connect_info _dbi_connect_info _dbh _sql_maker _sql_maker_opts - _conn_pid _conn_tid disable_sth_caching cursor on_connect_do - transaction_depth unsafe/ + _conn_pid _conn_tid transaction_depth _dbh_autocommit savepoints/ ); -BEGIN { +# the values for these accessors are picked out (and deleted) from +# the attribute hashref passed to connect_info +my @storage_options = qw/ + on_connect_do on_disconnect_do disable_sth_caching unsafe auto_savepoint +/; +__PACKAGE__->mk_group_accessors('simple' => @storage_options); -package DBIC::SQL::Abstract; # Would merge upstream, but nate doesn't reply :( -use base qw/SQL::Abstract::Limit/; +# default cursor class, overridable in connect_info attributes +__PACKAGE__->cursor_class('DBIx::Class::Storage::DBI::Cursor'); -# This prevents the caching of $dbh in S::A::L, I believe -sub new { - my $self = shift->SUPER::new(@_); +__PACKAGE__->mk_group_accessors('inherited' => qw/sql_maker_class/); +__PACKAGE__->sql_maker_class('DBIx::Class::SQLAHacks'); - # If limit_dialect is a ref (like a $dbh), go ahead and replace - # it with what it resolves to: - $self->{limit_dialect} = $self->_find_syntax($self->{limit_dialect}) - if ref $self->{limit_dialect}; - $self; -} +=head1 NAME -sub _RowNumberOver { - my ($self, $sql, $order, $rows, $offset ) = @_; +DBIx::Class::Storage::DBI - DBI storage handler - $offset += 1; - my $last = $rows + $offset; - my ( $order_by ) = $self->_order_by( $order ); +=head1 SYNOPSIS - $sql = <<""; -SELECT * FROM -( - SELECT Q1.*, ROW_NUMBER() OVER( ) AS ROW_NUM FROM ( - $sql - $order_by - ) Q1 -) Q2 -WHERE ROW_NUM BETWEEN $offset AND $last + my $schema = MySchema->connect('dbi:SQLite:my.db'); - return $sql; -} + $schema->storage->debug(1); + $schema->dbh_do("DROP TABLE authors"); + $schema->resultset('Book')->search({ + written_on => $schema->storage->datetime_parser(DateTime->now) + }); -# While we're at it, this should make LIMIT queries more efficient, -# without digging into things too deeply -use Scalar::Util 'blessed'; -sub _find_syntax { - my ($self, $syntax) = @_; - my $dbhname = blessed($syntax) ? $syntax->{Driver}{Name} : $syntax; - if(ref($self) && $dbhname && $dbhname eq 'DB2') { - return 'RowNumberOver'; - } +=head1 DESCRIPTION - $self->{_cached_syntax} ||= $self->SUPER::_find_syntax($syntax); -} +This class represents the connection to an RDBMS via L. See +L for general information. This pod only +documents DBI-specific methods and behaviors. -sub select { - my ($self, $table, $fields, $where, $order, @rest) = @_; - $table = $self->_quote($table) unless ref($table); - local $self->{rownum_hack_count} = 1 - if (defined $rest[0] && $self->{limit_dialect} eq 'RowNum'); - @rest = (-1) unless defined $rest[0]; - die "LIMIT 0 Does Not Compute" if $rest[0] == 0; - # and anyway, SQL::Abstract::Limit will cause a barf if we don't first - local $self->{having_bind} = []; - my ($sql, @ret) = $self->SUPER::select( - $table, $self->_recurse_fields($fields), $where, $order, @rest - ); - return wantarray ? ($sql, @ret, @{$self->{having_bind}}) : $sql; -} +=head1 METHODS -sub insert { - my $self = shift; - my $table = shift; - $table = $self->_quote($table) unless ref($table); - $self->SUPER::insert($table, @_); -} +=cut -sub update { - my $self = shift; - my $table = shift; - $table = $self->_quote($table) unless ref($table); - $self->SUPER::update($table, @_); -} +sub new { + my $new = shift->next::method(@_); -sub delete { - my $self = shift; - my $table = shift; - $table = $self->_quote($table) unless ref($table); - $self->SUPER::delete($table, @_); -} + $new->transaction_depth(0); + $new->_sql_maker_opts({}); + $new->{savepoints} = []; + $new->{_in_dbh_do} = 0; + $new->{_dbh_gen} = 0; -sub _emulate_limit { - my $self = shift; - if ($_[3] == -1) { - return $_[1].$self->_order_by($_[2]); - } else { - return $self->SUPER::_emulate_limit(@_); - } + $new; } -sub _recurse_fields { - my ($self, $fields, $params) = @_; - my $ref = ref $fields; - return $self->_quote($fields) unless $ref; - return $$fields if $ref eq 'SCALAR'; - - if ($ref eq 'ARRAY') { - return join(', ', map { - $self->_recurse_fields($_) - .(exists $self->{rownum_hack_count} && !($params && $params->{no_rownum_hack}) - ? ' AS col'.$self->{rownum_hack_count}++ - : '') - } @$fields); - } elsif ($ref eq 'HASH') { - foreach my $func (keys %$fields) { - return $self->_sqlcase($func) - .'( '.$self->_recurse_fields($fields->{$func}).' )'; - } - } -} +=head2 connect_info -sub _order_by { - my $self = shift; - my $ret = ''; - my @extra; - if (ref $_[0] eq 'HASH') { - if (defined $_[0]->{group_by}) { - $ret = $self->_sqlcase(' group by ') - .$self->_recurse_fields($_[0]->{group_by}, { no_rownum_hack => 1 }); - } - if (defined $_[0]->{having}) { - my $frag; - ($frag, @extra) = $self->_recurse_where($_[0]->{having}); - push(@{$self->{having_bind}}, @extra); - $ret .= $self->_sqlcase(' having ').$frag; - } - if (defined $_[0]->{order_by}) { - $ret .= $self->_order_by($_[0]->{order_by}); - } - } elsif (ref $_[0] eq 'SCALAR') { - $ret = $self->_sqlcase(' order by ').${ $_[0] }; - } elsif (ref $_[0] eq 'ARRAY' && @{$_[0]}) { - my @order = @{+shift}; - $ret = $self->_sqlcase(' order by ') - .join(', ', map { - my $r = $self->_order_by($_, @_); - $r =~ s/^ ?ORDER BY //i; - $r; - } @order); - } else { - $ret = $self->SUPER::_order_by(@_); - } - return $ret; -} +This method is normally called by L, which +encapsulates its argument list in an arrayref before passing them here. -sub _order_directions { - my ($self, $order) = @_; - $order = $order->{order_by} if ref $order eq 'HASH'; - return $self->SUPER::_order_directions($order); -} +The argument list may contain: -sub _table { - my ($self, $from) = @_; - if (ref $from eq 'ARRAY') { - return $self->_recurse_from(@$from); - } elsif (ref $from eq 'HASH') { - return $self->_make_as($from); - } else { - return $from; # would love to quote here but _table ends up getting called - # twice during an ->select without a limit clause due to - # the way S::A::Limit->select works. should maybe consider - # bypassing this and doing S::A::select($self, ...) in - # our select method above. meantime, quoting shims have - # been added to select/insert/update/delete here - } -} +=over -sub _recurse_from { - my ($self, $from, @join) = @_; - my @sqlf; - push(@sqlf, $self->_make_as($from)); - foreach my $j (@join) { - my ($to, $on) = @$j; +=item * - # check whether a join type exists - my $join_clause = ''; - my $to_jt = ref($to) eq 'ARRAY' ? $to->[0] : $to; - if (ref($to_jt) eq 'HASH' and exists($to_jt->{-join_type})) { - $join_clause = ' '.uc($to_jt->{-join_type}).' JOIN '; - } else { - $join_clause = ' JOIN '; - } - push(@sqlf, $join_clause); +The same 4-element argument set one would normally pass to +L, optionally followed by +L +recognized by DBIx::Class: - if (ref $to eq 'ARRAY') { - push(@sqlf, '(', $self->_recurse_from(@$to), ')'); - } else { - push(@sqlf, $self->_make_as($to)); - } - push(@sqlf, ' ON ', $self->_join_condition($on)); - } - return join('', @sqlf); -} - -sub _make_as { - my ($self, $from) = @_; - return join(' ', map { (ref $_ eq 'SCALAR' ? $$_ : $self->_quote($_)) } - reverse each %{$self->_skip_options($from)}); -} - -sub _skip_options { - my ($self, $hash) = @_; - my $clean_hash = {}; - $clean_hash->{$_} = $hash->{$_} - for grep {!/^-/} keys %$hash; - return $clean_hash; -} - -sub _join_condition { - my ($self, $cond) = @_; - if (ref $cond eq 'HASH') { - my %j; - for (keys %$cond) { - my $v = $cond->{$_}; - if (ref $v) { - # XXX no throw_exception() in this package and croak() fails with strange results - Carp::croak(ref($v) . qq{ reference arguments are not supported in JOINS - try using \"..." instead'}) - if ref($v) ne 'SCALAR'; - $j{$_} = $v; - } - else { - my $x = '= '.$self->_quote($v); $j{$_} = \$x; - } - }; - return scalar($self->_recurse_where(\%j)); - } elsif (ref $cond eq 'ARRAY') { - return join(' OR ', map { $self->_join_condition($_) } @$cond); - } else { - die "Can't handle this yet!"; - } -} + $connect_info_args = [ $dsn, $user, $password, \%dbi_attributes?, \%extra_attributes? ]; -sub _quote { - my ($self, $label) = @_; - return '' unless defined $label; - return "*" if $label eq '*'; - return $label unless $self->{quote_char}; - if(ref $self->{quote_char} eq "ARRAY"){ - return $self->{quote_char}->[0] . $label . $self->{quote_char}->[1] - if !defined $self->{name_sep}; - my $sep = $self->{name_sep}; - return join($self->{name_sep}, - map { $self->{quote_char}->[0] . $_ . $self->{quote_char}->[1] } - split(/\Q$sep\E/,$label)); - } - return $self->SUPER::_quote($label); -} +=item * -sub limit_dialect { - my $self = shift; - $self->{limit_dialect} = shift if @_; - return $self->{limit_dialect}; -} +A single code reference which returns a connected +L optionally followed by +L recognized +by DBIx::Class: -sub quote_char { - my $self = shift; - $self->{quote_char} = shift if @_; - return $self->{quote_char}; -} + $connect_info_args = [ sub { DBI->connect (...) }, \%extra_attributes? ]; -sub name_sep { - my $self = shift; - $self->{name_sep} = shift if @_; - return $self->{name_sep}; -} +=item * -} # End of BEGIN block +A single hashref with all the attributes and the dsn/user/password +mixed together: -=head1 NAME + $connect_info_args = [{ + dsn => $dsn, + user => $user, + password => $pass, + %dbi_attributes, + %extra_attributes, + }]; -DBIx::Class::Storage::DBI - DBI storage handler +This is particularly useful for L based applications, allowing the +following config (L style): -=head1 SYNOPSIS + + schema_class App::DB + + dsn dbi:mysql:database=test + user testuser + password TestPass + AutoCommit 1 + + -=head1 DESCRIPTION +=back -This class represents the connection to an RDBMS via L. See -L for general information. This pod only -documents DBI-specific methods and behaviors. +Please note that the L docs recommend that you always explicitly +set C to either I<0> or I<1>. L further +recommends that it be set to I<1>, and that you perform transactions +via our L method. L will set it +to I<1> if you do not do explicitly set it to zero. This is the default +for most DBDs. See L for details. -=head1 METHODS +=head3 DBIx::Class specific connection attributes -=cut +In addition to the standard L +L attributes, DBIx::Class recognizes +the following connection options. These options can be mixed in with your other +L connection attributes, or placed in a seperate hashref +(C<\%extra_attributes>) as shown above. -sub new { - my $new = shift->next::method(@_); +Every time C is invoked, any previous settings for +these options will be cleared before setting the new ones, regardless of +whether any options are specified in the new C. - $new->cursor("DBIx::Class::Storage::DBI::Cursor"); - $new->transaction_depth(0); - $new->_sql_maker_opts({}); - $new->{_in_dbh_do} = 0; - $new->{_dbh_gen} = 0; - $new; -} +=over -=head2 connect_info +=item on_connect_do -The arguments of C are always a single array reference. +Specifies things to do immediately after connecting or re-connecting to +the database. Its value may contain: -This is normally accessed via L, which -encapsulates its argument list in an arrayref before calling -C here. +=over -The arrayref can either contain the same set of arguments one would -normally pass to L, or a lone code reference which returns -a connected database handle. Please note that the L docs -recommend that you always explicitly set C to either -C<0> or C<1>. L further recommends that it be set -to C<1>, and that you perform transactions via our L -method. L will set it to C<1> if you do not do explicitly -set it to zero. This is the default for most DBDs. See below for more -details. +=item a scalar -In either case, if the final argument in your connect_info happens -to be a hashref, C will look there for several -connection-specific options: +This contains one SQL statement to execute. -=over 4 +=item an array reference -=item on_connect_do +This contains SQL statements to execute in order. Each element contains +a string or a code reference that returns a string. + +=item a code reference + +This contains some code to execute. Unlike code references within an +array reference, its return value is ignored. + +=back -This can be set to an arrayref of literal sql statements, which will -be executed immediately after making the connection to the database -every time we [re-]connect. +=item on_disconnect_do + +Takes arguments in the same form as L and executes them +immediately before disconnecting from the database. + +Note, this only runs if you explicitly call L on the +storage object. =item disable_sth_caching If set to a true value, this option will disable the caching of statement handles via L. -=item limit_dialect +=item limit_dialect Sets the limit dialect. This is useful for JDBC-bridge among others where the remote SQL-dialect cannot be determined by the name of the -driver alone. +driver alone. See also L. =item quote_char -Specifies what characters to use to quote table and column names. If -you use this you will want to specify L as well. +Specifies what characters to use to quote table and column names. If +you use this you will want to specify L as well. -quote_char expects either a single character, in which case is it is placed -on either side of the table/column, or an arrayref of length 2 in which case the -table/column name is placed between the elements. +C expects either a single character, in which case is it +is placed on either side of the table/column name, or an arrayref of length +2 in which case the table/column name is placed between the elements. -For example under MySQL you'd use C '`'>, and user SQL Server you'd -use C [qw/[ ]/]>. +For example under MySQL you should use C<< quote_char => '`' >>, and for +SQL Server you should use C<< quote_char => [qw/[ ]/] >>. =item name_sep -This only needs to be used in conjunction with L, and is used to -specify the charecter that seperates elements (schemas, tables, columns) from +This only needs to be used in conjunction with C, and is used to +specify the charecter that seperates elements (schemas, tables, columns) from each other. In most cases this is simply a C<.>. +The consequences of not supplying this value is that L +will assume DBIx::Class' uses of aliases to be complete column +names. The output will look like I<"me.name"> when it should actually +be I<"me"."name">. + =item unsafe This Storage driver normally installs its own C, sets @@ -395,32 +227,21 @@ Note that your custom settings can cause Storage to malfunction, especially if you set a C handler that suppresses exceptions and/or disable C. -=back - -These options can be mixed in with your other L connection attributes, -or placed in a seperate hashref after all other normal L connection -arguments. +=item auto_savepoint -Every time C is invoked, any previous settings for -these options will be cleared before setting the new ones, regardless of -whether any options are specified in the new C. +If this option is true, L will use savepoints when nesting +transactions, making it possible to recover from failure in the inner +transaction without having to abort all outer transactions. -Another Important Note: +=item cursor_class -DBIC can do some wonderful magic with handling exceptions, -disconnections, and transactions when you use C -combined with C for transaction support. +Use this argument to supply a cursor class other than the default +L. -If you set C in your connect info, then you are always -in an assumed transaction between commits, and you're telling us you'd -like to manage that manually. A lot of DBIC's magic protections -go away. We can't protect you from exceptions due to database -disconnects because we don't know anything about how to restart your -transactions. You're on your own for handling all sorts of exceptional -cases if you choose the C path, just as you would -be with raw DBI. +=back -Examples: +Some real-life examples of arguments to L and +L # Simple SQLite connection ->connect_info([ 'dbi:SQLite:./foo.db' ]); @@ -449,7 +270,20 @@ Examples: ] ); - # Subref + DBIC-specific connection options + # Same, but with hashref as argument + # See parse_connect_info for explanation + ->connect_info( + [{ + dsn => 'dbi:Pg:dbname=foo', + user => 'postgres', + password => 'my_pg_password', + AutoCommit => 1, + quote_char => q{"}, + name_sep => q{.}, + }] + ); + + # Subref + DBIx::Class-specific connection options ->connect_info( [ sub { DBI->connect(...) }, @@ -462,6 +296,8 @@ Examples: ] ); + + =cut sub connect_info { @@ -469,44 +305,67 @@ sub connect_info { return $self->_connect_info if !$info_arg; + my @args = @$info_arg; # take a shallow copy for further mutilation + $self->_connect_info([@args]); # copy for _connect_info + + + # combine/pre-parse arguments depending on invocation style + + my %attrs; + if (ref $args[0] eq 'CODE') { # coderef with optional \%extra_attributes + %attrs = %{ $args[1] || {} }; + @args = $args[0]; + } + elsif (ref $args[0] eq 'HASH') { # single hashref (i.e. Catalyst config) + %attrs = %{$args[0]}; + @args = (); + for (qw/password user dsn/) { + unshift @args, delete $attrs{$_}; + } + } + else { # otherwise assume dsn/user/password + \%attrs + \%extra_attrs + %attrs = ( + % { $args[3] || {} }, + % { $args[4] || {} }, + ); + @args = @args[0,1,2]; + } + # Kill sql_maker/_sql_maker_opts, so we get a fresh one with only # the new set of options $self->_sql_maker(undef); $self->_sql_maker_opts({}); - $self->_connect_info([@$info_arg]); # copy for _connect_info - my $dbi_info = [@$info_arg]; # copy for _dbi_connect_info - - my $last_info = $dbi_info->[-1]; - if(ref $last_info eq 'HASH') { - for my $storage_opt (qw/on_connect_do disable_sth_caching unsafe/) { - if(my $value = delete $last_info->{$storage_opt}) { + if(keys %attrs) { + for my $storage_opt (@storage_options, 'cursor_class') { # @storage_options is declared at the top of the module + if(my $value = delete $attrs{$storage_opt}) { $self->$storage_opt($value); } } for my $sql_maker_opt (qw/limit_dialect quote_char name_sep/) { - if(my $opt_val = delete $last_info->{$sql_maker_opt}) { + if(my $opt_val = delete $attrs{$sql_maker_opt}) { $self->_sql_maker_opts->{$sql_maker_opt} = $opt_val; } } - - # Get rid of any trailing empty hashref - pop(@$dbi_info) if !keys %$last_info; } - $self->_dbi_connect_info($dbi_info); + %attrs = () if (ref $args[0] eq 'CODE'); # _connect() never looks past $args[0] in this case + + $self->_dbi_connect_info([@args, keys %attrs ? \%attrs : ()]); $self->_connect_info; } =head2 on_connect_do -This method is deprecated in favor of setting via L. +This method is deprecated in favour of setting via L. + =head2 dbh_do -Arguments: $subref, @extra_coderef_args? +Arguments: ($subref | $method_name), @extra_coderef_args? -Execute the given subref using the new exception-based connection management. +Execute the given $subref or $method_name using the new exception-based +connection management. The first two arguments will be the storage object that C was called on and a database handle to use. Any additional arguments will be passed @@ -534,12 +393,11 @@ Example: sub dbh_do { my $self = shift; - my $coderef = shift; + my $code = shift; - ref $coderef eq 'CODE' or $self->throw_exception - ('$coderef must be a CODE reference'); + my $dbh = $self->_dbh; - return $coderef->($self, $self->_dbh, @_) if $self->{_in_dbh_do} + return $self->$code($dbh, @_) if $self->{_in_dbh_do} || $self->{transaction_depth}; local $self->{_in_dbh_do} = 1; @@ -548,16 +406,20 @@ sub dbh_do { my $want_array = wantarray; eval { - $self->_verify_pid if $self->_dbh; - $self->_populate_dbh if !$self->_dbh; + $self->_verify_pid if $dbh; + if(!$self->_dbh) { + $self->_populate_dbh; + $dbh = $self->_dbh; + } + if($want_array) { - @result = $coderef->($self, $self->_dbh, @_); + @result = $self->$code($dbh, @_); } elsif(defined $want_array) { - $result[0] = $coderef->($self, $self->_dbh, @_); + $result[0] = $self->$code($dbh, @_); } else { - $coderef->($self, $self->_dbh, @_); + $self->$code($dbh, @_); } }; @@ -569,7 +431,7 @@ sub dbh_do { # We were not connected - reconnect and retry, but let any # exception fall right through this time $self->_populate_dbh; - $coderef->($self, $self->_dbh, @_); + $self->$code($self->_dbh, @_); } # This is basically a blend of dbh_do above and DBIx::Class::Storage::txn_do. @@ -582,6 +444,8 @@ sub txn_do { ref $coderef eq 'CODE' or $self->throw_exception ('$coderef must be a CODE reference'); + return $coderef->(@_) if $self->{transaction_depth} && ! $self->auto_savepoint; + local $self->{_in_dbh_do} = 1; my @result; @@ -642,13 +506,38 @@ sub disconnect { my ($self) = @_; if( $self->connected ) { - $self->_dbh->rollback unless $self->_dbh->{AutoCommit}; + my $connection_do = $self->on_disconnect_do; + $self->_do_connection_actions($connection_do) if ref($connection_do); + + $self->_dbh->rollback unless $self->_dbh_autocommit; $self->_dbh->disconnect; $self->_dbh(undef); $self->{_dbh_gen}++; } } +=head2 with_deferred_fk_checks + +=over 4 + +=item Arguments: C<$coderef> + +=item Return Value: The return value of $coderef + +=back + +Storage specific method to run the code ref with FK checks deferred or +in MySQL's case disabled entirely. + +=cut + +# Storage subclasses should override this +sub with_deferred_fk_checks { + my ($self, $sub) = @_; + + $sub->(); +} + sub connected { my ($self) = @_; @@ -660,6 +549,7 @@ sub connected { } else { $self->_verify_pid; + return 0 if !$self->_dbh; } return ($dbh->FETCH('Active') && $dbh->ping); } @@ -672,7 +562,7 @@ sub connected { sub _verify_pid { my ($self) = @_; - return if $self->_conn_pid == $$; + return if defined $self->_conn_pid && $self->_conn_pid == $$; $self->_dbh->{InactiveDestroy} = 1; $self->_dbh(undef); @@ -704,44 +594,106 @@ sub dbh { sub _sql_maker_args { my ($self) = @_; - - return ( bindtype=>'columns', limit_dialect => $self->dbh, %{$self->_sql_maker_opts} ); + + return ( bindtype=>'columns', array_datatypes => 1, limit_dialect => $self->dbh, %{$self->_sql_maker_opts} ); } sub sql_maker { my ($self) = @_; unless ($self->_sql_maker) { - $self->_sql_maker(new DBIC::SQL::Abstract( $self->_sql_maker_args )); + my $sql_maker_class = $self->sql_maker_class; + $self->ensure_class_loaded ($sql_maker_class); + $self->_sql_maker($sql_maker_class->new( $self->_sql_maker_args )); } return $self->_sql_maker; } +sub _rebless {} + sub _populate_dbh { my ($self) = @_; my @info = @{$self->_dbi_connect_info || []}; $self->_dbh($self->_connect(@info)); + $self->_conn_pid($$); + $self->_conn_tid(threads->tid) if $INC{'threads.pm'}; + + $self->_determine_driver; + # Always set the transaction depth on connect, since # there is no transaction in progress by definition - $self->{transaction_depth} = $self->_dbh->{AutoCommit} ? 0 : 1; + $self->{transaction_depth} = $self->_dbh_autocommit ? 0 : 1; + + my $connection_do = $self->on_connect_do; + $self->_do_connection_actions($connection_do) if $connection_do; +} - if(ref $self eq 'DBIx::Class::Storage::DBI') { - my $driver = $self->_dbh->{Driver}->{Name}; - if ($self->load_optional_class("DBIx::Class::Storage::DBI::${driver}")) { - bless $self, "DBIx::Class::Storage::DBI::${driver}"; - $self->_rebless() if $self->can('_rebless'); +sub _determine_driver { + my ($self) = @_; + + if (ref $self eq 'DBIx::Class::Storage::DBI') { + my $driver; + + if ($self->_dbh) { # we are connected + $driver = $self->_dbh->{Driver}{Name}; + } else { + # try to use dsn to not require being connected, the driver may still + # force a connection in _rebless to determine version + ($driver) = $self->_dbi_connect_info->[0] =~ /dbi:([^:]+):/i; + } + + my $storage_class = "DBIx::Class::Storage::DBI::${driver}"; + if ($self->load_optional_class($storage_class)) { + mro::set_mro($storage_class, 'c3'); + bless $self, $storage_class; + $self->_rebless(); } } +} - # if on-connect sql statements are given execute them - foreach my $sql_statement (@{$self->on_connect_do || []}) { - $self->debugobj->query_start($sql_statement) if $self->debug(); - $self->_dbh->do($sql_statement); - $self->debugobj->query_end($sql_statement) if $self->debug(); +sub _do_connection_actions { + my $self = shift; + my $connection_do = shift; + + if (!ref $connection_do) { + $self->_do_query($connection_do); + } + elsif (ref $connection_do eq 'ARRAY') { + $self->_do_query($_) foreach @$connection_do; + } + elsif (ref $connection_do eq 'CODE') { + $connection_do->($self); + } + else { + $self->throw_exception (sprintf ("Don't know how to process conection actions of type '%s'", ref $connection_do) ); } - $self->_conn_pid($$); - $self->_conn_tid(threads->tid) if $INC{'threads.pm'}; + return $self; +} + +sub _do_query { + my ($self, $action) = @_; + + if (ref $action eq 'CODE') { + $action = $action->($self); + $self->_do_query($_) foreach @$action; + } + else { + # Most debuggers expect ($sql, @bind), so we need to exclude + # the attribute hash which is the second argument to $dbh->do + # furthermore the bind values are usually to be presented + # as named arrayref pairs, so wrap those here too + my @do_args = (ref $action eq 'ARRAY') ? (@$action) : ($action); + my $sql = shift @do_args; + my $attrs = shift @do_args; + my @bind = map { [ undef, $_ ] } @do_args; + + $self->_query_start($sql, @bind); + $self->_dbh->do($sql, $attrs, @do_args); + $self->_query_end($sql, @bind); + } + + return $self; } sub _connect { @@ -765,11 +717,16 @@ sub _connect { $dbh = DBI->connect(@info); } - if(!$self->unsafe) { + if($dbh && !$self->unsafe) { my $weak_self = $self; - weaken($weak_self); + Scalar::Util::weaken($weak_self); $dbh->{HandleError} = sub { - $weak_self->throw_exception("DBI Exception: $_[0]") + if ($weak_self) { + $weak_self->throw_exception("DBI Exception: $_[0]"); + } + else { + croak ("DBI Exception: $_[0]"); + } }; $dbh->{ShowErrorStatement} = 1; $dbh->{RaiseError} = 1; @@ -782,21 +739,110 @@ sub _connect { $self->throw_exception("DBI Connection failed: " . ($@||$DBI::errstr)) if !$dbh || $@; + $self->_dbh_autocommit($dbh->{AutoCommit}); + $dbh; } +sub svp_begin { + my ($self, $name) = @_; + + $name = $self->_svp_generate_name + unless defined $name; + + $self->throw_exception ("You can't use savepoints outside a transaction") + if $self->{transaction_depth} == 0; + + $self->throw_exception ("Your Storage implementation doesn't support savepoints") + unless $self->can('_svp_begin'); + + push @{ $self->{savepoints} }, $name; + + $self->debugobj->svp_begin($name) if $self->debug; + + return $self->_svp_begin($name); +} + +sub svp_release { + my ($self, $name) = @_; + + $self->throw_exception ("You can't use savepoints outside a transaction") + if $self->{transaction_depth} == 0; + + $self->throw_exception ("Your Storage implementation doesn't support savepoints") + unless $self->can('_svp_release'); + + if (defined $name) { + $self->throw_exception ("Savepoint '$name' does not exist") + unless grep { $_ eq $name } @{ $self->{savepoints} }; + + # Dig through the stack until we find the one we are releasing. This keeps + # the stack up to date. + my $svp; + + do { $svp = pop @{ $self->{savepoints} } } while $svp ne $name; + } else { + $name = pop @{ $self->{savepoints} }; + } + + $self->debugobj->svp_release($name) if $self->debug; + + return $self->_svp_release($name); +} + +sub svp_rollback { + my ($self, $name) = @_; + + $self->throw_exception ("You can't use savepoints outside a transaction") + if $self->{transaction_depth} == 0; + + $self->throw_exception ("Your Storage implementation doesn't support savepoints") + unless $self->can('_svp_rollback'); + + if (defined $name) { + # If they passed us a name, verify that it exists in the stack + unless(grep({ $_ eq $name } @{ $self->{savepoints} })) { + $self->throw_exception("Savepoint '$name' does not exist!"); + } + + # Dig through the stack until we find the one we are releasing. This keeps + # the stack up to date. + while(my $s = pop(@{ $self->{savepoints} })) { + last if($s eq $name); + } + # Add the savepoint back to the stack, as a rollback doesn't remove the + # named savepoint, only everything after it. + push(@{ $self->{savepoints} }, $name); + } else { + # We'll assume they want to rollback to the last savepoint + $name = $self->{savepoints}->[-1]; + } + + $self->debugobj->svp_rollback($name) if $self->debug; + + return $self->_svp_rollback($name); +} + +sub _svp_generate_name { + my ($self) = @_; + + return 'savepoint_'.scalar(@{ $self->{'savepoints'} }); +} sub txn_begin { my $self = shift; $self->ensure_connected(); - if($self->{transaction_depth}++ == 0) { + if($self->{transaction_depth} == 0) { $self->debugobj->txn_begin() if $self->debug; # this isn't ->_dbh-> because # we should reconnect on begin_work # for AutoCommit users $self->dbh->begin_work; + } elsif ($self->auto_savepoint) { + $self->svp_begin; } + $self->{transaction_depth}++; } sub txn_commit { @@ -807,28 +853,32 @@ sub txn_commit { if ($self->debug); $dbh->commit; $self->{transaction_depth} = 0 - if $dbh->{AutoCommit}; + if $self->_dbh_autocommit; } elsif($self->{transaction_depth} > 1) { - $self->{transaction_depth}-- + $self->{transaction_depth}--; + $self->svp_release + if $self->auto_savepoint; } } sub txn_rollback { my $self = shift; my $dbh = $self->_dbh; - my $autocommit; eval { - $autocommit = $dbh->{AutoCommit}; if ($self->{transaction_depth} == 1) { $self->debugobj->txn_rollback() if ($self->debug); - $dbh->rollback; $self->{transaction_depth} = 0 - if $autocommit; + if $self->_dbh_autocommit; + $dbh->rollback; } elsif($self->{transaction_depth} > 1) { $self->{transaction_depth}--; + if ($self->auto_savepoint) { + $self->svp_rollback; + $self->svp_release; + } } else { die DBIx::Class::Storage::NESTED_ROLLBACK_EXCEPTION->new; @@ -839,7 +889,7 @@ sub txn_rollback { my $exception_class = "DBIx::Class::Storage::NESTED_ROLLBACK_EXCEPTION"; $error =~ /$exception_class/ and $self->throw_exception($error); # ensure that a failed rollback resets the transaction depth - $self->{transaction_depth} = $autocommit ? 0 : 1; + $self->{transaction_depth} = $self->_dbh_autocommit ? 0 : 1; $self->throw_exception($error); } } @@ -850,32 +900,64 @@ sub txn_rollback { sub _prep_for_execute { my ($self, $op, $extra_bind, $ident, $args) = @_; + if( Scalar::Util::blessed($ident) && $ident->isa("DBIx::Class::ResultSource") ) { + $ident = $ident->from(); + } + my ($sql, @bind) = $self->sql_maker->$op($ident, @$args); + unshift(@bind, map { ref $_ eq 'ARRAY' ? $_ : [ '!!dummy', $_ ] } @$extra_bind) if $extra_bind; - return ($sql, \@bind); } + +sub _fix_bind_params { + my ($self, @bind) = @_; + + ### Turn @bind from something like this: + ### ( [ "artist", 1 ], [ "cdid", 1, 3 ] ) + ### to this: + ### ( "'1'", "'1'", "'3'" ) + return + map { + if ( defined( $_ && $_->[1] ) ) { + map { qq{'$_'}; } @{$_}[ 1 .. $#$_ ]; + } + else { q{'NULL'}; } + } @bind; +} + +sub _query_start { + my ( $self, $sql, @bind ) = @_; + + if ( $self->debug ) { + @bind = $self->_fix_bind_params(@bind); + + $self->debugobj->query_start( $sql, @bind ); + } +} + +sub _query_end { + my ( $self, $sql, @bind ) = @_; + + if ( $self->debug ) { + @bind = $self->_fix_bind_params(@bind); + $self->debugobj->query_end( $sql, @bind ); + } +} + sub _dbh_execute { my ($self, $dbh, $op, $extra_bind, $ident, $bind_attributes, @args) = @_; - - if( blessed($ident) && $ident->isa("DBIx::Class::ResultSource") ) { - $ident = $ident->from(); - } my ($sql, $bind) = $self->_prep_for_execute($op, $extra_bind, $ident, \@args); - if ($self->debug) { - my @debug_bind = - map { defined ($_ && $_->[1]) ? qq{'$_->[1]'} : q{'NULL'} } @$bind; - $self->debugobj->query_start($sql, @debug_bind); - } + $self->_query_start( $sql, @$bind ); my $sth = $self->sth($sql,$op); - my $placeholder_index = 1; + my $placeholder_index = 1; foreach my $bound (@$bind) { my $attributes = {}; @@ -887,7 +969,8 @@ sub _dbh_execute { } foreach my $data (@data) { - $data = ref $data ? ''.$data : $data; # stringify args + my $ref = ref $data; + $data = $ref && $ref ne 'ARRAY' ? ''.$data : $data; # stringify args (except arrayrefs) $sth->bind_param($placeholder_index, $data, $attributes); $placeholder_index++; @@ -898,33 +981,42 @@ sub _dbh_execute { my $rv = $sth->execute(); $self->throw_exception($sth->errstr) if !$rv; - if ($self->debug) { - my @debug_bind = - map { defined ($_ && $_->[1]) ? qq{'$_->[1]'} : q{'NULL'} } @$bind; - $self->debugobj->query_end($sql, @debug_bind); - } + $self->_query_end( $sql, @$bind ); return (wantarray ? ($rv, $sth, @$bind) : $rv); } sub _execute { my $self = shift; - $self->dbh_do($self->can('_dbh_execute'), @_) + $self->dbh_do('_dbh_execute', @_) } sub insert { my ($self, $source, $to_insert) = @_; - - my $ident = $source->from; + + my $ident = $source->from; my $bind_attributes = $self->source_bind_attributes($source); + my $updated_cols = {}; + + $self->ensure_connected; + foreach my $col ( $source->columns ) { + if ( !defined $to_insert->{$col} ) { + my $col_info = $source->column_info($col); + + if ( $col_info->{auto_nextval} ) { + $updated_cols->{$col} = $to_insert->{$col} = $self->_sequence_fetch( 'nextval', $col_info->{sequence} || $self->_dbh_get_autoinc_seq($self->dbh, $source) ); + } + } + } + $self->_execute('insert' => [], $source, $bind_attributes, $to_insert); - return $to_insert; + return $updated_cols; } ## Still not quite perfect, and EXPERIMENTAL -## Currently it is assumed that all values passed will be "normal", i.e. not +## Currently it is assumed that all values passed will be "normal", i.e. not ## scalar refs, or at least, all the same type as the first set, the statement is ## only prepped once. sub insert_bulk { @@ -933,29 +1025,20 @@ sub insert_bulk { my $table = $source->from; @colvalues{@$cols} = (0..$#$cols); my ($sql, @bind) = $self->sql_maker->insert($table, \%colvalues); - - if ($self->debug) { - my @debug_bind = map { defined $_->[1] ? qq{$_->[1]} : q{'NULL'} } @bind; - $self->debugobj->query_start($sql, @debug_bind); - } + + $self->_query_start( $sql, @bind ); my $sth = $self->sth($sql); # @bind = map { ref $_ ? ''.$_ : $_ } @bind; # stringify args ## This must be an arrayref, else nothing works! - my $tuple_status = []; - - ##use Data::Dumper; - ##print STDERR Dumper( $data, $sql, [@bind] ); - - my $time = time(); ## Get the bind_attributes, if any exist my $bind_attributes = $self->source_bind_attributes($source); ## Bind the values and execute - my $placeholder_index = 1; + my $placeholder_index = 1; foreach my $bound (@bind) { @@ -972,13 +1055,30 @@ sub insert_bulk { $sth->bind_param_array( $placeholder_index, [@data], $attributes ); $placeholder_index++; } - my $rv = $sth->execute_array({ArrayTupleStatus => $tuple_status}); + my $rv = eval { $sth->execute_array({ArrayTupleStatus => $tuple_status}) }; + if (my $err = $@) { + my $i = 0; + ++$i while $i <= $#$tuple_status && !ref $tuple_status->[$i]; + + $self->throw_exception($sth->errstr || "Unexpected populate error: $err") + if ($i > $#$tuple_status); + + require Data::Dumper; + local $Data::Dumper::Terse = 1; + local $Data::Dumper::Indent = 1; + local $Data::Dumper::Useqq = 1; + local $Data::Dumper::Quotekeys = 0; + + $self->throw_exception(sprintf "%s for populate slice:\n%s", + $tuple_status->[$i][1], + Data::Dumper::Dumper( + { map { $cols->[$_] => $data->[$i][$_] } (0 .. $#$cols) } + ), + ); + } $self->throw_exception($sth->errstr) if !$rv; - if ($self->debug) { - my @debug_bind = map { defined $_ ? qq{`$_'} : q{`NULL'} } @bind; - $self->debugobj->query_end($sql, @debug_bind); - } + $self->_query_end( $sql, @bind ); return (wantarray ? ($rv, $sth, @bind) : $rv); } @@ -986,7 +1086,7 @@ sub update { my $self = shift @_; my $source = shift @_; my $bind_attributes = $self->source_bind_attributes($source); - + return $self->_execute('update' => [], $source, $bind_attributes, @_); } @@ -994,44 +1094,452 @@ sub update { sub delete { my $self = shift @_; my $source = shift @_; - - my $bind_attrs = {}; ## If ever it's needed... - + + my $bind_attrs = $self->source_bind_attributes($source); + return $self->_execute('delete' => [], $source, $bind_attrs, @_); } -sub _select { - my ($self, $ident, $select, $condition, $attrs) = @_; - my $order = $attrs->{order_by}; - if (ref $condition eq 'SCALAR') { - $order = $1 if $$condition =~ s/ORDER BY (.*)$//i; +# We were sent here because the $rs contains a complex search +# which will require a subquery to select the correct rows +# (i.e. joined or limited resultsets) +# +# Genarating a single PK column subquery is trivial and supported +# by all RDBMS. However if we have a multicolumn PK, things get ugly. +# Look at _multipk_update_delete() +sub _subq_update_delete { + my $self = shift; + my ($rs, $op, $values) = @_; + + my $rsrc = $rs->result_source; + + # we already check this, but double check naively just in case. Should be removed soon + my $sel = $rs->_resolved_attrs->{select}; + $sel = [ $sel ] unless ref $sel eq 'ARRAY'; + my @pcols = $rsrc->primary_columns; + if (@$sel != @pcols) { + $self->throw_exception ( + 'Subquery update/delete can not be called on resultsets selecting a' + .' number of columns different than the number of primary keys' + ); } - if (exists $attrs->{group_by} || $attrs->{having}) { - $order = { - group_by => $attrs->{group_by}, - having => $attrs->{having}, - ($order ? (order_by => $order) : ()) - }; + + if (@pcols == 1) { + return $self->$op ( + $rsrc, + $op eq 'update' ? $values : (), + { $pcols[0] => { -in => $rs->as_query } }, + ); + } + + else { + return $self->_multipk_update_delete (@_); + } +} + +# ANSI SQL does not provide a reliable way to perform a multicol-PK +# resultset update/delete involving subqueries. So by default resort +# to simple (and inefficient) delete_all style per-row opearations, +# while allowing specific storages to override this with a faster +# implementation. +# +sub _multipk_update_delete { + return shift->_per_row_update_delete (@_); +} + +# This is the default loop used to delete/update rows for multi PK +# resultsets, and used by mysql exclusively (because it can't do anything +# else). +# +# We do not use $row->$op style queries, because resultset update/delete +# is not expected to cascade (this is what delete_all/update_all is for). +# +# There should be no race conditions as the entire operation is rolled +# in a transaction. +# +sub _per_row_update_delete { + my $self = shift; + my ($rs, $op, $values) = @_; + + my $rsrc = $rs->result_source; + my @pcols = $rsrc->primary_columns; + + my $guard = $self->txn_scope_guard; + + # emulate the return value of $sth->execute for non-selects + my $row_cnt = '0E0'; + + my $subrs_cur = $rs->cursor; + while (my @pks = $subrs_cur->next) { + + my $cond; + for my $i (0.. $#pcols) { + $cond->{$pcols[$i]} = $pks[$i]; + } + + $self->$op ( + $rsrc, + $op eq 'update' ? $values : (), + $cond, + ); + + $row_cnt++; } - my $bind_attrs = {}; ## Future support - my @args = ('select', $attrs->{bind}, $ident, $bind_attrs, $select, $condition, $order); + + $guard->commit; + + return $row_cnt; +} + +sub _select { + my $self = shift; + + # localization is neccessary as + # 1) there is no infrastructure to pass this around before SQLA2 + # 2) _select_args sets it and _prep_for_execute consumes it + my $sql_maker = $self->sql_maker; + local $sql_maker->{_dbic_rs_attrs}; + + return $self->_execute($self->_select_args(@_)); +} + +sub _select_args_to_query { + my $self = shift; + + # localization is neccessary as + # 1) there is no infrastructure to pass this around before SQLA2 + # 2) _select_args sets it and _prep_for_execute consumes it + my $sql_maker = $self->sql_maker; + local $sql_maker->{_dbic_rs_attrs}; + + # my ($op, $bind, $ident, $bind_attrs, $select, $cond, $order, $rows, $offset) + # = $self->_select_args($ident, $select, $cond, $attrs); + my ($op, $bind, $ident, $bind_attrs, @args) = + $self->_select_args(@_); + + # my ($sql, $prepared_bind) = $self->_prep_for_execute($op, $bind, $ident, [ $select, $cond, $order, $rows, $offset ]); + my ($sql, $prepared_bind) = $self->_prep_for_execute($op, $bind, $ident, \@args); + $prepared_bind ||= []; + + return wantarray + ? ($sql, $prepared_bind, $bind_attrs) + : \[ "($sql)", @$prepared_bind ] + ; +} + +sub _select_args { + my ($self, $ident, $select, $where, $attrs) = @_; + + my $sql_maker = $self->sql_maker; + $sql_maker->{_dbic_rs_attrs} = { + %$attrs, + select => $select, + from => $ident, + where => $where, + }; + + my ($alias2source, $root_alias) = $self->_resolve_ident_sources ($ident); + + # calculate bind_attrs before possible $ident mangling + my $bind_attrs = {}; + for my $alias (keys %$alias2source) { + my $bindtypes = $self->source_bind_attributes ($alias2source->{$alias}) || {}; + for my $col (keys %$bindtypes) { + + my $fqcn = join ('.', $alias, $col); + $bind_attrs->{$fqcn} = $bindtypes->{$col} if $bindtypes->{$col}; + + # so that unqualified searches can be bound too + $bind_attrs->{$col} = $bind_attrs->{$fqcn} if $alias eq $root_alias; + } + } + + my @limit; if ($attrs->{software_limit} || - $self->sql_maker->_default_limit_syntax eq "GenericSubQ") { + $sql_maker->_default_limit_syntax eq "GenericSubQ") { $attrs->{software_limit} = 1; } else { $self->throw_exception("rows attribute must be positive if present") if (defined($attrs->{rows}) && !($attrs->{rows} > 0)); - push @args, $attrs->{rows}, $attrs->{offset}; + + # MySQL actually recommends this approach. I cringe. + $attrs->{rows} = 2**48 if not defined $attrs->{rows} and defined $attrs->{offset}; + + if ($attrs->{rows} && keys %{$attrs->{collapse}}) { + ($ident, $select, $where, $attrs) + = $self->_adjust_select_args_for_limited_prefetch ($ident, $select, $where, $attrs); + } + else { + push @limit, $attrs->{rows}, $attrs->{offset}; + } } - return $self->_execute(@args); + +### + # This would be the point to deflate anything found in $where + # (and leave $attrs->{bind} intact). Problem is - inflators historically + # expect a row object. And all we have is a resultsource (it is trivial + # to extract deflator coderefs via $alias2source above). + # + # I don't see a way forward other than changing the way deflators are + # invoked, and that's just bad... +### + + my $order = { map + { $attrs->{$_} ? ( $_ => $attrs->{$_} ) : () } + (qw/order_by group_by having _virtual_order_by/ ) + }; + + + return ('select', $attrs->{bind}, $ident, $bind_attrs, $select, $where, $order, @limit); +} + +sub _adjust_select_args_for_limited_prefetch { + my ($self, $from, $select, $where, $attrs) = @_; + + if ($attrs->{group_by} && @{$attrs->{group_by}}) { + $self->throw_exception ('has_many prefetch with limit (rows/offset) is not supported on grouped resultsets'); + } + + $self->throw_exception ('has_many prefetch with limit (rows/offset) is not supported on resultsets with a custom from attribute') + if (ref $from ne 'ARRAY'); + + + # separate attributes + my $sub_attrs = { %$attrs }; + delete $attrs->{$_} for qw/where bind rows offset/; + delete $sub_attrs->{$_} for qw/for collapse select as order_by/; + + my $alias = $attrs->{alias}; + + # create subquery select list + my $sub_select = [ grep { $_ =~ /^$alias\./ } @{$attrs->{select}} ]; + + # bring over all non-collapse-induced order_by into the inner query (if any) + # the outer one will have to keep them all + if (my $ord_cnt = @{$attrs->{order_by}} - @{$attrs->{_collapse_order_by}} ) { + $sub_attrs->{order_by} = [ + @{$attrs->{order_by}}[ 0 .. ($#{$attrs->{order_by}} - $ord_cnt - 1) ] + ]; + } + + # mangle {from} + $from = [ @$from ]; + my $select_root = shift @$from; + my @outer_from = @$from; + + my %inner_joins; + my %join_info = map { $_->[0]{-alias} => $_->[0] } (@$from); + + # in complex search_related chains $alias may *not* be 'me' + # so always include it in the inner join, and also shift away + # from the outer stack, so that the two datasets actually do + # meet + if ($select_root->{-alias} ne $alias) { + $inner_joins{$alias} = 1; + + while (@outer_from && $outer_from[0][0]{-alias} ne $alias) { + shift @outer_from; + } + if (! @outer_from) { + $self->throw_exception ("Unable to find '$alias' in the {from} stack, something is wrong"); + } + + shift @outer_from; # the new subquery will represent this alias, so get rid of it + } + + + # decide which parts of the join will remain on the inside + # + # this is not a very viable optimisation, but it was written + # before I realised this, so might as well remain. We can throw + # away _any_ branches of the join tree that are: + # 1) not mentioned in the condition/order + # 2) left-join leaves (or left-join leaf chains) + # Most of the join ocnditions will not satisfy this, but for real + # complex queries some might, and we might make some RDBMS happy. + # + # + # since we do not have introspectable SQLA, we fall back to ugly + # scanning of raw SQL for WHERE, and for pieces of ORDER BY + # in order to determine what goes into %inner_joins + # It may not be very efficient, but it's a reasonable stop-gap + { + # produce stuff unquoted, so it can be scanned + my $sql_maker = $self->sql_maker; + local $sql_maker->{quote_char}; + + my @order_by = (map + { ref $_ ? $_->[0] : $_ } + $sql_maker->_order_by_chunks ($sub_attrs->{order_by}) + ); + + my $where_sql = $sql_maker->where ($where); + + # sort needed joins + for my $alias (keys %join_info) { + + # any table alias found on a column name in where or order_by + # gets included in %inner_joins + # Also any parent joins that are needed to reach this particular alias + for my $piece ($where_sql, @order_by ) { + if ($piece =~ /\b$alias\./) { + $inner_joins{$alias} = 1; + } + } + } + } + + # scan for non-leaf/non-left joins and mark as needed + # also mark all ancestor joins that are needed to reach this particular alias + # (e.g. join => { cds => 'tracks' } - tracks will bring cds too ) + # + # traverse by the size of the -join_path i.e. reverse depth first + for my $alias (sort { @{$join_info{$b}{-join_path}} <=> @{$join_info{$a}{-join_path}} } (keys %join_info) ) { + + my $j = $join_info{$alias}; + $inner_joins{$alias} = 1 if (! $j->{-join_type} || ($j->{-join_type} !~ /^left$/i) ); + + if ($inner_joins{$alias}) { + $inner_joins{$_} = 1 for (@{$j->{-join_path}}); + } + } + + # construct the inner $from for the subquery + my $inner_from = [ $select_root ]; + for my $j (@$from) { + push @$inner_from, $j if $inner_joins{$j->[0]{-alias}}; + } + + # if a multi-type join was needed in the subquery ("multi" is indicated by + # presence in {collapse}) - add a group_by to simulate the collapse in the subq + + for my $alias (keys %inner_joins) { + + # the dot comes from some weirdness in collapse + # remove after the rewrite + if ($attrs->{collapse}{".$alias"}) { + $sub_attrs->{group_by} = $sub_select; + last; + } + } + + # generate the subquery + my $subq = $self->_select_args_to_query ( + $inner_from, + $sub_select, + $where, + $sub_attrs + ); + + # put it in the new {from} + unshift @outer_from, { + -alias => $alias, + -source_handle => $select_root->{-source_handle}, + $alias => $subq, + }; + + # This is totally horrific - the $where ends up in both the inner and outer query + # Unfortunately not much can be done until SQLA2 introspection arrives, and even + # then if where conditions apply to the *right* side of the prefetch, you may have + # to both filter the inner select (e.g. to apply a limit) and then have to re-filter + # the outer select to exclude joins you didin't want in the first place + # + # OTOH it can be seen as a plus: (notes that this query would make a DBA cry ;) + return (\@outer_from, $select, $where, $attrs); +} + +sub _resolve_ident_sources { + my ($self, $ident) = @_; + + my $alias2source = {}; + my $root_alias; + + # the reason this is so contrived is that $ident may be a {from} + # structure, specifying multiple tables to join + if ( Scalar::Util::blessed($ident) && $ident->isa("DBIx::Class::ResultSource") ) { + # this is compat mode for insert/update/delete which do not deal with aliases + $alias2source->{me} = $ident; + $root_alias = 'me'; + } + elsif (ref $ident eq 'ARRAY') { + + for (@$ident) { + my $tabinfo; + if (ref $_ eq 'HASH') { + $tabinfo = $_; + $root_alias = $tabinfo->{-alias}; + } + if (ref $_ eq 'ARRAY' and ref $_->[0] eq 'HASH') { + $tabinfo = $_->[0]; + } + + $alias2source->{$tabinfo->{-alias}} = $tabinfo->{-source_handle}->resolve + if ($tabinfo->{-source_handle}); + } + } + + return ($alias2source, $root_alias); +} + +# Takes $ident, \@column_names +# +# returns { $column_name => \%column_info, ... } +# also note: this adds -result_source => $rsrc to the column info +# +# usage: +# my $col_sources = $self->_resolve_column_info($ident, [map $_->[0], @{$bind}]); +sub _resolve_column_info { + my ($self, $ident, $colnames) = @_; + my ($alias2src, $root_alias) = $self->_resolve_ident_sources($ident); + + my $sep = $self->_sql_maker_opts->{name_sep} || '.'; + $sep = "\Q$sep\E"; + + my (%return, %converted); + foreach my $col (@$colnames) { + my ($alias, $colname) = $col =~ m/^ (?: ([^$sep]+) $sep)? (.+) $/x; + + # deal with unqualified cols - we assume the main alias for all + # unqualified ones, ugly but can't think of anything better right now + $alias ||= $root_alias; + + my $rsrc = $alias2src->{$alias}; + $return{$col} = $rsrc && { %{$rsrc->column_info($colname)}, -result_source => $rsrc }; + } + return \%return; +} + +# Returns a counting SELECT for a simple count +# query. Abstracted so that a storage could override +# this to { count => 'firstcol' } or whatever makes +# sense as a performance optimization +sub _count_select { + #my ($self, $source, $rs_attrs) = @_; + return { count => '*' }; } +# Returns a SELECT which will end up in the subselect +# There may or may not be a group_by, as the subquery +# might have been called to accomodate a limit +# +# Most databases would be happy with whatever ends up +# here, but some choke in various ways. +# +sub _subq_count_select { + my ($self, $source, $rs_attrs) = @_; + return $rs_attrs->{group_by} if $rs_attrs->{group_by}; + + my @pcols = map { join '.', $rs_attrs->{alias}, $_ } ($source->primary_columns); + return @pcols ? \@pcols : [ 1 ]; +} + + sub source_bind_attributes { my ($self, $source) = @_; - + my $bind_attributes; foreach my $column ($source->columns) { - + my $data_type = $source->column_info($column)->{data_type} || ''; $bind_attributes->{$column} = $self->bind_attribute_by_data_type($data_type) if $data_type; @@ -1055,13 +1563,17 @@ Handle a SQL select statement. sub select { my $self = shift; my ($ident, $select, $condition, $attrs) = @_; - return $self->cursor->new($self, \@_, $attrs); + return $self->cursor_class->new($self, \@_, $attrs); } sub select_single { my $self = shift; my ($rv, $sth, @bind) = $self->_select(@_); my @row = $sth->fetchrow_array; + my @nextrow = $sth->fetchrow_array if @row; + if(@row && @nextrow) { + carp "Query returned more than one row. SQL that returns multiple rows is DEPRECATED for ->find and ->single"; + } # Need to call finish() to work round broken DBDs $sth->finish(); return @row; @@ -1096,7 +1608,7 @@ sub _dbh_sth { sub sth { my ($self, $sql) = @_; - $self->dbh_do($self->can('_dbh_sth'), $sql); + $self->dbh_do('_dbh_sth', $sql); } sub _dbh_columns_info_for { @@ -1158,7 +1670,7 @@ sub _dbh_columns_info_for { sub columns_info_for { my ($self, $table) = @_; - $self->dbh_do($self->can('_dbh_columns_info_for'), $table); + $self->dbh_do('_dbh_columns_info_for', $table); } =head2 last_insert_id @@ -1168,14 +1680,23 @@ Return the row id of the last insert. =cut sub _dbh_last_insert_id { - my ($self, $dbh, $source, $col) = @_; - # XXX This is a SQLite-ism as a default... is there a DBI-generic way? - $dbh->func('last_insert_rowid'); + # All Storage's need to register their own _dbh_last_insert_id + # the old SQLite-based method was highly inappropriate + + my $self = shift; + my $class = ref $self; + $self->throw_exception (<dbh_do($self->can('_dbh_last_insert_id'), @_); + $self->dbh_do('_dbh_last_insert_id', @_); } =head2 sqlt_type @@ -1188,9 +1709,9 @@ sub sqlt_type { shift->dbh->{Driver}->{Name} } =head2 bind_attribute_by_data_type -Given a datatype from column info, returns a database specific bind attribute for -$dbh->bind_param($val,$attribute) or nothing if we will let the database planner -just handle it. +Given a datatype from column info, returns a database specific bind +attribute for C<< $dbh->bind_param($val,$attribute) >> or nothing if we will +let the database planner just handle it. Generally only needed for special case column types, like bytea in postgres. @@ -1200,143 +1721,208 @@ sub bind_attribute_by_data_type { return; } +=head2 is_datatype_numeric + +Given a datatype from column_info, returns a boolean value indicating if +the current RDBMS considers it a numeric value. This controls how +L decides whether to mark the column as +dirty - when the datatype is deemed numeric a C<< != >> comparison will +be performed instead of the usual C. + +=cut + +sub is_datatype_numeric { + my ($self, $dt) = @_; + + return 0 unless $dt; + + return $dt =~ /^ (?: + numeric | int(?:eger)? | (?:tiny|small|medium|big)int | dec(?:imal)? | real | float | double (?: \s+ precision)? | (?:big)?serial + ) $/ix; +} + + =head2 create_ddl_dir (EXPERIMENTAL) =over 4 -=item Arguments: $schema \@databases, $version, $directory, $preversion, $sqlt_args +=item Arguments: $schema \@databases, $version, $directory, $preversion, \%sqlt_args =back Creates a SQL file based on the Schema, for each of the specified -database types, in the given directory. +database engines in C<\@databases> in the given directory. +(note: specify L names, not L driver names). + +Given a previous version number, this will also create a file containing +the ALTER TABLE statements to transform the previous schema into the +current one. Note that these statements may contain C or +C statements that can potentially destroy data. + +The file names are created using the C method below, please +override this method in your schema if you would like a different file +name format. For the ALTER file, the same format is used, replacing +$version in the name with "$preversion-$version". + +See L for a list of values for C<\%sqlt_args>. +The most common value for this would be C<< { add_drop_table => 1 } >> +to have the SQL produced include a C statement for each table +created. For quoting purposes supply C and +C. + +If no arguments are passed, then the following default values are assumed: + +=over 4 + +=item databases - ['MySQL', 'SQLite', 'PostgreSQL'] + +=item version - $schema->schema_version + +=item directory - './' + +=item preversion - + +=back + +By default, C<\%sqlt_args> will have + + { add_drop_table => 1, ignore_constraint_names => 1, ignore_index_names => 1 } + +merged with the hash passed in. To disable any of those features, pass in a +hashref like the following + + { ignore_constraint_names => 0, # ... other options } + Note that this feature is currently EXPERIMENTAL and may not work correctly across all databases, or fully handle complex relationships. +WARNING: Please check all SQL files created, before applying them. + =cut -sub create_ddl_dir -{ +sub create_ddl_dir { my ($self, $schema, $databases, $version, $dir, $preversion, $sqltargs) = @_; - if(!$dir || !-d $dir) - { - warn "No directory given, using ./\n"; + if(!$dir || !-d $dir) { + carp "No directory given, using ./\n"; $dir = "./"; } $databases ||= ['MySQL', 'SQLite', 'PostgreSQL']; $databases = [ $databases ] if(ref($databases) ne 'ARRAY'); - $version ||= $schema->VERSION || '1.x'; - $sqltargs = { ( add_drop_table => 1 ), %{$sqltargs || {}} }; - $self->throw_exception(q{Can't create a ddl file without SQL::Translator 0.08: '} + my $schema_version = $schema->schema_version || '1.x'; + $version ||= $schema_version; + + $sqltargs = { + add_drop_table => 1, + ignore_constraint_names => 1, + ignore_index_names => 1, + %{$sqltargs || {}} + }; + + $self->throw_exception(q{Can't create a ddl file without SQL::Translator 0.09003: '} . $self->_check_sqlt_message . q{'}) if !$self->_check_sqlt_version; - my $sqlt = SQL::Translator->new({ -# debug => 1, - add_drop_table => 1, - }); - foreach my $db (@$databases) - { + my $sqlt = SQL::Translator->new( $sqltargs ); + + $sqlt->parser('SQL::Translator::Parser::DBIx::Class'); + my $sqlt_schema = $sqlt->translate({ data => $schema }) + or $self->throw_exception ($sqlt->error); + + foreach my $db (@$databases) { $sqlt->reset(); - $sqlt->parser('SQL::Translator::Parser::DBIx::Class'); -# $sqlt->parser_args({'DBIx::Class' => $schema); - $sqlt = $self->configure_sqlt($sqlt, $db); - $sqlt->data($schema); + $sqlt->{schema} = $sqlt_schema; $sqlt->producer($db); my $file; - my $filename = $schema->ddl_filename($db, $dir, $version); - if(-e $filename) - { - warn("$filename already exists, skipping $db"); - next; + my $filename = $schema->ddl_filename($db, $version, $dir); + if (-e $filename && ($version eq $schema_version )) { + # if we are dumping the current version, overwrite the DDL + carp "Overwriting existing DDL file - $filename"; + unlink($filename); } my $output = $sqlt->translate; - if(!$output) - { - warn("Failed to translate to $db, skipping. (" . $sqlt->error . ")"); + if(!$output) { + carp("Failed to translate to $db, skipping. (" . $sqlt->error . ")"); next; } - if(!open($file, ">$filename")) - { - $self->throw_exception("Can't open $filename for writing ($!)"); - next; + if(!open($file, ">$filename")) { + $self->throw_exception("Can't open $filename for writing ($!)"); + next; } print $file $output; close($file); - if($preversion) + next unless ($preversion); + + require SQL::Translator::Diff; + + my $prefilename = $schema->ddl_filename($db, $preversion, $dir); + if(!-e $prefilename) { + carp("No previous schema file found ($prefilename)"); + next; + } + + my $difffile = $schema->ddl_filename($db, $version, $dir, $preversion); + if(-e $difffile) { + carp("Overwriting existing diff file - $difffile"); + unlink($difffile); + } + + my $source_schema; { - require SQL::Translator::Diff; + my $t = SQL::Translator->new($sqltargs); + $t->debug( 0 ); + $t->trace( 0 ); - my $prefilename = $schema->ddl_filename($db, $dir, $preversion); -# print "Previous version $prefilename\n"; - if(!-e $prefilename) - { - warn("No previous schema file found ($prefilename)"); - next; - } - #### We need to reparse the SQLite file we just wrote, so that - ## Diff doesnt get all confoosed, and Diff is *very* confused. - ## FIXME: rip Diff to pieces! -# my $target_schema = $sqlt->schema; -# unless ( $target_schema->name ) { -# $target_schema->name( $filename ); -# } - my @input; - push @input, {file => $prefilename, parser => $db}; - push @input, {file => $filename, parser => $db}; - my ( $source_schema, $source_db, $target_schema, $target_db ) = map { - my $file = $_->{'file'}; - my $parser = $_->{'parser'}; - - my $t = SQL::Translator->new; - $t->debug( 0 ); - $t->trace( 0 ); - $t->parser( $parser ) or die $t->error; - my $out = $t->translate( $file ) or die $t->error; - my $schema = $t->schema; - unless ( $schema->name ) { - $schema->name( $file ); - } - ($schema, $parser); - } @input; - - my $diff = SQL::Translator::Diff::schema_diff($source_schema, $db, - $target_schema, $db, - {} - ); - my $difffile = $schema->ddl_filename($db, $dir, $version, $preversion); - print STDERR "Diff: $difffile: $db, $dir, $version, $preversion \n"; - if(-e $difffile) - { - warn("$difffile already exists, skipping"); - next; - } - if(!open $file, ">$difffile") - { - $self->throw_exception("Can't write to $difffile ($!)"); - next; - } - print $file $diff; - close($file); + $t->parser( $db ) + or $self->throw_exception ($t->error); + + my $out = $t->translate( $prefilename ) + or $self->throw_exception ($t->error); + + $source_schema = $t->schema; + + $source_schema->name( $prefilename ) + unless ( $source_schema->name ); } - } -} -sub configure_sqlt() { - my $self = shift; - my $tr = shift; - my $db = shift || $self->sqlt_type; - if ($db eq 'PostgreSQL') { - $tr->quote_table_names(0); - $tr->quote_field_names(0); + # The "new" style of producers have sane normalization and can support + # diffing a SQL file against a DBIC->SQLT schema. Old style ones don't + # And we have to diff parsed SQL against parsed SQL. + my $dest_schema = $sqlt_schema; + + unless ( "SQL::Translator::Producer::$db"->can('preprocess_schema') ) { + my $t = SQL::Translator->new($sqltargs); + $t->debug( 0 ); + $t->trace( 0 ); + + $t->parser( $db ) + or $self->throw_exception ($t->error); + + my $out = $t->translate( $filename ) + or $self->throw_exception ($t->error); + + $dest_schema = $t->schema; + + $dest_schema->name( $filename ) + unless $dest_schema->name; + } + + my $diff = SQL::Translator::Diff::schema_diff($source_schema, $db, + $dest_schema, $db, + $sqltargs + ); + if(!open $file, ">$difffile") { + $self->throw_exception("Can't write to $difffile ($!)"); + next; + } + print $file $diff; + close($file); } - return $tr; } =head2 deployment_statements @@ -1348,8 +1934,9 @@ sub configure_sqlt() { =back Returns the statements used by L and L. -The database driver name is given by C<$type>, though the value from -L is used if it is not specified. + +The L (not L) database driver name can be explicitly +provided in C<$type>, otherwise the result of L is used as default. C<$directory> is used to return statements from files in a previously created L directory and is optional. The filenames are constructed @@ -1367,20 +1954,20 @@ sub deployment_statements { # Need to be connected to get the correct sqlt_type $self->ensure_connected() unless $type; $type ||= $self->sqlt_type; - $version ||= $schema->VERSION || '1.x'; + $version ||= $schema->schema_version || '1.x'; $dir ||= './'; - my $filename = $schema->ddl_filename($type, $dir, $version); + my $filename = $schema->ddl_filename($type, $version, $dir); if(-f $filename) { my $file; - open($file, "<$filename") + open($file, "<$filename") or $self->throw_exception("Can't open $filename ($!)"); my @rows = <$file>; close($file); return join('', @rows); } - $self->throw_exception(q{Can't deploy without SQL::Translator 0.08: '} + $self->throw_exception(q{Can't deploy without SQL::Translator 0.09003: '} . $self->_check_sqlt_message . q{'}) if !$self->_check_sqlt_version; @@ -1388,7 +1975,7 @@ sub deployment_statements { eval qq{use SQL::Translator::Producer::${type}}; $self->throw_exception($@) if $@; - # sources needs to be a parser arg, but for simplicty allow at top level + # sources needs to be a parser arg, but for simplicty allow at top level # coming in $sqltargs->{parser_args}{sources} = delete $sqltargs->{sources} if exists $sqltargs->{sources}; @@ -1396,29 +1983,36 @@ sub deployment_statements { my $tr = SQL::Translator->new(%$sqltargs); SQL::Translator::Parser::DBIx::Class::parse( $tr, $schema ); return "SQL::Translator::Producer::${type}"->can('produce')->($tr); - - return; - } sub deploy { my ($self, $schema, $type, $sqltargs, $dir) = @_; - foreach my $statement ( $self->deployment_statements($schema, $type, undef, $dir, { no_comments => 1, %{ $sqltargs || {} } } ) ) { - foreach my $line ( split(";\n", $statement)) { - next if($line =~ /^--/); - next if(!$line); -# next if($line =~ /^DROP/m); - next if($line =~ /^BEGIN TRANSACTION/m); - next if($line =~ /^COMMIT/m); - next if $line =~ /^\s+$/; # skip whitespace only - $self->debugobj->query_start($line) if $self->debug; - eval { - $self->dbh->do($line); # shouldn't be using ->dbh ? - }; - if ($@) { - warn qq{$@ (running "${line}")}; - } - $self->debugobj->query_end($line) if $self->debug; + my $deploy = sub { + my $line = shift; + return if($line =~ /^--/); + return if(!$line); + # next if($line =~ /^DROP/m); + return if($line =~ /^BEGIN TRANSACTION/m); + return if($line =~ /^COMMIT/m); + return if $line =~ /^\s+$/; # skip whitespace only + $self->_query_start($line); + eval { + $self->dbh->do($line); # shouldn't be using ->dbh ? + }; + if ($@) { + carp qq{$@ (running "${line}")}; + } + $self->_query_end($line); + }; + my @statements = $self->deployment_statements($schema, $type, undef, $dir, { %{ $sqltargs || {} }, no_comments => 1 } ); + if (@statements > 1) { + foreach my $statement (@statements) { + $deploy->( $statement ); + } + } + elsif (@statements == 1) { + foreach my $line ( split(";\n", $statements[0])) { + $deploy->( $line ); } } } @@ -1431,7 +2025,10 @@ Returns the datetime parser class sub datetime_parser { my $self = shift; - return $self->{datetime_parser} ||= $self->build_datetime_parser(@_); + return $self->{datetime_parser} ||= do { + $self->ensure_connected; + $self->build_datetime_parser(@_); + }; } =head2 datetime_parser_type @@ -1462,9 +2059,9 @@ sub build_datetime_parser { my $_check_sqlt_message; # private sub _check_sqlt_version { return $_check_sqlt_version if defined $_check_sqlt_version; - eval 'use SQL::Translator 0.08'; - $_check_sqlt_message = $@ ? $@ : ''; - $_check_sqlt_version = $@ ? 0 : 1; + eval 'use SQL::Translator "0.09003"'; + $_check_sqlt_message = $@ || ''; + $_check_sqlt_version = !$@; } sub _check_sqlt_message { @@ -1473,49 +2070,58 @@ sub build_datetime_parser { } } -sub DESTROY { - my $self = shift; - return if !$self->_dbh; - $self->_verify_pid; - $self->_dbh(undef); -} +=head2 is_replicating -1; +A boolean that reports if a particular L is set to +replicate from a master database. Default is undef, which is the result +returned by databases that don't support replication. -=head1 SQL METHODS +=cut -The module defines a set of methods within the DBIC::SQL::Abstract -namespace. These build on L to provide the -SQL query functions. +sub is_replicating { + return; -The following methods are extended:- +} -=over 4 +=head2 lag_behind_master -=item delete +Returns a number that represents a certain amount of lag behind a master db +when a given storage is replicating. The number is database dependent, but +starts at zero and increases with the amount of lag. Default in undef -=item insert +=cut -=item select +sub lag_behind_master { + return; +} -=item update +sub DESTROY { + my $self = shift; + return if !$self->_dbh; + $self->_verify_pid; + $self->_dbh(undef); +} -=item limit_dialect +1; -See L for details. -For setting, this method is deprecated in favor of L. +=head1 USAGE NOTES -=item quote_char +=head2 DBIx::Class and AutoCommit -See L for details. -For setting, this method is deprecated in favor of L. +DBIx::Class can do some wonderful magic with handling exceptions, +disconnections, and transactions when you use C<< AutoCommit => 1 >> +combined with C for transaction support. -=item name_sep +If you set C<< AutoCommit => 0 >> in your connect info, then you are always +in an assumed transaction between commits, and you're telling us you'd +like to manage that manually. A lot of the magic protections offered by +this module will go away. We can't protect you from exceptions due to database +disconnects because we don't know anything about how to restart your +transactions. You're on your own for handling all sorts of exceptional +cases if you choose the C<< AutoCommit => 0 >> path, just as you would +be with raw DBI. -See L for details. -For setting, this method is deprecated in favor of L. -=back =head1 AUTHORS