SQL::Statement::Embed 1.15 review

SQL::Statement::Embed can embed a SQL engine in a DBD or module. SQL::Statement is designed to be easy to embed in other modules a

License: Perl Artistic License File size: 85K Developer: Jeff Zucker

0

SQL::Statement::Embed can embed a SQL engine in a DBD or module.

SQL::Statement is designed to be easy to embed in other modules and to be especially easy to embed in DBI drivers. It provides a SQL Engine and the other module needs to then provide a data source and a storage mechanism. For example, the DBD::CSV module uses SQL::Statement as an embedded SQL engine by implementing a file-based data source and by using DBI as the user interface. Similarly DBD::Amazon uses SQL::Statement as its SQL engine, provides its own extensions to the supported SQL syntax, and uses on-the-fly searches of Amazon.com as its data source.

SQL::Statement is the basis for eight existing DBDs (DBI database drivers). If you have a new data source, you too can create a DBD without having to reinvent the SQL wheel. It's fun, it's easy, become a DBD author today!

SQL::Statement can be also be embedded without DBI. We'll explore that first since developing a DBD uses most of the same methods and techniques.

The role of SQL::Statement subclasses

SQL::Statement provides a SQL parsing and execution engine. It does not provide a data source or storage mechanism other than in-memory tables. The DBD::File module is a subclass of SQL::Statement that provides access to file-based storage mechanisms. It's quite possible to use things other than files as data souces, in which case we wouldn't use DBD::File, instead we'd replace DBD::File's methods with our own. In the examples below, we'll use DBD::File, replacing only a few methods.

SQL::Statement provides SQL parsing and evaluation and DBD::File provides file-based storage. The only thing missing is a data source - what we actually want to store and query. As an example suppose we are going to create a subclass called 'Foo' that will provide as a data source a simple file similar to a passwd file - one record per line, fields separated by colons, with only three fields "username, uid, gid".

Consider what needs to happen to perform a SELECT query on our 'Foo' data:

* recieve a SQL string
* parse the SQL string into a request structure
* open the table(s) specified in the request
* define column names and postions for the table
* read rows from the table
* convert the rows from colon-separated format into perl arrays
* match the columns and rows against the requested selection criteria
* return requested rows and columns to the user

To perform operations like INSERT and DELETE, we also need to:

* convert rows from perl arrays into colon-separated format
* write rows
* delete rows

SQL::Statement takes care of all of the SQL parsing and evaluation. DBD::File takes care of file opening, reading, writing, and deleting. So the only things 'Foo' is really responsible for are:

* define column names and postions for the table
* convert rows from colon-separated format into perl arrays
* convert rows from perl arrays into colon-separated format

In SQL::Statement subclasses these responsibilities are assigned to two objects, a ::Statement object is responsible for opening the table, defining the column names and positions, and for creating new ::Table objects. A ::Table object is responsible for reading, converting, writing, and deleting data.

Requirements:
Perl