sql - 如何在 TFDQuery 中找到唯一标识记录的数据库键？

Question

简而言之，我如何知道哪些(主要和唯一)键唯一标识 FireDAC 查询记录？

我正在使用 Delphi 10.1 & FireDAC & Firebird。

我正在使用代码生成来生成一个包含我的应用程序的所有查询的文件。它是遗留应用程序，因此当前的数据库结构无处不在。设计它的人在设计它时没有知识(仍在学习)。所以我们没有一个连贯的系统来唯一标识记录。其中一些在 autoinc int 生成器上。有些还有另一个字段用于唯一标识。有些有组合。链接到这些字段的外键也不连贯。

所以。由于数据库的整体重构不是一个直接的选择，我们必须处理它。我能想到的最好方法是能够检索给定查询的每个可能的键。这将使我们能够系统地检索我们可以唯一标识查询和表中的记录的所有可能方式。这将为我们提供一个记录标识符的抽象(基本上是一组键)，我们可以在重构时将其传递到我们的应用程序中。

第一步是使用此结构(为便于阅读而简化)生成数据库的虚拟表示(表、字段、字段类型、长度、键、外键):

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;

其次，我实现了从该模式为应用程序的每个表及其所有可能的键生成一个类的代码。看起来像这样:

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;

既然表格已经完成，我将攻击查询。我的目标是知道查询中存在哪些键。

为了知道这个查询有哪些键:

1. 获取此查询从中获取数据的表的列表
2. 遍历这些表的键
3. 检查查询中是否存在键的所有字段

示例(为简单起见跳过外键大小写):

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;

使用这个方法，并给定一个TFDQuery

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

我们可以轻松生成此代码，因为我们知道此查询同时具有 PK_CUSTOMERS 键和 PK_GROUPS 使用我们上面的测试:

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

现在，我面临的挑战如下。我们知道 TCustomersQuery 中的每条记录都唯一标识了 CUSTOMERS 表中的一条记录。换句话说，TCustomersQuery 中的每条记录都对应于 CUSTOMERS 表中的一条不同的、非重复出现的记录。

但是 TCustomersQuery 中的每条记录都不会唯一标识 GROUPS 表中的非重复记录。

换句话说，PK_CUSTOMERS 和 UK_CODE 键在此查询的上下文中仍然有效，但 PK_GROUPS 无效。

换句话说，PK_CUSTOMERS 和 UK_CODE 键的唯一性在此查询中传递，而不是 PK_GROUPS 的唯一性> 键。

我想找出哪些键在任意查询的上下文中仍然有效。

FireDAC 在 IDE 中首次创建查询时自动获取查询字段 ProviderFlags。它以某种方式知道哪些字段是主键的一部分。我想扩展它以了解适用于此查询的所有主键和唯一键。 我如何知道所有可能具有 ProviderFlags = pfInKey 值的字段？

简而言之，我如何知道哪些(主键和唯一键)唯一标识一个 FireDAC 查询记录？

Answer 1

我认为这不是 FireDAC 问题，而是一个更普遍的问题。

要实现您的目标，您首先需要获取查询(源表和源列)的所有列的元数据，这些列是其源表中唯一键的一部分。 (注意表和列的别名)

然后，按每个“外键”的源表和源列分组，您应该检查查询中是否有其源表的完整键(考虑多列 PK/AK 索引)。
为此，您应该获取每个源表的元数据并尝试匹配每个键列。

如果没有覆盖全键，则查询结果不能唯一(不保证)。
如果覆盖了完整键，则无论如何都必须检查查询结果的唯一性，因为根据查询语法，您可能会在结果中多次重复相同的键(在源表中是唯一的)。

小心!!
具有不同参数的相同查询可能具有唯一性或不唯一性的列。

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

对于 ExternalParameter = 1 t1.PKColumn 在上面的查询中也是唯一的，否则它不会

如您所见，所有这些东西都需要大量额外的工作和 sql 查询..

我不是 Firebird 专家，所以我在 SQLServer 中进行了测试，但我认为您可以使用 Firebird 系统表 (Look here for some example) 获得相同的架构信息

在 SQLServer 中，您可以使用 sys.dm_exec_describe_first_result_set 提取元数据信息:

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) 

这样您就可以提取有关要测试的列的所有索引的详细信息:

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
)

在 delphi 中，像这样测试 key 的唯一性:

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

而这只是最简单的案例分析!考虑不是 PK/AK 索引但可能是对源表的唯一引用的标识列，考虑涉及多个键的字段..

这可能会成为一项非常困难的任务..

我认为您最好的选择是介于自动和手动之间。