NAME
GX::SQL::Builder - Base class for SQL builders
SYNOPSIS
None.
DESCRIPTION
This module provides the GX::SQL::Builder class which extends the GX::Class::Object class.
METHODS
Constructor
new
Returns a new SQL builder instance.
$builder = $builder_class->new( %attributes );
- Attributes:
-
quote_char
( string )Defaults to a double quote.
- Returns:
-
$builder
( GX::SQL::Builder object )
- Exceptions:
Public Methods
count
Creates a SELECT COUNT(*)
statement.
( $sql, $bind ) = $builder->count( %arguments );
- Arguments:
-
bind
(HASH
reference )table
( string ) [ required ]where
(ARRAY
reference )
- Returns:
-
$sql
( string )$bind
(ARRAY
reference )
- Exceptions:
In scalar context, only the generated SQL is returned.
delete
Creates a DELETE
statement.
( $sql, $bind ) = $builder->delete( %arguments );
- Arguments:
-
bind
(HASH
reference )table
( string ) [ required ]where
(ARRAY
reference )
- Returns:
-
$sql
( string )$bind
(ARRAY
reference )
- Exceptions:
In scalar context, only the generated SQL is returned.
insert
Creates an INSERT
statement.
( $sql, $bind ) = $builder->insert( %arguments );
- Arguments:
-
bind
(HASH
reference )columns
(ARRAY
reference ) [ required ]table
( string ) [ required ]values
(ARRAY
reference )
- Returns:
-
$sql
( string )$bind
(ARRAY
reference )
- Exceptions:
In scalar context, only the generated SQL is returned.
select
Creates a SELECT
statement.
( $sql, $bind ) = $builder->select( %arguments );
- Arguments:
-
bind
(HASH
reference )columns
(ARRAY
reference )distinct
( bool )Adds the "DISTINCT" keyword to the query if set to true.
limit
( integer )offset
( integer )order
( string |SCALAR
reference |ARRAY
reference )Examples:
order => 'column_1' order => [ [ 'column_1', 'DESC' ] ] order => [ 'column_1', [ 'column_2', 'DESC' ] ] order => \$sql
table
( string ) [ required ]where
(ARRAY
reference )
- Returns:
-
$sql
( string )$bind
(ARRAY
reference )
- Exceptions:
In scalar context, only the generated SQL is returned.
update
Creates an UPDATE
statement.
( $sql, $bind ) = $builder->update( %arguments );
- Arguments:
-
bind
(HASH
reference )columns
(ARRAY
reference ) [ required ]table
( string ) [ required ]values
(ARRAY
reference )where
(ARRAY
reference )
- Returns:
-
$sql
( string )$bind
(ARRAY
reference )
- Exceptions:
In scalar context, only the generated SQL is returned.
where
Creates a WHERE
clause.
( $sql, $bind ) = $builder->where( $where );
( $sql, $bind ) = $builder->where( $where, bind => $bind );
- Arguments:
-
$bind
(HASH
reference ) [ optional ]$where
(ARRAY
reference ) [ required ]
- Returns:
-
$sql
( string )$bind
(ARRAY
reference )
- Exceptions:
In scalar context, only the generated SQL is returned.
USAGE
Introduction
Example 1 - Generate a SQL INSERT statement and the neccessary bind parameters to execute it:
my ( $sql, $bind ) = $builder->insert(
table => 'countries',
columns => [ 'id', 'name', 'code' ],
values => [ 1, 'Germany', 'DE' ]
);
Returned SQL:
'INSERT INTO "countries" ( "id", "name", "code" ) VALUES ( ?, ?, ? )'
Returned bind parameters:
[ 1, 'Germany', 'DE' ]
Prepare the statement:
my $sth = $dbh->prepare( $sql );
Execute the prepared statement:
$sth->execute( @$bind );
Example 2 - Generate a SQL INSERT statement and execute it with varying data:
my $sql = $builder->insert(
table => 'countries',
columns => [ 'id', 'name', 'code' ]
);
my $sth = $dbh->prepare( $sql );
my @data = (
[ 1, 'Germany', 'DE' ],
[ 2, 'Austria', 'AT' ],
# ...
);
for my $row ( @data ) {
$sth->execute( @$row );
}
Bind Values and Bind Types
Example:
use GX::SQL::Types qw( :all );
my ( $sql, $bind ) = $builder->insert(
table => 'countries',
columns => [ 'id', 'name', 'code' ],
values => [ 1, 'Germany', 'DE' ],
bind => {
'id' => INTEGER,
'name' => VARCHAR,
'code' => VARCHAR
}
);
Returned bind parameters:
[
[ 1, 1, DBI::SQL_INTEGER ],
[ 2, 'Germany', DBI::SQL_VARCHAR ],
[ 3, 'DE', DBI::SQL_VARCHAR ]
]
Usage:
my $sth = $dbh->prepare( $sql );
for my $parameters ( @$bind ) {
$sth->bind_param( @$parameters );
}
$sth->execute;
Custom Bind Arguments
my ( $sql, $bind ) = $builder->insert(
table => 'countries',
columns => [ 'id', 'name', 'code' ],
values => [ 1, 'Germany', 'DE' ],
bind => {
'id' => { TYPE => DBI::SQL_INTEGER },
'name' => { TYPE => DBI::SQL_VARCHAR },
'code' => { TYPE => DBI::SQL_VARCHAR }
}
);
Returned bind parameters:
[
[ 1, 1, { TYPE => DBI::SQL_INTEGER } ],
[ 2, 'Germany', { TYPE => DBI::SQL_VARCHAR } ],
[ 3, 'DE', { TYPE => DBI::SQL_VARCHAR } ]
]
WHERE Clauses
Basic Syntax
@where = ( 'name' => 'Germany' );
Result:
$sql = 'WHERE "name" = ?';
$bind = [ 'Germany' ];
"AND" logic by default:
@where = ( 'currency' => 'EUR', 'time_zone' => 'CET' );
Result:
$sql = 'WHERE "currency" = ? AND "time_zone" = ?';
$bind = [ 'Euro', 'CET' ];
Supported logic: "AND", "OR", "AND NOT", "OR NOT".
@where = ( 'currency' => 'EUR', 'OR', 'currency' => 'GBP' );
Result:
$sql = 'WHERE "currency" = ? OR "currency" = ?';
$bind = [ 'EUR', 'GBP' ];
Multiple values:
@where = ( 'currency' => [ 'EUR', 'GBP', 'USD' ] );
Result:
$sql = 'WHERE "currency" IN ( ?, ?, ? )';
$bind = [ 'EUR', 'GBP', 'USD' ];
Operators
Simple operators: "=", "!=", "<", ">", "<=", "=>", 'IS', 'IS NOT'.
@where = ( 'population' => { '>' => 50000000 } );
Result:
$sql = 'WHERE "population" > ?';
$bind = [ 50000000 ];
"IN" operator:
@where = ( 'currency' => { 'IN' => [ 'EUR', 'GBP', 'USD' ] } );
Result:
$sql = 'WHERE "currency" IN ( ?, ?, ? )';
$bind = [ 'EUR', 'GBP', 'USD' ];
"BETWEEN" operator:
@where = ( 'population' => { 'BETWEEN' => [ 10000000, 50000000 ] } );
Result:
$sql = 'WHERE "population" BETWEEN ? AND ?';
$bind = [ 10000000, 50000000 ];
NULL Conversion
@where = ( 'id' => undef );
Result:
$sql = 'WHERE "id" IS NULL';
$bind = [];
Also NOT NULL conversion:
@where = ( 'id' => { '!=' => undef } );
Result:
$sql = 'WHERE "id" IS NOT NULL';
$bind = [];
Nesting
@where = (
'id' => 1,
'OR',
[ 'name' => 'Germany', 'code' => 'DE' ]
);
Result:
$sql = 'WHERE "id" = ? OR ( "name" = ? AND "code" = ? )';
$bind = [ 1, 'Germany', 'DE' ];
Literal SQL
@where = ( 'id' => \'IS NOT NULL' );
Result:
$sql = 'WHERE "id" IS NOT NULL';
$bind = [];
SUBCLASSES
The following classes inherit directly from GX::SQL::Builder:
AUTHOR
Jörg A. Uzarek <uzarek@runlevelnull.de>
COPYRIGHT AND LICENSE
Copyright (c) 2009-2011 Jörg A. Uzarek.
This module is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License Version 3 as published by the Free Software Foundation.