Keita Sugama


SQL::OOP - Yet another SQL Generator


    my $select = SQL::OOP::Select->new;
        $select->ARG_FIELDS => '*',
        $select->ARG_FROM   => SQL::OOP::ID->new('public', 'master'),
        $select->ARG_WHERE  => sub {
            my $where = SQL::OOP::Where->new;
            return $where->and(
                $where->cmp('=', 'a', 1),
                $where->cmp('=', 'b', 1),
        $select->GROUP_BY => 'field1',
        $select->ARG_LIMIT => 10,


SQL::OOP provides an object oriented interface for generating SQL statements. This is an alternative to SQL::Abstract but doesn't require any complex syntactical hash structure. All you have to do is to call well-readable OOP methods. Moreover, if you use IDE for coding Perl, the auto completion and call tips may work well with this.

SQL::OOP distribution consists of following modules. The indentation indicates the hierarchy of inheritance.

    SQL::OOP::Base [abstract]
        SQL::OOP::Array [abstract]
            SQL::OOP::Command [abstract]
    SQL::OOP::Where [factory]

Base architecture

Any instance of the classes above are capable of to_string() and bind(). These methods returns similar values as SQL::Abstract, which can be thrown at DBI methods.

    my $string = $any->to_string;
    my @values = $any->bind

Most class inherits SQL::OOP::Array which can contain array of SQL::OOP::Base. This means they can recursively contain any others. For example, SELECT command instance can be part of other SELECT command. Since the instances are well-encapsulated, you can manipulate them flexibly.

All class of this distribution inherits SQL::OOP::Base class.

    my $snippet1 = SQL::OOP::Base->new('a = ?', [1]);
    my $snippet2 = SQL::OOP::Base->new('b = ?', [2]);

Any instance can be part of any others.

    my $array1 = SQL::OOP::Array->new($snippet1, $snippet2);
    $array1->set_sepa(', ');
    warn $array1->to_string; ## a = ?, b = ?
    warn join ',', $array1->bind; ## 1,2

Even arrays can contain arrays.

    my $array2 = SQL::OOP::Array->new($array1, $snippet1);
    $array2->set_sepa(', ');
    warn $array2->to_string; ## (a = ?, b = ?), a = ?
    warn join ',', $array2->bind; ## 1,2,1

This is an example of WHERE clause.

    my $util = SQL::OOP::Where->new; ## for convenience
    my $cond1 = $util->cmp('<', 'price', '100');
    my $cond2 = $util->cmp('=', 'category', 'book');
    my $and = $util->and($cond1, $cond2);
    my $sql1 = $and->to_string # price < ? AND category = ?

$cond1 and $cond2 are SQL::OOP::Base instances. $and which is a SQL::OOP::Array instance can contain $cond1 and $cond2.

    my $cond3 = $util->cmp('like', 'title', '%Perl%');
    my $or = $util->or($and, $cond3);
    my $sql2 = $and->to_string # (price < ? AND category = ?) OR title like ?

$or is a SQL::OOP::Array instance which can contain both SQL::OOP::Base and SQL::OOP::Array.

Code reference for arguments

All new constructors, append methods for array sub classes, and set methods for command sub classes are capable of code refs for arguments instead of string or objects so that you can encapsulate temporary things inside of it.

        $select->ARG_WHERE  => sub {
            if ($cond_exist) {
                my $tmp = $cond_exist;
                # DO SOMETHING to $tmp
                return SQL::OOP::Where->cmp('=', 'field', $tmp);
            } else {

Undefined values

This module doesn't always output undef for undef in input. Following example indicates undef in bind value doesn't appear in output but to_string returns right string.

    my $elem = SQL::OOP::Base->new('field', undef);
    print $elem->to_string; ## field
    print scalar $elem->bind; ## 0

On the other hand, undef values for array elements are totally omitted.

    my $elem1 = SQL::OOP::Base->new('field1', 1);
    my $elem2 = undef;
    my $elem3 = SQL::OOP::Base->new('field3', 3);
    my $array = SQL::OOP::Array->new($elem1, $elem2, $elem3);
    print $array->to_string; ## field1field3
    print $array->bind; ## 13

WHERE factory methods omits undef for field names.

    my $util = SQL::OOP::Where->new;
    my $cond = $util->cmp('=', undef, '1'); ## undef

This system makes things easy. The following two functions works well and you don't have to worry about undef.

    sub generate_where {
        my ($value1, $value2, $value3) = @_;
        my $util = SQL::OOP::Where->new;
        my $cond1 = $util->cmp('=', 'field', $value1); ## This may be undef
        my $cond2 = $util->cmp('=', 'field', $value2); ## This may be undef
        my $cond3 = $util->cmp('=', 'field', $value3); ## This may be undef
        my $and = $util->and($cond1, $cond2, $cond3);
        return $and;
    generate_select(generate_where(1), 50)
    sub generate_select {
        my ($where, $limit) = @_;
        my $select = SQL::OOP::Select->new;
            $select->ARG_FIELDS => '*',
            $select->ARG_FROM   => 'main',
            $select->ARG_WHERE  => $where,  ## This may be undef
            $select->ARG_LIMIT  => $limit,  ## This may be undef
        return $select;

If you need to compare something to NULL in WHERE clause, you can use specific methods.

    my $cond1 = $util->is_null('field');
    my $cond2 = $util->is_not_null('field');

The Only exception is the Dataset class for this system. Dataset can contain undef for value and explicitly output undefs to make DBI treats them as NULL.

Room for extreme complexity

If you need very complex SQL generation such as functions or conditional branches, you can always resort to string.

        $select->ARG_FIELDS     => '*', 
        $select->ARG_FROM       => 'main', 
        $select->ARG_ORDERBY    => q{
            abs(date("timestamp") - date('now')) DESC

The following is use of string in WHERE element.

    my $util = SQL::OOP::Where->new;
    my $cond1 = $util->cmp('=', 'a','b');
    my $cond2 = $util->cmp('=', 'a','b');
    my $cond3 = 'date("t1") > date("t2")';
    my $and = $util->and($cond1, $cond2, $cond3);
    warn $and->to_string; ## "a" = ? AND "a" = ? AND date("t1") > date("t2")


The following methods are all aliases to SQL::OOP::Base class methods.


This is an alias for SQL::OOP::Base->new


This is an alias for SQL::OOP::Base->quote_char


This is an alias for SQL::OOP::Base->escape_code_ref


DBI, SQL::Abstract


Sugama Keita, <>


Copyright (C) 2014 by Jamadam.

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