Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 31: обновление кэширующих таблиц и полей
В корректности работы полученного решения вы можете убедиться, выполнив следующие запросы (они выполнены пошагово с пояснениями и показом результатов в решении для MySQL):
Oracle Решение 4.1.1.b (запросы для проверки работоспособности)
1ALTER TRIGGER "TRG_subscribers_s_id" DISABLE;
2ALTER TRIGGER "TRG_subscriptions_sb_id" DISABLE;
3
4-- Добавление читателя:
5INSERT INTO "subscribers"
6 |
|
|
("s_id", |
7 |
|
|
"s_name") |
8 |
|
VALUES |
(500, |
9 |
|
|
N'Читателев Ч.Ч.'); |
10 |
|
|
|
11-- Удаление только что добавленного читателя:
12DELETE FROM "subscribers"
13WHERE "s_id" = 500;
14
15-- Добавление двух выдач книг:
16INSERT ALL
17INTO "subscriptions"
18 |
|
|
("sb_id", |
19 |
|
|
"sb_subscriber", |
20 |
|
|
"sb_book", |
21 |
|
|
"sb_start", |
22 |
|
|
"sb_finish", |
23 |
|
|
"sb_is_active") |
24 |
|
VALUES |
(200, |
25 |
|
|
1, |
26 |
|
|
1, |
27 |
|
|
TO_DATE('2019-01-12', 'YYYY-MM-DD'), |
28 |
|
|
TO_DATE('2019-02-12', 'YYYY-MM-DD'), |
29 |
|
|
'N') |
30 |
|
INTO "subscriptions" |
|
31 |
|
|
("sb_id", |
32 |
|
|
"sb_subscriber", |
33 |
|
|
"sb_book", |
34 |
|
|
"sb_start", |
35 |
|
|
"sb_finish", |
36 |
|
|
"sb_is_active") |
37 |
|
VALUES |
(201, |
38 |
|
|
2, |
39 |
|
|
1, |
40 |
|
|
TO_DATE('2020-01-12', 'YYYY-MM-DD'), |
41 |
|
|
TO_DATE('2020-02-12', 'YYYY-MM-DD'), |
42 |
|
|
'N') |
43 |
|
SELECT 1 FROM "DUAL"; |
|
44 |
|
|
|
|
|
|
|
45-- Изменение состояния добавленных выдач с «книга возвращена»
46-- на «книга не возвращена»:
47UPDATE "subscriptions"
48 |
|
SET |
"sb_is_active" = 'Y' |
49 |
|
WHERE |
"sb_id" >= 200; |
50 |
|
|
|
51-- Удаление только что добавленных выдач книг:
52DELETE FROM "subscriptions"
53WHERE "sb_id" >= 200;
54
55ALTER TRIGGER "TRG_subscribers_s_id" ENABLE;
56ALTER TRIGGER "TRG_subscriptions_sb_id" ENABLE;
На этом решение данной задачи завершено.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 290/545
Пример 31: обновление кэширующих таблиц и полей
Задание 4.1.1.TSK.A: модифицировать схему базы данных «Библиотека» таким образом, чтобы таблица authors хранила актуальную информацию о дате последней выдачи книги автора читателю.
Задание 4.1.1.TSK.B: создать кэширующую таблицу best_averages, содержащую в любой момент времени следующую актуальную информацию:
а) сколько в среднем книг находится на руках у читателей, за время работы с библиотекой прочитавших более 20 книг; б) за сколько в среднем по времени (в днях) прочитывает книгу читатель,
никогда не державший у себя книгу больше двух недель; в) сколько в среднем книг прочитал читатель, не имеющий просроченных выдач книг.
Задание 4.1.1.TSK.C: оптимизировать MySQL-триггеры из решения{281} задачи 4.1.1.b{272} так, чтобы не выполнять лишних действий там, где в них нет необходимости (подсказка: не в каждом случае нам нужны все собираемые имеющимися запросами данные).
Задание 4.1.1.TSK.D: доработать решение{272} задачи 4.1.1.a{272} для MySQL таким образом, чтобы оно учитывало изменения в таблице subscriptions, вызванные операцией каскадного удаления (при удалении книг). Убедиться, что решения для MS SQL Server и Oracle не требуют такой доработки.
Задание 4.1.1.TSK.E: доработать решение{281} задачи 4.1.1.b{272} для MySQL таким образом, чтобы оно учитывало изменения в таблице subscriptions, вызванные операцией каскадного удаления (при удалении книг). Убедиться, что решения для MS SQL Server и Oracle не требуют такой доработки.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 291/545
Пример 32: обеспечение консистентности данных
4.1.2. Пример 32: обеспечение консистентности данных
Задача 4.1.2.a{292}: модифицировать схему базы данных «Библиотека» таким образом, чтобы таблица subscribers хранила информацию о том, сколько в настоящий момент книг выдано каждому из читателей.
Задача 4.1.2.b{305}: модифицировать схему базы данных «Библиотека» таким образом, чтобы таблица genres хранила информацию о том, сколько в настоящий момент книг относится к каждому жанру.
Ожидаемый результат 4.1.2.a.
Таблица subscribers содержит дополнительное поле, хранящее актуальную информацию о количестве выданных каждому читателю книг:
s_id |
s_name |
s_books |
1 |
Иванов И.И. |
0 |
2 |
Петров П.П. |
0 |
3 |
Сидоров С.С. |
3 |
4 |
Сидоров С.С. |
2 |
Ожидаемый результат 4.1.2.b.
Таблица genres содержит дополнительное поле, хранящее актуальную информацию о количестве относящихся к каждому жанру книг:
g_id |
g_name |
g_books |
1 |
Поэзия |
2 |
2 |
Программирование |
3 |
3 |
Психология |
1 |
4 |
Наука |
0 |
5 |
Классика |
4 |
6 |
Фантастика |
1 |
Решение 4.1.2.a{292}.
Как и в решении{272} задачи 4.1.1.a{272} здесь нужно будет выполнить три шага:
•модифицировать таблицу subscribers (добавив туда поле для хранения количества выданных читателю книг);
•проинициализировать значения количества выданных книг для всех читателей;
•создать триггеры для поддержания этой информации в актуальном состоянии.
Вотличие от решения{272} задачи 4.1.1.a{272} здесь значение по умолчанию для нового поля представляет собой не NULL, а 0 (потому что «читатель ни разу не
приходил в библиотеку» — это «неизвестность», т.е. NULL, а «у читателя нет книг»
— это вполне чёткое и понятное значение, т.е. 0). Следуя той же логике, в инициализирующем запросе мы используем JOIN, а не LEFT JOIN — нет никакого смысла обновлять данные для читателей, ни разу не бравших книги.
Итак, для MySQL первые два шага выполняются с помощью следующих запросов.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 292/545
Пример 32: обеспечение консистентности данных
MySQL Решение 4.1.2.a (модификация таблицы и инициализация данных)
1-- Модификация таблицы:
2ALTER TABLE `subscribers`
3ADD COLUMN `s_books` INT(11) NOT NULL DEFAULT 0 AFTER `s_name`;
4
5-- Инициализация данных:
6UPDATE `subscribers`
7JOIN (SELECT `sb_subscriber`,
8 |
|
|
|
COUNT(`sb_id`) |
AS `s_has_books` |
9 |
|
|
FROM |
`subscriptions` |
|
10 |
|
|
WHERE |
`sb_is_active` |
= 'Y' |
11 |
|
|
GROUP |
BY `sb_subscriber`) AS `prepared_data` |
|
12 |
|
|
ON `s_id` = |
`sb_subscriber` |
|
13 |
|
SET |
`s_books` = |
`s_has_books`; |
|
Как видно из инициализирующего запроса, всю необходимую информацию для формирования значения поля s_books мы можем взять из таблицы subscriptions. На ней мы и будем создавать триггеры.
С INSERT- и DELETE-триггерами всё просто: если добавляется или удаляется «активная» выдача (поле sb_is_active равно Y), нужно увеличить или уменьшить на единицу значение счётчика выданных книг у соответствующего читателя.
MySQL Решение 4.1.2.a (триггеры для таблицы subscriptions)
1 DELIMITER $$
2
3-- Реакция на добавление выдачи книги:
4CREATE TRIGGER `s_has_books_on_subscriptions_ins`
5AFTER INSERT
6ON `subscriptions`
7FOR EACH ROW
8BEGIN
9IF (NEW.`sb_is_active` = 'Y') THEN
10UPDATE `subscribers`
11 |
SET |
`s_books` = `s_books` + 1 |
12WHERE `s_id` = NEW.`sb_subscriber`;
13END IF;
14END;
15$$
16
17-- Реакция на удаление выдачи книги:
18CREATE TRIGGER `s_has_books_on_subscriptions_del`
19AFTER DELETE
20ON `subscriptions`
21FOR EACH ROW
22BEGIN
23IF (OLD.`sb_is_active` = 'Y') THEN
24UPDATE `subscribers`
25 |
|
SET |
`s_books` = `s_books` - 1 |
26WHERE `s_id` = OLD.`sb_subscriber`;
27END IF;
28END;
29$$
30
31DELIMITER ;
СUPDATE-триггером ситуация будет более сложной, т.к. у нас есть два параметра, которые могут как измениться, так и остаться неизменными — идентификатор читателя и состояние выдачи.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 293/545
Пример 32: обеспечение консистентности данных
MySQL Решение 4.1.2.a (триггеры для таблицы subscriptions)
1 DELIMITER $$
2
3-- Реакция на обновление выдачи книги:
4CREATE TRIGGER `s_has_books_on_subscriptions_upd`
5AFTER UPDATE
6ON `subscriptions`
7FOR EACH ROW
8BEGIN
9
10-- A) Читатель тот же, Y -> N
11IF ((OLD.`sb_subscriber` = NEW.`sb_subscriber`) AND
12(OLD.`sb_is_active` = 'Y') AND
13(NEW.`sb_is_active` = 'N')) THEN
14UPDATE `subscribers`
15 |
SET |
`s_books` = `s_books` - 1 |
16WHERE `s_id` = OLD.`sb_subscriber`;
17END IF;
18
19-- B) Читатель тот же, N -> Y
20IF ((OLD.`sb_subscriber` = NEW.`sb_subscriber`) AND
21(OLD.`sb_is_active` = 'N') AND
22(NEW.`sb_is_active` = 'Y')) THEN
23UPDATE `subscribers`
24 |
SET |
`s_books` = `s_books` + 1 |
25WHERE `s_id` = OLD.`sb_subscriber`;
26END IF;
27
28-- C) Читатели разные, Y -> Y
29IF ((OLD.`sb_subscriber` != NEW.`sb_subscriber`) AND
30(OLD.`sb_is_active` = 'Y') AND
31(NEW.`sb_is_active` = 'Y')) THEN
32UPDATE `subscribers`
33 |
|
SET |
`s_books` = `s_books` - 1 |
34WHERE `s_id` = OLD.`sb_subscriber`;
35UPDATE `subscribers`
36 |
|
SET |
`s_books` = `s_books` + 1 |
37WHERE `s_id` = NEW.`sb_subscriber`;
38END IF;
39
40-- D) Читатели разные, Y -> N
41IF ((OLD.`sb_subscriber` != NEW.`sb_subscriber`) AND
42(OLD.`sb_is_active` = 'Y') AND
43(NEW.`sb_is_active` = 'N')) THEN
44UPDATE `subscribers`
45 |
|
SET |
`s_books` = `s_books` - 1 |
46WHERE `s_id` = OLD.`sb_subscriber`;
47END IF;
48
49-- E) Читатели разные, N -> Y
50IF ((OLD.`sb_subscriber` != NEW.`sb_subscriber`) AND
51(OLD.`sb_is_active` = 'N') AND
52(NEW.`sb_is_active` = 'Y')) THEN
53UPDATE `subscribers`
54 |
|
SET |
`s_books` = `s_books` + 1 |
|
|
|
|
55WHERE `s_id` = NEW.`sb_subscriber`;
56END IF;
57
58END;
59$$
60
61 DELIMITER ;
Изобразим рассмотренные ситуации графически.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 294/545