SQL::Abstract::Query - An advanced SQL generator.
use SQL::Abstract::Query; # Create a new query object by specifying the SQL dialect: my $query = SQL::Abstract::Query->new( $dbh ); my $query = SQL::Abstract::Query->new( 'mysql' ); my $query = SQL::Abstract::Query->new( $dialect ); my $query = SQL::Abstract::Query->new(); # Use the flexible OO interface: my $statement = $query->insert( $table, \@fields ); my $statement = $query->update( $table, \@fields, \%where ); my $statement = $query->select( \@fields, \@from, \%where, \%attributes ); my $statement = $query->delete( $table, \%where ); my $sth = $dbh->prepare( $statement->sql() ); $sth->execute( $statement->values( \%field_values ) ); # Use the simpler procedural interface: my ($sql, @bind_values) = $query->insert( $table, \%field_values ); my ($sql, @bind_values) = $query->update( $table, \%field_values, \%where ); my ($sql, @bind_values) = $query->select( \@fields, \@from, \%where, \%attributes ); my ($sql, @bind_values) = $query->delete( $table, \%where ); my $sth = $dbh->prepare( $sql ); $sth->execute( @bind_values );
This library provides the ability to generate SQL using database-independent Perl data structures, is built upon the proven capabilities of SQL::Abstract, and robust and extendable thanks to Moose.
Much of the inspiration for this library came from such modules as SQL::Abstract::Limit, SQL::Maker, and SQL::Abstract::More.
Queries are constructed as objects which can be re-used.
Supports explicit JOINs.
GROUP BY is supported.
LIMIT/OFFSET is supported (with cross-database compatibility) and uses placeholders.
Easy to extend with Moose subclassing, traits, and roles.
The API has been designed in such a way that extending the functionality in the future should be less likely to break backwards compatibility.
Re-using a statement via prepare/execute is trivial and can be done with for all statement types (even UPDATE ... WHERE ...).
# Auto-detect the appropriate dialect from a DBI handle: my $query = SQL::Abstract::Query->new( $dbh ); # Explicitly set the dialect that you want: my $query = SQL::Abstract::Query->new( 'oracle' ); # Pass a pre-created SQL::Dialect object: my $query = SQL::Abstract::Query->new( $dialect ); # The "standard" dialect is the default: my $query = SQL::Abstract::Query->new();
Each implementation, or dialect, of SQL has quirks that slightly (or in some cases drastically) change the way that the SQL must be written to get a particular task done. In order for this module to know which particular set of quirks a dialect must be declared. See the documentation for SQL::Dialect to see how this works.
The dialect can be set via a string (the dialect name), a $dbh (the dialect is auto-detected), or you can pass a SQL::Dialect object that you created yourself.
The dialect is optional. If none is specified then a simple, default, dialect will be used.
The underlying SQL::Abstract object that will be used to generate much of the SQL for this module. There really isn't much need for you to set this attribute yourself unless you are doing something really crazy.
New statement objects should be created using these methods. Each statement class has their own documentation that explains the various arguments that they accept, which ones are optional, and the intricacies of how they work.
All the satement classes apply the SQL::Abstract::Query::Statement role. Look there to see all the shared features which all statement objects posses.
my $insert = $query->insert( $table, \@fields ); my ($sql, @bind_values) = $query->insert( $table, \%field_values );
See SQL::Abstract::Query::Insert.
my $update = $query->update( $table, \@fields, \%where ); my ($sql, @bind_values) = $query->update( $table, \%field_values, \%where );
See SQL::Abstract::Query::Update.
my $select = $query->select( \@fields, \@from, \%where, \%arguments ); my ($sql, @bind_values) = $query->select( \@fields, \@from, \%where, \%arguments );
See SQL::Abstract::Query::Select.
my $delete = $query->delete( $table, \%where ); my ($sql, @bind_values) = $query->delete( $table, \%where );
See SQL::Abstract::Query::Delete.
In this module placeholders have an additional use. In order to re-use queries placeholder values must be named so that the "values" in SQL::Abstract::Query::Statement method may work correctly. In SQL::Abstract you must generate a new UPDATE statement for every update you want to execute. In this module this is much simplified by using named placeholders:
my $update = $query->update('customer', ['email', 'active'], {customer_id => 'id'}); my $sth = $dbh->prepare( $update->sql() ); $sth->execute( $update->values({ email=>'b@e.com', active=>1, id=>1 }) ); $sth->execute( $update->values({ email=>'j@e.com', active=>1, id=>2 }) ); $sth->execute( $update->values({ email=>'n@e.com', active=>1, id=>3 }) );
All statement types support named placeholders for use with values(). In any spot of a statement where you would normally pass a value you can instead pass a name, then when you call values() you use that name as the key.
Here's an example that uses this feature to provide contextual information in the placeholder names:
my $select = $query->select( 'user_id', 'users', { posts => {-between => ['min_posts', 'max_posts']}, role => 'moderator', }, ); my $sth = $dbh->prepare( $select->sql() ); my $one_star_moderators = $sth->fetchcol_arrayhref( $select->values({ min_posts => 0, max_posts => 10, }) ); my $two_star_moderators = $sth->fetchrow_hashref( $select->values({ min_posts => 11, max_posts => 50, }) );
Named placeholders can be mixed with non-named placeholders, as in the above example with the role=>'moderator' check.
There are quite a few SQL generators out there, including:
SQL::Abstract
SQL::Maker
SQL::Generator
SQL::OOP
SQL::Entity
By far the most popular and battle tested is SQL::Abstract. This module takes the great things about SQL::Abstract and makes them better. Others have tried to do this, but with limited success, so this module aims to do it right.
This module is currently in a working draft state. I am confident that the current implementation is complete, well thought-out, and well tested. But, I still need to receive some input from the perl community before I can say the API is 100% stable. Until then it is possible that changes will be made that break backwards compatibility.
If you use this module then please contact the author describing your experience and any thoughts you may have.
If this statement concerns you then you should also send the author an e-mail asking about the API stability. It may very well be that the API can now be considered stable but a release of this library has not yet been made that states as much.
This module aims to be compatible with the core SQL::Abstract API as much as possible, but not at the expense of degrading quality. There are parts of the SQL::Abstract API that are difficult to extend, others that are sub-optimal but cannot be changed due to backwards compatibility requirements, and still others that just don't make sense due to the drastic design difference of this module. These aspects of SQL::Abstract will not be reproduced in this module.
Here is a list of the current differences between this module's API and SQL generation and what SQL::Abstract does:
The select() method takes the fields as the first argument rather than the second argument. This better matches how SQL is written and is more natural.
All identifiers are quoted by default since not doing so will cause SQL that has identifiers which look like reserved words to fail.
The fourth argument to select() is not $order as it is in SQL::Abstract, instead it is a hash of SQL::Abstract::Query::Select attributes where one of the attributes may be order_by.
SQL::Abstract is not being used to generate the ORDER BY clause. This is partly due to other clauses needing to gain access to the SQL before the ORDER BY is appended to it, and also because SQL::Abstract's implementation of ORDER BY is a bit convoluted.
Many of SQL::Abstract's methods and attributes are not reproduced. Some of these may be made available at a later date, but likely not unless someone has a use-case for needing them.
SQL::Abstract supports *very* complex arguments, which is great, but some of them seem to be supported because they can be, rather than because someone actually needs it. For example, the ability to provide a reference of an array reference as the source for a select. Also, by not supporting all the multitudes of variations of arguments this module has much more room to grow and take advantage of these available argument formats for different purposes.
The insert() method does not accept field values as an array reference. This is by design - SQL that depends on the order of the columns in the database is brittle and will eventually break. Also, due to the need for the statement objects to be re-useable the array ref form of fields has been re-purposed. That being said, perhaps there is a use case where this would be useful. If so, thunk the author on the head and let him know.
The insert() method does not yet accept a returning option. This may change if a flexible implementation is developed.
If there is something in SQL::Abstract that you think this module should support then please let the author know.
Guidelines for extending the functionality of this module using plugins, or otherwise, have not yet been developed as the internal workings of this module are still in flux. There are several entries in the TODO section that reflect this.
For now, just shoot the author an e-mail.
If you'd like to contribute bug fixes, enhancements, additional test covergage, or documentation to this module then by all means do so. You can fork this repository using github and then send the author a pull request.
Please contact the author if you are considering doing this and discuss your ideas.
Currently there is no particular mailing list or IRC channel for this project. You can shoot the author an e-mail if you have a question.
If you'd like to report an issue you can use github's issue tracker.
The fields() argument of the select statement should support the ability to assign aliases to columns. Currently the only way to do this with SQL::Abstract is by providing a verbatim string where the fields sql must be manually built.
Document all the various ways this module can be used, possibly as a cookbook. Show how sub-queries can be used, for example.
Create a unit test that compares the output of this module compared to SQL::Abstract, proving that this module is at least as capable.
Support more dialects of SQL (and thus more DBD drivers). Help from generous volunteers encouraged and appreciated!
Support the ability to extend the SQL generation logic by hooking in to various stages of the SQL generation and alter the behavior. This would, for example, allow the LIMIT logic to be moved out of SQL::Abstract::Query::Select and in to SQL::Abstract::Query::Select::Limit. This would also allow other people to write their own modules that modify SQL generation without having to write brittle hacks.
In addition to the above it would be nice if just a portion of a SQL query could be generated, such as just the GROUP BY clause, etc. This would be similar to SQL::Abstracts's where() method but the API would likely be very different.
Allow for more join types. Currently only JOIN and LEFT JOIN work. This should be trivial to add.
Support UPDATE ... SELECT.
Possibly support more SQL commands such as TRUNCATE, ALTER, CREATE, DROP, etc.
Aran Clary Deltac <bluefeet@gmail.com>
Terrence Brannon
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.
To install SQL::Abstract::Query, copy and paste the appropriate command in to your terminal.
cpanm
cpanm SQL::Abstract::Query
CPAN shell
perl -MCPAN -e shell install SQL::Abstract::Query
For more information on module installation, please visit the detailed CPAN module installation guide.