From: Peter Rabbitson Date: Thu, 21 Oct 2010 18:04:21 +0000 (+0200) Subject: -ident implementation X-Git-Tag: v0.08124~27 X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits%2FDBIx-Class.git;a=commitdiff_plain;h=e66002835945589b7174d524ba5add64e714abd2;hp=4783a9a4721bc599e46de0ef2eed00a900325396 -ident implementation --- diff --git a/Changes b/Changes index c1291c7..4a609e2 100644 --- a/Changes +++ b/Changes @@ -1,6 +1,8 @@ Revision history for DBIx::Class * New Features / Changes + - Add new -ident "function" indicating rhs is a column name + { col => { -ident => 'othercol' } } vs { col => \'othercol' } - Extend 'proxy' relationship attribute - Use DBIx::Class::Storage::Debug::PrettyPrint when the environment variable DBIC_TRACE_PROFILE is set, see diff --git a/Makefile.PL b/Makefile.PL index 531c3a7..5fe648b 100644 --- a/Makefile.PL +++ b/Makefile.PL @@ -64,7 +64,7 @@ my $runtime_requires = { 'MRO::Compat' => '0.09', 'Module::Find' => '0.06', 'Path::Class' => '0.18', - 'SQL::Abstract' => '1.68', + 'SQL::Abstract' => '1.69', 'Sub::Name' => '0.04', 'Variable::Magic' => '0.44', 'Data::Dumper::Concise' => '1.000', diff --git a/lib/DBIx/Class/Manual/Cookbook.pod b/lib/DBIx/Class/Manual/Cookbook.pod index 4f599b7..83f7cee 100644 --- a/lib/DBIx/Class/Manual/Cookbook.pod +++ b/lib/DBIx/Class/Manual/Cookbook.pod @@ -349,7 +349,7 @@ from, select, and +select attributes. my $rs = $cdrs->search({ year => { '=' => $cdrs->search( - { artist_id => { '=' => \'me.artist_id' } }, + { artist_id => { '=' => { -ident => 'me.artist_id' } } }, { alias => 'inner' } )->get_column('year')->max_rs->as_query, }, diff --git a/lib/DBIx/Class/Manual/FAQ.pod b/lib/DBIx/Class/Manual/FAQ.pod index bbdfcf5..102c4e9 100644 --- a/lib/DBIx/Class/Manual/FAQ.pod +++ b/lib/DBIx/Class/Manual/FAQ.pod @@ -381,10 +381,11 @@ the rows at once. =item .. update a column using data from another column? -To stop the column name from being quoted, you'll need to supply a -scalar reference: +To stop the column name from being quoted, you'll need to tell DBIC +that the right hand side is an SQL identity (it will be quoted +properly if you have quoting enabled): - ->update({ somecolumn => \'othercolumn' }) + ->update({ somecolumn => { -ident => 'othercolumn' } }) This method will not retrieve the new value and put it in your Row object. To fetch the new value, use the C method on @@ -401,7 +402,7 @@ the Row. To update and refresh at once, chain your calls: - $row->update({ 'somecolumn' => \'othercolumn' })->discard_changes; + $row->update({ 'somecolumn' => { -ident => 'othercolumn' } })->discard_changes; =item .. store JSON/YAML in a column and have it deflate/inflate automatically? diff --git a/lib/DBIx/Class/SQLMaker.pm b/lib/DBIx/Class/SQLMaker.pm index f43aa0b..fe701ca 100644 --- a/lib/DBIx/Class/SQLMaker.pm +++ b/lib/DBIx/Class/SQLMaker.pm @@ -24,6 +24,8 @@ Currently the enhancements to L are: =item * Support of C<...FOR UPDATE> type of select statement modifiers +=item * The -ident operator + =back =cut @@ -79,6 +81,38 @@ BEGIN { # as the value to abuse with MSSQL ordered subqueries) sub __max_int { 0xFFFFFFFF }; +sub new { + my $self = shift->next::method(@_); + + # use the same coderef, it is prepared to handle both cases + push @{$self->{special_ops}}, { + regex => qr/^ ident $/xi, handler => '_where_op_IDENT', + }; + push @{$self->{unary_ops}}, { + regex => qr/^ ident $/xi, handler => '_where_op_IDENT', + }; + + $self; +} + +sub _where_op_IDENT { + my $self = shift; + my ($op, $rhs) = splice @_, -2; + if (ref $rhs) { + croak "-$op takes a single scalar argument (a quotable identifier)"; + } + + # in case we are called as a top level special op + my $lhs = shift; + + $_ = $self->_convert($self->_quote($_)) for ($lhs, $rhs); + + return $lhs + ? "$lhs = $rhs" + : $rhs + ; +} + # Handle limit-dialect selection sub select { my ($self, $table, $fields, $where, $rs_attrs, $limit, $offset) = @_; diff --git a/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm b/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm index 3d463ba..d976f38 100644 --- a/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm +++ b/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm @@ -48,7 +48,7 @@ DBIx::Class::Storage::DBI::Oracle::Generic - Oracle Support for DBIx::Class my $rs = $schema->resultset('Person')->search({}, { 'start_with' => { 'firstname' => 'foo', 'lastname' => 'bar' }, - 'connect_by' => { 'parentid' => { '-prior' => \'persionid' }, + 'connect_by' => { 'parentid' => { '-prior' => { -ident => 'personid' } }, 'order_siblings_by' => { -asc => 'name' }, }; ); @@ -62,7 +62,7 @@ DBIx::Class::Storage::DBI::Oracle::Generic - Oracle Support for DBIx::Class # START WITH # firstname = 'foo' and lastname = 'bar' # CONNECT BY - # parentid = prior persionid + # parentid = prior personid # ORDER SIBLINGS BY # firstname ASC diff --git a/t/73oracle.t b/t/73oracle.t index 540ce4e..c81f0fb 100644 --- a/t/73oracle.t +++ b/t/73oracle.t @@ -361,13 +361,13 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { ); $schema->resultset('Artist')->find({ name => 'cycle-root' }) - ->update({ parentid => \'artistid' }); + ->update({ parentid => { -ident => 'artistid' } }); # select the whole tree { my $rs = $schema->resultset('Artist')->search({}, { start_with => { name => 'root' }, - connect_by => { parentid => { -prior => \ 'artistid' } }, + connect_by => { parentid => { -prior => { -ident => 'artistid' } } }, }); is_same_sql_bind ( @@ -409,7 +409,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { my $rs = $schema->resultset('Artist')->search({}, { start_with => { name => 'root' }, - connect_by => { parentid => { -prior => \ 'artistid' } }, + connect_by => { parentid => { -prior => { -ident => 'artistid' } } }, order_siblings_by => { -desc => 'name' }, }); @@ -436,7 +436,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { { my $rs = $schema->resultset('Artist')->search({ parentid => undef }, { start_with => { name => 'root' }, - connect_by => { parentid => { -prior => \ 'artistid' } }, + connect_by => { parentid => { -prior => { -ident => 'artistid' } } }, }); is_same_sql_bind ( @@ -469,7 +469,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { { join => 'cds', start_with => { 'me.name' => 'root' }, - connect_by => { parentid => { -prior => \ 'artistid' } }, + connect_by => { parentid => { -prior => { -ident => 'artistid' } } }, } ); @@ -513,7 +513,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { { my $rs = $schema->resultset('Artist')->search({}, { start_with => { name => 'root' }, - connect_by => { parentid => { -prior => \ 'artistid' } }, + connect_by => { parentid => { -prior => { -ident => 'artistid' } } }, order_by => { -asc => [ 'LEVEL', 'name' ] }, }); @@ -558,7 +558,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { my $rs = $schema->resultset('Artist')->search({}, { start_with => { name => 'root' }, - connect_by => { parentid => { -prior => \ 'artistid' } }, + connect_by => { parentid => { -prior => { -ident => 'artistid' } } }, order_by => { -asc => 'name' }, rows => 2, }); @@ -615,7 +615,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { my $rs = $schema->resultset('Artist')->search({}, { select => ['count(rank)'], start_with => { name => 'root' }, - connect_by => { parentid => { -prior => \ 'artistid' } }, + connect_by => { parentid => { -prior => { -ident => 'artistid' } } }, group_by => ['rank'], having => { 'count(rank)' => { '<', 2 } }, }); @@ -644,7 +644,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { { my $rs = $schema->resultset('Artist')->search({}, { start_with => { name => 'cycle-root' }, - connect_by => { parentid => { -prior => \ 'artistid' } }, + connect_by => { parentid => { -prior => { -ident => 'artistid' } } }, }); eval { $rs->get_column ('name')->all }; if ( $@ =~ /ORA-01436/ ){ # ORA-01436: CONNECT BY loop in user data @@ -663,7 +663,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) { my $rs = $schema->resultset('Artist')->search({}, { start_with => { name => 'cycle-root' }, '+select' => [ \ 'CONNECT_BY_ISCYCLE' ], - connect_by_nocycle => { parentid => { -prior => \ 'artistid' } }, + connect_by_nocycle => { parentid => { -prior => { -ident => 'artistid' } } }, }); is_same_sql_bind ( diff --git a/t/sqlmaker/op_ident.t b/t/sqlmaker/op_ident.t new file mode 100644 index 0000000..46668a6 --- /dev/null +++ b/t/sqlmaker/op_ident.t @@ -0,0 +1,41 @@ +use strict; +use warnings; + +use Test::More; + +use lib qw(t/lib); +use DBIC::SqlMakerTest; + +use_ok('DBICTest'); + +my $schema = DBICTest->init_schema(); + +my $sql_maker = $schema->storage->sql_maker; + +for my $q ('', '"') { + + $sql_maker->quote_char($q); + + is_same_sql_bind ( + \[ $sql_maker->select ('artist', '*', { 'artist.name' => { -ident => 'artist.pseudonym' } } ) ], + "SELECT * + FROM ${q}artist${q} + WHERE ${q}artist${q}.${q}name${q} = ${q}artist${q}.${q}pseudonym${q} + ", + [], + ); + + is_same_sql_bind ( + \[ $sql_maker->update ('artist', + { 'artist.name' => { -ident => 'artist.pseudonym' } }, + { 'artist.name' => { '!=' => { -ident => 'artist.pseudonym' } } }, + ) ], + "UPDATE ${q}artist${q} + SET ${q}artist${q}.${q}name${q} = ${q}artist${q}.${q}pseudonym${q} + WHERE ${q}artist${q}.${q}name${q} != ${q}artist${q}.${q}pseudonym${q} + ", + [], + ); +} + +done_testing; diff --git a/t/sqlmaker/oracle.t b/t/sqlmaker/oracle.t index 6b9e6f9..8a2573c 100644 --- a/t/sqlmaker/oracle.t +++ b/t/sqlmaker/oracle.t @@ -20,10 +20,10 @@ my @handle_tests = ( msg => 'Simple: "parentid" = PRIOR artistid', }, { - connect_by => { 'parentid' => { '!=' => { '-prior' => \'artistid' } } }, - stmt => '"parentid" != ( PRIOR artistid )', + connect_by => { 'parentid' => { '!=' => { '-prior' => { -ident => 'artistid' } } } }, + stmt => '"parentid" != ( PRIOR "artistid" )', bind => [], - msg => 'Simple: "parentid" != ( PRIOR artistid )', + msg => 'Simple: "parentid" != ( PRIOR "artistid" )', }, # Examples from http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm @@ -31,9 +31,9 @@ my @handle_tests = ( { connect_by => [ last_name => { '!=' => 'King' }, - manager_id => { '-prior' => \'employee_id' }, + manager_id => { '-prior' => { -ident => 'employee_id' } }, ], - stmt => '( "last_name" != ? OR "manager_id" = PRIOR employee_id )', + stmt => '( "last_name" != ? OR "manager_id" = PRIOR "employee_id" )', bind => ['King'], msg => 'oracle.com example #1', }, @@ -41,10 +41,10 @@ my @handle_tests = ( # PRIOR account_mgr_id = customer_id ... { connect_by => { - manager_id => { '-prior' => \'employee_id' }, + manager_id => { '-prior' => { -ident => 'employee_id' } }, customer_id => { '>', { '-prior' => \'account_mgr_id' } }, }, - stmt => '( "customer_id" > ( PRIOR account_mgr_id ) AND "manager_id" = PRIOR employee_id )', + stmt => '( "customer_id" > ( PRIOR account_mgr_id ) AND "manager_id" = PRIOR "employee_id" )', bind => [], msg => 'oracle.com example #2', },