[SQLObject] Re: SQLObject / Oracle

[Dirk E. <D....@ex...>]

Hi,

I just hacked together the basic columnsFromSchema implementation for Oracle.
I'll post it here for John. Can't help it, I'm sitting behind a firewall.
I tried out the SQL statement behind it, but not the python code itself,
so there might be some stupid mistakes/typos in there...

The MySQL version of columnsFromSchema and guessClass is much easier to understand
than the PostgreSQL version.

Regards
Dirk

>>> snip

    def columnsFromSchema(self, tableName, soClass):
        # DE: Oracle is nice to us here, for a change!
        colData = self.queryAll("SELECT column_name, data_type, data_length, data_precision, data_scale, nullable, data_default FROM user_tab_columns WHERE table_name = UPPER('%s') ORDER BY column_id"
                                % tableName)
        results = []
        for field, t, dataLength, dataPrec, dataScale, nullAllowed, default, in colData:
            if field == 'ID':
                continue
            
            colClass, kw = self.guessClass(t,dataLength,dataPrec,dataScale)
            kw['name'] = soClass._style.dbColumnToPythonAttr(field)
            if nullAllowed == 'Y':
                # DE: nullable is a 'Y', 'N' column
                kw['notNone'] = False
            else:
                kw['notNone'] = True
            kw['default'] = default
            # @@ skip key...
            # @@ skip extra...
            results.append(colClass(**kw))
        return results

    def guessClass(self, t, dataLength, dataPrec, dataScale):
        # DE: Oracle gave us all, so there's no need to guess
        col = None
        kw = {}
        if t.startswith('NUMBER'):
            # DE: INT is NUMBER in Oracle
            col = Col.IntCol
            if dataPrec:
                kw['precision'] = int(dataPrec)
            if dataScale:
                kw['scale'] = int(dataPrec)
        elif t.startswith('VARCHAR'):
            col = Col.StringCol
            kw['length'] =  int(dataLength)
            kw['varchar'] = TRUE
        elif t.startswith('CHAR'):
            col =  Col.StringCol
            kw['length'] =  int(dataLength)
            kw['varchar'] = False
        elif t.startswith('DATE'):
            col = Col.DateTimeCol
        elif t.startswith('bool'):
            # DE: Hmm, no good idea how to check for bool yet!
            # DE: Need to identify CONSTRAINT CHECK by name maybe...
            col = Col.BoolCol
        else:
            col =  Col.Col

        return col, kw

<<< snip

-- 
--------------------------------------------------------------------------
Dirk Evers                               Bioinformatics Research Scientist
Exelixis Deutschland                              Fon: +49 (0)7071-9655-16
Spemannstr. 35                                    Fax: +49 (0)7071-9655-96
D-72076 Tuebingen, Germany                   URL: http://www.exelixis.com/
--------------------------------------------------------------------------