Статья продолжает знакомить с функциями для документирования баз данных PostgreSQL. Но на этот раз речь пойдет о специальных функциях, подготавливающих описания диаграмм классов на языке PlantUML.
В качестве основного средства документирования выбрана система управления проектами TRAC с подключенным плагином plantuml.
В первой половине статьи изложены комментарии к реализации функций. Во второй- исходные тексты функций, а также пошаговые инструкции включения PlantUML-скриптов в карточки TRAC, и тестирования этих скриптов на сайте PlantText UML редактора. Тем из читателей, кого интересуют только исходные тексты или пошаговые инструкции, предлагаем сразу перейти к Приложению или скачать все скрипты одним архивом.
Рис. 1. Пример диаграммы классов, созданной на языке PlantUML
Функции, описанные в предыдущих статьях, возвращали сведения об объектах баз данных (отношениях(таблицах), атрибутах таблиц, ограничениях (constraints), индексах и т.д.) в форме списков. Эти списки можно включать в документ, описывающий базу данных проекта, в виде таблиц. Но для документирования связей между таблицами списковая форма не слишком наглядна. Другое дело ER-диаграммы или диаграммы классов UML, такие как показана на Рис. 1. Обычно такие диаграммы создаются специальными графическими средствами, такими, например, как ERwin Data Modeler[*1] , Microsoft Visio[*2] , к недостаткам которых можно отнести отсутствие или сложность связи с существующей базой данных. Альтернативой графическим редакторам стала технология создания диаграмм из их текстовых описаний, которые создаются на на языке PlantUML. Эта технология предполагает создание UML за два шага — сначала создается описание или PlantUML-скрипт, который затем загружается в среду, преобразующую его в диаграмму.
Описываемые в этом документе функции предназначены для создания PlantUML-скриптов из данных системных каталогов PostgreSQL. Основной формат создаваемых PlantUML-скриптов соответствует требованиям плагина plantuml для системы управления проектами TRAC[*3] . Дополнительно описываемые функции могут создавать скрипты классической формы без дополнительной обёртки, необходимой для TRAC.
Общая структура функций
Головной функцией, создающей PlantUML-скрипты является umlfn_Table_fromArray. Она преобразует массив названий таблиц (отношений) в PlantUML-скрипт для последующего отображения в форме диаграммы классов UML.
Рис. 2. Структура вызовов функций в процессе создания PlantUML— скрипта
Дополнительно PlantUML-скрипты могут быть созданы функцией umlfn_Table_AllGroupScript, которая по заданному условию формирует массив таблиц из каталога pg_class и вызывает головную функцию umlfn_Table_fromArray.
На Рис. 2 показана схема взаимодействия функций в процессе создания PlantUML-скрипты. На ней основные функции имеют префикс umlfn_или umltf. Остальные функции — вспомогательные, предназначены для доступа и обработки данных из системных каталогов.
Таблица 1 содержит список и краткое назначение основных функций.
Таблица 1. Назначение основных функций
Таблица 2 содержит список и краткое назначение только тех вспомогательных функций, которые не описаны в следующих статьях:
В основном, эти функции создавались для удобства преобразования данных системных каталогов к формату PlantUML-скриптов.
Таблица 2. Назначение вспомогательных функций
Созданные PlantUML-скрипты могут быть выгружены в текстовый файл.
Рис. 3. Схема взаимодействия основных функций и процедур
Для выгрузки PlantUML-скриптов используются хранимые процедуры, которые вызывают головную или дополнительную функции, а их результат сохраняют в текстовом файле с помощью оператора COPY TO. Схема взаимодействия основных функций и процедур приведена на Рис. 3.
Таблица 3 содержит список и краткое назначение основных процедур.
Таблица 3. Назначение основных процедур
Процедура umlpr_TablesScipt_FromArray
Процедура umlpr_TablesScipt_FromArray выгружает в текстовый файл PlantUML-скрипт, созданный из массива с названиями таблиц функцией umlfn_Table_fromArray.
В качестве основных параметров процедура принимает массив названий таблиц (a_TableNameArray), путь к папке, к которую должен быть выгружен PlantUML-скрипт (a_OutputFolder) и название схемы, в пределах которой они находятся таблицы (a_SchemaName). Дополнительными параметрами являются: «Признак необходимости включения комментария атрибута» (a_NeedAttrDesc) и «Формат возвращаемого PlantUML-скрипта: ‘Trac’ или ‘PlantTest’» (a_Mode).
Название выгружаемого файла имеет один из следующих двух видов:
plantuml_YYYY-MM-DD;
plantuml_YYYY-MM-DD_classic
Файлы с именем второго вида создаются, если параметр a_Mode имеет значение ‘PlantTest’. На месте шаблона ‘YYYY-MM-DD’ в названии указывается дата создания файла, в которой ‘YYYY’ — год, ‘MM’ — порядковый номер месяца в году, ‘DD’ — день в месяце.
Текстовая версия оператора на рисунке
COPY_umlpr_Tables_fA_inside001
1
2
3
COPY(SELECT unnest FROM unnest(ARRAY[umlfn_Table_FromArray(a_SchemaName::VARCHAR,a_TableNameArray,FALSE,a_Mode)]))
CASE a_Mode WHEN 'PlantTest' THEN '_classic' ELSE '' END||'.txt''';
Рисунок, содержащий оператор COPY выгружающий в текстовый файл PlantUML-скрипт, представляет собой принципиальную схему того, как результат выполнения функции umlfn_Table_fromArray. Но из-за особенностей оператора COPY в формат PlantUML-скрипта приходится внести изменения. Дело в том, что umlfn_Table_fromArray возвращает текстовую переменную, состоящую из не заключённых в кавычки подстрок, а для нормального выполнения оператора COPY наоборот каждая подстрока должна быть заключена в кавычки. При этом, после преобразования исходного формата в массив строк, в выходной файл строки выгружаются уже без кавычек.
CASE a_Mode WHEN 'PlantTest' THEN '_classic' ELSE '' END||'.txt''';
В новой версии рисунка учтена необходимость предварительного заключения всех подстрок PlantUML-скрипта в кавычки. Это преобразование выполняется во второй строке.
Рис. 4. Пример папки с выгруженными файлами, содержащими PlantUML-скрипты
Рис. 4 демонстрирует вариант папки с файлами, выгруженными процедурой umlpr_TablesScipt_FromArray. Содержание файлов подробно описано в разделе «Функция umlfn_Table_fromArray…»
Процедура umlpr_TablesScipt_ByWhereOption выгружает в текстовый файл PlantUML-скрипт, созданный из массива с названиями таблиц, найденных по заданному параметром условию. Непосредственно PlantUML-скрипт создаётся функцией umlfn_Table_fromArray.
В качестве основных параметров принимает условие поиска таблиц (a_WHEREoption), которое не содержит ключевое слово WHERE, путь к папке, к которую должен быть выгружен PlantUML-скрипт (a_OutputFolder) и название схемы, в пределах которой они находятся таблицы (a_SchemaName). Дополнительными параметрами являются: «Признак необходимости включения комментария атрибута» (a_NeedAttrDesc) и «Формат возвращаемого PlantUML-скрипта: ‘Trac’ или ‘PlantTest’» (a_Mode).
Выполнение процедуры сводится к получению списка (массива) функций по заданному условию с помощью функции admfn_Table_TableNameArray, с последующим вызовом процедуры umlfn_Table_fromArray.
При описании условия выбора таблиц следует учесть названия псевдонимов (aliases) каталогов, которые используются в функции. Так для каталога pg_class используется псевдоним (alias) «tbl», а для каталога pg_namespace — «nsp».
Функция umlfn_Table_fromArray
Функция umlfn_Table_fromArray возвращает PlantUML-скрипт, созданный из массива с названиями таблиц.
В качестве основных параметров функция принимает массив названий таблиц (a_TableNameArray) и название схемы, в пределах которой они созданы (a_SchemaName). Дополнительными параметрами являются: «Признак необходимости включения комментария атрибута» (a_NeedAttrDesc) и «Формат возвращаемого PlantUML-скрипта: ‘Trac’ или ‘PlantTest’» (a_Mode).
Текстовая версия оператора на рисунке
SELECT_uml_Tables_fA_inside-v2
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT INTO v_TableGroupScript,v_TableGroupLegendScript
Основные данные функция извлекает из записи каталога pg_class, содержащего кроме записей о таблицах еще и, записи о последовательностях, представлениях, материализованных представлениях, составных типах. Поэтому для выбора таблиц используется условие relkind=‘r’.
Дополнительно функция обращается к данным каталогов pg_namespace и pg_description. Первый содержит названия схем базы данных, а второй — комментарии ко всем объектам БД.
Здесь важно обратить внимание на условие objsubid=0. Оно определяет комментарий к таблице, т к. значение поля objoid одинаково как для таблицы, так и для ее атрибутов. Комментарий к атрибуту таблицы содержится в записи, в которой objsubid совпадает с номером этого атрибута.
Кроме того, в условии выбора исключаются таблицы PostgreSQL-расширения (EXTENSION) postgis, а также все элементы исходного массива названий функций переводятся в нижний регистр при помощи вспомогательной функции cnfn_Array_LowerCase.
С помощью оператора SELECT из данных исходных таблиц группируются две части PlantUML-скрипта: v_TableLinksScript— список таблиц с их атрибутами, v_TableGroupLegendScript — легенда предыдущего списка, т.е. названия таблиц с комментариями к ним.
Для описания связей заданных таблиц вызывается функция umlfn_Table_LinksFromArray, которая анализирует внешние ключи заданных таблиц, устанавливает обнаруженные связи, а также обозначает характер этих связей: «*-» — многие к одному, «-*»— один ко многим, «1-1» — один к одному. Таблица 4 содержит описаниие символов связи таблиц из руководства по языку PlantUML.
Таблица 4. Описание символов связи в документации к PlantUML
Последним оператором все части PlantUML-скрипта соединяются в нужной последовательности. При этом, в начало строки добавляется признак, идентифицирующий PlantUML-скрипт, а в конец полученной строки признак PlantUML-скрипта.
Таблица 5 позволяет представить результат выполнения функции umlfn_Table_fromArray, а также состав его частей.
Таблица 5. Результат выполнения функции umlfn_Table_FromArray(‘public’::VARCHAR, ‘{Street, StreetType, StreetType, STREETSYNONYM}’::VARCHAR(256)[])
Замечание: О формате строк в массиве таблиц
Замечание
Таблица 5 демонстрирует PlantUML-скрипт как набор подстрок, расположенных сверху вниз. В тоже время, в теле хранимых функций и процедур эти подстроки образуют значение одной текстовой (TEXT) переменной, в которой они разделены символами конца строки (CHR(13) — «Возврат каретки» и CHR(10) — «перевод строки»). Иногда текст PlantUML-скрипта необходимо преобразовать в массив подстрок. Этим объясняется то, что каждая отдельная подстрока заключена в одиночные кавычки.
Но на выходе из umlfn_Table_fromArray одиночные кавычки не нужны. Поэтому текст временно преобразуется в массив, в каждом элементе которого убираются начальная и замыкающая кавычки. А затем массив снова преобразуется в строку, помещённую в тестовой переменной.
Обычно функция возвращает PlantUML-скрипт в формате плагина plantuml для системы управления проектами TRAC, но если дополнительный параметр a_Mode имеет значение ‘PlantTest’», то обёртка скрипта ограничивается «@startuml…@enduml». Где @startuml — оператор начала скрипта, @enduml—окончания.
Функция umlfn_Table_AllGroupScript возвращает PlantUML-скрипт, созданный таблиц, найденных по заданному параметром условию.
В качестве основных параметров функция принимает условие поиска таблиц (a_WHEREoption), которое не содержит ключевое слово WHERE, и название схемы, в пределах которой будет вестись поиск (a_SchemaName). Дополнительными параметрами являются: «Признак необходимости включения комментария аттрибута» (a_NeedAttrDesc) и «Формат возвращаемого PlantUML-скрипта:‘Trac’ или ‘PlantTest’» (a_Mode).
В теле функции для преобразования условия выборки в массив названий таблиц вызывается функция admfn_Table_TableNameArray и этот массив затем передается в качестве параметра функции umlfn_Table_fromArray, которая уже непосредственно создает PlantUML-скрипт.
При описании условия выбора таблиц следует учесть названия псевдонимов (aliases) каталогов, которые используются в функции. Так для каталога pg_class используется псевдоним (alias) «tbl», а для каталога pg_namespace — «nsp».
Таблица 6 показывает пример результата, возвращаемого функцией при корректном значении параметра a_WHEREoption, в режиме a_Mode=’PlantTest’.
Таблица 6. Результат выполнения функции umlfn_Table_AllGroupScript(‘public’,’tbl.relname ~* E»^Street»’, FALSE, ‘PlantTest‘)
Функция umlfn_Table_fromArray возвращает часть PlantUML-скрипта по названию одной таблицы с её атрибутами.
В качестве основных параметров функция принимает названиие таблицы (a_TableName) и название схемы, в пределах которой они созданы (a_SchemaName). Дополнительными параметрами являются: «Признак необходимости включения комментария аттрибута» (a_NeedAttrDesc) и «Массив таблиц допустимых для поиска внешних ключей таблиц» (a_AllowedTableName).
WHERE attr.attrelid=(a_SchemaName||'.'||a_TableName)::regclass ANDattr.attnum>0
ORDER BY tbl.relname,attr.attnum)oattr;
Основные данные функция извлекает из записи каталога pg_attribute, который содержит записи с данными об атрибутах таблиц, представлений, материализованных представлений, составных типов и даже функций.
Явное обращение к данным каталогов pg_namespace и pg_class отсутствует благодаря использованию псевдонима regclass, преобразующего квалифицированное имя таблицы в её системный ИД (OID).
Здесь важно обратить внимание на условие attnum>0. Оно определяет наличие типа атрибута в каталоге pg_type типов данных.
С помощью оператора SELECT из данных исходных атрибутов группируется часть PlantUML-скрипта: v_TableScript —таблица со списком её атрибутов. Описание каждого атрибута выполняется функцией umlfn_Attribute_String.
Таблица 7 позволяет представить результат выполния функции umlfn_Table_fromArray, а также состав его частей.
Таблица 7. Результат выполнения функции umlfn_Table_Script(‘public’,’Street’,TRUE)
Функция umlfn_Attribute_String возвращает часть PlantUML-скрипта для одного атрибута по его названию.
В качестве основных параметров функция принимает системный идентификатор (OID) таблицы (a_TableOID) и порядковый номер атрибута в этой таблице (a_AttributeNo). Дополнительными параметрами являются: «Признак необходимости включения комментария атрибута» (a_NeedAttrDesc) и «Массив таблиц допустимых для поиска внешних ключей таблиц» (a_AllowedTableName). При этом значение последнего параметра (a_AllowedTableName) не используется. Как говорится, создан в расчёте на будущее усовершенствование функции.
FROM admtf_Attribute_Features(a_TableOID,a_AttributeNo)iaf)af
ORDER BY fkInx)ass
GROUP BY ass AttString;
Некоторая громоздкость приведенного оператора SELECT вызвана тем, что функция admtf_Attribute_Features, как показывает Таблица 8, возвращает характеристики внешних ключей в виде массивов. Отсюда возникла необходимость использования функции GENERATE_SUBSCRIPTS и последующей группировкой данных о внешнем ключе. Хотя, возможно, от такой группировки следует отказаться в будущем.
Таблица 8. Характеристики внешнего ключа, возвращаемые функцией admtf_Attribute_Features
Кроме того, при формировании строки с описанием атрибута учитывается значение параметра a_NeedAttrDesc, который указывает на то, добавлять ли в него комментарий к названию атрибута или нет.Остальные данные атрибута просто соединяются в порядке и формате, которые указаны в требования к языку PlantUML.
Таблица 9 содержит пример выполнения функции umlfn_Attribute_String, особенность которого в том, что параметр a_NeedAttrDesc=‘TRUE’, а параметр a_TableOID задан при помощи псевдонима regclass, что более надёжно, чем использовать явное значение OID таблицы, т.к. последний может быть изменён в процессе эксплуатации базы данных.
Таблица 9. Результат выполнения функции umlfn_Attribute_String(‘public.Street’::REGCLASS, 2::SMALLINT, TRUE)
Функция umlfn_Table_LinksFromArray возвращает часть PlantUML-скрипт со списком связей, созданных из массива с названиями таблиц. В качестве основных параметров функция принимает массив названий таблиц (a_TableNameArray) и название схемы, в пределах которой они созданы (a_SchemaName).
Основные данные функция извлекает из записей каталога pg_constraint. Каталог pg_constraint при условии contype=‘f’ содержит записи с характеристиками внешних ключей таблиц, которые связывают записи из каталога pg_class, принадлежащие к типу relkind=‘r’. Для преобразования названия внешний ключей к характеристикам, соответствующей связи таблиц (‘N:1’,‘1:1’) используется функция admfn_Table_LinkSign.
Дополнительно функция обращается к данным каталога pg_namespace, содержащего названия схем базы данных.
Вспомогательные функции предназначены для доступа и обработки данных из системных каталогов. В основном, эти функции созданы для удобства преобразования данных системных каталогов к формату PlantUML-скриптов.
Функция admfn_Table_TableNameArray
Вспомогательная функция admfn_Table_TableNameArray возвращает массив названий таблиц, полеченный из записей каталога pg_class по условию WHERE, переданному через параметры.
В качестве основных параметров функция принимает условие поиска таблиц (a_WHEREoption), которое не содержит ключевое слово WHERE, и название схемы, в пределах которой будет вестись поиск (a_SchemaName).
Текстовая версия оператора на рисунке
SELECT_adm_Table_TNA_inside
1
2
3
SELECT ARRAY_AGG(tbl.relname)
FROM pg_class tbl INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid
WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName)ANDtbl.relkind=LOWER('r')AND(a_WHEREoption);
При описании условия выбора таблиц следует учесть названия псевдонимов (aliases) каталогов, которые используются в функции. Так для каталога pg_class используется псевдоним (alias) «tbl», а для каталога pg_namespace — «nsp».
Кроме, тего в теле функции предусмотрна проверка коректности оператора SELECT после включения в него значений параметров. Если переданное условие некорректно, вместо массива возвращается NULL и выдается сообщение об ошибке подобное следующему.
Таблица 11 показывает пример результата, возвращаемого функцией при корректном значении параметра a_WHEREoption.
Таблица 11. Результат выполнения функции admfn_Table_TableNameArray(‘public’, ‘tbl.relname ~* E»^Street»’)
Вспомогательная функция admfn_Table_LinkSign возвращает признак связи таблиц (‘N:1’,‘1:N’,‘1:1’), соответствующей ограничению Foreign Key.
Порядок формирования признака зависит от роли (места) исходной таблицы в заданном внешнем ключе, которая определяется функцией admfn_Table_ForeignKeyRole, в зависимости от того ссылается ли внешний ключ на таблицу (‘Referenced’) или является частью таблицы(‘Constrained’).
При этом значение признака связи главным образом зависит от результата выполнения функции admfn_Table_ConstrainedLinkSign, которая возвращает мощность таблицы, признак количества записей, участвующих в связи, образованной ограничением. Особенностью этой функции является то, что она возвращает значения (‘N’, ‘1’) только для таблицы, содержащей внешний ключ, т.к. мощность таблицы на которую ссылается внешний ключ всегда равна ‘1’.
Вспомогательная функция admfn_Table_ForeignKeyRole возвращает признак роли (места) таблицы в ограничении(constraint) внешний ключ (Foreign Key): ‘Referenced’ — таблица, на которую ссылается внешний ключ; ‘Constrained’ — таблица, которая ссылается на другую таблицу через внешний ключ.
В качестве основных параметров функция принимает названиие таблицы (a_TableName), название схемы, в пределах которой они созданы (a_SchemaName), а также название ограничения внешнего ключа (a_FKConstrantName).
Данные функция извлекает из записей каталога pg_constraint. Каталог pg_constraint при условии contype=‘f’ содержит записи с характеристиками внешних ключей таблиц.
Явное обращение к данным каталогов pg_namespace и pg_class отсутствует благодаря использованию псевдонима regclass, преобразующего квалифицированное имя таблицы в её системный ИД (OID).
Текстовая версия оператора на рисунке
SELECT_adm_Table_FKR_inside
1
2
3
4
SELECT CASE(a_SchemaName||'.'||a_TableName)::regclass WHEN con.conrelid THEN'Constrained'
WHEN con.confrelid THEN'Referenced'END
FROM pg_constraint con
WHERE LOWER(con.conname)=LOWER(a_FKConstrantName)ANDcon.contype='f';
Если системный ИД (OID) таблицы (a_TableName) совпадает со значением атрибута conrelid записи об ограничении внешнего ключа (a_FKConstrantName), то ограничение является частью описания таблицы. Если же OID таблицы равен confrelid, то внешний ключ ссылается на эту таблицу.
Таблица 13 показывает примеры вызова функции и результат. Для каждого параметра явно указывается его тип, потому что создано две версии функции admfn_Table_ForeignKeyRole. У одной параметры имеют тип NAME, у другой — VARCHAR.
Таблица 13. Результат выполнения функции SELECT admfn_Table_ForeignKeyRole
Вспомогательная функция admfn_Table_ConstrainedLinkSign возвращает признак количества записей, участвующих в связи, соответствующей ограничению Foreign Key, со стороны таблицы: ‘N’ — много, ‘1’— одна.
В качестве основных параметров функция принимает названиие таблицы (a_TableName), название схемы, в пределах которой они созданы (a_SchemaName), а также название ограничения внешнего ключа (a_FKConstrantName).
В теле функции сравнивается список атрибутов внешнего ключа (a_FKConstrantName) таблицы со следующими списками атрибутов исходной таблицы (a_TableName):
Первичного ключа (PRIMARY KEY);
Уникального индекса (UNIQUE INDEX);
Ограничения уникальности (UNIQUE CONSTRAINT).
Если атрибуты внешного ключа совпадают с одним из вышеперечисленных списков атрибутов, то функция возвращает значение ‘1’, во всех остальных случаях — ‘N’. При этом, есть одно исключение — если описание таблицы не содержит внешний ключ, а является таблицей, на которую этот ключ ссылается, то в этом случае функция возвращает значение ‘1’.
Массивы сравниваемых атрибутов извлекаются из записей каталогов при помощи функций, названия которых содержит Таблица 14.
Таблица 14. функции, вызываемые в теле admfn_Table_ConstrainedLinkSign
Вспомогательная функция admfn_ForeignKey_ConstrainedTableName возвращает название таблицы, в которой объявлен внешний ключ.
В качестве основных параметров функция принимает название схемы (a_SchemaName) и название ограничения внешнего ключа (a_FKConstrantName).
Основные данные функция извлекает из записей каталога pg_constraint. Каталог pg_constraint при условии contype=‘f’, а также из записей каталогов pg_class и pg_namespace.
Текстовая версия оператора на рисунке
SELECT_adm_FK_CTN_inside
1
2
3
4
5
6
SELECT bl.relname
FROM pg_constraint con
INNER JOIN pg_namespace nspc ON con.connamespace=nspc.oid
INNER JOIN pg_class tbl ON con.conrelid=tbl.oid
WHERE nspc.nspname=LOWER(a_SchemaName)ANDcon.contype='f'
ANDcon.conname=LOWER(a_FKConstrantName);
Таблица 16 содержит примеры результатов выполнения функции для двух внешних ключе, наименования которых могут иметь тип NAME или VARCHAR.
Таблица 16. Результат выполнения функции SELECT admfn_ForeignKey_ConstrainedTableName
INNER JOIN pg_attribute att ON att.attrelid=patt.TableOID ANDatt.attnum=patt.PKAttributeID;
Основные данные функция извлекает из записей каталогов pg_constraint, pg_attribute. Каталог pg_constraint при условии contype=’p’ содержит записи с характеристиками первичных ключей таблиц, а pg_attribute содержит записи с данными об атрибутах таблиц, представлений, материализованных представлений, составных типов и даже функций.
Явное обращение к данным каталогов pg_namespace и pg_class отсутствует благодаря использованию псевдонима regclass, преобразующего квалифицированное имя таблицы в её системный ИД (OID).
Массив названий атрибутов создаётся функцией ARRAY_AGG.
Таблица 17 показывает пример вызова функции и результат. Для каждого параметра явно указывается его тип, потому что создано две версии функции admfn_Table_TablePK. У одной параметры имеют тип NAME, у другой — VARCHAR.
Таблица 17. Результат выполнения функции SELECT admfn_Table_TablePK (‘public’::VARCHAR, ‘Street’::VARCHAR);
В качестве основных параметров функция принимает названиие таблицы (a_TableName), название схемы, в пределах которой они созданы (a_SchemaName), а также название ограничения внешнего ключа (a_FKConstrantName).
INNER JOIN pg_attribute att ON att.attrelid=patt.TableOID ANDatt.attnum=patt.PKAttributeID;
Основные данные функция извлекает из записей каталогов pg_constraint, pg_attribute. Каталог pg_constraint при условии contype=‘f’ содержит записи с характеристиками внешних ключей таблиц, а pg_attribute содержит записи с данными об атрибутах таблиц, представлений, материализованных представлений, составных типов и даже функций.
Явное обращение к данным каталогов pg_namespace и pg_class отсутствует благодаря использованию псевдонима regclass, преобразующего квалифицированное имя таблицы в её системный ИД (OID).
Массив названий атрибутов создаётся функцией ARRAY_AGG.
Таблица 18 показывает пример вызова функции и результат. Для каждого параметра явно указывается его тип, потому что создано две версии функции admfn_Table_TableFK. У одной параметры имеют тип NAME, у другой — VARCHAR.
Таблица 18. Результат выполнения функции admfn_Table_TableFK (‘public’::name, ‘StreetSynonym’::name, ‘fk_streetsynonym_street’::name);
Вспомогательная функция admfn_Table_haveUniqueIndexes возвращает значение ИСТИНА (TRUE), если описание таблицы содержит уникальный индекс.
В качестве основных параметров функция принимает названиие таблицы (a_TableName), название схемы, в пределах которой они созданы (a_SchemaName). Дополнительным параметром является признак необходимости учёта уникального индекса, соответствующего первичному ключу PRIMARY KEY (a_isPrimaryNeed).
Текстовая версия оператора на рисунке
SELECT_adm_Table_HUI_inside
1
2
3
4
SELECT inx.indisunique
FROM pg_index inx
WHERE inx.indrelid=(TRIM(LOWER(a_SchemaName))||'.'||TRIM(LOWER(a_TableName)))::regclass ANDinx.indisunique
Основные данные функция извлекает из записей каталогов pg_index, содержащий специальные данные об интексе таблицы, которые отсутствуют в каталоге pg_class. Уникальность возвращаемого индекса гарантируется истинностью значения атрибута indisunique.
Явное обращение к данным каталогов pg_namespace и pg_class отсутствует благодаря использованию псевдонима regclass, преобразующего квалифицированное имя таблицы в её системный ИД (OID).
Таблица 19 показывает пример вызова функции и результат.
Для каждого параметра явно указывается его тип, потому что создано две версии функции admfn_Table_haveUniqueIndexes. У одной параметры имеют тип NAME, у другой — VARCHAR.
Таблица 19. Результат выполнения функции admfn_Table_haveUniqueIndexes
Для оценки приведенных примеров необходимы данные индексах таблицы Street. Как показывает Таблица 20, у этой таблицы только один уникальный индекс, созданный на основе первичного ключа. Поэтому функция в режиме a_isPrimaryNeed = TRUE возвращает истинное значение, а в режиме умолчания — ложное.
Вспомогательная функция admfn_Table_haveUniqueIndexes возвращает список уникальных индексов таблицы, как часть полного списка индексов полученного путём вызова. Условием выбора является истинность атрибута r_isUnique. С полным описанием функции admtf_Table_Indexes можно ознакомиться в статье «Функции для документирования баз данных PostgreSQL».
В качестве основных параметров функция принимает названиие таблицы (a_TableName), название схемы, в пределах которой они созданы (a_SchemaName). Дополнительным параметром является признак необходимости учёта уникального индекса, соответствующего первичному ключу PRIMARY KEY (a_isPrimaryNeed).
Для каждого параметра явно указывается его тип, потому что создано две версии функции admtf_Table_UniqueIndexes. У одной параметры имеют тип NAME, у другой — VARCHAR.
FROM admtf_Table_Indexes(a_SchemaName,a_TableName,a_isPrimaryNeed)ti
WHERE ti.r_isUnique;
Таблица 21 демонстрирует список уникальных индексов для таблицы Street, т.к. у этой таблицы только один уникальный индекс, созданный на основе первичного ключа, . поэтому функция вызвана в режиме a_isPrimaryNeed = TRUE.
Таблица 21. Результат выполнения функции admtf_Table_UniqueIndexes (‘public’::name,’Street’::name,TRUE)
Вспомогательная функция cnfn_Array_QuotesWrapperWithout возвращает массив, из элементов которого удалены начальная и конечная одиночные кавычки, если они присутствуют.
В качестве основных параметров функция принимает массив произвольных строк (a_SourceArray).
В теле функции массив анализируется каждый элемент массива, если в значении найдены окружающие кавычки, то из такого значения функцией SUBSTRING извлекается подстрока.
COMMENT ON FUNCTIONumlpr_TablesScipt_ByWhereOption(a_WHEREoption VARCHAR(1000),a_OutputFolder TEXT,a_Schema VARCHAR(256),a_Mode VARCHAR(20))IS'Создание файла с описанием таблиц по условию выбора таблиц из системного каталога в формате плагина (plantuml) ';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
/*SELECT umlpr_TablesScipt_ByWhereOption('tbl.relname LIKE ''id_districts'' OR tbl.relname LIKE ''id_nps'''::VARCHAR(1000),
COMMENT ON FUNCTIONumlfn_Table_FromArray(a_SchemaName VARCHAR(100),a_TableNameArray VARCHAR(256)[],a_NeedAttrDesc BOOLEAN,a_Mode VARCHAR(20))IS'Возвращает строку с описанием таблиц массива в формате плагина (plantuml) к Trac';
(a_SchemaName VARCHAR(100)default'public',/* название схемы базы данных */
a_WHEREoption VARCHAR(1000)defaultNULL,/* условие запроса на выбор таблиц из системного каталога */
a_NeedAttrDesc BOOLEANdefaultfalse,/* Признак необходимости включения комментария аттрибута */
a_Mode VARCHAR(20)default'Trac'/* Формат возвращаемой строки: 'Trac' -для средства управления проектам; 'PlantTest' - для тестирования на сайте https://www.planttext.com/ */
)
RETURNS TEXT AS
$BODY$
DECLARE
c_TracMode CONSTANT VARCHAR(50):='Trac';/*для средства управления проектам*/
c_PlantTestMode CONSTANT VARCHAR(50):='PlantTest';/*для тестирования на сайте "https://www.planttext.com/ */
v_Mode VARCHAR(20);/* Формат возвращаемой строки: 'Trac' -для средства управления проектам; */
/* 'PlantTest' - для тестирования на сайте https://www.planttext.com/ */
COMMENT ON FUNCTIONumlfn_Table_AllGroupScript(a_SchemaName VARCHAR(100),a_WHEREoption VARCHAR(1000),a_NeedAttrDesc BOOLEAN,a_Mode VARCHAR(20))IS'Возвращает строку строку с описанием группы таблиц в формате плагина (plantuml) к Trac';
COMMENT ON FUNCTIONumlfn_Table_Script(a_SchemaName name,a_TableName name,a_NeedAttrDesc BOOLEAN,a_AllowedTableName VARCHAR(256)[])IS'Возвращает строку с описанием таблицы в формате плагина (plantuml) к Trac';
COMMENT ON FUNCTIONumlfn_Attribute_String(a_TableOID OID,a_AttributeNo SMALLINT,a_NeedAttrDesc BOOLEAN,a_AllowedTableName VARCHAR(256)[])IS'Возвращает строку с описанием атрибута таблицы в формате плагина (plantuml) к Trac';
COMMENT ON FUNCTIONumlfn_Table_LinksFromArray(a_SchemaName VARCHAR(100),a_TableNameArray VARCHAR(256)[])IS'Возвращает строку строку с описанием таблиц массива в формате плагина (plantuml) к Trac';
COMMENT ON FUNCTIONadmfn_Table_TableNameArray(a_SchemaName VARCHAR(256),a_WHEREoption VARCHAR(1000))IS'Возвращает массив названий таблиц, созданный из записей запроса';
SELECT INTO v_ForeignKeyRole CASEWHEN(SELECT tbl.relname FROM pg_class tbl WHERE con.conrelid=tbl.oid)=LOWER(a_TableName)
THENc_ConstrainedRole
WHEN(SELECT reftbl.relname FROM pg_class reftbl WHERE con.confrelid=reftbl.oid)=LOWER(a_TableName)
THENc_ReferencedRole
ELSE
NULL
END
FROM pg_constraint con INNER JOIN pg_namespace nsp ON con.connamespace=nsp.oid
WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName)ANDLOWER(con.conname)=LOWER(a_FKConstrantName)
ANDcon.contype=c_ForeignTypeType;
RETURNv_ForeignKeyRole;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTIONadmfn_Table_ForeignKeyRole(a_SchemaName name,a_TableName name,a_FKConstrantName name)
IS'Возвращает признак роли (места) таблицы в ограничении ForeignKey: Referenced - таблица, на которую ссылается "внешний ключ"; Constrained - таблица, которая ссылается на другаю таблицу через "внешний ключ"';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP FUNCTIONIFEXISTS admfn_Table_ForeignKeyRole(a_SchemaName VARCHAR(256),a_TableName VARCHAR(256),a_FKConstrantName VARCHAR(256));
COMMENT ON FUNCTIONadmfn_Table_ForeignKeyRole(a_SchemaName VARCHAR(256),a_TableName VARCHAR(256),a_FKConstrantName VARCHAR(256))
IS'Возвращает признак роли (места) таблицы в ограничении ForeignKey: Referenced - таблица, на которую ссылается "внешний ключ"; Constrained - таблица, которая ссылается на другаю таблицу через "внешний ключ"';
INNER JOIN pg_namespace nspc ON con.connamespace=nspc.oid
INNER JOIN pg_class tbl ON con.conrelid=tbl.oid
WHERE nspc.nspname=LOWER(a_SchemaName)ANDcon.contype=c_ForeignKeyKind
ANDcon.conname=LOWER(a_FKConstrantName);
RETURNv_ConstrainedTableName;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTIONadmfn_ForeignKey_ConstrainedTableName(a_SchemaName name,a_FKConstrantName name)IS'Возвращает название таблицы, в которой объявлен внешний ключ';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP FUNCTIONIFEXISTS admfn_ForeignKey_ConstrainedTableName(a_SchemaName VARCHAR(256),a_FKConstrantName VARCHAR(256));
COMMENT ON FUNCTIONadmfn_ForeignKey_ConstrainedTableName(a_SchemaName VARCHAR(256),a_FKConstrantName VARCHAR(256))IS'Возвращает название таблицы, в которой объявлен внешний ключ';
