Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Yet another yet another SQL builder
Perl
branch: master

Fetching latest commit…

Cannot retrieve the latest commit at this time

Failed to load latest commit information.
bench tweaks
lib/SQL
t
xt Checking in changes prior to tagging of version 0.12.
.gitignore Checking in changes prior to tagging of version 0.12.
.proverc
.travis.yml Added travis,yml
Build.PL
Changes Checking in changes prior to tagging of version 0.16.
LICENSE Checking in changes prior to tagging of version 0.12.
META.json Checking in changes prior to tagging of version 0.16.
README.md Checking in changes prior to tagging of version 0.16.
cpanfile scan-prereqs-cpanfile

README.md

NAME

SQL::Format - Yet another yet another SQL builder

SYNOPSIS

use SQL::Format;

my ($stmt, @bind) = sqlf 'SELECT %c FROM %t WHERE %w' => (
    [qw/bar baz/], # %c
    'foo',         # %t
    {
        hoge => 'fuga',
        piyo => [qw/100 200 300/],
    },             # %w
);
# $stmt: SELECT `bar`, `baz` FROM `foo` WHERE (`hoge` = ?) AND (`piyo` IN (?, ?, ?))
# @bind: ('fuga', 100, 200, 300);

($stmt, @bind) = sqlf 'SELECT %c FROM %t WHERE %w %o' => (
    '*',                # %c
    'foo',              # %t
    { hoge => 'fuga' }, # w
    {
        order_by => { bar => 'DESC' },
        limit    => 100,
        offset   => 10,
    },                  # %o
);
# $stmt: SELECT * FROM `foo` WHERE (`hoge` = ?) ORDER BY `bar` DESC LIMIT 100 OFFSET 10
# @bind: (`fuga`)

($stmt, @bind) = sqlf 'UPDATE %t SET %s' => (
    foo => { bar => 'baz', 'hoge => 'fuga' },
);
# $stmt: UPDATE `foo` SET `bar` = ?, `hoge` = ?
# @bind: ('baz', 'fuga')

my $sqlf = SQL::Format->new(
    quote_char    => '',        # do not quote
    limit_dialect => 'LimitXY', # mysql style limit-offset
);
($stmt, @bind) = $sqlf->select(foo => [qw/bar baz/], {
    hoge => 'fuga',
}, {
    order_by => 'bar',
    limit    => 100,
    offset   => 10,
});
# $stmt: SELECT bar, baz FROM foo WHERE (hoge = ?) ORDER BY bar LIMIT 10, 100
# @bind: ('fuga')

($stmt, @bind) = $sqlf->insert(foo => { bar => 'baz', hoge => 'fuga' });
# $stmt: INSERT INTO foo (bar, hoge) VALUES (?, ?)
# @bind: ('baz', 'fuga')

($stmt, @bind) = $sqlf->update(foo => { bar => 'xxx' }, { hoge => 'fuga' });
# $stmt: UPDATE foo SET bar = ? WHERE hoge = ?
# @bind: ('xxx', 'fuga')

($stmt, @bind) = $sqlf->delete(foo => { hoge => 'fuga' });
# $stmt: DELETE FROM foo WHERE (hoge = ?)
# @bind: ('fuga')

DESCRIPTION

SQL::Format is a easy to SQL query building library.

THIS MODULE IS ALPHA LEVEL INTERFACE!!

FUNCTIONS

sqlf($format, @args)

Generate SQL from formatted output conversion.

my ($stmt, @bind) = sqlf 'SELECT %c FROM %t WHERE %w' => (
    [qw/bar baz/],   # %c
    'foo',           # %t
    {
        hoge => 'fuga',
        piyo => [100, 200, 300],
    },               # %w
);
# $stmt: SELECT `foo` FROM `bar`, `baz WHERE (`hoge` = ?) AND (`piyo` IN (?, ?, ?))
# @bind: ('fuga', 100, 200, 300)

Currently implemented formatters are:

  • %t

    This format is a table name.

    ($stmt, @bind) = sqlf '%t', 'table_name';        # $stmt => `table_name`
    ($stmt, @bind) = sqlf '%t', [qw/tableA tableB/]; # $stmt => `tableA`, `tableB`
    ($stmt, @bind) = sqlf '%t', { tableA => 't1' };  # $stmt => `tableA` `t1`
    ($stmt, @bind) = sqlf '%t', {
        tableA => {
            index => { type => 'force', keys => [qw/key1 key2/] },
            alias => 't1',
    }; # $stmt: `tableA` `t1` FORCE INDEX (`key1`, `key2`)
    
  • %c

    This format is a column name.

    ($stmt, @bind) = sqlf '%c', 'column_name';       # $stmt => `column_name`
    ($stmt, @bind) = sqlf '%c', [qw/colA colB/];     # $stmt => `colA`, `colB`
    ($stmt, @bind) = sqlf '%c', '*';                 # $stmt => *
    ($stmt, @bind) = sqlf '%c', [\'COUNT(*)', colC]; # $stmt => COUNT(*), `colC`
    
  • %w

    This format is a where clause.

    ($stmt, @bind) = sqlf '%w', { foo => 'bar' };
    # $stmt: (`foo` = ?)
    # @bind: ("bar")
    
    ($stmt, @bind) = sqlf '%w', {
        foo => 'bar',
        baz => [qw/100 200 300/],
    };
    # $stmt: (`baz` IN (?, ?, ?) AND (`foo` = ?)
    # @bind: (100, 200, 300, 'bar')
    
  • %o

    This format is a options. Currently specified are:

    • limit

      This option makes LIMIT $n clause.

      ($stmt, @bind) = sqlf '%o', { limit => 100 }; # $stmt => LIMIT 100
      
    • offset

      This option makes OFFSET $n clause. You must be specified both limit option.

      ($stmt, @bind) = sqlf '%o', { limit => 100, offset => 20 }; # $stmt => LIMIT 100 OFFSET 20
      

      You can change limit dialects from $SQL::Format::LIMIT_DIALECT.

    • order_by

      This option makes ORDER BY clause.

      ($stmt, @bind) = sqlf '%o', { order_by => 'foo' };                       # $stmt => ORDER BY `foo`
      ($stmt, @bind) = sqlf '%o', { order_by => { foo => 'DESC' } };           # $stmt => ORDER BY `foo` DESC
      ($stmt, @bind) = sqlf '%o', { order_by => ['foo', { -asc => 'bar' } ] }; # $stmt => ORDER BY `foo`, `bar` ASC
      
    • group_by

      This option makes GROUP BY clause. Argument value some as order_by option.

      ($stmt, @bind) = sqlf '%o', { group_by => { foo => 'DESC' } }; # $stmt => GROUP BY `foo` DESC
      
    • having

      This option makes HAVING clause. Argument value some as where clause.

      ($stmt, @bind) = sqlf '%o', { having => { foo => 'bar' } };
      # $stmt: HAVING (`foo` = ?)
      # @bind: ('bar')
      
  • %j

    This format is join clause.

    ($stmt, @bind) = sqlf '%j', { table => 'bar', condition => 'foo.id = bar.id' };
    # $stmt: INNER JOIN `bar` ON (foo.id = bar.id)
    
    ($stmt, @bind) = sqlf '%j', {
        type      => 'left',
        table     => { bar => 'b' },
        condition => {
            'f.id'         => 'b.id',
            'f.updated_at' => \['UNIX_TIMESTAMP()', '2012-12-12']
            'f.created_at' => { '>' => 'b.created_at' },
        },
    };
    # $stmt: LEFT JOIN `bar` `b` ON (`f`.`id` = `b.id`)
    
  • %s

    This format is set clause.

    ($stmt, @bind) = sqlf '%s', { bar => 'baz' };
    # $stmt: `bar` = ?
    # @bind: ('baz')
    
    ($stmt, @bind) = sqlf '%s', { bar => 'baz', 'hoge' => \'UNIX_TIMESTAMP()' };
    # $stmt: `bar` = ?, `hoge` = UNIX_TIMESTAMP()
    # @bind: ('baz')
    
    ($stmt, @bind) = sqlf '%s', {
        bar  => 'baz',
        hoge => \['CONCAT(?, ?)', 'ya', 'ppo'],
    };
    # $stmt: `bar` = ?, `hoge` = CONCAT(?, ?)
    # @bind: ('baz', 'ya', 'ppo')
    

For more examples, see also SQL::Format::Spec.

You can change the behavior by changing the global variable.

  • $SQL::Format::QUOTE_CHAR : Str

    This is a quote character for table or column name.

    Default value is "`".

  • $SQL::Format::NAME_SEP : Str

    This is a separate character for table or column name.

    Default value is ".".

  • $SQL::Format::DELIMITER Str

    This is a delimiter for between columns.

    Default value is ", ".

  • $SQL::Format::LIMIT_DIALECT : Str

    This is a types for dialects of limit-offset.

    You can choose are:

    LimitOffset  # LIMIT 100 OFFSET 20  (SQLite / PostgreSQL / MySQL)
    LimitXY      # LIMIT 20, 100        (MySQL / SQLite)
    LimitYX      # LIMIT 100, 20        (other)
    

    Default value is LimitOffset".

METHODS

new([%options])

Create a new instance of SQL::Format.

my $sqlf = SQL::Format->new(
    quote_char    => '',
    limit_dialect => 'LimitXY',
);

%options specify are:

  • quote_char : Str

    Default value is $SQL::Format::QUOTE_CHAR.

  • name_sep : Str

    This is a separate character for table or column name.

    Default value is $SQL::Format::NAME_SEP.

  • delimiter: Str

    This is a delimiter for between columns.

    Default value is $SQL::Format::DELIMITER.

  • limit_dialect : Str

    This is a types for dialects of limit-offset.

    Default value is $SQL::Format::LIMIT_DIALECT.

format($format, \%args)

This method same as sqlf function.

my ($stmt, @bind) = $self->format('SELECT %c FROM %t WHERE %w',
    [qw/bar baz/],
    'foo',
    { hoge => 'fuga' },
);
# $stmt: SELECT `bar`, `baz` FROM ` foo` WHERE (`hoge` = ?)
# @bind: ('fuga')

select($table|\@table, $column|\@columns [, \%where, \%opts ])

This method returns SQL string and bind parameters for SELECT statement.

my ($stmt, @bind) = $sqlf->select(foo => [qw/bar baz/], {
    hoge => 'fuga',
    piyo => [100, 200, 300],
});
# $stmt: SELECT `foo` FROM `bar`, `baz` WHERE (`hoge` = ?) AND (`piyo` IN (?, ?, ?))
# @bind: ('fuga', 100, 200, 300)

Argument details are:

  • $table | \@table

    Same as %t format.

  • $column | \@columns

    Same as %c format.

  • \%where

    Same as %w format.

  • \%opts

    • $opts->{prefix}

      This is prefix for SELECT statement.

      my ($stmt, @bind) = $sqlf->select(foo => '*', { bar => 'baz' }, { prefix => 'SELECT SQL_CALC_FOUND_ROWS' });
      # $stmt: SELECT SQL_CALC_FOUND_ROWS * FROM `foo` WHERE (`bar` = ?)
      # @bind: ('baz')
      

      Default value is SELECT.

    • $opts->{suffix}

      Additional value for after the SELECT statement.

      my ($stmt, @bind) = $sqlf->select(foo => '*', { bar => 'baz' }, { suffix => 'FOR UPDATE' });
      # $stmt: SELECT * FROM `foo` WHERE (bar = ?) FOR UPDATE
      # @bind: ('baz')
      

      Default value is ''

    • $opts->{limit}

    • $opts->{offset}
    • $opts->{order_by}
    • $opts->{group_by}
    • $opts->{having}
    • $opts->{join}

      See also %o format.

insert($table, \%values|\@values [, \%opts ])

This method returns SQL string and bind parameters for INSERT statement.

my ($stmt, @bind) = $sqlf->insert(foo => { bar => 'baz', hoge => 'fuga' });
# $stmt: INSERT INTO `foo` (`bar`, `hoge`) VALUES (?, ?)
# @bind: ('baz', 'fuga')

my ($stmt, @bind) = $sqlf->insert(foo => [
    hoge => \'NOW()',
    fuga => \['UNIX_TIMESTAMP()', '2012-12-12 12:12:12'],
]);
# $stmt: INSERT INTO `foo` (`hoge`, `fuga`) VALUES (NOW(), UNIX_TIMESTAMP(?))
# @bind: ('2012-12-12 12:12:12')

Argument details are:

  • $table

    This is a table name for target of INSERT.

  • \%values | \@values

    This is a VALUES clause INSERT statement.

    Currently supported types are:

    # \%values case
    { foo => 'bar' }
    { foo => \'NOW()' }
    { foo => \['UNIX_TIMESTAMP()', '2012-12-12 12:12:12'] }
    
    # \@values case
    [ foo => 'bar' ]
    [ foo => \'NOW()' ]
    [ foo => \['UNIX_TIMESTAMP()', '2012-12-12 12:12:12'] ]
    
  • \%opts

    • $opts->{prefix}

      This is a prefix for INSERT statement.

      my ($stmt, @bind) = $sqlf->insert(foo => { bar => baz }, { prefix => 'INSERT IGNORE' });
      # $stmt: INSERT IGNORE INTO `foo` (`bar`) VALUES (?)
      # @bind: ('baz')
      

      Default value is INSERT.

update($table, \%set|\@set [, \%where, \%opts ])

This method returns SQL string and bind parameters for UPDATE statement.

my ($stmt, @bind) = $sqlf->update(foo => { bar => 'baz' }, { hoge => 'fuga' });
# $stmt: UPDATE `foo` SET `bar` = ? WHERE (`hoge` = ?)
# @bind: ('baz', 'fuga')

Argument details are:

  • $table

    This is a table name for target of UPDATE.

  • \%set | \@set

    This is a SET clause for INSERT statement.

    Currently supported types are:

    # \%values case
    { foo => 'bar' }
    { foo => \'NOW()' }
    { foo => \['UNIX_TIMESTAMP()', '2012-12-12 12:12:12'] }
    
    # \@values case
    [ foo => 'bar' ]
    [ foo => \'NOW()' ]
    [ foo => \['UNIX_TIMESTAMP()', '2012-12-12 12:12:12'] ]
    
  • \%where

    Same as %w format.

  • \%opts

    • $opts->{prefix}

      This is a prefix for UPDATE statement.

      my ($stmt, @bind) = $sqlf->update(
          'foo'                                # table
          { bar    => 'baz' },                 # sets
          { hoge   => 'fuga' },                # where
          { prefix => 'UPDATE LOW_PRIORITY' }, # opts
      );
      # $stmt: UPDATE LOW_PRIORITY `foo` SET `bar` = ? WHERE (`hoge` = ?)
      # @bind: ('baz', 'fuga')
      

      Default value is UPDATE.

    • $opts->{order_by}

    • $opts->{limit}

      See also %o format.

delete($table [, \%where, \%opts ])

This method returns SQL string and bind parameters for DELETE statement.

my ($stmt, @bind) = $sqlf->delete(foo => { bar => 'baz' });
# $stmt: DELETE FROM `foo` WHERE (`bar = ?)
# @bind: ('baz')

Argument details are:

  • $table

    This is a table name for target of DELETE.

  • \%where

    Same as %w format.

  • \%opts

    • $opts->{prefix}

      This is a prefix for DELETE statement.

      my ($stmt, @bind) = $sqlf->delete(foo => { bar => 'baz' }, { prefix => 'DELETE LOW_PRIORITY' });
      # $stmt: DELETE LOW_PRIORITY FROM `foo` WHERE (`bar` = ?)
      # @bind: ('baz')
      

      Default value is DELETE.

    • $opts->{order_by}

    • $opts->{limit}

      See also %o format.

insert_multi($table, \@cols, \@values [, \%opts])

This method returns SQL string and bind parameters for bulk insert.

my ($stmt, @bind) = $self->insert_multi(
    foo => [qw/bar baz/],
    [
        [qw/hoge fuga/],
        [qw/fizz buzz/],
    ],
);
# $stmt: INSERT INTO `foo` (`bar`, `baz`) VALUES (?, ?), (?, ?)
# @bind: (qw/hoge fuga fizz buzz/)

Argument details are:

  • $table

    This is a table name for target of INSERT.

  • \@cols

    This is a columns for target of INSERT.

  • \@values

    This is a values parameters. Must be ARRAY within ARRAY.

    my ($stmt, @bind) = $sqlf->insert_multi(
        foo => [qw/bar baz/], [
            [qw/foo bar/],
            [\'NOW()', \['UNIX_TIMESTAMP(?)', '2012-12-12 12:12:12'] ],
        ],
    );
    # $stmt: INSERT INTO `foo` (`bar`, `baz`) VALUES (?, ?), (NOW(), UNIX_TIMESTAMP(?))
    # @bind: (qw/foo bar/, '2012-12-12 12:12:12')
    
  • \%opts

    • $opts->{prefix}

      This is a prefix for INSERT statement.

      my ($stmt, @bind) = $sqlf->insert_multi(..., { prefix => 'INSERT IGNORE INTO' });
      # $stmt: INSERT IGNORE INTO ...
      

      Default value is INSERT INTO.

    • $opts->{update}

      Some as %s format.

      If this value specified then add ON DUPLICATE KEY UPDATE statement.

      my ($stmt, @bind) = $sqlf->insert_multi(
          foo => [qw/bar baz/],
          [
              [qw/hoge fuga/],
              [qw/fizz buzz/],
          ],
          { update => { bar => 'piyo' } },
      );
      # $stmt: INSERT INTO `foo` (`bar`, `baz`) VALUES (?, ?), (?, ?) ON DUPLICATE KEY UPDATE `bar` = ?
      # @bind: (qw/hoge fuga fizz buzz piyo/)
      

insert_multi_from_hash($table, \@values [, \%opts])

This method is a wrapper for insert_multi().

Argument dialects are:

  • $table

    Same as insert_multi()

  • \@values

    This is a values parameters. Must be HASH within ARRAY.

    my ($stmt, @bind) = $sqlf->insert_multi_from_hash(foo => [
        { bar => 'hoge', baz => 'fuga' },
        { bar => 'fizz', baz => 'buzz' },
    ]);
    # $stmt: INSERT INTO `foo` (`bar`, `baz`) VALUES (?, ?), (?, ?)
    # @bind: (qw/hoge fuga fizz buzz/)
    
  • \%opts

    Same as insert_multi()

insert_on_duplicate($table, \%values|\@values, \%update_values|\@update_values [, \%opts])

This method generate "INSERT INTO ... ON DUPLICATE KEY UPDATE" query for MySQL.

my ($stmt, @bind) = $sqlf->insert_on_duplicate(
    foo => {
        bar => 'hoge',
        baz => 'fuga',
    }, {
        bar => \'VALUES(bar)',
        baz => 'piyo',
    },
);
# $stmt: INSERT INTO `foo` (`bar`, `baz`) VALUES (?, ?) ON DUPLICATE KEY UPDATE `bar` = VALUES(bar), baz = 'piyo'
# @bind: (qw/hoge fuga piyo/)

Argument details are:

  • $table

    This is a table name for target of INSERT.

  • \%values|\@values

    This is a values parameters.

  • \%update_values|\@update_values

    This is a ON DUPLICATE KEY UPDATE parameters.

  • \%opts

    • $opts->{prefix}

      This is a prefix for INSERT statement.

      my ($stmt, @bind) = $sqlf->insert_on_duplicate(..., { prefix => 'INSERT IGNORE INTO' });
      # $stmt: INSERT IGNORE INTO ...
      

AUTHOR

xaicron

COPYRIGHT

Copyright 2012 - xaicron

LICENSE

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

SEE ALSO

SQL::Format::Spec

SQL::Maker

SQL::Abstract

Something went wrong with that request. Please try again.