22 01 25
Да вроде всё работает и тесты проходят. И блоки добавляются все что надо для MVP
This commit is contained in:
AidarKC
parent
3f5f94a53f
commit
a2495afa44
@ -0,0 +1,351 @@
|
||||
package shine.db;
|
||||
|
||||
import java.sql.SQLException;
|
||||
import java.sql.Statement;
|
||||
|
||||
/**
|
||||
* DatabaseTriggersInstaller — устанавливает триггеры, которые поддерживают бизнес-логику БД.
|
||||
*
|
||||
* Мы специально сделали триггеры максимально "совместимыми":
|
||||
* - НЕТ динамических сообщений в RAISE(...): только фиксированные строки.
|
||||
* (Некоторые SQLite-сборки / просмотрщики падают на "||" внутри RAISE.)
|
||||
* - НЕТ UPSERT "ON CONFLICT DO UPDATE" — вместо него:
|
||||
* INSERT OR IGNORE + UPDATE
|
||||
* (Старые SQLite не знают UPSERT.)
|
||||
*
|
||||
* =============================================================================
|
||||
* ОПИСАНИЕ ТРИГГЕРОВ
|
||||
* =============================================================================
|
||||
*
|
||||
* [1] trg_blocks_line_integrity_bi (BEFORE INSERT ON blocks)
|
||||
* Контроль целостности "линий" (line_code / prev_line_number / prev_line_hash / this_line_number).
|
||||
*
|
||||
* Зачем это нужно:
|
||||
* - В каналах/ветках/действиях ты хочешь иметь "линейную" последовательность,
|
||||
* где каждый следующий блок явно ссылается на предыдущий блок линии
|
||||
* и подтверждает, что ссылка не подменена.
|
||||
*
|
||||
* Когда срабатывает:
|
||||
* - ТОЛЬКО если при вставке передано ХОТЯ БЫ ОДНО из line-полей.
|
||||
* - Если line-поля не переданы — триггер вообще не работает (это важно).
|
||||
*
|
||||
* Что проверяет:
|
||||
* A) line-поля допускаются только для msg_type:
|
||||
* 0 (TECH), 1 (TEXT), 3 (CONNECTION), 4 (USER_PARAM)
|
||||
* B) Если пришло хоть одно line-поле — обязаны прийти ВСЕ 4 (никаких "частичных")
|
||||
* C) prev-блок линии существует в той же цепочке bch_name
|
||||
* D) prev_hash совпадает с block_hash найденного prev-блока
|
||||
* E) line_code корректный:
|
||||
* - либо первый шаг после root: prev_line_number == line_code
|
||||
* - либо prev уже принадлежит этой линии: p.line_code == NEW.line_code
|
||||
* F) this_line_number:
|
||||
* - первый шаг после root:
|
||||
* TEXT: this_line_number = 0
|
||||
* TECH/CONNECTION/USER_PARAM: this_line_number = 1
|
||||
* - обычный шаг:
|
||||
* TEXT: допускаем same или +1 (чтобы "edit" мог не двигать шаг)
|
||||
* TECH/CONNECTION/USER_PARAM: строго prev.this + 1
|
||||
*
|
||||
* Какие ошибки кидает:
|
||||
* - LINE_ERR_UNSUPPORTED_TYPE_WITH_LINE
|
||||
* - LINE_ERR_PARTIAL_FIELDS
|
||||
* - LINE_ERR_NO_PREV
|
||||
* - LINE_ERR_PREV_HASH_MISMATCH
|
||||
* - LINE_ERR_LINE_CODE_MISMATCH
|
||||
* - LINE_ERR_FIRST_STEP_BAD_THIS
|
||||
* - LINE_ERR_THIS_LINE_BAD_STEP
|
||||
*
|
||||
* [2] trg_blocks_connection_state_ai (AFTER INSERT ON blocks WHEN msg_type=3)
|
||||
* Поддерживает таблицу connections_state как "текущее состояние" отношений:
|
||||
* - FRIEND/CONTACT/FOLLOW -> добавить/обновить состояние
|
||||
* - UNFRIEND/UNCONTACT/UNFOLLOW -> удалить соответствующее "позитивное" состояние
|
||||
*
|
||||
* [3] trg_blocks_message_stats_like_ai (AFTER INSERT ON blocks WHEN msg_type=2 AND sub_type=LIKE)
|
||||
* Поддерживает likes_count в message_stats для цели (to_*).
|
||||
*
|
||||
* [4] trg_blocks_message_stats_reply_ai (AFTER INSERT ON blocks WHEN msg_type=1 AND sub_type=REPLY)
|
||||
* Поддерживает replies_count в message_stats.
|
||||
*
|
||||
* [5] trg_blocks_edit_apply_ai (AFTER INSERT ON blocks WHEN msg_type=1 AND sub_type=EDIT)
|
||||
* Логика edit:
|
||||
* - помечает исходный блок edited_by_block_number = NEW.block_number
|
||||
* - увеличивает edits_count в message_stats
|
||||
*/
|
||||
public final class DatabaseTriggersInstaller {
|
||||
|
||||
private DatabaseTriggersInstaller() {}
|
||||
|
||||
public static void createAllTriggers(Statement st) throws SQLException {
|
||||
// На всякий случай убираем старые "криво названные" триггеры,
|
||||
// если они когда-то попадали в БД.
|
||||
st.executeUpdate("DROP TRIGGER IF EXISTS trg_block_lini_integriti_by;");
|
||||
st.executeUpdate("DROP TRIGGER IF EXISTS trg_blocks_line_integrity_bi;");
|
||||
|
||||
st.executeUpdate("DROP TRIGGER IF EXISTS trg_blocks_connection_state_ai;");
|
||||
st.executeUpdate("DROP TRIGGER IF EXISTS trg_blocks_message_stats_like_ai;");
|
||||
st.executeUpdate("DROP TRIGGER IF EXISTS trg_blocks_message_stats_reply_ai;");
|
||||
st.executeUpdate("DROP TRIGGER IF EXISTS trg_blocks_edit_apply_ai;");
|
||||
|
||||
createLineIntegrityTrigger(st);
|
||||
createConnectionStateTrigger(st);
|
||||
createMessageStatsLikeTrigger(st);
|
||||
createMessageStatsReplyTrigger(st);
|
||||
createEditApplyTrigger(st);
|
||||
}
|
||||
|
||||
private static void createLineIntegrityTrigger(Statement st) throws SQLException {
|
||||
st.executeUpdate("""
|
||||
CREATE TRIGGER IF NOT EXISTS trg_blocks_line_integrity_bi
|
||||
BEFORE INSERT ON blocks
|
||||
WHEN
|
||||
NEW.line_code IS NOT NULL
|
||||
OR NEW.prev_line_number IS NOT NULL
|
||||
OR NEW.prev_line_hash IS NOT NULL
|
||||
OR NEW.this_line_number IS NOT NULL
|
||||
BEGIN
|
||||
SELECT RAISE(ABORT, 'LINE_ERR_UNSUPPORTED_TYPE_WITH_LINE')
|
||||
WHERE NOT (NEW.msg_type IN (0, 1, 3, 4));
|
||||
|
||||
SELECT RAISE(ABORT, 'LINE_ERR_PARTIAL_FIELDS')
|
||||
WHERE NEW.line_code IS NULL
|
||||
OR NEW.prev_line_number IS NULL
|
||||
OR NEW.prev_line_hash IS NULL
|
||||
OR NEW.this_line_number IS NULL;
|
||||
|
||||
SELECT RAISE(ABORT, 'LINE_ERR_NO_PREV')
|
||||
WHERE NOT EXISTS(
|
||||
SELECT 1
|
||||
FROM blocks p
|
||||
WHERE p.bch_name = NEW.bch_name
|
||||
AND p.block_number = NEW.prev_line_number
|
||||
LIMIT 1
|
||||
);
|
||||
|
||||
SELECT RAISE(ABORT, 'LINE_ERR_PREV_HASH_MISMATCH')
|
||||
WHERE NOT EXISTS(
|
||||
SELECT 1
|
||||
FROM blocks p
|
||||
WHERE p.bch_name = NEW.bch_name
|
||||
AND p.block_number = NEW.prev_line_number
|
||||
AND p.block_hash = NEW.prev_line_hash
|
||||
LIMIT 1
|
||||
);
|
||||
|
||||
SELECT RAISE(ABORT, 'LINE_ERR_LINE_CODE_MISMATCH')
|
||||
WHERE NEW.prev_line_number <> NEW.line_code
|
||||
AND NOT EXISTS(
|
||||
SELECT 1
|
||||
FROM blocks p
|
||||
WHERE p.bch_name = NEW.bch_name
|
||||
AND p.block_number = NEW.prev_line_number
|
||||
AND p.line_code = NEW.line_code
|
||||
LIMIT 1
|
||||
);
|
||||
|
||||
SELECT RAISE(ABORT, 'LINE_ERR_FIRST_STEP_BAD_THIS')
|
||||
WHERE NEW.prev_line_number = NEW.line_code
|
||||
AND NEW.this_line_number <> (CASE WHEN NEW.msg_type = 1 THEN 0 ELSE 1 END);
|
||||
|
||||
SELECT RAISE(ABORT, 'LINE_ERR_THIS_LINE_BAD_STEP')
|
||||
WHERE NEW.prev_line_number <> NEW.line_code
|
||||
AND NOT EXISTS(
|
||||
SELECT 1
|
||||
FROM blocks p
|
||||
WHERE p.bch_name = NEW.bch_name
|
||||
AND p.block_number = NEW.prev_line_number
|
||||
AND p.this_line_number IS NOT NULL
|
||||
AND (
|
||||
(NEW.msg_type = 1 AND
|
||||
(NEW.this_line_number = p.this_line_number OR NEW.this_line_number = p.this_line_number + 1)
|
||||
)
|
||||
OR
|
||||
(NEW.msg_type IN (0,3,4) AND NEW.this_line_number = p.this_line_number + 1)
|
||||
)
|
||||
LIMIT 1
|
||||
);
|
||||
END;
|
||||
""");
|
||||
}
|
||||
|
||||
private static void createConnectionStateTrigger(Statement st) throws SQLException {
|
||||
int FRIEND = (int) DatabaseInitializer.CONNECTION_FRIEND;
|
||||
int CONTACT = (int) DatabaseInitializer.CONNECTION_CONTACT;
|
||||
int FOLLOW = (int) DatabaseInitializer.CONNECTION_FOLLOW;
|
||||
|
||||
int UNFRIEND = (int) DatabaseInitializer.CONNECTION_UNFRIEND;
|
||||
int UNCONTACT = (int) DatabaseInitializer.CONNECTION_UNCONTACT;
|
||||
int UNFOLLOW = (int) DatabaseInitializer.CONNECTION_UNFOLLOW;
|
||||
|
||||
st.executeUpdate("""
|
||||
CREATE TRIGGER IF NOT EXISTS trg_blocks_connection_state_ai
|
||||
AFTER INSERT ON blocks
|
||||
WHEN NEW.msg_type = 3
|
||||
BEGIN
|
||||
-- FRIEND/CONTACT/FOLLOW:
|
||||
-- 1) если записи нет — создаём
|
||||
INSERT OR IGNORE INTO connections_state (
|
||||
login, rel_type, to_login, to_bch_name, to_block_number, to_block_hash
|
||||
)
|
||||
SELECT
|
||||
NEW.login,
|
||||
NEW.msg_sub_type,
|
||||
NEW.to_login,
|
||||
NEW.to_bch_name,
|
||||
NEW.to_block_number,
|
||||
NEW.to_block_hash
|
||||
WHERE NEW.msg_sub_type IN (%d, %d, %d)
|
||||
AND NEW.to_login IS NOT NULL
|
||||
AND NEW.to_bch_name IS NOT NULL;
|
||||
|
||||
-- 2) если запись есть — обновляем актуальные to_*
|
||||
UPDATE connections_state
|
||||
SET
|
||||
to_bch_name = NEW.to_bch_name,
|
||||
to_block_number = NEW.to_block_number,
|
||||
to_block_hash = NEW.to_block_hash
|
||||
WHERE login = NEW.login
|
||||
AND rel_type = NEW.msg_sub_type
|
||||
AND to_login = NEW.to_login
|
||||
AND NEW.msg_sub_type IN (%d, %d, %d)
|
||||
AND NEW.to_login IS NOT NULL
|
||||
AND NEW.to_bch_name IS NOT NULL;
|
||||
|
||||
-- UNFRIEND/UNCONTACT/UNFOLLOW:
|
||||
-- удаляем соответствующее "позитивное" состояние
|
||||
DELETE FROM connections_state
|
||||
WHERE login = NEW.login
|
||||
AND to_login = NEW.to_login
|
||||
AND rel_type = CASE NEW.msg_sub_type
|
||||
WHEN %d THEN %d
|
||||
WHEN %d THEN %d
|
||||
WHEN %d THEN %d
|
||||
ELSE rel_type
|
||||
END
|
||||
AND NEW.msg_sub_type IN (%d, %d, %d);
|
||||
END;
|
||||
""".formatted(
|
||||
FRIEND, CONTACT, FOLLOW,
|
||||
FRIEND, CONTACT, FOLLOW,
|
||||
|
||||
UNFRIEND, FRIEND,
|
||||
UNCONTACT, CONTACT,
|
||||
UNFOLLOW, FOLLOW,
|
||||
|
||||
UNFRIEND, UNCONTACT, UNFOLLOW
|
||||
));
|
||||
}
|
||||
|
||||
private static void createMessageStatsLikeTrigger(Statement st) throws SQLException {
|
||||
int LIKE = (int) DatabaseInitializer.REACTION_LIKE;
|
||||
|
||||
st.executeUpdate("""
|
||||
CREATE TRIGGER IF NOT EXISTS trg_blocks_message_stats_like_ai
|
||||
AFTER INSERT ON blocks
|
||||
WHEN NEW.msg_type = 2 AND NEW.msg_sub_type = %d
|
||||
BEGIN
|
||||
-- создаём строку, если её не было
|
||||
INSERT OR IGNORE INTO message_stats (
|
||||
to_login, to_bch_name, to_block_number, to_block_hash,
|
||||
likes_count, replies_count, edits_count
|
||||
)
|
||||
SELECT
|
||||
NEW.to_login, NEW.to_bch_name, NEW.to_block_number, NEW.to_block_hash,
|
||||
0, 0, 0
|
||||
WHERE NEW.to_login IS NOT NULL
|
||||
AND NEW.to_bch_name IS NOT NULL
|
||||
AND NEW.to_block_number IS NOT NULL
|
||||
AND NEW.to_block_hash IS NOT NULL;
|
||||
|
||||
-- +1 like
|
||||
UPDATE message_stats
|
||||
SET likes_count = likes_count + 1
|
||||
WHERE to_login = NEW.to_login
|
||||
AND to_bch_name = NEW.to_bch_name
|
||||
AND to_block_number = NEW.to_block_number
|
||||
AND to_block_hash = NEW.to_block_hash
|
||||
AND NEW.to_login IS NOT NULL
|
||||
AND NEW.to_bch_name IS NOT NULL
|
||||
AND NEW.to_block_number IS NOT NULL
|
||||
AND NEW.to_block_hash IS NOT NULL;
|
||||
END;
|
||||
""".formatted(LIKE));
|
||||
}
|
||||
|
||||
private static void createMessageStatsReplyTrigger(Statement st) throws SQLException {
|
||||
int REPLY = (int) DatabaseInitializer.TEXT_REPLY;
|
||||
|
||||
st.executeUpdate("""
|
||||
CREATE TRIGGER IF NOT EXISTS trg_blocks_message_stats_reply_ai
|
||||
AFTER INSERT ON blocks
|
||||
WHEN NEW.msg_type = 1 AND NEW.msg_sub_type = %d
|
||||
BEGIN
|
||||
INSERT OR IGNORE INTO message_stats (
|
||||
to_login, to_bch_name, to_block_number, to_block_hash,
|
||||
likes_count, replies_count, edits_count
|
||||
)
|
||||
SELECT
|
||||
NEW.to_login, NEW.to_bch_name, NEW.to_block_number, NEW.to_block_hash,
|
||||
0, 0, 0
|
||||
WHERE NEW.to_login IS NOT NULL
|
||||
AND NEW.to_bch_name IS NOT NULL
|
||||
AND NEW.to_block_number IS NOT NULL
|
||||
AND NEW.to_block_hash IS NOT NULL;
|
||||
|
||||
UPDATE message_stats
|
||||
SET replies_count = replies_count + 1
|
||||
WHERE to_login = NEW.to_login
|
||||
AND to_bch_name = NEW.to_bch_name
|
||||
AND to_block_number = NEW.to_block_number
|
||||
AND to_block_hash = NEW.to_block_hash
|
||||
AND NEW.to_login IS NOT NULL
|
||||
AND NEW.to_bch_name IS NOT NULL
|
||||
AND NEW.to_block_number IS NOT NULL
|
||||
AND NEW.to_block_hash IS NOT NULL;
|
||||
END;
|
||||
""".formatted(REPLY));
|
||||
}
|
||||
|
||||
private static void createEditApplyTrigger(Statement st) throws SQLException {
|
||||
int EDIT = (int) DatabaseInitializer.TEXT_EDIT;
|
||||
|
||||
st.executeUpdate("""
|
||||
CREATE TRIGGER IF NOT EXISTS trg_blocks_edit_apply_ai
|
||||
AFTER INSERT ON blocks
|
||||
WHEN NEW.msg_type = 1 AND NEW.msg_sub_type = %d
|
||||
BEGIN
|
||||
-- 1) помечаем исходный блок, что его "перекрыл" этот edit
|
||||
UPDATE blocks
|
||||
SET edited_by_block_number = NEW.block_number
|
||||
WHERE login = NEW.login
|
||||
AND bch_name = NEW.bch_name
|
||||
AND block_number = NEW.to_block_number
|
||||
AND NEW.to_block_number IS NOT NULL;
|
||||
|
||||
-- 2) создаём stats-строку если её не было
|
||||
INSERT OR IGNORE INTO message_stats (
|
||||
to_login, to_bch_name, to_block_number, to_block_hash,
|
||||
likes_count, replies_count, edits_count
|
||||
)
|
||||
SELECT
|
||||
NEW.to_login, NEW.to_bch_name, NEW.to_block_number, NEW.to_block_hash,
|
||||
0, 0, 0
|
||||
WHERE NEW.to_login IS NOT NULL
|
||||
AND NEW.to_bch_name IS NOT NULL
|
||||
AND NEW.to_block_number IS NOT NULL
|
||||
AND NEW.to_block_hash IS NOT NULL;
|
||||
|
||||
-- 3) +1 edit
|
||||
UPDATE message_stats
|
||||
SET edits_count = edits_count + 1
|
||||
WHERE to_login = NEW.to_login
|
||||
AND to_bch_name = NEW.to_bch_name
|
||||
AND to_block_number = NEW.to_block_number
|
||||
AND to_block_hash = NEW.to_block_hash
|
||||
AND NEW.to_login IS NOT NULL
|
||||
AND NEW.to_bch_name IS NOT NULL
|
||||
AND NEW.to_block_number IS NOT NULL
|
||||
AND NEW.to_block_hash IS NOT NULL;
|
||||
END;
|
||||
""".formatted(EDIT));
|
||||
}
|
||||
}
|
||||
Loading…
Reference in New Issue
Block a user