DBIx::SQLEngine - Extends DBI with high-level operations
my $db = DBIx::SQLEngine->new( @DBIConnectionArgs ); $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' }, );
The DBIx::SQLEngine class provides an extended interface for the DBI database framework. Each SQLEngine object is a wrapper around a DBI database handle, adding methods that support ad-hoc SQL generation and query execution in a single call.
Behind the scenes, different subclasses of SQLEngine are instantiated depending on the type of server to which you connect, thanks to DBIx::AnyData. As a result, SQL dialect ideosyncracies can be compensated for; this release includes subclasses supporting the MySQL, Pg, AnyData, and CSV drivers.
The public interface described below is shared by all SQLEngine subclasses. 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. To facilitate cross-platform subclassing, many of these methods are implemented by calling combinations of other methods, which may individually be overridden by subclasses.
This module deals with two different aspects of SQL, the Structured Query Language: generating statements that will perform a required function, and executing statements by passing them through the DBI handle.
The various methods whose names being with sql_, like sql_select, 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 using a sql parameter.
sql
The methods whose names end in _sql, like fetch_sql, each accept a SQL statement and parameters, pass it to the DBI data source, and return information about the results of the query.
The other fetch_*, visit_* and do_* methods, like do_insert, are wrappers that combine a SQL-generation and a SQL-execution method to provide a simple ways to perform a query in one call.
Create one SQLEngine for each DBI datasource you will use.
DBIx::SQLEngine->new( $dsn ) : $sqldb DBIx::SQLEngine->new( $dsn, $user, $pass ) : $sqldb DBIx::SQLEngine->new( $dsn, $user, $pass, $args ) : $sqldb
Accepts the same arguments as the standard DBI connect method.
Portability: After setting up the DBI handle that it will use, the SQLEngine is reblessed into a matching subclass, if one is available. Thus, if you create a DBIx::SQLEngine that's using DBD::mysql, by passing a DSN such as "dbi:mysql:test", your object will automatically shift to being an instance of the DBIx::SQLEngine::Mysql class. This allows the driver-specific subclasses to compensate for differences in the SQL dialect or execution ideosyncracies of that platform.
Information is obtained from a DBI database through the Data Query Language features of SQL.
The following methods may be used to retrieve data using SQL select statements.
$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->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->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->sql_select ( %sql_clauses ) : $sql_stmt, @params
Generate a SQL select statement and returns it as a query string and a list of values to be bound as parameters. Internally, this sql_ method is used by the fetch_ and visit_ methods above.
SQL Select Clauses: The above select methods accept a hash describing the clauses of the SQL statement they are to generate, and require a value for one or more of the following keys:
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.
Examples:
Each query can be written out explicitly or generated on demand:
$hashes = $sqldb->fetch_select( sql => "select * from students where status = 'minor'" ); $hashes = $sqldb->fetch_select( sql => [ 'select * from students where status = ?', 'minor' ] ); $hashes = $sqldb->fetch_select( table => 'students', criteria => { 'status' => 'minor' } );
Limiting the columns returned, and specifying an order:
$hashes = $sqldb->fetch_select( table => 'students', columns => 'name, age', order => 'name' );
Here's a criteria clause that uses a function to find the youngest people; note the use of a backslash to indicate that "min(age)" is an expression to be evaluated by the database server, rather than a literal value:
$hashes = $sqldb->fetch_select( table => 'students', criteria => { 'age' => \"min(age)" } );
Here's a join of two tables; note that we're using a backslash again to make it clear that we're looking for tuples where the students.id column matches that the grades.student_id column, rather than trying to match the literal string 'grades.student_id':
$hashes = $sqldb->fetch_select( tables => 'students, grades', criteria => { 'students.id' = \'grades.student_id' } order => 'students.name' );
If you know that only one row will match, you can use fetch_one_row:
$joe = $sqldb->fetch_one_row( table => 'student', criteria => { 'id' => 201 } );
All of the SQL select clauses are accepted, including explicit SQL statements with parameters:
$joe = $sqldb->fetch_one_row( sql => [ 'select * from students where id = ?', 201 ] );
And when you know that there will only be one row and one column in your result set, you can use fetch_one_value:
$count = $sqldb->fetch_one_value( table => 'student', columns => 'count(*)' );
$maxid = $sqldb->fetch_one_value( sql => 'select max(id) from students where status = ?' );
You can use visit_select to make a traversal of all rows that match a query without retrieving them all at once:
$sqldb->visit_select( sub { my $student = shift; print $student->{id}, $student->{name}, $student->{age}; }, table => 'student' );
You can collect values along the way:
my @firstnames = $sqldb->visit_select( sub { my $student = shift; ( $student->{name} =~ /(\w+)\s/ ) ? $1 : $student->{name}; }, table => 'student' );
You can use any combination of the other clauses supported by fetch_select:
$sqldb->visit_select( sub { my $student = shift; print $student->{id}, $student->{name}; }, table => 'student', columns => 'id, name', order => 'name, id desc', criteria => 'age < 22', );
Information is entered into a DBI database through the Data Manipulation Language features of SQL.
$sqldb->do_insert( %sql_clauses ) : $row_count
Insert a single row into a table in the datasource. Should always return 1.
$sqldb->sql_insert ( %sql_clauses ) : $sql_stmt, @params
Generate a SQL insert statement and returns it as a query string and a list of values to be bound as parameters. Internally, this sql_ method is used by the do_ method above.
SQL Insert Clauses: The above insert methods accept a hash describing the clauses of the SQL statement they are to generate, and require a value for one or more of the following keys:
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->do_insert( table => 'students', values => { 'name'=>'Dave', 'age'=>'19', 'status'=>'minor' } );
$sqldb->do_insert( table => 'students', columns => [ 'name', 'age', 'status' ], values => [ 'Dave', '19', 'minor' ] );
$sqldb->fetch_one_row( sql => [ 'insert into students (id, name) values (?, ?)', 201, 'Dave' ] );
$sqldb->do_update( %sql_clauses ) : $row_count
Modify one or more rows in a table in the datasource.
$sqldb->sql_update ( %sql_clauses ) : $sql_stmt, @params
Generate a SQL update statement and returns it as a query string and a list of values to be bound as parameters. Internally, this sql_ method is used by the do_ method above.
SQL Update Clauses: The above update methods accept a hash describing the clauses of the SQL statement they are to generate, and require a value for one or more of the following keys:
Required. The name of the table to update.
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->do_update( table => 'students', criteria => 'age > 20', values => { 'status'=>'adult' } );
$sqldb->do_update( table => 'students', criteria => 'age > 20', columns => [ 'status' ], values => [ 'adult' ] );
$sqldb->fetch_one_row( sql => [ 'update students set status = ? where age > ?', 'adult', 20 ] );
$sqldb->do_delete( %sql_clauses ) : $row_count
Delete one or more rows in a table in the datasource.
$sqldb->sql_delete ( %sql_clauses ) : $sql_stmt, @params
Generate a SQL delete statement and returns it as a query string and a list of values to be bound as parameters. Internally, this sql_ method is used by the do_ method above.
SQL Delete Clauses: The above delete methods accept a hash describing the clauses of the SQL statement they are to generate, and require a value for one or more of the following keys:
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->do_delete( table => 'students', criteria => { 'name'=>'Dave' } );
$sqldb->fetch_one_row( sql => [ 'delete from students where name = ?', 'Dave' ] );
The schema of a DBI database is controlled through the Data Definition Language features of SQL.
$sqldb->do_create_table( $tablename, $column_hash_ary )
Create a table.
The columns to be created in this table are defined as an array of hash references, as described in the Column Information section below.
$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.
If succssful, the columns contained in this table are returned as an array of hash references, as described in the Column Information section below.
Catches any exceptions; if the query fails for any reason we return an empty list. 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->do_drop_table( $tablename )
Delete the named table.
Column Information: The information about columns is presented as an array of hash references, each containing the following keys:
name => $column_name_string
Defines the name of the column.
Portability: No case or length restrictions are imposed on column names, but for incresased compatibility, you may wish to stick with single-case strings of moderate length.
type => $column_type_constant_string
Specifies the type of column to create. Discussed further below.
required => $not_nullable_boolean
Indicates whether a value for this column is required; if not, unspecified or undefined values will be stored as NULL values. Defaults to false.
length => $max_chars_integer
Only applicable to column of type => 'text'.
type => 'text'
Indicates the maximum number of ASCII characters that can be stored in this column.
SQL Generation: The above do_ methods use the following sql_ methods to generate SQL DDL statements.
$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_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->sql_drop_table ($tablename) : $sql_stmt
Column Type Info Methods: The following methods are used by sql_create_table to specify column information in a DBMS-specific fashion.
$sqldb->sql_create_column_type ( table, $column, $columns ) : $col_type_str
$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".
These methods allow arbitrary SQL statements to be executed.
Portability: Note that no processing of the SQL query string is performed, so if you call these low-level functions it is up to you to ensure that the query is correct and will function as expected when passed to whichever data source the SQLEngine is using.
$db->do_sql('insert into table values (?, ?)', 'A', 1);
Execute a given SQL statement. Internally, this method is used by the other do_ methods described above.
$sqldb->do_sql ($sql, @params) : $rowcount
Execute a SQL query by sending it to the DBI connection, and returns the number of rows modified, or -1 if unknown.
my $rows = $db->fetch_sql('select * from table where status = ?', 2);
Execute and fetch results of a given SQL statement. Internally, these methods are used by the other fetch_ and visit_ methods described above.
$sqldb->fetch_sql ($sql, @params) : $row_hash_ary $sqldb->fetch_sql ($sql, @params) : ( $row_hash_ary, $columnset )
Execute a SQL query by sending it to the DBI connection, and returns any rows that were produced, as an array of hashrefs, with the values in each entry keyed by column name. If called in a list context, also returns a reference to an array of information about the columns returned by the query.
$sqldb->fetch_sql_rows ($sql, @params) : $row_ary_ary
Execute a SQL query by sending it to the DBI connection, and returns any rows that were produced, as an array of arrays, with the values in each entry keyed by column order. If called in a list context, also returns a reference to an array of information about the columns returned by the query.
$sqldb->visit_sql ($coderef, $sql, @params) : @results
Similar to fetch_sql, but calls your coderef on each row, rather than returning them. Returns the results of each of those calls.
Note: this feature has been added recently, and the interface is subject to change.
DBIx::SQLEngine assumes auto-commit is on by default, so unless otherwise specified, each query is executed as a separate transaction. To execute multiple queries within a single transaction, use the as_one_transaction method.
$boolean = $sqldb->are_transactions_supported( );
Checks to see if the database has transaction support.
@results = $sqldb->as_one_transaction( $sub_ref, @args );
Will fail if we don't have transaction support.
For example:
my $sqldb = DBIx::SQLEngine->new( ... ); $sqldb->as_one_transaction( sub { $sqldb->do_insert( ... ); $sqldb->do_update( ... ); $sqldb->do_delete( ... ); } );
@results = $sqldb->as_one_transaction_if_supported($sub_ref, @args)
If transaction support is available, this is equivalent to as_one_transaction. If transactions are not supported, simply performs the code in $sub_ref with no transaction protection.
The following methods manage the DBI database handle through which we communicate with the datasource.
$sqldb->get_dbh () : $dbh
Get the current DBH
To determine if the connection is working.
$sqldb->detect_any () : $boolean $sqldb->detect_any ( 1 ) : $boolean
Attempts to confirm that values can be retreived from the database, allowing us to determine if the connection is working, 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 a false value. The reason for the failure is logged via warn() unless an additional argument with a true value is passed to surpress those error messages.
SQL Generation: The above detect_ method uses the following sql_ method to generate SQL statements.
$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->_init ()
Called by DBIx::AnyDBD after connection is made and class hierarchy has been juggled.
$sqldb->reconnect ()
Attempt to re-establish connection with original parameters
$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.
$sqldb->try_query ( $sql, \@params, $result_method, @result_args ) : @results
Error handling wrapper around the internal execute_query method.
$sqldb->catch_query_exception ( $exception, $sql, \@params, $result_method, @result_args ) : $resolution
This is a subclass hook that does nothing in the superclass but should be overridden in subclasses. Exceptions are passed to catch_query_exception; if it returns "REDO" the query will be retried up to five times.
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.
$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->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.
$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.
See DBIx::SQLEngine::Default for implementation details.
See DBIx::SQLEngine::ReadMe for distribution information.
See DBI and the various DBD modules for information about the underlying database interface.
See DBIx::AnyDBD for details on the dynamic subclass selection mechanism.
M. Simon Cavalletto, simonm@cavalletto.org Evolution Softworks, www.evoscript.org
Eric Schneider E. J. Evans, piglet@piglet.org Matthew Sheahan
Copyright 2002 Matthew Cavalletto.
Portions copyright 1998, 1999, 2000, 2001 Evolution Online Systems, Inc.
You may use, modify, and distribute this software under the same terms as Perl.
1 POD Error
The following errors were encountered while parsing the POD:
You forgot a '=back' before '=head1'
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.