Commit | Line | Data |
9382ad07 |
1 | package DBIx::Class::Storage::DBI::Oracle::WhereJoins; |
2 | |
3 | use base qw( DBIx::Class::Storage::DBI::Oracle::Generic ); |
4 | |
5 | use strict; |
6 | use warnings; |
7 | |
8 | BEGIN { |
9 | package DBIC::SQL::Abstract::Oracle; |
10 | |
11 | use base qw( DBIC::SQL::Abstract ); |
12 | |
13 | sub select { |
14 | my ($self, $table, $fields, $where, $order, @rest) = @_; |
15 | |
16 | $self->_oracle_joins($where, @{ $table }); |
17 | |
18 | return $self->SUPER::select($table, $fields, $where, $order, @rest); |
19 | } |
20 | |
21 | sub _recurse_from { |
22 | my ($self, $from, @join) = @_; |
23 | |
24 | my @sqlf = $self->_make_as($from); |
25 | |
26 | foreach my $j (@join) { |
27 | my ($to, $on) = @{ $j }; |
28 | |
29 | if (ref $to eq 'ARRAY') { |
30 | push (@sqlf, $self->_recurse_from(@{ $to })); |
31 | } |
32 | else { |
33 | push (@sqlf, $self->_make_as($to)); |
34 | } |
35 | } |
36 | |
37 | return join q{, }, @sqlf; |
38 | } |
39 | |
40 | sub _oracle_joins { |
41 | my ($self, $where, $from, @join) = @_; |
42 | |
43 | foreach my $j (@join) { |
44 | my ($to, $on) = @{ $j }; |
45 | |
46 | if (ref $to eq 'ARRAY') { |
47 | $self->_oracle_joins($where, @{ $to }); |
48 | } |
49 | |
50 | my $to_jt = ref $to eq 'ARRAY' ? $to->[0] : $to; |
51 | my $left_join = q{}; |
52 | my $right_join = q{}; |
53 | |
54 | if (ref $to_jt eq 'HASH' and exists $to_jt->{-join_type}) { |
55 | #TODO: Support full outer joins -- this would happen much earlier in |
56 | #the sequence since oracle 8's full outer join syntax is best |
57 | #described as INSANE. |
58 | die "Can't handle full outer joins in Oracle 8 yet!\n" |
59 | if $to_jt->{-join_type} =~ /full/i; |
60 | |
61 | $left_join = q{(+)} if $to_jt->{-join_type} =~ /right/i |
62 | && $to_jt->{-join_type} !~ /inner/i; |
63 | |
64 | $right_join = q{(+)} if $to_jt->{-join_type} =~ /left/i |
65 | && $to_jt->{-join_type} !~ /inner/i; |
66 | } |
67 | |
68 | foreach my $lhs (keys %{ $on }) { |
69 | $where->{$lhs . $left_join} = \" = $on->{ $lhs }$right_join"; |
70 | } |
71 | } |
72 | } |
73 | } |
74 | |
75 | sub sql_maker { |
76 | my ($self) = @_; |
77 | |
78 | unless ($self->_sql_maker) { |
79 | $self->_sql_maker( |
80 | new DBIC::SQL::Abstract::Oracle( $self->_sql_maker_args ) |
81 | ); |
82 | } |
83 | |
84 | return $self->_sql_maker; |
85 | } |
86 | |
87 | 1; |
88 | |
89 | __END__ |
90 | |
91 | =pod |
92 | |
93 | =head1 NAME |
94 | |
95 | DBIx::Class::Storage::DBI::Oracle::WhereJoins - Oracle joins in WHERE syntax |
96 | support (instead of ANSI). |
97 | |
98 | =head1 PURPOSE |
99 | |
100 | This module was originally written to support Oracle < 9i where ANSI joins |
101 | weren't supported at all, but became the module for Oracle >= 8 because |
102 | Oracle's optimising of ANSI joins is horrible. (See: |
103 | http://scsys.co.uk:8001/7495) |
104 | |
105 | =head1 SYNOPSIS |
106 | |
107 | DBIx::Class should automagically detect Oracle and use this module with no |
108 | work from you. |
109 | |
110 | =head1 DESCRIPTION |
111 | |
112 | This class implements Oracle's WhereJoin support. Instead of: |
113 | |
114 | SELECT x FROM y JOIN z ON y.id = z.id |
115 | |
116 | It will write: |
117 | |
118 | SELECT x FROM y, z WHERE y.id = z.id |
119 | |
120 | It should properly support left joins, and right joins. Full outer joins are |
121 | not possible due to the fact that Oracle requires the entire query be written |
122 | to union the results of a left and right join, and by the time this module is |
123 | called to create the where query and table definition part of the sql query, |
124 | it's already too late. |
125 | |
126 | =head1 METHODS |
127 | |
128 | This module replaces a subroutine contained in DBIC::SQL::Abstract: |
129 | |
130 | =over |
131 | |
132 | =item sql_maker |
133 | |
134 | =back |
135 | |
136 | It also creates a new module in its BEGIN { } block called |
137 | DBIC::SQL::Abstract::Oracle which has the following methods: |
138 | |
139 | =over |
140 | |
141 | =item select ($\@$;$$@) |
142 | |
143 | Replaces DBIC::SQL::Abstract's select() method, which calls _oracle_joins() |
144 | to modify the column and table list before calling SUPER::select(). |
145 | |
146 | =item _recurse_from ($$\@) |
147 | |
148 | Recursive subroutine that builds the table list. |
149 | |
150 | =item _oracle_joins ($$$@) |
151 | |
152 | Creates the left/right relationship in the where query. |
153 | |
154 | =back |
155 | |
156 | =head1 BUGS |
157 | |
158 | Does not support full outer joins. |
159 | Probably lots more. |
160 | |
161 | =head1 SEE ALSO |
162 | |
163 | =over |
164 | |
165 | =item L<DBIC::SQL::Abstract> |
166 | |
167 | =item L<DBIx::Class::Storage::DBI::Oracle::Generic> |
168 | |
169 | =item L<DBIx::Class> |
170 | |
171 | =back |
172 | |
173 | =head1 AUTHOR |
174 | |
175 | Justin Wheeler C<< <jwheeler@datademons.com> >> |
176 | |
177 | =head1 CONTRIBUTORS |
178 | |
179 | David Jack Olrik C<< <djo@cpan.org> >> |
180 | |
181 | =head1 LICENSE |
182 | |
183 | This module is licensed under the same terms as Perl itself. |
184 | |
185 | =cut |