Zeos 7.0 Interbase GetIndexInfo for all ind. in SQLMetadata

[EgonHugeist]

function TZInterbase6DatabaseMetadata.UncachedGetIndexInfo(const Catalog: string;
const Schema: string; const Table: string; Unique: Boolean;
Approximate: Boolean): IZResultSet;
var
SQL : string;
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, 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 ';
if ( Table = '' ) then //Added by EgonHugeist to show all indices 30.03.2011
SQL := SQL + 'I.RDB$RELATION_NAME != '''' GROUP BY '+
'I.RDB$RELATION_NAME, ISGMT.RDB$FIELD_NAME, I.RDB$INDEX_NAME, '+
'I.RDB$UNIQUE_FLAG, ISGMT.RDB$FIELD_POSITION, I.RDB$INDEX_TYPE, '+
'I.RDB$SEGMENT_COUNT ORDER BY 1,2,3,4'
else
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 ORDER BY 2,3,4';

with GetConnection.CreateStatement.ExecuteQuery(SQL) do
begin
while Next do
begin
Result.MoveToInsertRow;
Result.UpdateNull(1); //TABLE_CAT
Result.UpdateNull(2); //TABLE_SCHEM
Result.UpdateString(3, GetString(1)); //TABLE_NAME, RDB$RELATION_NAME
Result.UpdateBoolean(4, not GetBoolean(2)); //NON_UNIQUE, RDB$UNIQUE_FLAG
Result.UpdateNull(5); //INDEX_QUALIFIER
Result.UpdateString(6, GetString(3)); //INDEX_NAME, RDB$INDEX_NAME
Result.UpdateInt(7, Ord(ntNoNulls)); //TYPE
Result.UpdateInt(8, GetInt(4) + 1); //ORDINAL_POSITION, RDB$FIELD_POSITION
Result.UpdateString(9, GetString(5)); //COLUMN_NAME, RDB$FIELD_NAME
Result.UpdateNull(10); //ASC_OR_DESC
Result.UpdateNull(11); //CARDINALITY
Result.UpdateInt(12, GetInt(7)); //PAGES, RDB$SEGMENT_COUNT
Result.UpdateNull(13); //FILTER_CONDITION
Result.InsertRow;
end;
Close;
end;
end;

Just an example for Interbase

[mdaems]

Hi,

Can you please explain the differences with the current implementation?

Mark

[EgonHugeist]

Dear Mark,

the differences are:
You can show all indices which are embedded in the DB independend of TableName property. But it shows some indices more than once if more than one Field is included cause of joins and groupby statements. Its a an idea no patch. But i think it can be helpfull if you want to admin your db and check all indices...

EgonHugeist

P.S. i also posted a changed code of TZSQLMetadata, ZAbstractConnection component to view Triggers... I did'nt found an implemention or i'm wrong????

Hey mark i also miss a "ShowSystemData: Boolean" property to swich System-Metadata of or on.

[mdaems]

EgonHugeist,

Sorry for not checking your patch by comparing it to the base code. The change is quite clear when I do it now. (Only remark is I would separate the group by part from the if...then...else construct)

Now I wonder if we should include this when this behaviour is different in other databases. But on the other hand... checking the code some databases have this option and others don't.
ADO, ADA, Mssql, Sybase and Oracle do allow empty tablename. FB, SQLite, Mysql and PostgreSQL don't.

What do you think? Let's do it? (Zeos 7 only)

Concerning the triggers : Is your implementation complete for FB already?
Sorry again for not checking it yet. It was late yesterday... I hadn't touched the forum for more than 2 months, so I had to catch up a lot...

Mark

[EgonHugeist]

Dear Mark,

you are rigtht, but i think there is an easy workaround for supporting all indices. I'have made a real fast admin tool to check my DB, alter tables and so an. the fastestway to check it was to compare all DB items by there groups i think. On this way my tool automatically see what to alter, create or drop...


I think its helpfull an very userfriendly for everybody, so why don't we do?
If the database doesn't support it than you have the normal way and do it by your Statements in joining the tables.

Back to the Triggers. I think it would by helpfull if your Component supprts it. It is Metadata or not? internal yes i know. I'm working to add domains etc too. Your components utimately is named: ___Metadata. Laugh..

and yes it works quit well on FB/IB..

So i'll do it. But it would be helpfull if an new release comes out to download with the posted patches (for example the Unicode-support ) and helpfull ideas, else do the work twice...

P.S an Exception occours when closing Application after Interbase-Exception was raised... If no ib-Exception was raised, nothing happens. Try to debug but nothing to find just an CPU-Window, or was it to late today.

EgonHugeist

[EgonHugeist]

Me again...

I missunderstud your interpetation of my posted code. Its quiet simple. Look in the function header. There you'll find a "Table" string. This String is used as Filter/TablePattern in TSQLMetadata. So if it was empty no indix was resultet. I didnt select empty Tables! I select the Field RDB$RELATION_NAME and say Show me all non null Relations. So all indeces of the DataBase where resultet in case of used Fields in the Index. Very simple i think. That adjudged the if .. then .. else construct. It isn't a SQL-Statement but rather a check of the Filter Zeos sets in this Statement.

So i think it works in all Databases. And we should do it.

EgonHugeist

[mdaems]

EgonHugeist,
Probably you misunderstood my remark about separating the group by clause from the if then else construct.

This is my version from your patch:

Code: Select all

    if Unique then
      SQL := SQL + ' I.RDB$UNIQUE_FLAG = 1 AND ';
    if ( Table = '' ) then 
      SQL := SQL + 'I.RDB$RELATION_NAME != '''' '
    else 
      SQL := SQL + ' I.RDB$RELATION_NAME = ''' + Table + ''' ';

    SQL := SQL + ' 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 ORDER BY 1,2,3,4'; 

Does that work well for you as well?

I did commit this to testing branch (Rev. 886)

Looking for the trigger stuff now

Mark

[EgonHugeist]

Thanks Mark,

i post it too

EgonHugeist