Выборка данных из представления

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

Стандарт не содержит явных ограничений на операции выборки из представления, хотя, как будет видно из приведённых ниже примеров, здесь возможны проблемы[28]. Ссылку на представление в предложении FROM оператора выборки данных можно трактовать как подзапрос – часть источника данных – и выполнять обработку такого оператора в соответствии с этой трактовкой. Однако эта возможность появилась только с принятием стандарта SQL2 и не используется в распространённых СУБД. На практике реализуется концепция выборки данных из представления, изложенная ниже.

Для того чтобы осуществить выборку, система преобразует запрос к представлению (ЗП) в запрос к базовым таблицам (ЗБТ). При этом происходит следующее:

– формируется предложение SELECT ЗБТ, совпадающее с предложением SELECT ЗП с точностью до имён столбцов;

– предложение FROM ЗП преобразуется в предложение FROM ЗБТ путём замены ссылки на представление списком ссылок на его базовые таблицы;

– предикат предложения WHERE ЗП преобразуется в предикат предложения WHERE ЗБТ: все ссылки на столбцы представления заменяются ссылками на соответствующие столбцы базовых таблиц, и выполняется конъюнкция полученного выражения с предикатом предложения WHERE запроса из определения представления;

– если предложения GROUP BY и HAVING содержатся только в определении представления, то они добавляются к предыдущим предложениям ЗБТ;

– если предложения GROUP BY и HAVING содержатся только в ЗП, то в них все ссылки на столбцы представления заменяются ссылками на столбцы соответствующих базовых таблиц, и преобразованные предложения добавляются к ЗБТ.

Замечание. Запрос к представлению порождает ошибку, если операции группирования предусмотрены и в определении представления, и в запросе к нему. Это следствие запрета на вложение агрегатных функций (см. п. 6.2.10).

Примеры 1. Пусть в условиях примера 1 из п. 6.4.1 пользователь обращается к БД с запросом:

SELECT S_LOWER.Snum, S_LOWER.Snam

FROM S_LOWER

WHERE S_LOWER.Ci = ‘Томск’

AND S_LOWER.St = 30;

Система преобразует этот запрос к виду:

SELECT S.Snum, S.Snam

FROM S

WHERE S.Ci = ‘Томск’

AND S.St = 30

AND S.St < 50;

Разумеется, в данном случае последнее сравнение в предикате лишнее. Но если бы пользователь попытался получить через своё представление сведения о поставщиках из Томска со статусом, равным 100, то получил бы пустую таблицу. Именно последнее сравнение обеспечивает защиту данных от несанкционированного просмотра.

Пример 2. В условиях примера 3 из п. 6.4.1 система преобразует предыдущий запрос в следующий запрос к базовым таблицам:

SELECT S.Snum, S.Snam

FROM S, C

WHERE C.Ci = ‘Томск’

AND S.St = 30

AND S.St < 50

AND S.Cnum = C.Cnum;

Пример 3. Запрос к представлению из примера 4 из п. 6.4.1 будет очевидным образом приведён к оператору SELECT, составляющему тело определения представления. Заметим, что в определении представления мы использовали подзапрос в предложении FROM. По-видимому, в настоящее время это возможно лишь теоретически.

На практике существует две возможности: либо определить представление RPT(Pnum, NS, NJ, SumQt) и заменить подзапрос в определении REPORT ссылкой на него, либо откорректировать определение, приведённое в замечании к примеру 4 в п. 6.4.1.

Рассмотрим первый вариант:

CREATE VIEW RPT(Pnum, NS, NJ, SumQt)

AS SELECT Pnum,

COUNT(DISTINCT Snum),

COUNT(DISTINCT Jnum),

SUM(Qt)

FROM SPJ

GROUP BY Pnum;

CREATE VIEW REPORT

AS SELECT P.*, NS, NJ, SumQt

FROM P, RPT

WHERE P.Pnum = RPT.Pnum;

Легко убедиться в том, что описанная выше процедура преобразует запрос SELECT * FROM REPORT; в эквивалентный запрос к базовым таблицам:

SELECT P.Pnum, P.Pnam, P.We, P.Co, P.Ci,

COUNT(DISTINCT SPJ.Snum),

COUNT(DISTINCT SPJ.Jnum),

SUM(SPJ.Qt)

FROM P, SPJ

WHERE P.Pnum = SPJ.Pnum

GROUP BY P.Pnum;

Однако это недопустимый запрос! Он не будет исполнен.

Второй вариант таков:

CREATE VIEW REPORT(Pnum, Pnam, We, Co, Ci, NS, NJ, SumQt)

AS SELECT P.Pnum, P.Pnam, P.We, P.Co, P.Ci,

COUNT(DISTINCT Snum),

COUNT(DISTINCT Jnum),

SUM(Qt)

FROM P, SPJ

WHERE P.Pnum = SPJ.Pnum

GROUP BY P.Pnum, P.Pnam, P.We, P.Co, P.Ci;

Перечислив все столбцы таблицы P в списке группирования, мы не изменили структуру групп, т.к. столбец Pnum является первичным ключом P. Теперь запрос на выборку всех данных из представления REPORT имеет допустимую эквивалентную форму.

Пример 4. Попытаемся получить из только что определённого представления номера деталей, суммарные объёмы поставок которых максимальны:

SELECT Pnum

FROM REPORT

WHERE SumQt = (SELECT MAX(SumQt) FROM REPORT);

Это была бы безупречная формула, если бы таблица REPORT была базовой. Однако исполняющая система преобразует этот запрос в следующую эквивалентную форму:

SELECT P.Pnum

FROM P, SPJ

WHERE SUM(Qt) =

(SELECT MAX(SUM(Qt))

FROM P, SPJ

WHERE P.Pnum = SPJ.Pnum

GROUP BY P.Pnum, P.Pnam, P.We, P.Co, P.Ci)

AND P.Pnum = SPJ.Pnum

GROUP BY P.Pnum, P.Pnam, P.We, P.Co, P.Ci;

Это недопустимая формула. В ней агрегатная функция использована в предикате предложения WHERE и в качестве аргумента агрегатной функции. Варианты правильных формул запроса достаточно очевидны (см. п. 6.2.12, пример 4). Однако нет никаких способов преобразования запроса к представлению в одну из этих формул.

Обновление представления

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

Представление трактуется стандартами языка как таблица. Поэтому в общем случае имя представления может использоваться как имя приёмника данных в основном предложении любого оператора обновления. Однако представление – это виртуальная таблица, «обновить» которую можно, лишь фактически обновив базовые таблицы. Поэтому, обрабатывая оператор обновления представления, система должна на основании определения представления принять решение о том, в какие базовые таблицы и какие именно изменения следует внести.

Существуют типичные ситуации, в которых интерпретация некоторых или всех видов операций обновления представления в терминах базовых таблиц невозможна в принципе. Так, совершенно непонятно, как в терминах базовых таблиц интерпретировать следующий оператор обновления представления REPORT (п. 6.4.1, пример 4)

UPDATE REPORT SET SumQt = 25000 WHERE Pnum = ‘P2’;

Столбец SumQt содержит значения агрегатной функции. Соответствующего ему столбца нет ни в одной базовой таблице.

В ряде случаев операторы обновления представлений могут иметь «подозрительную» интерпретацию. Действительно, что означает приведённое ниже высказывание?

INSERT INTO REPORT

VALUES (‘P12’, ‘пульт’, 500, ‘белый’, ‘Яя’, 3, 5, 200);

Зная определения представления REPORT, можно предположить, что первые пять элементов списка составляют строку таблицы Р. Тогда возможна следующая интерпретация:

INSERT INTO P

VALUES (‘P12’, ‘пульт’, 500, ‘белый’, ‘Яя’);

Однако это только гипотеза. А что делать с остальными тремя элементами? Игнорировать? Но пользователь наверняка имел в виду совсем не такие «обновления». Возможно, он хотел поместить в отчёт дополнительную строку.

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

DELETE FROM REPORT;

Этот оператор имеет вполне логичную интерпретацию в терминах базовых таблиц:

DELETE FROM SPJ;

DELETE FROM P;

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

Существуют и такие типы представлений, при обновлении которых не возникает запутанных или неразрешимых проблем. Примером может служить представление S_LOWER (п. 6.4.1, пример 1). В самом деле, любой оператор обновления этого представления имеет единственную интерпретацию в терминах операций обновления таблицы S. Например,

UPDATE S_LOWER

SET Sname = ‘Игорь’, St = 40, Ci = ‘Яя’

WHERE Snum = ‘S8’;

однозначно интерпретируется как

UPDATE S

SET Sname = ‘Игорь’, St = 40, Ci = ‘Яя’

WHERE Snum = ‘S8’;

Аналогичные интерпретации существуют и для операций вставки/удаления строк представления.

Рассмотренные примеры показывают, что существует два типа представлений: обновляемые и необновляемые или только для чтения. На обновляемые представления можно ссылаться в операторах обновления как на приёмники данных. Исполняющая система преобразует оператор, содержащий такую ссылку, в эквивалентный оператор обновления базовых таблиц. Подобное использование необновляемых представлений бессмысленно. Оператор обновления, ссылающийся на представление только для чтения как на приёмник данных, не имеет однозначной интерпретации в терминах базовых таблиц.

Приведённые примеры являются крайними. Представление S_LOWER может использоваться для обновления так же, как базовая таблица, а REPORT вообще не пригодно для обновления данных. Между этими двумя крайностями существует много обновляемых представлений, более сложных, чем S_LOWER и необновляемых, устроенных значительно проще, чем REPORT. Кроме того, представления могут быть обновляемыми без ограничений, как S_LOWER, или частично обновляемыми. К этим последним применимы не все операции обновления. Проблема обновляемости представлений – это сложнейшая и интереснейшая проблема теории баз данных. Многие важные с практической точки зрения результаты теории обновляемости представлений можно найти в [1, гл. 17]. К сожалению, современный SQL не использует эти результаты. Приведём определение обновляемого представления, соответствующее стандарту SQL2.

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

■ Предложение FROM запроса ссылается на одну и только одну базовую таблицу или представление.

■ Если ссылка ведёт на представление, то оно является обновляемым и не ссылается прямо или косвенно на определяемое представление.

■ В предложении SELECT запроса используются только ссылки на столбцы источника данных. Выражения и агрегатные функции не допускаются. Ни на один столбец нельзя ссылаться более одного раза.

■ Предложение SELECT запроса не содержит спецификации DISTINCT.

■ Подзапрос в предикате предложения WHERE запроса не содержит прямых или косвенных ссылок на определяемое представление.[29]

■ Запрос не содержит предложения GROUP BY или HAVING.

■ Запрос не использует операторы UNION, EXCEPT, INTERSECT.

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

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

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

Пример 1. Определим представление S_LOWER (п. 6.4.1, пример 1) так:

CREATE VIEW S_LOWER

AS SELECT *

FROM S

WHERE St < 50;

Согласно стандарту оно обновляемо. Добавим в него две строки:

INSERT INTO S_LOWER

VALUES (‘S10’, ‘Алексей’, 30, ‘Шегарка’),

(‘S11’, ‘Кузьма’, 400, ‘Бакчар’);

Этот оператор будет исполнен как

INSERT INTO S

VALUES (‘S10’, ‘Алексей’, 30, ‘Шегарка’),

(‘S11’, ‘Кузьма’, 400, ‘Бакчар’);

и таблица S примет следующий вид:

Snum	Snam	St	Ci
S8	Владимир		Томск
S2	Николай		Асино
S5	Константин		Яя
S4	Петр		Рио-де-Жанейро
S10	Алексей		Шегарка
S11	Кузьма		Бакчар
S3	Григорий		Яя
S9	Егор		Яя
S7	Сергей		Асино
S1	Иван		Томск
S6	Иван		Лесото

Если теперь пользователь сделает выборку из своего представления:

SELECT * FROM S_LOWER;

то результат будет таким:

Snum	Snam	St	Ci
S8	Владимир		Томск
S4	Петр		Рио-де-Жанейро
S10	Алексей		Шегарка

Сведения о Кузьме не отражены в представлении. Если пользователь указал значение статуса 400 по ошибке, то он не сможет её исправить никак.

Для блокирования подобных ситуаций стандарт предлагает (но не требует!) использовать в определениях обновляемых представлений параметр WITH CHECK OPTION. Тогда при выполнении операций INSERT и UPDATE система будет автоматически вычислять значения предиката из определения представления на значениях вновь вводимых данных. Операция будет исполнена, если и только если предикат принял значение TRUE на всех строках обновления. Так, представление S_LOWER, определенное в п. 6.4.1, не будет обновлено приведённым выше оператором INSERT, т.е. в таблице Sновые строки не появятся.

Для того чтобы уберечь пользователя хотя бы от некоторых неожиданностей, настоятельно рекомендуется использовать параметр WITH CHECK OPTION в определениях обновляемых представлений, хотя стандарт этого и не требует.

Пример 2. Рассмотрим представление, отображающее только сведения о поставщиках из Томска:

CREATE VIEW S_TOMSK

AS SELECT Snum, Snam, St

FROM S

WHERE Ci = ‘Томск’

WITH CHECK OPTION;

Это обновляемое представление, поэтому параметр WITH CHECK OPTION использован правомерно. Однако, если пользователь попытается добавить в него новую строку:

INSERT INTO S_TOMSK

VALUES (‘S10’, ‘Алексей’, 30);

система преобразует этот оператор так:

INSERT INTO S

VALUES (‘S10’, ‘Алексей’, 30);

Затем она вычислит на вводимой строке значение предиката, и операция не будет выполнена, если (совершенно случайно) в определении таблицы S для столбца Ci значение ‘Томск’не задано по умолчанию.

Таким образом, параметр WITH CHECK OPTION фактически запрещает вставку строк в это представление. Однако, если его убрать из определения, то новые строки можно будет вводить в базовую таблицу через представление. Пользователь никогда их не увидит, но получит возможность складывать мусор в базу данных. Если мы хотим запретить пользователю представления S_TOMSK добавлять строки в базовую таблицу, то приведённое выше определение и есть реализация запрета.

Пример 3. Пусть некоторому пользователю запрещён доступ к кодам поставщиков, а прочие сведения о них он может использовать. Для него создано следующее представление:

CREATE VIEW SUPPL AS SELECT Snam, St, Ci FROM S;

Оно обновляемо по определению, но операция вставки строк в него фактически запрещена, т.к. первичный ключ базовой таблицы не включён в список столбцов.

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