How to find DB Keys that uniquely identify records in a TFDQuery?

Question

In a nutshell, how do I know which (primary & unique) keys uniquely identify a FireDAC query record ?

I am using Delphi 10.1 & FireDAC & Firebird.

I am using code generation to generate a file containing all the queries of my application. It is a legacy application, so the current DB structure is all over the place. The person who designed it didn't have the knowledge (was still learning) when he designed it. So we don't have a coherent system for uniquely identifying records. Some of them are on a autoinc int generator. Some have another field for unique identification. Some have combinations. And the foreign keys that link to these fields are not coherent neither.

So. Since the total refactoring of the DB is not an immediate option, we have to deal with it. The best way I could figure was to be able to retreive every possible key for a given query. This would allow us to retreive systematically all the possible ways we can uniquely identify a record in a query & table. This would give us an abstraction of a record identifier (basically a set of keys) that we could pass along in our application, while we refactor.

The first step was to generate a virtual representation of the DB (table, fields, field types, lengths, keys, foreign keys) with this structure (simplified for readability) :

TTable = class(TCollectionItem)
property Name : string;
property Fields : TFieldCollection;
property Keys : TKeyCollection;
end;

TField = class(TCollectionItem)
property Name : string;
property Table : TTable;
property Type : TFieldType;
property Size : integer;
property Required : Boolean;
end;

TKey = class(TCollectionItem)
property KeyType : TKeyType;
property Name : string;
property Table : TTable;
end;

TKeyType = (ktPrimary, ktUnique, ktForeign);

TKeyCollection = class(TCollection)
end;

TTableCollection = class(TCollection)
end;

TSchema = class(TObject)
property Tables : TTableCollection;
end;

Secondly, I acheived to generate code from this schema a class for each of the table of the application, along with all their possible keys. Looks like this :

CUSTOMERS table
---------------
ID int (autoinc, pk PK_CUSTOMERS)
CODE int (uk UK_CODE)
NAME string
GROUP_ID int (fk on GROUPS.ID)


GROUPS table
------------
ID int (autoinc, pk PK_GROUPS)
NAME string

// Generated code (simplified for readability)

TCustomerPk = class(TKey)
property ID : Integer;
end;

TCustomerCode = class(TKey)
property Code : Integer;
end;

TCustomerKeys = class(TKeys)
property CustomerPk : TCustomerPk ...
property CustomerCode : TCustomerCode ...
end;

TCustomers = class(TTableBase)
property ID : TIntegerField;
property Code : TIntegerField;
property Name : TWideStringField;
property Keys : TCustomerKeys; 
end;

TGroupsPK = class(TKey)
property ID : Integer;
end;

TGroupsKeys = class(TKeys)
property GroupsPk : TGroupsPk;
end;

TGroups = class(TTableBase)
property ID : TIntegerField;
property NAME : TStringField;
property Keys : TGroupKeys;
end;

Now that tables are done, I attack Queries. My aim is to know which keys are present in a query.

In order to know which keys this query has :

1. Get the list of tables that this query takes data from
2. Iterate through the keys of these tables
3. Check if all fields of the key is present in the query

Sample (skipping case of foreign keys for simplicity) :

for lQueryField in lQuery.Fields do
begin
  if not lTableList.Contains(lQueryField.Table)
    lTableList.Add(lQueryField.Table)
end;

for lQueryTable in lTableList do
begin
  for lKey in lQueryTable.Keys do
    begin
      lFound := true;
      for lKeyField in lKey.Fields do
      begin
        if not lQuery.Fields.Contains(lKeyField) then lFound := false;
      end;

      if lFound then
        // Query has key lKey

    end;
end;

Using this method, and given a TFDQuery

SELECT CUSTOMERS.ID CUSTOMERID, CUSTOMERS.CODE CUSTOMERCODE, GROUPS.ID GROUPID FROM CUSTOMERS INNER JOIN GROUPS ON GROUPS.ID = CUSTOMERS.GROUP_ID

We can easily generate this code because we know that this query has both the PK_CUSTOMERS key and PK_GROUPS key using our test above :

TCustomersQuery = class(TQueryBase)
property CUSTOMERID : TIntegerField;
property GROUPID : TIntegerField; 
property CUSTOMERCODE : TIntegerField;
property CustomerPk : TCustomerPk;
property CustomerCodeUk : TCustomerCode;
property GroupPk : TGroupPk;
end;

Now, the challenge I face is the following. We know that each record in the TCustomersQuery uniquely identify a record in the CUSTOMERS table. In other words, Each record in the TCustomersQuery corresponds to a distinct, non-reoccurring record in the CUSTOMERS table.

But each record in the TCustomersQuery does not uniquely identify a non-reoccurring record in the GROUPS table.

In other words, the PK_CUSTOMERS and UK_CODE keys are still valid in the context of this query, but the PK_GROUPS is not.

In other words, again, the uniqueness of the PK_CUSTOMERS and UK_CODE keys are conveyed in this query, but not the uniqueness of the PK_GROUPS key.

I want to find which keys are still valid in the context of an arbitrary query.

FireDAC automatically fetches the query fields ProviderFlags when first creating a query in the IDE. It somehow know which fields are part of the primary key. I would like to extend this to know all Primary and Unique keys that apply to this query. How can I know all of the fields that could have a ProviderFlags = pfInKey value ?

In a nutshell, how do I know which (primary & unique) keys uniquely identify a FireDAC query record ?

Answer 1

I think this is not a FireDAC problem but a more generic problem.

To achieve your goal you first need to get metadata of your query (source table and source column) for all columns that are part of a unique key in their source table. (beware of table and column aliases)

Then, grouping by source table and source column for each of this "foreign key" you should check if you have in your query the full key of their source table (think about multiple column PK/AK indexes).
To do this you should get metadata of each source table and try to match each key column.

If full key is not covered it can't be unique in query result (not guaranteed).
If full key is covered, you have to check uniqueness in your query result anyway, because, depending on your query syntax, you could have the same key (unique in source table) repeated many times in your result.

BECAREFUL!!
The same query with different parameters could have a column with uniqueness or not.

select t1.PKColumn, t2.PKColumn
from Table1 t1
cross join (
    select top @ExternalParameter * 
    from Table2
) t2

For ExternalParameter = 1 t1.PKColumn will be unique also in above query else it will not

As you can see, all this stuffs require a lot of extra work and sql queries..

I'm not expert of Firebird, so I have made my tests in SQLServer, but I think you can get same schema info using Firebird system tables (Look here for some example)

in SQLServer you can use sys.dm_exec_describe_first_result_set to extract metadata informations:

select column_ordinal, d.name, source_table, source_column, is_part_of_unique_key, is_identity_column, c.object_id, c.column_id
from sys.dm_exec_describe_first_result_set(N'SELECT CUSTOMERS.ID CUSTOMERID, CUSTOMERS.CODE CUSTOMERCODE, GROUPS.ID GROUPID FROM CUSTOMERS INNER JOIN GROUPS ON GROUPS.ID = CUSTOMERS.GROUP_ID',null,1) d
join sys.columns c on c.object_id = OBJECT_ID(d.source_table) and c.name=d.source_column
where (is_hidden=0 and is_part_of_unique_key=1 or is_identity_column=1) 

and with this you can extract details about all indexes of the column to test:

select *
from sys.indexes i 
join sys.index_columns c on i.object_id = c.object_id and i.index_id = c.index_id
where c.object_id = @TestObjectID 
and exists (
    select 1 
    from sys.index_columns cc 
    where cc.object_id = c.object_id and cc.index_id = c.index_id and cc.column_id = @TestColumnID
)

and from delphi, something like this to test for key uniqueness:

test_uniqe := 'if exists(select ' + SourceColumnList + ', count(*) from ' +  SourceTable + ' group by ' + SourceColumnList + ' having count(*)>1) select 0 uk else select 1 uk

And this is only the simplest case analisis! Think about identity column that are not PK/AK indexes but could be a unique reference to source table, think about fields that are involved in more than one key..

It can become a very difficult task..

I think your best option is something like a mid way from automatic and manual..