Статьи

Все, что вам нужно знать о рекурсивном SQL

Декабрь 16, 2018

Oracle SYNONYMs — отличная особенность. Вы можете реализовать всевозможные настройки обратной совместимости, просто создав SYNONYM в своей базе данных. Рассмотрим следующую схему:

1
2
3
4
CREATE TABLE my_table (col NUMBER(7));
 
CREATE SYNONYM my_table_old FOR my_table;
CREATE SYNONYM my_table_bak FOR my_table_old;

Теперь вы можете запросить вашу старую таблицу по трем различным именам, все это приведет к одному и тому же выводу

1
2
3
4
5
SELECT * FROM my_table;
 
-- Same thing:
SELECT * FROM my_table_old;
SELECT * FROM my_table_bak;

Проблема в том, что когда вы видите my_table_bak в коде (или какое-то еще более запутанное имя), вы сразу знаете, что это такое?

Используйте этот запрос, чтобы узнать

Мы можем использовать таблицу ALL_SYNONYMS, чтобы понять это. Этот запрос уже даст простой обзор:

1
2
3
SELECT *
FROM   ALL_SYNONYMS
WHERE  TABLE_OWNER = 'PLAYGROUND'

Выход:

1
2
3
4
OWNER       SYNONYM_NAME  TABLE_OWNER  TABLE_NAME
---------------------------------------------------
PLAYGROUND  MY_TABLE_BAK  PLAYGROUND   MY_TABLE_OLD
PLAYGROUND  MY_TABLE_OLD  PLAYGROUND   MY_TABLE

Но, как вы можете видеть, это скучно, потому что у нас есть транзитивные синонимы, и я не хочу просматривать всю таблицу, чтобы выяснить, что MY_TABLE_BAK -> MY_TABLE_OLD -> MY_TABLE .

Так что давайте использовать CONNECT BY!

Oracle (а также Informix и CUBRID) имеют это замечательное предложение CONNECT BY для иерархического SQL. Существует также возможность выражать иерархический SQL с использованием более мощных общих табличных выражений , если вы решитесь.

Но давайте посмотрим, как мы можем транзитивно разрешить наши таблицы. Вот как:

01
02
03
04
05
06
07
08
09
10
11
12
13
SELECT
  s.OWNER,
  s.SYNONYM_NAME,
 
  -- Get to the root of the hierarchy
  CONNECT_BY_ROOT s.TABLE_OWNER TABLE_OWNER,
  CONNECT_BY_ROOT s.TABLE_NAME  TABLE_NAME
FROM       ALL_SYNONYMS s
WHERE      s.TABLE_OWNER = 'PLAYGROUND'
 
-- The magic CONNECT BY clause!
CONNECT BY s.TABLE_OWNER = PRIOR s.OWNER
AND        s.TABLE_NAME  = PRIOR s.SYNONYM_NAME

Во-первых, есть CONNECT BY , который позволяет «связывать» иерархии по их иерархическим предшественникам. На каждом уровне иерархии мы будем связывать TABLE_NAME с его предыдущим («PRIOR») SYNONYM_NAME . Это будет повторяться до тех пор, пока цепочка не заканчивается (или если она запускается в цикл).

Также интересно ключевое слово CONNECT_BY_ROOT , которое для каждого пути в иерархии отображает корень пути. В нашем случае это цель TABLE_NAME .

Вывод можно увидеть здесь:

1
2
3
4
5
OWNER       SYNONYM_NAME  TABLE_OWNER  TABLE_NAME
---------------------------------------------------
PLAYGROUND  MY_TABLE_OLD  PLAYGROUND   MY_TABLE
PLAYGROUND  MY_TABLE_BAK  PLAYGROUND   MY_TABLE
PLAYGROUND  MY_TABLE_BAK  PLAYGROUND   MY_TABLE_OLD <-- Useless

Если вас смущают отображаемые записи, просто добавьте псевдостолбец LEVEL для отображения уровня рекурсии:

01
02
03
04
05
06
07
08
09
10
11
12
SELECT
 
  -- Add level here
  LEVEL,
  s.OWNER,
  s.SYNONYM_NAME,
  CONNECT_BY_ROOT s.TABLE_OWNER TABLE_OWNER,
  CONNECT_BY_ROOT s.TABLE_NAME  TABLE_NAME
FROM       ALL_SYNONYMS s
WHERE      s.TABLE_OWNER = 'PLAYGROUND'
CONNECT BY s.TABLE_OWNER = PRIOR s.OWNER
AND        s.TABLE_NAME  = PRIOR s.SYNONYM_NAME
1
2
3
4
5
6
7
LEVEL  OWNER       SYNONYM_NAME  TABLE_OWNER  TABLE_NAME
----------------------------------------------------------
1      PLAYGROUND  MY_TABLE_OLD  PLAYGROUND   MY_TABLE
2      PLAYGROUND  MY_TABLE_BAK  PLAYGROUND   MY_TABLE
1      PLAYGROUND  MY_TABLE_BAK  PLAYGROUND   MY_TABLE_OLD
^^^^^^
  Awesome!

Избавляемся от «плохих записей» с помощью START WITH

Как видите, некоторые из результатов теперь являются синонимами, указывающими непосредственно на целевую таблицу, тогда как последняя запись по-прежнему указывает на промежуточный элемент из пути синонима. Это потому, что мы возвращаемся к иерархии путей из каждой записи в таблице, а также из «промежуточных» ссылок на TABLE_NAME , у которых TABLE_NAME является еще одним синонимом.

Давайте также избавимся от них, используя опциональное предложение START WITH , которое позволяет ограничить обходы деревьев теми деревьями, корни которых выполняют данный предикат:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
SELECT
  s.OWNER,
  s.SYNONYM_NAME,
  CONNECT_BY_ROOT s.TABLE_OWNER TABLE_OWNER,
  CONNECT_BY_ROOT s.TABLE_NAME  TABLE_NAME
FROM       ALL_SYNONYMS s
WHERE      s.TABLE_OWNER = 'PLAYGROUND'
CONNECT BY s.TABLE_OWNER = PRIOR s.OWNER
AND        s.TABLE_NAME  = PRIOR s.SYNONYM_NAME
 
-- Start recursing only from non-synonym objects
START WITH EXISTS (
  SELECT 1
  FROM   ALL_OBJECTS
  WHERE  s.TABLE_OWNER           = ALL_OBJECTS.OWNER
  AND    s.TABLE_NAME            = ALL_OBJECTS.OBJECT_NAME
  AND    ALL_OBJECTS.OWNER       = 'PLAYGROUND'
  AND    ALL_OBJECTS.OBJECT_TYPE <> 'SYNONYM'
)

Итак, по сути, мы TABLE_NAME чтобы TABLE_NAME был любым объектом из ALL_OBJECTS который есть в нашей схеме, но не SYNONYM . (да, синонимы работают для всех объектов, включая процедуры, пакеты, типы и т. д.)

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

1
2
3
4
OWNER       SYNONYM_NAME  TABLE_OWNER  TABLE_NAME
---------------------------------------------------
PLAYGROUND  MY_TABLE_OLD  PLAYGROUND   MY_TABLE
PLAYGROUND  MY_TABLE_BAK  PLAYGROUND   MY_TABLE

Как насчет ПУБЛИЧНЫХ синонимов?

Чаще всего вы будете использовать не только локальные синонимы, но и ОБЩЕСТВЕННЫЕ. Oracle имеет эту причудливую псевдошуму PUBLIC , в которой вы не можете создавать объекты, но в которой вы можете создавать синонимы. Итак, давайте создадим еще несколько синонимов для целей обратной совместимости:

1
2
CREATE PUBLIC SYNONYM my_table_bak2 FOR my_table_bak;
CREATE SYNONYM bak_backup_old FOR my_table_bak2;

К сожалению, это нарушит нашу цепочку, потому что по какой-то причине только Oracle и Oracle Delphi знают, что PUBLIC хорошо известен как OWNER синонима, но не как TABLE_OWNER . Давайте посмотрим некоторые необработанные данные с:

1
2
3
SELECT *
FROM   ALL_SYNONYMS
WHERE  TABLE_OWNER = 'PLAYGROUND'

… и поэтому:

1
2
3
4
5
6
OWNER       SYNONYM_NAME    TABLE_OWNER  TABLE_NAME
------------------------------------------------------
PLAYGROUND  MY_TABLE_OLD    PLAYGROUND   MY_TABLE
PLAYGROUND  MY_TABLE_BAK    PLAYGROUND   MY_TABLE_OLD
PUBLIC      MY_TABLE_BAK2   PLAYGROUND   MY_TABLE_BAK
PLAYGROUND  BAK_BACKUP_OLD  PLAYGROUND   MY_TABLE_BAK2 <-- Not PUBLIC

Как вы можете видеть, PUBLIC SYNONYM MY_TABLE_BAK2 как сообщается, находится в схеме PUBLIC SYNONYM MY_TABLE_BAK2 ! Это нарушает рекурсию, конечно. Мы пропустили запись:

1
2
3
4
5
OWNER       SYNONYM_NAME    TABLE_OWNER  TABLE_NAME
------------------------------------------------------
PLAYGROUND  MY_TABLE_OLD    PLAYGROUND   MY_TABLE
PLAYGROUND  MY_TABLE_BAK    PLAYGROUND   MY_TABLE
PUBLIC      MY_TABLE_BAK2   PLAYGROUND   MY_TABLE <-- Hmm?

Чтобы обойти эту проблему, нам нужно настроить исходный набор данных. Любой объект, указанный как (TABLE_OWNER, TABLE_NAME) на самом деле может быть синонимом ('PUBLIC', TABLE_NAME) . Хитрость заключается в том, чтобы просто дублировать все входные данные как таковые:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SELECT
  s.OWNER,
  s.SYNONYM_NAME,
  CONNECT_BY_ROOT s.TABLE_OWNER TABLE_OWNER,
  CONNECT_BY_ROOT s.TABLE_NAME  TABLE_NAME
 
-- Tweaked data set
FROM (
  SELECT OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
  FROM ALL_SYNONYMS
  UNION ALL
  SELECT OWNER, SYNONYM_NAME, 'PUBLIC', TABLE_NAME
  FROM ALL_SYNONYMS
) s
 
-- Add the synthetic PUBLIC TABLE_OWNER as well
WHERE      s.TABLE_OWNER IN (
  'PLAYGROUND', 'PUBLIC'
)
CONNECT BY s.TABLE_OWNER = PRIOR s.OWNER
AND        s.TABLE_NAME  = PRIOR s.SYNONYM_NAME
START WITH EXISTS (
  SELECT 1
  FROM   ALL_OBJECTS
  WHERE  s.TABLE_OWNER           = ALL_OBJECTS.OWNER
  AND    s.TABLE_NAME            = ALL_OBJECTS.OBJECT_NAME
  AND    ALL_OBJECTS.OWNER       = 'PLAYGROUND'
  AND    ALL_OBJECTS.OBJECT_TYPE <> 'SYNONYM'
)

Вот оно, наша пропавшая запись!

1
2
3
4
5
6
OWNER       SYNONYM_NAME    TABLE_OWNER  TABLE_NAME
---------------------------------------------------
PLAYGROUND  MY_TABLE_OLD    PLAYGROUND   MY_TABLE
PLAYGROUND  MY_TABLE_BAK    PLAYGROUND   MY_TABLE
PUBLIC      MY_TABLE_BAK2   PLAYGROUND   MY_TABLE
PLAYGROUND  BAK_BACKUP_OLD  PLAYGROUND   MY_TABLE <-- Yep!

Отображение иерархии

Существует также причудливая функция SYS_CONNECT_BY_PATH , которая может быть использована для отображения всей иерархии в виде строки (VARCHAR2, максимум 4000 символов!). Вот как:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SELECT
 
-- Magic function
  SUBSTR(
    sys_connect_by_path(
         s.TABLE_OWNER
      || '.'
      || s.TABLE_NAME, ' <- '
    ) || ' <- '
      || s.OWNER
      || '.'
      || s.SYNONYM_NAME, 5
  )
FROM (
  SELECT OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
  FROM ALL_SYNONYMS
  UNION ALL
  SELECT OWNER, SYNONYM_NAME, 'PUBLIC', TABLE_NAME
  FROM ALL_SYNONYMS
) s
WHERE      s.TABLE_OWNER IN (
  'PLAYGROUND', 'PUBLIC'
)
CONNECT BY s.TABLE_OWNER = PRIOR s.OWNER
AND        s.TABLE_NAME  = PRIOR s.SYNONYM_NAME
START WITH EXISTS (
  SELECT 1
  FROM   ALL_OBJECTS
  WHERE  s.TABLE_OWNER           = ALL_OBJECTS.OWNER
  AND    s.TABLE_NAME            = ALL_OBJECTS.OBJECT_NAME
  AND    ALL_OBJECTS.OWNER       = 'PLAYGROUND'
  AND    ALL_OBJECTS.OBJECT_TYPE <> 'SYNONYM'
)

Приведенный выше запрос теперь выведет следующие записи:

1
2
3
4
PLAYGROUND.MY_TABLE <- PLAYGROUND.MY_TABLE_OLD
PLAYGROUND.MY_TABLE <- PLAYGROUND.MY_TABLE_OLD <- PLAYGROUND.MY_TABLE_BAK
PLAYGROUND.MY_TABLE <- PLAYGROUND.MY_TABLE_OLD <- PLAYGROUND.MY_TABLE_BAK <- PUBLIC.MY_TABLE_BAK2
PLAYGROUND.MY_TABLE <- PLAYGROUND.MY_TABLE_OLD <- PLAYGROUND.MY_TABLE_BAK <- PUBLIC.MY_TABLE_BAK2 <- PLAYGROUND.BAK_BACKUP_OLD

Впечатляет, а?

Примечание: если у вас устаревшие синонимы

Если у вас есть «устаревшие» синонимы, то есть синонимы, указывающие на никуда, Oracle может сообщить, что они указывают на себя. Это печально и создает ЦИКЛ в CONNECT BY . Чтобы этого не случилось, просто добавьте еще один предикат, например:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
SELECT
  SUBSTR(
    sys_connect_by_path(
         s.TABLE_OWNER
      || '.'
      || s.TABLE_NAME, ' <- '
    ) || ' <- '
      || s.OWNER
      || '.'
      || s.SYNONYM_NAME, 5
  )
FROM (
  SELECT * FROM (
    SELECT OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
    FROM ALL_SYNONYMS
    UNION ALL
    SELECT OWNER, SYNONYM_NAME, 'PUBLIC', TABLE_NAME
    FROM ALL_SYNONYMS
  ) s
 
  -- Add this predicate to prevent cycles
  WHERE (s.OWNER       , s.SYNONYM_NAME)
    != ((s.TABLE_OWNER , s.TABLE_NAME))
) s
CONNECT BY s.TABLE_OWNER = PRIOR s.OWNER
AND        s.TABLE_NAME  = PRIOR s.SYNONYM_NAME
START WITH EXISTS (
  SELECT 1
  FROM   ALL_OBJECTS
  WHERE  s.TABLE_OWNER           = ALL_OBJECTS.OWNER
  AND    s.TABLE_NAME            = ALL_OBJECTS.OBJECT_NAME
  AND    ALL_OBJECTS.OWNER       = 'PLAYGROUND'
  AND    ALL_OBJECTS.OBJECT_TYPE <> 'SYNONYM'
)

Можно ли написать запрос в jOOQ?

Ну конечно; естественно. В jOOQ почти все возможно, если вы можете написать это на SQL. Вот как мы используем запрос, аналогичный приведенному выше, для разрешения Oracle Synonmys в генераторе кода jOOQ:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
// Some reusable variables
AllObjects o   = ALL_OBJECTS;
AllSynonyms s1 = ALL_SYNONYMS;
AllSynonyms s2 = ALL_SYNONYMS.as("s2");
AllSynonyms s3 = ALL_SYNONYMS.as("s3");
 
Field<String> dot = inline(".");
String arr = " <- ";
 
// The actual qeury
DSL
.using(configuration)
.select(
  s3.OWNER,
  s3.SYNONYM_NAME,
  connectByRoot(s3.TABLE_OWNER).as("TABLE_OWNER"),
  connectByRoot(s3.TABLE_NAME).as("TABLE_NAME"),
  substring(
    sysConnectByPath(
      s3.TABLE_OWNER.concat(dot)
                    .concat(s3.TABLE_NAME),
      arr
    )
    .concat(arr)
    .concat(s3.OWNER)
    .concat(dot)
    .concat(s3.SYNONYM_NAME),
    5
  ))
.from(
  select()
  .from(
    select(
      s1.OWNER, s1.SYNONYM_NAME,
      s1.TABLE_OWNER, s1.TABLE_NAME)
    .from(s1)
    .union(
    select(
      s1.OWNER, s1.SYNONYM_NAME,
      inline("PUBLIC"), s1.TABLE_NAME)
    .from(s1))
    .asTable("s2"))
  .where(row(s2.OWNER, s2.SYNONYM_NAME)
         .ne(s2.TABLE_OWNER, s2.TABLE_NAME))
  .asTable("s3"))
.connectBy(s3.TABLE_OWNER.eq(prior(s3.OWNER)))
.and(s3.TABLE_NAME.eq(prior(s3.SYNONYM_NAME)))
.startWith(exists(
  selectOne()
  .from(o)
  .where(s3.TABLE_OWNER.eq(o.OWNER))
  .and(s3.TABLE_NAME.eq(o.OBJECT_NAME))
  .and(o.OBJECT_TYPE.ne("SYNONYM"))
  .and(o.OWNER.in(getInputSchemata()))
))
.fetch();

Скачайте jOOQ сегодня и попробуйте сами!

Вывод

Если у вас есть внутренне иерархический набор данных, то вы будете очень недовольны этими упрощенными иерархическими функциями SQL (также с выражениями таблиц общего доступа). Они не очень хорошо работают, и их очень сложно выразить, если иерархия усложняется. Так что вы можете также рассмотреть возможность использования реальной графовой базы данных, такой как Neo4j .

Но время от времени небольшая иерархия может проникнуть в вашу «стандартную» модель реляционных данных. Когда это произойдет, убедитесь, что это полезное предложение CONNECT BY готово к действию.

CONNECT BY поддерживается (как минимум):

  • CUBRID
  • Informix
  • оракул

Рекурсивные общие табличные выражения (аналог стандарта SQL для CONNECT BY поддерживается (как минимум):

  • DB2
  • жар-птица
  • HSQLDB
  • оракул
  • PostgreSQL
  • SQL Server
  • Sybase SQL Anywhere

и…

  • H2 имеет некоторую экспериментальную поддержку

В следующем посте мы рассмотрим, как сделать то же самое с рекурсивным CTE.

Ссылка: Все, что вам нужно знать о рекурсивном SQL от нашего партнера по JCG Лукаса Эдера из блога JAVA, SQL и JOOQ .
46
Share: