Discussion:
DBI v2 - The Plan and How You Can Help [DRAFT]
(too old to reply)
Sam Tregar
2005-07-02 18:32:18 UTC
Permalink
http://groups-beta.google.com/group/perl.dbi.users/msg/caf189d7b404a003?dmode=source&hl=en
Minor note: these URLs are too long to easily copy and paste. Maybe
use a URL shortening sevrice like tinyurl.com.
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.)
How much development will $500 buy? It seems like the funding drive
was a failure. Maybe skip talking about it or move it to a separate
email? (Yes, I'm treating this like marketting.)
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.
My first reaction to this was not good, mostly because I expect Perl 6
to take a long time to reach a state when I can use it for real work.
However, on further consideration I think this is a reasonable
approach. Perl 5 doesn't need a DBI 2 - DBI 1 is working great!
Since Perl 6 will need a new DBI then it might as well be a better
one.
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.
Well, since you asked, my list:

- Asynchronous queries (coroutines? threads?)

- High-level support for cursors

- Ditch magic tied attributes and route all access through method
calls. This would make sub-classing (and programming) DBI easier,
in my opinion.

- Default RaiseError to 1 (pretty minor but I see a lot of pain
related to missed errors)

- Integrated transactions and Perl 6 try/catch semantics? It seems
like they're often used to address the same problem but I'm not
sure what the right integration is.

-sam
Dean Arnold
2005-07-02 20:30:15 UTC
Permalink
Post by Sam Tregar
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.)
How much development will $500 buy? It seems like the funding drive
was a failure. Maybe skip talking about it or move it to a separate
email? (Yes, I'm treating this like marketting.)
Er, marketting in what way ? Developing DBI/DBDs costs time & resources.
Many major for-profit orgs are leveraging
DBI (and Perl in general) to make lots (and I again mean *lots*)
of money. Asking them to pony up some modest contributions
(or at least staff time) *for their own benefit and security*
doesn't seem like "marketting" any more than this weekend's Live 8
concert is "marketting" for starving children in Africa (ok, maybe
thats not a PC metaphor, but hopefully it gets the point across).
The fact they choose to "live off the fat of the land" is more than
a bit frustrating. If anything, perhaps DBI and DBD authors should
use a MySQL type dual license to cover the development/support costs.
Post by Sam Tregar
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.
My first reaction to this was not good, mostly because I expect Perl 6
to take a long time to reach a state when I can use it for real work.
However, on further consideration I think this is a reasonable
approach. Perl 5 doesn't need a DBI 2 - DBI 1 is working great!
Well, maybe.

While Perl5, DBI, and threading may never be able to mix properly,
it would be nice to be able to exploit them in
large scale/complex apps, which I fear Perl6 may not be capable of
for 2+ years. And Perl5 will live on long after Perl6 has fully
ripened.
Post by Sam Tregar
- Asynchronous queries (coroutines? threads?)
Threads. If you've ever done much Java/JDBC work, you'll
realize how much simpler a solution to async it is.
(Ignoring the rest of Java/JDBC's undesirable traits)
Post by Sam Tregar
- High-level support for cursors
Seconded.
Post by Sam Tregar
- Ditch magic tied attributes and route all access through method
calls. This would make sub-classing (and programming) DBI easier,
in my opinion.
I'd vote against "ditch", maybe provide accessor/mutator i/fs
for those that desire them. Some of us still prefer a declarative
approach. (In fact, I'd vote for a fully declarative i/f
to *everything*, but that would likely scare the OO crowd away,
it certainly keeps them away from SQL::Preproc !^)
Post by Sam Tregar
- Default RaiseError to 1 (pretty minor but I see a lot of pain
related to missed errors)
I'd think the native exception handling would be the proper paradigm.
At which point things like RaiseError, PrintError, HandleError, etc
aren't really needed.
Post by Sam Tregar
- Integrated transactions and Perl 6 try/catch semantics? It seems
like they're often used to address the same problem but I'm not
sure what the right integration is.
Not certain what you mean by "integrated transactions" ?
I'd suggest a peek at JDBC. An explicit indication of "inside transaction"
or "outside transaction" would be nice.
Post by Sam Tregar
-sam
I think some of the following have been raised before, but to reiterate:

1. A standard conformance test suite for DBDs: a very big
(and likely mundane) chore, but really needed/valuable.

2. Full thread safe support.

3. A std. i/f for multistatement result sets.

4. Std. 2PC i/f (XA) ?

5. A better set of metadata for DBDs to report the functionality
they support. E.g., today, the only way to find out if a DBD
is threadsafe (at runtime) seems to be try it and hope for the best.

6. Std. i/f support for exotic datatypes (intervals, UDTs, ARRAY, etc.)

7. Better docs for DBD developers (hopefully PDBI2E will address this ?)

8. (I know I've argued against this in the past, but I've
had a change of heart) DBI inclusion as a Perl CORE module. Database
access is too prevalent these days for it to continue as an afterthought,
and it would likely increase test coverage/exposure.

Now, if only I had time to pitch and resolve some of these (tho I
may, of neccesity, help solve Perl5 thread issues in the near future).

My 0.02,

Dean Arnold
Presicient Corp.
Sam Tregar
2005-07-02 21:52:52 UTC
Permalink
Post by Dean Arnold
Er, marketting in what way ?
I see the email as an attempt to attract new developers to the
project. Hence it is, in some respects, an attempt to "market" DBI v2
to developers.
Post by Dean Arnold
Developing DBI/DBDs costs time & resources. Many major for-profit
orgs are leveraging DBI (and Perl in general) to make lots (and I
again mean *lots*) of money. Asking them to pony up some modest
contributions (or at least staff time) *for their own benefit and
security* doesn't seem like "marketting" any more than this
weekend's Live 8 concert is "marketting" for starving children in
Africa (ok, maybe thats not a PC metaphor, but hopefully it gets the
point across). The fact they choose to "live off the fat of the
land" is more than a bit frustrating. If anything, perhaps DBI and
DBD authors should use a MySQL type dual license to cover the
development/support costs.
Relax. I'm just suggesting that Tim not talk about a fund drive that
more-or-less failed when trying to drum up volunteers for the new
project. It will be obvious to everyone who reads this that $500
isn't enough to help in a significant way. That may serve to subtract
from the otherwise positive tone of his plea.

-sam
Dean Arnold
2005-07-02 22:56:29 UTC
Permalink
Post by Sam Tregar
Post by Dean Arnold
Er, marketting in what way ?
I see the email as an attempt to attract new developers to the
project. Hence it is, in some respects, an attempt to "market" DBI v2
to developers.
<My rant redacted.>
Post by Sam Tregar
Relax. I'm just suggesting that Tim not talk about a fund drive that
more-or-less failed when trying to drum up volunteers for the new
project. It will be obvious to everyone who reads this that $500
isn't enough to help in a significant way. That may serve to subtract
from the otherwise positive tone of his plea.
-sam
[ I'll take the role of "bad cop" here...
I don't intend this as personal attack.
Fortunately, Tim's on vacation, so he can dress me down
after he gets back. ]

These forums are intended to ask for, and provide assistance to,
users and developers of the Perl DBI. One way (a very
important way) to provide that assistance is for those who
profit the most from the Perl DBI,
to provide funding and/or other resources. If it's unseemly
for Mssr. Bunce to ask for community assistance here (esp. when
so many ask for assistance from *him*), then where should he ask ?
If the issue is the perceived "shaming", I can only posit that
asking nicely obviously didn't work.

This issue needs to be addressed. Those who profit from DBI need
to be made aware that resources are needed to continue to
provide a quality product, if only so the websites that
provide their profits don't start succumbing to code rot.

My rant is intended more as incitement to the community at large
(the "silent majority").
If you work for an org that relies on DBI to keep the customers
coming thru the doors (or URLs, as the case may be), you need to
elevate this issue to your management and get some commitment from
them to help out, if only in a "Googlesque" manner (i.e.,
letting some staff contribute 10-20% of their paid time
to the effort).

Its all well and good for us to give Tim a "honey-do" list
for DBIv2. Whats needed is some serious commitment from
developers, and esp. users who apparently feel entitled to the
fruits of Tim's (and others) labors. After all, this isn't about
maintaining yet another module that parses zipcodes. We're talking
about the maintenance and development of a fundamental
component of enterprise infrastructure.

Now I'll take my lithium and go watch fireworks.

Dean
Sam Tregar
2005-07-02 23:36:27 UTC
Permalink
Post by Dean Arnold
Post by Sam Tregar
Relax. I'm just suggesting that Tim not talk about a fund drive that
more-or-less failed when trying to drum up volunteers for the new
project. It will be obvious to everyone who reads this that $500
isn't enough to help in a significant way. That may serve to subtract
from the otherwise positive tone of his plea.
These forums are intended to ask for, and provide assistance to,
users and developers of the Perl DBI. One way (a very
important way) to provide that assistance is for those who
profit the most from the Perl DBI,
to provide funding and/or other resources. If it's unseemly
for Mssr. Bunce to ask for community assistance here (esp. when
so many ask for assistance from *him*), then where should he ask ?
If the issue is the perceived "shaming", I can only posit that
asking nicely obviously didn't work.
I didn't mean to imply that asking for money was a bad idea. I think
it's a fine idea. I just didn't think that was the main point of the
email. Here's the message I got from that email:

There's a new project starting that needs help. I tried to fund it
but we only got $500, and I'm not sure how to spend it. The project
will produce a new version of DBI for Perl 6. Send in your
suggestions and join the project!

I think it would be a stronger message, in a marketing sense, like
this:

There's a new project starting that needs help. The project will
produce a new version of DBI for Perl 6. Send in your suggestions
and join the project!

That's it.

-sam
Ronald J Kimball
2005-07-05 14:24:01 UTC
Permalink
Post by Sam Tregar
I didn't mean to imply that asking for money was a bad idea. I think
it's a fine idea. I just didn't think that was the main point of the
There's a new project starting that needs help. I tried to fund it
but we only got $500, and I'm not sure how to spend it. The project
will produce a new version of DBI for Perl 6. Send in your
suggestions and join the project!
https://donate.perlfoundation.org/index.pl?node=Fund+Drive+Details&selfund=1
02

Total Contributions: $1320.25

I suggest you go back and reread Tim's email. What he said was that he
donated a little over $500 himself, not that the total raised was $500.


Ronald
Ronald J Kimball
2005-07-05 14:24:01 UTC
Permalink
Post by Sam Tregar
I didn't mean to imply that asking for money was a bad idea. I think
it's a fine idea. I just didn't think that was the main point of the
There's a new project starting that needs help. I tried to fund it
but we only got $500, and I'm not sure how to spend it. The project
will produce a new version of DBI for Perl 6. Send in your
suggestions and join the project!
https://donate.perlfoundation.org/index.pl?node=Fund+Drive+Details&selfund=1
02

Total Contributions: $1320.25

I suggest you go back and reread Tim's email. What he said was that he
donated a little over $500 himself, not that the total raised was $500.


Ronald
Dean Arnold
2005-07-03 17:21:27 UTC
Permalink
Post by Dean Arnold
Post by Sam Tregar
- High-level support for cursors
Seconded.
<snip>
Post by Dean Arnold
1. A standard conformance test suite for DBDs: a very big
(and likely mundane) chore, but really needed/valuable.
2. Full thread safe support.
3. A std. i/f for multistatement result sets.
4. Std. 2PC i/f (XA) ?
5. A better set of metadata for DBDs to report the functionality
they support. E.g., today, the only way to find out if a DBD
is threadsafe (at runtime) seems to be try it and hope for the best.
6. Std. i/f support for exotic datatypes (intervals, UDTs, ARRAY, etc.)
7. Better docs for DBD developers (hopefully PDBI2E will address this ?)
8. (I know I've argued against this in the past, but I've
had a change of heart) DBI inclusion as a Perl CORE module. Database
access is too prevalent these days for it to continue as an afterthought,
and it would likely increase test coverage/exposure.
Forgot another item:

9. A standard resultset/rowset object. High level cursor support
(esp. scrollable cursors) probably requires it anyway, but
separating the resultset from the executable statement seems
to be a popular idea (see OLEDB/ADO/JDBC/etc....and, in some sense,
Class::DBI, for that matter)

Dean
Eric
2005-07-04 20:51:59 UTC
Permalink
Hi,

Is something like connection pooling considered outside the scope of
DBI? If not that would be a big one for me. Apache::DBI only goes so far.
I guess 2. would be a sensible requirement for this as well.

Thanks,

Eric
Post by Dean Arnold
Post by Dean Arnold
Post by Sam Tregar
- High-level support for cursors
Seconded.
<snip>
Post by Dean Arnold
1. A standard conformance test suite for DBDs: a very big
(and likely mundane) chore, but really needed/valuable.
2. Full thread safe support.
3. A std. i/f for multistatement result sets.
4. Std. 2PC i/f (XA) ?
5. A better set of metadata for DBDs to report the functionality
they support. E.g., today, the only way to find out if a DBD
is threadsafe (at runtime) seems to be try it and hope for the best.
6. Std. i/f support for exotic datatypes (intervals, UDTs, ARRAY, etc.)
7. Better docs for DBD developers (hopefully PDBI2E will address this ?)
8. (I know I've argued against this in the past, but I've
had a change of heart) DBI inclusion as a Perl CORE module. Database
access is too prevalent these days for it to continue as an afterthought,
and it would likely increase test coverage/exposure.
9. A standard resultset/rowset object. High level cursor support
(esp. scrollable cursors) probably requires it anyway, but
separating the resultset from the executable statement seems
to be a popular idea (see OLEDB/ADO/JDBC/etc....and, in some sense,
Class::DBI, for that matter)
Dean
David Nicol
2005-07-06 02:18:57 UTC
Permalink
Post by Dean Arnold
Post by Sam Tregar
- Asynchronous queries (coroutines? threads?)
Threads. If you've ever done much Java/JDBC work, you'll
realize how much simpler a solution to async it is.
(Ignoring the rest of Java/JDBC's undesirable traits)
A couple quarters ago I submitted a proposal to write a general
pragma delivering sugar to simply wrap any module with message-passing,
including worker process/thread pooling and testing against DBI to the
TPF, I guess the proposal has expired by now. Anyway this can be
solved in a general way that is larger than DBI, and get it solved in
DBI "for free."
Post by Dean Arnold
5. A better set of metadata for DBDs to report the functionality
they support. E.g., today, the only way to find out if a DBD
is threadsafe (at runtime) seems to be try it and hope for the best.
This is another problem that would better be solved at a higher level than
for DBI exclusively. There are other projects with plug-in architectures,
and there are meta.yaml files -- the problem turns into, who names the
functionalities and what are the standard names of the levels of support,
and back-documenting the massive base of CPAN modules that wouldn't
have function level listings in their metadata files. Support for
modules indicating
in a better fashion what versions of Perl they require would be very
closely related.

Maybe the next META.yaml comittee meeting might produce a document describing
a way for modules to identify the levels at which they support names
functionalities
and the next DBI standard could mandate a set of functionalities
that would have to be listed in conforming database drivers.

Whew!
Dean Arnold
2005-07-06 02:27:07 UTC
Permalink
Post by David Nicol
Post by Dean Arnold
Post by Sam Tregar
- Asynchronous queries (coroutines? threads?)
Threads. If you've ever done much Java/JDBC work, you'll
realize how much simpler a solution to async it is.
(Ignoring the rest of Java/JDBC's undesirable traits)
A couple quarters ago I submitted a proposal to write a general
pragma delivering sugar to simply wrap any module with message-passing,
including worker process/thread pooling and testing against DBI to the
TPF, I guess the proposal has expired by now. Anyway this can be
solved in a general way that is larger than DBI, and get it solved in
DBI "for free."
I'm already implementing such a wrapper for DBI (DBIx::Threaded);
not a pragma, and very specific to DBIv1, but hopefully it solves
at least 85-90% of the problem. (tho async cancel/abort isn't
solvable at this point)

BTW: the Pots::* modules already do what I *think* you're
proposing (again, not as pragmas, and Perl5 based)

Dean
David Nicol
2005-07-06 02:42:43 UTC
Permalink
I'm already implementing [a message-passing async] wrapper for DBI
(DBIx::Threaded); not a pragma, and very specific to DBIv1, but hopefully it solves
at least 85-90% of the problem. (tho async cancel/abort isn't
solvable at this point)
BTW: the Pots::* modules already do what I *think* you're
proposing (again, not as pragmas, and Perl5 based)
Dean
I'm going to forward you the Asynchronous pragma proposal off this list for your
consideration of the syntax. Elizabeth Mattijsen liked it, for what
that's worth.

I don't see what the advantage of using the Pots modules is over directly using
Thread::queue -- it doesn't appear to me to save any coding, although using
Pots modules would enforce some structure, which can be helpful.
--
David L Nicol
Greg Sabino Mullane
2005-07-08 01:58:24 UTC
Permalink
Post by Dean Arnold
5. A better set of metadata for DBDs to report the functionality
they support. E.g., today, the only way to find out if a DBD
is threadsafe (at runtime) seems to be try it and hope for the best.
Could get_info be expanded to include this?
Post by Dean Arnold
8. (I know I've argued against this in the past, but I've
had a change of heart) DBI inclusion as a Perl CORE module. Database
access is too prevalent these days for it to continue as an afterthought,
and it would likely increase test coverage/exposure.
On the other hand, making it a non-core module greatly increases the
chances that they will have a more reasonably recent version of DBI. :)

My own:

* Gettting rid of bind_param, or at least making it flexible so that I
can specify a placeholder type without sending a value (and vice-versa)
and bind a bunch of params in one command (e.g. a hashref of placeholder
names).

* Making the fetch series a bit more intuitive (and shorter named), so I
can avoid doing things like this:

$info = $sth->fetchall_arrayref({});

* Fixing this problem:

$dbh->connect(... {RaiseError = 0}) or die "Blah..\n";
$dbh->{RaiseError} = 1;


- --
Greg Sabino Mullane ***@turnstep.com
PGP Key: 0x14964AC8 200507072147
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
Dean Arnold
2005-07-08 03:08:33 UTC
Permalink
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Post by Dean Arnold
5. A better set of metadata for DBDs to report the functionality
they support. E.g., today, the only way to find out if a DBD
is threadsafe (at runtime) seems to be try it and hope for the best.
Could get_info be expanded to include this?
Seems the proper place for it...
Post by Dean Arnold
8. (I know I've argued against this in the past, but I've
had a change of heart) DBI inclusion as a Perl CORE module. Database
access is too prevalent these days for it to continue as an afterthought,
and it would likely increase test coverage/exposure.
On the other hand, making it a non-core module greatly increases the
chances that they will have a more reasonably recent version of DBI. :)
Or, it gives us the opportunity to coax people to upgrade
their perl versions ;^). And of course, just cuz its CORE, doesn't
preclude interim updates.
* Gettting rid of bind_param, or at least making it flexible so that I
can specify a placeholder type without sending a value (and vice-versa)
and bind a bunch of params in one command (e.g. a hashref of placeholder
names).
Does $sth->bind_param(1, undef, { TYPE => SQL_VARCHAR });
not work for your case ?

bind_param_all() or some such does sound like a nice idea...
and/or an lvalue'd $sth method ? ie, just assigning values
* Making the fetch series a bit more intuitive (and shorter named), so I
$info = $sth->fetchall_arrayref({});
Not certain thats solvable. Assuming Perl6 is still 'TMTOWTDI',
some ability to provide explicit differentiation is probably needed,
tho I spose some wantarray tests might help reduce the number of i/f's.
(albeit at the price of possible confusion).
$dbh->connect(... {RaiseError = 0}) or die "Blah..\n";
$dbh->{RaiseError} = 1;
I'd hope that Perl6 exception handling would be the ultimate
solution to such issues.

Dean Arnold
Presicient Corp.
Greg Sabino Mullane
2005-07-08 11:45:23 UTC
Permalink
(DBD metadata)
Post by Dean Arnold
Post by Greg Sabino Mullane
Could get_info be expanded to include this?
Seems the proper place for it...
Cool. Maybe we can come up with a list of metadata? This is certainly
something that we can do in DBI 1.
Post by Dean Arnold
Or, it gives us the opportunity to coax people to upgrade
their perl versions ;^). And of course, just cuz its CORE, doesn't
preclude interim updates.
True, but DBI changes must faster than Perl, and upgrading Perl will
affect a lot of other things. Maybe we should have the DBD drivers
start adding "require DBI 1.48" to the top of their modules. :)
Post by Dean Arnold
Does $sth->bind_param(1, undef, { TYPE => SQL_VARCHAR });
not work for your case ?
Sure, it works, but that undef (and the TYPE) is ugly, and it forces the
underlying code to go through an actual assignment of the placeholder, only
to subsequently throw it out when I execute with specific values. I
want to *only* set the placeholder type, and throw the values in later:

$sth = $dbh->prepare("SELECT subject FROM email WHERE rdate >= :rdate
AND whofrom = :from AND folder = :folder");
$sth->bind_params({rdate => SQL_TIMESTAMP});
## or
$sth->bind_params(SQL_TIMESTAMP, SQL_VARCHAR, SQL_INTEGER);
$sth->execute($sometime,$somebody,$somewhere);
Post by Dean Arnold
bind_param_all() or some such does sound like a nice idea...
and/or an lvalue'd $sth method ? ie, just assigning values
Yes, perhaps something like:
my %knownvals = (rdate => $sometime, folder => $somewhere);
$sth->bind_values(\%knownvals);
SOMELOOP: {
$sth->execute($somebody);
}
Post by Dean Arnold
Post by Greg Sabino Mullane
$info = $sth->fetchall_arrayref({});
Not certain thats solvable. Assuming Perl6 is still 'TMTOWTDI',
some ability to provide explicit differentiation is probably needed,
tho I spose some wantarray tests might help reduce the number of i/f's.
(albeit at the price of possible confusion).
True, but at the very least I'd like to see that particular idiom
(which I use quite a lot) better documented, and perhaps become more
of a default.

- --
Greg Sabino Mullane ***@turnstep.com
PGP Key: 0x14964AC8 200507080739
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
Ron Savage
2005-07-10 00:57:46 UTC
Permalink
On Thu, 07 Jul 2005 20:08:33 -0700, Dean Arnold wrote:

Hi Dean
Or, it gives us the opportunity to coax people to upgrade their
perl versions ;^). And of course, just cuz its CORE, doesn't
I know we agree on some things, Dean, but this is a can of wormskis, as we say
in Russia.

Where I work they pay a fortune to RH, who maintain but do /not/ upgrade their
RHE devil-spawn Perl V 5.8.0. I get syntax errors just trying to install
Contextual::Return, for example. This means I can't install Damian Conway's
Class::Std to use 'inside-out' classes.

I have permission to install and maintain my own Perl, but that creates overhead
for me. And being a contractor rather than staff confuses the issue, too.
--
Cheers
Ron Savage, ***@savage.net.au on 10/07/2005
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company
Darren Duncan
2005-07-05 01:31:03 UTC
Permalink
Tim et al,

Following are some ideas I have for the new DBI, that were thought
about greatly as I was both working on Rosetta/SQL::Routine and
writing Perl 6 under Pugs. These are all language-independent and
should be implemented at the Parrot-DBI level for all Parrot-hosted
languages to take advantage of, rather than just in the Perl 6
specific additions. I believe in them strongly enough that they are
in the core of how Rosetta et al operates (partly released, partly
pending).

0. There were a lot of good ideas in other people's replies to this
topic and I won't repeat them here, for the most part.

1. Always use distinct functions/methods to separate the declaration
and destruction of a resource handle / object from any of its
activities. With a database connection handle, both the
open/connect() and close/disconnect() are $dbh methods; the $dbh
itself is created separately, such as with a DBI.new_connection()
function. With a statement handle, the prepare() is also a $sth
method like with execute() et al; the $sth itself is created
separately, such as with a $dbh.new_statement() method. If new
handle types are created, such as a separate one for cursors, they
would likewise be declared and used separately.

With this separation, you can re-use the resource handles more
easily, and you don't have to re-supply static descriptive
configuration details each time you use it, but rather only when the
handle is declared. At the very least, such static details for a
connection handle include what DBI implementor/driver module to use;
as well, these details include what database product is being used,
and locating details for the database, whether internet address or
local service name or on-disk file name and so on. This can
optionally include the authorization identifier / user name and
password, or those details can be provided at open() time instead if
they are likely to be variable.

2. Always separate out any usage stages that can be performed apart
from the database itself. This allows an application to do those
stages more efficiently, consuming fewer resources of both itself and
the database.

For example, a pre-forked Apache process can declare all of the
database and statement handles that it plans to use, and do as much
of the prepare()-type work that can be done internally as possible,
prior to forking; all of that work can be done just once, saving CPU,
and only one instance of it consumes RAM. All actual invocations of
a database, the open()/connect() and execute() happen after forking,
and at that point all of the database-involving work is consolidated.

Or even when you have a single process, most of the work you have to
do, including any SQL generation et al, can be more easily be
pre-performed and the results cached for multiple later uses. Some
DBI wrappers may do a lot of work with SQL generation et al and be
slow, but if this work is mainly preparatory, they can still be used
in a high-speed environment as that work tends to only need doing
once. Most of the prep work of a DBI wrapper can be done effectively
prior to ever opening the database connection.

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.

Under this new scheme, when a database has native prepared statements
support that you want to leverage, the database will be invoked to
prepare said statements the first time you run execute(), and then
the result of this is cached by DBI or the driver for all subsequent
execute() to use. In that case, any input errors detected by the
database will be thrown at execute() time regardless of their nature;
only input errors detected by the DBD module itself would be thrown
at prepare() time. (Note that module-caught input errors are much
more likely when the module itself is handling SQL in AST form,
whereas database-caught input errors are much more likely when SQL is
always maintained in the program as string form.) Note also that the
deferal to execute() time of error detection is what tends to happen
already with any databases that don't have native prepared statement
support or for whom the DBI driver doesn't use them; these won't be
affected by the official definition change.

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.

4. 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, making programmers a lot
less error prone, more powerful, and particularly more resource
efficient when the same parameter is conceptually used multiple times
in a SQL statement (it only has to be bound once). If anyone wants
to use positional format, it could easily be emulated on top of this.
Or, if native positional support is still important, then it should
be a parallel option that can be used at the same time as named in
any particular SQL statement. See the native API of SQLite 3 for one
example that (I believe) supports both in parallel. This also means
that execute() et al should take arguments in a hash rather than an
array.

5. All details used to construct a connection handle should be
completely decomposed rather than shoved into an ungainly "data
source". Examples of what should be distinct (not all being
applicable at once) are: 1. the DBI driver module to use; 2. the
internet server IP address or domain name and port; 3. the locally
defined server device socket; 4. the locally defined service (eg,
ODBC or SQL*Net) name; 5. the file system file name; 6. the file
system directory name; 7. some other detail if any for fully in-RAM
databases; 8. the authorization identifier / user name; 9. the
password; 10. some other authorization credential, or channel
encryption details, or whatever else; 11. what kind of database or
what database product is being used, if known. If the DBI driver
talks to a client-configurable DBI proxy server, then, it should be
possible to nest a set of the above settings (eg, as a hash-ref) as
one part of the main settings given to the proxy client.

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.

7. Error conditions should *always* be thrown as exceptions by DBI;
no exception thrown means that the request succeeded, even if its
result was nothing/undef. This is a lot simpler to implement or use
than any alternative. If people don't like that, then some wrapper
should be employed to block the exceptions. Or, if it is really
important to have a non-exception alternative, then that should be an
alternative, with thrown exceptions being the default behaviour.

8. Split off the proxy server/client stuff into a separate
distribution; they are conceptually add-ons anyway and could benefit
from independent development. Split off any SQL parser utilities
(eg, SQL::Nano, SQL::Statement) into a separate distribution, since
only a small fraction of potential drivers would use them, and they
are better off to just require them separately. Split off all
bundled DBI drivers (DBD::File, etc) into separate distributions,
unless they exist soley to provide an example of how to make a DBI
driver and are not actually useful in themselves. The DBI
distribution should focus simply on defining an interface, and let
anything that will help with implementing the drivers to be optional
and separate.

9. As Sam Vilain suggested, prepare() type methods should accept both
SQL strings and any type of object as input, so that drivers have the
option to directly accept AST forms; particularly useful when the
drivers themselves would otherwise have to parse the SQL into an AST
anyway.

And now ...

Here's an example of some things that implementing some of the above
suggestions will let an application do (code may not compile as is):

method init($self) {
$self.db = DBI.new_connection( driver => 'DBD::SQLite', host => 'test' );

my $sth1 = $self.db.new_statement(
"select * from baz where abc = :bar or def = :bar" );
$sth1.prepare();
my $sth2 = $self.db.new_statement(
"insert into baz (abc, def) values (:p_abc, :p_def)" );
$sth2.prepare();

$self.routines = (
'get_all_baz' => -> ($bar) {
$sth1.execute( { bar => $bar } );
return $sth1.fetch_all_hashref();
},
'add_one_baz' => -> ($abc, $def) {
$sth2.execute( { p_abc => $abc, p_def => $def } );
},
);
}

method main($self) {
try {
$self.db.open( user => 'jane', pass => 'k34l5jr' );

try {
$self.routines.{'add_one_baz'}.('hello','world');

my $results = $self.routines.{'get_all_baz'}.('world');

my $sth3 = $self.db.new_statement(
"delete from baz where def = :foo" );
$sth3.prepare();
$sth3.execute( { foo => 'blarch' } );
};
$! and say "dag nabit!";

$self.db.close();
};
$! and say "dog gone!";
}

In the above example, only main() actually invokes a database; init()
does load the DBI driver, though. You can also invoke main() as many
times as you want, and you can run init() prior to forking without
trouble.

What I've said in this email is not exhaustive and I may add or amend
items later; but, its a good start. Feedback is welcome of course.

Thank you. -- Darren Duncan
Sam Vilain
2005-07-05 02:49:15 UTC
Permalink
3. Redefine prepare() and execute() such that the first is expressly for
activities that can be done apart from a database (and hence can also be
done for a connection handle that is closed at the time) while all
activities that require database interaction are deferred to the second.
That would be nice, but there are some DBDs for which you need the database
on hand for $dbh.prepare() to work. In particular, DBD::Oracle.

I think that what you are asking for can still work, though;

# this module creates lots of SQL::Statement derived objects, without
# necessarily loading DBI.
use MyApp::Queries <%queries>;

# not connect, so doesn't connect
my $db = DBI.new( :source("myapp") );

# prepare the objects as far as possible
my %sths;
for %queries.kv -> $query_id, $query_ast_or_template {
%sths{$query_id} = $db.prepare($query_ast_or_template);
}

# connect
$db.connect;

# now proceed as normal
my $sth = %sths<some_query_id>;
$sth.execute( :param("foo"), :this("that") );

So, effectively the prepare can happen at any time, and it's up to the
DBD to decide whether to actually do anything with it immediately or not.
ie, on Pg the STHs would be built before the DB is connected, and on Oracle
they are built the first time they are used (and then cached).
Now I realize that it may be critically important for an application to
know at prepare() time about statically-determinable errors, such as
mal-formed SQL syntax, where error detection is handled just by the
database. For their benefit, the prepare()+execute() duality could be
broken up into more methods, either all used in sequence or some
alternately to each other, so users get their errors when they want
them. But regardless of the solution, it should permit for all
database-independent preparation to be separated out.
OK, so we have these stages;

1. (optional) generate an AST from SQL
2. (optional) generate SQL string from an AST
3. generate a handle for the statement, sans connection
4. prepare handle for execution, with connection
5. execute statement

I think these all fit into;

1. SQL::Statement.new(:sql("..."));
2. $statement.as_sql;
3. $dbh.prepare($statement) or $dbh.prepare($statement, :nodb);
4. $dbh.prepare($statement) or $sth.prepare while connected
5. $sth.execute

In particular, I don't think that the DB driver should automatically
get a chance to interfere with SQL::Statement; if they want to do that,
then they should specialise SQL::Statement. IMHO.

Perhaps you have some other examples that don't fit this?
5. All details used to construct a connection handle should be
completely decomposed rather than shoved into an ungainly "data
source".
I interpret this as asking that the detailed parameters to the DBI
connection are expanded into named options rather than simply bundled into
a string.

That, I agree with, and I guess it would be useful occasionally to be
able to specify all that rather than just setting it up once and labelling
those connection parameters with a "source" that comes from ~/.dbi.
Particularly for writing gui dialogs for interactive database utilities.

Either way, you don't want most applications dealing with this complexity
at all, really.
6. DBI drivers should always be specified by users with their actual
package name, such as 'DBD::SQLite', and not some alternate or
abbreviated version that either leaves the 'DBD::' out or is spelled
differently. Similarly, the DBI driver loader should simply try to load
exactly the driver name it is given, without munging of any type. This
approach is a lot more simple, flexible and lacks the cludges of the
current DBI. DBI driver implementers can also name their module
anything they want, and don't have to name it 'DBD::*'. A DBI driver
should not have to conform to anything except a specific API by which it
is called, which includes its behaviour upon initialization, invocation,
and destruction.
Is this useful?

I can't see a reason that the DBI.new() / DBI.connect() call shouldn't be
flexible in what it accepts;

$dbh = DBI.new( :driver<Rosetta> ); # means DBD::Rosetta
$dbh = DBI.new( :driver<Rosetta::Emulate::DBD> ); # specify full package
$dbh = DBI.new( :driver(Rosetta::Emulate::DBD) ); # pass type object
$dbh = DBI.new( :driver(DBD::SQLite.new(:foo<bar>)) ); # pass driver object

Sam.
Darren Duncan
2005-07-05 05:01:39 UTC
Permalink
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:

# 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

# Opt 2: If they want less control, they do this (same as old DBI):

my $sth2 = $dbh.prepare( $sql_or_ast ); # combines Opt 1's comp/prep
$sth2.execute(); # same as Opt 1

# Opt 3: Alternately, there is this (akin to my older suggestion):

my $sth3 = $dbh.compile( $sql_or_ast ); # same as Opt 1
$sth3.execute(); # combines Opt 1's prep/exec

# Opt 4: Even less control (akin to old DBI's "do"):

$dbh.execute( $sql_or_ast ); # combines Opt 1's comp/prep/exec

In this model, when you use just prepare() and execute(), they behave
identically to the old DBI, including that they require an open
connection. So no mystery there.

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.

Invoking just compile() then execute() will cause the execute() to do
what prepare() normally does against a database, and cache the
prepared handle.

In option 4, I renamed the old DBI's do() to execute() for
consistency with the other examples; but this execute() is different
in that it caches the prepared statement handle. In any event, with
all 4 examples, execute() gives you the same result regardless of
what is called before it.
Post by Sam Vilain
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"?
Post by Sam Vilain
Post by Darren Duncan
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.
I see the act of storing all the data as a single string at any time
to be a messy affair to be avoided. The application doesn't have to
know about the complexity to pass around a hash of values any more
than it does with a string; but when the application wants to know
the details, dealing with a hash is easier.
Post by Sam Vilain
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.
Post by Sam Vilain
Post by Darren Duncan
6. DBI drivers should always be specified by users with their
actual package name, such as 'DBD::SQLite', and not some alternate
or abbreviated version that either leaves the 'DBD::' out or is
spelled differently. Similarly, the DBI driver loader should
simply try to load exactly the driver name it is given, without
munging of any type. This approach is a lot more simple, flexible
and lacks the cludges of the current DBI. DBI driver implementers
can also name their module anything they want, and don't have to
name it 'DBD::*'. A DBI driver should not have to conform to
anything except a specific API by which it is called, which
includes its behaviour upon initialization, invocation, and
destruction.
Is this useful?
I can't see a reason that the DBI.new() / DBI.connect() call shouldn't be
flexible in what it accepts;
$dbh = DBI.new( :driver<Rosetta> ); # means DBD::Rosetta
$dbh = DBI.new( :driver<Rosetta::Emulate::DBD> ); # specify full package
$dbh = DBI.new( :driver(Rosetta::Emulate::DBD) ); # pass type object
$dbh = DBI.new( :driver(DBD::SQLite.new(:foo<bar>)) ); # pass driver object
My main point here is that DBI should not have to know any details
about particular drivers that are written to it, except in a generic
sense that may apply to any driver. The driver should know about DBI
details, but the reverse should never be true.

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', },
ado_ => { class => 'DBD::ADO', },
amzn_ => { class => 'DBD::Amazon', },
best_ => { class => 'DBD::BestWins', },
csv_ => { class => 'DBD::CSV', },
db2_ => { class => 'DBD::DB2', },
dbi_ => { class => 'DBI', },
dbm_ => { class => 'DBD::DBM', },
df_ => { class => 'DBD::DF', },
f_ => { class => 'DBD::File', },
file_ => { class => 'DBD::TextFile', },
ib_ => { class => 'DBD::InterBase', },
ing_ => { class => 'DBD::Ingres', },
ix_ => { class => 'DBD::Informix', },
jdbc_ => { class => 'DBD::JDBC', },
msql_ => { class => 'DBD::mSQL', },
mysql_ => { class => 'DBD::mysql', },
mx_ => { class => 'DBD::Multiplex', },
nullp_ => { class => 'DBD::NullP', },
odbc_ => { class => 'DBD::ODBC', },
ora_ => { class => 'DBD::Oracle', },
pg_ => { class => 'DBD::Pg', },
proxy_ => { class => 'DBD::Proxy', },
rdb_ => { class => 'DBD::RDB', },
sapdb_ => { class => 'DBD::SAP_DB', },
solid_ => { class => 'DBD::Solid', },
sponge_ => { class => 'DBD::Sponge', },
sql_ => { class => 'SQL::Statement', },
syb_ => { class => 'DBD::Sybase', },
tdat_ => { class => 'DBD::Teradata', },
tmpl_ => { class => 'DBD::Template', },
tmplss_ => { class => 'DBD::TemplateSS', },
tuber_ => { class => 'DBD::Tuber', },
uni_ => { class => 'DBD::Unify', },
xbase_ => { class => 'DBD::XBase', },
xl_ => { class => 'DBD::Excel', },
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.
By having users specify the full driver class name, DBI won't have to
do any such explicit mapping.

By the way, most driver names are quite short already, so its not
like abbreviations are necessary.

-- Darren Duncan
Sam Vilain
2005-07-05 06:14:41 UTC
Permalink
Post by Darren Duncan
Okay, considering that using the same name prepare() like this may
confuse some people, here is a refined solution that uses 3 methods
I think I'm beginning to like it.

Allow me to suggest one or two further refinements...
Post by Darren Duncan
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<SQLite> );

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.
Post by Darren Duncan
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.
Post by Darren Duncan
Post by Sam Vilain
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.
Post by Darren Duncan
Post by Sam Vilain
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.
Post by Darren Duncan
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.
my $dbd_prefix_registry = {
ad_ => { class => 'DBD::AnyData', },
[...]
Post by Darren Duncan
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.
Darren Duncan
2005-07-05 07:19:28 UTC
Permalink
Post by Sam Vilain
I think I'm beginning to like it.
Allow me to suggest one or two further refinements...
Post by Darren Duncan
my $sth1 = $dbh.compile( $sql_or_ast ); # always sans connection
$sth1.prepare(); # always with connection, even if DBD doesn't use it
$sth1.execute(); # always with connection
FYI, I'm not stuck on the name 'compile'; the method could be called
something else. In fact, I still think 'prepare' is more broadly
descriptive considering the wide range of things that could be
happening inside that method; I see it as ultimately getting ready
for the execute() anyway. The problem is that the word 'prepare' is
commonly associated with something different, so we're stuck having
to find a different word.
Post by Sam Vilain
To me, the "compiled" form of the STH is related to the driver, but
re-usable between connections; you should be able to use something like;
my $sth1 = DBD::SQLite.compile( $sql_or_ast );
$sth1 = DBI.compile( :statement($sql_or_ast), :driver<SQLite> );
This would give you a STH which is divorced from the actual DB connection
instance.
Yes, that is true. However, I like for all application calls to go
through the Interface module (DBI) since that is what the Interface
is for. It is also more practical to go by way of the DBI module
because it can do things for you like certain kinds of validation on
application input and driver output, so the application or driver
respectively doesn't have to do those tests for robustness. Each DBI
driver can worry less about that its input is correct and focus more
on its actual work. (FYI, a common input/output validation is one of
the main things that the 'Rosetta' module does for its own drivers
and users. More so in next release v0.46.)
Post by Sam Vilain
Because you constructed it like this, without reference to a
(possibly unconnected) connection object, then $sth1.prepare is not
available.
I wouldn't worry about that; the proposed DBI would elegantly handle
(throw an exception) attempts to invoke methods that depend on an
open connection when there is none. People who like to check first
will also have a $dbh.is_open() method available to them. I also see
the situation as no worse than the current DBI v1 where you can
invoke such things as fetchrow_arrayref() on a $sth that hasn't been
executed yet.

Oh, and I have another DBI v2 suggestion to add, also following Rosetta design:

1. A $sth should not contain any methods for fetching the result of
an executed statement; rather, execute() should return an object when
successful that represents its result; you invoke fetching methods on
that object. For example:

my $rlh = $sth->execute();
my $rowset = $rlh->fetchrow_arrayref();

This approach is a lot more flexible.
Post by Sam Vilain
Well, when you parse the statement into an AST, the flavour of SQL will
affect how it is parsed and what is allowed. Eg, Oracle has significant
features in some comments (query hints). It also has quirky and somewhat
useless keywords like CONNECT BY.
So, when you ask a DBH connected to a driver to parse something, then it
will use that driver's SQL dialect, if one exists, but I still want to be
able to deal with SQL ASTs without implying a SQL flavour.
You still can. The point of an "abstract syntax tree" is that the
original SQL dialect isn't very important to what it contains (and
this is how SQL::Routine is); by contrast, a "concrete syntax tree"
cares very much about the original syntax and preserves it to the
last detail. I don't see a problem here.
...
Except that those AST-like-modules which embed raw SQL fragments
(like where-clauses) as a matter of course aren't actually that
abstract and could pose a problem; lots of DBI wrappers are this way,
unfortunately (but not mine).
Post by Sam Vilain
Post by Darren Duncan
I am operating under the assumption that this system should work if
there are no external config files that the DBI/DBD would read, and
the application would provide that information; if its in a file,
the application would read it in, or would explicitly tell DBI
where it is. Or at least it should be possible for this to happen,
even if a DBD defaults to look in a default location when it
doesn't get the equivalent from the application.
Absolutely, that must work. But it would still be nice to be able to
config this without digging through the application to see where the
password is written.
Er, I hope you didn't interpret this as the application source code.
My point was that the database connection details can be part of the
general application configuration file, which is still data and not
code. This way, each application can have distinct database settings
if it wants to.
Post by Sam Vilain
Such a great word, abhorrent. So fitting for this case. It sure does
look like an (over&premature&misguided)-optimisation to avoid using the
full module name in an internal hash or something like that. But then
maybe (I&we&none(Gaia)) are missing some context there.
Well, I have also decided that I can accept one specific type of
flexability on the 'whole module name' thing. That is, if we assume
that DBI drivers will commonly have 'DBD::' at the start of their
names, users can optionally leave that prefix off, but they still
have to spell the rest of the name out in full and with the correct
letter case. In this case, when trying to load a driver, DBI would
first try the pristine module name it was given, and if a module
fails to load by that name, then it will try adding 'DBD::' to the
front as an alternative; failing those two, it will croak. But the
point is, aside from the text 'DBD::', which is reasonable as it is a
general case, there are no hard coded driver names in DBI.

Another suggestion:

2. Unless there is a strong impetus for there being a separate root
namespace for DBI drivers, I suggest a better naming scheme would be
to put everything under DBI:: instead. For example,
DBI::Driver::SQLite or DBI::D::SQLite, or DBI::DBD::SQLite. If this
idea is adopted, then that auto-prefix thing I previously mentioned
would be changed accordingly. I think that having the standard
driver namespace being outside DBI::* is an antiquated notion that
bears little resemblence to what is standard in most frameworks. All
this said, I'm not stuck on a change here, so do as you will.

-- Darren Duncan
Greg Sabino Mullane
2005-07-08 01:36:24 UTC
Permalink
No time to respond fully, but a few cents here and there, with some
devil's advocate thrown in.
Post by Darren Duncan
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.
Post by Darren Duncan
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.
Post by Darren Duncan
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.
Post by Darren Duncan
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. :)
Post by Darren Duncan
A $sth should not contain any methods for fetching the result of
an executed statement;
...
Post by Darren Duncan
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 ***@turnstep.com
PGP Key: 0x14964AC8 200507072132
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

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... :)
Darren Duncan
2005-07-08 09:36:03 UTC
Permalink
Post by Greg Sabino Mullane
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.
Post by Greg Sabino Mullane
Post by Sam Vilain
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.
Post by Greg Sabino Mullane
Post by Sam Vilain
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.
Post by Greg Sabino Mullane
Post by Sam Vilain
A $sth should not contain any methods for fetching the result of
an executed statement;
...
Post by Sam Vilain
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
Greg Sabino Mullane
2005-07-10 13:24:11 UTC
Permalink
Post by Darren Duncan
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).
Post by Darren Duncan
I see bind parameters to be exactly the same as function arguments,
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();
Post by Darren Duncan
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. :)
Post by Darren Duncan
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.
Post by Darren Duncan
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.
Post by Darren Duncan
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).
Post by Darren Duncan
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 ***@turnstep.com
PGP Key: 0x14964AC8 200507100855
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
Darren Duncan
2005-07-10 23:37:33 UTC
Permalink
Post by Greg Sabino Mullane
Sure, I don't have a problem with the :foo form, I was just saying
$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.
Post by Greg Sabino Mullane
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.
Post by Greg Sabino Mullane
I just want to make sure that we keep
DBI as an abstraction layer. Additional hooks are fine: additional
tasks are not.
I agree.
Post by Greg Sabino Mullane
Post by Darren Duncan
Post by Darren Duncan
A $sth should not contain any methods for fetching the result of
an executed statement;
...
Post by Darren Duncan
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.
Post by Greg Sabino Mullane
Post by Darren Duncan
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
Adam Kennedy
2005-07-05 02:26:50 UTC
Permalink
Post by Darren Duncan
4. 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, making programmers a lot
less error prone, more powerful, and particularly more resource
efficient when the same parameter is conceptually used multiple times in
a SQL statement (it only has to be bound once). If anyone wants to use
positional format, it could easily be emulated on top of this. Or, if
native positional support is still important, then it should be a
parallel option that can be used at the same time as named in any
particular SQL statement. See the native API of SQLite 3 for one
example that (I believe) supports both in parallel. This also means
that execute() et al should take arguments in a hash rather than an array.
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.

and we'd just end up with every second person implementing a ? to :p1,
:p2 scheme in their database layers. If you want positional
placeholders, it needs to be an additional feature, not a replacement
for positional placeholders.
Sam Vilain
2005-07-05 22:47:38 UTC
Permalink
Post by Adam Kennedy
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 <<YAML);
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 };
Adam Kennedy
2005-07-06 00:51:49 UTC
Permalink
Post by Sam Vilain
Post by Adam Kennedy
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;
(SNIP)

Well, that was a simplistic example from which I had intended the
implication to be that it could be ANY two arbitrary pieces of sql. It
could just have easily been...

$foo = [
"ifelse(null(column), 0, DB2LOB.length(column)) > ? OR column5 is in
( ?, ?, ?, ? )",
100000, "alpha", "beta", "gamma", "delta"
];

$bar = [
"str2date(?, ?) between now() and str2date(?, ?)",
"20050401", "YYYYMMDD", "20040123", "YYYYMMDD"
];

$baz = [ "DB2XML.xpath_match(column3, ?)", "//foo" ];

my $where = join( 'and', $foo, $bar, $baz );

my $sql = DB->table->sql_select . 'where' . $where;

Who's to know what could be in the SQL fragments. We've all seen some of
the crazy and non-standard stuff various databases have.

(note: The SQL expressions were made up, but are of similar complexity
to known existing syntaxes)

Adam K
Peter J. Holzer
2005-07-19 10:53:47 UTC
Permalink
Post by Darren Duncan
5. All details used to construct a connection handle should be
completely decomposed rather than shoved into an ungainly "data
source". Examples of what should be distinct (not all being applicable
[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
--
_ | Peter J. Holzer \Beta means "we're down to fixing misspelled comments in
|_|_) | Sysadmin WSR \the source, and you might run into a memory leak if
| | | ***@wsr.ac.at \you enable embedded haskell as a loadable module and
__/ | http://www.hjp.at/ \write your plugins upside-down in lisp". --***@op5.se
Sam Tregar
2005-07-05 18:01:36 UTC
Permalink
Post by Ronald J Kimball
Total Contributions: $1320.25
I suggest you go back and reread Tim's email. What he said was that he
donated a little over $500 himself, not that the total raised was $500.
That's good to know. It doesn't really alter my assessment though -
the funding drive was a failure and won't significantly affect the
future of the DBI v2 project.

-sam
Jonathan Leffler
2005-07-09 07:35:30 UTC
Permalink
Late to the ball - and only picking up on one issue...
Post by Darren Duncan
2. Always separate out any usage stages that can be performed apart
from the database itself. This allows an application to do those
stages more efficiently, consuming fewer resources of both itself and
the database.
For example, a pre-forked Apache process can declare all of the
database and statement handles that it plans to use, and do as much
of the prepare()-type work that can be done internally as possible,
prior to forking; all of that work can be done just once, saving CPU,
and only one instance of it consumes RAM. All actual invocations of
a database, the open()/connect() and execute() happen after forking,
and at that point all of the database-involving work is consolidated.
Or even when you have a single process, most of the work you have to
do, including any SQL generation et al, can be more easily be
pre-performed and the results cached for multiple later uses. Some
DBI wrappers may do a lot of work with SQL generation et al and be
slow, but if this work is mainly preparatory, they can still be used
in a high-speed environment as that work tends to only need doing
once. Most of the prep work of a DBI wrapper can be done effectively
prior to ever opening the database connection.
I dunno which DBMS support prepare without a database connection, but I
would expect all the mainstream databases to require a database connection.
IBM DB2 does; IBM Informix Dynamic Server (IDS) does; someone else commented
on this and said Oracle does; I would expect both Sybase and MS SQL Server
to need the DB connection too. Probably PostgreSQL; not sure about MySQL.
The only systems that might not need the DB connection are those that aren't
using a separate SQL-based database server.

I'm also far from convinced that there's any significant benefit in
separating the 'create a database handle' from the 'connect to database
server' part. The overhead of creating the handle - as distinct from make
the connection to the DBMS - is negligible. There's nothing of any
significance that can be done with the unconnected handle either - at least,
for the mainstream SQL DBMS. So, the pre-fork creation of an unconnected
handle provides negligible savings. Similar comments apply to the statement
handles - in IDS, at any rate, there's nothing useful that can be done for
statements until you've got a database handle. Or, at the least, you'd be
moving away from the ODBC paradigm, and the ESQL/C paradgm too.

As a general comment on DBI v2; we need to beware of the second-system
effect (Brooks "Mythical Man Month").

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.
--
Jonathan Leffler <***@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2005.01 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."
Darren Duncan
2005-07-09 09:39:34 UTC
Permalink
Post by Jonathan Leffler
I dunno which DBMS support prepare without a database connection,
but I would expect all the mainstream databases to require a
database connection. IBM DB2 does; IBM Informix Dynamic Server
(IDS) does; someone else commented on this and said Oracle does; I
would expect both Sybase and MS SQL Server to need the DB connection
too. Probably PostgreSQL; not sure about MySQL. The only systems
that might not need the DB connection are those that aren't using a
separate SQL-based database server.
See my subsequent email/s where I left prepare() requiring an open
database connection, and a separate function (for example,
'compile'), is split out and handles any prep work that can be done
without an open connection.

Since you mention that some systems don't have the separate SQL-based
database server, there are indeed some drivers that can take
advantage of a separate 'compile' then, so they benefit from a split;
the worst that happens with the others is a no-op. Some drivers
gain, and no one loses anything.
Post by Jonathan Leffler
I'm also far from convinced that there's any significant benefit in
separating the 'create a database handle' from the 'connect to
database server' part. The overhead of creating the handle - as
distinct from make the connection to the DBMS - is negligible.
There's nothing of any significance that can be done with the
unconnected handle either - at least, for the mainstream SQL DBMS.
So, the pre-fork creation of an unconnected handle provides
negligible savings. Similar comments apply to the statement handles
- in IDS, at any rate, there's nothing useful that can be done for
statements until you've got a database handle. Or, at the least,
you'd be moving away from the ODBC paradigm, and the ESQL/C paradgm
too.
A main benefit of separating out the handle creation and database
connection is the secondary effect whereby you can pre-associate
statement handles with it that are expensive to create, such as
because they generate SQL. Generating SQL and other such things is
separate from the database and the speed advantage from separating
this out as I described is not lost by the fact of what database
servers don't support, since the databases are never given that work.

A primary effect benefit is any time you want to open the same
connection more than once; you can configure it once when making the
handle, and not again no matter how many times you open/close/open
the connection, or re-open a connection that dies. Moreover, every
open from the first to subsequent ones are done in the same way.

Certainly, one can make a DBI wrapper that adds some of those
advantages, but given their simplicity and universal desirability,
its better to build them in; any addition of complexity over the
current DBI is negligible, or it may in fact be less complex than the
current DBI.
Post by Jonathan Leffler
As a general comment on DBI v2; we need to beware of the
second-system effect (Brooks "Mythical Man Month").
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.
What you say is fair enough, but I never proposed anything impossible
or difficult; everything that I am proposing here is easy and simple.

-- Darren Duncan
Jochen Wiedmann
2005-07-09 10:42:38 UTC
Permalink
Post by Jonathan Leffler
I dunno which DBMS support prepare without a database connection, but I
would expect all the mainstream databases to require a database connection.
+1
Post by Jonathan Leffler
I'm also far from convinced that there's any significant benefit in
separating the 'create a database handle' from the 'connect to database
server' part.
+1


Not to mention the effect, that one major charm of DBI is its
simplicity: Connect, Execute for updates, inserts, or deletes and
Connect, Execute, Fetch for select. I can't see an advantage in overly
extending the interface.


Jochen
Michael Peppler
2005-07-11 17:40:04 UTC
Permalink
Post by Jochen Wiedmann
Post by Jonathan Leffler
I dunno which DBMS support prepare without a database connection, but I
would expect all the mainstream databases to require a database connection.
+1
Post by Jonathan Leffler
I'm also far from convinced that there's any significant benefit in
separating the 'create a database handle' from the 'connect to database
server' part.
+1
Not to mention the effect, that one major charm of DBI is its
simplicity: Connect, Execute for updates, inserts, or deletes and
Connect, Execute, Fetch for select. I can't see an advantage in overly
extending the interface.
Personally I tend to agree with you. I haven't read the whole thread,
but I'm not yet convinced that the DBI needs to change that much.
Certainly the Sybase driver won't be able to support many of the
proposed functionality, or won't benefit from the changes (i.e. no speed
gain, no improved flexibility, etc).

Michael
--
Michael Peppler - ***@peppler.org - http://www.peppler.org/
Sybase DBA/Developer
Sybase on Linux FAQ: http://www.peppler.org/FAQ/linux.html
Kiran Kumar
2005-07-19 09:19:57 UTC
Permalink
We could have an option to do Bulk Inserts ..
Peter J. Holzer
2005-07-19 10:58:59 UTC
Permalink
Post by Kiran Kumar
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
--
_ | Peter J. Holzer \Beta means "we're down to fixing misspelled comments in
|_|_) | Sysadmin WSR \the source, and you might run into a memory leak if
| | | ***@wsr.ac.at \you enable embedded haskell as a loadable module and
__/ | http://www.hjp.at/ \write your plugins upside-down in lisp". --***@op5.se
Adam Kennedy
2005-07-09 12:25:32 UTC
Permalink
Post by Jonathan Leffler
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...

This sort of behaviour can play a big part in ending up with second
system problems.

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.

People are free to comment on things that 1) They won't have to
implement themselves and (in some cases, but not you Duncan) 2) They
think they know what they are talking about, but really have no idea
what it means underneath the surface.

In any case, I still propose that DBI2 split the driver interface into
Roles. The main "DBI2::Role::Transport" role does ONLY what DBI does
best now. That is, connecting to the database, preparing and sending
queries, and fetching the results.

Forget the current ->tables interface. Drivers can OPTIONALLY implement
the DBI2::Role::Schema interface to handle interrogation of the database
schema. The current way we handle it is really messy by my standards,
and could use a dedicated interface.

As you invent major new features for DBI2, implement them roles.
DBI2::Role::Transational, DBI2::Role::RFC90210 (the super flashy Beverly
Hills feature), etc etc.

Exactly what these roles should be I don't want to state with any
certainty. That's the sort of thing that Tim, with his complete
understanding of the issues, should be doing on his own.

But I _would_ certainly like to see schema/table stuff separated from
the base connection/query functionality.

While I'm on the topic of DBI, one other feature I'd like to see would
be something like better support for large objects or various types.

In my personal DBI wrapper I've been using plain SCALAR refs as
parameters to signify a BLOB, so I can pass by reference without a copy
of a potentially large memory chunk, and then having to do
driver-specific translation to bind variables (Oracle) or what have you.

Some base way of default method defining a BLOB object (whether provided
in memory, or pointing at a file handle to pull the data from at commit
time) in would be really nice. Even if the way I have to pass the blobs
to each driver differs, I'd like to be at least be able to say,

This is a DBI2::Data::BLOB object (or something functionally equivalent).

Adam K
Darren Duncan
2005-07-11 03:53:46 UTC
Permalink
Post by Adam Kennedy
In any case, I still propose that DBI2 split the driver interface
into Roles. The main "DBI2::Role::Transport" role does ONLY what DBI
does best now. That is, connecting to the database, preparing and
sending queries, and fetching the results.
Forget the current ->tables interface. Drivers can OPTIONALLY
implement the DBI2::Role::Schema interface to handle interrogation
of the database schema. The current way we handle it is really messy
by my standards, and could use a dedicated interface.
As you invent major new features for DBI2, implement them roles.
DBI2::Role::Transational, DBI2::Role::RFC90210 (the super flashy
Beverly Hills feature), etc etc.
Exactly what these roles should be I don't want to state with any
certainty. That's the sort of thing that Tim, with his complete
understanding of the issues, should be doing on his own.
That sounds like a great idea and a good starting place on which to
build certain design issues.

In fact, I have already been implementing something similar to that
in my 'Rosetta' database access library for over a year now. It has
a concept of "feature support lists" where each Rosetta Engine/driver
must take a standard fine-ish-grained checklist and programmatically
declare which list items it officially supports.

An application can see what an Engine/driver claims to support before
trying to use it, and will know whether or not the Engine/driver
can meet its needs or not. An Engine's/driver's declaration can be
read by invoking the features() method of some Rosetta Interface
objects.

An additional use for this feature is that a comprehensive common
test suite for all Engines/drivers can consult features() prior to
running its tests so that it can "skip" any tests that an
Engine/driver doesn't claim to support; it will only invoke and
pass/fail features that the Engine/driver claims to support.

See the Rosetta::Details documentation section 'FEATURE SUPPORT
VALIDATION' for the main summary of "feature support lists".
Post by Adam Kennedy
But I _would_ certainly like to see schema/table stuff separated
from the base connection/query functionality.
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; let wrappers do that stuff through the SQL pipe that
DBI provides. This said, it is still very useful for DBI to provide
a function for fetching a list of auto-detectable data sources, so
that should stay.

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.
Post by Adam Kennedy
While I'm on the topic of DBI, one other feature I'd like to see
would be something like better support for large objects or various
types.
I second that. Have API methods for fetching or storing by-the-chunk
pieces of LOBs that one could not do with a traditional bind_param()
etc since the whole thing won't fit in RAM at once.

-- Darren Duncan
Greg Sabino Mullane
2005-07-12 00:12:40 UTC
Permalink
Post by Darren Duncan
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.
Post by Darren Duncan
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 ***@turnstep.com
PGP Key: 0x14964AC8 200507112009
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
Darren Duncan
2005-07-11 06:19:10 UTC
Permalink
I have an additional reply to the following ...
Post by Adam Kennedy
In any case, I still propose that DBI2 split the driver interface
into Roles. The main "DBI2::Role::Transport" role does ONLY what DBI
does best now. That is, connecting to the database, preparing and
sending queries, and fetching the results.
Forget the current ->tables interface. Drivers can OPTIONALLY
implement the DBI2::Role::Schema interface to handle interrogation
of the database schema. The current way we handle it is really messy
by my standards, and could use a dedicated interface.
As you invent major new features for DBI2, implement them roles.
DBI2::Role::Transational, DBI2::Role::RFC90210 (the super flashy
Beverly Hills feature), etc etc.
Exactly what these roles should be I don't want to state with any
certainty. That's the sort of thing that Tim, with his complete
understanding of the issues, should be doing on his own.
One of the reasons I like this idea is that it lets DBI define
multiple Roles that may be mutually exclusive and accomplish the same
task in different ways.

For example, the idea of whether the interface will accept SQL AST
objects in addition to or instead of SQL strings. The standard
DBI2::Role::Transport will take SQL strings and only SQL strings, as
the current DBI does. There can be an additional Role for each AST.

In that situation, there are several case scenarios depending on the
type of database being used and what the driver maker is willing to
support.

1. The first case is the common database server that always takes its
input as string SQL. In this case, the standard string-taking Role
will be the most native fit for it, and any AST-supporting Roles
would have to be, in some fashion, implemented as wrappers on top of
this, converting their contents to string SQL for the database.

It is assumed that the AST in question would come with its own set of
SQL generators, either in the same distribution or a separate
distribution that is an extension to it. If the DBI driver writer
chooses to implement the relevant AST-taking Role, it would
presumably use the AST's own SQL generator to implement that support,
so the driver writer hardly has any work to do of their own. Or, if
the DBI driver writer does not choose to implement the Role, then
users of said AST would use it as or with a DBI wrapper that invokes
the SQL generating functionality itself and passes the result to
DBI2::Role::Transport.

2. The second case is a usually-embedded database server or library
that does not take string SQL as its native input. In this case,
some sort of AST-supporting Role would be the most native fit for it,
since it is probably easier for that DBI driver to extract the
information it needs from the AST than to parse string SQL; the
DBI2::Role::Transport Role would have to be, in some fashion,
implemented as a wrapper on top of this, which parses string SQL into
an AST. Most likely, an externally destributed SQL parser would be
the best choice, so it can be improved independently of the driver,
as with any SQL generators. (SQL::Statement is an example of this
already being done.)

Now, if support for a string-SQL interface is mandatory for all DBI
drivers, then it is possible that all AST-taking Roles can be
implemented on top of the string-taking Role. As is the case with
DBI v1. But, for the sake of those drivers that want ASTs
internally, having a way for them to get that input natively, through
officially sanctioned appropriate Roles, that lets those drivers save
themselves a lot of work and the system is potentially much faster
and easier to improve, without sacrificing compatability.

A separate advantage of this Roles thing is backwards compatability
due to a multi-versioned API. If we wish to implement a significant
API or implementation change in DBI after DBI 2 is launched, we can
be free to do so without breaking older programs because they will by
definition be writing against a specific API version. We just have
to maintain a Role for each distinct version that maps what the
application expects to how things work in the background.

I liken this to a new feature in Mac OS X starting with 10.4 Tiger;
versioned KPIs (kernal programming interfaces); from now on, it is a
lot easier for Apple to release significantly changed kernals in
newer Mac OS X versions without breaking any applications or drivers,
because those specify a versioned API which continues to exist as a
wrapper. See http://arstechnica.com/reviews/os/macosx-10.4.ars/4 for
what I'm talking about.

However it's done, I'm thinking something akin to that would be good for DBI.

-- Darren Duncan
Tim Bunce
2005-08-16 15:04:00 UTC
Permalink
Post by Adam Kennedy
Post by Jonathan Leffler
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.
Post by Adam Kennedy
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.
Dean Arnold
2005-08-16 19:12:02 UTC
Permalink
Post by Tim Bunce
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).
Post by Tim Bunce
Still, I'm sure things will liven up once I've put an initial sketch
together...
Tim.
Dean Arnold
Presicient Corp.
Tim Bunce
2005-08-17 09:26:32 UTC
Permalink
Post by Dean Arnold
Post by Tim Bunce
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.
Post by Dean Arnold
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.
Post by Dean Arnold
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.
Darren Duncan
2005-08-16 20:16:19 UTC
Permalink
Post by Tim Bunce
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
Tim Bunce
2005-08-17 09:59:33 UTC
Permalink
Post by Darren Duncan
Post by Tim Bunce
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.
Yeap. I'm awaiting delivery of that one, plus several others including
MJDs Higher Order Perl.
Post by Darren Duncan
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
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 agree entirely - except for the word "basic" in item 6 :)

One of the key things missing from DBI 1 was a test suite that could be
reused to test/validate different drivers.

Note that what you've described is essentially just what JDBC is.
Only JDBC has a comprehensive driver test/validate suite.

At the moment I'm thinking in terms of a Parrot-level DBDI modeled on
JDBC, with a thin Perl6-specific DBI layered on top. Other languages
targeting Parrot would have their own thin language adaption layers.
Post by Darren Duncan
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.
I'd like to see someone do a stateless proxy sometime (as I've
outlined previously) and I'll be ensuring there's a serializable RowSet
object available - but, yes, such things should be separate.

Tim.
Alexander Foken
2005-08-17 05:57:08 UTC
Permalink
Post by Tim Bunce
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. 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. 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;


Second: $h->func(@func_arguments, $func_name) is ugly. The $func_name
argument should really be the first argument, not the last.
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-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?


Alexander

Please inform us immediately if this e-mail and/or any attachment was
transmitted incompletely or was not intelligible.
___________________________________________________________________________

This e-mail and any attachment is for authorized use by the intended
recipient(s) only. It may contain proprietary material, confidential
information and/or be subject to legal privilege. It should not be copied,
disclosed to, retained or used by any other party.
If you are not an intended recipient then please promptly delete this
e-mail and any attachment and all copies and inform the sender.
Tim Bunce
2005-08-17 09:06:10 UTC
Permalink
Post by Tim Bunce
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 :)
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
package DBD::MyBadlyHackedODBC;
use base 'DBD::ODBC'; # <--- ordinary Perl inheritance without any DBI "magic"
sub someMethodInODBC
{
my $self=shift;
}
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.
use DBI::base 'DBD::ODBC' (instead of use base 'DBD::ODBC') would create
the four classes for my hacked DBD, all inheriting from their
package DBD::MyBadlyHackedODBC;
use DBI::base 'DBD::ODBC';
package DBD::MyBadlyHackedODBC::db;
sub prepare
{
$dbh->SUPER::prepare(reverse $statement,$attribs)
}
1;
Patches welcome!

It certainly can be done: DBD::CSV, for example, is a subclass of DBD::File.
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.
Tim Bunce
2005-08-16 13:18:45 UTC
Permalink
Post by Jonathan Leffler
As a general comment on DBI v2; we need to beware of the second-system
effect (Brooks "Mythical Man Month").
Well understood.
Post by Jonathan Leffler
In particular, the DBI must not mandate impossible levels of support from
the drivers.
Also well understood.
Post by Jonathan Leffler
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.
Jonathan Leffler
2005-07-09 08:03:11 UTC
Permalink
Still late to the party - another one bullet point item...
Post by Darren Duncan
4. 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, making programmers a lot
less error prone, more powerful, and particularly more resource
efficient when the same parameter is conceptually used multiple times
in a SQL statement (it only has to be bound once). If anyone wants
to use positional format, it could easily be emulated on top of this.
Or, if native positional support is still important, then it should
be a parallel option that can be used at the same time as named in
any particular SQL statement. See the native API of SQLite 3 for one
example that (I believe) supports both in parallel. This also means
that execute() et al should take arguments in a hash rather than an
array.
Can you explain which parts of the SQL:2003 mandate this notation? I've had
a moderately good poke around my copy of ISO/IEC 9075-2:2003
(SQL/Foundation) and cannot find this. I'd like a few section numbers listed
which describe this.

The various places I've looked include: 19.6 (prepare statement), 9
(Additional Common Rules), 6.4 (<value specification> and <target
specification>). I could have missed something in these places - or I could
be looking in the wrong place.

The IDS (IBM Informix Dynamic SQL) syntax has a number of places where
:<digits> can appear with a meaning other than placeholder, and there are
also a number of places where :<identifier> can appear with a meaning other
than placeholder. So, it would be extremely difficult to add :<identifier>
notation into IDS. (One of the bits I had to remove from DBD::Informix was
code from DBD::Oracle that simulated :<identifier> notation - because it
breaks too much else.)

This ties in with my previous comment, too; don't try to demand too much of
the drivers, or the driver writers. As long as you've got a good surrogate
system in DBI that can simulate those accurately for DBMS that only support
'?' (positional) placeholders, then DBI v2 can do what the heck it likes.
But as soon as it is inaccurate - translates things that should not be
translated - or cannot do the translation automatically, then you will lose
drivers (or, more accurately, driver writers).

I'm all in favour of looking at the big picture and trying to see where you
want to go. However, you must also keep an eye out for the marshes between
where you are and where you want to go; don't let DBI be sunk by ignoring
the realities of the available DBMS.
--
Jonathan Leffler <***@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2005.01 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."
Darren Duncan
2005-07-09 10:01:14 UTC
Permalink
Post by Jonathan Leffler
Can you explain which parts of the SQL:2003 mandate this notation?
I've had a moderately good poke around my copy of ISO/IEC
9075-2:2003 (SQL/Foundation) and cannot find this. I'd like a few
section numbers listed which describe this.
The various places I've looked include: 19.6 (prepare statement), 9
(Additional Common Rules), 6.4 (<value specification> and <target
specification>). I could have missed something in these places - or
I could be looking in the wrong place.
Yes, I can quote some, in different places. Look at these:

SQL:2003, 4.29 "Host parameters" (pp90,91,92)
SQL:2003, 5.4 "Names and identifiers" (pp151,152)
SQL:2003 Foundation page 152 says:
<host parameter name> ::= <colon><identifier>

See that last line in particular. I don't see how it could be more clear.

This is my main source for the SQL:2003 documentation:

http://www.wiscorp.com/SQLStandards.html

Or specifically:

http://www.wiscorp.com/sql/sql_2003_standard.zip (warning, large file)

That page is run by someone leading / high up in the SQL standards group.

Note that those urls are printed in my SQL::Routine::Language POD
file on CPAN, and the lines with specific pages and section names are
in source/reference comments/documentation for the
build_identifier_host_parameter_name() function
in my SQL::Routine::SQLBuilder module. I do tend to give sources to
back up anything important I do, which is good for third party
validation.
Post by Jonathan Leffler
The IDS (IBM Informix Dynamic SQL) syntax has a number of places
where :<digits> can appear with a meaning other than placeholder,
and there are also a number of places where :<identifier> can appear
with a meaning other than placeholder. So, it would be extremely
difficult to add :<identifier> notation into IDS. (One of the bits
I had to remove from DBD::Informix was code from DBD::Oracle that
simulated :<identifier> notation - because it breaks too much else.)
A variety of databases, such as Oracle, already have support for just
the format I described, which I believe was also in SQL:1999. In
fact, I think that Oracle's own extensions of earlier SQL standards
had a lot of influence on later SQL standards such as this, though I
can think of multiple differences too.
Post by Jonathan Leffler
This ties in with my previous comment, too; don't try to demand too
much of the drivers, or the driver writers. As long as you've got a
good surrogate system in DBI that can simulate those accurately for
DBMS that only support '?' (positional) placeholders, then DBI v2
can do what the heck it likes. But as soon as it is inaccurate -
translates things that should not be translated - or cannot do the
translation automatically, then you will lose drivers (or, more
accurately, driver writers).
I don't see how :foo is significantly more difficult than ? to
simulate accurately.
Post by Jonathan Leffler
I'm all in favour of looking at the big picture and trying to see
where you want to go. However, you must also keep an eye out for
the marshes between where you are and where you want to go; don't
let DBI be sunk by ignoring the realities of the available DBMS.
At the same time, don't let today's technology limit what you do in
preparation for tomorrow. DBI v2 is meant to be a forward-looking
plan, as Perl 6 is, so we have to consider things that are reasonable
for inclusion; even if it isn't common for native database support
now, that isn't to say that native support won't come later, and when
it does, we'll already be ready, or they'll even take what DBI does
as a cue for what they can add.

-- Darren Duncan
Jochen Wiedmann
2005-07-09 10:45:21 UTC
Permalink
Post by Jonathan Leffler
Can you explain which parts of the SQL:2003 mandate this notation? I've had
a moderately good poke around my copy of ISO/IEC 9075-2:2003
(SQL/Foundation) and cannot find this. I'd like a few section numbers listed
which describe this.
I second that one too. Besides, I am not convinced that a marriage of
DBI and SQL is a good idea at all. Note, that there are quite some
drivers which aren't using SQL. And, besides, most drivers have an
internal SQL processing engine too. What's the gain in processing SQL
twice or more?


Jochen
Jonathan Leffler
2005-07-11 04:27:41 UTC
Permalink
Post by Darren Duncan
Post by Jonathan Leffler
Can you explain which parts of the SQL:2003 mandate this notation?
I've had a moderately good poke around my copy of ISO/IEC
9075-2:2003 (SQL/Foundation) and cannot find this. I'd like a few
section numbers listed which describe this.
The various places I've looked include: 19.6 (prepare statement), 9
(Additional Common Rules), 6.4 (<value specification> and <target
specification>). I could have missed something in these places - or
I could be looking in the wrong place.
SQL:2003, 4.29 "Host parameters" (pp90,91,92)
SQL:2003, 5.4 "Names and identifiers" (pp151,152)
<host parameter name> ::= <colon><identifier>
See that last line in particular. I don't see how it could be more clear.
Oh - hmmm...Embedded SQL...Yes, even Informix supports that notation in
Embedded SQL/C (ESQL/C); I'd forgotten about it because it is not part of
what goes to the (Informix) server.

EXEC SQL EXECUTE :prepared_stmt USING :hostvar1:indvar1, :hostvar2:indvar2,
...;

This is not what DBI deals with - it deals more nearly with the CLI syntax,
where that is not, as far as I know, permitted. However, I'm not yet sure
about all the contexts in which a <host parameter name> can appear - it is
the sort thing that is quite hard to determine from the standard,
unfortunately. Even with my heavily hyperlinked grammar - generated from the
BNF in the standard - it is fairly hard to tell.

Consider my objection temporarily on ice - I'm not wholly convinced, but
clearly the onus is on me to show why it should not be mandated by DBI v2.
Post by Darren Duncan
http://www.wiscorp.com/SQLStandards.html
Thanks. I have copies of the official standard - but I didn't have the
presentations, etc.

If anyone wants to use my hyperlinked HTML version of the standard SQL
syntax, let me know and I'll make it available for download on a website. I
updated it last week, so the version I have at home isn't current (though
most people wouldn't notice the change - it was very minor and solely
related to formatting).
--
Jonathan Leffler <***@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2005.01 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."
Darren Duncan
2005-07-11 05:06:00 UTC
Permalink
Post by Jonathan Leffler
This is not what DBI deals with - it deals more nearly with the CLI
syntax, where that is not, as far as I know, permitted.
My impression of DBI is that it is a stand-in of sorts for a SQL CLI,
and does or should do all of the same sorts of things. Each time you
run a SQL statement, it is like typing said statement into a CLI.
(In fact, I seem to recall that DBI ships with a simple SQL CLI
program that runs on top of it, and that has an almost 1:1 mapping.)
DBI abstracts the CLI a bit by providing things like connect()
methods rather than having users execute 'CONNECT TO ...' SQL, but
that doesn't really change what I've said. So DBI is basically the
same as CLI but that it is easier for programmers to use by replacing
an input terminal with functions. In that context, the :foo syntax
corresponds to what bind_var() etc maps to.
Post by Jonathan Leffler
If anyone wants to use my hyperlinked HTML version of the standard
SQL syntax, let me know and I'll make it available for download on a
website. I updated it last week, so the version I have at home
isn't current (though most people wouldn't notice the change - it
was very minor and solely related to formatting).
Please do that. So far I have had the drudgery of manually scrolling
through a 1000+ page PDF document to look up things. Your version
should be much faster. This said, does it include what page in the
original the info came from, so I can correlate them in reference
documentation?

-- Darren Duncan
Tim Bunce
2005-08-16 15:25:33 UTC
Permalink
Post by Darren Duncan
Post by Jonathan Leffler
This is not what DBI deals with - it deals more nearly with the CLI
syntax, where that is not, as far as I know, permitted.
My impression of DBI is that it is a stand-in of sorts for a SQL CLI,
and does or should do all of the same sorts of things. Each time you
run a SQL statement, it is like typing said statement into a CLI.
(In fact, I seem to recall that DBI ships with a simple SQL CLI
program that runs on top of it, and that has an almost 1:1 mapping.)
DBI abstracts the CLI a bit by providing things like connect()
methods rather than having users execute 'CONNECT TO ...' SQL, but
that doesn't really change what I've said. So DBI is basically the
same as CLI but that it is easier for programmers to use by replacing
an input terminal with functions. In that context, the :foo syntax
corresponds to what bind_var() etc maps to.
Seems like you've not got the right meaning of CLI here.
It's Call Level Interface not Command Line Interface.

The relevant (old) standards are:

- The X/Open CAE Specification "Data Management: SQL Call-Level Interface (CLI)"
- ISO/IEC 9075-3:1995 (E) Call-Level Interface (SQL/CLI)

There are later versions of ISO/IEC SQL/CLI for 1999 and 2003.

Tim.
Jonathan Leffler
2005-07-09 08:22:30 UTC
Permalink
Oh drat - not the DBI connection string discussion again!
Post by Darren Duncan
5. All details used to construct a connection handle should be
completely decomposed rather than shoved into an ungainly "data
source". Examples of what should be distinct (not all being
applicable at once) are: 1. the DBI driver module to use; 2. the
internet server IP address or domain name and port; 3. the locally
defined server device socket; 4. the locally defined service (eg,
ODBC or SQL*Net) name; 5. the file system file name; 6. the file
system directory name; 7. some other detail if any for fully in-RAM
databases; 8. the authorization identifier / user name; 9. the
password; 10. some other authorization credential, or channel
encryption details, or whatever else; 11. what kind of database or
what database product is being used, if known. If the DBI driver
talks to a client-configurable DBI proxy server, then, it should be
possible to nest a set of the above settings (eg, as a hash-ref) as
one part of the main settings given to the proxy client.
Only 1, 8 and 9 (driver name, user ID, password) apply to Informix databases
- and you've missed out the other bit that applies - the database name, and
optionally database server name hosting it (either 'dbase' or '***@server').
These are not the same as 2, 3, 4, 5, 6, 7, 10, or 11, and I can't think how
any of them (except perhaps 10) could be applied to an IDS connection.

Oh, and if you're going to enhance the connection, please ensure you cover
challenge-response protocols (where you send an initial username/password
and the authentication server comes back with a question such as "what is
the number on your RSA key fob at the moment", and the hapless user has to
type that information in, and the connection management code has to deal
with this - callbacks and the like.

So, as was discussed emphatically and exhaustively (in January 2005 in
dbi-dev under 'Proposing an API for the extension for simplifying database
connections'), this is not readily going to fly with the existing DBMS -
specifically, not with IDS. Closed-source DBMS are *not* necessarily going
to adapt to meet the needs of Perl and DBI. You can argue that's their loss
- you may even be right. But you'll be limiting the acceptability of Perl +
DBI in some respects. You'll also be annoying the hell out of me if you
can't define a connection string that will work with Informix (I'm not too
worried about the challenge-response stuff, though Informix can handle
that).

Oh - and DBI v2 should have support for scrollable cursors.
--
Jonathan Leffler <***@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2005.01 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."
Darren Duncan
2005-07-09 10:10:41 UTC
Permalink
On 7/4/05, Darren Duncan
5. All details used to construct a connection handle should be
completely decomposed rather than shoved into an ungainly "data
source". Examples of what should be distinct (not all being
applicable at once) are: 1. the DBI driver module to use; 2. the
internet server IP address or domain name and port; 3. the locally
defined server device socket; 4. the locally defined service (eg,
ODBC or SQL*Net) name; 5. the file system file name; 6. the file
system directory name; 7. some other detail if any for fully in-RAM
databases; 8. the authorization identifier / user name; 9. the
password; 10. some other authorization credential, or channel
encryption details, or whatever else; 11. what kind of database or
what database product is being used, if known. If the DBI driver
talks to a client-configurable DBI proxy server, then, it should be
possible to nest a set of the above settings (eg, as a hash-ref) as
one part of the main settings given to the proxy client.
Only 1, 8 and 9 (driver name, user ID, password) apply to Informix databases -
Re-read that paragraph. It says 'not all being applicable at once'.
and you've missed out the other bit that applies - the database
name, and optionally database server name hosting it (either 'dbase'
or 11, and I can't think how any of them (except perhaps 10) could
be applied to an IDS connection.
I think that 2 thru 4 cover this, though I used more generic language.
Oh, and if you're going to enhance the connection, please ensure you
cover challenge-response protocols (where you send an initial
username/password and the authentication server comes back with a
question such as "what is the number on your RSA key fob at the
moment", and the hapless user has to type that information in, and
the connection management code has to deal with this - callbacks and
the like.
I mentioned those generically in 8 thru 10; the latter is "some other
authorization credential etc".
So, as was discussed emphatically and exhaustively (in January 2005
in dbi-dev under 'Proposing an API for the extension for simplifying
database connections'), this is not readily going to fly with the
existing DBMS - specifically, not with IDS. Closed-source DBMS are
*not* necessarily going to adapt to meet the needs of Perl and DBI.
You can argue that's their loss - you may even be right. But you'll
be limiting the acceptability of Perl + DBI in some respects.
You'll also be annoying the hell out of me if you can't define a
connection string that will work with Informix (I'm not too worried
about the challenge-response stuff, though Informix can handle that).
If a database only accepts input in string form, then the DBI driver
can generate one out of the decomposed information it is given. No
trouble here, really; no trouble at all. The whole point of a DBI
driver is to map between the DBI interface and how the database
product natively does things. It is true that some things can only
be done by changing the database product itself, but other things can
be done in the DBI driver, with no support from closed-source DBMS
necessary.

-- Darren Duncan
Jochen Wiedmann
2005-07-09 10:46:07 UTC
Permalink
Post by Jonathan Leffler
Oh drat - not the DBI connection string discussion again!
;-)
Jeffrey W. Baker
2005-07-09 18:27:37 UTC
Permalink
Post by Jonathan Leffler
Oh drat - not the DBI connection string discussion again!
Post by Darren Duncan
5. All details used to construct a connection handle should be
completely decomposed rather than shoved into an ungainly "data
source". Examples of what should be distinct (not all being
applicable at once) are: 1. the DBI driver module to use; 2. the
internet server IP address or domain name and port; 3. the locally
defined server device socket; 4. the locally defined service (eg,
ODBC or SQL*Net) name; 5. the file system file name; 6. the file
system directory name; 7. some other detail if any for fully in-RAM
databases; 8. the authorization identifier / user name; 9. the
password; 10. some other authorization credential, or channel
encryption details, or whatever else; 11. what kind of database or
what database product is being used, if known. If the DBI driver
talks to a client-configurable DBI proxy server, then, it should be
possible to nest a set of the above settings (eg, as a hash-ref) as
one part of the main settings given to the proxy client.
Only 1, 8 and 9 (driver name, user ID, password) apply to Informix databases
- and you've missed out the other bit that applies - the database name, and
These are not the same as 2, 3, 4, 5, 6, 7, 10, or 11, and I can't think how
any of them (except perhaps 10) could be applied to an IDS connection.
There are certainly database-specific things to be worked around. An
improvement to the current DSN scheme would be a URI, as discussed in
the past. The leading dbi: on every DSN is redundant, so a URI might
Post by Jonathan Leffler
Oh, and if you're going to enhance the connection, please ensure you cover
challenge-response protocols (where you send an initial username/password
and the authentication server comes back with a question such as "what is
the number on your RSA key fob at the moment", and the hapless user has to
type that information in, and the connection management code has to deal
with this - callbacks and the like.
Seconded, with request for support of SSL client certificate
authentication.

-jwb
Juerd
2005-07-10 16:43:39 UTC
Permalink
Post by Jeffrey W. Baker
Post by Jonathan Leffler
Oh drat - not the DBI connection string discussion again!
There are certainly database-specific things to be worked around. An
improvement to the current DSN scheme would be a URI, as discussed in
the past. The leading dbi: on every DSN is redundant, so a URI might
I think URIs are the right way to go, and one of the very few things PHP
(though be it with PEAR) did right.

http://pear.php.net/manual/en/package.database.db.intro-dsn.php

It would be fun if we could just steal that design and build on top of
it, for compatibility, but also because other people have already
thought about it and proven that it works.


Juerd
--
http://convolution.nl/maak_juerd_blij.html
http://convolution.nl/make_juerd_happy.html
http://convolution.nl/gajigu_juerd_n.html
Tim Bunce
2005-08-16 15:48:21 UTC
Permalink
Post by Juerd
Post by Jeffrey W. Baker
Post by Jonathan Leffler
Oh drat - not the DBI connection string discussion again!
Relax. DSN strings are not going away.
Post by Juerd
Post by Jeffrey W. Baker
There are certainly database-specific things to be worked around. An
improvement to the current DSN scheme would be a URI, as discussed in
the past. The leading dbi: on every DSN is redundant, so a URI might
I think URIs are the right way to go, and one of the very few things PHP
(though be it with PEAR) did right.
http://pear.php.net/manual/en/package.database.db.intro-dsn.php
And they possibly copied the idea from JDBC.

Note that the DBIs DSNs are already URIs in as much as the format is

<scheme>:<scheme-specific-part>

(with the exception of no formal encoding/escaping rules, but they
wouldn't make much difference in practice.)
Post by Juerd
It would be fun if we could just steal that design and build on top of
it, for compatibility, but also because other people have already
thought about it and proven that it works.
It's not exactly complicated.

DSNs for DBI v2 are very likely to be more formally specified as URIs.

Tim.
Jared Still
2005-07-11 01:29:04 UTC
Permalink
I haven't been following this too closely, so my apologies
if already mentioned.

This connect string is very much like the new Easy Connect
Naming method in Oracle 10g.

eg. sqlplus scott/***@server:port/service

Note that it is not 'instance', but 'service'.

Jared
Jonathan Leffler
2005-07-09 23:55:12 UTC
Permalink
Post by Darren Duncan
5. All details used to construct a connection handle should be
completely decomposed rather than shoved into an ungainly "data
source". Examples of what should be distinct (not all being
applicable at once) are: 1. the DBI driver module to use; 2. the
internet server IP address or domain name and port; 3. the locally
defined server device socket; 4. the locally defined service (eg,
ODBC or SQL*Net) name; 5. the file system file name; 6. the file
system directory name; 7. some other detail if any for fully in-RAM
databases; 8. the authorization identifier / user name; 9. the
password; 10. some other authorization credential, or channel
encryption details, or whatever else; 11. what kind of database or
what database product is being used, if known. If the DBI driver
talks to a client-configurable DBI proxy server, then, it should be
possible to nest a set of the above settings (eg, as a hash-ref) as
one part of the main settings given to the proxy client.
Only 1, 8 and 9 (driver name, user ID, password) apply to Informix
databases -
Re-read that paragraph. It says 'not all being applicable at once'.
Yes - I realized that.
and you've missed out the other bit that applies - the database
Post by Darren Duncan
name, and optionally database server name hosting it (either 'dbase'
or 11, and I can't think how any of them (except perhaps 10) could
be applied to an IDS connection.
I think that 2 thru 4 cover this, though I used more generic language
I don't. The server name in IDS is not a host name or domain name, nor is it
a port number (so 2 is not applicable), nor is it the locally named socket
(so 3 is not applicable), nor is it the locally defined service (so 4 is not
applicable).

All of these are identified by, but are distinct from, the IDS server name.
There is a mechanism (configuration file) that hides all the gory details
from the user. Users should not need to know sordid details like port
numbers, or whether the host is on an IPv4 or IPv6 network, etc.

Further, within an IDS instance, there are multiple databases that can be
separately connected to - '***@server1', '***@server1', '
***@server2', '***@server2'. You must be able to specify the database
within the server instance.

Now, in fact, server1 and server2 could be alternative names for the same
hunk of disk space, supervised by the same IDS instance but with different
connection properties - such as encrypted vs unencrypted - but that is
probably just too confusing. Equally, and more normally, server1 could be on
a wholly different machine from server2.

[Concrete example: I have many IDS instances running on my machine at work.
One instance has 4 names:
anubis_17, anubis_17_tcp, anubis_17_str, anubis_17_shm. The first two are
both network connections - albeit usually using loopback since I usually
work on the same machine. The third uses a STREAMS pipe; the fourth uses
shared memory. All allow me to connect to the same set of databases, which
includes 'sysmaster', 'sysutils', 'stores', 'logged', 'unlogged',
'mode_ansi'. Each of those databases has its own independent set of system
catalogs. I can connect to '***@anubis_17' or '***@anubis_17_shm' and
edit the same data - it's the same database, identified by different server
names and different connection properties. I also have another IDS instance,
running an older version of IDS, with server names anubis_23, anubis_23_tcp,
anubis_23_str, anubis_23_shm - and the same set of databases, but only
because some of them are standard and the others I keep there to make
testing DBD::Informix easier. I also have an entry configured for a database
server called 'smartpts' that is based in Lenexa, KS - I work in Menlo Park,
CA - about 1800 miles away. I can run $db1 = DBI->connect('
dbi:Informix:***@anubis_18'); $db2 = DBI->connect('
dbi:Informix:***@smartpts'); to connect to those databases. I can also, in
general, do distributed queries between the two databases without explicitly
connecting to the other. And note that I can switch between TCP and SHM
connections on the local machine (which is
anubis.menlo.ibm.com<http://anubis.menlo.ibm.com>)
simply by changing the server name - I don't have to do any other
modifications to the connection string.]

Please note that for Informix, the database name (optionally with server
name) is all you need to specify. (There's an environment variable that
specifies the default server name if you omit it.) Specifying a 'host name'
won't work; there is no mechanism for connecting by specifying a host name -
except as part of the configuration file. Specifying a port number (or
service name) won't work; there is no mechanism for connecting by specifying
a port number or service name - except as a part of the configuration file.

I'm sorry that life is more complex that you wish to recognize - but that is
how life is.
Oh, and if you're going to enhance the connection, please ensure you
Post by Darren Duncan
cover challenge-response protocols (where you send an initial
username/password and the authentication server comes back with a
question such as "what is the number on your RSA key fob at the
moment", and the hapless user has to type that information in, and
the connection management code has to deal with this - callbacks and
the like.
I mentioned those generically in 8 thru 10; the latter is "some other
authorization credential etc".
No - you don't seem to understand. The challenge-response protocol can ask
someone for the RSA key fob number this time, their mother's maiden name the
next time, their employee number the time after that, and nothing on the
fourth occasion. You cannot predict what the extra information requested is
going to be - so you can't provide the extra information in the initial
connection attempt because you don't know which extra information is going
to be needed. That's what provides the security - the unpredictability of
the question, so that it is hard to pre-programme the answer.

Don't get hung up on this - challenge-response authentication protocols are
not in everyday use in very many places, but they are likely to become more
prevalent in future. In particular, the RADIUS protocol (RFC2865 plus sundry
supporting RFCs) supports challenge-response. And the PAM (Pluggable
Authentication Modules) support it - look them up sometime.
So, as was discussed emphatically and exhaustively (in January 2005
Post by Darren Duncan
in dbi-dev under 'Proposing an API for the extension for simplifying
database connections'), this is not readily going to fly with the
existing DBMS - specifically, not with IDS. Closed-source DBMS are
*not* necessarily going to adapt to meet the needs of Perl and DBI.
You can argue that's their loss - you may even be right. But you'll
be limiting the acceptability of Perl + DBI in some respects.
You'll also be annoying the hell out of me if you can't define a
connection string that will work with Informix (I'm not too worried
about the challenge-response stuff, though Informix can handle that).
If a database only accepts input in string form, then the DBI driver
can generate one out of the decomposed information it is given.
Only if the decomposed form contains the information that is needed in a
form that can be used.

OK - bullet 10 says:

10. some other authorization credential, or channel encryption details, or
whatever else;

I ruled out 10a (some other authorization credential) as a way of dealing
with challenge-response protocols; it does not prevent it from being usable
in some circumstances. 10b is of some value - though IDS hides that
information from the average user (the same configuration file that defines
what the server means and how to connect to it includes the information on
whether encryption is to be used; another configuration file typically
defines the detailed encryption options). DBD::Informix won't be able to
accommodate arbitrary choices on encryption; both the client and the server
have to be set up compatibly, both expecting to use encryption.

That leaves, I suppose, 10c 'or whatever else'. If that is intended to be a
wholly open-ended set of options, then we can, if necessary, cover the
critical part of the IDS connection information in 'whatever else' - but it
feels kind of odd that the database name is 'whatever else'. The IDS server
- that I can live with DBI not handling; it can simply be regarded as a
funny part of the IDS database naming convention. So, as long as the rules
allow '***@server' as a valid name for a database, DBD::Informix can hide
the server from the rest of DBI. But you must allow users to specify just a
database name and the driver name 'dbi:Informix:dbase' or '
dbi:Informix:***@server', or a segregated equivalent: {Driver=>'Informix',
Database=>'dbase'} or {Driver=>'Informix', Database=>'***@server'}.

I note in passing that database names and server names can be up to 128
characters each; don't impose arbitrary limits there, either, though that
isn't part of the Perl philosophy so I don't expect it to be an issue.

Now, if some idiot tries to connect to IDS with connection information such
as {Driver=>'Informix',
Host=>'yucatan.example.com<http://yucatan.example.com>',
Port=>1526}, DBD::Informix will have to reject the connection attempt -
insufficient information. If {Database=>'***@server'} is part of the
connection string, DBD::Informix can ignore the extraneous matter, or reject
the connection attempt because the extraneous matter is irrelevant and
probably incorrect. Now, at one level, I neither know nor care whether a
database name as found in IDS is of any relevance elsewhere - it is of
relevance to the DBMS that I deal with. As such, DBI should be able to
accommodate it. If DBI won't accommodate it, it won't be because the
designers were not made aware of the issue. However, I believe other DBMS
can support multiple things called databases within the scope of a single
DBMS instance.

Your proposal does not contain one, arguably two, critical pieces of
information -- the Informix-style database server name and the
Informix-style database name. A single Informix host machine can be running
multiple instances of IDS. I'd like to be able to manufacture the necessary
background information for you - but I can't. The server name and the
database name are both needed, and are not covered explicitly by the items
on your list. It doesn't matter how much you try to bend it - the
information you are planning on providing does NOT include all the
information necessary to connect to an IDS database except under the
catch-all clause in item 10, which is not a satisfactory place for it to be
handled.


No
trouble here, really; no trouble at all. The whole point of a DBI
driver is to map between the DBI interface and how the database
product natively does things. It is true that some things can only
be done by changing the database product itself, but other things can
be done in the DBI driver, with no support from closed-source DBMS
necessary.
Please - listen to me. I'm an expert on IDS. I know whereof I speak. I don't
have a mandate to make changes to IDS because of possible future
requirements from Perl and DBI. I regard the details you are trying to
expose as retrograde steps - however, if there was a need from our customers
for the retrograde setup, I'd support adding it. But I'm not getting any
pressure on that - there are many other much more important problems and
features to add.

The problem that I'm trying to get across to the people involved in the
design of DBI v2 is the same as the problem that I had to explain to back in
January. The model you wish to impose on every DBMS in the world does not
fit every DBMS in the world - there are real, practical, in-use
counter-examples which demonstrate that the way you (collectively - not you
specifically, Duncan) would like to be able to do it will not work for some
DBMS. I'm sorry that (a small portion of) the real world is raining on your
parade - but something has to give, and the bit that is still unfinished is
DBI v2 - IDS has been working the way it currently does for upwards of a
decade, and DBI v1 accommodates Informix quite adequately; I don't see any
good reason for DBI v2 to break it. IDS goes to lengths to conceal from the
people using it (as opposed to the administrators setting it up) all the
ghastly details you are revelling in exposing. Further, it not only conceals
those details, it uses some extra information that you've not yet accounted
for. No; you probably don't need to worry about the extra information - but
it is there and is hidden by the configuration file.

Please, please, please provide a mechanism that will work for Informix as
well as the other DBMS.

OK; I'll answer questions about what would or would not work for Informix,
and help design the mechanism. I'm not going to expound any more on the
basic problem - but I will vote against any proposal that cannot be managed
by Informix. And you can't say you were not warned.
--
Jonathan Leffler <***@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2005.01 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."
Darren Duncan
2005-07-10 00:32:53 UTC
Permalink
Jonathan, while you are well-meaning in your comments, you are
mis-reading what I have said multiple times and are therefore making
a straw man argument against it.
Post by Darren Duncan
5. All details used to construct a connection handle should be
completely decomposed rather than shoved into an ungainly "data
source". Examples of what should be distinct (not all being
applicable at once) are: <snip>
That's the key part of my proposal right there. I don't care about
exactly what break-down of pieces we collectively come up with, as
long as they are unambiguous, reasonably atomic, account for all the
possible needs, and have reasonable names. That 11-point list of
mine was only meant to be an example of what breaking up a "data
source" string can give us; it was not meant to be exhaustive and
there is no point in arguing at length against what it missed and how
horrible it is. No doubt, an IDS and/or components thereof should
also be in the list of connection parameters. I'm not arguing
against what one needs for Informix, either explicitly or implicitly.

All this said, thank you for all the information about Informix how
you set up your systems, and how many levels of hierarchy there can
be with a database; eg, a server has 1+ databases, each of which has
1+ catalogs (and presumably each of those has 1+ schemas and each of
those 1+ tables and other schema objects). I will probably find it
helpful when I plan various DBI-using tasks.

-- Darren Duncan
Adam Kennedy
2005-07-11 13:29:21 UTC
Permalink
Post by Jonathan Leffler
No - you don't seem to understand. The challenge-response protocol can ask
someone for the RSA key fob number this time, their mother's maiden name the
next time, their employee number the time after that, and nothing on the
fourth occasion. You cannot predict what the extra information requested is
going to be - so you can't provide the extra information in the initial
connection attempt because you don't know which extra information is going
to be needed. That's what provides the security - the unpredictability of
the question, so that it is hard to pre-programme the answer.
Ah but you can know in advance! :) You may not know the actual result
per instance, but you CAN know the decision process you'll need to go
through. Which you can provide as a parameter in the form of a CODE
reference. :) i.e. a callback

But that's a minor point and overall I completely agree with your
general ideas.

Adam K
Dean Arnold
2005-07-12 01:30:25 UTC
Permalink
RE: Placeholders: since DBIv1 already supports both forms of
PH's, I see no reason to deprecate or abandon either form.
Furthermore, to my knowledge, none of (ODBC, JDBC, ADO.NET)
has abandonded or deprecated the ? form, so I don't see
the need for DBI to.

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
robust enough to support LOBs, they'll almost always provide
sufficient metadata info and/or SQL syntax to manipulate them;
only databases which don't support LOBs have that difficulty.
Furthermore, a quick review of the current DBI will indicate that
Mssr. Bunce has already implemented some stub methods for
generalized support.

RE: SQL Parse Trees (or other non-SQL query input)

Since none of (ODBC, JDBC, ADO.NET) seems compelled to
impose this concept on driver writers, I don't see why
DBI should be the vanguard.

However, implementing a subclass of DBI to support it
seems technically feasible, so I'd suggest that
those of you championing this requirement implement one
on DBI v1. Feel free to use DBIx::Chart to bootstrap
your project. As the proponents of this notion
are so generous with their requirements for those of us
who develop DBI drivers and/or contribute
development efforts to the DBI itself, I'm sure they won't
object if I provide a few requirements:

1. For DBI drivers which support them, your subclass
must support
- arbitrary numbers and levels of JOINs (including
various outer, and non-equijoins)
- arbitrarily nested subqueries (including correlated)
- HAVING and GROUP BY clauses
- ORDER and LIMIT clauses
- updatable cursors
- database-specific SQL extensions

2. It must function with current versions of 40% of DBD's
created or updated on CPAN since Jan. 1, 2003. Said 40%
must include
- DBD::ODBC
- DBD::Oracle
- DBD::Pg
- DBD::MySQL
- DBD::CSV
- one 'exotic' driver (e.g.,
DBD::iPod or DBD::Amazon, but excluding DBD::Google,
whose syntax is too simplistic for a meaningful test)

(FWIW: Past experience (e.g., execute_array()) leads me to believe
Mssr. Bunce's requirements are likely much higher than 40%, so
"choose wisely, grasshopper")

BTW: If you need a list of DBD's meeting said requirement, let me know,
I just pulled one down.

3. It cannot require any changes to either DBI or the
selected DBD's.

4. It must produce a database-independent conforming set of error codes
(feel free to use SQLSTATE aka $h->state)

5. It must be uploaded to CPAN, and list, and demonstrably function against,
the DBD's selected in requirement (2) above.

Once you've implemented the subclass, you'll have empirical proof
of the feasibility, and, more importantly, you'll be able to port
the subclass to DBIv2, without any additional burden on DBI
developers.

Regards,
Dean Arnold
Presicient Corp.
Darren Duncan
2005-07-12 02:37:30 UTC
Permalink
Post by Dean Arnold
RE: SQL Parse Trees (or other non-SQL query input)
Since none of (ODBC, JDBC, ADO.NET) seems compelled to
impose this concept on driver writers, I don't see why
DBI should be the vanguard.
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.
Post by Dean Arnold
However, implementing a subclass of DBI to support it
seems technically feasible, so I'd suggest that
those of you championing this requirement implement one
on DBI v1.
I agree. Already in progress. In a week or two I should announce
"Rosetta/SQL::Routine Developer Preview #3" and look forward to
people trying it out and giving me the usual "how do I" questions.
But wait until then before making any in-depth analysis, since I have
some large updates to post first.
Post by Dean Arnold
As the proponents of this notion
are so generous with their requirements for those of us
who develop DBI drivers and/or contribute
development efforts to the DBI itself, I'm sure they won't
I agree.
Post by Dean Arnold
1. For DBI drivers which support them, your subclass
must support
- arbitrary numbers and levels of JOINs (including
various outer, and non-equijoins)
- arbitrarily nested subqueries (including correlated)
- HAVING and GROUP BY clauses
- ORDER and LIMIT clauses
- updatable cursors
- database-specific SQL extensions
Rosetta supports the definition of all of the above right now, except
that the last 2 are incomplete (for now). I still have to add "the
last mile", which is joining the definition to the implementation,
though that is what DR3 is for. See the top of
SQL::Routine::Language for a summary of what I claim to support.
Post by Dean Arnold
2. It must function with current versions of 40% of DBD's
created or updated on CPAN since Jan. 1, 2003. Said 40%
must include
- DBD::ODBC
- DBD::Oracle
- DBD::Pg
- DBD::MySQL
- DBD::CSV
- one 'exotic' driver (e.g.,
DBD::iPod or DBD::Amazon, but excluding DBD::Google,
whose syntax is too simplistic for a meaningful test)
My expectation is to support all DBDs that are themselves known to
work under the newest Perls and DBIs. Databases that already support
a feature when it is invoked as a SQL string will be supported for
that feature by Rosetta prior to those that don't and for which the
features need to be emulated.
Post by Dean Arnold
BTW: If you need a list of DBD's meeting said requirement, let me know,
I just pulled one down.
Sure, send it over.
Post by Dean Arnold
3. It cannot require any changes to either DBI or the
selected DBD's.
Yes.
Post by Dean Arnold
4. It must produce a database-independent conforming set of error codes
(feel free to use SQLSTATE aka $h->state)
Already done, though the current list of codes is short and pending updates.
Post by Dean Arnold
5. It must be uploaded to CPAN, and list, and demonstrably function against,
the DBD's selected in requirement (2) above.
I can only personally test against databases that are easy to install
on Mac OS X 10.3.9 and are free-as-in-beer (eg, SQLite, MySQL); for
others, I will need assistence to prove that they work.
Post by Dean Arnold
Once you've implemented the subclass, you'll have empirical proof
of the feasibility, and, more importantly, you'll be able to port
the subclass to DBIv2, without any additional burden on DBI
developers.
I accept your challenge.

-- Darren Duncan
Dean Arnold
2005-07-12 03:49:08 UTC
Permalink
Post by Darren Duncan
Post by Dean Arnold
BTW: If you need a list of DBD's meeting said requirement, let me know,
I just pulled one down.
Sure, send it over.
[ ] DBD-ADO-2.94.tar.gz 31-Jan-2005 02:40 41k GZIP compressed docume>
[ ] DBD-ASAny-1.13.tar.gz 31-Oct-2003 15:00 30k GZIP compressed docume>
[ ] DBD-Amazon-0.10.tar.gz 23-May-2005 15:41 58k GZIP compressed docume>
[ ] DBD-AnyData-0.08.tar.gz 19-Apr-2004 03:16 20k GZIP compressed docume>
[ ] DBD-CSV-0.22.tar.gz 31-Mar-2005 18:06 36k GZIP compressed docume>
[ ] DBD-Chart-0.81.tar.gz 26-Jan-2005 19:59 212k GZIP compressed docume>
[ ] DBD-DB2-0.78.tar.gz 19-Sep-2004 10:34 75k GZIP compressed docume>
[ ] DBD-File-0.34.tar.gz 21-Jun-2005 01:14 8k GZIP compressed docume>
[ ] DBD-Google-0.11.tar.gz 04-Mar-2004 18:51 20k GZIP compressed docume>
[ ] DBD-Informix-2005.01..> 14-Mar-2005 19:01 267k GZIP compressed docume>
[ ] DBD-Ingres-0.51.tar.gz 12-Jan-2004 06:18 46k GZIP compressed docume>
[ ] DBD-InterBase-0.43.t..> 25-Feb-2004 04:30 78k GZIP compressed docume>
[ ] DBD-LDAP-0.06.tar.gz 12-Mar-2004 21:48 25k GZIP compressed docume>
[ ] DBD-Log-0.22.tar.gz 27-May-2005 06:51 14k GZIP compressed docume>
[ ] DBD-MaxDB-7_5_00_26...> 18-Apr-2005 08:38 79k GZIP compressed docume>
[ ] DBD-Mimer-1.00.tar.gz 25-Nov-2003 15:51 71k GZIP compressed docume>
[ ] DBD-Mock-0.27.tar.gz 11-Jul-2005 11:36 34k GZIP compressed docume>
[ ] DBD-Multiplex-1.96.t..> 25-Jan-2005 17:30 9k GZIP compressed docume>
[ ] DBD-ODBC-1.13.tar.gz 08-Nov-2004 10:15 95k GZIP compressed docume>
[ ] DBD-Oracle-1.16.tar.gz 22-Oct-2004 05:17 230k GZIP compressed docume>
[ ] DBD-Pg-1.43.tar.gz 23-Jun-2005 08:09 128k GZIP compressed docume>
[ ] DBD-PgPP-0.05.readme 09-May-2004 08:06 3k
[ ] DBD-PgPP-0.05.tar.gz 13-May-2004 12:56 16k GZIP compressed docume>
[ ] DBD-PgSPI-0.02.tar.gz 06-Dec-2004 00:30 21k GZIP compressed docume>
[ ] DBD-Redbase-0.22.tar.gz 21-Oct-2003 22:51 28k GZIP compressed docume>
[ ] DBD-SQLite-1.09.tar.gz 20-Jun-2005 11:42 464k GZIP compressed docume>
[ ] DBD-SQLite2-0.33.tar.gz 10-Sep-2004 11:50 355k GZIP compressed docume>
[ ] DBD-Sprite-0.56.tar.gz 12-Jun-2005 21:52 86k GZIP compressed docume>
[ ] DBD-Sybase-1.05.tar.gz 19-Dec-2004 05:01 183k GZIP compressed docume>
[ ] DBD-TSM-0.04.readme 22-Mar-2005 16:05 2k
[ ] DBD-TSM-0.04.tar.gz 23-Jun-2005 16:32 9k GZIP compressed docume>
[ ] DBD-Teradata-1.20.ta..> 17-Sep-2004 19:27 36k GZIP compressed docume>
[ ] DBD-Trini-0.01.tar.gz 15-Jul-2003 03:18 21k GZIP compressed docume>
[ ] DBD-Unify-0.31.tgz 16-Mar-2004 11:07 31k GZIP compressed tar ar>
[ ] DBD-XBase-0.241.tar.gz 21-Nov-2003 09:25 109k GZIP compressed docume>
[ ] DBD-Yaswi-0.01.tar.gz 21-Feb-2005 19:46 4k GZIP compressed docume>
[ ] DBD-iPod-0.01.tar.gz 06-Jan-2005 02:41 13k GZIP compressed docume>
[ ] DBD-mysql-3.0002.tar.gz 11-Jul-2005 12:49 127k GZIP compressed docume>
[ ] DBD-mysql-AutoTypes-..> 02-Mar-2004 06:03 3k GZIP compressed docume>
[ ] DBD-mysql-SimpleMySQ..> 28-Apr-2004 16:39 4k GZIP compressed docume>
[ ] DBD-mysqlPP-0.04.tar.gz 24-Jan-2003 06:14 7k GZIP compressed docume>

- Dean
Sam Vilain
2005-07-12 04:13:24 UTC
Permalink
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.
Sam Vilain
2005-07-12 23:04:23 UTC
Permalink
Post by Dean Arnold
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.
Jonathan Leffler
2005-07-13 07:57:40 UTC
Permalink
I've dropped perl6-language off the addressee list - this is pretty much
internals of DBI or DBD::WhatNot and not Perl language per se.
Post by Sam Vilain
Post by Dean Arnold
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(?)'.
DBD::Informix deals with both of these correctly in a variety of ways. The
DATE column is the easier - Informix Dynamic Server (IDS) is very good about
converting strings to DATE values - and to most other types. Also, since
Informix describes the types of the columns of the INSERT statement - and
can describe the input parameters of the SELECT statement (using DESCRIBE
INPUT) in the more recent versions of IDS - it can arrange the necessary
conversion.

The BYTEA example - corresponding to BYTE in IDS - is trickier. The string
you supply is converted into the relevant C structure - it happens to be a
loc_t in Informix ESQL/C - and then passed to the database. For INSERT, this
is easy because the types are described and the code in DBD::Informix can
tell that it needs to treat that properly. In other places, you have to use
the Informix type codes to convey the information to DBD::Informix. From
'perldoc DBD::Informix':


$upd = 'UPDATE SomeTable SET TextCol = ? WHERE Pkey = ?';
$sth = $dbh->prepare($upd);
$sth->bind_param(1, $blob_val, { ix_type => IX_TEXT });
$sth->bind_param(2, $pkey);
$sth->execute;

Internally, DBD::Informix knows that it must do the Perl string to Informix
loc_t mapping when this is specified.

Yes, it is a bit of work for the driver - but, for at least some drivers, it
is doable.
--
Jonathan Leffler <***@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2005.01 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."
Dean Arnold
2005-07-13 14:58:15 UTC
Permalink
Post by Jonathan Leffler
I've dropped perl6-language off the addressee list - this is pretty much
internals of DBI or DBD::WhatNot and not Perl language per se.
Post by Sam Vilain
Post by Dean Arnold
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.
What sort of "peppering" ? DBI provides SQL_BLOB, and SQL_CLOB
type descriptors (as well as SQL_BLOB_LOCATOR and SQL_CLOB_LOCATOR), so
presumably DBD::Pg (or any other DBD supporting LOBs) provides the
logic to map from

$sth->bind_param(3, $somelob, SQL_CLOB);

to whatever it needs to send on the wire. No different than, e.g.,
binding an integer or decimal(15,4). If some drivers don't support that,
thats a driver conformance issue, not a requirement for a new interface
mechanism.
Post by Jonathan Leffler
Post by Sam Vilain
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(?)'.
Er, why ? I haven't used DBD::Oracle lately, but assuming you
$sth->bind_param(1, '2005-07-13', SQL_DATE),
I'd assume DBD::Oracle would be smart enough to communicate that
to Oracle (either by munging the query text, or providing type codes
in the client request structure). I certainly handle that sort of
thing in DBD::Teradata, and I suspect DBD::ODBC would as well.
Post by Jonathan Leffler
DBD::Informix deals with both of these correctly in a variety of ways. The
DATE column is the easier - Informix Dynamic Server (IDS) is very good about
converting strings to DATE values - and to most other types. Also, since
Informix describes the types of the columns of the INSERT statement - and
can describe the input parameters of the SELECT statement (using DESCRIBE
INPUT) in the more recent versions of IDS - it can arrange the necessary
conversion.
The BYTEA example - corresponding to BYTE in IDS - is trickier. The string
you supply is converted into the relevant C structure - it happens to be a
loc_t in Informix ESQL/C - and then passed to the database. For INSERT, this
is easy because the types are described and the code in DBD::Informix can
tell that it needs to treat that properly. In other places, you have to use
the Informix type codes to convey the information to DBD::Informix. From
$upd = 'UPDATE SomeTable SET TextCol = ? WHERE Pkey = ?';
$sth = $dbh->prepare($upd);
$sth->bind_param(1, $blob_val, { ix_type => IX_TEXT });
$sth->bind_param(2, $pkey);
$sth->execute;
Internally, DBD::Informix knows that it must do the Perl string to Informix
loc_t mapping when this is specified.
Yes, it is a bit of work for the driver - but, for at least some drivers, it
is doable.
Have you considered updating DBD::Informix to use the SQL_BLOB/CLOB type codes ?

Regards,
Dean Arnold
Presicient Corp.
Jonathan Leffler
2005-07-13 15:17:23 UTC
Permalink
Post by Darren Duncan
[...]
Post by Jonathan Leffler
The BYTEA example - corresponding to BYTE in IDS - is trickier. The
string
Post by Jonathan Leffler
you supply is converted into the relevant C structure - it happens to be
a
Post by Jonathan Leffler
loc_t in Informix ESQL/C - and then passed to the database. For INSERT,
this
Post by Jonathan Leffler
is easy because the types are described and the code in DBD::Informix
can
Post by Jonathan Leffler
tell that it needs to treat that properly. In other places, you have to
use
Post by Jonathan Leffler
the Informix type codes to convey the information to DBD::Informix. From
$upd = 'UPDATE SomeTable SET TextCol = ? WHERE Pkey = ?';
$sth = $dbh->prepare($upd);
$sth->bind_param(1, $blob_val, { ix_type => IX_TEXT });
$sth->bind_param(2, $pkey);
$sth->execute;
Internally, DBD::Informix knows that it must do the Perl string to
Informix
Post by Jonathan Leffler
loc_t mapping when this is specified.
Yes, it is a bit of work for the driver - but, for at least some
drivers, it
Post by Jonathan Leffler
is doable.
Have you considered updating DBD::Informix to use the SQL_BLOB/CLOB type codes ?
It recognizes those too - AFAICR. I just prefer to use Informix type names
for Informix types. And there are also BLOB and CLOB type LOBs in Informix -
which are distinctly different from the BYTE and TEXT LOBs, and even less
supported by DBD::Informix.
--
Jonathan Leffler <***@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2005.01 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."
Tim Bunce
2005-08-16 16:01:41 UTC
Permalink
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.
Reidy, Ron
2005-07-13 15:06:09 UTC
Permalink
-----Original Message-----
From: Sam Vilain [mailto:***@vilain.net]
Sent: Tuesday, July 12, 2005 5:04 PM
To: Dean Arnold
Cc: dbi-***@perl.org; dbi-***@perl.org; perl6-***@perl.org
Subject: Re: DBI v2 - The Plan and How You Can Help
Post by Sam Vilain
Post by Dean Arnold
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.
Post by Sam Vilain
Sam.
This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
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.
Reidy, Ron
2005-07-13 15:07:56 UTC
Permalink
Sorry, instead of implicit 'commit', I mean to say implicit conversion.

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


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


-----Original Message-----
From: Sam Vilain [mailto:***@vilain.net]
Sent: Tuesday, July 12, 2005 5:04 PM
To: Dean Arnold
Cc: dbi-***@perl.org; dbi-***@perl.org; perl6-***@perl.org
Subject: Re: DBI v2 - The Plan and How You Can Help
Post by Sam Vilain
Post by Dean Arnold
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.
Post by Sam Vilain
Sam.
This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
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
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
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.
John Siracusa
2005-08-16 19:58:54 UTC
Permalink
Post by Tim Bunce
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
Tim Bunce
2005-08-17 09:39:43 UTC
Permalink
Post by John Siracusa
Post by Tim Bunce
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 :)
Post by John Siracusa
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.)
Post by John Siracusa
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 :)
Post by John Siracusa
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.
John Siracusa
2005-08-17 11:58:52 UTC
Permalink
Post by Tim Bunce
Post by John Siracusa
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 ;)
Post by Tim Bunce
With Perl 6 there's going to be some almighty hangovers :)
Understatement of the week :)
Post by Tim Bunce
Post by John Siracusa
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
Bart Lateur
2005-08-17 12:21:48 UTC
Permalink
Post by Tim Bunce
Post by John Siracusa
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.

<http://www.perl.com/pub/a/1999/09/topaz.html>
--
Bart.
Tim Bunce
2005-08-17 15:24:35 UTC
Permalink
Post by Bart Lateur
Post by Tim Bunce
Post by John Siracusa
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.
<http://www.perl.com/pub/a/1999/09/topaz.html>
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.

Continue reading on narkive:
Loading...