Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 33: контроль операций модификации данных
MySQL I |
Решение 4.2.1.b (триггеры для таблицы subscriptions) | |
|||
1 |
DELIMITER $$ |
|
|
|
2 |
|
|
|
|
3 |
CREATE TRIGGER 'sbs cntrl 10 books ins OK' |
|||
4 |
BEFORE INSERT |
|
|
|
5 |
ON 'subscriptions' |
|
|
|
6 |
|
FOR EACH ROW |
|
|
7 |
|
BEGIN |
|
|
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 Стр: 350/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 I > 0
23BEGIN
24SET @msg = CONCAT('The following readers have more books
25 |
than allowed (10 allowed): ', @bad records) ; |
26 |
RAISERROR @msg 16 1); |
|
|
27ROLLBACK TRANSACTION;
28RETURN;
29END;
30GO
Правильный вариант решения для MS SQL Server подвержен ограничениям, подробно описанным в решении{315} задачи 4.2.1.a{315} (невозможность создания IN- STEAD OF UPDATE триггера без отключения операции каскадного обновления на
внешних ключах, необходимость вычислять значение первичного ключа), потому здесь мы также ограничимся созданием только iNSERT-триггера.
Однако, благодаря тому, что мы ограничиваем анализ выполнения условия задачи только перечнем читателей, чьи идентификаторы находятся в псевдотаблице inserted, а также выполняем этот анализ до реальной вставки данных, есть шанс существенно повысить скорость работы нашего триггера.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 351/545
Пример 33: контроль операций модификации данных
MS SQL I |
Решение 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 ) ; |
29 |
RAISERROR @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 Стр: 352/545
Пример 33: контроль операций модификации данных
Исследование 4.2.1.EXP.B. Проведём исследование на базе данных «Большая библиотека», сравнив скорость работы представленных неправильного и правильного решений для MS SQL Server.
После выполнения тысячи запросов на вставку данных, нарушающих условие задачи, медианы времени (в секундах) приняли следующие значения:
Неправильное решение |
Правильное решение |
Разница, раз |
6.598 |
2.746 |
2.4 |
Получилось не так внушительно, как в случае с MySQL, но всё равно достаточно, чтобы ощутить разницу в производительности неправильного и правильного вариантов решения.
Переходим к решению для Oracle, в котором также представим два варианта
— неправильный и правильный. Их внутренняя логика будет полностью эквивалентна решению данной задачи для MySQL, потому сразу переходим к коду.
Oracl |
і |
Решение 4.2.1.b (неправильное решение) |
| |
|
||
e |
|
|||||
|
|
|
|
|
|
|
1 |
CREATE TRIGGER "sbs ctr 10 bks ins upd WRONG" |
|
||||
2 |
AFTER INSERT OR UPDATE |
|
|
|
|
|
3 |
ON "subscriptions" |
|
|
|
|
|
4 |
FOR EACH ROW |
|
|
|
|
|
5 |
|
DECLARE |
|
|
|
|
6 |
|
PRAGMA AUTONOMOUS TRANSACTION; |
|
|
||
7 |
|
msg NCLOB; |
|
|
|
|
8 |
|
BEGIN |
|
|
|
|
9 |
|
SELECT 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 |
|
|
|
|
|
|
32 |
|
IF (LENGTH msg) > 0 |
|
|
|
|
33 |
|
THEN |
|
|
|
|
34 |
|
RAISE APPLICATION ERROR(-20001 |
'The following readers have |
|||
35 |
|
|
|
|
more books than allowed |
|
36 |
|
|
|
|
(10 allowed): ' || msg); |
|
37 |
|
END IF; |
|
|
|
|
38 |
|
END; |
|
|
|
|
Поскольку Oracle запрещает обращение из триггера к таблице subscriptions, в которую производится вставка, мы запускаем выполнение триггера в автономной транзакции (строка 6). Эту особенность придётся учитывать и в правильном решении, которое мы сейчас и рассмотрим.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 353/545
Пример 33: контроль операций модификации данных |
|
|
|||
Oracl |
і Решение 4.2.1.b (триггеры для таблицы subscriptions) |
| |
|
||
e |
|
||||
|
|
|
|
|
|
1 |
CREATE TRIGGER "sbs ctr 10 bks ins upd OK" |
|
|
||
2 |
BEFORE INSERT OR UPDATE |
|
|
|
|
3 |
ON "subscriptions" |
|
|
|
|
4 |
FOR EACH ROW |
|
|
|
|
5 |
DECLARE |
|
|
|
|
6 |
PRAGMA AUTONOMOUS TRANSACTION; |
|
|
|
|
7 |
msg NCLOB; |
|
|
|
|
8 |
BEGIN |
|
|
|
|
9 |
SELECT 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 |
|
|
|
|
|
26 |
IF (LENGTH msg) > 0 |
|
|
|
|
27 |
THEN |
|
|
|
|
28 |
RAISE APPLICATION ERROR(-20001 |
msg); |
|
|
|
29 |
END IF; |
|
|
|
|
30 |
END; |
|
|
|
|
Остаётся проверить разницу в скорости работы представленных решений.
Исследование 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 Стр: 354/545