The proposals so far have dealt mostly with the SQL itself, and
supporting database-neutral layers on top of DBI.
Personally, I don't mind writing the SQL myself, I rarely need to make
a particular statement work on two databases in my work, and I can
optimize a lot better than any SQL generator.
I like DBI shortcuts (selectrow_array, etc), and I would like them
to become even more convenient in DBIv2, so I have been thinking
about ways to streamline the movement of data in and out of DBI.
A lot of these ideas are probably obvious, but I haven't seen them
actually stated anywhere yet. A lot may be bad or wrong, which is
where you readers come in...
Data Input
* placeholders (3 types):
?
PRO: simple
everyone supports this
CON: each parameter may only be accessed once
and in the order determined by the SQL
(rewrite your function and your interface changes)
:1 :2 :3
"true" positional placeholders
PRO: elimiates redundancy (list the same argument twice)
and dependancy (reorder args if sql changes)
:name
PRO: easier to manage for dynamic sql
CON: requires separate bind_param for each
biggest CON: not all are supported by every driver.
DBI could have a (optional?) compatibility layer, which would
translate down for those drivers that do not support :1 or :name,
replacing them with ? and reordering and duplicating the args
if necessary. I don't think a full SQL parser would be required.
For :name syntax, the execute function (do, selectrow_*, etc)
should do the binding automatically if Pairs are passed as arguments.
$dbh->do('update table set x = :x where y = :y', undef,
x => $foo, y => $bar );
* bind_param
I would like to avoid the need to call this if possible.
Perhaps attributes on the parameters could be used to flag
certain things, but I would prefer to do it semi-automatically,
based on the object type of the parameter, as described in
"Data Types on input" below.
* bind_param_array
I think execute_array with ArrayTupleFetch is a better solution.
* execute_array with ArrayTupleFetch
ArrayTupleFetch should accept a (Lazy) Array of Array|Hash as
well as a code block in perl6.
Data Types on input
strings and numbers are handled transparently (good!).
Other types less so: Dates, LOBs, etc.
It would be nice if we could tell DBI how to handle objects of
various types. If I pass it a DateTime object, format it "this way"
to pass it to the driver. If I pass it a LOB or IO object, bind it with
the type needed to handle it correctly.
I'm not sure what the best way to do this would be.
Maybe it looks for an object method with a specific name, which
we could define as a multi sub.
multi sub _dbi_format( DateTime $d ) returns String { ... }
Or maybe we register types with DBI and tell it what to do.
$dbh->register_type( DateTime, format => &my_format_sub , type =>
SQL_VARCHAR );
Data Output
ways to get data out:
* fetchrow get a single row
I would like it if there was a single fetchrow function which returned a
row object. Or at least if the unsuffixed fetchrow did that.
The row object can be accessed as both an array or a hash,
since query results have both a position and a name.
If the row object is evaluated in Array or Hash context, it should return
a list of values or key=>value pairs, so that it can be easily assigned
to an array or hash.
In String or Number context, it should probably return the value of the
first column.
* fetchall get multiple rows
This should return a Lazy List (iterator) of row objects.
* selectcol get column(s) as a list
This is a bit of an oddball, since there is no corresponding fetchcol
function. But it is a very useful oddball for when you want to turn
a lookup table into a hash, for instance.
I think the thing to do is to unify this with fetchall, by allowing
the $slice parameter to fetchall to be a code block. The code block
could return a arrayref or hashref to duplicate the existing slice
functionality, or it could return a list of values, a pair, a row object,
or something else entirely.
%hash = $sth->fetchall( slice => sub($row) { return ($row[0] =>
$row[1]) } );
This could also be added to DBIv1. A couple of the more obvious
slice funtions could be provided, allowing a nice looking call:
%hash = $sth->fetchall_array( &as_pairs );
* bind_col
Honestly, I don't use this method personally, so I haven't thought
about it much. fetchrow_hashref works well enough for me.
* bind_param_inout
I do use this, but only behind an sqlProcedure( $procname, $arg ... )
wrapper, which calls bind_param_inout for any $args which are
references.
This type of binding could be invoked by an attribute on the
parameter:
$sth->execute( $arg but inout );
or maybe "but rw", but rw may be overoverloaded already.
Data Types on output
It would be nice to have some datatypes already objectified when
the are returned from DBI. DATE columns as a DateTime object,
LOB colums as an IO object, etc.
I think this requires more cooperation from the drivers than
any of the previous suggestions. It might also be slower, due to
the need to ask for type information from the database.
So maybe this one is turned on optionally, and not guaranteed to
work with all drivers.
Something which is complementary to the method used to handle
data types on input would be good.
Maybe return values are assigned a role such a SqlInteger, SqlDate,
etc, and users can install multi subs to coerce them to the type
the user desires. Not sure how well that would work for LOBs.
Or maybe we register a conversion sub with DBI for each database
type we want to be handled specially.
~ John Williams
