[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
