Добро пожаловать!
Здесь вы можете найти ответ на интересующий вас вопрос в отрасли сайтостроения, познакомится ближе с web технологиями и web стандартами.

SQL

Глава 21. Изменение данных через представления *

В этой главе речь пойдет об использовании операторов обновления INSERT, UPDATE и DELETE с представлениями. Применение этих операторов к представлениям является косвенным способом их применения к основным таблицам, на которые ссылаются запросы в представлениях. Однако не все представления можно обновлять. Мы рассмотрим правила, определяющие возможность обновления, и изучим их следствия. Вы также научитесь использовать предложение WITH CHECK OPTION для контроля значений, вводимых в таблицу через представление.

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

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

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

CREATE VIEW Citymatch (custcity, salescity) AS 
   SELECT DISTINCT a.city, b.city
      FROM Customers a, Salespeople b
      WHERE a.snum = b.snum;

Это представление показывает все ссответствия между городами покупателей (custcity) и продавцов (salescity), когда по меньшей мере один покупатель в custcity обслуживается продавцом из salescity.

            
   ===============  SQL Execution Log ==============
  |                                                 |
  | SELECT *                                        |
  | FROM  Citymatch;                                |
  | =============================================== |
  |   custcity    salescity                         |
  |  ---------    ---------                         |
  |  Berlin       San Jose                          |
  |  London       London                            |
  |  Rome         London                            |
  |  Rome         New York                          |
  |  San Jose     Barselona                         |
  |  San Jose     San Jose                          |
  |                                                 |
   =================================================

   Рисунок 21.1 Представление совпадения по городам

Например, одна из строк этой таблицы - London London - показывает, что как минимум один покупатель в Лондоне обслуживается продавцом из Лондона. Эта строка может быть выведена для пары Hoffman - Peel, поскольку и тот, и другой находятся в Лондоне. Однако тот же результат даст совпадение snum покупателя Clemens, тоже находящегося в Лондоне, и его продавца, которым тоже оказался Peel. Поскольку мы выбирали различающиеся комбинации городов, была выведена только одна составная строка с этим значением. Но каким совпадающим значениям из основных таблиц она соответствует?

Даже если не использовать DISTINCT при выборе комбинации городов, вопрос остается открытым, поскольку в представлении окажутся две строки с идентичными значениями столбцов. Эти строки будут неразличимы, поэтому вы по-прежнему не сможете сказать, от каких значений основных таблиц они произошли. (Имейте в виду, что запросы без ORDER BY выдают результаты в произвольном порядке. Это справедливо и для запросов внутри представлений, которые, если строго следовать стандарту, не могут содержать ORDER BY. Следовательно, вы не можете различать строки по их расположению в результатах запроса.) В итоге у нас опять окажутся выходные строки, которые не могут быть однозначно связаны с определенными строками основных таблиц.

Теперь представим, что нужно удалить строку London London из представления. Какой покупатель должен быть удален из таблицы Customers - Hoffman, Clemens или оба? Должна ли СУБД также удалить продавца Peel из таблицы Salespeople? Однозначного ответа нет, поэтому в представлениях такого рода удаления недопустимы. Представление Citymatch является примером представления только для чтения (read-only view), данные которого можно просматривать, но не изменять.

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

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

Если можно применить к представлению оператор обновления, то говорят, что оно является обновляемым (updatable), в противном случае - только для чтения. Следуя этой терминологии, мы будем подразумевать под "обновлением представления" применение к нему одного из трех операторов обновления DML (INSERT, UPDATE и DELETE).

Как определить, является ли представление обновляемым? В теории баз данных этот вопрос еще дискутируется. Основной принцип заключается в том, что обновляемое представление - это представление, над которым может быть выполнена операция обновления, затрагивающая за один раз только одну строку основной таблицы и не влияющая на остальные строки этой и любых других таблиц. Однако практическое применение этого принципа может представлять трудности. Некоторые представления, которые теоретически являются обновляемыми, в действительности нельзя обновить (по крайней мере в рамках стандарта ISO SQL). Возможность обновления представления определяется основным запросом. Этот запрос должен удовлетворять следующим требованиям:

  • Должен выполняться только над одной основной таблицей
  • Не должен содержать агрегатных функций
  • Не должен содержать атрибута DISTINCT
  • Не должен использовать GROUP BY или HAVING
  • Может быть определен над другим представлением, но это представление также должно быть обновляемым
  • Не должен содержать констант, строк или выражений (например, comm * 100) среди выбранных выходных столбцов
  • Для INSERT он должен включать любые столбцы основной таблицы с ограничением NOT NULL, если только не указано другое значение по умолчанию
  • В старых версиях стандарта SQL он не мог содержать подзапросы; некоторые СУБД все еще могут это требовать, хотя большинство - уже нет
  • В него следует включать первичный ключ основной таблицы, это не обязательно, но рекомендуется

Обновляемые представления и представления только для чтения

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

Между обновляемыми представлениями и представлениями толко для чтения есть существенные различия. Зачастую эти представления служат для разных целей. Обновляемые представления обычно испоьзуют аналогично базовым таблицам. Фактически пользователи даже могут не знать, является ли объект запроса базовой таблицей или представлением, - для них это может не иметь никакого значения. Такие представления являются превосходным средством защиты данных, поскольку они позволяют скрыть конфиденциальные или избыточные для данного пользователя части таблицы. (В Главе 22 показано, как разрешить пользователям доступ к представлениям, но не основным таблицам.)

Представления только для чтения позволяют переформатировать и обрабатывать данные. Они предоставляют вам библиотеку сложных запросов, которые можно выполнять многократно, поддерживая актуальность производной информации. Кроме того, хранение результатов таких запросов в таблицах, которые затем можно использовать в других запросах (например, в соединениях) имеет преимущество над простым выполнением запросов. Представления только для чтения могут применяться и для защиты данных. Например, можно разрешить некоторым пользователям просмотр агрегированных данных, таких, как средние комиссионные продавца, но закрыть доступ к отдельным значениям комиссионных.

Определение обновляемых представлений

Рассмотрим некоторые примеры обновляемых представлений и представлений только для чтения.

CREATE VIEW Dateorders (odate, ocount) AS 
   SELECT odate, COUNT (*)
      FROM Orders
      GROUP BY odate;

Это представление допускает только чтение из-за наличия агрегатной функции и GROUP BY.

CREATE VIEW Londoncust AS 
   SELECT *
      FROM Customers
      WHERE city = 'London';

Это представление является обновляемым.

CREATE VIEW SJsales (name, number, percentage) AS 
   SELECT sname, snum, comm * 100
      FROM Salespeople
      WHERE city = 'SanJose';

Это представление допускает только чтение из-за выражения comm * 100. Некоторые СУБД могут разрешать для такого представления удаление или изменение столбцов snum и sname.

CREATE VIEW Salesonthird AS 
   SELECT *
     FROM Salespeople
     WHERE snum IN
        (SELECT snum
            FROM Orders
            WHERE odate = '10/03/1990');

С точки зрения SQL92 это представление является обновляемым. В более старых стандартах SQL к нему можно было обращаться только для чтения из-за наличия подзапроса.

CREATE VIEW Someorders AS 
   SELECT snum, onum, cnum
      FROM Orders
      WHERE odate IN ('10/03/1990', '10/05/1990');

Это представление является обновляемым.

CREATE VIEW SanJosePeople AS
   SELECT snum, sname, 'salesperson'
      FROM Salespeople
      WHERE city = 'San Jose'
   UNION
   SELECT  cnum, cname, '   customer'
      FROM Customers
      WHERE city = 'San Jose';

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

Проверка значений, помещаемых в представления

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

CREATE VIEW Highratings AS 
   SELECT cnum, rating
      FROM Customers
      WHERE rating = 300;

Это представление является обновляемым. Оно просто ограничивает доступ к таблице определенными строками и столбцами. Предположим, что мы вставили следующую строку:

INSERT INTO Highratings VALUES (2018, 200);

Применение оператора INSERT к этому представлению вполне законно. Строка будет действительно вставлена в таблицу Customers. Но оказавшись там, она исчезнет из представления, поскольку значение рейтинга в ней не равно 300. Такое исчезновение обычно представляет собой проблему. Значение 200 могло быть простой опечаткой, но строка уже находится в таблице Customers, и вы даже не можете ее увидеть через представление. Пользователь может не заметить, что он ввел "невидимую" строку, и в любом случае он не сможет удалить ее через представление.

Модификации такого сорта можно предотвратить, включив в определение представления WITH CHECK OPTION. Если бы мы определили Highratings следующим образом:

CREATE VIEW Highratings AS 
   SELECT cnum, rating
      FROM Customers
      WHERE rating = 300
      WITH CHECK OPTION;

то СУБД не допустила бы приведенную выше вставку.

WITH CHECK OPTION действует по принципу "все или ничего" (all-or-nothing). Вы помещаете эту опцию в определение представления, а не оператор обновления, поэтому будут проверяться либо все операторы обновления, применимые к представлению, либо никакие. В общем случае CHECK OPTION следует использовать для всех обновляемых представлений, если только нет каких-либо специфических причин для помещения в таблицу значений, которые не будут отображаться в представлении. В представлениях только для чтения эта опция не нужна.

Предикаты и исключенные столбцы

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

CREATE VIEW Londonstaff AS 
   SELECT snum, sname, comm
      FROM Salespeople
      WHERE city = 'London';

Зачем включать столбец city, если все значения city будут одинаковыми, а название города содержится в имени представления? Посмотрим, что произойдет при попытке вставить строку. Поскольку мы не можем указать city, СУБД введет в этот столбец значение по умолчанию, возможно, NULL. (NULL будет использован в том случае, если мы явно не указали другое значение по умолчанию, подробности см. в Главе 18). Поскольку столбец city не равен 'London', вставленная строка будет исключена из представления.

Итак, какие бы строки вы не вводили через представление Londonstaff в таблицу Salespeople, они будут отсутствовать в самом представлении (если только вы явно не указали 'London' в качестве значения по умолчанию). Пользователю не удастся ввести строки в это представление, хотя он сможет вводить строки в основную таблицу, возможно, сам того не подозревая. Даже если добавить WITH CHECK OPTION к определению представления

CREATE VIEW Londonstate AS 
   SELECT snum, sname, comm
      FROM Salespeople
      WHERE city = 'London'
      WITH CHECK OPTION;

проблема не обязательно будет решена. Мы получим представление, к которому можно применять операторы UPDATE или DELETE, но не INSERT. В некоторых случаях этого достаточно; возможно, пользователям, работающим с этим представлением, не нужно добавлять строки. Но перед созданием подобного представления вы должны убедиться, что имеете дело именно с такой ситуацией.

Хотя столбцы, использованные в предикате, не всегда содержат полезную информацию, обычно их стоит включать в представление. Если вы не хотите видеть их среди результатов, это всегда можно обеспечить в запросе к представлению, а не в самом представлении. Иными словами, представление Londonstaff можно было определить так:

CREATE VIEW Londonstaff AS 
   SELECT *
      FROM Salespeople
      WHERE city = 'London'
      WITH CHECK OPTION;

Этот оператор заполнит представление идентичными значениями city, которые можно просто пропустить в последующем запросе:

SELECT snum, sname, comm
   FROM Londonstaff;

Можно определить еще одно представление на основе Londonstaff, предназначенное только для просмотра, а не для изменения данных, хотя в принципе оно также является обновляемым:

CREATE VIEW Londonstafflookie AS
   SELECT snum, sname, comm
      FROM Londonstaff;

В этом случае не нужно добавлять CHECK OPTION. Основной запрос этого представления не содержит предиката. Идея состоит в том, чтобы использовать Londonstafflookie для отчетов, которые не должны быть загромождены строками 'London'. Само же представление Londonstaff можно использовать для изменения данных о продавцах в Лондоне. Данные об остальных продавцах вы должны по-прежнему обновлять путем прямого обращения к таблике Salepeople или через другой набор представлений.

Проверка представлений, основанных на других представлениях

При использовании CHECK OPTION возникает сложный вопрос - должны ли эти ограничения каскадироваться? Должно ли предложение CHECK OPTION применяться только к предикату того представления, в котором оно непосредственно содержится, или к предикатам всех представлений, содержащихся в данном представлении? В качестве примера рассмотрим следующее представление:

CREATE VIEW Highratings AS 
   SELECT cnum, rating
      FROM Customers
      WHERE rating = 300
      WITH CHECK OPTION;

Предположим, что мы создали другое представление, основанное на первом:

CREATE VIEW Myhighratings AS 
   SELECT *
      FROM Highratings;

Можем ли мы теперь использовать в операторе UPDATE рейтинг, отличный от 300?

UPDATE Myhighratings
   SET rating = 200
   WHERE cnum = 2004;

Ведь это не нарушает предикат в представлении Myhighratings. Ограничение на рейтинг расположено глубже, в представлении Highratings. Ранние стандарты SQL не касались это вопроса. Разумнее всего по умолчанию запретить такие обновления, что и сделано в большинстве СУБД. Вы можете выполнить простой тест, создав приведенные выше представления и попробовав их обновить.

Согласно SQL92 CHECK OPTION может иметь варианты CASCADED или LOCAL. CASCADED означает, что будут проверяться предикаты всех основных представлений, и при их нарушении обновления будут отклонены. LOCAL означает, что проверяется только предикат в текущем представлении. Синтаксис обеих разновидностей очень прост:

WITH CASCADED CHECK OPTION
WITH LOCAL CHECK OPTION

Резюме

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

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

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

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

Представления только_чтение, с другой стороны, могут содержать более допустимые запросы SQL; они могут, следовательно, стать способом хранения запросов, которые вам нужно часто выполнять в неизменной форме. Кроме того, наличие запроса, чей вывод обрабатывается как объект данных, дает вам возможность иметь ясность и удобство при создании запросов в выводе запросов.

Вы можете теперь в представлении предотвращать создание командами модификации строк в базовой таблице, которые не представлены в самом представлении, с помощью предложения WITH CHECK OPTION в определения представления.

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

Вы видели некоторые подходы к этим проблемам. В Главах 20 и 21, мы говорили, что представления имеют прикладные программы защиты. Вы можете дать пользователям возможность обращаться к представлениям, не разрешая в то же время обращаться к таблицам, в которых эти представления непосредственно находятся. Глава 22 будет исследовать вопросы доступа к объектам данных в SQL.

Работа с SQL

1. Какое из этих представлений - модифицируемое?

       #1 CREATE VIEW Dailyorders
             AS SELECT DISTINCT cnum, snum, onum,
             odate
               FROM Orders;

       #2 CREATE VIEW Custotals
             AS SELECT cname, SUM (amt)
                FROM Orders, Customers
                WHERE Orders.cnum = customer.cnum
                GROUP BY cname;

       #3 CREATE VIEW Thirdorders
             AS SELECT *
                FROM Dailyorders
                WHERE odate = 10/03/1990;

       #4 CREATE VIEW Nullcities
             AS SELECT snum, sname, city
                FROM Salespeople
                WHERE city IS NULL
                   OR sname BETWEEN 'A' AND 'MZ';

2. Создайте представление таблицы Продавцов с именем Commissions
   (Комиссионные). Это представление должно включать только поля
   comm и snum. С помощью этого представления можно будет вводить
   или изменять комиссионные, но только для значений между .10 и .20.

3. Некоторые SQL-реализации имеют встроенную константу, представляющую текущую дату,
   иногда называемую " CURDATE ". 
   Слово CURDATE может, следовательно, использоваться в операторе SQL
   и заменяться текущей датой, когда его значение станет доступным, с помощью таких
   команд как SELECT или INSERT. Мы будем использовать
   представление таблицы Заказов с именем Entryorders для вставки строк
   в таблицу Заказов. Создайте таблицу заказов так, чтобы CURDATE
   автоматически вставлялась в поле odate, если не указано другое значение.
   Затем создайте представление Entryorders так, чтобы значения не могли быть указаны.

(См. ответы в Приложении A.)