* heterogeneous SQL across multiple DBI sources * per-table DBI connections on a single dbh * improved API for DBD writers * support for in-memory tables ala DBD::RAM for all SQL::Statement DBDs * improved ability to specify SQL dialect features on a per-DBD, per-session, or per-statement basis * user-supplied functions, procedures, and persistent stored modules
For heterogeneous DBI sources, all statements except joins are executed directly by the relevant DBD after a pre-parse by SQL::Statement so should be close to as fast as the DBD/rdbms itself. For joins, the physical fetches are handled by the underlying DBDs but the join and where_clause evaluation are handled by SQL::Statement so these will be slow as sin. The slowness applies only to joins, not to non-join multi-table subqueries which should be close to as fast as similar statements executed directly in the relevant DBDs.
In terms of the interface to all of this, I am leaning toward SQL extensions and away from $dbh->func() calls and $dbh->{attr} settings but I'm open to all suggestions. This means support for the following kinds of constructs (though the [EMAIL PROTECTED] form may be too much):
For control over SQL dialect features
-------------------------------------
CREATE/DROP TYPE
CREATE/DROP KEYWORD
CREATE/DROP FUNCTION For per-table connections
-------------------------
CONNECT TO '$dsn1' AS tbl1
CONNECT TO '$dsn2' AS tbl2
SELECT $cols FROM tbl1 ... JOIN tbl2 ...and/or
SELECT $cols FROM tbl1@"$dsn1" ... JOIN tbl2@"$dsn2" ...
and/or, for DSNs that can't be reduced to strings
$dbh->prepare(q{ SELECT $cols FROM [EMAIL PROTECTED] ... JOIN [EMAIL PROTECTED]
... }
,{}
,$dsn1
,$dsn2
); For importing, exporting, converting heterogeneous sources
----------------------------------------------------------
convert data from one rdbms to another CREATE INTO TABLE x@"$dsn1" ($new_col_defs)
SELECT $oldcols FROM y@"$dsn2" WHERE ... SELECT INTO x@"$dsn1" ($newcols)
$oldcols FROM y@"$dsn2" WHERE ...import/export in-memory tables
CREATE INTO TABLE [EMAIL PROTECTED] SELECT $cols FROM y@"$dsn2" ...
CREATE INTO TABLE x@"$dsn2" SELECT $cols FROM [EMAIL PROTECTED] ...
For calling temporary & stored procedures, functions, & modules ---------------------------------------------------------------
my $rv = $dbh->do( # Run a procedure or function
"CALL $procedure($args)"
);print $dbh->selectrow_array( # print results of a function or
"SELECT function($args)" # procedure (no FROM or WHERE clause)
);
$dbh->do("LOAD psmfile"); # Load & run SQL dialect commands,
# or DDL, or procedures or
# CONNECTION setings
For printing out resultsets in formats --------------------------------------
print $dbh->selectrow_array(
"SELECT INTO @$format $cols FROM ..."
);note: $format could be any format that is based on SQL::Statement, like
CSV, AnyData::Fixed, AnyData::XML, etc. or it could be a coderef
to a user-supplied routine that accepts a table in AoA and
outputs a formatted string
For directly querying strings, AoAs, and AoHs
---------------------------------------------
my $sth=$dbh->prepare(
"SELECT $cols FROM @? WHERE ...",
{},
{format=>'AoA',source=>$data_array_ref}
);All of this infrstructure is really just an excuse to be able to write the JAPH in my sig. :-)
Thanks for any comments.
--
Jeff
perl -MDBI -e 'print DBI->connect("dbi:AnyData:")->selectrow_array(
"SELECT DISTINCT col0 FROM @? ORDER BY col1",{},{format=>"AoA",
source=>[["H",4],["A",2],["J",1],["J",1],["P",3]]})'