[jira] Commented: (DERBY-775) Network client: Add support for scrollable, updatable, insensitive result sets

["Dag H. Wanvik (JIRA)" <de...@db.apache.org>]

    [ http://issues.apache.org/jira/browse/DERBY-775?page=comments#action_12360849 ] 

Dag H. Wanvik commented on DERBY-775:
-------------------------------------

Posting this in the hope that some of you DRDA experts can comment on
it :)

I have been trying to figure out how to map JDBC scrollable,
updatable, insensitive result sets (SUR for short) onto DRDA and have
three questions (below).  I am assuming the semantics described in the
attachedment to DERBY-690.

Some background: 

DRDA has no concept of JDBC result sets, so the solution used in Derby
is to map JDBC result sets onto the DRDA cursor mechanism. Appendix B
in "DRDA, Version 3, Volume 1: Distributed Relational Database
Architecture (DRDA)" gives an overview of "Scrollable Cursors" (p
655).

a) Sensitivity

I first considered to map SUR to "insensitive scrollable cursors", but
DRDA states that these are read only (B.2.2). It seems the "sensitive
static cursors" better matches the semantics we want: For this
category of cursors, the size of the result set ("result table" in
DRDA parlance) as well as the ordering of rows are fixed and this
cursor is updatable, cf. quote on page 656:

     "The cursor is always immediately sensitive to changes made using
     the cursor (that is, positioned updates and deletes using the
     same cursor).

     The size of the result table does not grow after the cursor is
     opened and the rows are materialized. The order of the rows is
     established as the result table is materialized."

Combined with the proper fetch, i.e. fetch INSENSITIVE (p 658) on
CNTQRY (i.e. we are INSENSITIVE to changes made by others), the
semantics match those of JDBC's insensitive result sets as we have
specified them :

      ownDeletesAreVisible(TYPE_SCROLL_INSENSITIVE) -> true
      ownInsertsAreVisible(TYPE_SCROLL_INSENSITIVE) -> false
      ownUpdatesAreVisible(TYPE_SCROLL_INSENSITIVE) -> true

      othersDeletesAreVisible(TYPE_SCROLL_INSENSITIVE) -> false
      othersInsertsAreVisible(TYPE_SCROLL_INSENSITIVE) -> false
      othersUpdatesAreVisible(TYPE_SCROLL_INSENSITIVE) -> false

*Question 1*: Can anyone see a problem with mapping JDBC "updatable,
insensitive" onto DRDA "sensitive static"? Would this be violating
(the spirit of) the DRDA in any way?


b) Detectability

In the spec, we required that we be able to detect deletes and updates
to the rows in the result set:

      deletesAreDetected(TYPE_SCROLL_INSENSITIVE) -> true
      updatesAreDetected(TYPE_SCROLL_INSENSITIVE) -> true

(Since inserts are not visible, they can not be detectable, either).
DRDA supports detection of holes in the following manner (quote,
P. 656):

    "To present a static size and static ordering for the result table, the
    relational database may return a hole to the application that fetches
    an updated or deleted row in the result table. A hole in the result
    table occurs when there is a difference between the result table and
    the underlying base table. No data can be fetched from a hole, and the
    hole is manifested in the QRYDTA as a row consisting of a non-null
    SQLCARD and a null data group.

    When the current value of a row no longer satisfies the
    select-statement or statement-name, that row is visible in the cursor
    as an update hole , where the SQLCARD has a warning SQLSTATE of 02502.

    When a row of the result table is deleted from the underlying base
    table, the row is visible in the cursor as a delete hole , where the
    SQLCARD has a warning SQLSTATE of 02502."

For deletes, the "delete hole" is exactly what we need to support
ResultSet#rowDeleted().

For updates, is is not exactly what we want, since we do not intend to
requalify a row after it has been updated (thereby possibly making it
an "update hole"). On the other hand, when we update a row and let it
remain in the result table, DRDA offers no means of conveying that the
row has been changed in the sense of JDBC ResultSet#rowUpdated(), as
far as I can tell.

*Question 2*: Is there some way we can detect the latter without
violating the protocol? One could imagine signalling this using
another warning SQLSTATE. Would this be an acceptable tweaking of the
DRDA?

c) Query protocol

For scrollable result sets, Derby uses the "Limited Block Query
Protocol". This is allowable also for "static sensitive", so I assume
we can use that also for SUR in the way it is presently used for
scrollable, read-only result sets. 

d) Rowset cursors (p 667)

"Rowset cursors" seem not to be in use by the server, but the client
has code for handling it. Not sure at this point if this code needs updating
for SUR.. 

*Question 3*: Is it a requirement that the client be able to handle
row sets to be DRDA compliant? (We know our server doesn't use it for
now....) 

d) Equality of result set modification and positioned delete/update.

The same cursor is being used, and the server (and our prosed
implementation of SUR) is agnostic to whether an updateRow/deleteRow
or a positioned update/delete is being invokled by the network client
driver, the only difference being the autocommit semantics, which is
handled by the driver.


> Network client: Add support for scrollable, updatable, insensitive result sets
> ------------------------------------------------------------------------------
>
>          Key: DERBY-775
>          URL: http://issues.apache.org/jira/browse/DERBY-775
>      Project: Derby
>         Type: New Feature
>   Components: JDBC, Network Client
>     Versions: 10.2.0.0
>     Reporter: Dag H. Wanvik
>     Priority: Minor

>
> This is a part of the DERBY-690 effort.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira