# 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](https://metacpan.org/pod/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](https://metacpan.org/pod/SQL::Maker::Select) using the settings from __$builder__. This method returns an instance of [SQL::Maker::Select](https://metacpan.org/pod/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](https://metacpan.org/pod/SQL::Maker::Condition), or [SQL::Maker::Condition](https://metacpan.org/pod/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](https://metacpan.org/pod/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](https://metacpan.org/pod/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](https://metacpan.org/pod/SQL::Maker::Condition), or [SQL::Maker::Condition](https://metacpan.org/pod/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](https://metacpan.org/pod/SQL::Maker::Condition), or [SQL::Maker::Condition](https://metacpan.org/pod/SQL::Maker::Condition) object. - `$builder->new_condition()` Create new [SQL::Maker::Condition](https://metacpan.org/pod/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](https://metacpan.org/pod/SQL::Maker::Condition), or [SQL::Maker::Condition](https://metacpan.org/pod/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](https://metacpan.org/pod/SQL::Maker::Select), the extensible __SELECT__ clause object. # AUTHOR Tokuhiro Matsuno # SEE ALSO [SQL::Abstract](https://metacpan.org/pod/SQL::Abstract) The whole code was taken from [DBIx::Skinny](https://metacpan.org/pod/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.