Change Oracle Number to Integer conversions

[Former user]

I'm reflecting Oracle tables and creating tables in other dbs based on the reflection.

I have tables that have a primary key defined (in oracle) as NUMBER(9). With the current scheme, this is converted to a float, when in oracle this is really an int.

Am using this page for type conversions: http://www.oracle.com/technology/tech/migration/ama/exchange/docs/ss2k/datatypecompatability.htm

Here's a proposed change to oracle.py OracleDialect.reflecttable method:

Change:

            if coltype=='NUMBER' :
                if precision is None and scale is None:
                    coltype = OracleNumeric
                elif precision is None and scale == 0  :
                    coltype = OracleInteger
                else :
                    coltype = OracleNumeric(precision, scale)

to:

            # using http://www.oracle.com/technology/tech/migration/ama/exchange/docs/ss2k/datatypecompatability.htm
            # as conversion table for numbers
            if coltype == "NUMBER":
                if precision is None and scale is None:
                    coltype = OracleNumeric
                elif scale is None or scale == 0:
                    #int type
                    if precision >= 10:
                        coltype = OracleInteger # (bigint on non-oracle dbs)
                    elif precision >= 6:
                        coltype = OracleInteger # normal int
                    elif precision >= 3:
                        coltype = OracleSmallInteger 
                    elif precision >= 0:
                        coltype = OracleSmallInteger # (tinyint on ms)
                    else:
                        coltype = OracleInteger
                else:
                    coltype = OracleNumeric(precision, scale)

I realize there are some issues with this. Perhaps Oracle (and types.py) needs a BigInt and TinyInt so other dbs can know when to use these types....

thanks,

matt harrison

[Mike Bayer]

patch + some test cases for either test/dialect/oracle.py would be helpful here else we'll have to get around to this...

[Mike Bayer]

Looking at this more closely, one thing I notice is that Numeric returns Decimal instances, not floats, unless the asdecimal=True flag is turned off.

I'm not sure its appropriate to reflect a type that is specifically NUMERIC into an OracleInteger - Oracle has explicit INT and SMALLINT types which we also reflect, but in this case it seems like it might be more appropriate for OracleNumeric to understand the special meaning of Oracle's precision and scale parameters (which seems basically that when scale is not present, it returns integers ?) that way we would more closely map to Oracle's behavior.

[Mike Bayer]

0.6's approach of reflecting as accurately as possible has led to d564baf593311ca5bc62dbc401e57986bf9169ba - reflect as NUMBER and keep it that way, thus preserving the exact precision and scale received. NUMBER then ensures that all bind/result behaviors are compatible with the generic versions.

The potential downside here is that NUMBER has the dual parents of Numeric and Integer. I considered changing Integer to descend from Numeric, though that is a pretty dramatic change - at the end of the day, NUMBER is both a Numeric and Integer, but based on its arguments is only one of those at a time when instantiated. There's the potential to play instantiation games with __bases__ but I don't think that's going to make anything simpler for anyone.

We do have SMALLINT, BIGINT, etc. in types now. We might also reflect to those, but preserve "precision" (which might mean adding all those to the oracle dialect separately)...but for the moment just using NUMBER seems to be the more straightforward approach. It reflects well and acts correctly, the only issue would be if someone is relying upon isinstance(x, Numeric) or isinstance(x, Integer) somehow...which I am not worried about at the moment.

[Mike Bayer]

Removing milestone: 0.6.0 (automated comment)