From: Daevid Vincent
Date: July 12 2006 7:26am
Subject: [ANNOUNCE] dumpster :: dumps out all related records in a mySQL InnoDB database
List-Archive: http://lists.mysql.com/mysql/199848
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hey all. Well I just finished my first version of a little tool I have
affectionately dubbed "dumpster".=20

I do use my own SQL wrapper functions, but they should map fairly =
cleanly to
a search and replace for the stock PHP mysql_*() ones, or your own ones. =


Mad props to Peter Brawley [peter.brawley@stripped] 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.

=D0=C65=CF=D0=20

--------------

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.=20

  (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
 =20
  --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",=20
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.=20
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