Re: [elixir] [RE-OPENED] Foreign key constraints not fired ?
chaouche yacine
There's one problem : the error I'm getting is not on the foreign key constraint it's on the NOT NULL contraint.
Now there's a weired thing happening :
I define my models like this :
<code>
class BaseModel(Entity):
using_options(abstract=True)
repr_attr = "name"
def __repr__(self):
"""
"""
return "<%s '%s' id='%s'>" % (self.__class__.__name__,getattr(self,self.repr_attr,id(self)),self.id)
class Country(BaseModel):
"""
"""
using_options(tablename = "countries")
name = Field(Unicode,nullable=False)
cities = OneToMany("City")
class City(BaseModel):
"""
"""
using_options(tablename = "cities")
name = Field(Unicode)
country = ManyToOne("Country",ondelete="restrict")
</code>
The table definition that elixir generates is correct : my foreign key contraint is there :
<sql>
CREATE TABLE cities (
id SERIAL NOT NULL,
name VARCHAR,
country_id INTEGER,
PRIMARY KEY (id),
CONSTRAINT cities_country_id_fk FOREIGN KEY(country_id) REFERENCES countries (id) ON DELETE restrict
)
</sql>
Here's the rest of the script
<code>
metadata.bind = "postgres://coriolis:coriolis234access@localhost/testdb"
metadata.bind.echo = True
setup_all()
drop_all()
create_all()
session.commit()
# just to make them global
algeria = None
algiers = None
def create():
global algeria,algiers
algeria = Country(name=u"Algeria")
algiers = City(name=u"Algiers",country=algeria)
session.commit()
def delete():
global algeria,algiers
# THIS DOSEN'T BREAK which is NOT what we want
algeria.delete()
session.commit()
create()
#delete()
session.commit()
</code>
First, I comment the delete() function call to let the created instances there on the database. Then I go on phppgadmin and tries to delete the country but get an error (table cities still referencing etc.), which is the desired behaviour.
Now i re-run the script and uncomment the delete function call like this :
<code>
metadata.bind = "postgres://coriolis:coriolis234access@localhost/testdb"
metadata.bind.echo = True
setup_all()
drop_all()
create_all()
session.commit()
# just to make them global
algeria = None
algiers = None
def create():
global algeria,algiers
algeria = Country(name=u"Algeria")
algiers = City(name=u"Algiers",country=algeria)
session.commit()
def delete():
global algeria,algiers
# THIS DOSEN'T BREAK which is NOT what we want
algeria.delete()
session.commit()
create()
delete()
session.commit()
</code>
But the script dosen't raise the exception.
Now a solution proposed by Diez was to add a required=True on the country relation, which kind of works, but is semantically acceptable. Furthermore, on some of my models, certain relations are not required, so setting a required=True dosen't make sens I guess, but I still want them to prevent a deletion on the other side of the relation if they are still pointing to it.
What can I do to impement this ? shouldn't ondelete="restrict" be enough ?
Thank you for your precious help.
Y.Chaouche
Diez B. Roggisch
Am 01.07.2010 um 11:36 schrieb chaouche yacine:
> Hi list,
>
> There's one problem : the error I'm getting is not on the foreign
> key constraint it's on the NOT NULL contraint.
This is because SA issues an update to the city, setting the country
to NULL. You can see that if you'd use my code that set the bind's
echo property to True.
Now honestly I don't know how to fix this, but this might help:
http://www.sqlalchemy.org/docs/05/mappers.html#using-passive-deletes
Passing passive_deletes=True to the OneToMany-relation might solve the
issue of trying to update child-relations.
Diez
chaouche yacine
Should I consider this a bug and open a ticket for it ?
Diez B. Roggisch
I'd say no. It is a documented behavior. Just not the one you expected.
Diez
Gaetan de Menten
passive_deletes="all" should do what you want (seems to be only
available in SA 0.6+). I have never used it myself and I must admit I
am quite astonished as to how hard it was (how deep in the docs I had
to dig) to find how to support this (IMO) trivial setup.
If that does not work for you, please ask on SQLAlchemy's mailing
list, or open a bug in their tracker, as this problem is not specific
to Elixir.
Hope it helps,
--
Gaëtan de Menten
Bidossessi Sodonon
The operation was a migration from SA to Elixir, and we noticed the
regression when we ran our CRUD and parent elements got delete,
ignoring the ondelete restriction of their children.
In SA, using ondelete="restrict" on any relation works as expected:
an error is raised when you try to commit a deletion that breaks a
forignKeyConstraint. (SA raises IntegrityError).
Now I'm assuming that the ondelete syntax in Elixir is inherited from
SA, or a wrapper for the SA methods.
However, in Elixir's case, No Exception Is Raised. On the contrary,
elixir goes ahead and BYPASSES the foreignkey restrictions that ARE
PRESENT IN THE DB, and deletes the "parent" object forcibly, stranding
dependencies, and generally messing up the DB.
Once again, the expected behaivour is "IntegrityError: you tried to
delete an object that is still being referenced in other tables,
etc..." and NOT "IntegrityError, you tried to set a foreignkey
constraint to None". We do NOT want to update child relations, we want
to BLOCK DELETION IN THE FIRST PLACE.
So this is going in Elixir's bug tracker and NOT SA's because it IS
elixir specific. SA handles it without a hiccup.
On Jul 12, 8:44 am, Gaetan de Menten <gdemen...@gmail.com> wrote:
Bidossessi Sodonon
i found the "passive_deletes" option that was missing from the whole
discussion, and curiously not needed on our SA models to raise the
exception.
Your documentation, (and SA's as well) only illustrate on the cascade
side.
reference found here:
http://www.sqlalchemy.org/docs/reference/orm/mapping.html?highlight=relationship#sqlalchemy.orm.relationship
it could be said that this is how deletion of the parent on a
OneToMany relation is handled :
1- ondelete arguments on the ManyToOne side really only create
referential integrity rules for that table in capable databases
2- cascade="all, merge, etc" on the OneToMany side enforces the chosen
option on the application side
3- the default behaviour when a parent is deleted is SET NULL on all
children
4-passive_deletes, if present and set to true/all on the OneToMany
side, hands over referential integrity enforcement to the database
dialect, implying either ondelete present in the schema, or a rule
present in the database (or both)
5-with passive_deletes set to True on the OneToMany side, when a
parent is deleted, the ondelete in the corresponding ManyToOne
declaration or its table WILL be enforced (an exception will be raised
if ondelete=="RESTRICT") for capable databases (which excludes SQLite
<= 3.0 and MySQL ISAM)
6-with passive_deletes set to all, SET NULL on children will also be
disabled.
Issue solved.
runnable code to test
<code>
from elixir import *
metadata.bind = 'postgresql://localhost/testdb'
metadata.bind.echo = True
setup_all()
create_all()
class Person(Entity):
name = Field(Unicode(60))
dogs = OneToMany("Dog", passive_deletes="all")
def __repr__(self):
return '<Person "%s">' % self.name
class Dog(Entity):
name = Field(Unicode(60))
master = ManyToOne("Person", ondelete="RESTRICT")
def __repr__(self):
return '<Dog "%s">' % self.name
setup_all()
create_all()
john = Person(name=u"John")
spike = Dog(name=u"Spike")
pluto = Dog(name=u"Pluto")
john.dogs.append(spike)
john.dogs.append(pluto)
print john.dogs
session.delete(john)
# below should break with IntegrityError
try:
session.flush()
except IntegrityError, e:
print "error was caught here:", "-"*9,"\n", e
</code>
Diez B. Roggisch
Am 14.07.2010 um 03:29 schrieb Bidossessi Sodonon:
> After lots of inverstigation,
>
> i found the "passive_deletes" option that was missing from the whole
> discussion, and curiously not needed on our SA models to raise the
> exception.
Missing from this discussion? It was the first answer of me in the re-
opened thread here, and already solved the issue for the OP two weeks
ago...
Diez
Bidossessi Sodonon
I had a look through our code for passive_deletes and found nothing,
so i was leaving a marker for chaouche.
the issue was still not solved. Chaouche works with me, so i should
know.
Bidossessi Sodonon
I had a look through our code for passive_deletes and found nothing,
so i was leaving a marker for chaouche.
the issue was still not solved. Chaouche works with me, so i should
know.