Неприятное ограничение рекурсивных CTE

Простейший способ заехать в дурдом -- это отладка рекурсивных запросов в уме. В предыдущем примере у нас закралась ошибка. Объекты, которые ни от кого не зависят, не попадут в итоговую выборку. Но, только в том случае, если глубина уровня их вложенности больше единицы. Всему виной JOIN at_namespace_file_link l ON l.filename = f.filename во второй части CTE. По логике вещей, тут должен быть LEFT JOIN, но рекурсивное CTE внутри себя не может участвовать во внешних объединениях. C'est la vie. И никакой возможности разрулить данную ситуацию внутри самого CTE не просматривается. Остается городить огород с объединением двух выборок.

WITH RECURSIVE
  ns_tree AS (
    SELECT
      f.filename,
      CAST((f.xid || '_' || f.dbid) AS VARCHAR(1024)) AS path,
      l2.uses_xid,
      l2.uses_dbid
    FROM
      at_namespace_file f
      JOIN at_namespace_file_link l
        ON l.uses_xid = f.xid AND l.uses_dbid = f.dbid
      LEFT JOIN at_namespace_file_link l2
        ON l2.filename = f.filename
    WHERE
      l.filename = :filename

    UNION ALL

    SELECT
      f.filename,
      (t.path || '-' || f.xid || '_' || f.dbid)
        AS path,
      l.uses_xid,
      l.uses_dbid
    FROM
      ns_tree t
      JOIN at_namespace_file f
        ON t.uses_xid = f.xid AND t.uses_dbid = f.dbid
      JOIN at_namespace_file_link l
        ON l.filename = f.filename
    WHERE
      POSITION ((f.xid || '_' || f.dbid)
        IN t.path) = 0)

SELECT
  t.filename
FROM
  ns_tree t

UNION

SELECT
  f.filename
FROM
  ns_tree t
  JOIN at_namespace_file f
    ON f.xid = t.uses_xid AND f.dbid = t.uses_dbid
  LEFT JOIN at_namespace_file_link l
    ON l.filename = f.filename
WHERE
  l.filename IS NULL

Список объектов в соответствии с заданными зависимостями

Если в предыдущем примере мы ранжировали заданный список объектов, то следующий пример возвращает упорядоченный в соответствии с иерархией зависимости список объектов, от которых зависит указанный объект.

WITH RECURSIVE 
  ns_tree AS ( 
    SELECT 
      f.filename, 
      CAST((f.xid || '_' || f.dbid) AS VARCHAR(1024)) AS path, 
      l2.uses_xid, 
      l2.uses_dbid 
    FROM 
      at_namespace_file f 
      JOIN at_namespace_file_link l 
        ON l.uses_xid = f.xid AND l.uses_dbid = f.dbid 
      LEFT JOIN at_namespace_file_link l2 
        ON l2.filename = f.filename 
    WHERE 
      l.filename = :filename 
         
    UNION ALL 
     
    SELECT 
      f.filename, 
      (t.path || '-' || f.xid || '_' || f.dbid) 
        AS path, 
      l.uses_xid, 
      l.uses_dbid 
    FROM 
      ns_tree t 
      JOIN at_namespace_file f 
        ON t.uses_xid = f.xid AND t.uses_dbid = f.dbid 
      JOIN at_namespace_file_link l 
        ON l.filename = f.filename 
    WHERE 
      POSITION ((f.xid || '_' || f.dbid) 
        IN t.path) = 0) 
SELECT 
  * 
FROM 
  ns_tree

Таблица at_namespace_file содержит список объектов, а at_namespace_file_link -- связи между ними. Вычисление пути (path) и дополнительная проверка через функцию POSITION позволяют избежать зацикливания на кольцевых ссылках, если такие попадутся в исходных данных.

Firebird 2013 Tour

Firebird Project is glad to announce Firebird 2013 Tour – series of seminars around the world, devoted to Firebird, with members of Firebird Project as speakers.

The first seminar will be in Siegburg (North Rhine-Westphalia, Germany), November 22, 2013.

Удаление гланд автогеном...

Таблицу со строковым первичным ключем подключить к визуальным контролам Гедымина нельзя, так как все они поголовно спроектированы для работы с целочисленными идентификаторами. Выход -- добавить вычисляемую колонку в запрос, которую заполнять хэшем от строкового значения. Встроенная функция Firebird HASH() возвращает 64-х битное целое (тип BIGINT), которое следует сдвигать вправо, пока значение не войдет в допустимый для 32-х битных целых диапазон.

В следующем примере мы генерируем ключи типа INTEGER по наименованиям контактов (что не совсем корректно, так как сами наименования у нас могут быть неуникальными).

select
  cast(iif(log(2, hash(name)) > 30,
    bin_shr(hash(name), 
      cast(log(2, hash(name)) as integer) - 30),
    hash(name)) as integer),
  name
from 
  gd_contact

В некоторых случаях может подойти и заполнение колонки последовательными значениями генератора:

select
  gen_id(my_gen, 1),
  name
from 
  gd_contact

Построение списка зависимых объектов

Замечательная задача для проверки программиста на знание SQL и реляционных БД. Пусть в одной таблице хранится список объектов. Объекты могут зависеть друг от друга. Связи хранятся в другой таблице ввиде пар ключей: объект - объект, от которого зависит данный объект. Требуется построить упорядоченный список, чтобы для любого объекта в нем, все объекты, от которых он зависит, располагались перед ним.

Решение с использованием рекурсивного CTE:

WITH RECURSIVE
  ns_tree AS (
    SELECT
      n.filename AS headname,
      0 AS usescount,
      CAST((n.xid || '_' || n.dbid) AS VARCHAR(1024)) AS path,
      n.filename
    FROM
      at_namespace_file n

    UNION ALL

    SELECT
      t.headname,
      (t.usescount + 1) AS usescount,
      (t.path || '-' || n.xid || '_' || n.dbid) AS path,
      n.filename
    FROM
      ns_tree t
      JOIN at_namespace_file_link l
        ON l.filename = t.filename
      JOIN at_namespace_file n
        ON l.uses_xid = n.xid and l.uses_dbid = n.dbid
    WHERE
      POSITION ((n.xid || '_' || n.dbid) IN t.path) = 0
    )
SELECT
  t.headname, sum(t.usescount)
FROM
  ns_tree t
GROUP BY
  1
ORDER BY
  2

В приведенном запросе at_namespace -- список объектов, а at_namespace_link -- связи между ними. Вычисление пути (path) и дополнительная проверка через функцию POSITION позволяют избежать зацикливания на кольцевых ссылках, если такие попадутся в исходных данных.

Предупрежден -- значит вооружен!

Контроллеры Marvell 88SE91xx и HDD > 2.2TB (3TB, 4TB).