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
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig
