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

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

CREATE TABLE my_table (col NUMBER(7));
CREATE SYNONYM my_table_old FOR my_table;
CREATE SYNONYM my_table_bak FOR my_table_old;

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

SELECT* FROMmy_table;
-- Same thing:
SELECT* FROMmy_table_old;
SELECT* FROMmy_table_bak;

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

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

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

SELECT*
FROM  ALL_SYNONYMS
WHERE  TABLE_OWNER = 'PLAYGROUND'

Выход:

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

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

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

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

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!
CONNECTBYs.TABLE_OWNER = PRIORs.OWNER
AND  s.TABLE_NAME  = PRIORs.SYNONYM_NAME

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

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

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

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

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'
CONNECTBYs.TABLE_OWNER = PRIORs.OWNER
AND  s.TABLE_NAME  = PRIORs.SYNONYM_NAME

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

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

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

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'
CONNECTBYs.TABLE_OWNER = PRIORs.OWNER
AND  s.TABLE_NAME  = PRIORs.SYNONYM_NAME
-- Start recursing only from non-synonym objects
START WITHEXISTS (
SELECT1
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чтобы любой объект был из ALL_OBJECTSнашей схемы, но не a SYNONYM. (да, синонимы работают для всех объектов, включая процедуры, пакеты, типы и т. д.)

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

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

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

CREATEPUBLICSYNONYM my_table_bak2 FORmy_table_bak;
CREATESYNONYM bak_backup_old FORmy_table_bak2;

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

SELECT*
FROM  ALL_SYNONYMS
WHERE  TABLE_OWNER = 'PLAYGROUND'

… и поэтому:

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

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

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(
SELECTOWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
FROMALL_SYNONYMS
UNIONALL
SELECTOWNER, SYNONYM_NAME, 'PUBLIC', TABLE_NAME
FROMALL_SYNONYMS
) s
-- Add the synthetic PUBLIC TABLE_OWNER as well
WHERE  s.TABLE_OWNER IN(
'PLAYGROUND', 'PUBLIC'
)
CONNECTBYs.TABLE_OWNER = PRIORs.OWNER
AND  s.TABLE_NAME  = PRIORs.SYNONYM_NAME
START WITHEXISTS (
SELECT1
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'
)

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

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

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

SELECT
-- Magic function
SUBSTR(
sys_connect_by_path(
s.TABLE_OWNER
|| '.'
|| s.TABLE_NAME, ' <- '
) || ' <- '
|| s.OWNER
|| '.'
|| s.SYNONYM_NAME, 5
)
FROM(
SELECTOWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
FROMALL_SYNONYMS
UNIONALL
SELECTOWNER, SYNONYM_NAME, 'PUBLIC', TABLE_NAME
FROMALL_SYNONYMS
) s
WHERE  s.TABLE_OWNER IN(
'PLAYGROUND', 'PUBLIC'
)
CONNECTBYs.TABLE_OWNER = PRIORs.OWNER
AND  s.TABLE_NAME  = PRIORs.SYNONYM_NAME
START WITHEXISTS (
SELECT1
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'
)

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

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

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

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

SELECT
SUBSTR(
sys_connect_by_path(
s.TABLE_OWNER
|| '.'
|| s.TABLE_NAME, ' <- '
) || ' <- '
|| s.OWNER
|| '.'
|| s.SYNONYM_NAME, 5
)
FROM(
SELECT* FROM(
SELECTOWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
FROMALL_SYNONYMS
UNIONALL
SELECTOWNER, SYNONYM_NAME, 'PUBLIC', TABLE_NAME
FROMALL_SYNONYMS
) s
-- Add this predicate to prevent cycles
WHERE(s.OWNER  , s.SYNONYM_NAME)
!= ((s.TABLE_OWNER , s.TABLE_NAME))
) s
CONNECTBYs.TABLE_OWNER = PRIORs.OWNER
AND  s.TABLE_NAME  = PRIORs.SYNONYM_NAME
START WITHEXISTS (
SELECT1
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:

// 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

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

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

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

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

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

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

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

Вывод

Категории

Последние статьи

Рефакторинг Hudson God Class

Альтернативы синтаксиса Java лямбда

Morphia и MongoDB: развивающиеся структуры документов

OpenShift Express: развертывание приложения Java EE (с поддержкой AS7)

Интеграция jqGrid, REST, AJAX и Spring MVC