Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 32: обеспечение консистентности данных
Решение 4.1.2.b{292}.
Как и в решении{292} задачи 4.1.2.a{292} здесь нужно будет выполнить те же самые действия — модифицировать таблицу, проинициализировать данные, создать триггеры. И даже код триггеров будет чем-то похож на рассмотренные ранее решения.
Традиционно начинаем с решения для MySQL: модифицируем таблицу и проинициализируем данные.
MySQL Решение 4.1.2.b (модификация таблицы и инициализация данных)
1-- Модификация таблицы:
2ALTER TABLE `genres`
3ADD COLUMN `g_books` INT(11) NOT NULL DEFAULT 0 AFTER `g_name`;
4
5-- Инициализация данных:
6UPDATE `genres`
7JOIN (SELECT `g_id`,
8 |
|
|
|
COUNT(`b_id`) |
AS `g_has_books` |
9 |
|
|
FROM |
`m2m_books_genres` |
|
10 |
|
|
GROUP |
BY `g_id`) AS |
`prepared_data` |
11 |
|
|
USING (`g_id`) |
|
|
12 |
|
SET |
`g_books` = `g_has_books`; |
|
|
Код всех трёх триггеров будет предельно прост: в INSERT-триггере мы увеличиваем счётчик книг у соответствующего жанра, в DELETE-триггере — уменьшаем, в UPDATE-триггере уменьшаем «старому» жанру и увеличиваем «новому» жанру. Никаких дополнительных проверок и ухищрений здесь не требуется.
MySQL Решение 4.1.2.b (триггеры для таблицы m2m_books_genres)
1 DELIMITER $$
2
3-- Реакция на добавление связи между книгами и жанрами:
4CREATE TRIGGER `g_has_books_on_m2m_b_g_ins`
5AFTER INSERT
6ON `m2m_books_genres`
7FOR EACH ROW
8BEGIN
9UPDATE `genres`
10 |
|
SET |
`g_books` = `g_books` + 1 |
11WHERE `g_id` = NEW.`g_id`;
12END;
13$$
14
15-- Реакция на обновление связи между книгами и жанрами:
16CREATE TRIGGER `g_has_books_on_m2m_b_g_upd`
17AFTER UPDATE
18ON `m2m_books_genres`
19FOR EACH ROW
20BEGIN
21UPDATE `genres`
22 |
SET |
`g_books` = `g_books` - 1 |
23WHERE `g_id` = OLD.`g_id`;
24UPDATE `genres`
25 |
SET |
`g_books` = `g_books` + 1 |
26WHERE `g_id` = NEW.`g_id`;
27END;
28$$
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 305/545
Пример 32: обеспечение консистентности данных
MySQL Решение 4.1.2.b (триггеры для таблицы m2m_books_genres) (продолжение)
29-- Реакция на удаление связи между книгами и жанрами:
30CREATE TRIGGER `g_has_books_on_m2m_b_g_del`
31AFTER DELETE
32ON `m2m_books_genres`
33FOR EACH ROW
34BEGIN
35UPDATE `genres`
36 |
|
SET |
`g_books` = `g_books` - 1 |
37WHERE `g_id` = OLD.`g_id`;
38END;
39$$
40
41 DELIMITER ;
Поскольку в MySQL триггеры не активируются каскадными операциями, удаление книги (которое приведёт к удалению всех её связей со всеми жанрами) останется незаметным для триггеров на таблице m2m_books_genres. Потому мы должны создать триггер на таблице books, учитывающий соответствующую ситуацию.
Каждая книга связана с каждым жанром не более одного раза, потому при удалении любой книги нужно на единицу уменьшить счётчик книг у каждого из жанров, с которыми она связана.
MySQL Решение 4.1.2.b (триггер для таблицы books)
1 DELIMITER $$
2
3-- Реакция на удаление книги:
4CREATE TRIGGER `g_has_books_on_books_del`
5BEFORE DELETE
6ON `books`
7FOR EACH ROW
8BEGIN
9UPDATE `genres`
10 |
|
SET |
`g_books` |
= `g_books` - 1 |
|
11 |
|
WHERE |
`g_id` IN |
(SELECT |
`g_id` |
12 |
|
|
|
FROM |
`m2m_books_genres` |
13 |
|
|
|
WHERE |
`b_id` = OLD.`b_id`); |
14END;
15$$
16
17 DELIMITER ;
Проверим корректность полученного решения. Будем модифицировать данные в таблицах m2m_books_genres и books и проверять изменения в таблице genres.
Исходное состояние таблицы genres:
g_id |
g_name |
g_books |
1 |
Поэзия |
2 |
2 |
Программирование |
3 |
3 |
Психология |
1 |
4 |
Наука |
0 |
5 |
Классика |
4 |
6 |
Фантастика |
1 |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 306/545
Пример 32: обеспечение консистентности данных
Добавим две связи к жанру «Наука» (идентификатор жанра равен 4):
MySQL Решение 4.1.2.b (проверка работоспособности)
|
1 |
INSERT INTO `m2m_books_genres` |
|||
|
2 |
|
(`b_id`, |
|
|
|
3 |
|
`g_id`) |
|
|
|
4 |
VALUES |
(1, 4), |
|
|
|
5 |
|
(2, 4) |
|
|
|
|
|
|
|
|
|
g_id |
g_name |
g_books |
|
|
|
1 |
Поэзия |
|
2 |
|
|
2 |
Программирование |
3 |
|
|
|
3 |
Психология |
1 |
|
|
|
4 |
Наука |
|
2 |
|
|
5 |
Классика |
|
4 |
|
|
6 |
Фантастика |
1 |
|
|
Изменим в этих связях значения идентификаторов книг, не меняя значения идентификаторов жанров:
MySQL Решение 4.1.2.b (проверка работоспособности)
|
1 |
UPDATE |
`m2m_books_genres` |
|||
|
2 |
SET |
`b_id` = |
3 |
|
|
|
3 |
WHERE |
`b_id` = |
1 |
|
|
|
4 |
AND |
`g_id` = |
4; |
|
|
|
5 |
|
|
|
|
|
|
6 |
UPDATE |
`m2m_books_genres` |
|||
|
7 |
SET |
`b_id` = |
4 |
|
|
|
8 |
WHERE |
`b_id` = |
2 |
|
|
|
9 |
AND |
`g_id` = |
4; |
|
|
|
|
|
|
|
|
|
|
g_id |
|
g_name |
|
g_books |
|
|
1 |
Поэзия |
|
2 |
|
|
|
2 |
Программирование |
3 |
|
||
|
3 |
Психология |
|
1 |
|
|
|
4 |
Наука |
|
2 |
|
|
|
5 |
Классика |
|
4 |
|
|
|
6 |
Фантастика |
|
1 |
|
|
Изменим в этих связях значения идентификаторов жанров, не меняя значения идентификаторов книг:
|
MySQL |
|
Решение 4.1.2.b (проверка работоспособности) |
||||
|
1 |
UPDATE |
`m2m_books_genres` |
||||
|
2 |
SET |
`g_id` = |
5 |
|
|
|
|
3 |
WHERE |
`b_id` = |
3 |
|
|
|
|
4 |
|
AND |
`g_id` = |
4; |
|
|
|
5 |
|
|
|
|
|
|
|
6 |
UPDATE |
`m2m_books_genres` |
||||
|
7 |
SET |
`g_id` = |
5 |
|
|
|
|
8 |
WHERE |
`b_id` = |
4 |
|
|
|
|
9 |
|
AND |
`g_id` = |
4; |
|
|
|
|
|
|
|
|
|
|
|
g_id |
|
|
g_name |
|
g_books |
|
|
1 |
Поэзия |
|
2 |
|
||
|
2 |
Программирование |
3 |
|
|||
|
3 |
Психология |
|
1 |
|
||
|
4 |
Наука |
|
0 |
|
||
|
5 |
Классика |
|
6 |
|
||
|
6 |
Фантастика |
|
1 |
|
||
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 307/545
Пример 32: обеспечение консистентности данных
Изменим в этих связях значения идентификаторов жанров, и идентификаторов книг одновременно:
|
MySQL |
|
Решение 4.1.2.b (проверка работоспособности) |
|||
|
1 |
|
UPDATE |
`m2m_books_genres` |
||
|
2 |
|
SET |
|
`b_id` = 1, |
|
|
3 |
|
|
|
|
`g_id` = 4 |
|
4 |
|
WHERE |
`b_id` = 3 |
||
|
5 |
|
|
|
AND |
`g_id` = 5; |
|
6 |
|
|
|
|
|
|
7 |
|
UPDATE |
`m2m_books_genres` |
||
|
8 |
|
SET |
|
`b_id` = 2, |
|
|
|
|
|
|
|
|
9`g_id` = 4
10WHERE `b_id` = 4
11AND `g_id` = 5;
g_id |
g_name |
g_books |
1 |
Поэзия |
2 |
2 |
Программирование |
3 |
3 |
Психология |
1 |
4 |
Наука |
2 |
5 |
Классика |
4 |
6 |
Фантастика |
1 |
Удалим эти созданные для проверки работоспособности решения связи:
MySQL Решение 4.1.2.b (проверка работоспособности)
|
1 |
DELETE |
FROM `m2m_books_genres` |
||
|
2 |
WHERE |
`b_id` = 1 |
|
|
|
3 |
AND |
`g_id` = 4; |
|
|
|
4 |
|
|
|
|
|
5 |
DELETE |
FROM `m2m_books_genres` |
||
|
6 |
WHERE |
`b_id` = 2 |
|
|
|
7 |
AND |
`g_id` = 4; |
|
|
|
|
|
|
|
|
|
g_id |
|
g_name |
g_books |
|
|
1 |
Поэзия |
2 |
|
|
|
2 |
Программирование |
3 |
|
|
|
3 |
Психология |
1 |
|
|
|
4 |
Наука |
0 |
|
|
|
5 |
Классика |
4 |
|
|
|
6 |
Фантастика |
1 |
|
|
Удалим книги с идентификаторами 1 и 2 (обе эти книги одновременно относятся к жанрам «Поэзия» и «Классика»):
MySQL Решение 4.1.2.b (проверка работоспособности)
1DELETE FROM `books`
2WHERE `b_id` IN (1, 2)
g_id |
g_name |
g_books |
1 |
Поэзия |
0 |
2 |
Программирование |
3 |
3 |
Психология |
1 |
4 |
Наука |
0 |
5 |
Классика |
2 |
6 |
Фантастика |
1 |
Итак, решение для MySQL завершено и проверено.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 308/545
Пример 32: обеспечение консистентности данных
Переходим к решению для MS SQL Server. Модифицируем таблицу и проинициализируем данные.
MS SQL Решение 4.1.2.b (модификация таблицы и инициализация данных)
1-- Модификация таблицы:
2ALTER TABLE [genres]
3ADD [g_books] INT NOT NULL DEFAULT 0;
4
5-- Инициализация данных:
6UPDATE [genres]
7 |
|
SET |
[g_books] = [g_has_books] |
|
8 |
|
FROM |
[genres] |
|
9 |
|
|
JOIN (SELECT |
[g_id], |
10 |
|
|
|
COUNT([b_id]) AS [g_has_books] |
11 |
|
|
FROM |
[m2m_books_genres] |
12 |
|
|
GROUP |
BY [g_id]) AS [prepared_data] |
13ON [genres].[g_id] = [prepared_data].[g_id];
ВMS SQL Server триггеры активируются каскадными операциями, потому здесь будет достаточно создать триггеры только на таблице m2m_books_genres.
INSERT- и DELETE-триггеры достаточно просты: каждый из них подсчитывает
количество книг, добавленных к жанру или убранных у жанра, и изменяет счётчик книг у соответствующего жанра на полученное значение.
MS SQL Решение 4.1.2.b (триггеры для таблицы m2m_books_genres)
1-- Реакция на добавление связи между книгами и жанрами:
2CREATE TRIGGER [g_has_books_on_m2m_b_g_ins]
3ON [m2m_books_genres]
4AFTER INSERT
5AS
6UPDATE [genres]
7 |
|
SET |
[g_books] = [g_books] + |
[g_new_books] |
|
8 |
|
FROM |
[genres] |
|
|
9 |
|
|
JOIN (SELECT |
[g_id], |
|
10 |
|
|
|
COUNT([b_id]) AS [g_new_books] |
|
11 |
|
|
FROM |
[inserted] |
|
12 |
|
|
GROUP |
BY [g_id]) |
AS [prepared_data] |
13ON [genres].[g_id] = [prepared_data].[g_id];
14GO
15
16-- Реакция на обновление связи между книгами и жанрами:
17CREATE TRIGGER [g_has_books_on_m2m_b_g_upd]
18ON [m2m_books_genres]
19AFTER UPDATE
20AS
21UPDATE [genres]
22 |
|
SET |
[g_books] = [g_books] + [delta] |
||
23 |
|
FROM |
[genres] |
|
|
24 |
|
|
JOIN (SELECT [g_id], |
|
|
25 |
|
|
|
SUM([delta]) AS [delta] |
|
26 |
|
|
FROM |
(SELECT |
[g_id], |
27 |
|
|
|
|
-COUNT([b_id]) AS [delta] |
28 |
|
|
|
FROM |
[deleted] |
29 |
|
|
|
GROUP |
BY [g_id] |
30 |
|
|
|
UNION |
|
31 |
|
|
|
SELECT |
[g_id], |
32 |
|
|
|
|
COUNT([b_id]) AS [delta] |
33 |
|
|
|
FROM |
[inserted] |
34 |
|
|
|
GROUP |
BY [g_id]) AS [raw_deltas] |
35 |
|
|
GROUP |
BY [g_id]) AS [ready_delta] |
|
36ON [genres].[g_id] = [ready_delta].[g_id];
37GO
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 309/545