Hey all. Well I just finished my first version of a little tool I have affectionately dubbed "dumpster". I do use my own SQL wrapper functions, but they should map fairly cleanly to a search and replace for the stock mysql_*() ones, or your own ones. Mad props to Peter Brawley [peter.brawley@xxxxxxxxxxxxx] for the initial SQL statement to get the FK constraints. If someone can point me at how to get the information I need to fix that bug, that'd be swell. ÐÆ5ÏÐ -------------- This script attempts to generate all the SQL statements needed to archive a snapshot of a single 'thing'. For example, it can harvest all records related to a given user. (This only works for InnoDB tables that utilize proper FK constraints) Usage: ./dumpster.php --database mydb --table users --id 1 [--delete] > user_1.sql Then later simply "mysql --force -u root mydb < user_1.sql" to put the 'user' back --help, -help, -h, or -? options, to get this help. --database the name of the database to use. --table the name of the table to use in the database --id the ID that joins all these tables together in the database --FKonly only show the Foreign Key list and exit. --debug to turn on output debugging. --version to return the version of this file. --delete deletes the record as it is output (in 'debug' mode this outputs only, no action). KNOWN ISSUE: if a column is defined as "ON DELETE SET NULL", then there's a better than average chance that it might get NULL'd by a DELETE before it, therefore we won't be able to clean up some records properly as their FK ID is now NULL. catch22. :-| There is probably a way to find out which FKs have this particular constraint action and then we could save off their PK in an array and loop through them at the end I think? http://daevid.com/examples/dumpster.tgz -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
