NAME

DBIx::Simple::Procedure - An Alternative To SQL Stored Procedures using DBIx::Simple

VERSION

Version 1.61

SYNOPSIS

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::Procedure 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.

Here is an example of how to setup and process a (sql) text file.

# DBIx::Simple::Procedure uses DBIx::Simple and provides an accessor through the
DBIx::Simple::Procedure->{dbix} hash reference.

use DBIx::Simple::Procedure;
my $db = DBIx::Simple::Procedure->new($path_to_sqlfiles, 'dbi:SQLite:dbname=file.dat');
# Will error out using DBIx::Simple if a connection error occurs.

# The queue function takes one parameter (a text file) that contains DBIx::Simple::Procedure
# sql commands.

# The process_queue function processes all queued sql statements using the parameters passed
# to it, similar to the execute function of DBI.

$db->queue($sql_file)->process_queue(@sql_parameters, {other_param_a => 'excepts_hashrefs_also'});

# The cache function returns an array of resultsets, or the resultset of the index passed,
# return by the select statements encountered in the sql file.
# Note! files included using the "include" command will not have there resultsets cached,
# even if a "capture" command is encountered, only the select statement(s) found in the
# initial sql file are cached in the order they are encountered. 

foreach my $result (@{$db->cache(0)}){
    # do something with the records of the first resultset
    $result->{...};
}

SQL FILE SYNTAX

The (sql) procedural text file to be processed may contain any text you desire, e.g. comments and other markup. DBIx::Simple::Procedure only reacts to command lines (commands). These instructions (or commands) must be placed on its own line and be prefixed with an exclamation point, a space, the command, another space, and the statement to be evaluated.

E.g. "! execute select * from foo".

Multiple commands can be used in a single sql file. Note! multi-line sql statements not supported in this release.

SQL File Commands:
    
! execute:
    This command simply execute the supplied sql statement.

! capture:
    This is an execute command who's dataset will be cached (stored) for later use.
    Note! This command can only be used with a select statement.

! replace:
    This is an execute command that after successfully executed, replaces the scope
    parameters with data from the last row in its dataset. Note! This command can
    only be used with a select statement.

! include:
    This command processes the supplied sql file in a sub transaction. Note! Included
    sql file processing is isolated from the current processing. Any capture commands
    encountered in the included sql files will not cache the dataset.

! proceed:
    This command should be read "proceed if" because it evaluates the string passed
    (perl code) for truth, if true, it continues if false it skips to the next proceed
    command or until the end of the sql file.

! ifvalid: [validif:]
    This command is a synonym for proceed.

! storage:
    This command does absolutely nothing except store the sql statement in the commands
    list (queue) for processing individually from within the perl code with a method
    like process_command.

! declare:
    This command is effectively equivalent to the select .. into sql sytax and uses an
    sql select statement to add vairables to the scope for processing
    (e.g. ! declare select `name` from `foo` where `id` = $0) can be used in other
    instructions as $!name, e.g. ! execute update `foo` set `name` = $!name where `id` = $0.

! forward:
    This command takes an index and jumps to that command line and continues from there.
    Similar to a rewind or fast forward function for the command queue.

! process:
    This command takes an index and executes that command line.

! perl -e:
    This command passes the statement to perl's eval function which can evaluate perl code
    and even runtime variables.

! examine:
    This command is used for debugging, it errors out with the compiled statement passed to it.

INCLUDED EXAMPLE

Inside tables/users/getall (.sql omitted purposefully).

# mysql example

# this command tell DSP to treat passed in parameters that are left blank
# as integers with a value of zero, by default it is '' (an empty string),
# it can also be null
! setting blank as zero

# does what it says, no special magic here
! execute create table if not exists `group` (`id` int(11) auto_increment, `info` varchar(255) not null, primary key(`id`) )
! execute truncate table `group`

# the declare command stores the list of values in the custom parameters hash
# using the column names as keys
! declare select '0' as `count`
! execute insert into `group` values (null, concat_ws(' ', 'I typed', $0, ($!count + 1), 'times.'))

# hopefully not often but there are times when the sql command file should
# loop, evakuate, etc

# validif, and ifvalid are synonyms for proceed reads a perl expression which
# may contain passed or declared parameters, and if true proceeds to the next
# command, not false, skip every following command until it reached a proceed,
# ifvalid, or validif command the evalutes true, or reaches the end of the file

# begin loop
! declare select count(*) as `count` from `group`
! validif $!count < 5
! forward 4
! ifvalid 1
# end loop

# capture store the returned data in the sets array which contains all resultsets
# returned by the encountered capture commands
! capture select * from `group`

Inside the test.pl script.

#!/usr/env/perl -w

BEGIN {
    use FindBin;
    use lib "$FindBin::Bin/lib";
}

use DBIx::Simple::Procedure;

# connecting to a mysql database
my $fs = "$FindBin::Bin/sql/";
my $db = DBIx::Simple::Procedure->new(
    $fs,
    'dbi:mysql:database=test', # dbi source specification
    'root', '',                     # username and password
);

$db->queue('tables/users/getall')->process_queue('this is a test');

foreach my $result (@{$db->cache(0)}){
    print "$result->{info}\n"; # database column
}
 
print "\nDone. Found " . ( @{$db->cache(0)} || 0 ) . " records";

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 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].

MORE SQL COMMAND FILE EXAMPLES

Replacing passed in parameters.

... in script
$dsp->queue(...)->process_queue('this', 'that');
... in sql file
! replace select 'baz' as `foo`
# $0 in the sql command file was 'this' and now has the value 'baz'
# $1 in the sql command file was 'that' and now has no value or is undefined
# basically, the replace command overwrites @_ (all passed in values)

Alternatively...

... in script
$dsp->queue(...)->process_queue('this', 'that', { foo => 'the', bar => 'other'});
... in sql file
! declare select 'baz' as `foo`
# $0, and $1 in the sql command file are left untouched
# $!foo in the sql command file was 'the' and now has the value 'baz'
# basically, the declare command updates or appends the internal (custom parameters) hash

Using the storage, forward and process commands.

... in script
$dsp->queue(...)->process_queue({'foo' => 'bar'});
... in sql file
! forward 2
! execute insert into `foo` (NULL, 'bar', 'baz')
! execute insert into `foo` (NULL, 'bar', 'baz')
! process 1
! capture select * from `foo`

# this sql file is intentionally meant to be confusing, the insert query will
# be executed 2 times, as a test, see if you can figure out why

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.

METHODS

new

The new method initializes a new DBIx::Simple and DBIx::Simple::Procedure object and accepts all parameters required/accepted by DBIx::Simple.