GX::SQL::Builder

Module Version: 0.2000_01

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:
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 = [];

AUTHOR

Jörg A. Uzarek <uzarek@runlevelnull.de>

COPYRIGHT AND LICENSE

Copyright © 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.