Модифицируемые и не модифицируемые представления

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

· относиться к одной и только одной базовой таблице;

· содержит первичный ключ этой таблицы;

· не имеет никаких полей, которые бы являлись агрегатными функциями;

· не содержит DISTINCT, GROUP BY или HAVING в своем определении;

· не использует подзапросы;

· может быть использовано в другом представлении, но это представление также модифицируемо;

· не использует константы, строки, или выражения среди выбранных полей вывода;

· содержит любые поля основной таблицы, которые имеют ограничение NOT NULL, если другое ограничение по умолчанию не определено.

На практике ответ на вопрос о модифицируемости представлений оказывается неоднозначным, и теоретически модифицируемое представление может оказаться не модифицируемым или наоборот. Безусловно, модифицируемыми являются представления, полученные из единственной базовой таблицы простым исключением некоторых ее строк и/или столбцов, обычно называемые «представление-подмножество строк и столбцов».

Различия между модифицируемыми и не модифицируемыми представлениями очевидны. Модифицируемые представления используются в основном точно так же, как и базовые таблицы. Фактически, пользователи не могут даже осознать, является ли объект, который они запрашивают, базовой таблицей или представлением. Это – превосходный механизм защиты конфиденциальной информации или тех частей таблицы, которые не относятся к потребностям данного пользователя. Пользователям предоставляют только интересующие их данные в наиболее удобной для них форме (окно в таблицу или в любое соединение любых таблиц).

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

В PostgreSQL все создаваемые представления по умолчанию являются не модифицируемыми. Чтобы сделать представление модифицируемым (если это возможно!), необходимо создать правило (RULE), которое обеспечивает выполнение требуемых действий над другими таблицами. Вообще, с помощью правил можно исполнять дополнительные действия при выполнении тех или иных команд над любыми таблицами или представления. Создать правило можно с помощью инструкции:

CREATE [ OR REPLACE ] RULE { таблица | представление } AS

ON { SELECT | INSERT | UPDATE | DELETE.}

TO таблица [ WHERE условие ]

DO [ [ALSO] | INSTEAD ] { NOTHING | команда | (команда; команда [;... ]) }

команда::= SELECT | INSERT | UPDATE | DELETE | NOTIFY

где ALSO - указывает на то, что команды, содержащиеся в правиле, будут выполняться как дополнение к исходной команде, INSTEAD - вместо исходной команды, NOTHING - ничего не будет выполняться.

 

Инструкции языка манипулирования данными

Инструкция INSERT

Таблицы создаются инструкцией CREATE TABLE. Эта инструкция создает пустую таблицу – таблицу без строк. Значения вводятся, удаляются или обновляются с помощью инструкций языка манипулирования данными (DML), основными из которых являются инструкции INSERT (вставить), DELETE (удалить), и UPDATE (обновить). Подобно предложению SELECT они могут оперировать как базовыми таблицами, так и представлениями.

Синтаксис инструкцииINSERT:

INSERT [INTO] {базовая_таблица | представление} [(столбец [,...])]

{ DEFAULT VALUES

| VALUES ({ DEFAULT | NULL | выражение } [,...])

| запрос }

[ RETURNING { * | выражение [ [ AS ] имя ] } [,...] ]

Предложение INTO необязательно и просто улучшает читабельность инструкции.

Предложение RETURNING (не стандартное) позволяет вывести на экран (так же, как и при использовании SELECT) значения выражений, построенных на основе вставляемых данных.

Возможны два варианта использования INSERT: без запроса и с запросом. В первом варианте в указанную таблицу вставляется строка со значениями полей, указанными в перечне предложения VALUES (значения), причем i-е значение должно соответствовать i-му столбцу в списке столбцов (как и в инструкции SELECT, порядок следования столбцов может быть произвольным). Столбцы, не указанные в списке, заполняются NULL-значениями или значениями по умолчанию. Если NULL-значениями для такого столбца не допустимы и не указано значение по умолчанию, то транзакция отменяется и выводится сообщение об ошибке. Если в списке VALUES указаны все столбцы модифицируемой таблицы и порядок их перечисления соответствует порядку столбцов в описании таблицы, то список столбцов после имени таблицы или представления можно опустить.

Во втором варианте использования инструкции INSERT сначала выполняется запрос, т.е. по предложению SELECT в памяти формируется рабочая таблица, а потом строки рабочей таблицы загружаются в INSERT-таблицу. При этом i-й столбец рабочей таблицы (i-й элемент списка SELECT) соответствует i-му столбцу в списке столбцов модифицируемой таблицы. Здесь также при выполнении указанных выше условий может быть опущен список столбцов предложения INTO. Инструкция INSERT с запросом чаще применяется для переноса строк из одной таблицы в другую (возможно через представление, но, очевидно, модифицируемое).

Инструкция DELETE

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

DELETE FROM [ ONLY ] {базовая_таблица | представление} [ [ AS ] псевдоним ]

[ USING список ]

[ WHERE { условие | WHERE CURRENT OF курсор } ]

[ RETURNING { * | выражение [ [ AS ] имя ] [,...] } ]

Список в предложении USING аналогичен списку предложения FROM инструкции SELECT.

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

Предложение RETURNING аналогично такому же предложению инструкции INSERT.

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

Для удаления всех строк инструкция DELETE используется редко. Если необходимо удалить все строки в таблице, во многих случаях следует отдать предпочтение инструкции TRUNCATE TABLE, т.к. эта инструкция работает более быстро, удаляя ссылки данной таблицы на физические страницы данных, а не удаляя данные построчно как инструкция DELETE. Но следует помнить, что TRUNCATE TABLE, в отличие от DELETE, не записывает удаляемые строки в журнал транзакций, а значит, удаленные строки восстановить нельзя. Кроме того, TRUNCATE TABLE не применима к таблицам, на которые ссылаются другие таблицы через внешние ключи.

 

Инструкция UPDATE

Инструкция UPDATE используется для изменения существующих значений. В разных диалектах SQL форматы этой инструкции могут отличаться как друг от друга, так и от формата ANSI SQL. В PostgreSQL принят следующий формат (в упрощенном виде):

UPDATE [ ONLY ] {базовая_таблица | представление} [ [ AS ] псевдоним ]

SET { столбец = { выражение | DEFAULT } |

(столбец [,...]) = ({ выражение | DEFAULT } [,...]) } [,...]

[ FROM список ]

[ WHERE условие | WHERE CURRENT OF курсор ]

[ RETURNING { * | выражение [ [ AS ] имя ] [,...] } ]

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

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

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

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

Ход работы

Порядок выполнения работы

 

БД BookShop

 

1. Создайте копию разработанной ранее базы данных, включая содержимое таблиц. Все дальнейшие операции выполнять для этой копии.

 

2. В одну из базовых таблиц, на которую не ссылаются внешние ключи других таблиц, добавить столбец типа uuid и сделать его первичным ключом, отменив существующее в этой таблице ограничение первичного ключа. В этот столбец в каждой строке ввести значение, равное количеству секунд, прошедших от ‘01.01.1900 00:00’ до того момента, когда очередное такое значение вносится в таблицу. Для столбцов, входивших в состав первичного ключа, создать уникальный индекс.