Beta Shell
v2.0 ยท web2.us.cloudlogin.co
[FM]
[CMD]
[PHP]
[DB]
[INFO]
[SEC]
File Manager
~
/
usr
/
share
/
doc
/
perl-DBIx-Class
/
t
/
sqlmaker
/
hierarchical
Upload
3 items
Name
Size
Perms
Modified
Actions
[ .. / .. ]
oracle.t
9.98 KB
-rw-r--r--
2020-03-29 18:33:33
Edit
Del
Editing: oracle.t
(9.98 KB)
Path: /usr/share/doc/perl-DBIx-Class/t/sqlmaker/hierarchical/oracle.t
Back
use strict; use warnings; use Test::More; use lib qw(t/lib); use DBIx::Class::Optional::Dependencies; plan skip_all => 'Test needs ' . DBIx::Class::Optional::Dependencies->req_missing_for ('id_shortener') unless DBIx::Class::Optional::Dependencies->req_ok_for ('id_shortener'); use DBICTest::Schema::Artist; BEGIN { DBICTest::Schema::Artist->add_column('parentid'); DBICTest::Schema::Artist->has_many( children => 'DBICTest::Schema::Artist', { 'foreign.parentid' => 'self.artistid' } ); DBICTest::Schema::Artist->belongs_to( parent => 'DBICTest::Schema::Artist', { 'foreign.artistid' => 'self.parentid' } ); } use DBICTest ':DiffSQL'; my $ROWS = DBIx::Class::SQLMaker::ClassicExtensions->__rows_bindtype; my $TOTAL = DBIx::Class::SQLMaker::ClassicExtensions->__total_bindtype; for my $q ( '', '"' ) { my $schema = DBICTest->init_schema( storage_type => 'DBIx::Class::Storage::DBI::Oracle::Generic', no_deploy => 1, quote_char => $q, ); # select the whole tree { my $rs = $schema->resultset('Artist')->search({}, { start_with => { name => 'root' }, connect_by => { parentid => { -prior => { -ident => 'artistid' } } }, }); is_same_sql_bind ( $rs->as_query, "( SELECT ${q}me${q}.${q}artistid${q}, ${q}me${q}.${q}name${q}, ${q}me${q}.${q}rank${q}, ${q}me${q}.${q}charfield${q}, ${q}me${q}.${q}parentid${q} FROM ${q}artist${q} ${q}me${q} START WITH ${q}name${q} = ? CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q} )", [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 } => 'root'] ], ); is_same_sql_bind ( $rs->count_rs->as_query, "( SELECT COUNT( * ) FROM ${q}artist${q} ${q}me${q} START WITH ${q}name${q} = ? CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q} )", [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 } => 'root'] ], ); } # use order siblings by statement { my $rs = $schema->resultset('Artist')->search({}, { start_with => { name => 'root' }, connect_by => { parentid => { -prior => { -ident => 'artistid' } } }, order_siblings_by => { -desc => 'name' }, }); is_same_sql_bind ( $rs->as_query, "( SELECT ${q}me${q}.${q}artistid${q}, ${q}me${q}.${q}name${q}, ${q}me${q}.${q}rank${q}, ${q}me${q}.${q}charfield${q}, ${q}me${q}.${q}parentid${q} FROM ${q}artist${q} ${q}me${q} START WITH ${q}name${q} = ? CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q} ORDER SIBLINGS BY ${q}name${q} DESC )", [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 } => 'root'] ], ); } # get the root node { my $rs = $schema->resultset('Artist')->search({ parentid => undef }, { start_with => { name => 'root' }, connect_by => { parentid => { -prior => { -ident => 'artistid' } } }, }); is_same_sql_bind ( $rs->as_query, "( SELECT ${q}me${q}.${q}artistid${q}, ${q}me${q}.${q}name${q}, ${q}me${q}.${q}rank${q}, ${q}me${q}.${q}charfield${q}, ${q}me${q}.${q}parentid${q} FROM ${q}artist${q} ${q}me${q} WHERE ( ${q}parentid${q} IS NULL ) START WITH ${q}name${q} = ? CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q} )", [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 } => 'root'] ], ); } # combine a connect by with a join { my $rs = $schema->resultset('Artist')->search( {'cds.title' => { -like => '%cd'} }, { join => 'cds', start_with => { 'me.name' => 'root' }, connect_by => { parentid => { -prior => { -ident => 'artistid' } } }, } ); is_same_sql_bind ( $rs->as_query, "( SELECT ${q}me${q}.${q}artistid${q}, ${q}me${q}.${q}name${q}, ${q}me${q}.${q}rank${q}, ${q}me${q}.${q}charfield${q}, ${q}me${q}.${q}parentid${q} FROM ${q}artist${q} ${q}me${q} LEFT JOIN cd ${q}cds${q} ON ${q}cds${q}.${q}artist${q} = ${q}me${q}.${q}artistid${q} WHERE ( ${q}cds${q}.${q}title${q} LIKE ? ) START WITH ${q}me${q}.${q}name${q} = ? CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q} )", [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'cds.title', 'sqlt_size' => 100 } => '%cd'], [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'me.name', 'sqlt_size' => 100 } => 'root'], ], ); is_same_sql_bind ( $rs->count_rs->as_query, "( SELECT COUNT( * ) FROM ${q}artist${q} ${q}me${q} LEFT JOIN cd ${q}cds${q} ON ${q}cds${q}.${q}artist${q} = ${q}me${q}.${q}artistid${q} WHERE ( ${q}cds${q}.${q}title${q} LIKE ? ) START WITH ${q}me${q}.${q}name${q} = ? CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q} )", [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'cds.title', 'sqlt_size' => 100 } => '%cd'], [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'me.name', 'sqlt_size' => 100 } => 'root'], ], ); } # combine a connect by with order_by { my $rs = $schema->resultset('Artist')->search({}, { start_with => { name => 'root' }, connect_by => { parentid => { -prior => { -ident => 'artistid' } } }, order_by => { -asc => [ 'LEVEL', 'name' ] }, }); is_same_sql_bind ( $rs->as_query, "( SELECT ${q}me${q}.${q}artistid${q}, ${q}me${q}.${q}name${q}, ${q}me${q}.${q}rank${q}, ${q}me${q}.${q}charfield${q}, ${q}me${q}.${q}parentid${q} FROM ${q}artist${q} ${q}me${q} START WITH ${q}name${q} = ? CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q} ORDER BY ${q}LEVEL${q} ASC, ${q}name${q} ASC )", [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 } => 'root'], ], ); } # limit a connect by { my $rs = $schema->resultset('Artist')->search({}, { start_with => { name => 'root' }, connect_by => { parentid => { -prior => { -ident => 'artistid' } } }, order_by => [ { -asc => 'name' }, { -desc => 'artistid' } ], rows => 2, }); is_same_sql_bind ( $rs->as_query, "( SELECT ${q}me${q}.${q}artistid${q}, ${q}me${q}.${q}name${q}, ${q}me${q}.${q}rank${q}, ${q}me${q}.${q}charfield${q}, ${q}me${q}.${q}parentid${q} FROM ( SELECT ${q}me${q}.${q}artistid${q}, ${q}me${q}.${q}name${q}, ${q}me${q}.${q}rank${q}, ${q}me${q}.${q}charfield${q}, ${q}me${q}.${q}parentid${q} FROM ${q}artist${q} ${q}me${q} START WITH ${q}name${q} = ? CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q} ORDER BY ${q}name${q} ASC, ${q}artistid${q} DESC ) ${q}me${q} WHERE ROWNUM <= ? )", [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 } => 'root'], [ $ROWS => 2 ], ], ); is_same_sql_bind ( $rs->count_rs->as_query, "( SELECT COUNT( * ) FROM ( SELECT ${q}me${q}.${q}artistid${q} FROM ( SELECT ${q}me${q}.${q}artistid${q} FROM ${q}artist${q} ${q}me${q} START WITH ${q}name${q} = ? CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q} ) ${q}me${q} WHERE ROWNUM <= ? ) ${q}me${q} )", [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 } => 'root'], [ $ROWS => 2 ], ], ); } # combine a connect_by with group_by and having # add some bindvals to make sure things still work { my $rs = $schema->resultset('Artist')->search({}, { select => \[ 'COUNT(rank) + ?', [ __cbind => 3 ] ], as => 'cnt', start_with => { name => 'root' }, connect_by => { parentid => { -prior => { -ident => 'artistid' } } }, group_by => \[ 'rank + ? ', [ __gbind => 1] ], having => \[ 'count(rank) < ?', [ cnt => 2 ] ], }); is_same_sql_bind ( $rs->as_query, "( SELECT COUNT(rank) + ? FROM ${q}artist${q} ${q}me${q} START WITH ${q}name${q} = ? CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q} GROUP BY( rank + ? ) HAVING count(rank) < ? )", [ [ { dbic_colname => '__cbind' } => 3 ], [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 } => 'root'], [ { dbic_colname => '__gbind' } => 1 ], [ { dbic_colname => 'cnt' } => 2 ], ], ); } # select the whole cycle tree with nocylce { my $rs = $schema->resultset('Artist')->search({}, { start_with => { name => 'cycle-root' }, '+select' => \ 'CONNECT_BY_ISCYCLE', '+as' => [ 'connector' ], connect_by_nocycle => { parentid => { -prior => { -ident => 'artistid' } } }, }); is_same_sql_bind ( $rs->as_query, "( SELECT ${q}me${q}.${q}artistid${q}, ${q}me${q}.${q}name${q}, ${q}me${q}.${q}rank${q}, ${q}me${q}.${q}charfield${q}, ${q}me${q}.${q}parentid${q}, CONNECT_BY_ISCYCLE FROM ${q}artist${q} ${q}me${q} START WITH ${q}name${q} = ? CONNECT BY NOCYCLE ${q}parentid${q} = PRIOR ${q}artistid${q} )", [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 } => 'cycle-root'], ], ); is_same_sql_bind ( $rs->count_rs->as_query, "( SELECT COUNT( * ) FROM ${q}artist${q} ${q}me${q} START WITH ${q}name${q} = ? CONNECT BY NOCYCLE ${q}parentid${q} = PRIOR ${q}artistid${q} )", [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 } => 'cycle-root'], ], ); } } done_testing;