Determine Entity Order In Schema

[JimDiaz]

I needed to determine the order of operation on tables within a Sybase 12 database and couldn't find an easy solution. I came up with the following procedures and thought I might send them our for review / comment. Please feel free to use them if they prove helpful.

Jim

-- James J. Diaz
-- 2016.05.30

CREATE OR REPLACE VIEW DBA.ViewForeignKeys
  AS
  -- Include all tables without a foreign key
  SELECT Entities.Table_Id                    AS PrimaryTableId,
         Entities.Object_Id                   AS PrimaryObjectId,
         Owners.user_name                     AS PrimaryOwner,
         Entities.table_name                  AS PrimaryTableName,
         NULL                                 AS ForeignTableId,
         NULL                                 AS ForeignObjectId,
         NULL                                 AS ForeignOwner,
         NULL                                 AS ForeignTableName,
         'Bogus'                              AS LastColumn
    FROM SYS.SYSTAB     AS Entities
    JOIN SYS.SYSUSER    AS Owners     ON ( Owners.user_id   = Entities.creator  )
   WHERE table_type = 1
     AND PrimaryTableId NOT IN ( SELECT table_id FROM SYS.SYSIDX AS Indexes WHERE index_category = 2 )
  UNION ALL
  SELECT PrimaryTables.Table_Id   AS PrimaryTableId,
         PrimaryTables.Object_Id  AS PrimaryObjectId,
         PrimaryOwners.user_name  AS PrimaryOwner,
         PrimaryTables.table_name AS PrimaryTableName,
         ForeignTables.Table_Id   AS ForeignTableId,
         ForeignTables.Object_Id  AS ForeignObjectId,
         ForeignOwners.user_name  AS ForeignOwner,
         ForeignTables.table_name AS ForeignTableName,
         'Bogus'                  AS LastColumn
    FROM SYS.SYSFKEY AS ForeignKeys
    JOIN SYS.SYSIDX  AS PrimaryIndexes  ON ( PrimaryIndexes.table_id  = ForeignKeys.primary_table_id
                                        AND  PrimaryIndexes.index_id  = ForeignKeys.primary_index_id )
    JOIN SYS.SYSTAB  AS PrimaryTables   ON ( PrimaryIndexes.table_id  = PrimaryTables.table_id )
    JOIN SYS.SYSUSER AS PrimaryOwners   ON ( PrimaryOwners.user_id    = PrimaryTables.creator  )
    JOIN SYS.SYSIDX  AS ForeignIndexes  ON ( ForeignIndexes.table_id  = ForeignKeys.foreign_table_id
                                        AND  ForeignIndexes.index_id  = ForeignKeys.foreign_index_id )
    JOIN SYS.SYSTAB  AS ForeignTables   ON ( ForeignIndexes.table_id  = ForeignTables.table_id )
    JOIN SYS.SYSUSER AS ForeignOwners   ON ( ForeignOwners.user_id    = ForeignTables.creator  );

CREATE OR REPLACE PROCEDURE DBA.EntityOrder()
RESULT(
  EntityId        INTEGER,
  EntityOwner     CHAR(128),
  EntityName      CHAR(128),
  EntityOrder     INTEGER,
  ReferenceLevel  INTEGER
)
BEGIN
  DECLARE @EntityOrder    INTEGER;
  DECLARE @ReferenceLevel INTEGER;
  DECLARE @LastCount      INTEGER;
  DECLARE @ThisCount      INTEGER;

  DECLARE LOCAL TEMPORARY TABLE @Results (
    EntityId        INTEGER,
    EntityOwner     CHAR(128),
    EntityName      CHAR(128),
    EntityOrder     INTEGER,
    ReferenceLevel  INTEGER,
  ) NOT TRANSACTIONAL;

  SET @EntityOrder    = 0;
  SET @ReferenceLevel = 0;

  FOR EachPrimaryTable AS PrimaryTableCursor INSENSITIVE CURSOR FOR
    SELECT DISTINCT
           PrimaryTableId   AS @PrimaryTableId,
           PrimaryOwner     AS @PrimaryOwner,
           PrimaryTableName AS @PrimaryTableName
      FROM DBA.ViewForeignKeys
     WHERE @PrimaryOwner NOT IN ( 'DBO', 'SYS', 'rs_systabgroup')
       AND ForeignTableId IS NULL
  DO
    SET @EntityOrder = @EntityOrder + 1;
    INSERT INTO @Results( EntityId, EntityOwner, EntityName, EntityOrder, ReferenceLevel ) VALUES ( @PrimaryTableId, @PrimaryOwner, @PrimaryTableName, @EntityOrder, @ReferenceLevel );
  END FOR;

  SET @LastCount = 0;
  SELECT COUNT(*) INTO @ThisCount FROM @Results;

  WHILE @ThisCount > @LastCount LOOP
    SET @ReferenceLevel = @ReferenceLevel + 1;
    SET @LastCount = @ThisCount;
    FOR EachResult AS ResultCursor INSENSITIVE CURSOR FOR
      SELECT DISTINCT
             ForeignTableId   AS @ForeignTableId,
             ForeignOwner     AS @ForeignOwner,
             ForeignTableName AS @ForeignTableName
        FROM DBA.ViewForeignKeys
       WHERE @ForeignOwner NOT IN ( 'DBO', 'SYS', 'rs_systabgroup')
         AND PrimaryTableId IN ( SELECT EntityId FROM @Results )
         AND ForeignTableId NOT IN  ( SELECT EntityId FROM @Results )
    DO
      -- don't include those in current result set
      IF @ForeignTableId NOT IN (
        SELECT PrimaryTableId
          FROM DBA.ViewForeignKeys
         WHERE @ForeignOwner NOT IN ( 'DBO', 'SYS', 'rs_systabgroup')
           AND PrimaryTableId IN ( SELECT EntityId FROM @Results )
           AND ForeignTableId NOT IN ( SELECT EntityId FROM @Results )
           AND ForeignTableId <> @ForeignTableId )
      THEN
        SET @EntityOrder = @EntityOrder + 1;
        INSERT INTO @Results( EntityId, EntityOwner, EntityName, EntityOrder, ReferenceLevel ) VALUES ( @ForeignTableId, @ForeignOwner, @ForeignTableName, @EntityOrder, @ReferenceLevel );
      END IF;
    END FOR;
    SELECT COUNT(*) INTO @ThisCount FROM @Results;
  END LOOP;

  SELECT EntityId, EntityOwner, EntityName, EntityOrder, ReferenceLevel FROM @Results ORDER BY EntityOrder;

END;

CREATE OR REPLACE PROCEDURE DBA.DeleteOrder(
  IN @TableOwner        CHAR(128),
  IN @TableName         CHAR(128),
  IN @AttributeName     CHAR(128) DEFAULT NULL,
  IN @AttributeValue    CHAR(128) DEFAULT NULL,
  IN @QuotedIdentifier  BIT DEFAULT 0
  )
RESULT(
  EntityId        INTEGER,
  EntityOwner     CHAR(128),
  EntityName      CHAR(128),
  EntityOrder     INTEGER,
  ReferenceLevel  INTEGER,
  SQLStatement    CHAR(2048)
)
BEGIN
  DECLARE @EntityOrder    INTEGER;
  DECLARE @ReferenceLevel INTEGER;
  DECLARE @LastCount      INTEGER;
  DECLARE @ThisCount      INTEGER;
  DECLARE @SQLStatement   CHAR(2048);

  DECLARE LOCAL TEMPORARY TABLE @Results (
    EntityId        INTEGER,
    EntityOwner     CHAR(128),
    EntityName      CHAR(128),
    EntityOrder     INTEGER,
    ReferenceLevel  INTEGER,
    SQLStatement    CHAR(2048),
  ) NOT TRANSACTIONAL;

  SET @EntityOrder    = 0;
  SET @ReferenceLevel = 0;

  FOR EachPrimaryTable AS PrimaryTableCursor INSENSITIVE CURSOR FOR
    -- Get the tableid of the requested table and make sure
    -- it is foreign keyed to another table
    SELECT DISTINCT
           PrimaryTableId   AS @PrimaryTableId,
           PrimaryOwner     AS @PrimaryOwner,
           PrimaryTableName AS @PrimaryTableName
      FROM DBA.ViewForeignKeys
     WHERE PrimaryOwner     = @TableOwner
       AND PrimaryTableName = @TableName
  DO
    SET @EntityOrder = @EntityOrder + 1;

    SET @SQLStatement = STRING( 'DELETE FROM ', @PrimaryOwner, '.', @PrimaryTableName );
    IF @AttributeName IS NOT NULL THEN
      SET @SQLStatement = STRING( @SQLStatement, ' WHERE ', @AttributeName, ' = ');
      IF @AttributeValue IS NOT NULL THEN
        IF @QuotedIdentifier = 1 THEN
          SET @SQLStatement = STRING( @SQLStatement, '''', @AttributeValue, '''');
        ELSE
          SET @SQLStatement = STRING( @SQLStatement, @AttributeValue );
        END IF;
      END IF;
    END IF;
    SET @SQLStatement = STRING( @SQLStatement, '; COMMIT WORK;');

    INSERT INTO @Results( EntityId, EntityOwner, EntityName, EntityOrder, ReferenceLevel, SQLStatement ) VALUES ( @PrimaryTableId, @PrimaryOwner, @PrimaryTableName, @EntityOrder, @ReferenceLevel, @SQLStatement );
  END FOR;

  SET @LastCount = 0;
  SELECT COUNT(*) INTO @ThisCount FROM @Results;

  -- Table is not referenced may not actually exist
  IF @ThisCount = 0 THEN

    SET @SQLStatement = STRING( 'DELETE FROM ', @TableOwner, '.', @TableName );
    IF @AttributeName IS NOT NULL THEN
      SET @SQLStatement = STRING( @SQLStatement, ' WHERE ', @AttributeName, ' = ');
      IF @AttributeValue IS NOT NULL THEN
        IF @QuotedIdentifier = 1 THEN
          SET @SQLStatement = STRING( @SQLStatement, '''', @AttributeValue, '''');
        ELSE
          SET @SQLStatement = STRING( @SQLStatement, @AttributeValue );
        END IF;
      END IF;
    END IF;
    SET @SQLStatement = STRING( @SQLStatement, '; COMMIT WORK;');

    INSERT INTO @Results( EntityId, EntityOwner, EntityName, EntityOrder, ReferenceLevel, SQLStatement ) VALUES ( -1, @TableOwner, @TableName, @EntityOrder, @ReferenceLevel, @SQLStatement );

  ELSE
    WHILE @ThisCount > @LastCount LOOP
      SET @ReferenceLevel = @ReferenceLevel + 1;
      SET @LastCount = @ThisCount;
      FOR EachResult AS ResultCursor INSENSITIVE CURSOR FOR
        SELECT DISTINCT
               ForeignTableId   AS @ForeignTableId,
               ForeignOwner     AS @ForeignOwner,
               ForeignTableName AS @ForeignTableName
          FROM DBA.ViewForeignKeys
         WHERE PrimaryTableId IN ( SELECT EntityId FROM @Results )
           AND ForeignTableId NOT IN ( SELECT EntityId FROM @Results )
      DO
        -- don't include those in current result set
        IF @ForeignTableId NOT IN (
          SELECT PrimaryTableId
            FROM DBA.ViewForeignKeys
           WHERE PrimaryTableId IN ( SELECT EntityId FROM @Results )
             AND ForeignTableId NOT IN ( SELECT EntityId FROM @Results )
             AND ForeignTableId <> @ForeignTableId )
        THEN

          SET @EntityOrder = @EntityOrder + 1;

          SET @SQLStatement = STRING( 'DELETE FROM ', @ForeignOwner, '.', @ForeignTableName );
          IF @AttributeName IS NOT NULL THEN
            SET @SQLStatement = STRING( @SQLStatement, ' WHERE ', @AttributeName, ' = ');
            IF @AttributeValue IS NOT NULL THEN
              IF @QuotedIdentifier = 1 THEN
                SET @SQLStatement = STRING( @SQLStatement, '''', @AttributeValue, '''');
              ELSE
                SET @SQLStatement = STRING( @SQLStatement, @AttributeValue );
              END IF;
            END IF;
          END IF;
          SET @SQLStatement = STRING( @SQLStatement, '; COMMIT WORK;');

          INSERT INTO @Results( EntityId, EntityOwner, EntityName, EntityOrder, ReferenceLevel, SQLStatement ) VALUES ( @ForeignTableId, @ForeignOwner, @ForeignTableName, @EntityOrder, @ReferenceLevel, @SQLStatement );
        END IF;
      END FOR;
      SELECT COUNT(*) INTO @ThisCount FROM @Results;
    END LOOP;
  END IF;

  SELECT EntityId, EntityOwner, EntityName, EntityOrder, ReferenceLevel, SQLStatement FROM @Results ORDER BY EntityOrder DESC;

END;

CREATE OR REPLACE PROCEDURE DBA.DeleteOrderOwner(
  IN @TableOwner CHAR(128)
  )
RESULT(
  EntityId        INTEGER,
  EntityOwner     CHAR(128),
  EntityName      CHAR(128),
  EntityOrder     INTEGER,
  ReferenceLevel  INTEGER,
  SQLStatement    CHAR(2048)
)
BEGIN
  DECLARE @EntityOrder    INTEGER;
  DECLARE @ReferenceLevel INTEGER;
  DECLARE @LastCount      INTEGER;
  DECLARE @ThisCount      INTEGER;
  DECLARE @SQLStatement   CHAR(2048);

  DECLARE LOCAL TEMPORARY TABLE @Results (
    EntityId        INTEGER,
    EntityOwner     CHAR(128),
    EntityName      CHAR(128),
    EntityOrder     INTEGER,
    ReferenceLevel  INTEGER,
    SQLStatement    CHAR(2048),
  ) NOT TRANSACTIONAL;

  SET @EntityOrder    = 0;
  SET @ReferenceLevel = 0;

  FOR EachPrimaryTable AS PrimaryTableCursor INSENSITIVE CURSOR FOR
    SELECT DISTINCT
           PrimaryTableId   AS @PrimaryTableId,
           PrimaryOwner     AS @PrimaryOwner,
           PrimaryTableName AS @PrimaryTableName
      FROM DBA.ViewForeignKeys
     WHERE @PrimaryOwner    = @TableOwner
       AND ForeignTableId IS NULL
    UNION ALL
    SELECT DISTINCT
           ForeignTableId   AS @PrimaryTableId,
           ForeignOwner     AS @PrimaryOwner,
           ForeignTableName AS @PrimaryTableName
      FROM DBA.ViewForeignKeys
     WHERE ForeignOwner     = @TableOwner
       AND @TableOwner NOT IN ( SELECT PrimaryOwner FROM DBA.ViewForeignKeys WHERE ForeignTableId = @PrimaryTableId )
  DO
    SET @EntityOrder = @EntityOrder + 1;

    SET @SQLStatement = STRING( 'DELETE FROM ', @PrimaryOwner, '.', @PrimaryTableName );
    SET @SQLStatement = STRING( @SQLStatement, '; COMMIT WORK;');

    INSERT INTO @Results( EntityId, EntityOwner, EntityName, EntityOrder, ReferenceLevel, SQLStatement ) VALUES ( @PrimaryTableId, @PrimaryOwner, @PrimaryTableName, @EntityOrder, @ReferenceLevel, @SQLStatement );
  END FOR;

  SET @LastCount = 0;
  SELECT COUNT(*) INTO @ThisCount FROM @Results;


  WHILE @ThisCount > @LastCount LOOP
    SET @ReferenceLevel = @ReferenceLevel + 1;
    SET @LastCount = @ThisCount;

    FOR EachResult AS ResultCursor INSENSITIVE CURSOR FOR

      -- Get all tables with foreign keys that come from results
      SELECT DISTINCT
             ForeignTableId   AS @ForeignTableId,
             ForeignOwner     AS @ForeignOwner,
             ForeignTableName AS @ForeignTableName
        FROM DBA.ViewForeignKeys
       WHERE PrimaryTableId IN ( SELECT EntityId FROM @Results )
         -- don't include those that have foreign keys from tables not already identified
         AND ForeignTableId NOT IN ( SELECT EntityId FROM @Results )
    DO
      -- don't include those in current result set
      IF @ForeignTableId NOT IN (
        SELECT PrimaryTableId
          FROM DBA.ViewForeignKeys
         WHERE PrimaryTableId IN ( SELECT EntityId FROM @Results )
           AND ForeignTableId NOT IN ( SELECT EntityId FROM @Results )
           AND ForeignTableId <> @ForeignTableId )
      THEN
        SET @EntityOrder = @EntityOrder + 1;

        SET @SQLStatement = STRING( 'DELETE FROM ', @ForeignOwner, '.', @ForeignTableName );
        SET @SQLStatement = STRING( @SQLStatement, '; COMMIT WORK;');

        INSERT INTO @Results( EntityId, EntityOwner, EntityName, EntityOrder, ReferenceLevel, SQLStatement ) VALUES ( @ForeignTableId, @ForeignOwner, @ForeignTableName, @EntityOrder, @ReferenceLevel, @SQLStatement );
      END IF;
    END FOR;
    SELECT COUNT(*) INTO @ThisCount FROM @Results;
  END LOOP;

  SELECT EntityId, EntityOwner, EntityName, EntityOrder, ReferenceLevel, SQLStatement FROM @Results ORDER BY EntityOrder DESC;

END;

[VolkerBarth]

Why is the order important to you - what are you trying to achieve?

[JimDiaz]

For example if I want to delete a record that does have on delete cascade

[JimDiaz]

Okay this doesn't work as planned. Does anyone know of a way to determine the order of operations required for referential integrity?

[VolkerBarth]

Sorry, I still don't get the point: If you use a FK with ON DELETE CASCADE semantics, why would you need to know the FK order - do you run into troubles because of FK cycles or the like? (Otherwise I would think the cascading simply just should work as expected...)

Or do you use DELETE RESTRICT semantics and want to find out all depending child records and delete them expliticly before deleting a parent record (which would otherwise fail, of course)? (I guess your code seems to imply that, though I'm not that good in studying long SQL procedures without much comments...)

---

Note, when trying to build a tree of FK dependencies (which will fail if they build a DAG and not a tree...), a RECURSIVE UNION might be a natural way to do so...

---

FWIW, in case you do use MobiLink: As the MobiLink client generates an automatic FK order based on the publication's definition by default (and would warn about FK cycles) - you might try to capture the queries that DBMLSYNC issues initially to get the according logic - possibly by comparing a RLL with the extended option TableOrderChecking (toc) extended option both set/unset - beware, that's a veeeeeeeeeery wild guess...:)

[VolkerBarth]

the order of operations required for referential integrity

Sorry, just another question: What do you mean by that expression? - Note, you can always set option WAIT_FOR_COMMIT = ON to defer that checking...

[JimDiaz]

I am in the process of merging two databases both of which are involved in SQL Remote replication. Once merged I will be keeping these databases in sync using Mobilink. Our approach is to first merge and than once merged to sync. The databases are very complex each having over 1000 tables. One of my objectives is to minimize the impact on the SQL remote messages going out to those databases involved in SQL Remote replication so WAIT_FOR_COMMIT as I understand it is not an option.

Having said this I can see many other reasons why it would be nice to be able to determine the "Order Of Operations Required for Referential Integrity" some of which both you and I mentioned above.

Thanks for your help when able I will try your suggestion regarding tracing the DBMLSYNC.