Перейти к содержанию

На практике

В учебных примерах у нас была одна схема базы, все связи были объявлены внешними ключами, а сама схема не менялась. В настоящем проекте всё сложнее: таблицы бывают разложены по нескольким схемам PostgreSQL, не каждая связь оформлена внешним ключом, а структура базы меняется с каждой миграцией. В этой главе мы посмотрим, как pg-schema справляется со всем этим и как встроить генерацию схемы в процесс разработки и в CI.

Примеры этой главы лежат в tutorial/app/app/Tut/Practice.hs, а генератор — в tutorial/app/generator/Main.hs. Перед примерами программа пересоздаёт схему и добавляет двух пользователей, Alice (id = 1) и Bob (id = 2).

Что описывает генератор

Какие таблицы и связи попадут в Sch.hs, определяет последний аргумент updateSchemaFile — значение типа GenNames. У нашего генератора оно такое:

genNames :: GenNames
genNames = GenNames
  { schemas = ["tut", "audit"] -- all tables of these schemas
  , tables = []                -- and single tables of other schemas (none here)
  , addRelations =             -- relations that are missing in the DB
    [ AddRelation
      { name = "task_events_author"
      , from = "tut" ->> "task_events"
      , to = "tut" ->> "users"
      , cols = [("author_id", "id")] } ] }

В schemas перечисляются схемы базы, все таблицы которых нужно описать. Если из какой-то схемы нужны только отдельные таблицы, их можно перечислить в tables полными именами, например ["billing" ->> "invoices"]. Связи между выбранными таблицами генератор находит сам по внешним ключам, а в addRelations можно добавить связи, которых в базе нет. Обо всём этом по порядку.

Несколько схем базы

В учебной базе кроме схемы tut есть схема audit с журналом изменений проектов:

create schema audit;

create table audit.project_log (
  id          bigserial primary key,
  project_id  bigint not null references tut.projects(id) on delete cascade,
  message     text not null,
  client_ip   inet,                                 -- a type with no built-in mapping (appendix "Data types")
  created_at  timestamptz not null default now()
);

Раз обе схемы перечислены в GenNames, таблица audit.project_log описана в том же Sch.hs, что и остальные. Аннотация для неё отличается от MyAnn из главы «Рабочая модель pg-schema» только схемой базы в имени таблицы, поэтому заведём для неё такой же синоним и, как MyAnn, будем передавать его операциям первым аргументом:

type AuditAnn t = 'Ann MyRenamer Sch 3 ("audit" ->> t)

Внешний ключ audit.project_log.project_id → tut.projects.id — обычная связь, и то, что таблицы лежат в разных схемах, ничего не меняет. Можно, например, вставить проект вместе с записями журнала одним вызовом insertJSON:

type LogIn = "message" := Text

type ProjectWithLog = "ownerId" := Int64 :. "title" := Text
  :. "projectLogProjectIdFkey" := [LogIn]

(keys, sqlIns) <- insertJSON (MyAnn "projects") conn $ rowsOf @ProjectWithLog
  [ 1 :. "Website" :. [ "created", "owner assigned" ] ]
print (plains @("id" := Int64 :. "projectLogProjectIdFkey" := ["id" := Int64]) keys)

[1 :. [1,2]]
SQL, отформатированный для чтения 
create or replace function pg_temp.__ins(data_0 jsonb) returns jsonb as $$
declare
  found_0 boolean;
  row_0 record;
  arr_0 jsonb[];
  id0 int8; 
  data_1 jsonb;
  found_1 boolean;
  row_1 record;
  arr_1 jsonb[];
  id1 int8; 
begin
  arr_0:= '{}';
  for row_0 in select * from jsonb_array_elements(data_0)
  loop
    insert into tut.projects(owner_id, title)
      values ((row_0.value->>'ownerId')::int8, (row_0.value->>'title')::text)
      returning id into id0;
      found_0 := found;
    data_1 := row_0.value->'projectLogProjectIdFkey';
    arr_1:= '{}';
    for row_1 in select * from jsonb_array_elements(data_1)
    loop
      insert into audit.project_log(project_id, message)
        values (id0, (row_1.value->>'message')::text)
        returning id into id1;
          found_1 := found;
      arr_1 := array_append(arr_1, jsonb_build_object('id', id1));
    end loop;
    arr_0 := array_append(arr_0, jsonb_build_object('id', id0, 'projectLogProjectIdFkey', to_jsonb(arr_1)));
  end loop;
  return to_jsonb(arr_0);
end; 
$$ language plpgsql;

А читать журнал можно, начиная с таблицы audit.project_log, вместе с родительским проектом из схемы tut:

(rows, (sql, _)) <- selectSch (AuditAnn "project_log")
  @("message" := Text :. "projectLogProjectIdFkey" := ("title" := Text))
  conn $ qRoot do
    qOrderBy [ascf "id"]
mapM_ print rows

"message" =: "created" :. "projectLogProjectIdFkey" =: ("title" =: "Website")
"message" =: "owner assigned" :. "projectLogProjectIdFkey" =: ("title" =: "Website")
SQL, отформатированный для чтения 
select t0.message "message",
       jsonb_build_object('title',t1.title) "projectLogProjectIdFkey"
from audit.project_log t0
join tut.projects t1 on t0.project_id=t1.id
order by t0.id Asc nulls last;

Связи, которых нет в базе

Иногда связь между таблицами есть по смыслу, но не объявлена внешним ключом. Так бывает с представлениями (у них внешних ключей не бывает вовсе), с секционированными таблицами, со старыми базами или когда внешний ключ сознательно не заводят, например из-за нагрузки. В учебной базе так устроена колонка task_events.author_id: она хранит id пользователя, но внешнего ключа на users у неё нет.

Такую связь можно описать в генераторе через AddRelation, как в GenNames выше: имя связи, таблица, из которой она ведёт, таблица, в которую она ведёт, и пары колонок. После этого pg-schema работает с ней так же, как с внешним ключом. В типах результата и в qPath её имя, как обычно, проходит через ренеймер: "taskEventsAuthor". Там же, где связь задаётся полным именем вместе со схемой, как в pchild и pparent из главы «Select», вместо схемы базы указывается _add: все добавленные связи генератор помещает в это пространство имён. Пример с pparent будет чуть ниже.

Добавим задачи с событиями. Колонка author_id — обычная колонка, и мы заполняем её значениями, а у одного события намеренно укажем несуществующего пользователя 99:

type EventIn = "eventNo" := Int32 :. "kind" := Text :. "authorId" := Maybe Int64

type TaskIn = "seq" := Int32 :. "title" := Text
  :. "taskEventsProjectIdSeqFkey" := [EventIn]

_ <- insertJSON_ (MyAnn "tasks") conn $ rowsOf @("projectId" := Int64 :. TaskIn)
  [ 1 :. 1 :. "Design" :. [ 1 :. "created" :. Just 1, 2 :. "reviewed" :. Just 2 ]
  , 1 :. 2 :. "Backend" :. [ 1 :. "created" :. Nothing, 2 :. "edited" :. Just 99 ] ]

Теперь прочитаем события вместе с их авторами:

(rows, (sql, _)) <- selectSch (MyAnn "task_events")
  @( "seq" := Int32 :. "eventNo" := Int32 :. "kind" := Text
  :. "taskEventsAuthor" := Maybe ("name" := Text) )
  conn $ qRoot do
    qOrderBy [ascf "seq", ascf "eventNo"]
mapM_ print rows

"seq" =: 1 :. ("eventNo" =: 1 :. ("kind" =: "created" :. "taskEventsAuthor" =: Just ("name" =: "Alice")))
"seq" =: 1 :. ("eventNo" =: 2 :. ("kind" =: "reviewed" :. "taskEventsAuthor" =: Just ("name" =: "Bob")))
"seq" =: 2 :. ("eventNo" =: 1 :. ("kind" =: "created" :. "taskEventsAuthor" =: Nothing))
"seq" =: 2 :. ("eventNo" =: 2 :. ("kind" =: "edited" :. "taskEventsAuthor" =: Nothing))
SQL, отформатированный для чтения 
select t0.seq "seq",
       t0.event_no "eventNo",
       t0.kind "kind",
       case when t1.name is null then null else jsonb_build_object('name',t1.name) end "taskEventsAuthor"
from tut.task_events t0
left outer join tut.users t1 on t0.author_id=t1.id
order by t0.seq Asc nulls last,t0.event_no Asc nulls last;

Колонка author_id допускает NULL, поэтому автор — Maybe, и в запросе используется left outer join. У события (2, 1) автора нет, потому что author_id пуст, а у события (2, 2) — потому что пользователя 99 не существует. По той же связи можно пройти и в обратную сторону, от пользователя к его событиям:

(rows, (sql, _)) <- selectSch (MyAnn "users")
  @("name" := Text :. "taskEventsAuthor" := ["kind" := Text])
  conn $ qRoot do
    qOrderBy [ascf "name"]
mapM_ print rows

"name" =: "Alice" :. "taskEventsAuthor" =: ["kind" =: "created"]
"name" =: "Bob" :. "taskEventsAuthor" =: ["kind" =: "reviewed"]
SQL, отформатированный для чтения 
select t0.name "name",
       array_to_json(array(
           select jsonb_build_object('kind',t1.kind)
           from tut.task_events t1
           where t1.author_id=t0.id)) "taskEventsAuthor"
from tut.users t0
order by t0.name Asc nulls last;

А так выглядит отбор событий по автору с помощью pparent. Как и в главе «Select», полное имя связи удобно записать через синоним типа, только схема базы здесь _add:

type Add name = "_add" ->> name

(rows, (sql, _)) <- selectSch (MyAnn "task_events")
  @("seq" := Int32 :. "eventNo" := Int32 :. "kind" := Text) conn $ qRoot do
    qWhere $ pparent (Add "taskEventsAuthor") ("name" =? ("Alice" :: Text))
mapM_ print rows

"seq" =: 1 :. ("eventNo" =: 1 :. "kind" =: "created")
SQL, отформатированный для чтения 
select t0.seq "seq",
       t0.event_no "eventNo",
       t0.kind "kind"
from tut.task_events t0
where exists (
    select 1
    from (
        select *
        from tut.users t0q1
        where t0q1.id = t0.author_id) t0q1
    where t0q1.name = ?);

В документации GenNames (её фрагмент есть в главе «Рабочая модель pg-schema») про добавленные связи сказано «Be careful!». База ничего о них не знает и не проверяет: в author_id может оказаться id несуществующего пользователя, как у события (2, 2), и автор просто не найдётся. Компилятор проверит, что запросы соответствуют описанию связи, но за то, что описание соответствует данным, отвечаете вы.

Когда схема меняется

Самое полезное свойство pg-schema проявляется тогда, когда структура базы меняется. Возьмём для примера миграцию, которая переименовывает колонку и добавляет обязательную:

-- tutorial/sql/migration-example.sql
-- An example migration for chapter 07. Not part of the tutorial schema:
-- `make tut-db-init` restores the database after the experiment.
alter table tut.tasks rename column priority to weight;
alter table tut.projects add column code text not null;

Колонка code добавляется без значения по умолчанию, поэтому такая миграция пройдёт только на пустой таблице, как в нашей учебной базе сразу после make tut-db-init. В настоящей базе понадобилось бы значение по умолчанию или заполнение колонки, но для pg-schema это ничего не меняет: важно лишь, что колонка обязательна.

После того как миграция применена, нужно перегенерировать Sch.hs:

psql "$PG_CONN" -f tutorial/sql/migration-example.sql
cabal run tutorial:generator

Генератор перезапишет файл, а git diff покажет, что именно изменилось в описании базы. Предыдущая версия сохраняется в Sch.hs.bak и перезаписывается при каждой генерации, так что этот файл стоит добавить в .gitignore. Если описание базы не изменилось, файл не трогается, а вывод генератора детерминирован, поэтому ложных изменений в git diff не бывает. Теперь достаточно собрать проект, и компилятор перечислит все места, которые миграция сломала. На учебном проекте таких ошибок 42, в пяти модулях. Вот две из них. В главе «Изменение данных в одной таблице» мы вставляли проекты без колонки code:

• We can't insert data because not all mandatory fields are present.
  Table: NameNS "tut" "projects"
  Missing mandatory fields: '["code"]

А в главе «Select» условие использовало колонку priority, которой больше нет:

• In schema Sch
  for table "tut" ->> "tasks"
  name "priority" is not defined.

  Valid values are:
    Fields: project_id, seq, title, weight, payload, watchers.
    Foreign key constraints: tasks_project_id_fkey, task_events_project_id_seq_fkey.

  Your source or target type or renamer is probably invalid.

Без pg-schema каждая из этих ошибок обнаружилась бы только при выполнении соответствующего запроса — в тестах, если они его покрывают, или уже в работе. Здесь же компилятор сразу показывает полный список мест, которые нужно исправить, и пока он не пуст, программа не соберётся.

Чтобы вернуть учебную базу в исходное состояние, достаточно снова выполнить make tut-db-init и cabal run tutorial:generator.

Отсюда важное практическое правило: генератор должен быть отдельной программой (в нашем проекте — отдельным исполняемым компонентом cabal), которая не зависит от Sch.hs. Иначе после миграции, пока Sch.hs не перегенерирован, не соберётся и сам генератор. Запускать его нужно из того каталога, относительно которого указан путь к Sch.hs, у нас — из корня репозитория.

Проверка в CI

Остаётся убедиться, что Sch.hs в репозитории не отстаёт от схемы базы: кто-то мог добавить миграцию и забыть перегенерировать файл. Для этого пригодится результат updateSchemaFile: он возвращает True, если файл пришлось создать или изменить. Наш генератор принимает флаг --check и в этом случае завершается с ошибкой. Вот он целиком (первый аргумент updateSchemaFile включает подробный вывод):

{-# LANGUAGE OverloadedStrings #-}
module Main (main) where

import Control.Monad (when)
import PgSchema.Generation
import System.Environment (getArgs)
import System.Exit (exitFailure)
import System.IO (hPutStrLn, stderr)

genNames :: GenNames
genNames = GenNames
  { schemas = ["tut", "audit"] -- all tables of these schemas
  , tables = []                -- and single tables of other schemas (none here)
  , addRelations =             -- relations that are missing in the DB
    [ AddRelation
      { name = "task_events_author"
      , from = "tut" ->> "task_events"
      , to = "tut" ->> "users"
      , cols = [("author_id", "id")] } ] }

main :: IO ()
main = do
  check <- elem "--check" <$> getArgs
  changed <- updateSchemaFile False "tutorial/app/app/Sch.hs"
    (Left "PG_CONN") -- connection string from the PG_CONN environment variable
    "Sch" -- name of the generated module
    "Sch" -- name of the schema type
    genNames
  when (check && changed) $ do
    hPutStrLn stderr "Sch.hs did not match the database and has been regenerated"
    exitFailure

В CI достаточно поднять базу (например, сервис PostgreSQL в контейнере), применить к ней все миграции тем же инструментом, которым вы пользуетесь в проекте, задать PG_CONN и запустить генератор с этим флагом. Если Sch.hs устарел, генератор завершится с ошибкой, и сборка не пройдёт. Заодно он перезапишет файл, так что при запуске на своей машине останется только посмотреть git diff и закоммитить изменения.

В учебном проекте всё это делает цель Makefile:

make tut-schema-check

Она пересоздаёт учебную базу скриптом схемы и запускает cabal run tutorial:generator -- --check. В настоящем проекте базу пересоздавать, конечно, не нужно: генератору достаточно базы, к которой применены все миграции.

Можно поступить и иначе: не хранить Sch.hs в репозитории, а генерировать его в CI прямо перед сборкой. Тогда проверка не нужна, но сборка без базы становится невозможной. Если же в проекте несколько баз, для каждой генерируется свой модуль: updateSchemaFile вызывается несколько раз с разными путями, именами модулей и типов схемы.

Резюме

  • GenNames задаёт схемы, отдельные таблицы и добавленные связи.
  • Таблицы из разных схем базы описываются в одном Sch.hs, и связи между ними работают как обычно.
  • Связь без внешнего ключа добавляется через AddRelation, но база её не проверяет.
  • После миграции нужно перегенерировать Sch.hs, и компилятор покажет все места, которые нужно исправить.
  • В CI генератор с проверкой (--check) не даёт забыть о перегенерации.

Что дальше

Это последняя глава учебника. Сообщения компилятора, которые встречаются при работе с pg-schema, собраны в главе «Ошибки компиляции», а правила и ограничения — в справочнике.