Читать интересную книгу Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ - Хелен Борри

Упорядочение и группирование строк

Определение просмотра не может быть упорядоченным. Добавление предложения ORDER BY вызывает исключение. Следовательно, не имеет смысла использовать в операторе SELECT ключевых слов FIRST и/или SKIP, поскольку они оперируют с упорядоченными наборами.

Спецификация группирования запроса (с использованием допустимого предложения GROUP BY) является нормальной практикой.

Некоторые простые спецификации просмотров

Просмотр может быть создан практически из любой спецификации запроса SELECT. Примеры приведены в следующих разделах.

Вертикальное подмножество столбцов одной таблицы

Таблица JOB в базе данных employee.fdb имеет восемь столбцов: JOB_CODE, JOB_GRADE, JOB_COUNTRY, JOB_TITLE, MIN_SALARY, MAX_SALARY, JOB_REQUIREMENT и LANGUAGE_REQ.

Следующий просмотр возвращает список диапазонов (подмножество столбцов) для всех работ (все строки) таблицы JOB:

CREATE VIEW JOB_SALARY_RANGES AS

SELECT JOB_CODE, MIN_SALARY, MAX_SALARY FROM JOB;

Горизонтальное подмножество строк одной таблицы

Этот просмотр возвращает только столбцы JOB_CODE и JOB_TITLE только для тех работ, где MAX_SALARY меньше $15 000:

CREATE VIEW ENTRY_LEVEL_JOBS AS

SELECT JOB_CODE, JOB_TITLE FROM JOB

WHERE MAX_SALARY < 15000;

Подмножество строк и столбцов для нескольких таблиц

Следующий пример показывает просмотр, который соединяет таблицы JOB и EMPLOYEE.

EMPLOYEE содержит 11 столбцов: EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE, DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY, SALARY и FULL_NAME.

Просмотр возвращает два столбца из таблицы JOB и два столбца из таблицы EMPLOYEE, фильтруя записи таким образом, что записи работников, чья зарплата составляет $15 000 и выше. не попадают в выходной набор:

CREATE VIEW ENTRY_LEVEL_WORKERS AS SELECT

E.JOB_CODE,

J.JOB_TITLE,

E.FIRST_NAME,

E. LAST_NAME

FROM JOB J

JOIN EMPLOYEE E

ON J.JOB_CODE - E.JOB_CODE

WHERE E.SALARY < 15000;

Почему просмотры могут быть полезными

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

* Упрощенные, повторно используемые пути доступа к данным: просмотры позволяют инкапсулировать подмножество данных одной или более таблиц для использования в качестве основы других запросов.

* Настраиваемый доступ к данным: просмотры предоставляют способ для такой адаптации выхода базы данных, что он будет ориентирован на задачу, будет подходить для специфических навыков и требований пользователей и будет сокращать объем перемещаемых по сети данных.

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

* Безопасность данных: просмотры позволяют ограничить доступ к важным или не относящимся к делу частям таблиц.

Привилегии

Поскольку просмотр является объектом базы данных, он требует специальных привилегий для доступа к нему пользователя. Предоставляя привилегии к просмотру, можно дать пользователям очень детализированный доступ к отдельным столбцам и строкам таблиц, не давая им доступ к другим, более чувствительным данным, хранимым в базовых таблицах. В этом случае просмотру предоставляются привилегии к таблицам, а пользователям - привилегии к этому просмотру.

Привилегии владельца

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

* Некоторые просмотры по своей природе являются просмотрами только для чтения (см. разд. "Просмотры только для чтения и изменяемые"). Для формирования просмотра только для чтения создателю нужны привилегии SELECT К каждой базовой таблице.

* Для изменяемых просмотров создателю нужны привилегии ALL К базовым таблицам.

В дополнение к этому владельцы базовых таблиц и других просмотров, к которым обращается создаваемый просмотр, должны предоставить все требуемые привилегии для доступа и модификации этих объектов через просмотр самому просмотру. То есть привилегии к этим базовым объектам должны быть предоставлены просмотру.

Владелец просмотра имеет к нему все привилегии, включая возможность предоставлять привилегии к нему другим пользователям, триггерам и хранимым процедурам.

Привилегии пользователя

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

В случае изменяемых просмотров привилегии INSERT, UPDATE и DELETE должны быть предоставлены всем пользователям, которым нужно выполнять операции DML над базовыми таблицами через просмотр. И наоборот, предоставляйте пользователям только привилегию SELECT, если вы собираетесь создавать просмотр только для чтения.

Если пользователь уже имеет требуемые права к базовым объектам просмотра, он автоматически будет использовать те же права к просмотру.

! ! !

ВНИМАНИЕ! Чем меньше привилегий предоставлено пользователю, тем больше защищены объекты базы данных. При этом потенциально большое количество привилегий в иерархии может вызвать проблемы, если цепь будет разрушена удалением привилегий владельца просмотра. Учитывая привлекательность просмотров в качестве механизма защиты данных от просмотра ненужными людьми, системному администратору следует поддерживать документацию по всем предоставленным привилегиям.

. ! .

Подробное описание привилегий SQL см. в главе 35.

Создание просмотров

Оператором DDL, определяющим запрос, который будет трансформирован в объект просмотра, является CREATE VIEW. Хотя он определяет таблицу (пусть и виртуальную) и позволяет употреблять пользовательские имена для объявления столбцов, его синтаксис не включает никаких определений данных для столбцов. Его структура создается на основе списка столбцов оператора SELECT и таблиц, указанных в предложении FROM, а также в необязательных предложениях JOIN этого оператора.

Все стили соединения (join) и объединения (union) наборов, которые поддерживаются для запросов, также поддерживаются и для просмотров. При этом не существует возможности определять просмотр, который получает выходной набор из хранимой процедуры, недопустимо также включать предложение ORDER BY.

Оператор CREATE VIEW

Синтаксис для CREATE VIEW:

CREATE VIEW имя-просмотра

[(-имя-столбца-просмотра [, имя-столбца-просмотра [,...]])]

AS

<спецификация-выбора> [WITH CHECK OPTION];

Имя просмотра

Имя просмотра уникально идентифицирует просмотр как объект базы данных. Имя не может быть тем же самым, что и имя другого просмотра, таблицы или хранимой процедуры.

Задание имен столбцов просмотра

Задание списка имен столбцов просмотра является необязательным, если не существует дубликатов имен в списке столбцов. По умолчанию будут использованы имена столбцов базовой таблицы (таблиц).

В случае, когда результатом соединения является появление дубликатов имен, то здесь необходимо использовать список, который будет переименовывать столбцы.

Следующий довольно безобразный пример демонстрирует, как могут появиться дубликаты имен столбцов:

CREATE VIEW VJOB_LISTING

AS

SELECT E.*,

J. J0B_C0DE,

J.JOB_TITLE

FROM EMPLOYEE E

JOIN JOB J

ON E.JOB_CODE = J.JOB_CODE ;

ISC ERROR CODE:335544351

unsuccessful metadata update

STORE RDB$RELATION_FIELDS failed

attempt to store duplicate value (visible to active transactions)

in unique index "RDB$INDEX_15"

(ISC ERROR CODE:335544351 неудачное изменение

метаданных невозможно сохранить RDB$RELATION_FIELDS

попытка сохранить дубликат значения (видимый в активных транзакциях) в уникальном индексе "RDB$INDEX_15")

Индекс RDB$INDEX_15 является уникальным индексом для имени отношения и имени поля. Столбец JOB_CODE из таблицы EMPLOYEE уже сохранен для VJOB_LISTING, отсюда и исключение.

Необходимо именовать все столбцы в этом просмотре:

CREATE VIEW VJOB_LISTING (

EMP_NO, FIRST_NAME, LAST_NAME,

PHONE_EXT, HIRE_DATE, DEPT_NO,

EMP_JOB_CODE, /* альтернативное имя */

JOB_GRADE, JOB_COUNTRY, SALARY, FULL_NAME,

JOB_JOB_CODE, /* альтернативное имя * /

JOB_TITLE)

AS

SELECT

E.*,

J. JOB_CODE,

J. JOB_TITLE

FROM EMPLOYEE E

JOIN JOB J

ON E.JOB CODE = J.JOB_CODE ;

Список также является обязательным, если список столбцов содержит какие-либо поля, полученные из выражений. Например, следующее определение будет ошибочным:

CREATE VIEW VJOB_ALTNAMES

AS

SELECT JOB_СODE || 'for ' || JOB_TITLE AS ALTNAME