Re: [firebird-support] Listing Table of Database in Order of Dependency

[LtColRDSChauhan rdsc1...@gmail.com [firebird-support]]

On Mon, Mar 28, 2016 at 11:21 PM, setysvar setys...@gmail.com
[firebird-support]  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
>

Re: [firebird-support] Listing Table of Database in Order of Dependency

[setysvar setys...@gmail.com [firebird-support]]

 >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?

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.

HTH,
Set

Re: [firebird-support] Listing Table of Database in Order of Dependency

[LtColRDSChauhan rdsc1...@gmail.com [firebird-support]]

On Fri, Mar 25, 2016 at 7:07 PM, liviuslivius liviusliv...@poczta.onet.pl
[firebird-support]  wrote:

>
>
> hi,
>
> why you need this?
>

I have been updating a deployed application of mine for last 8 yrs. The
database has changed from some 30 tables to 80 tables. To migrate data (i
have a .NET application of mine) i need to transfer data of tables from
least dependent onwards.

I use the following two stored procedures

SET TERM ^ ;
ALTER PROCEDURE DB_TBLS_ROWS_FK_0
RETURNS (
TBL_NAME varchar(50),
FKS smallint )
AS
BEGIN
for SELECT DISTINCT RDB$RELATION_NAME FROM RDB$RELATION_FIELDS WHERE
RDB$SYSTEM_FLAG=0 into :TBL_NAME
do begin
SELECT count(RDB$INDEX_NAME) FROM RDB$INDICES WHERE
RDB$RELATION_NAME= :TBL_NAME AND (RDB$FOREIGN_KEY IS NOT NULL) into :FKS;
suspend;
end
END^
SET TERM ; ^

GRANT EXECUTE
 ON PROCEDURE DB_TBLS_ROWS_FK_0 TO  SYSDBA;


SET TERM ^ ;
ALTER PROCEDURE DB_TBLS_ROWS_FK (
INCL_FKS smallint DEFAULT 1 )
RETURNS (
TBL_NAME varchar(50),
FKS smallint,
PK varchar(50),
TBL_FIELDS bigint,
TBL_ROWS bigint )
AS
declare variable STMT_TO_EXEC varchar(80);
BEGIN
for SELECT p.TBL_NAME, p.FKS FROM DB_TBLS_ROWS_FK_0  p order by p.FKS
into :TBL_NAME, :FKS
do begin
TBL_ROWS = null;
TBL_FIELDS = null;
STMT_TO_EXEC = 'select count(*) as nr_of_rows from ' || :TBL_NAME;
execute statement STMT_TO_EXEC INTO :TBL_ROWS;
select count(*) from rdb$relation_fields flds where
flds.RDB$RELATION_NAME = :TBL_NAME into :TBL_FIELDS;
suspend;

if(0 <> :INCL_FKS) then begin
for SELECT r.RDB$FOREIGN_KEY FROM RDB$INDICES r WHERE
r.RDB$RELATION_NAME= :TBL_NAME AND (r.RDB$FOREIGN_KEY IS NOT NULL) into :PK
do begin
/*TBL_NAME = null;*/
FKS = null;
suspend;
end
PK = null;
end
end
END^
SET TERM ; ^

GRANT EXECUTE
 ON PROCEDURE DB_TBLS_ROWS_FK TO  SYSDBA;


i ask because it smells me wrong direction of doing something.
>
> regards,
> Karol Bieniaszewski
>
>
>
>  Oryginalna wiadomość 
> Od: "LtColRDSChauhan rdsc1...@gmail.com [firebird-support]" <
> firebird-support@yahoogroups.com>
> Data: 25.03.2016 11:36 (GMT+01:00)
> Do: firebird-support@yahoogroups.com
> Temat: [firebird-support] Listing Table of Database in Order of Dependency
>
>
>
> Hi,
>
> 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)
>
> 
>
> Thanks and Regards,
>
> Rajiv
>
> 
>



-- 
  Regards,
  Lt Col (Retd) Rajiv D.S. Chauhan
  in.linkedin.com/in/ltcolrdschauhan
_