NAME
DBIx::Simple::Batch::Documentation
DESCRIPTION
This module allows your program to process text files containing one or many commands that execute SQL statements sequentially. Please keep in mind that DBIx::Simple::Batch is an alternative to database stored procedures and not a replacement or emulation of them. Essentially it is an interface to execute and return data from multiple queries.
SYNOPSIS
DBIx::Simple::Batch, formerly DBIx::Simple::Procedure, is an alternative to ORM packages and Database Stored Procedures.
QUICK START
DBIx::Simple::Batch is meant to be a viable alternative to SQL stored procedures and ORM packages. DBIx::Simple::Batch attmepts to accomplish this by allowing the developer to stored SQL statements with some logic in text files to be parsed and executed sequentially hence the idea of "simple-batch" processing.
use DBIx::Simple::Batch;
my $db = DBIx::Simple::Batch->new($dir, 'dbi:mysql:test', 'root');
$db->call->file(@parameters);
foreach my $row (@{$db->rs('interesting')}) {
print "$row->{column}\n";
}THE SQL FILE
As stated before, the sql file contains one or more single or multi-line SQL statements. The sql file may also contain one or more directives which controls how the file is processed. The following is a simple example of an sql file.
# this is a comment
# this file is stored in /var/www/newapp/queries
! setname interesting
! capture {
SELECT
fileinfo_id
, fileinfo_author_id
, fileinfo_blog_id
, fileinfo_category_id
, fileinfo_entry_id
, fileinfo_file_path
, fileinfo_url
FROM
mt_fileinfo
WHERE
fileinfo_id = $!id;
}
Now for the explanation, the `setname` directive tells the sql file processor to create a reference to the next `capture`d resultset using the name specified which in this case is (`interesting`). The `capture` directive instructs the processor to store the resultset (if any) in the resultset `cache` for later retrieval. By default, directives can only exist on a single line in an sql file but this is not optimal for SQL statements and readibility, so by using curly brackets (`{...}`), you can now span your commands across multiple lines. Note! The capture command only processes `select` statements.
THE CODE FILE
Now that we have our basic sql file, we need to access those instructions from our Perl code file. The following is an example of how you might execute the sql file above.
my $dir = '/var/www/newapp/queries/';
my $db = DBIx::Simple::Batch->new($dir, 'dbi:mysql:movabletype', 'root');
# the standard way to process and retrieve results
$db->queue($dir."files/get_all')->process_queue({ id => 1 });
my $files = $db->rs('interesting'); # or $db->rs(0);
foreach my $file (@{$files}) {
...
}
$db->clear;
# the object-oriented way to process and retrieve results
# Note! this behavior is not activated unless the path passed to the `new`
# method ends in a valid file extension pattern, wildcards ok, or *.*
my $dir = '/var/www/newapp/queries/*.*';
my $db = DBIx::Simple::Batch->new($dir, 'dbi:mysql:movabletype', 'root');
my $files = $db->files->get_all({id => 1})->rs('interesting');
foreach my $file (@{$files}) {
...
}
$db->clear;
# we just processed sql file /var/www/newapp/queries/files/get_all
SQL FILE PARAMS
Parameters can be passed to the sql file to create more complex queries. These parameters are defined and passed to the sql file accessor or process/process_queue function as follows:
# sql file accessor
$db->call->folder->file(@parameters);
# process/process_queue function
$db->queue('folder/file');
$db->process_queue(@parameters)
# parameters should be scalars or hashrefs when using standard queries
my @parameters = ('this', 'that', {'the_other' => 'and_then_some'});
# SQL::Interp style sql statements are supported also. SQL::Interp
# parameters are hashrefs of hashrefs
my @parameters = ({
where => {
this => 'that',
more => 'less'
}
});
Parameters can be scalars, arrayrefs and/or hashrefs and are used in the sql file as follows (can be mixed). See SQL::Interp for specifics on SQL::Interp style and syntax.
# passed-in scalars
! execute select * from foo where id = $0 and bar = $1
# passed-in hashref
! execute select * from foo where id = $!this and bar = $!that
# sql::interp syntax
! execute select * from foo, $%where
# sql::interp syntax with mix-in
! execute select * from foo, $%where, and id = $!idSQL FILE LOGIC
The logical capabilities of an sql file are far from that of a scripting language or even stored procedure standards, however, the logical constructs that exist are designed to be easier to use, understand, alter and execute. The following are a few examples of how and when to use the logical directives and strategies.
# insert some stuff and return the insert id
! setting blank as null
! execute insert into `foo` (id, name, created) values ($!id, $!name, $!created)
# id and created variables that are blank and set to null
! setname new_user
! capture select max(id), name, email, created from `foo`Not much to see there, a simple insert and select procedure. Now lets try a more plausibile example. Here we will take user input from a standard contact form, log the message and create a user account.
The html page contact form will submit the following parameters:
full_name, title, address, city, state, zip, email, comments
The sql files that will store the form data:
# in file /var/www/newapp/queries/messages/log_message
! execute ...
! declare select $!full_name as `name`, $!email as `email`
! include users/new_user
# in file /var/www/newapp/queries/users/new_user
! setting blank as null
! execute insert into `users` (name, email) values ($!name, $!email)
! setname new_user
! capture select max(id), name, email, created from `foo`PASSED-IN PARAMETERS Vs. CUSTOM PARAMETERS
The difference between (what we refer to as) passed-in parameters and custom parameters is determined by how those values are passed to the process_queue and process_command methods. They are also differentiated by the expressions used to identify them. Technically, passed-in parameters and custom parameters are one in the same. Passed-in parameters are passed to the process_command and process_queue methods as an array of values and are referred to in the sql file using expressions like this: [$0, $1, $2, $3]. Custom parameters are passed to the process_command and process_queue methods as a hash reference and are referred to in the sql file using expressions like this: [$!hashrefkey1, $!hashrefkey2].
IMPORTANT NEWS
NOTE! When using the forward and/or process commands, please be aware that they both take a command line index which means that if your not careful when you update the sql file at a later date, you could be shifting the index which means your sql file will execute but not as you intended.
OTHER DOCUMENTATION
PROJECT
DBIx-Simple-Batch - Alternative to ORM and Stored Procedures
PROJECT DESCRIPTION
DBIx::Simple::Batch is formerly DBIx::Simple::Procedure.
This module allows your program to process text files containing one or many commands that execute SQL statements sequentially. Please keep in mind that DBIx::Simple::Batch is an alternative to database stored procedures and not a replacement or emulation of them. Essentially it is an interface to execute and return data from multiple queries with some logic.
PROJECT POD URL
http://app.alnewkirk.com/pod/projects/dbix/simple/batch/
AUTHOR
Al Newkirk, <awncorp at cpan.org>
BUGS
Please report any bugs or feature requests to Al Newkirk, <awncorp at cpan.org>. I will respond in the order they are processed.
SUPPORT
You can find documentation for this module with the perldoc command.
perldoc DBIx::Simple::Batchor email the developer at:
Al Newkirk, C<< <awncorp at cpan.org> >>ACKNOWLEDGEMENTS
Al Newkirk, <awncorp at cpan.org>
COPYRIGHT & LICENSE
Copyright 2009 Al Newkirk, all rights reserved.
This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
1 POD Error
The following errors were encountered while parsing the POD:
- Around line 218:
L<> starts or ends with whitespace