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

Изменение данных в одной таблице

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

Как и в предыдущей главе, мы используем аннотацию MyAnn с ренеймером MyRenamer, поэтому поля можно называть в стиле camelCase, а данные описываем с помощью PgTag. Все примеры этой главы лежат в tutorial/app/app/Tut/FlatDml.hs и выполняются после примеров главы «Рабочая модель pg-schema», на тех же данных.

Для работы с одной таблицей в pg-schema есть пять групп операций: insertSch вставляет строки, updateByCond изменяет строки, подходящие под условие, deleteByCond удаляет их, а updateByKey и upsertByKey изменяют или вставляют строки, найденные по первичному или уникальному ключу. У всех операций, кроме удаления, есть вариант с подчёркиванием в конце имени (insertSch_, updateByCond_ и так далее), который ничего не возвращает из базы. Как и при чтении, SQL каждой операции определяют типы: колонки, которые мы записываем, берутся из типа входных данных, а колонки в returning — из типа результата.

Вставка

С insertSch и insertSch_ мы уже встречались в предыдущей главе. Вставим с их помощью два проекта и попросим базу вернуть их идентификаторы. Чтобы не повторять "поле" =: значение в каждой строке, воспользуемся функцией named: она получает список имён полей один раз и приписывает их значениям каждой строки по порядку.

(res, tIns) <- insertSch (MyAnn "projects") conn $
  named ["ownerId", "title", "status", "tags"]
    [ (1 :: Int64) :. ("pg-schema" :: Text) :. Project_status_active :. pgArr' ["db" :: Text, "haskell"]
    , 1 :. "tutorial" :. Project_status_draft :. pgArr' ["learning"] ]
putStrLn $ "insert text: " <> T.unpack tIns
putStrLn $ "inserted ids: " <> show (plains @("id" := Int64) res)

insert text: insert into tut.projects(owner_id,title,status,tags) values (?,?,?,?) returning id
inserted ids: [1,2]
SQL, отформатированный для чтения 
insert into tut.projects(owner_id,title,status,tags)
values (?,?,?,?)
returning id;

named возвращает ровно те же строки, что получились бы с (=:), просто их короче записывать. Значения при этом берутся как есть: конструктор Project_status_active или Nothing не нужно оборачивать. Если число имён не совпадёт с числом значений в строке, программа не скомпилируется.

С результатом мы поступили наоборот: plains @("id" := Int64) убирает имена полей и оставляет только значения, поэтому в выводе — просто список идентификаторов. Тип, переданный plains, одновременно задаёт и тип результата insertSch, а значит, и колонки в returning. Так мы будем поступать и в остальных примерах этой главы.

Колонки id и created_at во входных данных не указаны, их заполняют значения по умолчанию. Зато все обязательные колонки, то есть объявленные как NOT NULL и не имеющие значения по умолчанию, во входных данных быть обязаны, и это проверяется при компиляции. Если, например, забыть title, программа не соберётся:

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

Та же ошибка появится и в том случае, если в таблицу добавят новую обязательную колонку, а мы перегенерируем Sch.hs: компилятор покажет все места, где вставляются строки в эту таблицу.

🔍 Колонка tags — это массив text[]. Для массивов в pg-schema есть свой тип PgArr. В отличие от PGArray из postgresql-simple, его инстанс ToField знает тип элементов в базе, поэтому вставлять можно массивы любых типов. Элементы PgArr имеют тип Maybe, как и элементы массивов в PostgreSQL, а функция pgArr' строит массив из значений без Nothing.

Изменение по условию

updateByCond записывает одни и те же значения во все строки, подходящие под условие. Переведём второй проект в архив и попросим вернуть изменённые строки. Тип перечисления project_status нам понадобится не раз, поэтому заведём для него синоним:

type ProjectStatus = PGEnum Sch ("tut" ->> "project_status")

res <- updateByCond (MyAnn "projects") conn
  ("status" =: Project_status_archived)
  ("id" =? (2 :: Int64))
putStrLn $ "updated: " <> show (plains @("id" := Int64 :. "title" := Text :. "status" := ProjectStatus) res)

updated: [2 :. ("tutorial" :. Project_status_archived)]

Первый аргумент после соединения — новые значения, второй — условие. Условия здесь те же, что и в запросах на чтение, и проверяются так же: подробно они разобраны в главе «Select». Если нужно только число изменённых строк, подойдёт updateByCond_.

Сама updateByCond возвращает только строки из returning. Посмотреть на её SQL можно с помощью updateText, которому нужны те же типы и то же условие:

let
  (tUpd :: Text, updParams) = updateText (MyAnn "projects")
    @("status" := ProjectStatus)
    @("id" := Int64 :. "title" := Text :. "status" := ProjectStatus)
    ("id" =? (2 :: Int64))
putStrLn $ "update text: " <> T.unpack tUpd
putStrLn $ "update params: " <> show updParams

update text: update tut.projects t0 set status = ? where t0.id = ? returning id,title,status
update params: [SomeToField 2]
SQL, отформатированный для чтения 
update tut.projects t0
set status = ?
where t0.id = ?
returning id,title,status;

В списке параметров здесь только значение из условия. Новые значения колонок updateText не получает, а updateByCond подставляет их перед параметрами условия, на места первых знаков ?.

Имена колонок в новых значениях проверяются так же, как и везде. Опечатка "status1" вместо "status" даст знакомую ошибку:

• In schema Sch
  for table "tut" ->> "projects"
  name "status1" is not defined.

  Valid values are:
    Fields: id, owner_id, title, status, tags, created_at.
    Foreign key constraints: projects_owner_id_fkey, project_log_project_id_fkey, tasks_project_id_fkey.

  Your source or target type or renamer is probably invalid.

Удаление

Удаление устроено так же, только новых значений нет, а возвращает deleteByCond число удалённых строк вместе с текстом запроса и его параметрами:

(cnt5, tDel5) <- deleteByCond (MyAnn "projects") conn $ "id" =? (2 :: Int64)
putStrLn $ "delete text: " <> T.unpack (fst tDel5)
putStrLn $ "delete params: " <> show (snd tDel5)
putStrLn $ "deleted: " <> show cnt5 <> " rows"

delete text: delete from tut.projects t0  where t0.id = ?
delete params: [SomeToField 2]
deleted: 1 rows
SQL, отформатированный для чтения 
delete from tut.projects t0
where t0.id = ?;

Изменение по ключу

Часто нужно изменить не все строки, подходящие под условие, а несколько конкретных строк, причём каждую по-своему. Для этого есть updateByKey. Он принимает список записей, в каждой из которых есть ключ строки и новые значения, и для каждой записи выполняет update по этому ключу. Ключом может быть первичный ключ или уникальный ключ таблицы, и какой из них использовать, pg-schema определяет по полям записи. В примере ниже это первичный ключ id:

(res :: [Maybe ("id" := Int64 :. "name" := Text)], tUpd) <-
  updateByKey (MyAnn "users") conn $ named ["id", "name"]
    [ (2 :: Int64) :. ("Quentin T." :: Text)
    , 999 :. "Nobody" ]
putStrLn $ "update text: " <> T.unpack tUpd
putStrLn $ "updated: " <> show res

update text: update tut.users set name = ? where id IS NOT DISTINCT FROM ? returning id,name
updated: [Just ("id" =: 2 :. "name" =: "Quentin T."),Nothing]
SQL, отформатированный для чтения 
update tut.users
set name = ?
where id IS NOT DISTINCT FROM ?
returning id,name;

Строки с id = 999 в таблице нет, и updateByKey ничего не вставляет. Поэтому результат имеет тип [Maybe r]: для каждой входной записи — либо изменённая строка, либо Nothing, если строки с таким ключом не нашлось. Сам тип r при этом остаётся обычным, без Maybe. Важно, что результаты гарантированно идут в том же порядке, что и входные записи, и их ровно столько же. Поэтому каждый Nothing легко сопоставить с записью, для которой строка не нашлась: например, с помощью zip входного списка и результата. Обратите внимание и на IS NOT DISTINCT FROM в условии: так ключ совпадает и тогда, когда в нём есть NULL, что важно для уникальных ключей из nullable-колонок.

Если в записи нет ни одного полного ключа, изменить строку не по чему, и программа не скомпилируется. Компилятор перечислит ключи, которые можно использовать:

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

Вставка или изменение по ключу

upsertByKey вставляет строку, а если строка с таким же ключом уже есть, изменяет её. В SQL это insert … on conflict (ключ) do update. Поскольку строка может оказаться новой, в записи должны быть все обязательные колонки, как для insertSch, и, кроме того, полный ключ, по которому определяется конфликт. Обновим пользователя с id = 3 и вставим нового с id = 100:

(res, tUps) <- upsertByKey (MyAnn "users") conn $ named ["id", "name"]
  [ (3 :: Int64) :. ("Jason B." :: Text)
  , 100 :. "Margo" ]
putStrLn $ "upsert text: " <> T.unpack tUps
putStrLn $ "upserted: " <> show (plains @("id" := Int64 :. "name" := Text) res)

upsert text: insert into tut.users(id, name) values (?, ?) on conflict (id) do update set name = EXCLUDED.name returning id,name
upserted: [3 :. "Jason B.",100 :. "Margo"]
SQL, отформатированный для чтения 
insert into tut.users(id, name)
values (?, ?)
on conflict (id) do update set name = EXCLUDED.name
returning id,name;

Ключом не обязательно должен быть первичный ключ. У таблицы users есть уникальный ключ по email, и если в записи нет id, но есть email, конфликт определяется по нему:

(res, tUps) <- upsertByKey (MyAnn "users") conn $ named ["name", "email"]
  [ ("Quentin Tarantino" :: Text) :. Just ("quentin@example.com" :: Text)
  , "Mia" :. Just "mia@example.com" ]
putStrLn $ "upsert text: " <> T.unpack tUps
putStrLn $ "upserted: " <> show (plains @("id" := Int64 :. "name" := Text :. "email" := Maybe Text) res)

upsert text: insert into tut.users(name, email) values (?, ?) on conflict (email) do update set name = EXCLUDED.name returning id,name,email
upserted: [2 :. ("Quentin Tarantino" :. Just "quentin@example.com"),5 :. ("Mia" :. Just "mia@example.com")]
SQL, отформатированный для чтения 
insert into tut.users(name, email)
values (?, ?)
on conflict (email) do update set name = EXCLUDED.name
returning id,name,email;

Пользователь с адресом quentin@example.com уже был, поэтому изменилось только его имя, а id остался прежним. Новая пользовательница «Mia» получила id = 5, а не 4: при конфликте PostgreSQL успел взять следующее значение последовательности для id до того, как обнаружил конфликт, и это значение пропало. Так ведёт себя любой insert … on conflict, и на работу программы это не влияет.

Запись, в которой есть только ключ, upsertByKey не примет: вставить такую строку нельзя, потому что не хватает обязательных колонок.

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

Для изменения строки по одному лишь ключу используйте updateByKey.

Какую операцию выбрать

Если строки заведомо новые, подойдёт insertSch. Если строки уже есть и их нужно изменить по ключу, причём отсутствие строки — не повод её создавать, нужен updateByKey. Если строки может не быть и тогда её нужно создать, нужен upsertByKey, и в записи должны быть все обязательные колонки. Наконец, updateByCond и deleteByCond работают не с отдельными строками, а со всеми строками, подходящими под условие. Какие типы допустимы в returning у каждой операции, сведено в таблицу в приложении.

Резюме

  • Строки вставляются через insertSch, а named позволяет не повторять имена полей в каждой строке.
  • Тип результата (например, переданный plains) задаёт колонки в returning.
  • updateByCond и deleteByCond работают со всеми строками, подходящими под условие.
  • updateByKey возвращает Nothing для ненайденных ключей, в том же порядке, что и вход.
  • upsertByKey требует все обязательные колонки и полный ключ.

Что дальше

Условия, которые мы использовали в updateByCond и deleteByCond, подробно разобраны в главе «Select», вместе со всеми способами читать данные. Вставке и изменению данных сразу в нескольких связанных таблицах посвящена глава «Изменение связанных данных».