pgm - это shell-скрипт для создания, обновления и удаления объектов БД. В текущей версии скрипт поддерживает только СУБД Postgresql.
Алгоритм работы заключается в препроцессинге .sql файлов из заданных каталогов (пакетов) и генерации скрипта для psql, запускающего в заданной БД подготовленные файлы.
Выполняется после установки pg-skel.
Текущий каталог - место для нового sql-проекта.
Создаем в нем подкаталог (или подмодуль или симлинк) pgm:
git clone https://github.com/TenderPro/pgm.git
Проверяем наличие используемых программ
bash pgm/pgm.sh check
Если не все Ок - надо установить недостающее штатными средствами ОС.
Создаем файл настроек .config
bash pgm/pgm.sh init
Редактируем .config. Надо прописать пользователя с правами создания БД. Его можно создать, используя Доступ к БД под суперпользователем.
В параметре DB_TEMPLATE надо указать имя шаблона БД, созданного с помощью pg-skel.
Создание БД
bash pgm/pgm.sh createdb
Создание объектов pgm
SQLROOT=pgm/sql bash pgm/pgm.sh creatif ws utils
Создание файлов пакета demo
bash pgm/pgm.sh init demo
После этой операции будут созданы первичные файлы в каталоге sql/
. (См ниже Размещение SQL-кода).
Загрузка пакета demo в БД
bash pgm/pgm.sh create demo
Повторная загрузка пакета demo с предварительным удалением
bash pgm/pgm.sh recreate demo
Компиляция хранимого кода пакета demo
bash pgm/pgm.sh make demo
Работа с БД является развитием идеи разделения БД на три составляющих:
- Оперативные данные (ОД) - таблицы, которые изменяются в процессе эксплуатации
- Внешние связи этих таблиц (FOREIGN KEY, DEFAULT)
- Все остальные объекты (изменяются только в процессе разработки)
Разделение оперативных (вводимых в процессе эксплуатации) и справочных (вводимых в процессе разработки системы) данных реализовано следующим образом:
- Все таблицы оперативных данных создаются в схеме
wsd
- Справочные данные создаются в индивидуальных схемах
- Весь код поддержки изменения данных (и их чтения) создается в индивидуальных схемах
Кроме этого, код и данные поддержки pgm размещаются в схеме ws
.
Такая реализация позволяет полностью удалить весь код пакета (методы, триггеры, справочные данные), сохранив оперативные данные (команда drop
) или удалив и их (команда erase
), т.е. не нужно писать скрипт обновления версии А до версии В, достаточно удалить пакет (или все), обновить ПО (git pull
) и создать пакет(ы) заново (create
).
Под схемой понимается схема БД (создаваемая командой CREATE SCHEMA
).
Весь код создания объектов схемы размещается в одноименном схеме каталоге.
Пакет - логическое объединение нескольких схем. Может состоять и из одной схемы.
Скрипт pgm реализует выполнение в БД операций:
- init - создать .config по шаблону
- init PKG - создать каталог пакета и шаблоны файлов
- create PKGS - создать объекты БД
- creatif PKGS - создать объекты БД, если их нет
- recreate PKGS - создать объекты БД, предварительно удалив
- make PKGS - выполнить компилируемый код (CREATE OR REPLACE) после сделанного ранее create
- drop PKGS - удалить объектв БД (кроме wsd)
- erase PKGS - очистить бд (включая удаление wsd)
- createdb - создать БД
- dump SCHEMA - дамп заданной схемы
- restore SCHEMA - восстановление дампа
где
- PKGS - список имен пакетов в порядке создания
- SCHEMA - имя схемы БД
Работа скрипта заключается в формировании соответствующего файла var/build/build.sql и выполнении его в psql
Размещение SQL-кода
SQL-код размещается в каталогах схемы
- sql/PKG/NN_SCHEMA/ (NN - с порядковый номер обработки схемы при обработке пакета)
- sql/PKG/ (если SCHEMA=PKG)
Каталог схемы содержит .sql файлы
Формат имени .sql файла - MM_descr.sql
- файл с типом MM и описанием descr
тип MM имеет значения:
- 00 - drop/erase: удаление связей текущей схемы с другими схемами
- 01 - erase: удаление защищенных объектов из других схем (wsd)
- 02 - drop/erase: удаление текущей схемы (02_drop)
- 10 - init: инициализация до создания схемы
- 11 - init: создание схемы, после выполнения 11* имя схемы из имени каталога добавится в путь поиска
- 12 - init: зависимости от других пакетов, создание доменов и типов
- 1[4-9] - общие файлы для init и make, код, не имеющий зависимостей от объектов, может использоваться при создании таблиц
- 2x - создание таблиц
- 3x - ф-и для представлений
- 4x - представления
- 5x - основной код функций
- 6x - код триггеров
- 7x - создание триггеров
- 8x - наполнение таблиц
- 9x - тесты
Файлы выполняются в порядке сортировки имен.
Для каждой из операций выбираются файлы по соответствующей маске:
- init: [1-9]?_*.sql
- make: 1[4-9]*.sql, [3-6]?.sql, 9?_.sql
- drop: 00_.sql, 02_.sql
- erase: 0?_*.sql
В каждом пакете код, который производит изменения в схеме оперативных данных (wsd), решает одну из следующих задач:
- инициализация, создание таблиц в схеме wsd (20_wsd_000.sql)
- привязка объектов пакета в схеме wsd (создание внешних ключей и триггеров - 8?_*_wsd_000.sql)
- очистка схемы wsd от объектов пакета (01_drop_wsd.sql)
- удаление связей объектов схемы wsd с объектами пакета (00_cleanup.sql)
Задачи привязка и удаление выполняются при стандартном обновлении пакета (create
и drop
соответственно), очистка выполняется при полном удалении пакета (erase
), а инициализация должна выполняться только перед привязкой, которая производится впервые или после очистки.
Особенности инициализации реализованы следующим образом:
- Файл, содержащий команды инициализации, имеет в имени суффикс
_wsd_NNN.sql
- При первом выполнении файла с таким суффиксом (по команде
create
), его атрибуты (включая контрольную сумму) сохраняются в таблицеwsd.pkg_script_protected
- При наличии файла в этой таблице, при выполнении
create
(послеdrop
) его повторный запуск не производится. При изменении контрольной суммы выводится уведомление об этом. - Удаление строки из
wsd.pkg_script_protected
производится при выполненииerase pkg
автоматически.
Используемая техника создания объектов БД позволяет обновлять все схемы БД посредством цепочки drop
, git update
, create
.
Задача обновления схемы wsd решается следующим образом:
- После установки релиза прекращается изменение существующих файлов
*_wsd_000.sql
- Для изменений схемы wsd создаются новые файлы, (
*_wsd_001.sql
итд) - При обновлении системы каждый такой файл отработает на БД однократно
Согласно принятой архитектуре, любой пакет ничего не знает о пакетах, которые будут добавлены в БД после него. Т.е., если есть pkg_B, использующий данные (или код) из pkg_A, то pkg_A об этом ничего не знает. Это порождает необходимость существования механизма, который бы
- Запретил установку pkg_B при отсутствии установленного pkg_A
- Запретил удаление pkg_A при наличии установленного pkg_B
Этот механизм реализовывается добавлением в sql-каталог Пакета_В файла 12_deps.sql
, содержащего инструкцию вида
INSERT INTO ws.pkg_required_by(code) VALUES ('Пакет_А');
В некоторых случаях пакетам необходимо менять внутренние данные в схемах других пакетов (например, справочники файл-сервера). Т.е. возникает ситуация, когда
-
- pkg_A создает таблицу
wsd.T1
, которая ссылается на таблицу пакетаpkg_A.T2
внешним ключомFK1
- pkg_A создает таблицу
Пример:
Пакет FS создает wsd.file_link, поля которой (class_id, folder_code) REFERENCES fs.folder(class_id, code)
-
- pkg_B для работы с
wsd.T1
добавляет строки вpkg_A.T2
- pkg_B для работы с
Пример:
Пакет wiki добавляет в fs.folder(class_id, code) VALUES (12, 'files') - папку для файлов wiki
-
- В процессе эксплуатации происходит наполнение данными таблицы
wsd.Т1
- В процессе эксплуатации происходит наполнение данными таблицы
В результате возникают вопросы
- удалять ли внешний ключ
FK1
, если удаляются пакеты pkg_A или pkg_B, но остаются данные вwsd.T1
? - создавать ли внешний ключ
FK1
при повторном создании pkg_A или pkg_B?
Эти вопросы решаются следующим образом:
- регистрируется зависимость pkg_B от pkg_A
INSERT INTO ws.pkg_required_by(code) VALUES ('fs');
, чем запрещается
-
создание pkg_B при отсутствии pkg_A
-
удаление pkg_A при наличии pkg_B
-
pkg_A не создает внешний ключ FK1, а регистрирует его в таблице wsd.pkg_fkey_protected
INSERT INTO wsd.pkg_fkey_protected (rel, wsd_rel, wsd_col) VALUES
('fs.folder', 'file_link', 'class_id, folder_code')
;
- регистрируется зависимость данных pkg_B от внешнего ключа pkg_A
INSERT INTO wsd.pkg_fkey_required_by (pkg, rel) VALUES ('fs','fs.folder');
В результате внешний ключ FK1
- удаляется перед удалением первого же зависящего от него пакета
- при наличии данных в wsd, создается после создания всех зависящих от него пакетов
Т.е. при удалении pkg_B можно удалять строки из pkg_A.T2
, а при создании - добавлять:
- После создания пакета - создаются все еще несуществующие зарегистрированные FK присоединенных пакетом таблиц
- Перед удалением пакета - удаляются все зарегистрированные пакетом зависимости FK
Со значениями по умолчанию, если они заданы функцией, имеет место картина, аналогичная внешним ключам:
-
- pkg_A создает таблицу wsd.T1, у которой поле F1 имеет DEFAULT - результат вызова функции из некоторого пакета pkg_C
Пример:
Таблица acc.permission имеет поле pkg DEFAULT ws.pg_pkg()
В результате возникают вопросы
- Как избежать удаления поля F1 при удалении схемы pkg_C?
- Как восстановить DEFAULT при повторном создании пакета pkg_C?
Эти вопросы решаются следующим образом:
- pkg_A не задает DEFAULT, а регистрирует его в таблице wsd.pkg_default_protected
INSERT INTO wsd.pkg_default_protected (pkg, schema, wsd_rel, wsd_col, func) VALUES ('acc', 'acc', 'permission', 'pkg', 'ws.pg_pkg()');
, в результате этого
- После создания пакета, этот DEFAULT создается автоматически
- Перед удалением пакета, DEFAULT автоматически удаляется.
Тесты размещаются в файлах 9?_*.sql и выполняются внутри транзакций init
и make
. Вывод теста сравнивается с содержимым файла 9?_*.md
и при несовпадении возникает ошибка.
Наличие ошибок тестов отменяет выполнение основной команды
Номер нужен только для того, чтобы гарантировать порядок выполнения. В тестах он вообще не важен, поэтому все они могут иметь префикс "90_". 91 или 92 - это ни о чем не говорит. В файле должен быть комментарий о том, что тестируется. Также надо смысл теста оформить краткой фразой в латиннице (DESCRIPTION) в соответствии с типом ws.d_code и применить ее дважды:
- в имени файла -
90_DESCRIPTION.sql
- внутри файла, написав
SELECT ws.test('DESCRIPTION');
Чтобы наделить 9Х хоть каким-то смыслом, есть рекомендация: Номер 90 использовать для тестов, не связанных с проверкой наличия в БД данных (когда объекты создаются и тут же удаляются), 91 - для проверки корректности данных в БД.
Если один и тот же блок кода надо выполнить в тесте несколько раз с разными параметрами, этот код помещается в 9X_name.macro.sql и используется в тесте:
\set FILE :TEST .macro.sql
\set VAR 1
\i :FILE
\set VAR 2
\i :FILE
Как можно увидеть из расширения, файлы 9?_*.md
имеют синтаксис markdown. В этот файл помещаются
- названия тестов
- тексты SQL-запросов
- результаты SQL-запросов
Эти файлы не пишутся руками, при первом запуске pgm формирует такой файл в var/build/PKG/
и, если результат теста приемлем, достаточно скопировать
его в sql/PKG/
.
Для того, чтобы 9?_*.md
был сгенерирован корректно, при создании теста 9?_*.sql
используются следующие правила:
- В заголовок попадает результат запроса, который завершается
; -- BOT
(Begin Of Test)
Это может быть любой запрос, но рекомендуется использовать вызов вида SELECT ws.test('TITLE'); -- BOT
, эта функция не только
возвращает TITLE
, но и делает RAISE WARNING
в специальном формате, который pgm использует для вывода в консоли имени выполняемого теста.
- Документируется только запрос, который завершается
; -- EOT
(End Of Test)
Текст этого запроса форматируется как код SQL, результат - как таблица в markdown.
Т.к. текст запроса сохраняется как "текущий буфер запроса psql", в него попадают также и предшествующие многострочные SQL-комментарии (вида /* .. */
).
Содержимое этого буфера psql сбрасывает после каждого запроса, но если после предыдущего запроса есть многострочный комментарий, который не надо
переносить в .md, достаточно после него очистить буфер запроса (инструкцией \r
).
pgm при запуске подключает файл `.config' и использует из него следующие переменные:
DB_NAME=iac1
PG_HOST=127.0.0.1
PG_PORT=5432
Пример этого файла с полным списком переменных можно сгеренить командой bash pgm.sh init
-
удалять из pkg_script_protected по имени пакета
-
вылетать по ошибке если в пакете при выполнении drop/create не найдено файлов
-
добавить команду test
-
добавить команду bench
-
перенести код в из ws в pgm
-
sql/upd - код обновления версий:
Содержит подкаталоги с именами, соответствующими номеру обновления(версии)
NNN/ - каталог обновления MM-* - файл с обновлением Файлы выполняются в порядке сортировки имен, в рамках одной транзакции, однократно.
This project is under the MIT License. See the LICENSE file for the full license text.
Copyright (c) 2010 - 2016 Tender.Pro