I've set my Reply-to to just [EMAIL PROTECTED] as I'm not fond of
cross-posting between the two lists. So could people reply just to
[EMAIL PROTECTED] please. Thanks.
On Wed, Jun 04, 2003 at 09:15:28AM -0700, Jeff Zucker wrote:
> I will be releasing a significantly upgraded SQL::Statement and
> DBD::File shortly and I have some questions about interface. I'd really
> appreciate some feedback. These are the features that are near
> finalization:
>
> * 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.
(Note that func() isn't needed now the DBI has exposed install_method().)
> 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 ...
I'd like to be able to refer to existing dbh's as well.
I'd also like to be able to treat an existing $sth as a 'table'.
> and/or
>
> SELECT $cols FROM tbl1@"$dsn1" ... JOIN tbl2@"$dsn2" ...
Both are a little ugly and prone to nested quote problems.
I think some form of naming would be handy:
$dbh->{sql_sources} = {
foo => 'dbi:Driver:dbname", # qualify with table name
bar => $dbh, # qualify with table name
baz => $sth, # implies select * from itself
};
then:
SELECT $cols FROM [EMAIL PROTECTED] JOIN [EMAIL PROTECTED] ...
> and/or, for DSNs that can't be reduced to strings
>
> $dbh->prepare(q{ SELECT $cols FROM [EMAIL PROTECTED] ... JOIN [EMAIL PROTECTED]
> ... }
> ,{}
> ,$dsn1
> ,$dsn2
> );
[FYI, I'm rather fond of the nested select syntax used by Oracle,
mysql v4, and others:
SELECT foo, bar FROM (SELECT ...) table_alias WHERE ...
also, I recently saw that the SQL99 standard allows
WITH RECURSIVE
Q1 AS SELECT ...
Q2 AS SELECT ...
SELECT ... FROM Q1, Q2 WHERE ...
which might be a useful style. But the [EMAIL PROTECTED] is fine.]
> 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 ...
"CREATE INTO TABLE" is a new one on me. I think "CREATE TABLE ... AS SELECT"
is more common. Are you specifically choosing non-standard syntax?
> SELECT INTO x@"$dsn1" ($newcols)
> $oldcols FROM y@"$dsn2" WHERE ...
Same here, "INSERT INTO ... SELECT" is more common.
> 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)
> );
I'm not really sure what you're saying here, for those two.
> 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
I think the nested select syntax may be more natural/obvious here:
SELECT format_as_foo(table.*) FROM (SELECT ...) table
but maybe that doesn't fit with with the [EMAIL PROTECTED] style you're using.
> For directly querying strings, AoAs, and AoHs
> ---------------------------------------------
> my $sth=$dbh->prepare(
> "SELECT $cols FROM @? WHERE ...",
> {},
> {format=>'AoA',source=>$data_array_ref}
> );
This reminds me that I have vague plans to introduce an explicit 'record set'
object that would encapsulate result data and metadata ($sth->{NAME} etc).
It would be a natural part of a scrollable cursor implementation
but I've never got round to it. But in the meantime, $data_array_ref
should be able to be an $sth.
> All of this infrstructure is really just an excuse to be able to write
> the JAPH in my sig. :-)
:-)
> Thanks for any comments.
This is all great stuff.
I've long wanted 'stackable' drivers to do 'fancy plumbing' for the DBI.
First we had DBD::Proxy, then DBD::Multiplex (currently being
revamped and expanded), and now DBD::AnyData. Thank you!
Tim.
