- changed milestone to 0.4.3
Change Oracle Number to Integer conversions
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
Comments (5)
-
repo owner -
repo owner 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.
-
repo owner - changed milestone to 0.5.xx
-
repo owner 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 ofNumeric
andInteger
. I considered changingInteger
to descend fromNumeric
, though that is a pretty dramatic change - at the end of the day,NUMBER
is both aNumeric
andInteger
, 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 uponisinstance(x, Numeric)
orisinstance(x, Integer)
somehow...which I am not worried about at the moment. -
repo owner - removed milestone
Removing milestone: 0.6.0 (automated comment)
- Log in to comment
patch + some test cases for either test/dialect/oracle.py would be helpful here else we'll have to get around to this...