[fdo-dev] About DBI, GDBI and RDBI

[Brent Robinson]

Hi Mateusz,

To answer one of the questions:

>"The current Oracle Provider has no GDBI equivalent (DBI is too
>high-level and RDBI does not provide enough array fetching support).
>Therefore the Oracle Provider's Physical Object implementation will
talk
>directly to the Oracle Driver (OCIDR) package. This makes these classes
>conceptually part of OCIDR."

>Does it mean there is a possibility to implement RDBMS
>provider without implementing RDBI layer but to put
>all RDBMS-specific operations on higher level, like Dbi derivatives?

This particular part of the design doc is a bit out of date. It ended up
being implemented by changing the Oracle Provider to call GDBI. The main
reason for the change is that I decided ( with some prodding from other
team members (:)) ) to leverage off the array fetching functionality in
GDBI, rather than implement my own in the Oracle Schema Manager. 

In GenericRdbms, all access to the RDBMS currently goes through RDBI.
The main advantage of this is that the higher level parts of
GenericRdbms are insulated from the specifics of each type of RDBMS.
However, there's a disadvantage in that RDBI must provider a superset of
all RDBMS functionality that every GenericRdbms provider needs. 

In the PostGIS provider case, for functions general to most RDBMS types
(e.g execute a SQL statement), I'd recommend following the current
convention of providing a PostGIS driver function that is only called
through an RDBI wrapper. 

However, in my own opinion, I think it's ok to call PostGIS or PostGIS
driver functions directly, if the function has no equivalent in other
types of RDBMS's and the call is made from provider-specific code such
as the PostGIS Schema Manager. If the calling code is already
RDBMS-specific then it is already not insulated from the RDBMS. Also,
providing an RDBI wrapper for these types of functions would eventually
fill the RDBI interface up with functions supported on only one RDBMS.

Brent. 

-----Original Message-----
From: Mateusz Loskot [mailto:mateusz at loskot.net] 
Sent: Monday, August 21, 2006 1:50 PM
To: dev at fdo.osgeo.org
Subject: [fdo-dev] About DBI, GDBI and RDBI

Hi,

Before the weekend, Brent sent me some docs about Schema Manager.
BTW, these docs are very helpful for me, so thanks!

Unfortunately, I'm still trying to grasp how to design Rdbi driver
for PostGIS properly. The main problems I have is to get an idea
about how to implement cursors and statements execution.
When I analyze it on the Rdbi-only level, the lowest-level, it's quite
clear, but I'm still not sure how to verify my considerations on the
upper level. Simply, how to answer questions like "is this
implementation of postgis_est_cursor, postgis_execute, postgis_bind,
etc. functions, going to work with Gdbi, Dbi and Schema
Manger properly"?


I still don't see the proper path of query execution.
Analyzing it on the Gdbi level, I see there are two possibilities:
- through GdbiConnection
- or GdbiCommands.

Am I correct that client will deal only with GdbiConnection
interface to prepare and execute SQL query?

Second, could you help me with ordering GdbiConnection and
GdbiCommands calls?
I mean, what's the relation between

GdbiConnection::ExecuteQuery()
and
GdbiConnection::Prepare()

Both functions forwards execution to the same function
GdbiCommands::sql()

Also, I can't see what is the query id for?
It seems to be never used, for example:

GdbiStatement*  GdbiConnection::Prepare( const wchar_t *sqlStr )
{
    m_pGdbiCommands->CheckDB();
    int     qid = -1;
^^^^^^^^^^^^^^^^^^^^^^^^^^^----WHERE IS THIS ID USED?

    if( m_pGdbiCommands->sql( (wchar_t*)sqlStr, &qid ) == RDBI_SUCCESS )
    {
        return new GdbiStatement( m_pGdbiCommands, qid );
    }

    m_pGdbiCommands->ThrowException();
    return NULL; // to supress a compiler warning
}

As I understand, the qid is assigned to GdbiStatement returned
from the function, but it's not saved for further references,
for example to fetch query id=102 from the stack, and to execute it.

How this GdbiStatement relates to GdbiCommands::execute() function
(and rdbi_execute) which AFAI understand is dedicated to execute
previously prepared and parsed statements.


Next, I'd like to ask for some suggestion about how to implement
the cursor structure for PostGIS.
I analysed MySQL and ODBC versions but both are completely different
and not comparable, so may be you could provide me with general rules
about Rdbi cursor implementation best practice?

Here is my prototype:

typedef struct tag_postgis_redefine_def
{
    void *original;
    void *substitution;
    void *geometry; /* Geometry to be deleted, since the client won't */
    int   orig_type;
} postgis_define_def;

As you see, my postgis_define_def is defined as MySQL's version.
Now, the cursor:

typedef struct tag_postgis_cursor_def
{
    PGresult *statement;
    int   define_count;
    PGSQL_BIND *defines;
    int   bind_count;
    PGSQL_BIND *binds
    postgis_define_def *redefines; /* array of size define_count, NULL
entries for non-geometries */
    postgis_define_def *rebinds;   /* array of size bind_count, NULL
entries for non-geometries */
} postgis_cursor_def;

PGresult* statement is pointer to prepared statement
returned by PQprepare() function, from PostgreSQL API.
So, this statement includes name, SQL query.
Next, I'll define PGSQL_BIND structure (similar to MYSQL_BING)
that will be used to on-fly conversion of Geometry, between
FGF and OGC WKB binary streams.
So, the PGSQL_BIND structure will hold buffer as void *buffer;,
lenght of the buffer, type of data in the buffer, and buffer state
indicators like if the buffer is SQL NULL type.

PostgreSQL/PostGIS does not provide anything like MYSQL_BIND and
MYSQL_STMT to exchange input/output data. Also, every cursor and
prepared statement is assigned to SQL query during creation, so there is
no function like mysql_stmt_init() in MySQL.
So, my postgis_est_cursor() function only allocates and zero'es
instance of the postgis_cursor_def structure (MySQL driver
also initializes MYSQL_STMT structure).

Please, if anyone could share some comments about my cursor structure
design, I'd be thankful.
May be I don't see yet all important details that may make my cursor
unusable with some Gdbi or Dbi features :)

My last question is about the "FDO Schema Manager Design" document I got
from Brent. On page 43, I read:

"The current Oracle Provider has no GDBI equivalent (DBI is too
high-level and RDBI does not provide enough array fetching support).
Therefore the Oracle Provider's Physical Object implementation will talk
directly to the Oracle Driver (OCIDR) package. This makes these classes
conceptually part of OCIDR."

Does it mean there is a possibility to implement RDBMS
provider without implementing RDBI layer but to put
all RDBMS-specific operations on higher level, like Dbi derivatives?

If there are more possibilities, please tell me which one is
recommended, may be drivers for C-coded RDBI layer are deprecated?

Thank you for any help!

Cheers
-- 
Mateusz Loskot
http://mateusz.loskot.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe at fdo.osgeo.org
For additional commands, e-mail: dev-help at fdo.osgeo.org