File Coverage

blib/lib/SQL/Format.pm

Criterion	Covered	Total	%
statement	466	472	98.7
branch	257	272	94.4
condition	100	127	78.7
subroutine	25	25	100.0
pod	10	10	100.0
total	858	906	94.7

package SQL::Format;

use strict;
use warnings;
use 5.010_000;
our $VERSION = '0.19';

use Exporter 'import';
use Carp qw(croak carp);

our @EXPORT = qw(sqlf);

our $DELIMITER     = ', ';
our $NAME_SEP      = '.';
our $QUOTE_CHAR    = '`';
our $LIMIT_DIALECT = 'LimitOffset';

our $SELF = __PACKAGE__->new;

my $SPEC_TO_METHOD_MAP = {
    '%c' => '_columns',
    '%t' => '_table',
    '%w' => '_where',
    '%o' => '_options',
    '%j' => '_join',
    '%s' => '_set',
};

my $OP_ALIAS = {
    -IN              => 'IN',
    -NOT_IN          => 'NOT IN',
    -BETWEEN         => 'BETWEEN',
    -NOT_BETWEEN     => 'NOT BETWEEN',
    -LIKE            => 'LIKE',
    -NOT_LIKE        => 'NOT LIKE',
    -LIKE_BINARY     => 'LIKE BINARY',
    -NOT_LIKE_BINARY => 'NOT LIKE BINARY',
};

my $OP_TYPE_MAP = {
    in => {
        'IN'     => 1,
        'NOT IN' => 1,
    },
    between => {
        'BETWEEN'     => 1,
        'NOT BETWEEN' => 1,
    },
    like => {
        'LIKE'            => 1,
        'NOT LIKE'        => 1,
        'LIKE BINARY'     => 1,
        'NOT LIKE BINARY' => 1,
    },
};

my $SORT_OP_ALIAS = {
    -ASC  => 'ASC',
    -DESC => 'DESC',
};

my $SUPPORTED_INDEX_TYPE_MAP = {
    USE    => 1,
    FORCE  => 1,
    IGNORE => 1,
};

use constant {
    _LIMIT_OFFSET => 1,
    _LIMIT_XY     => 2,
    _LIMIT_YX     => 3,
};
my $LIMIT_DIALECT_MAP = {
    LimitOffset => _LIMIT_OFFSET, # PostgreSQL, SQLite, MySQL 5.0
    LimitXY     => _LIMIT_XY,     # MySQL
    LimitYX     => _LIMIT_YX,     # SQLite
};

sub sqlf {
    my $format = shift;

    my @bind;
    my @tokens = split m#(%[ctwosj])(?=\W|$)#, $format;
    for (my $i = 1; $i < @tokens; $i += 2) {
        my $spec = $tokens[$i];
        my $method = $SPEC_TO_METHOD_MAP->{$spec};
        croak "'$spec' does not supported format" unless $method;
        croak sprintf "missing arguments nummber of %i and '%s' format in sqlf",
            ($i + 1) / 2, $spec unless @_;

        $tokens[$i] = $SELF->$method(shift(@_), \@bind);
    }

    return join('',@tokens), @bind;
}

sub _columns {
    my ($self, $val, $bind) = @_;
    my $ret;

    if (!defined $val) {
        $ret = '*';
    }
    elsif (ref $val eq 'ARRAY') {
        if (@$val) {
            $ret = join $DELIMITER, map {
                my $ret;
                my $ref = ref $_;
                if ($ref eq 'HASH') {
                    my ($term, $col) = %$_;
                    $ret = _quote($term).' '._quote($col);
                }
                elsif ($ref eq 'ARRAY') {
                    my ($term, $col) = @$_;
                    my @params;
                    if (ref $term eq 'ARRAY') {
                        ($term, @params) = @$term;
                    }
                    elsif (ref $term eq 'REF' && ref $$term eq 'ARRAY') {
                        ($term, @params) = @{$$term};
                    }

                    $ret = (
                        ref $term eq 'SCALAR' ? $$term : _quote($term)
                    ).' '._quote($col);
                    push @$bind, @params;
                }
                elsif ($ref eq 'REF' && ref $$_ eq 'ARRAY') {
                    my ($term, $col, @params) = @{$$_};
                    $ret = (
                        ref $term eq 'SCALAR' ? $$term : _quote($term)
                    ).' '._quote($col);
                    push @$bind, @params;
                }
                else {
                    $ret = _quote($_)
                }
                $ret;
            } @$val;
        }
        else {
            $ret = '*';
        }
    }
    elsif (ref $val eq 'SCALAR') {
        $ret = $$val;
    }
    else {
        $ret = _quote($val);
    }

    return $ret;
}

sub _table {
    my ($self, $val, $bind) = @_;
    my $ret;

    if (ref $val eq 'ARRAY') {
        $ret = join $DELIMITER, map {
            my $v = $_;
            my $ret;
            if (ref $v eq 'HASH') {
                $ret = _complex_table_expr($v);
            }
            else {
                $ret = _quote($v);
            }
            $ret;
        } @$val;
    }
    elsif (ref $val eq 'HASH') {
        $ret = _complex_table_expr($val);
    }
    elsif (defined $val) {
        $ret = _quote($val);
    }
    else {
        # noop
    }

    return $ret;
}

sub _where {
    my ($self, $val, $bind, $logic) = @_;

    if (ref $val eq 'ARRAY') {
        my @ret;
        for my $v (@$val) {
            push @ret, $self->_where($v, $bind);
        }
        $logic ||= 'OR';
        return @ret == 1 ? $ret[0] : join " $logic ", map { "($_)" } @ret;
    }

    return unless ref $val eq 'HASH';

    return '(1=1)' unless %$val;

    my $ret = join ' AND ', map {
        my $org_key  = $_;
        my $no_paren = 0;
        my ($k, $v) = (_quote($org_key), $val->{$org_key});
        if (uc $org_key eq '-OR') {
            $k = $self->_where($v, $bind);
        }
        elsif (uc $org_key eq '-AND') {
            $k = $self->_where($v, $bind, 'AND');
        }
        elsif (ref $v eq 'ARRAY') {
            if (
                   ref $v->[0]
                or (($v->[0]||'') eq '-and')
                or (($v->[0]||'') eq '-or')
            ) {
                # [-and => qw/foo bar baz/]
                # [-and => { '>' => 10 }, { '<' => 20 } ]
                # [-or  => qw/foo bar baz/]
                # [-or  => { '>' => 10 }, { '<' => 20 } ]
                # [{ '>' => 10 }, { '<' => 20 } ]
                my $logic = 'OR';
                my @values = @$v;
                if ($v->[0] && $v->[0] eq '-and') {
                    $logic = 'AND';
                    @values = @values[1..$#values];
                }
                elsif ($v->[0] && $v->[0] eq '-or') {
                    @values = @values[1..$#values];
                }
                my @statements;
                for my $arg (@values) {
                    my ($_stmt, @_bind) = sqlf('%w', { $org_key => $arg });
                    push @statements, $_stmt;
                    push @$bind, @_bind;
                }
                $k = join " $logic ", @statements;
            }
            elsif (@$v == 0) {
                # []
                $k = '0=1';
            }
            else {
                # [qw/1 2 3/]
                $k .= ' IN ('.join($DELIMITER, ('?')x@$v).')';
                push @$bind, @$v;
            }
        }
        elsif (ref $v eq 'HASH') {
            my $no_paren = scalar keys %$v > 1 ? 0 : 1;
            $k = join ' AND ', map {
                my $k = $k;
                my ($op, $v) = (uc($_), $v->{$_});
                $op = $OP_ALIAS->{$op} || $op;
                if ($OP_TYPE_MAP->{in}{$op}) {
                    my $ref = ref $v;
                    if ($ref eq 'ARRAY') {
                        unless (@$v) {
                            # { IN => [] }
                            $k = $op eq 'IN' ? '0=1' : '1=1';
                        }
                        else {
                            # { IN => [qw/1 2 3/] }
                            $k .= " $op (".join($DELIMITER, ('?')x@$v).')';
                            push @$bind, @$v;
                        }
                    }
                    elsif ($ref eq 'REF') {
                        # { IN => \['SELECT foo FROM bar WHERE hoge = ?', 'fuga']
                        $k .= " $op (${$v}->[0])";
                        push @$bind, @{$$v}[1..$#$$v];
                    }
                    elsif ($ref eq 'SCALAR') {
                        # { IN => \'SELECT foo FROM bar' }
                        $k .= " $op ($$v)";
                    }
                    elsif (defined $v) {
                        # { IN => 'foo' }
                        $k .= $op eq 'IN' ? ' = ?' : ' <> ?';
                        push @$bind, $v;
                    }
                    else {
                        # { IN => undef }
                        $k .= $op eq 'IN' ? ' IS NULL' : ' IS NOT NULL';
                    }
                }
                elsif ($OP_TYPE_MAP->{between}{$op}) {
                    my $ref = ref $v;
                    if ($ref eq 'ARRAY') {
                        # { BETWEEN => ['foo', 'bar'] }
                        # { BETWEEN => [\'lower(x)', \['upper(?)', 'y']] }
                        my ($va, $vb) = @$v;
                        my @stmt;
                        for my $value ($va, $vb) {
                            if (ref $value eq 'SCALAR') {
                                push @stmt, $$value;
                            }
                            elsif (ref $value eq 'REF') {
                                push @stmt, ${$value}->[0];
                                push @$bind, @{$$value}[1..$#$$value];
                            }
                            else {
                                push @stmt, '?';
                                push @$bind, $value;
                            }
                        }
                        $k .= " $op ".join ' AND ', @stmt;
                    }
                    elsif ($ref eq 'REF') {
                        # { BETWEEN => \["? AND ?", 1, 2] }
                        $k .= " $op ${$v}->[0]";
                        push @$bind, @{$$v}[1..$#$$v];
                    }
                    elsif ($ref eq 'SCALAR') {
                        # { BETWEEN => \'lower(x) AND upper(y)' }
                        $k .= " $op $$v";
                    }
                    else {
                        # { BETWEEN => $scalar }
                        # noop
                    }
                }
                elsif ($OP_TYPE_MAP->{like}{$op}) {
                    my $ref = ref $v;
                    my $escape_char;
                    if ($ref eq 'HASH') {
                        ($escape_char, $v) = %$v;
                        $ref = ref $v;
                    }
                    if ($ref eq 'ARRAY') {
                        # { LIKE => ['%foo', 'bar%'] }
                        # { LIKE => [\'"%foo"', \'"bar%"'] }
                        my @stmt;
                        for my $value (@$v) {
                            if (ref $value eq 'SCALAR') {
                                push @stmt, $$value;
                            }
                            else {
                                push @stmt, '?';
                                push @$bind, $value;
                            }
                            if ($escape_char) {
                                $stmt[-1] .= ' ESCAPE ?';
                                push @$bind, $escape_char;
                            }
                        }
                        $k = join ' OR ', map { "$k $op $_" } @stmt;
                    }
                    elsif ($ref eq 'SCALAR') {
                        # { LIKE => \'"foo%"' }
                        $k .= " $op $$v";
                        if ($escape_char) {
                            $k .= ' ESCAPE ?';
                            push @$bind, $escape_char;
                        }
                    }
                    else {
                        $k .= " $op ?";
                        push @$bind, $v;
                        if ($escape_char) {
                            $k .= ' ESCAPE ?';
                            push @$bind, $escape_char;
                        }
                    }
                }
                elsif (ref $v eq 'SCALAR') {
                    # { '>' => \'foo' }
                    $k .= " $op $$v";
                }
                elsif (ref $v eq 'ARRAY') {
                    if ($op eq '=') {
                        unless (@$v) {
                            $k = '0=1';
                        }
                        else {
                            $k .= " IN (".join($DELIMITER, ('?')x@$v).')';
                            push @$bind, @$v;
                        }
                    }
                    elsif ($op eq '!=') {
                        unless (@$v) {
                            $k = '1=1';
                        }
                        else {
                            $k .= " NOT IN (".join($DELIMITER, ('?')x@$v).')';
                            push @$bind, @$v;
                        }
                    }
                    else {
                        # { '>' => [qw/1 2 3/] }
                        $k .= join ' OR ', map { "$op ?" } @$v;
                        push @$bind, @$v;
                    }
                }
                elsif (ref $v eq 'REF' && ref $$v eq 'ARRAY') {
                    # { '>' => \['UNIX_TIMESTAMP(?)', '2012-12-12 00:00:00'] }
                    $k .= " $op ${$v}->[0]";
                    push @$bind, @{$$v}[1..$#$$v];
                }
                else {
                    # { '>' => 'foo' }
                    $k .= " $op ?";
                    push @$bind, $v;
                }
                $no_paren ? $k : "($k)";
            } sort keys %$v;
        }
        elsif (ref $v eq 'REF' && ref $$v eq 'ARRAY') {
            $k .= " IN ($$v->[0])";
            push @$bind, @{$$v}[1..$#$$v];
        }
        elsif (ref $v eq 'SCALAR') {
            # \'foo'
            $k .= " $$v";
        }
        elsif (!defined $v) {
            # undef
            $k .= ' IS NULL';
        }
        else {
            # 'foo'
            $k .= ' = ?';
            push @$bind, $v;
        }
        $no_paren ? $k : "($k)";
    } sort keys %$val;

    return $ret;
}

sub _options {
    my ($self, $val, $bind) = @_;

    my @exprs;
    if (exists $val->{group_by}) {
        my $ret = _sort_expr($val->{group_by});
        push @exprs, 'GROUP BY '.$ret;
    }
    if (exists $val->{having}) {
        my ($ret, @new_bind) = sqlf('%w', $val->{having});
        push @exprs, 'HAVING '.$ret;
        push @$bind, @new_bind;
    }
    if (exists $val->{order_by}) {
        my $ret = _sort_expr($val->{order_by});
        push @exprs, 'ORDER BY '.$ret;
    }
    if (defined $val->{limit}) {
        croak "limit must be numeric specified ($val->{limit})" if $val->{limit} =~ /\D/;
        my $ret = 'LIMIT ';
        if ($val->{offset}) { # defined and > 0
            croak "offset must be numeric specified ($val->{offset})" if $val->{offset} =~ /\D/;
            my $limit_dialect = $LIMIT_DIALECT_MAP->{$LIMIT_DIALECT} || 0;
            if ($limit_dialect == _LIMIT_OFFSET) {
                $ret .= "$val->{limit} OFFSET $val->{offset}";
            }
            elsif ($limit_dialect == _LIMIT_XY) {
                $ret .= "$val->{offset}, $val->{limit}";
            }
            elsif ($limit_dialect == _LIMIT_YX) {
                $ret .= "$val->{limit}, $val->{offset}";
            }
            else {
                croak "Unkown LIMIT_DIALECT `$LIMIT_DIALECT`";
                $ret .= $val->{limit};
            }
        }
        else {
            $ret .= $val->{limit};
        }
        push @exprs, $ret;
    }

    return join ' ', @exprs;
}

sub _join {
    my ($self, $val, $bind) = @_;

    my @statements;
    $val = [$val] unless ref $val eq 'ARRAY';
    for my $param (@$val) {
        croak '%j mast be HASH ref specified' unless ref $param eq 'HASH';
        croak 'table and condition options must be specified at %j'
            unless $param->{table} && $param->{condition};

        my $ret = sprintf '%s JOIN ', uc($param->{type} || 'INNER');
        $ret .= $self->_table($param->{table}, $bind);

        if (ref $param->{condition} eq 'ARRAY') {
            $ret .= ' USING ('.(
                join $DELIMITER, map { _quote($_) } @{$param->{condition}}
            ).')';
        }
        elsif (ref $param->{condition} eq 'HASH') {
            my $cond = $param->{condition};
            my $no_paren = keys %$cond > 1 ? 0 : 1;
            $ret .= ' ON '.(join ' AND ', map {
                my ($k, $v) = ($_, $cond->{$_});
                my $ret;
                if (uc $k eq '-WHERE') {
                    $ret = $self->_where($v, $bind);
                }
                elsif (ref $v eq 'HASH') {
                    my $no_paren = keys %$v > 1 ? 0 : 1;
                    $ret = join ' AND ', map {
                        my $op = $_;
                        my $ret;
                        if (ref $v->{$op} eq 'REF' && ref ${$v->{$op}} eq 'ARRAY') {
                            my $v = ${$v->{$op}};
                            $ret = _quote($k)." $op ".$v->[0];
                            push @$bind, @{$v}[1..$#$v];
                        }
                        else {
                            $ret = _quote($k)." $op "._quote($v->{$_});
                        }
                        $no_paren ? $ret : "($ret)";
                    } sort keys %$v;
                }
                elsif (ref $v eq 'REF' && ref $$v eq 'ARRAY') {
                    my $v = $$v;
                    $ret = _quote($k).' = '._quote($v->[0]);
                    push @$bind, @{$v}[1..$#$v];
                }
                else {
                    $ret = _quote($k).' = '._quote($v);
                }
                $no_paren ? $ret : "($ret)";
            } sort keys %$cond);
        }
        else {
            $ret .= ' ON '.$param->{condition};
        }
        push @statements, $ret;
    }

    return join ' ', @statements;
}

sub _quote {
    my $stuff = shift;
    return $$stuff if ref $stuff eq 'SCALAR';
    return $stuff unless $QUOTE_CHAR && $NAME_SEP;
    return $stuff if $stuff eq '*';
    return $stuff if substr($stuff, 0, 1) eq $QUOTE_CHAR; # skip if maybe quoted
    return $stuff if $stuff =~ /\(/; # skip if maybe used function
    return join $NAME_SEP, map {
        "$QUOTE_CHAR$_$QUOTE_CHAR"
    } split /\Q$NAME_SEP\E/, $stuff;
}

sub _complex_table_expr {
    my $stuff = shift;
    my $ret = join $DELIMITER, map {
        my ($k, $v) = ($_, $stuff->{$_});
        my $ret = _quote($k);
        if (ref $v eq 'HASH') {
            $ret .= ' '._quote($v->{alias}) if $v->{alias};
            if (exists $v->{index} && ref $v->{index}) {
                my $type = uc($v->{index}{type} || 'USE');
                croak "unkown index type: $type"
                    unless $SUPPORTED_INDEX_TYPE_MAP->{$type};
                croak "keys field must be specified in index option"
                    unless defined $v->{index}{keys};
                my $keys = $v->{index}{keys};
                $keys = [ $keys ] unless ref $keys eq 'ARRAY';
                $ret .= " $type INDEX (".join($DELIMITER,
                    map { _quote($_) } @$keys
                ).")";
            }
        }
        else {
            $ret .= ' '._quote($v);
        }
        $ret;
    } sort keys %$stuff;

    return $ret;
}

sub _sort_expr {
    my $stuff = shift;
    my $ret = '';
    if (!defined $stuff) {
        # undef
        $ret .= 'NULL';
    }
    elsif (ref $stuff eq 'HASH') {
        # { colA => 'DESC' }
        # { -asc => 'colB' }
        $ret .= join $DELIMITER, map {
            if (my $sort_op = $SORT_OP_ALIAS->{uc $_}) {
                _quote($stuff->{$_}).' '.$sort_op,
            }
            else {
                _quote($_).' '.$stuff->{$_}
            }
        } sort keys %$stuff;
    }
    elsif (ref $stuff eq 'ARRAY') {
        # ['column1', { column2 => 'DESC', -asc => 'column3' }]
        my @parts;
        for my $part (@$stuff) {
            if (ref $part eq 'HASH') {
                push @parts, join $DELIMITER, map {
                    if (my $sort_op = $SORT_OP_ALIAS->{uc $_}) {
                        _quote($part->{$_}).' '.$sort_op,
                    }
                    else {
                        _quote($_).' '.$part->{$_}
                    }
                } sort keys %$part;
            }
            else {
                push @parts, _quote($part);
            }
        }
        $ret .= join $DELIMITER, @parts;
    }
    else {
        # 'column'
        $ret .= _quote($stuff);
    }
    return $ret;
}

sub _set {
    my ($self, $val, $bind) = @_;

    my @set = ref $val eq 'HASH' ? map { $_ => $val->{$_} } sort keys %$val : @$val;
    my @columns;
    for (my $i = 0; $i < @set; $i += 2) {
        my ($col, $val) = ($set[$i], $set[$i+1]);
        my $quoted_col = _quote($col);
        if (ref $val eq 'SCALAR') {
            # foo => { bar => \'NOW()' }
            push @columns, "$quoted_col = $$val";
        }
        elsif (ref $val eq 'REF' && ref $$val eq 'ARRAY') {
            # foo => { bar => \['UNIX_TIMESTAMP(?)', '2011-11-11 11:11:11'] }
            my ($stmt, @sub_bind) = @{$$val};
            push @columns, "$quoted_col = $stmt";
            push @$bind, @sub_bind;
        }
        else {
            # foo => { bar => 'baz' }
            push @columns, "$quoted_col = ?";
            push @$bind, $val;
        }
    }

    my $ret = join $self->{delimiter}, @columns;
}

sub new {
    my ($class, %args) = @_;

    if (exists $args{driver} && defined $args{driver}) {
        my $driver = lc $args{driver};
        unless (defined $args{quote_char}) {
            $args{quote_char} = $driver eq 'mysql' ? '`' : '"';
        }
        unless (defined $args{limit_dialect}) {
            $args{limit_dialect} =
                $driver eq 'mysql'  ? 'LimitXY' : 'LimitOffset';
        }
    }

    bless {
        delimiter     => $DELIMITER,
        name_sep      => $NAME_SEP,
        quote_char    => $QUOTE_CHAR,
        limit_dialect => $LIMIT_DIALECT,
        %args,
    }, $class;
}

sub format {
    my $self = shift;
    local $SELF          = $self;
    local $DELIMITER     = $self->{delimiter};
    local $NAME_SEP      = $self->{name_sep};
    local $QUOTE_CHAR    = $self->{quote_char};
    local $LIMIT_DIALECT = $self->{limit_dialect};
    sqlf(@_);
}

sub select {
    my ($self, $table, $cols, $where, $opts) = @_;
    croak 'Usage: $sqlf->select($table [, \@cols, \%where, \%opts])' unless defined $table;

    local $SELF          = $self;
    local $DELIMITER     = $self->{delimiter};
    local $NAME_SEP      = $self->{name_sep};
    local $QUOTE_CHAR    = $self->{quote_char};
    local $LIMIT_DIALECT = $self->{limit_dialect};

    if (delete $opts->{for_update}) {
        if (exists $opts->{suffix}) {
            croak 'Conflict option `for_update` and `suffix`. `for_update` option is ignored.';
        }
        else {
            $opts->{suffix} = 'FOR UPDATE';
        }
    }

    my $prefix = delete $opts->{prefix} || 'SELECT';
    my $suffix = delete $opts->{suffix};
    my $format = "$prefix %c FROM %t";
    my @args   = ($cols, $table);

    if (my $join = delete $opts->{join}) {
        $format .= ' %j';
        push @args, $join;
    }
    if ($where && (ref $where eq 'HASH' && keys %$where) || (ref $where eq 'ARRAY' && @$where)) {
        $format .= ' WHERE %w';
        push @args, $where;
    }
    if (keys %$opts) {
        $format .= ' %o';
        push @args, $opts;
    }
    if ($suffix) {
        $format .= " $suffix";
    }

    sqlf($format, @args);
}

sub insert {
    my ($self, $table, $values, $opts) = @_;
    croak 'Usage: $sqlf->insert($table \%values|\@values [, \%opts])' unless defined $table && ref $values;

    local $SELF          = $self;
    local $DELIMITER     = $self->{delimiter};
    local $NAME_SEP      = $self->{name_sep};
    local $QUOTE_CHAR    = $self->{quote_char};
    local $LIMIT_DIALECT = $self->{limit_dialect};

    my $prefix       = $opts->{prefix} || 'INSERT INTO';
    my $quoted_table = _quote($table);

    my @values = ref $values eq 'HASH' ? %$values : @$values;
    my (@columns, @bind_cols, @bind_params);
    for (my $i = 0; $i < @values; $i += 2) {
        my ($col, $val) = ($values[$i], $values[$i+1]);
        push @columns, _quote($col);
        if (ref $val eq 'SCALAR') {
            # foo => { bar => \'NOW()' }
            push @bind_cols, $$val;
        }
        elsif (ref $val eq 'REF' && ref $$val eq 'ARRAY') {
            # foo => { bar => \['UNIX_TIMESTAMP(?)', '2011-11-11 11:11:11'] }
            my ($stmt, @sub_bind) = @{$$val};
            push @bind_cols, $stmt;
            push @bind_params, @sub_bind;
        }
        else {
            # foo => { bar => 'baz' }
            push @bind_cols, '?';
            push @bind_params, $val;
        }
    }

    my $stmt = "$prefix $quoted_table "
             . '('.join(', ', @columns).') '
             . 'VALUES ('.join($self->{delimiter}, @bind_cols).')';

    return $stmt, @bind_params;
}

sub update {
    my ($self, $table, $set, $where, $opts) = @_;
    croak 'Usage: $sqlf->update($table \%set|\@set [, \%where, \%opts])' unless defined $table && ref $set;

    local $SELF          = $self;
    local $DELIMITER     = $self->{delimiter};
    local $NAME_SEP      = $self->{name_sep};
    local $QUOTE_CHAR    = $self->{quote_char};
    local $LIMIT_DIALECT = $self->{limit_dialect};

    my $prefix       = delete $opts->{prefix} || 'UPDATE';
    my $quoted_table = _quote($table);

    my $set_clause = $self->_set($set, \my @bind_params);
    my $format = "$prefix $quoted_table SET ".$set_clause;

    my @args;
    if ($where && (ref $where eq 'HASH' && keys %$where) || (ref $where eq 'ARRAY' && @$where)) {
        $format .= ' WHERE %w';
        push @args, $where;
    }
    if (keys %$opts) {
        $format .= ' %o';
        push @args, $opts;
    }

    my ($stmt, @bind) = sqlf($format, @args);

    return $stmt, (@bind_params, @bind);
}

sub delete {
    my ($self, $table, $where, $opts) = @_;
    croak 'Usage: $sqlf->delete($table [, \%where, \%opts])' unless defined $table;

    local $SELF          = $self;
    local $DELIMITER     = $self->{delimiter};
    local $NAME_SEP      = $self->{name_sep};
    local $QUOTE_CHAR    = $self->{quote_char};
    local $LIMIT_DIALECT = $self->{limit_dialect};

    my $prefix       = delete $opts->{prefix} || 'DELETE';
    my $quoted_table = _quote($table);
    my $format       = "$prefix FROM $quoted_table";

    my @args;
    if ($where && (ref $where eq 'HASH' && keys %$where) || (ref $where eq 'ARRAY' && @$where)) {
        $format .= ' WHERE %w';
        push @args, $where;
    }
    if (keys %$opts) {
        $format .= ' %o';
        push @args, $opts;
    }

    sqlf($format, @args);
}

sub insert_multi {
    my ($self, $table, $cols, $values, $opts) = @_;
    croak 'Usage: $sqlf->insert_multi($table, \@cols, [ \@values1, \@values2, ... ] [, \%opts])'
        unless ref $cols eq 'ARRAY' && ref $values eq 'ARRAY';

    local $SELF          = $self;
    local $DELIMITER     = $self->{delimiter};
    local $NAME_SEP      = $self->{name_sep};
    local $QUOTE_CHAR    = $self->{quote_char};
    local $LIMIT_DIALECT = $self->{limit_dialect};

    my $prefix       = $opts->{prefix} || 'INSERT INTO';
    my $quoted_table = _quote($table);

    my $columns_num = @$cols;
    my @bind_params;
    my @values_stmt;
    for my $value (@$values) {
        my @bind_cols;
        for (my $i = 0; $i < $columns_num; $i++) {
            my $val = $value->[$i];
            if (ref $val eq 'SCALAR') {
                # \'NOW()'
                push @bind_cols, $$val;
            }
            elsif (ref $val eq 'REF' && ref $$val eq 'ARRAY') {
                # \['UNIX_TIMESTAMP(?)', '2011-11-11 11:11:11']
                my ($expr, @sub_bind) = @{$$val};
                push @bind_cols, $expr;
                push @bind_params, @sub_bind;
            }
            else {
                # 'baz'
                push @bind_cols, '?';
                push @bind_params, $val;
            }
        }
        push @values_stmt, '('.join($self->{delimiter}, @bind_cols).')';
    }

    my $stmt = "$prefix $quoted_table "
        . '('.join($self->{delimiter}, map { _quote($_) } @$cols).') '
        . 'VALUES '.join($self->{delimiter}, @values_stmt);

    if ($opts->{update}) {
        my $update_stmt = $self->_set($opts->{update}, \@bind_params);
        $stmt .= " ON DUPLICATE KEY UPDATE $update_stmt";
    }

    return $stmt, @bind_params;
}

sub insert_multi_from_hash {
    my ($self, $table, $values, $opts) = @_;
    croak 'Usage: $sqlf->insert_multi_from_hash($table, [ { colA => $valA, colB => $valB }, { ... } ] [, \%opts])'
        unless ref $values eq 'ARRAY' && ref $values->[0] eq 'HASH';

    my $cols = [ keys %{$values->[0]} ];
    my $new_values = [];
    for my $value (@$values) {
        push @$new_values, [ @$value{@$cols} ];
    }

    $self->insert_multi($table, $cols, $new_values, $opts);
}

sub insert_on_duplicate {
    my ($self, $table, $values, $update_values, $opts) = @_;
    croak 'Usage: $sqlf->insert_on_duplicate($table, \%values|\@values, \%update_values|\@update_values [, \%opts])'
        unless ref $values && ref $update_values;

    my ($stmt, @bind) = $self->insert($table, $values, $opts);
    my $set_clause = $self->_set($update_values, \@bind);

    $stmt .= " ON DUPLICATE KEY UPDATE $set_clause";

    return $stmt, @bind;
}

1;
__END__