Aran Clary Deltac > SQL-Abstract-Query-0.03 > SQL::Abstract::Query

Download:
SQL-Abstract-Query-0.03.tar.gz

Dependencies

Annotate this POD

View/Report Bugs
Module Version: 0.03   Source  

NAME ^

SQL::Abstract::Query - An advanced SQL generator.

SYNOPSIS ^

    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 );

DESCRIPTION ^

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.

CONSTRUCTOR ^

    # 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();

ARGUMENTS ^

dialect

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.

ATTRIBUTES ^

abstract

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.

STATEMENT METHODS ^

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.

insert

    my $insert = $query->insert( $table, \@fields );
    
    my ($sql, @bind_values) = $query->insert( $table, \%field_values );

See SQL::Abstract::Query::Insert.

update

    my $update = $query->update( $table, \@fields, \%where );
    
    my ($sql, @bind_values) = $query->update( $table, \%field_values, \%where );

See SQL::Abstract::Query::Update.

select

    my $select = $query->select( \@fields, \@from, \%where, \%arguments );
    
    my ($sql, @bind_values) = $query->select( \@fields, \@from, \%where, \%arguments );

See SQL::Abstract::Query::Select.

delete

    my $delete = $query->delete( $table, \%where );
    
    my ($sql, @bind_values) = $query->delete( $table, \%where );

See SQL::Abstract::Query::Delete.

NAMED PLACEHOLDERS ^

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.

APPENDIX ^

Why Yet Another SQL Generator?

There are quite a few SQL generators out there, including:

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.

API Stability

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.

Compatibility

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:

If there is something in SQL::Abstract that you think this module should support then please let the author know.

EXTENDING ^

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.

CONTRIBUTING ^

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.

SUPPORT ^

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.

TODO ^

AUTHOR ^

Aran Clary Deltac <bluefeet@gmail.com>

CONTRIBUTORS ^

LICENSE ^

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.

syntax highlighting: