Skip Menu |
 

This queue is for tickets about the DBIx-Class CPAN distribution.

Report information
The Basics
Id: 39121
Status: stalled
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: alex [...] keusch.at
Cc: rbo [...] cpan.org
AdminCc:

Bug Information
Severity: Wishlist
Broken in: (no value)
Fixed in: (no value)



Subject: [patch] hierarchical queries - oracle specific
MIME-Version: 1.0
X-Mailer: MIME-tools 5.426 (Entity 5.426)
Charset: utf8
X-RT-Original-Encoding: utf-8
Content-Type: multipart/mixed; boundary="----------=_1220972642-31906-8"
Content-Length: 0
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: binary
Content-Length: 352
Download (untitled) / with headers
text/plain 352b
Oracle supports a Oracle specific syntax to run hierarchical queries: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm This patch enables the Oracle storage modul to handle the resultset-attributes 'connect_by', 'start_with', and 'order_siblings_by'. This makes it possible to run hierarchical queries through DBIx::Class.
Subject: oracle-hierarchical-queries.patch
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----------=_1220972642-31906-7"
X-Mailer: MIME-tools 5.426 (Entity 5.426)
Charset: utf8
Content-Length: 0
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: iso-8859-1
Content-Length: 0
Content-Type: text/x-patch; name="oracle-hierarchical-queries.patch"
Content-Disposition: inline; filename="oracle-hierarchical-queries.patch"
Content-Transfer-Encoding: binary
Content-Length: 9573
diff -Nurb DBIx-Class-0.08010-orig/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm DBIx-Class-0.08010/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm --- DBIx-Class-0.08010-orig/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm 2007-09-04 20:33:11.000000000 +0200 +++ DBIx-Class-0.08010/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm 2008-09-09 16:47:57.000000000 +0200 @@ -4,9 +4,69 @@ use strict; use warnings; +__PACKAGE__->sql_maker_class('DBIC::SQL::Abstract::Oracle'); + +BEGIN { + package DBIC::SQL::Abstract::Oracle; + + use base qw( DBIC::SQL::Abstract ); + + sub select { + my ($self, $table, $fields, $where, $order, @rest) = @_; + + my $db_specific_attrs = pop @rest; + + my ($sql, @bind) = $self->SUPER::select($table, $fields, $where, $order, @rest); + my ($cb_sql, @cb_bind) = $self->_connect_by($db_specific_attrs); + $sql .= $cb_sql; + push @bind, @cb_bind; + + return wantarray ? ($sql, @bind) : $sql; + } + + sub _connect_by { + my ($self, $attrs) = @_; + my $sql = ''; + my @bind; + + if ( ref($attrs) eq 'HASH' ) { + if ( $attrs->{'start_with'} ) { + my ($ws, @wb) = $self->_recurse_where( $attrs->{'start_with'} ); + $sql .= $self->_sqlcase(' start with ') . $ws; + push @bind, @wb; + } + if ( my $connect_by = $attrs->{'connect_by'}) { + $sql .= $self->_sqlcase(' connect by'); + foreach my $key ( keys %$connect_by ) { + $sql .= " $key = " . $connect_by->{$key}; + } + } + if ( $attrs->{'order_siblings_by'} ) { + $sql .= $self->_order_siblings_by( $attrs->{'order_siblings_by'} ); + } + } + + return wantarray ? ($sql, @bind) : $sql; + } + + sub _order_siblings_by { + my $self = shift; + my $ref = ref $_[0]; + + my @vals = $ref eq 'ARRAY' ? @{$_[0]} : + $ref eq 'SCALAR' ? ${$_[0]} : + $ref eq '' ? $_[0] : + puke( "Unsupported data struct $ref for ORDER SIBILINGS BY" ); + + my $val = join ', ', map { $self->_quote($_) } @vals; + return $val ? $self->_sqlcase(' order siblings by')." $val" : ''; + } + +} # end of BEGIN - package DBIC::SQL::Abstract::Oracle + =head1 NAME -DBIx::Class::Storage::DBI::Oracle - Automatic primary key class for Oracle +DBIx::Class::Storage::DBI::Oracle - Automatic primary key class and "Connect By" support for Oracle =head1 SYNOPSIS @@ -15,12 +75,67 @@ __PACKAGE__->set_primary_key('id'); __PACKAGE__->sequence('mysequence'); + # with a resultset using a hierarchical relationship + my $rs = $schema->resultset('Person')->search({}, + { + 'start_with' => { 'firstname' => 'Foo', 'lastname' => 'Bar' }, + 'connect_by' => { 'parentid' => 'prior persionid'}, + 'order_siblings_by' => 'firstname ASC', + }; + ); + =head1 DESCRIPTION -This class implements autoincrements for Oracle. +This class implements autoincrements for Oracle and adds support for Oracle +specific hierarchical queries. =head1 METHODS +=head1 ATTRIBUTES + +Following additional attributes can be used in resultsets. + +=head2 connect_by + +=over 4 + +=item Value: \%connect_by + +=back + +A hashref of conditions used to specify the relationship between parent rows +and child rows of the hierarchy. + + connect_by => { parentid => 'prior personid' } + +=head2 start_with + +=over 4 + +=item Value: \%condition + +=back + +A hashref of conditions which specify the root row(s) of the hierarchy. + +It uses the same syntax as L<DBIx::Class::ResultSet/search> + + start_with => { firstname => 'Foo', lastname => 'Bar' } + +=head2 order_siblings_by + +=over 4 + +=item Value: ($order_siblings_by | \@order_siblings_by) + +=back + +Which column(s) to order the siblings by. + +It uses the same syntax as L<DBIx::Class::ResultSet/order_by> + + 'order_siblings_by' => 'firstname ASC' + =cut use Carp::Clan qw/^DBIx::Class/; @@ -29,6 +144,21 @@ # __PACKAGE__->load_components(qw/PK::Auto/); +sub _db_specific_attrs { + my ($self, $attrs) = @_; + + my $rv = {}; + if ( $attrs->{connect_by} || $attrs->{start_with} || $attrs->{order_siblings_by} ) { + $rv = { + connect_by => $attrs->{connect_by}, + start_with => $attrs->{start_with}, + order_siblings_by => $attrs->{order_siblings_by}, + } + } + + return $rv; +} + sub _dbh_last_insert_id { my ($self, $dbh, $source, $col) = @_; my $seq = ($source->column_info($col)->{sequence} ||= $self->get_autoinc_seq($source,$col)); diff -Nurb DBIx-Class-0.08010-orig/lib/DBIx/Class/Storage/DBI.pm DBIx-Class-0.08010/lib/DBIx/Class/Storage/DBI.pm --- DBIx-Class-0.08010-orig/lib/DBIx/Class/Storage/DBI.pm 2008-02-27 14:49:09.000000000 +0100 +++ DBIx-Class-0.08010/lib/DBIx/Class/Storage/DBI.pm 2008-09-09 14:31:17.000000000 +0200 @@ -1125,9 +1125,18 @@ push @args, $attrs->{rows}, $attrs->{offset}; } + # give DB specific DBI subclasses the chance to pass DB specific attributes to + # the spl_maker, without overriding the whole _select method + if (my $db_specific_attrs = $self->_db_specific_attrs($attrs) ) { + push @args, $db_specific_attrs; + } + return $self->_execute(@args); } +# override this method to add DB specific attributes +sub _db_specific_attrs { undef; } + sub source_bind_attributes { my ($self, $source) = @_; diff -Nurb DBIx-Class-0.08010-orig/t/73oracle.t DBIx-Class-0.08010/t/73oracle.t --- DBIx-Class-0.08010-orig/t/73oracle.t 2007-08-11 23:07:59.000000000 +0200 +++ DBIx-Class-0.08010/t/73oracle.t 2008-09-09 16:52:47.000000000 +0200 @@ -11,7 +11,7 @@ 'Warning: This test drops and creates tables called \'artist\', \'cd\' and \'track\'' unless ($dsn && $user && $pass); -plan tests => 7; +plan tests => 14; my $schema = DBICTest::Schema->connect($dsn, $user, $pass); @@ -24,10 +24,12 @@ $dbh->do("DROP TABLE track"); }; $dbh->do("CREATE SEQUENCE artist_seq START WITH 1 MAXVALUE 999999 MINVALUE 0"); -$dbh->do("CREATE TABLE artist (artistid NUMBER(12), name VARCHAR(255))"); +$dbh->do("CREATE TABLE artist (artistid NUMBER(12), parentid NUMBER(12), name VARCHAR(255))"); $dbh->do("CREATE TABLE cd (cdid NUMBER(12), artist NUMBER(12), title VARCHAR(255), year VARCHAR(4))"); $dbh->do("CREATE TABLE track (trackid NUMBER(12), cd NUMBER(12), position NUMBER(12), title VARCHAR(255), last_updated_on DATE)"); +$schema->class('Artist')->add_columns('parentid'); + $dbh->do("ALTER TABLE artist ADD (CONSTRAINT artist_pk PRIMARY KEY (artistid))"); $dbh->do(qq{ CREATE OR REPLACE TRIGGER artist_insert_trg @@ -95,6 +97,71 @@ is( scalar @results, 1, "Group by with limit OK" ); } +# create a tree of artists +my $afoo_id = $schema->resultset('Artist')->create({ name => 'afoo', parentid => 1 })->id; +$schema->resultset('Artist')->create({ name => 'bfoo', parentid => 1 }); +my $cfoo_id = $schema->resultset('Artist')->create({ name => 'cfoo', parentid => $afoo_id })->id; +$schema->resultset('Artist')->create({ name => 'dfoo', parentid => $cfoo_id }); +my $xfoo_id = $schema->resultset('Artist')->create({ name => 'xfoo' })->id; + +# create some cds and tracks +$schema->resultset('CD')->create({ cdid => 2, artist => $cfoo_id, title => "cfoo's cd", year => '2008' }); +$schema->resultset('Track')->create({ trackid => 2, cd => 2, position => 1, title => 'Track1 cfoo' }); +$schema->resultset('CD')->create({ cdid => 3, artist => $xfoo_id, title => "xfoo's cd", year => '2008' }); +$schema->resultset('Track')->create({ trackid => 3, cd => 3, position => 1, title => 'Track1 xfoo' }); + +{ + my $rs = $schema->resultset('Artist')->search({}, # get the whole tree + { + 'start_with' => { 'name' => 'foo' }, + 'connect_by' => { 'parentid' => 'prior artistid'}, + }); + is( $rs->count, 5, 'Connect By count ok' ); + my $ok = 1; + foreach my $node_name (qw(foo afoo cfoo dfoo bfoo)) { + $ok = 0 if $rs->next->name ne $node_name; + } + ok( $ok, 'got artist tree'); +} + +{ + # use order siblings by statement + my $rs = $schema->resultset('Artist')->search({}, + { + 'start_with' => { 'name' => 'foo' }, + 'connect_by' => { 'parentid' => 'prior artistid'}, + 'order_siblings_by' => 'name DESC', + }); + my $ok = 1; + foreach my $node_name (qw(foo bfoo afoo cfoo dfoo)) { + $ok = 0 if $rs->next->name ne $node_name; + } + ok( $ok, 'Order Siblings By ok'); +} + +{ + # get the root node + my $rs = $schema->resultset('Artist')->search({ parentid => undef }, + { + 'start_with' => { 'name' => 'dfoo' }, + 'connect_by' => { 'prior parentid' => 'artistid'}, + }); + is( $rs->count, 1, 'root node count ok' ); + ok( $rs->next->name eq 'foo', 'found root node'); +} + +{ + # combine a connect by with a join + my $rs = $schema->resultset('Artist')->search({'cds.title' => { 'like' => '%cd'}}, + { + 'join' => 'cds', + 'start_with' => { 'name' => 'foo' }, + 'connect_by' => { 'parentid' => 'prior artistid'}, + }); + is( $rs->count, 1, 'Connect By with a join; count ok' ); + ok( $rs->next->name eq 'cfoo', 'Connect By with a join; result name ok') +} + # clean up our mess END { if($dbh) {
MIME-Version: 1.0
X-Mailer: MIME-tools 5.426 (Entity 5.426)
Charset: utf8
Message-Id: <rt-3.6.HEAD-31926-1221050226-366.39121-0-0 [...] rt.cpan.org>
Content-Type: multipart/mixed; boundary="----------=_1221050226-31926-35"
From: alex [...] keusch.at
X-RT-Original-Encoding: utf-8
Content-Length: 0
Content-Disposition: inline
Content-Type: text/plain
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 104
Download (untitled) / with headers
text/plain 104b
With the last patch limiting hierarchical queries does not work. The attached patch fixes this Problem.
MIME-Version: 1.0
X-Mailer: MIME-tools 5.426 (Entity 5.426)
Content-Type: multipart/mixed; boundary="----------=_1221050226-31926-34"
Charset: utf8
Content-Length: 0
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: iso-8859-1
Content-Length: 0
Content-Type: text/x-patch; name="oracle-hierarchical-queries.patch"
Content-Disposition: inline; filename="oracle-hierarchical-queries.patch"
Content-Transfer-Encoding: binary
Content-Length: 11041
diff -Nurb DBIx-Class-0.08010-orig/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm DBIx-Class-0.08010/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm --- DBIx-Class-0.08010-orig/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm 2007-09-04 20:33:11.000000000 +0200 +++ DBIx-Class-0.08010/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm 2008-09-10 16:16:17.000000000 +0200 @@ -4,9 +4,78 @@ use strict; use warnings; +__PACKAGE__->sql_maker_class('DBIC::SQL::Abstract::Oracle'); + +BEGIN { + package DBIC::SQL::Abstract::Oracle; + + use base qw( DBIC::SQL::Abstract ); + + sub select { + my ($self, $table, $fields, $where, $order, @rest) = @_; + + $self->{_db_specific_attrs} = pop @rest; + + my ($sql, @bind) = $self->SUPER::select($table, $fields, $where, $order, @rest); + push @bind, @{$self->{_oracle_connect_by_binds}}; + + return wantarray ? ($sql, @bind) : $sql; + } + + sub _emulate_limit { + my ( $self, $syntax, $sql, $order, $rows, $offset ) = @_; + + my ($cb_sql, @cb_bind) = $self->_connect_by(); + $sql .= $cb_sql; + $self->{_oracle_connect_by_binds} = \@cb_bind; + + return $self->SUPER::_emulate_limit($syntax, $sql, $order, $rows, $offset); + } + + sub _connect_by { + my ($self) = @_; + my $attrs = $self->{_db_specific_attrs}; + my $sql = ''; + my @bind; + + if ( ref($attrs) eq 'HASH' ) { + if ( $attrs->{'start_with'} ) { + my ($ws, @wb) = $self->_recurse_where( $attrs->{'start_with'} ); + $sql .= $self->_sqlcase(' start with ') . $ws; + push @bind, @wb; + } + if ( my $connect_by = $attrs->{'connect_by'}) { + $sql .= $self->_sqlcase(' connect by'); + foreach my $key ( keys %$connect_by ) { + $sql .= " $key = " . $connect_by->{$key}; + } + } + if ( $attrs->{'order_siblings_by'} ) { + $sql .= $self->_order_siblings_by( $attrs->{'order_siblings_by'} ); + } + } + + return wantarray ? ($sql, @bind) : $sql; + } + + sub _order_siblings_by { + my $self = shift; + my $ref = ref $_[0]; + + my @vals = $ref eq 'ARRAY' ? @{$_[0]} : + $ref eq 'SCALAR' ? ${$_[0]} : + $ref eq '' ? $_[0] : + puke( "Unsupported data struct $ref for ORDER SIBILINGS BY" ); + + my $val = join ', ', map { $self->_quote($_) } @vals; + return $val ? $self->_sqlcase(' order siblings by')." $val" : ''; + } + +} # end of BEGIN - package DBIC::SQL::Abstract::Oracle + =head1 NAME -DBIx::Class::Storage::DBI::Oracle - Automatic primary key class for Oracle +DBIx::Class::Storage::DBI::Oracle - Automatic primary key class and "Connect By" support for Oracle =head1 SYNOPSIS @@ -15,12 +84,67 @@ __PACKAGE__->set_primary_key('id'); __PACKAGE__->sequence('mysequence'); + # with a resultset using a hierarchical relationship + my $rs = $schema->resultset('Person')->search({}, + { + 'start_with' => { 'firstname' => 'Foo', 'lastname' => 'Bar' }, + 'connect_by' => { 'parentid' => 'prior persionid'}, + 'order_siblings_by' => 'firstname ASC', + }; + ); + =head1 DESCRIPTION -This class implements autoincrements for Oracle. +This class implements autoincrements for Oracle and adds support for Oracle +specific hierarchical queries. =head1 METHODS +=head1 ATTRIBUTES + +Following additional attributes can be used in resultsets. + +=head2 connect_by + +=over 4 + +=item Value: \%connect_by + +=back + +A hashref of conditions used to specify the relationship between parent rows +and child rows of the hierarchy. + + connect_by => { parentid => 'prior personid' } + +=head2 start_with + +=over 4 + +=item Value: \%condition + +=back + +A hashref of conditions which specify the root row(s) of the hierarchy. + +It uses the same syntax as L<DBIx::Class::ResultSet/search> + + start_with => { firstname => 'Foo', lastname => 'Bar' } + +=head2 order_siblings_by + +=over 4 + +=item Value: ($order_siblings_by | \@order_siblings_by) + +=back + +Which column(s) to order the siblings by. + +It uses the same syntax as L<DBIx::Class::ResultSet/order_by> + + 'order_siblings_by' => 'firstname ASC' + =cut use Carp::Clan qw/^DBIx::Class/; @@ -29,6 +153,21 @@ # __PACKAGE__->load_components(qw/PK::Auto/); +sub _db_specific_attrs { + my ($self, $attrs) = @_; + + my $rv = {}; + if ( $attrs->{connect_by} || $attrs->{start_with} || $attrs->{order_siblings_by} ) { + $rv = { + connect_by => $attrs->{connect_by}, + start_with => $attrs->{start_with}, + order_siblings_by => $attrs->{order_siblings_by}, + } + } + + return $rv; +} + sub _dbh_last_insert_id { my ($self, $dbh, $source, $col) = @_; my $seq = ($source->column_info($col)->{sequence} ||= $self->get_autoinc_seq($source,$col)); diff -Nurb DBIx-Class-0.08010-orig/lib/DBIx/Class/Storage/DBI.pm DBIx-Class-0.08010/lib/DBIx/Class/Storage/DBI.pm --- DBIx-Class-0.08010-orig/lib/DBIx/Class/Storage/DBI.pm 2008-02-27 14:49:09.000000000 +0100 +++ DBIx-Class-0.08010/lib/DBIx/Class/Storage/DBI.pm 2008-09-09 14:31:17.000000000 +0200 @@ -1125,9 +1125,18 @@ push @args, $attrs->{rows}, $attrs->{offset}; } + # give DB specific DBI subclasses the chance to pass DB specific attributes to + # the spl_maker, without overriding the whole _select method + if (my $db_specific_attrs = $self->_db_specific_attrs($attrs) ) { + push @args, $db_specific_attrs; + } + return $self->_execute(@args); } +# override this method to add DB specific attributes +sub _db_specific_attrs { undef; } + sub source_bind_attributes { my ($self, $source) = @_; diff -Nurb DBIx-Class-0.08010-orig/t/73oracle.t DBIx-Class-0.08010/t/73oracle.t --- DBIx-Class-0.08010-orig/t/73oracle.t 2007-08-11 23:07:59.000000000 +0200 +++ DBIx-Class-0.08010/t/73oracle.t 2008-09-10 16:23:00.000000000 +0200 @@ -11,7 +11,7 @@ 'Warning: This test drops and creates tables called \'artist\', \'cd\' and \'track\'' unless ($dsn && $user && $pass); -plan tests => 7; +plan tests => 17; my $schema = DBICTest::Schema->connect($dsn, $user, $pass); @@ -24,10 +24,12 @@ $dbh->do("DROP TABLE track"); }; $dbh->do("CREATE SEQUENCE artist_seq START WITH 1 MAXVALUE 999999 MINVALUE 0"); -$dbh->do("CREATE TABLE artist (artistid NUMBER(12), name VARCHAR(255))"); +$dbh->do("CREATE TABLE artist (artistid NUMBER(12), parentid NUMBER(12), name VARCHAR(255))"); $dbh->do("CREATE TABLE cd (cdid NUMBER(12), artist NUMBER(12), title VARCHAR(255), year VARCHAR(4))"); $dbh->do("CREATE TABLE track (trackid NUMBER(12), cd NUMBER(12), position NUMBER(12), title VARCHAR(255), last_updated_on DATE)"); +$schema->class('Artist')->add_columns('parentid'); + $dbh->do("ALTER TABLE artist ADD (CONSTRAINT artist_pk PRIMARY KEY (artistid))"); $dbh->do(qq{ CREATE OR REPLACE TRIGGER artist_insert_trg @@ -95,6 +97,104 @@ is( scalar @results, 1, "Group by with limit OK" ); } +# create a tree of artists +my $afoo_id = $schema->resultset('Artist')->create({ name => 'afoo', parentid => 1 })->id; +$schema->resultset('Artist')->create({ name => 'bfoo', parentid => 1 }); +my $cfoo_id = $schema->resultset('Artist')->create({ name => 'cfoo', parentid => $afoo_id })->id; +$schema->resultset('Artist')->create({ name => 'dfoo', parentid => $cfoo_id }); +my $xfoo_id = $schema->resultset('Artist')->create({ name => 'xfoo' })->id; + +# create some cds and tracks +$schema->resultset('CD')->create({ cdid => 2, artist => $cfoo_id, title => "cfoo's cd", year => '2008' }); +$schema->resultset('Track')->create({ trackid => 2, cd => 2, position => 1, title => 'Track1 cfoo' }); +$schema->resultset('CD')->create({ cdid => 3, artist => $xfoo_id, title => "xfoo's cd", year => '2008' }); +$schema->resultset('Track')->create({ trackid => 3, cd => 3, position => 1, title => 'Track1 xfoo' }); + +{ + my $rs = $schema->resultset('Artist')->search({}, # get the whole tree + { + 'start_with' => { 'name' => 'foo' }, + 'connect_by' => { 'parentid' => 'prior artistid'}, + }); + is( $rs->count, 5, 'Connect By count ok' ); + my $ok = 1; + foreach my $node_name (qw(foo afoo cfoo dfoo bfoo)) { + $ok = 0 if $rs->next->name ne $node_name; + } + ok( $ok, 'got artist tree'); +} + +{ + # use order siblings by statement + my $rs = $schema->resultset('Artist')->search({}, + { + 'start_with' => { 'name' => 'foo' }, + 'connect_by' => { 'parentid' => 'prior artistid'}, + 'order_siblings_by' => 'name DESC', + }); + my $ok = 1; + foreach my $node_name (qw(foo bfoo afoo cfoo dfoo)) { + $ok = 0 if $rs->next->name ne $node_name; + } + ok( $ok, 'Order Siblings By ok'); +} + +{ + # get the root node + my $rs = $schema->resultset('Artist')->search({ parentid => undef }, + { + 'start_with' => { 'name' => 'dfoo' }, + 'connect_by' => { 'prior parentid' => 'artistid'}, + }); + is( $rs->count, 1, 'root node count ok' ); + ok( $rs->next->name eq 'foo', 'found root node'); +} + +{ + # combine a connect by with a join + my $rs = $schema->resultset('Artist')->search({'cds.title' => { 'like' => '%cd'}}, + { + 'join' => 'cds', + 'start_with' => { 'name' => 'foo' }, + 'connect_by' => { 'parentid' => 'prior artistid'}, + }); + is( $rs->count, 1, 'Connect By with a join; count ok' ); + ok( $rs->next->name eq 'cfoo', 'Connect By with a join; result name ok') +} + +{ + # combine a connect by with order_by + my $rs = $schema->resultset('Artist')->search({}, + { + 'start_with' => { 'name' => 'dfoo' }, + 'connect_by' => { 'prior parentid' => 'artistid'}, + 'order_by' => 'name ASC', + }); + my $ok = 1; + foreach my $node_name (qw(afoo cfoo dfoo foo)) { + $ok = 0 if $rs->next->name ne $node_name; + } + ok( $ok, 'Connect By with a order_by; result name ok'); +} + +{ + # limit a connect by + my $rs = $schema->resultset('Artist')->search({}, + { + 'start_with' => { 'name' => 'dfoo' }, + 'connect_by' => { 'prior parentid' => 'artistid'}, + 'order_by' => 'name ASC', + 'rows' => 2, + 'page' => 1, + }); + is( $rs->count(), 2, 'Connect By; LIMIT count ok' ); + my $ok = 1; + foreach my $node_name (qw(afoo cfoo)) { + $ok = 0 if $rs->next->name ne $node_name; + } + ok( $ok, 'LIMIT a Connect By query ok'); +} + # clean up our mess END { if($dbh) {
MIME-Version: 1.0
X-Spam-Status: No, hits=-2.6 required=8.0 tests=BAYES_00
In-Reply-To: <rt-3.6.HEAD-31926-1221050226-366.39121-5-0 [...] rt.cpan.org>
Content-Disposition: inline
References: <RT-Ticket-39121 [...] rt.cpan.org> <rt-3.6.HEAD-31926-1221050226-366.39121-5-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="utf-8"
X-RT-Original-Encoding: utf-8
Received: from x1.develooper.com (x1.develooper.com [63.251.223.170]) by diesel.bestpractical.com (Postfix) with SMTP id 64BAF19B80A1 for <bug-DBIx-Class [...] rt.cpan.org>; Wed, 24 Sep 2008 11:41:41 -0400 (EDT)
Received: (qmail 646 invoked from network); 24 Sep 2008 15:41:40 -0000
Received: from x16.dev (10.0.100.26) by x1.dev with QMQP; 24 Sep 2008 15:41:40 -0000
Received: from de007061.idaq.com (HELO jules.scsys.co.uk) (217.168.153.160) by 16.mx.develooper.com (qpsmtpd/0.43rc1) with ESMTP; Wed, 24 Sep 2008 08:41:38 -0700
Received: from [217.168.150.167] (helo=agaton.scsys.co.uk) by jules.scsys.co.uk with esmtp (Exim 4.60) (envelope-from <matthewt [...] agaton.scsys.co.uk>) id 1KiWUg-0006Vy-LT for bug-DBIx-Class [...] rt.cpan.org; Wed, 24 Sep 2008 16:41:34 +0100
Received: from matthewt by agaton.scsys.co.uk with local (Exim 4.60) (envelope-from <matthewt [...] agaton.scsys.co.uk>) id 1KiWUg-0003Jy-IG for bug-DBIx-Class [...] rt.cpan.org; Wed, 24 Sep 2008 16:41:34 +0100
Delivered-To: cpan-bug+DBIx-Class [...] diesel.bestpractical.com
Subject: Re: [rt.cpan.org #39121] [patch] hierarchical queries - oracle specific
User-Agent: Mutt/1.5.11
Return-Path: <matthewt [...] agaton.scsys.co.uk>
X-Spam-Check-BY: 16.mx.develooper.com
X-Original-To: bug-DBIx-Class [...] rt.cpan.org
Date: Wed, 24 Sep 2008 16:41:34 +0100
X-Spam-Level: *
Message-Id: <20080924154134.GF9470 [...] agaton.scsys.co.uk>
To: Alexande Keusch via RT <bug-DBIx-Class [...] rt.cpan.org>
From: Matt S Trout <mst [...] shadowcat.co.uk>
RT-Message-ID: <rt-3.6.HEAD-13830-1222270926-268.39121-0-0 [...] rt.cpan.org>
Content-Length: 793
Download (untitled) / with headers
text/plain 793b
On Wed, Sep 10, 2008 at 08:37:11AM -0400, Alexande Keusch via RT wrote: Show quoted text
> Queue: DBIx-Class > Ticket <URL: http://rt.cpan.org/Ticket/Display.html?id=39121 > > > With the last patch limiting hierarchical queries does not work. > > The attached patch fixes this Problem.
You need to send this to the dbix-class mailing list, not RT. The people who maintain the oracle stuff don't do releases of DBIx::Class and rt.cpan.org only sets up release managers as queue users. -- Matt S Trout Need help with your Catalyst or DBIx::Class project? Technical Director http://www.shadowcat.co.uk/catalyst/ Shadowcat Systems Ltd. Want a managed development or deployment platform? http://chainsawblues.vox.com/ http://www.shadowcat.co.uk/servers/
MIME-Version: 1.0
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
Charset: utf8
Content-Type: text/plain
Message-ID: <rt-3.6.HEAD-4108-1242122395-1055.39121-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 95
No practical way to shoehorn this into 0.08, stalling until 0.09 development begins in earnest.
MIME-Version: 1.0
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Charset: utf8
Content-Type: multipart/mixed; boundary="----------=_1256110421-3617-155"
Message-ID: <rt-3.6.HEAD-3617-1256110421-1163.39121-0-0 [...] rt.cpan.org>
X-RT-Original-Encoding: utf-8
Content-Length: 0
Content-Disposition: inline
Content-Type: text/plain
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 88
I'v create a patch for newer versions of DBIx::Class. Patch against 0.08112 is attached.
MIME-Version: 1.0
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Type: multipart/mixed; boundary="----------=_1256110421-3617-154"
Charset: utf8
Content-Length: 0
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: iso-8859-1
Content-Length: 0
Content-Type: application/octet-stream; name="oracle-hierarchical-queries-0.08112.patch"
Content-Disposition: inline; filename="oracle-hierarchical-queries-0.08112.patch"
Content-Transfer-Encoding: base64
Content-Length: 13066

Message body is not shown because it is too large.

MIME-Version: 1.0
In-Reply-To: <rt-3.6.HEAD-3617-1256110421-1163.39121-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
Charset: utf8
References: <rt-3.6.HEAD-3617-1256110421-1163.39121-0-0 [...] rt.cpan.org>
Content-Type: text/plain
Message-ID: <rt-3.6.HEAD-3617-1256174354-830.39121-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 502
Download (untitled) / with headers
text/plain 502b
On Wed Oct 21 03:33:41 2009, kalex wrote: Show quoted text
> I'v create a patch for newer versions of DBIx::Class. Patch against > 0.08112 is attached.
This is *very* interesting, and much cleaner. Please rewrite the test to use multicreate, so it is clearer what is it you are doing. Also add resulting SQL in the POD, so the user (and the reviewers) know what the result is without trying it. P.S. for a wicked MC example: http://dev.catalyst.perl.org/svnweb/bast/view/DBIx-Class/0.08/trunk/t/multi_create/torture.t
MIME-Version: 1.0
In-Reply-To: <rt-3.6.HEAD-3617-1256174354-830.39121-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Charset: utf8
References: <rt-3.6.HEAD-3617-1256110421-1163.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-3617-1256174354-830.39121-0-0 [...] rt.cpan.org>
Content-Type: multipart/mixed; boundary="----------=_1256725262-3617-558"
Message-ID: <rt-3.6.HEAD-3617-1256725262-482.39121-0-0 [...] rt.cpan.org>
X-RT-Original-Encoding: utf-8
Content-Length: 0
Content-Disposition: inline
Content-Type: text/plain
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 221
Download (untitled) / with headers
text/plain 221b
Glad to see some interest! A patch with tests using multicreate is attached. This patch also includes the autoincrement changes I've reported in #39121. Otherwise MC doesn't work with the DBICTest schema and a Oracle DB.
MIME-Version: 1.0
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Type: multipart/mixed; boundary="----------=_1256725262-3617-557"
Charset: utf8
Content-Length: 0
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: iso-8859-1
Content-Length: 0
Content-Type: application/octet-stream; name="oracle-hierarchical-queries-0.08112-multicreate.patch"
Content-Disposition: inline; filename="oracle-hierarchical-queries-0.08112-multicreate.patch"
Content-Transfer-Encoding: base64
Content-Length: 21249

Message body is not shown because it is too large.

MIME-Version: 1.0
In-Reply-To: <rt-3.6.HEAD-3617-1256725262-482.39121-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
Charset: utf8
References: <rt-3.6.HEAD-3617-1256110421-1163.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-3617-1256174354-830.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-3617-1256725262-482.39121-0-0 [...] rt.cpan.org>
Content-Type: text/plain
Message-ID: <rt-3.6.HEAD-6514-1258025675-792.39121-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 641
Download (untitled) / with headers
text/plain 641b
On Wed Oct 28 06:21:03 2009, kalex wrote: Show quoted text
> Glad to see some interest! > > A patch with tests using multicreate is attached. This patch also > includes the autoincrement changes I've reported in #39121. Otherwise MC > doesn't work with the DBICTest schema and a Oracle DB.
Sorry I wasn't clear. I was asking you to put the SQL snippets in the POD, so the user has an idea what is actually going to happen when he uses each attribute (however what you did in the tests is great as well, thank you for that). So could you adjust the patch one last time adding actual SQL snippets to the examples in the user-visible documentation. Thank you
MIME-Version: 1.0
In-Reply-To: <rt-3.6.HEAD-6514-1258025675-792.39121-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Charset: utf8
References: <rt-3.6.HEAD-3617-1256110421-1163.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-3617-1256174354-830.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-3617-1256725262-482.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-6514-1258025675-792.39121-0-0 [...] rt.cpan.org>
Content-Type: multipart/mixed; boundary="----------=_1258539992-6514-432"
Message-ID: <rt-3.6.HEAD-6514-1258539992-1814.39121-0-0 [...] rt.cpan.org>
X-RT-Original-Encoding: utf-8
Content-Length: 0
Content-Disposition: inline
Content-Type: text/plain
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 470
Download (untitled) / with headers
text/plain 470b
On Thu Nov 12 06:34:35 2009, RIBASUSHI wrote: Show quoted text
> Sorry I wasn't clear. I was asking you to put the SQL snippets in the > POD, so the user has an idea what is actually going to happen when he > uses each attribute (however what you did in the tests is great as well, > thank you for that). So could you adjust the patch one last time adding > actual SQL snippets to the examples in the user-visible documentation. > > Thank you
Done! Patch against 0.08114 is attached.
MIME-Version: 1.0
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Type: multipart/mixed; boundary="----------=_1258539992-6514-431"
Charset: utf8
Content-Length: 0
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: iso-8859-1
Content-Length: 0
Content-Type: application/octet-stream; name="oracle-hierarchical-queries-0.08114.patch"
Content-Disposition: inline; filename="oracle-hierarchical-queries-0.08114.patch"
Content-Transfer-Encoding: base64
Content-Length: 20073

Message body is not shown because it is too large.

MIME-Version: 1.0
In-Reply-To: <rt-3.6.HEAD-6514-1258539992-1814.39121-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
Charset: utf8
References: <rt-3.6.HEAD-3617-1256110421-1163.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-3617-1256174354-830.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-3617-1256725262-482.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-6514-1258025675-792.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-6514-1258539992-1814.39121-0-0 [...] rt.cpan.org>
Content-Type: text/plain
Message-ID: <rt-3.6.HEAD-6514-1258625759-17.39121-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 977
Download (untitled) / with headers
text/plain 977b
On Wed Nov 18 02:26:32 2009, kalex wrote: Show quoted text
> On Thu Nov 12 06:34:35 2009, RIBASUSHI wrote: >
> > Sorry I wasn't clear. I was asking you to put the SQL snippets in the > > POD, so the user has an idea what is actually going to happen when he > > uses each attribute (however what you did in the tests is great as well, > > thank you for that). So could you adjust the patch one last time adding > > actual SQL snippets to the examples in the user-visible documentation. > > > > Thank you
> > Done! > > Patch against 0.08114 is attached.
Ok, I created a branch with your code in it: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/0.08/branches/oracle_hierarchical_queries_rt39121/ As far as the code itself - I am happy. It is clean, unobtrusive and generally easy to follow. Now it needs another Oracle user to validate it and it can merge. I already brought it to the attention of folk that might be interested on irc.perl.org#dbix-class. Expect to hear something soon.
MIME-Version: 1.0
In-Reply-To: <rt-3.6.HEAD-6514-1258625759-17.39121-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
Charset: utf8
References: <rt-3.6.HEAD-3617-1256110421-1163.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-3617-1256174354-830.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-3617-1256725262-482.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-6514-1258025675-792.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-6514-1258539992-1814.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-6514-1258625759-17.39121-0-0 [...] rt.cpan.org>
Content-Type: text/plain
Message-ID: <rt-3.6.HEAD-6514-1258640211-1195.39121-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 375
Download (untitled) / with headers
text/plain 375b
kalex - I've reviewed the patch and have a few concerns about the construction of the CONNECT BY clause: * The LHS is a column name and so needs to be quotable (there's a config that specifies this) * The operator can be something other than '=' * The RHS can be either a column name (and so needs to be quotable) or a value (and so needs to be a bind variable). Rob
MIME-Version: 1.0
In-Reply-To: <rt-3.6.HEAD-6514-1258640211-1195.39121-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
References: <rt-3.6.HEAD-3617-1256110421-1163.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-3617-1256174354-830.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-3617-1256725262-482.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-6514-1258025675-792.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-6514-1258539992-1814.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-6514-1258625759-17.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-6514-1258640211-1195.39121-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-6788-1274778681-1849.39121-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 577
Download (untitled) / with headers
text/plain 577b
better late than never ... I'am still working on this feature and made some changes an bugfixes since the last patch I've posted here. Also support for Postgres has been added (simulating the "connect by" statement with a "with recursice" statement). Now I'm not sure anymore if this features should be a part of 'core' DBIx::Class, as it is not an generic feature but also simulates a DB specific feature for other databases. My idea is to release a custom storage driver (like DBIx::Class::Storge::Hierarchical) which could be used optionally to the default storage driver.
MIME-Version: 1.0
In-Reply-To: <rt-3.8.HEAD-6788-1274778681-1849.39121-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
References: <rt-3.6.HEAD-3617-1256110421-1163.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-3617-1256174354-830.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-3617-1256725262-482.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-6514-1258025675-792.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-6514-1258539992-1814.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-6514-1258625759-17.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-6514-1258640211-1195.39121-0-0 [...] rt.cpan.org> <rt-3.8.HEAD-6788-1274778681-1849.39121-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-6786-1274790160-317.39121-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 1542
Download (untitled) / with headers
text/plain 1.5k
On Tue May 25 05:11:21 2010, kalex wrote: Show quoted text
> better late than never ...
In fact work has been progressing on the branch I made some time ago. The thing is feature complete and prompted a number of rewrites in various subsystems. It was supposed to merge to trunk last week, but there is one last outstanding issue that needed a change of dbic-core. Show quoted text
> I'am still working on this feature and made some changes an bugfixes > since the last patch I've posted here. Also support for Postgres has > been added (simulating the "connect by" statement with a "with > recursice" statement). > > Now I'm not sure anymore if this features should be a part of 'core' > DBIx::Class, as it is not an generic feature but also simulates a DB > specific feature for other databases. > My idea is to release a custom storage driver (like > DBIx::Class::Storge::Hierarchical) which could be used optionally to the > default storage driver.
Please reconsider this plan, as the Storage API is not stable, nor public. If you go ahead and release an alternative storage driver, it will never be guaranteed to work with newer versions of DBIC, as there will never be any effort to stabilize the source-storage communication mechanisms. Please review what has been done so far at http://dev.catalyst.perl.org/repos/bast/DBIx-Class/0.08/branches/oracle_hierarchical_queries_rt39121/ and consider contributing whatever fixes you find necessary. It is currently blocked by a lapse in count() handling, which will be rectified within a couple of weeks at the most.
MIME-Version: 1.0
In-Reply-To: <rt-3.8.HEAD-6786-1274790160-317.39121-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
References: <rt-3.6.HEAD-3617-1256110421-1163.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-3617-1256174354-830.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-3617-1256725262-482.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-6514-1258025675-792.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-6514-1258539992-1814.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-6514-1258625759-17.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-6514-1258640211-1195.39121-0-0 [...] rt.cpan.org> <rt-3.8.HEAD-6788-1274778681-1849.39121-0-0 [...] rt.cpan.org> <rt-3.8.HEAD-6786-1274790160-317.39121-0-0 [...] rt.cpan.org>
Content-Type: multipart/mixed; boundary="----------=_1274943310-10882-9"
Message-ID: <rt-3.8.HEAD-10882-1274943310-911.39121-0-0 [...] rt.cpan.org>
X-RT-Original-Encoding: utf-8
Content-Length: 0
Content-Disposition: inline
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 286
Download (untitled) / with headers
text/plain 286b
I took a look at the branch and found another problem: Combining a "connect by" statement with a "group by" statement creates bind values in the wrong order. The "connect by" bind should be inserted before the "group by" and "order by" binds. A (little bit hackish) patch is attached.
MIME-Version: 1.0
Subject: group_by_bind_position.diff
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Type: application/octet-stream; name="group_by_bind_position.diff"
Content-Disposition: inline; filename="group_by_bind_position.diff"
Content-Transfer-Encoding: base64
Content-Length: 2908
Index: t/73oracle.t =================================================================== --- t/73oracle.t (Revision 9439) +++ t/73oracle.t (Arbeitskopie) @@ -343,13 +343,16 @@ $schema->resultset('Artist')->create ({ name => 'root', + rank => 1, cds => [], children => [ { name => 'child1', + rank => 2, children => [ { name => 'grandchild', + rank => 3, cds => [ { title => "grandchilds's cd" , @@ -365,6 +368,7 @@ children => [ { name => 'greatgrandchild', + rank => 3, } ], } @@ -372,6 +376,7 @@ }, { name => 'child2', + rank => 3, }, ], }); @@ -623,6 +628,36 @@ is( $rs->count, 2, 'Connect By; LIMIT count ok' ); } + # combine a connect_by with group_by and having + { + my $rs = $schema->resultset('Artist')->search({}, { + select => ['count(rank)'], + start_with => { name => 'root' }, + connect_by => { parentid => { -prior => \ 'artistid' } }, + group_by => ['rank'], + having => { 'count(rank)' => { '<', 2 } }, + }); + + is_same_sql_bind ( + $rs->as_query, + '( + SELECT count(rank) + FROM artist me + START WITH name = ? + CONNECT BY parentid = PRIOR artistid + GROUP BY rank HAVING count(rank) < ? + )', + [ [ name => 'root' ], [ 'count(rank)' => 2 ] ], + ); + + is_deeply ( + [ $rs->get_column ('count(rank)')->all ], + [1, 1], + 'Group By a Connect By query - correct values' + ); + } + + # select the whole cycle tree without nocylce { my $rs = $schema->resultset('Artist')->search({}, { Index: lib/DBIx/Class/SQLAHacks/Oracle.pm =================================================================== --- lib/DBIx/Class/SQLAHacks/Oracle.pm (Revision 9439) +++ lib/DBIx/Class/SQLAHacks/Oracle.pm (Arbeitskopie) @@ -28,7 +28,9 @@ my ($self, $table, $fields, $where, $rs_attrs, @rest) = @_; my ($sql, @bind) = $self->SUPER::select($table, $fields, $where, $rs_attrs, @rest); - push @bind, @{$self->{_oracle_connect_by_binds}}; + # insert '_oracle_connect_by_binds' before 'having_bind' and 'order_bind' + @bind = @bind[0..@bind-@{$self->{having_bind}}-@{$self->{order_bind}}-1]; + push @bind, @{$self->{_oracle_connect_by_binds}}, @{$self->{having_bind}}, @{$self->{order_bind}}; return wantarray ? ($sql, @bind) : $sql; }
MIME-Version: 1.0
In-Reply-To: <rt-3.8.HEAD-10882-1274943310-911.39121-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
References: <rt-3.6.HEAD-3617-1256110421-1163.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-3617-1256174354-830.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-3617-1256725262-482.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-6514-1258025675-792.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-6514-1258539992-1814.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-6514-1258625759-17.39121-0-0 [...] rt.cpan.org> <rt-3.6.HEAD-6514-1258640211-1195.39121-0-0 [...] rt.cpan.org> <rt-3.8.HEAD-6788-1274778681-1849.39121-0-0 [...] rt.cpan.org> <rt-3.8.HEAD-6786-1274790160-317.39121-0-0 [...] rt.cpan.org> <rt-3.8.HEAD-10882-1274943310-911.39121-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-10884-1274948440-1584.39121-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 446
Download (untitled) / with headers
text/plain 446b
On Thu May 27 08:55:10 2010, kalex wrote: Show quoted text
> I took a look at the branch and found another problem: > > Combining a "connect by" statement with a "group by" statement creates > bind values in the wrong order. The "connect by" bind should be inserted > before the "group by" and "order by" binds. > > A (little bit hackish) patch is attached.
Patch has been adjusted and committed. http://dev.catalystframework.org/svnweb/bast/revision?rev=9449
MIME-Version: 1.0
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Type: multipart/mixed; boundary="----------=_1275030022-10882-20"
Message-ID: <rt-3.8.HEAD-10882-1275030022-438.39121-0-0 [...] rt.cpan.org>
X-RT-Original-Encoding: utf-8
Content-Length: 0
Content-Disposition: inline
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 525
Download (untitled) / with headers
text/plain 525b
As stated above I've also added hierarchical query support for Postgres. I'm using the "with recursive" statement to imitate the behavior of Oracles hierarchical implementation: http://www.postgresql.org/docs/8.4/static/queries-with.html Apart from following incompatibilitys it behaves like Oracle: * rows are retured in a different order * order_siblings_by is not supported * cycles within a connect_by statement will result in an endless loop * connect_by_nocycle will return one row twice count() tests are failing.
MIME-Version: 1.0
Subject: postgres_hierarchical_queries.diff
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Type: application/octet-stream; name="postgres_hierarchical_queries.diff"
Content-Disposition: inline; filename="postgres_hierarchical_queries.diff"
Content-Transfer-Encoding: base64
Content-Length: 27483

Message body is not shown because it is too large.

MIME-Version: 1.0
In-Reply-To: <rt-3.8.HEAD-10882-1275030022-438.39121-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
References: <rt-3.8.HEAD-10882-1275030022-438.39121-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-10878-1275353406-1602.39121-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 2526
Download (untitled) / with headers
text/plain 2.4k
Hi! The long journey is at its end \o/ I just committed what I think is the last piece of branches/oracle_hierarchical_queries_rt39121. It would be greatly appreciated if you look through the code again and see if there is anything else we missed. If I do not hear from you by 21:00 UTC Tuesday the 1st, I will just merge it to trunk as-is. Also please provide us with a name/email you would like to see added to the Contributors section of DBIx/Class.pm On Fri May 28 03:00:22 2010, kalex wrote: Show quoted text
> As stated above I've also added hierarchical query support for Postgres. > > I'm using the "with recursive" statement to imitate the behavior of > Oracles hierarchical implementation: > http://www.postgresql.org/docs/8.4/static/queries-with.html > > Apart from following incompatibilitys it behaves like Oracle: > > * rows are retured in a different order > * order_siblings_by is not supported > * cycles within a connect_by statement will result in an endless loop > * connect_by_nocycle will return one row twice > > count() tests are failing.
Right... this patch leaves a lot to be desired. I will just note some random thoughts here: * The "temp_rec_query" table rename in select() - is this really necessary? I do not see a subselect taking place... * What is the idea behind _get_prior_sqlmaker() ? I don't see why $self isn't sufficient * You (as per comments) "simulate connect by nocycle" - if this is not a native operator, why do we support it? Also in the POD you state "It tries to be as compatible as possilbe to the Oracle implementation". The point behind the Oracle stuff was that it is oracle-specific syntax, not something that is to be made to work transparently across multiple backends. Even if we had the infrastructure for this (we don't), the functionality would be locked behind a higher level API, not based on one or another flavor of RDBMS. So I guess a viable strategy after the oracle stuff merges, is to define the *interesting* subset of Pg hierarchical queries support, and implement it just like we did for Oracle, but without striving for a certain degree of compatibility. The functionality being added now is strictly to make queries specific to a certain rdbms possible. A side benefit is a build up of a test suite and use cases, which would be very useful when work on Data::Query (a dialect-aware SQL::Abstract replacement) begins in earnest. If you are interested in working more on pg - please come to #dbix-class on irc.perl.org, so we can fit you with a commit-bit.
MIME-Version: 1.0
In-Reply-To: <rt-3.8.HEAD-10878-1275353406-1602.39121-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
References: <rt-3.8.HEAD-10882-1275030022-438.39121-0-0 [...] rt.cpan.org> <rt-3.8.HEAD-10878-1275353406-1602.39121-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-10879-1275392437-965.39121-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 1706
Download (untitled) / with headers
text/plain 1.6k
Am Mo 31. Mai 2010, 20:50:06, RIBASUSHI schrieb: Show quoted text
> The long journey is at its end \o/ I just committed what I think is the > last piece of branches/oracle_hierarchical_queries_rt39121.
Great to hear! :) Show quoted text
> It would be > greatly appreciated if you look through the code again and see if there > is anything else we missed.
No more issues from my side. Just ran the testsuite of our product (which is heavily using hierarchical queries), and it passed without any problems. Show quoted text
> Also please provide us with a name/email you would like to see added to > the Contributors section of DBIx/Class.pm
Alexander Keusch cpan@keusch.at Show quoted text
> The > point behind the Oracle stuff was that it is oracle-specific syntax, not > something that is to be made to work transparently across multiple > backends. Even if we had the infrastructure for this (we don't), the > functionality would be locked behind a higher level API, not based on > one or another flavor of RDBMS.
The intention behind this was, that we have a product running on oracle, and now we also need to support postgres. With the (largely) compatible postgres implementation, we can switch between databases by just changing the connection string. Show quoted text
> So I guess a viable strategy after the oracle stuff merges, is to define > the *interesting* subset of Pg hierarchical queries support, and > implement it just like we did for Oracle, but without striving for a > certain degree of compatibility.
Yes, I agree with you. That was the reason I was thinking of a custom storage driver (so that people optionally can use such a "compatibility layer"). But for the moment I'am very happy with the upcoming DBIx::Class Version, it will make things alot easier.
MIME-Version: 1.0
In-Reply-To: <rt-3.8.HEAD-10879-1275392437-965.39121-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
References: <rt-3.8.HEAD-10882-1275030022-438.39121-0-0 [...] rt.cpan.org> <rt-3.8.HEAD-10878-1275353406-1602.39121-0-0 [...] rt.cpan.org> <rt-3.8.HEAD-10879-1275392437-965.39121-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-10893-1275401639-559.39121-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 2531
Download (untitled) / with headers
text/plain 2.4k
On Tue Jun 01 07:40:38 2010, kalex wrote: Show quoted text
> Am Mo 31. Mai 2010, 20:50:06, RIBASUSHI schrieb:
> > The long journey is at its end \o/ I just committed what I think is the > > last piece of branches/oracle_hierarchical_queries_rt39121.
> > Great to hear! :) >
> > It would be > > greatly appreciated if you look through the code again and see if there > > is anything else we missed.
> > No more issues from my side. Just ran the testsuite of our product > (which is heavily using hierarchical queries), and it passed without any > problems. >
> > Also please provide us with a name/email you would like to see added to > > the Contributors section of DBIx/Class.pm
> > Alexander Keusch cpan@keusch.at
Authorship committed and everything is merged to trunk. A release should come along in a couple of days. Thank you for all the work \o/ Show quoted text
> > The > > point behind the Oracle stuff was that it is oracle-specific syntax, not > > something that is to be made to work transparently across multiple > > backends. Even if we had the infrastructure for this (we don't), the > > functionality would be locked behind a higher level API, not based on > > one or another flavor of RDBMS.
> > The intention behind this was, that we have a product running on oracle, > and now we also need to support postgres. With the (largely) compatible > postgres implementation, we can switch between databases by just > changing the connection string. >
> > So I guess a viable strategy after the oracle stuff merges, is to define > > the *interesting* subset of Pg hierarchical queries support, and > > implement it just like we did for Oracle, but without striving for a > > certain degree of compatibility.
> > Yes, I agree with you. That was the reason I was thinking of a custom > storage driver (so that people optionally can use such a "compatibility > layer"). But for the moment I'am very happy with the upcoming > DBIx::Class Version, it will make things alot easier.
Well - why stop here: you can still work on a paralel Pg implementation, just do not couple it so tightly with what we already have for Oracle. Once the Pg part is implemented we can do proper merging/rename of the common parts, before we actually merge the support to trunk. I am very open to contributions, given we don't resort to "afroengineering" just for the sake of pushing something out. So if you have the time - please join us on #dbix-class so you can be properly set with commit access (and discuss whatever technicalities there are to discuss) Cheers!
MIME-Version: 1.0
Subject: hierarchical queries - Pg WITH
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-12654-1325327120-848.39121-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 217
Download (untitled) / with headers
text/plain 217b
Reopening/renaming ticket, it got closed by mistake, still contains a patch we are very interested in. Patch however requires some work, see https://rt.cpan.org/Public/Bug/Display.html?id=39121#txn-784658 for details.


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.