Представления (InterBase и Firebird)
Алексей Ковязин, последнее обновление 13 апреля 2012 года
Тем, кто знаком с языком SQL, не требуются подробные объяснения этого предмета, но для сохранения последовательности изложения мы приведем краткое определение представлений.
ПРЕДСТАВЛЕНИЕ (VIEW) - это виртуальная таблица, созданная на основе запроса к обычным таблицам. Представление реализуется как запрос, хранящийся на сервере и выполняемый каждый раз при обращении к представлению.
Рассмотрим различные варианты использования представлений. Представления позволяют создавать уровни структуры данных, позволяя отделить реализацию хранения данных от их типа. Например, мы можем создать представление, которое выбирает данные из нескольких таблиц. Если клиенты используют это представление вместо прямого обращения к базовым таблицам, разработчик базы данных сможет изменять запрос, лежащий в основе представления, модифицировать его (например, для оптимизации), и клиент ничего не заметит - для него это будет то же самое представление. Помимо того, что они изолируют реализацию хранения данных от пользователя, представления позволяют организовать данные более удобным и простым способом. Проблема «упрощения» структуры данных возникает, когда количество таблиц в базе данных становится достаточно большим, а взаимосвязи между ними - сложными. Представление позволяет исключить (или, наоборот, добавить) часть данных, не необходимых конкретному клиенту базы данных (или - необходимых).
Кроме того, представления позволяют проще организовать безопасность в базе данных InterBase. Некоторые пользователи могут иметь права только на чтение/обновление данных в представлении, но не иметь прав (и даже представления) на таблицы, лежащие в основе представления! Более подробно о безопасности в InterBase см. главу «Безопасность в InterBase: пользователи, их функции и права» (часть 4).
Синтаксис DDL для работы с представлениями
Теперь рассмотрим команды создания и удаления представлений, определяемые DDL (Data Definition Language - подмножество SQL, см. глоссарий). Для создания представления в InterBase следует использовать предложение следующего синтаксиса:
CREATE VIEW имя_представления [(столбец_представления[, столбец_представления…])] AS [WITH CHECK OPTION]; Здесь имя_представления - это имя представления, которое должно быть уникальным в пределах базы данных, а затем идет группа не всегда обязательных имен полей, включенных в представление: [(столбец_представления [, столбец_представления …])]. Обязательно необходимо определить оператор , который выбирает данные, включенные в представление. Необязательный параметр WITH CHECK OPTION мы обсудим чуть позже в части «Изменяемые представления».
Для изменения представления нам придется пересоздать его, т.е. удалить и создать заново. При удалении представления необходимо также удалить все зависимые объекты - триггеры, хранимые процедуры и другие представления. Это одно из основных неудобств работы с представлениями: необходимость пересоздавать дерево объектов, использующих представление (существуют утилиты, позволяющие сделать это проще, например IBAlterView, см. приложение «Инструменты администратора и разработчика InterBase»). Для удаления представления следует использовать следующую команду DDL:
DROP VIEW имя_представления;
Примеры представлений
Вот пример простого представления:
CREATE VIEW MyView AS SELECT NAME, PRICE_1 FROM Table_example;
В этом примере мы создаем представление на основе запроса к таблице Table_example, которую мы рассмотрели в главе «Таблицы. Первичные ключи и генераторы». В этом случае представление будет состоять из двух полей - NAME и PRICE_1, которые будут выбраны из таблицы Table_example без каких-либо условий, т.е. количество записей в представлении MyView будет равно количеству записей в Table_example. Однако представления не всегда так просты. Они могут быть основаны на данных из нескольких таблиц и даже на основе других представлений. Кроме того, представления могут содержать данные, полученные на основе различных выражений - в том числе на основе агрегатных функций. Чтобы более подробно рассмотреть использование этого представления, создадим две таблицы, связанные отношением «один-ко-многим» (часто такое отношение называют «мастер-деталь»). Вот DDL-скрипт для создания этих таблиц:
/\* Таблица: WISEMEN */
CREATE TABLE WISEMEN ( ID_WISEMAN INTEGER NOT NULL, WISEMAN_NAME VARCHAR(80));
/\* Определение первичных ключей */
ALTER TABLE WISEMEN ADD CONSTRAINT PK_WISEMEN PRIMARY KEY (ID_WISEMAN);
/\* Таблица: WISEBOOK */
CREATE TABLE WISEBOOK ( ID_BOOK INTEGER NOT NULL, ID_WISEMAN INTEGER, BOOK VARCHAR(80));
/\* Определение первичных ключей */
ALTER TABLE WISEBOOK ADD CONSTRAINT PK_WISEBOOK PRIMARY KEY (ID_BOOK);
/\* Определение внешних ключей */
ALTER TABLE WISEBOOK ADD CONSTRAINT FK_WISEBOOK FOREIGN KEY (ID_WISEMAN) REFERENCES WISEMEN (ID_WISEMAN);
Итак, мы создали две таблицы - WISEMEN и WISEBOOK, связанные отношением «мастер-деталь» с помощью ограничения внешнего ключа - FOREIGN KEY. Предположим, что в этих таблицах будет храниться информация о великих китайских мудрецах и их трудах. Теперь мы можем создать несколько представлений на основе этих таблиц. Например, создадим представление, показывающее, сколько трудов написал каждый мудрец:
CREATE VIEW WiseBookCount (WISEMAN, HOW_WISEBOOKS) AS SELECT M.WISEMAN_NAME, COUNT(B.BOOK) FROM WISEMEN M, WISEBOOK B WHERE (M.ID_WISEMAN = B.ID_WISEMAN) GROUP BY M.WISEMAN_NAME
Обратите внимание, что при использовании любых вычисляемых выражений, таких как агрегатные функции COUNT (), SUM (), MAX () и т.д., обязательно использовать определенные имена полей представления, т.е. давать имена всем полям, возвращаемым запросом. Как видно из этого примера, эти имена не обязательно должны совпадать с именами полей запроса, но их количество должно совпадать с количеством полей, возвращаемых запросом. Определение того, какое поле, возвращаемое запросом, соответствует какому полю представления, производится по порядковому номеру - первое поле запроса будет отражено в первом поле представления, второе - во втором и т.д.
А если мы хотим узнать, какой из мудрецов написал больше всего книг? Мы попробуем добавить выражение для сортировки - ORDER BY в запрос, лежащий в основе представления. Однако эта попытка будет неудачной: использование сортировки ORDER BY в представлениях не разрешено, и при попытке создать представление с запросом, содержащим ORDER BY, возникнет ошибка. Если мы хотим отсортировать результаты, возвращаемые представлением, нам придется сделать это от имени клиента:
SELECT * FROM WiseBookCount ORDER BY HOW_WISEBOOKS
Выполнение этого SQL-запроса приведет к желаемому результату. Помимо ограничения на использование выражения ORDER BY в представлениях, мы также не можем использовать набор данных, полученный в результате выполнения хранимых процедур, в качестве источника данных (см. главу «Хранимые процедуры» ниже).
Возможно, стоит привести еще один пример, иллюстрирующий применение представлений. Предположим, что нам нужно вывести список мудрецов, чье имя начинается с буквы «К». В этом случае мы используем представление с условиями:
CREATE VIEW WiseMen2 (WISEMAN) AS SELECT M.WISEMAN_NAME FROM WISEMEN M WHERE M.WISEMAN_NAME LIKE ‘K%’
Таким образом, легко создавать представления, которые играют роль постоянно обновляемых поставщиков данных, выбирая их из базы данных по определенным условиям.
Изменяемые представления
Мы упомянули выше, что существует возможность создавать изменяемые представления данных. Это действительно так - есть возможность не только читать данные из представления, но и изменять их!
Есть два способа сделать представление изменяемым. Первый способ применяется, когда представление создано на основе единственной таблицы (или другого изменяемого представления), и все столбцы данной таблицы должны допускать наличие NULL. Таким образом, запрос, на котором основано представление, не может содержать подзапросы, агрегатные функции, UDF, хранимые процедуры, операторы DISTINCT и HAVING. Если все эти условия выполнены, представление автоматически становится изменяемым, т.е. мы можем выполнять для него запросы DELETE, INSERT и UPDATE, которые будут изменять данные в таблице-источнике.
Список условий довольно внушителен и значительно ограничивает применение таких изменяемых представлений, поэтому они используются довольно редко.
Для того чтобы создать изменяемое представление, нарушающее любое из перечисленных выше условий, применяется механизм триггеров. Более подробно о триггерах см. главу «Триггеры» (часть 1). Сейчас мы рассмотрим только общие принципы организации изменения данных в VIEW.
Для реализации обновляемого представления с помощью триггеров необходимо выполнить следующее. Создать 3 триггера для данного представления на события: BEFORE DELETE, BEFORE UPDATE и BEFORE INSERT. Описать в этих триггерах, что должно быть сделано с данными при удалении, обновлении и вставке.
Затем следует использовать данное представление в запросах на изменение - DELETE, INSERT или UPDATE. Когда InterBase получит этот запрос, он проверит, существуют ли соответствующие триггеры для данного представления, т.е. BEFORE DELETE/INSERT/UPDATE. Если триггер для выполняемого действия существует, InterBase вызовет его для изменения реальных данных в таблицах, лежащих в основе представления (хотя это могут быть и другие данные - текстовых ограничений для этих триггеров нет), а затем повторно прочитает строку (или строки), над которыми было выполнено изменение.
Таким образом, существует возможность реализовать сложные цепочки обновления данных в представлениях.
Опция WITH CHECK OPTION упоминалась в описании синтаксиса создания представления. Если эта опция установлена при создании изменяемого представления, каждая строка данных, вставленная или измененная в этом представлении, будет проверяться на условие попадания в представление. Это можно объяснить так: если новая запись, вставленная пользователем или полученная в результате обновления существующей записи, не удовлетворяет условиям запроса, который является поставщиком данных для VIEW, вставка этой записи будет отменена и возникнет ошибка.
Заключение
Несмотря на кажущуюся простоту создания и использования представлений, они предоставляют большие возможности для улучшения организации данных в базе данных и позволяют создавать иерархию организации данных.
Некоторые разработчики приложений баз данных очень часто используют представления в своей работе, другие избегают их применения, мотивируя это сложностью модификации представлений и стремлением сохранить схему базы данных как можно более простой и эффективной. Решать вам, как применять представления в своей работе. Самое главное - помнить о существовании такого мощного инструмента, как представление, и знать, как им пользоваться.