/ 
DBIx-SQLEngine-0.002
River stage one • 1 direct dependent • 1 total dependent
/ DBIx::SQLEngine::Default

NAME

DBIx::SQLEngine::Default - Core SQLEngine methods

SYNOPSIS

# This module is loaded by DBIx::SQLEngine; do not use it directly.
my $db = DBIx::SQLEngine->new( $DBIConnectionString );

$datasource->do_insert(
  table => 'students', 
  values => { 'name'=>'Dave', 'age'=>'19', 'status'=>'minor' },
);

$hash_ary = $datasource->fetch_select( 
  table => 'students' 
  criteria => { 'status'=>'minor' },
);

$datasource->do_update( 
  table => 'students', 
  criteria => 'age > 20' 
  values => { 'status'=>'adult' },
);

$datasource->do_delete(
  table => 'students', 
  criteria => { 'name'=>'Dave' },
);

DESCRIPTION

Each DBIx::SQLEngine object is a wrapper around a DBI database handle.

PUBLIC INTERFACE

The public interface described below is shared by all SQLEngine subclasses. To facilitate cross-platform subclassing, these methods are implemented by calling combinations of other methods described in the INTERNALS sections below.

Object Creation

Create one SQLEngine for each DBI datasource you will use.

new
DBIx::SQLEngine->new( $DBIConnectionString ) : $sqldb
DBIx::SQLEngine->new( $DBIConnectionString, $user, $pass, $args ) : $sqldb

Accepts the same arguments as the standard DBI connect method.

DBILogging
$sqldb->DBILogging : $value
$sqldb->DBILogging( $value )

Global. Set this to a true value to turn on logging.

log_connect
$sqldb->log_connect ( $dsn )

Writes out connection logging message.

Retrieving Data

fetch_select
$sqldb->fetch_select( %sql_clauses ) : $row_hashes
$sqldb->fetch_select( %sql_clauses ) : ( $row_hashes, $column_hashes )

Retrieve rows from the datasource as an array of hashrefs. If called in a list context, also returns an array of hashrefs containing information about the columns included in the result set.

visit_select
$sqldb->visit_select( $code_ref, %sql_clauses ) : @results

Retrieve rows from the datasource as a series of hashrefs, and call the user provided function for each one. Returns the results returned by each of those function calls. This can allow for more efficient processing if you are processing a large number of rows and do not need to keep them all in memory.

fetch_one_row
$sqldb->fetch_one_row( %sql_clauses ) : $row_hash

Calls fetch_select, then returns only the first row of results.

fetch_one_value
$sqldb->fetch_one_value( %sql_clauses ) : $scalar

Calls fetch_select, then returns a single value from the first row of results.

Updating Data

do_insert
$sqldb->do_insert( %sql_clauses )

Insert a single row into a table in the datasource.

do_update
$sqldb->do_update( %sql_clauses )

Modify one or more rows in a table in the datasource.

do_delete
$sqldb->do_delete( %sql_clauses )

Delete one or more rows in a table in the datasource.

DBH METHODS

The following methods manage the DBI database handle through which we communicate with the datasource.

_init
$sqldb->_init ()

Called by DBIx::AnyDBD after connection is made and class hierarch has been juggled.

reconnect
$sqldb->reconnect ()

Attempt to re-establish connection with original parameters

DBH Access

get_dbh
$sqldb->get_dbh () : $dbh

Get the current DBH

check_or_reconnect
$sqldb->check_or_reconnect () : $dbh

Incomplete. Subclass hook. Get the current DBH or reconnect.

STH METHODS

The following methods manipulate DBI statement handles as part of processing queries and their results.

Query Execution

$db->do_sql('insert into table values (?, ?)', 'A', 1);
my $rows = $db->fetch_sql('select * from table where status = ?', 2);

This is the public interface for accessing data through the SQLEngine.

do_sql
$sqldb->do_sql ($sql, @params) : ()

Execute a SQL query by sending it to the DBI connection.

fetch_sql
$sqldb->fetch_sql ($sql, @params) : ( $row_hash_ary, $columnset )

Similar to do_sql, but returns any rows that were produced.

visit_sql
$sqldb->visit_sql ($coderef, $sql, @params) : ()

Similar to fetch_sql, but calls your coderef on each row, rather than returning them.

Error Handling

try_query
$sqldb->try_query ( $sql, \@params, $result_method, @result_args ) : @results

Error handling wrapper around execute_query.

try_query
$sqldb->catch_query_exception ( $exception, $sql, \@params, $result_method, @result_args ) : $resolution

Subclass hook. Exceptions are passed to catch_query_exception; if it returns "REDO" the query will be retried up to five times.

Logging

DBILogging
$sqldb->DBILogging : $value
$sqldb->DBILogging( $value )

Global. Set this to a true value to turn on logging.

log_start
$sqldb->log_start( $sql ) : $timer

Called at start of query execution.

log_stop
$sqldb->log_stop( $timer ) : ()

Called at end of query execution.

Statement Handle Lifecycle

These are internal methods for query operations

execute_query
$sqldb->execute_query($sql, \@params, $result_method, @result_args) : @results
prepare_execute
$sqldb->prepare_execute ($sql, @params) : $sth

Prepare, bind, and execute a SQL statement.

done_with_query
$sqldb->done_with_query ($sth) : ()

Called when we're done with the $sth.

Retrieving Rows from a Statement

do_nothing
$sqldb->do_nothing ($sth) : ()

Does nothing.

fetchall_arrayref
$sqldb->fetchall_arrayref ($sth) : $array_of_arrays

Calls the STH's fetchall_arrayref method to retrieve all of the result rows into an array of arrayrefs.

fetchall_hashref
$sqldb->fetchall_hashref ($sth) : $array_of_hashes

Calls the STH's fetchall_arrayref method with an empty hashref to retrieve all of the result rows into an array of hashrefs.

fetchall_hashref_columns
$sqldb->fetchall_hashref ($sth) : $array_of_hashes, $column_info

Calls the STH's fetchall_arrayref method with an empty hashref, and also retrieves information about the columns used in the query result set.

visitall_hashref
$sqldb->visitall_hashref ($sth, $coderef) : ()

Calls coderef on each row with values as hashref; does not return them.

visitall_array
$sqldb->visitall_array ($sth, $coderef) : ()

Calls coderef on each row with values as list; does not return them.

Retrieving Columns from a Statement

retrieve_columns
$sqldb->retrieve_columns ($sth) : $columnset

Obtains information about the columns used in the result set.

column_type_codes
$sqldb->column_type_codes - Standard::Global:hash

Maps the ODBC numeric constants used by DBI to the names we want to use for simplified internal representation.

To Do: this should probably be using DBI's type_info methods.

SQL METHODS

The various sql_* methods below each accept a hash of arguments and combines then to return a SQL statement and corresponding parameters. Data for each clause of the statement is accepted in a variety of formats to facilitate query abstraction.

Each method also supports passing arbitrary queries through.

Select

sql_select
$sqldb->sql_select ( %CLAUSES ) : $sql_stmt, @params

Generate a SQL select statement. If provided, the criteria are used to generate a where clause using sql_where.

The following argument clauses are supported:

sql

Optional; overrides all other arguments. May contain a plain SQL statement to be executed, or a reference to an array of a SQL statement followed by parameters for embedded placeholders.

table or tables

Required. The name of the tables to select from.

columns

Optional; defaults to '*'. May contain a comma-separated string of column names, or an reference to an array of column names, or a reference to an object with a "column_names" method.

criteria

Optional. If the criteria is one of the DBO::Criteria objects, its sql() expression will be used. Criteria are blessed hashes of { 'key'=> word, 'match'=> text, 'value'=> ref_val }; see DBO::Criteria for details.

order

Optional. May contain a comma-separated string of column names or experessions, optionally followed by "DESC", or an reference to an array of the same.

group

Optional. May contain a comma-separated string of column names or experessions, or an reference to an array of the same.

Insert

sql_insert
$sqldb->sql_insert ( %CLAUSES ) : $sql_stmt

Generate a SQL insert statement.

The following argument clauses are supported:

sql

Optional; overrides all other arguments. May contain a plain SQL statement to be executed, or a reference to an array of a SQL statement followed by parameters for embedded placeholders.

table

Required. The name of the table to insert into.

columns

Optional; defaults to '*'. May contain a comma-separated string of column names, or an reference to an array of column names, or a reference to a hash whose keys contain the column names, or a reference to an object with a "column_names" method.

values

Required. May contain a string with one or more comma-separated quoted values or expressions in SQL format, or a reference to an array of values to insert in order, or a reference to a hash whose values are to be inserted.

Update

sql_update
$sqldb->sql_update ( %CLAUSES ) : $sql_stmt

Generate a SQL update statement. The criteria are used to generate a where clause using sql_where.

The following argument clauses are supported:

sql

Optional; overrides all other arguments. May contain a plain SQL statement to be executed, or a reference to an array of a SQL statement followed by parameters for embedded placeholders.

table

Required. The name of the table to insert into.

columns

Optional; defaults to '*'. May contain a comma-separated string of column names, or an reference to an array of column names, or a reference to a hash whose keys contain the column names, or a reference to an object with a "column_names" method.

values

Required. May contain a string with one or more comma-separated quoted values or expressions in SQL format, or a reference to an array of values to insert in order, or a reference to a hash whose values are to be inserted.

criteria

Optional, but remember that ommitting this will cause all of your rows to be updated! If the criteria is one of the DBO::Criteria objects, its sql() expression will be used. Criteria are blessed hashes of { 'key'=> word, 'match'=> text, 'value'=> ref_val }; see DBO::Criteria for details.

Delete

sql_delete
$sqldb->sql_delete ( %CLAUSES ) : $sql_stmt

Generate a SQL delete statement. The criteria are used to generate a where clause using sql_where.

The following argument clauses are supported:

sql

Optional; overrides all other arguments. May contain a plain SQL statement to be executed, or a reference to an array of a SQL statement followed by parameters for embedded placeholders.

table

Required. The name of the table to insert into.

criteria

Optional, but remember that ommitting this will cause all of your rows to be updated! If the criteria is one of the DBO::Criteria objects, its sql() expression will be used. Criteria are blessed hashes of { 'key'=> word, 'match'=> text, 'value'=> ref_val }; see DBO::Criteria for details.

Data Definition

sql_create_table
$sqldb->sql_create_table ($tablename, $columns) : $sql_stmt

Generate a SQL create-table statement based on the column information. Text columns are checked with sql_create_column_text_length() to provide server-appropriate types.

sql_drop_table
$sqldb->sql_drop_table ($tablename) : $sql_stmt

Server-Specific SQL

sql_create_column_text_length
$sqldb->sql_create_column_text_length ( $length ) : $col_type_str

Returns varchar(length) for values under 256, otherwise calls sql_create_column_text_long_type.

sql_create_column_text_long_type
$sqldb->sql_create_column_text_long_type () : $col_type_str

Fails with message "DBMS-Specific Function".

Subclasses should, based on the datasource's server_type, return the appropriate type of column for long text values, such as "BLOB", "TEXT", "LONGTEXT", or "MEMO".

sql_escape_text_for_like
$sqldb->sql_escape_text_for_like ( $text ) : $escaped_expr

Fails with message "DBMS-Specific Function".

Subclasses should, based on the datasource's server_type, protect a literal value for use in a like expression.

SQL Logging

SQLLogging
$sqldb->SQLLogging () : $value 
$sqldb->SQLLogging( $value )

Global. Set this to a true value to turn on logging.

log_sql
$sqldb->log_sql( $sql ) : ()

Called when SQL is generated.

SEE ALSO

DBIx::SQLEngine