DBIx-Custom / lib / DBIx / Custom / QueryBuilder.pm /
81d2da9 14 years ago
1 contributor
315 lines | 8.398kb
package DBIx::Custom::QueryBuilder;

use strict;
use warnings;

use base 'Object::Simple';

use Carp 'croak';
use DBIx::Custom::Query;
use DBIx::Custom::QueryBuilder::TagProcessor;

__PACKAGE__->dual_attr('tag_processors', default => sub { {} }, inherit => 'hash_copy');
__PACKAGE__->register_tag_processor(
    '?'      => \&DBIx::Custom::QueryBuilder::TagProcessors::expand_placeholder_tag,
    '='      => \&DBIx::Custom::QueryBuilder::TagProcessors::expand_equal_tag,
    '<>'     => \&DBIx::Custom::QueryBuilder::TagProcessors::expand_not_equal_tag,
    '>'      => \&DBIx::Custom::QueryBuilder::TagProcessors::expand_greater_than_tag,
    '<'      => \&DBIx::Custom::QueryBuilder::TagProcessors::expand_lower_than_tag,
    '>='     => \&DBIx::Custom::QueryBuilder::TagProcessors::expand_greater_than_equal_tag,
    '<='     => \&DBIx::Custom::QueryBuilder::TagProcessors::expand_lower_than_equal_tag,
    'like'   => \&DBIx::Custom::QueryBuilder::TagProcessors::expand_like_tag,
    'in'     => \&DBIx::Custom::QueryBuilder::TagProcessors::expand_in_tag,
    'insert' => \&DBIx::Custom::QueryBuilder::TagProcessors::expand_insert_tag,
    'update' => \&DBIx::Custom::QueryBuilder::TagProcessors::expand_update_tag
);

__PACKAGE__->attr(tag_start => '{');
__PACKAGE__->attr(tag_end   => '}');

__PACKAGE__->attr('tag_syntax' => <<'EOS');
[tag]                     [expand]
{? NAME}                  ?
{= NAME}                  NAME = ?
{<> NAME}                 NAME <> ?

{< NAME}                  NAME < ?
{> NAME}                  NAME > ?
{>= NAME}                 NAME >= ?
{<= NAME}                 NAME <= ?

{like NAME}               NAME like ?
{in NAME number}          NAME in [?, ?, ..]

{insert NAME1 NAME2}      (NAME1, NAME2) values (?, ?)
{update NAME1 NAME2}      set NAME1 = ?, NAME2 = ?
EOS

sub register_tag_processor {
    my $self = shift;
    
    # Merge
    my $tag_processors = ref $_[0] eq 'HASH' ? $_[0] : {@_};
    $self->tag_processors({%{$self->tag_processors}, %{$tag_processors}});
    
    return $self;
}

sub build_query {
    my ($self, $source)  = @_;
    
    # Parse
    my $tree = $self->_parse($source);
    
    # Build query
    my $query = $self->_build_query($tree);
    
    return $query;
}

sub _parse {
    my ($self, $source) = @_;
    
    $source ||= '';
    
    my $tree = [];
    
    # Tags
    my $tag_start = quotemeta $self->tag_start;
    my $tag_end   = quotemeta $self->tag_end;
    
    # Tokenize
    my $state = 'text';
    
    # Save original
    my $original = $source;
    
    # Parse
    while ($source =~ s/([^$tag_start]*?)$tag_start([^$tag_end].*?)$tag_end//sm) {
        my $text = $1;
        my $tag  = $2;
        
        # Parse tree
        push @$tree, {type => 'text', tag_args => [$text]} if $text;
        
        if ($tag) {
            # Get tag name and arguments
            my ($tag_name, @tag_args) = split /\s+/, $tag;
            
            # Tag processor is exist?
            unless ($self->tag_processors->{$tag_name}) {
                my $tag_syntax = $self->tag_syntax;
                croak qq{Tag "{$tag}" is not registerd.\n\n} .
                      "<SQL builder syntax>\n" .
                      "$tag_syntax\n" .
                      "<Your source>\n" .
                      "$original\n\n";
            }
            
            # Check tag arguments
            foreach my $tag_arg (@tag_args) {
            
                # Cannot cantain placehosder '?'
                croak qq{Tag cannot contain "?"}
                  if $tag_arg =~ /\?/;
            }
            
            # Add tag to parsing tree
            push @$tree, {type => 'tag', tag_name => $tag_name, tag_args => [@tag_args]};
        }
    }
    
    # Add text to parsing tree 
    push @$tree, {type => 'text', tag_args => [$source]} if $source;
    
    return $tree;
}

sub _build_query {
    my ($self, $tree) = @_;
    
    # SQL
    my $sql = '';
    
    # All Columns
    my $all_columns = [];
    
    # Build SQL 
    foreach my $node (@$tree) {
        
        # Get type, tag name, and arguments
        my $type     = $node->{type};
        my $tag_name = $node->{tag_name};
        my $tag_args = $node->{tag_args};
        
        # Text
        if ($type eq 'text') {
            # Join text
            $sql .= $tag_args->[0];
        }
        
        # Tag
        elsif ($type eq 'tag') {
            
            # Get tag processor
            my $tag_processor = $self->tag_processors->{$tag_name};
            
            # Tag processor is code ref?
            croak qq{Tag processor "$tag_name" must be code reference}
              unless ref $tag_processor eq 'CODE';
            
            # Expand tag using tag processor
            my ($expand, $columns) = @{$tag_processor->($tag_args)};
            
            # Check tag processor return value
            croak qq{Tag processor "$tag_name" must return [\$expand, \$columns]}
              if !defined $expand || ref $columns ne 'ARRAY';
            
            # Check placeholder count
            croak qq{Count of Placeholder must be same as count of columns in "$tag_name"}
              unless $self->_placeholder_count($expand) eq @$columns;
            
            # Add columns
            push @$all_columns, @$columns;
            
            # Join expand tag to SQL
            $sql .= $expand;
        }
    }
    
    # Add semicolon
    $sql .= ';' unless $sql =~ /;$/;
    
    # Query
    my $query = DBIx::Custom::Query->new(sql => $sql, columns => $all_columns);
    
    return $query;
}

sub _placeholder_count {
    my ($self, $expand) = @_;
    
    # Count
    $expand ||= '';
    my $count = 0;
    my $pos   = -1;
    while (($pos = index($expand, '?', $pos + 1)) != -1) {
        $count++;
    }
    return $count;
}

1;

=head1 NAME

DBIx::Custom::QueryBuilder - Query builder

=head1 SYNOPSIS
    
    my $builder = DBIx::Custom::QueryBuilder->new;
    
    my $source = "select from table {= k1} && {<> k2} || {like k3}";
    my $param = {k1 => 1, k2 => 2, k3 => 3};
    
    my $query = $sql_builder->build_query($source);

=head1 ATTRIBUTES

=head2 C<tag_processors>

    my $tag_processors = $builder->tag_processors;
    $builder           = $builder->tag_processors(\%tag_processors);

Tag processors.

=head2 C<tag_start>
    
    my $tag_start = $builder->tag_start;
    $builder      = $builder->tag_start('{');

String of tag start.
Default to '{'

=head2 C<tag_end>
    
    my $tag_end = $builder->tag_start;
    $builder    = $builder->tag_start('}');

String of tag end.
Default to '}'
    
=head2 C<tag_syntax>
    
    my $tag_syntax = $builder->tag_syntax;
    $builder       = $builder->tag_syntax($tag_syntax);

Tag syntax.

=head1 METHODS

This class is L<Object::Simple> subclass.
You can use all methods of L<Object::Simple>

=head2 C<new>

    my $builder = DBIx::Custom::SQLBuilder->new;
    my $builder = DBIx::Custom::SQLBuilder->new(%attrs);
    my $builder = DBIx::Custom::SQLBuilder->new(\%attrs);

Create a instance.

=head2 C<build_query>
    
    my $query = $builder->build_query($source);

Build L<DBIx::Custom::Query> object.

B<Example:>

Source:

    my $query = $builder->build_query(
      "select * from table where {= title} && {like author} || {<= price}")

Query:

    $qeury->sql : "select * from table where title = ? && author like ? price <= ?;"
    $query->columns : ['title', 'author', 'price']

=head2 C<register_tag_processor>

    $builder = $builder->register_tag_processor($tag_processor);

Register tag processor.

    $builder->register_tag_processor(
        '?' => sub {
            my $args = shift;
            
            # Do something
            
            # Expanded tag and column names
            return ($expand, $columns);
        }
    );

Tag processor receive arguments in tags
and must return expanded tag and column names.

=head1 Tags

    {? NAME}    ->   ?
    {= NAME}    ->   NAME = ?
    {<> NAME}   ->   NAME <> ?
    
    {< NAME}    ->   NAME < ?
    {> NAME}    ->   NAME > ?
    {>= NAME}   ->   NAME >= ?
    {<= NAME}   ->   NAME <= ?
    
    {like NAME}       ->   NAME like ?
    {in NAME COUNT}   ->   NAME in [?, ?, ..]
    
    {insert NAME1 NAME2 NAME3}   ->   (NAME1, NAME2, NAME3) values (?, ?, ?)
    {update NAME1 NAME2 NAME3}   ->   set NAME1 = ?, NAME2 = ?, NAME3 = ?