Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 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_finish",
16"sb_is_active"
17 |
|
FROM "books" |
|
18 |
|
JOIN |
"subscriptions" |
19 |
|
ON |
"b_id" = "sb_book" |
20 |
|
JOIN |
"subscribers" |
21 |
|
ON |
"sb_subscriber" = "s_id"; |
|
|
|
|
Задание 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 таким образом, чтобы оно учитывало изменения в таблице subscriptions, вызванные операцией каскадного удаления (при удалении читателей). Убедиться, что решения для MS SQL Server и Oracle не требуют такой доработки.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 240/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 {представление} |
позволяет |
|||||
получить результат следующего вида: |
|
|
|
|||||
|
|
|
|
|
|
|
||
sb_id |
sb_book |
sb_start |
sb_finish |
|
sb_is_active |
|
||
2 |
1 |
2011-01-12 |
2011-02-12 |
|
N |
|
||
3 |
3 |
2012-05-17 |
2012-07-17 |
|
Y |
|
||
42 |
2 |
2012-06-11 |
2012-08-11 |
|
N |
|
||
57 |
5 |
2012-06-11 |
2012-08-11 |
|
N |
|
||
61 |
7 |
2014-08-03 |
2014-10-03 |
|
N |
|
||
62 |
5 |
2014-08-03 |
2014-10-03 |
|
Y |
|
||
86 |
1 |
2014-08-03 |
2014-09-03 |
|
Y |
|
||
91 |
1 |
2015-10-07 |
2015-03-07 |
|
Y |
|
||
95 |
4 |
2015-10-07 |
2015-11-07 |
|
N |
|
||
99 |
4 |
2015-10-08 |
2025-11-08 |
|
Y |
|
||
100 |
3 |
2011-01-12 |
2011-02-12 |
|
N |
|
||
Ожидаемый результат 3.1.3.b.
Выполнение запроса вида SELECT * FROM {представление} позволяет получить результат следующего вида:
sb_id |
sb_subscriber |
sb_book |
sb_start |
sb_finish |
sb_is_active |
2 |
1 |
1 |
1294779600 |
1297458000 |
N |
3 |
3 |
3 |
1337202000 |
1342472400 |
Y |
42 |
1 |
2 |
1339362000 |
1344632400 |
N |
57 |
4 |
5 |
1339362000 |
1344632400 |
N |
61 |
1 |
7 |
1407013200 |
1412283600 |
N |
62 |
3 |
5 |
1407013200 |
1412283600 |
Y |
86 |
3 |
1 |
1407013200 |
1409691600 |
Y |
91 |
4 |
1 |
1444165200 |
1425675600 |
Y |
95 |
1 |
4 |
1444165200 |
1446843600 |
N |
99 |
4 |
4 |
1444251600 |
1762549200 |
Y |
100 |
1 |
3 |
1294779600 |
1297458000 |
N |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 241/545
Пример 28: использование представлений для сокрытия значений и структур данных
Решение 3.1.3.a{241}.
Единственное, что нужно сделать для решения этой задачи, — построить представление, выбирающее все поля таблицы subscriptions, кроме поля sb_subscriber. Внутренняя логика работы представлений будет совершенно идентична для всех трёх СУБД, и решения будут отличаться только особенностями синтаксиса создания представлений.
MySQL Решение 3.1.3.a
1CREATE VIEW `subscriptions_anonymous`
2AS
3SELECT `sb_id`,
4`sb_book`,
5`sb_start`,
6`sb_finish`,
7`sb_is_active`
8 FROM `subscriptions`
MS SQL Решение 3.1.3.a
1CREATE VIEW [subscriptions_anonymous]
2WITH SCHEMABINDING
3AS
4SELECT [sb_id],
5[sb_book],
6[sb_start],
7[sb_finish],
8[sb_is_active]
9 FROM [dbo].[subscriptions]
В MS SQL Server мы можем позволить себе повысить надёжность работы представления, указав (строка 2 запроса) СУБД на необходимость установить и отслеживать соответствие между использованием в коде представления объектов базы данных и реальным состоянием таких объектов (их существованием, доступностью и т.д.) не только в момент создания представления, но и в момент любой модификации объектов базы данных, на которые ссылается представление. Для включения этой опции мы также должны указать имя таблицы вместе с именем схемы ([dbo]), которой она принадлежит (строка 9 запроса).
Oracle Решение 3.1.3.a
1CREATE VIEW "subscriptions_anonymous"
2AS
3SELECT "sb_id",
4"sb_book",
5"sb_start",
6"sb_finish",
7"sb_is_active"
8FROM "subscriptions"
Oracle (как и MySQL) не поддерживает опцию WITH SCHEMABINDING, потому здесь мы создаём обычное представление с использованием тривиального запроса на выборку.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 242/545
Пример 28: использование представлений для сокрытия значений и структур данных
Решение 3.1.3.b{241}.
Решение данной задачи сводится к построению представления на выборке всех полей из таблицы subscriptions, где к полям sb_start и sb_finish применены функции преобразования из внутреннего формата СУБД представления даты-времени в формат UNIXTIME.
MySQL Решение 3.1.3.b
1CREATE VIEW `subscriptions_unixtime`
2AS
3SELECT `sb_id`,
4`sb_subscriber`,
5`sb_book`,
6 |
|
UNIX_TIMESTAMP(`sb_start`) AS `sb_start`, |
|
|
|
7UNIX_TIMESTAMP(`sb_finish`) AS `sb_finish`,
8`sb_is_active`
9 FROM `subscriptions`
В MySQL есть готовая функция для представления даты-времени в формате UNIXTIME, её мы и использовали в строках 6-7.
MS SQL Решение 3.1.3.b
1CREATE VIEW [subscriptions_unixtime]
2WITH SCHEMABINDING
3AS
4SELECT [sb_id],
5[sb_subscriber],
6[sb_book],
7DATEDIFF(SECOND, CAST(N'1970-01-01' AS DATE), [sb_start])
8AS [sb_start],
9DATEDIFF(SECOND, CAST(N'1970-01-01' AS DATE), [sb_finish])
10AS [sb_finish],
11[sb_is_active]
12 FROM [subscriptions]
В MS SQL Server нет готовой функции для представления даты-времени в формате UNIXTIME, потому в строках 7-10 мы вычисляем UNIXTIME-значение по его определению — т.е. находим количество секунд, прошедших с 1 января 1970 года до указанной даты.
Пояснения относительно опции WITH SCHEMABINDING см. в решении{242} за-
дачи 3.1.3.a{241}.
Oracle Решение 3.1.3.b
1CREATE VIEW "subscriptions_unixtime"
2AS
3SELECT "sb_id",
4"sb_subscriber",
5"sb_book",
6(("sb_start" - TO_DATE('01-01-1970','DD-MM-YYYY')) * 86400)
7AS "sb_start",
8(("sb_finish" - TO_DATE('01-01-1970','DD-MM-YYYY')) * 86400)
9AS "sb_finish",
10"sb_is_active"
11 FROM "subscriptions"
В Oracle (как и в MS SQL Server) нет готовой функции для представления даты-времени в формате UNIXTIME, потому в строках 6-9 мы вычисляем UNIX- TIME-значение по его определению — т.е. находим количество секунд, прошедших с 1 января 1970 года до указанной даты.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 243/545
Пример 28: использование представлений для сокрытия значений и структур данных
Несмотря на кажущуюся простоту, в самом условии этой задачи кроется ловушка, которая находится не столько в области написания SQL-запросов, сколько в области работы с разными представлениями даты-времени.
Если вы создадите описанные выше представления и выберете с их помощью данные, вы увидите, что в разных СУБД они немного различаются. Так, например, дата «12 января 2011 года» преобразуется следующим образом:
MySQL |
MS SQL Server |
Oracle |
1294779600 |
1294790400 |
1294790400 |
Результаты MS SQL Server и Oracle совпадают и отличаются от результата MySQL на 10800 секунд (т.е. 180 минут, т.е. три часа). Причём оба результата — верные. Просто в решении для MySQL не учтена временная зона (в нашем случае UTC+3), а в двух других решениях учтена.
Чтобы получить в MySQL такой же результат, как в MS SQL Server и Oracle, можно воспользоваться функций CONVERT_TZ для преобразования временной зоны:
MySQL Решение 3.1.3.b (вариант с преобразованием временной зоны)
1CREATE VIEW `subscriptions_unixtime_tz`
2AS
3SELECT `sb_id`,
4`sb_subscriber`,
5`sb_book`,
6UNIX_TIMESTAMP(CONVERT_TZ(`sb_start`, '+00:00', '+03:00'))
7AS `sb_start`,
8UNIX_TIMESTAMP(CONVERT_TZ(`sb_finish`, '+00:00', '+03:00'))
9AS `sb_finish`,
10`sb_is_active`
11FROM `subscriptions`
Задание 3.1.3.TSK.A: создать представление, через которое невозможно получить информацию о том, какая конкретно книга была выдана читателю в любой из выдач.
Задание 3.1.3.TSK.B: создать представление, возвращающее всю информацию из таблицы subscriptions, преобразуя даты из полей sb_start и sb_finish в формат «ГГГГ-ММ-ДД НН», где «НН» — день недели в виде своего полного названия (т.е. «Понедельник», «Вторник» и т.д.)
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 244/545