Darren Duncan wrote:
3. Redefine prepare() and execute() such that the first is expressly for
activities that can be done apart from a database (and hence can also be
done for a connection handle that is closed at the time) while all
activities that require database interaction are deferred to the second.
That would be nice, but there are some DBDs for which you need the database
on hand for $dbh.prepare() to work. In particular, DBD::Oracle.
I think that what you are asking for can still work, though;
# this module creates lots of SQL::Statement derived objects, without
# necessarily loading DBI.
use MyApp::Queries <%queries>;
# not connect, so doesn't connect
my $db = DBI.new( :source("myapp") );
# prepare the objects as far as possible
my %sths;
for %queries.kv -> $query_id, $query_ast_or_template {
%sths{$query_id} = $db.prepare($query_ast_or_template);
}
# connect
$db.connect;
# now proceed as normal
my $sth = %sths<some_query_id>;
$sth.execute( :param("foo"), :this("that") );
So, effectively the prepare can happen at any time, and it's up to the
DBD to decide whether to actually do anything with it immediately or not.
ie, on Pg the STHs would be built before the DB is connected, and on Oracle
they are built the first time they are used (and then cached).
Now I realize that it may be critically important for an application to
know at prepare() time about statically-determinable errors, such as
mal-formed SQL syntax, where error detection is handled just by the
database. For their benefit, the prepare()+execute() duality could be
broken up into more methods, either all used in sequence or some
alternately to each other, so users get their errors when they want
them. But regardless of the solution, it should permit for all
database-independent preparation to be separated out.
OK, so we have these stages;
1. (optional) generate an AST from SQL
2. (optional) generate SQL string from an AST
3. generate a handle for the statement, sans connection
4. prepare handle for execution, with connection
5. execute statement
I think these all fit into;
1. SQL::Statement.new(:sql("..."));
2. $statement.as_sql;
3. $dbh.prepare($statement) or $dbh.prepare($statement, :nodb);
4. $dbh.prepare($statement) or $sth.prepare while connected
5. $sth.execute
In particular, I don't think that the DB driver should automatically
get a chance to interfere with SQL::Statement; if they want to do that,
then they should specialise SQL::Statement. IMHO.
Perhaps you have some other examples that don't fit this?
5. All details used to construct a connection handle should be
completely decomposed rather than shoved into an ungainly "data
source".
I interpret this as asking that the detailed parameters to the DBI
connection are expanded into named options rather than simply bundled into
a string.
That, I agree with, and I guess it would be useful occasionally to be
able to specify all that rather than just setting it up once and labelling
those connection parameters with a "source" that comes from ~/.dbi.
Particularly for writing gui dialogs for interactive database utilities.
Either way, you don't want most applications dealing with this complexity
at all, really.
6. DBI drivers should always be specified by users with their actual
package name, such as 'DBD::SQLite', and not some alternate or
abbreviated version that either leaves the 'DBD::' out or is spelled
differently. Similarly, the DBI driver loader should simply try to load
exactly the driver name it is given, without munging of any type. This
approach is a lot more simple, flexible and lacks the cludges of the
current DBI. DBI driver implementers can also name their module
anything they want, and don't have to name it 'DBD::*'. A DBI driver
should not have to conform to anything except a specific API by which it
is called, which includes its behaviour upon initialization, invocation,
and destruction.
Is this useful?
I can't see a reason that the DBI.new() / DBI.connect() call shouldn't be
flexible in what it accepts;
$dbh = DBI.new( :driver<Rosetta> ); # means DBD::Rosetta
$dbh = DBI.new( :driver<Rosetta::Emulate::DBD> ); # specify full package
$dbh = DBI.new( :driver(Rosetta::Emulate::DBD) ); # pass type object
$dbh = DBI.new( :driver(DBD::SQLite.new(:foo<bar>)) ); # pass driver object
Sam.
