SQL::Query - Sql query generator for database entities.

SQL-Entity documentation Contained in the SQL-Entity distribution.

Index

Code Index:

NAME

Top

SQL::Query - Sql query generator for database entities.

SYNOPSIS

Top

    my $entity = SQL::Entity->new(
        name                  => 'emp',
        unique_expression     => 'rowid',
        columns               => [
            sql_column(name => 'ename'),
            sql_column(name => 'empno'),
            sql_column(name => 'deptno')
        ],
        indexes => [
            sql_index(name => 'foo', columns => ['empno', hint => 'INDEX_ASC(emp FORCE_ORDER)'])
        ].
        order_index => 'foo',
    );

    my $query = SQL::Query->new(entity => $entity);
    my ($sql, $bind_variables) = $query->query();

DESCRIPTION

Top

Generates sql for entity definition,

- navigation feature (limit/offset)

- ad hoc query optimialization (for where condition uses (subquerties or join dynamically))

Lets into account the following use cases:

    1, Retrieve employers with thier department:
    Physically it requries retrieving N rows starts from X row (offset/limit),
    so assuming that you have to fetch physically only 50 rows at the time
    from a few millions rows subquery will executes 50 times in the outer results.
    It may be faster the the join clause, especially when using single table CBO hint that
    is forcing result order rather then ORDER BY ...

    2, Retrieve employers with thier department for departaments 'hr','ho' :

EXPORT

None.

ATTRIBUTES

limit
offset
dialect
entity
_entity_limit_wrapper

Stores entity object that is wrapped by limit implementation.

METHODS

initialise
query

Returns sql statement and bind variables array ref. Takes optionally array ref of the requested columns(undef return all columns in projection), condition object,

set_sql_template_parameters
order_index

Change order_index for associated entity

load_module

Loads specyfic limit module wrapper.

SEE ALSO

Top

SQL::Entity SQL::Entity::Column SQL::Entity::Condition

COPYRIGHT AND LICENSE

Top

The SQL::Query module is free software. You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file.

AUTHOR

Top

Adrian Witas, adrian@webapp.strefa.pl

SQL-Entity documentation Contained in the SQL-Entity distribution. 
package SQL::Query;

use strict;
use warnings;
use vars qw($VERSION);

$VERSION = 0.01;

use Abstract::Meta::Class ':all';
use SQL::Entity::Column ':all';


has '$.limit' => (default => '20');



has '$.offset' => (default => '1');



has '$.dialect' => (default => 'Oracle');



has '$.entity' => (associated_class => 'SQL::Entity');




has '$._entity_limit_wrapper';



sub initialise {
    my ($self) = @_;
    my $class = $self->load_module($self->dialect);
    my $entity_manager;
    my $entity = $self->entity->clone;
    $self->_entity_limit_wrapper(bless $entity, $class);
}





sub query {
    my ($self, $requested_columns, $condition) = @_;
    my $wrapper = $self->_entity_limit_wrapper;
    my ($sql, $bind_variables) = $wrapper->query($self->offset, $self->limit, $requested_columns, $condition);
    ($sql, $bind_variables);
}



sub set_sql_template_parameters {
    my ($self, $value) = @_;
    $self->_entity_limit_wrapper->set_sql_template_parameters($value);
}


sub order_index {
    my ($self, $index_name) = @_;
    $self->_entity_limit_wrapper->set_order_index($index_name);
}



{
    my %loaded_modules = ();
    sub load_module {
        my ($self, $module) = @_;
        my $module_name = __PACKAGE__ . "::Limit::\u$module";
        return $loaded_modules{$module_name} if $loaded_modules{$module_name};
        my $module_to_load =  $module_name;
        $module_to_load =~ s/::/\//g;
        eval { require "${module_to_load}.pm" };
        $loaded_modules{$module_name} = $module_name;
        $module_name;
    }
}


1;

__END__