View on
MetaCPAN is shutting down
For details read Perl NOC. After June 25th this page will redirect to
Matthew Simon Cavalletto > DBIx-SQLEngine-0.91 > DBIx::SQLEngine



Annotate this POD

Related Modules



New  2
Open  0
View/Report Bugs
Module Version: 0.91   Source   Latest Release: DBIx-SQLEngine-0.93


DBIx::SQLEngine - Extends DBI with High-Level Operations


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. An object mapping layer provides classes for tables, columns, and records.


DBI Wrapper: Adds methods to a DBI database handle.

  $sqldb = DBIx::SQLEngine->new( $dbi_dsn, $dbi_user, $dbi_passwd );
  $sqldb = DBIx::SQLEngine->new( $dbh ); # or use your existing handle

  $dbh = $sqldb->get_dbh();              # get the wraped DBI dbh
  $sth = $sqldb->prepare($statement);    # or just call any dbh method

High-Level Interface: Prepare and fetch in one call.

  $row_count = $sqldb->try_query($sql, \@params, 'get_execute_rowcount');
  $array_ary = $sqldb->try_query($sql, \@params, 'fetchall_arrayref');
  $hash_ary  = $sqldb->try_query($sql, \@params, 'fetchall_hashref');

Data-Driven SQL: SQL generation with flexible arguments.

  $hash_ary = $sqldb->fetch_select( 
    table => 'students', where => { 'status'=>'minor' },
    table => 'students', 
    values => { 'name'=>'Dave', 'age'=>'19', 'status'=>'minor' },
    table => 'students', where => 'age > 20',
    values => { 'status'=>'adult' },
    table => 'students', where => { 'name'=>'Dave' },

Named Definitions: Pre-define connections and queries.

    'test'       => 'dbi:AnyData:test',
    'production' => [ 'dbi:Mysql:our_data:dbhost', 'user', 'passwd' ],

    'all_students'  => 'select * from students',
    'delete_student' => [ 'delete * from students where id = ?', \$1 ],

  $sqldb = DBIx::SQLEngine->new( 'test' );

  $hash_ary = $sqldb->fetch_named_query( 'all_students' );

  $rowcount = $sqldb->do_named_query( 'delete_student', $my_id );

Portability Subclasses: Uses driver's idioms or emulation.

  $hash_ary = $sqldb->fetch_select( # uses database's limit syntax 
    table => 'students', order => 'last_name, first_name',
    limit => 20, offset => 100,    
  $hash_ary = $sqldb->fetch_select( # use "join on" or merge with "where"
    table => ['students'=>{''=>\'grades.student'}=>'grades'],
    where => { 'academic_year'=>'2004' },
  $hash_ary = $sqldb->fetch_select( # combines multiple query results
    union => [ { table=>'students', columns=>'first_name, last_name' },
               { table=>'staff',    columns=>'name_f, name_l' }        ],

  $sqldb->do_insert(                # use auto_increment/sequence column
    table => 'students', sequence => 'id',        
    values => { 'name'=>'Dave', 'age'=>'19', 'status'=>'minor' },

Object Mapping: Classes for tables, columns, and records.

  $table = $sqldb->table('grades');

  $hash_ary = $table->fetch_select(); 

  $table->delete_row( $primary_key ); 

  $sqldb->record_class( 'students', 'My::Student' );

  @records = My::Student->fetch_select( 
                where => 'age > 20',
                order => 'last_name, first_name',

  $record = My::Student->new_with_values( 'first_name' => 'Dave' );

  $record = My::Student->fetch_record( $primary_key );

  print $record->get_values('first_name', 'last_name');

  $record->change_values( 'status' => 'adult' );



DBIx::SQLEngine is the latest generation of a toolkit used by the authors for several years to develop business data applications. Its goal is to simplify dynamic query execution and to minimize cross-RDMS portability issues.

DBI Wrapper

Each DBIx::SQLEngine::Driver 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. Error handling is standardized, and routine annoyances like timed-out connections are retried automatically.

The Driver also allows direct access to the wrapped database handle, enabling use of the entire DBI API for cases when high-level interfaces are insufficient.

Relevant methods are descrbed in the "Driver Object Creation", "Connection Methods", and "Statement Methods" sections of DBIx::SQLEngine::Driver.

High-Level Interface

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

These methods are defined in "Fetching Data", "Editing Data", and "Defining Structures" sections of DBIx::SQLEngine::Driver.

Data-Driven SQL

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.

Named Definitions

Connections and queries may be registered in named collections. The named connection feature allows the definition of names for sets of connection parameters, while the named query methods support names for various types of queries in either data-driven or plain-SQL formats.

The definitions may include nested data structures with a special type of placeholders to be replaced by additional values at run-time. References to subroutines can also be registed as definitions, to be called at run-time with any additional values to produce the connection or query arguments.

This functionality is described in "Named Connections" and "Named Query Catalog" sections of DBIx::SQLEngine::Driver.

Portability Subclasses

Behind the scenes, different subclasses of SQLEngine are instantiated depending on the type of server to which you connect, thanks to DBIx::AnyData.

This release includes subclasses for connections to MySQL, PostgreSQL, Oracle, Informix, Sybase, and Microsoft SQL servers, as well as for the standalone SQLite, AnyData, CSV and XBase packages. For more information about supported drivers, see "Driver Subclasses" in DBIx::SQLEngine::Driver.

As a result, if you use the data-driven query interface, 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.

However, some features can not be effectively emulated; it's no use to pretend that you're starting a transaction if your database don't have a real atomic rollback/commit function. In those areas, the subclasses provide capability methods that allow callers to determine whether the current driver has the features they require. Features which are only available on a limited number of platforms are listed in "ADVANCED CAPABILITIES" in DBIx::SQLEngine::Driver.

Object Mapping

Built on top of the core SQLEngine functionality is an object mapping layer that provides a variety of classes which serve as an alternate interface to database content.

The Schema classes provide objects for tables and columns which call methods on a SQLEngine to fetch and store data, while the Record classes provide a means of creating subclasses whose instances map to to rows in a particular table using the Schema classes.

Note that this is not a general-purpose "object persistence" system, or even a full-fledged "object-relational mapping" system. It is rather a "relational-object mapping" system: each record class is linked to a single table, each instance to a single row in that table, and each key in the record hash to a value in an identically named column.

Furthermore, no effort has been made to obscure the relational implementation behind the object abstraction; for example, if you don't need the portability provided by the data-driven query interface, you can include arbitrary bits of SQL in the arguments passed to a method that fetch objects from the database.

This functionality is described in "OBJECT MAPPING" and the documentation for the various classes.


DBIx::SQLEngine is an object-oriented framework containing several class hierarchies grouped into three layers. Applications can use the Driver layer directly, or they can use the Schema or Record layers built on top of it.

Driver Layer

The Driver layer is the primary and lowest-level layer upon which the other layers depend. Each Driver object contains a DBI database handle and is responsible for generating SQL queries, executing them, and returning the results.

See DBIx::SQLEngine::Driver and DBIx::SQLEngine::Criteria.

Schema Layer

The Schema layer centers around the Table object, which combines a Driver object with the name of a table to perform queries against that table. Table objects keep track of their structure as Column objects, and use that information to facilitate common types of queries.

See DBIx::SQLEngine::Table and DBIx::SQLEngine::Column, as well as DBIx::SQLEngine::TableSet and DBIx::SQLEngine::ColumnSet.

Record Layer

The Record layer allows you to create Perl classes which are bound to a given Table object. Your Record subclass can fetch rows from the table which will be blessed into that class, and have methods allowing them to be changed and updated back to the database.

See DBIx::SQLEngine::Record::Class and DBIx::SQLEngine::Record::Set.


This section briefly introduces some of the methods provided by the Driver layer.


Create one SQLEngine Driver for each DBI datasource you will use.

  DBIx::SQLEngine->new( $dsn, $user, $pass ) : $sqldb
  DBIx::SQLEngine->new( $dbh ) : $sqldb
  DBIx::SQLEngine->new( $cnxn_name, @params ) : $sqldb

Creates a Driver object with associated DBI database handle

  DBIx::SQLEngine->define_named_connections( $name, $cnxn_info )

Defines one or more named connections using the names and definitions provided.


Select to Retrieve Data

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->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. Returns the results returned by each of those function calls.


Insert, Update and Delete

You can perform database modifications with these methods.

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


Named Query Catalog

These methods manage a collection of named query definitions.

  $sqldb->define_named_queries( $query_name, $query_info )

Defines one or more named queries using the names and definitions provided.



The following provides a brief overview of methods provided by the schema classes.

Querying Table Objects

Table objects pass the various fetch_ and do_ methods through to the SQLEngine Driver along with their table name.

Enumerating TableSets

A Schema::TableSet is simply an array of Schema::Table objects.

  $tableset->count : $number_of_tables
  $tableset->tables : @table_objects
  $tableset->table_named( $name ) : $table_object


For more information see the documentation for these packages: DBIx::SQLEngine::Schema::Table, DBIx::SQLEngine::Schema::TableSet, DBIx::SQLEngine::Schema::Column, and DBIx::SQLEngine::Schema::ColumnSet.


The following provides a brief overview of methods provided by the record classes.

Setting Up a Record Class

Selecting Records

Changing Records

For more information see the documentation for these packages: DBIx::SQLEngine::Record::Base and DBIx::SQLEngine::Record::Set.


The following three examples, based on a writeup by Ron Savage, show a connection being opened, a table created, several rows of data inserted, and then retrieved again. Each uses one of the Driver, Table, or Record interfaces to accomplish the same tasks.


Many types of databases are not yet directly supported. While the default functionality should work with any DBI-accessible data source, support for complex queries and advanced features will typically require the addition of a Driver subclass which compensates for local idiom.

Some of the more advanced capabililities have only been added recently, and have not yet been tested in real-world conditions.

See DBIx::SQLEngine::ToDo for a list of bugs and missing features.


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.

The driver interface is described in DBIx::SQLEngine::Driver.

For distribution, installation, support, copyright and license information, see DBIx::SQLEngine::Docs::ReadMe.

syntax highlighting: