FAQ
[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

Search Discussions

  • Paul Boutros at Feb 28, 2005 at 12:58 am
    Hi Jeff,

    I'm on:
    AIX 5.2.0.0
    Perl 5.8.0.0
    DBI 1.46

    I get an error on make test with:

    $ perl Makefile.PL LIB=/db2blast/Perl
    Checking if your kit is complete...
    Looks good
    Writing Makefile for SQL::Statement
    $ make
    cp lib/SQL/Dialects/ANSI.pm blib/lib/SQL/Dialects/ANSI.pm
    cp lib/SQL/Parser.pm blib/lib/SQL/Parser.pm
    cp lib/SQL/Statement/GetInfo.pm blib/lib/SQL/Statement/GetInfo.pm
    cp lib/SQL/Statement.pm blib/lib/SQL/Statement.pm
    cp lib/SQL/Dialects/CSV.pm blib/lib/SQL/Dialects/CSV.pm
    cp lib/SQL/Eval.pm blib/lib/SQL/Eval.pm
    cp lib/SQL/Dialects/AnyData.pm blib/lib/SQL/Dialects/AnyData.pm
    cp lib/SQL/Statement/Functions.pm blib/lib/SQL/Statement/Functions.pm
    Manifying blib/man3/SQL::Dialects::ANSI.3
    Manifying blib/man3/SQL::Parser.3
    Manifying blib/man3/SQL::Statement.3
    Manifying blib/man3/SQL::Dialects::CSV.3
    Manifying blib/man3/SQL::Eval.3
    Manifying blib/man3/SQL::Dialects::AnyData.3
    Manifying blib/man3/SQL::Statement::Functions.3
    Target "makemakerdflt" is up to date.
    $ make test
    PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e"
    "test_har
    ness(0, 'blib/lib', 'blib/arch')" t/*.t
    t/01basics....ok 4/100SQL ERROR: Bad predicate: 'id'!

    SQL ERROR: Bad predicate: 'id'!

    # Looks like you planned 100 tests but only ran 4.
    # Looks like your test died just after 4.
    t/01basics....dubious
    Test returned status 255 (wstat 65280, 0xff00)
    DIED. FAILED tests 5-100
    Failed 96/100 tests, 4.00% okay
    Failed Test Stat Wstat Total Fail Failed List of Failed
    ----------------------------------------------------------------------------
    ---
    t/01basics.t 255 65280 100 96 96.00% 5-100
    Failed 1/1 test scripts, 0.00% okay. 96/100 subtests failed, 4.00% okay.
    make: 1254-004 The error code from the last command is 2.


    Stop.
    $
    -----Original Message-----
    From: Jeff Zucker
    Sent: Saturday, February 26, 2005 3:21 PM
    To: dbi-users; dbi-dev
    Subject: 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
  • Jeff Zucker at Feb 28, 2005 at 1:15 am
    Hi Paul,

    Paul Boutros wrote:
    Hi Jeff,

    I'm on:
    AIX 5.2.0.0
    Perl 5.8.0.0
    DBI 1.46

    I get an error on make test with:
    ...
    SQL ERROR: Bad predicate: 'id'!
    Yes, sorry, I've fixed that and a few other bugs, and Dean has added
    some more code. The SQL::Statement::Functions pod is much expanded and
    I've added CREATE RAM TABLE, see pod in SQL::Parser under temporary tables.

    New version with all that added is now available at:

    http://www.vpservices.com/jeff/programs/SQL-Statement-1.10.tar.gz

    Thanks, much for testing!

    --
    Jeff
  • Paul Boutros at Feb 28, 2005 at 1:58 am
    Yup, the new version install cleanly. If I come across any problems I'll
    let you know.
    Paul
    -----Original Message-----
    From: Jeff Zucker
    Sent: Sunday, February 27, 2005 8:19 PM
    To: Paul Boutros
    Cc: dbi-users
    Subject: Re: New DBI in-memory tables, heterogeneous operations,
    SQL-Statement


    Hi Paul,

    Paul Boutros wrote:
    Hi Jeff,

    I'm on:
    AIX 5.2.0.0
    Perl 5.8.0.0
    DBI 1.46

    I get an error on make test with:
    ...
    SQL ERROR: Bad predicate: 'id'!
    Yes, sorry, I've fixed that and a few other bugs, and Dean has added
    some more code. The SQL::Statement::Functions pod is much expanded and
    I've added CREATE RAM TABLE, see pod in SQL::Parser under
    temporary tables.

    New version with all that added is now available at:

    http://www.vpservices.com/jeff/programs/SQL-Statement-1.10.tar.gz

    Thanks, much for testing!

    --
    Jeff

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedFeb 26, '05 at 8:18p
activeFeb 28, '05 at 1:58a
posts4
users2
websitedbi.perl.org

2 users in discussion

Paul Boutros: 2 posts Jeff Zucker: 2 posts

People

Translate

site design / logo © 2022 Grokbase