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

'host'		=> MySql host [default=localhost]
'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

cpanm App::Framework

perl -MCPAN -e shell
install App::Framework