1 package DBIx::Class::SQLMaker;
8 DBIx::Class::SQLMaker - An SQL::Abstract-based SQL maker class
12 This module is a subclass of L<SQL::Abstract> and includes a number of
13 DBIC-specific workarounds, not yet suitable for inclusion into the
14 L<SQL::Abstract> core. It also provides all (and more than) the functionality
15 of L<SQL::Abstract::Limit>, see L<DBIx::Class::SQLMaker::LimitDialects> for
18 Currently the enhancements to L<SQL::Abstract> are:
22 =item * Support for C<JOIN> statements (via extended C<table/from> support)
24 =item * Support of functions in C<SELECT> lists
26 =item * C<GROUP BY>/C<HAVING> support (via extensions to the order_by parameter)
28 =item * Support of C<...FOR UPDATE> type of select statement modifiers
30 =item * The L</-ident> operator
32 =item * The L</-value> operator
40 DBIx::Class::SQLMaker::LimitDialects
44 use Module::Runtime qw(use_module);
45 use Sub::Name 'subname';
46 use DBIx::Class::Carp;
47 use DBIx::Class::Exception;
51 has limit_dialect => (
52 is => 'rw', default => sub { 'LimitOffset' },
53 trigger => sub { shift->clear_renderer_class }
56 our %LIMIT_DIALECT_MAP = (
57 'GenericSubQ' => 'GenericSubquery',
58 'RowCountOrGenericSubQ' => 'RowCountOrGenericSubquery',
61 sub mapped_limit_dialect {
63 my $unmapped = $self->limit_dialect;
64 $LIMIT_DIALECT_MAP{$unmapped}||$unmapped;
67 around _build_renderer_roles => sub {
68 my ($orig, $self) = (shift, shift);
71 'Data::Query::Renderer::SQL::Slice::'.$self->mapped_limit_dialect
75 # for when I need a normalized l/r pair
78 { defined $_ ? $_ : '' }
79 ( ref $_[0]->{quote_char} ? (@{$_[0]->{quote_char}}) : ( ($_[0]->{quote_char}) x 2 ) )
83 sub _build_converter_class {
84 Module::Runtime::use_module('DBIx::Class::SQLMaker::Converter')
87 # FIXME when we bring in the storage weaklink, check its schema
88 # weaklink and channel through $schema->throw_exception
89 sub throw_exception { DBIx::Class::Exception->throw($_[1]) }
92 # reinstall the belch()/puke() functions of SQL::Abstract with custom versions
93 # that use DBIx::Class::Carp/DBIx::Class::Exception instead of plain Carp
94 no warnings qw/redefine/;
96 *SQL::Abstract::belch = subname 'SQL::Abstract::belch' => sub (@) {
97 my($func) = (caller(1))[3];
98 carp "[$func] Warning: ", @_;
101 *SQL::Abstract::puke = subname 'SQL::Abstract::puke' => sub (@) {
102 my($func) = (caller(1))[3];
103 __PACKAGE__->throw_exception("[$func] Fatal: " . join ('', @_));
106 # Current SQLA pollutes its namespace - clean for the time being
107 namespace::clean->clean_subroutines(qw/SQL::Abstract carp croak confess/);
110 # the "oh noes offset/top without limit" constant
111 # limited to 31 bits for sanity (and consistency,
112 # since it may be handed to the like of sprintf %u)
114 # Also *some* builds of SQLite fail the test
115 # some_column BETWEEN ? AND ?: 1, 4294967295
116 # with the proper integer bind attrs
118 # Implemented as a method, since ::Storage::DBI also
119 # refers to it (i.e. for the case of software_limit or
120 # as the value to abuse with MSSQL ordered subqueries)
121 sub __max_int () { 0x7FFFFFFF };
123 # poor man's de-qualifier
125 $_[0]->next::method( ( $_[0]{_dequalify_idents} and ! ref $_[1] )
126 ? $_[1] =~ / ([^\.]+) $ /x
132 carp_unique ("-nest in search conditions is deprecated, you most probably wanted:\n"
133 .q|{..., -and => [ \%cond0, \@cond1, \'cond2', \[ 'cond3', [ col => bind ] ], etc. ], ... }|
136 shift->next::method(@_);
139 # Handle limit-dialect selection
141 my ($self, $table, $fields, $where, $rs_attrs, $limit, $offset) = @_;
143 if (defined $offset) {
144 $self->throw_exception('A supplied offset must be a non-negative integer')
145 if ( $offset =~ /\D/ or $offset < 0 );
149 if (defined $limit) {
150 $self->throw_exception('A supplied limit must be a positive integer')
151 if ( $limit =~ /\D/ or $limit <= 0 );
154 $limit = $self->__max_int;
157 my %final_attrs = (%{$rs_attrs||{}}, limit => $limit, offset => $offset);
159 if ($limit or $offset) {
160 my %slice_stability = $self->renderer->slice_stability;
162 if (my $stability = $slice_stability{$offset ? 'offset' : 'limit'}) {
163 my $source = $rs_attrs->{_rsroot_rsrc};
165 $final_attrs{order_is_stable}
166 = $final_attrs{preserve_order}
167 = $source->schema->storage
168 ->_order_by_is_stable(
169 @final_attrs{qw(from order_by where)}
172 if ($stability eq 'requires') {
173 if ($self->converter->_order_by_to_dq($final_attrs{order_by})) {
174 $self->throw_exception(
175 $self->limit_dialect.' limit/offset implementation requires a stable order for offset'
178 if (my $ident_cols = $source->_identifying_column_set) {
179 $final_attrs{order_by} = [
180 map "$final_attrs{alias}.$_", @$ident_cols
182 $final_attrs{order_is_stable} = 1;
184 $self->throw_exception(sprintf(
185 'Unable to auto-construct stable order criteria for "skimming type"
187 . "dialect based on source '%s'", $source->name) );
194 my %slice_subquery = $self->renderer->slice_subquery;
196 if (my $subquery = $slice_subquery{$offset ? 'offset' : 'limit'}) {
198 my $f = $fields->[$_];
200 $f = { '' => $f } unless ref($f) eq 'HASH';
201 ($f->{-as} ||= $final_attrs{as}[$_]) =~ s/\Q${\$self->name_sep}/__/g;
202 } elsif ($f !~ /^\Q$final_attrs{alias}${\$self->name_sep}/) {
204 ($f->{-as} ||= $final_attrs{as}[$_]) =~ s/\Q${\$self->name_sep}/__/g;
211 my ($sql, @bind) = $self->next::method ($table, $fields, $where, $final_attrs{order_by}, \%final_attrs );
213 $sql .= $self->_lock_select ($rs_attrs->{for})
216 return wantarray ? ($sql, @bind) : $sql;
219 sub _assemble_binds {
221 return map { @{ (delete $self->{"${_}_bind"}) || [] } } (qw/pre_select select from where group having order limit/);
225 update => 'FOR UPDATE',
226 shared => 'FOR SHARE',
229 my ($self, $type) = @_;
232 if (ref($type) eq 'SCALAR') {
236 $sql = $for_syntax->{$type} || $self->throw_exception( "Unknown SELECT .. FO
237 R type '$type' requested" );
244 scalar shift->_render_sqla(table => \@_);
253 Used to explicitly specify an SQL identifier. Takes a plain string as value
254 which is then invariably treated as a column name (and is being properly
255 quoted if quoting has been requested). Most useful for comparison of two
259 priority => { '<', 2 },
260 requestor => { -ident => 'submitter' }
265 $stmt = 'WHERE "priority" < ? AND "requestor" = "submitter"';
270 The -value operator signals that the argument to the right is a raw bind value.
271 It will be passed straight to DBI, without invoking any of the SQL::Abstract
272 condition-parsing logic. This allows you to, for example, pass an array as a
273 column value for databases that support array datatypes, e.g.:
276 array => { -value => [1, 2, 3] }
281 $stmt = 'WHERE array = ?';
286 See L<DBIx::Class/CONTRIBUTORS>.
290 You may distribute this code under the same terms as Perl itself.