Integer of higher length than 11

[JakeAustwick]

I can't seem to find a way for pony to specify a db int field with a length > 11, so if I try and insert something longer than 11 digits, I get:

cannot be stored in the database. DataError: 1264 Out of range value for column 'xxx' at row 1

If I increase the column length manually of the database column, the same problem persists. Any ideas / suggestions?

[matthewrobertbell]

The clue here is that it's out of range, 32 bit ints will never be longer than 11 digits (when you include negatives).

You need bigints / longs.

[JakeAustwick]

Please ignore. Realised this is due to the max size of int, and nothing to do with Pony. My bad.

[kozlovsky]

Just in case, this is how you can specify bigint column in Pony:

db = Database(...)

class MyEntity(db.Entity):
    attr1 = Required(int)  # Usual INT column
    attr2 = Required(long)  # BIGINT column
    attr3 = Required(int, sql_type='BIGINT')  # alternative way

[socketpair]

@kozlovsky, There is no long in python3. So, I consider you should make Required(long) to be obsolete and issue log.warning when detected. This WILL silently make bug when porting Pony-enabled applications to Python3.

Also, documentation update is required

[kozlovsky]

The resulting error is not silent, the exception NameError: name 'long' is not defined is raised during entity class definition in Python 3.

I'm not sure about deprecation. For projects where porting to Python 3 is not planned, using long type looks reasonable at the first sight. I see two possible solutions:

1. Deprecate long as you say, suggest using int with sql_type='BIGINT' instead. Downside: sql_type is database-specific. But currently BIGINT will work fine in all supported databases except Oracle, so maybe it is not a big problem. Probably we can add silent conversion of sql_type='BIGINT' to appropriate numeric type when using Oracle.

2. In Python 3 define long as a subclass of int, and automatically import it in Python 3 when doing from pony.orm import *. Override isinstance check of this 'long' type in such a way that isinstance(value, long) will return True for int values bigger then 2^32.

I need to think a bit more about this topic.

[socketpair]

Nice to listen to you! Please note:

1. The 2to3 tool will automatically convert long to int adding bug in user programs, so Pony cannot detect that sort of things.
2. Not all users use from pony.orm import *, so after migrating to Python3, long may be not pony.orm.long, but int in some programs where user uses same hack (if ... : long = int) for Python3 in order not to use 2to3 :(

[socketpair]

It seems that you should write Pyton2 to Python3 migration guide in documentation, and specify that undetectable problem (at least).

[socketpair]

maybe x = Required(int, big=True) or x = Required(int, bigint=True)?

[kozlovsky]

We deprecated long data type for attributes and introduced size option instead which can be used to specify column size in bits. This is the excerpt from the upcoming documentation:

For the int type you can specify the size of integer type that should be used in the database using the size keyword. This parameter receives the number of bits that should be used for representing an integer in the database. Allowed values are 8, 16, 24, 32 and 64:

    attr1 = Required(int, size=8)   # 8 bit - TINYINT in MySQL
    attr2 = Required(int, size=16)  # 16 bit - SMALLINT in MySQL
    attr3 = Required(int, size=24)  # 24 bit - MEDIUMINT in MySQL
    attr4 = Required(int, size=32)  # 32 bit - INTEGER in MySQL
    attr5 = Required(int, size=64)  # 64 bit - BIGINT in MySQL

You can use the unsigned parameter to specify that the attribute is unsigned:

attr1 = Required(int, size=8, unsigned=True) # TINYINT UNSIGNED in MySQL

The default value of the unsigned parameter is False. If unsigned is set to True, but size is not provided, size assumed to be 32 bits.

If current database does not support specified attribute size, the next bigger size is used. For example, PostgreSQL does not have MEDIUMINT numeric type, so INTEGER type will be used for an attribute with size 24.

Only MySQL actually supports unsigned types. For other databases the column will use signed numeric type which can hold all valid values for the specified unsigned type. For example, in PostgreSQL an unsigned attribute with size 16 will use INTEGER type. An unsigned attribute with size 64 can be represented only in MySQL and Oracle.

When size is specified, Pony automatically assigns min and max values for this attribute. For example, a signed attribute with size 8 will receive min value -128 and max value 127, while unsigned attribute with the same size will receive min value 0 and max value 255. Is is possible to further constraint possible values by specifying custom min and max values for the attribute.

Starting with the Pony release 0.6 the long type is deprecated and if you want to store 64 bit integers in the database, you need to use int instead with size=64. If you don't specify the size parameter, Pony will use the default integer type for the specific database.

[kozlovsky]

@socketpair, we just make Pony ORM 0.6 Release Candidate 3, if you think that the long behavior needs to be further changed then please let me know. We are going to release 0.6 final on Monday.

[socketpair]

As I see (I did no check), Required(long) should raise Exception/Warning. This is sufficient.