Re: DBI - SCROLL CURSOR support - outline implementation
I've mercilessly truncated the message to which I'm responding with [...] marking most omissions. It's still a fairly long response, though. Dean Arnold wrote: Jonathan Leffler wrote: Dean Arnold wrote: 1. Will this support updatable cursors? [...] [...]Informix does not support updatable scrollable cursors [but they should exist according to] the [JO]DBC standards. [...] I guess if ODBC is the LCD for most platforms, then following whatever model it provides should be sufficient...I must admit I'm not a big fan of ODBC, and I hope Mssr. Bunce will *never* let DBI get as bloated with committee think as ODBC! Whether it's the LCD or something else, ODBC is the general direction that DBI is headed. [...] As you may be aware, JDBC uses explicit function methods prev(), next(), first(), last(), etc. to move the cursor, and then individual getXXX()'s to retrieve the fields. [...] SQLFetchScroll is the ODBC function [...] I'm not clear how you update a row via the cursor without fetching it first [...] Since a cursor is positioned, strictly speaking, a fetch is not needed, ie, just move the cursor to some row and apply your update/delete...and in JDBC, they add the insert row notion to allow inserts thru the cursor (a bit clumsy in my opinion, but consistent with the notion of positioning). Well, maybe, but the traditional way of positioning a cursor is by fetching a row of data. It starts to sound more and more like a bad case of CODASYL reviviscit - currency indicators and all. However, I've not used JDBC enough to have a worthwhile opinion on its details. And any SQL which doesn't explicitly reference a cursor doesn't use the cursor; visibility to a cursor of the changes applied by other apps updating the datasource is platform and/or app specific, based on all the isolation issues you mention below. For that matter, updates made thru a cursor may not even be visible to that cursor, depending on how the dbms/driver/app behaves. With Informix, you can only update via non-scroll cursors, and you can't refetch a row, so you can't see the update via the cursor (though if the update moves the row from row 123 to row 6923 of the result set, you might see it again in its updated form, though that's dependent on more details than are relevant to this forum). [...] 4. Where do attributes end and SQL begin? Rather, who wins when it's SQL vs. DBI attribute vs.DBI method call ? Ie, if I prepare()/execute() an SELECT * FROM MyTable FOR UPDATE, does the $sth implicitly behave as a scrollable if my platform supports scrollable? I'm not sure I understand the preamble to the last question. I was wondering if native SQL already provides most of the cursor functionality, and DBI's current definition is sufficient to provide most of the scaffolding...I guess the answer is No, since arbitrary positioning in a result set isn't available in any SQL dialect I'm aware of. I guess I need to raise your awareness of Informix. It has had scroll cursor support since about 1988. Granted, those are not updatable scroll cursors, but it has support for 'next', 'previous' (or 'prior'), 'first', 'last', 'current', 'relative', 'absolute' fetching. That's why I'm interested in this -- I want to provide access to native Informix functionality via DBI, but Tim (not wholly unreasonably) won't provide such methods unless there's a semi-decent backup implementation that will work for drivers where the database does not provide the support. And the normal technique for dealing with non-updatable scroll cursors is actually simple -- you only select the primary key columns and any necessary ordering columns in the scroll cursor (which can have an ORDER BY clause, therefore), and you select rows of data in two steps: collect the PK information from the scroll cursor, and then collect the rest of the data in a second operation using a cursor FOR UPDATE with primary key access (which should be fast). One advantage of this is that it reduces the amount of data that has to be stored in the scroll cursor. One disadvantage is the double trip to the DBMS to get the full data. Incidentally, it will be critical with scroll cursors to use $sth-finish() to release the stored data! Sorry Tim, it's the function that won't die. My concern is API bloat: if SQL can do it within the current DBI, then defining a new set of functions/attributes is probably not desirable (tho there seems to be a significant audience for the counter argument - SQL bad, Perl good -, hence DBIx::Recordset and its kin). My POV derives from DBD::Teradata's support for simple updatable cursors...but it doesn't support arbitrary positioning, so I guess it doesn't really apply. I wasn't aware of how ODBC did it when I sent the original mailing. Tim has indicated that what would happen is that a new function, $sth-setattr($$) would be added which would be called between the $sth-prepare() and $sth-execute() to indicate that this
Re: DBI - SCROLL CURSOR support - outline implementation
Dean Arnold wrote:
Questions/issues:
1. Will this support updatable cursors? If so, how?
If not, can it be extended to support them?
No. N/A. Maybe. Any ideas?
Since Informix does not support updatable scrollable cursors, I have
no immediate need of the concept, but I know they are intended to
exist by the [JO]DBC standards. I also know that (if the
functionality is provided at all) the Informix implementations jump
through hoops to provide client-side updatable scrollable cursors.
However, I've not investigated exactly how bad those hoops are.
I guess if ODBC is the LCD for most platforms, then followng
whatever model it provides should be sufficient...I must admit
I'm not a big fan of ODBC, and I hope Mssr. Bunce will *never*
let DBI get as bloated with committee think as ODBC!
2. Something about the i/f spec doesn't seem quite right to me.
(Probably because I've been doing way too much Java/JDBC lately).
As you may be aware, JDBC uses explicit function methods prev(),
next(), first(), last(), etc. to move the cursor, and then
individual getXXX()'s to retrieve the fields. Perhaps a bit too
wordy for Perl (as Java ever seems to be), but I think it feels a
bit better and maybe separates the functionality a bit more cleanly?
Ie, maybe I just want to move to the next row and apply an update
to one of the fields without fetching everything...if an $sth is
derived from execute_scroll() then fetchrow_XXX() doesn't move the
cursor; only one of the movement i/f's can do that.
SQLFetchScroll is the ODBC function - it takes a statement handle, a
fetch orientation, and an offset. The proposed Perl interface is
pretty close to that! The ODBC interface includes a 'bookmark' option
and does not include 'current', though that is not a big loss since it
is directly equivalent to Relative 0.
I'm not clear how you update a row via the cursor without fetching it
first -- but then I'm not clear how the updates occur to the database
which can affect the scroll cursor contents. Is it only the DELETE
and UPDARTE operations executed with 'WHERE CURRENT OF' or does it
include unrelated (searched) updates - INSERT, DELETE and UPDATE
operations?
Since a cursor is positioned, strictly speaking, a fetch is not needed,
ie, just
move the cursor to some row and apply your update/delete...and in JDBC, they
add the insert row notion to allow inserts thru the cursor (a bit clumsy
in my opinion,
but consistent with the notion of positioning).
And any SQL which doesn't explicitly reference a cursor doesn't use the
cursor;
visibility to a cursor of the changes applied by other apps updating the
datasource
is platform and/or app specific, based on all the isolation issues you
mention below.
For that matter, updates made thru a cursor may not even be visible to that
cursor,
depending on how the dbms/driver/app behaves.
For example, consider a scroll cursor on SELECT a.*, b.* FROM
OneTable A, TwoTable B WHERE A.Column = B.Column. Now suppose there
are three rows in OneTable with Column = 123 and there is one row in
TwoTable with Column = 123. Now, suppose the scroll cursor has one of
these three rows visible, and the user executes $dbh-do('DELETE FROM
TwoTable WHERE Column = 123'). If the select statement were
re-executed (and nothing else had changed), then there'd be three rows
fewer in the result set. How is a client-side implementation supposed
to work out that three rows from result set need to be removed (and
which ones they are). Note that the DELETE was completely independent
of anything to do with the scrollable $sth, beyond sharing the same
$dbh. Of course, if you can't have multiple statements at work on a
single $dbh - permitted by the DBI - then the problem doesn't arise in
quite that form, but if $dbh1 has the scrollable cursor and $dbh2 has
the delete operation, what then. And, of course, isolation levels
come into play too. If the scrollable cursor is running at
SERIALIZABLE isolation, then independent connections cannot interfere
with each other - and $dbh2 can't mess with the data that $dbh1 is
reading. OTOH, when you have multiple statements runnable on a single
$dbh, then you can modify the database with one statement without
running into isolation constraints. Now suppose the scroll cursor has
not yet reached the position where the three rows would have appeared?
What about if it has gone beyond that position? Are you allowed to
have an ORDER BY clause in your scrollable cursor? Are you allowed to
have joins in your scrollable cursor? If not, what about aggregates, etc?
It sounds a bit as if the Java code expects you to think as if you are
playing with indexed-sequential files, which is badly non-relational.
There's an element of 'cursors are non-relational, doubly so scroll
cursors', too - independent of JDBC or ODBC.
Agreed...IMHO, cursors, while convenient, are a bad habit that can often
be avoided by
Re: DBI - SCROLL CURSOR support - outline implementation
Questions/issues: 1. Will this support updatable cursors ? If so, how ? If not, can it be extended to support them ? 2. Something about the i/f spec doesn't seem quite right to me. (Probably because I've been doing way too much Java/JDBC lately ). As you may be aware, JDBC uses explicit function methods prev(), next(), first(), last(), etc. to move the cursor, and then individual getXXX()'s to retrieve the fields. Perhaps a bit too wordy for Perl (as Java ever seems to be), but I think it feels a bit better and maybe separates the functionality a bit more cleanly ? Ie, maybe I just want to move to the next row and apply an update to one of the fields without fetching everything...if an $sth is derived from execute_scroll() then fetchrow_XXX() doesn't move the cursor; only one of the movement i/f's can do that. 3. There are a whole host of side issues involved with updatable and scrolled cursors (e.g., if I apply an update to the current row, do I see it immediately ? Does the cursor survive a commit of updates ? If I DELETE WHERE CURRENT ..., where is the cursor after the delete ? etc.) 4. Where do attributes end and SQL begin ? Rather, who wins when its SQL vs. DBI attribute vs.DBI method call ? Ie, if I prepare()/execute() an SELECT * FROM MyTable FOR UPDATE, does the $sth implicitly behave as a scrollable if my platform supports scrollable ? I think this i/f needs more scrutiny/debate before rollout, due to all the add'l issues that may arise later. Even if its limited solely to read-only cursors initially, someone will eventually want updatables, so planning for them now may help avoid major API rework later. I think this area covers a lot more terrain than the bulk API (the number of usage scenarios for bulk operations is pretty small compared to all the permutations of how various cursor operations can behave and be used), so it likely could benefit from some healthy debate. My 2 cents, I now cede the soapbox... Later, Dean Arnold Presicient Corp. www.presicient.com - Original Message - From: Jonathan Leffler [EMAIL PROTECTED] To: Tim Bunce [EMAIL PROTECTED]; Jonathan Leffler [EMAIL PROTECTED] Cc: DBI Developers Mailing List [EMAIL PROTECTED] Sent: Friday, January 10, 2003 11:58 AM Subject: DBI - SCROLL CURSOR support - outline implementation Dear Tim, Here is a working proof of concept for scroll cursors in pure Perl. At least, it works with DBD::Informix, and I believe the SQL is platform neutral enough to work on other DBMS too if you fix the DBI-connect information. There are a ton of issues to resolve, but I think it demonstrates that the basic Perl support for scroll cursors in DBI would be fairly simple and short (and hence easily included when the drivers do not support it). Issues: * How should the user tell prepare/execute that this will be used as a scroll cursor? * I envisage (a variant on) the %ctl structure actually being an attribute of the $sth, so that instead of being passed explicitly to the fetchrow_scroll_arrayref method, it would arrive with the $sth. * I need to track down how (whether) to modify the %ctl structure so it contains the array directly rather than a reference to the array. * I need to track down how (whether) to remove some other references in the test code. * We need to decide whether to use strings to determine the fetch operation. They're simple, but... * We would need to review parameter binding. * We would need to review column binding. * We would need to review alternatives to returning arrayref: presumably fetchrow_scroll_array and fetchrow_scroll_hashref would be desirable. * We do not need to support selectall_scroll_* methods - you don't want to fetch everything if you want to scroll. * I assume we'd enforce rules that if a cursor is not started as a scroll cursor, you can't decide to use it as a scroll cursor after the (first) execute? Or is that after the prepare? I think it probably needs to be the execute that gets told about the scroll-ness since you would not know, in general, whether you have a fetchable statement until after the prepare has completed. * Are the implemented semantics of fetching before first and after last row correct? * How much does the prototype POD need to be fixed? Where do you want to go from here? -- Jonathan Leffler #include disclaimer.h STSM, Informix Database Engineering, IBM Data Management Phone: +1 650-926-6921 Fax: +1 650-926-6971 Tie-line: 630-6921 Email: [EMAIL PROTECTED] Guardian of DBD::Informix v1.04.PC1 -- http://dbi.perl.org
