5 use Data::Dumper::Concise;
7 use DBIC::SqlMakerTest;
8 use DBIx::Class::SQLMaker::Oracle;
11 # Offline test for connect_by
12 # ( without acitve database connection)
16 connect_by => { 'parentid' => { '-prior' => \'artistid' } },
17 stmt => '"parentid" = PRIOR artistid',
19 msg => 'Simple: "parentid" = PRIOR artistid',
22 connect_by => { 'parentid' => { '!=' => { '-prior' => { -ident => 'artistid' } } } },
23 stmt => '"parentid" != ( PRIOR "artistid" )',
25 msg => 'Simple: "parentid" != ( PRIOR "artistid" )',
27 # Examples from http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm
29 # CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ...
32 last_name => { '!=' => 'King' },
33 manager_id => { '-prior' => { -ident => 'employee_id' } },
35 stmt => '( "last_name" != ? OR "manager_id" = PRIOR "employee_id" )',
37 msg => 'oracle.com example #1',
39 # CONNECT BY PRIOR employee_id = manager_id and
40 # PRIOR account_mgr_id = customer_id ...
43 manager_id => { '-prior' => { -ident => 'employee_id' } },
44 customer_id => { '>', { '-prior' => \'account_mgr_id' } },
46 stmt => '( "customer_id" > ( PRIOR account_mgr_id ) AND "manager_id" = PRIOR "employee_id" )',
48 msg => 'oracle.com example #2',
50 # CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4;
51 # TODO: NOCYCLE parameter doesn't work
54 my $sqla_oracle = DBIx::Class::SQLMaker::Oracle->new( quote_char => '"', name_sep => '.' );
55 isa_ok($sqla_oracle, 'DBIx::Class::SQLMaker::Oracle');
58 for my $case (@handle_tests) {
60 my $msg = sprintf("Offline: %s",
61 $case->{msg} || substr($case->{stmt},0,25),
65 ( $stmt, @bind ) = $sqla_oracle->_recurse_where( $case->{connect_by} );
66 is_same_sql_bind( $stmt, \@bind, $case->{stmt}, $case->{bind},$msg )
67 || diag "Search term:\n" . Dumper $case->{connect_by};
69 ,sprintf("lives is ok from '%s'",$msg));
73 $sqla_oracle->_shorten_identifier('short_id'),
75 '_shorten_identifier for short id without keywords ok'
79 $sqla_oracle->_shorten_identifier('short_id', [qw/ foo /]),
81 '_shorten_identifier for short id with one keyword ok'
85 $sqla_oracle->_shorten_identifier('short_id', [qw/ foo bar baz /]),
87 '_shorten_identifier for short id with keywords ok'
91 $sqla_oracle->_shorten_identifier('very_long_identifier_which_exceeds_the_30char_limit'),
92 'VryLngIdntfrWhchExc_72M8CIDTM7',
93 '_shorten_identifier without keywords ok',
97 $sqla_oracle->_shorten_identifier('very_long_identifier_which_exceeds_the_30char_limit',[qw/ foo /]),
98 'Foo_72M8CIDTM7KBAUPXG48B22P4E',
99 '_shorten_identifier with one keyword ok',
102 $sqla_oracle->_shorten_identifier('very_long_identifier_which_exceeds_the_30char_limit',[qw/ foo bar baz /]),
103 'FooBarBaz_72M8CIDTM7KBAUPXG48B',
104 '_shorten_identifier with keywords ok',
107 # test SQL generation for INSERT ... RETURNING
109 sub UREF { \do { my $x } };
111 $sqla_oracle->{bindtype} = 'columns';
113 for my $q ('', '"') {
114 local $sqla_oracle->{quote_char} = $q;
116 my ($sql, @bind) = $sqla_oracle->insert(
119 'name' => 'Testartist',
122 'returning' => 'artistid',
123 'returning_container' => [],
129 "INSERT INTO ${q}artist${q} (${q}name${q}) VALUES (?) RETURNING ${q}artistid${q} INTO ?",
130 [ [ name => 'Testartist' ], [ artistid => UREF ] ],
131 'sql_maker generates insert returning for one column'
134 ($sql, @bind) = $sqla_oracle->insert(
137 'name' => 'Testartist',
140 'returning' => \'artistid',
141 'returning_container' => [],
147 "INSERT INTO ${q}artist${q} (${q}name${q}) VALUES (?) RETURNING artistid INTO ?",
148 [ [ name => 'Testartist' ], [ artistid => UREF ] ],
149 'sql_maker generates insert returning for one column'
153 ($sql, @bind) = $sqla_oracle->insert(
154 'computed_column_test',
156 'a_timestamp' => '2010-05-26 18:22:00',
159 'returning' => [ 'id', 'a_computed_column', 'charfield' ],
160 'returning_container' => [],
166 "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 ?, ?, ?",
167 [ [ a_timestamp => '2010-05-26 18:22:00' ], [ id => UREF ], [ a_computed_column => UREF ], [ charfield => UREF ] ],
168 'sql_maker generates insert returning for multiple columns'