如何检索存储在具有多重继承的SQL中的属性

Question

我将记录存储在SQL中，这些记录表示类似于C++中的多重继承关系。比如：

CREATE TABLE Classes 
(
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE Inheritance 
(
class_id INTEGER NOT NULL,
base_class_id INTEGER NOT NULL,
FOREIGN KEY (class_id) REFERENCES Classes(id),
FOREIGN KEY (base_class_id) REFERENCES Classes(id)
);

类具有两种类型的属性。这些属性由类继承，但方式不同。无论何时为类定义的第一种类型的属性都会覆盖在任何基类中使用的同一属性的值。另一种类型累积值：属性实际上是一组值，每个类继承其基类的所有值，加上可以向该集添加一个额外的(单个(值：

CREATE TABLE OverridableValues
(
class_id INTEGER PRIMARY KEY,
value TEXT NOT NULL,
FOREIGN KEY (class_id) REFERENCES Classes(id)
);
CREATE TABLE AccumulableValues
(
class_id INTEGER PRIMARY KEY,
value TEXT NOT NULL,
FOREIGN KEY (class_id) REFERENCES Classes(id)
);

OverridableValues的警告：在多重继承的不同路径上，不存在覆盖同一属性的情况。

我正在尝试使用公共表表达式来设计查询，这些表达式将返回给定属性和类的值。

我尝试使用的方法是从根开始(为了简单起见，假设有一个根类(，然后构建从根到其他所有类的路径树。问题是如何将有关属性的信息从父母传递给子女。例如，以下是一个错误的尝试：

WITH ParentProperty (id, value) AS 
(
SELECT c.id, a.value
FROM Classes c
LEFT JOIN AccumulableValues a
ON a.class_id = c.id
WHERE c.id = 1 --This is the root
UNION ALL
SELECT i.class_id, IFNULL(a.value, ba.value)
FROM ParentProperty p
JOIN Inheritance i
ON i.base_class_id = p.id
LEFT JOIN AccumulableValues a
ON a.class_id = i.class_id
LEFT JOIN AccumulableValues ba
ON ba.class_id = i.base_class_id
)
SELECT id, value
FROM ParentProperty;

我觉得我需要在CTE内再加一个UNION ALL，这是不允许的。但如果没有它，我要么会错过正确的价值观，要么会错过继承的价值观。到目前为止，我未能为这两种类型的属性设计查询。

我使用SQLite作为我的数据库引擎。

Answer 1

终于找到了解决方案。我在下面描述它，但更有效的方法仍然受到欢迎。

让我们从Accumulable属性开始。我的问题是，我试图在一个CTE中添加多个UNION ALL。我已经通过添加额外的CTE(参见AcquiresFrom(解决了这个问题

WITH AcquiresFrom (class_id, from_class_id, value) AS
(
SELECT a.class_id, a.class_id, a.value
FROM AccumulatableValues a
UNION ALL
SELECT i.class_id, i.base_class_id, NULL
FROM Inheritance i
),
ClassProperty (class_id, value) AS
(
SELECT c.id, NULL
FROM Classes c
LEFT JOIN Inheritance i
ON i.class_id = c.id
WHERE i.base_class_id IS NULL
UNION ALL
SELECT a.class_id, IFNULL(a.value, p.value)
FROM ClassProperty p
JOIN AcquiresFrom a
ON (a.from_class_id = p.class_id AND a.from_class_id != a.class_id) OR
(a.class_id = p.class_id AND a.class_id = a.from_class_id AND p.value IS NULL)
)
SELECT DISTINCT class_id, value
FROM ClassProperty
WHERE value IS NOT NULL
ORDER BY class_id;

AcquiresFrom表示获取值的方法：类要么引入新值(第一个子句(，要么继承它(第二个子句(。ClassProperty递增地将值从基类传播到派生类。剩下要做的惟一一件事就是消除重复项和NULL值(最后一个子句SELECT DISTINCT/WHERE value IS NOT NULL(。

可重写属性更为复杂。

WITH Roots (id, value) AS
(
SELECT c.id, o.value
FROM Classes c
LEFT JOIN Inheritance i
ON i.class_id = c.id
LEFT JOIN OverridableValues o
ON o.class_id = c.id
WHERE i.base_class_id IS NULL
),
PossibleValues (id, acquired_from_id, value) AS 
(
SELECT r.id, r.id, r.value
FROM Roots r
UNION ALL
SELECT i.class_id, CASE WHEN o.value IS NULL THEN p.acquired_from_id ELSE i.class_id END, IFNULL(o.value, p.value)
FROM PossibleValues p
JOIN Inheritance i
ON i.base_class_id = p.id
LEFT JOIN OverridableValues o
ON o.class_id = i.class_id
),
Split (class_id, base_class_id, direct) AS (
SELECT i.class_id, i.base_class_id, 1
FROM Inheritance i
UNION ALL
SELECT i.class_id, i.base_class_id, 0
FROM Inheritance i
),
Ancestors (id, ancestor_id) AS (
SELECT r.id, NULL
FROM Roots r
UNION ALL
SELECT s.class_id, CASE WHEN s.direct == 1 THEN a.id ELSE a.ancestor_id END
FROM Ancestors a
JOIN Split s
ON s.base_class_id = a.id
)
SELECT DISTINCT p.id, p.value
FROM PossibleValues p
WHERE p.acquired_from_id NOT IN
(
SELECT a.ancestor_id
FROM PossibleValues p1
JOIN PossibleValues p2
ON p2.id = p1.id
JOIN Ancestors a
ON a.id = p1.acquired_from_id AND a.ancestor_id = p2.acquired_from_id
WHERE p1.id = p.id
);

Roots显然是没有父类的类的列表。PossibleValuesCTE将值从根传播/覆盖到最终类，并打破多个继承循环，使结构成为树状结构。此查询的结果中存在所有有效的id/值对，但也存在一些无效值。这些无效值是在其中一个分支上重写的值，但在另一个分支中不知道这一事实。acquired_from_id允许我们重建谁是第一个引入该值的类(当两个不同的类引入相同的值时，这可能很有用(。

剩下的最后一件事就是解决由多重继承引起的歧义。知道类和两个可能的值，我们需要知道一个值是否覆盖另一个值。这通过Ancestors表达式来解决。