agateau/yokadi — pull request #44 by agateau (2014-10-02 08:41:37+00:00)
Title: Mark all 1-n database relations as cascade deletes

Contrary to SQLObject, SQLAlchemy defaults to not cascading deletes. This means for example that when one deletes a task with associated keywords, the relevant rows in the TaskKeyword table are not deleted, their `task_id` field is set to `null` instead. I discovered this after a crash in `k_list`, which does not expect the `task_id` field to be `null`.

This branch marks all 1-n relations as cascade deletes and add unit tests to check they work. While adding the test for TaskLock, I realized we might want to prevent deleting a task which is locked, but that is left as an exercise for another day :)

That bug means we need to tell users of the master branch to clean their databases, something like this:

delete from task_keyword where task_id is null;
delete from project_keyword where project_id is null;
delete from task where project_id is null;
delete from task_lock where task_id is null;
delete from recurrence where id not in (select distinct recurrence_id from task);

I also realized Recurrence does not have a `task_id` field. Such a field is not strictly necessary since the relation between Task and Recurrence is 1 -> 0,1, but it makes it a bit less handy to remove stale entries (see last line of my sql snippet). Do we want to fix this?