On 10 May 2010, at 5:32pm, Patrick Earl wrote: > 1. Find all direct and indirect dependants of the table being modified. > 2. Create temporary tables for all of these. > 3. Copy the data from the main tables into these temporary tables. > 3a. If no circular dependencies, do a topological sort on the tables > to get the correct insertion order. > 3b. If circular dependencies, either use deferred constraints or come > up with a sophisticated algorithm to reinsert the original data (needs > to take into account not null columns with circular references > present). > 4. Drop all the original tables, again with similar steps to 3a and 3b. > 5. Rename all the temporary tables to their original names.
It should not be possible to have circular dependencies. Because you somehow got the data in in the first place, and /that/ wouldn't have been possible had you had circular dependencies. Part of normalising your data structure includes making sure that you haven't duplicated data. I do note that you appear to be trying to solve an extremely general case, as if you, the programmer, have no idea why your schema is the way it is. I have to warn you that if you're going to solve the general case, you are going to run into situations which are not solvable without considering individual rows of a table. For instance, consider a genealogy database with a TABLE like this: People: rowid INTEGER name TEXT sex TEXT fatherID INTEGER REFERENCES People(id) motherID INTEGER REFERENCES People(id) If the father or mother is unknown, you use NULL. You can try to populate a clone of this TABLE but unless you insert the records in the right order you're going to get errors at some points as you create the records. Alternatively you can put NULLs in all fatherID and motherID fields when you create the rows, then go back and set the right values once all the rows exist. I think the only way to solve the general solution is to forget the complicated logic in your steps and just create temp TABLEs for all the TABLEs in your database, whether you want to modify them or not. That way you can completely ignore any logic analysis, insert the data in any order you want, and rely on DEFERRABLE/DEFERRED to prevent error messages. But this gets back to an earlier point of mine: why go through any of this performance at all ? Why do you need to create temporary copies of databases only to originally delete and replace the originals ? Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
