line |
stmt |
bran |
cond |
sub |
pod |
time |
code |
1
|
|
|
|
|
|
|
use strict; |
2
|
16
|
|
|
16
|
|
1639453
|
use warnings; |
|
16
|
|
|
|
|
187
|
|
|
16
|
|
|
|
|
473
|
|
3
|
16
|
|
|
16
|
|
102
|
use 5.008001; |
|
16
|
|
|
|
|
38
|
|
|
16
|
|
|
|
|
396
|
|
4
|
16
|
|
|
16
|
|
478
|
our $VERSION = '1.22'; |
|
16
|
|
|
|
|
57
|
|
5
|
|
|
|
|
|
|
use Class::Accessor::Lite 0.05 ( |
6
|
|
|
|
|
|
|
ro => [qw/quote_char name_sep new_line strict driver select_class/], |
7
|
16
|
|
|
|
|
113
|
); |
8
|
16
|
|
|
16
|
|
7454
|
|
|
16
|
|
|
|
|
20011
|
|
9
|
|
|
|
|
|
|
use Carp (); |
10
|
16
|
|
|
16
|
|
1962
|
use SQL::Maker::Select; |
|
16
|
|
|
|
|
35
|
|
|
16
|
|
|
|
|
264
|
|
11
|
16
|
|
|
16
|
|
7318
|
use SQL::Maker::Select::Oracle; |
|
16
|
|
|
|
|
49
|
|
|
16
|
|
|
|
|
529
|
|
12
|
16
|
|
|
16
|
|
6658
|
use SQL::Maker::Condition; |
|
16
|
|
|
|
|
49
|
|
|
16
|
|
|
|
|
470
|
|
13
|
16
|
|
|
16
|
|
99
|
use SQL::Maker::Util; |
|
16
|
|
|
|
|
37
|
|
|
16
|
|
|
|
|
297
|
|
14
|
16
|
|
|
16
|
|
80
|
use Module::Load (); |
|
16
|
|
|
|
|
25
|
|
|
16
|
|
|
|
|
254
|
|
15
|
16
|
|
|
16
|
|
7409
|
use Scalar::Util (); |
|
16
|
|
|
|
|
18337
|
|
|
16
|
|
|
|
|
335
|
|
16
|
16
|
|
|
16
|
|
110
|
|
|
16
|
|
|
|
|
31
|
|
|
16
|
|
|
|
|
1086
|
|
17
|
|
|
|
|
|
|
my ($class, $role) = @_; |
18
|
|
|
|
|
|
|
$role = $role =~ s/^\+// ? $role : "SQL::Maker::Plugin::$role"; |
19
|
2
|
|
|
2
|
0
|
302
|
Module::Load::load($role); |
20
|
2
|
50
|
|
|
|
15
|
|
21
|
2
|
|
|
|
|
14
|
no strict 'refs'; |
22
|
|
|
|
|
|
|
for (@{"${role}::EXPORT"}) { |
23
|
16
|
|
|
16
|
|
101
|
*{"${class}::$_"} = *{"${role}::$_"}; |
|
16
|
|
|
|
|
31
|
|
|
16
|
|
|
|
|
35918
|
|
24
|
2
|
|
|
|
|
26
|
} |
|
2
|
|
|
|
|
18
|
|
25
|
2
|
|
|
|
|
7
|
} |
|
2
|
|
|
|
|
19
|
|
|
2
|
|
|
|
|
10
|
|
26
|
|
|
|
|
|
|
|
27
|
|
|
|
|
|
|
my $class = shift; |
28
|
|
|
|
|
|
|
my %args = @_ == 1 ? %{$_[0]} : @_; |
29
|
|
|
|
|
|
|
unless ($args{driver}) { |
30
|
56
|
|
|
56
|
1
|
275721
|
Carp::croak("'driver' is required for creating new instance of $class"); |
31
|
56
|
50
|
|
|
|
279
|
} |
|
0
|
|
|
|
|
0
|
|
32
|
56
|
50
|
|
|
|
180
|
my $driver = $args{driver}; |
33
|
0
|
|
|
|
|
0
|
unless ( defined $args{quote_char} ) { |
34
|
|
|
|
|
|
|
$args{quote_char} = do{ |
35
|
56
|
|
|
|
|
114
|
if ($driver eq 'mysql') { |
36
|
56
|
100
|
|
|
|
151
|
q{`} |
37
|
41
|
|
|
|
|
65
|
} else { |
38
|
41
|
100
|
|
|
|
105
|
q{"} |
39
|
25
|
|
|
|
|
161
|
} |
40
|
|
|
|
|
|
|
}; |
41
|
16
|
|
|
|
|
41
|
} |
42
|
|
|
|
|
|
|
$args{select_class} = $driver eq 'Oracle' ? 'SQL::Maker::Select::Oracle' : 'SQL::Maker::Select'; |
43
|
|
|
|
|
|
|
|
44
|
|
|
|
|
|
|
return bless { |
45
|
56
|
50
|
|
|
|
178
|
name_sep => '.', |
46
|
|
|
|
|
|
|
new_line => "\n", |
47
|
56
|
|
|
|
|
431
|
strict => 0, |
48
|
|
|
|
|
|
|
%args |
49
|
|
|
|
|
|
|
}, $class; |
50
|
|
|
|
|
|
|
} |
51
|
|
|
|
|
|
|
|
52
|
|
|
|
|
|
|
my $self = shift; |
53
|
|
|
|
|
|
|
|
54
|
|
|
|
|
|
|
SQL::Maker::Condition->new( |
55
|
|
|
|
|
|
|
quote_char => $self->{quote_char}, |
56
|
125
|
|
|
125
|
1
|
85892
|
name_sep => $self->{name_sep}, |
57
|
|
|
|
|
|
|
strict => $self->{strict}, |
58
|
|
|
|
|
|
|
); |
59
|
|
|
|
|
|
|
} |
60
|
|
|
|
|
|
|
|
61
|
|
|
|
|
|
|
my $self = shift; |
62
|
125
|
|
|
|
|
529
|
my %args = @_==1 ? %{$_[0]} : @_; |
63
|
|
|
|
|
|
|
|
64
|
|
|
|
|
|
|
return $self->select_class->new( |
65
|
|
|
|
|
|
|
name_sep => $self->name_sep, |
66
|
96
|
|
|
96
|
1
|
3973
|
quote_char => $self->quote_char, |
67
|
96
|
50
|
|
|
|
245
|
new_line => $self->new_line, |
|
0
|
|
|
|
|
0
|
|
68
|
|
|
|
|
|
|
strict => $self->strict, |
69
|
96
|
|
|
|
|
271
|
%args, |
70
|
|
|
|
|
|
|
); |
71
|
|
|
|
|
|
|
} |
72
|
|
|
|
|
|
|
|
73
|
|
|
|
|
|
|
# $builder->insert($table, \%values, \%opt); |
74
|
|
|
|
|
|
|
# $builder->insert($table, \@values, \%opt); |
75
|
|
|
|
|
|
|
my ($self, $table, $values, $opt) = @_; |
76
|
|
|
|
|
|
|
my $prefix = $opt->{prefix} || 'INSERT INTO'; |
77
|
|
|
|
|
|
|
|
78
|
|
|
|
|
|
|
my $quoted_table = $self->_quote($table); |
79
|
|
|
|
|
|
|
|
80
|
|
|
|
|
|
|
my (@columns, @bind_columns, @quoted_columns, @values); |
81
|
15
|
|
|
15
|
1
|
9471
|
@values = ref $values eq 'HASH' ? %$values : @$values; |
82
|
15
|
|
100
|
|
|
70
|
while (my ($col, $val) = splice(@values, 0, 2)) { |
83
|
|
|
|
|
|
|
push @quoted_columns, $self->_quote($col); |
84
|
15
|
|
|
|
|
37
|
if (Scalar::Util::blessed($val)) { |
85
|
|
|
|
|
|
|
if ($val->can('as_sql')) { |
86
|
15
|
|
|
|
|
36
|
push @columns, $val->as_sql(undef, sub { $self->_quote($_[0]) }); |
87
|
15
|
100
|
|
|
|
77
|
push @bind_columns, $val->bind(); |
88
|
15
|
|
|
|
|
395
|
} else { |
89
|
50
|
|
|
|
|
116
|
push @columns, '?'; |
90
|
50
|
100
|
|
|
|
133
|
push @bind_columns, $val; |
91
|
9
|
100
|
|
|
|
52
|
} |
92
|
5
|
|
|
0
|
|
28
|
} else { |
|
0
|
|
|
|
|
0
|
|
93
|
5
|
|
|
|
|
74
|
Carp::croak("cannot pass in an unblessed ref as an argument in strict mode") |
94
|
|
|
|
|
|
|
if ref($val) && $self->strict; |
95
|
4
|
|
|
|
|
7
|
if (ref($val) eq 'SCALAR') { |
96
|
4
|
|
|
|
|
16
|
# $builder->insert(foo => { created_on => \"NOW()" }); |
97
|
|
|
|
|
|
|
push @columns, $$val; |
98
|
|
|
|
|
|
|
} |
99
|
41
|
100
|
100
|
|
|
116
|
elsif (ref($val) eq 'REF' && ref($$val) eq 'ARRAY') { |
100
|
|
|
|
|
|
|
# $builder->insert( foo => \[ 'UNIX_TIMESTAMP(?)', '2011-04-12 00:34:12' ] ); |
101
|
40
|
100
|
66
|
|
|
215
|
my ( $stmt, @sub_bind ) = @{$$val}; |
|
|
100
|
|
|
|
|
|
102
|
|
|
|
|
|
|
push @columns, $stmt; |
103
|
8
|
|
|
|
|
26
|
push @bind_columns, @sub_bind; |
104
|
|
|
|
|
|
|
} |
105
|
|
|
|
|
|
|
else { |
106
|
|
|
|
|
|
|
# normal values |
107
|
8
|
|
|
|
|
15
|
push @columns, '?'; |
|
8
|
|
|
|
|
20
|
|
108
|
8
|
|
|
|
|
20
|
push @bind_columns, $val; |
109
|
8
|
|
|
|
|
29
|
} |
110
|
|
|
|
|
|
|
} |
111
|
|
|
|
|
|
|
} |
112
|
|
|
|
|
|
|
|
113
|
24
|
|
|
|
|
38
|
# Insert an empty record in SQLite. |
114
|
24
|
|
|
|
|
77
|
# ref. https://github.com/tokuhirom/SQL-Maker/issues/11 |
115
|
|
|
|
|
|
|
if ($self->driver eq 'SQLite' && @columns==0) { |
116
|
|
|
|
|
|
|
my $sql = "$prefix $quoted_table" . $self->new_line . 'DEFAULT VALUES'; |
117
|
|
|
|
|
|
|
return ($sql); |
118
|
|
|
|
|
|
|
} |
119
|
|
|
|
|
|
|
|
120
|
|
|
|
|
|
|
my $sql = "$prefix $quoted_table" . $self->new_line; |
121
|
14
|
100
|
100
|
|
|
80
|
$sql .= '(' . join(', ', @quoted_columns) .')' . $self->new_line . |
122
|
1
|
|
|
|
|
16
|
'VALUES (' . join(', ', @columns) . ')'; |
123
|
1
|
|
|
|
|
9
|
|
124
|
|
|
|
|
|
|
return ($sql, @bind_columns); |
125
|
|
|
|
|
|
|
} |
126
|
13
|
|
|
|
|
136
|
|
127
|
13
|
|
|
|
|
106
|
my ($self, $label) = @_; |
128
|
|
|
|
|
|
|
|
129
|
|
|
|
|
|
|
SQL::Maker::Util::quote_identifier($label, $self->quote_char(), $self->name_sep()); |
130
|
13
|
|
|
|
|
142
|
} |
131
|
|
|
|
|
|
|
|
132
|
|
|
|
|
|
|
my ($self, $table, $where, $opt) = @_; |
133
|
|
|
|
|
|
|
|
134
|
242
|
|
|
242
|
|
493
|
my $w = $self->_make_where_clause($where); |
135
|
|
|
|
|
|
|
my $quoted_table = $self->_quote($table); |
136
|
242
|
|
|
|
|
556
|
my $sql = "DELETE FROM $quoted_table"; |
137
|
|
|
|
|
|
|
if ($opt->{using}) { |
138
|
|
|
|
|
|
|
# $bulder->delete('foo', \%where, { using => 'bar' }); |
139
|
|
|
|
|
|
|
# $bulder->delete('foo', \%where, { using => ['bar', 'qux'] }); |
140
|
21
|
|
|
21
|
1
|
11018
|
my $tables = ref($opt->{using}) eq 'ARRAY' ? $opt->{using} : [$opt->{using}]; |
141
|
|
|
|
|
|
|
my $using = join(', ', map { $self->_quote($_) } @$tables); |
142
|
21
|
|
|
|
|
48
|
$sql .= " USING " . $using; |
143
|
20
|
|
|
|
|
75
|
} |
144
|
20
|
|
|
|
|
45
|
$sql .= $w->[0]; |
145
|
20
|
100
|
|
|
|
74
|
return ($sql, @{$w->[1]}); |
146
|
|
|
|
|
|
|
} |
147
|
|
|
|
|
|
|
|
148
|
4
|
100
|
|
|
|
23
|
my ($self, $table, $args, $where) = @_; |
149
|
4
|
|
|
|
|
10
|
|
|
6
|
|
|
|
|
13
|
|
150
|
4
|
|
|
|
|
22
|
my ($columns, $bind_columns) = $self->make_set_clause($args); |
151
|
|
|
|
|
|
|
|
152
|
20
|
|
|
|
|
47
|
my $w = $self->_make_where_clause($where); |
153
|
20
|
|
|
|
|
32
|
push @$bind_columns, @{$w->[1]}; |
|
20
|
|
|
|
|
116
|
|
154
|
|
|
|
|
|
|
|
155
|
|
|
|
|
|
|
my $quoted_table = $self->_quote($table); |
156
|
|
|
|
|
|
|
my $sql = "UPDATE $quoted_table SET " . join(', ', @$columns) . $w->[0]; |
157
|
33
|
|
|
33
|
1
|
114795
|
return ($sql, @$bind_columns); |
158
|
|
|
|
|
|
|
} |
159
|
33
|
|
|
|
|
89
|
|
160
|
|
|
|
|
|
|
# make "SET" clause. |
161
|
32
|
|
|
|
|
82
|
my ($self, $args) = @_; |
162
|
31
|
|
|
|
|
71
|
|
|
31
|
|
|
|
|
60
|
|
163
|
|
|
|
|
|
|
my (@columns, @bind_columns); |
164
|
31
|
|
|
|
|
70
|
my @args = ref $args eq 'HASH' ? %$args : @$args; |
165
|
31
|
|
|
|
|
143
|
while (my ($col, $val) = splice @args, 0, 2) { |
166
|
31
|
|
|
|
|
171
|
my $quoted_col = $self->_quote($col); |
167
|
|
|
|
|
|
|
if (Scalar::Util::blessed($val)) { |
168
|
|
|
|
|
|
|
if ($val->can('as_sql')) { |
169
|
|
|
|
|
|
|
push @columns, "$quoted_col = " . $val->as_sql(undef, sub { $self->_quote($_[0]) }); |
170
|
|
|
|
|
|
|
push @bind_columns, $val->bind(); |
171
|
37
|
|
|
37
|
0
|
82
|
} else { |
172
|
|
|
|
|
|
|
push @columns, "$quoted_col = ?"; |
173
|
37
|
|
|
|
|
59
|
push @bind_columns, $val; |
174
|
37
|
100
|
|
|
|
175
|
} |
175
|
37
|
|
|
|
|
652
|
} else { |
176
|
83
|
|
|
|
|
220
|
Carp::croak("cannot pass in an unblessed ref as an argument in strict mode") |
177
|
83
|
100
|
|
|
|
243
|
if ref($val) && $self->strict; |
178
|
14
|
100
|
|
|
|
74
|
if (ref $val eq 'SCALAR') { |
179
|
7
|
|
|
0
|
|
48
|
# $builder->update(foo => { created_on => \"NOW()" }); |
|
0
|
|
|
|
|
0
|
|
180
|
7
|
|
|
|
|
107
|
push @columns, "$quoted_col = " . $$val; |
181
|
|
|
|
|
|
|
} |
182
|
7
|
|
|
|
|
20
|
elsif (ref $val eq 'REF' && ref $$val eq 'ARRAY' ) { |
183
|
7
|
|
|
|
|
30
|
# $builder->update( foo => \[ 'VALUES(foo) + ?', 10 ] ); |
184
|
|
|
|
|
|
|
my ( $stmt, @sub_bind ) = @{$$val}; |
185
|
|
|
|
|
|
|
push @columns, "$quoted_col = " . $stmt; |
186
|
69
|
100
|
100
|
|
|
191
|
push @bind_columns, @sub_bind; |
187
|
|
|
|
|
|
|
} |
188
|
68
|
100
|
66
|
|
|
286
|
else { |
|
|
100
|
|
|
|
|
|
189
|
|
|
|
|
|
|
# normal values |
190
|
6
|
|
|
|
|
42
|
push @columns, "$quoted_col = ?"; |
191
|
|
|
|
|
|
|
push @bind_columns, $val; |
192
|
|
|
|
|
|
|
} |
193
|
|
|
|
|
|
|
} |
194
|
5
|
|
|
|
|
12
|
} |
|
5
|
|
|
|
|
13
|
|
195
|
5
|
|
|
|
|
16
|
return (\@columns, \@bind_columns); |
196
|
5
|
|
|
|
|
19
|
} |
197
|
|
|
|
|
|
|
|
198
|
|
|
|
|
|
|
my ($self, $where) = @_; |
199
|
|
|
|
|
|
|
my $cond = $self->_make_where_condition($where); |
200
|
57
|
|
|
|
|
133
|
return ($cond->as_sql(undef, sub { $self->_quote($_[0]) }), $cond->bind()); |
201
|
57
|
|
|
|
|
206
|
} |
202
|
|
|
|
|
|
|
|
203
|
|
|
|
|
|
|
my ($self, $where) = @_; |
204
|
|
|
|
|
|
|
|
205
|
36
|
|
|
|
|
134
|
return $self->new_condition unless $where; |
206
|
|
|
|
|
|
|
if ( Scalar::Util::blessed( $where ) and $where->can('as_sql') ) { |
207
|
|
|
|
|
|
|
return $where; |
208
|
|
|
|
|
|
|
} |
209
|
9
|
|
|
9
|
1
|
30275
|
|
210
|
9
|
|
|
|
|
27
|
my $w = $self->new_condition; |
211
|
9
|
|
|
2
|
|
54
|
my @w = ref $where eq 'ARRAY' ? @$where : %$where; |
|
2
|
|
|
|
|
31
|
|
212
|
|
|
|
|
|
|
while (my ($col, $val) = splice @w, 0, 2) { |
213
|
|
|
|
|
|
|
$w->add($col => $val); |
214
|
|
|
|
|
|
|
} |
215
|
132
|
|
|
132
|
|
237
|
return $w; |
216
|
|
|
|
|
|
|
} |
217
|
132
|
50
|
|
|
|
321
|
|
218
|
132
|
100
|
66
|
|
|
579
|
my ($self, $where) = @_; |
219
|
33
|
|
|
|
|
182
|
|
220
|
|
|
|
|
|
|
return ['', []] unless $where; |
221
|
|
|
|
|
|
|
|
222
|
99
|
|
|
|
|
230
|
my $w = $self->_make_where_condition($where); |
223
|
99
|
100
|
|
|
|
429
|
my $sql = $w->as_sql(undef, sub { $self->_quote($_[0]) }); |
224
|
99
|
|
|
|
|
840
|
return [$sql ? " WHERE $sql" : '', [$w->bind]]; |
225
|
79
|
|
|
|
|
225
|
} |
226
|
|
|
|
|
|
|
|
227
|
96
|
|
|
|
|
272
|
# my($stmt, @bind) = $sql−>select($table, \@fields, \%where, \%opt); |
228
|
|
|
|
|
|
|
my $stmt = shift->select_query(@_); |
229
|
|
|
|
|
|
|
return ($stmt->as_sql,@{$stmt->bind}); |
230
|
|
|
|
|
|
|
} |
231
|
53
|
|
|
53
|
|
105
|
|
232
|
|
|
|
|
|
|
my ($self, $table, $fields, $where, $opt) = @_; |
233
|
53
|
100
|
|
|
|
151
|
|
234
|
|
|
|
|
|
|
unless (ref $fields eq 'ARRAY') { |
235
|
41
|
|
|
|
|
97
|
Carp::croak("SQL::Maker::select_query: \$fields should be ArrayRef[Str]"); |
236
|
39
|
|
|
4
|
|
272
|
} |
|
4
|
|
|
|
|
55
|
|
237
|
39
|
50
|
|
|
|
246
|
|
238
|
|
|
|
|
|
|
my $stmt = $self->new_select; |
239
|
|
|
|
|
|
|
for my $field (@$fields) { |
240
|
|
|
|
|
|
|
$stmt->add_select(ref $field eq 'ARRAY' ? @$field : $field); |
241
|
|
|
|
|
|
|
} |
242
|
84
|
|
|
84
|
1
|
373173
|
|
243
|
83
|
|
|
|
|
212
|
if ( defined $table ) { |
|
80
|
|
|
|
|
187
|
|
244
|
|
|
|
|
|
|
unless ( ref $table ) { |
245
|
|
|
|
|
|
|
# $table = 'foo' |
246
|
|
|
|
|
|
|
$stmt->add_from( $table ); |
247
|
92
|
|
|
92
|
0
|
2108
|
} |
248
|
|
|
|
|
|
|
else { |
249
|
92
|
50
|
|
|
|
280
|
# $table = [ 'foo', [ bar => 'b' ] ] |
250
|
0
|
|
|
|
|
0
|
for ( @$table ) { |
251
|
|
|
|
|
|
|
$stmt->add_from( ref $_ eq 'ARRAY' ? @$_ : $_ ); |
252
|
|
|
|
|
|
|
} |
253
|
92
|
|
|
|
|
190
|
} |
254
|
92
|
|
|
|
|
225
|
} |
255
|
130
|
100
|
|
|
|
413
|
|
256
|
|
|
|
|
|
|
$stmt->prefix($opt->{prefix}) if $opt->{prefix}; |
257
|
|
|
|
|
|
|
|
258
|
92
|
100
|
|
|
|
192
|
if ( $where ) { |
259
|
87
|
100
|
|
|
|
170
|
$stmt->set_where($self->_make_where_condition($where)); |
260
|
|
|
|
|
|
|
} |
261
|
79
|
|
|
|
|
201
|
|
262
|
|
|
|
|
|
|
if ( my $joins = $opt->{joins} ) { |
263
|
|
|
|
|
|
|
for my $join ( @$joins ) { |
264
|
|
|
|
|
|
|
$stmt->add_join(ref $join eq 'ARRAY' ? @$join : $join); |
265
|
8
|
|
|
|
|
17
|
} |
266
|
14
|
100
|
|
|
|
49
|
} |
267
|
|
|
|
|
|
|
|
268
|
|
|
|
|
|
|
if (my $o = $opt->{order_by}) { |
269
|
|
|
|
|
|
|
if (ref $o eq 'ARRAY') { |
270
|
|
|
|
|
|
|
for my $order (@$o) { |
271
|
92
|
100
|
|
|
|
219
|
if (ref $order eq 'HASH') { |
272
|
|
|
|
|
|
|
# Skinny-ish [{foo => 'DESC'}, {bar => 'ASC'}] |
273
|
92
|
100
|
|
|
|
223
|
$stmt->add_order_by(%$order); |
274
|
82
|
|
|
|
|
182
|
} else { |
275
|
|
|
|
|
|
|
# just ['foo DESC', 'bar ASC'] |
276
|
|
|
|
|
|
|
$stmt->add_order_by(\$order); |
277
|
91
|
100
|
|
|
|
232
|
} |
278
|
5
|
|
|
|
|
18
|
} |
279
|
5
|
50
|
|
|
|
27
|
} elsif (ref $o eq 'HASH') { |
280
|
|
|
|
|
|
|
# Skinny-ish {foo => 'DESC'} |
281
|
|
|
|
|
|
|
$stmt->add_order_by(%$o); |
282
|
|
|
|
|
|
|
} else { |
283
|
91
|
100
|
|
|
|
210
|
# just 'foo DESC, bar ASC' |
284
|
27
|
100
|
|
|
|
83
|
$stmt->add_order_by(\$o); |
|
|
100
|
|
|
|
|
|
285
|
6
|
|
|
|
|
13
|
} |
286
|
12
|
100
|
|
|
|
25
|
} |
287
|
|
|
|
|
|
|
if (my $o = $opt->{group_by}) { |
288
|
3
|
|
|
|
|
21
|
if (ref $o eq 'ARRAY') { |
289
|
|
|
|
|
|
|
for my $group (@$o) { |
290
|
|
|
|
|
|
|
if (ref $group eq 'HASH') { |
291
|
9
|
|
|
|
|
22
|
# Skinny-ish [{foo => 'DESC'}, {bar => 'ASC'}] |
292
|
|
|
|
|
|
|
$stmt->add_group_by(%$group); |
293
|
|
|
|
|
|
|
} else { |
294
|
|
|
|
|
|
|
# just ['foo DESC', 'bar ASC'] |
295
|
|
|
|
|
|
|
$stmt->add_group_by(\$group); |
296
|
3
|
|
|
|
|
11
|
} |
297
|
|
|
|
|
|
|
} |
298
|
|
|
|
|
|
|
} elsif (ref $o eq 'HASH') { |
299
|
18
|
|
|
|
|
53
|
# Skinny-ish {foo => 'DESC'} |
300
|
|
|
|
|
|
|
$stmt->add_group_by(%$o); |
301
|
|
|
|
|
|
|
} else { |
302
|
91
|
100
|
|
|
|
230
|
# just 'foo DESC, bar ASC' |
303
|
12
|
100
|
|
|
|
42
|
$stmt->add_group_by(\$o); |
|
|
100
|
|
|
|
|
|
304
|
6
|
|
|
|
|
27
|
} |
305
|
12
|
100
|
|
|
|
36
|
} |
306
|
|
|
|
|
|
|
if (my $o = $opt->{index_hint}) { |
307
|
3
|
|
|
|
|
19
|
if (defined $table) { |
308
|
|
|
|
|
|
|
$stmt->add_index_hint($table, $o); |
309
|
|
|
|
|
|
|
} |
310
|
9
|
|
|
|
|
20
|
elsif (my $joins = $opt->{joins}) { |
311
|
|
|
|
|
|
|
my $target_table = ref $joins->[0][0] eq 'ARRAY' ? $joins->[0][0][0] : $joins->[0][0]; |
312
|
|
|
|
|
|
|
$stmt->add_index_hint($target_table, $o); |
313
|
|
|
|
|
|
|
} |
314
|
|
|
|
|
|
|
} |
315
|
3
|
|
|
|
|
13
|
|
316
|
|
|
|
|
|
|
$stmt->limit( $opt->{limit} ) if defined $opt->{limit}; |
317
|
|
|
|
|
|
|
$stmt->offset( $opt->{offset} ) if $opt->{offset}; |
318
|
3
|
|
|
|
|
10
|
|
319
|
|
|
|
|
|
|
if (my $terms = $opt->{having}) { |
320
|
|
|
|
|
|
|
while (my ($col, $val) = each %$terms) { |
321
|
91
|
100
|
|
|
|
178
|
$stmt->add_having($col => $val); |
322
|
5
|
100
|
|
|
|
15
|
} |
|
|
50
|
|
|
|
|
|
323
|
3
|
|
|
|
|
8
|
} |
324
|
|
|
|
|
|
|
|
325
|
|
|
|
|
|
|
$stmt->for_update(1) if $opt->{for_update}; |
326
|
2
|
100
|
|
|
|
7
|
return $stmt; |
327
|
2
|
|
|
|
|
7
|
} |
328
|
|
|
|
|
|
|
|
329
|
|
|
|
|
|
|
1; |
330
|
|
|
|
|
|
|
|
331
|
91
|
100
|
|
|
|
217
|
=encoding utf8 |
332
|
91
|
100
|
|
|
|
180
|
|
333
|
|
|
|
|
|
|
=for test_synopsis |
334
|
91
|
50
|
|
|
|
167
|
my ($table, @fields, %where, %opt, %values, %set, $sql, @binds, @set); |
335
|
0
|
|
|
|
|
0
|
|
336
|
0
|
|
|
|
|
0
|
=head1 NAME |
337
|
|
|
|
|
|
|
|
338
|
|
|
|
|
|
|
SQL::Maker - Yet another SQL builder |
339
|
|
|
|
|
|
|
|
340
|
91
|
50
|
|
|
|
173
|
=head1 SYNOPSIS |
341
|
91
|
|
|
|
|
187
|
|
342
|
|
|
|
|
|
|
use SQL::Maker; |
343
|
|
|
|
|
|
|
|
344
|
|
|
|
|
|
|
my $builder = SQL::Maker->new( |
345
|
|
|
|
|
|
|
driver => 'SQLite', # or your favorite driver |
346
|
|
|
|
|
|
|
); |
347
|
|
|
|
|
|
|
|
348
|
|
|
|
|
|
|
# SELECT |
349
|
|
|
|
|
|
|
($sql, @binds) = $builder->select($table, \@fields, \%where, \%opt); |
350
|
|
|
|
|
|
|
|
351
|
|
|
|
|
|
|
# INSERT |
352
|
|
|
|
|
|
|
($sql, @binds) = $builder->insert($table, \%values, \%opt); |
353
|
|
|
|
|
|
|
|
354
|
|
|
|
|
|
|
# DELETE |
355
|
|
|
|
|
|
|
($sql, @binds) = $builder->delete($table, \%where, \%opt); |
356
|
|
|
|
|
|
|
|
357
|
|
|
|
|
|
|
# UPDATE |
358
|
|
|
|
|
|
|
($sql, @binds) = $builder->update($table, \%set, \%where); |
359
|
|
|
|
|
|
|
($sql, @binds) = $builder->update($table, \@set, \%where); |
360
|
|
|
|
|
|
|
|
361
|
|
|
|
|
|
|
=head1 DESCRIPTION |
362
|
|
|
|
|
|
|
|
363
|
|
|
|
|
|
|
SQL::Maker is yet another SQL builder class. It is based on L<DBIx::Skinny>'s SQL generator. |
364
|
|
|
|
|
|
|
|
365
|
|
|
|
|
|
|
=head1 METHODS |
366
|
|
|
|
|
|
|
|
367
|
|
|
|
|
|
|
=over 4 |
368
|
|
|
|
|
|
|
|
369
|
|
|
|
|
|
|
=item C<< my $builder = SQL::Maker->new(%args); >> |
370
|
|
|
|
|
|
|
|
371
|
|
|
|
|
|
|
Create new instance of SQL::Maker. |
372
|
|
|
|
|
|
|
|
373
|
|
|
|
|
|
|
Attributes are the following: |
374
|
|
|
|
|
|
|
|
375
|
|
|
|
|
|
|
=over 4 |
376
|
|
|
|
|
|
|
|
377
|
|
|
|
|
|
|
=item driver: Str |
378
|
|
|
|
|
|
|
|
379
|
|
|
|
|
|
|
Driver name is required. The driver type is needed to create SQL string. |
380
|
|
|
|
|
|
|
|
381
|
|
|
|
|
|
|
=item quote_char: Str |
382
|
|
|
|
|
|
|
|
383
|
|
|
|
|
|
|
This is the character that a table or column name will be quoted with. |
384
|
|
|
|
|
|
|
|
385
|
|
|
|
|
|
|
Default: auto detect from $driver. |
386
|
|
|
|
|
|
|
|
387
|
|
|
|
|
|
|
=item name_sep: Str |
388
|
|
|
|
|
|
|
|
389
|
|
|
|
|
|
|
This is the character that separates a table and column name. |
390
|
|
|
|
|
|
|
|
391
|
|
|
|
|
|
|
Default: '.' |
392
|
|
|
|
|
|
|
|
393
|
|
|
|
|
|
|
=item new_line: Str |
394
|
|
|
|
|
|
|
|
395
|
|
|
|
|
|
|
This is the character that separates a part of statements. |
396
|
|
|
|
|
|
|
|
397
|
|
|
|
|
|
|
Default: '\n' |
398
|
|
|
|
|
|
|
|
399
|
|
|
|
|
|
|
=item strict: Bool |
400
|
|
|
|
|
|
|
|
401
|
|
|
|
|
|
|
Whether or not the use of unblessed references are prohibited for defining the SQL expressions. |
402
|
|
|
|
|
|
|
|
403
|
|
|
|
|
|
|
In strict mode, all the expressions must be declared by using blessed references that export C<as_sql> and C<bind> methods like L<SQL::QueryMaker>. |
404
|
|
|
|
|
|
|
See L</STRICT MODE> for detail. |
405
|
|
|
|
|
|
|
|
406
|
|
|
|
|
|
|
Default: undef |
407
|
|
|
|
|
|
|
|
408
|
|
|
|
|
|
|
=back |
409
|
|
|
|
|
|
|
|
410
|
|
|
|
|
|
|
=item C<< my $select = $builder->new_select(%args|\%args); >> |
411
|
|
|
|
|
|
|
|
412
|
|
|
|
|
|
|
Create new instance of L<SQL::Maker::Select> using the settings from B<$builder>. |
413
|
|
|
|
|
|
|
|
414
|
|
|
|
|
|
|
This method returns an instance of L<SQL::Maker::Select>. |
415
|
|
|
|
|
|
|
|
416
|
|
|
|
|
|
|
=item C<< my ($sql, @binds) = $builder->select($table|\@tables, \@fields, \%where|\@where|$where, \%opt); >> |
417
|
|
|
|
|
|
|
|
418
|
|
|
|
|
|
|
my ($sql, @binds) = $builder->select('user', ['*'], {name => 'john'}, {order_by => 'user_id DESC'}); |
419
|
|
|
|
|
|
|
# => |
420
|
|
|
|
|
|
|
# SELECT * FROM `user` WHERE (`name` = ?) ORDER BY user_id DESC |
421
|
|
|
|
|
|
|
# ['john'] |
422
|
|
|
|
|
|
|
|
423
|
|
|
|
|
|
|
This method returns the SQL string and bind variables for a SELECT statement. |
424
|
|
|
|
|
|
|
|
425
|
|
|
|
|
|
|
=over 4 |
426
|
|
|
|
|
|
|
|
427
|
|
|
|
|
|
|
=item C<< $table >> |
428
|
|
|
|
|
|
|
|
429
|
|
|
|
|
|
|
=item C<< \@tables >> |
430
|
|
|
|
|
|
|
|
431
|
|
|
|
|
|
|
Table name for the B<FROM> clause as scalar or arrayref. You can specify the instance of B<SQL::Maker::Select> for a sub-query. |
432
|
|
|
|
|
|
|
|
433
|
|
|
|
|
|
|
If you are using C<< $opt->{joins} >> this should be I<< undef >> since it's passed via the first join. |
434
|
|
|
|
|
|
|
|
435
|
|
|
|
|
|
|
=item C<< \@fields >> |
436
|
|
|
|
|
|
|
|
437
|
|
|
|
|
|
|
This is a list for retrieving fields from database. |
438
|
|
|
|
|
|
|
|
439
|
|
|
|
|
|
|
Each element of the C<@fields> is normally a scalar or a scalar ref containing the column name. |
440
|
|
|
|
|
|
|
If you want to specify an alias of the field, you can use an arrayref containing a pair |
441
|
|
|
|
|
|
|
of column and alias names (e.g. C<< ['foo.id' => 'foo_id'] >>). |
442
|
|
|
|
|
|
|
|
443
|
|
|
|
|
|
|
=item C<< \%where >> |
444
|
|
|
|
|
|
|
|
445
|
|
|
|
|
|
|
=item C<< \@where >> |
446
|
|
|
|
|
|
|
|
447
|
|
|
|
|
|
|
=item C<< $where >> |
448
|
|
|
|
|
|
|
|
449
|
|
|
|
|
|
|
where clause from hashref or arrayref via L<SQL::Maker::Condition>, or L<SQL::Maker::Condition> object, or L<SQL::QueryMaker> object. |
450
|
|
|
|
|
|
|
|
451
|
|
|
|
|
|
|
=item C<< \%opt >> |
452
|
|
|
|
|
|
|
|
453
|
|
|
|
|
|
|
These are the options for the SELECT statement |
454
|
|
|
|
|
|
|
|
455
|
|
|
|
|
|
|
=over 4 |
456
|
|
|
|
|
|
|
|
457
|
|
|
|
|
|
|
=item C<< $opt->{prefix} >> |
458
|
|
|
|
|
|
|
|
459
|
|
|
|
|
|
|
This is a prefix for the SELECT statement. |
460
|
|
|
|
|
|
|
|
461
|
|
|
|
|
|
|
For example, you can provide the 'SELECT SQL_CALC_FOUND_ROWS '. It's useful for MySQL. |
462
|
|
|
|
|
|
|
|
463
|
|
|
|
|
|
|
Default Value: 'SELECT ' |
464
|
|
|
|
|
|
|
|
465
|
|
|
|
|
|
|
=item C<< $opt->{limit} >> |
466
|
|
|
|
|
|
|
|
467
|
|
|
|
|
|
|
This option adds a 'LIMIT $n' clause. |
468
|
|
|
|
|
|
|
|
469
|
|
|
|
|
|
|
=item C<< $opt->{offset} >> |
470
|
|
|
|
|
|
|
|
471
|
|
|
|
|
|
|
This option adds an 'OFFSET $n' clause. |
472
|
|
|
|
|
|
|
|
473
|
|
|
|
|
|
|
=item C<< $opt->{order_by} >> |
474
|
|
|
|
|
|
|
|
475
|
|
|
|
|
|
|
This option adds an B<ORDER BY> clause |
476
|
|
|
|
|
|
|
|
477
|
|
|
|
|
|
|
You can write it in any of the following forms: |
478
|
|
|
|
|
|
|
|
479
|
|
|
|
|
|
|
$builder->select(..., {order_by => 'foo DESC, bar ASC'}); |
480
|
|
|
|
|
|
|
$builder->select(..., {order_by => ['foo DESC', 'bar ASC']}); |
481
|
|
|
|
|
|
|
$builder->select(..., {order_by => {foo => 'DESC'}}); |
482
|
|
|
|
|
|
|
$builder->select(..., {order_by => [{foo => 'DESC'}, {bar => 'ASC'}]}); |
483
|
|
|
|
|
|
|
|
484
|
|
|
|
|
|
|
=item C<< $opt->{group_by} >> |
485
|
|
|
|
|
|
|
|
486
|
|
|
|
|
|
|
This option adds a B<GROUP BY> clause |
487
|
|
|
|
|
|
|
|
488
|
|
|
|
|
|
|
You can write it in any of the following forms: |
489
|
|
|
|
|
|
|
|
490
|
|
|
|
|
|
|
$builder->select(..., {group_by => 'foo DESC, bar ASC'}); |
491
|
|
|
|
|
|
|
$builder->select(..., {group_by => ['foo DESC', 'bar ASC']}); |
492
|
|
|
|
|
|
|
$builder->select(..., {group_by => {foo => 'DESC'}}); |
493
|
|
|
|
|
|
|
$builder->select(..., {group_by => [{foo => 'DESC'}, {bar => 'ASC'}]}); |
494
|
|
|
|
|
|
|
|
495
|
|
|
|
|
|
|
=item C<< $opt->{having} >> |
496
|
|
|
|
|
|
|
|
497
|
|
|
|
|
|
|
This option adds a HAVING clause |
498
|
|
|
|
|
|
|
|
499
|
|
|
|
|
|
|
=item C<< $opt->{for_update} >> |
500
|
|
|
|
|
|
|
|
501
|
|
|
|
|
|
|
This option adds a 'FOR UPDATE" clause. |
502
|
|
|
|
|
|
|
|
503
|
|
|
|
|
|
|
=item C<< $opt->{joins} >> |
504
|
|
|
|
|
|
|
|
505
|
|
|
|
|
|
|
This option adds a 'JOIN' via L<SQL::Maker::Select>. |
506
|
|
|
|
|
|
|
|
507
|
|
|
|
|
|
|
You can write it as follows: |
508
|
|
|
|
|
|
|
|
509
|
|
|
|
|
|
|
$builder->select(undef, ..., {joins => [[user => {table => 'group', condition => 'user.gid = group.gid'}], ...]}); |
510
|
|
|
|
|
|
|
|
511
|
|
|
|
|
|
|
=item C<< $opt->{index_hint} >> |
512
|
|
|
|
|
|
|
|
513
|
|
|
|
|
|
|
This option adds an INDEX HINT like as 'USE INDEX' clause for MySQL via L<SQL::Maker::Select>. |
514
|
|
|
|
|
|
|
|
515
|
|
|
|
|
|
|
You can write it as follows: |
516
|
|
|
|
|
|
|
|
517
|
|
|
|
|
|
|
$builder->select(..., { index_hint => 'foo' }); |
518
|
|
|
|
|
|
|
$builder->select(..., { index_hint => ['foo', 'bar'] }); |
519
|
|
|
|
|
|
|
$builder->select(..., { index_hint => { list => 'foo' }); |
520
|
|
|
|
|
|
|
$builder->select(..., { index_hint => { type => 'FORCE', list => ['foo', 'bar'] }); |
521
|
|
|
|
|
|
|
|
522
|
|
|
|
|
|
|
=back |
523
|
|
|
|
|
|
|
|
524
|
|
|
|
|
|
|
=back |
525
|
|
|
|
|
|
|
|
526
|
|
|
|
|
|
|
=item C<< my ($sql, @binds) = $builder->insert($table, \%values|\@values, \%opt); >> |
527
|
|
|
|
|
|
|
|
528
|
|
|
|
|
|
|
my ($sql, @binds) = $builder->insert(user => {name => 'john'}); |
529
|
|
|
|
|
|
|
# => |
530
|
|
|
|
|
|
|
# INSERT INTO `user` (`name`) VALUES (?) |
531
|
|
|
|
|
|
|
# ['john'] |
532
|
|
|
|
|
|
|
|
533
|
|
|
|
|
|
|
Generate an INSERT query. |
534
|
|
|
|
|
|
|
|
535
|
|
|
|
|
|
|
=over 4 |
536
|
|
|
|
|
|
|
|
537
|
|
|
|
|
|
|
=item C<< $table >> |
538
|
|
|
|
|
|
|
|
539
|
|
|
|
|
|
|
Table name in scalar. |
540
|
|
|
|
|
|
|
|
541
|
|
|
|
|
|
|
=item C<< \%values >> |
542
|
|
|
|
|
|
|
|
543
|
|
|
|
|
|
|
These are the values for the INSERT statement. |
544
|
|
|
|
|
|
|
|
545
|
|
|
|
|
|
|
=item C<< \%opt >> |
546
|
|
|
|
|
|
|
|
547
|
|
|
|
|
|
|
These are the options for the INSERT statement |
548
|
|
|
|
|
|
|
|
549
|
|
|
|
|
|
|
=over 4 |
550
|
|
|
|
|
|
|
|
551
|
|
|
|
|
|
|
=item C<< $opt->{prefix} >> |
552
|
|
|
|
|
|
|
|
553
|
|
|
|
|
|
|
This is a prefix for the INSERT statement. |
554
|
|
|
|
|
|
|
|
555
|
|
|
|
|
|
|
For example, you can provide 'INSERT IGNORE INTO' for MySQL. |
556
|
|
|
|
|
|
|
|
557
|
|
|
|
|
|
|
Default Value: 'INSERT INTO' |
558
|
|
|
|
|
|
|
|
559
|
|
|
|
|
|
|
=back |
560
|
|
|
|
|
|
|
|
561
|
|
|
|
|
|
|
=back |
562
|
|
|
|
|
|
|
|
563
|
|
|
|
|
|
|
=item C<< my ($sql, @binds) = $builder->delete($table, \%where|\@where|$where, \%opt); >> |
564
|
|
|
|
|
|
|
|
565
|
|
|
|
|
|
|
my ($sql, @binds) = $builder->delete($table, \%where); |
566
|
|
|
|
|
|
|
# => |
567
|
|
|
|
|
|
|
# DELETE FROM `user` WHERE (`name` = ?) |
568
|
|
|
|
|
|
|
# ['john'] |
569
|
|
|
|
|
|
|
|
570
|
|
|
|
|
|
|
Generate a DELETE query. |
571
|
|
|
|
|
|
|
|
572
|
|
|
|
|
|
|
=over 4 |
573
|
|
|
|
|
|
|
|
574
|
|
|
|
|
|
|
=item C<< $table >> |
575
|
|
|
|
|
|
|
|
576
|
|
|
|
|
|
|
Table name in scalar. |
577
|
|
|
|
|
|
|
|
578
|
|
|
|
|
|
|
=item C<< \%where >> |
579
|
|
|
|
|
|
|
|
580
|
|
|
|
|
|
|
=item C<< \@where >> |
581
|
|
|
|
|
|
|
|
582
|
|
|
|
|
|
|
=item C<< $where >> |
583
|
|
|
|
|
|
|
|
584
|
|
|
|
|
|
|
where clause from hashref or arrayref via L<SQL::Maker::Condition>, or L<SQL::Maker::Condition> object, or L<SQL::QueryMaker> object. |
585
|
|
|
|
|
|
|
|
586
|
|
|
|
|
|
|
=item C<< \%opt >> |
587
|
|
|
|
|
|
|
|
588
|
|
|
|
|
|
|
These are the options for the DELETE statement |
589
|
|
|
|
|
|
|
|
590
|
|
|
|
|
|
|
=over 4 |
591
|
|
|
|
|
|
|
|
592
|
|
|
|
|
|
|
=item C<< $opt->{using} >> |
593
|
|
|
|
|
|
|
|
594
|
|
|
|
|
|
|
This option adds a USING clause. It takes a scalar or an arrayref of table names as argument: |
595
|
|
|
|
|
|
|
|
596
|
|
|
|
|
|
|
my ($sql, $binds) = $bulder->delete($table, \%where, { using => 'group' }); |
597
|
|
|
|
|
|
|
# => |
598
|
|
|
|
|
|
|
# DELETE FROM `user` USING `group` WHERE (`group`.`name` = ?) |
599
|
|
|
|
|
|
|
# ['doe'] |
600
|
|
|
|
|
|
|
$bulder->delete(..., { using => ['bar', 'qux'] }); |
601
|
|
|
|
|
|
|
|
602
|
|
|
|
|
|
|
=back |
603
|
|
|
|
|
|
|
|
604
|
|
|
|
|
|
|
=back |
605
|
|
|
|
|
|
|
|
606
|
|
|
|
|
|
|
=item C<< my ($sql, @binds) = $builder->update($table, \%set|@set, \%where|\@where|$where); >> |
607
|
|
|
|
|
|
|
|
608
|
|
|
|
|
|
|
Generate a UPDATE query. |
609
|
|
|
|
|
|
|
|
610
|
|
|
|
|
|
|
my ($sql, @binds) = $builder->update('user', ['name' => 'john', email => 'john@example.com'], {user_id => 3}); |
611
|
|
|
|
|
|
|
# => |
612
|
|
|
|
|
|
|
# 'UPDATE `user` SET `name` = ?, `email` = ? WHERE (`user_id` = ?)' |
613
|
|
|
|
|
|
|
# ['john','john@example.com',3] |
614
|
|
|
|
|
|
|
|
615
|
|
|
|
|
|
|
=over 4 |
616
|
|
|
|
|
|
|
|
617
|
|
|
|
|
|
|
=item $table |
618
|
|
|
|
|
|
|
|
619
|
|
|
|
|
|
|
Table name in scalar. |
620
|
|
|
|
|
|
|
|
621
|
|
|
|
|
|
|
=item \%set |
622
|
|
|
|
|
|
|
|
623
|
|
|
|
|
|
|
Setting values. |
624
|
|
|
|
|
|
|
|
625
|
|
|
|
|
|
|
=item \%where |
626
|
|
|
|
|
|
|
|
627
|
|
|
|
|
|
|
=item \@where |
628
|
|
|
|
|
|
|
|
629
|
|
|
|
|
|
|
=item $where |
630
|
|
|
|
|
|
|
|
631
|
|
|
|
|
|
|
where clause from a hashref or arrayref via L<SQL::Maker::Condition>, or L<SQL::Maker::Condition> object, or L<SQL::QueryMaker> object. |
632
|
|
|
|
|
|
|
|
633
|
|
|
|
|
|
|
=back |
634
|
|
|
|
|
|
|
|
635
|
|
|
|
|
|
|
=item C<< $builder->new_condition() >> |
636
|
|
|
|
|
|
|
|
637
|
|
|
|
|
|
|
Create new L<SQL::Maker::Condition> object from C< $builder > settings. |
638
|
|
|
|
|
|
|
|
639
|
|
|
|
|
|
|
=item C<< my ($sql, @binds) = $builder->where(\%where) >> |
640
|
|
|
|
|
|
|
|
641
|
|
|
|
|
|
|
=item C<< my ($sql, @binds) = $builder->where(\@where) >> |
642
|
|
|
|
|
|
|
|
643
|
|
|
|
|
|
|
=item C<< my ($sql, @binds) = $builder->where(\@where) >> |
644
|
|
|
|
|
|
|
|
645
|
|
|
|
|
|
|
Where clause from a hashref or arrayref via L<SQL::Maker::Condition>, or L<SQL::Maker::Condition> object, or L<SQL::QueryMaker> object. |
646
|
|
|
|
|
|
|
|
647
|
|
|
|
|
|
|
=back |
648
|
|
|
|
|
|
|
|
649
|
|
|
|
|
|
|
=head1 PLUGINS |
650
|
|
|
|
|
|
|
|
651
|
|
|
|
|
|
|
SQL::Maker features a plugin system. Write the code as follows: |
652
|
|
|
|
|
|
|
|
653
|
|
|
|
|
|
|
package My::SQL::Maker; |
654
|
|
|
|
|
|
|
use parent qw/SQL::Maker/; |
655
|
|
|
|
|
|
|
__PACKAGE__->load_plugin('InsertMulti'); |
656
|
|
|
|
|
|
|
|
657
|
|
|
|
|
|
|
=head1 STRICT MODE |
658
|
|
|
|
|
|
|
|
659
|
|
|
|
|
|
|
See L<http://blog.kazuhooku.com/2014/07/the-json-sql-injection-vulnerability.html> for why |
660
|
|
|
|
|
|
|
do we need the strict mode in the first place. |
661
|
|
|
|
|
|
|
|
662
|
|
|
|
|
|
|
In strict mode, the following parameters must be blessed references implementing C<as_sql> and C<bind> methods |
663
|
|
|
|
|
|
|
if they are NOT simple scalars (i.e. if they are references of any kind). |
664
|
|
|
|
|
|
|
|
665
|
|
|
|
|
|
|
=over |
666
|
|
|
|
|
|
|
|
667
|
|
|
|
|
|
|
=item * |
668
|
|
|
|
|
|
|
|
669
|
|
|
|
|
|
|
Values in C<$where> parameter for C<select>, C<update>, C<delete> methods. |
670
|
|
|
|
|
|
|
|
671
|
|
|
|
|
|
|
=item * |
672
|
|
|
|
|
|
|
|
673
|
|
|
|
|
|
|
Values in C<%values> and C<%set> parameter for C<insert> and C<update> methods, respectively. |
674
|
|
|
|
|
|
|
|
675
|
|
|
|
|
|
|
=back |
676
|
|
|
|
|
|
|
|
677
|
|
|
|
|
|
|
You can use L<SQL::QueryMaker> objects for those parameters. |
678
|
|
|
|
|
|
|
|
679
|
|
|
|
|
|
|
Example: |
680
|
|
|
|
|
|
|
|
681
|
|
|
|
|
|
|
use SQL::QueryMaker qw(sql_in sql_raw); |
682
|
|
|
|
|
|
|
|
683
|
|
|
|
|
|
|
## NG: Use array-ref for values. |
684
|
|
|
|
|
|
|
$maker->select("user", ['*'], { name => ["John", "Tom"] }); |
685
|
|
|
|
|
|
|
|
686
|
|
|
|
|
|
|
## OK: Use SQL::QueryMaker |
687
|
|
|
|
|
|
|
$maker->select("user", ['*'], { name => sql_in(["John", "Tom"]) }); |
688
|
|
|
|
|
|
|
|
689
|
|
|
|
|
|
|
## Also OK: $where parameter itself is a blessed object. |
690
|
|
|
|
|
|
|
$maker->select("user", ['*'], $maker->new_condition->add(name => sql_in(["John", "Tom"]))); |
691
|
|
|
|
|
|
|
$maker->select("user", ['*'], sql_in(name => ["John", "Tom"])); |
692
|
|
|
|
|
|
|
|
693
|
|
|
|
|
|
|
|
694
|
|
|
|
|
|
|
## NG: Use scalar-ref for a raw value. |
695
|
|
|
|
|
|
|
$maker->insert(user => [ name => "John", created_on => \"datetime(now)" ]); |
696
|
|
|
|
|
|
|
|
697
|
|
|
|
|
|
|
## OK: Use SQL::QueryMaker |
698
|
|
|
|
|
|
|
$maker->insert(user => [name => "John", created_on => sql_raw("datetime(now)")]); |
699
|
|
|
|
|
|
|
|
700
|
|
|
|
|
|
|
|
701
|
|
|
|
|
|
|
=head1 FAQ |
702
|
|
|
|
|
|
|
|
703
|
|
|
|
|
|
|
=over 4 |
704
|
|
|
|
|
|
|
|
705
|
|
|
|
|
|
|
=item Why don't you use SQL::Abstract? |
706
|
|
|
|
|
|
|
|
707
|
|
|
|
|
|
|
I need a more extensible one. |
708
|
|
|
|
|
|
|
|
709
|
|
|
|
|
|
|
So, this module contains L<SQL::Maker::Select>, the extensible B<SELECT> clause object. |
710
|
|
|
|
|
|
|
|
711
|
|
|
|
|
|
|
=back |
712
|
|
|
|
|
|
|
|
713
|
|
|
|
|
|
|
=head1 AUTHOR |
714
|
|
|
|
|
|
|
|
715
|
|
|
|
|
|
|
Tokuhiro Matsuno E<lt>tokuhirom AAJKLFJEF@ GMAIL COME<gt> |
716
|
|
|
|
|
|
|
|
717
|
|
|
|
|
|
|
=head1 SEE ALSO |
718
|
|
|
|
|
|
|
|
719
|
|
|
|
|
|
|
L<SQL::Abstract> |
720
|
|
|
|
|
|
|
L<SQL::QueryMaker> |
721
|
|
|
|
|
|
|
|
722
|
|
|
|
|
|
|
The whole code was taken from L<DBIx::Skinny> by nekokak++. |
723
|
|
|
|
|
|
|
|
724
|
|
|
|
|
|
|
=head1 LICENSE |
725
|
|
|
|
|
|
|
|
726
|
|
|
|
|
|
|
Copyright (C) Tokuhiro Matsuno |
727
|
|
|
|
|
|
|
|
728
|
|
|
|
|
|
|
This library is free software; you can redistribute it and/or modify |
729
|
|
|
|
|
|
|
it under the same terms as Perl itself. |
730
|
|
|
|
|
|
|
|
731
|
|
|
|
|
|
|
=cut |