AlignDB-SQL

 view release on metacpan or  search on metacpan

README  view on Meta::CPAN

NAME

    AlignDB::SQL - An SQL statement generator.

SYNOPSIS

        my $sql = AlignDB::SQL->new();
        $sql->select([ 'id', 'name', 'bucket_id', 'note_id' ]);
        $sql->from([ 'foo' ]);
        $sql->add_where('name',      'fred');
        $sql->add_where('bucket_id', { op => '!=', value => 47 });
        $sql->add_where('note_id',   \'IS NULL');
        $sql->limit(1);
    
        my $sth = $dbh->prepare($sql->as_sql);
        $sth->execute(@{ $sql->{bind} });
        my @values = $sth->selectrow_array();
    
        my $obj = SomeObject->new();
        $obj->set_columns(...);

DESCRIPTION

    AlignDB::SQL represents an SQL statement.

    Most codes come from Data::ObjectDriver::SQL

lib/AlignDB/SQL.pm  view on Meta::CPAN

package AlignDB::SQL;
use Moose;
use MooseX::Storage;
use YAML qw(Dump Load DumpFile LoadFile);
with Storage( 'format' => 'YAML' );

our $VERSION = '1.0.2';

has 'select'             => ( is => 'rw', isa => 'ArrayRef', default => sub { [] } );
has 'select_map'         => ( is => 'rw', isa => 'HashRef',  default => sub { {} } );
has 'select_map_reverse' => ( is => 'rw', isa => 'HashRef',  default => sub { {} } );
has 'from'               => ( is => 'rw', isa => 'ArrayRef', default => sub { [] } );
has 'joins'              => ( is => 'rw', isa => 'ArrayRef', default => sub { [] } );
has 'where'              => ( is => 'rw', isa => 'ArrayRef', default => sub { [] } );
has 'bind'               => ( is => 'rw', isa => 'ArrayRef', default => sub { [] } );
has 'limit'              => ( is => 'rw', isa => 'Int' );
has 'offset'             => ( is => 'rw', );
has 'group'              => ( is => 'rw', );
has 'order'              => ( is => 'rw', );
has 'having'             => ( is => 'rw', isa => 'ArrayRef', default => sub { [] } );
has 'where_values'       => ( is => 'rw', isa => 'HashRef',  default => sub { {} } );
has '_sql'    => ( is => 'rw', isa => 'Str',     default => '' );
has 'indent'  => ( is => 'rw', isa => 'Str',     default => ' ' x 2 );
has 'replace' => ( is => 'rw', isa => 'HashRef', default => sub { {} } );

sub add_select {
    my $self = shift;
    my ( $term, $col ) = @_;
    $col ||= $term;
    push @{ $self->select }, $term;
    $self->select_map->{$term}        = $col;
    $self->select_map_reverse->{$col} = $term;
}

sub add_join {
    my $self = shift;
    my ( $table, $joins ) = @_;
    push @{ $self->joins },
        {
        table => $table,
        joins => ref($joins) eq 'ARRAY' ? $joins : [$joins],
        };
}

sub as_header {
    my $self = shift;

    my @terms;
    if ( @{ $self->select } ) {
        my %select_map = %{ $self->select_map };
        for my $term ( @{ $self->select } ) {
            if ( exists $select_map{$term} ) {
                my $alias = $select_map{$term};
                push @terms, $alias;
            }
            else {
                push @terms, $term;
            }
        }
    }

    if ( keys %{ $self->replace } ) {
        for my $find ( keys %{ $self->replace } ) {

lib/AlignDB/SQL.pm  view on Meta::CPAN

    return @terms;
}

sub as_sql {
    my $self = shift;

    my $indent = $self->indent;
    my $sql    = '';

    if ( @{ $self->select } ) {
        my %select_map = %{ $self->select_map };
        my @terms;
        for my $term ( @{ $self->select } ) {
            if ( exists $select_map{$term} ) {
                my $alias = $select_map{$term};

                # add_select( 'f.foo'    => 'foo' ) ===> f.foo
                # add_select( 'COUNT(*)' => 'count' ) ===> COUNT(*) count
                if ( $alias and $term =~ /(?:^|\.)\Q$alias\E$/ ) {
                    push @terms, $term;
                }
                else {
                    push @terms, "$term $alias";
                }
            }
            else {
                push @terms, $term;
            }

lib/AlignDB/SQL.pm  view on Meta::CPAN

    my ( $col, $val ) = @_;

    # TODO: should check if the value is same with $val?
    exists $self->where_values->{$col};
}

sub add_having {
    my $self = shift;
    my ( $col, $val ) = @_;

    if ( my $orig = $self->select_map_reverse->{$col} ) {
        $col = $orig;
    }

    my ( $term, $bind ) = $self->_mk_term( $col, $val );
    push @{ $self->{having} }, "($term)";
    push @{ $self->{bind} },   @$bind;
}

#@returns AlignDB::SQL
sub copy {

lib/AlignDB/SQL.pm  view on Meta::CPAN

=encoding UTF-8

=head1 NAME

AlignDB::SQL - An SQL statement generator.

=head1 SYNOPSIS

    my $sql = AlignDB::SQL->new();
    $sql->select([ 'id', 'name', 'bucket_id', 'note_id' ]);
    $sql->from([ 'foo' ]);
    $sql->add_where('name',      'fred');
    $sql->add_where('bucket_id', { op => '!=', value => 47 });
    $sql->add_where('note_id',   \'IS NULL');
    $sql->limit(1);

    my $sth = $dbh->prepare($sql->as_sql);
    $sth->execute(@{ $sql->{bind} });
    my @values = $sth->selectrow_array();

    my $obj = SomeObject->new();
    $obj->set_columns(...);

=head1 DESCRIPTION

I<AlignDB::SQL> represents an SQL statement.

Most codes come from Data::ObjectDriver::SQL

t/01.sql.t  view on Meta::CPAN

$stmt = ns();
$stmt->add_where(%terms);
is( strip( $stmt->as_sql_where ),
    "WHERE ((foo = ?) AND (foo = ?) AND (foo = ?))" );
$stmt->add_where(%terms);
is( strip( $stmt->as_sql_where ),
    "WHERE ((foo = ?) AND (foo = ?) AND (foo = ?)) AND ((foo = ?) AND (foo = ?) AND (foo = ?))"
);

$stmt = ns();
$stmt->add_select( foo => 'foo' );
$stmt->add_select('bar');
$stmt->from( [qw( baz )] );
is( strip( $stmt->as_sql ), "SELECT foo, bar FROM baz" );

$stmt = ns();
$stmt->add_select( 'f.foo'    => 'foo' );
$stmt->add_select( 'COUNT(*)' => 'count' );
$stmt->from( [qw( baz )] );
is( strip( $stmt->as_sql ), "SELECT f.foo, COUNT(*) count FROM baz" );
my $map = $stmt->select_map;
is( scalar( keys %$map ), 2 );
is( $map->{'f.foo'},      'foo' );
is( $map->{'COUNT(*)'},   'count' );

# HAVING
$stmt = ns();
$stmt->add_select( foo        => 'foo' );
$stmt->add_select( 'COUNT(*)' => 'count' );
$stmt->from( [qw(baz)] );
$stmt->add_where( foo => 1 );
$stmt->group( { column => 'baz' } );
$stmt->order( { column => 'foo', desc => 'DESC' } );
$stmt->limit(2);
$stmt->add_having( count => 2 );

is( strip( $stmt->as_sql ),
    "SELECT foo, COUNT(*) count FROM baz WHERE (foo = ?) GROUP BY baz HAVING (COUNT(*) = ?) ORDER BY foo DESC LIMIT 2"
);

t/02.replace.t  view on Meta::CPAN

    $_[0] =~ s/\s+/ /g;
    $_[0] =~ s/ $//s;
    return $_[0];
}

my $stmt = ns();
ok( $stmt, 'Created SQL object' );

# Replace
$stmt = ns();
$stmt->add_select( foo        => 'foo' );
$stmt->add_select( 'COUNT(*)' => 'count' );
$stmt->from( [qw(baz)] );
$stmt->add_where( foo => 1 );
$stmt->group( { column => 'baz' } );
$stmt->order( { column => 'foo', desc => 'DESC' } );
$stmt->limit(2);
$stmt->add_having( count => 2 );
$stmt->replace( { foo => 'foobar', baz => 'barbaz' } );
is strip($stmt->as_sql),
    "SELECT foobar, COUNT(*) count FROM barbaz WHERE (foobar = ?) GROUP BY barbaz HAVING (COUNT(*) = ?) ORDER BY foobar DESC LIMIT 2";

# Replace with meta-char
$stmt = ns();
$stmt->add_select( 'foo.bar'  => 'foo.bar' );
$stmt->add_select( 'COUNT(*)' => 'count' );
$stmt->from( [qw(baz)] );
$stmt->replace( { 'foo.bar' => 'foo.foobar' } );
is strip($stmt->as_sql), "SELECT foo.foobar, COUNT(*) count FROM baz";

t/04.copy.t  view on Meta::CPAN

sub ns {
    AlignDB::SQL->new;
}

my $stmt = ns();
ok $stmt, 'Created SQL object';

# Replace
$stmt = ns();
$stmt->add_select( foo        => 'foo' );
$stmt->add_select( 'COUNT(*)' => 'count' );
$stmt->from( [qw(baz)] );
$stmt->add_where( foo => 1 );
$stmt->group( { column => 'baz' } );
$stmt->order( { column => 'foo', desc => 'DESC' } );
$stmt->limit(2);
$stmt->add_having( count => 2 );
$stmt->replace( { foo => 'foobar', baz => 'barbaz' } );

my $stmt_copy = $stmt->copy;
is $stmt->as_sql, $stmt_copy->as_sql, "Copy, clone, duplicate or what ever";

t/library/02.freeze.t  view on Meta::CPAN

BEGIN {
    use_ok('AlignDB::SQL::Library');
}
use AlignDB::SQL;

my $temp_lib = "temp.lib";
my $sql_file = AlignDB::SQL::Library->new( lib => $temp_lib );

my $sql = AlignDB::SQL->new();
$sql->select( [ 'id', 'name' ] );
$sql->add_select('bucket_id');
$sql->add_select('note_id');
$sql->from( ['foo'] );
$sql->add_where( 'name', 'fred' );
$sql->add_where( 'bucket_id', { op => '!=', value => 47 } );
$sql->add_where( 'note_id', \'IS NULL' );
$sql->limit(1);

$sql_file->set( 'foobar_query', $sql );
is( $sql_file->retrieve('foobar_query')->as_sql,
    $sql->as_sql, 'Create a new query in the library.' );

t/library/03.thaw.t  view on Meta::CPAN

BEGIN {
    use_ok('AlignDB::SQL::Library');
}
use AlignDB::SQL;

my $temp_lib = "temp.lib";
my $sql_file = AlignDB::SQL::Library->new( lib => $temp_lib );

my $sql = AlignDB::SQL->new();
$sql->select( [ 'id', 'name' ] );
$sql->add_select('bucket_id');
$sql->add_select('note_id');
$sql->from( ['foo'] );
$sql->add_where( 'name', 'fred' );
$sql->add_where( 'bucket_id', { op => '!=', value => 47 } );
$sql->add_where( 'note_id', \'IS NULL' );
$sql->limit(1);

my $retr_sql = $sql_file->retr('foobar_query');
my $thaw_sql = AlignDB::SQL->thaw($retr_sql);
is( $thaw_sql->as_sql, $sql->as_sql, 'Retrieved query from library.' );