phrrn...@googlemail.com wrote: > > Then I must have a bug in the FK introspection. Which unit tests would > you suggest getting running first? Is there one that specifically > tests foreign key stuff?
the tests in engine/reflection.py should do a basic workup of that feature. > > pjjH > > > On Mar 13, 3:41 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote: >> phrrn...@googlemail.com wrote: >> >> > Hi Mike, >> > the situation I am encountering is when the other table is not part of >> > the metadata collection i.e. SQLAlchemy doesn't know anything about >> > it. It looks like the unit-tests enumerate the tables by calling >> > table_names() which causes has_table() and reflecttable() to be called >> > in turn. There doesn't appear to be a way of expressing that a table >> > is referenced by a foreign key constraint .. from what I can see, the >> > various dialects implementation of reflecttable check to see if this >> > table references other tables. >> >> I'm not familiar with any test that relies upon that method - every unit >> test within SQLA deals with a single MetaData object which contains all >> tables, and the foreign key references between those tables are known. >> >> There is an option called "--dropfirst" which does do the table_names() >> thing you mention, but that option is only a convenience measure to >> rerun >> the tests on a database that still has tables leftover from a previously >> failed run. It also makes usage of foreign keys to drop tables in the >> correct order. >> >> Any foreign key constraint is represented in a Table object using the >> ForeignKey() or ForeignKeyConstraint() object. When tables are loaded >> via >> reflecttable(), the tables are all loaded into a single MetaData object, >> and the foreign keys are reflected into ForeignKey objects, and the drop >> in order of dependency works in all cases. So I don't know what you >> mean >> by "there doesn't appear to be a way of expressing that a table is >> referenced by a foreign key constraint". >> >> >> >> > At the moment, I am hacking up my own visit_tables() in the >> > SchemaDropper and putting in an explicit check to see if I am targeted >> > by any FKs .. if so, I will do an ALTER TABLE on the other tables to >> > remove the FK constraint to me. >> >> > pjjH >> >> > On Mar 13, 2:41 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote: >> >> SQLAlchemy normally drops tables in order of foreign key dependency >> so >> >> that there's no need for ALTER. in the case that two tables have a >> >> mutual >> >> foreign key dependency, one of the ForeignKey objects has the >> >> "use_alter=True" flag set so that just the one FK gets dropped first >> via >> >> ALTER. >> >> >> phrrn...@googlemail.com wrote: >> >> >> > I am encountering a problem with getting the unit tests to run on >> >> > Sybase because of cascades of errors originating from failure to >> drop >> >> > a table that is referenced by a FK constraint in another table. >> When >> >> > attempting to drop the people table, I need the >> SybaseSQLSchemaDropper >> >> > to emit SQL like this which first does an ALTER TABLE to remove FK >> >> > constraints from all tables which reference people: >> >> >> > 344:1> ALTER TABLE managers DROP CONSTRAINT managers_1466289798 >> >> > 344:2> go >> >> > 345:1> drop table people >> >> > 345:2> go >> >> > 346:1> >> >> >> > How should one deal with the situation when the referring tables >> are >> >> > not part of the same metadata collection? >> >> >> > pjjH >> >> >> > 341:2> sp__revtable people >> >> > 341:3> go >> >> > -- Table_DDL >> >> >> > ---------------------------------------------------------------------- >> >> > CREATE TABLE people >> >> > ( >> >> > person_id int identity NOT NULL, >> >> > name varchar(50) NULL, >> >> > type varchar(30) NULL >> >> > ) >> >> >> > ----------------------------------------------------------------------------------------------------- >> >> > ------------------- >> >> >> > ALTER TABLE people ADD PRIMARY KEY CLUSTERED ( >> person_id) >> >> > -- FOREIGN REFERENCE >> >> >> > --------------------------------------- >> >> > -- No Indexes found in Current Database >> >> >> > (return status = 0) >> >> > 342:1> sp_helpconstraint people >> >> > 342:2> go >> >> > name >> >> > definition >> created >> >> > ------------------- >> >> > ------------------------------------------------------------- >> >> > ------------------- >> >> > managers_1466289798 managers FOREIGN KEY (person_id) REFERENCES >> >> people >> >> > (person_id) Mar 4 2009 9:12PM >> >> > people_17556708171 PRIMARY KEY INDEX ( person_id) : CLUSTERED, >> >> > FOREIGN REFERENCE Mar 4 2009 9:11PM >> >> >> > Total Number of Referential Constraints: 1 >> >> >> > Details: >> >> > -- Number of references made by this table: 0 >> >> > -- Number of references to this table: 1 >> >> > -- Number of self references to this table: 0 >> >> >> > Formula for Calculation: >> >> > Total Number of Referential Constraints >> >> > = Number of references made by this table >> >> > + Number of references made to this table >> >> > - Number of self references within this table >> >> >> > (2 rows affected, return status = 0) >> >> >> > 343:1> sp__revtable managers >> >> > 343:2> go >> >> > -- Table_DDL >> >> >> > ---------------------------------------------------------------------- >> >> > CREATE TABLE managers >> >> > ( >> >> > person_id int identity NOT NULL, >> >> > status varchar(30) NULL, >> >> > manager_name varchar(50) NULL >> >> > ) >> >> >> > ----------------------------------------------------------------------------------------------------- >> >> > ------------------- >> >> >> > ALTER TABLE managers ADD PRIMARY KEY CLUSTERED ( >> person_id) >> >> >> > constraint_desc >> >> >> > ----------------------------------------------------------------------------------------------------- >> >> > ------------------------------------------------------------------------------------------------------------- >> >> > --------------------------------------------- >> >> >> > ALTER TABLE managers ADD CONSTRAINT managers_1466289798 >> >> > FOREIGN KEY (person_id) REFERENCES people(person_id) >> >> >> > --------------------------------------- >> >> > -- No Indexes found in Current Database >> >> >> > (return status = 0) > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---