[DB-SIG] Other extensions

[Art Protin]

Dear Marc-Andre, et al,
    M.-A. Lemburg wrote:

>On 2007-05-15 18:06, Art Protin wrote:
>  
>
>>Dear folks,
>>    I have lots more questions about ways that the API could and possibly
>>should be enriched.
>>    
>>
>
>There have been some discussions about this, but since no standard
>API could be found, no additions to the DB-API were made.
>
>ODBC has a very complete set of catalog functions for querying
>meta-data of a database. It works by creating result sets that you
>can then fetch using the standard DB-API tools, so it's fairly
>straight forward to use.
>
>Internally, most ODBC drivers map these function calls to standard
>SQL SELECTs which work against the internal system tables or call
>special stored procedures in the database to create the result sets.
>
>I suppose the same could be done at a Python interface level (which
>could be the DB-API level or a level above the DB-API).
>
>  
>
No, this can not be done in a standard way above the DB-API level.  
There is nothing
in the DB-API specification that can be used in a DBMS independent 
manner, that
would be assured of producing the answers.  One would need to presume 
that all
DBMSs have SQL access to the systems tables and ours for one does not.

I value a simple and clean interface.  I feel that nothing should be 
added to the API
can be built from the tools that API provides.  I do not see how the 
existing API provides
enough functionality to get this data in a generic way.

>>A. Stored Procedures:
>>1.    The API provides a method for calling a stored procedure.  Has there
>>    been any discussion about how a user/application might discover the
>>    names of such store procedures?
>>2.   Has there been any discussion of how a user/application might create
>>    a stored procedure?
>>    
>>
>
>This can normally be done using standard .execute() calls.
>  
>
Please explain how this is done, as I believe that there is no way 
provided in the
API to do it in a standard or DBMS independent manner.

>  
>
>>*   My implementation has made some attempt to address this.  All of our
>>    queries are "named" and "stored" but they are either stored with the
>>    session (connection) or with the user account (as provided in 
>>connect()).
>>    Everything stored with the session vanishes when the connection closes
>>    and everything stored with user account is visible by all connections
>>    using that account.  Thus I made visible objects of the class Server 
>>(via
>>    an attribute of connection objects), keep all the account info there and
>>    provided some methods on server objects to create persistent named
>>    queries and to control access to them by other accounts.  I have no
>>    method to destroy a persistent query yet.
>>    
>>
>
>Like everything that deals with stored procedures, this is highly
>database specific.
>
>  
>
Yes, but is there any commonality in what the different interfaces could 
offer
that could be the basis for defining general approaches?

>>B. Metadata
>>    Not all DBMSs provide SQL access to the system tables.  In fact, the
>>    DBMS I work with most is one that doesn't.
>>1. Has there been a discussion yet about how a user/application might do
>>    discovery of the table names?
>>2. and the column names with a table?
>>3. and the types of the columns?
>>*    My implementation has done naught to address this limitation.
>>    
>>
>
>See our mxODBC interface for how this can be done via catalog
>methods:
>
>	http://www.egenix.com/products/python/mxODBC/
>
>  
>
>>C. Non-SQL Queries
>>1. Has there been any discussion of how a user/application should present
>>    queries that are in some other query language?
>>    
>>
>
>No. The DB-API is about relational databases and SQL as query
>language. The interfaces may also be suitable for other query
>languages, but that's out of scope for the DB-API.
>
>  
>
It seems strange to me to contradict the editor of the specification 
about what the
specification says.  However, I do not find anything in the first 
hundred lines that
mention either "relational" or "SQL", rather it talks about "Database 
Interfacing"
and queries.
I have no problem at all with placing the utmost priority in making sure 
that the
API works with SQL queries on relational DBMSs, but I have no respect for
efforts to make it only work with relational DBMSs or only work with SQL.

If my DBMS is the only one that has an alternative to SQL, then it makes no
sense to try to "standardize" alternative languages, and I accept that.  
I do not
accept that the API must somehow limit itself to SQL even when there is a
common need for more.

When no one else comes forward with a similar need, I will assume that 
there is
no one else who has such a need or interest and that is reason enough to 
drop
this debate.

>>2. Has there been any discussion of the representation of query language
>>    names?
>>*    My implementation had to address this because our DBMS has its own
>>    preferred query language and management requires that I provide access
>>    to it (which I accept as perfectly reasonable).  To avoid confusion 
>>that might
>>    arise when trying to recognize the difference between it and SQL, I 
>>simply
>>    added extension methods like Cursor.exec_alt(prog, parm, keys) where
>>    prog is just the (non-SQL) program in a string, parm is just 
>>parameters for
>>    the query (just like for .execute()) and keys is a list of keys to 
>>use when
>>    parm is a dictionary (to linearize the parameters for handing off to 
>>the DBMS).
>>    But this does not address how a third party application might 
>>discover that
>>    an alternative language is available nor how it would know how to pass
>>    such a query from a sophisticated user to this alternative method.
>>    
>>
>
>Unless the .execute() method signature doesn't provide the
>necessary detail, I'd generally do this by passing an additional
>(keyword) parameter to .execute().
>
>  
>
I could easily adopt such an approach.  What about the next revision 
having some
mention of such addition parameters to .execute()?  Would that be 
something like
lang='SQL'
being the default that a user could override?

>I don't think that the DB-API should require a mechanism for
>querying the query language as this is normally always SQL (in
>some dialect).
>  
>
Only if no one else needs something besides SQL.

Moreover, I did not expect such a mechanism would be required.   Rather, 
I had
expected it to be the recognized optional mechanism.  If an interface 
does not
support anything but SQL, it would not need the mechanism.  A 
user/application
could look for the method or attribute and know the default (that only 
SQL is
supported) by the absense.

The issue is it is much much better that implementers use the same common
extensions when they will suffice and not do yet another unique solution.
We will however add what ever extensions are needed to make the features
of our unique DBMS available to our users.  SO it is all about balance.

>  
>
>>    I doubt this is a complete list, but my mind has gotten empty while 
>>writing
>>this so I will send it as is.
>>    
>>
>
>  
>
    Thank you all,
    Art Protin
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.python.org/pipermail/db-sig/attachments/20070515/26db211b/attachment-0001.html