Материал: Using_MySql,_MS_SQL_Server_and_Oracle

Пример 27: выборка данных с использованием кэширующих представлений и таблиц

MySQL I Решение 3.1.2.b (триггеры для таблицы subscriptions) (продолжение) |

51-- Создание триггера, реагирующего на обновление выдачи книг:

52CREATE TRIGGER 'upd_sbs_rdy_on_subscriptions_upd'

53AFTER UPDATE

54ON 'subscriptions'

55FOR EACH ROW

56BEGIN

57UPDATE 'subscriptions_ready'

58JOIN (SELECT 'sb_id',

'subscriptions_ready' 'sb_subscriber' = 'new_data' 's_name',

71'subscriptions_ready' 'sb_book' = 'new_data' 'b_name',

72'subscriptions_ready' 'sb_start' = NEW.'sb_start',

73'subscriptions_ready' 'sb_finish' = NEW.'sb_finish',

74'subscriptions_ready' 'sb_is_active' = NEW.'sb_is_active'

75WHERE 'subscriptions_ready' 'sb_id' = OLD.'sb_id';

76END;

77$$

78

79-- Восстановление разделителя завершения запросов:

80DELIMITER ;

Код iNSERT-триггера (строки 12-39) очень похож на код аналогичного триггера на таблице books. Отличие состоит в том, что в данном случае мы знаем идентификаторы читателя и книги, и это избавляет нас от необходимости формировать полную выборку.

Код DELETE-триггера (строки 41-50) получаемся самым компактным: нам нужно просто удалить из таблицы subscriptions_ready записи, идентификаторы которых совпадают с идентификаторами записей, удаляемых из таблицы subscriptions.

Код UPDATE-триггера (строки 51-77) — самый нетривиальный. Сам по себе

синтаксис обновления на основе выборки выглядит непривычно (мы вынуждены объединять результаты выборки из обновляемой таблицы и выборки-источника — строки 57-68).

Условия в строках 66-67 позволяют сократить количество выбираемых рядов, а условие в строке 68 гарантирует, что мы получим данные о новой записи таблицы subscriptions, даже если у неё изменился первичный ключ. Следуя этой же логике, мы не указываем условие объединения ' subscriptions_ready' JOIN

... 'new_data', т.к. единственным здравым условием объединения здесь может быть совпадение значений sb_id, но если первичный ключ записи в таблице subscriptions поменялся, то такого совпадения не будет, т.к. в таблице subscriptions_ready всё ещё хранится старое значение первичного ключа обновляемой записи.

В строках 69-74 новые данные для обновления полей мы берём из двух источников: из явно переданных данных через ключевое слово NEW (все значения, которые мы можем получить напрямую) и из результатов выборки new_data (имя читателя и название книги, т.к. их нет и не может быть в явно переданных данных,

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 250/545

Пример 27: выборка данных с использованием кэширующих представлений и таблиц

доступных через ключевое слово NEW).

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 251/545

Пример 27: выборка данных с использованием кэширующих представлений и таблиц

Условие в строке 75 гарантирует, что мы обновим нужную запись: здесь необходимо сравнение идентификатора обновляемой записи именно с OLD .' sb_id', а не с NEW.' sb_id', т.к. у обновляемой записи в таблице subscriptions мог поменяться первичный ключ, и нам нужно найти его старое значение в таблице subscriptions_ready (благодаря выражению в строке 69 это значение тоже обновится, если оно изменилось).

Вся логика проверки работы таких триггеров подробно показана в решении{216} задачи 3.1.2.a{215}. Вы можете самостоятельно провести эксперимент, изменяя произвольным образом данные в таблице books, subscribers и subscriptions и отслеживая соответствующие изменения в таблице subscriptions_ready.

По сравнению с решением для MySQL, решения для MS SQL Server и Oracle предельно просты. В их основе лежит обычный запрос на выборку, который можно выполнить и сам по себе.

1-- Удаление старой версии индексированного представления

2-- (удобно при разработке и отладке):

3DROP VIEW [subscriptions_ready]; 4

5-- Создание представления:

6CREATE VIEW [subscriptions_ready]

7WITH SCHEMABINDING

8AS

9SELECT [sb_id],

10[s_name] AS [sb_subscriber],

11[b_name] AS[sb_book]

12[sb_start],

13[sb_finish],

14[sb_is_active]

15FROM [dbo] [books]

21-- Создание уникального кластерного индекса на представлении.

22-- Именно эта операция "включает" автоматическое обновление

23-- представления при изменении данных в таблицах,

24-- на которых оно построено:

25CREATE UNIQUE CLUSTERED INDEX [idx_subscriptions_ready]

26ON [subscriptions ready] ( [sb id] ;

Чтобы данные в представлении subscriptions_ready обновлялись автоматически, его нужно сделать индексированным, т.е. создать на нём уникальный кластерный индекс (строки 25-26).

Необходимым условием создания такого индекса на представлении является привязка представления к схеме базы данных (строка 7), указывающая СУБД на необходимость установить и отслеживать соответствие между использованием в коде представления объектов базы данных и реальным состоянием таких объектов (их существованием, доступностью и т.д.) не только в момент создания представления, но и в момент любой модификации объектов базы данных, на которые ссылается представление.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 252/545

Пример 27: выборка данных с использованием кэширующих представлений и таблиц

Решение данной задачи для Oracle ещё проще: берётся запрос на выборку, позволяющий получить желаемые данные, и используется как тело материализованного представления. СУБД будет автоматически обновлять данные в этом представлении раз в минуту (логика такого поведения и остальные особенности создания материализованных представлений в Oracle была рассмотрена в решении{216} задачи 3.1.2. a{215}.).

Oracle і Решение 3.1.2.b [

1-- Удаление старой версии материализованного представления

2-- (удобно при разработке и отладке):

3DROP MATERIALIZED VIEW "subscriptions_ready" 4

5-- Создание материализованного представления:

6CREATE MATERIALIZED VIEW "subscriptions_ready"

7BUILD IMMEDIATE

8REFRESH FORCE

9START WITH (SYSDATE) NEXT (SYSDATE + 1/1440)

10AS

11SELECT "sb_id",

12"s_name" AS "sb_subscriber"

13"b_name" AS "sb_book",

14"sb_start",

15"sb_fini sh",

16"sb_i s_active"

17FROM "books"

Задание 3.1.2.TSK.A: проверить корректность обновления кэширующей таблицы и представлений из решения задачи 3.1.2.b{215}.

Задание 3.1.2.TSK.B: создать кэширующее представление, позволяющее получать список всех книг и их жанров (две колонки: первая — название книги, вторая — жанры книги, перечисленные через запятую).

Задание 3.1.2.TSK.C: создать кэширующее представление, позволяющее получать список всех авторов и их книг (две колонки: первая — имя автора, вторая — написанные автором книги, перечисленные через запятую).

Задание 3.1.2.TSK.D: доработать решение{246} задачи 3.1.2.a{215} для (Ъ MySQL таким образом, чтобы оно учитывало изменения в таблице sub-

scriptions, вызванные операцией каскадного удаления (при удалении читателей). Убедиться, что решения для MS SQL Server и Oracle не тре-

буют такой доработки.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 253/545

Пример 28: использование представлений для сокрытия значений и структур данных

3.1.3.Пример 28: использование представлений для сокрытия значений и структур данных

Задача 3.1.3.a{242}: создать представление, через которое невозможно получить информацию о том, какой конкретно читатель взял ту или иную книгу.

Задача 3.1.3.b{243}: создать представление, возвращающее всю информацию из таблицы subscriptions, преобразуя даты из полей sb_start и sb_finish в формат UNIXTIME.

Ожидаемый результат 3.1.3.a.

Выполнение запроса вида SELECT * FROM {представление}

позволяет получить результат следующего вида:

Ожидаемый результат 3.1.3.b.

Выполнение запроса вида SELECT * FROM {представление} позволяет получить результат следующего вида:

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 254/545