NAME
Sql - MySql interface
SYNOPSIS
use App::Framework::Base::Sql ;
DESCRIPTION
Provides a simplified interface to MySQL via DBI.
DIAGNOSTICS
Setting the debug flag to level 1 prints out (to STDOUT) some debug messages, setting it to level 2 prints out more verbose messages.
AUTHOR
Steve Price <sdprice@sdprice.plus.com>
BUGS
None that I know of!
NOTE: To avoid the common "Mysql server gone away" problem, everywhere that I get the database connection handle, I actually call the connect() method to ensure the connection is working.
INTERFACE
Fields
'database'
=> Database name (required)
'table'
=> Table name
'user'
=> User name (required)
'password'
=> Password (required)
'trace'
=> Sql debug trace level (
default
=0)
'trace_file'
=> If specified, output trace information to file (
default
=STDOUT)
'prepare'
=> HASH
ref
to one or more STH definitions (as required by L<sth_create()>)
Each HASH entry is of the form:
'name'
=> (specification as per L<sth_create()>)
Where
'name'
is the name used
when
the STH is created
%CMD_SQL - Parse control hash
Variables get created with the name
*
$sqlvar_
<context>
where <context> is the hash key. This created variable contains the sql for this command or option.
If the control hash entry contains a 'vals' entry, then the following variable is created:
*
@sqlvar_
<context>
This will be a text string containing something like "@sqlvar_select_vals,@sqlvar_where_vals" i.e. a comma seperated list of references to other arrays. These values will be expanded into a real array before use in the sql prepare.
Also, as each entry is processed, extra variables are created:
*
$sqlvar_
<context>_prefix - Prefix string
for
this entry
*
$sqlvar_
<context>_format - Just the same as sqlvar_<context>
Specification variables
This control hash is used to direct processing of the SQL specification passed to sth_create(). If the spec contains a 'vars' field then these additional variables are created in the context:
*
$sqlvar_
<context>_varlist - List of the
'vars'
in the
format
`var`, `var` ..
*
$sqlvar_
<context>_andlist - List of the
'vars'
in the
format
`var` AND `var` ..
*
$sqlvar_
<context>_varlist - List of the
'vars'
in the
format
`var`=?, `var`=? ..
If the spec has a 'vals' entry, then these are pushed on to an ARRAY ref and stored in:
*
@sqlvar_
<context>_vals
@sqlvar_<context>_vals = Real ARRAY ref (provided by the spec) @sqlvar_<context> = String in the format "@sqlvar_select_vals,@sqlvar_where_vals" (provided by parse control hash)
App::Framework::Base::Sql->new([%args])
-
Create a new Sql object.
The %args are specified as they would be in the set method, for example:
'mmap_handler'
=>
$mmap_handler
The full list of possible arguments are :
'fields'
=> Either ARRAY list of valid field names, or HASH of field names
with
default
values
App::Framework::Base::Sql->init_class([%args])
-
Initialises the Sql object class variables.
Object->set(%args)
-
Set one or more settable parameter.
The %args are specified as a hash, for example
set(
'mmap_handler'
=>
$mmap_handler
)
Sets field values. Field values are expressed as part of the HASH (i.e. normal field => value pairs).
Sql->prepare($prepare_href)
-
Use HASH ref to create 1 or more STHs
Sql->trace(@args)
-
Change trace level
Sql->trace_file(@args)
-
Change trace file
Sql->connect(%args)
-
Connects to database. Either uses pre-set values for user/password/database, or can use optionally specified args
Sql->disconnect()
-
Disconnect from database (if connected)
Sql->sth_create($name, $spec)
-
Prepare a named SQL query & store it for later execution by query_sth()
Name is saved as $name. Certain names are 'special':
ins* - Create an
'insert'
type command
upd* - Create an
'update'
type command
sel* - Create a
'select'
type command
check* - Create a
'select'
type command
The $spec is either a SCALAR or HASH ref
If $spec is a SCALAR then it is in the form of sql. Note, when the query is executed the values (if required) must be specified.
If $spec is a HASH ref then it can contain the following fields:
'cmd'
=> Command type:
'insert'
,
'update'
,
'select'
'vars'
=> ARRAY
ref
list of variable names (used
for
'insert'
,
'update'
)
'vals'
=> Provides
values
to be used in the query (
no
extra
values
need to be specified). HASH
ref
or ARRAY
ref
.
HASH
ref
- the hash is used to look up the
values
using the
'vars'
names
ARRAY
ref
- list of
values
(or refs to
values
)
NOTE: If insufficient
values
are provided
for
the query, then the remaining
values
must be specified in the query call
'sql'
=> Sql string.
NOTE: Depending on the command type,
if
the command is not specified then a
default
will be prepended to this string.
'table'
=> Overrides the object table setting
for
this query
'limit'
=> Sets the limit on the number of results
'group'
=> Specify group by string
'where'
=> Where clause. String or HASH
ref
.
String - specify sql
for
where clause (can omit
'WHERE'
prefix)
HASH
ref
- specify where clause as HASH:
'sql'
=> Used to specify more complicated where clauses (e.g.
'`pid`=? AND `channel`=?'
)
'vars'
=> ARRAY
ref
list of variable names (used
for
'where'
). If
no
'sql'
is specified, then the where clause
is created by ANDing the vars together (e.g. [
qw/pid channel/
] gives
'`pid`=? AND `channel`=?'
)
'vals'
=> Provides
values
to be used in the query (
no
extra
values
need to be specified). HASH
ref
or ARRAY
ref
.
EXAMPLES
The following are all (almost) equivalent:
$sql
->sth_create(
'check'
, {
'table'
=>
'$table'
,
'limit'
=> 1,
'where'
=> {
'sql'
=>
'`pid`=? AND `channel`=?'
,
'vars'
=> [
qw/pid channel/
],
'vals'
=> \
%sql_vars
}) ;
$sql
->sth_create(
'check2'
, {
'table'
=>
'$table'
,
'limit'
=> 1,
'where'
=>
'`pid`=? AND `channel`=?'
,
# need to pass in extra params to query method
}}) ;
$sql
->sth_create(
'check3'
,
"SELECT * FROM `$table` WHERE `pid`=? AND `channel`=? LIMIT 1"
) ;
$sql
->sth_create(
'select'
,
"WHERE `pid`=? AND `channel`=? LIMIT 1"
) ;
They are then used as:
$sql
->sth_query(
'check'
) ;
# already given it's parameters
$sql
->sth_query(
'check2'
,
$pid
,
$channel
) ;
$sql
->sth_query(
'check3'
,
$pid
,
$channel
) ;
$sql
->sth_query(
'select'
,
$pid
,
$channel
) ;
Sql->sth_query($name, [@vals])
-
Use a pre-prepared named sql query to return results. If the query has already been given a set of values, then use them; otherwise use the values specified in this call (or append the values to an insufficient list of values given when the sth was created)
Sql->sth_query_all($name, [@vals])
-
Use a pre-prepared named sql query to return results. Return all results in array.
Sql->query($query [, @vals])
-
Query database
Sql->query_all($query)
-
Query database - return array of complete results, each entry is a hash ref
Sql->do($sql)
-
Do sql command
Sql->do_sql_text($sql_text)
-
Process the SQL text, split it into one or more SQL command, then execute each of them
Sql->next([$name])
-
Returns hash ref to next row (as a result of query). Uses prepared STH name $name (as created by sth_create method), or default name (as created by query method)
Sql->tables()
-
Returns list of tables for this database
Sql->datestr_to_sqldate($datestr)
-
Convert standard date string (d-MMM-YYYY) to SQL based date (YYYY-MM-DD)
Sql->sqldate_to_date($sql_date)
-
Convert SQL based date (YYYY-MM-DD) to standard date string (d-MMM-YYYY)
Sql->_sql_cmd($name)
-
Convert $name into a sql command if possible
Sql->_sql_setvars($context, $spec, $vars_href)
-
Set/add variables into the $vars_href HASH driven by the specification $spec (which may be a sql string or a HASH specification). Creates the variables in the namespace defined by the $context string (which is usually the lookup string into the %CMD_SQL table)
Sql->_sql_expand_vars($vars_href)
-
Expand all the variables in the HASH ref
Sql->_sql_expand_arrays($vars_href)
-
Expand all the array variables in the HASH ref
Sql->_sql_expand_array($arr, $vars_href)
-
Expand the named array
Sql->_sth_record($name)
-
Returns the saved sth information looked up from $name; returns undef otherwise
Sql->_sth_record_sth($name)
-
Returns the saved sth looked up from $name; returns undef otherwise
Sql->_set_trace($dbh, $trace, $trace_file)
-
Update trace level
1 POD Error
The following errors were encountered while parsing the POD:
- Around line 256:
=over without closing =back