Trees in SQL

 Перевод статьи про древовидные структуры в SQL на английский:

• Trees in SQL. Part 1
• Trees in SQL. Part 2
  

GDMNN: Задача #1

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

Цель:

• Унифицировать механизмы фиксации и учета движения, обобщив и распространив их не только на движение ТМЦ, но и на движение (изменение, трансформацию) любого объекта учета.
• Избавиться от дублирования данных и непрозрачных функций преобразования (поля документа => аналитические признаки в проводках).
• Любое поле документа может быть использовано в качестве аналитического признака при построении отчетов.
• Отойти от ограниченной структуры шапка-позиции. Для сложных документов предусмотреть наличие нескольких датасетов с произвольным уровнем вложенности. 
• Для сумовых данных, используемых с целью ускорения выборок (INV_BALANCE), предусмотреть неблокирующую схему обновления, чтобы отказаться от автокомита в складских документах (комита частично введенного документа). 
• Статус документа: черновик, отложенный, готовый.
• Уменьшить размер базы и, как следствие, увеличить скорость операций по изменению и выборке. 

Для новой структуры представить запрос на построение журнала ордера с использованием SQL window functions и другого функционала Firebird 3.

Задание на неделю #1. Firebird 3

На этой неделе произошло событие, которого мы с нетерпением ждали шесть долгих лет -- вышла третья версия сервера Firebird. В двух словах предыстория такова: Interbase, от которого в 2000-м году отпочковался Firebird, был задуман в те времена, когда многопроцессорные системы с десятками гигабайт оперативной памяти встречались разве что в фантастических рассказах. Когда же научно-технический прогресс догнал и перегнал самые смелые фантазии, именно внутренняя архитектура сервера стала основным тормозом. По сути, в многопользовательском сценарии у системного администратора было два выбора: или использовать оперативную память для кэширования данных, но тогда запросы из всех подключений будут выполняться только на одном процессоре/ядре (архитектура SuperServer), или задействовать все процессоры/ядра, но тогда не будет общего кэша и скорость будет зависеть от производительности дисковой подсистемы (архитектура Classic). Тот случай, когда хрен редьки не слаще.

Изменения в архитектуре SuperServer движка Firebird 3 теперь позволяют последнему выполнять запросы сетевых клиентов параллельно на разных процессорах/ядрах, при доступе к общему кэшу, что теоретически делает SuperServer выбором по умолчанию при развертывании системы на предприятии. Как оно получится на практике -- зависит от надежности тройки. В начале 2000-х мы перевели всех клиентов с SuperServer на Classic по двум причинам: падение процесса SuperServer означает обрыв всех коннектов и потерю данных во всех открытых транзакциях у сетевых пользователей; выполнение тяжелого запроса одним из клиентов практически парализует работу всех остальных.

У одного нашего клиента база данных имеет размер 50 Гб, количество одновременных подключений 50-60, на сервере 128 Гб оперативной памяти и 20 физических ядер. План: использовать SuperServer, установить кэш размером 50 Гб и выделить под буфер сортировки 32 Гб. Учитывая, что дисковая подсистема построена на RAID контроллере с 2 Гб энергонезависимой памяти, теоретически, это позволит вообще исключить прямое обращение к дискам. Т.е. производительность сервера базы данных будет определяться процессором, памятью и скоростью обмена с RAID контроллером.

Напомним, что именно производительность дисковой подсистемы всегда возглавляла список факторов, влияющих на общую производительность СУБД. Мы ожидаем ускорения выполнения запросов минимум на порядок. О достигнутых результатах напишем в этом блоге.

Практически все нововведения из третьей версии найдут применение в Гедымине:

• Передача НУЛЛ значений по сети битовой маской. Сейчас каждое поле с НУЛЛ значением занимает при передаче 4 байта + длина поля. Например, в таблице с бухгалтерскими проводками у нас десятки полей-аналитических признаков, которые могут быть не заполнены. Экономия может достигать нескольких сотен байт на каждую запись.
• Возможность увеличить TCP буфер до 32 Кб и применить компрессию данных при передаче. В интернете есть свидетельства о том, что скорость возрастает десятикратно при подключении по сетям с большой латентностью. Например, к серверу в удаленном офисе по VPN каналу.
• Шифрование файла базы данных. Теперь злоумышленник не сможет получить доступ к конфиденциальной информации просто переписав файл на сервер с чистой установкой Firebird и известным паролем к учетной записи SYSDBA.
• Window функции в SQL запросах позволяют объединять вместе данные и агрегатные значения по заданным группам этих данных. До Firebird 3 такую задачу можно было решить либо подзапросами (крайне медленно, так как подзапрос будет выполняться для каждой записи), либо выполнением в цикле отдельных запросов для каждой группы и объединением их результатов в единый набор данных с помощью EXECUTE BLOCK или STORED PROCEDURE, либо алгоритмической обработкой на клиенте (например, внутри Fast Report при построении отчета).
• “Linger” Database Closure for Superserver -- период времени, в течение которого сервер сохраняет в памяти все ресурсы, после закрытия последнего подключения к базе данных -- позволит ускорить загрузку пакета пространств имен, так как переподключение к базе данных выполняется после каждого ПИ, содержащего метаданные.
• DDL триггеры позволят отказаться от выполнения процедуры at_p_sync при старте системы для синхронизации информации о структуре базы данных с содержимым AT_ таблиц.
• Другие изменения и улучшения, на которых мы подробнее остановимся в следующих выпусках.

Исходный код Гедымина уже поправлен для совместимости с последней версией. После внутреннего тестирования мы опубликуем подробную инструкцию апгрейда для наших клиентов.

Поиск кольцевых ссылок в реляционной базе данных рекурсивным SQL запросом

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

Между тем, существует способ однозначного выявления циклов без "наматывания" кругов по графу. Рассмотрим граф, показанный на рисунке ниже:

В базе данных он хранится в виде матрицы смежностей в таблице t:

  CREATE TABLE t (
    a INTEGER,
    b INTEGER
  )

Соответственно, исходные данные:

  INSERT INTO t VALUES (1, 2);
  INSERT INTO t VALUES (2, 3);
  INSERT INTO t VALUES (3, 4);
  INSERT INTO t VALUES (3, 5);
  INSERT INTO t VALUES (3, 6);
  INSERT INTO t VALUES (2, 7);
  INSERT INTO t VALUES (6, 1);

Для поиска кольцевых ссылок напишем запрос:

with recursive tree as
  (
    select distinct
      a as initial, a, b, -1 as prev
    from
      t

    union all
    
    select
      iif(tr.initial <> tt.a, tr.initial, -1) as initial,
      tt.a,
      tt.b,
      tr.a as prev
    from
      t tt JOIN tree tr ON
        tr.b = tt.a and tr.initial > 0

  )
select
  *
from
  tree
where
  initial = -1

Запрос покажет все первые сегменты A - B (и предыдущие, т.е. сегменты непосредственно приведшие к циклу, PREV - A), всех возможных циклов в заданных данных. В нашем случае:

INITIAL A B PREV
-1 1 2 6
-1 2 3 1
-1 2 7 1
-1 3 4 2
-1 3 5 2
-1 3 6 2
-1 6 1 3

При необходимости проверить не входит ли в кольцо конкретный узел, следует прописать его ИД в первом запросе рекурсивного CTE:

with recursive tree as
  (
    select distinct
      a as initial, a, b, -1 as prev
    from
      t
    where 
      a = _some_id_

    union all
    
    select
      iif(tr.initial <> tt.a, tr.initial, -1) as initial,
      tt.a,
      tt.b,
      tr.a as prev
    from
      t tt JOIN tree tr ON
        tr.b = tt.a and tr.initial > 0

  )
select
  *
from
  tree
where
  initial = -1

Интервью с Jim Starkey, создателем СУБД Interbase.

I think we're living in a world where the expertise of programmers is now very heavily oriented towards mobile applications and fancy GUIs. So dealing with practical aspects of data management in the application just isn't going to happen.

Temporal SQL

Слюнки текут. Доживем ли мы до поддержки Temporal SQL в Firebird? В кратце: для указанных таблиц сервер связывает значение записи с определенным временным интервалом. Разумеется, интервалов может быть несколько и для каждого будет храниться своя запись. При извлечении указываем период и, вуаля, получаем актуальные значения именно для этого периода. Например, печатаем накладную за 1930 г. -- видим Кооператив "Геркулес". Тот же бланк, но за 2000 г. -- ООО "Геркулес". А в 2015 г. может и целое ЧУП "Геркулес", если не прикроют конечно.

Древовидная структура -> HTML

В копилку полезных запросов. Берем дерево команд Исследователя и получаем на выходе HTML код.

EXECUTE BLOCK
  RETURNS(name VARCHAR(200))
AS
  DECLARE VARIABLE prev_indent INTEGER = 0;
  DECLARE VARIABLE indent INTEGER;
  DECLARE VARIABLE I INTEGER;
  DECLARE VARIABLE n VARCHAR(200);
BEGIN
  name = '
';
  SUSPEND;

  FOR
    WITH RECURSIVE
      group_tree AS (
        SELECT id, parent, name,
          CAST('' AS VARCHAR(255)) AS indent
        FROM gd_command
        WHERE parent IS NULL

        UNION ALL

        SELECT g.id, g.parent, g.name,
          h.indent || rpad('', 2)
        FROM gd_command g JOIN group_tree h
          ON g.parent = h.id
          
      )
    SELECT
      CHARACTER_LENGTH(gt.indent),
      TRIM(gt.name)
    FROM
      group_tree gt
    INTO :indent, :n
    DO BEGIN
      I = :indent - :prev_indent;
    
      IF (:I > 0) THEN
      BEGIN
        name = '
';
        SUSPEND;
        prev_indent = :indent;
      END
    
      IF (:I < 0) THEN
      BEGIN
        name = '
';
        SUSPEND;
        prev_indent = :indent;
      END
      
      name = '
' || :n || '
';
      SUSPEND;
    END

  name = '
';
  SUSPEND;
END

Синхронизация БД с несколькими приложениями Android

Здесь мы писали о синхронизации между Андроид устройством и сервером Гедымин.

Поскольку у нас появляется второе приложение, упомянутый алгоритм следует расширить для поддержки произвольного количества приложений и произвольного количества наборов данных в рамках каждого приложения. А именно:

1. В таблицу с версиями данных добавляем поля:
   1. ИД приложения, для которого готовятся данные. Целое число. Определяется проектировщиком/разработчиком.
   2. РУИД потребителя данных.
2. Аналогичные поля добавляется в таблицу с командами изменения данных.
3. При установке связи приложения с сервером передаем ИД приложения и РУИД потребителя. Вместо РУИД потребителя могут использоваться данные, которые позволя однозначно установить потребителя и определить его РУИД на базе данных сервера.

Пропали буквы "И" и "ш" после восстановления базы mediawiki

Вот же послал нам высший разум наказание в виде так называемой СУБД MySQL. Неважно, как круто корпорация Oracle выглядит в бизнес отчетах, важно, что ваши бесценные данные могут испортиться в результате простой операции архивного копирования. А если архивируется база mediawiki, где в односимвольных кодировках записаны строки в UTF-8, которые при бэкапе будут побуквенно переведены в UTF-8... Удивительно, но есть люди, которые даже могут во всем этом разобраться.

Из кириллического подмножества символов UTF-8 почему-то не повезло буквам "ш" (0xD188) и "И" (0xD098). Вместо них пишутся в файл коды 0xD13F и 0xD03F.

Лечим следующим образом. После восстановления из архива базы mediawiki сформируем запросы для обновления символьных полей. Для буквы "И":

SELECT 
  CONCAT('UPDATE ', table_name, ' SET ', column_name, 
    ' = REPLACE(', column_name, ', 
    CONCAT( CHAR(208), CHAR(63) ), 
    CONCAT( CHAR(208), CHAR(152) ));') 
FROM columns 
WHERE 
  table_schema = 'your_schema_name_here' 
  AND data_type = 'varchar'

И для буквы "ш":

SELECT 
  CONCAT('UPDATE ', table_name, ' SET ', column_name, 
    ' = REPLACE(', column_name, ', 
    CONCAT( CHAR(209), CHAR(63) ), 
    CONCAT( CHAR(209), CHAR(136) ));') 
FROM columns 
WHERE 
  table_schema = 'your_schema_name_here' 
  AND data_type = 'varchar'

Результаты приведенных запросов -- готовые SQL команды для замены неправильных последовательностей байтов на правильные во всех строковых полях. Экспортируем их в текст и выполняем на базе данных.

UPD: Версия MySQL 5.5.32-cll-lve. Бэкап базы данных через phpMyAdmin 4.1.8 и cPanel 11.42.1.

CURRENT OF vs поиск по ключу

Какое же утро без хорошего эксперимента. Берем встроенный сервер Firebird 2.5.3 и чистую БД:

Page size               8192 
ODS version             11.2 
Page buffers            75 
Database dialect        3 
Attributes              force write 

Создаем две идентичных по структуре таблицы:

create table t1 (i integer not null)
create table t2 (i integer not null)

Одинаково заполняем каждую из них миллионом случайных значений:

execute block
as
  declare variable i integer = 0;
  declare variable v integer = 0;
begin
  while (i < 1000000) do
  begin
    v = round(rand() * 2000000000);
    insert into t1 values (:v);
    insert into t2 values (:v);
    i = :i + 1;
  end
end

Для таблицы t1 создаем индекс:

create index t1x on t1 (i)

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

execute block
as
  declare variable i integer;
begin
  for select i from t1 into :i
  do
  begin
    if (rand() < 0.25) then
      delete from t1 where i = :i;
  end
end

Статистика выполнения:

Execute       : 10 717.00 ms
Read          : 239 855
Writes        : 6 250
Fetches       : 4 264 681
Marks         : 250 286
IR            : 250 286
NIR           : 999 934
Deletes       : 250 286

Не совсем понятно почему NIR не миллион. Все остальное вполне логично. Коммит транзакции и еще раз удаляем четверть, теперь от оставшихся записей:

Execute       : 582 243.00 ms
Read          : 414 519
Writes        : 246 390
Fetches       : 5 712 020
Marks         : 944 206
IR            : 187 214
NIR           : 749 693 
Deletes       : 187 214
Expunges      : 250 286

Почти 10 минут! Обратите внимание на сумасшедшее количество Writes и Marks. Сборка мусора и перестроение индекса?

Далее, берем таблицу t2 и удаляем из нее четверть записей, но с помощью курсора и конструкции CURRENT OF:

execute block
as
  declare variable c cursor for
    (select i from t2);
  declare variable i integer;
begin
  open c;
  while (1=1) do
  begin
    fetch c into :i;
    if (row_count = 0) then
      leave;
    if (rand() < 0.25) then
      delete from t2 where current of c;
  end
  close c;
end

Статистика:

Execute       : 8 362.00 ms
Read          : 6 138
Writes        : 6 064
Fetches       : 2 762 582
Marks         : 250 100
NIR           : 1 000 000
Deletes       : 250 100

Обратите внимание, насколько меньше чтений по сравнению с удалением по индексированному полю. Выполнение на 20% быстрее за счет того, что не надо дергать индекс.

Коммит транзакции и еще раз удаляем четверть записей:

Execute       : 8 252.00 ms
Read          : 6 137
Writes        : 6 067
Fetches       : 3 587 315
Marks         : 693 789
NIR           : 749 900
Deletes       : 187 455
Expunges      : 250 100

Выполнился даже быстрее чем первый запрос, несмотря на сборку мусора, и в 70 (!!!) раз быстрее чем запрос с удалением по индексированному полю. Непонятно почему так велико значение Marks, ведь удаляется всего 187 455 записей.

Подсчитаем количество записей в первой таблице:

select count(*) from t1

Статистика:

Execute       : 493 697.00 ms
Read          : 181 339
Writes        : 181 084
Fetches       : 3 009 620
Marks         : 561 642
NIR           : 562 500
Expunges      : 187 214

Все ясно, сборка мусора напоролась на индекс. 8 минут на кофе. Теперь считаем во второй:

select count(*) from t2

Статистика:

Execute       : 6 443.00 ms
Read          : 6 137
Writes        : 6 064
Fetches       : 2 261 901
Marks         : 374 910
NIR           : 562 445
Expunges      : 187 455

Упс! Мы ожидали увидеть сравнимые цифры по времени выполнения для первой и второй таблицы, но разница составила 76 (!!!) раз. Все из-за наличия в первой таблице индекса, который при подсчете количества никак не используется, но катастрофически тормозит сборку мусора.

Попытаемся выяснить как влияет наличие индекса на выполнение операции CURRENT OF. Возвращаемся к исходной базе данных. Будем удалять записи из таблицы t1 с помощью конструкции CURRENT OF:

Execute       : 8 050.00 ms
Read          : 6 138
Writes        : 6 064
Fetches       : 2 765 717
Marks         : 251 145
NIR           : 1 000 000
Deletes       : 251 145

В одно время с удалением из таблицы, по которой нет индекса.

Второй цикл удаления:

Execute       : 688 480.00 ms
Read          : 240 628
Writes        : 240 341
Fetches       : 4 592 695
Marks         : 945 817
NIR           : 748 855
Deletes       : 186 248
Expunges      : 251 145

Самое большое время, которое нам пришлось наблюдать сегодня. Сборка мусора и индекс явно не дружат друг с другом.

Для оценки влияния сборки мусора повторим все операции с самого начала на исходной базе данных с флагом подключения no_garbage_collect. Первое удаление из таблицы t1 (поиск по индексированному полю):

Execute       : 12 418.00 ms
Read          : 239 735
Writes        : 6 231
Fetches       : 4 260 434
Marks         : 249 809
NIR           : 999 939
IR            : 249 809
Deletes       : 249 809

Странно, но NIR снова не миллион. Куда деваются около шестидесяти чтений непонятно.

Второе удаление:

Execute       : 9 563.00 ms
Read          : 181 842
Writes        : 6 189
Fetches       : 3 702 914
Marks         : 187 849
NIR           : 750 162
IR            : 187 849
Deletes       : 187 849

Записей стало меньше и время сканирования и удаления уменьшилось соответственно.

Для второй таблицы удаление через курсор. Первый проход:

Execute       : 8 112.00 ms
Read          : 6 138
Writes        : 6 064
Fetches       : 2 764 184
Marks         : 250 634
NIR           : 1 000 000
Deletes       : 250 634

Второй:

Execute       : 8 034.00 ms
Read          : 6 137
Writes        : 6 064
Fetches       : 2 574 131
Marks         : 187 283
NIR           : 749 366
Deletes       : 187 283

Обратите внимание, что цифры идентичны тем, которые мы имели при включенной сборке мусора. Т.е. нет индекса и сборка мусора не проблема.

Получаем количество записей для первой таблицы:

Execute       : 546.00 ms
Read          : 6 137
Writes        : 2
Fetches       : 2 012 281
Marks         : 0
NIR           : 562 342

Для второй:

Execute       : 531.00 ms
Read          : 6 137
Writes        : 2
Fetches       : 2 012 281
Marks         : 0
NIR           : 562 083

Выводы:

• Нижесказанное применимо к частным случаям массовой обработки данных.
• Если по таблице нет индексов, то включение/выключение сборки мусора практически не влияет на производительность.
• Если по таблице созданы индексы, то сборка мусора способна радикально, в десятки и сотни раз, замедлить ход процесса.
• В нашем примере CURRENT OF был на 20-25% быстрее чем поиск по индексированному полю.
• CURRENT OF может применяться на таблицах, где индексов нет вообще. Совершенно очевидно, что удаление с поиском по неиндесированному полю выполнялось бы в нашем случае часами, если не сутками.

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

На заметку. Если в процедуре встретится конструкция:

...
INSERT INTO table1 
  SELECT * FROM table2;
...

то возникнет зависимость между процедурой и всеми полями из table2, хотя они тут и не прописаны явно.

Попытка №2

Упростим ситуацию и будем рассматривать только удаление из базы данных документов указанных типов, даты меньше заданной (D). Под документом в широком смысле мы понимаем:

• Cовокупность записей в таблице GD_DOCUMENT, шапка и позиции.
• Присоединенные к ним записи 1-к-1 (включая записи в специфических таблицах документов, связанные через поле documentkey).
• Связанные с ними таблицы с уточняющей информацией (например, USR$INV_ADDINFO). По сути это 1-к-1, но через отдельное поле внешний ключ. Из структуры БД мы не можем знать, что некоторая таблица несет уточняющую информацию, поэтому определим список таких таблиц через константу в программе.
• Бухгалтерские проводки (AC_RECORD, AC_ENTRY).
• Складское движение (INV_CARD, INV_MOVEMENT).
• Ко всему вышеперечисленному связанные записи в кросс-таблицах множеств.

Типы указываем, так как некоторые документы (например, зарплатные или по ОС) удалять нельзя за весь период.

Процесс "обрезания" базы сводится к следующему:

1. Вычисляем бухгалтерские и складские остатки на заданную дату. Сохраняем их в базе.
2. Формируем массив М из ИД записей, которые останутся в результирующей базе:
   1. Сканируем все таблицы, не относящиеся к документам. Добавляем в М ИД записи (если он есть и целочисленный) и идентификаторы всех ссылок на таблицы документов. Прочие ссылки нас не интересуют, так как все записи из прочих таблиц в любом случае будут сохранены.
   2. Выбираем из таблиц документов указанных типов записи с датой больше либо равно D. Сканируем их аналогичным образом, добавляя ИД в М.
   3. Сканируем аналогичным образом таблицы документов остальных типов.
3. Запоминаем информацию о структуре БД в части обрабатываемых таблиц.
4. Отключаем ключи, индексы, триггеры, ограничения на таблицах, из которых будем удалять записи.
5. Удаляем записи, отсутствующие в М.
6. Удаляем из таблицы GD_RUID записи для удаленных ИД.
7. Восстанавливаем структуру БД.

Подготовка исходной базы данных:

1. Пользователю рекомендуется провести бэкап-разбэкап исходной базы перед началом процесса.
2. Кэш не должен превышать 500 Мб.
3. Режим принудительной записи должен быть отключен
4. Если задействован наш механизм замены внешних ключей, то отключенные ключи должны быть включены.
5. Аудит средствами платформы Гедымин должен быть отключен.

Для оценки эффективности процесса будем использовать количество записей до и после "обрезания" в затрагиваемых процессом таблицах.

Синхронизация данных между мобильным устройством на Android и сервером Firebird SQL

Ради скорости и независимости от сетевого соединения мобильное приложение должно использовать локальную базу, которая периодически синхронизируется с рабочей базой данных предприятия. Пусть мобильная БД состоит из таблиц М1, М2,... Мn и используется только для чтения. Таблицы имеют целочисленные первичные ключи. Рассмотрим вариант однонаправленной синхронизации:

1. Версию данных будем обозначать целым числом и хранить в отведенной для этого таблице. 0 -- соответствует чистой БД.
2. На сервере создадим таблицы S1, S2,... Sn, идентичные по структуре таблицам из мобильной БД. Указанные таблицы хранят текущую версию данных на сервере.
3. На сервере создадим GLOBAL TEMPORARY таблицы уровня транзакции T1, T2,... Tn, также как и S идентичные по структуре таблицам из мобильной БД.
4. На сервере создадим таблицу для команд обновления данных. Каждая запись таблицы хранит в текстовом виде список команд для перевода версии J в версию K.
5. Команда состоит из заголовка и тела (или только из заголовка). Поддерживаются следующие команды:
   1. RALL -- очистить все таблицы.
   2. R -- очистить одну таблицу. В заголовке передается имя таблицы.
   3. D -- удалить записи из таблицы. В заголовке передается имя таблицы. В теле -- список ИД удаляемых записей.
   4. U -- вставить или обновить записи в таблице. В заголовке передается имя таблицы и список полей. В теле передаются значения полей для каждой записи. Первое значение -- всегда ИД записи. Так как присутствие идентификатора обязательно, в списке имя поля первичного ключа не перечисляется.

   Для одной и той же таблицы в списке может присутствовать неограниченное число команд.

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

6. Процедура формирования очередной версии данных на сервере вызывается вручную или автоматически, по установленному расписанию:
   1. Стартует транзакция.
   2. Сформированные данные данные помещаются в таблицы T.
   3. На основе сравнения S и T создаются и записываются в журнал команды изменения данных от версии N к N + 1.
   4. Таблицы S очищаются, в них переносятся записи из T.
   5. Инкрементируется номер версии данных.
   6. Транзакция завершается.
7. Диалог между мобильным приложением и сервером:
   1. Мобильное приложение подключается к серверу и сообщает свою версию структуры БД и версию данных (V).
   2. Если версия структуры БД нас не устраивает, то сообщаем пользователю, что приложение следует обновить.
   3. Из журнала извлекаем команды для обновления данных от версии V к N и передаем их мобильному приложению.
   4. Если у нас нет прямого перехода от V к N, то последовательно ищем команды для апгрейда от V до V + 1 и формируем из них единый список для инкрементного обновления.
   5. Передаем список команд на мобильное устройство.
   6. По окончании процесса применения полученных команд инкрементируем номер версии данных мобильного приложения.
   7. Если мобильное приложение передало на сервер 0, то вместо передачи длинной цепочки последовательных обновлений, сразу формируем команды на основе содержимого таблиц S.
   8. Процесс обновления выполняется на одной транзакции, которая подтверждается только в случае успешного выполнения всех команд.

Неприятное ограничение рекурсивных 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 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 позволяют избежать зацикливания на кольцевых ссылках, если такие попадутся в исходных данных.

Подключение Embedded SWI-Prolog в Гедымин

Есть два варианта подключения Пролога. Первый -- мы обращаемся к embedded SWI-Prolog из кода на VBScript через глобальный объект (назовем его PL). Второй -- алгоритм пишется непосредственно на Прологе, а доступ к функционалу платформы происходит через Foreign Predicates.

Обращение к Прологу через глобальный объект мы рассмотрим на сравнительном примере доступа к реляционным данным из макроса на Гедымине. Пусть данные находятся в двух таблицах: GD_CONTACT(ID, CITYKEY, NAME) и GD_PLACE(ID, NAME):

ID  CITYKEY  NAME
======================
1   1        Company A
2   1        Company B
3   2        Company C

ID  NAME
======================
1   Minsk
2   Berioza 

Для извлечения cписка компаний по заданному наименованию города мы пишем SQL запрос, который выполняем через объект класса TIBSQL:

...
Dim q
Set q = Creator.GetObject(nil, "TIBSQL", "")
Set q.Transaction = gdcBaseManager.ReadTransaction
q.SQL.Text = _
  "SELECT c.name " & _
  "FROM gd_contact c JOIN gd_place p " & _
  "  ON p.id = c.citykey " & _
  "WHERE p.name = 'Minsk' "
q.ExecQuery
While Not q.EOF 
  ...
  q.Next
WEnd 
...

Теперь изобразим тоже самое на Prolog.

Исходные данные в виде предикатов Prolog выглядят так:

gd_contact(1, 1, 'Company A').
gd_contact(2, 1, 'Company B').
gd_contact(3, 2, 'Company C').

gd_place(1, 'Minsk').
gd_place(2, 'Berioza').

Правило связи компании с городом через ключи:

bycity(City, Name) :- 
  gd_place(CityID, City),
  gd_contact(_, CityID, Name).

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

? bycity('Minsk', Name).

Что мы видим из данного примера?

1. Необходим класс для доступа к Прологу из макросов. Далее по тексту будем называть его PL. Экземпляр данного класса создается стандартным образом через глобальный объект Designer/Creator. Каждый экземпляр может иметь свое имя.
2. PL должен иметь метод для формирования предикатов на основании заданной SQL выборки. Пример кода:

   ...
   Call PL.MakePredicates(_
     "SELECT id, citykey, name FROM gd_contact", _
     gdcBaseManager.ReadTransaction, _ 
     "gd_contact", "name")
   ...
   

   Последний параметр -- имя выборки, которое будет использоваться для формирования имени .pl файла в режиме отладки.

   Пусть это и не касается непосредственно сопряжения Пролога с Гедымином, но здесь стоит вспомнить об идее изоляции разработчика от работы с SQL запросами и инкапсуляции последних внутрь т.н. SQL объекта. SQL объект -- это заранее написанный, проверенный и оптимизированный SQL запрос, загружаемый на базу стандартным образом через настройки или пространства имен. Для выполнения, программист обращается к нужному SQL объекту по его РУИДу.

3. PL должен иметь метод для формирования предикатов на основании бизнес-класса. На вход передается класс-подтип, подмножество, дополнительные условия выборки. Сразу следует договориться о стандартном механизме (способе) загрузки данных мастер-дитэйл (например, документ накладная с позициями).

   ...
   Call PL.MakePredicates2(_
     "TgdcCompany", "", "ByID", Array(777777), "", _
     gdcBaseManager.ReadTransaction, _ 
     "gd_contact", "name")
   ...
   

4. PL должен иметь метод для формирования предикатов на основании существующего набора данных (дата-сета).
5. PL должен иметь метод для ручного формирования произвольных предикатов.
6. PL должен иметь метод для вычисления цели. Результат вычисления может быть как одиночное значение, так и выборка. Для работы с последней предусмотрены методы итерации (EOF, First, Next) и методы для обращения к параметрам по имени.
7. С помощью соответствующих методов результат вычисления может быть напрямую помещен в указанную таблицу в базе, клиент-датасет в оперативной памяти, или на его основе могут быть созданы новые бизнес-объекты в БД.
8. Предикаты, которые создает разработчик, должны храниться в базе данных аналогично тому, как сейчас хранится код VBScript.
9. В дереве Проводника Редактора скрипт-объектов следует добавить корневой раздел для кода на Прологе. Внутри раздела пролог-скрипты могут размещаться в папках.
10. Минимальная единица для загрузки в объект PL -- один пролог-скрипт. Каждый скрипт имеет уникальное имя и РУИД. Загрузка осуществляется по РУИДу. Внутри скрипта могут встречаться директивы %#INCLUDE для загрузки других пролог-скриптов (аналогично директиве '#INCLUDE для VBScript).
11. Стандартные библиотеки SWI-Prolog, которые в дистрибутиве занимают 8 Мб в 377 файлах, будут храниться в базе данных точно так же, как и обычные пролог-скрипты.
12. Отладка:
    1. Для отладки на компьютере должен быть установлен полный вариант SWI-Prolog и, возможно, SWI-Prolog Editor.
    2. Местоположение SWI-Prolog и каталога для временных файлов задается в параметрах платформы.
    3. Режим отладки активизируется в параметрах редактора скрипт-объектов для списка конкретных экземпляров PL, заданных их именами. Если список пуст, то режим активируется для всех PL объектов.
    4. Когда активирован режим дебаггера предикаты записываются в текстовые файлы .pl программ во временном каталоге. При этом файлы формируются следующим образом:
       • Для каждого пролог-скрипта отдельный файл.
       • Результаты вызова MakePredicatesX помещаются в файлы в соответствии с заданными именами.
    5. При вызове метода вычисления цели в режиме дебаггера загружается SWI-Prolog или SWI-Prolog Editor. В среду выполнения/отладки подгружаются созданные временные файлы.
    6. Гедымин ждет завершения работы процесса SWI-Prolog.
    7. После окончания отладки проверяются файлы для выгруженных пролог-скриптов. Если обнаружены изменения Гедымин предлагает загрузить их в базу данных.

В Редактор SQL добавлена вкладка Бизнес-классы:

Выбрав класс в списке можно перенести его SelectSQL в окно выполнения запроса или открыть форму просмотра.

PS: Знаю, знаю, фильтр не помешал бы. Но, и Вильня не сразу строилась.

Просмотр объекта по ссылке

Добавлено в Гедымин. Если в окне редактора SQL, в таблице с результатом выполнения запроса, установить курсор на поле-ссылку, то с помощью кнопок на панели инструментов (на рисунке ниже) можно: открыть объект на просмотр/изменение, удалить объект, открыть форму просмотра объекта.