[DB-SIG] Building Cross-Platform DB Apps: dbinfo.py

[M.-A. Lemburg]

Stuart Bishop wrote:
> 
> On Wed, 18 Oct 2000, M.-A. Lemburg wrote:
> 
> > Improvements and suggestions for new APIs are welcome. I think
> > I'll put this module up on starship as soon as it reaches a
> > usable size.
> 
> Will it be possible for code to create a dbinfo object using
> just a connection handle? It may be worth adding a new method
> to the connection object into the next rev of the DB spec if people
> are happy with the MetaDB concept.

The DBInfo objects probably won't need a connection object.
DBs could simply make use of whichever class is necessary, e.g.
the MySQL module would use MySQLInfo, DCOracle the OracleInfo
class, etc.

But yes, since the code is written in Python, we could easily
add a new method to the connection object which then returns
the right DBInfo singleton, e.g. connection.getinfo().

BTW, I've renamed it dbinfo.py.
 
> Anyone know their ANSI SQL '92 standard well enough to write
> a generic dbinfo object? This would probably end up as the
> implementation or at least a superclass for any database that has ODBC
> or JDBC interfaces (since I believe that both of these require SQL'92
> level 2 compliance).

I think it would make a good baseclass...
 
> I'm unsure about the case sensitivity options in the example implementation.
> I guess that databases that don't have 'caseinsensitive string' types
> (I really only know Oracle) will just have to throw an exception.
> However, there is usually some way of doing case insensitive
> comparisions.
> 
> Is it worth adding methods to help build (simple) generic SQL? 

Yes, I think so, e.g. many DBs support joins, but there doesn't
seem to be a standard for the syntax. Also there are some things
which tend to become annoying when writing DB-independent code:
e.g. MySQL doesn't support server side cursors, so the client
will always load the entire result set. This is find for small
result sets, but think of what happens when you want to page
through a result set at 10 rows at a time... fortunately, MySQL
support the "limit x,y" syntax, but again, that's DB-specific
and even though some DBs do support that syntax as well, their
semantics are different :-(

> Would
> anything else be required besides a comparision generator? I think that
> all of the the NOT, AND, OR stuff can be done if you write ANSI SQL.
> I can't remember if there is a standard way of specifying outer joins :-(
> 
>     class OraInfo:
>         def join(self,o1,o2,**kw):

Shouldn't this be named "compare(self, left, right, casesensitive=1)" ?!

>             '''Should allow more than two arguments, creating a
>                statement using the IN operator'''
>             qo1 = self.quote(o1)
>             qo2 = self.quote(o2)
> 
>             if qo1 is None and qo2 is None:
>                 return ''
> 
>             elif qo1 is None:
>                 return '%s IS NULL' % (qo2)
> 
>             elif qo2 is None:
>                 return '%s IS NULL' % (qo1)
> 
>             elif kw['casesensitive'] == 1:
>                 return '%s=%s' % (qo1,qo2)
> 
>             else:
>                 # Should ensure string type.
>                 return 'UPPER(%s)=UPPER(%s)' % (qo1,qo2)

Thanks,
-- 
Marc-Andre Lemburg
______________________________________________________________________
Business:                                      http://www.lemburg.com/
Python Pages:                           http://www.lemburg.com/python/