At 6:14 PM +1200 7/5/05, Sam Vilain wrote:
I think I'm beginning to like it.
Allow me to suggest one or two further refinements...
my $sth1 = $dbh.compile( $sql_or_ast ); # always sans connection
$sth1.prepare(); # always with connection, even if DBD doesn't use it
$sth1.execute(); # always with connection
FYI, I'm not stuck on the name 'compile'; the method could be called
something else. In fact, I still think 'prepare' is more broadly
descriptive considering the wide range of things that could be
happening inside that method; I see it as ultimately getting ready
for the execute() anyway. The problem is that the word 'prepare' is
commonly associated with something different, so we're stuck having
to find a different word.
To me, the "compiled" form of the STH is related to the driver, but
re-usable between connections; you should be able to use something like;
my $sth1 = DBD::SQLite.compile( $sql_or_ast );
$sth1 = DBI.compile( :statement($sql_or_ast), :driver<SQLite> );
This would give you a STH which is divorced from the actual DB connection
instance.
Yes, that is true. However, I like for all application calls to go
through the Interface module (DBI) since that is what the Interface
is for. It is also more practical to go by way of the DBI module
because it can do things for you like certain kinds of validation on
application input and driver output, so the application or driver
respectively doesn't have to do those tests for robustness. Each DBI
driver can worry less about that its input is correct and focus more
on its actual work. (FYI, a common input/output validation is one of
the main things that the 'Rosetta' module does for its own drivers
and users. More so in next release v0.46.)
Because you constructed it like this, without reference to a
(possibly unconnected) connection object, then $sth1.prepare is not
available.
I wouldn't worry about that; the proposed DBI would elegantly handle
(throw an exception) attempts to invoke methods that depend on an
open connection when there is none. People who like to check first
will also have a $dbh.is_open() method available to them. I also see
the situation as no worse than the current DBI v1 where you can
invoke such things as fetchrow_arrayref() on a $sth that hasn't been
executed yet.
Oh, and I have another DBI v2 suggestion to add, also following Rosetta design:
1. A $sth should not contain any methods for fetching the result of
an executed statement; rather, execute() should return an object when
successful that represents its result; you invoke fetching methods on
that object. For example:
my $rlh = $sth->execute();
my $rowset = $rlh->fetchrow_arrayref();
This approach is a lot more flexible.
Well, when you parse the statement into an AST, the flavour of SQL will
affect how it is parsed and what is allowed. Eg, Oracle has significant
features in some comments (query hints). It also has quirky and somewhat
useless keywords like CONNECT BY.
So, when you ask a DBH connected to a driver to parse something, then it
will use that driver's SQL dialect, if one exists, but I still want to be
able to deal with SQL ASTs without implying a SQL flavour.
You still can. The point of an "abstract syntax tree" is that the
original SQL dialect isn't very important to what it contains (and
this is how SQL::Routine is); by contrast, a "concrete syntax tree"
cares very much about the original syntax and preserves it to the
last detail. I don't see a problem here.
...
Except that those AST-like-modules which embed raw SQL fragments
(like where-clauses) as a matter of course aren't actually that
abstract and could pose a problem; lots of DBI wrappers are this way,
unfortunately (but not mine).
I am operating under the assumption that this system should work if
there are no external config files that the DBI/DBD would read, and
the application would provide that information; if its in a file,
the application would read it in, or would explicitly tell DBI
where it is. Or at least it should be possible for this to happen,
even if a DBD defaults to look in a default location when it
doesn't get the equivalent from the application.
Absolutely, that must work. But it would still be nice to be able to
config this without digging through the application to see where the
password is written.
Er, I hope you didn't interpret this as the application source code.
My point was that the database connection details can be part of the
general application configuration file, which is still data and not
code. This way, each application can have distinct database settings
if it wants to.
Such a great word, abhorrent. So fitting for this case. It sure does
look like an (over&premature&misguided)-optimisation to avoid using the
full module name in an internal hash or something like that. But then
maybe (I&we&none(Gaia)) are missing some context there.
Well, I have also decided that I can accept one specific type of
flexability on the 'whole module name' thing. That is, if we assume
that DBI drivers will commonly have 'DBD::' at the start of their
names, users can optionally leave that prefix off, but they still
have to spell the rest of the name out in full and with the correct
letter case. In this case, when trying to load a driver, DBI would
first try the pristine module name it was given, and if a module
fails to load by that name, then it will try adding 'DBD::' to the
front as an alternative; failing those two, it will croak. But the
point is, aside from the text 'DBD::', which is reasonable as it is a
general case, there are no hard coded driver names in DBI.
Another suggestion:
2. Unless there is a strong impetus for there being a separate root
namespace for DBI drivers, I suggest a better naming scheme would be
to put everything under DBI:: instead. For example,
DBI::Driver::SQLite or DBI::D::SQLite, or DBI::DBD::SQLite. If this
idea is adopted, then that auto-prefix thing I previously mentioned
would be changed accordingly. I think that having the standard
driver namespace being outside DBI::* is an antiquated notion that
bears little resemblence to what is standard in most frameworks. All
this said, I'm not stuck on a change here, so do as you will.
-- Darren Duncan
