Управление структурой базы данных без боли

в 9:37, , рубрики: mysql, postgresql, Администрирование баз данных, базы данных

image

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

Сам проект написан на 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, будем работать руками:

  1. Изменим инструкцию CREATE TABLE;
  2. Сгенерируем новую миграцию той же командой makemigration;
  3. Добавим в 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, я им пользуюсь именно в этом контексте.

Сайт проекта — здесь, баг-репорты приветствуются.

Автор: fevral13

Источник

* - обязательные к заполнению поля

Главная   |  Архив новостей  |   Android  |   Google  |   Apple  |   Microsoft  |   Информационная безопасность  |   Веб – разработка
Публикации RSS  |  Комментарии RSS
https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js