Sounds like you want to walk tables in order of their fk dependencies
- a topological ordering. You might want to take a look at SQLAlchemy
which has some methods to do just this in sqlalchemy.sql.util:
def sort_tables(tables, reverse=False):
"""sort a collection of Table objects in order of their
foreign-key dependency."""
~Andrew
On Tue, Feb 3, 2009 at 3:40 PM, Philip Pemberton <[email protected]> wrote:
> Hi,
> First of all, I apologise in advance for any mind-altering, or
> headache-inducing effects this question may have. I've spent the past two
> days trying to figure it out, and all I've got to show for it is a
> mostly-working recursive depth-first-search routine and an empty packet of
> painkillers.
>
> MySQL version: 5.0.67-0ubuntu6
>
> I'm trying to write a code generator (in Python) that reads in a MySQL
> database, enumerates all the tables, then produces INSERT, DELETE and UPDATE
> code in PHP. The INSERT and UPDATE code generation was fairly easy, and
> works quite well. What I'm having trouble with is the DELETE code generator
> -- more specifically, resolving foreign key references.
>
> Basically, what I have is a tree built in memory, so I can go:
> tableinfo['thetable']['fieldname']['refs']
> And get a complete list of all the tables (and the fields within that table)
> that reference 'fieldname' in 'thetable'.
>
> What I want is an answer to the question: "If all my foreign keys were set
> to 'ON DELETE CASCADE', what would I need to do to delete row 'X' in table
> 'Y' without violating any foreign key constraints?"
>
>
>
> Here's an example. Let's say I've got these tables:
>
> CREATE TABLE `Manufacturers` (
> `idManufacturer` int(11) NOT NULL auto_increment,
> `name` varchar(255) NOT NULL,
> PRIMARY KEY (`idManufacturer`)
> ) ENGINE=InnoDB
>
> CREATE TABLE `Parts` (
> `idPart` int(11) NOT NULL auto_increment,
> `idManufacturer` int(11) NOT NULL,
> `partnumber` int(11) NOT NULL,
> PRIMARY KEY (`idPart`),
> KEY `Parts_idManufacturer_FKIndex` (`idManufacturer`),
> CONSTRAINT `Parts_ibfk_1` FOREIGN KEY (`idManufacturer`) REFERENCES
> `Manufacturers` (`idManufacturer`)
> ) ENGINE=InnoDB
>
> And my database contains:
> Manufacturers:
> idManufacturer name
> 123 Any Company Inc.
>
> Parts:
> idPart idManufacturer partnumber
> 1 123 12345
>
> Now, let's say I want to do this:
> DELETE FROM Manufacturers WHERE idManufacturer=123
>
> Because I have a part that references Manufacturer #123, I have to do this
> instead:
> DELETE FROM Parts WHERE idManufacturer=123
> DELETE FROM Manufacturer WHERE idManufacturer=123
>
>
> What I want is something I can feed the table definitions to, and the name
> of the table I want to delete a row from (in this case 'Manufacturers'), and
> generate a list of the DELETE commands that would allow me to delete that
> row while enforcing FK dependencies.
>
> I figure this is going to have to work something like mathematical
> expression evaluation -- build up a list of dependencies, then deal with the
> deepest dependency first. Catch being I can't see an obvious way to deal
> with generating the necessary DELETE commands without having to write a
> massive "if recursion_level = 0 then generate_a_straight_delete else if
> recursion_level = 1 then..." statement...
>
> Thanks,
> --
> Phil.
> [email protected]
> http://www.philpem.me.uk/
> If mail bounces, replace "08" with the last two digits of the current year.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[email protected]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[email protected]
