Warning: Can't synchronize with repository "(default)" (Unsupported version control system "svn": No module named svn). Look in the Trac log for more information.

Ticket #1364 (closed enhancement: fixed)

Opened 12 years ago

Last modified 12 years ago

[PATCH] Cascade on deleting/updating users and groups

Reported by: chrisz Owned by: anonymous
Priority: normal Milestone: 1.0.2
Component: SQLAlchemy Version: trunk
Severity: normal Keywords:
Cc:

Description

The patch creates the user and group tables with ON UPDATE CASCADE and ON DELETE CASCADE when SQLAlchemy is used. This makes deleting users and groups or changing user and group ids much easier since you don't need to care for the intermediate tables.

Attachments

model.patch Download (1.9 KB) - added by chrisz 12 years ago.
Add on update/delete cascade to the model template

Change History

Changed 12 years ago by chrisz

Add on update/delete cascade to the model template

comment:1 Changed 12 years ago by faide

Hi,

I use SQLAlchemy and identity on a dev project with sqlite at the moment.

In my config the intermediate tables are correctly cleaned-up or updated when a user/group/permission is deleted or changed from group.

Could you give some more info on what this patch will add in terms of functionality ? Maybe this is needed specifically for Mysql ?

comment:2 Changed 12 years ago by chrisz

In fact I was using PostgreSQL.

SQLite and even MySQL (without InnoDB) don't enforce relational constraints, so they won't complain when deleting users, but they will not clean up automatically, ruining the referential integrity. If you use a "real" database like PostgreSQL or Oracle, then you'll not be allowed to delete a user if he is still in a group, unless you use "on delete cascade". The referential integrity is always ensured.

Please try the following with both SQLite (pysqlite2) and PostgreSQL:

tg-admin sql create
tg-admin shell
>>> u, g = User(), Group()
>>> u.groups.append(g)
>>> session.flush()
>>> user_group_table.select().execute().fetchall()
[(1, 1)]
>>> u.delete()
>>> session.flush()

With PostgreSQL you'll get an IntegrityError? at this point. SQLite will allow the delete, but not clean up the intermediate table:

>>> user_group_table.select().execute().fetchall()
[(1, 1)]

If you add the "on delete" clause, then SQLite will still behave the same (silently ignoring it), but PostgreSQL will clean up the intermediate table:

>>> user_group_table.select().execute().fetchall()
[]

comment:3 Changed 12 years ago by faide

My point is exactly that:

Using sqlite I did in tg-admin shell:

u, g = User(), Group() u.groups.append(g) session.flush() user_group_table.select().execute().fetchall()

[(1, 1)]

u.delete() session.flush()

and looking into the "user_group" table with a sqlite shell I could see that it was cleaned and the user was no more associated with the group. Maybe SQLAlchemy did this for me magically but I did not have to clean the intermediate table thus my question.

I am using sqlalchemy 0.3.6 at the moment.

As per your remark I myself prefer to use _real_ databases for production use but for some part of the dev it is interesting to have small setups with sqlite :)

comment:4 Changed 12 years ago by faide

I tested the same thing on Postgresql 8.2 on Ubuntu Linux without any problems.

Could you find out the version number of SQLAlchemy you are using so we can see if it comes from this?

comment:5 Changed 12 years ago by chrisz

You're right. I must have had an old SQLAlchemy version installed (not sure which, it wasn't installed as an egg and I did not find a version number in the package). I installed the current 0.3.6 version and checked again, and it works like you say. Seems SQLAlchemy cascades automagically when you set up a mapping using the "secondary" argument, which actually makes a lot of sense. I assume the updates are cascaded as well (haven't tested it).

So the "on delete/update" is not really necessary. The only benefit you get is when you're editing the user tables directly, e.g. using the pgAdmin tool, so it may still make some sense.

comment:6 Changed 12 years ago by alberto

Ok, so lets apply it so real databases have a more precise schema.

Alberto

comment:7 Changed 12 years ago by faide

  • Status changed from new to closed
  • Resolution set to fixed

Committed in 1.0 (rev 2891) and trunk (rev 2892). Thanks Chris!

Note: See TracTickets for help on using tickets.