NAME

SQL::Maker - Yet another SQL builder

SYNOPSIS

    use SQL::Maker;

    my $builder = SQL::Maker->new(
        driver => 'SQLite', # or your favorite driver
    );

    # SELECT
    ($sql, @binds) = $builder->select($table, \@fields, \%where, \%opt);

    # INSERT
    ($sql, @binds) = $builder->insert($table, \%values, \%opt);

    # DELETE
    ($sql, @binds) = $builder->delete($table, \%where, \%opt);

    # UPDATE
    ($sql, @binds) = $builder->update($table, \%set, \%where);
    ($sql, @binds) = $builder->update($table, \@set, \%where);

DESCRIPTION

SQL::Maker is yet another SQL builder class. It is based on DBIx::Skinny's SQL generator.

METHODS

my $builder = SQL::Maker->new(%args);

Create new instance of SQL::Maker.

Attributes are the following:

driver: Str

Driver name is required. The driver type is needed to create SQL string.

quote_char: Str

This is the character that a table or column name will be quoted with.

Default: auto detect from $driver.

name_sep: Str

This is the character that separates a table and column name.

Default: '.'

new_line: Str

This is the character that separates a part of statements.

Default: '\n'

my $select = $builder->new_select(%args|\%args);

Create new instance of SQL::Maker::Select using the settings from $builder.

This method returns an instance of SQL::Maker::Select.

my ($sql, @binds) = $builder->select($table|\@tables, \@fields, \%where|\@where|$where, \%opt);

    my ($sql, @binds) = $builder->select('user', ['*'], {name => 'john'}, {order_by => 'user_id DESC'});
    # =>
    #   SELECT * FROM `user` WHERE (`name` = ?) ORDER BY user_id DESC
    #   ['john']

This method returns the SQL string and bind variables for a SELECT statement.

$table

\@tables

Table name for the FROM clause as scalar or arrayref. You can specify the instance of SQL::Maker::Select for a sub-query.

If you are using $opt->{joins} this should be undef since it's passed via the first join.

\@fields

This is a list for retrieving fields from database.

Each element of the @fields is normally a scalar or a scalar ref containing the column name. If you want to specify an alias of the field, you can use an arrayref containing a pair of column and alias names (e.g. ['foo.id' => 'foo_id']).

\%where

\@where

$where

where clause from hashref or arrayref via SQL::Maker::Condition, or SQL::Maker::Condition object.

\%opt

These are the options for the SELECT statement

$opt->{prefix}

This is a prefix for the SELECT statement.

For example, you can provide the 'SELECT SQL_CALC_FOUND_ROWS '. It's useful for MySQL.

Default Value: 'SELECT '

$opt->{limit}

This option adds a 'LIMIT $n' clause.

$opt->{offset}

This option adds an 'OFFSET $n' clause.

$opt->{order_by}

This option adds an ORDER BY clause

You can write it in any of the following forms:

    $builder->select(..., {order_by => 'foo DESC, bar ASC'});
    $builder->select(..., {order_by => ['foo DESC', 'bar ASC']});
    $builder->select(..., {order_by => {foo => 'DESC'}});
    $builder->select(..., {order_by => [{foo => 'DESC'}, {bar => 'ASC'}]});

$opt->{group_by}

This option adds a GROUP BY clause

You can write it in any of the following forms:

    $builder->select(..., {group_by => 'foo DESC, bar ASC'});
    $builder->select(..., {group_by => ['foo DESC', 'bar ASC']});
    $builder->select(..., {group_by => {foo => 'DESC'}});
    $builder->select(..., {group_by => [{foo => 'DESC'}, {bar => 'ASC'}]});

$opt->{having}

This option adds a HAVING clause

$opt->{for_update}

This option adds a 'FOR UPDATE" clause.

$opt->{joins}

This option adds a 'JOIN' via SQL::Maker::Select.

You can write it as follows:

    $builder->select(undef, ..., {joins => [[user => {table => 'group', condition => 'user.gid = group.gid'}], ...]});

$opt->{index_hint}

This option adds an INDEX HINT like as 'USE INDEX' clause for MySQL via SQL::Maker::Select.

You can write it as follows:

    $builder->select(..., { index_hint => 'foo' });
    $builder->select(..., { index_hint => ['foo', 'bar'] });
    $builder->select(..., { index_hint => { list => 'foo' });
    $builder->select(..., { index_hint => { type => 'FORCE', list => ['foo', 'bar'] });

my ($sql, @binds) = $builder->insert($table, \%values|\@values, \%opt);

    my ($sql, @binds) = $builder->insert(user => {name => 'john'});
    # =>
    #    INSERT INTO `user` (`name`) VALUES (?)
    #    ['john']

Generate an INSERT query.

$table

Table name in scalar.

\%values

These are the values for the INSERT statement.

\%opt

These are the options for the INSERT statement

$opt->{prefix}

This is a prefix for the INSERT statement.

For example, you can provide 'INSERT IGNORE INTO' for MySQL.

Default Value: 'INSERT INTO'

my ($sql, @binds) = $builder->delete($table, \%where|\@where|$where, \%opt);

    my ($sql, @binds) = $builder->delete($table, \%where);
    # =>
    #    DELETE FROM `user` WHERE (`name` = ?)
    #    ['john']

Generate a DELETE query.

$table

Table name in scalar.

\%where

\@where

$where

where clause from hashref or arrayref via SQL::Maker::Condition, or SQL::Maker::Condition object.

\%opt

These are the options for the DELETE statement

$opt->{using}

This option adds a USING clause. It takes a scalar or an arrayref of table names as argument:

    my ($sql, $binds) = $bulder->delete($table, \%where, { using => 'group' });
    # =>
    #    DELETE FROM `user` USING `group` WHERE (`group`.`name` = ?)
    #    ['doe']
    $bulder->delete(..., { using => ['bar', 'qux'] });

my ($sql, @binds) = $builder->update($table, \%set|@set, \%where|\@where|$where);

Generate a UPDATE query.

    my ($sql, @binds) = $builder->update('user', ['name' => 'john', email => 'john@example.com'], {user_id => 3});
    # =>
    #    'UPDATE `user` SET `name` = ?, `email` = ? WHERE (`user_id` = ?)'
    #    ['john','john@example.com',3]

$table

Table name in scalar.

\%set

Setting values.

\%where

\@where

$where

where clause from a hashref or arrayref via SQL::Maker::Condition, or SQL::Maker::Condition object.

$builder->new_condition()

Create new SQL::Maker::Condition object from $builder settings.

my ($sql, @binds) = $builder->where(\%where)

my ($sql, @binds) = $builder->where(\@where)

my ($sql, @binds) = $builder->where(\@where)

Where clause from a hashref or arrayref via SQL::Maker::Condition, or SQL::Maker::Condition object.

PLUGINS

SQL::Maker features a plugin system. Write the code as follows:

    package My::SQL::Maker;
    use parent qw/SQL::Maker/;
    __PACKAGE__->load_plugin('InsertMulti');

FAQ

Why don't you use SQL::Abstract?

I need a more extensible one.

So, this module contains SQL::Maker::Select, the extensible SELECT clause object.

AUTHOR

Tokuhiro Matsuno <tokuhirom AAJKLFJEF@ GMAIL COM>

SEE ALSO

SQL::Abstract

The whole code was taken from DBIx::Skinny by nekokak++.

LICENSE

Copyright (C) Tokuhiro Matsuno

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