1 BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) }
2 use DBIx::Class::Optional::Dependencies -skip_all_without => 'id_shortener';
10 use DBIx::Class::_Util 'dump_value';
11 use DBICTest ':DiffSQL';
12 use DBIx::Class::SQLMaker::Oracle;
15 # Offline test for connect_by
16 # ( without active database connection)
20 connect_by => { 'parentid' => { '-prior' => \'artistid' } },
21 stmt => '"parentid" = PRIOR artistid',
23 msg => 'Simple: "parentid" = PRIOR artistid',
26 connect_by => { 'parentid' => { '!=' => { '-prior' => { -ident => 'artistid' } } } },
27 stmt => '"parentid" != ( PRIOR "artistid" )',
29 msg => 'Simple: "parentid" != ( PRIOR "artistid" )',
31 # Examples from http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm
33 # CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ...
36 last_name => { '!=' => 'King' },
37 manager_id => { '-prior' => { -ident => 'employee_id' } },
39 stmt => '( "last_name" != ? OR "manager_id" = PRIOR "employee_id" )',
41 msg => 'oracle.com example #1',
43 # CONNECT BY PRIOR employee_id = manager_id and
44 # PRIOR account_mgr_id = customer_id ...
47 manager_id => { '-prior' => { -ident => 'employee_id' } },
48 customer_id => { '>', { '-prior' => \'account_mgr_id' } },
50 stmt => '( "customer_id" > ( PRIOR account_mgr_id ) AND "manager_id" = PRIOR "employee_id" )',
52 msg => 'oracle.com example #2',
54 # CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4;
55 # TODO: NOCYCLE parameter doesn't work
58 my $sqla_oracle = DBIx::Class::SQLMaker::Oracle->new( quote_char => '"', name_sep => '.' );
59 isa_ok($sqla_oracle, 'DBIx::Class::SQLMaker::Oracle');
62 for my $case (@handle_tests) {
64 my $msg = sprintf("Offline: %s",
65 $case->{msg} || substr($case->{stmt},0,25),
69 ( $stmt, @bind ) = $sqla_oracle->_recurse_where( $case->{connect_by} );
70 is_same_sql_bind( $stmt, \@bind, $case->{stmt}, $case->{bind},$msg )
71 || diag "Search term:\n" . dump_value $case->{connect_by};
73 ,sprintf("lives is ok from '%s'",$msg));
77 $sqla_oracle->_shorten_identifier('short_id'),
79 '_shorten_identifier for short id without keywords ok'
83 $sqla_oracle->_shorten_identifier('short_id', [qw/ foo /]),
85 '_shorten_identifier for short id with one keyword ok'
89 $sqla_oracle->_shorten_identifier('short_id', [qw/ foo bar baz /]),
91 '_shorten_identifier for short id with keywords ok'
95 $sqla_oracle->_shorten_identifier('very_long_identifier_which_exceeds_the_30char_limit'),
96 'VryLngIdntfrWhchExc_72M8CIDTM7',
97 '_shorten_identifier without keywords ok',
101 $sqla_oracle->_shorten_identifier('very_long_identifier_which_exceeds_the_30char_limit',[qw/ foo /]),
102 'Foo_72M8CIDTM7KBAUPXG48B22P4E',
103 '_shorten_identifier with one keyword ok',
106 $sqla_oracle->_shorten_identifier('very_long_identifier_which_exceeds_the_30char_limit',[qw/ foo bar baz /]),
107 'FooBarBaz_72M8CIDTM7KBAUPXG48B',
108 '_shorten_identifier with keywords ok',
111 # test SQL generation for INSERT ... RETURNING
113 sub UREF { \do { my $x } };
115 $sqla_oracle->{bindtype} = 'columns';
117 for my $q ('', '"') {
118 local $sqla_oracle->{quote_char} = $q;
120 my ($sql, @bind) = $sqla_oracle->insert(
123 'name' => 'Testartist',
126 'returning' => 'artistid',
127 'returning_container' => [],
133 "INSERT INTO ${q}artist${q} (${q}name${q}) VALUES (?) RETURNING ${q}artistid${q} INTO ?",
134 [ [ name => 'Testartist' ], [ artistid => UREF ] ],
135 'sql_maker generates insert returning for one column'
138 ($sql, @bind) = $sqla_oracle->insert(
141 'name' => 'Testartist',
144 'returning' => \'artistid',
145 'returning_container' => [],
151 "INSERT INTO ${q}artist${q} (${q}name${q}) VALUES (?) RETURNING artistid INTO ?",
152 [ [ name => 'Testartist' ], [ artistid => UREF ] ],
153 'sql_maker generates insert returning for one column'
157 ($sql, @bind) = $sqla_oracle->insert(
158 'computed_column_test',
160 'a_timestamp' => '2010-05-26 18:22:00',
163 'returning' => [ 'id', 'a_computed_column', 'charfield' ],
164 'returning_container' => [],
170 "INSERT INTO ${q}computed_column_test${q} (${q}a_timestamp${q}) VALUES (?) RETURNING ${q}id${q}, ${q}a_computed_column${q}, ${q}charfield${q} INTO ?, ?, ?",
171 [ [ a_timestamp => '2010-05-26 18:22:00' ], [ id => UREF ], [ a_computed_column => UREF ], [ charfield => UREF ] ],
172 'sql_maker generates insert returning for multiple columns'