Хочу поделиться инструментом, который родился при разработке одного веб-проекта и очень помогает мне не потеряться в море таблиц, хранимых процедур, индексов и прочих обитателей базы данных.
Сам проект написан на Django, в качестве бекенда — PostgreSQL. В самом начале работы было решено, по крайней мере, частично отказаться от использования Django ORM в пользу «сырого» SQL и хранимых процедур. Другими словами, почти вся бизнес-логика вынесена на уровень базы данных. Сразу скажу, что готовить ORM я умею, но в данном случае требовалось производить многоступенчатые вычисления, связанные с множеством выборок, а это лучше делать на сервере БД и не таскать промежуточные данные в приложение.
Столкнувшись с необходимостью поддержания структуры базы данных вручную, без приятностей Django Migrations, я выяснил, что вручную писать инкрементальные SQL патчи возможно, но трудно уследить за зависимостями объектов БД. К примеру, когда функции, которая используется где-то еще, добавляешь еще один аргумент, простого CREATE OR REPLACE недостаточно — ее нужно сначала DROP, а потом CREATE. При этом нужно предварительно удалить зависимые от нее функции, а потом создать заново (а если от этих функций еще кто-то зависит, тогда надо и их пересоздать).
Под катом краткое описание возможностей в виде туториала. Встречайте — Sqlibrist.
Надо сказать, что мою проблему уже научились решать. Например, сравнительно давно существует Sqitch. Он позволяет описывать структуру базы данных в декларативном виде на SQL. Каждая таблица, вид или функция хранится в отдельном файле, а простой DSL описывает зависимости. Утилита написана на Perl, и мне, не знакомому с разработкой на Perl и экосистемой его пакетов, пришлось очень постараться, чтобы эту утилиту скомпилировать. Возможно, в силу долгой истории разработки, Sqitch имеет многовато зависимостей, как для такой простой программы. Еще мне не понравилось запутанное описание зависимостей и работа с версиями структуры. Признаю, что я просто не захотел подстраиваться и разбираться с инструментом, который мне показался неудобным.
Создавая Sqlibrist, я вдохновлялся и Sqitch, и Django Migrations, и немного VCS. А еще хотел, чтобы он был простым и понятным в использовании. Объекты структуры БД хранятся в отдельных файлах. Каждый содержит SQL-инструкцию для создания и (не обязательно) удаления этого объекта. Зависимости между объектами описываются явно в виде директив на встроенном DSL (в нем, кстати, только три ключевых слова: REQ, UP, DOWN). Подобно системе управления версиями, Sqlibrist хранит снимки структуры БД и SQL-патч для обновления до него с предыдущего снимка.
Интеллект Sqlibrist ограничен, он не парсит SQL и не генерирует ALTER TABLE — это ваша работа. Он только отслеживает изменения в файлах и создает патчи с вашими инструкциями, а также ведет учет примененных миграций.
Все это звучит как-то абстрактно, давайте перейдем к практике.
Установка
Моя основная ОС — Linux и на сервере и на десктопе, поэтому инструкции по установке только для нее. Возможно, кто-нибудь поможет мне с Windows и Mac.
Сначала заголовочные файлы:
Ubuntu
$ sudo apt-get install python-pip python-dev libyaml-dev $ sudo apt-get install libmysqlclient-dev # for MySQL $ sudo apt-get install libpq-dev # PostgreSQL
Fedora/CentOS
$ sudo dnf install python-devel python-pip libyaml-devel $ sudo dnf install postgresql-devel # PostgreSQL
$ sudo dnf install mariadb-devel # for MariaDB или
$ sudo dnf install mysql++-devel # for MySQL Sqlibrist написан на Python и имеет две зависимости: PyYAML и что-то одно из psycopg2 и mysql-python.
Устанавливается с помощью pip либо в virtualenv, либо в системные библиотеки:
$ pip install sqlibrist или
$ sudo pip install sqlibrist После установки становится доступной команда sqlibrist.
База данных интернет-магазина
Давайте поиграемся с Sqlibrist на примере примитивного интернет-магазина.
$ mkdir shop_schema $ cd shop_schema $ sqlibrist init Creating directories... Done. Команда init создала структуру директорий нашего проекта:
shop_schema sqlibrist.yaml migrations schema constraints functions indexes tables triggers types views В sqlibrist.yaml конфигурация проекта для подключения к БД:
--- default: engine: pg user: <username> name: <database_name> password: <password> # host: 127.0.0.1 # port: 5432 Чтобы проверить, что настройки верны:
$ sqlibrist test_connection Connection OK Дальше проинициализируем таблицу, где Sqlibrist будет хранить информацию о примененных миграциях. Эта часть идентична Django Migrations/South.
$ sqlibrist initdb Creating db... Creating schema and migrations log table... Done. Кстати, в терминологии Sqlibrist, миграция — это снимок структуры базы и патчи для применения этой миграции или отката к предыдущей.
Далее создадим файл shop_schema/schema/tables/user.sql:
--UP CREATE TABLE "user" ( id SERIAL PRIMARY KEY, name TEXT, password TEXT); Первая строка —UP означает, что следующие SQL-инструкции создают объект БД. Этого достаточно для создания таблицы.
Аналогично создадим еще два файла:
shop_schema/schema/tables/product.sql:
--UP CREATE TABLE product ( id SERIAL PRIMARY KEY, name TEXT, price MONEY); shop_schema/schema/tables/order.sql:
--REQ tables/user --UP CREATE TABLE "order" ( id SERIAL PRIMARY KEY, user_id INTEGER REFERENCES "user"(id), date DATE); Обратите внимание на строку —REQ tables/user. Она означает, что текущий объект зависит от объекта в файле tables/user.sql (в REQ расширение не пишется). Это гарантирует, что при генерации патча таблица user будет создана перед таблицей order. Все —REQ должны идти в начале файла.
Еще один файл:
shop_schema/schema/tables/order_product.sql:
--REQ tables/order --UP CREATE TABLE order_product ( id SERIAL PRIMARY KEY, order_id INTEGER REFERENCES "order"(id), product_id INTEGER REFERENCES product(id), quantity INTEGER); Создадим первую миграцию:
$ sqlibrist makemigration -n 'initial' Creating: tables/user tables/product tables/order tables/order_product Creating new migration 0001-initial Файлы миграции созданы в shop_schema/migrations/0001-initial:
up.sql down.sql schema.json В up.sql содержится патч для применения миграции, down.sql в данном случае пустой, а в schema.json снимок текущей структуры БД.
Перед применением патча вы можете (и это желательно) ознакомиться с текстом патча и удостовериться, что он делает то, что нужно. Если он вас не устраивает, удалите всю директорию 0001-initial и создайте миграцию заново. Можете редактировать up.sql и down.sql, если знаете, что делаете, но не трогайте schema.json.
Теперь применим нашу первую миграцию:
$ sqlibrist migrate Applying migration 0001-initial... done Три таблицы созданы. Теперь нам нужен вид, который выводит заказы пользователя с суммами заказа:
shop_schema/schema/views/user_orders.sql:
--REQ tables/user --REQ tables/order --REQ tables/product --REQ tables/order_product --UP CREATE VIEW user_orders AS SELECT u.id as user_id, o.id as order_id, o.date, SUM(p.price*op.quantity) AS total FROM "user" u INNER JOIN "order" o ON u.id=o.user_id INNER JOIN order_product op ON o.id=op.order_id INNER JOIN product p ON p.id=op.product_id GROUP BY o.id, u.id; --DOWN DROP VIEW user_orders; После директивы —DOWN идут инструкции для удаления user_orders при его пересоздании.
Общее правило: содержащие данные объекты, например таблицы, мы обновляем вручную, поэтому их описания не содержат —DOWN, а функции, типы, индексы можно безболезненно удалять и создавать, поэтому это можно доверить автоматике.
Еще нам нужна функция, которая возвращает user_orders для заданного пользователя:
--REQ views/user_orders --UP CREATE FUNCTION get_user_orders(_user_id INTEGER) RETURNS SETOF user_orders LANGUAGE SQL AS $$ SELECT * FROM user_orders WHERE user_id=_user_id; $$; --DOWN DROP FUNCTION get_user_orders(INTEGER); Создадим и применим следующую миграцию:
$ sqlibrist makemigration -n 'user_orders view and function' Creating: views/user_orders functions/get_user_orders Creating new migration 0002-user_orders view and function $ sqlibrist migrate Applying migration 0002-user_orders view and function... done Таким образом, у нас 4 таблицы, один вид и одна функция.
Допустим, нам нужно добавить еще одно поле в вид user_orders. Вот какие проблемы могут возникнуть:
- мы можем удалить и создать заново новый вид user_orders, но БД не позволит этого сделать, потому что функция get_user_orders зависит от этого вида;
- можно схитрить и выкрутиться CREATE OR REPLACE VIEW user_orders…, но тип поля вида и тип результата функции будут отличаться. И в этом случае БД не даст нам этого сделать без пересоздания функции.
Sqlibrist как раз и предназначен разрешать такие проблемы. Добавим поле SUM(op.quantity) as order_total в вид user_orders:
--REQ tables/user --REQ tables/order --REQ tables/product --REQ tables/order_product --UP CREATE VIEW user_orders AS SELECT u.id as user_id, o.id as order_id, o.date, SUM(p.price*op.quantity) AS total, SUM(op.quantity) as order_total FROM "user" u INNER JOIN "order" o ON u.id=o.user_id INNER JOIN order_product op ON o.id=op.order_id INNER JOIN product p ON p.id=op.product_id GROUP BY o.id, u.id; --DOWN DROP VIEW user_orders; Можно посмотреть, что же изменилось:
$ sqlibrist -V diff Changed items: views/user_orders --- +++ @@ -2,7 +2,8 @@ u.id as user_id, o.id as order_id, o.date, - SUM(p.price*op.quantity) AS total + SUM(p.price*op.quantity) AS total, + SUM(op.quantity) as total_quantity FROM "user" u INNER JOIN "order" o ON u.id=o.user_id Создадим миграцию:
$ sqlibrist makemigration Updating: dropping: functions/get_user_orders views/user_orders creating: views/user_orders functions/get_user_orders Creating new migration 0003-auto Вы видите, что сначала удаляется зависящий объект — функция get_user_orders, потом сам вид. Далее вид создается с новой структурой, после восстанавливается функция. Такая схема будет работать для зависимостей произвольной глубины (но не циклической зависимости — Sqlibrist попросит исправить ее).
Применим эту миграцию:
$ sqlibrist migrate Applying migration 0003-auto... done Наконец, давайте внесем изменение в таблицу. Поскольку файлы с определениями таблиц не содержат —DROP, будем работать руками:
- Изменим инструкцию CREATE TABLE;
- Сгенерируем новую миграцию той же командой makemigration;
- Добавим в up.sql необходимый ALTER TABLE.
Добавим новое поле «type» text в таблицу product:
shop_schema/schema/tables/product.sql:
--UP CREATE TABLE product ( id SERIAL PRIMARY KEY, name TEXT, "type" TEXT, price MONEY); Это пункт 1. Теперь создадим миграцию:
$ sqlibrist makemigration -n 'new product field' Updating: dropping: functions/get_user_orders views/user_orders creating: views/user_orders functions/get_user_orders Creating new migration 0004-new product field Обратите внимание, что несмотря на то, что мы изменили определение таблицы product, tables/product не присутствует в журнале миграции, НО все зависимые от него объекты пересоздаются. Это пункт 2.
Теперь пункт 3: откройте в редакторе shop_schema/migrations/0004-new product field/up.sql и найдите строку 12 с текстом — ==== Add your instruction here ====. Это логическая середина миграции. В этот момент все зависимые объекты удалены и мы можем вставить наш ALTER TABLE.
Вставьте следующее:
ALTER TABLE product ADD COLUMN "type" TEXT; Наш up.sql будет выглядеть так:
-- begin -- DROP FUNCTION get_user_orders(INTEGER); -- end -- -- begin -- DROP VIEW user_orders; -- end -- -- begin -- -- ==== Add your instruction here ==== ALTER TABLE product ADD COLUMN "type" TEXT; -- end -- -- begin -- CREATE VIEW user_orders AS SELECT u.id as user_id, o.id as order_id, o.date, SUM(p.price*op.quantity) AS total, SUM(op.quantity) as total_quantity FROM "user" u INNER JOIN "order" o ON u.id=o.user_id INNER JOIN order_product op ON o.id=op.order_id INNER JOIN product p ON p.id=op.product_id GROUP BY o.id, u.id; -- end -- -- begin -- CREATE FUNCTION get_user_orders(_user_id INTEGER) RETURNS SETOF user_orders LANGUAGE SQL AS $$ SELECT * FROM user_orders WHERE user_id=_user_id; $$; -- end -- Можно применить этот патч:
$ sqlibrist migrate Applying migration 0004-new product field... done На этом моменте оставим в покое наш интернет-магазин.
Еще Sqlibrist умеет интегрироваться в проект Django, я им пользуюсь именно в этом контексте.
Сайт проекта — здесь, баг-репорты приветствуются.
ссылка на оригинал статьи https://habrahabr.ru/post/282123/
Добавить комментарий