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

Изменение связанных данных

В главе «Изменение данных в одной таблице» каждая операция работала с одной таблицей. Но данные часто приходят целым деревом: проект вместе с задачами, а задачи вместе с событиями. Записывать такое дерево по частям неудобно: нужно вставить проект, узнать его id, подставить его в задачи, вставить их и так далее. pg-schema умеет записать всё дерево одной операцией. Для этого есть три функции: insertJSON вставляет новые данные, upsertJSON вставляет новые и изменяет существующие, а updateJSON только изменяет. У каждой есть вариант с подчёркиванием (insertJSON_ и так далее), который ничего не возвращает из базы.

Все три функции устроены одинаково, и мы уже видели это в главе «Быстрый старт». По типам входных данных и результата pg-schema генерирует временную функцию на plpgsql, создаёт её в базе, вызывает, передав всё дерево одним параметром типа jsonb, и удаляет. Это три команды, и сколько бы строк ни было в дереве, их число не меняется. Если транзакция ещё не открыта (это видно по состоянию соединения), всё это выполняется в отдельной транзакции, так что дерево записывается целиком или не записывается вовсе.

Примеры этой главы лежат в tutorial/app/app/Tut/TreeDml.hs. Перед ними программа пересоздаёт схему и добавляет одного пользователя с id = 1, владельца всех проектов.

Вставка: insertJSON

Входные данные опишем типами. Дочерние строки — это поля со списком строк, названные по имени внешнего ключа. Как и в главах «Быстрый старт» и «Select», это имя ограничения в базе (tasks_project_id_fkey, task_events_project_id_seq_fkey), прошедшее через ренеймер MyRenamer, поэтому мы пишем его в camelCase:

type EventIn = "eventNo" := Int32 :. "kind" := Text

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

type ProjectIn = "ownerId" := Int64 :. "title" := Text :. "status" := ProjectStatus
  :. "tags" := PgArr Text :. "tasksProjectIdFkey" := [TaskIn]

В типе задачи нет колонки project_id, а в типе события — колонок project_id и seq, хотя все они обязательны. Их значения берутся из родительской строки: insertJSON сначала вставляет проект, получает его id и подставляет его в задачи, а затем так же поступает с событиями. Поэтому insertJSON требует во входных данных все обязательные колонки, кроме тех, что приходят из связи с родителем, и тех, у которых есть значение по умолчанию. Если обязательной колонки не хватает, программа не скомпилируется, так же как с insertSch в главе «Изменение данных в одной таблице».

Сами деревья удобно строить функцией rowsOf. Она похожа на named из главы «Изменение данных в одной таблице», но named получает имена полей списком строк, а rowsOf берёт и имена, и типы полей из типа строки, который передаётся ей как rowsOf @ProjectIn. Поэтому значения можно писать без имён и без аннотаций типов, а дочерние строки — просто вложенными списками.

Как и в плоских операциях, тип результата задаёт, что вернуть после вставки. Вернём ключи всех вставленных строк, на всех трёх уровнях:

type ProjectKeys = "id" := Int64
  :. "tasksProjectIdFkey" := [ "seq" := Int32 :. "taskEventsProjectIdSeqFkey" := ["eventNo" := Int32] ]

(keys, sqlIns) <- insertJSON (MyAnn "projects") conn $ rowsOf @ProjectIn
  [ 1 :. "Website" :. Project_status_active :. pgArr' ["web"] :.
      [ 1 :. "Design" :. 10 :. [ 1 :. "created", 2 :. "assigned" ]
      , 2 :. "Frontend" :. 30 :. [] ]
  , 1 :. "API" :. Project_status_draft :. pgArr' [] :.
      [ 1 :. "Schema" :. 20 :. [ 1 :. "created" ] ] ]
mapM_ print (keys :: [ProjectKeys])

"id" =: 1 :. "tasksProjectIdFkey" =: ["seq" =: 1 :. "taskEventsProjectIdSeqFkey" =: ["eventNo" =: 1,"eventNo" =: 2],"seq" =: 2 :. "taskEventsProjectIdSeqFkey" =: []]
"id" =: 2 :. "tasksProjectIdFkey" =: ["seq" =: 1 :. "taskEventsProjectIdSeqFkey" =: ["eventNo" =: 1]]
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[];
  project_id1 int8; 
  seq1 int4; 
  data_2 jsonb;
  found_2 boolean;
  row_2 record;
  arr_2 jsonb[];
  event_no2 int4; 
begin
  arr_0:= '{}';
  for row_0 in select * from jsonb_array_elements(data_0)
  loop
    insert into tut.projects(owner_id, title, status, tags)
      values ((row_0.value->>'ownerId')::int8, (row_0.value->>'title')::text, (row_0.value->>'status')::tut.project_status, case when jsonb_typeof(row_0.value->'tags') = 'array' then (select coalesce(array_agg(__x)::text[], '{}') from jsonb_array_elements_text(row_0.value->'tags') __x) else null end)
      returning id into id0;
      found_0 := found;
    data_1 := row_0.value->'tasksProjectIdFkey';
    arr_1:= '{}';
    for row_1 in select * from jsonb_array_elements(data_1)
    loop
      insert into tut.tasks(project_id, seq, title, priority)
        values (id0, (row_1.value->>'seq')::int4, (row_1.value->>'title')::text, (row_1.value->>'priority')::int4)
        returning project_id, seq into project_id1, seq1;
          found_1 := found;
      data_2 := row_1.value->'taskEventsProjectIdSeqFkey';
      arr_2:= '{}';
      for row_2 in select * from jsonb_array_elements(data_2)
      loop
        insert into tut.task_events(project_id, seq, event_no, kind)
          values (project_id1, seq1, (row_2.value->>'eventNo')::int4, (row_2.value->>'kind')::text)
          returning event_no into event_no2;
              found_2 := found;
        arr_2 := array_append(arr_2, jsonb_build_object('eventNo', event_no2));
      end loop;
      arr_1 := array_append(arr_1, jsonb_build_object('seq', seq1, 'taskEventsProjectIdSeqFkey', to_jsonb(arr_2)));
    end loop;
    arr_0 := array_append(arr_0, jsonb_build_object('id', id0, 'tasksProjectIdFkey', to_jsonb(arr_1)));
  end loop;
  return to_jsonb(arr_0);
end; 
$$ language plpgsql;

В типе результата могут быть любые колонки таблиц, в том числе те, которых не было во входных данных, как id проекта. А вот ветки результата должны быть среди веток входных данных: вернуть задачи можно только тогда, когда мы их вставляли. Это, как и остальные правила для типа результата, проверяется при компиляции.

insertJSON_ делает то же самое, но ничего не возвращает из базы. Вместо функции он создаёт временную процедуру, а в программу возвращает только сгенерированный SQL. Если результат не нужен, это немного экономит работу: базе не приходится собирать ответ в jsonb, а программе — разбирать его.

sqlInsNr <- insertJSON_ (MyAnn "projects") conn $ rowsOf @ProjectIn
  [ 1 :. "Docs" :. Project_status_draft :. pgArr' [] :. [] ]
SQL, отформатированный для чтения 
create or replace procedure pg_temp.__ins(data_0 jsonb) as $$
declare
  found_0 boolean;
  row_0 record;
  id0 int8; 
  data_1 jsonb;
  found_1 boolean;
  row_1 record;
  project_id1 int8; 
  seq1 int4; 
  data_2 jsonb;
  found_2 boolean;
  row_2 record;
begin
  for row_0 in select * from jsonb_array_elements(data_0)
  loop
    insert into tut.projects(owner_id, title, status, tags)
      values ((row_0.value->>'ownerId')::int8, (row_0.value->>'title')::text, (row_0.value->>'status')::tut.project_status, case when jsonb_typeof(row_0.value->'tags') = 'array' then (select coalesce(array_agg(__x)::text[], '{}') from jsonb_array_elements_text(row_0.value->'tags') __x) else null end)
      returning id into id0;
      found_0 := found;
    data_1 := row_0.value->'tasksProjectIdFkey';
    for row_1 in select * from jsonb_array_elements(data_1)
    loop
      insert into tut.tasks(project_id, seq, title, priority)
        values (id0, (row_1.value->>'seq')::int4, (row_1.value->>'title')::text, (row_1.value->>'priority')::int4)
        returning project_id, seq into project_id1, seq1;
          found_1 := found;
      data_2 := row_1.value->'taskEventsProjectIdSeqFkey';
      for row_2 in select * from jsonb_array_elements(data_2)
      loop
        insert into tut.task_events(project_id, seq, event_no, kind)
          values (project_id1, seq1, (row_2.value->>'eventNo')::int4, (row_2.value->>'kind')::text);
              found_2 := found;
      end loop;
    end loop;
  end loop;

end; 
$$ language plpgsql;

Вставка или изменение: upsertJSON

insertJSON только вставляет. Если строка с таким ключом уже есть, PostgreSQL сообщит о нарушении уникальности, и вся операция будет отменена. Когда же данные могут быть как новыми, так и уже существующими, нужен upsertJSON. Что делать со строками, он решает для каждого уровня дерева (то есть для каждой таблицы) по полям его типа, и решает это при компиляции:

  • если в типе есть ключ, но есть не все обязательные колонки, строки изменяются по этому ключу (update);
  • если есть и ключ, и все обязательные колонки, строки вставляются, а при конфликте по ключу изменяются (insert … on conflict … do update);
  • если есть все обязательные колонки, но нет ключа, строки просто вставляются (insert);
  • если нет ни ключа, ни всех обязательных колонок, программа не скомпилируется: со строкой нечего делать.

Обязательные колонки здесь те же, что для insertJSON: NOT NULL без значения по умолчанию, кроме тех, что приходят от родителя. Ключом считается первичный ключ, а если его колонок в типе нет — уникальный ключ, все колонки которого есть в типе. Сначала рассматриваются уникальные ключи из NOT NULL-колонок, а ключи с nullable-колонками — только если первых в таблице нет. Колонки, которые приходят от родителя, тоже считаются. После родительской строки так же обрабатываются её дочерние строки. Изменим проект 1: переименуем его первую задачу, добавим ей событие и добавим проекту новую задачу:

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

type ProjectPatch = "id" := Int64 :. "tasksProjectIdFkey" := [TaskPatch]

type PatchOut = Maybe ("id" := Int64 :. "tasksProjectIdFkey" := ["seq" := Int32 :. "title" := Text])

(res, sqlUps) <- upsertJSON (MyAnn "projects") conn $ rowsOf @ProjectPatch
  [ 1 :.
      [ 1 :. "Design v2" :. [ 3 :. "renamed" ]
      , 3 :. "Backend" :. [] ] ]
mapM_ print (res :: [PatchOut])

Just ("id" =: 1 :. "tasksProjectIdFkey" =: ["seq" =: 1 :. "title" =: "Design v2","seq" =: 3 :. "title" =: "Backend"])
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[];
  project_id1 int8; 
  seq1 int4; 
  title1 text; 
  data_2 jsonb;
  found_2 boolean;
  row_2 record;
begin
  arr_0:= '{}';
  for row_0 in select * from jsonb_array_elements(data_0)
  loop
    update tut.projects
      set id = (row_0.value->>'id')::int8
      where id IS NOT DISTINCT FROM (row_0.value->>'id')::int8
      returning id into id0;
      found_0 := found;
    if found_0 then
      data_1 := row_0.value->'tasksProjectIdFkey';
      arr_1:= '{}';
      for row_1 in select * from jsonb_array_elements(data_1)
      loop
        insert into tut.tasks(project_id, seq, title)
          values (id0, (row_1.value->>'seq')::int4, (row_1.value->>'title')::text)
          on conflict (project_id, seq)
            do update set title = EXCLUDED.title
          returning project_id, seq, title into project_id1, seq1, title1;
              found_1 := found;
        data_2 := row_1.value->'taskEventsProjectIdSeqFkey';
        for row_2 in select * from jsonb_array_elements(data_2)
        loop
          insert into tut.task_events(project_id, seq, event_no, kind)
            values (project_id1, seq1, (row_2.value->>'eventNo')::int4, (row_2.value->>'kind')::text)
            on conflict (project_id, seq, event_no)
              do update set kind = EXCLUDED.kind;
                  found_2 := found;
        end loop;
        arr_1 := array_append(arr_1, jsonb_build_object('seq', seq1, 'title', title1));
      end loop;
    end if;
    if found_0 then
      arr_0 := array_append(arr_0, jsonb_build_object('id', id0, 'tasksProjectIdFkey', to_jsonb(arr_1)));
    else
      arr_0 := array_append(arr_0, 'null'::jsonb);
    end if;
  end loop;
  return to_jsonb(arr_0);
end; 
$$ language plpgsql;

Корень здесь — только ключ проекта, поэтому проект изменяется по id (менять в нём нечего, но найти его нужно, чтобы дойти до задач). У задач есть ключ (project_id, seq), где project_id приходит от проекта, и все обязательные колонки: title мы передали, а у priority есть значение по умолчанию. Поэтому для задач выполняется insert … on conflict: задача 1 уже была, и у неё изменилось название, а задачи 3 не было, и она вставилась. При конфликте изменяются только колонки, которые есть в типе, поэтому приоритет задачи 1 остался прежним. Так же вставилось и новое событие.

Обратите внимание на тип результата PatchOut: корень в нём обёрнут в Maybe, а задачи — нет. Проект изменяется по ключу (update) и может не найтись, а задачи идут через insert … on conflict, и строка задачи после этого есть всегда. Поэтому Maybe нужен только там, где выполняется update. Если строка не нашлась, на её месте в результате будет Nothing, а её дочерние строки обрабатываться не будут:

(res, _) <- upsertJSON (MyAnn "projects") conn $ rowsOf @ProjectPatch
  [ 999 :. [ 1 :. "Ghost" :. [] ] ]
print (res :: [PatchOut])

[Nothing]

Проекта 999 нет, и задача «Ghost» не вставилась. Где в результате upsertJSON обязателен Maybe, а где запрещён, сведено в таблицу в справочнике.

Изменение: updateJSON

updateJSON никогда ничего не вставляет: каждую строку дерева он изменяет по ключу. Поэтому ключ нужен на каждом уровне, а вот обязательные колонки — нет. Переведём проект 2 в статус active, изменим приоритет его задачи 1 и попробуем изменить несуществующую задачу 9 и несуществующий проект 999:

type TaskUpd = "seq" := Int32 :. "priority" := Int32

type ProjectUpd = "id" := Int64 :. "status" := ProjectStatus
  :. "tasksProjectIdFkey" := [TaskUpd]

type UpdOut = Maybe ("id" := Int64 :. "status" := ProjectStatus
  :. "tasksProjectIdFkey" := [Maybe ("seq" := Int32 :. "priority" := Int32)])

(res, sqlUpd) <- updateJSON (MyAnn "projects") conn $ rowsOf @ProjectUpd
  [ 2 :. Project_status_active :. [ 1 :. 5, 9 :. 50 ]
  , 999 :. Project_status_active :. [] ]
mapM_ print (res :: [UpdOut])

Just ("id" =: 2 :. ("status" =: Project_status_active :. "tasksProjectIdFkey" =: [Just ("seq" =: 1 :. "priority" =: 5),Nothing]))
Nothing
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; 
  status0 tut.project_status; 
  data_1 jsonb;
  found_1 boolean;
  row_1 record;
  arr_1 jsonb[];
  seq1 int4; 
  priority1 int4; 
begin
  arr_0:= '{}';
  for row_0 in select * from jsonb_array_elements(data_0)
  loop
    update tut.projects
      set id = (row_0.value->>'id')::int8, status = (row_0.value->>'status')::tut.project_status
      where id IS NOT DISTINCT FROM (row_0.value->>'id')::int8
      returning id, status into id0, status0;
      found_0 := found;
    if found_0 then
      data_1 := row_0.value->'tasksProjectIdFkey';
      arr_1:= '{}';
      for row_1 in select * from jsonb_array_elements(data_1)
      loop
        update tut.tasks
          set seq = (row_1.value->>'seq')::int4, priority = (row_1.value->>'priority')::int4
          where project_id = id0 and seq IS NOT DISTINCT FROM (row_1.value->>'seq')::int4
          returning seq, priority into seq1, priority1;
              found_1 := found;
        if found_1 then
        end if;
        if found_1 then
          arr_1 := array_append(arr_1, jsonb_build_object('seq', seq1, 'priority', priority1));
        else
          arr_1 := array_append(arr_1, 'null'::jsonb);
        end if;
      end loop;
    end if;
    if found_0 then
      arr_0 := array_append(arr_0, jsonb_build_object('id', id0, 'status', status0, 'tasksProjectIdFkey', to_jsonb(arr_1)));
    else
      arr_0 := array_append(arr_0, 'null'::jsonb);
    end if;
  end loop;
  return to_jsonb(arr_0);
end; 
$$ language plpgsql;

Результат показывает судьбу каждой строки. Проект 2 найден, и в его списке задач первая изменилась, а вместо задачи 9 стоит Nothing. Проекта 999 нет, и вместо него тоже Nothing. Списки результата идут в том же порядке, что и входные данные, так что каждый Nothing легко сопоставить с тем, что не нашлось. Не найтись может любая строка, поэтому в типе результата updateJSON в Maybe должны быть обёрнуты и корень, и элементы дочерних списков.

Если в какой-то строке дерева ключа нет, изменить её не по чему, и программа не скомпилируется. Уберём, например, seq из типа задачи:

• We can't upsert data because for table NameNS "tut" "tasks"
  not all mandatory fields are present and no full primary or
  unique key is covered by record fields.
  Missing mandatory fields: ["seq", "title"]
  Possible keys: '[["project_id", "seq"]]

Сообщение общее для upsertJSON и updateJSON, поэтому в нём говорится об upsert и об обязательных колонках. Для updateJSON важна последняя строка: список ключей, один из которых должен целиком быть в типе. Колонка project_id придёт от проекта, так что для задач не хватает seq.

Транзакции

Как мы уже говорили, каждая из этих операций сама открывает транзакцию, если она ещё не открыта, и при ошибке ничего частично записанного не остаётся. Если же транзакция уже открыта, операция выполняется внутри неё и подчиняется ей: при откате внешней транзакции откатится и всё, что записала операция. Это позволяет объединять несколько операций в одну транзакцию. В следующем примере мы дважды вставляем проект с одним и тем же id внутри withTransaction из postgresql-simple:

let
  docs = rowsOf @("id" := Int64 :. ProjectIn)
    [ 100 :. 1 :. "Tx demo" :. Project_status_draft :. pgArr' [] :. [] ]
res <- try @SqlError $ withTransaction conn do
  _ <- insertJSON_ (MyAnn "projects") conn docs
  insertJSON_ (MyAnn "projects") conn docs  -- the same id: an error
putStrLn $ either (("error: " <>) . show . sqlErrorMsg) (const "ok") res
cnt <- countProjects conn "Tx demo"
putStrLn $ "projects \"Tx demo\": " <> show cnt

error: "duplicate key value violates unique constraint \"projects_pkey\""
projects "Tx demo": 0

Вторая вставка нарушила уникальность, и withTransaction откатил всю транзакцию, включая первую, успешную вставку. Проекта «Tx demo» в базе нет. Для подсчёта мы использовали агрегат из главы «Select»:

-- | How many projects have this title.
countProjects :: Connection -> Text -> IO Int64
countProjects conn title = do
  (rows, _) <- selectSch (MyAnn "projects") conn $ qRoot $ qWhere $ "title" =? title
  pure $ sum $ unAggr <$> plains @("cnt" := Aggr ACount Int64) rows

Резюме

  • insertJSON вставляет дерево одной операцией, а колонки, которые приходят из связи с родителем, указывать не нужно.
  • Ветки результата должны быть среди веток входных данных, колонки — любые.
  • upsertJSON по полям типа выбирает для каждого уровня update, insert … on conflict или insert.
  • updateJSON никогда не вставляет и требует ключ на каждом уровне.
  • Ненайденные строки дают Nothing на своём месте в результате.
  • Операции выполняются в транзакции и подчиняются внешней транзакции.

Что дальше

Мы прошли все основные операции pg-schema: чтение и изменение данных как в одной таблице, так и в нескольких связанных. Сообщения компилятора, которые встречались по дороге, собраны в главе «Ошибки компиляции», а правила, которые мы видели на примерах, — в справочнике.