65.9K
CodeProject 正在变化。 阅读更多。
Home

深度表

Alejandro Xalabarder
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.27/5 (10投票s)

2010年4月25日

CPOL

7分钟阅读

viewsIcon

38760

downloadIcon

205

使用 SQL 自然连接表

引言

在处理关系型数据库时，几乎不可能不谈论 SQL。使用这种语言，可以在数据库上执行各种操作和查询，并且根据其实现和使用方式，可以非常高效地执行。尽管从设计上看，它显然是一种人类可读的语言，但很快就会变得难以阅读。例如，select 查询

SELECT id, date, customerId FROM tSales

完美地反映了查询的意图，即

"want from the table tSales: id, data and customerId"

但是，这个查询

SELECT id, date, customerId, tCustomer.name FROM tSales, _
	tCustomer WHERE tSales.customerId = tCustomer.id

虽然也很明确，但不知何故让开发者的眼睛感到不适。我们指定了如何访问表 tCustomer 中的记录，而在给定的模式中，这通常是唯一可以做到这一点的方式。在这种情况下，意图是

"want from the table tSales: id, data, customerId and the customer's name"

如果事先定义了关系 tSales <-> tCustomer，那么“客户姓名”之类的内容就可以实现了。但在 SQL 中不行，因为这种表之间的关系至少不能以 SELECT 查询可以使用的方式包含在数据库模式中。但通常，当语言无法实现某事时，我们就需要代码生成。

背景

本文仅讨论构建 SELECT 语句的问题，特别是连接表的问题。这个问题并不新鲜，因此有许多不同语言的工具和库可以帮助解决这个问题。通常图形化解决方案更受欢迎，但当数据库模式变得很大（现实世界）时，图形本身就开始成为另一个问题，并且通常不提供比生成的“难以阅读”的查询好多少的文档。

这里并不真正提供一个适用于市场上所有数据库的工具，而是提出一个“深度选择”（deep select）的想法，针对单个表（深度表），将其转换为标准 SQL 的算法，以及图形界面如何使用它来促进开发，就像在附加的演示中那样。

深度表

假设我们已经在数据库中定义了表连接，深度表的含义几乎是微不足道的：一个表，不仅可以选择其列，还可以选择连接表的列，如果这些表又连接着其他表，那么也可以选择它们连接的表的列，依此类推。

例如，假设有数据库模式

table tISOCountries  (isoA2, isoA3, number, name)
table tCompanies     (id, name, countryId)
table tProducts      (prodID, providerID, name, price)
table tSales         (id, date, customerId, productId, quantity)

我们希望能够对表 tSales 表达一个深度查询，例如

Deep select on table tSales:

id
date
customer name
customer country name
product name
quantity
product provider name
product provider country name

也就是说，只需一组“深度列”，其中“customer”、“product”、“provider”和“country”是假定的连接，最后一个是真实表中的真实列。这种语法至少在英语中是相当可读的，而在西班牙语中，可能最好使用反向的单词顺序。例如，

 deep column : customer country name
 english     : Customer's country name
 spanish     : name del country del customer = Nombre del pais del cliente

尽管这种反向顺序也可能很有趣，但我们将保留英语（别担心。;）

可以使用前面的深度选择在 SQL92 语法中生成的查询是

SELECT
   tSales.id AS id ,
   tSales.date AS date ,
   customer.name AS customer_name ,
   customer_country.name AS customer_country_name ,
   product.name AS product_name ,
   product_provider.name AS product_provider_name ,
   product_provider_country.name AS product_provider_country_name ,
   tSales.quantity AS quantity
FROM
   tSales ,
   tCompanies AS customer ,
   tISOCountries AS customer_country ,
   tProducts AS product ,
   tCompanies AS product_provider ,
   tISOCountries AS product_provider_country
WHERE
   tSales.customerId == customer.id  AND
   customer.countryId == customer_country.isoA2  AND
   tSales.productId == product.prodID  AND
   product.providerID == product_provider.id  AND
   product_provider.countryId == product_provider_country.isoA2

以及查询的演示数据结果

Possible result table

请注意，生成的列名和表别名都非常易读。并非所有生成代码的系统都具有此功能，而在开发中此功能非常重要。

连接

表之间的连接不是什么新鲜事，例如 SQL92 使用外键来描述这些连接。但是，尽管在语法和概念上与我在这里提出的连接相同，但外键的使用严格用于引用完整性，也就是说，用于 INSERT、DELETE 和 UPDATE 等写操作的约束。深度表所需的连接仅用于 SELECT 查询，因此其定义比外键更宽松。另一方面，如果模式中提供了外键，则没有任何理由不将它们用作附加连接，从而避免在这些情况下重复定义。

就像外键一样，可以通过一组表（源表）的列来定义连接，这些列引用另一个表（目标表）的记录。以下结构定义了一个连接条目

    connectionName, sourceTable, sourceKey, targetName, targetKey

其中 connectionName 是我们将在深度选择（深度列）中使用的名称，它由两个表和连接两者的列（们）定义。如果连接需要一个以上的列，则只需使用具有相同 connectionName、sourceTable 和 targetTable 的更多条目即可。

    connectionName, sourceTable, sourceKey  , targetName   , targetKey
       customer      , tSales     , customerId , tCompanies   , id
       product       , tSales     , productId  , tProducts    , prodID
       provider      , tProducts  , providerID , tCompanies   , id
       country       , tCompanies , countryId  , tISOCountries, isoA2

这是根据给定模式的这些连接的图形表示

Connections graphically

也可以考虑在连接中不指定 sourceTable，例如

    connectionName, sourceTable, sourceKey  , targetName   , targetKey
       country       ,            , countryId  , tISOCountries, isoA2

在这种情况下，所有具有 countryId 列的表都将自动连接到 tISOCountries 表。这是一个“开放连接”，表示方法如下

Open connection

深度选择算法

“深度表”的主要目标是生成一个 SQL select 语句，就像在“深度表”一节中所示的那样。为了进行此生成，我们甚至不需要数据库的真实模式，只需一组连接和一个“深度选择”（表的深度列集），我们就可以生成文本形式的 SQL，就像这里描述的那样。

对于该算法，方便将“深度列”结构定义为如下

    baseTable, [ connection1, ..., connectionN, ] columnName

有了这个结构，基表对于所有深度列都是相同的，在我们的例子中是 tSales。

如果我们看一下示例中的 select 查询，我们会发现问题可以简化为获取三个列表，每个子句一个：SELECT、FROM 和 WHERE。将 FROM 列表初始化为“baseTable”，其余的可以通过对每个深度列应用以下算法来完成

Deep column reduction algorithm

获得三个列表后，构建最终的 SQL 就很简单了。

请注意，必须避免重复条目，尤其是在“FROM”列表中。还要注意，使用此算法，所有别名都将以初始别名（baseTable）开头。例如，它将生成“tSales_customer.name AS tSales_customer_name”，而不是示例中的“customer.name AS customer_name”。如果您更喜欢较短的别名，则可以在算法中删除以“baseTable_”开头的前缀。

深度模式

如果像本文中那样定义了连接，那么使用反映表之间这些连接的扩展数据库模式会很方便。在我们的示例中，一个简单的扩展模式是

table tISOCountries :

   isoA2,
   isoA3,
   number,
   name,

table tCompanies :

   o--o Connection [country] to table [tISOCountries] through (countryId)
   id,
   name,
   countryId,

table tProducts :

   o--o Connection [provider] to table [tCompanies] through (providerID)
   prodID,
   providerID,
   name,
   price,

table tSales :

   o--o Connection [customer] to table [tCompanies] through (customerId)
   o--o Connection [product] to table [tProducts] through (productId)
   id,
   date,
   customerId,
   productId,
   quantity,

对于工具来说，递归地开发一个表的所有连接，无论是列表形式还是更图形化的形式（例如使用树），也可能很有用。例如，对于表 tSales

Deep table schema

这里必须考虑到，递归连接（表连接到自身）或某些其他循环递归会生成一个无限列表，必须避免。至少在列表形式中，而在树中，如果它是按需动态加载的（例如，打开节点时），则可以接受。

具有这些元素的工具只需选择所需的列即可生成甚至执行深度 SQL。

结论

在不添加新的大型概念的情况下，仅通过深度列作为访问表中相关信息的方式（例如，“connection connection ... colName”），我们可以执行大量查询，这些查询直接使用 SQL 会变得复杂得多，因为需要关联的连接。所有这些都以一种相当自然的方式（例如，customer name = 客户的姓名）实现，并生成具有可读列名的查询（customer_name）。这也是一种看待表记录的方式，它不仅包含值（例如，“id”、“date”），还包含“objects”，其中连接就是对象，例如“客户姓名”可以被认为是访问对象“customer”的字段“name”（例如 customer.name）。

我们生成的 SQL 使用 INNER JOINS，但请注意，这可以被视为实现问题。该算法可以进行调整，以便生成其他 join 语法。

© . All rights reserved.