How to find useless foreign keys in a database

With times, old databases tend to overgrow with unnecessary or just empty fields. Whilst regular fields are not much of a concern, those with foreign key constraints, especially in tables with millions of records, would needlessly inflate the database file and put performance penalty on every insert/update/delete operation, as appropriate index need to be updated.

For example, an index on a field that holds nothing more than NULL values over a table with 100 000 000 records has a size approximately of 600 MB.

The query below helps to spot fields with foreign keys, which contain no more than a given count of unique values (by default, the variable maxuniqvalues is set to 1 to find all fields which are empty or contain exactly one value). Additionally, the condition on minimal record count in a table in question could be set through variable minreccnt.

The result data set includes the following columns: name of the relation, number of records in the relation, name of the field, value (only the first value is shown), and a list of objects dependent on the field. The latter will help a lot if the field to be deleted later.

  EXECUTE BLOCK
  RETURNS(
    rn VARCHAR(31),
    reccnt INTEGER,
    fkfieldname VARCHAR(31),
    val INTEGER,
    dependent VARCHAR(8192)
  )
AS
  DECLARE VARIABLE minreccnt DOUBLE PRECISION = 1000000;
  DECLARE VARIABLE maxuniqvalues DOUBLE PRECISION = 1;
BEGIN
  FOR
    SELECT
      rc.rdb$relation_name,
      CAST((1 / idx.rdb$statistics) AS INTEGER),
      idxsfk.rdb$field_name,
      (SELECT
         LIST(TRIM(d.rdb$dependent_name))
         FROM rdb$dependencies d
         WHERE 
           d.rdb$depended_on_name = rc.rdb$relation_name
           AND 
           d.rdb$field_name = idxsfk.rdb$field_name)
    FROM
      rdb$relation_constraints rc
      JOIN rdb$indices idx
        ON idx.rdb$index_name = rc.rdb$index_name
      JOIN rdb$index_segments idxs
        ON idxs.rdb$index_name = idx.rdb$index_name
        AND idxs.rdb$field_position = 0
      JOIN rdb$relation_constraints rcfk
        ON rcfk.rdb$constraint_type = 'FOREIGN KEY'
        AND rcfk.rdb$relation_name = rc.rdb$relation_name
      JOIN rdb$indices idxfk
        ON idxfk.rdb$index_name = rcfk.rdb$index_name
      JOIN rdb$index_segments idxsfk
        ON idxsfk.rdb$index_name = idxfk.rdb$index_name
        AND idxsfk.rdb$field_position = 0
    WHERE
      rc.rdb$constraint_type = 'PRIMARY KEY'
      AND
      (idx.rdb$statistics > 0 
        AND idx.rdb$statistics < (1.0 / :minreccnt))
      AND
      idxfk.rdb$statistics >= (1.0 / :maxuniqvalues)
    ORDER BY
      idx.rdb$statistics ASC
    INTO
      :rn, :reccnt, :fkfieldname, :dependent
  DO BEGIN
    EXECUTE STATEMENT 
      'SELECT FIRST 1 ' || 
      :fkfieldname || 
      ' FROM ' || 
      :rn
      INTO :val;
    SUSPEND;
  END
END

Конфигурация небольшого сервера для базы данных Гедымина

На днях один клиент обратился с просьбой порекомендовать конфигурацию сервера под базу данных размером в районе 50 Гб и количеством одновременных подключений 25+. Думаю такая рекомендация может быть интересна и другим нашим клиентам, а также как исторический материал, который через некоторое время позволит сравнить цены и оценить скорость развития вычислительной техники.

Курс доллара на момент написания где-то в районе 2.5 руб за 1 USD.

1. Память. Ставьте 256 ГБ самой быстрой DDR4, которую будет поддерживать материнская плата. Это на вырост. Если 256 Гб дорого, то никак не меньше 128 Гб. Планка на 32 Гб стоит сейчас где-то в районе 400 руб.
2. Процессор AMD Ryzen 9 5900X (12 ядер, ~1500 руб) или 5950X (16 ядер, ~2200 руб).
3. 4 планки NVME по 1 Tb. Что-нибудь вроде SSD Samsung 980 Pro 1TB (по 550 руб за штуку). Из них собрать два зеркала RAID 1. Одно использовать для размещения операционной системы, темп каталога и папки с архивами бд, а второе -- для размещения рабочего файла базы данных.
4. Материнская плата с поддержкой всего вышеперечисленного (в т.ч. PCI Express 4.0). Сеть желательно 10 Gbit Ethernet до центрального свитча. Видео встроенное в материнскую плату или самое простое. Монитор можно б/у-шный тоже самый простой.
5. Корпус можно самый обычный брать. Блока питания на 600 W хватит вполне.
6. Надежный бесперебойник.

На вскидку, суммарная стоимость будет:

8 * 400 + 1500 + 550 * 4 + 600 + 300 + 400 = 8200, округленно -- 9000 руб.

Этой техники хватит без проблем, по производительности, на 5 ближайших лет.

Каждой программе по своей "проблеме 2000 года"

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

В Гедымине есть своя "проблема  2000 года", а точнее проблема 32-х битного идентификатора бизнес-объекта. Чтобы быть еще более точным, не самого идентификатора а генератора GD_G_UNIQUE, с помощью которого идентификаторы добываются.

Данный генератор стартует со значения 147 000 000 и увеличивается по мере запроса новых идентификаторов. Причем, для сокращения количества запросов к серверу, увеличиение идет с шагом в 100, а неиспользованный на момент завершения программы интервал сохраняется в системном реестре.

Так как у нас ИД объекта -- это знаковое 32-х битное целое, то всего доступно чуть более 2-х миллиардов идентификаторов (мы не учитываем первые 147 миллионов, которые выделены под системные объекты платформы).

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

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

Что делать?

Теоретически есть два варианта решения проблемы. Первый -- это сдвинуть, утрамбовать все идентификаторы "вниз" на выявленные пустые пробелы. Затем изменить значение генератора в соответствии с максимальным ИД в базе.

Технически, для этого придется выполнить следующую последовательность шагов:

1. Сохранить все существующие ИД в некоторой структуре.
2. Построить таблицу соответствия старый ИД -- новый ИД.
3. Отключить все внешние и первичные ключи.
4. Обновить ВСЕ записи в базе данных, заменяя старые идентификаторы на новые.
5. После предыдущего шага желательно выполнить бэкап-восстановление БД для чистки мусора.
6. Восстановить все первичные и внешние ключи.

На базах размером свыше 100 Гб мы не представляем как можно выполнить указанную последовательность в доступное нам технологическое окно (обычно 8-10 часов).  И, если где-то в коде, используется привязка к ИД записи, вместо РУИД, то такой код перестанет работать. К тому же, надо будет как-то вычистить из реестров всех компьютеров сохраненные интервалы или одномоментно заменить все экзешники, скорректировав алгоритм кэширования.

Второй вариант:

1. Создать таблицу для доступных интервалов идентификаторов GD_AVAILABLE_ID.
2. При обращении к функции gdcBaseManager.GetNextID проверять не приблизились ли мы к опасной черте. 
3. Если нет, то работать по-старому -- через генератор. 
4. Если уже пора, то заполняем таблицу и по-мере необходимости берем очередной интервал из нее.

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

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

DATABASE TRIGGERS в Гедымине

Буквально на днях появится возможность создавать в Гедымине DATABASE TRIGGERS. Такие триггеры могут быть назначены на следующие события:

• CONNECT
• DISCONNECT 
• TRANSACTION START
• TRANSACTION COMMIT 
• TRANSACTION ROLLBACK

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

Окно работает на своей транзакции, которая по итогу или комитится -- кнопка Ок, или отменяется.

Периоды бронирования заносятся в отдельную таблицу (назовем её BOOKINGS). Здесь хранятся начало, окончание, ссылка на номер и ссылка на клиента. Перед добавлением записи происходит проверка не занят ли уже данный интервал.

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

В такой ситуации на помощь приходит триггер на комит транзакции. Сначала, триггером на добавление/изменений записей в таблице бронирования мы выставим флаг о необходимости проверки при комите текущей транзакции:

CREATE OR ALTER TRIGGER after_ins_update
  FOR bookings
  ACTIVE
  AFTER INSERT OR UPDATE
  POSITION 32000
AS BEGIN
  RDB$SET_CONTEXT('user_transaction', 'check_tr', 1);
END

Затем, если флаг выставлен, сделаем проверку корректности данных:

CREATE OR ALTER TRIGGER transaction_check
  ACTIVE
  ON TRANSACTION COMMIT
  POSITION 32000
AS BEGIN
  IF (RDB$GET_CONTEXT('user_transaction', 'check_tr') = 1) THEN
  BEGIN
     --   проверяем не пересекаются ли интервалы
     --   если пересекаются, то вызываем EXCEPTION
  END
END

В тексте исключения можно подробно указать пользователю с каким именно бронированием возник конфликт.

Как не запутаться в зависимостях объектов ПИ

Уже миллион раз успел пожалеть, что сделал в Гедымине режим добавления объектов в ПИ "с зависимыми". Это мощная функция, которая требует от применяющего досконального знания теории реляционных баз данных, структуры конкретной БД, внутреннего устройства своего прикладного решения.

Добросовестная разработка с использованием данной галки требует следования определенной последовательности операций:

1. Добавить объект в ПИ с зависимыми.
2. Открыть список ПИ. Найти нужное и по-порядку, по списку входящих в него объектов, проверить что именно добавилось. Убрать лишнее.
3. Открыть список зависимостей для выбранного ПИ и проверить какие зависимости добавились. При необходимости изменить.
4. Сохранить ПИ в репозиторий.
5. Взять чистый эталон и загрузить на него пакет (каждый функционально завершенный и обособленный модуль должен быть оформлен в виде пакета).
6. Протестировать работоспособность загруженного пакета.

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

Почему?

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

1. В такой базе может присутствовать целый букет устаревших, временных, давно забытых ПИ. При добавлении "с зависимостями" они подхватятся и затянутся в список зависимых ПИ.
2. В таблицах на таких БД могут присутствовать устаревшие, уже не используемые поля. Мало того, что они затянутся в ПИ, так еще затянутся и объекты на которые они ссылаются.
3. Устаревшие скрипт-функции могут привести к тому, что код, работающий на разработочной базе, не будет работать на чистой базе, собранной из актуальных ПИ.

Прозаическая реальность оказалась далека от ожиданий. Добавление с зависимостями применяется чтобы на скорую руку получить результат, а какого он будет качества мало кого волнует. Потом, когда выясняется что ПИ не устанавливается, требуется затратить в 10 раз больше усилий, чтобы распутать зависимости между объектами и файлами ПИ.

Если не уверен в себе и на все 100% не понимаешь что происходит "под капотом" системы, то каждый объект надо добавлять в ПИ вручную, по-отдельности, в порядке реляционной зависимости. Что особенно ценно, при добавлении вручную маловероятны ситуации "запутывания" связей между объектами или файлами ПИ.

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

Пример: пусть мы создаем небольшое прикладное решение, расширяющее стандартный пакет Зарплата и Кадры. Назовем его Наряды.

1. Поскольку решение не велико, то объекты разобъем по следующим ПИ:
   • GS.Зарплата.Наряды.Метаданные -- домены, таблицы, триггеры и т.п.
   • GS.Зарплата.Наряды.Хранилище -- экранные формы.
   • GS.Зарплата.Наряды.Макросы -- перекрытые методы, локальные макросы форм.
   • GS.Зарплата.Наряды.Отчеты -- печатные формы.
2. Зависимости настроим следующим образом:
   • GS.Зарплата.Наряды.Метаданные зависит от пакета GS.Зарплата.
   • GS.Зарплата.Наряды.Хранилище зависит от GS.Зарплата.Наряды.Метаданные.
   • GS.Зарплата.Наряды.Макросы зависит от GS.Зарплата.Наряды.Хранилище.
   • GS.Зарплата.Наряды.Отчеты зависит от GS.Зарплата.Наряды.Макросы.
3. Создадим пакет GS.Зарплата.Наряды и сделаем его зависимым от GS.Зарплата.Наряды.Отчеты. Обратите внимание, что нет необходимости в пакете ставить зависимость от всех ПИ нашего решения, так как между ними итак уже присутствуют зависимости.
4. Сохраним ПИ в файлы и расположим их в подкаталоге Наряды в папке Зарплата и кадры.
5. Запишем в репозиторий.
6. Подключимся к чистой эталонной базе данных ипоставим на загрузку пакет GS.Зарплата.Наряды. Проверим, чтобы загрузка не кидала ошибок. Проверим работоспособность после загрузки.

Новые поля в GD_CURRRATE и встроенная функция пересчета валюты

После деноминации курсы некоторых валют (в частности российского рубля) стали такими маленькими, что требуют хранения минимум 6-ти знаков после запятой. Для базы данных всё равно (домен dcurrrate у нас имеет точность до 10-ти знаков после запятой), но если такое значение где-то пройдет через тип Currency сохранятся только четыре знака, т.е. произойдет потеря точности. Мы добавили в таблицу GD_CURRRATE два поля amount и val. Где val -- это курс валюты за количество единиц amount. Теперь для российского рубля можно использовать amount=100 и четыре знака после запятой, как это делает наш Национальный Банк, когда публикует валютные сводки. При amount=1, val=coeff. Пересчет полей происходит на триггере. Весь старый код, который читает или изменяет только поле coeff сохранил свою работоспособность.

Попутно мы внесли следующие улучшения:

• Для курса валюты можно указать кто его установил. Например, курс Национального банка, курс Валютно-фондовой биржи, курс банка Васи Пупкина и т.п.
• Для курса можно указать не только дату, но и время, если курс меняется несколько раз в течение дня.

Для пересчета сумм из одной валюты в другую (в том числе и с использованием кросс-курса) в платформу добавлена функция System.GetCurrRate.

GDMNN: Задача #1

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

Цель:

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

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

Деноминация 2016. Технические аспекты

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

В отличие от конкурирующих решений наш подход сохраняет всю историю, весь документооборот предприятия до 1 июля.

Первая проблема, которая возникает, это возможная потеря точности. Платформа использует тип Currency, который позволяет обрабатывать денежные величины с точностью до 4-го знака после запятой. После деноминации 0.0001 в новых деньгах будет соответствовать 1-му старому рублю. Все что меньше рубля потеряется (округлится по правилам математического округления). Хотя 1 неденоминированный рубль очень маленькая величина (коробок спичек сейчас стоит 400 рублей) можно представить базу данных, где мелкий товар учитывается поштучно и цена единицы содержит копейки -- пуговицы, швейные иголки, болты, гайки и т.п. Мы рекомендуем в таком случае провести на 30 июня 2016 г. переоценку товара, округлив учетные цены до целых рублей.

Вторая проблема -- округление до целого числа в скрипт-функциях и отчетных формах через вызов функций Round, Int, Fix, CLng, CInt или обращение к полю через свойство AsInteger. Мы внесем исправления в типовые прикладные решения, но решить задачу в общем случае, анализируя программный код и автоматически заменяя вызовы функций нам не представляется возможным. Единственный путь -- заранее создать копию рабочей базы данных, деноминировать ее и затем тестировать на ней все режимы работы и устранять выявленные проблемы.

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

Мы видим такое решение данной проблемы: предприятие продолжает после 1 июля вести учет в неденоминированных рублях при этом, непосредственно в печатных формах товарных накладных, денежные величины делятся на 10 000; суммы, поступающие из системы банк-клиент умножаются на 10 000, соответственно, суммы, уходящие в банк-клиент, делятся на 10 000; входящие накладные умножаются на 10 000 и т.д. После того, как налоговые декларации будут сданы можно будет проводить деноминацию базы данных и переходить на учет в деноминированных рублях.

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

Деноминация базы данных производится с помощью отдельной утилиты dnmn.exe, которая предоставляется клиентам на абонентском обслуживании. Мы обработали несколько десятков баз данных и сформировали список полей для деноминации. Если на вашем предприятии используются частные решения, то перед запуском процесса следует добавить нужные поля в список деноминируемых.

Прямой доступ к DBF файлам

Устав бороться с "тормозами" и ошибками стандартных ODBC драйверов для доступа к DBF файлам мы включили компонент TDBF в исходный код Гедымина. Пример использования можно посмотреть тут.

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

Интервью с 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.

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

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

Попытка №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. Аудит средствами платформы Гедымин должен быть отключен.

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

Вот почему у нас куча односимвольных типов объявлена как VARCHAR вместо CHAR? Ладно бы еще всякие там activity флажки в плане счетов, но признак дебет/кредит для записи проводки это никуда не годится. Десять миллионов проводок, двадцать миллионов записей в AC_ENTRY == сорок миллионов байт на хранение длины поля, которая всегда равна 1. Может в этом и был когда-то какой-то смысл, но я решительно ничего не могу вспомнить.

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

Ручное изменение поля типа ссылка

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

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

Предположим, в некоторой таблице TABLE_A находится поле USR$FIELD_A типа USR$DFIELD_A, которое ссылается на таблицу TABLE_B. Изменим его так, чтобы оно ссылалось на таблицу TABLE_C. Поле USR$NAME из TABLE_C используется для отображения наименований объектов в выпадающих списках.

1. Идем Исследователь-Сервис-Атрибуты-Таблицы. Находим в списке нашу таблицу и открываем в диалоговом окне. Переходим на вкладку Скрипт.
2. Ищем строку вида:

   ALTER TABLE TABLE_A ADD CONSTRAINT USR$FKTABLE_ANN FOREIGN KEY (USR$FIELD_A) REFERENCES TABLE_B (ID) ON UPDATE CASCADE;

3. Из найденой строки узнаем имя ограничения. В нашем случае это USR$FKTABLE_ANN, где вместо NN будут какие-нибудь цифры.
4. Идем в окно SQL редактора и выполняем команду:

   ALTER TABLE TABLE_A DROP CONSTRAINT USR$FKTABLE_ANN

5. Там же набираем и выполняем команду:

   ALTER TABLE TABLE_A ADD CONSTRAINT USR$FKTABLE_ANN FOREIGN KEY (USR$FIELD_A) REFERENCES TABLE_C (ID) ON UPDATE CASCADE ON DELETE CASCADE

   Обратите внимание, что правила ON UPDATE и ON DELETE вы должны указать в соответствии с логикой вашей задачи.

6. Как вы поняли, первая команда удалила старую ссылку, а вторая создала новую. Остается сообщить об изменившейся ссылке Гедымину. Для начала следует в разделе Исследователь-Сервис-Атрибуты-Таблицы узнать идентификаторы таблицы TABLE_C и ее поля USR$NAME.

   Узнать ИД проще простого: выделяем запись в гриде, правая кнопка мыши, команда Свойства...

7. Не выходя из формы просмотра с таблицами ищем TABLE_A и в ней поле USR$FIELD_A. Затем, правая кнопка мыши, комадна Свойства..., вкладка Данные:
   1. DELETERULE -- прописываем правило для удаления записи, которое мы указали в команде создания FOREIGN KEY. Например, CASCADE, RESTRICT и т.д.
8. Идем Исследователь-Сервис-Атрибуты-Домены и находим тип USR$DFIELD_A. Правая кнопка мыши, комадна Свойства..., вкладка Данные:
   1. REFTABLE -- Прописываем имя TABLE_C.
   2. REFLISTFIELD -- Имя поля из таблицы TABLE_C, которое будет использоваться для отображения в выпадающих списках.
   3. REFTABLEKEY -- ИД таблицы TABLE_C. Его мы определили выше.
   4. REFLISTFIELDKEY -- ИД поля из таблицы TABLE_C, которое будет использоваться для отображения в выпадающих списка.
   5. REFLNAME -- локализованное наименование таблицы справочника.
   6. REFLISTLNAME -- локализованное наименование поля для отображения в выпадающих списках.
   Нажимаем Ок для сохранения изменений.

В данном примере мы предполагаем, что тип USR$DFIELD_A используется единственный раз только для нашего поля USR$FIELD_A. В противном случае, следует создать новый тип, настроить его и вручную перевести на него ссылку, отредактировав запись для поля USR$FIELD_A в таблице AT_RELATION_FIELDS.

Опять двадцать пять...

...или почему задвоились объекты при переносе потоком.

Когда 3 апреля грохнулась база предприятия Б, мы восстановили архив на 30 марта. Так как часть информации из исходной базы данных могла быть прочитана, объекты за 1-2 апреля были сохранены в поток и загружены на новую базу. В результате, некоторые записи задвоились. Почему?

Как известно, при загрузке из потока система пытается найти объект по его РУИДу или с помощью запроса из метода CheckTheSameStatement. Последний определен только для узкого круга классов, так что вся надежда на РУИДы. Если РУИД загружаемого объекта не найден, то считается что такого объекта нет и создается новая запись.

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

1. Если работа идет под учетной записью Administrator.
2. В момент открытия диалогового окна Свойства объекта.
3. В процессе сохранения объекта в поток.

Первый раз РУИД сформируется при сохранении в поток на поврежденной базе данных. Естественно, что поиск по этому РУИДу при загрузке из потока на базу данных из архива ничего не даст. Система примет загружаемую запись за новую и создаст дубликат.

Можно ли было избежать такого неприятного развития событий? Да. Так как архивная база является копией исходной по состоянию на 30 марта, все объекты в обеих базах, созданные до указанной даты, имеют одинаковые идентификаторы. Следовательно, если мы:

1. Не меняли идентификатор базы данных, восстановленной из архива. (Заметим, что даже если идентификатор базы был изменен, ничего страшного не произойдет. Главное — обеспечить равенство идентификаторов двух баз до начала выполнения шагов 2 и 3.)
2. В поврежденной базе создали вручную РУИДы для всех записей.
3. В архивной базе создали вручную РУИДы для всех записей.

то, можно спокойно переносить объекты потоком без угрозы задвоения.

Принудительно создать РУИДы для всех записей в базе можно с помощью приведенного здесь запроса. Обратите внимание на использование оператора MERGE.

Специфика ORM модели платформы Гедымин

ORM Гедымина имеет ряд нестыковок. Известны они давно и очередной раз привлекли к себе внимание во время разработки автоматизированного теста (см. предыдущий пост). Ниже перечислены некоторые из них:

• Не учитывается разница между истинным абстрактным базовым классом (например, TgdcBase), и базовым классом c конкретной ListTable, который служит фундаментом для иерархии наследованных классов (например, TgdcBaseContact).
• Некоторые объекты могут использоваться только внутри кода других объектов с выполнением магических действий по программной настройке:
  1. Все наследники TgdcInvBaseRemains
  2. TgdcInvCard
  3. TgdcLink
  4. TgdcAcctDocument
  5. TgdcAcctEntryRegister
• К списку выше можно добавить все объекты позиций документов, создать запись в которых можно только при настроенной связи мастер-дитэйл с шапкой или непосредственно указывая ИД головной записи. При этом связь надо настраивать вручную, так как объекты шапки и позиций автономны и ничего не знают друг о друге. Частично задача решена для документов и только с вовлечением интерфейса пользователя — платформа формирует экранную форму просмотра на основе информации о типе документа.
• В ситуациях, когда бизнес-сущность хранится в нескольких таблицах, список последних нигде не задан явно. Мы можем полагаться на сформированный запрос или обращаться к метаданным. В последнем случае мы рискуем получить слишком большой список связанных таблиц, не все из которых являются частью нашего бизнес-класса. Например, окно Свойства объекта выдает такое множество связанных таблиц для документа Отпуск товара на сторону из стандартного пакета настроек:

  BN_BANKCATALOGUE, BN_BANKCATALOGUELINE, BN_BANKSTATEMENT, BN_BANKSTATEMENTLINE, BN_CURRCOMMISSION, BN_CHECKLIST, BN_CURRBUYCONTRACT, BN_CURRCOMMISSSELL, BN_CURRCONVCONTRACT, BN_CURRLISTALLOCATION, BN_CURRSELLCONTRACT, BN_DEMANDPAYMENT, CTL_INVOICE, CTL_RECEIPT, GD_TAXDESIGNDATE, GD_TAXRESULT, GD_CONTRACT, GD_DOCREALIZATION, INV_PRICE, INV_PRICELINE, USR$INV_ADDINFO, USR$BN_ACCMAKE, USR$BN_ACCMAKELINE, USR$BN_BILL, USR$BN_BILLLINE, USR$BN_BILLNDS, USR$BN_BILLNDSLINE, USR$BN_BLANKTRIP, USR$BN_BTRIPMONEY, USR$BN_BTRIPMONEYLINE, USR$BN_BUYCURR, USR$BN_CASHBOOK, USR$BN_CASHBOOKLINE, USR$BN_CASHDEPOSIT, USR$BN_CASHDEPOSITLINE, USR$BN_CASHREPORT, USR$BN_CASHREPORTLINE, USR$BN_CHANGECURR, USR$BN_COLLECT, USR$BN_COMMISS, USR$BN_COMMISSLINE, USR$BN_DATABTRIP, USR$BN_DATABTRIPLINE, USR$BN_DEMAND, USR$BN_INCOMECURR, USR$BN_INCOMECURRLINE, USR$BN_LISTDEM, USR$BN_LISTDEMLINE, USR$BN_NOTICE, USR$BN_NOTICELINE, USR$BN_ORDERBTRIP, USR$BN_ORDERBTRIPLINE, USR$BN_PAYMENT, USR$BN_SALECURR, USR$BN_SALECURRLINE, USR$BN_SHARECURR, USR$BN_SHARECURRLINE, USR$BN_TRANSFER, USR$BN_TTN, USR$BN_TTNLINE, USR$BNF_ACTS, USR$BNF_ACTSLINE, USR$BNF_CONTRACT, USR$BNF_CONTRACTLINE, USR$CHECKREGISTER, USR$CHECKREGISTERLINE, USR$CURR_PAYMENT, USR$FA_ACTNEW, USR$FA_ACTNEWLINE, USR$FA_AMORT, USR$FA_AMORTLINE, USR$FA_CHANGEPROP, USR$FA_CHANGEPROPLINE, USR$FA_COMPLECT, USR$FA_COMPLECTLINE, USR$FA_CONSERVATIONLINE, USR$FA_CONSERVATION, USR$FA_MOVEMENT, USR$FA_MOVEMENTLINE, USR$FA_REAPP_B, USR$FA_REAPP_BLINE, USR$FA_REAPP, USR$FA_REAPPLINE, USR$FA_REMONT, USR$FA_REMONTLINE, USR$FA_REPAIR, USR$FA_REPAIRLINE, USR$GS_ACCBALANCE, USR$GS_ACTWORK, USR$GS_ACTWORKLINE, USR$INCOME_ORD, USR$INCOME_ORDLINE, USR$INV_ACTWORK, USR$INV_ACTWORKLINE, USR$INV_ADDWBILL, USR$INV_ADDWBILLLINE, USR$INV_ATTORNEY, USR$INV_ATTORNEYLINE, USR$INV_BILL, USR$INV_BILLLINE, USR$INV_CARDPRMET, USR$INV_CARDPRMETLINE, USR$INV_CERT, USR$INV_CHANGEPERC, USR$INV_CHANGEPERCLINE, USR$INV_COMP, USR$INV_COMPLINE, USR$INV_CONTRACT, USR$INV_CONTRACTLINE, USR$INV_DECOMP, USR$INV_DECOMPLINE, USR$INV_GOODORDER, USR$INV_GOODORDERLINE, USR$INV_INVENT, USR$INV_INVENTLINE, USR$INV_INVMOVE, USR$INV_INVMOVELINE, USR$INV_NEWCOST, USR$INV_NEWCOSTLINE, USR$INV_OUTLAYS, USR$INV_PROCESSING, USR$INV_PROCESSINGLINE, USR$INV_PRODUCT, USR$INV_PRODUCTLINE, USR$INV_PRREC, USR$INV_PRRECLINE, USR$INV_REALCOMMIS, USR$INV_REALCOMMISLINE, USR$INV_RETAIL, USR$INV_RETAILLINE, USR$INV_RETCUST, USR$INV_RETCUSTLINE, USR$INV_RETPROV, USR$INV_RETPROVLINE, USR$INV_SELLBILL, USR$INV_SELLBILLLINE, USR$INV_SPEND, USR$INV_SPENDLINE, USR$INV_SPENDPREC, USR$INV_SPENDPRECLINE, USR$INV_TOTRADE, USR$INV_TOTRADELINE, USR$MOG_DEBTACC, USR$MOG_DEBTCONTR, USR$MOG_INBILLVAT, USR$MOG_INBILLVATLINE, USR$MOG_SERVICE, USR$REN_DIVSTATEMNLINE, USR$SKIDKI, USR$SKIDKILINE, USR$WG_ADDPAYBYPERIODLINE, USR$WG_ADDPAYBYPERIOD, USR$WG_AGREEMENTLONGLINE, USR$WG_AGREEMENT, USR$WG_AGREEMENTLINE, USR$WG_AGREEMENTLONG, USR$WG_ALIMONY, USR$WG_ALIMONYDEBT, USR$WG_ATTEST, USR$WG_AVGADDPAY, USR$WG_AVGADDPAYLINE, USR$WG_BANKCALC, USR$WG_BRIGADEORDERLINE, USR$WG_BRIGADEORDER, USR$WG_CHARGEREG, USR$WG_CHILDAID, USR$WG_CONTRACT, USR$WG_CONTRACTLINE, USR$WG_CORRECT, USR$WG_CORRECTLINE, USR$WG_FAMCHAG, USR$WG_HAZARDS, USR$WG_HAZARDSLINE, USR$WG_HOLIDAYWORK, USR$WG_HOLIDAYWORKLINE, USR$WG_INCTAXDEDUCTION, USR$WG_INCTAXOTHERPAYLINE, USR$WG_INCTAXOTHER, USR$WG_INCTAXOTHERPAY, USR$WG_INCTAXSECURITIES, USR$WG_INITINCOME, USR$WG_INITINCOMELINE, USR$WG_KINDDAY, USR$WG_KINDDAYLINE, USR$WG_LEAVEDOC, USR$WG_LEAVEDOCLINE, USR$WG_LEAVEPASS, USR$WG_LEAVESCHED, USR$WG_LEAVESCHEDLINE, USR$WG_LEAVESTOPDOC, USR$WG_MANUALINPUT, USR$WG_MANUALINPUTLINE, USR$WG_MOVEMENT, USR$WG_MOVEMENTLINE, USR$WG_MULTIORDER, USR$WG_MULTIORDERLINE, USR$WG_PARTDAY, USR$WG_PARTDAYLINE, USR$WG_PENALTYDOC, USR$WG_PERSONALORDERLINE, USR$WG_PERSONALORDER, USR$WG_PIECEWORK, USR$WG_PIECEWORKLINE, USR$WG_PROFDEVELOP, USR$WG_PU6, USR$WG_PU6LINE, USR$WG_RETRAINING, USR$WG_REWARDDOC, USR$WG_SALARYCALC, USR$WG_SALARYCALCLINE, USR$WG_SALARYPAY, USR$WG_SALARYPAYLINE, USR$WG_SCIENCEDEVELOP, USR$WG_SENCALC, USR$WG_SENCALCLINE, USR$WG_SETPAYMENT, USR$WG_SETPAYMENTLINE, USR$WG_SETSENBONUS, USR$WG_SICKLIST, USR$WG_SICKLISTJOURNAL, USR$WG_SICKLISTLINE, USR$WG_SINKDEBT, USR$WG_SINKDEBTLINE, USR$WG_STAFFLIST, USR$WG_STAFFLISTLINE, USR$WG_TAXATION, USR$WG_TBLCAL, USR$WG_TBLCALLINE, USR$WG_TIMEWORK, USR$WG_TIMEWORKLINE, USR$WG_TOTAL, USR$WG_TOTALLINE, USR$WG_VACATION, USR$WG_VACATIONLINE, USR$WG_WRITEOFF, USR$WG_WRITEOFFLINE, USR$WS_DISTANCE_LINE, USR$WS_FUEL_LINE, USR$WS_WAYSHEET

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

Список всех бизнес-классов платформы и пакета типовых настроек Комплексная автоматизация теперь выглядит так.

Путь к элементу дерева

Следующий запрос выводит содержимое справочника товарных групп, причем в колонке path для каждой группы формируется путь к корневому элементу дерева через конкатенцию идентификаторов всех родительских узлов:

WITH RECURSIVE
  group_tree AS (
    SELECT
      CAST('' AS VARCHAR(120)) as path,
      -1 as id,
      -1 as parent,
      CAST('' AS dname) as name
    FROM
      rdb$database

    UNION ALL
    
    SELECT
      IIF(gt.path > '', gt.path || '.', '') || g2.id,
      g2.id,
      g2.parent,
      g2.name
    FROM
      gd_goodgroup g2 JOIN group_tree gt
        ON COALESCE(g2.parent, -1) = gt.id
  )
SELECT
  *
FROM
  group_tree
WHERE
  path > ''

Запрос может быть легко изменен для любого древовидного справочника из базы данных Гедымина.

Если из получаемого пути следует исключить наименование корневой группы, то строку:

  IIF(gt.path > '', gt.path || '.', '') || g2.id,

Следует заменить на:

  IIF(gt.path > '', gt.path || '.', '') || 
    IIF(g2.parent IS NULL, '', g2.id),

Для того, чтобы запрос возвращал только пути элементов для поддерева заданного его корневым узлом, следует заменить первый SELECT в объединении на:

SELECT
  CAST(name AS VARCHAR(120)) as path,
  id,
  parent,
  name
FROM
  gd_goodgroup
WHERE
  id = идентификатор_корня_поддерева

Quick and dirty DB cleanup

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

1. Отключить триггеры (перед этим желательно запомнить какие триггеры были в неактивном состоянии).
   
2. Выполнить delete from ac_entry.
   
3. Выполнить delete from inv_movement.
   
4. Выполнить delete from inv_balance.
   
5. Потом удалять данные из таблиц позиций документов (имена %LINE). Можно написать такой запрос: SELECT 'DELETE FROM ' || a.relationname || ';' FROM at_relations WHERE relationname like 'USR$%LINE', выполнить в IBExpert, скопировать в буфер результат и вставить в окно скрипта.
   
6. Выполнять многократно delete from inv_card c where not exists (select c1.id from inv_card с1 where c1.parent = c.id) пока все не удалится.
   
7. Выполнять многократно delete from gd_document, смотреть какие таблицы ругаются и удалять из них информацию. Повторять, пока не очистится gd_document.
   
8. Подключить отключенные триггеры.
   

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

execute block
as
  declare variable stmt varchar(1024);
  declare variable dt integer;
  declare variable rn varchar(31);
begin
  for
    select t.id, r.relationname from at_relations r
      join gd_documenttype t on t.linerelkey =  r.id
    into :dt, :rn
  do begin
    stmt = 'delete from gd_document doc ' ||
      ' where doc.documenttypekey = ' || :dt ||
      ' and not (doc.parent is null) ' ||
      ' and not (doc.id in (select documentkey ' ||
      '   from ' || :rn || '))';
    execute statement :stmt;
  end
end