Zeoslib Portal

Code: Select all

function TZInterbase6DatabaseMetadata.GetIndexInfo(const Catalog: string;
  const Schema: string; const Table: string; Unique: Boolean;
  Approximate: Boolean): IZResultSet;
var
  SQL, Key: string;
begin
  Key := Format('get-index-info:%s:%s:%s:%s:%s',
    [Catalog, Schema, Table, BoolToStr(Unique), BoolToStr(Approximate)]);

  Result := GetResultSetFromCache(Key);
  if Result = nil then
  begin
    Result := ConstructVirtualResultSet(IndexInfoColumnsDynArray);

    SQL :=  ' SELECT I.RDB$RELATION_NAME, I.RDB$UNIQUE_FLAG, I.RDB$INDEX_NAME,'
      + ' ISGMT.RDB$FIELD_POSITION,	ISGMT.RDB$FIELD_NAME, I.RDB$INDEX_TYPE,'
      + ' I.RDB$SEGMENT_COUNT, I.RDB$INDEX_INACTIVE, '
      + ' COUNT (DISTINCT P.RDB$PAGE_NUMBER) '
      + ' FROM RDB$INDICES I JOIN RDB$INDEX_SEGMENTS ISGMT ON'
      + ' I.RDB$INDEX_NAME = ISGMT.RDB$INDEX_NAME JOIN RDB$RELATIONS R ON'
      + ' (R.RDB$RELATION_NAME = I.RDB$RELATION_NAME) JOIN RDB$PAGES P ON'
      + ' (P.RDB$RELATION_ID = R.RDB$RELATION_ID AND P.RDB$PAGE_TYPE = 7'
      + ' OR P.RDB$PAGE_TYPE = 6) WHERE ';
    if Unique then
      SQL := SQL + ' I.RDB$UNIQUE_FLAG = 1 AND ';
    SQL := SQL + ' I.RDB$RELATION_NAME = ''' + Table + ''' GROUP BY '
      + ' I.RDB$INDEX_NAME, I.RDB$RELATION_NAME, I.RDB$UNIQUE_FLAG, '
      + ' ISGMT.RDB$FIELD_POSITION, ISGMT.RDB$FIELD_NAME, I.RDB$INDEX_TYPE, '
      + ' I.RDB$SEGMENT_COUNT, I.RDB$INDEX_INACTIVE '
      + ' ORDER BY 2,3,4';

    with GetConnection.CreateStatement.ExecuteQuery(SQL) do
    begin
      while Next do
      begin
        Result.MoveToInsertRow;
        Result.UpdateNullByName('TABLE_CAT');
        Result.UpdateNullByName('TABLE_SCHEM');
        Result.UpdateStringByName('TABLE_NAME',
          GetStringByName('RDB$RELATION_NAME'));
        Result.UpdateBooleanByName('NON_UNIQUE',
          not GetBooleanByName('RDB$UNIQUE_FLAG'));
        Result.UpdateNullByName('INDEX_QUALIFIER');
        Result.UpdateStringByName('INDEX_NAME',
          GetStringByName('RDB$INDEX_NAME'));
        Result.UpdateIntByName('TYPE', Ord(ntNoNulls));
        Result.UpdateIntByName('ORDINAL_POSITION',
          GetIntByName('RDB$FIELD_POSITION') + 1);
        Result.UpdateStringByName('COLUMN_NAME',
          GetStringByName('RDB$FIELD_NAME'));
        // Check for 1 instead of 0 or NULL.
        if (GetIntByName('RDB$INDEX_TYPE') = 1) then
          Result.UpdateStringByName('ASC_OR_DESC', 'Desc')
        else
          Result.UpdateStringByName('ASC_OR_DESC', 'Asc');
        Result.UpdateNullByName('CARDINALITY');
        Result.UpdateIntByName('PAGES',
          GetIntByName('RDB$SEGMENT_COUNT'));
        Result.UpdateNullByName('FILTER_CONDITION');
        if (GetIntByName('RDB$INDEX_INACTIVE') = 1) then
          Result.UpdateStringByName('INDEX_ACTIVE', 'No')
        else
          Result.UpdateStringByName('INDEX_ACTIVE', 'Yes');
        Result.InsertRow;
      end;
      Close;
    end;

    AddResultSetToCache(Key, Result);
  end;
end;