DBIx::SQLEngine - Extends DBI with high-level operations
my $sqldb = DBIx::SQLEngine->new( @DBIConnectionArgs ); $sqldb->do_insert( table => 'students', values => { 'name'=>'Dave', 'age'=>'19', 'status'=>'minor' }, ); $hash_ary = $sqldb->fetch_select( table => 'students' criteria => { 'status'=>'minor' }, ); $sqldb->do_update( table => 'students', criteria => 'age > 20' values => { 'status'=>'adult' }, ); $sqldb->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. Dynamic subclassing based on database server type enables cross-platform portability.
DBIx::SQLEngine is the latest generation of a toolkit used by the authors for several years to develop business data applications and object-relational mapping toolkits. Its goal is to simplify dynamic query execution with the following capabilities:
Data-driven SQL: Ad-hoc generation of SQL statements from Perl data structures in a variety of formats; simple hash and array references are flexibly converted to form clauses in standard SQL query syntax.
High-Level Interface: Standard query operations are handled by a single method call each. Error handling is standardized, and routine annoyances like timed-out connections are retried automatically.
Full DBI Access: Accepts arbitrary SQL queries with placeholder parameters to be passed through, and delegates all other method calls to a wrapped database handle, allowing access to the entire DBI API for cases when high-level interfaces are insufficient
Portability Subclasses: Uses dynamic subclassing (via DBIx::AnyDBD) to allow platform-specific support for driver idiosyncrasies and DBMS workarounds. This release includes subclasses for connections to MySQL, PostgreSQL, Oracle, and Microsoft SQL servers, as well as for the standalone SQLite, AnyData, and CSV packages.
Several methods are responsible for converting their arguments into commands and placeholder parameters in SQL, the Structured Query Language.
The various methods whose names being with sql_, like sql_select and sql_insert, 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 multiple formats to facilitate query abstraction, often including various strings, array refs, and hash refs. Each method also supports passing arbitrary queries through using a sql parameter.
sql
The combined query interface provides a useful high-level idiom to perform the typical cycle of SQL generation, query execution, and results fetching, all through a single method call.
The various fetch_*, visit_* and do_* methods that don't end in _sql, like fetch_select and 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.
Each DBIx::SQLEngine object is implemented as a wrapper around a database handle provided by DBI, the Perl Database Interface.
Arbitrary queries can be executed, bypassing the SQL generation capabilities. The methods whose names end in _sql, like fetch_sql and do_sql, each accept a SQL statement and parameters, pass it to the DBI data source, and return information about the results of the query.
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, some range of SQL dialect ideosyncracies can be compensated for.
For example, the sql_limit method controls the syntax for select statements with limit and offset clauses, and both MySQL and Oracle override this method to use their local syntax.
The only method that's actually provided by the DBIx::SQLEngine class itself is the new() constructor. All of the other methods are defined in DBIx::SQLEngine::Driver::Default, or in one of its automatically-loaded subclasses.
The public interface of DBIx::SQLEngine is shared by all of its subclasses. The superclass methods aim to produce and perform generic queries in an database-independent fashion, using standard SQL syntax. Subclasses may override these methods to compensate for idiosyncrasies of their database server or mechanism. To facilitate cross-platform subclassing, many of these methods are implemented by calling combinations of other methods, which may individually be overridden by subclasses.
Create one SQLEngine for each DBI datasource you will use.
DBIx::SQLEngine->new( $dbh ) : $sqldb DBIx::SQLEngine->new( $dsn ) : $sqldb DBIx::SQLEngine->new( $dsn, $user, $pass ) : $sqldb DBIx::SQLEngine->new( $dsn, $user, $pass, $args ) : $sqldb
Based on the arguments supplied, invokes either new_with_connect() or new_with_dbh() and returns the resulting new object.
DBIx::SQLEngine->new_with_connect( $dsn ) : $sqldb DBIx::SQLEngine->new_with_connect( $dsn, $user, $pass ) : $sqldb DBIx::SQLEngine->new_with_connect( $dsn, $user, $pass, $args ) : $sqldb
Accepts the same arguments as the standard DBI connect method.
Note: this method has recently been added and the interface is subject to change.
DBIx::SQLEngine->new_with_dbh( $dbh ) : $sqldb
Accepts an existing DBI database handle and creates a new SQLEngine object around it.
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 connect a DBIx::SQLEngine through DBD::mysql, by passing a DSN such as "dbi:mysql:test", your object will automatically shift to being an instance of the DBIx::SQLEngine::Driver::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. They all accept a flexible set of key-value arguments describing the query to be run, as described in the "SQL Select Clauses" section below.
$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_select_rows( %sql_clauses ) : $row_arrays $sqldb->fetch_select_rows( %sql_clauses ) : ($row_arrays,$column_hashes)
Retrieve rows from the datasource as an array of arrayrefs. 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 $sqldb->visit_select( %sql_clauses, $code_ref ) : @results
Retrieve rows from the datasource as a series of hashrefs, and call the user provided function for each one. For your convenience, will accept a coderef as either the first or the last argument. Returns the results returned by each of those function calls. Processing with visit_select rather than fetch_select can be more efficient if you are looping over a large number of rows and do not need to keep them all in memory.
$sqldb->visit_select_rows( $code_ref, %sql_clauses ) : @results $sqldb->visit_select_rows( %sql_clauses, $code_ref ) : @results
Like visit_select, but for each row the code ref is called with the current row retrieved as a list of values, rather than a hash ref.
$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, using the values provided for the keys defined below.
Uses the named_query catalog to build the query. May contain a defined query name, or a reference to an array of a query name followed by parameters to be handled by interpret_named_query. See "NAMED QUERY CATALOG" for details.
Can not be used in combination with the table and columns 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 unless sql is provided. 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, an array ref with a SQL clause and parameter list, a hash of field => value pairs, or an object that supports a sql_where() method. See the sql_where() method for details.
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.
Optional. Maximum number of rows to be retrieved from the server. Relies on DBMS-specific behavior provided by sql_limit().
Optional. Number of rows at the start of the result which should be skipped over. Relies on DBMS-specific behavior provided by sql_limit().
Examples:
Each query can be written out explicitly or generated on demand using whichever syntax is most appropriate to your application:
$hashes = $sqldb->fetch_select( table => 'students', criteria => { 'status' => 'minor' } ); $hashes = $sqldb->fetch_select( table => 'students', criteria => [ 'status = ?', 'minor' ] ); $hashes = $sqldb->fetch_select( sql => 'select * from students', criteria => { 'status' => 'minor' } ); $hashes = $sqldb->fetch_select( table => 'students', criteria => DBIx::SQLEngine::Criteria->type_new('Equality','status'=>'minor') ); $hashes = $sqldb->fetch_select( sql => "select * from students where status = 'minor'" ); $hashes = $sqldb->fetch_select( sql => [ 'select * from students where status = ?', 'minor' ] ); $sqldb->define_named_query( 'minor_students' => "select * from students where status = 'minor'" ); $hashes = $sqldb->fetch_select( named_query => 'minor_students' );
Optional clauses 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 = ?', 'minor' ] );
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 visit with 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', );
Portability: Limit and offset clauses are handled differently by various DBMS platforms. For example, MySQL accepts "limit 20,10", Postgres "limit 10 offset 20", and Oracle requires a nested select with rowcount. The sql_limit method can be overridden by subclasses to adjust this behavior.
$sqldb->sql_limit( $limit, $offset, $sql, @params ) : $sql, @params
Modifies the SQL statement and parameters list provided to apply the specified limit and offset requirements. Triggered by use of a limit or offset clause in a call to sql_select().
$sqldb->sql_where( $criteria, $sql, @params ) : $sql, @params
Modifies the SQL statement and parameters list provided to append the specified criteria as a where clause. Triggered by use of criteria in a call to sql_select(), sql_update(), or sql_delete().
The criteria may be a literal SQL where clause (everything after the 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 argument is a reference to 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.
If no SQL statement or parameters are provided, this just returns the where clause and associated parameters. If a SQL statement is provided, the where clauses is appended to it; if the SQL statement already includes a where clause, the additional criteria are inserted into the existing statement and AND'ed together with the existing criteria.
$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.
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 return 1, unless there's an exception.
$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:
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 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).
Optional. May contain a string with the name of a column in the target table which should receive an automatically incremented value. If present, triggers use of the DMBS-specific do_insert_with_sequence() method, described below.
Here's a simple insert using a hash of column-value pairs:
$sqldb->do_insert( table => 'students', values => { 'name'=>'Dave', 'age'=>'19', 'status'=>'minor' } );
Here's the same insert using separate arrays of column names and values to be inserted:
$sqldb->do_insert( table => 'students', columns => [ 'name', 'age', 'status' ], values => [ 'Dave', '19', 'minor' ] );
Of course you can also use your own arbitrary SQL and placeholder parameters.
$sqldb->do_insert( sql=>['insert into students (id, name) values (?, ?)', 201, 'Dave'] );
And the named_query interface is supported as well:
$sqldb->define_named_query( 'insert_student' => 'insert into students (id, name) values (?, ?)' ); $hashes = $sqldb->do_insert( named_query => [ 'insert_student', 201, 'Dave' ] );
Portability: Auto-incrementing sequences are handled differently by various DBMS platforms. For example, the MySQL and MSSQL subclasses use auto-incrementing fields, Oracle and Pg use server-specific sequence objects, and AnyData and CSV make their own ad-hoc table of incrementing values.
To standardize their use, this package defines an interface with several typical methods which may or may not be supported by individual subclasses. You may need to consult the documentation for the SQLEngine subclass and DBMS platform you're using to confirm that the sequence functionality you need is available.
$sqldb->do_insert_with_sequence( $seq_name, %sql_clauses ) : $row_count
Insert a single row into a table in the datasource, using a sequence to fill in the values of the column named in the first argument. Should return 1, unless there's an exception.
Subclasses will probably want to call either the _seq_do_insert_preinc() method or the _seq_do_insert_postfetch() method, and define the appropriate other seq_* methods to support them. These two methods are not part of the public interface but instead provide a template for the two most common types of insert-with-sequence behavior. The _seq_do_insert_preinc() method first obtaines a new number from the sequence using seq_increment(), and then performs a normal do_insert(). The _seq_do_insert_postfetch() method performs a normal do_insert() and then fetches the resulting value that was automatically incremented using seq_fetch_current().
$sqldb->seq_fetch_current( $table, $field ) : $current_value
Fetches the current sequence value.
$sqldb->seq_increment( $table, $field ) : $new_value
Increments the sequence, and returns the newly allocated value.
$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:
Optional; conflicts with table, columns and values 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 unless sql argument is used. The name of the table to update.
Optional unless sql argument is used. 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 unless sql argument is used. 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).
Optional, but remember that ommitting this will cause all of your rows to be updated! May contain a literal SQL where clause, an array ref with a SQL clause and parameter list, a hash of field => value pairs, or an object that supports a sql_where() method. See the sql_where() method for details.
Here's a basic update statement with a hash of columns-value pairs to change:
$sqldb->do_update( table => 'students', criteria => 'age > 20', values => { 'status'=>'adult' } );
Here's an equivalent update statement using separate lists of columns and values:
$sqldb->do_update( table => 'students', criteria => 'age > 20', columns => [ 'status' ], values => [ 'adult' ] );
You can also use your own arbitrary SQL statements and placeholders:
$sqldb->do_update( sql=>['update students set status = ? where age > ?', 'adult', 20] );
$sqldb->define_named_query( 'update_minors' => [ 'update students set status = ? where age > ?', 'adult', 20 ] ); $hashes = $sqldb->do_update( named_query => 'update_minors' );
$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:
Optional; conflicts with 'table' argument. 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 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, an array ref with a SQL clause and parameter list, a hash of field => value pairs, or an object that supports a sql_where() method. See the sql_where() method for details.
Here's a basic delete with a table name and criteria.
$sqldb->do_delete( table => 'students', criteria => { 'name'=>'Dave' } );
You can use your own arbitrary SQL and placeholders:
$sqldb->do_delete( sql => [ 'delete from students where name = ?', 'Dave' ] );
You can combine an explicit delete statement with dynamic criteria:
$sqldb->do_delete( sql => 'delete from students', criteria => { 'name'=>'Dave' } );
$sqldb->define_named_query( 'delete_by_name' => 'delete from students where name = ?' ); $hashes = $sqldb->do_delete( named_query => [ 'delete_by_name', 'Dave' ] );
The schema of a DBI database is controlled through the Data Definition Language features of SQL.
$sqldb->detect_table_names () : @table_names
Attempts to collect a list of the available tables in the database we have connected to. Uses the DBI tables() method.
$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->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 "select * from table limit 1" or a local equivalent.
$sqldb->table( $tablename ) : $table
Returns a new DBIx::SQLEngine::Schema::Table object with this SQLEngine and the given table name. See DBIx::SQLEngine::Schema::Table for more information on this object's interface.
$sqldb->tables() : $tableset
Returns a new DBIx::SQLEngine::Schema::TableSet object containing table objects with the names discovered by detect_table_names(). See DBIx::SQLEngine::Schema::TableSet for more information on this object's interface.
$sqldb->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->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_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
Returns an appropriate
$sqldb->dbms_create_column_types () : %column_type_codes
Subclass hook. Defaults to empty. Should return a hash mapping column type codes to the specific strings used in a SQL create statement for such a column.
Subclasses should provide at least two entries, for the symbolic types referenced elsewhere in this interface, "sequential" and "binary".
$sqldb->sql_create_column_text_length ( $length ) : $col_type_str
Returns "varchar(length)" for values under 256, otherwise calls dbms_create_column_text_long_type.
$sqldb->dbms_create_column_text_long_type () : $col_type_str
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".
Not all of these capabilities will be available on all database servers.
Note: this feature has been added recently, and the interface is subject to change.
The following methods all default to returning undef, but may be overridden by subclasses to return a true or false value, indicating whether their connection has this limitation.
Can the database driver return a list of tables that currently exist? (True for some simple drivers like CSV.)
Does the database driver support select statements with joins across multiple tables? (True for some simple drivers like CSV.)
Does the database driver have a problem removing a column from an existing table? (True for Postgres.)
Does the database driver store column type information, or are all columns the same type? (True for some simple drivers like CSV.)
Does the database driver automatically convert null values in insert and update statements to empty strings? (True for some simple drivers like CSV.)
Does the database driver automatically convert empty strings in insert and update statements to null values? (True for Oracle.)
Does the database driver support having ? placehoders or not? (This is a problem for Linux users of DBD::Sybase connecting to MS SQL Servers on Windows.)
Does the database driver support real transactions with rollback and commit or not?
Does the database driver support having multiple statement handles active at once or not? (This is a problem for several types of drivers.)
Does the database driver support server-side indexes or not?
Does the database driver support server-side stored procedures or not?
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( ... ); } );
Or using a reference to a predefined subroutine:
sub do_stuff { my $sqldb = shift; $sqldb->do_insert( ... ); $sqldb->do_update( ... ); $sqldb->do_delete( ... ); } my $sqldb = DBIx::SQLEngine->new( ... ); $sqldb->as_one_transaction( \&do_stuff, $sqldb );
@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.
$sqldb->create_index( %clauses )
$sqldb->sql_create_index( %clauses ) : $sql, @params
$sqldb->drop_index( %clauses )
$sqldb->sql_drop_index( %clauses ) : $sql, @params
Example:
$sqldb->create_index( table => $table_name, columns => @columns ); $sqldb->drop_index( table => $table_name, columns => @columns );
$sqldb->create_index( name => $index_name, table => $table_name, columns => @columns ); $sqldb->drop_index( name => $index_name );
These methods are all subclass hooks. Fail with message "DBMS-Specific Function".
Subclasses may
$sqldb->create_database( $db_name )
$sqldb->drop_database( $db_name )
$sqldb->fetch_storedproc( $proc_name, @arguments ) : $rows
$sqldb->do_storedproc( $proc_name, @arguments ) : $row_count
$sqldb->create_storedproc( $proc_name, $definition )
$sqldb->drop_storedproc( $proc_name )
The following methods manage a collection of named query definitions.
$sqldb->named_queries() : %query_names_and_info $sqldb->named_queries( $query_name ) : $query_info $sqldb->named_queries( \@query_names ) : @query_info $sqldb->named_queries( $query_name, $query_info, ... ) $sqldb->named_queries( \%query_names_and_info )
Accessor and mutator for a hash mappping query names to their definitions. Created with Class::MakeMethods::Standard::Inheritable, so if called as a class method, uses class-wide methods, and if called on an instance inherits from its class but is overrideable.
$sqldb->named_query( $query_name ) : $query_info
Retrieves the query definition matching the name provided, or croaks.
$sqldb->define_named_query( $query_name, $query_info )
Provides a new query definition for the name provided. The query definition must be in one of the following formats:
A literal SQL string. May contain placeholders to be passed as arguments.
A reference to an array of a SQL string and placeholder parameters. Parameters which should be replaced by user-supplied arguments should be represented by references to the special Perl variables $1, $2, $3, and so forth, corresponding to the argument order.
A reference to a hash of a clauses supported by one of the SQL generation methods. Parameters which should be replaced by user-supplied arguments should be represented by references to the special Perl variables $1, $2, $3, and so forth, corresponding to the argument order.
A reference to a subroutine or code block which will process the user-supplied arguments and return either a SQL statement, a reference to an array of a SQL statement and associated parameters, or a list of key-value pairs to be used as clauses by the SQL generation methods.
See the Examples section below for illustrations of these various options.
$sqldb->define_named_queries( %query_names_and_info )
Defines one or more queries, using define_named_query.
$sqldb->define_named_query_from_text($query_name, $query_info_text)
Defines a query, using some special processing to facilitate storing dynamic query definitions in an external source such as a text file or database table. Query definitions which begin with a [ or { character are presumed to contain an array or hash definition and are evaluated immediately. Definitions which begin with a " or ; character are presumed to contain a code definition and evaluated as the contents of an anonymous subroutine. All evaluations are done via a Safe compartment, which is required when this function is first used, so the code is extremely limited and can not call most other functions.
$sqldb->define_named_queries_from_text(%query_names_and_info_text)
Defines one or more queries, using define_named_query_from_text.
$sqldb->interpret_named_query( $query_name, @params ) : %clauses
Combines the query definition matching the name provided with the following arguments and returns the resulting hash of query clauses.
$sqldb->fetch_named_query( $query_name, @params ) : $rows $sqldb->fetch_named_query( $query_name, @params ) : ( $rows, $columns )
Calls fetch_select using the named query and arguments provided.
$sqldb->visit_named_query($query_name, @params, $code) : @results $sqldb->visit_named_query($code, $query_name, @params) : @results
Calls visit_select using the named query and arguments provided.
$sqldb->do_named_query( $query_name, @params ) : $row_count
Calls do_query using the named query and arguments provided.
A simple named query can be defined in SQL or as generator clauses:
$sqldb->define_named_query('all_students', 'select * from students'); $sqldb->define_named_query('all_students', { table => 'students' });
The results of a named select query can be retrieved in several equivalent ways:
$rows = $sqldb->fetch_named_query( 'all_students' ); $rows = $sqldb->fetch_select( named_query => 'all_students' ); @rows = $sqldb->visit_select( named_query => 'all_students', sub { $_[0] } );
There are numerous ways of defining a query which accepts parameters; any of the following are basically equivalent:
$sqldb->define_named_query('student_by_id', 'select * from students where id = ?' ); $sqldb->define_named_query('student_by_id', { sql=>['select * from students where id = ?', \$1 ] } ); $sqldb->define_named_query('student_by_id', { table=>'students', criteria=>[ 'id = ?', \$1 ] } ); $sqldb->define_named_query('student_by_id', { table=>'students', criteria=>{ 'id' => \$1 } } ); $sqldb->define_named_query('student_by_id', { action=>'select', table=>'students', criteria=>{ 'id'=>\$1 } } );
Using a named query with parameters requires that the arguments be passed after the name:
$rows = $sqldb->fetch_named_query( 'student_by_id', $my_id ); $rows = $sqldb->fetch_select(named_query=>['student_by_id', $my_id]);
If the query is defined using a plain string, as in the first line of the student_by_id example, no checking is done to ensure that the correct number of parameters have been passed; the result will depend on your database server, but will presumably be a fatal error. In contrast, the definitions that use the \$1 format will have their parameters counted and arranged before being executed.
Queries which insert, update, or delete can be defined in much the same way as select queries are; again, all of the following are roughly equivalent:
$sqldb->define_named_query('delete_student', 'delete from students where id = ?'); $sqldb->define_named_query('delete_student', [ 'delete from students where id = ?', \$1 ]); $sqldb->define_named_query('delete_student', { action=>'delete', table=>'students', criteria=>{ id=>\$1 } });
These modification queries can be invoked with one of the do_ methods:
$sqldb->do_named_query( 'delete_student', 201 ); $sqldb->do_query( named_query => [ 'delete_student', 201 ] ); $sqldb->do_delete( named_query => [ 'delete_student', 201 ] );
Queries can be defined using subroutines:
$sqldb->define_named_query('name_search', sub { my $name = lc( shift ); return "select * from students where name like '%$name%'" }); $rows = $sqldb->fetch_named_query( 'name_search', 'DAV' );
Query definitions can be stored in external text files or database tables and then evaluated into data structures or code references.
open( QUERIES, '/path/to/my/queries' ); my %queries = map { split ':', $_, 2 } <QUERIES>; close QUERIES; $sqldb->define_named_queries_from_text( %queries );
Placing the following text in the target file will define all of the queries used above:
all_students: select * from students student_by_id: [ 'select * from students where id = ?', \$1 ] delete_student: { action => 'delete', table => 'students', criteria => { id => \$1 } }); name_search: "select * from students where name like '%\L$_[0]\E%'"
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); my $rows = $db->fetch_sql('select * from table where status = ?', 2);
Execute and fetch some kind of result from a given SQL statement. Internally, these methods are used by the other do_, fetch_ and visit_ 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.
$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 $sqldb->visit_sql ($sql, @params, $coderef) : @results
Similar to fetch_sql, but calls your coderef on each row, passing it as a hashref, and returns the results of each of those calls. For your convenience, will accept a coderef as either the first or the last argument.
Similar to fetch_sql, but calls your coderef on each row, passing it as a list of values, and returns the results of each of those calls. For your convenience, will accept a coderef as either the first or the last argument.
The following methods manage the DBI database handle through which we communicate with the datasource.
$sqldb->get_dbh () : $dbh
Get the current DBH
$sqldb->dbh_func ( $func_name, @args ) : @results
Calls the DBI func() method on the database handle returned by get_dbh, passing the provided function name and arguments. See the documentation for your DBD driver to learn which functions it supports.
$sqldb->_init ()
Empty subclass hook. 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
Confirms the current DBH is available with detect_any() or reconnect().
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 database driver might prefer something else, like Oracle's "select 1 from dual".
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.
The $result_method should be the name of a method supported by that SQLEngine instance, typically one of those shown in the "Retrieving Rows from an Executed Statement" section below. The @result_args, if any, are passed to the named method along with the active statement handle.
$sqldb->catch_query_exception ( $exception, $sql, \@params, $result_method, @result_args ) : $resolution
Exceptions are passed to catch_query_exception; if it returns "REDO" the query will be retried up to five times. The superclass checks the error message against the recoverable_query_exceptions; subclasses may wish to override this to provide specialized handling.
$sqldb->recoverable_query_exceptions() : @common_error_messages
Subclass hook. Defaults to empty. Subclasses may provide a list of error messages which represent common communication failures or other incidental errors.
These are internal methods for query operations
$sqldb->execute_query($sql, \@params, $result_method, @result_args) : @results
This overall lifecycle method calls prepare_execute(), runs the $result_method, and then calls done_with_query().
$sqldb->prepare_execute ($sql, @params) : $sth
Prepare, bind, and execute a SQL statement to create a DBI statement handle.
Uses prepare_cached(), bind_param(), and execute().
If you need to pass type information with your parameters, pass a reference to an array of the parameter and the type information.
$sqldb->done_with_query ($sth) : ()
Called when we're done with the $sth.
$sqldb->do_nothing ($sth) : ()
Does nothing.
$sqldb->get_execute_rowcount ($sth) : $row_count
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->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.
This example, based on a writeup by Ron Savage, shows a connection being opened, a table created, several rows of data inserted, and then retrieved again:
#!/usr/bin/perl use strict; use warnings; use DBIx::SQLEngine; eval { my $engine = DBIx::SQLEngine->new( 'DBI:mysql:test:127.0.0.1', 'route', 'bier', { RaiseError => 1, ShowErrorStatement => 1, } ); my $table_name = 'sqle'; my $columns = [ { name => 'sqle_id', type => 'sequential', }, { name => 'sqle_name', type => 'text', length => 255, }, ]; $engine->drop_table($table_name); $engine->create_table($table_name, $columns); $engine->do_insert(table => $table_name, values => {sqle_name => 'One'}); $engine->do_insert(table => $table_name, values => {sqle_name => 'Two'}); $engine->do_insert(table => $table_name, values => {sqle_name => 'Three'}); my $dataset = $engine->fetch_select(table => $table_name); my $count = 0; for my $data (@$dataset) { $count++; print "Row $count: ", map( {"\t$_ => " . (defined $$data{$_} ? $$data{$_} : 'NULL')} sort keys %$data), "\n"; } }; if ( $@ ) { warn "Unable to build sample table: $@"; }
Many types of database servers are not yet supported.
Database driver/server combinations that do not support placeholders will fail. (http://groups.google.com/groups?selm=dftza.3519%24ol.117790%40news.chello.at)
See DBIx::SQLEngine::ToDo for additional bugs and missing features.
See DBIx::SQLEngine::ReadMe for distribution and support 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.
Developed by Matthew Simon Cavalletto at Evolution Softworks.
You may contact the author directly at evo@cpan.org or simonm@cavalletto.org. More free Perl software is available at www.evoscript.org.
evo@cpan.org
simonm@cavalletto.org
www.evoscript.org
Many thanks to the kind people who have contributed code and other feedback:
Eric Schneider, Evolution Online Systems E. J. Evans, Evolution Online Systems Matthew Sheahan, Evolution Online Systems Eduardo Iturrate, Evolution Online Systems Ron Savage Christian Glahn, Innsbruck University Michael Kroll, Innsbruck University
Inspiration, tricks, and bits of useful code were taken from these CPAN modules:
DBIx::AnyDBD DBIx::Compat DBIx::Datasource DBIx::Renderer
Copyright 2001, 2002, 2003, 2004 Matthew Cavalletto.
Portions copyright 1998, 1999, 2000, 2001 Evolution Online Systems, Inc.
Portions copyright 2002 ZID, Innsbruck University (Austria).
Portions of the documentation are copyright 2003 Ron Savage.
You may use, modify, and distribute this software under the same terms as Perl.
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.