cascade delete foreign key error #136

Closed
gabriel-ilie opened this Issue Jul 13, 2015 · 1 comment

Projects

None yet

2 participants

@gabriel-ilie

I'm trying to use cascade_delete as detailed in the documentation (http://doc.ponyorm.com/working_with_entity_instances.html#cascade-delete).
I'm using pony 0.6.1 and python2.7

####################################################################
from pony.orm import *
db = Database("sqlite", ":memory:", create_db=True)

class Person(db.Entity):
    name = Required(str)
    passport = Optional("Passport", cascade_delete=True)

class Passport(db.Entity):
    number = Required(str)
    person = Required("Person")

sql_debug(True)
db.generate_mapping(create_tables=True)

with db_session:
    pers = Person(name='John')
    passport = Passport(number='1234', person=pers)

with db_session:
    p = Person.get(name='John')
    p.delete()

####################################################################

Output:

>>> 
GET CONNECTION FROM THE LOCAL POOL
PRAGMA foreign_keys = false
BEGIN IMMEDIATE TRANSACTION
CREATE TABLE "Person" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "name" TEXT NOT NULL
)

CREATE TABLE "Passport" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "number" TEXT NOT NULL,
  "person" INTEGER NOT NULL REFERENCES "Person" ("id")
)

CREATE INDEX "idx_passport__person" ON "Passport" ("person")

SELECT "Passport"."id", "Passport"."number", "Passport"."person"
FROM "Passport" "Passport"
WHERE 0 = 1

SELECT "Person"."id", "Person"."name"
FROM "Person" "Person"
WHERE 0 = 1

COMMIT
PRAGMA foreign_keys = true
CLOSE CONNECTION
GET CONNECTION FROM THE LOCAL POOL
BEGIN IMMEDIATE TRANSACTION
INSERT INTO "Person" ("name") VALUES (?)
[u'John']

INSERT INTO "Passport" ("number", "person") VALUES (?, ?)
[u'1234', 1]

COMMIT
RELEASE CONNECTION
GET CONNECTION FROM THE LOCAL POOL
SWITCH TO AUTOCOMMIT MODE
SELECT "id", "name"
FROM "Person"
WHERE "name" = ?
LIMIT 2
[u'John']

SELECT "id", "number", "person"
FROM "Passport"
WHERE "person" = ?
LIMIT 2
[1]

BEGIN IMMEDIATE TRANSACTION
DELETE FROM "Person"
WHERE "id" = ?
  AND "name" = ?
[1, u'John']

ROLLBACK
RELEASE CONNECTION

Traceback (most recent call last):
  File "/mnt/hgfs/share/code_repo/PassCon/Server/cascade_delete_issue.py", line 22, in <module>
    p.delete()
  File "/usr/local/lib/python2.7/site-packages/pony/orm/core.py", line 388, in __exit__
    commit()
  File "<auto generated wrapper of commit() function>", line 2, in commit
  File "/usr/local/lib/python2.7/site-packages/pony/utils.py", line 88, in cut_traceback
    return func(*args, **kwargs)
  File "/usr/local/lib/python2.7/site-packages/pony/orm/core.py", line 284, in commit
    transact_reraise(CommitException, exceptions)
  File "/usr/local/lib/python2.7/site-packages/pony/orm/core.py", line 268, in transact_reraise
    reraise(exc_class, new_exc, tb)
  File "/usr/local/lib/python2.7/site-packages/pony/orm/core.py", line 278, in commit
    try: primary_cache.commit()
  File "/usr/local/lib/python2.7/site-packages/pony/orm/core.py", line 965, in commit
    if cache.modified: cache.flush()
  File "/usr/local/lib/python2.7/site-packages/pony/orm/core.py", line 1030, in flush
    if obj is not None: obj._save_()
  File "/usr/local/lib/python2.7/site-packages/pony/orm/core.py", line 4146, in _save_
    elif status == 'marked_to_delete': obj._save_deleted_()
  File "/usr/local/lib/python2.7/site-packages/pony/orm/core.py", line 4131, in _save_deleted_
    database._exec_sql(sql, arguments)
  File "/usr/local/lib/python2.7/site-packages/pony/orm/core.py", line 599, in _exec_sql
    connection = cache.reconnect(e)
  File "/usr/local/lib/python2.7/site-packages/pony/orm/core.py", line 932, in reconnect
    if not provider.should_reconnect(exc): reraise(*sys.exc_info())
  File "/usr/local/lib/python2.7/site-packages/pony/orm/core.py", line 597, in _exec_sql
    try: new_id = provider.execute(cursor, sql, arguments, returning_id)
  File "<auto generated wrapper of execute() function>", line 2, in execute
  File "/usr/local/lib/python2.7/site-packages/pony/orm/dbapiprovider.py", line 52, in wrap_dbapi_exceptions
    except dbapi_module.IntegrityError as e: raise IntegrityError(e)
CommitException: IntegrityError: foreign key constraint failed
>>> 
@kozlovsky
Contributor

Thanks for the reporting! This is a bug, I'll try to fix it today.

@kozlovsky kozlovsky added the bug label Jul 14, 2015
@kozlovsky kozlovsky self-assigned this Jul 14, 2015
@kozlovsky kozlovsky added this to the 0.6.2 milestone Jul 14, 2015
@kozlovsky kozlovsky closed this in 3cf7530 Jul 27, 2015
@kozlovsky kozlovsky added a commit that referenced this issue Jan 11, 2016
@kozlovsky kozlovsky Pony ORM Release 0.6.2 (2015-01-11)
The documentation was moved from this repo to a separate one at https://github.com/ponyorm/pony-doc
The compiled version can be found at https://docs.ponyorm.com

# New features

* Python 3.5 support
* #132, #145: raw_sql() function was added
* #126: Ability to use @db_session with generator functions
* #116: Add support to select by UUID
* Ability to get string SQL statement using the Query.get_sql() method
* New function delete(gen) and Query.delete(bulk=False)
* Now it is possible to override Entity.__init__() and declare custom entity methods

# Backward incompatible changes

* Normalizing table names for symmetric relationships
* Autostrip - automatically remove leading and trailing characters

# Bugfixes

* #87: Pony fails with pymysql installed as MySQLdb
* #118: Pony should reconnect if previous connection was created before process was forked
* #121: Unable to update value of unique attribute
* #122: AssertionError when changing part of a composite key
* #127: a workaround for incorrect pysqlite locking behavior
* #136: Cascade delete does not work correctly for one-to-one relationships
* #141, #143: remove restriction on adding new methods to entities
* #142: Entity.select_random() AssertionError
* #147: Add 'atom_expr' symbol handling for Python 3.5 grammar
ab09f64
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment