I have made some changes to GetIndexInfo method. Please look at them and see if they look alright.
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;
