NAME

SQL::Composer - sql builder

SYNOPSIS

    use DBI;

    my $select = SQL::Composer->build('select',
        from    => 'book_description',
        columns => ['description'],
        join    => [
            {
                source  => 'book',
                columns => ['title'],
                on      => ['book_description.book_id' => {-col => 'book.id'}],
                join    => [
                    {
                        source  => 'author',
                        columns => ['name'],
                        on      => ['book.author_id' => {-col => 'author.id'}]
                    }
                ]
            }
        ]
    );

    my $sql  = $select->to_sql;
    my @bind = $select->to_bind;

    my $dbh = DBI->connect(...);
    my $sth = dbh->prepare($sql);
    $sth->execute(@bind);
    my $rows = $sth->fetchall_arrayref;

    my $objects = $select->from_rows($rows);

    # $objects = [
    #   description => 'Nice Book',
    #   book => {
    #       title => 'My Book',
    #       author => {
    #           name => 'Author'
    #       }
    #   }
    # ]

DESCRIPTION

SQL::Composer is a SQL builder and rows parser in one module. It allows deep joins and automatic convertion from arrayref to a hashref, keeping the nested join structure if needed.

This module itself is just a factory for the common SQL statements: SELECT, DELETE, INSERT and UPDATE.

METHODS

build

Build SQL statement.

    my $select = SQL::Composer->build('select, @params);

FUNCTIONS

Sometimes it is easier to work with functions, using :funcs tags you will get sql_select, sql_insert, sql_update, sql_upsert and sql_delete functions which are equivalents of using build method.

    my $sql = sql_select from => 'authors', where => [name => 'vti'];

SQL

SQL expressions

SQL expressions are everything used in where, join and other statements. So the following rules apply to all of them. For more details see SQL::Composer::Expression.

    my $expr = SQL::Composer::Expression->new(expr => [a => 'b']);

    my $sql = $expr->to_sql;   # `a` = ?
    my @bind = $expr->to_bind; # ('b')

SQL Joins

For more details see SQL::Composer::Join.

    my $expr = SQL::Composer::Join->new(source => 'table', on => [a => 'b']);

    my $sql = $expr->to_sql;   # JOIN `table` ON `table`.`a` = ?
    my @bind = $expr->to_bind; # ('b')

SQL Inserts

For more details see SQL::Composer::Insert.

    my $expr =
      SQL::Composer::Insert->new(into => 'table', values => [foo => 'bar']);

    my $sql = $expr->to_sql;   # INSERT INTO `table` (`foo`) VALUES (?)
    my @bind = $expr->to_bind; # ('bar')

SQL Updates

For more details see SQL::Composer::Update.

    my $expr =
      SQL::Composer::Update->new(table => 'table', values => [a => 'b']);

    my $sql = $expr->to_sql;   # UPDATE `table` SET `a` = ?
    my @bind = $expr->to_bind; # ('b')

SQL Upserts

For more details see SQL::Composer::Upsert.

    my $expr =
      SQL::Composer::Upsert->new(into => 'table', values => [a => 'b'], driver => 'SQLite');

    my $sql = $expr->to_sql;   # INSERT OR REPLACE INTO `table` (`a`) VALUES (?)
    my @bind = $expr->to_bind; # ('b')

SQL Deletes

For more details see SQL::Composer::Delete.

    my $expr = SQL::Composer::Delete->new(from => 'table');

    my $sql = $expr->to_sql;   # DELETE FROM `table`
    my @bind = $expr->to_bind; # ()

SQL Selects

For more details see SQL::Composer::Select.

    my $expr =
      SQL::Composer::Select->new(from => 'table', columns => ['a', 'b']);

    my $sql = $expr->to_sql;   # SELECT `table`.`a`,`table`.`b` FROM `table`
    my @bind = $expr->to_bind; # ()

    my $objects = $expr->from_rows([['c', 'd']]); # [{a => 'c', b => 'd'}];

CREDITS

Stevan Little

SEE ALSO

SQL::Abstract

AUTHOR

Viacheslav Tykhanovskyi

COPYRIGHT AND LICENSE

Copyright 2013, Viacheslav Tykhanovskyi.

This module is free software, you may distribute it under the same terms as Perl.

cpanm SQL::Composer

perl -MCPAN -e shell
install SQL::Composer