Zeoslib Portal

Code: Select all

function TZInterbase6DatabaseMetadata.UncachedGetImportedKeys(const Catalog: string;
  const Schema: string; const Table: string): IZResultSet;
var
  //HA 090515 remove because never used
  //Key, SQL: string;
  SQL: string;
  LTable: string;
begin
    Result := ConstructVirtualResultSet(ImportedKeyColumnsDynArray);

    LTable := ConstructNameCondition(AddEscapeCharToWildcards(Table), 'RELC_FOR.RDB$RELATION_NAME'); // Modified by cipto 6/11/2007 4:53:02 PM 
    SQL := 'SELECT RELC_PRIM.RDB$RELATION_NAME, '    // 1 prim.RDB$ key table name
      + ' IS_PRIM.RDB$FIELD_NAME, '         // 2 prim.RDB$ key column name
      + ' RELC_FOR.RDB$RELATION_NAME, '     // 3 foreign key table name
      + ' IS_FOR.RDB$FIELD_NAME, '          // 4 foreign key column name
      + ' IS_FOR.RDB$FIELD_POSITION, '      // 5 key sequence
      + ' REFC_PRIM.RDB$UPDATE_RULE, '      // 6
      + ' REFC_PRIM.RDB$DELETE_RULE, '      // 7
      + ' RELC_FOR.RDB$CONSTRAINT_NAME, '   // 8 foreign key constraint name
      + ' RELC_PRIM.RDB$CONSTRAINT_NAME '   // 9 primary key constraint name
      + ' FROM RDB$RELATION_CONSTRAINTS RELC_FOR, RDB$REF_CONSTRAINTS REFC_FOR, '
      + ' RDB$RELATION_CONSTRAINTS RELC_PRIM, RDB$REF_CONSTRAINTS REFC_PRIM, '
      + ' RDB$INDEX_SEGMENTS IS_PRIM,  RDB$INDEX_SEGMENTS IS_FOR '
      + ' WHERE RELC_FOR.RDB$CONSTRAINT_TYPE = ''FOREIGN KEY'' AND ';
     if LTable <> '' then
       SQL := SQL + LTable + ' AND ';
     SQL := SQL + ' RELC_FOR.RDB$CONSTRAINT_NAME=REFC_FOR.RDB$CONSTRAINT_NAME'
       + ' and REFC_FOR.RDB$CONST_NAME_UQ = RELC_PRIM.RDB$CONSTRAINT_NAME and '
       + ' RELC_PRIM.RDB$CONSTRAINT_TYPE = ''PRIMARY KEY'' and ' // useful check, anyay
       + ' RELC_PRIM.RDB$INDEX_NAME = IS_PRIM.RDB$INDEX_NAME and '
       + ' IS_FOR.RDB$INDEX_NAME = RELC_FOR.RDB$INDEX_NAME   and '
       + ' IS_PRIM.RDB$FIELD_POSITION = IS_FOR.RDB$FIELD_POSITION  and '
       + ' REFC_PRIM.RDB$CONSTRAINT_NAME = RELC_FOR.RDB$CONSTRAINT_NAME '
       + ' ORDER BY RELC_PRIM.RDB$RELATION_NAME, IS_FOR.RDB$FIELD_POSITION ';

    with GetConnection.CreateStatement.ExecuteQuery(SQL) do
    begin
      while Next do
      begin
        Result.MoveToInsertRow;
        Result.UpdateNullByName('PKTABLE_CAT');
        Result.UpdateNullByName('PKTABLE_SCHEM');
        Result.UpdateStringByName('PKTABLE_NAME', GetString(1));
        Result.UpdateStringByName('PKCOLUMN_NAME', GetString(2));
        Result.UpdateNullByName('FKTABLE_CAT');
        Result.UpdateNullByName('FKTABLE_SCHEM');
        Result.UpdateStringByName('FKTABLE_NAME', GetString(3));
        Result.UpdateStringByName('FKCOLUMN_NAME', GetString(4));
        Result.UpdateIntByName('KEY_SEQ', GetInt(5) + 1);

        if GetString(6) = 'RESTRICT' then
          Result.UpdateIntByName('UPDATE_RULE', Ord(ikRestrict))
        else if GetString(6) = 'NO ACTION' then
          Result.UpdateIntByName('UPDATE_RULE', Ord(ikNoAction))
        else if GetString(6) = 'SET DEFAULT' then
          Result.UpdateIntByName('UPDATE_RULE', Ord(ikSetDefault))
        else if GetString(6) = 'CASCADE' then
          Result.UpdateIntByName('UPDATE_RULE', Ord(ikCascade))
        else if GetString(6) = 'SET NULL' then
          Result.UpdateIntByName('UPDATE_RULE', Ord(ikSetNull));

        if GetString(7) = 'RESTRICT' then
          Result.UpdateIntByName('DELETE_RULE', Ord(ikRestrict))
        else if GetString(7) = 'NO ACTION' then
          Result.UpdateIntByName('DELETE_RULE', Ord(ikNoAction))
        else if GetString(7) = 'SET DEFAULT' then
          Result.UpdateIntByName('DELETE_RULE', Ord(ikSetDefault))
        else if GetString(7) = 'CASCADE' then
          Result.UpdateIntByName('DELETE_RULE', Ord(ikCascade))
        else if GetString(7) = 'SET NULL' then
          Result.UpdateIntByName('DELETE_RULE', Ord(ikSetNull));

        Result.UpdateString(3, GetString(1));
        Result.UpdateStringByName('FK_NAME', GetString(8));
        Result.UpdateStringByName('PK_NAME', GetString(9));
        Result.UpdateNullByName('DEFERRABILITY');
        Result.InsertRow;
      end;
      Close;
    end;
end;