Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 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 I |
Решение 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 запроса).
|
Oracl |
і |
Решение 3.1.3.a |
I |
e |
|
|||
|
|
|
|
|
1 |
|
CREATE VIEW "subscriptions_anonymous" |
||
2AS
3SELECT "sb id",
4 |
|
"sb book", |
|
5 |
|
"sb start" |
|
6 |
|
"sb |
finish", |
7 |
|
"sb |
is active" |
8 |
FROM |
"subscriptions" |
|
Oracle (как и MySQL) не поддерживает опцию WITH SCHEMABINDING, потому
здесь мы создаём обычное представление с использованием тривиального запроса на выборку.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 255/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', |
7 |
|
UNIX 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], |
7 |
DATEDIFF(SECOND, CAST(N'1970-01-01' AS DATE), [sb start] |
8 |
AS [sb start] |
9 |
DATEDIFF(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) |
|
7 |
|
AS "sb |
start" |
8 |
|
(("sb finish" - TO DATE('01-01-1970','DD-MM-YYYY')) * 86400' |
|
9 |
|
AS "sb |
finish" |
10 |
|
"sb is |
active" |
11 |
FROM |
"subscriptions" |
|
В Oracle (как и в MS SQL Server) нет готовой функции для представления даты-времени в формате UNIXTIME, потому в строках 6-9 мы вычисляем UNIXTIMEзначение по его определению — т.е. находим количество секунд, прошедших с 1 января 1970 года до указанной даты.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 256/545
Пример 29: модификация данных с использованием «прозрачных» представлений
3.2. Модификация данных с использованием представлений
3.2.1.Пример 29: модификация данных с использованием «прозрачных» представлений
Задача 3.2.1.a{246}: создать представление, извлекающее информацию о читателях, переводя весь текст в верхний регистр и при этом допускающее модификацию списка читателей.
Задача 3.2.1.b{251}: создать представление, извлекающее информацию о датах выдачи и возврата книг в виде единой строки и при этом допускающее обновление информации в таблице subscriptions.
Ожидаемый результат 3.2.1.a.
Выполнение запроса вида SELECT * FROM {представление} позволяет получить представленный ниже результат, и при этом выполненные с представлением операции INSERT, UPDATE, DELETE модифицируют соответствующие данные в исходной таблице.
s_id |
s_name |
1 |
ИВАНОВ И.И. |
2 |
ПЕТРОВ П.П. |
3 |
СИДОРОВ С.С. |
4 |
СИДОРОВ С.С. |
Ожидаемый результат 3.2.1.b.
Выполнение запроса вида SELECT * FROM {представление} позволяет получить представленный ниже результат, и при этом выполненные с представлением операции INSERT, UPDATE, DELETE модифицируют соответствующие данные в исходной таблице.
sb_id |
sb_subscriber |
sb_book |
sb_dates |
sb_is_active |
2 |
1 |
1 |
2011-02-12 - 2011-02-12 |
N |
3 |
3 |
3 |
2012-05-17 - 2012-07-17 |
Y |
42 |
1 |
2 |
2012-06-11 - 2012-08-11 |
N |
57 |
4 |
5 |
2012-06-11 - 2012-08-11 |
N |
61 |
1 |
7 |
2014-08-03 - 2014-10-03 |
N |
62 |
3 |
5 |
2014-08-03 - 2014-10-03 |
Y |
86 |
3 |
1 |
2014-08-03 - 2014-09-03 |
Y |
91 |
4 |
1 |
2015-10-07 - 2015-03-07 |
Y |
95 |
1 |
4 |
2015-10-07 - 2015-11-07 |
N |
99 |
4 |
4 |
2015-10-08 - 2025-11-08 |
Y |
100 |
1 |
3 |
2011-01-12 - 2011-02-12 |
N |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 258/545
Пример 29: модификация данных с использованием «прозрачных» представлений
Решение 3.2.1.a{245}.
Создать представление, позволяющее извлечь из базы данных информацию в требуемой форме, легко. Достаточно использовать функцию UPPER для приведения имени читателя к верхнему регистру (строка 4).
MySQL Решение 3.2.1 .а (представление, допускающее только удаление данных)
1CREATE VIEW 'subscribers_upper_case'
2AS
3SELECT 's_id',
4UPPER (' s_name') AS ' s_name'
5FROM 'subscribers'
Проблема заключается в том, что MySQL налагает широкий спектр ограничений11 на обновляемые представления, среди которых есть и использование функций для преобразования значений полей. Мы используем функцию UPPER, что при-
водит к невозможности выполнить операции вставки и обновления с использованием полученного представления (удаление будет работать).
Обойти ограничение на обновление можно следующим образом: в представлении нужно выбирать как «нетронутое» исходное поле, так и его обработанную копию. Этим мы частично нарушим условие задачи, по которому представление должно возвращать только два поля, одноимённые полям исходной таблицы, но зато получим возможность выполнять обновление:
MySQL Решение 3.2.1.а (представление, допускающее удаление и обновление данных)
1CREATE VIEW 'subscribers_upper_case_trick'
2AS
3SELECT 's_id',
4' s_name' ,
5UPPER (' s_name') AS ' s_name_upper'
6FROM 'subscribers'
Теперь у нас уже работают и удаление, и обновление. Вы можете выполнить следующие запросы, чтобы проверить данное утверждение.
MySQL і Решение 3.2.1.а (проверка работы обновления и удаления)
1 |
|
U 'subscribers_upper_case_trick' 's_name' |
= |
'Сидоров А.А.' |
PDATE |
's_id' = 4; |
|
|
|
2 |
SET |
|
|
|
3 |
WHERE |
'subscribers_upper_case_trick' 's_id' = |
10 |
|
4's_id' = 4;
5U
PDATE |
FROM 'subscribers_upper_case' 's id' = |
10; |
|
6 |
SET |
|
|
WHERE
8
К сожалению, реализовать вставку через такое представление не получится: чтобы вставка работала, представление не должно несколько раз ссылаться на одно и то же поле исходной таблицы.
Таким образом, для MySQL поставленная задача решается лишь частично.
В MS SQL Server тоже есть серия ограничений11 12, налагаемых на представления, с помощью которых планируется модифицировать данные. Однако (в отличие от MySQL) MS SQL Server допускает создание на представлениях триггеров, с помощью которых мы можем решить поставленную задачу.
11http://dev.mysql.eom/doc/refman/5.6/en/view-updatability.html
12https://msdn.microsoft.com/en-us/library/ms187956.aspx
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 259/545