Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 33: контроль операций модификации данных
MySQL Решение 4.2.1.b (триггеры для таблицы subscriptions)
1 DELIMITER $$
2
3CREATE TRIGGER `sbs_cntrl_10_books_ins_OK`
4BEFORE INSERT
5ON `subscriptions`
6FOR EACH ROW
7BEGIN
8 |
|
|
|
9 |
|
SET @msg = IFNULL((SELECT CONCAT('Subscriber ', `s_name`, |
|
10 |
|
' (id=', `sb_subscriber`, ') already has ', |
|
11 |
|
`sb_books`, ' books out of 10 allowed.') |
|
12 |
|
AS `message` |
|
13 |
|
FROM (SELECT |
`sb_subscriber`, |
14 |
|
|
COUNT(`sb_book`) AS `sb_books` |
15 |
|
FROM |
`subscriptions` |
16 |
|
WHERE |
`sb_is_active` = 'Y' |
17 |
|
AND `sb_subscriber` = NEW.`sb_subscriber` |
|
18 |
|
GROUP |
BY `sb_subscriber` |
19 |
|
HAVING |
`sb_books` >= 10) AS `prepared_data` |
20 |
|
JOIN `subscribers` |
|
21 |
|
ON `sb_subscriber` = `s_id`), |
|
22 |
|
''); |
|
23 |
|
|
|
24IF (LENGTH(@msg) > 0)
25THEN
26SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @msg, MYSQL_ERRNO = 1001;
27END IF;
28
29END;
30$$
31
32CREATE TRIGGER `sbs_cntrl_10_books_upd_OK`
33BEFORE UPDATE
34ON `subscriptions`
35FOR EACH ROW
36BEGIN
37 |
|
|
|
38 |
|
SET @msg = IFNULL((SELECT CONCAT('Subscriber ', `s_name`, |
|
39 |
|
' (id=', `sb_subscriber`, ') already has ', |
|
40 |
|
`sb_books`, ' books out of 10 allowed.') |
|
41 |
|
AS `message` |
|
42 |
|
FROM (SELECT |
`sb_subscriber`, |
43 |
|
|
COUNT(`sb_book`) AS `sb_books` |
44 |
|
FROM |
`subscriptions` |
45 |
|
WHERE |
`sb_is_active` = 'Y' |
46 |
|
AND `sb_subscriber` = NEW.`sb_subscriber` |
|
47 |
|
GROUP |
BY `sb_subscriber` |
48 |
|
HAVING |
`sb_books` >= 10) AS `prepared_data` |
49 |
|
JOIN `subscribers` |
|
50 |
|
ON `sb_subscriber` = `s_id`), |
|
51 |
|
''); |
|
52 |
|
|
|
53IF (LENGTH(@msg) > 0)
54THEN
55SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @msg, MYSQL_ERRNO = 1001;
56END IF;
57
58END;
59$$
60
61DELIMITER ;
Вправильном решении для MySQL мы реагируем только на выдачи книг для читателя, идентификатор которого фигурирует в добавляемой/изменяемой записи. Также проверку мы выполняем перед тем, как изменения вступят в силу, и потому
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 330/545
Пример 33: контроль операций модификации данных
СУБД даже не придётся их отменять, если операция будет запрещена (что также сэкономит немного времени).
Исследование 4.2.1.EXP.A. Проведём исследование на базе данных «Большая библиотека», сравнив скорость работы представленных неправильного и правильного решений для MySQL.
После выполнения тысячи запросов на вставку данных, нарушающих условие задачи, медианы времени (в секундах) приняли следующие значения:
Неправильное решение |
Правильное решение |
Разница, раз |
51.177 |
0.009 |
5686 |
Переходим к решению для MS SQL Server, в котором также представим два варианта — неправильный и правильный.
Неправильный вариант полностью повторяет аналогичную неверную логику решения для MySQL — мы пытаемся анализировать полный набор информации в базе данных, к тому же делаем это после выполнения операции модификации данных, заставляя СУБД отменять полученные изменения.
MS SQL Решение 4.2.1.b (неправильное решение)
1CREATE TRIGGER [sbs_cntrl_10_books_ins_upd_WRONG]
2ON [subscriptions]
3AFTER INSERT, UPDATE
4AS
5DECLARE @bad_records NVARCHAR(max);
6DECLARE @msg NVARCHAR(max);
7 |
|
|
|
8 |
|
SELECT @bad_records = STUFF((SELECT |
', ' + [list] |
9 |
|
FROM (SELECT |
CONCAT('(id=', [s_id], ', ', |
10 |
|
|
[s_name], ', books=', |
11 |
|
|
COUNT([sb_book]), ')') AS [list] |
12 |
|
FROM |
[subscribers] |
13 |
|
|
JOIN [subscriptions] |
14 |
|
|
ON [s_id] = [sb_subscriber] |
15 |
|
WHERE |
[sb_is_active] = 'Y' |
16 |
|
GROUP |
BY [s_id], [s_name] |
17 |
|
HAVING |
COUNT([sb_book]) > 10) |
18 |
|
AS [prepared_data] |
|
19 |
|
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), |
|
20 |
|
1, 2, ''); |
|
21 |
|
|
|
22IF (LEN(@bad_records) > 0)
23BEGIN
24SET @msg = CONCAT('The following readers have more books
25 |
|
than allowed (10 allowed): ', @bad_records); |
|
|
|
26RAISERROR (@msg, 16, 1);
27ROLLBACK TRANSACTION;
28RETURN;
29END;
30GO
Правильный вариант решения для MS SQL Server подвержен ограничениям, подробно описанным в решении{315} задачи 4.2.1.a{315} (невозможность создания INSTEAD OF UPDATE триггера без отключения операции каскадного обновления на внешних ключах, необходимость вычислять значение первичного ключа), потому здесь мы также ограничимся созданием только INSERT-триггера.
Однако, благодаря тому, что мы ограничиваем анализ выполнения условия задачи только перечнем читателей, чьи идентификаторы находятся в псевдотаблице inserted, а также выполняем этот анализ до реальной вставки данных, есть шанс существенно повысить скорость работы нашего триггера.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 331/545
Пример 33: контроль операций модификации данных
MS SQL Решение 4.2.1.b (триггер для таблицы subscriptions)
1CREATE TRIGGER [sbs_cntrl_10_books_ins_OK]
2ON [subscriptions]
3INSTEAD OF INSERT
4AS
5DECLARE @bad_records NVARCHAR(max);
6DECLARE @msg NVARCHAR(max);
7 |
|
|
|
|
8 |
|
SELECT @bad_records = STUFF((SELECT |
', ' + [list] |
|
9 |
|
FROM (SELECT |
CONCAT('(id=', [s_id], ', ', |
|
10 |
|
|
[s_name], ', books=', |
|
11 |
|
|
COUNT([sb_book]), ')') AS [list] |
|
12 |
|
FROM |
[subscribers] |
|
13 |
|
|
JOIN [subscriptions] |
|
14 |
|
|
ON [s_id] = [sb_subscriber] |
|
15 |
|
WHERE |
[sb_is_active] = 'Y' |
|
16 |
|
|
AND [sb_subscriber] IN |
|
17 |
|
|
(SELECT |
[sb_subscriber] |
18 |
|
|
FROM |
[inserted]) |
19 |
|
GROUP |
BY [s_id], [s_name] |
|
20 |
|
HAVING |
COUNT([sb_book]) >= 10) |
|
21 |
|
AS [prepared_data] |
|
|
22 |
|
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), |
||
23 |
|
1, 2, ''); |
|
|
24 |
|
|
|
|
25IF (LEN(@bad_records) > 0)
26BEGIN
27SET @msg = CONCAT('The following readers have more books
28 |
|
than allowed (10 allowed): ', @bad_records); |
|
|
|
29RAISERROR (@msg, 16, 1);
30ROLLBACK TRANSACTION;
31RETURN;
32END;
33
34SET IDENTITY_INSERT [subscriptions] ON;
35INSERT INTO [subscriptions]
36 |
|
([sb_id], |
37 |
|
[sb_subscriber], |
38 |
|
[sb_book], |
39 |
|
[sb_start], |
40 |
|
[sb_finish], |
41 |
|
[sb_is_active]) |
42 |
|
SELECT ( CASE |
43 |
|
WHEN [sb_id] IS NULL |
44 |
|
OR [sb_id] = 0 THEN IDENT_CURRENT('subscriptions') |
45 |
|
+ IDENT_INCR('subscriptions') |
46 |
|
+ ROW_NUMBER() OVER (ORDER BY |
47 |
|
(SELECT 1)) |
48 |
|
- 1 |
49 |
|
ELSE [sb_id] |
50 |
|
END ) AS [sb_id], |
51[sb_subscriber],
52[sb_book],
53[sb_start],
54[sb_finish],
55[sb_is_active]
56 FROM [inserted];
57SET IDENTITY_INSERT [subscriptions] OFF;
58GO
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 332/545
Пример 33: контроль операций модификации данных
Исследование 4.2.1.EXP.B. Проведём исследование на базе данных «Большая библиотека», сравнив скорость работы представленных неправильного и правильного решений для MS SQL Server.
После выполнения тысячи запросов на вставку данных, нарушающих условие задачи, медианы времени (в секундах) приняли следующие значения:
Неправильное решение |
Правильное решение |
Разница, раз |
6.598 |
2.746 |
2.4 |
Получилось не так внушительно, как в случае с MySQL, но всё равно достаточно, чтобы ощутить разницу в производительности неправильного и правильного вариантов решения.
Переходим к решению для Oracle, в котором также представим два варианта
— неправильный и правильный. Их внутренняя логика будет полностью эквивалентна решению данной задачи для MySQL, потому сразу переходим к коду.
Oracle Решение 4.2.1.b (неправильное решение)
1CREATE TRIGGER "sbs_ctr_10_bks_ins_upd_WRONG"
2AFTER INSERT OR UPDATE
3ON "subscriptions"
4FOR EACH ROW
5DECLARE
6PRAGMA AUTONOMOUS_TRANSACTION;
7msg NCLOB;
8BEGIN
9SELECT NVL((SELECT UTL_RAW.CAST_TO_NVARCHAR2
10 |
|
( |
|
11 |
|
LISTAGG |
|
12 |
|
( |
|
13 |
|
UTL_RAW.CAST_TO_RAW(N'(id=' || |
|
14 |
|
"sb_subscriber" || N', ' || "s_name" || |
|
15 |
|
N', books=' || "s_books" || N')'), |
|
16 |
|
UTL_RAW.CAST_TO_RAW(N', ') |
|
17 |
|
) |
|
18 |
|
WITHIN |
GROUP (ORDER BY "sb_subscriber") |
19 |
|
) |
|
20 |
|
FROM (SELECT |
"sb_subscriber", |
21 |
|
|
"s_name", |
22 |
|
|
COUNT("sb_book") AS "s_books" |
23 |
|
FROM |
"subscribers" |
24 |
|
|
JOIN "subscriptions" |
25 |
|
|
ON "s_id" = "sb_subscriber" |
26 |
|
WHERE |
"sb_is_active" = 'Y' |
27 |
|
GROUP |
BY "sb_subscriber", "s_name" |
28 |
|
HAVING |
COUNT("sb_book") > 10) "prepared_data"), |
29 |
|
'') |
|
30 |
|
INTO msg FROM dual; |
|
31 |
|
|
|
32IF (LENGTH(msg) > 0)
33THEN
34RAISE_APPLICATION_ERROR(-20001, 'The following readers have
35 |
|
more books than |
allowed |
36 |
|
(10 allowed): ' |
|| msg); |
37END IF;
38END;
Поскольку Oracle запрещает обращение из триггера к таблице subscrip-
tions, в которую производится вставка, мы запускаем выполнение триггера в ав-
тономной транзакции (строка 6). Эту особенность придётся учитывать и в правильном решении, которое мы сейчас и рассмотрим.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 333/545
Пример 33: контроль операций модификации данных
Oracle Решение 4.2.1.b (триггеры для таблицы subscriptions)
1CREATE TRIGGER "sbs_ctr_10_bks_ins_upd_OK"
2BEFORE INSERT OR UPDATE
3ON "subscriptions"
4FOR EACH ROW
5DECLARE
6PRAGMA AUTONOMOUS_TRANSACTION;
7msg NCLOB;
8BEGIN
9SELECT NVL((SELECT (N'Subscriber ' || "s_name" || N' (id=' ||
10 |
|
"sb_subscriber" || N') already has ' || |
|
11 |
|
"sb_books" || |
N' books out of 10 allowed.') |
12 |
|
AS "message" |
|
13 |
|
FROM (SELECT |
"sb_subscriber", |
14 |
|
|
COUNT("sb_book") AS "sb_books" |
15 |
|
FROM |
"subscriptions" |
16 |
|
WHERE |
"sb_is_active" = 'Y' |
17 |
|
AND "sb_subscriber" = :new."sb_subscriber" |
|
18 |
|
GROUP |
BY "sb_subscriber" |
19 |
|
HAVING |
COUNT("sb_book") >= 10) |
20 |
|
"prepared_data" |
|
21 |
|
JOIN "subscribers" |
|
22 |
|
ON "sb_subscriber" = "s_id"), |
|
23 |
|
'') |
|
24 |
|
INTO msg FROM dual; |
|
25 |
|
|
|
|
|
|
|
26IF (LENGTH(msg) > 0)
27THEN
28RAISE_APPLICATION_ERROR(-20001, msg);
29END IF;
30END;
Остаётся проверить разницу в скорости работы представленных решений.
Исследование 4.2.1.EXP.C. Проведём исследование на базе данных «Большая библиотека», сравнив скорость работы представленных неправильного и правильного решений для Oracle.
После выполнения тысячи запросов на вставку данных, нарушающих условие задачи, медианы времени (в секундах) приняли следующие значения:
Неправильное решение |
Правильное решение |
Разница, раз |
99.667 |
4.961 |
20 |
Если свести все результаты исследований в одну таблицу, получается:
СУБД |
Неправильное |
Правильное |
Разница, раз |
|
решение |
решение |
|
MySQL |
51.177 |
0.009 |
5686 |
MS SQL Server |
6.598 |
2.746 |
2.4 |
Oracle |
99.667 |
4.961 |
20 |
Также отметим, что во всех СУБД в неправильном варианте решения в процессе накопления информации обо всех читателях, для которых нарушается условие задачи, мы рискуем превысить максимально допустимый размер строки, аккумулирующей данную информацию. Так что второй (правильный) вариант решения оказывается не только быстрее, но и надёжнее.
На этом решение данной задачи завершено.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 334/545