Firebird Reihenfolge der Tabellen bei Insert mit Foreignkey

[Kostas]

Die Version ohne Views.

Code:

create or alter procedure ORDER_ALLTABLES
as
declare variable COUNTNEWINSERTS integer;
declare variable NEWINSERTS integer;
begin
 /* http://firebird-support.yahoogroups.narkive.com/XPYLG1la/odp-firebird-support-listing-table-of-database-in-order-of-dependency */
  delete from tables_ordered;

  INSERT INTO TABLES_ORDERED(RDB$RELATION_NAME)
          SELECT RDB$RELATION_NAME FROM RDB$RELATIONS r
          WHERE RDB$SYSTEM_FLAG=0
          AND RDB$RELATION_TYPE = 0 /* nur Tabellen */
          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);

  select count(*)
  from tables_ordered
  into :countNewInserts;
  newInserts = 0;

  while (1=1) do
  begin
      INSERT INTO TABLES_ORDERED(RDB$RELATION_NAME)
              SELECT RDB$RELATION_NAME
                FROM RDB$RELATIONS r
               WHERE RDB$SYSTEM_FLAG=0
               AND RDB$RELATION_TYPE = 0 /* nur Tabellen */
               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);

      select count(*)
      from tables_ordered
      into :newInserts;

      if (countNewInserts <> newInserts)then
      begin
        countNewInserts = newInserts;
      end else break;
  end


        
end