SQL для генерации описания структуры таблиц Firebird 2.x

Обычно процесс разработки базы данных начинается с создания документации, в которой описывается структура таблиц и других элементов будущей базы. Но иногда приходится решать противоположную задачу: на руках у разработчика есть база данных, к которой документация отсутствует (например, доставшаяся «по наследству» от менее организованных предшественников), или к ней по каким-то причинам нет доступа. В таком случае хотелось бы иметь возможность получить описание структуры таблиц в более или менее автоматическом режиме.

Разумеется, если вы ежедневно пользуетесь программами типа DBSchema или им подобными (или хотя бы встроенной подпрограммой генерации документации из IBExpert), то ничего изобретать не нужно: они уже умеют это делать. Если же нужно просто быстро получить в читаемом виде описание структуры десятка таблиц базы и нет под рукой никаких нужных программ (или результаты их работы не устраивают, или — почему бы и нет? — вы самостоятельно пишете такую систему), то можно воспользоваться SQL-запросом, который извлекает всю нужную информацию прямо из метаданных базы.

Постановка задачи

Мы хотим написать SQL-запрос, получающий на вход список таблиц базы данных, разделённых запятыми (или как-то ещё), а на выходе предоставляющий нам отформатированный определённым образом текст, который включает: названия и описания таблиц, а также список полей с указанием для каждого из них:

• типа,
• особенностей (первичный ключ, уникальное, ссылочный ключ),
• имени таблицы и поля в ней, на которые данное поле ссылается,
• описания.

Выходной текст потом можно без существенных правок загрузить в какую-нибудь программу (я использую форматы CSV для Excel и HTML, но ниже рассказывается и о других применениях).

SQL

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

Требования

Запрос тестировался под Firebird 2.5. Скорее всего, он также будет работать и под управлением Firebird 2.1. Наверняка не будет под Firebird 1.x. По поводу СУБД версии 3.x ничего пока сказать не могу.

Параметры

Запрос принимает на вход:

• список таблиц,
• разделитель к этому списку (например, это может быть запятая или перевод строки),
• подстановку для замены перевода строки (например, в HTML вместо перевода строки нужно писать <br/>, а в Excel — символ «line feed» с кодом 10),
• две строки формата: для таблицы и для поля,
• необязательную строку для «подножия» таблицы (в HTML это </table>, а в CSV не нужно ничего, хотя ради удобства можно вставить пустую строку между таблицами).

На выходе — строка varchar(10000) вместо blob, чтобы не нужно было производить никакие специальные действия при чтении строки сторонними программами (например, если вставить вызов этого запроса в программу на PHP для получения информации о базе через web, то для чтения blob-полей придётся писать специальный код, а varchar читается «как есть»). Число 10000 взято наобум, если этого будет мало, то можно добавить.

Скачать SQL (Google Drive)  

---

/* Создание текстового блока со структурой таблиц
 
 Входные параметры:
 TABLE_LIST - список таблиц через разделитель
 LIST_DELIMITER - разделитель для списка таблиц, например ',' или перевод строки (для того и varchar(2)). Если null, то запятая
 TABLE_FORMAT - строка формата с названием и описанием таблицы. Используются подстановки:
   %NAME% - имя таблицы
   %DESCRIPTION% - описание таблицы
 FIELD_FORMAT - строка формата со структурой полей. Используются подстановки:
   %FIELDNO% - № п/п
   %NAME% - имя поля
   %TYPE% - тип поля
   %KEY% - ключи (P - первичный, U - уникальный, F - ссылка)
   %FK_TABLE_NAME% - имя таблицы, на которую ссылается (для полей-ссылок с ключом F)
   %FK_FIELD% - имя поля, на которое ссылается (для полей-ссылок с ключом F)
   %DESCRIPTION% - описание поля
 CRLF_REPLACEMENT - подстрока для замены символов перевода строки в описании таблицы или поля. По умолчанию символ с кодом 10 (ascii_char(10)), это подходит для Excel
 TABLE_FOOTER - строка, которая вставляется после вывода последнего поля в каждой таблице (если null, то не выводится ничего)
 
 Выходной параметр:
 OUT_LINE - отформатированная строка
 
 Примеры форматов:
 1. Excel CSV
   TABLE_LIST = TABLE1,TABLE2,TABLE3
   LIST_DELIMITER = null
   TABLE_FORMAT = ';"%NAME%";"%DESCRIPTION%"'
   FIELD_FORMAT = '%FIELDNO%;"%NAME%";"%TYPE%;"%KEY%";"%FK_TABLE_NAME%";"%FK_FIELD%";"%DESCRIPTION%"'
   CRLF_REPLACEMENT = null
   TABLE_FOOTER = null
 
 2. HTML
   TABLE_LIST = TABLE1,TABLE2,TABLE3
   LIST_DELIMITER = null
   TABLE_FORMAT = '<h2>%NAME% &mdash; %DESCRIPTION%</h2>
<table><tr><th>#</th><th>Поле</th><th>Тип</th><th>Ключи</th><th>FK - Таблица</th><th>FK - Поле</th><th>Описание</th></tr>'
   FIELD_FORMAT = '<tr><td>%FIELDNO%</td><td>%NAME%</td><td>%TYPE%</td><td>%KEY%</td><td>%FK_TABLE_NAME%</td><td>%FK_FIELD%</td><td>%DESCRIPTION%</td></tr>'
   CRLF_REPLACEMENT = null
   TABLE_FOOTER = '</table>'
 
*/
execute block (
 TABLE_LIST varchar(1000) = :TABLE_LIST,
 LIST_DELIMITER varchar(2) = :LIST_DELIMITER,
 TABLE_FORMAT varchar(1000) = :TABLE_FORMAT,
 FIELD_FORMAT varchar(1000) = :FIELD_FORMAT, /* строка формата со структурой полей */
 CRLF_REPLACEMENTvarchar(10) = :CRLF_REPLACEMENT,
 TABLE_FOOTER varchar(100) = :TABLE_FOOTER
 )
returns (
 OUT_LINE varchar(10000))
as
declare variable FIELD_KEY varchar(13);
declare variable FIELD_NAME varchar(31);
declare variable FIELD_TYPE varchar(49);
declare variable TABLE_NAME varchar(31);
declare variable OLD_TABLE_NAME varchar(31);
declare variable FK_TABLE_NAME varchar(33);
declare variable FK_FIELD varchar(33);
declare variable I smallint;
declare variable DESCRIPTION BLOB SUB_TYPE 1 SEGMENT SIZE 100;
begin
 OLD_TABLE_NAME = '';
 /* по умолчанию перевод строки заменяется на символ с кодом 10, это годится для Excel */
 if (CRLF_REPLACEMENT is null) then
   CRLF_REPLACEMENT = ascii_char(10);
 
 /* разделитель по умолчанию - запятая */
 LIST_DELIMITER = coalesce(LIST_DELIMITER, ',');
 
 while (TABLE_LIST <> '') do
 begin
   /* разбиение списка таблиц по резделителям */
   I = position(LIST_DELIMITER in TABLE_LIST);
   if (I = 0) then
   begin
     TABLE_NAME = TABLE_LIST;
     TABLE_LIST = '';
   end
   else
   begin
     TABLE_NAME = substring(TABLE_LIST from 1 for I - 1);
     TABLE_LIST = substring(TABLE_LIST from I + char_length(LIST_DELIMITER));
   end
 
   I = 1;
 
   /* список полей */
   for select distinct
         coalesce(coalesce(substring(IDX.FIELD_KEY from 1 for 1), '') || iif(R.RDB$NULL_FLAG is null, '', iif(IDX.FIELD_KEY is null, cast('N' as varchar(2)), ',N')), '') as FIELD_KEY,
         coalesce(iif(trim(IDX.FIELD_KEY) = 'FOREIGN KEY', IDX.FK_TABLE_NAME, null), '') as FK_TABLE_NAME,
         coalesce(iif(trim(IDX.FIELD_KEY) = 'FOREIGN KEY', IDX.FK_FIELD, null), '') as FK_FIELD,
         trim(cast(R.RDB$FIELD_NAME as varchar(31))) as FIELD_NAME,
         /* мы пользуемся тем, что автоматически сгенерированные домены
            начинаются на RDB$, а сделанные "вручную" - нет */
         iif(F.RDB$SYSTEM_FLAG = 0 and F.RDB$FIELD_NAME not starting with 'RDB$', trim(F.RDB$FIELD_NAME),
         /* преобразуем системное имя в привычное */
         decode(T.RDB$TYPE_NAME,
           'LONG', 'integer',
           'SHORT', 'smallint',
           'VARYING', 'varchar',
           lower(trim(cast(T.RDB$TYPE_NAME as varchar(31)))))
         ) || coalesce('(' || nullif(F.RDB$CHARACTER_LENGTH, 0) || ')', '') as FIELD_TYPE,
         /* заменяем перевод строки в описании */
         coalesce(replace(cast(R.RDB$DESCRIPTION as varchar(10000)), '
', :CRLF_REPLACEMENT), '') as DESCRIPTION
       from
         RDB$RELATION_FIELDS R
       inner join
         RDB$FIELDS F on F.RDB$FIELD_NAME = R.RDB$FIELD_SOURCE
       inner join
         RDB$TYPES T on T.RDB$TYPE = F.RDB$FIELD_TYPE and T.RDB$FIELD_NAME = 'RDB$FIELD_TYPE'
       left join
         (select distinct
            coalesce(I.RDB$UNIQUE_FLAG, 0) as UNIQUE_FLAG,
            I.RDB$INDEX_NAME,
            trim(cast(I.RDB$RELATION_NAME as varchar(31))) as TABLE_NAME,
            trim(cast(S.RDB$FIELD_NAME as varchar(31))) as FIELD_NAME,
            trim(cast(C.RDB$CONSTRAINT_TYPE as varchar(31))) as FIELD_KEY,
            (select trim(RDB$FIELD_NAME) from RDB$INDEX_SEGMENTS where RDB$INDEX_NAME = I.RDB$FOREIGN_KEY) as FK_FIELD,
            (select trim(RDB$RELATION_NAME) from RDB$INDICES where RDB$INDEX_NAME = I.RDB$FOREIGN_KEY) as FK_TABLE_NAME
          from
            RDB$INDICES I
          inner join
            RDB$INDEX_SEGMENTS S on S.RDB$INDEX_NAME = I.RDB$INDEX_NAME
          inner join
            RDB$RELATION_CONSTRAINTS C on C.RDB$INDEX_NAME = I.RDB$INDEX_NAME and C.RDB$CONSTRAINT_TYPE is not null) as IDX on IDX.TABLE_NAME = R.RDB$RELATION_NAME and IDX.FIELD_NAME = R.RDB$FIELD_NAME
       where
         R.RDB$RELATION_NAME = upper(:TABLE_NAME)
       order by
         R.RDB$FIELD_POSITION
       into
         :FIELD_KEY,
         :FK_TABLE_NAME,
         :FK_FIELD,
         :FIELD_NAME,
         :FIELD_TYPE,
         :DESCRIPTION
   do
   begin
     if (OLD_TABLE_NAME <> TABLE_NAME) then
     begin
       /* подножие таблицы */
       if (TABLE_FOOTER is not null and OLD_TABLE_NAME <> '') then
       begin
         OUT_LINE = TABLE_FOOTER;
         suspend;
       end
       /* заголовок таблицы */
       select replace(replace(:TABLE_FORMAT, '%NAME%', :TABLE_NAME), '%DESCRIPTION%', replace(cast(RDB$DESCRIPTION as varchar(10000)), '
', :CRLF_REPLACEMENT))
       from
         RDB$RELATIONS
       where
         RDB$RELATION_NAME = :TABLE_NAME
       into
         :OUT_LINE;
       OLD_TABLE_NAME = TABLE_NAME;
       suspend;
     end
     /* тело таблицы: заменяем все подстановки; увы, FB 2.5 не поддерживает регулярные выражения в заменах */
     OUT_LINE = replace(replace(replace(replace(replace(replace(replace(FIELD_FORMAT, '%FIELDNO%', I), '%NAME%', FIELD_NAME), '%TYPE%', FIELD_TYPE), '%KEY%', FIELD_KEY), '%FK_TABLE_NAME%', FK_TABLE_NAME), '%FK_FIELD%', FK_FIELD), '%DESCRIPTION%', DESCRIPTION);
     I = I + 1;
     suspend;
   end
 end
 /* подножие последней таблицы: если список таблиц пуст, то это будет единственная строка, поэтому мы считаем, что он не пуст */
 if (TABLE_FOOTERis not null) then
 begin
   OUT_LINE = TABLE_FOOTER;
   suspend;
 end
end

---

Необычные применения

Одно время мы пытались строить ER-диаграммы для наших баз данных, используя web-сервисы для работы с векторной бизнес-графикой: бесплатный draw.io и коммерческий Lucidchart (они удобны тем, что оба интегрируются в состав приложений Google Drive). Тогда я написал пару вариантов этого запроса для частичной автоматизации построения диаграмм.

Так, draw.io позволяет импортировать файлы в формате XML. Изучив этот формат, я написал запрос, который генерирует совместимый с этой программой текст в формате XML. После импорта полученного файла на экране оказывается набор прямоугольников, соответствующих таблицам, внутри у них список полей с типами и ключами (первичный, уникальный и т. д.). К сожалению, автоматизировать построение связей между таблицами не получилось, главным образом из-за того, что часто тип отношений («один-к-одному», «один-ко-многим») не закреплён в структуре таблицы, а регулируется какими-то внешними по отношению к базе данных соглашениями. К тому же, этот SQL «не умеет» красиво расставить прямоугольники на экране, чтобы было удобно потом рисовать связи… хотя уже сама постановка задачи: аккуратно расставить прямоугольники при помощи SQL — звучит абсурдно! Достаточно и того, что прямоугольники выстраиваются ровными рядами слева направо и сверху вниз.

Потом мы перешли на более продвинутый web-сервис Lucidchart. Этот сервис умеет автоматически строить ER-диаграммы, если загрузить в него список таблиц и связей в формате CSV заранее определённой структуры. Эту задачу я тогда тоже решил. Но потом мы от использования этого сервиса также отказались, перейдя на DBSchema.