On Mon, Mar 28, 2016 at 11:21 PM, setysvar setys...@gmail.com [firebird-support] <firebird-support@yahoogroups.com> wrote:
> > > >Hi, > > Hi Rajiv! > > >How can i list tables of a database in order of dependency ie > > > >TABLE_1 (no foreign key references) > >TABLE_2 (foreign key references, if any, in TABLE_1 ) > >TABLE_3 (foreign key references, if any, in TABLE_1 / TABLE_2 ) > >TABLE_4 (foreign key references, if any, in TABLE_1 / TABLE_2 / TABLE_3) > > Ideally, this would be how you designed your database, i.e. have a > forced order of your tables and refuse foreign keys pointing to any > tables further down the list. I'm not certain whether or not things will > want to line up as nicely as you want them in an existing database. > > Nevertheless, I suggest you create a new table: > > CREATE TABLE TABLES_ORDERED > ( > ID INTEGER NOT NULL, > RDB$RELATION_NAME CHAR( 31 ), > CONSTRAINT PK_TABLES_ORDERED PRIMARY KEY ( ID ) > ); > > CREATE GENERATOR TABLES_ORDERED_GEN; > SET TERM ^^ ; > > CREATE TRIGGER TABLES_ORDERED_ID FOR TABLES_ORDERED ACTIVE BEFORE INSERT > POSITION 0 AS > begin > if ( ( new.ID is null ) or ( new.ID = 0 ) ) then > new.ID = gen_id( TABLES_ORDERED_GEN, 1 ); > end > ^^ > SET TERM ; ^^ > > commit; > > fill it with those tables that have no foreign key (start by running > DELETE FROM TABLES_ORDERED if the table is not empty): > > INSERT INTO TABLES_ORDERED(RDB$RELATION_NAME) > SELECT RDB$RELATION_NAME FROM RDB$RELATIONS r > WHERE RDB$SYSTEM_FLAG=0 > AND NOT EXISTS(SELECT * FROM RDB$INDICES i > WHERE r.RDB$RELATION_NAME=i.RDB$RELATION_NAME > AND i.RDB$FOREIGN_KEY IS NOT NULL); > > and then, repeatedly, try: > > INSERT INTO TABLES_ORDERED(RDB$RELATION_NAME) > SELECT RDB$RELATION_NAME FROM RDB$RELATIONS r > WHERE RDB$SYSTEM_FLAG=0 > AND NOT EXISTS(SELECT * FROM TABLES_ORDERED o /*Ignore tables already > inserted*/ > WHERE r.RDB$RELATION_NAME = o.RDB$RELATION_NAME) > AND NOT EXISTS(SELECT * FROM RDB$INDICES i /*Only insert tables whose > foreign key tables are inserted already*/ > JOIN RDB$INDICES i2 ON i.RDB$FOREIGN_KEY = > i2.RDB$INDEX_NAME > AND r.RDB$RELATION_NAME <> > i2.RDB$RELATION_NAME /*Omit this line if you don't want to include > tables pointing to themselves*/ > LEFT JOIN TABLES_ORDERED o ON i2.RDB$RELATION_NAME = > o.RDB$RELATION_NAME > WHERE r.RDB$RELATION_NAME=i.RDB$RELATION_NAME > AND i.RDB$FOREIGN_KEY IS NOT NULL > AND o.ID IS NULL); > > When you've done this enough times for no further table to be inserted, > you can get your ordered list by simply running: > > SELECT RDB$RELATION_NAME > FROM TABLES_ORDERED > ORDER BY ID > > If you find that the list lacks two or more tables, investigate those > remaining tables - maybe you have some circular dependencies? > All Tables got added to TABLES_ORDERED > > Note that I've never tried doing anything similar myself (I don't even > think I've written a query that uses the RDB$FOREIGN_KEY before), and > that there may well be errors in what I've written above. > > There were no errors, Set. Thanks a lot. This helped ! Regards, Rajiv > HTH, > Set >
