line |
stmt |
bran |
cond |
sub |
pod |
time |
code |
1
|
|
|
|
|
|
|
package SQL::Composer::Select; |
2
|
|
|
|
|
|
|
|
3
|
3
|
|
|
3
|
|
12872
|
use strict; |
|
3
|
|
|
|
|
6
|
|
|
3
|
|
|
|
|
67
|
|
4
|
3
|
|
|
3
|
|
13
|
use warnings; |
|
3
|
|
|
|
|
5
|
|
|
3
|
|
|
|
|
70
|
|
5
|
|
|
|
|
|
|
|
6
|
|
|
|
|
|
|
require Carp; |
7
|
3
|
|
|
3
|
|
13
|
use Scalar::Util (); |
|
3
|
|
|
|
|
6
|
|
|
3
|
|
|
|
|
39
|
|
8
|
3
|
|
|
3
|
|
838
|
use SQL::Composer::Join; |
|
3
|
|
|
|
|
7
|
|
|
3
|
|
|
|
|
66
|
|
9
|
3
|
|
|
3
|
|
15
|
use SQL::Composer::Expression; |
|
3
|
|
|
|
|
4
|
|
|
3
|
|
|
|
|
44
|
|
10
|
3
|
|
|
3
|
|
12
|
use SQL::Composer::Quoter; |
|
3
|
|
|
|
|
5
|
|
|
3
|
|
|
|
|
3488
|
|
11
|
|
|
|
|
|
|
|
12
|
|
|
|
|
|
|
sub new { |
13
|
32
|
|
|
32
|
0
|
50895
|
my $class = shift; |
14
|
32
|
|
|
|
|
127
|
my (%params) = @_; |
15
|
|
|
|
|
|
|
|
16
|
32
|
|
|
|
|
87
|
my $self = { table => $params{from} }; |
17
|
32
|
|
|
|
|
71
|
bless $self, $class; |
18
|
|
|
|
|
|
|
|
19
|
32
|
|
|
|
|
61
|
$self->{from} = $params{from}; |
20
|
32
|
|
|
|
|
55
|
$self->{columns} = $params{columns}; |
21
|
|
|
|
|
|
|
|
22
|
32
|
|
|
|
|
58
|
$self->{join} = $params{join}; |
23
|
|
|
|
|
|
|
$self->{join} = [$self->{join}] |
24
|
32
|
100
|
100
|
|
|
140
|
if $self->{join} && ref $self->{join} ne 'ARRAY'; |
25
|
|
|
|
|
|
|
|
26
|
|
|
|
|
|
|
$self->{quoter} = |
27
|
32
|
|
33
|
|
|
181
|
$params{quoter} || SQL::Composer::Quoter->new(driver => $params{driver}); |
28
|
|
|
|
|
|
|
|
29
|
32
|
|
|
|
|
69
|
my $sql = ''; |
30
|
32
|
|
|
|
|
45
|
my @bind; |
31
|
|
|
|
|
|
|
|
32
|
|
|
|
|
|
|
my @columns = |
33
|
54
|
|
|
|
|
124
|
map { $self->_prepare_column($_, $self->{from}, \@bind) } |
34
|
32
|
|
|
|
|
56
|
@{$self->{columns}}; |
|
32
|
|
|
|
|
70
|
|
35
|
32
|
|
|
|
|
107
|
push @columns, $self->_collect_columns_from_joins($self->{join}); |
36
|
|
|
|
|
|
|
|
37
|
32
|
|
|
|
|
58
|
$sql .= 'SELECT '; |
38
|
|
|
|
|
|
|
|
39
|
32
|
50
|
|
|
|
83
|
if (@columns) { |
40
|
32
|
|
|
|
|
73
|
$sql .= join ',', @columns; |
41
|
|
|
|
|
|
|
} |
42
|
|
|
|
|
|
|
|
43
|
32
|
|
|
|
|
57
|
$sql .= ' FROM '; |
44
|
32
|
|
|
|
|
69
|
$sql .= $self->_quote($params{from}); |
45
|
|
|
|
|
|
|
|
46
|
32
|
100
|
|
|
|
79
|
if (my $joins = $self->{join}) { |
47
|
7
|
|
|
|
|
15
|
my ($join_sql, $join_bind) = $self->_build_join($joins); |
48
|
7
|
|
|
|
|
14
|
$sql .= $join_sql; |
49
|
7
|
|
|
|
|
16
|
push @bind, @$join_bind; |
50
|
|
|
|
|
|
|
} |
51
|
|
|
|
|
|
|
|
52
|
32
|
100
|
|
|
|
82
|
if (my $where = $params{where}) { |
53
|
1
|
50
|
|
|
|
13
|
if (!Scalar::Util::blessed($where)) { |
54
|
|
|
|
|
|
|
$where = SQL::Composer::Expression->new( |
55
|
|
|
|
|
|
|
default_prefix => $self->{from}, |
56
|
|
|
|
|
|
|
quoter => $self->{quoter}, |
57
|
1
|
|
|
|
|
9
|
expr => $where |
58
|
|
|
|
|
|
|
); |
59
|
|
|
|
|
|
|
} |
60
|
|
|
|
|
|
|
|
61
|
1
|
50
|
|
|
|
4
|
if (my $where_sql = $where->to_sql) { |
62
|
1
|
|
|
|
|
3
|
$sql .= ' WHERE ' . $where_sql; |
63
|
1
|
|
|
|
|
4
|
push @bind, $where->to_bind; |
64
|
|
|
|
|
|
|
} |
65
|
|
|
|
|
|
|
} |
66
|
|
|
|
|
|
|
|
67
|
32
|
100
|
|
|
|
71
|
if (my $group_bys = $params{group_by}) { |
68
|
4
|
100
|
|
|
|
13
|
$group_bys = [$group_bys] unless ref $group_bys eq 'ARRAY'; |
69
|
|
|
|
|
|
|
|
70
|
4
|
|
|
|
|
7
|
my @group_by; |
71
|
4
|
|
|
|
|
9
|
foreach my $group_by (@$group_bys) { |
72
|
|
|
|
|
|
|
push @group_by, |
73
|
|
|
|
|
|
|
ref($group_by) |
74
|
|
|
|
|
|
|
? $$group_by |
75
|
6
|
100
|
|
|
|
19
|
: $self->_quote($group_by, $self->{from}); |
76
|
|
|
|
|
|
|
} |
77
|
|
|
|
|
|
|
|
78
|
4
|
|
|
|
|
12
|
$sql .= ' GROUP BY ' . join(', ', @group_by); |
79
|
|
|
|
|
|
|
} |
80
|
|
|
|
|
|
|
|
81
|
32
|
100
|
|
|
|
80
|
if (my $having = $params{having}) { |
82
|
1
|
50
|
|
|
|
7
|
if (!Scalar::Util::blessed($having)) { |
83
|
|
|
|
|
|
|
$having = SQL::Composer::Expression->new( |
84
|
|
|
|
|
|
|
default_prefix => $self->{from}, |
85
|
|
|
|
|
|
|
quoter => $self->{quoter}, |
86
|
1
|
|
|
|
|
5
|
expr => $having |
87
|
|
|
|
|
|
|
); |
88
|
|
|
|
|
|
|
} |
89
|
|
|
|
|
|
|
|
90
|
1
|
50
|
|
|
|
4
|
if (my $having_sql = $having->to_sql) { |
91
|
1
|
|
|
|
|
4
|
$sql .= ' HAVING ' . $having->to_sql; |
92
|
1
|
|
|
|
|
3
|
push @bind, $having->to_bind; |
93
|
|
|
|
|
|
|
} |
94
|
|
|
|
|
|
|
} |
95
|
|
|
|
|
|
|
|
96
|
32
|
100
|
|
|
|
69
|
if (my $order_by = $params{order_by}) { |
97
|
8
|
|
|
|
|
17
|
$sql .= ' ORDER BY '; |
98
|
8
|
100
|
|
|
|
20
|
if (ref $order_by) { |
99
|
7
|
100
|
|
|
|
20
|
if (ref($order_by) eq 'ARRAY') { |
|
|
50
|
|
|
|
|
|
100
|
6
|
|
|
|
|
9
|
my @order; |
101
|
6
|
|
|
|
|
22
|
while (my ($key, $value) = splice @$order_by, 0, 2) { |
102
|
7
|
|
|
|
|
14
|
my $order_type; |
103
|
|
|
|
|
|
|
|
104
|
7
|
100
|
|
|
|
14
|
if (ref $value) { |
105
|
1
|
|
|
|
|
2
|
$order_type = ' ' . $$value; |
106
|
|
|
|
|
|
|
} |
107
|
|
|
|
|
|
|
else { |
108
|
6
|
100
|
|
|
|
15
|
$value = '' unless defined $value; |
109
|
6
|
|
|
|
|
10
|
$order_type = uc($value); |
110
|
6
|
100
|
100
|
|
|
27
|
if ($order_type eq 'ASC' || $order_type eq 'DESC') { |
111
|
4
|
|
|
|
|
9
|
$order_type = " $order_type"; |
112
|
|
|
|
|
|
|
} |
113
|
|
|
|
|
|
|
else { |
114
|
2
|
|
|
|
|
5
|
$order_type = ''; |
115
|
|
|
|
|
|
|
} |
116
|
|
|
|
|
|
|
} |
117
|
|
|
|
|
|
|
|
118
|
7
|
100
|
|
|
|
16
|
if (ref($key) eq 'SCALAR') { |
119
|
1
|
|
|
|
|
5
|
push @order, $$key . $order_type; |
120
|
|
|
|
|
|
|
} |
121
|
|
|
|
|
|
|
else { |
122
|
|
|
|
|
|
|
push @order, |
123
|
6
|
|
|
|
|
15
|
$self->_quote($key, $self->{from}) . $order_type; |
124
|
|
|
|
|
|
|
} |
125
|
|
|
|
|
|
|
} |
126
|
6
|
|
|
|
|
16
|
$sql .= join ',', @order; |
127
|
|
|
|
|
|
|
} |
128
|
|
|
|
|
|
|
elsif (ref($order_by) eq 'SCALAR') { |
129
|
1
|
|
|
|
|
2
|
$sql .= $$order_by; |
130
|
|
|
|
|
|
|
} |
131
|
|
|
|
|
|
|
else { |
132
|
0
|
|
|
|
|
0
|
Carp::croak('unexpected reference'); |
133
|
|
|
|
|
|
|
} |
134
|
|
|
|
|
|
|
} |
135
|
|
|
|
|
|
|
else { |
136
|
1
|
|
|
|
|
4
|
$sql .= $self->_quote($order_by); |
137
|
|
|
|
|
|
|
} |
138
|
|
|
|
|
|
|
} |
139
|
|
|
|
|
|
|
|
140
|
32
|
100
|
|
|
|
81
|
if (defined(my $limit = $params{limit})) { |
141
|
5
|
100
|
|
|
|
26
|
$limit = 0 unless $limit =~ m/^[0-9]+$/; |
142
|
5
|
|
|
|
|
12
|
$sql .= ' LIMIT ' . $limit; |
143
|
|
|
|
|
|
|
} |
144
|
|
|
|
|
|
|
|
145
|
32
|
100
|
|
|
|
77
|
if (defined(my $offset = $params{offset})) { |
146
|
3
|
100
|
|
|
|
11
|
$offset = 0 unless $offset =~ m/^[0-9]+$/; |
147
|
3
|
|
|
|
|
7
|
$sql .= ' OFFSET ' . $offset; |
148
|
|
|
|
|
|
|
} |
149
|
|
|
|
|
|
|
|
150
|
32
|
100
|
|
|
|
67
|
if ($params{for_update}) { |
151
|
1
|
|
|
|
|
2
|
$sql .= ' FOR UPDATE'; |
152
|
|
|
|
|
|
|
} |
153
|
|
|
|
|
|
|
|
154
|
32
|
|
|
|
|
58
|
$self->{sql} = $sql; |
155
|
32
|
|
|
|
|
53
|
$self->{bind} = \@bind; |
156
|
|
|
|
|
|
|
|
157
|
32
|
|
|
|
|
115
|
return $self; |
158
|
|
|
|
|
|
|
} |
159
|
|
|
|
|
|
|
|
160
|
1
|
|
|
1
|
0
|
8
|
sub table { shift->{table} } |
161
|
|
|
|
|
|
|
|
162
|
31
|
|
|
31
|
0
|
131
|
sub to_sql { shift->{sql} } |
163
|
31
|
50
|
|
31
|
0
|
12457
|
sub to_bind { @{shift->{bind} || []} } |
|
31
|
|
|
|
|
146
|
|
164
|
|
|
|
|
|
|
|
165
|
|
|
|
|
|
|
sub from_rows { |
166
|
10
|
|
|
10
|
0
|
5510
|
my $self = shift; |
167
|
10
|
|
|
|
|
22
|
my ($rows) = @_; |
168
|
|
|
|
|
|
|
|
169
|
10
|
|
|
|
|
18
|
my $result = []; |
170
|
10
|
|
|
|
|
24
|
foreach my $row (@$rows) { |
171
|
10
|
|
|
|
|
18
|
my $set = {}; |
172
|
|
|
|
|
|
|
|
173
|
10
|
|
|
|
|
29
|
$self->_populate($set, $row, $self->{columns}); |
174
|
|
|
|
|
|
|
|
175
|
10
|
|
|
|
|
30
|
$self->_populate_joins($set, $row, $self->{join}); |
176
|
|
|
|
|
|
|
|
177
|
10
|
|
|
|
|
23
|
push @$result, $set; |
178
|
|
|
|
|
|
|
} |
179
|
|
|
|
|
|
|
|
180
|
10
|
|
|
|
|
56
|
return $result; |
181
|
|
|
|
|
|
|
} |
182
|
|
|
|
|
|
|
|
183
|
|
|
|
|
|
|
sub _prepare_column { |
184
|
63
|
|
|
63
|
|
102
|
my $self = shift; |
185
|
63
|
|
|
|
|
115
|
my ($column, $prefix, $bind) = @_; |
186
|
|
|
|
|
|
|
|
187
|
63
|
100
|
|
|
|
171
|
if (ref $column eq 'SCALAR') { |
|
|
100
|
|
|
|
|
|
188
|
1
|
|
|
|
|
4
|
return $$column; |
189
|
|
|
|
|
|
|
} |
190
|
|
|
|
|
|
|
elsif (ref $column eq 'HASH') { |
191
|
|
|
|
|
|
|
return ( |
192
|
|
|
|
|
|
|
ref($column->{-col}) |
193
|
|
|
|
|
|
|
? ( |
194
|
|
|
|
|
|
|
do { |
195
|
2
|
|
|
|
|
5
|
my $value = $column->{-col}; |
196
|
2
|
100
|
|
|
|
6
|
if (ref $$value eq 'ARRAY') { |
197
|
1
|
|
|
|
|
3
|
my $sql = $$value->[0]; |
198
|
1
|
|
|
|
|
4
|
push @$bind, @$$value[1 .. $#{$$value}]; |
|
1
|
|
|
|
|
3
|
|
199
|
1
|
|
|
|
|
5
|
$sql; |
200
|
|
|
|
|
|
|
} |
201
|
|
|
|
|
|
|
else { |
202
|
1
|
|
|
|
|
16
|
$$value; |
203
|
|
|
|
|
|
|
} |
204
|
|
|
|
|
|
|
} |
205
|
|
|
|
|
|
|
) |
206
|
|
|
|
|
|
|
: $self->_quote($column->{-col}, $prefix) |
207
|
|
|
|
|
|
|
) |
208
|
|
|
|
|
|
|
. ' AS ' |
209
|
3
|
100
|
|
|
|
13
|
. $self->_quote($column->{-as}); |
210
|
|
|
|
|
|
|
} |
211
|
|
|
|
|
|
|
else { |
212
|
59
|
|
|
|
|
129
|
return $self->_quote($column, $prefix); |
213
|
|
|
|
|
|
|
} |
214
|
|
|
|
|
|
|
} |
215
|
|
|
|
|
|
|
|
216
|
|
|
|
|
|
|
sub _populate { |
217
|
19
|
|
|
19
|
|
28
|
my $self = shift; |
218
|
19
|
|
|
|
|
33
|
my ($set, $row, $columns) = @_; |
219
|
|
|
|
|
|
|
|
220
|
19
|
|
|
|
|
26
|
my $name; |
221
|
19
|
|
|
|
|
30
|
foreach my $column (@$columns) { |
222
|
20
|
100
|
|
|
|
57
|
if (ref($column) eq 'HASH') { |
|
|
100
|
|
|
|
|
|
223
|
2
|
|
|
|
|
5
|
$name = $column->{-as}; |
224
|
|
|
|
|
|
|
} |
225
|
|
|
|
|
|
|
elsif (ref($column) eq 'SCALAR') { |
226
|
1
|
|
|
|
|
2
|
$name = $$column; |
227
|
|
|
|
|
|
|
} |
228
|
|
|
|
|
|
|
else { |
229
|
17
|
|
|
|
|
28
|
$name = $column; |
230
|
|
|
|
|
|
|
} |
231
|
|
|
|
|
|
|
|
232
|
20
|
|
|
|
|
53
|
$set->{$name} = shift @$row; |
233
|
|
|
|
|
|
|
} |
234
|
|
|
|
|
|
|
} |
235
|
|
|
|
|
|
|
|
236
|
|
|
|
|
|
|
sub _populate_joins { |
237
|
13
|
|
|
13
|
|
23
|
my $self = shift; |
238
|
13
|
|
|
|
|
21
|
my ($set, $row, $joins) = @_; |
239
|
|
|
|
|
|
|
|
240
|
13
|
|
|
|
|
23
|
foreach my $join (@$joins) { |
241
|
9
|
|
66
|
|
|
41
|
my $join_source = $join->{rel_name} || $join->{as} || $join->{source}; |
242
|
|
|
|
|
|
|
|
243
|
9
|
|
50
|
|
|
44
|
$set->{$join_source} ||= {}; |
244
|
9
|
|
|
|
|
26
|
$self->_populate($set->{$join_source}, $row, $join->{columns}); |
245
|
|
|
|
|
|
|
|
246
|
9
|
100
|
|
|
|
27
|
if (my $subjoins = $join->{join}) { |
247
|
3
|
50
|
|
|
|
9
|
$subjoins = [$subjoins] unless ref $subjoins eq 'ARRAY'; |
248
|
|
|
|
|
|
|
|
249
|
3
|
|
|
|
|
9
|
$self->_populate_joins($set->{$join_source}, $row, $subjoins); |
250
|
|
|
|
|
|
|
} |
251
|
|
|
|
|
|
|
} |
252
|
|
|
|
|
|
|
} |
253
|
|
|
|
|
|
|
|
254
|
|
|
|
|
|
|
sub _collect_columns_from_joins { |
255
|
35
|
|
|
35
|
|
52
|
my $self = shift; |
256
|
35
|
|
|
|
|
61
|
my ($joins) = @_; |
257
|
|
|
|
|
|
|
|
258
|
35
|
100
|
66
|
|
|
122
|
return () unless $joins && @$joins; |
259
|
|
|
|
|
|
|
|
260
|
10
|
|
|
|
|
16
|
my @join_columns; |
261
|
10
|
|
|
|
|
19
|
foreach my $join_params (@$joins) { |
262
|
11
|
100
|
|
|
|
28
|
if (my $join_columns = $join_params->{columns}) { |
263
|
|
|
|
|
|
|
push @join_columns, map { |
264
|
9
|
|
|
|
|
14
|
$self->_prepare_column($_, |
265
|
|
|
|
|
|
|
$join_params->{as} |
266
|
|
|
|
|
|
|
? $join_params->{as} |
267
|
|
|
|
|
|
|
: $join_params->{source}) |
268
|
9
|
100
|
|
|
|
30
|
} @$join_columns; |
269
|
|
|
|
|
|
|
} |
270
|
|
|
|
|
|
|
|
271
|
11
|
100
|
|
|
|
36
|
if (my $subjoins = $join_params->{join}) { |
272
|
3
|
50
|
|
|
|
10
|
$subjoins = [$subjoins] unless ref $subjoins eq 'ARRAY'; |
273
|
|
|
|
|
|
|
|
274
|
3
|
|
|
|
|
11
|
push @join_columns, $self->_collect_columns_from_joins($subjoins); |
275
|
|
|
|
|
|
|
} |
276
|
|
|
|
|
|
|
} |
277
|
|
|
|
|
|
|
|
278
|
10
|
|
|
|
|
23
|
return @join_columns; |
279
|
|
|
|
|
|
|
} |
280
|
|
|
|
|
|
|
|
281
|
|
|
|
|
|
|
sub _build_join { |
282
|
10
|
|
|
10
|
|
20
|
my $self = shift; |
283
|
10
|
|
|
|
|
13
|
my ($joins) = @_; |
284
|
|
|
|
|
|
|
|
285
|
10
|
50
|
|
|
|
31
|
$joins = [$joins] unless ref $joins eq 'ARRAY'; |
286
|
|
|
|
|
|
|
|
287
|
10
|
|
|
|
|
14
|
my $sql = ''; |
288
|
10
|
|
|
|
|
15
|
my @bind; |
289
|
10
|
|
|
|
|
17
|
foreach my $join_params (@$joins) { |
290
|
|
|
|
|
|
|
my $join = |
291
|
11
|
|
|
|
|
49
|
SQL::Composer::Join->new(quoter => $self->{quoter}, %$join_params); |
292
|
|
|
|
|
|
|
|
293
|
11
|
|
|
|
|
30
|
$sql .= ' ' . $join->to_sql; |
294
|
11
|
|
|
|
|
46
|
push @bind, $join->to_bind; |
295
|
|
|
|
|
|
|
|
296
|
11
|
100
|
|
|
|
40
|
if (my $subjoin = $join_params->{join}) { |
297
|
3
|
|
|
|
|
9
|
my ($subsql, $subbind) = $self->_build_join($subjoin); |
298
|
3
|
|
|
|
|
6
|
$sql .= $subsql; |
299
|
3
|
|
|
|
|
8
|
push @bind, @$subbind; |
300
|
|
|
|
|
|
|
} |
301
|
|
|
|
|
|
|
} |
302
|
|
|
|
|
|
|
|
303
|
10
|
|
|
|
|
26
|
return ($sql, \@bind); |
304
|
|
|
|
|
|
|
} |
305
|
|
|
|
|
|
|
|
306
|
|
|
|
|
|
|
sub _quote { |
307
|
106
|
|
|
106
|
|
149
|
my $self = shift; |
308
|
106
|
|
|
|
|
176
|
my ($column, $prefix) = @_; |
309
|
|
|
|
|
|
|
|
310
|
106
|
|
|
|
|
244
|
return $self->{quoter}->quote($column, $prefix); |
311
|
|
|
|
|
|
|
} |
312
|
|
|
|
|
|
|
|
313
|
|
|
|
|
|
|
1; |
314
|
|
|
|
|
|
|
__END__ |