New DBI in-memory tables, heterogeneous operations, SQL-Statement

[cc to dbi-dev but please reply to dbi-users or to me]

I'm making available a pre-release version of SQL::Statement with many 
new features including vastly improved parens parsing (thanks Dean 
Arnold), column aliases (thanks Robert Rothenberg), new built-in 
functions including SOUNDER() and REGEX(),support for user-defined 
functions, in-memory tables, and heterogeneous operations across all DBI 
data sources.

This release will be of interest to all DBI users, in addition to users 
of SQL::Statement and the DBDs which subclass it.  Why?  Because it 
supports the use of any of the SQL::Statement DBDs (including DBD::File 
which comes with DBI) to create temporary in-memory tables and to 
perform heterogeneous operations using *any* DBI data sources.  This 
means you can do a join on a PostgreSQL table and a MySQL table.  Or do 
a join query on two XBase tables even though XBase itself doesn't 
support joins.  Or load a selection from an Oracle table into a 
temporary in-memory table, cache that table, then perform SQL queries on 
it without going back to Oracle.

DBD::File and the other SQL::Statement subclassing DBDs can now be used 
as utilities to perform multiplexing and heterogeneous operations.  They 
function somewhat similarly to DBD::Multiplex, but they use *per-table* 
multiplexing.  In other words, once a DBI connection has been associated 
with an in-memory table, the data from that connection can be used 
simply by referring to the table's name in a SQL statement.

All of these features are currently working (knock on wood) and 
available for testing.  Please grab the pre-release version at 
http://www.vpservices.com/jeff/programs/SQL-Statement-1.10.tar.gz.  
(This is a brand new version so re-grab if you grabbed it from the 
PerlMonks posting). See the included SQL::Statement::Function.pm for 
documentation of built-in and user-defined functions.  Here's the POD 
section on in-memory tables and heterogeneous operations.  Even if you 
don't grab the tarball, I'd appreciate comments on the proposed syntax 
shown below.

=head2 In-memory tables & heterogeneous operations

You can now use any DBD that subclasses SQL::Statement (e.g. DBD::File, 
DBD::DBM, DBD::CSV) to create temporary in-memory tables from perl data 
structures or from DBI heterogeneous queries.  This is the SQL syntax to 
create the in-memory table:

 CREATE RAM TABLE tblname LIKE ?

The placeholder can be replaced by an AoA (a reference to an array of 
array references) or by a DBI database handle with additional arguments.

In the case of an AoA, the first row of the AoA must be the column names 
of the table.  Here's an example in which the column names are 'id' and 
'word':

 $dbh->do( "CREATE RAM TABLE Japh LIKE ?", {}, [
           [qw( id word    )],
           [qw( 1  Hacker  )],
           [qw( 2  Perl    )],
           [qw( 3  Another )],
           [qw( 4  Just    )],
 ]);

Once the in-memory RAM table is created, it can be accessed from SQL 
like any other table, for example, using the RAM table 'Japh' created by 
the statement above:

 printf "%s\n", join ' ',@{ $dbh->selectcol_arrayref("
     SELECT word FROM Japh ORDER BY id DESC
 ")};
 #
 # outputs "Just Another Perl Hacker"

In the case of DBI heterogeneous queries, the placeholder should be 
replaced by a) any valid DBI database handle ($dbh) and b) any valid 
data-returning SQL query that can be executed by that database handle.

For example, if you have a PostgreSQL table called 'phrase' that has 
columns 'id' and 'phrase', this will create a temporary in-memory table 
containing a selected subset of that table:

 $dbh->do(
    "CREATE RAM TABLE Pg_phrases LIKE ?", {},
     DBI->connect('dbi:Pg:dbname=test1'),
     "SELECT id,phrase from phrase WHERE id < 10"
 );

If the heterogeneous query requires placeholders, those may be appended 
to the placeholders from the original statement.  For example, the 
statement above, done with a placeholder in the Pg query would look like 
this:

 $dbh->do(
    "CREATE RAM TABLE Pg_phrases LIKE ?", {},
     DBI->connect('dbi:Pg:dbname=test1'),
     "SELECT id,phrase from phrase WHERE id < ?",
     10
 );

The "10" (and values following it) will become the value(s) for the 
placeholder in the SELECT statement to the PostgreSQL table.

As with AoA in-memory tables, once a heterogeneous query in-memory table 
is created, it can be used in a SQL statement like any other table.  
Here's an example that creates one in-memory table from a heterogeneous 
query to a PostgreSQL table, then creates a second in-memory table from 
a heterogeneous query to an XBase table, then does a query joining the 
two in-memory tables:

 # 1. connect to any DBD that subclasses SQL::Statement
 #
 my $dbh  = DBI->connect('dbi:File(RaiseError=1):');

 # 2. create a RAM table from a heterogeneous query to PostgreSQL
 #
 $dbh->do(
    "CREATE RAM TABLE Pg_phrases LIKE ?", {},
     DBI->connect('dbi:Pg:dbname=test1'),
     "SELECT id,phrase from phrase WHERE id < 10"
 );

 # 3. create a RAM table from a heterogeneous query to XBase
 #
 $dbh->do(
    "CREATE RAM TABLE Xb_phrases LIKE ?", {},
     DBI->connect('dbi:XBase:./'),
     "SELECT id,phrase from phrase WHERE id < 10",
 );

 # 4. use the RAM tables in SQL statements ...
 #
 my $sth = $dbh->prepare("SELECT * FROM Pg_phrases NATURAL JOIN 
Xb_phrases");
 $sth->execute;
 $sth->dump_results;

SQL that is supplied as a placeholder in the CREATE RAM TABLE statement 
follows the syntax for whatever datasource is specified (e.g. PostgreSQL 
in #2 above and XBase in #3 above).  SQL that is run from the 
SQL-Statement DBD (e.g. #4 above) follows SQL syntax of that DBD 
(DBD::File in the example).

=cut

Thanks in advance for your comments and suggestions.

-- 
Jeff

• New DBI in-memory tables, heterogeneous operations, SQL-Statement by Jeff Zucker
• RE: New DBI in-memory tables, heterogeneous operations, SQL-Statement by Paul Boutros
• Re: New DBI in-memory tables, heterogeneous operations, SQL-Statement by Jeff Zucker
• RE: New DBI in-memory tables, heterogeneous operations, SQL-Statement by Paul Boutros