Improvement request: Avoiding orphans in the case of deletion of parent table’s record #15
Description
Assuming that your Sqlite db is growing too big and you like to delete irrelevant records. We are currently having a db that has size of almost 600G.
Of course, the simplest way to solve this is to delete whole db and let DbBot create new one for you. But this way the fetching of history data is difficult, since after delete even yesterday is gone.
Then, if you like to be bit clever, you may like to delete all records from the robot_results.db that are older than certain date and time. Or make a clever script that leaves only those records of test runs that are related to some delivered SW and deletes everything else.
As you know, deleting record(s) is trivial: DELETE FROM test_runs WHERE test_runs.finished_at < '2014-08-08 08:00:00.00000'. So that’s it, or is it after all? No, because this isn’t the whole story, since doing this (with current robot_results.db) leaves bunch of orphan records that are not deleted /1/. And sadly for us, this is correct behavior with current DbBot setup.
Solution to this would be that, if Foreign Key Constraints could have clause ‘ON DELETE CASCADE’. Then possible delete action of a parent table’s record is propagated, and no orphan will be left.
So I suggest that DbBot would have this ‘ON DELETE CASCADE’ clause in every Foreign Key. I can’t imagine any user case in where you want to delete one record from the parent table, and want to leave the orphans. So this should be a safe move.
/1/ Of course, this wouldn’t be allowed, if PRAGMA foreign_keys = ON; hasn’t been entered, but this isn't a solution either.