DBI v2 - The Plan and How You Can Help

Once upon a time I said:

http://groups-beta.google.com/group/perl.dbi.users/msg/caf18 9d7b404a003?dmode=source&hl=en

and wrote

http://search.cpan.org/~timb/DBI/Roadmap.pod

which yielded:

https://donate.perlfoundation.org/index.pl?node=Fund+Drive+D etails&selfund=102

(A little over $500 of that I effectively put in myself.)

My *sincere* thanks to all those who donated to the fund, especially
individuals. I had hoped for more corporate response with less from
individuals and I'm touched by the personal generosity shown.

I've not drawn any money from it yet and doubt that I will myself.
(I'm considering suggesting that the Perl Foundation make payments
from the fund to people making specific contributions to the DBI.
I'm thinking especially of work on a comprehensive test harness.
But I'll see how the developments below pan out before making
specific arrangements.)


So, that lead to:

http://groups-beta.google.com/group/perl.dbi.dev/browse_frm/ thread/ef14a9fc0a37441f/fb8fe20a86723da0#fb8fe20a86723da0

Which sums up fairly well where I'm at: DBI v1 will rumble on for Perl 5
and DBI v2 will be implemented for Perl 6.


--- digression ---

At this point I'd like to make a slight digression to highlight the
amazing work going on in the Perl 6 community at the moment.
Especially Autrijus' Pugs project which has brought Perl 6 to life.
Literally. Take a look at:

http://pugscode.org/talks/yapc/slide1.html
http://use.perl.org/~autrijus/journal

and especially:

http://use.perl.org/~autrijus/journal/24898

Yes, that really is Perl 6 code using the DBI being executed by Pugs.

That's great, and I was truly delighted to see it because it takes the
pressure off the need to get a DBI working for Perl 6 - because it
already is working for Perl 6. At least for Pugs. (The Ponie project
is also likely to provide access to Perl 5 DBI from Perl 6 by enabling
future versions of Perl 5 to run on Parrot.)

--- digression ---


I have recently come to an arrangement that will enable me to put some
worthwhile development time into DBI (still very much part-time, but
enough to give it focus and move forward).

My initial goals are:

1. to work on a more detailed specification for the DBI v2 API that
takes advantage of appropriate features of Perl 6.

2. to work on a more detailed specification for the DBDI API
http://groups-beta.google.com/group/perl.perl6.internals/msg /cfcbd9ca7ee6ab4

3. to work on tools to automate building Parrot NCI interfaces to
libraries (such as database client libraries, obviously :)


But I'm hoping you'll join in and help out.

I've kept an eye on Perl 6 and Parrot developments but I'm no expert in
either. What I'd like *you* to do is make proposals (ideally fairly
detailed proposals, but vague ideas are okay) for what a Perl 6 DBI API
should look like.

Keep in mind that the role of the DBI is to provide a consistent
interface to databases at a fairly low level. To provide a *foundation*
upon which higher level interfaces (such as Class::DBI, Tangram, Alzabo
etc. in Perl 5) can be built.

So, if you have an interest in the DBI and Perl 6, put your thinking
cap on, kick back and dream a little dream of how the DBI could be.
How to make best use of the new features in Perl 6 to make life easier.

Then jot down the details and email them to me (or to dbi-users@perl.org
if you want to kick them around in public for a while first).

I'm about to fly off for two weeks vacation (in a few hours), blissfully
absent of any hi-tech gear beyond a mobile phone. When I get back I'll
gather up your emails and try to distill them into a coherent whole.

Have fun!

Tim.

Re: DBI v2 - The Plan and How You Can Help

Hey Tim.

> I've kept an eye on Perl 6 and Parrot developments but I'm no expert in
> either. What I'd like *you* to do is make proposals (ideally fairly
> detailed proposals, but vague ideas are okay) for what a Perl 6 DBI API
> should look like.
> Keep in mind that the role of the DBI is to provide a consistent
> interface to databases at a fairly low level. To provide a *foundation*
> upon which higher level interfaces (such as Class::DBI, Tangram, Alzabo
> etc. in Perl 5) can be built.

OK, well based on my experience, here's a few things that would be nice;

- optional treatment of the statements as an AST, similar in concept to
SQL::Routine, or Tangram::Expr. Death to SQL templating systems!

Ideally there should be no need for a module like DBD::CSV or even,
eventually, DBD::SQLite to actually generate SQL strings for queries;
the AST is enough for them to go away and run the query.

It should be possible to use either, though - and use /named/
placeholder arguments for either, something like:

use DBI-2;
my $dbi = DBI->connect(...);
my $sth = $dbi->prepare("select * from foo where bar = :bar");
$sth->execute(:bar($bar));

my $table = $dbi->table("foo");
my $sth = $dbi->select
(:all
:from($table),
:where($table.bar == $dbh.placeholder("bar"))
);
$sth->execute(:bar($bar));

Virtually every major DB abstraction tends to build this, whether or
not they know they're doing it ;).

- support for automatically pulling database DSN information from a
~/.dbi (or similar) file. This is constantly re-invented poorly.
Let's just do a connect by logical application name and let the
SysAdmins sort out which DB that connects to, in a standard way.

- object-oriented modelling of core database objects, so that schema
operations can be made portable, a la Rosetta. This should really
just constitute re-thinking the existing interfaces, and leaving it
up to the DBD authors to finish them up.

This may also open the door for unifying the way that relationships
such as foreign key constraints, etc are dealt with.

- make it easier to get ACID right; steal Tangram's `tx_do` transaction
interface; though I'd suggest a name including the perl6-ish `try'

$dbh.tx_try {

# ... transaction ...

};

in fact, if you were to wrap the closure in an atomic{ } block, then
you could probably easily support replaying the transaction in the
event of a forced rollback (so long as the code in the closure has no
side effects ... )

Also there is the nested transction interface;

$dbh.tx_start;
$dbh.tx_start;
$dbh.tx_commit;
$dbh.tx_commit; # commit happens here

Some databases can even support nested transactions internally via
SQL SAVEPOINTS.

- asynchronous/event-based processing of queries, so that we don't need
yet another sub-process when in POE-like environments (which might
include a standard Perl 6 event programming system).

In terms of making things Perl6-ish, you probably want to look at technology
such as coroutines for cursors, and continuations for exception handling, for
their implications to writing transactional applications.

Perl 6 will be able to serialise continuations and probably also coroutines,
so it should be possible for a continuation serialised with an active cursor
in a coroutine to automatically resume itself once the continuation is thaw'ed.

This might happen by simply leaving the transaction open (I hear screams!) in
a single threaded context, or by rolling back, replaying the transaction's
queries on the continuation resume and checking all the database responses
match what was previously read. At the first sign of discrepancies, the
next database operation would throw a fake ROLLBACK; which would even be
caught and the application transaction replayed from the beginning, if they
used .tx_try :retry(3), :{ ... }

This would make some of the "old problems", such as object versioning,
potentially a lot easier to solve.

Allow me to illustrate with some application code;

my $mvc;

if ($mvc.controller.action eq "edit") {
my $id = $mvc.controller.edit_id;
my $object;
$dbi.tx_try {
$object = $fetch_query.select_only(:id($id));

$mvc.view(:state("edit"), :object($object))
until $mvc.controller.action eq "commit";

$update_query.execute(:id($id));

CATCH {
$mvc.view(:state("error"),
:message("object modified by another!"));
}

}, :retry(3);
$mvc.view(:state("edit"), :object($object));
}

So, depending on the MVC system in use, when you wrote "$mvc.view()", it
will have done one of the following things;

Session-based continuations:

1. Serialise the continuation - when it comes across the DBH with an
active transaction, it creates an object which represents the
queries issued so far in this transaction, and the checksum of the
responses they returned and positions of any cursors.

2. When the "real" continuation is destroyed, rollback to the
savepoint that the application server started just after opening
the transaction.

3. save the serialised continuation into a LOB field in the session,
save the session and commit the outer transaction.

4. issue the page/response

Pure serialisable continuations:

1. Serialise the continuation as above.

2. rollback the transaction as its live continuation object is
destroyed.

3. build into a hidden form field/client side state variable

4. issue the page/response

Each time the controller receives an action, it is resuming a
continuation. It will then start the operation described above; re-issuing
the queries (a SELECT in this case), and checking the checksum it got back.
If there was a discrepancy, it is treated as a normal transaction
inconsistency, and the CATCH block issues a message saying that the
data was stale. As it is logically outside the try { } block, it does not
need to serialise an open transaction when the continuation is serialised
to the response or saved to the session.

Finally, the transaction is committed. For the whole session, there were
no long running transactions and in the case of the pure serialisable
continuations, no need for anything other than client-side state.

Consider my 2¢ deposited ;-)

> I'm about to fly off for two weeks vacation (in a few hours), blissfully
> absent of any hi-tech gear beyond a mobile phone. When I get back I'll
> gather up your emails and try to distill them into a coherent whole.

Hope you're having a great time.

Sam.

Re: DBI v2 - The Plan and How You Can Help

> - support for automatically pulling database DSN information from a
> ~/.dbi (or similar) file. This is constantly re-invented poorly.
> Let's just do a connect by logical application name and let the
> SysAdmins sort out which DB that connects to, in a standard way.

This reminds me one one thing I hate about DB access, and that is having
the DB password
stored in plain text.

Of course there are ways to provide some concealment, but nothing
particularly good or
integrated into the access.

If the "connecting by logical application name" could also include some
level of security
access, that would be a big improvement.

R.

Re: DBI v2 - The Plan and How You Can Help

Richard Nuttall wrote:
>
>> - support for automatically pulling database DSN information from a
>> ~/.dbi (or similar) file. This is constantly re-invented poorly.
>> Let's just do a connect by logical application name and let the
>> SysAdmins sort out which DB that connects to, in a standard way.
>
>
> This reminds me one one thing I hate about DB access, and that is having
> the DB password
> stored in plain text.
>
> Of course there are ways to provide some concealment, but nothing
> particularly good or
> integrated into the access.
>
> If the "connecting by logical application name" could also include some
> level of security
> access, that would be a big improvement.
>
> R.
>
>

Which is why major DBMSs are increasingly relying on SSO
based solutions. (e.g., Kerberos/LDAP authentication).
Not certain if DBI is the proper level to implement that,
(probably needs to be down at the DBD <=> DBMS level).
And "in a standard way" may still be wishful thinking.

Also, I'm not sold on the idea that a ~/.dbi file is particularly
secure in that regard. Not neccesarily opposed, just not convinced
its the right solution. (I don't like cleartext passwords either,
but due to the variance in DBMS's authentication methods, I don't know if
DBI can solve that problem).

- Dean

Re: DBI v2 - The Plan and How You Can Help

On Mon, 04 Jul 2005 15:04:43 -0700, Dean Arnold wrote:

Hi Dean

> Also, I'm not sold on the idea that a ~/.dbi file is particularly
> secure in that regard. Not neccesarily opposed, just not convinced
> its the right solution. (I don't like cleartext passwords either,
> but due to the variance in DBMS's authentication methods, I don't
> know if DBI can solve that problem).

Same here.

I wrote Javascript::(MD5, SHA1) and CGI::Session::MembersArea to encourage
(myself) sending only encrypted passwords across the net, and for storing=
them
outside the code. And even though I'm not 100% happy (the latter module is
definitely awkward to use, although that's perhaps par for the course with
security) it's given me some small insight into these problems.

And in the end, delivering a password for DBI to use to connect does not=
seem to
me to be DBI's responsibility.

And, yes, I too hear both MD5 and SHA1 are attackable, but that's not the=
point
here.
--
Cheers
Ron Savage, ron@savage.net.au on 5/07/2005
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company

Re: DBI v2 - The Plan and How You Can Help

Richard Nuttall wrote:
>> - support for automatically pulling database DSN information from a
>> ~/.dbi (or similar) file. This is constantly re-invented poorly.
>> Let's just do a connect by logical application name and let the
>> SysAdmins sort out which DB that connects to, in a standard way.
> This reminds me one one thing I hate about DB access, and that is having
> the DB password
> stored in plain text.

Sadly, there is really nothing that can be done about this, other than
"casual" obscuring of the real password like CVS does in ~/.cvspass

However, making it in a file in $HOME/.xxx means that the sysadmin can
set it up to be mode 400 or something like that, to ensure other users
can't access it if someone forgot to set the permissions right on the
application code (or, hopefully, configuration file).

Of course, for more secure access schemes like kerberos, etc, the file
is really just being a little registry of available data sources.

On a similar note could be allowing overriding the data source used by
an application by setting an environment variable. That way, the
SysAdmin has got lots of options when it comes to managing different
production levels - Oracle has this with TWO_TASK, and while it's a
PITA when it's not there (no doubt why DBD::Oracle allows this to be
specified in the DSN string), it's also useful for what it's intended
for - switching databases from an operational perspective.

Sam.

Re: DBI v2 - The Plan and How You Can Help

> - optional treatment of the statements as an AST, similar in concept to
> SQL::Routine, or Tangram::Expr. Death to SQL templating systems!

I suspect during this process people are going to want a lot of things
that layer on top of what we currently see as DBI.

Personally I think Tim got it right to initially only handle
connectivity and preparation stuff in DBI, and not try to deal with
schemas or relational-mapping or any of that other gumpf.

I see the issues with the table methods in DBI as an example of this.
What you want in many situations is something much more details that
just a list of table names.

So with that in mind.

I'd like to see DBI done as a set of multiple interfaces that each
driver can choose to support or not support.

For example, the connectivity layer. This currently works very very well
and has allowed huge numbers of different interfaces to almost every
database in existance.

Every driver, as they do now, should implement support for this layer.

On top of that, if you really must interact with the database to query
table names and so on, lets see that done as a separate layer.

DBI2::Schema or something. drivers could then specifically advertise
support or non-support for that API.

And we could get an interface somewhat richer than the current "raw
hashes" one.

Adam K

Re: DBI v2 - The Plan and How You Can Help

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;
$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 ); # means DBD::Rosetta
$dbh = DBI.new( :driver ); # specify full package
$dbh = DBI.new( :driver(Rosetta::Emulate::DBD) ); # pass type object
$dbh = DBI.new( :driver(DBD::SQLite.new(:foo)) ); # pass driver object

Sam.

Re: DBI v2 - The Plan and How You Can Help

Sam Vilain wrote:
>
> However, making it in a file in $HOME/.xxx means that the sysadmin can
> set it up to be mode 400 or something like that, to ensure other users
> can't access it if someone forgot to set the permissions right on the
> application code (or, hopefully, configuration file).
>

I don't think this solves the problem, because what I usually want is
the user to be able to use the application, but unable to see the DB
password. So the user should have "read" permission set for the file,
but on the other hand he shouldn't. It's not not a problem for Web App,
though.

--
Maxim Sloyko

Re: DBI v2 - The Plan and How You Can Help

Darren Duncan wrote:
> Okay, considering that using the same name prepare() like this may
> confuse some people, here is a refined solution that uses 3 methods
> instead; please disregard any contrary statements that I previously made:

I think I'm beginning to like it.

Allow me to suggest one or two further refinements...

> # Opt 1: A user that wants the most control can do this (new feature):
>
> 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

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 );

This would give you a STH which is divorced from the actual DB connection
instance. Because you constructed it like this, without reference to a
(possibly unconnected) connection object, then $sth1.prepare is not
available.

You'd then need to use something like;

$sth1.prepare($dbh);
$dbh.prepare($sth1);

Note I also think what you wrote should work, too.

> The new feature is if you decide to use compile(); you then give that
> method the arguments you would have given to prepare(), and you invoke
> prepare() on the result with no arguments; each DBD would decide for
> itself how the work is divided between compile() and prepare() with the
> limitation that compile() is not allowed to access the database; ideally
> the DBD would place as much work there as is possible, which would vary
> between Oracle/Pg/etc.

Agreed.

>> 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.
> I am operating under the assumption here that while the new DBI is
> designed to effectively support wrapper modules, the wrapper modules
> would also be altered from their current DBI-1-geared designs to
> accomodate DBI-2.
> But still, what do you mean by "interfere"?

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.

>> Either way, you don't want most applications dealing with this complexity
>> at all, really.
> 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.

> Unless there is a design flaw in DBI, we should not have to update that
> module just because a new driver came into existence whose name has not
> yet been hard-coded into DBI.
> See this block for example, from DBI.pm v1.48:
> my $dbd_prefix_registry = {
> ad_ => { class => 'DBD::AnyData', },
[...]
> yaswi_ => { class => 'DBD::Yaswi', },
> };
> I mean, what's up with that? I assume DBI 1 has this for legacy app
> backwards compatability, but DBI version 2 should never have to
> accomodate such abhorrent computer programming practices in its core.

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.

Sam.

Re: DBI v2 - The Plan and How You Can Help

Adam Kennedy wrote:
> Yes, native positional support is still important.
> positions make it very easy to do SQL math.
> To express it in overly simplistic code
> $foo = [ "a = ?", "foo" ];
> $bar = [ "b = ?", "bar" ];
> $baz = "$foo and $bar";
> # $baz now is [ "a = ? and b = ?", "foo", "bar" ];
> Bearing mind a situation with an arbitrary number and complexity of
> these sql fragments to be added together, doing this sort of thing using
> named placeholders would be a nightmare.

Interesting. You are demonstrating reasons it is useful to deal with
SQL fragments in non-templated form.

In Tangram what you describe is written like this;

my $table = $storage->remote("Table");
my $foo = $table->{a} == "foo";
my $bar = $table->{b} == "bar";
my $baz = $foo & $bar;

$foo expresses "a = 'foo'", $bar "b = 'bar'", and baz
"a = 'foo' AND b = 'bar'". Currently this is not done with placeholders.

--- Full demo script:

use Tangram;
use YAML;

my $schema = Tangram::Schema->new(Load < classes:
MyTable:
fields:
string: [ a, b ]
YAML

my @dsn = (...);

# print the schema for informational purposes
Tangram::Relational->deploy($schema);

eval { Tangram::Relational->retreat($schema, @dsn); };
Tangram::Relational->deploy($schema, @dsn);
my $storage = Tangram::Storage->connect($schema, @dsn);

my $table = $storage->remote("MyTable");
my $foo = $table->{a} == "foo";
my $bar = $table->{b} == "bar";
my $baz = $foo & $bar;

print Dump { "1. foo" => $foo, "2. bar" => $bar, "3. baz" => $baz };

Re: DBI v2 - The Plan and How You Can Help

Maxim Sloyko wrote:

> I don't think this solves the problem, because what I usually want is
> the user to be able to use the application, but unable to see the DB
> password. So the user should have "read" permission set for the file,
> but on the other hand he shouldn't. It's not not a problem for Web App,
> though.

Storing passwords encrypted, decrypting before using doesn't cover this?
I've played around with Crypt::CBC (and different ciphers) for this sort
of thing but admittedly have not applied this to any production systems
yet. You still have a key somewhere to hide/obscure. You can also use
Perl source filters to totally encrypt the source -- something else I've
done but not in production. Just some things you may want to look at ...

--
Steve Sapovits steves06@comcast.net

Re: DBI v2 - The Plan and How You Can Help

Steve Sapovits wrote:
> Maxim Sloyko wrote:
>
>> I don't think this solves the problem, because what I usually want is
>> the user to be able to use the application, but unable to see the DB
>> password. So the user should have "read" permission set for the file,
>> but on the other hand he shouldn't. It's not not a problem for Web
>> App, though.
>
>
> Storing passwords encrypted, decrypting before using doesn't cover this?
> I've played around with Crypt::CBC (and different ciphers) for this sort
> of thing but admittedly have not applied this to any production systems
> yet. You still have a key somewhere to hide/obscure. You can also use
> Perl source filters to totally encrypt the source -- something else I've
> done but not in production. Just some things you may want to look at ...

Well, I mostly do Web apps, so it is not a problem since user can't see
code. If the user can see the code, can find a password int it and
understand what password is this, then there is no way encryption can
help much (storing keys etc. as you pointed it out.), because this kind
of user can figure it all out, if he wants to.

But this is not the point. The point was that usage of some file with
passwords by *DEFAULT* is not the way to go, IMHO. It raises more
problems than it solves.

--
Maxim Sloyko

Re: DBI v2 - The Plan and How You Can Help

Maxim Sloyko wrote:

> Well, I mostly do Web apps, so it is not a problem since user can't see
> code. If the user can see the code, can find a password int it and
> understand what password is this, then there is no way encryption can
> help much (storing keys etc. as you pointed it out.), because this kind
> of user can figure it all out, if he wants to.

The encrypted source code seems to solve that. I've seen it used
for commercial apps. Basically you see the source filter plain
text in a 'use' statement and the rest is jibberish. If you write
the encrypting source filter in C and follow other guidlines it
becomes next to impossible to know how to decrypt things. 'man
perlfilter' if you want to take a look.

> But this is not the point. The point was that usage of some file with
> passwords by *DEFAULT* is not the way to go, IMHO. It raises more
> problems than it solves.

I agree. We don't have any connect strings in code. We use higher
level names that map to connect strings, subclassing DBI to do that.
An advantage of that is that we can change what's under a logical
connect name in one place (e.g., if a database moves). That puts
them all in one place, which is a start anyway. What might make
sense is some way to subclass and override just the connect string
related access to make this sort of thing easier.

--
Steve Sapovits steves06@comcast.net

Re: DBI v2 - The Plan and How You Can Help

Maxim Sloyko wrote:
> But this is not the point. The point was that usage of some file with
> passwords by *DEFAULT* is not the way to go, IMHO. It raises more
> problems than it solves.

Can you give an example of such a problem that wasn't already there?

Just to be clear, the file would only need to contain passwords if the
DBD requires them.

Sam.

Re: DBI v2 - The Plan and How You Can Help

Sam Vilain wrote:
> Maxim Sloyko wrote:
>
>> But this is not the point. The point was that usage of some file with
>> passwords by *DEFAULT* is not the way to go, IMHO. It raises more
>> problems than it solves.
>
>
> Can you give an example of such a problem that wasn't already there?
>
> Just to be clear, the file would only need to contain passwords if the
> DBD requires them.
>
> Sam.

May be it is just me, but having a bunch of config files is not very
good. Config files should be for program, not for separate modules of
that program. They are hard to manage, when there are many of them.
Besides, DBI is a high level abstraction and it is not a good idea to
tie it to some file. May be it is better to let DBD:: modules choose?
For example, you can pass some parameter to the driver in connect
string, which tells it, where passwords are stored. Something like this
is impleneted in DBD::Oracle, where you can just pass 'sid' parameter to
the driver, the rest configuration parameters (except passwords and
usernames) are read from oracle config file, from the section to which
that sid parameter points.

I don't mind if you implement this ".dbi" feature though, I just want it
to be invisible :) i.e. don't check this file, if I explicitly supply
username and password (this is obvious, right?) and show some warnings
if don't. Say, make a connect parameter "use_dot_dbi", which is zero by
default.

--
Maxim Sloyko

RE: DBI v2 - The Plan and How You Can Help

When I go to the donation page and attempt to make a donation, the
drop-down box does not give DBI as a valid recipient. Is it possible
several people may not have donated as they noticed the same results, or
maybe they did and it all went into the Perl Development Fund instead?



> -----Original Message-----
> From: Tim Bunce [mailto:Tim.Bunce@pobox.com]
> Sent: Friday, July 01, 2005 7:06 PM
> To: perl6-language@perl.org; dbi-users@perl.org
> Subject: DBI v2 - The Plan and How You Can Help
>
>
> Once upon a time I said:
>
>
> http://groups-beta.google.com/group/perl.dbi.users/msg/caf18 9d
> 7b404a003?dmode=source&hl=en
>
> and wrote
>
> http://search.cpan.org/~timb/DBI/Roadmap.pod
>
> which yielded:
>
>
> https://donate.perlfoundation.org/index.pl?node=Fund+Drive+D et
> ails&selfund=102
>
> (A little over $500 of that I effectively put in myself.)
>
> My *sincere* thanks to all those who donated to the fund, especially
> individuals. I had hoped for more corporate response with less from
> individuals and I'm touched by the personal generosity shown.
>
> I've not drawn any money from it yet and doubt that I will myself.
> (I'm considering suggesting that the Perl Foundation make payments
> from the fund to people making specific contributions to the DBI.
> I'm thinking especially of work on a comprehensive test harness.
> But I'll see how the developments below pan out before making
> specific arrangements.)
>
>
> So, that lead to:
>
>
> http://groups-beta.google.com/group/perl.dbi.dev/browse_frm/ th
> read/ef14a9fc0a37441f/fb8fe20a86723da0#fb8fe20a86723da0
>
> Which sums up fairly well where I'm at: DBI v1 will rumble on
> for Perl 5
> and DBI v2 will be implemented for Perl 6.
>
>
> --- digression ---
>
> At this point I'd like to make a slight digression to highlight the
> amazing work going on in the Perl 6 community at the moment.
> Especially Autrijus' Pugs project which has brought Perl 6 to life.
> Literally. Take a look at:
>
> http://pugscode.org/talks/yapc/slide1.html
> http://use.perl.org/~autrijus/journal
>
> and especially:
>
> http://use.perl.org/~autrijus/journal/24898
>
> Yes, that really is Perl 6 code using the DBI being executed by Pugs.
>
> That's great, and I was truly delighted to see it because it takes the
> pressure off the need to get a DBI working for Perl 6 - because it
> already is working for Perl 6. At least for Pugs. (The Ponie project
> is also likely to provide access to Perl 5 DBI from Perl 6 by enabling
> future versions of Perl 5 to run on Parrot.)
>
> --- digression ---
>
>
> I have recently come to an arrangement that will enable me to put some
> worthwhile development time into DBI (still very much part-time, but
> enough to give it focus and move forward).
>
> My initial goals are:
>
> 1. to work on a more detailed specification for the DBI v2 API that
> takes advantage of appropriate features of Perl 6.
>
> 2. to work on a more detailed specification for the DBDI API
>
> http://groups-beta.google.com/group/perl.perl6.internals/msg /c
> fcbd9ca7ee6ab4
>
> 3. to work on tools to automate building Parrot NCI interfaces to
> libraries (such as database client libraries, obviously :)
>
>
> But I'm hoping you'll join in and help out.
>
> I've kept an eye on Perl 6 and Parrot developments but I'm no
> expert in
> either. What I'd like *you* to do is make proposals (ideally fairly
> detailed proposals, but vague ideas are okay) for what a Perl
> 6 DBI API
> should look like.
>
> Keep in mind that the role of the DBI is to provide a consistent
> interface to databases at a fairly low level. To provide a
> *foundation*
> upon which higher level interfaces (such as Class::DBI,
> Tangram, Alzabo
> etc. in Perl 5) can be built.
>
> So, if you have an interest in the DBI and Perl 6, put your thinking
> cap on, kick back and dream a little dream of how the DBI could be.
> How to make best use of the new features in Perl 6 to make
> life easier.
>
> Then jot down the details and email them to me (or to
> dbi-users@perl.org
> if you want to kick them around in public for a while first).
>
> I'm about to fly off for two weeks vacation (in a few hours),
> blissfully
> absent of any hi-tech gear beyond a mobile phone. When I get back I'll
> gather up your emails and try to distill them into a coherent whole.
>
> Have fun!
>
> Tim.
>

RE: DBI v2 - The Plan and How You Can Help

Sam Vilain wrote:
> Maxim Sloyko wrote:
>=20
>> But this is not the point. The point was that usage of some file =
with=20
>> passwords by *DEFAULT* is not the way to go, IMHO. It raises more=20
>> problems than it solves.
>=20
>=20
> Can you give an example of such a problem that wasn't already there?
>=20
> Just to be clear, the file would only need to contain passwords if the
> DBD requires them.
>=20
> Sam.

> May be it is just me, but having a bunch of config files is not very=20
> good. Config files should be for program, not for separate modules of=20
> that program. They are hard to manage, when there are many of them.=20
> Besides, DBI is a high level abstraction and it is not a good idea to=20
> tie it to some file. May be it is better to let DBD:: modules choose?=20
> For example, you can pass some parameter to the driver in connect=20
> string, which tells it, where passwords are stored. Something like =
this=20
> is impleneted in DBD::Oracle, where you can just pass 'sid' parameter =
to=20
> the driver, the rest configuration parameters (except passwords and=20
> usernames) are read from oracle config file, from the section to =
which=20
> that sid parameter points.

No, it is not just you. I find this type of setup appalling. As an =
Oracle DBA, I do not want yet another place where I will need to manage =
passwords. This type of setup will most likely restrict and possibly =
prohibit the use of Oracle OID and Enterprise User Management. It is, =
IMHO, unacceptable to force this type of behavior into a database access =
module and onto the general user community.

> I don't mind if you implement this ".dbi" feature though, I just want =
it=20
> to be invisible :) i.e. don't check this file, if I explicitly supply=20
> username and password (this is obvious, right?) and show some warnings =

> if don't. Say, make a connect parameter "use_dot_dbi", which is zero =
by=20
> default.

> --
> Maxim Sloyko

--
Ron Reidy
Lead DBA
Array BioPharma, Inc.

This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

Re: DBI v2 - The Plan and How You Can Help

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


No time to respond fully, but a few cents here and there, with some
devil's advocate thrown in.

Darren Duncan wrote:
> All host parameters should be named (like ":foo") rather than
> positional (like "?"), meeting with the SQL:2003 standard. The named
> format is a lot easier to use and flexible.

I don't know about easier. I like the question mark for quick little
queries, and you can simply call execute with a single argument, for
example. Can't get easier than that. :) I also would probably prefer
the $1,$2,$3,..$N style over the :foo style myself. I can see room for all
of them, but all of this is really a DBD, not a DBI, issue. The DBI
module could certainly more strongly recommend one of the '?' alternatives.

> and not some alternate or abbreviated version that either leaves
> the 'DBD::' out or is spelled differently

I mostly agree with this, but it's not as if every DBI release adds a
whole bunch of new DBDs. I'd at least try and keep a common prefix.

Sam Vilain wrote:
> 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).

Actually, Pg uses server-side prepares whenever possible, so this would be
of limited use to it as well. I know mysql is going that way as well, so I'm not
sure how useful all of this will be. I also don't know if all these
contortions will really save all that much: if performance is that much of an
issue, you can certainly use mod_perl to prepare them once (with or without
a database) in your BEGIN block, and execute thousands of times after that.

Darren Duncan again:
> Each DBI driver can worry less about that its input is correct and focus more
> on its actual work.

Can you expand on this a little? Not sure I understand: DBI already validates
the number and type of the common methods. I *know* we aren't talking about a
global SQL parser - that way lies madness. :)

> A $sth should not contain any methods for fetching the result of
> an executed statement;
....
> my $rlh = $sth->execute();
> my $rowset = $rlh->fetchrow_arrayref();
>
> This approach is a lot more flexible.

This seems like an unnecessary step. Fetching data seems like a normal
method for a statement handle.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200507072132
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B90 6714964AC8

P.S. No reason to cc dbi-dev, everyone there should be on users, and no reason
to cc Tim, he's on both. Doesn't seem particularly perl6 related now either... :)

-----BEGIN PGP SIGNATURE-----

iD8DBQFCzdgYvJuQZxSWSsgRAhWAAJ49XoMjTWhOQ7ddqKmjvthtqyakOACg k9kI
IMIKahnbi+kyQCrxOUnSbr4=
=3Zkw
-----END PGP SIGNATURE-----

Re: DBI v2 - The Plan and How You Can Help

I use a (Perl) password server for this.

Passwords are stored encrypted in a configuration file.

Clients authenticate with the server, and receive a requested
password (encrypted) across the network, if the client is
entitled.

The user authentication is rudimentary, but it works.
SSH certificates would be better, just need to do it.

Jared


On Mon, 2005-07-04 at 22:21, Maxim Sloyko wrote:
> Sam Vilain wrote:
> >
> > However, making it in a file in $HOME/.xxx means that the sysadmin can
> > set it up to be mode 400 or something like that, to ensure other users
> > can't access it if someone forgot to set the permissions right on the
> > application code (or, hopefully, configuration file).
> >
>
> I don't think this solves the problem, because what I usually want is
> the user to be able to use the application, but unable to see the DB
> password. So the user should have "read" permission set for the file,
> but on the other hand he shouldn't. It's not not a problem for Web App,
> though.
>
> --
> Maxim Sloyko

Re: DBI v2 - The Plan and How You Can Help

> I don't mind if you implement this ".dbi" feature though, I just want it
> to be invisible :) i.e. don't check this file, if I explicitly supply
> username and password (this is obvious, right?) and show some warnings
> if don't. Say, make a connect parameter "use_dot_dbi", which is zero by
> default.

I concur. I've been presuming what they are talking about is a standard
implementation for those that want to use that sort of feature. Not a
default implementation that everyone should use.

So "if you want to use database config files, we've got a standard way"

Adam K

Re: DBI v2 - The Plan and How You Can Help

At 1:36 AM +0000 7/8/05, Greg Sabino Mullane wrote:
>I don't know about easier. I like the question mark for quick little
>queries, and you can simply call execute with a single argument, for
>example. Can't get easier than that. :) I also would probably prefer
>the $1,$2,$3,..$N style over the :foo style myself. I can see room for all
>of them, but all of this is really a DBD, not a DBI, issue. The DBI
>module could certainly more strongly recommend one of the '?' alternatives.

Actually, DBI already mandates support for the "?" in all DBDs, as
far as I know, and some of them have to resort to search-n-replace in
the SQL string at execute() time since the database doesn't have
native support. So if that's indeed the case, then I believe that
":foo" etc should get the same treatment.

I can also think of several ways that named is easier.

As an analogy, say you've written a Perl 5 function multiple
arguments. Would you rather use those argument values in the
function by way of a named intermediary variable, or would you rather
just use @_ subscripts everywhere instead? I see bind parameters to
be exactly the same as function arguments, conceptually speaking.
Your $1, $2 etc is like using @_ subscripts.

Then there's the classic example of ...

prepare( "insert into foo (a,b,c,d,e,f,g,h,i,j) values
(?,?,?,?,?,?,?,?,?,?)" );
execute( $z,$y,$x,$w,$v,$u,$t,$s,$r,$q );

It's just so easy to lose track of what ? goes with what. Here, and
in many other cases.

Then there's the time you want to use the same bind variable in
several places in the same query. With the ? approach, you have to
bind it multiple times; being limited in that way doesn't seem right.

>Sam Vilain wrote:
>> 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).
>
>Actually, Pg uses server-side prepares whenever possible, so this would be
>of limited use to it as well. I know mysql is going that way as
>well, so I'm not
>sure how useful all of this will be. I also don't know if all these
>contortions will really save all that much: if performance is that much of an
>issue, you can certainly use mod_perl to prepare them once (with or without
>a database) in your BEGIN block, and execute thousands of times after that.

MySQL uses server side prepares since version 4.1, and the new driver
lets you access that functionality.

As for contortions, well I am thinking of large program environments
here where data dictionaries and generated SQL are the norm. Having
this feature allows for more elegant yet fast generated SQL. And
even speed aside, it gives users some more flexability of how they
want to organize their programs.

>Darren Duncan again:
>> Each DBI driver can worry less about that its input is correct and
>>focus more
>> on its actual work.
>
>Can you expand on this a little? Not sure I understand: DBI already validates
>the number and type of the common methods. I *know* we aren't talking about a
>global SQL parser - that way lies madness. :)

Are you sure about that? I am making such a thing right now. I
certainly don't expect DBI to have that built in, but it would be
nice for DBI to be structured that it is easier to build one on top.
And Tim explicitly said he wants input towards making DBI effective
for people putting layers on top of it.


> > A $sth should not contain any methods for fetching the result of
>> an executed statement;
>...
>> my $rlh = $sth->execute();
>> my $rowset = $rlh->fetchrow_arrayref();
>>
>> This approach is a lot more flexible.
>
>This seems like an unnecessary step. Fetching data seems like a normal
>method for a statement handle.

Not to me. I see a statement as a program function. You prepare it,
invoke it with arguments, and it returns a result. The result is
separate from the function. In programs you often do "$foo = bar(
$baz )"; the result of bar() is put into something the moment it
executes.

-- Darren Duncan

Re: DBI v2 - The Plan and How You Can Help

Jared Still wrote:

> I use a (Perl) password server for this.
>
> Passwords are stored encrypted in a configuration file.
>
> Clients authenticate with the server, and receive a requested
> password (encrypted) across the network, if the client is
> entitled.
>
> The user authentication is rudimentary, but it works.
> SSH certificates would be better, just need to do it.

This is more of the direction I'm going; i.e., I don't want a
DBI-specific password file as a requirement since I'm looking
at a central way of storing a variety of passwords, DBI and
non-DBI. I think the file idea is fine as an environment- or
configuration-driven _option_.

--
Steve Sapovits steves06@comcast.net

Re: DBI v2 - The Plan and How You Can Help

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> Actually, DBI already mandates support for the "?" in all DBDs, as
> far as I know, and some of them have to resort to search-n-replace in
> the SQL string at execute() time since the database doesn't have
> native support.

Or search-n-replace at prepare time for those that support native
placeholders (none that I know of actually use '?', it's $1 or :foo).

> I see bind parameters to be exactly the same as function arguments,
> conceptually speaking. Your $1, $2 etc is like using @_ subscripts.
>
> I can also think of several ways that named is easier.

Sure, I don't have a problem with the :foo form, I was just saying
that for short examples, the '?' format is simpler:

$sth->execute(101);

vs.

$sth->execute( { "myfirstarg", 101 });
or
$sth->bind_param("myfirstarg", 101 ...);
$sth->execute();

> Then there's the time you want to use the same bind variable in
> several places in the same query. With the ? approach, you have to
> bind it multiple times; being limited in that way doesn't seem right.

Sure, that's why DBI should encourage drivers to support the other two
ways as well. Having said all of the above, if I had to pick only
one, I would choose the :foo method. :)

> As for contortions, well I am thinking of large program environments
> here where data dictionaries and generated SQL are the norm. Having
> this feature allows for more elegant yet fast generated SQL.

The can already generate the SQL without a database connection. I guess
the real advantage is in pre-parsing the SQL and turning it into a
form more amenable to passing to the database server, by quoting values
and/or changing placeholder forms. I could possibly see a place for this,
and I like the option of allowing some things to possible be done before
the database is connected, but I don't really see much of a real-world
time saving. But where would this method live? It obviously could not
be called via $dbh->compile, nor could it be DBI->compile. You would
almost need a new connection mtehod, or perhaps a flag on the current
connect() method that tells the driver to load, but defer actually
conecting to the database.

>> I *know* we aren't talking about a >global SQL parser - that way lies
>> madness. :)

> Are you sure about that? I am making such a thing right now. I
> certainly don't expect DBI to have that built in, but it would be
> nice for DBI to be structured that it is easier to build one on top.
> And Tim explicitly said he wants input towards making DBI effective
> for people putting layers on top of it.

Sure, I have no problem with making things more extensible, but the
implication was that the DBI would be doing SQL parsing directly,
which is not the route to go. I just want to make sure that we keep
DBI as an abstraction layer. Additional hooks are fine: additional
tasks are not.

>> A $sth should not contain any methods for fetching the result of
>> an executed statement;
>...
>> my $rlh = $sth->execute();
>> my $rowset = $rlh->fetchrow_arrayref();

At the very least, you'll need a different interface, as execute()
already returns a value (number of rows affected).

> Not to me. I see a statement as a program function. You prepare it,
> invoke it with arguments, and it returns a result. The result is
> separate from the function.

I see a statement as an object. The results are attributes of the statement
object, and there are methods for getting at that information. The
results are closely tied to that particular statement, so disassociating
them gains nothing, and adds possible confusion. And what do call finish()
with - rlh or sth?

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200507100855
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B90 6714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFC0SELvJuQZxSWSsgRAnSzAJ9V1kgJ1M/7U56hvIcKi6/HhEevBwCg vdli
Roud86HrkgU+7dNnfqygwc4=
=GN3E
-----END PGP SIGNATURE-----

Re: DBI v2 - The Plan and How You Can Help

At 1:24 PM +0000 7/10/05, Greg Sabino Mullane wrote:
>Sure, I don't have a problem with the :foo form, I was just saying
>that for short examples, the '?' format is simpler:
>
>$sth->execute(101);
>
>vs.
>
>$sth->execute( { "myfirstarg", 101 });
>or
>$sth->bind_param("myfirstarg", 101 ...);
>$sth->execute();

My implication is that when you want to use named host parameters,
you would be using names that are descriptive, like this:

$sth = $dbh->prepare(
"select * from foo where bar = :search_text or baz = :search_text" );
$sth->execute( 'search_text' => 'abc' );

Looks better then, doesn't it?

As for the cases where descriptive names aren't so important, such as
with an ad-hoc query for doing arbitrary math, the code which is
generating this statement from the little pieces anyway would also
generate :foo names such as :p1, :p2, etc; that is a named equivalent
to the ? format.

>The can already generate the SQL without a database connection. I guess
>the real advantage is in pre-parsing the SQL and turning it into a
>form more amenable to passing to the database server, by quoting values
>and/or changing placeholder forms. I could possibly see a place for this,
>and I like the option of allowing some things to possible be done before
>the database is connected, but I don't really see much of a real-world
>time saving. But where would this method live? It obviously could not
>be called via $dbh->compile, nor could it be DBI->compile. You would
>almost need a new connection mtehod, or perhaps a flag on the current
>connect() method that tells the driver to load, but defer actually
>conecting to the database.

If my other suggestion about declaring and opening a database
connection is adopted, then that provides what is needed; you can do
$dbh->compile() on a $dbh that hasn't been opened.

>I just want to make sure that we keep
>DBI as an abstraction layer. Additional hooks are fine: additional
>tasks are not.

I agree.

> >> A $sth should not contain any methods for fetching the result of
>>> an executed statement;
>>...
>>> my $rlh = $sth->execute();
>>> my $rowset = $rlh->fetchrow_arrayref();
>
>At the very least, you'll need a different interface, as execute()
>already returns a value (number of rows affected).

If you are striving for full backwards compatability, that is true.
If you are not, then in this example, the number of rows affected can
be gained by invoking a method of $rlh.

> > Not to me. I see a statement as a program function. You prepare it,
>> invoke it with arguments, and it returns a result. The result is
>> separate from the function.
>
>I see a statement as an object. The results are attributes of the statement
>object, and there are methods for getting at that information. The
>results are closely tied to that particular statement, so disassociating
>them gains nothing, and adds possible confusion. And what do call finish()
>with - rlh or sth?

That depends entirely on whether DBI will let you execute() a $sth a
second time, and afterwards continue to read results of the previous
execution from the previous $rlh. If another execute() will kill the
previous results, then you invoke finish() on the $sth; if several
can work concurrently (as you can have several prepared statements
concurrently, I think), then you call it on the $rlh instead. Having
a separate $rlh gives an API to allowing several concurrent executes
on the same $sth.

In any event, if the kill-previous scenario is what always happens, ...

I've had a partial re-thinking on this matter and am now inclined to
agree with you, to remove the rlh object. In that case, a $sth would
continue to work more or less as it already has, but that it should
be possible to get all types of return values, whether a row set or
count of rows affected, using an appropriate $sth method. This would
be a complement or mixin with the current bind_param() functionality,
where you act like the result of a statement is placed in an
'OUT/INOUT' host parameter rather than being something different.

SQL itself has a structure that lends itself to this approach
already. For example, in a SQL stored function, if you want to
store/return the result of a query, you can't do this, that I'm aware
of, but I would be happy if it would work:

SET myvar = SELECT * FROM foo;

or

RETURN SELECT * FROM foo;

But rather you have to:

SELECT * INTO myvar FROM foo;
RETURN myvar;

or just the first line assuming that myvar is an OUT/INOUT function parameter.

If we have to use the OUT/INOUT parameter thing anyway, then a DBI
interface akin to bind_param(), or a new $sth.get_out_param() would
work to get the result. With no separate object for results required.

-- Darren Duncan

Re: DBI v2 - The Plan and How You Can Help

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> I agree. When you get down to it, schema/table/etc reverse
> engineering is a very complicated and involved process. Fetching
> lists of tables or columns etc should no more be built in than SQL
> parsing or generating. In short, anything that can normally be
> fetched or changed using ordinary SQL statements should be left out
> of the DBI core

It's already out of the core though - it's the DBDs responsibility to
do the heavy lifting - DBI just provides a set of skeletized methods.

> On a similar note, utility functions like quote() should be left out
> of the DBI core, and left to either a separate module or someone's
> wrapper, since it's firmly related to SQL generation. If people want
> DBI itself to handle stuff like that for them, they should use host
> parameters for the literals in question.

They should be handled by the DBDs, but I don't really think the default
method in DBI really does any harm (it's simply a s/'/''/g after all :)
I do agree that things like preparse() are perhaps going a bit too far.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200507112009
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B90 6714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFC0wqVvJuQZxSWSsgRAiOoAJ48g3GjHCYXlpSnLgDGi9q2uAfOgACf WyGH
uOPROT8dgCcNyakfe9icOXo=
=D5zv
-----END PGP SIGNATURE-----

Re: DBI v2 - The Plan and How You Can Help

Darren Duncan wrote:
> I should emphasize that I never expected to be able to send any type of
> ASTs over the pipe to the database. They would still be interpreted by
> the database driver for Perl and/or a wrapper thereon, into the database
> native format. Its just that, to an application, it would appear that
> the ASTs were going over the pipe, as to their effect, even though they
> weren't behind the scenes.

Indeed. I think the principle "bug" to "fix" is getting away from this
notion that all you need to do is do a little bit of template-based
query building, use the DBI and magically expect all database portability
problems to go away.

And then, recommend an approach that *is* portable. Take your excellent
Rosetta infrastructure, pull the API to pieces, simplify the
documentation, then condone it as another simple and effective way to
write new database driven applications. And hopefully simplify the DBDs
that necessarily need to do SQL parsing along the way.

So, everyone who is still happy to code to a particular database's SQL
language can continue to do so, but we'll eventually move the Cargo Cult
away from the situation we're in today where there is a vague promise of
portability but so many caveats that it's practically impossible to
write portable code.

Sam.

Re: DBI v2 - The Plan and How You Can Help

Dean Arnold wrote:
> RE: LOBs and "SQL Parse Trees": having recently implemented
> LOB support for a JDBC driver (and soon for a DBD), I can assure
> you that SQL parse trees are unneeded to support them. For databases

Great!

Perhaps you can shed some light on how to do it for this, then.

SQL command;

INSERT INTO FOO (?, ?, ?, ?);

Column 3 is a BYTEA column in Pg and needs special peppering to work.

or this;

SELECT
*
FROM
FOO
WHERE
SOME_DATE_COLUMN > ?

SOME_DATE_COLUMN is the database native date type. On Oracle you'll
need to convert the ? to a 'TO_DATE(?)'.

Sam.

RE: DBI v2 - The Plan and How You Can Help

Sorry, instead of implicit 'commit', I mean to say implicit conversion.

-----------------
Ron Reidy
Lead DBA
Array BioPharma, Inc.


-----Original Message-----
From: Reidy, Ron=20
Sent: Wednesday, July 13, 2005 9:06 AM
To: Sam Vilain; Dean Arnold
Cc: dbi-users@perl.org; dbi-dev@perl.org; perl6-language@perl.org
Subject: RE: DBI v2 - The Plan and How You Can Help


-----Original Message-----
From: Sam Vilain [mailto:sam@vilain.net]
Sent: Tuesday, July 12, 2005 5:04 PM
To: Dean Arnold
Cc: dbi-users@perl.org; dbi-dev@perl.org; perl6-language@perl.org
Subject: Re: DBI v2 - The Plan and How You Can Help


>Dean Arnold wrote:
>> RE: LOBs and "SQL Parse Trees": having recently implemented
>> LOB support for a JDBC driver (and soon for a DBD), I can assure
>> you that SQL parse trees are unneeded to support them. For databases

> or this;

> SELECT
> *
> FROM
> FOO
> WHERE
> SOME_DATE_COLUMN > ?

> SOME_DATE_COLUMN is the database native date type. On Oracle you'll
> need to convert the ? to a 'TO_DATE(?)'.

No you do not. The SQL engine will perform an implicit commit of the =
data.

-----------------
Ron Reidy
Lead DBA
Array BioPharma, Inc.

> Sam.

This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.


This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

Re: DBI v2 - The Plan and How You Can Help

--jt0yj30bxbg11sci
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2005-07-04 18:31:03 -0700, Darren Duncan wrote:
> 5. All details used to construct a connection handle should be=20
> completely decomposed rather than shoved into an ungainly "data=20
> source". Examples of what should be distinct (not all being applicable=
=20
> at once) are:
[list deleted]

The main problem I see with this is that *each application* needs to
handle the decomposed form. With the current opaque "data source" it
doesn't have to know what it means: It just reads it from a config file
(or asks the user in a dialog box or whatever) and passes it on to the
connect method. If this is a complex data type like a hash, it has to
construct the hash. If the application programmer only knows about
databases which know about hostname, port, usename and password, he will
write an application which gets these four parameters and stuffs them in
the hash. He will never anticipate that Informix (as Jonathan explained at
length) needs a database and a server name instead of the hostname and
port, so his application won't be able to connect to Informix.

hp

--=20
_ | Peter J. Holzer \Beta means "we're down to fixing misspelled commen=
ts in
|_|_) | Sysadmin WSR \the source, and you might run into a memory leak =
if=20
| | | hjp@wsr.ac.at \you enable embedded haskell as a loadable module=
and
__/ | http://www.hjp.at/ \write your plugins upside-down in lisp". --ae@o=
p5.se

--jt0yj30bxbg11sci
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iQDQAwUBQtzbu1LjemazOuKpAQGn1gXTBtuv+k7iWv45pQc49h1UTWRR4ne9 Ugnf
tDsXWSyV3VoZEFGQpPPqU9TTDAMhFyleebjDkgUbwljSWtPKqiVLk/pW2fC2 dEws
a18660ozYrpzeFVnG60w++Gmx/X6S6BTJtrmEzsjMARmmVES0j3S69u7b9aV sBQe
LfzLrqJhanKhhs/NkB52YUj1Nf+pEr0gMFap1HOTycoGKGYvweqqd8rAajSG xc1l
ecH3IG5vm2QpxYpCXVaD3ygA/g==
=fmKO
-----END PGP SIGNATURE-----

--jt0yj30bxbg11sci--

Re: DBI v2 - The Plan and How You Can Help

--lrvsYIebpInmECXG
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2005-07-19 14:49:57 +0530, Kiran Kumar wrote:
> We could have an option to do Bulk Inserts ..

Seconded. Actually, DBIv1 already has it (look for bind_param_array in
the manual), but I think support in the DBDs is still not very good
(there was a patch for DBD::Oracle, but if that ever was included in the
official release, I missed the announcement).

hp

--=20
_ | Peter J. Holzer \Beta means "we're down to fixing misspelled commen=
ts in
|_|_) | Sysadmin WSR \the source, and you might run into a memory leak =
if=20
| | | hjp@wsr.ac.at \you enable embedded haskell as a loadable module=
and
__/ | http://www.hjp.at/ \write your plugins upside-down in lisp". --ae@o=
p5.se

--lrvsYIebpInmECXG
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iQDQAwUBQtzc81LjemazOuKpAQEg6gXUClSD75Y4rUYM3IgHEyPEUt/Efb2q 2JnR
XD53DhYkEQWSvk7bly6eofIZsMSBps1kFTaxdLzQl4a4+iEuRkQ/JPI9ZGNA tZz8
VcS1pkwyvePCQPweUT4J5+PAC/rMwDcStxGJA8Rn4b23XqWtJun/kV1ce7YD DOLh
vBmI2feV1H7iRFxaSyFHK0qxu+13/qtu+ActOtpz6kTWlX0EYURCH4l/6zjs pFsM
cTE7ksdI9j1sZ7YdwQYH7JG1fQ==
=kr4N
-----END PGP SIGNATURE-----

--lrvsYIebpInmECXG--

Re: DBI v2 - The Plan and How You Can Help

On Thu, Jul 07, 2005 at 08:32:47AM -0500, Jones Robert TTMS Contractor wrote:
>
> When I go to the donation page and attempt to make a donation, the
> drop-down box does not give DBI as a valid recipient. Is it possible
> several people may not have donated as they noticed the same results, or
> maybe they did and it all went into the Perl Development Fund instead?

The Perl Foundation default donation page doesn't list the DBI
Development Fund (for various reasons). To get that option you can use
http://dbi.perl.org/donate/ which will redirect you[1]

Thank you.

Tim.

[1] https://donate.perlfoundation.org/index.pl?node=Contribution %20Info&selfund=102

>
>
> > -----Original Message-----
> > From: Tim Bunce [mailto:Tim.Bunce@pobox.com]
> > Sent: Friday, July 01, 2005 7:06 PM
> > To: perl6-language@perl.org; dbi-users@perl.org
> > Subject: DBI v2 - The Plan and How You Can Help
> >
> >
> > Once upon a time I said:
> >
> >
> > http://groups-beta.google.com/group/perl.dbi.users/msg/caf18 9d7b404a003?dmode=source&hl=en
> >
> > and wrote
> >
> > http://search.cpan.org/~timb/DBI/Roadmap.pod
> >
> > which yielded:
> >
> >
> > https://donate.perlfoundation.org/index.pl?node=Fund+Drive+D et
> > ails&selfund=102
> >
> > (A little over $500 of that I effectively put in myself.)
> >
> > My *sincere* thanks to all those who donated to the fund, especially
> > individuals. I had hoped for more corporate response with less from
> > individuals and I'm touched by the personal generosity shown.
> >
> > I've not drawn any money from it yet and doubt that I will myself.
> > (I'm considering suggesting that the Perl Foundation make payments
> > from the fund to people making specific contributions to the DBI.
> > I'm thinking especially of work on a comprehensive test harness.
> > But I'll see how the developments below pan out before making
> > specific arrangements.)
> >
> >
> > So, that lead to:
> >
> >
> > http://groups-beta.google.com/group/perl.dbi.dev/browse_frm/ th
> > read/ef14a9fc0a37441f/fb8fe20a86723da0#fb8fe20a86723da0
> >
> > Which sums up fairly well where I'm at: DBI v1 will rumble on
> > for Perl 5
> > and DBI v2 will be implemented for Perl 6.
> >
> >
> > --- digression ---
> >
> > At this point I'd like to make a slight digression to highlight the
> > amazing work going on in the Perl 6 community at the moment.
> > Especially Autrijus' Pugs project which has brought Perl 6 to life.
> > Literally. Take a look at:
> >
> > http://pugscode.org/talks/yapc/slide1.html
> > http://use.perl.org/~autrijus/journal
> >
> > and especially:
> >
> > http://use.perl.org/~autrijus/journal/24898
> >
> > Yes, that really is Perl 6 code using the DBI being executed by Pugs.
> >
> > That's great, and I was truly delighted to see it because it takes the
> > pressure off the need to get a DBI working for Perl 6 - because it
> > already is working for Perl 6. At least for Pugs. (The Ponie project
> > is also likely to provide access to Perl 5 DBI from Perl 6 by enabling
> > future versions of Perl 5 to run on Parrot.)
> >
> > --- digression ---
> >
> >
> > I have recently come to an arrangement that will enable me to put some
> > worthwhile development time into DBI (still very much part-time, but
> > enough to give it focus and move forward).
> >
> > My initial goals are:
> >
> > 1. to work on a more detailed specification for the DBI v2 API that
> > takes advantage of appropriate features of Perl 6.
> >
> > 2. to work on a more detailed specification for the DBDI API
> >
> > http://groups-beta.google.com/group/perl.perl6.internals/msg /c
> > fcbd9ca7ee6ab4
> >
> > 3. to work on tools to automate building Parrot NCI interfaces to
> > libraries (such as database client libraries, obviously :)
> >
> >
> > But I'm hoping you'll join in and help out.
> >
> > I've kept an eye on Perl 6 and Parrot developments but I'm no
> > expert in
> > either. What I'd like *you* to do is make proposals (ideally fairly
> > detailed proposals, but vague ideas are okay) for what a Perl
> > 6 DBI API
> > should look like.
> >
> > Keep in mind that the role of the DBI is to provide a consistent
> > interface to databases at a fairly low level. To provide a
> > *foundation*
> > upon which higher level interfaces (such as Class::DBI,
> > Tangram, Alzabo
> > etc. in Perl 5) can be built.
> >
> > So, if you have an interest in the DBI and Perl 6, put your thinking
> > cap on, kick back and dream a little dream of how the DBI could be.
> > How to make best use of the new features in Perl 6 to make
> > life easier.
> >
> > Then jot down the details and email them to me (or to
> > dbi-users@perl.org
> > if you want to kick them around in public for a while first).
> >
> > I'm about to fly off for two weeks vacation (in a few hours),
> > blissfully
> > absent of any hi-tech gear beyond a mobile phone. When I get back I'll
> > gather up your emails and try to distill them into a coherent whole.
> >
> > Have fun!
> >
> > Tim.
> >

Re: DBI v2 - The Plan and How You Can Help

On Sat, Jul 02, 2005 at 01:06:02AM +0100, Tim Bunce wrote:
> Once upon a time I said:

I'm back now and, after digesting a small mountain of non-DBI related
emails, I'll start digesting all your replies and getting up to speed
with Perl 6.

Many thanks to all who replied on and off-list.

Tim.

Re: DBI v2 - The Plan and How You Can Help

I am moving the following from a different thread into this dbi-users
one, where it is more relevant, since it is effectively a DBI v2
suggestion. Portions not relevant to DBI v2 have been snipped out.
While poop-group is cc'd, send all replies on this subject just to
dbi-users. -- Darren Duncan

At 8:24 PM +0000 7/21/05, Terrence Brannon wrote (on poop-group):
> > At 7:05 AM -0400 7/19/05, John Siracusa wrote:
> >>I use a module (Rose::DB) that parses and formats db-specific column values
>>>for me: various kinds of dates, "SET"s, arrays, all the crazy db-specific
>>>data types that are a pain to manually parse and then format.
>
>Are sets and arrays useful types? I don't think so. The date
>functionality you speak of might belong in DBI proper perhaps?
>
>>>Very few DBI abstraction modules provide this feature, but IMO it's
>>>essential.
>
>Yes, and since it is essential it belongs at DBI level perhaps?

Re: DBI v2 - The Plan and How You Can Help

> At 8:24 PM +0000 7/21/05, Terrence Brannon wrote (on poop-group):
>>> At 7:05 AM -0400 7/19/05, John Siracusa wrote:
>>>> I use a module (Rose::DB) that parses and formats db-specific column values
>>>> for me: various kinds of dates, "SET"s, arrays, all the crazy db-specific
>>>> data types that are a pain to manually parse and then format.
>>
>> Are sets and arrays useful types? I don't think so.

You're looking at it the wrong way: are SETs and arrays used in real
database installations running real apps? Yes, they are. Maybe they
"shouldn't be" or whatever, but I need code that helps me deal with my
reality, not the world as I might imagine it, were I consulted from day one
;)

>> The date functionality you speak of might belong in DBI proper perhaps?

I argued for that a while ago but it was deemed inappropriate for something
as low-level as DBI. I can see some truth to that argument, but I'd still
like to see some sort of official hooks in DBI, if not actual
implementations.

Anyway, even ignoring dates, the bottom line is that every database has its
own funky data types, and syntaxes to go with them. I don't want to have to
remember the N ways to write a BIT constant in Postgres, and I certainly
don't want to have to parse them manually each time I want to do something
with such a column value. I want my DB abstraction layer to help me here.
If it's not DBI proper, then it should be something above it.

-John

Re: DBI v2 - The Plan and How You Can Help

> Which is why major DBMSs are increasingly relying on SSO
> based solutions. (e.g., Kerberos/LDAP authentication).
> Not certain if DBI is the proper level to implement that,
> (probably needs to be down at the DBD <=> DBMS level).
> And "in a standard way" may still be wishful thinking.
>
> Also, I'm not sold on the idea that a ~/.dbi file is particularly
> secure in that regard. Not neccesarily opposed, just not convinced
> its the right solution. (I don't like cleartext passwords either,
> but due to the variance in DBMS's authentication methods, I don't know if
> DBI can solve that problem).

Could use an approach like LWP: Stock "authentication" hook
gets called if the remote side asks for authn data,
something like:

{
RaiseError => 1,
AuthnHook => $coderef,
}

would let you cover it however you like.

And/or an automatic redirect via tunnel (basically something
like ssh -L with an ssh module handling the redirect) might
also work.


--
Steven Lembark 85-09 90th Street
Workhorse Computing Woodhaven, NY 11421
lembark@wrkhors.com 1 888 359 3508

Re: DBI v2 - The Plan and How You Can Help

On Sat, Jul 09, 2005 at 12:35:30AM -0700, Jonathan Leffler wrote:
>
> As a general comment on DBI v2; we need to beware of the second-system
> effect (Brooks "Mythical Man Month").

Well understood.

> In particular, the DBI must not mandate impossible levels of support from
> the drivers.

Also well understood.

> It will benefit you nothing if the DBI is immaculate and
> wonderful and incredibly all-singing and all-dancing, but no-one can write a
> driver for it because the requirements cannot be met by the actual DBMS that
> Perl + DBI needs to work with.

One of my key goals is to make writing drivers, for all kinds of data
sources, easier than it is now.

Tim.

Re: DBI v2 - The Plan and How You Can Help

On Sat, Jul 09, 2005 at 10:25:32PM +1000, Adam Kennedy wrote:
> >In particular, the DBI must not mandate impossible levels of support from
> >the drivers. It will benefit you nothing if the DBI is immaculate and
> >wonderful and incredibly all-singing and all-dancing, but no-one can write
> >a driver for it because the requirements cannot be met by the actual DBMS
> >that Perl + DBI needs to work with.
>
> I concur. Like CPAN as a whole, DBI's strength is in it's complete and
> near universal coverage of all databases, and insanely great (and
> occasisionally greatly insane) drivers that do strange and wonderful things.
>
> If we start sacrificing drivers by raising the bar too high, DBI as a
> whole suffers. Anyone proposing new features for DBI needs to be
> extremely careful of CYJ syndrome.
>
> Can't You Just (or sometimes Could You Just) syndrome is described here.
>
> http://c2.com/cgi/wiki?CouldYouJust
> http://www.oreillynet.com/pub/wlg/3593
> http://c2.com/cgi/wiki?JustIsaDangerousWord
>
> Go read them now. I'll wait...

That's a significant part of what happened to perl5-porters in The Bad Years.

Many more talkers than doers and much use of "we could do ..." when
the doing would clearly have to be done by someone else.

> I have an increasing suspicion that having open design processes like
> the Tim's call for comments plays a big part in it as well.

Did I ever say we'd have an open design process? :-)

I just called for suggestions, proposals, and random thoughts.
It's my job to mix those in with my own random thoughts and
try to distill something reasonably coherent and Practical.

Then we'll go round the loop a few (dozen) times kicking the tires
and mixing metaphors till enough people are happy enough.
(I get the casting vote on behalf of the silent majority :)

I was a little dissapointed that there wasn't greater focus on using
Perl6 features - especially as it would have helped kick-start my own
understanding of Perl6 topics that I expect to be significant (such as
Roles and Pairs, to pick two at random). Perhaps the community of
Perl6+DBI users is too small at this point.

And nobody mentioned JDBC as a potential model. Odd that.

Still, I'm sure things will liven up once I've put an initial sketch
together...

Tim.

Layering (was: DBI v2 - The Plan and How You Can Help)

On Thu, Jul 14, 2005 at 04:15:53PM +0200, Jochen Wiedmann wrote:
> On 7/14/05, Sam Vilain wrote:
>
> > Of course it will be entirely possible to layer support for this sort of
> > thing atop any DBI interface;
>
> Exactly my point. Please be so kind as to implement your ideas in a
> DBI extension. Time and community will prove whether you are right by
> using your extension or not.

*nod*

> Fact is, that there are quite some drivers which will never be able to
> adhere to your wishes. Blocking them implementing a DBI2 driver seems
> (to me) to be a larger problem, compared with the need of using a DBI2
> subclass and not DBI2 directly.

The key design issue here is not "can the DBI do it" but "can the DBI
make it possible to implement X elsewhere".

The DBI is fundamentally a low-level interface to database-specific
APIs with just enough sugar to make it palatable.

I hope DBI v2 will polish up some rough edges and make more things
possible - rather than actually doing much more itself - but it'll
remain a low-level interface to database-specific APIs.

If it can be done outside the DBI it probably should.

Tim.

Re: DBI v2 - The Plan and How You Can Help

Tim Bunce wrote:

>
> And nobody mentioned JDBC as a potential model. Odd that.
>

I was sorely tempted to do so (and did mention it a few times in
my posts, along w/ ODBC and ADO.NET), but there are some things about
JDBC which rub me the wrong way (e.g., explicit set/get methods for every
data type; no true binding support; the lame "bulk" interface; etc.).
I'm not crazy about all the DataSource business, either.

But the threading support, the Factory pattern presented by Statement classes,
the nice separation of metadata from statements/resultsets/connections, and XA
would certainly be nice models to follow.

One area of concern I have is the ability to subclass. I've been struggling
w/ trying to subclass+extend JDBC the same way I subclass DBI for some things,
and haven't found any app-neutral solutions just yet (trying to wrap
another JDBC driver and expose its driver specific methods seems to require
a lot of extra heavy lifting).

> Still, I'm sure things will liven up once I've put an initial sketch
> together...
>
> Tim.
>

Dean Arnold
Presicient Corp.

Re: DBI v2 - The Plan and How You Can Help

On 8/16/05, Tim Bunce wrote:
> I was a little dissapointed that there wasn't greater focus on using
> Perl6 features - especially as it would have helped kick-start my own
> understanding of Perl6 topics that I expect to be significant (such as
> Roles and Pairs, to pick two at random). Perhaps the community of
> Perl6+DBI users is too small at this point.

I'm afraid that DBI2 for Perl 6 will fall into the trap that I sometimes
feel like DBI1 fell into: the curse of being designed before the idioms and
Best Practices of API design in the language have been established.

I think it'll take years, and much actual production experience building
Perl 6 modules before the community learns what works and what doesn't for a
Perl 6 API (let alone implementation). So trying to pin down a "properly
Perl-6-ish" API before Perl 6 is even through the language design process
strikes me as a Very Bad Idea.

That could explain why there were so few Perl 6 related suggestions: no one
knows how to design a good Perl 6 API yet, and any guess is very likely to
be wrong. Instead, suggestions have focused on what we do know: DBI in Perl
5 and Perl 5 API design. In that spirit, here's my suggestion: no more
configuration through magic/tied hashes. (e.g., $dbh->{'AutoCommit'} = 1)
(Probably goes without saying, but I wanted to make sure someone said it ;)

Anyway, it maybe worthwhile to have a DBI 1.99 first, and then maybe a 1.999
after that. Basically, get one or two willing DBD authors who will help you
to test and then throw away the first two attempts at a Perl 6 DBI API.
Then at least you'll have some confidence when you commit to a DBI 2.0
API...which will be several years after Perl 6 is released, I hope.

Of course, *someone* has to "go first" so we can all learn what works best
in Perl 6. I'm just saying that maybe DBI, which took the bullet in Perl 5
to some degree, is under no obligation to do so again. (This assumes that
we'll have some way to use Perl 5 DBI within Perl 6 to tide us over, of
course...)

-John

Re: DBI v2 - The Plan and How You Can Help

At 4:04 PM +0100 8/16/05, Tim Bunce wrote:
>I was a little dissapointed that there wasn't greater focus on using
>Perl6 features - especially as it would have helped kick-start my own
>understanding of Perl6 topics that I expect to be significant (such as
>Roles and Pairs, to pick two at random). Perhaps the community of
>Perl6+DBI users is too small at this point.

One way that the Perl 6 thought process can be started is in
considering the design principles laid out in Damian's new Best
Practices book. I said to Damian at OSCON that I thought the
practices he was putting forward were intended to get people thinking
now in Perl 5 about ways of doing things that will be the natural way
of doing them in Perl 6; he said something along the lines that I had
good insight. So these practices are probably some good things to
keep in mind as we move forward.

Now, speaking specifically in Perl 6 terms ...

I suggest that DBI v2 has a more formal separation between interface
and implementation. The parts of DBI can be grouped into these
categories:

1. Role definitions for the public behaviour/API that DBI-using apps see.

2. Role definitions for the behaviour/API that DBI drivers/engines must have.

3. Class definitions that implement #1 and invoke #2.

4. Class definitions having a generic implementation of #2 or parts
thereof, which a driver/engine can complete or override.

5. Basic utility classes that exist to the side of the above, which
such as DBI drivers can optionally use to do some common things
without rolling their own.

6. A basic test suite.

I also recommend expelling some parts of the DBI distro into their
own distros and/or leaving them to third parties. A prime example is
the proxy server/client stuff; that should be a separate project.

-- Darren Duncan

Re: DBI v2 - The Plan and How You Can Help

Tim Bunce wrote:

>I just called for suggestions, proposals, and random thoughts.
>It's my job to mix those in with my own random thoughts and
>try to distill something reasonably coherent and Practical.
>
>Then we'll go round the loop a few (dozen) times kicking the tires
>and mixing metaphors till enough people are happy enough.
>(I get the casting vote on behalf of the silent majority :)
>
> =20
>
Just two ideas from one of the usually silent majority:

First: There should be a *simple* way for a DBD to inherit from another=20
DBD. From what I've learned the last two weeks about the internals of=20
the DBI, this is currently done in a way that is quite different from=20
the way the remainder of Perl does it. I would like to be able to do the =

following:

package DBD::MyBadlyHackedODBC;
use base 'DBD::ODBC'; # <--- ordinary Perl inheritance without any=20
DBI "magic"
sub someMethodInODBC
{
my $self=3Dshift;
$self->SUPER::someMethodInODBC(reverse @_)
}
1;

my $dbh=3DDBI->connect('dbi:MyBadlyHackedODBC',...);

This would allow me to change the behaviour of a DBD for my needs=20
without having to dig through kilobytes of C and XS code.

DBDs are currently four classes, making this kind of inheritance a=20
little bit harder, because my hacked DBD would have to inherit the base, =

::dr, ::db and ::st classes. Perhaps a tiny pragmatic module could help: =

use DBI::base 'DBD::ODBC' (instead of use base 'DBD::ODBC') would create =

the four classes for my hacked DBD, all inheriting from their=20
corresponding classes. So my code would look like this:

package DBD::MyBadlyHackedODBC;
use DBI::base 'DBD::ODBC';
package DBD::MyBadlyHackedODBC::db;
sub prepare
{
my ($dbh, $statement, $attribs)=3D@_;
$dbh->SUPER::prepare(reverse $statement,$attribs)
}
1;


Second: $h->func(@func_arguments, $func_name) is ugly. The $func_name=20
argument should really be the first argument, not the last.=20
DBD::Foo::db->install_method($method_name, \%attr); is a step into the=20
right direction, but the driver-specific methods should be available for =

use right after DBI->connect(), without having to "import" them=20
manually. The driver-specific prefix is a good thing, it clearly=20
indicates non-portable code: If I write=20
$dbh->pg_polish_harddisk('now','shiny'), why should I have to call=20
DBD::Pg::db->install_method('pg_polish_harddisk') first?


Alexander=20
=20
Please inform us immediately if this e-mail and/or any attachment was=20
transmitted incompletely or was not intelligible.
____________________________________________________________ _____________=
__
=20
This e-mail and any attachment is for authorized use by the intended=20
recipient(s) only. It may contain proprietary material, confidential=20
information and/or be subject to legal privilege. It should not be =
copied,=20
disclosed to, retained or used by any other party.=20
If you are not an intended recipient then please promptly delete this=20
e-mail and any attachment and all copies and inform the sender.=20

Re: DBI v2 - The Plan and How You Can Help

On Wed, Aug 17, 2005 at 07:57:08AM +0200, Alexander Foken wrote:
> Tim Bunce wrote:
>
> >I just called for suggestions, proposals, and random thoughts.
> >It's my job to mix those in with my own random thoughts and
> >try to distill something reasonably coherent and Practical.
> >
> >Then we'll go round the loop a few (dozen) times kicking the tires
> >and mixing metaphors till enough people are happy enough.
> >(I get the casting vote on behalf of the silent majority :)
>
> Just two ideas from one of the usually silent majority:
>
> First: There should be a *simple* way for a DBD to inherit from another DBD.

Yes, that's a big part of the plan. Logging and profiling, for example,
may be implemented by a proxy driver that intercepts and forwards calls
to the main driver.

> From what I've learned the last two weeks about the internals of
> the DBI, this is currently done in a way that is quite different from
> the way the remainder of Perl does it. I would like to be able to do the
> following:
>
> package DBD::MyBadlyHackedODBC;
> use base 'DBD::ODBC'; # <--- ordinary Perl inheritance without any DBI "magic"
> sub someMethodInODBC
> {
> my $self=shift;
> $self->SUPER::someMethodInODBC(reverse @_)
> }
> 1;
> my $dbh=DBI->connect('dbi:MyBadlyHackedODBC',...);
>
> This would allow me to change the behaviour of a DBD for my needs
> without having to dig through kilobytes of C and XS code.
>
> DBDs are currently four classes, making this kind of inheritance a
> little bit harder, because my hacked DBD would have to inherit the base,
> ::dr, ::db and ::st classes.

Yeap.

> Perhaps a tiny pragmatic module could help:
> use DBI::base 'DBD::ODBC' (instead of use base 'DBD::ODBC') would create
> the four classes for my hacked DBD, all inheriting from their
> corresponding classes. So my code would look like this:
>
> package DBD::MyBadlyHackedODBC;
> use DBI::base 'DBD::ODBC';
> package DBD::MyBadlyHackedODBC::db;
> sub prepare
> {
> my ($dbh, $statement, $attribs)=@_;
> $dbh->SUPER::prepare(reverse $statement,$attribs)
> }
> 1;

Patches welcome!

It certainly can be done: DBD::CSV, for example, is a subclass of DBD::File.

> Second: $h->func(@func_arguments, $func_name) is ugly. The $func_name
> argument should really be the first argument, not the last.

That was done for efficiency (back in the days when that mattered more)
so the name could just be popped off the argument stack.

But these days you shouldn't use func() at all - the driver should use
install_method() to register its private methods with the DBI dispatcher
so they can be called directly.

> DBD::Foo::db->install_method($method_name, \%attr); is a step into the
> right direction, but the driver-specific methods should be available for
> use right after DBI->connect(), without having to "import" them
> manually.

The _driver_ is meant to call install_method when it's loaded.
Talk to the driver authors. I'm just the man in the middle.

> The driver-specific prefix is a good thing, it clearly
> indicates non-portable code: If I write
> $dbh->pg_polish_harddisk('now','shiny'), why should I have to call
> DBD::Pg::db->install_method('pg_polish_harddisk') first?

You shouldn't - see above.

Tim.

Re: DBI v2 - The Plan and How You Can Help

On Tue, Aug 16, 2005 at 12:12:02PM -0700, Dean Arnold wrote:
> Tim Bunce wrote:
>
> >And nobody mentioned JDBC as a potential model. Odd that.
>
> I was sorely tempted to do so (and did mention it a few times in
> my posts, along w/ ODBC and ADO.NET), but there are some things about
> JDBC which rub me the wrong way (e.g., explicit set/get methods for every
> data type no true binding support; the lame "bulk" interface; etc.).
> I'm not crazy about all the DataSource business, either.

I think all those are fixable for Perl/Parrot. Same for the painful
need for try & catch every few lines.

> But the threading support, the Factory pattern presented by Statement
> classes, the nice separation of metadata from
> statements/resultsets/connections, and XA would certainly be nice
> models to follow.

That's what I'm thinking. Not only nice but also well proven and widely
understood.

> One area of concern I have is the ability to subclass. I've been
> struggling w/ trying to subclass+extend JDBC the same way I subclass
> DBI for some things, and haven't found any app-neutral solutions just
> yet (trying to wrap another JDBC driver and expose its driver specific
> methods seems to require a lot of extra heavy lifting).

There are lots of people who have problems or complaints about
subclassing the DBI :)

Tim.

Re: DBI v2 - The Plan and How You Can Help

On Tue, Aug 16, 2005 at 03:58:54PM -0400, John Siracusa wrote:
> On 8/16/05, Tim Bunce wrote:
> > I was a little dissapointed that there wasn't greater focus on using
> > Perl6 features - especially as it would have helped kick-start my own
> > understanding of Perl6 topics that I expect to be significant (such as
> > Roles and Pairs, to pick two at random). Perhaps the community of
> > Perl6+DBI users is too small at this point.
>
> I'm afraid that DBI2 for Perl 6 will fall into the trap that I sometimes
> feel like DBI1 fell into: the curse of being designed before the idioms and
> Best Practices of API design in the language have been established.
>
> I think it'll take years, and much actual production experience building
> Perl 6 modules before the community learns what works and what doesn't for a
> Perl 6 API (let alone implementation). So trying to pin down a "properly
> Perl-6-ish" API before Perl 6 is even through the language design process
> strikes me as a Very Bad Idea.

I remember the early years of Perl 5 development, when a new feature was
added there'd be a period of over-zealous use followed by a hangover as
all the problems and edge-cases became apparent.

With Perl 6 there's going to be some almighty hangovers :)

> That could explain why there were so few Perl 6 related suggestions: no one
> knows how to design a good Perl 6 API yet, and any guess is very likely to
> be wrong. Instead, suggestions have focused on what we do know: DBI in Perl
> 5 and Perl 5 API design. In that spirit, here's my suggestion: no more
> configuration through magic/tied hashes. (e.g., $dbh->{'AutoCommit'} = 1)
> (Probably goes without saying, but I wanted to make sure someone said it ;)

Hey, it seemed like a good idea at the time :)

(Actually it's still a good idea in many ways, especially in relation to
its behaviour for unknown driver-private attributes and DBI version skew.
But it does need rethinking for DBI2.)

> Anyway, it maybe worthwhile to have a DBI 1.99 first, and then maybe a 1.999
> after that. Basically, get one or two willing DBD authors who will help you
> to test and then throw away the first two attempts at a Perl 6 DBI API.
> Then at least you'll have some confidence when you commit to a DBI 2.0
> API...which will be several years after Perl 6 is released, I hope.

It'll be DBI 2 as DBI 1 still has a very long life ahead of it, but
it'll be DBI 2.0.00xxx for quite a while :)

> Of course, *someone* has to "go first" so we can all learn what works best
> in Perl 6. I'm just saying that maybe DBI, which took the bullet in Perl 5
> to some degree, is under no obligation to do so again. (This assumes that
> we'll have some way to use Perl 5 DBI within Perl 6 to tide us over, of
> course...)

I'm in no great rush as one of my core assumptions is that DBI v1 will
be available in Perl 6 via either Ponie or direct embedding of libperl5.so.

Tim.

Re: DBI v2 - The Plan and How You Can Help

On 8/17/05 5:39 AM, Tim Bunce wrote:
> On Tue, Aug 16, 2005 at 03:58:54PM -0400, John Siracusa wrote:
>> I think it'll take years, and much actual production experience building
>> Perl 6 modules before the community learns what works and what doesn't for a
>> Perl 6 API (let alone implementation). So trying to pin down a "properly
>> Perl-6-ish" API before Perl 6 is even through the language design process
>> strikes me as a Very Bad Idea.
>
> I remember the early years of Perl 5 development, when a new feature was
> added there'd be a period of over-zealous use followed by a hangover as
> all the problems and edge-cases became apparent.

Early years? Just look at inside-out objects or MI with NEXT today! Maybe
it never ends...for some people, anyway ;)

> With Perl 6 there's going to be some almighty hangovers :)

Understatement of the week :)

>> Anyway, it maybe worthwhile to have a DBI 1.99 first, and then maybe a 1.999
>> after that. Basically, get one or two willing DBD authors who will help you
>> to test and then throw away the first two attempts at a Perl 6 DBI API.
>> Then at least you'll have some confidence when you commit to a DBI 2.0
>> API...which will be several years after Perl 6 is released, I hope.
>
> It'll be DBI 2 as DBI 1 still has a very long life ahead of it, but
> it'll be DBI 2.0.00xxx for quite a while :)

I just meant that there should be several, possibly very different, attempts
at DBI2 before the "real" DBI2 API is pinned down. Making the experiments
have a 1.99x version helps to prevent people from thinking "this is DBI2!"
when it's really just the first or second prototype.

As for the actual 1.x DBI reaching 1.99, well, all I can say is to start
using that hundredths place! :)

-John

Re: DBI v2 - The Plan and How You Can Help

On Wed, 17 Aug 2005 10:39:43 +0100, Tim Bunce wrote:

>> I think it'll take years, and much actual production experience building
>> Perl 6 modules before the community learns what works and what doesn't for a
>> Perl 6 API (let alone implementation). So trying to pin down a "properly
>> Perl-6-ish" API before Perl 6 is even through the language design process
>> strikes me as a Very Bad Idea.
>
>I remember the early years of Perl 5 development, when a new feature was
>added there'd be a period of over-zealous use followed by a hangover as
>all the problems and edge-cases became apparent.
>
>With Perl 6 there's going to be some almighty hangovers :)

Go ahead, implement it already. But it should be a side project, not
"the future of DBI". Like Topaz is not Perl6.



--
Bart.

Re: DBI v2 - The Plan and How You Can Help

On Wed, Aug 17, 2005 at 02:21:48PM +0200, Bart Lateur wrote:
> On Wed, 17 Aug 2005 10:39:43 +0100, Tim Bunce wrote:
>
> >> I think it'll take years, and much actual production experience building
> >> Perl 6 modules before the community learns what works and what doesn't for a
> >> Perl 6 API (let alone implementation). So trying to pin down a "properly
> >> Perl-6-ish" API before Perl 6 is even through the language design process
> >> strikes me as a Very Bad Idea.
> >
> >I remember the early years of Perl 5 development, when a new feature was
> >added there'd be a period of over-zealous use followed by a hangover as
> >all the problems and edge-cases became apparent.
> >
> >With Perl 6 there's going to be some almighty hangovers :)
>
> Go ahead, implement it already. But it should be a side project, not
> "the future of DBI". Like Topaz is not Perl6.
>
>

The original DBI took two years being specified for Perl 4 and was then
implemented for Perl 5. DBI v2 started out life as a set of enhancements
for DBI on Perl5 but will now be implemented for Perl 6.

At least this time the specification work will be on the right version
of Perl.

Even after all that specification work, the DBI took two more years
from first release to reach 1.0. It's certainly not perfect, but it
ain't too bad either.

I guess I don't have a history of rushing...

Tim.