Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 32: обеспечение консистентности данных
Изменим в этих связях значения идентификаторов жанров, и идентификаторов книг одновременно:
|
MySQL I |
Решение 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 |
|
|
|
|
Удалим эти созданные для проверки работоспособности решения связи: |
|||
|
|
Решение 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 (проверка работоспособности)
|
|
1 DELETE FROM 'books' |
|
2 |
WHERE '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 Стр: 325/545
Пример 32: обеспечение консистентности данных
Переходим к решению для MS SQL Server. Модифицируем таблицу и проинициализируем данные.
MS SQL Решение 4.1.2.b (модификация таблицы и инициализация данных)
1-- Модификация таблицы:
2ALTER TABLE [genres]
3ADD [g_books] INT NOT NULL DEFAULT 0;
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
ВMS SQL Server триггеры активируются каскадными операциями, потому здесь будет достаточно создать триггеры только на таблице m2m_books_genres.
INSERT- и DELETE-триггеры достаточно просты: каждый из них подсчитывает
количество книг, добавленных к жанру или убранных у жанра, и изменяет счётчик книг у соответствующего жанра на полученное значение.
MS S |
QL I |
Решение 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] i AS [ready delta] |
|
36ON [genres] [g id] = [ready delta] [g id];
37GO
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 326/545
Пример 32: обеспечение консистентности данных
MS SQL |
|
Решение 4.1.2.b (триггеры для таблицы m2m books genres) (продолжение) |
| |
38 |
-- Реакция на удаление связи между книгами и жанрами: |
|
|
39 |
CREATE TRIGGER [g has books on m2m b g del] |
|
|
40ON [m2m books genres]
41AFTER DELETE
42AS
43UPDATE [genres]
44 |
SET |
[g books] = [g books] - [g old books] |
45FROM [genres]
46JOIN (SELECT [g id],
47 |
|
COUNTl[b id]) AS [g old books] |
|
48 |
FROM |
[deleted] |
|
49 |
GROUP |
BY [g id] |
AS [prepared data] |
50ON [genres] [g id] = [prepared data] [g id];
51GO
Логика UPDATE-триггера чуть более сложная. Чтобы не выполнять два отдельных обновления таблицы genres, мы сначала в строках 26-34 запроса получаем «сводную таблицу» по удалённым и добавленным связям между книгами и жанрами. Эта таблица в некоторой гипотетической ситуации может выглядеть так:
g_id |
delta |
3 |
4 |
5 |
1 |
1 |
-3 |
3 |
-6 |
6 |
2 |
Со знаком минус представлено количество удалённых связей между книгами и жанрами, со знаком полюс — количество добавленных связей. Обратите внимание на жанр с идентификатором 3, у которого за одну операцию обновления часть связей было удалено, часть добавлено. В строке 25 запроса эти отрицательные и положительные значения суммируются, формируя таким образом итоговую дельту количества связей между жанрами и книгами.
g_id |
delta |
3 |
-2 |
5 |
1 |
1 |
-3 |
6 |
2 |
В строке 22 запроса эти данные используются для изменения значения счётчика связей между жанрами и книгами.
Проверить работоспособность полученного решения можно с помощью следующих запросов (которые подробно рассмотрены в решении для MySQL).
MS SQL Решение 4.1.2.b (проверка работоспособности) і
1-- Добавление двух связей к жанру «Наука» (идентификатор жанра равен 4):
2INSERT INTO [m2m_books_genres]
3 |
|
([b_id], |
|
4 |
|
[g_id]) |
|
|
(1, 4), |
||
5 |
VALUES |
||
(2, 4); |
|||
6 |
|
||
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 327/545
Пример 32: обеспечение консистентности данных
MS SQL Решение 4.1.2.b (проверка работоспособности) (продолжение)
7-- Изменение в добавленных связях значения идентификаторов книг,
8-- без изменения значения идентификаторов жанров:
9UPDATE [m2m_books_genres]
10 SET |
[b_id] = 3 |
11WHERE [b_id] = 1
12AND [g_id] = 4;
14UPDATE [m2m_books_genres]
15 SET |
[b_id] = 4 |
16WHERE [b_id] = 2
17AND [g_id] = 4;
19-- Изменение в добавленных связях значения идентификаторов жанров,
20-- без изменения значения идентификаторов книг:
21UPDATE [m2m_books_genres]
22 SET |
[g_id] = 5 |
23WHERE [b_id] = 3
24AND [g_id] = 4;
26UPDATE [m2m_books_genres]
27 SET |
[g_id] = 5 |
28WHERE [b_id] = 4
29AND [g_id] = 4;
31-- Изменение в добавленных связях значения идентификаторов жанров,
32-- и идентификаторов книг одновременно:
33UPDATE [m2m_books_genres]
34 SET |
[b_id] = 1, |
35[g_id] = 4
36WHERE [b_id] = 3
37AND [g_id] = 5;
39UPDATE [m2m_books_genres]
40 SET |
[b_id] = 2, |
41[g_id] = 4
42WHERE [b_id] = 4
43AND [g_id] = 5;
45-- Удаление ранее созданных связей:
46DELETE FROM [m2m_books_genres]
47 |
WHERE [b_id] = |
1 |
48 |
AND [g_id] = |
4; |
49 |
|
|
50 |
DELETE FROM [m2m_books_genres] |
|
51WHERE [b_id] = 2
52AND [g_id] = 4;
54-- Удаление книг с идентификаторами 1 и 2 (обе эти книги одновременно
55-- относятся к жанрам «Поэзия» и «Классика»):
56DELETE FROM [books]
57WHERE [bid] IN (1, 2);
Итак, решение для MySQL завершено и проверено.
Переходим к решению для Oracle. Т.к. данная СУБД не поддерживает псевдотаблицы inserted и deleted, мы будем опираться на логику решения для MySQL. Все соответствующие подробности этого решения уже описаны выше, потому здесь будет представлен только SQL-код.
Также отметим, что поскольку в Oracle триггеры активируются каскадными операциями, в данном решении (в отличие от решения для MySQL) не потребуется создавать DELETE-триггер на таблице books.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 328/545
Пример 32: обеспечение консистентности данных |
|
|||||
Oracl |
і |
Решение 4.1.2.b (модификация таблицы и инициализация данных) |
| |
|||
e |
||||||
|
|
|
|
|
||
1 |
-- Модификация таблицы: |
|
|
|||
2 |
ALTER TABLE "genres" |
|
|
|||
3 |
ADD |
"g_books" NUMBER(10 |
DEFAULT 0 NOT NULL); |
|
||
4 |
|
|
|
|
|
|
5 |
-- Инициализация данных: |
|
|
|||
6 |
UPDATE "genres" "outer" |
|
|
|||
7 |
SET |
"g books" = |
|
|
||
8 |
|
NVL((SELECT COUNT "b id") AS "g has books" |
|
|||
9 |
|
FROM |
"m2m books genres" |
|
||
10 |
|
WHERE "outer" "g id" = "g id" |
|
|||
11 |
|
GROUP |
BY "g__id"),0); |
|
||
Oracle I Решение 4.1.2.b (триггеры для таблицы m2m_books_genres)
1-- Реакция на добавление связи между книгами и жанрами:
2CREATE TRIGGER "g_has_bks_on_m2m_b_g_ins" AFTER INSERT
3ON "m2m_books_genres"
4FOR EACH ROW
5BEGIN
6UPDATE "genres"
7 |
SET |
"g_books" = "g_books" + 1 |
8 |
WHERE |
"g_id" = new "g_id" |
9 |
END; |
|
10 |
|
|
11 |
-- Реакция на обновление связи между книгами и жанрами: |
|
12 |
|
|
13CREATE TRIGGER "g_has_bks_on_m2m_b_g_upd"
14AFTER UPDATE
15ON "m2m_books_genres"
16FOR EACH ROW
17BEGIN
18UPDATE "genres"
19 |
SET |
"g_books" = "g_books" + 1 |
20WHERE "g_id" = new "g_id"
21UPDATE "genres"
22 |
SET |
"g_books" = "g_books" - 1 |
23WHERE "g_id" = :old "g_id"
24END;
25
26-- Реакция на удаление связи между книгами и жанрами:
27CREATE TRIGGER "g_has_bks_on_m2m_b_g_del" AFTER DELETE
28ON "m2m_books_genres"
29FOR EACH ROW
30BEGIN
31UPDATE "genres"
32 |
SET |
"g_books" = "g_books" - 1 |
33WHERE "g_id" = :old "g_id"
34END;
35
Проверить работоспособность полученного решения можно с помощью следующих запросов (которые подробно рассмотрены в решении для MySQL).
Oracle |
Решение 4.1.2.b (проверка работоспособности) |
||
|
|
|
|
1 |
-- Добавление двух связей к жанру «Наука» (идентификатор жанра равен 4): |
||
2 |
INSERT INTO "m2m_books_genres" |
||
3 |
|
("b id", "g id") |
|
4 |
VALUES |
(1, 4); |
|
5 |
|
|
|
6 |
INSERT INTO "m2m_books_genres" |
||
7 |
|
("b id", "g id") |
|
8 |
VALUES |
(2, 4); |
|
|
|
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 329/545