Wrong FK column type when using `sql_type` on foreign ID column #74

Closed
crazygolem opened this Issue Sep 23, 2014 · 1 comment

Projects

None yet

2 participants

@crazygolem

When an entity Bar references an entity Foo, if the underlying type of the primary key of Foo has been modified using the sql_type parameter, then the FK column's type in Bar is not properly generated.

Before anything else, I acknowledge that what I was trying to do is a terrible hack and might even be unusable when actually managing the objects. Still, the generation part is problematic here, as even if doing so is not correct, mismatching FK column types are generated without warnings / errors.

Example

from pony.orm import *

sql_debug(True)

db = Database('mysql', db='ponybug', host='localhost', user='root')

class Foo(db.Entity):
    id = PrimaryKey(unicode, sql_type='INTEGER', auto = True)
    name = Optional(unicode)
    bars = Set(lambda: Bar)

class Bar(db.Entity):
    foo = Required(Foo)
    name = Optional(unicode)

db.generate_mapping(create_tables=True)

Excerpt from the debug output:

CREATE TABLE `foo` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL
)

CREATE TABLE `bar` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `foo` VARCHAR(255) NOT NULL,
  `name` VARCHAR(255) NOT NULL
)

Here, the bar table is created with a foo column defined with

`foo` VARCHAR(255) NOT NULL

where it should have been

`foo` INTEGER NOT NULL

Workaround

Explicitly specifying the underlying type of the foreign key solves the problem:

class Bar(db.Entity):
    foo = Required(Foo, sql_type='INTEGER')
    ...
@kozlovsky kozlovsky added the bug label Sep 23, 2014
@kozlovsky kozlovsky self-assigned this Sep 23, 2014
@kozlovsky kozlovsky closed this in 6c24cb1 Sep 24, 2014
@kozlovsky
Contributor

Thanks, fixed. Note that for MySQL, when the PK column of the main table has the sql_type specified as a SERIAL, the FK column of the related table will have SQL type BIGINT UNSIGNED.

For PostgreSQL, when the PK column of the main table has the SQL type SERIAL, the FK column of the related table will have SQL type INTEGER. When the PK column of the main table has the SQL type BIGSERIAL, the FK column of the related table will have SQL type BIGINT.

@kozlovsky kozlovsky added a commit that referenced this issue Oct 8, 2014
@kozlovsky kozlovsky Pony ORM Release Candidate 0.6rc1
http://blog.ponyorm.com/2014/10/08/pony-orm-0-6-release-candidate-1

# New features:

* Python 3 support
* pymysql adapter support for MySQL databases

# Backward incompatible changes

Now Pony treats both `str`` and `unicode`` attribute types as they are unicode strings in both Python 2 and 3. So, the attribute declaration `attr = Required(str)` is equal to `attr = Required(unicode)` in Python 2 and 3. The same thing is with `LongStr` and `LongUnicode` - both of them are represented as unicode strings now.

For the sake of backward compatibility Pony adds `unicode` as an alias to `str` and `buffer` as an alias to `bytes` in Python 3.

# Other changes and bug fixes

* Fixes #74: Wrong FK column type when using sql_type on foreign ID column
* Fixes #75: MappingError for self-referenced entities in a many-to-many relationship
* Fixes #80: “Entity NoneType does not belong to database” when using to_dict
395036b
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment