| App::Framework::Feature::Sql - MySql interface |
Sql - MySql interface
use App::Framework::Feature::Sql ;
Provides a simplified interface to MySQL via DBI.
'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
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>
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)
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
Initialises the Sql object class variables.
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) or (d/M/YY) to SQL based date (YYYY-MM-DD)
=cut
sub datestr_to_sqldate { my $this = shift ; my ($datestr) = @_ ;
my $sqldate ;
#print "datestr_to_sqldate($datestr)\n" ;
if ($datestr =~ m/(\d{2})\-(\d{2})\-(\d{4})/)
{
$sqldate = "$3-$2-$1" ;
#print " + simple : date=$sqldate\n" ;
}
else
{
# Handle d-MMM-YYYY (already copes with d/M/YY)
$datestr =~ s%-%/%g ;
my $date = ParseDate($datestr) ;
$sqldate = UnixDate($date, "%Y-%m-%d") ;
#print " + UnixDate : date=$sqldate\n" ;
}
return $sqldate ;
}
#----------------------------------------------------------------------------
Sql->sqldate_to_date($sql_date)
Convert SQL based date (YYYY-MM-DD) to standard date string (d-MMM-YYYY)
=cut
sub sqldate_to_date { my $this = shift ; my ($sqldate) = @_ ;
my $datestr ;
if ($sqldate =~ m/(\d{4})\-(\d{2})\-(\d{2})/)
{
$datestr = "$3-$2-$1" ;
}
else
{
$sqldate =~ s%-%/%g ;
my $date = ParseDate($sqldate) ;
$datestr = UnixDate($date, "%d-%m-%Y") ;
}
return $datestr ;
}
#----------------------------------------------------------------------------
Sql->sqldate_to_datemanip($sql_date)
Convert SQL based date (YYYY-MM-DD) to a date string suitable for Date::Manip (d/M/YYYY)
=cut
sub sqldate_to_datemanip { my $this = shift ; my ($sqldate) = @_ ;
my $datestr ;
if ($sqldate =~ m/(\d{4})\-(\d{2})\-(\d{2})/)
{
$datestr = "$3/$2/$1" ;
}
else
{
$sqldate =~ s%-%/%g ;
my $date = ParseDate($sqldate) ;
$datestr = UnixDate($date, "%d/%m/%Y") ;
}
return $datestr ;
}
#----------------------------------------------------------------------------
App::Framework::Core->sql_from_data($name)
NOTE: Only works when feature is registered with an application
Execute the (possible sequence of) command(s) stored in a named __DATA__ area in the application.
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
Setting the debug flag to level 1 prints out (to STDOUT) some debug messages, setting it to level 2 prints out more verbose messages.
Steve Price <sdprice at cpan.org>
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.
| App::Framework::Feature::Sql - MySql interface |