Поддерживает ли SQLite вызов из TRIGGER пользовательской функции, использующей предложение With? ⇐ C++

[Anonymous]

У меня есть база данных, которая реализует очень простую древовидную структуру с таблицей иерархии, которая имеет два столбца: родительский и дочерний.

Код: Выделить всё

 CREATE TABLE hierarchy (
parent INTEGER,
child  INTEGER,
PRIMARY KEY(parent, child)
);

Мне нужно убедиться, что на графике, определенном строками таблицы, нет циклов. Например, если иерархия имеет (1, 2) и (2, 3), вставка (3, 1) или использование UPDATE для изменения (2, 3) на (2, 1) не должны быть разрешены. Я решил, что для этого наиболее подходящим способом будет определение двух ТРИГГЕРОВ (один для INSERT и другой для UPDATE), которые будут проверять, создаст ли команда цикл в базе данных. Для этого мне также понадобится рекурсия, поэтому мне понадобится предложение With.
Однако, согласно разделу 5 документации SQLite о предложении With, его нельзя использовать внутри TRIGGER. Таким образом, я переместил With в функцию, определенную в C++, то есть LookForCycles, которая вызывается из TRIGGER.

Код: Выделить всё

CREATE TRIGGER prevent_cycles_insert
BEFORE INSERT ON hierarchy
FOR EACH ROW
BEGIN
SELECT
CASE
WHEN lookForCycles(NEW.parent, NEW.child) = 1 THEN
RAISE(ABORT, 'Cycle detected in INSERT INTO hierarchy!')
END;
END;

CREATE TRIGGER prevent_cycles_update
BEFORE UPDATE ON hierarchy
FOR EACH ROW
BEGIN
SELECT
CASE
WHEN lookForCycles(NEW.parent, NEW.child) = 1 THEN
RAISE(ABORT, 'Cycle detected in UPDATE hierarchy!')
END;
END;

Код: Выделить всё

lookForCycles

просто выполняет следующую команду SQLite. Обратите внимание, что параметрам (знакам вопроса) присваиваются значения аргументов LookForCycles с помощью sqlite3_bind_value .

Код: Выделить всё

WITH RECURSIVE ancestors(node) AS(
SELECT ?
UNION ALL
SELECT h.parent
FROM hierarchy h
JOIN ancestors a ON h.child = a.node
)
SELECT 1 FROM ancestors WHERE node = ?;

Я протестировал эту реализацию, и она работает так, как ожидалось. Например, если я помещу следующие строки в иерархию: (1, 2) (1, 3), (2, 3) и (3, 4), попытка вставить (4, 1) или обновление (3, 4) до (3, 1) завершится неудачно с сообщением «Обнаружен цикл в иерархии INSERT INTO/UPDATE!».
Меня беспокоит то, что даже если предложение With используется в совершенно другом контексте, чем TRIGGER, как он вызывается из функции C++, я не знаю, работает ли он сейчас, но не гарантируется, что он будет делать то же самое во всех случаях. Если бы кто-то, обладающий большими знаниями о SQLite, мог бы сказать мне, как мне следует это делать, было бы здорово!
Две аннотации:

• Я знаю, что могу реализовать это с помощью рекурсивных TRIGGER, и я уже думал о возможном алгоритме, но я бы предпочел избежать этого варианта, поскольку он, вероятно, потребует использования временной таблицы и будет намного сложнее, чем просто использование With.
• По какой-то причине это тоже работает, хотя я использую FOR непосредственно внутри TRIGGER:

  Код: Выделить всё

  CREATE TRIGGER prevent_cycles
  BEFORE INSERT ON hierarchy
  FOR EACH ROW
  BEGIN
  WITH RECURSIVE ancestors(node) AS (
  SELECT NEW.parent
  UNION ALL
  SELECT h.parent
  FROM hierarchy h
  JOIN ancestors a ON h.child = a.node
  )
  SELECT
  CASE
  WHEN EXISTS (
  SELECT 1 FROM ancestors WHERE node = NEW.child
  ) THEN
  RAISE(ABORT, 'Cycle detected!')
  END;
  END;
  

Подробнее здесь: https://stackoverflow.com/questions/798 ... t-uses-the