DBIx::SQLEngine - Extends DBI with high-level operations
my $sqldb = DBIx::SQLEngine->new( $DBIConnectionString ); $sqldb->do_insert( table => 'students', values => { 'name'=>'Dave', 'age'=>'19', 'status'=>'minor' } ); $hashes = $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 interface, adding methods that support ad-hoc SQL generation and query execution in a single call.
Behind the scenes, different subclasses 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.
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.
The following methods retrive data from the datasource 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.
Argument Pairs:
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 (unless explicit "sql => ..." is used). 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:
$hashes = $sqldb->fetch_select( sql => 'select * from students where id > 200' );
$hashes = $sqldb->fetch_select( sql => [ 'select * from students where id > ?', 200 ] );
$hashes = $sqldb->fetch_select( table => 'students', criteria => { 'status'=>'minor' } );
$hashes = $sqldb->fetch_select( table => 'students', columns => 'name, age', order => 'name' );
$hashes = $sqldb->fetch_select( tables => 'students, grades', criteria => 'students.id = grades.student_id', order => 'students.name' );
$sqldb->visit_select( $code_ref, %sql_clauses ) : @results
Takes the same arguments as fetch_select. 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->visit_select( sub { my $student = shift; ... do something with %$student ... }, table => 'student' );
$sqldb->visit_select( sub { my $student = shift; ... do something with $student->{id} and $student->{name} ... }, table => 'student', columns => 'id, name', order => 'name, id desc' );
$sqldb->fetch_one_row( %sql_clauses ) : $row_hash
Calls fetch_select, then returns only the first row of results.
$joe = $sqldb->fetch_one_row( table => 'student', criteria => { 'id' => 201 } );
$joe = $sqldb->fetch_one_row( sql => [ 'select * from students where id = ?', 201 ] );
$sqldb->fetch_one_value( %sql_clauses ) : $scalar
Calls fetch_select, then returns a single value from the first row of results.
$maxid = $sqldb->fetch_one_value( sql => 'select max(id) from students where status = ?' );
$count = $sqldb->fetch_one_value( table => 'student', columns => 'count(*)' );
$sqldb->do_insert( %sql_clauses )
Insert a single row into a table in the datasource.
Required (unless explicit "sql => ..." is used). 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.
$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' ] );
Modify one or more rows in a table in the datasource.
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 ] );
Delete one or more rows in a table in the datasource.
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' ] );
To determine if the connection is working and whether a table exists.
$sqldb->detect_any () : $boolean
Attempts to confirm that values can be retreived from the database, using a server-specific "trivial" or "guaranteed" query provided by the subclass. Catches any exceptions; if the query fails for any reason we return nothing.
$sqldb->detect_table ( $tablename ) : @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 the subclass. Catches any exceptions; if the query fails for any reason we return nothing.
$sqldb->do_create_table( $tablename, $column_hash_ary )
Create a table.
$sqldb->do_drop_table( $tablename )
Delete a table.
$sqldb->DBILogging : $value $sqldb->DBILogging( $value )
Set this to a true value to turn on logging. 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->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.
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, roark@evolution.com E. J. Evans, piglet@piglet.org
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.
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.