DBIx::SQLEngine::Default - Core SQLEngine methods
# 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' }, );
Each DBIx::SQLEngine object is a wrapper around a DBI database handle.
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.
Create one SQLEngine for each DBI datasource you will use.
DBIx::SQLEngine->new( $DBIConnectionString ) : $sqldb DBIx::SQLEngine->new( $DBIConnectionString, $user, $pass, $args ) : $sqldb
Accepts the same arguments as the standard DBI connect method.
$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.
$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.
$sqldb->fetch_one_row( %sql_clauses ) : $row_hash
Calls fetch_select, then returns only the first row of results.
$sqldb->fetch_one_value( %sql_clauses ) : $scalar
Calls fetch_select, then returns a single value from the first row of results.
$sqldb->do_insert( %sql_clauses ) : $row_count
Insert a single row into a table in the datasource. Should always return 1.
$sqldb->do_update( %sql_clauses ) : $row_count
Modify one or more rows in a table in the datasource.
$sqldb->do_delete( %sql_clauses ) : $row_count
Delete one or more rows in a table in the datasource.
To determine if the connection is working and whether a table exists.
$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.
$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.
$sqldb->do_create_table( $tablename, $column_hash_ary )
Create a table.
$sqldb->do_drop_table( $tablename )
Delete a table.
The following methods manage the DBI database handle through which we communicate with the datasource.
$sqldb->_init ()
Called by DBIx::AnyDBD after connection is made and class hierarch has been juggled.
$sqldb->reconnect ()
Attempt to re-establish connection with original parameters
$sqldb->get_dbh () : $dbh
Get the current DBH
$sqldb->check_or_reconnect () : $dbh
Incomplete. Subclass hook. Get the current DBH or reconnect.
The following methods manipulate DBI statement handles as part of processing queries and their results.
$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.
$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.
$sqldb->fetch_sql ($sql, @params) : ( $row_hash_ary, $columnset )
Similar to do_sql, but returns any rows that were produced.
$sqldb->visit_sql ($coderef, $sql, @params) : ()
Similar to fetch_sql, but calls your coderef on each row, rather than returning them.
$sqldb->try_query ( $sql, \@params, $result_method, @result_args ) : @results
Error handling wrapper around execute_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.
$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.
$sqldb->log_connect ( $dsn )
Writes out connection logging message.
$sqldb->log_start( $sql ) : $timer
Called at start of query execution.
$sqldb->log_stop( $timer ) : ()
Called at end of query execution.
These are internal methods for query operations
$sqldb->execute_query($sql, \@params, $result_method, @result_args) : @results
$sqldb->prepare_execute ($sql, @params) : $sth
Prepare, bind, and execute a SQL statement.
$sqldb->done_with_query ($sth) : ()
Called when we're done with the $sth.
$sqldb->do_nothing ($sth) : ()
Does nothing.
$sqldb->get_execute_rowcount ($sth) : ()
Returns the row count reported by the last statement executed.
$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.
$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.
$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.
$sqldb->visitall_hashref ($sth, $coderef) : ()
Calls coderef on each row with values as hashref; does not return them.
$sqldb->visitall_array ($sth, $coderef) : ()
Calls coderef on each row with values as list; does not return them.
$sqldb->retrieve_columns ($sth) : $columnset
Obtains information about the columns used in the result set.
$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.
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.
sql
$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:
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.
Required. The name of the tables to select from.
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.
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.
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.
Optional. May contain a comma-separated string of column names or experessions, or an reference to an array of the same.
$sqldb->sql_insert ( %CLAUSES ) : $sql_stmt
Generate a SQL insert statement.
Required. The name of the table to insert into.
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.
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).
$sqldb->sql_update ( %CLAUSES ) : $sql_stmt
Generate a SQL update statement. The criteria are used to generate a where clause using sql_where.
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.
$sqldb->sql_delete ( %CLAUSES ) : $sql_stmt
Generate a SQL delete statement. The criteria are used to generate a where clause using sql_where.
Required (unless explicit "sql => ..." is used). The name of the table to delete from.
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.
$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.
$sqldb->sql_drop_table ($tablename) : $sql_stmt
$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.
$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".
$sqldb->sql_escape_text_for_like ( $text ) : $escaped_expr
Subclasses should, based on the datasource's server_type, protect a literal value for use in a like expression.
$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)...
$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)...
$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.
$sqldb->log_sql( $sql ) : ()
Called when SQL is generated.
DBIx::SQLEngine
To install DBIx::SQLEngine, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::SQLEngine
CPAN shell
perl -MCPAN -e shell install DBIx::SQLEngine
For more information on module installation, please visit the detailed CPAN module installation guide.