Move find_co_root into DBICTest::Util
[dbsrgits/DBIx-Class-Historic.git] / t / sqlmaker / oracle.t
CommitLineData
cb551b07 1use DBIx::Class::Optional::Dependencies -skip_all_without => 'id_shortener';
2
c2f2a718 3use strict;
4use warnings;
c7d50a7d 5
cb551b07 6use Test::More;
c2f2a718 7use Test::Exception;
a84fe712 8use Data::Dumper::Concise;
c2f2a718 9use lib qw(t/lib);
a5a7bb73 10use DBICTest ':DiffSQL';
d5dedbd6 11use DBIx::Class::SQLMaker::Oracle;
c2f2a718 12
bf51641f 13#
8273e845 14# Offline test for connect_by
a6646e1b 15# ( without active database connection)
bf51641f 16#
c2f2a718 17my @handle_tests = (
18 {
19 connect_by => { 'parentid' => { '-prior' => \'artistid' } },
20 stmt => '"parentid" = PRIOR artistid',
21 bind => [],
22 msg => 'Simple: "parentid" = PRIOR artistid',
23 },
24 {
e6600283 25 connect_by => { 'parentid' => { '!=' => { '-prior' => { -ident => 'artistid' } } } },
26 stmt => '"parentid" != ( PRIOR "artistid" )',
c2f2a718 27 bind => [],
e6600283 28 msg => 'Simple: "parentid" != ( PRIOR "artistid" )',
c2f2a718 29 },
30 # Examples from http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm
31
32 # CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ...
33 {
34 connect_by => [
35 last_name => { '!=' => 'King' },
e6600283 36 manager_id => { '-prior' => { -ident => 'employee_id' } },
c2f2a718 37 ],
e6600283 38 stmt => '( "last_name" != ? OR "manager_id" = PRIOR "employee_id" )',
c2f2a718 39 bind => ['King'],
40 msg => 'oracle.com example #1',
41 },
8273e845 42 # CONNECT BY PRIOR employee_id = manager_id and
c2f2a718 43 # PRIOR account_mgr_id = customer_id ...
44 {
45 connect_by => {
e6600283 46 manager_id => { '-prior' => { -ident => 'employee_id' } },
c2f2a718 47 customer_id => { '>', { '-prior' => \'account_mgr_id' } },
48 },
e6600283 49 stmt => '( "customer_id" > ( PRIOR account_mgr_id ) AND "manager_id" = PRIOR "employee_id" )',
c2f2a718 50 bind => [],
51 msg => 'oracle.com example #2',
52 },
53 # CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4;
54 # TODO: NOCYCLE parameter doesn't work
55);
56
d5dedbd6 57my $sqla_oracle = DBIx::Class::SQLMaker::Oracle->new( quote_char => '"', name_sep => '.' );
58isa_ok($sqla_oracle, 'DBIx::Class::SQLMaker::Oracle');
c2f2a718 59
60
c2f2a718 61for my $case (@handle_tests) {
c2f2a718 62 my ( $stmt, @bind );
63 my $msg = sprintf("Offline: %s",
64 $case->{msg} || substr($case->{stmt},0,25),
65 );
66 lives_ok(
67 sub {
68 ( $stmt, @bind ) = $sqla_oracle->_recurse_where( $case->{connect_by} );
69 is_same_sql_bind( $stmt, \@bind, $case->{stmt}, $case->{bind},$msg )
70 || diag "Search term:\n" . Dumper $case->{connect_by};
71 }
72 ,sprintf("lives is ok from '%s'",$msg));
73}
74
63ca94e1 75is (
76 $sqla_oracle->_shorten_identifier('short_id'),
77 'short_id',
78 '_shorten_identifier for short id without keywords ok'
79);
80
81is (
82 $sqla_oracle->_shorten_identifier('short_id', [qw/ foo /]),
83 'short_id',
84 '_shorten_identifier for short id with one keyword ok'
85);
86
87is (
88 $sqla_oracle->_shorten_identifier('short_id', [qw/ foo bar baz /]),
89 'short_id',
90 '_shorten_identifier for short id with keywords ok'
91);
92
93is (
94 $sqla_oracle->_shorten_identifier('very_long_identifier_which_exceeds_the_30char_limit'),
95 'VryLngIdntfrWhchExc_72M8CIDTM7',
96 '_shorten_identifier without keywords ok',
97);
98
99is (
100 $sqla_oracle->_shorten_identifier('very_long_identifier_which_exceeds_the_30char_limit',[qw/ foo /]),
101 'Foo_72M8CIDTM7KBAUPXG48B22P4E',
102 '_shorten_identifier with one keyword ok',
103);
104is (
105 $sqla_oracle->_shorten_identifier('very_long_identifier_which_exceeds_the_30char_limit',[qw/ foo bar baz /]),
106 'FooBarBaz_72M8CIDTM7KBAUPXG48B',
107 '_shorten_identifier with keywords ok',
108);
c2f2a718 109
bf51641f 110# test SQL generation for INSERT ... RETURNING
111
112sub UREF { \do { my $x } };
113
114$sqla_oracle->{bindtype} = 'columns';
115
116for my $q ('', '"') {
117 local $sqla_oracle->{quote_char} = $q;
118
119 my ($sql, @bind) = $sqla_oracle->insert(
120 'artist',
121 {
122 'name' => 'Testartist',
123 },
124 {
125 'returning' => 'artistid',
126 'returning_container' => [],
127 },
128 );
129
130 is_same_sql_bind(
131 $sql, \@bind,
132 "INSERT INTO ${q}artist${q} (${q}name${q}) VALUES (?) RETURNING ${q}artistid${q} INTO ?",
133 [ [ name => 'Testartist' ], [ artistid => UREF ] ],
134 'sql_maker generates insert returning for one column'
135 );
136
137 ($sql, @bind) = $sqla_oracle->insert(
138 'artist',
139 {
140 'name' => 'Testartist',
141 },
142 {
143 'returning' => \'artistid',
144 'returning_container' => [],
145 },
146 );
147
148 is_same_sql_bind(
149 $sql, \@bind,
150 "INSERT INTO ${q}artist${q} (${q}name${q}) VALUES (?) RETURNING artistid INTO ?",
151 [ [ name => 'Testartist' ], [ artistid => UREF ] ],
152 'sql_maker generates insert returning for one column'
153 );
154
155
156 ($sql, @bind) = $sqla_oracle->insert(
157 'computed_column_test',
158 {
159 'a_timestamp' => '2010-05-26 18:22:00',
160 },
161 {
162 'returning' => [ 'id', 'a_computed_column', 'charfield' ],
163 'returning_container' => [],
164 },
165 );
166
167 is_same_sql_bind(
168 $sql, \@bind,
169 "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 ?, ?, ?",
170 [ [ a_timestamp => '2010-05-26 18:22:00' ], [ id => UREF ], [ a_computed_column => UREF ], [ charfield => UREF ] ],
171 'sql_maker generates insert returning for multiple columns'
172 );
173}
174
63ca94e1 175done_testing;