NAME
DBIx::Composer - Composes and runs SQL statement.
SYNOPSIS
use DBIx::Composer
$cmd = new DBIx::Composer();
$cmd->{table} = 'table1';
$cmd->{fields} = 'name, email';
$cmd->{where} = "where login = 'peter'";
print $cmd->compose_select;
# Prints "select name, surname from table1 where login = 'peter'"
$dbh = open_database(); # Open database yourself
use DBIx::Composer
$cmd = new DBIx::Composer(dbh=>$dbh, debug=> 1);
$cmd->{table} = 'table1';
$cmd->{fields} = 'login, name, email';
$cmd->{values} = "'john', 'John Smith', 'john@smith.com'";
$cmd->insert();
# Executes command "insert into table1 (login, name, email) values
# ('john', 'John Smith', 'john@smith.com')"
# Prints this command on STDERR before execution.
DESCRIPTION
This module helps you to compose and run SQL statements. First you
create new object and fill its hash for common parts of SQL statements.
Then you may either compose SQL statement from these parts or both
compose and execute it.
USAGE
You connect to database using your favorite method of connection and
supply DBIx::Composer object with standard database handler $dbh. If you
don't plan to execute statements, you may omit connection to database.
So, after creating new object you set its parameters, or SQL command
parts. Modifiers for command, such as "where ...", "order ...", "limit
..." must be full modifiers like "where a=b", not only "a=b".
You don't need to prepare() SQL fetch statements - they are prepared
internally. You cant execute statements right after setting their parts
- the module checks whether command has been composed, prepared and
executed. Because of such behaviour don't try to reset command parts
after executing, but better create new DBIx::Composer object.
Command parts
Valid command parts are:
table - table name.
Examples:
$cmd->{table} = 'table1'
$cmd->{table} = 'db left join user using (host)';
fields - fields to select, insert, update, etc.
Examples:
$cmd->{fields} = 'login, email, tries + 5';
$cmd->{fields} = 'curdate(), now()';
$cmd->{fields} = 'ip, traf_in+thaf_out as sum';
where
Examples:
$cmd->{where} = 'login = peter';
$cmd->{where} = 'tries > limit + 2';
order
Examples:
$cmd->{order} = "order by ip desc";
limit
Examples:
$cmd->{limit} = "limit 20";
$cmd->{limit} = "limit 100, 20";
Opening database
DBIx::Composer doesn't touch opening database. You should open it
yourself somewhere else in your program. As for me, I use special
function open_database() like this:
#====================
sub open_database {
#====================
# Read config from some file
my ($db_db, $db_login, $db_passwd, $debug) = read_config();
$driver_name = "dbi:mysql:$db_db";
# Connect to database
$dbh = DBI->connect
($driver_name, $db_login, $db_passwd)||
die $DBI::errstr;
# Initialize DBIx::Composer
$DBIx::Composer::DBH = $dbh;
$DBIx::Composer::DEBUG = $debug;
return $dbh; # returns true on success
}
Then in your program you don't need to set $dbh anymore, so you can
simply write:
$cmd = new $DBIx::Composer;
and $cmd knows yet about dbh handler and debug level.
You have to set $dbh in new() explicitly only if you want to make two or
more database connections in one program, e.g. on migrating from old
database to new one.
Debug levels
If debug level is set to 1 or more, the module prints SQL commands to
STDERR (for cgi scripts it's error_log of web server).
If debug level >= 1, it prints executed statements before executing
them.
If debug level >= 2, it prints composed statements after composing them.
So usually (not always) on level 2 you have 2 lines in STDERR - of
composing and of executing.
Debug line starts from prefix ("SQL run" or "SQL debug") and program
name (it's convenient for cgi-bin's).
Things you must pay attention to
*
In order to avoid confusing, don't reuse DBIx::Composer objects after
executing them, but simply create new object. It's because the module
remembers its state and don't composes statement again. Probably such
behaviour will change partially in future.
*
Command parts 'where', 'limit', 'order' must be started from these
words, that is, 'where a=b', not 'a=b'; 'order by c desc', not 'c desc'
or 'by c desc'. You may have in your mind the string "select $fields
from $table $where".
REQUIRES
Perl 5.6.1 (for lvalue functions)
ExtUtils::MakeMaker - for installation
Test::More - for installation tests
DBI etc. - if you want to execute statements
BUGS
I have tested it only in environment with MySQL database and localhost
as database host.
Installation tests doesn't check that it works on real database - they
only check that the module composes statements.
AUTHOR
Igor Plisco
igor at plisco dot ru
http://plisco.ru/soft
COPYRIGHT
This program is free software; you can redistribute it and/or modify it
under the same terms as Perl itself.
The full text of the license can be found in the LICENSE file included
with this module.
SEE ALSO
perl(1).
FUNCTION DESCRIPTIONS
new
Purpose : Creates new DBIx::Object object.
Usage : DBIx::Composer->new()
Returns : Object handler.
Argument : Nothing or dbh handler or config.
Throws : None.
Comments : No.
See Also : N/A
debug_level
Usage : DBIx::Composer->debug_level()
Purpose : Sets default debug level for all newly created objects.
Returns : current value of debug level
Argument : Debug level as integer.
Throws : None.
Comments : Warning: now returns global $dbh, not local for object.
See Also :
dbh
Usage : DBIx::Composer->dbh()
Purpose : Sets default database handler for all newly created objects.
Returns : current value of $dbh
Argument : database handler
Throws : None.
Comments : Warning: now returns global $dbh, not local for object.
See Also :
quote
Usage : DBIx::Composer->quote()
Purpose : Quotes its value by calling $dbh->quote.
Returns : Quoted string
Argument : String or number to quote
Throws : None.
Comments : None.
See Also :
compose_select
Usage : $cmd->compose_select()
Purpose : Composes select statement for given object.
Returns : Composed statement
Argument : None.
Throws : Returns undef, if required fields are missed.
Comments : Composes "select $fields from $table $where $order $limit"
statement where $fields stands for $cmd->fields and so on.
$where, $group, $order and $limit are optional.
See Also :
compose_insert
Usage : $cmd->compose_insert()
Purpose : Composes insert statement for given object.
Returns : Composed statement
Argument : None.
Throws : Returns undef, if required fields are missed.
Comments : Composes "insert into $table ($fields) values ($values)"
or "insert into $table values ($values)" if $fields omitted.
See Also :
compose_replace
Usage : $cmd->compose_replace()
Purpose : Composes replace statement for given object.
Returns : Composed statement
Argument : None.
Throws : Returns undef, if required fields are missed.
Comments : Composes "replace into $table ($fields) values ($values)"
or "replace into $table values ($values)" if $fields omitted.
See Also :
compose_delete
Usage : $cmd->compose_delete()
Purpose : Composes delete statement for given object.
Returns : Composed statement
Argument : None.
Throws : Returns undef, if required fields are missed.
Comments : Composes "delete from $table $where"
or "delete from $table" if $where omitted.
See Also :
compose_update
Usage : $cmd->compose_update()
Purpose : Composes update statement for given object.
Returns : Composed statement
Argument : None.
Throws : Returns undef, if required fields are missed.
Comments : Composes "update $table $set $where"
or "update $table $set" if $where omitted.
See Also :
selectrow_array
Usage : $cmd->selectrow_array()
Purpose : Makes DBI call of selectrow_array
Returns : Array or scalar
Argument : None.
Throws : Returns undef, if required fields are missed.
Comments :
See Also :
fetch
Usage : $cmd->fetch()
Purpose : Makes DBI call of fetch
Returns : Array of data
Argument : None.
Throws : Returns undef, if error occured. See $dbh->errstr for errors.
Comments : After last row returns undef too.
See Also :
fetchrow_hashref
Usage : $cmd->fetchrow_hashref()
Purpose : Makes DBI call of fetchrow_hashref
Returns : Array of data
Argument : None.
Throws : Returns undef, if error occured. See $dbh->errstr for errors.
Comments : After last row returns undef too.
See Also :
insert
Usage : $cmd->insert()
Purpose : Makes DBI call of insert
Returns : ID of inserted row (as "last insert id").
Argument : None.
Throws : Returns undef, if required fields are missed.
Comments :
See Also :
replace
Usage : $cmd->replace()
Purpose : Makes DBI call of replace
Returns : ID of replaced row (as "last insert id").
Argument : None.
Throws : Returns undef, if required fields are missed.
Comments :
See Also :
delete
Usage : $cmd->delete()
Purpose : Makes DBI call of delete
Returns : 1 if OK, false otherwise.
Argument : None.
Throws : Returns undef, if required fields are missed.
Comments :
See Also :
update
Usage : $cmd->update()
Purpose : Makes DBI call of update
Returns : 1 if OK, false otherwise.
Argument : None.
Throws : Returns undef, if required fields are missed.
Comments :
See Also :
log_cmd
Usage : $cmd->log_cmd()
Purpose : Logs SQL command to STDERR.
Returns : Nothing.
Argument : 1 - output format
Throws : Returns undef, if required fields are missed.
Comments : Should't be called directly. Set flag debug
instead when called new().
See Also :
debug
Usage : $cmd->debug()
Purpose : Logs SQL command to STDERR.
Returns : Nothing.
Argument : None.
Throws : Returns undef, if required fields are missed.
Comments : Should't be called directly. Set flag debug > 0
instead when called new().
See Also :
log
Usage : $cmd->log()
Purpose : Logs SQL command to STDERR.
Returns : Nothing.
Argument : None.
Throws : Returns undef, if required fields are missed.
Comments : Should't be called directly. Set flag debug > 1
instead when called new().
See Also :
Functions for access to inner object data as lvalue
Usage : $cmd->table = "users"; or: $table_sav = $cmd->table;
Purpose : Make access to inner variables without hash curlies.
Comments : Warning: don't work in Perl < 5.6. Use form $cmd->{table} instead.
Currently supported functions:
table()
fields()
values()
where()
set()
order()
group()
limit()