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, many 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.

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 ) : $row_count

Insert a single row into a table in the datasource. Should always return 1.

do_update

$sqldb->do_update( %sql_clauses ) : $row_count

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

do_delete

$sqldb->do_delete( %sql_clauses ) : $row_count

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

Checking For Existence

To determine if the connection is working and whether a table exists.

detect_any

$sqldb->detect_any () : $boolean
$sqldb->detect_any ( 1 ) : $boolean

Attempts to confirm that values can be retreived from the database, using a server-specific "trivial" or "guaranteed" query provided by sql_detect_any.

Catches any exceptions; if the query fails for any reason we return nothing. The reason for the failure is logged via warn() unless an additional argument with a true value is passed to surpress those error messages.

detect_table

$sqldb->detect_table ( $tablename ) : @columns_or_empty
$sqldb->detect_table ( $tablename, 1 ) : @columns_or_empty

Attempts to query the given table without retrieving many (or any) rows. Uses a server-specific "trivial" or "guaranteed" query provided by sql_detect_any.

Create and Drop Tables

do_create_table

$sqldb->do_create_table( $tablename, $column_hash_ary )

Create a table.

do_drop_table

$sqldb->do_drop_table( $tablename )

Delete a table.

INTERNAL 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.

INTERNAL 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) : $rowcount

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

Returns the number of rows modified, or -1 if unknown.

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 )

Set this to a true value to turn on logging of DBI interactions. Can be called on the class to set a shared default for all instances, or on any instance to set the value for it alone.

log_connect

$sqldb->log_connect ( $dsn )

Writes out connection logging message.

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.

get_execute_rowcount

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

Returns the row count reported by the last statement executed.

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.

INTERNAL 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.

In general, these methods aim to produce generic, database-independent queries, using standard SQL syntax. Subclasses may override these methods to compensate for SQL syntax idiosyncrasies.

Each method also supports passing arbitrary queries through using a sql parameter.

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. May contain a literal SQL where clause (everything after there word "where"), or a reference to an array of a SQL string with embedded placeholders followed by the values that should be bound to those placeholders.

If the criteria argument is a reference to hash, it is treated as a set of field-name => value pairs, and a SQL expression is created that requires each one of the named fields to exactly match the value provided for it, or if the value is an array reference to match any one of the array's contents; see DBIx::SQLEngine::Criteria::HashGroup for details.

Alternately, if the criteria is an object which supports a sql_where() method, the results of that method will be used; see DBIx::SQLEngine::Criteria for classes with this behavior.

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. If an array or hash reference is used, each value may either be a scalar to be used as a literal value (passed via placeholder), or a reference to a scalar to be used directly (such as a sql function or other non-literal expression).

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. If an array or hash reference is used, each value may either be a scalar to be used as a literal value (passed via placeholder), or a reference to a scalar to be used directly (such as a sql function or other non-literal expression).

criteria

Optional, but remember that ommitting this will cause all of your rows to be updated! May contain a literal SQL where clause (everything after there word "where"), or a reference to an array of a SQL string with embedded placeholders followed by the values that should be bound to those placeholders.

Alternately, if the criteria is an object which supports a sql_where() method, the results of that method will be used; see DBIx::SQLEngine::Criteria for classes with this behavior.

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 (unless explicit "sql => ..." is used). The name of the table to delete from.

criteria

Optional, but remember that ommitting this will cause all of your rows to be deleted! May contain a literal SQL where clause (everything after there word "where"), or a reference to an array of a SQL string with embedded placeholders followed by the values that should be bound to those placeholders.

Alternately, if the criteria is an object which supports a sql_where() method, the results of that method will be used; see DBIx::SQLEngine::Criteria for classes with this behavior.

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.

Checking For Existence

To determine if the connection is working and whether a table exists.

sql_detect_any

$sqldb->sql_detect_any : %sql_select_clauses

Subclass hook. Retrieve something from the database that is guaranteed to exist. Defaults to SQL literal "select 1", which may not work on all platforms. Your subclass might prefer one of these: "select SYSDATE() from dual", (I'm unsure of the others)...

sql_detect_table

$sqldb->sql_detect_table ( $tablename )  : %sql_select_clauses

Subclass hook. Retrieve something from the given table that is guaranteed to exist but does not return many rows, without knowning its table structure.

Defaults to "select * from table where 1 = 0", which may not work on all platforms. Your subclass might prefer one of these: "select * from table limit 1", (I'm unsure of the others)...

SQL Logging

SQLLogging

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

Set this to a true value to turn on logging of internally-generated SQL statements (all queries except for those with complete SQL statements explicitly passed in by the caller). Can be called on the class to set a shared default for all instances, or on any instance to set the value for it alone.

log_sql

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

Called when SQL is generated.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

DESCRIPTION

PUBLIC INTERFACE

Object Creation

Retrieving Data

Updating Data

Checking For Existence

Create and Drop Tables

INTERNAL DBH METHODS

DBH Access

INTERNAL STH METHODS

Query Execution

Error Handling

Logging

Statement Handle Lifecycle

Retrieving Rows from a Statement

Retrieving Columns from a Statement

INTERNAL SQL METHODS

Select

Insert

Update

Delete

Data Definition

Server-Specific SQL

Checking For Existence

SQL Logging

SEE ALSO

Module Install Instructions