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:

GX::Exception

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:

GX::Exception

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:

GX::Exception

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:

GX::Exception

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:

GX::Exception

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:

GX::Exception

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:

GX::Exception

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

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.

To install GX, copy and paste the appropriate command in to your terminal.

cpanm

cpanm GX

CPAN shell

perl -MCPAN -e shell
install GX

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

DESCRIPTION

METHODS

Constructor

new

Public Methods

count

delete

insert

select

update

where

USAGE

Introduction

Bind Values and Bind Types

Custom Bind Arguments

WHERE Clauses

Basic Syntax

Operators

NULL Conversion

Nesting

Literal SQL

SUBCLASSES

AUTHOR

COPYRIGHT AND LICENSE

Module Install Instructions

`new`

`count`

`delete`

`insert`

`select`

`update`

`where`