obsidian/hardcore web development/11. SQL/Boolean и всякое.md

Создание таблицы из видео:

drop table if exists rroom_user, email cascade;

create table rroom_user(
    user_id bigint generated always as identity primary key,
    birth_date DATE
);

create table email (
    email varchar(320),
    user_id bigint references rroom_user(user_id),
    is_verified boolean not null default false,
    primary key (email, user_id)
);

Обратите внимание, как здесь создан первичный ключ — он составной, из двух колонок email и user_id. Так можно делать. В данном случае это оправдано, потому что мы хотим, чтобы была уникальная связка email и user_id, чтобы эта связка не дублировалась. В такой схеме у нас один мейл может быть у разных пользователей, но при этом только у одного он может быть верифицирован:

create unique index unique_verified_email_per_user
on email (email)
where is_verified = true;

Об индексах мы поговори позже, но сейчас можно проверить, что оно работает. Создадим пару пользователей:

insert into rroom_user(birth_date) values ('1988-07-29') returning user_id; -- 1
insert into rroom_user(birth_date) values ('1996-01-01') returning user_id; -- 2

Обратите внимание на конструкцию returning. С её помощью можно одним запросом как вставить данные, так и вернуть какие-то данные этой вставленной записи, в данном случае поле user_id генерируется последовательностью, поэтому очень удобно его значение вернуть в одном запросе с insert.

Теперь создадим два одинаковых мейла у каждого:

insert into email (email, user_id) values ('sterx@rl6.ru', 1);
insert into email (email, user_id) values ('sterx@rl6.ru', 2);

select * from email;

Видим, что у обоих записей is_verified=false. Это окей. У нас может быть мейл, принадлежащий сначала одному пользователю, а потом другому — какой-то рабочий мейл, например. То есть в нашей схеме нет жесткой связки один email один аккаунт. Это особенно актуально для телефонов, когда один и тот же номер телефона может менять разных владельцев и в разные периоды времени принадлежать разным пользователям, что совершенно нормально. Для мейлов возможно это в меньшей степени актуально, но пусть будет.

Так вот, попробуем верифицировать email сразу у обоих пользователей:

update email set is_verified=true where email='sterx@rl6.ru'; -- ошибка
select * from email;

update email set is_verified=true where email='sterx@rl6.ru' and user_id=1;
select * from email;

update email set is_verified=true where email='sterx@rl6.ru' and user_id=2; -- ошибка
select * from email;

Видим, что может быть только один пользователь с верифицированным мейлом! Это благодаря нашему индексу. Повторюсь, об индексах подробнее поговорим дальше.

Кстати, выше я упоминал, но подчеркну ещё раз подход с колонкой is_deleted. Зачастую мы не удаляем данные их БД, а просто помечаем их удалёнными, проставляя в колонке is_deleted значение TRUE. И тогда при каждой выборке активных записей надо будет смотреть, что поле is_deleted имеет значение FALSE. Тогда у нас все исторические данные будут доступны в таблице и мы сможем при необходимости их использовать в будущем.

create temp table some_entity(
    entity_id bigint generated always as identity primary key,
    value text not null,
    is_deleted boolean not null default false
);

insert into some_entity (value) values ('some value');
insert into some_entity (value) values ('some another value');

select * from some_entity where is_deleted=false;

update some_entity set is_deleted=true where entity_id=2;
select * from some_entity where is_deleted=false;
select * from some_entity;

Другим подходом может быть перенос удалённых значений в отдельную таблицу, например, так:

drop table if exists some_entity;
drop table if exists some_entity_deleted;

create temp table some_entity(
    entity_id bigint generated always as identity primary key,
    value text not null
);

create table some_entity_deleted (like some_entity including defaults including constraints including indexes);

insert into some_entity (value) values ('some value');
insert into some_entity (value) values ('some another value');

select * from some_entity;

begin;
insert into some_entity_deleted select * from some_entity where entity_id=2;
delete from some_entity where entity_id=2;
commit;

Здесь мы создаём таблицу some_entity такую же как и раньше, но без поля is_deleted, так как в этом случае вся таблица будет хранить в себе только актуальные записи. Затем мы создаём таблицу some_entity_deleted, которая копирует структуру, индексы, ограничения и значения колонок по умолчанию такие же как в some_entity. И затем мы вставляем в some_entity две строки, одну из которых я затем удалю.

Операция удаления должна быть атомарной — то есть мы в 1 операции должны вставить запись в таблицу удаленных записей и удалить запись из основной таблицы. Иначе если операция будет неатомарной, то вставка записи может пройти, а удаление не пройти, например. Поэтому мы оборачиваем это в транзакцию — о транзакциях мы поговорим позже.

Ну и обращаю ваше внимание, что insert into таблица select работает. То есть можно вставлять записи в таблицу из какой-то выборки.

Аналогично можно сделать и без явного определения транзакции с помощью CTE, Common Table Expression, который мы изучим дальше:

insert into some_entity (value) values ('some another value');

with deleted_rows as (
    delete from some_entity
    where entity_id=3
    returning *
)
insert into some_entity_deleted select * from deleted_rows;

select * from some_entity;
select * from some_entity_deleted;

Преимуществом такого подхода без колонки is_deleted может быть то, что не надо помнить, что в каждый запрос надо вставлять это условие where is_deleted=false. Таблицы для хранения исторических данных можно при желании вынести в отдельную схему, чтобы они не были видны в основной рабочей схеме.

Отдельно здесь хочу отметить 2 момента, которые здесь промелькнули в этом уроке. Во-первых, как создать таблицу такую же, как уже имеющаяся таблица:

create table some_entity_deleted (like some_entity including defaults including constraints including indexes);

Можно также создать таблицу на основе результатов запроса вместе с результатами этого запроса:

create table some_tmp_table as select * from book;
drop table some_tmp_table;

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

И можно вставить в существующую таблицу результаты какого-то запроса:

insert into some_entity_deleted select * from some_entity where entity_id=2;

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