Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)

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

Увеличим на пять единиц количество экземпляров книги, которой сейчас в библиотеке зарегистрировано 10 экземпляров (такая книга у нас одна):

3WHERE "b_quantity" = 10;

4COMMIT; -- И подождать минуту.

Удалим книгу, оба экземпляра которой сейчас находится на руках у читателей (книга с идентификатором 1).

1DELETE FROM "books"

2WHERE "b_id" = 1;

3COMMIT; -- И подождать минуту.

Отметим, что по выдаче с идентификатором 3 книга возвращена:

3WHERE "sb_id" = 3;

4COMMIT; -- И подождать минуту.

Отменим эту операцию (снова отметим книгу как невозвращённую):

3WHERE "sb_id" = 3;

4COMMIT; -- И подождать минуту.

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

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

Добавим в базу данных информацию о том, что читатель с идентификатором 2 взял в библиотеке книги с идентификаторами 5 и 6:

1INSERT ALL

2INTO "subscriptions" ("sb_subscriber",

22SELECT 1 FROM "DUAL";

23COMMIT; -- И подождать минуту.

Удалим информацию о выдаче с идентификатором 42 (книга по этой выдаче уже возвращена):

1DELETE FROM "subscriptions"

2WHERE "sb_id" = 42;

3COMMIT; -- И подождать минуту.

Удалим информацию о выдаче с идентификатором 62 (книга по этой выдаче ещё не возвращена):

1DELETE FROM "subscriptions"

2WHERE "sb_id" = 62;

3COMMIT; -- И подождать минуту.

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

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

Наконец, удалим все книги (что также приведёт к каскадному удалению всех выдач):

1DELETE FROM "books";

2COMMIT; -- И подождать минуту.

Итак, все операции модификации данных в таблицах books и subscriptions вызывают соответствующие изменения в материализованном представле-

нии books_statistics.

Решение 3.1.2.b{215}.

Если вы пропустили решение{216} задачи 3.1.2.a{215}, настоятельно рекомендуется ознакомиться с ним перед тем, как продолжить чтение, т.к. многие неочевидные моменты, которые встретят в данном решении, были рассмотрены ранее.

По сравнению с предыдущей задачей{215} здесь всё будет намного проще, т.к. запрос, формирующий необходимый набор данных, не содержит выражений, подпадающих под ограничения индексированных представлений MS SQL Server и материализованных представлений Oracle.

Проблема будет только с MySQL, т.к. в нём подобных представлений нет как явления, и нам снова придётся создавать кэширующую таблицу и триггеры.

Создадим кэширующую таблицу (именно кэширующую, а не агрегирующую, как в задаче 3.1.2.a{215}, т.к. здесь мы ничего не агрегируем, а лишь сохраняем готовый результат). Код её создания можно почти полностью взять из кода создания таблицы subscritions, а для полей sb_subscriber и sb_book взять их опреде-

ления из таблицы subscribers и books.

MySQL Решение 3.1.2.b (создание кэширующей таблицы)

1CREATE TABLE `subscriptions_ready`

2(

3`sb_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

4`sb_subscriber` VARCHAR(150) NOT NULL,

5`sb_book` VARCHAR(150) NOT NULL,

6`sb_start` DATE NOT NULL,

7`sb_finish` DATE NOT NULL,

8`sb_is_active` ENUM ('Y', 'N') NOT NULL,

9CONSTRAINT `PK_subscriptions` PRIMARY KEY (`sb_id`)

10)

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

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

Проинициализируем данные в созданной таблице:

MySQL Решение 3.1.2.b (очистка таблицы и инициализация данных)

1-- Очистка таблицы:

2TRUNCATE TABLE `subscriptions_ready`;

3

4-- Инициализация данных:

5INSERT INTO `subscriptions_ready`

12SELECT `sb_id`,

13`s_name` AS `sb_subscriber`,

14`b_name` AS `sb_book`,

15`sb_start`,

16`sb_finish`,

17`sb_is_active`

18 FROM `books`

19JOIN `subscriptions`

20ON `b_id` = `sb_book`

21JOIN `subscribers`

22ON `sb_subscriber` = `s_id`;

Напишем триггеры, модифицирующие данные в кэширующей таблице. Источником данных являются таблицы books, subscribers и subscriptions, потому придётся создавать триггеры для всех трёх таблиц.

Поскольку MySQL версии 5.6 не позволяет создавать несколько однотипных триггеров на одной и той же таблице, перед выполнением следующего кода придётся удалить созданные в задаче 3.2.1.a{215} триггеры с таблиц books и subscriptions.

Регистрация в библиотеке новых книг и читателей никак не влияет на содержимое таблицы subscriptions, потому нет необходимости создавать INSERT- триггеры на таблицах books и subscribers — достаточно DELETE- и UPDATE- триггеров.

Обратите внимание на несколько важных моментов в представленном ниже

коде:

•внутри триггера в MySQL мы не можем явно или неявно подтверждать транзакцию, а потому не можем использовать для очистки таблицы оператор TRUNCATE (он является «нетранзакционным», и потому приводит к неявному подтверждению транзакции);

•мы не храним в нашей кэширующей таблице идентификаторы книг, и потому не можем найти и удалить только отдельные записи (искать и удалять по названию книги тоже нельзя — несколько разных книг могут иметь одинаковое название), потому мы вынуждены каждый раз очищать всю таблицу и заново наполнять её данными;

•в задаче 3.2.1.a{215} мы использовали BEFORE-триггеры, хотя могли использовать и AFTER — там это не имело значения, здесь же мы обязаны использовать только AFTER-триггеры, т.к. в противном случае в силу особенностей логики транзакций поведение MySQL отличается от ожидаемого, и информация в кэширующей таблице может не обновиться.

За исключением только что рассмотренных нюансов код тела обоих триггеров совершенно тривиален и представляет собой два запроса — на удаление всех

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

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

данных из таблицы и на наполнение таблицы данными на основе полученной выборки. Оба эти запроса можно выполнить совершенно отдельно от триггеров как самостоятельные SQL-конструкции.

В триггере, реагирующем на обновление информации о книге, есть проверка (строка 45) того, поменялось ли название книги. Если не поменялось, то нет необходимости обновлять закэшированные данные.

MySQL Решение 3.1.2.b (триггеры для таблицы books)

1-- Удаление старых версий триггеров

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

3DROP TRIGGER `upd_sbs_rdy_on_books_del`;

4DROP TRIGGER `upd_sbs_rdy_on_books_upd`;

5

6-- Переключение разделителя завершения запроса,

7-- т.к. сейчас запросом будет создание триггера,

8-- внутри которого есть свои, классические запросы:

9DELIMITER $$

10

11-- Создание триггера, реагирующего на удаление книг:

12CREATE TRIGGER `upd_sbs_rdy_on_books_del`

13AFTER DELETE

14ON `books`

15FOR EACH ROW

16BEGIN

17DELETE FROM `subscriptions_ready`;

18INSERT INTO `subscriptions_ready`

36END;

37$$

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