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

Stuart Bishop zen@cs.rmit.edu.au
Sat, 21 Oct 2000 11:05:59 +1100 (EST)

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.

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'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

Is it worth adding methods to help build (simple) generic SQL? 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):
	    '''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)

		# Should ensure string type.
		return 'UPPER(%s)=UPPER(%s)' % (qo1,qo2)

Stuart Bishop                          Work: zen@cs.rmit.edu.au
Senior Systems Alchemist               Play: zen@shangri-la.dropbear.id.au
Computer Science, RMIT University