DRKB Download О проекте Авторы Ссылки Благодарности Пожертвования Реклама

DRKB ➤ Базы данных ➤ Informix ➤ Язык SQL (на примере диалекта Informix)

Язык SQL (на примере диалекта Informix)

01.01.2007

Язык SQL

Основные типы данных. Синтаксис и семантика основных операторов. Методика обеспечения многопользовательского доступа к данным. Операторы с расширенными возможностями. Интеграция языка SQL и универсальных систем программирования.

5.1. Типы данных, доступные в SQL

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

-	какие значения может принимать тот или иной атрибут;

-	какие операции будут выполняться с данным атрибутом;

-	сколько физического места займет одно значение для атрибута.

Прежде всего, следует бороться с "очевидностью". Например, в банковском деле имеется термин "номер счета". Сразу возникает желание использовать для данного атрибута тип "целое". Но может оказаться, что номер счета формируется достаточно специфично - например, он состоит из 9 цифр, средние 3 которых определяют категорию счета. И если в Вашей задаче требуется осуществлять поиск счетов той или иной категории, то реализовать этот поиск для типа "целое" очень непросто, а для типа "символьная строка" - достаточно просто. И, возможно, использовать тип "символьная строка" для данного атрибута предпочительнее, чем тип "целое".

В варианте SQL, реализованным фирмой Informix, имеются следующие типы данных:

INTEGER и SMALLINT - целое и короткое целое. Для их представления используется, соответственно, 4 и 2 байта.

Следовательно, диапазон допустимых значений для INTEGER будет от -2 147 483 647 до 2 147 483 647, а для SMALLINT - от -32 767 до 32 767. Данный тип используется для представления счетчиков, кодов чего-либо и т.д. К недостаткам данного типа данных следует отнести ограниченный набор значений. Впрочем, это не проблема, если Вы уверены, что для Вашего поля допустимый диапазон значений данного типа подходит.

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

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

Объем памяти (число байтов), выделяемых для хранения значений данного типа зависит от компьютера, но обычно составляет 8 байт для типа FLOAT и 4 байта для SMALLFLOAT. При таком представлении точность типа FLOAT составляет 16 десятичных цифр, а SMALLFLOAT - 8 цифр.

Типы FLOAT и SMALLFLOAT имеют одну особенность - из-за внутреннего двоичного представления нельзя гарантировать, что значения данного типа будут представлены точно. Поэтому данный тип нельзя использовать для хранения денежных сумм или других величин, которые нельзя округлять. Для хранения "неокругляемых" значений используются другие типы данных - DECIMAL и MONEY.

DECIMAL(p) - этот тип аналогичен FLOAT, но предназначен для хранения данных с фиксированным числом значащих цифр. Например, если Вы знаете, что некоторое значение всегда измеряется с точностью до пяти знаков, то это как раз тот тип данных, который Вам нужен. Чмсло значащих цифр (параметр 'p') может быть в пределах от 1 до 32. Диапазон допустимых значений для данного типа находится в пределах от 10^-128 до 10^126.

Для представления данных типа DECIMAL(p) используется двоично-десятичное представление. Следовательно, число байтов, необходимое для представления одного значения типа DECIMAL(p) будет равно 1+p/2.

По сравнению с типом FLOAT тип DECIMAL(p) имеет следующие преимущества:

- можно регулировать точность представления;

- размер требуемой памяти зависит от точности;

Но есть у типа DECIMAL(p) и недостатки по сравнению с типом FLOAT:

- операции сортировки и арифметические операции требуют большего, чем для типа FLOAT, времени;

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

DECIMAL (p,n) - этот тип предназначен для хранения данных не только с фиксированным числом значащих цифр, но и с фиксированным числом значащих цифр после запятой. Параметр 'p' задает общее число десятичных цифр в числе, а 'n' - сколько десятичных цифр будет после запятой. Таким образом, данный тип данный позволяет хранить числа, в десятичном представлении которых не более 32 цифр. Размер памяти для хранения одного значения данного типа составляет 1+p/2 байт.

По сравнению с типами INTEGER и FLOAT тип DECIMAL(p,n) имеет те же достоинства и недостатки, что и тип DECIMAL(p).

MONEY (p,n) - этот тип предназначен для хранения денежных величин. Он полностью аналогичен типу DECIMAL(p,n), но отличается от него тем, что в некоторых языках (например, INFORMIX 4GL) существуют специальные способы форматирования для этого типа на основе некоторых внешних по отношению к программе переменных окружения. Это позволяет писать программы, не зависящие от способа написания денжных величин в каждой конкретной стране (например, величина 3000000 будет печататься или как $3,000,000.00 или как 3.000.000,00Рбл).

DATE - этот тип предназначен для хранения дат. По сути, значение этого типа представляет собой число дней, прошедших с 31 декабря 1899 года. Так как это значение может быть отрицательным, то можно хранить и даты до 1899 года. Под значения этого типа выделяется 4 байта, поэтому диапазон допустимых значений очень широк - около 58 000 столетий вперед и назад. Так же как и для типа MONEY, форматирование ввода и вывода для переменных данного типа может быть указано с помощью внешних переменных.

DATETIME - этот тип предназначен для хранения точных моментов времени. Тип DATETIME содержит информацию о годе (YEAR), месяце (MONTH), дне (DAY), часе (HOUR), минуте (MINUTE), секунде (SECOND) и долях секунды (FRACTION). Вы может выбирать нужный диапазон этих значений. Например, если Вам нужен момент времени с точностью до секунды в течении дня, то следует указать тип DATETIME HOUR TO SECOND. Если же Вам нужна информация о событиии с точностью до минуты, но в произвольном году, то требуемый тип должен записываться как DATETIME YEAR TO MINUTE. При указании долей секунды, надо указывать точность представления - от десятых до тысячных. Соответственно, FRACTION(1) указывает время с точностью до десятых, FRACTION(2) - до сотых, а FRACTION(3) - до тысячных.

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

INTERVAL - тип данных для хранения временных интервалов. Значение типа INTERVAL получается, когда, например, из одной даты вычитают другую. Так же как и DATETIME, следует уточнить диапазон возможных значений.

CHAR и CHAR(n) - тип для хранения символьных строк фиксированной длины. Для типа CHAR(n) параметр 'n' задает длину строки. Тип CHAR без параметра является одиночным символом и идентичен типу CHAR(1). Максимальная длина данного типа (значение параметра 'n') равно 32 511. Для хранения данных данного типа всегда отводитя n байт вне зависимости от реальной длины строки.

VARCHAR(m) - короткая (до 255 символов) символьная строка переменной длины. Параметр 'm' задает максимальную длину строки, но не более 255. Для хранения строк переменной длины произвольного размера испльзуеся тип TEXT. При хранении значений типа VARCHAR используется столько байтов, сколько реально занимает строка.

По сравнению с типом CHAR(n), тип VARCHAR имеет то преимущество, что экономится место при хранении строк разной длины. Но такой способ хранения замедляет операции модификации данных.

TEXT - этот тип предназначен для хранения символьных строк произвольной переменной длины. По сравнению с типом VARCHAR тип TEXT имеет повышенные накладные расходы, но не имеет практического ограничения по длине, Например, для сервера Informix DS максимальная длина значения типа TEXT составяет 2 Гигабайта.

BYTE - тип для хранения двоичных объектов произвольного объема. Этот тип данных можно (и нужно) использовать для хранения исполняемых файлов, оцифрованных картинок, звука и т.д. Максимальная длина значения данного типа (для сервера Informix DS ) ограничена 2 Гигабайтами.

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

5.2. SQL-операторы создания схемы базы данных

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

Первым делом надо создать саму базу данных. Для этого используется оператор

CREATE DATABASE <имя базы>

В качестве имени базы данных Вы можете использовать любое имя, состоящее из латинских букв, цифр и символа подчеркивания. Имя должно начинаться с буквы или символа подчеркивания. Если используемые SQL-сервер и операционная система обеспечивают NLS (National Language Support - поддержка национальных языков)), или SQL-сервер поддерживает GLS (Global Language Support – глобальная поддержка языков), то Вы можете использовать для именования и национальные символы. Кстати, большие и маленькие буквы не различаются. Максимальная длина имени составляет 14 символов. Вы не можете создать на одном SQL-сервере две базы данных с одинаковым именем.

Пример:

CREATE DATABASE my_base

В SQL существует понятие "текущая база данных". После того, как Вы создали базу данных оператором CREATE DATABASE, она становится текущей. Все указываемые таблицы, все индексы берутся из текущей базы данных. В каждый момент времени для каждой задачи существует единственная текущая база данных (или таковой не существует вовсе). Это не означает, что Вы не можете обратиться к таблице из другой базы данных, просто при обращении к такой таблице надо кроме имени таблицы, еще указывать и имя базы данны, а если база данных расположена на другом сервере, то и имя сервера базы данных.

Сразу опишем и обратный оператор - оператор удаления базы данных

DROP DATABASE <имя базы>

Пример:

DROP DATABASE my_base

Естественно, удаляемая база должна существовать и Вы должны иметь право на ее удаление (но о правах несколько позднее). Кстати, Вы не можете удалить текущую базу.

Если база данных уже существует, то сделать ее текущей (открыть ее) можно оператором

DATABASE <имя базы>

Пример:

DATABASE my_base

Закрытие текущей базы данных производится оператором

CLOSE DATABASE

Итак, предположим, что база данных создана. Для того, чтобы создавать таблицы используется оператор CREATE TABLE. Его синтаксис

CREATE TABLE <имя таблицы> (
<имя колонки> <тип колонки> [ NOT NULL],
<имя колонки> <тип колонки> [ NOT NULL]

[, ...] )

То есть Вы указываете имя таблицы, а затем в скобках через запятую перечисляете названия полей и их тип. Если Вы хотите сказать серверу, что какое-то поле обязательно должно иметь какое-то определенное значение, то после типа надо добавить слова NOT NULL.

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

CREATE TABLE companies (
company_id SERIAL,
name CHAR(40) NOT NULL,
address CHAR(60)

)

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

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

CREATE TABLE items (
item_id   SERIAL,
company   INTEGER NOT NULL,
name      CHAR(40) NOT NULL,
price     MONEY(20,2)

)

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

Оператор удаления таблицы очень похож на оператор удаления базы данных:

DROP TABLE <имя таблицы>

Если вдруг Вы ошиблись, либо изменились условия задачи, и Вам требуется поменять структуру таблицу, то вовсе не обязательно удалять старую таблицу и вместо нее создавать новую. Для модификации структуры существующей таблицы существует оператор ALTER TABLE, который позволяет изменять все то, что указывается при выполнении оператора CREATE TABLE.

Добавление новых полей в твблицу производится следующим образом:

ALTER TABLE <имя таблицы> ADD
( <имя колонки> <тип колонки> [ NOT NULL]

[, ...] )

Например, если для поставщика товаров (таблица 'companies') надо добавить и контактный телефон, то следует выполнить следующий оператор:

ALTER TABLE companies ADD (phone CHAR(35))

Для удаления ненужных полей надо выполнить другой вариант оператора ALTER TABLE:

ALTER TABLE <имя таблицы> DROP ( <имя колонки> [, ...] )

Существует вариант этого оператора и для модификации поля:

ALTER TABLE <имя таблицы> MODIFY
( <имя колонки> <тип колонки> [ NOT NULL]

[, ...] )

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

5.3. Основные SQL-операторы для доступа и модификации данных

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

Здесь мы приведем несколько упрощенные формы этих операторов, а более полный синтаксис будет рассмотрен в одной из следующих статей. Рассмотрим каждый из этих операторов.

Оператор INSERT вставляет в таблицу новую запись:

INSERT INTO <имя таблицы>(<поле1>, <поле2>, ...)

VALUES (<значение1>, <значние2>, ...)

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

Полю типа SERIAL, если его нет в списке или его значением указано 0, присваивается новое уникальное значение. Если для поля типа SERIAL указано отличное от нуля значение, то СУБД использует указанное значение.

Например, последовательность операторов

INSERT INTO companies (name) VALUES ("АО Рога и Копыта")

INSERT INTO companies (name, address)

VALUES ("ТОО Добро пожаловать", "Энск, 5-е авеню")

INSERT INTO companies (company_id, name)

VALUES (157, "АОЗТ Сделай Сам")

наполнит таблицу companies следующим содержанием

------------------T----------------------T-----------------¬
¦ company_id      ¦ name                 ¦ address         ¦
+-----------------+----------------------+-----------------+
¦               1 ¦ АО Рога и Копыта     ¦ NULL            ¦
¦               2 ¦ ТОО Добро пожаловать ¦ Энск, 5-е авеню ¦
¦             157 ¦ АОЗТ Сделай Сам      ¦ NULL            ¦

L-----------------+----------------------+-----------------+

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

USER - имя пользователя, который выполняет этот SQL-оператор;

TODAY - дату выполнения этого оператора;

CURRENT - момент времени, когда выполняется этот оператор.

Например, если некоторая таблица под названием 'protocol' содержит описание некоторых действий и, в частности, поля when и who, указывающие когда и кто выполнил это действие, занесение новой записи в эту таблицу будет выглядеть так:

INSERT INTO protocol (who, when, ...)

VALUES (USER, CURRENT YEAR TO MINUTE, ...)

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

UPDATE <имя таблицы> SET <имя поля>=<значение> [,....]

[WHERE <условие>]

или

UPDATE <имя таблицы>
SET (<имя поля1>, <имя поля2>, ...) =
(<значение1>, <значение2>, ...)

[WHERE <условие>]

Эти два варианта оператора UPDATE отличаются только синтаксически: в первом варианте явно указывается колонка и сразу после нее и знака '=' пишется новое значение, а во втором варианте модифицируемые поля и их новые значение сгруппированы по отдельности. Мы, в основном, будем пользоваться первым вариантом, как наиболее защищенным от случайных ошибок.

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

В качестве условия могут использоваться логические выражения над константами и полями. В логических выражениях допускается испльзовать операции сравнения >, <, >=, <=, =, <>, !=. Для проверки поля на значение NULL используются логические операции IS NULL или IS NOT NULL. Отдельные логические операции могут быть соединены связками AND, OR, NOT и сгруппированы с помощью скобок. Примеры правильных условий:

name IS NULL

price > 200 OR name = "кеды"

(name IS NULL AND address IS NULL) OR (name = "АО Рога и Копыта")

Использование в SQL логических связок и операций сравнения аналогично логическим выражениям в обычных языках программирования. Представляет интерес использование в операциях сравнения и в логических связках значения NULL (неопределено). Если NULL появляется в операциях сравнения (<, >, <= и т.д.), то результатом этой операции тоже будет NULL. Если применить к NULL операцию отрицания (NOT), то снова получим NULL. Логическое умножение (AND) значений NULL и "ложь" дает "ложь", а NULL и "истина" - NULL. Логическое сложение (OR) значений NULL и "ложь" дает в результате NULL, а NULL и "истина" - "истину".

Например, если на момент вставки записи с информацией о фирме "АО Рога и Копыта" мы не знали ее адреса, то записать ее адрес после можно оператором:

UPDATE companies SET address = "Одесса, п/я 13"

WHERE name = "АО Рога и Копыта"

Далее, если мы хотим поднять минимальную цену на товары до 1000, следует выполнить оператор:

UPDATE items SET price = 1000 WHERE price < 1000

Теперь, немного забегая вперед (мы еще не рассматривали оператор SELECT), напишем оператор UPDATE, который увеличивает в два раза цену на все товары, поставляемые фирмой "АОЗТ Сделай Сам":

UPDATE items SET price = price*2
WHERE company =
(SELECT company_id FROM companies

WHERE name = "АОЗТ Сделай Сам")

Это пример двойного запроса, то есть запроса в запросе. Вначале ищется идентификатор фирмы по ее имени (оператор SELECT), а затем обновляется поле 'price' для всех товаров, поставляемых данной фирмой.

Для того, что бы удалить ненужные записи в таблице, существует оператор DELETE:

DELETE FROM <имя таблицы> [WHERE <условие>]

Использование ключевого слово WHERE, задающего условие на записи, подлежащие удалению, аналогично его использованию в операторе UPDATE. Точно так же, если условие не задано, то удалены будут все записи из таблицы.

Предположим, фирма "АОЗТ Сделай Сам" разорилась, больше не поставляет нам ни одного товара, и мы хотим удалить ее из списка поставщиков. Это может быть сделано оператором:

DELETE FROM companies WHERE name = "АОЗТ Сделай Сам"

Теперь рассмотрим оператор выборки SELECT. На его долю приходится, наверное, более 2/3 всех SQL-операторов используемых в программах. Оператор SELECT выбирает из одной или нескольких таблиц множество значений, которое и является результатом его работы. К сложным, многотабличным вариантам этого оператора мы вернемся позже.

Синтаксис оператора SELECT (сильно упрощенный вариант):

SELECT <имя поля> [,...] FROM <имя таблицы> [WHERE <условие>]

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

Примеры:

SELECT company_id, name, address FROM companies

- результатом этого запроса будет вся таблица 'companies'.

SELECT company_id FROM companies WHERE name = "АО Рога и Копыта"

- этот запрос возвращает одно единственное значение (1), а именно уникальный идентификатор фирмы "АО Рога и Копыта".

SELECT name FROM companies

- возвращает имена всех фирм-поставщиков, имеющихся в нашей базе данных и занесенных в таблицу 'companies'.

SELECT name, price FROM items WHERE company =
(SELECT company_id FROM companies

WHERE name = "АО Рога и Копыта")

- возвращает все названия товаров и их цены, поставляемые фирмой "АО Рога и Копыта".

SELECT name FROM companies WHERE address IS NULL

- возвращает названия тех фирм, у которых неизвестен адрес.

5.4. Управление транзакциями

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

В SQL имеются следующие операторы управления транзакциями:

·	BEGIN WORK - начать транзакцию;

·	COMMIT WORK - успешно завершить транзакцию;

·	ROLLBACK WORK - откатить транзакцию, то есть вернуть базу данных в состояние, которое она имела на момент начала текущей транзакции.

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

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

Операторы управления транзакциями выполняет программа-клиент. Если она обнаруживает какую-то ошибку, то она может выполнить откат транзакции. В самом SQL нет управляющих структур (циклов, условных операторов, операторов перехода), поэтому приведем примеры на языке Informix 4GL, который содержит SQL как подмножество:

IF error            { проверка какого-то события }
THEN
ROLLBACK WORK   { откат транзакции в случае ошибки }
ELSE
COMMIT WORK     { успешное завершене транзакции }

END IF

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

Представим себе, что на компьютере, где исполняется программа-клиент, то есть, где работает пользователь, пропало напряжение. Программа-сервер обнаруживает "гибель" клиента и выполняет оператор ROLLBACK WORK сама, без явного указания от клиента.

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

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

CREATE DATABASE <имя базы> WITH LOG

- создание базы данных с транзакциями, и

CREATE DATABASE <имя базы> WITH LOG MODE ANSI

- создание базы данных с транзакциями в режиме ANSI.

Если база данных была создана с параметром WITH LOG, то сервер начинает для данной базы данных отслеживать транзакции. Выполнение оператора COMMIT WORK, говорит серверу о начале транзакции, которая закончится по оператору COMMIT WORK или откатится по оператору ROLLBACK WORK в состояние на момент выполнения BEGIN WORK.

BEGIN WORK; { Явно задаем начало транзакции }
DELETE .......; { некоторые изменения }
INSERT .......; { данных }

COMMIT WORK; { Оператор успешного завершения транзакции }

Если база данных была создана в режиме ANSI, то есть в операторе CREATE DATABASE было указано WITH LOG MODE ANSI, то оператор BEGIN WORK не нужен. Транзакция открывается автоматически сразу после удачного или неудачного завершения предыдущей. Открытие базы данных автоматически открывает транзакцию.

{ транзакция для ANSI-базы данных }
COMMIT WORK; { завершение предыдущей транзакции }
{ Здесь автоматически начинается следующая транзакция}
UPDATE .......; { некоторые изменения }
INSERT .......; { данных }
COMMIT WORK; { успешное программное завершение транзакции }

{ Здесь автоматически начинается следующая транзакция}

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

5.5. Продвинутые варианты оператора поиска

5.5.1. Поиск по нескольким таблицам

Оператор SELECT может осуществлять поиск сразу по нескольким таблицам. В этом случае после слова FROM в операторе SELECT надо указать таблицы,по которым производится поиск. Если в нескольких полях имеются одноименные поля (например, поле name присутсвует и в таблице companies, и в таблице items), то для устранения неясностей надо перед именем поля указать имя таблицы и символ точка.

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

SELECT name, address, lname, fname FROM companies, persons

WHERE company_id = company

В данном примере не возникает коллизий и запрос будет правильно отработан. Но если мы захотим выдать список товаров и названий фирм, их производящих, то, так как поле name присутствует и в таблице items, и в таблице companies, нам потребуется для поля name указывать таблицу:

SELECT items.name, companies.name FROM persons, companies

WHERE company = company_id

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

Стоит обратить внимание, что при выборке из двух таблиц мы получаем все возможные комбинации значений, при этом из всех таблиц выбираются только те записи, которые удовлетворяют заданному условию. Например, в приведенном выше примере на получение информации о фирмах и людях, в них работающих, мы получим список только тех компаний, для которых в таблице persons есть хотя бы один человек. А если мы хотим в этом запросе получить информацию и о фирмах, для которых мы не знаем ни одного сотрудника? Для этого в разделе FROM оператора SELECT перед таблицей persons надо поставить слово OUTER:

SELECT name, address, lname, fname FROM companies, OUTER persons

WHERE company_id = company

Тогда такой оператор будет работать следующим образом. Он последовательно будет перебирать все записи из таблицы companies. Для каждой записи из companies будет просмотрена таблица persons. Как только будет найдена запись из persons, удосвлетворяющая условию WHERE, в результат добавится новый ряд, сформированный из полей name и address записи из companies и полей lname и fname записи из persons. Если же в persons не будет найдено ни одной записи, то при простом операторе SELECT информация о текущей записи из companies в результат не попадет, а в случае SELECT с OUTER - попадет, а поля lname и fname получат значение NULL:

Таблица companies:
-------------T----------------------T------------------¬
¦ company_id ¦ name                 ¦ address          ¦
+------------+----------------------+------------------+
¦        101 ¦ АО Рога и Копыта     ¦ Одесса, п/я 13   ¦
¦        105 ¦ ТОО Добро пожаловать ¦ Энск, 5-е авеню ¦
¦        107 ¦ АОЗТ Сделай сам      ¦ Городская свалка ¦

L------------+----------------------+-------------------

Таблица persons:
----------T----------T---------¬
¦ company ¦ lname    ¦ fname   ¦
+---------+----------+---------+
¦     101 ¦ Антонов ¦ Сергей ¦
¦     105 ¦ Шапокляк ¦ Алексей ¦
¦     102 ¦ Антонов ¦ Антон   ¦
¦     101 ¦ Бендер   ¦ Остап   ¦

L---------+----------+----------

Результат простого оператора SELECT

SELECT name, address, lname, fname FROM companies, persons

WHERE company_id = company

-----------------------T-----------------T----------T---------¬
¦ name                 ¦ address         ¦ lname    ¦ fname   ¦
+----------------------+-----------------+----------+---------+
¦ АО Рога и Копыта     ¦ Одесса, п/я 13 ¦ Антонов ¦ Сергей ¦
¦ АО Рога и Копыта     ¦ Одесса, п/я 13 ¦ Бендер   ¦ Остап   ¦
¦ ТОО Добро пожаловать ¦ Энск, 5-е авеню ¦ Шапокляк ¦ Алексей ¦

L----------------------+-----------------+----------+----------

Результат оператора SELECT с указателем OUTER

SELECT name, address, lname, fname
FROM companies, OUTER persons

WHERE company_id = company

L----------------------+-----------------+----------+----------

Стоит или не стоит использовать OUTER - зависит от того, что Вы хотите получить в результате оператора SELECT. Относительно приведенного Выше примера, то если бы нас интересовали именно люди, то следовало бы выбрать оператор SELECT в первом варианте, без OUTER. Если же нам было интересно получить информацию о фирмах, а заодно и узнать кто там работает, то следует выбрать второй вариант - c OUTER.

Действие, производимое оператором SELECT при выборке из двух таблиц называют слияние таблиц (join), а если производится слияние с OUTER, то такое слияние таблиц называется внешним (outer join). Внутри одного запроса можно использовать несколько таблиц с OUTER (кстати, такие таблицы называют подчиненными, а таблицы без OUTER - ведущими).

5.5.2. Устранение повторения данных в операторе SELECT

Отношение или таблица- это множество. Следовательно, в них могут содержаться повторяющиеся элементы (одинаковые записи). И если в реальных таблицах это практически никогда не встречается (ведь надо же как-то различать описания разных сущностей!), то в отношении, которое является результатом работы оператора SELECT такие повторения могут возникать достаточно часто. Если в формируемой с помощью оператора SELECT отношении Вам надо избавиться от повторений, то перед списком полей следует поставить ключевое слово DISTINCT или UNIQUE (это синонимы). В этом случае, если в выборке, удовлетворяющей условию WHERE, имеется несколько записей с одинаковыми значениями, то оператор SELECT исключит повторения. Например, если Вам требуется распечатать только номенклатуру поставляемых товаров, без учета того, что один и тот же вид продукта может пставляться разными поставщиками, Вам следует выполнить запрос:

SELECT UNIQUE name FROM items

Если в запросе выбирается два или более поля, то две записи считаются равными, если равны все их составляющие поля.

5.5.3. Вычисления внутри оператора SELECT

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

Выражения над полями

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

К символьным операциям относится конкатенация двух строк и выделение подстроки. Операция конкатенации записывается в виде двух вертикальных черт: '||'. Операция выделения подстроки записывается с помощью квадратных скобок, в которых через запятую указан номер первого и последнего символа в подстроке. Рассмотрим примеры:

SELECT lname[4,9] FROM companies

"Рога и Ко"
" Добро по"

"Т Сделай "

SELECT lname || " " || fname FROM persons

"Антонов Сергей"
"Шапокляк Алексей"
"Антонов Антон"

"Бендер Остап"

Обратите внимание, что в последнем примере результат - это четыре ряда, каждый из которых состоит из одной строки. Если бы мы выполнили запрос

SELECT lname, fname FROM persons

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

В последнем примере мы также использовали и текстовую константу - строку из одного пробела, заключенную в кавычки. Можно использовать строковые константы любой длины и состоящие из любых символов. Если внутри константы надо использовать символ "кавычки", то следует написать его подряд дважды. В символьных выражениях Вы можете использовать и не символьные выражения операция преобразования типов (например, числа 1234 в строку "1234") будет произведена автоматически.

К арифметическим операциям относятся операции сложения ('+'), вычитания ('-'), умножения ('*') и деления ('/'). Пример:

SELECT price*1.2 FROM items

SELECT tax*quantity/1.2 FROM orders

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

Функция определения длины:

LENGTH(<имя поля>) - вычисляет длину строки, которая

LENGTH(<текстовая строка>) содержится в указанном поле записи или указанной текстовой констант;

Временные функции:

DATE (<выражение не типа DATE>) - преобразует выражение типа CHAR, INTEGER или DATETIMEв значение типа DATE;

DAY(<выражение типа DATE или DATETIME>) - по заданному в качестве аргумента значению вычисляет номер дня в месяце;

MDY(<месяц>, <день>, <год>) - по заданным трем целым значением, определяющим номер месяца, дня, года строится значение типа DATE;

MONTH (<выражение типа DATE или DATETIME>) - по заданному в качестве аргумента значению вычисляет номер месяца в годе;

WEEKDAY (<выражение типа DATE или DATETIME>) - по заданному в качестве аргумента значению вычисляет номер дня недели, причем восвкресенью соответсвует значение 0, понедельнику - 1 и т.д.;

YEAR (<<выражение типа DATE или DATETIME>) - по заданному в качестве аргумента значению вычисляет номер года, состоящий из 4-х цифр;

Функции преобразования

HEX (<целое выражение>) - преобразует целое, заданное в качестве аргумента, в строку, представляющую шестнадцатеричную запись данного целого;

ROUND (<числовое выражение, точность>) - округляет с указанной точностью числовое выражение, заданное в качестве аргумента; точность определяет степень одной десятой, до которой надо округлять.

TRUNC (<числовое выражение, точность>) - обрезает незначащие цифры у заданного в качестве аргумента выражения, причем точность определяет смещение влево от запятой последней значащей цифры;

Математические функции:

ACOS(<числовое выражение>) - арккосинус от заданного значения; ASIN(<числовое выражение>) - арксинус от заданного значения;

ATAN (<числовое выражение>) - арктангенс от заданного значения;

COS(<числовое выражение>) - косинус от заданного числа радиан;
SIN(<числовое выражение>) - синус от заданного числа радиан;

TAN (<числовое выражение>) - тангенс от заданного числа радиан;
ABS (<числовое выражение>) - абсолютное значение;

EXP (<степень>) - вычисление заданной экспоненты;

LOGN (<выражение>) - вычисление натурального логарифма;
LOG10 (<выражение>) - вычисление десятичного логарифма;
MOD (<делимое>, <делитель>) - остаток от деления;

POW (<значение>, <степень>) - возведение в степень;

ROOT (<значение>, <степень>) - вычисление корня заданной степени;

SQRT (<числовое выражение>) - квадратный корень;

Функции – псевдополя

CURRENT <диапазон> - возвращает точный момент времени (тип DATETIME), когда происхoдит обработка данного запроса; параметр <диапазон> задает (как и при описании типа DATETIME) требуемое точность - от года до тысячных секунды (например, CURRENT DAY TO SECOND);

TODAY - возвращает дату (тип DATE) выполнения даного запроса;

USER - возвращает имя пользователя (как он зарегестрировался в операционной системе), который выполняет данный запрос.

Следует напомнить про специальное значение NULL - "не определено". Если это значение встретится в выражении, то и все выражение будет иметь значение NULL. Даже умножив нуль на NULL, Вы все равно получите NULL. Конкатенация любой строки с неопределенной строкой (то есть строкой, имеющей значение NULL) тоже в результате даст NULL.

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

Агрегатные функции

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

SELECT MAX(price) FROM items

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

Если мы хотим просто узнать, сколько записей получается в результирующей выборке, то надо использовать агрегатную функцию COUNT(*). Например, для того, чтобы получить число предложений поставок джинсов (сколько раз в таблице items имеется записей с названием товара "джинсы"), надо выполнить запрос

SELECT COUNT(*) FROM items WHERE name="джинсы"

А если мы хотим получить пару значений - минимальная и максимальная цены на кеды, то это достигается следующим запросом:

SELECT MIN(price), MAX(price) FROM items WHERE name="кеды"

Некоторые агрегатные функции перечислены в таблице:

AVG(<имя поля>) - среднее по всем значениям данного поля;
COUNT(*) - количество записей;
MAX(<имя поля>) - максимум из всех значений данного поля;
MIN(<имя поля>) - минимум по всем значениям данного поля;

SUM(<имя поля>) - сумма всех значений данного поля.

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

SELECT name, COUNT(*), MAX(price) FROM items GROUP BY name

Полученные результаты могли бы быть, например, такими:

кеды 2 3000.00
джинсы 4 46000.00

рога 1 999999.85

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

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

SELECT name, COUNT(*), MAX(price) FROM items

GROUP BY name HAVING COUNT(*) > 1

и получим следующие результаты:

кеды 2 3000.00

джинсы 4 46000.00

5.5.4. Логические выражения в условии SQL-операторов

Когда выше обсуждались SQL-операторы и, в частности, раздел WHERE (условие), то были перечислены лишь некоторые возможные способы задания условия на выбираемые записи. В частности, были упомянуты операции сравнения (больше, меньше и т.д.) и логические связки (и, или, не). В условии поиска (в разделе WHERE) операторов SELECT, UPDATE и DELETE можно использовать и более интересные логические выражения.

Сопоставление текстовых полей с образцом

Для сравнения текстовых строк с образцом существуют логические операции LIKE и MATCHES. Операция LIKE обладает несколько меньшими возможностями по сравнению с MATCHES, поэтому рассматриваться не будет. Формат операции MATCHES:

<имя поля> MATCHES <образец>

Образец для оператора MATCHES является текстовой строкой и строится по следующим правилам:

·	специальный символ '*' соответсвует произвольной последовательности из 0 или более символов;

·	специальный символ '?' соответсвует одному произвольному символу;

·	символы внутри квадратных скобок ([...]) позволяют задавать удовлетворяющие образцу символы (например, [абв] или [аеиуюыя]) диапазон символов через дефис (например, [А-Я] или [0-9]), или с помощью символа '^' указывать те символы, которые не удовлетворяют образцу (например, [^абв]);

·	специальный символ '\' отменяет значение специального символа, указанного за '\', например '\' означает именно символ '', а не произвольную последовательность символов;

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

Например, по следующему запросу

SELECT person_id FROM persons WHERE lname MATCHES "[Бб]ендер"

будут выбраны номера записей (значения поля person_id) для лиц с фамилиями "Бендер" и "бендер".

Если нам требуется отобрать все записи о товарах из таблицы items, в названии которых присутствует слово "грибы" (например, "Соленые грибы", "Грибы по-французски", "Грибы-мухоморы маринованные"), то такую задачу решает следующий запрос:

SELECT item_id FROM items WHERE name MATCHES "*[Гг]рибы*"

Операцию MATCHES, как и любую другую, можно использовать вместе с другими логическими операциями путем использования логических связок OR/NOT/AND и скобок:

SELECT item_id FROM items

WHERE (name MATCHES "теннис") AND price > 20.00

Проверка на вхождение в множество

Но самая, пожалуй, мощная логическая операция, которую можно использовать в условии для операторов SELECT, UPDATE и DELETE это операция IN. Эта логическая операция возвращает истину, когда значение слева от слова IN входит в множество значений, указанное справа от слова IN. Множество возможный значений может быть указано явно - через запятую в скобках, а может формроваться другим оператором SELECT:

<выражение> IN (<значение 1>, <значение 2>, ....)

<выражение> IN (<оператор SELECT>)

Например, если мы хотим получить адреса фирм "АО Рога и Копыта" и "ТОО Добро пожаловать", то должны выполнить следующий запрос

SELECT name, address FROM companies

WHERE name IN ("АО Рога и Копыта", "ТОО Добро пожаловать")

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

Если же множество значений формируется динамически, в процессе выполнения запроса, то использование операции IN становится единственно возможным способом. Например, нам надо выполнить запрос "найти названия и адреса всех фирм, поставляющих джинсы". Этот запрос выполняется одним SQL-оператором:

SELECT name, address FROM companies WHERE company_id IN

(SELECT company FROM items WHERE name = "джинсы")

В запросах подобного рода во внутренний оператор SELECT можно добавить слово UNIQUE - это позволит повысить скорость обработки запроса:

SELECT name, address FROM companies WHERE company_id IN

(SELECT UNIQUE company FROM items WHERE name = "джинсы")

Если же требуется найти все фирмы, которые еще не поставляют нам кеды, то такой запрос получается небольшой модификацией предыдущего - заменой "джинсы" на "кеды" и добавлении операции NOT:

SELECT name, address FROM companies WHERE company_id NOT IN (SELECT company FROM items WHERE name = "кеды")

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

DELETE FROM companies WHERE company_id NOT IN

(SELECT company FROM items).

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

BEGIN WORK
DELETE FROM companies WHERE company_id NOT IN
(SELECT company FROM items)
DELETE FROM persons WHERE company NOT IN (SELECT company_id FROM companies)

COMMIT WORK

Если известно, что оператор SELECT возвращает одну запись, то можно вместо слова IN использовать проверку на равенство. Например, если мы хотим получить список самых дорогих товаров, а именно их названия и цену (цена будет для всех одна - максимальная), то надо выплнить запрос:

SELECT name, price FROM items WHERE price = (SELECT MAX(price) FROM items)

Проверка на существование выборки

С помощью логической операции EXISTS можно проверить, возвращает ли тот или иной SELECT-оператор какие-то значения. Соответственно, можно в условии определять только те записи, для которых существует (или не существует) какая-то информация. Формат логической операции EXISTS:

EXISTS (<оператор SELECT>)

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

SELECT name, address FROM companies WHERE
NOT EXISTS (SELECT company FROM items

WHERE items.company=companies.company_id)

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

Сравнивая операцию EXISTS с операцией IN, можно заметить. что одни и те же запросы можно выразить с помощью разных средств (а именно, EXISTS или IN). Какое из средств выбрать, должно определяться логикой задачи и эффективностью исполнения. Последнее, в свою очередь, зависит от реализации и может принципиально различаться для SQL-серверов разных фирм.

Условия "Существует" и "Для всех"

Всем, кто когда-то более или менее серьезно, занимался математикой, известны кванторы "Существует" и "Для всех". Аналогичные операции введены и в SQL. Эти операции должны использоваться после операции сравнения (больше, меньше, равно и т.д.), а в качестве правого операнда должен задаваться оператор SELECT:

<выражение> <операция сравнения> ALL (<оператор SELECT>)

<выражение> <операция сравнения> ANY (<оператор SELECT>)

Соответсвенно, операция ALL возвращает значение "истина", если операция сравнения истинна для каждого значения, возвращаемого оператором SELECT, а операция ANY - хотя бы для одного. Если оператор SELECT не вернул ни одного знчения, то операция ALL возвращает истину, а ANY - ложь. Вместо ключевого слова ANY можно использовать слоово SOME.

Например, если мы хотим определить фирму-производителя самых дешевых кед, то запрос с использованием операции ALL будет выглядеть следующим образом:

SELECT company_id, name FROM companies WHERE
EXISTS (SELECT company FROM items
WHERE companies.company_id = items.company
AND items.name = "кеды"
AND items.price <= ALL
(SELECT price FROM items WHERE name = "кеды")

)

Тот же самый запрос мог быть сформулирован и с помощью агрегатной функции MIN:

SELECT company_id, name FROM companies WHERE
EXISTS (SELECT company FROM items
WHERE companies.company_id = items.company
AND items.name = "кеды"
AND items.price =
(SELECT MIN(price) FROM items WHERE name = "кеды")

)

5.5.5. Слияние двух выборок

Результаты работы двух или более операторов SELECT могут быть объединены в одну выборку с помощью операторов UNION или UNION ALL. Оператор UNION, помещенный между двумя операторами SELECT делает из двух выборок одну, причем повторяющиеся записи отсутствуют в результирующей выборке. Оператор UNION ALL не убирает повторяющиеся записи. Синтаксис:

<оператор SELECT>
UNION [ALL]
<оператор SELECT>
UNION [ALL]
<оператор SELECT>

................

Например, если бы мы имели вместо одной таблицы companies две фирмы-поставщики (suppliers) и фирмы-производители (manufacters), и хотели бы получить выборку с названиями всех фирм, с которыми мы работаем, то требуемый запрос формулируется так:

SELECT name FROM suppliers
UNION

SELECT name FROM manufactures

С помощью операторов UNION и UNION ALL вы можете объединять выборки, возвращающие самые разные поля из разных таблиц. Но при этом все операторы SELECT должны возвращать записи с одинаковым количеством полей и соответсвующие поля во всех операторах SELECT должны иметь приводимые типы данных.

5.5.6. Сортировка выборки

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

Для упорядочивания выборки в оператор SELECT добавляется раздел ORDER BY. Этот раздел указывается псле условия (раздела WHERE). После слов ORDER BY перечисляются имена полей, по которым надо производить сортировку. Поля, по которым надо сортировать, обязательно должны быть в списке выбираемых полей (присутствовать в разделе SELECT).

Например, если мы хотим получить в алфавитном порядке список фирм, с которыми мы работаем, то надо выполнить запрос:

SELECT name FROM companies ORDER BY name

Для упорядочивания в обратном порядке, например, если мы хотим получить список названий и цен на товары в порядке уменьшения цен, то после имени поля в разделе GROUP BY надо поставить ключевое слово DESC:

SELECT name, price FROM items ORDER BY price DESC

Если в разделе ORDER BY указано несколько полей, то вначале производится сортировка по первому полю, затем записи, у которых первые поля совпадают, сортируются по второму полю и т.д.

Если мы желаем отсортировать по некоторому значению, которое не является полем, а, например, вычисляется по значению поля, то для этого в разделе ORDER BY надо указывать не имена полей и не выражения, а номер выражения (начиная с 1) в разделе SELECT. Например, у нас есть таблица result с результатами некоторого физического эксперимента. Пусть в этой таблице есть поле angle со значением некоторого угла, и height - высота чего-то там для заданного угла. Если мы хотим получить выборку, содержащую значения полей angle, height и sin(angle), при этом отсортировать эту выборку в соответствии с синусом угла, то надо выполнить запрос

SELECT angle, height, SIN(angle) FROM results ORDER BY 3

Номер выражения в ORDER BY ничем принципиально не отличается от имени поля. Точно так же можно упорядочивать в порядке убывания с помощью слова DESC и можно перечислять несколько номеров выражений. Кстати, номер выражения можно использовать и для группирования по нему (см. пункт Агрегатные функции.).

5.5.7. Вставка в таблицу нескольких строк одновременно

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

INSERT INTO <имя таблицы> (имя поля1, имя поля2, ...) <оператор SELECT>

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

INSERT INTO strange_companies(name, address)
SELECT name, address FROM companies
WHERE company_id NOT IN

(SELECT UNIQUE company FROM items)

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

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

SELECT name, address FROM companies
WHERE company_id NOT IN (SELECT UNIQUE company FROM items)

INTO TEMP strange_companies

Оператор SELECT ... INTO TEMP автоматически создает таблицу, причем эта таблица имеет столько полей, сколько выражений указано в разделе SELECT. Если в разделе SELECT выражение есть просто значение поля (указно имя поля), то формируемая временная таблица будет иметь поле такого же типа с таким же именем. Если в разделе SELECT стоит составное выражение, то имя поля формируется автоматически по некоторым правилам (здесь они не рассматриваются). В нашем примере, временная таблица strange_companies будет иметь два поля - name и address. Типы этих полей будут идентичны типам полей name и address таблицы companies, то есть CHAR(40) и CHAR(60) соответственно.

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

Кроме оператора SELECT ... INTO TEMP временные таблицы могут быть созданы с помощью оператора создания таблицы - в этом случае он записывается как CREATE TEMP TABLE. Например:

CREATE TEMP TABLE strange_companies (
name CHAR(40),
address CHAR(60)

)

5.6. Использование SQL в языках программирования

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

SQL обычно встраивается в другие языки. Например, существуют продукты Informix ESQL/C, /COBOL, /Ada, /FORTRAN, которые представляют собой препроцессор и библиотеки соответственно для С/С++, COBOL, Ada и FORTRAN. На ESQL/C, например, SQL-оператор, встраиваемый в программу, должен начинаться с символа '$' или со слов 'exec sql’. Пример фрагмента программы на ESQL/C:

$database test;
for (i=0; i++; i<10) {
$insert into my_table values ($i);
$select name into $buf from my_2nd_table where key = i;
printf ("key = %d, name = %s\n", i, buf);

}

Препроцессор переведет приведенный выше фрагмент в "чистую" С-программу, где вместо SQL-операторов будут стоять вызовы фунций, а затем вызовет внешний С-компилятор. С-компилятор соберет конечную программу, используя библиотеку функций, реализующих SQL-запросы.

Другой вариант построения программы, работающий с SQL-сервером - это непосредственное встраивание SQL в язык программирования. Именно по такой схеме построены Informix 4GL и NewEra SQL-операторы являются одними из возможных операторов этого языка. Если приведенную выше программу переписать на Informix-4GL, то она будет выглядеть так:

DATABASE test
FOR I=0 TO 9
INSERT INTO my_table VALUES (i)
SELECT name INTO buf FROM my_2nd_table WHERE key = i
DISPLAY "KEY = ", i, " NAME = ", buf

END IF

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

программист (или конечный пользователь) "рисует" форму или отчет. О связи с SQL-сервером позаботится оболочка, которая будет заполнять форму или отчет данными, автоматически посылая SQL-запросы на сервер.

Курсоры

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

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

всегда можно сказать, какой номер от начала выборки какая запись имеет. Например, нам надо выдать на экран имена и фамилии из таблицы persons в алфавитном порядке. Воспользуемся для этого курсором, а уж потом дадим точный синтаксис описания курсора и правила работы с ним (используем Informix 4GL для описания перемнных и оператора вывода):

{ определяем переменные для считывания данных из курсора }
DEFINE ln LIKE persons.lname
DEFINE fn LIKE persons.fname
{ определяем курсор }
DECLARE my_cursor CURSOR FOR
SELECT lname, fname FROM persons ORDER BY lname, fname
{последовательно перебираем все значения из курсора и печатаем их}
FOREACH my_cursor INTO ln, fn
DISPLAY ln, fn

END FOREACH

Результат работы данного программного фрагмента мог бы быть примерно следующим:

Антонов         Антон
Антонов         Сергей
Бендер          Остап

Шапокляк Алексей

В приведенном простом примере три оператора работают с курсором - DECLARE, FOREACH и END FOREACH. С помощью оператора DECLARE мы объявили курсор, то есть связали его с некоторой выборкой из базы. Затем, с помощью цикла FOREACH ... END FOREACH ) мы перебрали все записи из выборки, соответсвующей курсору и сделали с ними то, что хотели - вывели на экран. С таким же успехом можно было бы вывести эту информацию в отчет или провести статистическую обработку. Причем, в данной выборке мы уже имеем упорядоченность - вначале по фамилии, а для одинаковых фамилий - по имени. Упорядоченность была указана в SELECT-запросе, связанном с данным курсором (раздел ORDER BY).

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

Если при работе с курсором предполагается последовательный доступ к записям из выборки, то есть известно, что каждая запись будет выбрана один раз и в том порядке, который сформирован связанным оператором SELECT, то курсор может быть определен следующим вариантом оператора DECLARE:

DECLARE <имя курсора> CURSOR FOR <SELECT-оператор>

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

Например:

{курсор для перебора названий и адресов фирм}
DECLARE cursor1 CURSOR FOR

SELECT name, address FROM companies ORDER BY name

{курсор для перебора названий товаров и фирм, их производящих}
DECLARE dialog_c CURSOR FOR
SELECT items.name, company.name FROM companies,items

WHERE companies.company_id = items.company ORDER BY items.name

После того, как курсор описан, с ним можно начинать работу. Простейший пример использования курсора был приведен выше цикл FOREACH <имя курсора> .... END FOREACH. Внутри этого цикла производится последовательный перебор записей из выборки.

Последовательный курсор хорош для случаев, когда не надо организовывать диалог с пользователем - например, для формирования отчетов. Если же на основе курсора планируется организовать диалог с пользователем, то последовательным курсором здесь не обойтись - человеку привычно просматривать содержимое базы данных в любых направлениях (как вперед, так и назад). Для таких задач используется скроллируемый курсор, который описывается так:

DECLARE <имя курсора> SCROLL CURSOR FOR <SELECT-оператор>

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

Если курсор является скроллируемым, то к нему, как и к последовательному, можно применять цикл FOREACH ... END FOREACH. Но для того, чтобы организовать произвольный доступ к записям из выборки, связанной со скроллируемым курсором, служат другие операторы.

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

OPEN <имя курсора>

Для доступа к набору записей, связанных с курсором, служит оператор перемещения по выборке FETCH. В зависимости от того, какую запись Вы хотите выбрать (сделать текущей), можно использовать следующие варианты этого оператора:

FETCH NEXT <имя курсора>
FETCH PREVIOUS <имя курсора>
FETCH FIRST <имя курсора>
FETCH LAST <имя курсора>
FETCH CURRENT <имя курсора>
FETCH RELATIVE <смещение> <имя курсора>

FETCH ABSOLUTE <номер записи> <имя курсора>

Смысл ключевых слов NEXT, PREVIOUS и т.д. и, соответственно, выполняемых ими действий, понятен из их перевода с английского:

·	NEXT - перейти на следующую запись в выборке;

·	PREVIOUS - перейти на предыдущую запись в выборке;

·	FIRST - перейти на первую запись выборки;

·	LAST - перейти на последнюю запись выборки;

·	CURRENT - никуда не перемещаясь, перечитать текущую запись;

·	RELATIVE <смещение> - сместиться от текущей записи на <смещение> записей, причем если смещение больше нуля, то сместиться "вниз" - к последней записи, а если меньше - то вверх, к первой записи;

·	ABSOLUTE <номер записи> - перейти на запись, имеющую в данной выборке указанный порядковый номер, причем записи нумеруются с единицы.

После оператора OPEN, кстати, текущей записи нет (текущей записью является несуществующая запись с номером ноль). То есть, сразу после открытия курсора оператор FETCH CURRENT выполнять нельзя.

Слово NEXT в операторе FETCH можно опускать - оно принимается по умолчанию. Вместо ключевого слова PREVIOUS можно использовать синоним PRIOR.

В конкретных языках программирования для задания переменных можно (как и для оператора SELECT) использовать расширение INTO со списком переменных:

... INTO <переменная>, <переменна> ...

Например, если в программе на INFORMIX-4GL нам надо прочитать очередную запись в переменные, то соотвествующий оператор будет выглядеть так:

FETCH NEXT my_cursor INTO my_var1, my_var2

Или для программы на INFORMIX ESQL/C:

$fetch next $my_cursor into $my_var1, $my_var2

После завершения работы с выборкой для данного курсора надо закрыть курсор оператором

CLOSE <имя курсора>

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

FREE <имя курсора>

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

----------------T-------------¬
¦ lname         ¦ fname       ¦
+---------------+-------------+
¦ Антонов       ¦ Сергей      ¦
¦ Шапокляк      ¦ Алексей     ¦
¦ Антонов       ¦ Антон       ¦
¦ Бендер        ¦ Остап       ¦

L---------------+--------------

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

DECLARE my_scroll_cursor SCROLL CURSOR FOR
SELECT lname, fname FROM persons ORDER BY lname, fname

OPEN my_scroll_cr

После этих операторов сформирован набор записей в следующем порядке (порядок уже определен разделом ORDER BY оператора SELECT):

---------T-----------T----------¬
¦ No п/п ¦ lname     ¦ fname    ¦
+--------+-----------+----------+
¦      1 ¦ Антонов   ¦ Антон    ¦
¦      2 ¦ Антонов   ¦ Сергей   ¦
¦      3 ¦ Бендер    ¦ Остап    ¦
¦      4 ¦ Шапокляк ¦ Алексей ¦

L--------+-----------+-----------

Теперь рассмотрим последовательность операторов FETCH и запись, ими выбираемая (порядок выполнения операторов FETCH, очевидно, существенен):

---------------------T------------------------------------¬
¦ Оператор FETCH     | Выбранная запись No (lname, fname) ¦
+--------------------+------------------------------------+
¦ FETCH NEXT         ¦ 1 (Антонов    Антон       )        ¦
¦ FETCH NEXT         ¦ 2 (Антонов    Сергей      )        ¦
¦ FETCH CURRENT      ¦ 2 (Антонов    Сергей      )        ¦
¦ FETCH NEXT         ¦ 3 (Бендер     Остап       )        ¦
¦ FETCH RELATIVE -2 ¦ 1 (Антонов    Антон       )        ¦
¦ FETCH LAST         ¦ 4 (Шапокляк   Алексей     )        ¦
¦ FETCH ABSOLUTE 3   ¦ 3 (Бендер     Остап       )        ¦
¦ FETCH PREVIOUS     ¦ 2 (Антонов    Сергей      )        ¦
¦ FETCH FIRST        ¦ 1 (Антонов    Антон       )        ¦
¦ FETCH RELATIVE 2   ¦ 3 (Бендер     Остап       )        ¦

L--------------------+-------------------------------------

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

Область взаимодействия SQLCA

Сервер базы данных для каждого пользователя (точнее, для каждого процесса) заводит так называемую область взаимодействия SQLCA (SQL Communication Area). Эта область фактически является глобальной структурой данных, состощей из нескольких именованных полей (переменных). Каждая переменная этой структуры содержит ту или иную характеристику последнего выполненного оператора или состояние сервера в целом. Поля структуры SQLCA перечислены в таблице:

-----------T------------T----------------------------------------¬
¦ название ¦ тип        ¦ описание                               ¦
+----------+------------+----------------------------------------+
¦ SQLCODE ¦ целое      ¦ Содержит признак завершения оператора. ¦
¦          ¦            ¦ Может принимать следующие значения:    ¦
¦          ¦            ¦ 0 - признак успешного завершения;      ¦
¦          ¦            ¦ 100 - признак того, что запрос завер- ¦
¦          ¦            ¦     шен нормально, но не было найдено ¦
¦          ¦            ¦     ни одной записи;                   ¦
¦          ¦            ¦ отрицательное значение - признак неу- ¦
¦          ¦            ¦     дачного завершения; содержит код   ¦
¦          ¦            ¦     ошибки.                            ¦
+----------+------------+----------------------------------------+
¦ SQLERRM ¦ строка из ¦ Содержит текстовую строку с описанием ¦
¦          ¦ 71 символа ¦ ошибки в случае, если поле SQLCODE     ¦
¦          ¦            ¦ меньше нуля.                           ¦
+----------+------------+----------------------------------------+
¦ SQLERRD ¦ массив из ¦ Описывает результат выполнения послед- ¦
¦          ¦ 6 целых    ¦ него оператора SQL:                    ¦
¦          ¦            ¦ 1-й элемент - внутренняя информация;   ¦
¦          ¦            ¦ 2-й элемент - содержит сгенерированное ¦
¦          ¦            ¦     сервером значение поля типа SERIAL ¦
¦          ¦            ¦     для оператора INSERT, либо допол- ¦
¦          ¦            ¦     нительный код ошибки;              ¦
¦          ¦            ¦ 3-й элемент - равен количеству обрабо- ¦
¦          ¦            ¦     танных записей;                    ¦
¦          ¦            ¦ 4-й элемент - примерная "стоимость"    ¦
¦          ¦            ¦     выполнения данного оператора;      ¦
¦          ¦            ¦ 5-й элемент - смещение ошибки в текс- ¦
¦          ¦            ¦     товой записи оператор SQL          ¦
¦          ¦            ¦ 6-й элемент - внутренняя информация;   ¦

L----------+------------+-----------------------------------------

Таблица: Поля области взаимодействия (SQLCA) )

Предположим, мы хотим снизить на 20% цену на все товары, описанные в таблице items. А заодно, сообщить пользователю, что цены снижены на столько-то товаров. С ипользованием Informix ESQL/C и области взаимодействия SQLCA фрагмент программы будет выглядеть так:

$update items set price = 0.8*price;

printf ("Цены снижены на %d наименований.\n", sqlca.sqlerrd[2]);

В данном примере (на ESQL/C) количество обработанных записей содержится в sqlca.sqlerrd[2], так как в языке C элементы массива нумеруются с 0 и 3-й элемент массива sqlca.sqlerrd как раз и будет иметь индекс 2.

Теперь рассмотрим пример вставки связанных значений в две таблицы. Например, мы подписали договор с новым поставщиком на поставку некоторого товара. Нам надо вставить информацию о поставщике в таблицу companies, а о товаре в таблицу items. Причем, в таблице items есть поле company, которое указывает на запись в таблице companies. Эта запись определяет фирму-поставщика данного товара (см. описание структуры базы данных в предыдущем номере). Можно, конечно, вставить информацию о фирме, по ее имени найти ее ключ, и это значение использовать для ссылки. Но это плохо - выполняется лишняя операция поиска и нигде не сказано, что имя компании уникально. С помощью области взаимодействия SQLCA данная задача решается достаточно просто (пример на Informix 4GL или NewEra):

DEFINE new_serial INTEGER
............
BEGIN WORK
INSERT INTO companies(name) VALUES ("Наш новый партнер")
LET new_serial = SQLCA.SQLERRD[2] -- ключ (company_id) для новой фирмы
INSERT INTO items (name, company) VALUES ("Новый товар", new_serial)

COMMIT WORK

В данном примере, кстати, объединять два оператора в одну транзакцию (BEGIN WORK ... COMMIT WORK) очень желательно. Если этого не сделать и произойдет какой-то сбой между двумя операторами INSERT, то в базе окажется информация о компании, но не будет информации о товаре. В случае использования транзакции этого можно не опасаться.

Другое очень важное использование области взаимодействия SQL это проверка на то, что в результате оператора выборки (FETCH или SELECT) была найдена хотя бы одна запись. Для этого служит поле SQLCODE области взаимодействия. Данное поле также устанавливается оператором открытия курсора OPEN. Например, если мы хотим после открытия курсора выдать либо первую запись, либо сообщить о том, что записей не найдено, то соответствующий фрагмент программы будет выглядеть примерно так (пример на Informix ESQL/C):

#define NOTFOUND 100
........
$declare x scroll cursor for select name, address from companies; $open x;
/* теперь проверим на наличие хотя бы одной записи в выборке */
if (sqlca.sqlerrd == NOTFOUND) {
/* нет ни одной записи - сообщить об этом */
printf("Записей не найдено.\n");
}
else {
/* показать первую запись и перейти к диалогу */
$fetch x into $name, $address;
printf("Фирма %s расположена по адресу %s.\n", name, address);
.......
/* диалог с пользователем */
}

$close x; /* закрыте курсора по завершению работы */

Примером проверки ситуации, когда пользователь, выбрав последнюю запись, захотел выбрать еще и следующую (несуществующую), может служить следующий фрагмент программы на языке Informix 4GL:

FETCH x INTO name, address
{ не вышли ли мы за границы выборки? }
IF (SQLCA.SQLERRD = NOTFOUND)
THEN { больше записей нет - сообщить об этом }
ERROR "Записей больше нет."

END IF

И, естественно, область взаимодействия SQL активно используется для контроля за ошибками и состоянием базы данных. Для этого надо проверять сотояние сервера SQL после выполнения какого-либо оператора. Надо отметить, что можно управлять реакцией прикладной программы на возникновение ошибок. Например, программа может автоматически завершаться при возникновении ошибки, а может и продолжать работу, сама обрабатывая ошибочную ситуацию (см. следующий параграф). Предположим, что программа работает в режиме, когда обработкой ошибок занимется сама программа и при возникновении ошибки выполнение программы продолжается. Приведем фрагмент программы, проверяющий наличие базы данных, и, если таковая отсутствует, создающий ее (Informix ESQL/C):

$database my_base;
if (sqlca.sqlerrd < 0) {
/* база данных отсутствует, пытаемся ее создать */
$create database my_base;
if (sqlca.sqlerrd < 0) {
/* ошибка при создании базы */
printf("Базы данных нет и она не может быть создана!\n");
exit(0);
}

}

Управление ошибками

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

Указание программе прекращать выполнение при возникновении ошибке задается так:

WHENEVER SQLERROR STOP

Для перехватывания ошибок и обработки их внутри программы служит следующий вариант оператора WHENEVER:

WHENEVER SQLERROR CONTINUE

или

WHENEVER SQLERROR CALL <имя функции>

В случае CONTINUE при возникновении ошибки управление будет передано на следующий оператор, а в случае CALL - вначале будет вызвана указанная функция. Отслеживание возникновения ошибки в этом случае производится тестированием поля SQLCODE области взаимодействия SQL.

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

WHENEVER NOT FOUND { CONTINUE или STOP или <имя функции> }

WHENEVER SQLWARNING { CONTINUE или STOP или <имя функции> }

Для примера приведем простую программу на Informix-4GL с тремя операторами WHENEVER. Первый оператор задает режим продолжения работы (CONTINUE) при вознкновении ошибок, второй - задает реакцию на ненахождение выборки, а третий - говорит о необходимости завершить программу при возникновении ошибки. Любая ошибка между первым и третьим опреатором WHENEVER будет проигнорирована, а ошибка после третьего оператора WHENEVER приведет к аварийному завершнию программы.

MAIN
DEFINE char_num        INTEGER
DATABASE test
WHENEVER SQLERROR CONTINUE   -- первый оператор WHENEVER
DISPLAY "Пытаемся выполнить первый оператор INSERT"
INSERT INTO test_table(color) VALUES ("красный")
IF SQLCA.SQLCODE < 0
THEN
DISPLAY "Ошибка при выполнении оператора вставки:", SQLCA.SQLERRM
END IF
WHENEVER NOT FOUND CONTINUE -- второй оператор WHWNEVER
WHENEVER SQLERROR STOP       -- третий оператор WHENEVER
DISPLAY "Пытаемся выполнить второй оператор INSERT"
INSERT INTO test_table(color) VALUES ("зеленый")
CLOSE DATABASE
DISPLAY "Программа выполнена"

END MAIN

После первого оператора INSERT мы можем проверить, был ли он реально выполнен путем сравнения SQLCA.SQLCODE с нулем. После второго оператора INSERT данная проверка бессмыслена, так как был выполнен оператор WHENEVER ERROR STOP и любая ошибка приведет к завершению программы.

5.7. Программирование сервера базы данных

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

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

5.7.1. Динамический SQL

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

комбинации этих сортировок - по доходу и числу прогулов, по фамилии и стажу в обратном порядке и т.д. Простейший подсчет показывает - вариантов раздела ORDER BY оператора SELECT будет 81 (всего 4 поля, каждое поле допускает три разных способа использования при сотрировке - сортировать в порядке возрастания, убывания, вообще не сортировать - три в четвертой степени и есть 81). Согласитесь, что весьма утомительно указывать все 81 варианта оператора SELECT внутри программы.

В системах разработки приложений (ESQL/C, NewEra, 4GL и т.д.) имеется возможность формировать и исполнять SQL-запроса в процессе работы прикладной программы, в динамике ее исполнения. Отсюда и название - "динамический SQL" (впрочем, автор не считает это название очень удачным). Фактически, это тот же самый язык - SQL, но SQL-оператор не подвергаетсясинтаксическому разбору на этапе компиляции прикладной программы, а в текстовом виде передается непосредственно серверу базу данных.

Имеются следующие операторы для работы с динамическим SQL:

PREPARE <имя оператора> FROM <текстовая строка>

EXECUTE <имя оператора>

FREE <имя оператора>

Оператор PREPARE "подготавливает" SQL-оператор для исполнения. Сам SQL-оператор указывается либо явно в виде текстовой строки, либо через значение текстовой переменной. Оператор PREPARE "связывает" с SQL-оператором имя. Это имя - самое обычное имя в среде разработки. Примеры (Informix-4GL):

PREPARE sel1 FROM "select name from items where price < 1.50"

PREPARE empty_comp FROM

"INSERT INTO companies(name) VALUES ('undef')"

Оператор PREPARE посылает переданный ему текст SQL-серверу. Сервер анализирует переданные ему SQL-операторы и, если нет ошибок, переводит их во внутреннее представление.

После того, как оператор подготовлен, он может быть исполнен оператором EXECUTE. Здесь-то и надо указывать имя, данное подготовленному оператору:

EXECUTE sel1

EXECUTE empty_comp

Один и тот же подготовленный оператор можно исполнять многократно. Оператор FREE освобождает все ресурсы (память), связанные с подготовленным оператором. Выполнять оператор FREE следует тогда, когда подговленный оператор заведомо больше не потребуется:

FREE sel1

Если не выполнить оператор FREE ничего страшного не произойдет, но выделенная для оператора память будет висеть мертвым грузом.

Если на момент подготовки SQL-оператора не все конкретные значения известны, то имеется возможность подставлять эти значения в момент исполнения. Для этого SQL-оператор, подготовленный с помощью PREPARE, должен быть снабжен параметрами. Параметры, значения которых будут определяться в момент исполнения, задаются символом "?":

PREPARE select2 FROM "SELECT price FROM items WHERE name = ?"
PREPARE new_comp FROM

"INSERT INTO companies(name, address) VALUES (?,?)"

Для задания фактических параметров в оператор EXECUTE надо добавить раздел USING:

EXECUTE new_comp USING "Кооператив 'Эх, ухнем'", "Москва, Арбат, 21"

EXECUTE new_comp USING "ИЧП 'Бумеранг'", "Магадан, п/я 777"

Параметры в операторах PREPARE/EXECUTE являются позиционными. То есть при исполнении на место первого вопросительного знака подставляется первое значение в разделе USING, на место второго вопросительного знака - второе значение из раздела USING и т.д.

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

DEFINE string1 CHAR(60)
..........
IF flag
THEN    { сортируем по названию }
       LET string1 = "SELECT name, price FROM items ORDER BY name"
ELSE    { сортируем по цене }
       LET string1 = "SELECT name, price FROM items ORDER BY price"
END IF
PREPARE select_st FROM string1
DECLARE my_cursor CURSOR FOR select_st

..........

Очевидно, можно еще сократить приведенный выше фрагмент, если использовать оператор конкатенации строк.

Помимо возможности формировать запросы не на этапе написания программы, а на этапе ее иполнения, операторы PREPARE/EXECUTE/FREE могут быть полезны еще, как минимум, в двух случаях: для повышения эффективности программы и для исполнения SQL-операторов, которые понятны серверу, но которых нет в системе разработки.

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

(напомним, что все серьезные реляционные СУБД выполнены по схеме клиент-сервер). SQL-сервер исполняет запрос в четыре этапа: (1) анализирует пришедший запрос, (2) выбирает оптимальный способ его исполнения, (3) исполняет и (4) отсылает результаты приложению. Если выполнять несколько одинаковых запросов, то для каждого запроса будeт делаться все четыре этапа. Если же мы подготавливаем запрос с помощью оператора PREPARE, а затем несколько раз исполняем его оператором EXECUTE, то анализ запроса и поиск оптимального способа исполнения будет делаться только один раз - в момент выполнения оператора PREPARE. А на каждое исполнение запроса оператором EXECUTE требуется только два последних этапа - непосредственное исполнение и отсылка результатов.

Другое полезное свойство подготавливаемых операторов - это расширение возможностей среды разработки. Подготавливаемый оператор для приложения существует только как текстовая строка. Его исполнение и синтаксический разбор возложен на SQL-сервер. Поэтому с помощью операторов PREPARE/EXECUTE можно выполнить SQL-запрос, не предусмотренный в синтаксисе среды разоаботки.

Например, Вы имеете среду разработки Informix-4GL старой версии (предположим, 4-й). Она вас вполне устраивает. Но в качестве SQL-сервера используется 7-я версия Informix DS Dynamic Scalable Architecture. Этот сервер "понимает" уже значительно более широкий набор SQL-операторов по сравнению с 4-й версией Informix-4GL. В частности, оператор создания триггеров CREATE TRIGGER (о том, что это такое, будет сказано ниже) может быть исполнен сервером, но отсутствует в продукте Informix-4GL версии 4.10. Используя операторы PREPARE/EXECUTE триггер можно создать:

PREPARE cr_trig FROM "CREATE TRIGGER trig1 ..."
EXECUTE cr_trig

FREE cr_trig

Подготовленные с помощью оператора PREPARE SQL-запросы доступны (видимы) только в данном приложении, а именно между операторами DATABASE ... CLOSE DATABASE. То есть, если вы завершили работу (а точнее, закрыли базу данных оператором CLOSE DATABASE), то подготовленные запросы пропадают. Или, если вы подготовили SQL-запрос, то другой пользователь за другим компьютером не может выполнить подготовленный вами запрос (этот другой пользователь, конечно, может выполнить ту же самую последовательность PREPARE/EXECUTE/FREE, но это будет уже другой SQL-запрос). Однако, существует возможность подготовить для исполнения SQL-запросы так, что эти запросы будут доступны многим пользователям. Но для этого используется уже другой механизм - хранимые процедуры.

5.7.3. Хранимые процедуры

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

Основное назначение хранимых процедур - это функциональное расширение схемы базы данных. Хранимая процедура может содержать некоторый нетривиальный набор операторов, реализующий то или иное логическое действие. Например, администратор банковской системы разработал хранимую процедуру, которая реализует функцию "занести на счет номер X сумму Y". Программист, разрабатывающий приложение пользуется этой процедурой, но не знает КАК именно она это делает. В результате:

·	- когда меняется алгоритм данного действия (например, было принято решение сразу списывать налог), то администратор меняет только эту хранимую процедуру, и все приложению сразу начинают работать по-новому;

·	- никто кроме администратора не знает о том, как устроена схема базы данных и меньше шансов, что кто-то использует эти знания в корыстных целях;

·	- независимо от типа рабочего места одно и тоже действие выполняется гарантированно одними и теми же операторами - выше надежность разработанной системы;

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

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

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

Языки написания хранимых процедур в настоящий момент сильно различается у разных производителей. Для серверов Informix язык для создания хранимых процедур носит название SPL - Stored Procedure Languages. Именно его (точнее, его подмножество), мы и рассмотрим.

Хранимая процедура создается оператором

CREATE PROCEDURE <имя процедуры> (<формальные параметры>)
<тело хранимой процедуры>

END PROCEDURE

Если хранимая процедура будет возвращать какие-то значения, то добавляется раздел RETURNING, в котором перечислены типы возвращаемых значений:

CREATE PROCEDURE <имя процедуры> (<формальные параметры>)
RETURNING <тип 1>, <тип 2> ...
<тело хранимой процедуры>

END PROCEDURE

Хранимая процедура может получать и возвращать произвольное число значений разных типов. Типы входных параметров и выходных значений могут быть любыми, доступными в SQL, за исключением SERIAL, BYTE и TEXT. Вместо типа SERIAL надо указывать тип INTEGER.

Примеры описаний хранимых процедур:

CREATE PROCEDURE incr_account (account_no INTEGER,
quantity MONEY(20,2))
.....

END PROCEDURE

CREATE PROCEDURE add_new_user (name CHAR(20) default NULL)
RETURNING INTEGER {серийный номер нового пользователя}
.....

END PROCEDURE

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

Хранимая процедура, хоторая больше не нужна, может быть удалена. Для этого используется оператор:

DROP PROCEDURE <имя хранимой процедуры>

Например:

DROP PROCEDURE add_new_user

Для исполнения хранимой процедуры используется оператор

EXECUTE PROCEDURE <имя процедуры> (<фактические параметры>)

Например:

EXECUTE PROCEDURE incr_account (213917008, 23000000.00)

Если хранимая процедура возвращает какие-то значения (то есть в ее описании есть раздел RETURNING), то при исполнении этой процедуры в оператор EXECUTE PROCEDURE надо добавить раздел INTO с перечислением имен переменных (иногда вместо INTO используется слово RETURNING):

EXECUTE PROCEDURE <имя процедуры> (<фактические параметры>)

INTO <имя переменной>, ...

Например:

EXECUTE PROCEDURE add_new_user ("Прутков") INTO user_id

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

EXECUTE PROCEDURE incr_account (123456789, 1500000.00)
EXECUTE PROCEDURE incr_account
(account_no=123456789, quantity=1500000.00)
EXECUTE PROCEDURE incr_account

(quantity=1500000.00, account_no=123456789)

Если при исполнении хранимой процедуры значения каких-либо фактических параметров не указаны, то используются значения по умолчанию. Значения по умолчанию задаются при описании хранимой процедуры с помощью ключевого слова DEFAULT. Например, в описанной выше процедуре add_new_user для параметра name задано значение по умолчанию - NULL.

Язык хранимых процедур

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

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

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

В разделе исполняемых операторов могут использоваться любые операторы из таблицы 1, а также любой из SQL-операторов, за исключеием тех, которые создают, удаляют, открывают или закрывают базу данных. При обращении к хранимой процедуре управление передается первому оператору в ее теле. Исполнение оператора RETURN завершает хранимую процедуру и возвращает исполнение в то место, откуда хранимая процедура была вызвана. Хранимая процедура завершаетя также и при достижении оператора END PROCEDURE. При этом никаких значений в вызывающую программу не возвращается.

В качестве примера рассмотрим реализацию хранимых процедур, приведенных при рассмотрении оператора CREATE PROCEDURE:

CREATE PROCEDURE incr_account (account_no INTEGER, quantity MONEY(20,2))
UPDATE accounts SET value = value + quantity
WHERE account_id = account_no

END PROCEDURE

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

CREATE PROCEDURE add_new_user (name CHAR(20) default NULL)
       RETURNING INTEGER {серийный номер нового пользователя}
       DEFINE user_id INTEGER;
       INSERT INTO users(lname) VALUES (name);
       LET user_id = SQLCA.SQLERR[2];
       RETURN user_id

END PROCEDURE

Рассмотрим основные операторы, имеющиеся в языке хранимых процедур Informix SPL (SPL - это аббревиатура отStored Procedure Language):

CALL - то же самое, что и EXECUTE PROCEDURE.

CONTINUE - продолжить выполнение цикла. После этого слова надо указать какой именно цикл надо продолжить выполнять -FOR/WHILE/FOREACH. Примеры:

CONTINUE FOR
....

CONTINUE FOREACH

DEFINE - определить внутреннюю переменную. Для каждой переменной надо задать ее имя и ее тип:

DEFINE <имя переменной> <тип>

Примеры:

DEFINE user_id INTEGER

DEFINE comp_name CHAR(40)

EXIT - прекратить выполнение цикла. После этого слова надо указать какой именно цикл надо прекратитьвыполнять - FOR/WHILE/FOREACH. Примеры:

EXIT WHILE
....

EXIT FOR

FOR - цикл со счетчиком. Аналогичен оператору цикла со счетчиком в других языках программирования. Значения шага, начального и конечного значений вычисляются заранее, до начала выполнения операторов тела цикла:

FOR <переменная> = <целое> TO <целое> STEP <целое>
<операторы>

END FOR

Если шаг (STEP) не указан, то он принимается равным 1 если начальное значение меньше конечного, и -1, если нет. Пример:

FOR i = 1 TO 14
....

END FOR

IF - условный оператор. Проверяет условие и, если оно верно, выполняет операторы после слова THEN. Если не верно, и присутствует раздел ELSE - то выполняются операторы после слова ELSE:

IF <условие> THEN <операторы> END IF
IF <условие>
THEN <операторы>
ELSE <операторы>

END IF

Если после слова ELSE по логике должен следовать другой оператор IF, то такое сочетание ELSE IF ... END IF END IF можно записывать более компактно: ELIF ... END IF. Пример:

IF j > 20
THEN RETURN j
ELIF j > 10
THEN RETURN 10
ELSE RETURN 0

END IF

LET - оператор присваивания. Вычисляет выражение и присваивает его значение указанной переменно:

LET <имя переменной> = <выражение>

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

LET i = 0

LET str1 = "абвгдежз"

RAISE EXCEPTION - возбудить ошибку. Если на данную ошибку не стоит реакция (нет соответствующего оператора ON EXCEPTION), то исполнение хранимой процедуры завершается и в вызвавшую программу "передается" данная ошибка:

RAISE EXCEPTION <номер ошибки>

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

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

RETURN <выражение 1>, ....

SYSTEM - позволяет выполнить внешнюю команду. Имя этой внешней команды передается операционной системе, то есть это может быть или встроенная команда ОС, или исполнимый файл:

SYSTEM <символьная строка>

SYSTEM <имя символьной переменной>

Например, можно выполнить командный файл my_cmd:

SYSTEM "/usr/local/bin/my_cmd"

WHILE - цикл с завершением по условию. Аналогичен циклу WHILE в других языках программирования:

WHILE <условие>
<операторы>

END WHILE

Пример:

LET i = 1
WHILE i = 1
EXECUTE PROCEDURE my_proc RETURNING I

END WHILE

BEGIN ... END - блок операторов. В блоке операторов можно определять и использовать локальные переменные. Допустимые любые другие операторы SPL. Пример:

BEGIN
   DEFINE i CHAR(20)
   LET i = "Ну, погоди!"
   IF ... THEN
      BEGIN
           DEFINE i INTEGER
           LET i = 0
           ....
      END
   END IF

END

5.7.4. Триггеры

Идея триггеров

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

Для подобных задач в SQL введено понятие триггера, Триггер - это механизм, который автоматически выполняет некоторый набор SQL-операторов когда происходит некоторое событие. То есть триггер задается парой "событие-действие". Событиями, на которые можно установить триггер, являются модификации данных, то есть операторы DELETE, INSERT, UPDATE. Причем, триггер связан с конкретной таблицей. То есть событием, вызывающим триггер (триггерным событием) является выполнение операторов удаления, вставки или модификации конкретной таблицы.

Триггер хранится как объект в базе данных, то есть принадлежит схеме базы данных. )

Создание и удаление триггера

Для создания и удаления триггеров, как и для таблиц, и для хранимых процедур, используются операторы CREATE и DROP.

CREATE TRIGGER <имя> <событие> <действие>

DROP TRIGGER <имя>

Имя триггера - обычное имя в SQL. Главное, что бы оно было уникальным. Событие указывает момент, когда триггер срабатывает. Триггеры бывают трех типов - на вставку нового ряда в таблицу, удаление ряда и модификацию каких-либо полей. Для каждой таблицы могут существовать все три типа триггеров, как, впрочем, и не существовать ни одного. При этом, если для каждой таблицы может существовать только по одному триггеру на вставку и удаление, то триггеров на обновление можжет быть несколько в зависимости от того, какие поля обновляются (но не может быть двух триггеров на обновление одного и того же поля).

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

INSERT ON <имя таблицы>
DELETE ON <имя таблицы>
UPDATE ON <имя таблицы>

UPDATE OF <поле>, <поле>, ... ON <имя таблицы>

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

{ триггер на вставку нового документа }
CREATE TRIGGER trig1

INSERT ON documents ....

{ триггер на удаление информации о фирме }
CREATE TRIGGER del_trig

DELETE ON companies ....

{ триггер на обновление цены товара }
CREATE TRIGGER upd_price

UPDATE OF price ON items ....

{ триггер на изменение имени или фамилии человека }
CREATE TRIGGER upd_name

UPDATE OF lname, fname ON persons ....

Теперь рассмотрим, как описываются действия триггера. Каждое действие состоит из описания того, что выполняется единожды перед началом исполнения оператора, вызвавшего событие для триггера (раздел BEFORE), для каждого ряда (раздел FOR EACH ROW) и после исполнения оператора (раздел AFTER).

CREATE TRIGGER .... ON ...
BEFORE <операторы>
FOR EACH ROW <операторы>

AFTER <операторы>

Можно использовать произвольное сочетание из разделов BEFORE, FOR EACH ROW и AFTER, главное, что бы был хотя бы один раздел. В качестве операторов для триггеров могут быть использованы всего четыре типа SQL-операторов - оператор вставки (INSERT), удаления (DELETE), обновления (UPDATE) и выполнения хранимой процедуры (EXECUTE PROCEDURE). Очевидно, что наличие в этом списке оператора вызова хранимой процедуры позводяет сделать триггер сколь угодно сложным. Эти SQL-операторы должны разделяться запятой и находиться в круглых скобках.

Примеры:

CREATE TRIGGER trig1 DELETE ON persons
AFTER ( UPDATE tab2 SET x=x+1,

UPDATE tab3 SET y=y-1 )

CREATE TRIGGER upd_trig UPDATE OF name ON companies
FOR EACH ROW ( EXECUTE PROCEDURE proc1 )

AFTER ( EXECUTE PROCEDURE proc2 )

Рассмотрим поведение последнего триггера (upd_trig) более подробно. Пусть таблица companies имеет следующую структуру и состоит из следующих записей:

CREATE TABLE companies (
   company_id SERIAL    { уникальный идентификатор фирмы }
   name        CHAR(40) { название фирмы }
   address     CHAR(60) { адрес фирмы }

)

+------------+----------------------+------------------+

При такой структуре данной таблицы и при таком ее содержании оператор

UPDATE companies
SET name = "ИЧП Мастер Безенчук"

WHERE company_id = 101

приведет к исполнению триггера upd_trig. Причем, так как модифицироваться будет одна запись, то и процедура proc1, и proc2 будут исполнены по одному разу, причем вначале proc1, а затем proc2. Раздел AFTER и, соответсвенно, процедура proc2, будут исполнены уже после внесения изменений в базу данных. Но если оператор UPDATE модифицирует несколько записей, то процедура proc1 будет исполнена несколько раз (по одному разу для каждой модифицируемой записи), а процедура proc2 - только один раз - после внесения всех изменений. Так, оператор

UPDATE companies
SET name = "ТОО Льдинка"

WHERE company_id > 103

изменит название у двух компаний, то есть в одном запросе будут модифицированы поля name у двух записей. В результате, процедура proc1 будет исполнена дважды, а затем один раз будет вызвана процедура proc2. Если в операторе UPDATE предполагается изменение поля name, но ни одна запись не была модифицирована (не удовлетворила условию в разделе WHERE, например), то раздел FOR EACH ROW триггера не будет выполнен ни разу, тогда как разделы BEFORE и AFTER все равно сработают. Так, следующий оператор исполнит процедуру proc2 и ни разу не выполнит proc1:

UPDATE companies
SET name = NULL

WHERE company_id < 57

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

UPDATE companies
SET address = "Москва, Бутырка"

WHERE company_id = 101

Имена для старого и нового значений записи

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

В механизме триггеров предусмотрена возможность получения значений полей модифицируемой записи. Причем, можно получить значение записи как до модификации (старого значения), так и после (нового значения). Для этого, раздел "действие" в описании триггера надо начать с задания связанных имен записи:

REFERENCING OLD AS <связанное имя для старого значения>

REFERENCING NEW AS <связанное имя для нового значения>

Указывать связанные имена можно в любом порядке. Если какое-то из имен не нужно (например, нам не требуется имя для старого значения), то его можно не указывать. Слово AS можно опускать. )

Примеры использования связанных имен:

CREATE TRIGGER del_trig DELETE ON items
REFERENCING OLD del_rec

FOR EACH ROW ( UPDATE tab2 SET total=total-del_rec.price )

CREATE TRIGGER upd_trig UPDATE OF name ON companies
REFERENCING NEW newval
REFERENCING OLD oldval
FOR EACH ROW
(EXECUTE PROCEDURE proc4(oldval.name, newval.name) )

AFTER ( EXECUTE PROCEDURE proc2 )

Итак, все-таки, напишем триггер, который будет срабатывать на удаление информации о фирме (таблица companies) и действие которого будет состоять в удалении из таблицы persons информацию о всех сотрудниках этой фирмы:

CREATE TABLE companies (
company_id SERIAL, { первичный ключ фирмы }

....)

CREATE TABLE persons (...,
company INTEGER, { ссылка на фирму, где работает }

....)

CREATE TRIGGER del_comp DELETE ON companies
       REFERENCING OLD AS rec
       FOR EACH ROW
          (DELETE FROM persons

WHERE persons.company = rec.company_id )

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

CREATE TABLE protocol (
     company INTEGER, { идентификатор измененной записи }
     login    CHAR(8), { ситемное имя пользователя }
     oldname CHAR(40), { старое имя фирмы }
     newname CHAR(40), { новое имя фирмы }
     when     DATETIME YEAR TO SECOND { когда }

)

Тогда, требуемый нам триггер будет выглядеть так:

CREATE TRIGGER upd_compname UPDATE OF name ON companies
REFERENNCING NEW AS newcomp
REFERENNCING OLD AS oldcomp
FOR EACH ROW
    (INSERT
      INTO protocol (company, login, oldname, newname, when)
      VALUES (oldcomp.company_id, USER, oldcomp.name,

newcomp.name, CURRENT)

Условия внутри триггера

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

........
WHEN ( <условие> ) ( <оператор>, <оператор>, ...),
WHEN ( <условие> ) ( <оператор>, <оператор>, ...),

........

Для каждого из разделов BEFORE/FOR EACH ROW/AFTER можно указывать произвольное количество таких пар условие-операторы. Пример триггера с дополнительными условиями:

CREATE TRIGGER upd_price UPDATE OF price ON items
   REFERENCING OLD AS pre
   REFERENCING NEW AS post
   FOR EACH ROW
     WHEN (post.price < 0)
          (EXECUTE PROCEDURE proc1(post.price))
     WHEN (post.price > pre.price * 2)

(EXECUTE PROCEDURE proc2(ppost.price, pre.price))

5.8. Ограничители (задание целостности на уровне схемы)

Целостность и ограничители

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

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

Выше мы рассмотрели как с помощью триггера можно поддерживать целостность базы данных, а именно как устранять "висячие" ссылки. Другим примером ограничителя, с которым мы уже знакомы, является описатель NOT NULL (смотри оператор создания таблицы CREATE TABLE), который говорит серверу о том, что данное поле всегда должно иметь определенное значение.

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

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

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

Ниже мы будем предполагать, что все ограничители вводятся при создании таблицы, хотя их можно определить и позднее оператором модификации структуры таблицы (ALTER TABLE).

Значения по умолчанию для полей

При вставке новых рядов в таблицу часто указываются значения не для всех полей, а только для некоторых. Поля, значения для которых не указаны, приобретают значение NULL (не определено).Но иногда разработчикам хочется, что бы значение по умолчанию было каким-то конкретным. Например, при вставке информации о каком-то товаре поле "количество заказов на этот товар" разумно обнулять.

Значения по умолчанию для того или иного пля задаются при создании таблицы (оператор CREATE TABLE), либо при модификации структуры таблицы (оператор ALTER TABLE). При описании поля после типа данных надо указать ключевое слово DEFAULT и значение по умолчанию. В качестве значения по умолчанию может стоять константа (в том числе NULL) или функция-псевдополе (CURRENT, TODAY, USER). Пример:

{ таблица с заказами }
CREATE TABLE orders (
   order_id    SERIAL,     { уникальный ключ для заказа }
   item        INTEGER NOT NULL,         { ссылка на товар }
   when        DATE DEFAULT TODAY,       { дата заказа }
   quantity    INTEGER DEFAULT 0,        { количество }
   tot_price   MONEY(20,2) DEFAULT "0.0" { общая стоимость }

)

Проверка на допустимость значения поля

Когда создается таблица, то для каждого поля задается тип его значения. Это может быть INTEGER, CHAR и т.д. Тип определяет допустимое множество значений для данного поля. Но в некоторых случаях это множество значений много шире реально используемого множества. Например, для обозначения количества единиц товара в заказе (см. структуру таблицы orders в предыдущем пункте) используется тип INTEGER. Но это количество не может быть отрицательным - это диктуется логикой задачи, а тип INTEGER допускает отрицательные значения.

В SQL есть средства более тонкого описания множества допустимых значений поля (кстати, это множество в теории называют доменом). Более точно задать домен для того или иного поля можно с помощью ограничителя на значение (check constraint). Этот ограничитель указывается при создании таблицы (оператор CREATE TABLE). После типа поля или значения по умолчанию надо указать ключевое слово CHECK и логическое выражение в скобках:

CREATE TABLE <имя таблицы> (
   .....
   <имя поля> <тип поля> [NOT NULL]
       [<значение по умолчанию>]
       CHECK (<логическое выражение>)

.....)

Это логическое выражение и будет опредеять допустимость значения. Условие проверяется перед изменением данных в поле операторами UPDATE или INSERT. Если значение логического выражение ложь или NULL, то сервер базы данных возвращает ошибку. Если вычисленное логическое выражение имеет значение истина, то новое значение считается допустимым и операция модификации завершается успешно. Например, можно описание таблицы orders расширить следующим образом:

CREATE TABLE orders (
   .....
   item        INTEGER NOT NULL CHECK
       (EXISTS (SELECT items.item_id
                   FROM items
                   WHERE items.item_id = item) ),
   .....
   quantity    INTEGER DEFAULT 0 CHECK (quantity > 0),
   tot_price   MONEY(20,2) DEFAULT "0.0" CHECK (quantity > 0)

)

В ограничителе на значение поля можно использовать сколь угодно сложное логическое выражение. Требования к этому выражению такие же, как и к условию в разделе WHERE оператора SELECT. То есть можно использовать логические операции IN, MATCHES, NOT, OR, AND, EXISTS и т.д. Не допускается, однако, использование вложенных подзапросов, агрегатных функций, псевдофункций-полей (CURRENT, TODAY, USER) и вызовов хранимых процедур. Нельзя, также, использовать логическое выражение, которое зависит от других полей в этой таблице. Например, если мы напишем

CREATE TABLE ....
price MONEY,
quantity INTEGER,
tot_price MONEY CHECK (tot_price = price*quantity)

.....

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

Уникальные поля

Для задания уникального ключа в таблице обычно используется тип SERIAL. Этот тип по множеству значений и способу внутреннего представления идентичен типу INTEGER. Но гарантировать уникальность значений поля типа SERIAL можно только в том случае, когда сервер сам генерирует новое значение для типа SERIAL, то есть оператор INSERT с явным указанием нового значения SERIAL не выполняется. Но иногда задача требует, что бы гарантированно поля любого типа, в том числе и SERIAL, использовались в качестве первичного ключа. Или, что тоже часто встречается, предметная область накладывает требование, что бы набор некоторых полей был уникальным, то есть был бы тоже первичным ключом (ключ, состоящий из нескольких полей, называется составным). Для проверки поля или группы полей на то, что они являются первичным ключом используется ограничитель на уникальность. Ограничитель на уникальность группы полей будет рассмотрен в параграфе 4.5. Здесь мы рассмотрим ограничитель на уникальность для отдельного поля.

Для того, чтобы сервер автоматически проверял и поддерживал уникальность для некоторого поля, надо для данного поля ввести ограничитель на уникальность. Для обозначения этого используется ключевое слово UNIQUE, которое ставится после типа поля или после значения по умолчанию, если таковое есть:

CREATE TABLE <имя таблицы> (
   .....
   <имя поля> <тип поля> [NOT NULL]
       [<значение по умолчанию>] UNIQUE

.....)

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

CREATE TABLE trademarks (
name CHAR(64) UNIQUE, { название торговой марки }
company INTEGER, { какой компании принадлежит }

.... )

Отличие типа SERIAL от поля, объявленного уникальным, заключается в том, что сервер при вставке новой записи с полем типа SERIAL сам генерирует новое уникальное значение для типа SERIAL и не делает этого для поля с ограничителем на уникальность. Приложение должно само заботиться о присвоении полям с ограничителем на уникальность новых значений. Иначе, при вставке нового ряда при неуказанном значении уникального поля ему будет присвоено или значение NULL, или значение по умолчанию. Ни то, ни другое значение, скорее всего, не будет уникальным.

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

Наличие в таблице уникальных полей, диктуемых логикой предметной области, не мешает нам завести искусственный первичный ключ типа SERIAL и использовать именно его для ссылок. Более того, в целях повышения эффективности, стоит поступать именно так. Тогда структура таблицы с торговыми марками будет выглядеть так:

CREATE TABLE trademarks (
   trademark_id SERIAL UNIQUE, { первичный ключ }
   name     CHAR(64) UNIQUE,   { название торговой марки }
   company INTEGER,           { какой компании принадлежит }

.... )

Ограничитель на уникальность не может использоваться совместо с ограничителем на значение.

Ссылочная целостность

Различные таблицы в базе данных тем или иным способом связаны друг с другом. Логика этой связи определяется предметной областью. Например, человек связан с компанией тем, что работает в ней. Или товар связан с компанией тем, что поставляется этой компанией. Эти связи (отношения) могут быть типа один-к-одному, один-ко-многим, многие-ко-многим. Например, одной компании может соответсвовать несколько товаров, которые она поставляет (теоретически, в том числе и ноль). Это соотношение один-к-многим. Примером отношения один-к-одному является связь таблиц "паспорт" и "человек" (конечно, если не рассматриать криминальные случаи).

Для реализации таких отношений между таблицами используются ссылки. То есть в одной из двух связанных таблиц заводится поле (или поля), которое представляет собой значение первичного ключа в другой таблице. Именно таким образом связаны таблицы companies, persons, items, и orders. Здесь главной таблицей является таблица с информацией о фирмах (companies). Все остальные таблицы - люди (persons) и товары (items) в некотором смысле являются подчиненными таблице companies. То есть ни человек, ни товар не могут быть определены, если нет информации о соответсвующей фирме. Таблица orders, хотя и связана с таблицей items соотношением один-к-одному, тем не менее должна рассматриваться как пдчиненная последней, так как поставляемые товары могут существовать без конкретных заказов (мы этот товар еще ни разу не покупали), а наоборот - нет. В соответствии с этим, в подчиненной таблице хранится ссылка на главную, а не наоборот.

Итак, в главной таблице есть некоторый первичный ключ, а в подчиненной таблице есть ссылка, представляющая собой поле (или поля), хранящие значение первичного ключа главной таблицы:

                   +-------------+        +-------------+
+----------+     | companies   |       ++------------+|
++---------+|     +-------------+      ++------------+||
++---------+|| +->| company_id |<--+ | persons    |||
| items    ||| | | name        |   | +-------------+||
+----------+|| | | address     |   | | person_id |||
| item_id ||| | | phone       |   +--+- company    |||
| company -+----+ +-------------+      | lname      |||
| name     |++                          | fname      |++
| price    ++                           | sname      ++

+----------+ +-------------+

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

CREATE TABLE <имя таблицы> (
   .....
   <имя поля> <тип поля> [NOT NULL]
       [<значение по умолчанию>] PRIMARY KEY,
   .....,
   <имя поля> <тип поля> [NOT NULL]
       [<значение по умолчанию>]
       REFERENCES <имя таблицы> (<имя поля>)

.....)

Если попытаться написать схему нашей базы данных с использованием ссылочных ограничителей, то получится примерно следующее:

CREATE TABLE companies (
company_id SERIAL PRIMARY KEY,

....)

CREATE TABLE items (
item_id SERIAL PRIMARY KEY,
company INTEGER REFERENCES companies(company_id),

....)

CREATE TABLE persons (
person_id SERIAL PRIMARY KEY,
company INTEGER REFERENCES companies(company_id),

....)

CREATE TABLE orders (
.....
item INTEGER REFERENCES items(item_id),

....)

Ссылочный ограничитель не может использоваться совместо с ограничителем на значение или с ограничителем на уникальность.

Ограничители на уровне таблицы

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

Ограничитель на значения (в нем можно использовать проверку нескольких полей одновременно) записывается точно так же, как и для одного поля - ключевое слово CHECK, после которого в скобках задается условие. Например:

CREATE TABLE ....
       price   MONEY,
       quantity INTEGER,
       tot_price MONEY,
       ....,

CHECK (tot_price = price*quantity) )

Ограничитель на уникальность может описывать составной ключ. Для этого после слова UNIQUE в скобках перечисляются поля из составного первичного ключа:

.... UNIQUE (<имя поля>, <имя поля>, ...)

Ссылочный ограничитель для таблицы, так же как и для одного поля, состоит из описания первичного ключа и ссылки на первичный ключ. Первичный ключ задается с помощью ключевых слов PRIMARY KEY, после которых в скобках через запятую идут составляющие его поля. Ссылка на первичный ключ начинается со слов FOREIGN KEY, после которых, также в скобках через запятую, перечислены составляющие ссылку поля. Затем после слова REFERENCES надо указать имя таблицы и в скобках поля первичного ключа для главной таблицы:

.... PRIMARY KEY (<имя поля>, <имя поля>, ...)
.... FOREIGN KEY (<имя поля>, <имя поля>

REFERENCES (<имя поля>, <имя поля>, ...)

Очевидно, должно быть соответствие между порядком и количеством полей после слов FOREIGN KEY и слова REFERENCES. Пример:

CREATE TABLE accounts (
      acc_num INTEGER,
       acc_type INTEGER,
       ...,

PRIMARY KEY (acc_num, acc_type))

CREATE TABLE sub_accounts (
       ...,
       ref_num INTEGER NOT NULL,
       ref_type INTEGER NOT NULL,
       ...,
       FOREIGN KEY (ref_num, ref_type)

REFERENCES accounts (acc_num, acc_type) )

5.9. Разграничение в SQL прав пользователей

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

5.9.1. Права доступа

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

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

INSERT INTO protocol(user_id, action, date)

VALUES (USER, "удалил запись", CURRENT YEAR TO SECOND)

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

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

5.9.2. Права на уровне базы данных

Имеется три категории прав на уровне базы данных. Это право на адимнистрирование (DBA), право на управление ресурсами (RESOURCE), право на доступ (CONNECT). Некоторые пользователи могут вообще не иметь каких-либо прав, связанных с конкретной базой данных.

Пользователь, имеющий права на доступ (CONNECT) имеет возможность получать и модифицировать данные в базе. Он может модифицировать те объекты, которыми владеет. Любой пользователь, имеющий право доступа может делать следующее:

·	выполнять операторы SELECT, INSERT, DELETE, UPDATE, если это ему позволено на уровне объекта (таблицы);

·	создавать новую псевдотаблицу (VIEW) по таблицам (см. ниже), если он имеет права на выборку по требуемым таблицам;

·	создавать синонимы (см. ниже);

·	создавать временные таблицы и индексы по временным таблицам;

·	изменять или удалять те объекты, которыми владеет;

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

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

Право на администрирование базы данных (DBA) подразумевает следующие возможности:

·	удалить базу данных (выполнить оператор DROP DATABASE);

·	удалять любые объекты вне зависимости от того, кто ими владеет;

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

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

GRANT <тип права на базу данных> TO <имя пользователя>

При управлении правами на уровне базы данных имя базы не указывается. Подразумевается текущая база данных. Примеры оператора GRANT:

GRANT DBA TO andy

GRANT RESOURCE TO micky

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

GRANT CONNECT TO PUBLIC

Права могут не только передаваться, но и отбираться. Естественно, для этого надо иметь право на администрирование базы данных. Отбор права выполняется оператором

REVOKE <тип права на базу данных> FROM <имя пользователя>

Например:

REVOKE CONNECT FROM roma

Поскольку права на базу данных имеют иерархию (право на администрирование включает в себя право на управление ресурсами, а право на управление ресурсами включает в себя право на доступ), то попытка лишить права на доступ пользователя, имеющего право на администрирование ни к чему не приведет. Пользователя надо явно лишить права на администрирование, при этом ему останется право на доступ. Предположим, мы хотим лишить пользователя "andy" каких-либо прав на базу данных. Если он имеет право на администрирование, то надо выполнить следующие два оператора:

REVOKE DBA FROM andy

REVOKE CONNECT FROM andy

Аналогично и с правом на управление ресурсами - выполнение оператора

REVOKE CONNECT FROM micky

ни к чему не приведет, так как пользователь "micky" имеет права RESOURCE, а выполнение оператора

REVOKE RESOURCE FROM micky

оставит пользователю "micky" право на доступ.

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

5.9.3. Права на таблицы

Если пользователь имеет права на базу данных, это не означает автоматически, что он имеет возможность получать любую информацию из любой таблицы. Наличие права на уровне базы данных означает возможность подключиться к базе данных, то есть выполнить оператор DATABASE. Все дальнейшие действия с содержимым базы данных производятся в соответствии с правами на уровне объектов (таблиц и процедур).

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

Права на уровне таблиц бывают следующего типа:

·	SELECT - право на получение информации из таблицы. При необходимости можно дополнительно указать те поля, значения которых доступны. То есть можно указать, что

·	DELETE - право на удаление записей из таблицы.

·	INDEX - право на создание индексов для данной таблицы (индексы будут рассмотрены позднее). Для использования этого права надо иметь право на управление ресурсами на уровне всей базы.

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

·	REFERENCES - право на задание ссылочных ограничетелей для данной таблицы. Для данного права можно специфицировать поля, которые можно использовать в ограничителях.

·	ALL - все перечисленные выше права на таблицу.

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

GRANT <тип права на таблицу> ON <имя таблицы> TO <имя пользователя>

Вместо имени пользователя можно использовать слово PUBLIC, означающее всех пользоователей. Например:

GRANT ALL ON persons TO micky
GRANT SELECT ON persons TO roma

GRANT INSERT ON items TO PUBLIC

В тех случаях, когда при передаче права на таблицу можно специфицировать конкретные поля (права UPDATE, SELECT и REFERENCES), то имена полей надо указать в скобках после названия права:

GRANT <тип права на таблицу> (<имя поля>, <имя поля>, ....)
ON <имя таблицы>

TO <имя пользователя>

Например:

GRANT SELECT(person_id, lname, fname)

ON persons TO PUBLIC

Если какой-либо пользователь не имеет права на выборку того или иного поля, то он не может его использовать не только в разделе SELECT, но и в разделе WHERE. Например, если пользователь nick не имеет права на выборку поля address из таблицы persons, то он не имеет права выполнить следующие два запроса:

SELECT lname, fname, address FROM persons WHERE person_id = 101

SELECT lname, fname FROM persons WHERE address MATCHES "*Одесса*"

Пользователь, получивший то или иное право с помощью оператора GRANT ... ON ... TO ... не может передать это право другому пользователю. Для возможности передачи права другим, пользователь должен получить право на его передачу. Это право указывается в операторе GRANT с помощью ключевых слов WITH GRANT OPTION:

GRANT <тип права на таблицу> (<имя поля>, ....) ON <имя таблицы>

TO <имя пользователя> WITH GRANT OPTION

Например, владелец таблицы tabl может передать право на выборку для пользователей "sales" и "tech", но пользователю "tech" это право передается с правом передачи:

GRANT SELECT ON tabl TO sales

GRANT SELECT ON tabl TO tech WITH GRANT OPTION

После этого пользователь "tech" может выполнить следующие два оператора, а пользователь "sales" не может:

GRANT SELECT ON tabl TO jason WITH GRANT OPTION

GRANT SELECT ON tabl TO micky

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

Если Вы отбираете право у пользователя , который получил его с правом на передачу, то автоматически этого права лишаются и те, кому пользователь это право передал. Отбирание права производится оператором

REVOKE <тип права на таблицу> ON <имя таблицы> FROM <имя пользователя>

Например:

REVOKE UPDATE ON persons FROM george

REVOKE ALL ON passwords FROM PUBLIC

Если какое-либо право отбирается у пользователя, которому оно было дано с правом на передачу (WITH GRANT OPTION), то это право теряют и те пользователи, кому этот пользователь его передал. Предположим, Вы владелец таблицы tab1. Вы передаете право на вставку пользователям user1, user2:

GRANT INSERT ON tab1 TO user1 WITH GRANT OPTION

GRANT INSERT ON tab1 TO user2 WITH GRANT OPTION

Затем, пользователь "user1" передал это право пользователю "user3":

GRANT INSERT ON tab1 TO user3

Теперь, если Вы отберете право на выборку у пользователя "user1", то его автоматически потеряет и пользователь "user3".

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

GRANT SELECT, UPDATE(fname, lname, address) ON persons TO micky, roma

Эквивалентен следующим четырем операторам:

GRANT UPDATE(fname, lname, address) ON persons TO micky
GRANT SELECT ON persons TO micky
GRANT UPDATE(fname, lname, address) ON persons TO roma

GRANT SELECT ON persons TO roma

5.9.4. Права на хранимые процедуры

Для хранимых процедур существует единственный тип права доступа - выполнять ее. Если база данных находится не в режиме ANSI, то при создании хранимой процедуры все пользователи автоматически получают право на ее исполнение. Если же база данных находится в режиме ANSI, то надо явно указать, какие пользователи имеют право ее исполнять. Право на исполнение называется EXECUTE. Передача и отбор этого права аналогичны передаче и отбору прав на уровне таблицы. Можно передавать право и с правом на передачу:

GRANT EXECUTE ON <имя процелуры> TO <имя пользователя>

GRANT EXECUTE ON <имя процелуры> TO <имя пользователя> WITH GRANT OPTION REVOKE EXECUTE ON <имя процелуры> FROM <имя пользователя>

5.9.5. Кто и как следит за соблюдением прав

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

Как хранится информация о привилегиях - это внутреннее дело SQL-сервера. Например, Informix DS использует для этого системные таблицы (каталоги), где и зафиксирована кто и что может делать с данными.

5.9.6. Механизм ролей

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

Итак, роль - это группа пользователей с определенными правами. Для создания роли используется оператор

CREATE ROLE <имя>

Например, если мы хотим создать три группы - admin (администратор), sales (продавцы) и tech (технический персонал), то надо выполнить следующие операторы:

CREATE ROLE admin
CREATE ROLE sales

CREATE ROLE tech

Имя роли должно быть не длиннее 8 символов и не должно совпадать с именем пользователя, которому были переданы права на какой-либо объект в базе данных. Кроме того, имя пользователя не должно совпадать с именами пользователей, работающих в данный момент с базой данных. Если какой-то пользователь имеет имя, совпадающее с именем роли, он не сможет получить доступ к базе данных. То есть, для ролей имеет смысл выбирать имена, отличные от имен пользователей, которые могут работать на данной машине.

Для указания пользователей, входящих в ту или иную роль, используется оператор

GRANT <имя роли> TO <имя пользователя1>, <имя пользователя2> ….

Один пользователь может принадлежать нескольким ролям, также как и не принадлежать ни одной роли (или, что тоже самое, принадлежать только роли по имени NULL). Например:

GRANT admin TO andy
GRANT tech TO andy, micky

GRANT sales TO nick, kate

В этом примере пользователь andy входит сразу в две роли - admin и tech, пользователи micky, nick, kate - входят только в одну роль.

Исключение пользователя из роли производится оператором

REVOKE <имя роли> FROM <имя пользователя>

Например:

REVOKE sales FROM nick

Для удаления роли используется оператор

DROP ROLE <имя>

Для приписывания роли или отбирания у роли тех или иных прав и привилегий используются варианты операторов GRANT и REVOKE, рассмотренные в пунктах 5.9.2 и 5.9.3, только вместо имени пользователя необходимо указывать имя роли:

GRANT UPDATE(fname, lname, address) ON persons TO admin
GRANT SELECT ON persons TO tech

REVOKE UPDATE(fname, lname, address) ON persons FROM sales

Определять новые роли может только пользователь, являющийся администратором данной базы данных, то есть имеющий привилегию DBA на текущую базу данных. Удалить роль, модифицировать список пользователей, входящих в ту или иную роль, а также приписать или отобрать у роли некоторые привилегии, может только адинистратор базы данных, или пользователь, которому данная роль была передана с опцией WITH GRANT OPTION.

Итак, пользователь может принадлежать нескольким ролям, кроме того, у него еще могут быть права и привилегии, данные ему лично (как пользователю). Для того, чтобы воспользоваться правами и привилегиями, принадлежащими какой-либо роли, пользователь должен выполнить оператор (или, естественно, внутри его программы должен быть выполнен оператор):

SET ROLE <имя роли>

например:

SET ROLE admin

Естественно, если пользователь не принадлежит этой роли, то данный оператор возвратит ошибку. Если оператор SET ROLE не будет выполнен, то права пользователя определяются на основе прав данных ему лично. Если оператор SET ROLE был выполнен, то пользователь в дополнение к своим личным правам, получает права, приписанные указанной роли. Одновременно может использоваться только одна роль, то есть новый оператор SET ROLE отменяет действие предыдущего. Для того, чтобы отказатья от прав, приписанных роли и вернуться только к правам, данных пользователю лично, надо выполнить оператор

SET ROLE NULL

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

5.9.7. Псевдотаблицы (VIEW)

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

В SQL данная проблема может быть решена с помощью псевдотаблиц (VIEW). Псевдотаблица строится на основе уже существующих таблиц или пседотаблиц. В основе псевдотаблицы лежит оператор выборки. Собственно говоря, псевдотаблица и задается оператором выборки:

CREATE VIEW <имя псевдотаблицы> AS <оператор выборки>

Например:

CREATE VIEW cheap_items AS
SELECT item_id, company, name, price FROM items

WHERE price < 100.00

В этом примере была создана псевдотаблица, содержащая информацию только о дешевых (дешевле 100 единиц) товаров.

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

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

Так как же решить ту задачу, с которой мы начали этот параграф? А именно, как сделать так, чтобы разные пользователи, просматривая одну и ту же таблицу, видели разные данные, а, говоря точнее, информацию только о тех сотрудниках, которые работают в одном с ним отделе? Для этого надо вначале создать таблицу, в которой будет поле с номером отдела:

CREATE TABLE employers (
login_name CHAR(8), { системное имя }
depat_num INTEGER, { номер отдела }
..............

)

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

SELECT login_name, depat_num, ... FROM employers
WHERE depat_num =
(SELECT depat_num FROM employers

WHERE login_name = USER)

Если, например, таблица employers имеет следующие записи

-------------T-----------T-----¬
¦ login_name ¦ depat_num ¦.....¦
+------------+-----------+-----+
¦ nick      ¦      0    ¦     ¦
¦ andy      ¦      4    ¦     ¦
¦ kate      ¦      2    ¦     ¦
¦ george    ¦      2    ¦     ¦
¦ micky     ¦      4    ¦     ¦
¦ roma      ¦      4    ¦     ¦

L------------+-----------+------

то пользователь "george" при исполнении этого запроса увидит информацию только о себе и о пользователе "kate", пользователь "andy" увидит информацию о себе и пользователях "roma" и "micky", а пользователь "nick" получит данные только о себе.

Теперь осталось только на основе данного оператора SELECT создать псевдотаблицу my_collegues:

CRETATE VIEW my_collegues AS
SELECT login_name, depat_num, ... FROM employers
WHERE depat_num =
(SELECT depat_num FROM employers

WHERE login_name = USER)

Теперь запросы вида

SELECT login_name FROM my_collegues

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

GRANT SELECT ON my_collegues TO PUBLIC

REVOKE ALL ON employers FROM PUBLIC

В псевдотаблицу можно вставлять значения, обновлять данные и удалять записи, но только в том случае, если псевдотаблица образована из одной таблицы. Так, операторы UPDATE, INSERT, DELETE применимы для описанных выше псевдотаблиц my_collegues и cheap_items. Но если для определенния псевдотаблицы использовались поля из двух или более таблиц, то такая псевдотаблица может быть использована только в операторе SELECT. Пример определения псевдотаблицы, построенной из нескольких таблиц:

CREATE VIEW pers_comp AS
SELECT persons.lname, persons.fname, persons.sname,
companies.name, companies.addresss
FROM persons, companies

WHERE persons.company = companies.company_id

При создании VIEW можно дополнительно указать, что эта псевдотаблица будет с проверкой. Это означает, что при вставке, удалении или модификации записи будет проверяться условие WHERE в операторе SELECT, который образует данную псевдотаблицу. Для описания псевдотаблицы с проверкой надо указать ключевые слова WITH CHECK OPTION в операторе CREATE VIEW:

CREATE VIEW <имя псевдотаблицы> AS <оператор выборки>

WITH CHECK OPTION

Например, в определенную выше псевдотаблицу cheap_items можно вставить запись с ценой, равной 500 единиц:

INSERT INTO cheap_items (company, name, price)

VALUES (101, "смокинг", 500)

так как эта псевдотаблица создана без проверки. Правда, сразу после этого оператора вставки в данной псевдотаблице запись о смокинге ценой в 500 единиц будет все равно отсутствовать (при выборке эта запись будет отсечена по условию price<100). А если бы мы создали эту псевдотаблицу с проверкой, то SQL-сервер выдал бы сообщение об ошибке:

CREATE VIEW cheap_items AS
SELECT item_id, company, name, price FROM items
WHERE price < 100.00

WITH CHECK OPTION

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

Удаляется псевдотаблицы оператором

DROP VIEW <имя псевдотаблицы>

Например:

DROP VIEW cheap_items

DROP VIEW employers

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

5.9.7. Синонимы

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

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

CREATE PUBLIC SYNONYM <имя синонима> FOR <имя таблицы>

Личный синоним создается аналогично, только вместо ключевого слова PUBLIC используется слово PRIVATE:

CREATE PRIVATE SYNONYM <имя синонима> FOR <имя таблицы>

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

Примеры создания синонимов:

CREATE PUBLIC SYNONYM goods FOR items

CREATE PRIVATE SYNONYM tovary FOR items

Для баз данных в режиме ANSI синонимы бывают только личными и при создании синонима слово PRIVATE указывать не надо. Имена синонимов могут использоваться в операторах GRANT и REVOKE при передаче или лишения права на доступ к таблице..

Удаляется синоним оператором

DROP SYNONYM <имя синонима>

5.10. Управление одновременным доступом к данным

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

5.10.1. Что бывает, когда несколько человек одновременно пытаются обновить одни и теже данные

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

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

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

Итак, рассмотрим те механизмы, которые есть в SQL, и которые используются для предотвращения описанных выше ситуаций.

5.10.2. Открытие базы данных только для себя

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

DATABASE <имя базы> EXCLUSIVE

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

Точно так же, если указанная база данных уже открыта другим пользователем (как с указанием EXCLUSIVE, так и в обычном режиме), то Вы получите сообщение об ошибке.

Действие данного оператора, так же как и обычного оператора открытия базы данных, завершается выполнением оператора

CLOSE DATABASE

Оператор DATABASE EXCLUSIVE используется достаточно редко. Он обычно применяется при каком-то существенном перестроении схемы базы данных. Для реализации многопользовательского доступа к данным существуют более мягкие и изящные способы - блокирование таблицы и уровни изоляции.

Блокирование означает ограничение доступа других пользователей к некоторым ресурсам в течении некоторого промежутка времени, обычно небольшого. Например, вместо термина "открытие базы данных в эксклюзивном режиме" используют также термин "блокировка базы данных". Блокировка может быть разной. Тип блокировки определяется тем, что можно другим пользователям делать с заблокированным объектом, насколько ограничен доступ к этому объекту.

5.10.3. Блокирование таблицы

Выше мы расмотрели способы ограничения доступа ко всей базе данных. При необходимости ограничить доступ других пользователей к некоторой таблице можно заблокировать только эту таблицу. Заблокировать таблицу можно двумя способами. Первый способ разрешает доступ других пользователей к заблокированной таблице на чтение данных. Такая блокировка называется "разделяемой". При втором способе любой доступ к таблице для других пользователей запрещен. Такая блокировка называется эксклюзивной (эксклюзивная блокировка аналогична открытию базы данных в исключительном режиме).

Разделяемая блокировка на таблицу ставится оператором

LOCK TABLE <имя таблицы> IN SHARE MODE

Эксклюзивная блокировка на таблицу ставится оператором

LOCK TABLE <имя таблицы> IN EXCLUSIVE MODE

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

Снятие блокировки производится оператором

UNLOCK TABLE <имя таблицы>

Блокировка с таблиц также снимается автоматически при закрытии базы данных (оператор CLOSE DATABASE) или при завершении (например, аварийном) пользовательской программы.

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

5.10.4. Механизм блокирования записей и уровни изоляции

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

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

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

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

Существуют следующие основные уровни изоляции: грязное чтение (DIRTY READ), достоверное чтение (COMMITTED READ), стабильный курсор (CURSOR STABILITY), многократное чтение (REPEATABLE READ).

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

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

Уровень изоляции DIRTY READ является единственно допустимым для баз данных без транзакций, но может применяться и для баз данных с транзакциями. Уровень изоляции "грязное чтение" обеспечивает самую высокую производительность. Его обычно используют для доступа к данным, которые хранятся в малоизменяемых таблицах, например для выборки из таблиц с описанием классификаторов или для чтения таблиц с результатами проведенных экспериментов.

Уровень изоляции COMMITTED READ (достоверное чтение) обеспечивает чтение данных, когда они не модифицируются другим процессом. Использование этого уровня изоляции обеспечивает Вам невозможность прочесть так называемые "фантомные" записи, которые появляется в процессе выполнения транзакции другим процессом. То есть если Ваша программа прочитала запись, то эта запись гарантирована существует. Однако, никаких блокировок на прочитанные записи при этом не делается, то есть другой процесс может прочитать эту же запись и удалить или изменить ее. Данный уровень изоляции является уровнем, принимаемым по умолчанию для баз данных с транзакциями.

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

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

Уровень изоляции CURSOR STABILITY (стабильный курсор) обеспечивает невозможность другим пользователям изменить прочитанную Вами запись до тех пор, пока Вы не прочтете другую запись. То есть, прочитанный ряд блокируется. Обычно последовательный просмотр записей реализуется с помощью механизма, называющегося "курсор", отсюда и название данного уровня изоляции.

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

Самый высший уровень изоляции - это REPETABLE READ (повторяемое чтение). Если программа использует данный уровень изоляции, то SQL-сервер заблокирует все записи, прочитанные данной программой в пределах транзакции. То есть уровень изоляции REPEATABLE READ означает запрещение изменения другими процессами всех записей, задействованных Вами в течении транзакции. Это означает, что Вы можете в течение одной транзакции многократно читать одни и те же записи и они гарантированно не могут быть изменены другими пользователями. Этот уровень изоляции является самым сильным и принимается по умолчанию для баз данных с транзакциями в режиме ANSI.

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

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

SET ISOLATION TO <уровень изоляции>

Например:

SET ISOLATION TO REPEATABLE READ

SET ISOLATION TO DIRTY READ

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

SET ISOLATION TO DIRTY READ
SELECT ... INTO ...
..........................
SET ISOLATION TO CURSOR STABILITY
FOREACH my_cursor INTO var, var
..........................

END FOREACH

5.10.5. Управление ожиданием снятия блокировок

В реальных программах надо как-то уметь обрабатывать ситуации, когда пользовательская программа пытается обратитьться к заблокированным ресурсам (таблице, записи). Помимо стандартного управления ошибками (оператор WHENEVER, мы его рассмотрели в одной из предыдущих статей), существует специальный оператор установки режима ожидания SET LOCK MODE. Его синтакис:

SET LOCK MODE TO WAIT <число секунд>

SET LOCK MODE TO NOT WAIT

Если Вы установили режим ожидание в "NOT WAIT" ("не ждать"), то если Ваша программа пытается обратиться к заблокированным ресурсам, то тут же получает сообщение об ошибке. Этот режим устанавливается по умолчанию.

Если Вы установили режим ожидания в "WAIT" ("ждать") и не указали число секунд, то Ваша прогрмма будет ожидать разблокирования ресурсов до бесконечности. Если же при этом указать и число секунд, то Ваша программа будет ожидать разблокирования ресурсов указанное число секунд, и, если за указанное время этого не произошло, то она получает сообщение об ошибке, Например, если Вы хотите, что бы пользователь ждал не более 5 секунд, а затем получал сообщение о недоступности ресурсов, то надо выполнить оператор

SET LOCK MODE TO WAIT 5

5.10.6. Тупиковые ситуации

Теоретически, особенно при использовании режима ожидания до бесконечности, возможно возникновение тупиковой ситуации, клинча (английский термин deadlock - смертельные объятия). Это означает, что две или более программ заблокировали некоторые данные, но для продолжения работы им нужны данные, которые заблокированы другими программами. То есть программы ждут друг друга. Рассмотрим пример: пользователи А и В исполняют некоторые программы (соответсвенно программы А и В), работающие с одной и той же базой данных. Обе программы находятся в режиме бесконечного ожидания снятия блокировок (был выполнен оператор SET LOCK MODE TO WAIT). Программа А выполнила оператор

LOCK TABLE table_a IN EXCLUSIVE MODE

заблокировав, таким образом, таблицу table_a. Затем программа В выполняет оператор

LOCK TABLE table_b IN EXCLUSIVE MODE

и блокирует таблицу. Для дальнейшей работы программе А надо заблокировать таблицу table_b и она выполняет оператор

LOCK TABLE table_b IN EXCLUSIVE MODE

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

LOCK TABLE table_a IN EXCLUSIVE MODE

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

5.11. Повышение скорости обработки запросов.

Язык SQL - не процедурный язык. Когда мы обсуждали, ЧТО делает тот или иной оператор, то никогда не обсуждалось, КАК это выполняется. Но SQL используется для написания реальных программ, с которыми работают реальные люди. Поэтому во многих случаях требуется обеспечить быстрое исполнение SQL-операторов. В данном параграфе будут рассмотрены способы управления скоростью исполнения тех или иных SQL-запросов.

5.11.1. Индексы

Предположим, в нашей базе данных есть таблица persons, содержащая сведения о людях (ФИО и где работают:)

CREATE TABLE persons (
person_id SERIAL UNIQUE,
company INTEGER,
lname CHAR(40),
fname CHAR(30),
sname CHAR(30),

);

Если мы попытаемся определить, какие люди работают в фирме с уникальным номером 105 (то есть у какх записей в таблице persons поле company равно 105), нам надо будет выполнить запрос

SELECT lname, fname, sname FROM persons

WHERE company = 105

При исполнении этого запроса сервер базы данных должен будет просмотрть всю таблицу persons и для каждой записи из этой таблицы проверить условие "company = 105". Если в таблице persons несколько миллионов записей, то такой запрос может потребовать для своей обработки длительное время. Совсем не обязательно иметь таблицу в несколько миллионов записей, чтобы нагрузить сервер работой на несколько минут. Предположим, в таблице companies одна тысяча записей, а в таблице persons всего десять тысяч записей (в каждой фирме, в среднем, работает десять человек). Таблица compenies имеет следующую структуру:

CREATE TABLE companies (
company_id SERIAL UNIQUE,
name CHAR(40),
address CHAR(40)

);

Если мы будем искать всех людей и их рабочие адреса для фирм под названием "АО Рога и Копыта" и "АОЗТ Сделай Сам", то при исполнении запроса

SELECT persons.lname, persons.fname, persons.sname, companies.address
FROM persons, companies
WHERE company.name IN ("АО Рога и Копыта", "АОЗТ Сделай Сам")

AND persons.company = company.company_id

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

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

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

CREATE INDEX pers_comp_index ON persons(company)

В общем случае, оператор создания индекса выглядит так:

CREATE INDEX <имя индекса>

ON <имя таблицы> (<имя поля>,<имя поля> ...)

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

CREATE index1 ON persons(lname)

CREATE comp_indx ON items(company, name)

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

Пусть у нас есть таблица persons с полями:

------------T---------T----------T---------¬
¦ person_id ¦ company ¦ lname    ¦ fname   ¦
+-----------+---------+----------+---------+
¦         1 ¦     101 ¦ Антонов ¦ Сергей ¦
¦         2 ¦     105 ¦ Шапокляк ¦ Алексей ¦
¦         3 ¦     102 ¦ Антонов ¦ Антон   ¦
¦         4 ¦     101 ¦ Бендер   ¦ Остап   ¦

L-----------+---------+----------+----------

Каждая запись в этой таблице реально где-то размещена. С местом ее размещения может быть однозначно связано какое-то значение. Если для хранения используется файл, как в сервере Informix-SE, то в качестве этого уникального значения может выступать смещение записи от начала файла. Если для хранения используется своя собственная файловая ситема, как, например, в Informix DS, то в качестве внутреннего идентификатора записи может выступать составной ключ (номер диска, номер сектора, смещение от начала сектора). Как выглядит это уникальное для каждой записи значение - абсолютно не важно, это внутреннее дело сервера. Очевидно, что такое значение всегда можно построить. Предположим, что это значение всегда присутствует для любой таблицы и выглядит как псевдополе с именем "rowid" (идентификатор записи). С учетом этого поля записи в данной таблице выглядят следующим образом:

- - - - T-----------T---------T----------T---------¬
¦ rowid ¦ person_id ¦ company ¦ lname    ¦ fname   ¦
+ - - - +-----------+---------+----------+---------+
¦ 1003 ¦         1 ¦     101 ¦ Антонов ¦ Сергей ¦
¦ 1023 ¦         2 ¦     105 ¦ Шапокляк ¦ Алексей ¦
¦ 1063 ¦         3 ¦     102 ¦ Антонов ¦ Антон   ¦
¦ 1053 ¦         4 ¦     101 ¦ Бендер   ¦ Остап   ¦

L - - - +-----------+---------+----------+----------

При исполнении оператора создания индекса сервер создает набор записей, собственно и составляющих индекс. Каждая запись из индекса состоит из значений той таблицы, по которой построен этот индекс (естественно, хранятся только те поля, которые указаны в данном индексе - эти поля называют проиндексированными) и ссылки на физическое расположение записи в таблице (rowid этой записи). Записи из индекса упорядочены по индексируемым значениям. Например, для индекса pers_comp_indx, построенного по полю company таблицы persons набор составляющих его записей будет выглядеть следующим образом:

-----------T---------¬
¦ значение ¦ rowid ¦
+----------+---------+
¦      101 ¦   1003 ¦
¦      101 ¦   1053 ¦
¦      102 ¦   1063 ¦
¦      105 ¦   1023 ¦

L----------+----------

Тогда при исполнении запроса

SELECT .... FROM persons WHERE company = 105

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

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

SELECT company FROM persons WHERE lname = "Бендер"

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

CRATE INDEX second_index ON persons(lname)

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

CREATE INDEX pers_names ON persons(lname, fname)

Заметим, что порядок, в котором указаны поля для составного индекса, существенен. Так, определенный нами индекс по комбинации фамилия-имя будет отличаться от индекса по комбинации имя-фамилия:

CREATE INDEX pers_names2 ON persons(fname, sname)

Кстати, если создан составной индекс по полям (lname, sname), то отпадает необходимость в индексе по полю lname (но не в индексе по полю sname). То есть, если создан индекс по некоторой последовательности полей (a1, a2, ... aN), то он функционально покрывает индекс по последовательности полей (a1, a2, ... aK), если K < N.

У оператора создания индекса помимо базового варианта есть модификации. Более полный синтаксис оператора создания индекса выглядит так:

CREATE [UNIQUE] [CLUSTER] INDEX <имя индекса>

ON <имя таблицы> (<имя поля>, <имя поля> ...)

Ключевое слово UNIQUE (вместо него можно использовать слово DISTINCT) означает, что в таблице не допускаются одинаковые наборы значений по совокупности полей, указанным в индексе. Другими словами, это способ следить за уникальностью указанного набора полей. Поэтому такой индекс называется уникальным. Использование уникального индекса функционально идентично заданию уникального ключа в таблице за тем исключением, что для добавления уникального индекса надо иметь привилегии RESOURCE, а для указания уникального ключа в структуре таблицы - быть владельцем таблицы, иметь права администратора или иметь привилегию на модификацию таблицы. Пример:

CREATE UNIQUE INDEX my_index ON persons (person_id, company)

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

CREATE CLUSTER INDEX my_2nd_index ON persons (lname, fname)

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

Использование индексов в SQL принципиально отличается от того, как они применяются в индексно-последовательных СУБД типа dBase, Clipper или FoxPro. В случае SQL-сервера, поддержание целостности индекса и его использование - это внутренняя задача самого сервера. У пользователя или программиста, работающего с SQL-сервером, есть только возможность создать (оператор CREATE INDEX) и удалить (оператор DROP INDEX) индекс, а также изменить его структуру (с помощью оператора ALTER INDEX, который здесь не рассматривается). Оператор удаления индекса имеет следующий синтаксис:

DROP INDEX <имя индекса>

Естественно, для удаления индекса надо быть или его владельцем, или иметь права администратора.

5.11.2. Буферизация журнала транзакций

Обычно, при завершении транзакции (оператор COMMIT WORK) происходит принудительный сброс обновленной базы данных на внешний носитель (жесткий диск). Причем информация о проведенной транзакции дополнительно записывается в специальный файл - журнал транзакций. Если этот журнал и обновленное содержимое базы данных не сбрасывать на диск по окончании транзакции, а буферизировать в памяти, то в случае падения напряжения питания или сбоя аппаратуры информация о нескольких последних завершенных транзакциях может быть потеряна. Целостность базы данных при этом гарантированно сохранится, однако ее содержимое будет соответсвовать или последней завершенной транзакции, или предпоследней, или пред…предпоследней. Для банковских задач буферизация журнала транзакций, наверное, неприемлима, но для целого ряда других задакч, например регистрации каких-либо параметров, подлежащих статистической обработке. - вполне допустима.

Операции обмена с жестким диском - одно из самых узких мест в современных вычислительных системах (с точки зрения производительности). Поэтому буферизация журнала транзакций может заметно повысить общую производительность системы. Естественно, повышать производительность в ущерб надежности мы не рекомендуем, но если задача допускает буферизацию журнала транзакций, то при создании базы данных это можно указать с помощью оператора CREATE DATABASE:

CREATE DATABASE <имя базы данных> WITH BUFFERED LOG

Буферизация журнала транзакций позволяет в несколько раз повысить скорость исполнения операторов INSERT, UPDATE и DELETE, но при этом практически не влияет на скорость работы оператора SELECT.

5.11.3. Блокировка на уровне записей и страниц

При рассмотрении уровней изоляции предполагалось, что блокировки ставятся на уровне записей (то есть блокируется отдельная запись). На самом деле, сервера Informix Dynamic Server позволяют выбирать уровень блокировки между блокировкой на уровне записи и блокировкой на уровне страницы. Практически во всех SQLсерверах (в том числе, и Informix Dynamic Server) память под таблицы выделяется порциями фиксированного объема - страницами или группами страниц. Обычно размер страницы составляет 2 или 4 килобайта. В целях повышения производительности можно блокировать записи не по одной, а целыми страницами.

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

CREATE TABLE … LOCK MODE {PAGE | ROW}

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

CREATE TABLE companies (
company_id SERIAL UNIQUE,
name CHAR(40),
address CHAR(40)

) LOCK MODE ROW

CREATE TABLE persons (
person_id SERIAL UNIQUE,
company   INTEGER,
lname     CHAR(40),
fname     CHAR(30),
sname     CHAR(20),
position CHAR(20)

) LOCK MODE PAGE

5.11.4. Эффективное построение запросов

Производительность сервера базы данных при выполнении доступа к информации зависит не только от наличия или отсутствия индексов, буферизации журнала транзакций и т.д., но и от того, какие запросы исполняются. Не секрет, что одно и тоже действие (например, построение списка сотрудников какой-либо фирмы) можно запрограммировать поразному. Суммарная скорость исполнения данного действия зависит от того, какими операторами на SQL оно представлено. Рассмотрим пример. Допустим, у нас есть таблицы persons и companies (их структура привевдена выше, в пункте 5.11.3). Требуется построить поименный список сотрудников, работающих в фирме “АО Рога и Копыта”. Первый вариант реализации данного действия выглядит так:

SELECT lname, fname FROM persons, companies
WHERE persons.company = companies.company_id

AND companies.name = “АО Рога и Копыта”

При исполнении данного запроса при отсутствии индексов сервер базы данных должен будет перебрать все возможные комбинации записей из таблиц persons и companies, и для каждой комбинации проверить, выполняется ли условие из раздела WHERE. Если в таблице persons находится M записей, а в таблице companies - N записей, то всего будет проверено M*N комбинаций.

Другой запрос, функцмионально реализующий тоже самое действие, а именно, формирование списка сотрудников АО Рога и Копыта выглядит следующим образом:

SELECT lname, fname FROM persons
WHERE persons.company IN
(SELECT company_id FROM companies

WHERE name = “АО Рога и Копыта”)

При исполнении этого запроса, сервер вначале просмотрит таблицу companies и найдет одно требуемое значение, затем он просмотрит таблицу persons и сравнит поле company с найденным значением. В итоге, он просмотрит M+N записей. Если значения M и N достаточно велики (порядка сотен или более), то второй запрос будет исполняться много быстрее первого. Естественно, это очень грубая оценка без учета наличия индексов, возможностей сервера по оптимальному исполнению запросов и т.д., но выигрыш в полмиллиона раз раз для таблиц с миллионом записей каждая уже впечатляет.

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

На основании этого правила можно сделать вывод о том, что иногда выгоднее разбить сложный запрос на несколько простых (с использованием временной таблицы), нежели пытаться уместить какое-либо действие в один запрос. Допустим, вас отправляют в командировку в г. Тверь, и вы хотите посетить директоров всех имеющихся в этом городе фирм-партнеров. Если данный запрос представить на SQL, то первый, неоптимизированный вариант запроса мог бы выгшлядеть следующим образом:

SELECT persons.lname, persons.fname, persons.sname, companies.address
FROM persons, companies
WHERE companies.address MATCHES "*Тверь*"
AND companies.companies_id = persons.company

AND persons.position = “директор”

Привести данный запрос к виду

SELECT . . . FROM persons WHERE . . .

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

CREATE TEMP TABLE local_companies (
company_id INTEGER,
name CHAR(40),
address CHAR(40)

)

{наполнение временной таблицы списком фирм, распорложенных в Твери}
INSERT INTO local_companies(company_id, name, address)
SELECT company_id, name, address
FROM companies

WHERE address MATCHES "*Тверь*"

{получение требуемых данных}
SELECT persons.lname, persons.fname, persons.sname,
local_companies.address, local_companies.name
FROM persons, local_companies
WHERE local_companies.companies_id = persons.company

AND persons.position = “директор”

DROP TABLE local_companies

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

5.11.5. Сортировка и поиск по коротким полям. Классификаторы

Сортировка (т.е. использование раздела ORDER BY в операторе SELECT) требует очень больших времЕнных затрат. Особенно это касается больших таблиц. Сортировка, выполняемая по проиндексированным полям, происходит существенно быстрее, чем по непроиндексированным. Если же сортировка все же проводится по полям, для которых нет индекса, то лучше для сортировки использовать короткие поля (типа INTEGER, SMALLINT), нежели длинные (например, символьные). В условии WHERE операторов также предпочтительнее использовать короткие поля. Например, оператор

SELECT address FROM companies WHERE name=”АО Рога и Копыта”

будет в среднем выполняться дольше, нежели оператор

SELECT address FROM companies WHERE company_id=105

Это правило можно распространить и на использование классификаторов. Классификатор - это специально введенная таблица, в которой хранится набор возможных значений какого-либо атрибута. Предположим, для каждой фирмы требуется хранить ее тип (госпредприятие, ООО, АОЗТ и т.д.). Набор типов предприятия ограничен. В принципе, в таблице companies нетрудно предусмотреть на этот случай специальное символьное поле:

CREATE TABLE companies(
. . . . .

type CHAR(20) )

Сортировка и поиск по полю CHAR(20) nбудет происходить значительно медленне, чем по полю INTEGER. К тому же, если записей в таблице companies много, то использование поля INTEGER как ссылки на классификатор с типами предприятий вместо непосредственного задания этого значения в таблице даст экономию памяти. Поэтому более грамотным решением, на наш взгляд, является создание специального классификатора (таблицы types):

CREATE TABLE types (
type_id SERIAL UNIQUE,

name CHAR(40) )

и введение в таблицу companies ссылки (внешнего ключа) на этот классификатор:

CREATE TABLE companies(
. . . . .

type INTEGER )

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

SELECT name, address FROM companies
WHERE type = (SELECT type_id FROM types

WHERE name = “ООО”)

5.12. Объектное расширение SQL в Informix DS/Universal Data Option

5.12.1. Зачем нужна поддержка объектов в серверах БД?

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

Неравноправие атрибутов

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

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

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

CREATE TABLE persons (
tabel_num INTEGER, {табельный номер}
last_name CHAR(40), {фамилия}
first_name CHAR(40), {имя}
second_name CHAR(40), {отчество}
department INTEGER, {отдел}
dolgnost CHAR(20), {должность}
emp_from DATE, {работает с…}
. . . . . .

)

При такой структуре данной таблицы все атрибуты “свалены” в кучу. Более правильно было бы сгруппировать фамилию, имя и отчество в новый составной тип под названием “ФИО”, а отдел и должность - в тип “позиция”:

CREATE ROW TYPE fio_t (
last_name CHAR(40), {фамилия}
first_name CHAR(40), {имя}
second_name CHAR(40) {отчество}

)

CREATE ROW TYPE position_t (
department INTEGER, {отдел}
dolgnost CHAR(20) {должность}

)

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

CREATE TABLE persons (
tabel_num INTEGER, {табельный номер}
fio fio_t, {ФИО}
position position_t, {позиция}
emp_from DATE, {работает с…}
. . . . . .

)

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

Ограниченный набор базовых типов

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

Нельзя сказать, что современные реляционные СУБД такой возможности не предоставляют совсем. Например, такие типы данных в Informix Dynamic Server, как BYTE и TEXT предназначены для хранения больших (до 2 ГБт) двоичных и текстовых объектов соответственно. Сервер обеспечивает только хранение таких объектов, он не может сам обрабатывать данные этих типов. Вся обработка должна проводиться на программеклиенте. То есть для того, чтобы, например, просмотреть все имеющиеся в базе данных отпечатки пальцев и отобрать те, которые похожи на заданный образец, программаклиент должна последовательно просмотреть все имеющиеся отпечатки. Проведение такого просмотра самим сервером, а, тем более, наличие индекса по отпечаткам, значительно бы ускорило обработку такого запроса.

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

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

Рассмотрим простейший пример. Предположим, нам надо хранить информацию об адресах и делать выборку по улице. В Российских городах для названия улиц может использоваться дополнительная нумерация (1-я Парковая, 9-я Парковая) или прилагательное “Большой” или “Малый” (Малая Бронная). Соответственно, существует несколько способов записи таких названий. Следующие три названия обозначают одну и туже улицу:

М.Бронная
Бронная, М.

Малая Бронная

Очевидно, что если мы будем использовать для хранения названия улиц обычные строковые типы, то придется следить за тем, чтобы в базе данных названия улиц хранились в едином, унифицированном формате. А при запросе на поиск по названию улицы потребуется преобразовывать введенное пользователем название к этому единому формату. Было бы удобнее, если бы можно было определить новый тип данных “Название улицы”, который бы делал это автоматически и, следовательно, минимизировал возможные ошибки. Для данного типа нужно было бы переопределить операцию сравнения, чтобы при сравнении двух значений учитывалось разное написание улиц. Кроме того, для данного типа потребуется переопределить операции сравнения, чтобы сортировка значений данного типа проводилась с учетом разных написаний:

SELECT streets FROM table1 ORDER by streets

. . . .
Бауманская
Б.Бронная
М.Бронная
Буденного

. . . .

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

Сложные типы данных

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

Таблица “Дети”

ID	Фамилия	Имя	Отчество	Родитель
7	Иванов	Игорь	Иванович	106
8	Иванова	Анна	Ивановна	106
14	Петров	Сергей	Петрович	107
. .	. . . .	. . .	. . . .	. . ..

Таблица “Сотрудники”:

ID	Фамилия	Имя	Отчество	. . .
106	Иванов	Иван	Иванович	. . . .
107	Петров	Петр	Петрович	. . . .
108	Сидоров	Сидор	Сидорович	. . . .
. .	. . . .	. . .	. . . .	. . ..

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

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

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

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

CREATE TABLE persons (
tabel_num INTEGER, {табельный номер}
last_name CHAR(40), {фамилия}
first_name CHAR(40), {имя}
second_name CHAR(40), {отчество}
children SET OF ( {дети - множество записей вида}
last_name CHAR(40), {фамилия ребенка}
first_name CHAR(40), {имя ребенка}
second_name CHAR(40), {отчество}
was_born DATE {дата рождения}
)
. . . . .

)

Мы рассмотрели один конкретный пример, и убедились, что структура данных “множество” в некоторых задачах может быть очень полезна. Можно привести примеры, когда было бы полезно, если бы в качестве атрибутов в таблице можно было использовать сложные, составные типы данных со структурами “массив”, “список” и т.д. Реляционные СУБД в своей классической теории не позволяют этого сделать.

Специальные методы хранения и доступа

Современные СУБД практически полностью скрывают от программиста методы физической организации хранения данных (физическую модель). Только администратор сервера базы данных, в силу специфики своих задач, должен и может иметь некоторое представление об используемой физической модели данных.

Существующие реляционные СУБД весьма эффективно реализуют способы представлениея, хранения, доступа и поиска традиционных реляционных данных - чисел, текстовых строк, дат и т.д. Для ускорения поиска обычно используются методы индексирования на основе B-деревьев или B+-деревьев. Когда же мы пытаемся выполнить запрос на поиск текстовой строки, содержащей внутри себя некоторую заданную подстроку, например:

SELECT name, address FROM companies WHERE name MATCHES “*Рога*”

или пытаемся выбрать информацию по продажам в феврале в течении нескольких лет:

SELECT price FROM contracts WHERE MONTH(was_signed)=2

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

Существуют специальные методы индексирования для поиска и хранения некоторых типов данных - например, существуют bitmap-индексы, существуют специальные индексы для изображений. Реализовать все возможные типы индексов и все возможные методы хранения на одном сервере БД просто невозможно. Следовательно, надо иметь возможность не просто определять новый тип данных, но и определять методы хранения, доступа и индексирования для данного типа, если стандартные по тем или иным причинам не подходят.

Иерархия данных

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

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

Если мы попытаемся объединить требования отдела кадров и бухгалтерии в единой базе данных, то в стандартных реляционных СУБД мы будем стоять перед выбором: или иметь единую таблицу со всеми возможными атрибутами, или иметь три разных таблицы с разным набором атрибутов.

Первый подход, когда информация о всех сотрудниках хранится в едином файле, приведет к излишнему расходу памяти (серым цветом показаны неиспользуемые поля в таблице):

Фамилия	Имя	Отчество	Должность	Оклад	Премия (%)	Комиссия (%)	Сумма сделок
Иванов	Иван	Иванович	продавец	1000		0.5	500000
Петров	Петр	Петрович	продавец	1000		0.6	400000
Сидоров	Сидор	Сидорович	инженер	1500	20
Матвеев	Матвей	Матвеевич	инженер	1600	20
Степанов	Степан	Степанович	админист.	1700
Кузьмин	Кузьма	Кузьмич	админист.	1800

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

Другой подход предполагает наличие трех разных таблиц - своя таблица для каждой категории сотрудников:

Таблица “Продавцы”

Фамилия	Имя	Отчество	Должность	Оклад	Комиссия (%)	Сумма сделок
Иванов	Иван	Иванович	продавец	1000	0.5	500000
Петров	Петр	Петрович	продавец	1000	0.6	400000

Таблица “Инженеры”

Фамилия	Имя	Отчество	Должность	Оклад	Премия (%)
Сидоров	Сидор	Сидорович	инженер	1500	20
Матвеев	Матвей	Матвеевич	инженер	1600	20

Таблица “Администраторы”

Фамилия	Имя	Отчество	Должность	Оклад
Степанов	Степан	Степанович	админист.	1700
Кузьмин	Кузьма	Кузьмич	админист.	1800

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

5.12.3. Внедрение объектно-ориентированной технологии

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

Концепция объектно-ориентированного подхода

Концепция объектно-ориентированного подхода возникла в конце 70-х - начале 80-х как альтернатива традиционному стилю программирования. Традиционный стиль программирования подразумевал главенствование алгоритма, программы над данными. Такой подход хорошо подходил для вычислительных задач, относительно неплохо подходил для коммерческих, в основном, учетно-расчетных задач. Как альтернатива традиционному подходу постепенно сформировался объектно-ориентированный подход.

Объектно-ориентированный подход предполагал сосредоточиться на описании предметной области как некоторого набора объектов, которые общаются между собой, к которым можно обратиться с запросами, но про внутреннее устройство которых нам на данном уровне абстракции знать не надо. Данная технология не предполагала никаких специальных конструкций в языках программирования - разрабатывать программу с использованием данной технологии можно было на любых языках (С, Фортран, Паскаль, Ассемблер и т.д.).

Следующим шагом была разработка Б.Страуструпом языка С++, который предназначался для поддержки технологии объектно-ориентированного программирования. Для осуществления такой поддержки в С++ был реализован механизм абстрактных типов данных (с такими свойствами, как полиморфизм и инкапсуляция) и механизм наследования типов. Такой симбиоз получился настолько удачным, что сейчас многие просто отождествляют объектно-ориентированное программирование с поддержкой инкапсуляции, полиморфизма, наследования.

Объектно-ориентированные СУБД

Как утверждается в [Манифест 95], СУБД только тогда может считаться объектно-ориентированной, когда она поддерживает следующие “восемь свойств: сложные объекты, идентифицируемость объектов, инкапсуляцию, типы или классы, наследование, перекрытие методов совместно с поздним связыванием, расширяемость и вычислительную полноту”.

Но кроме того, что бы поддерживать в той или иной степени объектно-ориентированную технологию, такая система должна оставаться собственно базой данной и решать связанные с этим вопросы. А именно, обеспечивать операции доступа и преобразования данных, одновременный доступ к данным нескольких пользователей, разграничение доступа и защиту данных от сбоев. В частности, ООСУБД должна предоставлять язык описания данных (ЯОД) и язык манипулирования данными (ЯМД). Язык манипулирования данными должен или быть встраиваемым в какой-либо язык программирования, или быть реализован в виде API.

В модели ODMG93 [Калиниченко96] были описаны и ЯОД, и ЯМД. К сожалению, пока ни одна из фирм не реализовала полностью этот стандарт. Именно отсутствие реально работающего стандарта является сдерживающим фактором в распространении объектно-ориентированных СУБД.

Объектно-реляционные СУБД

Реляционные СУБД, в отличии от “чистых” объектно-ориентированных СУБД, имеют реально работающие стандарты - стандарты на язык запросов SQL. Кроме того, существует огромная база заказчиков, которые пока не готовы отходить от реляционной технологии. И фирмы-производители реляционных СУБД пошли по пути внедрения объектной технологии в отработанную и популярную технологию реляционных СУБД. В частности, сейчас готовится к выходу стандарт SQL-3, в котором уже заложена поддержка объектно-ориентированной концепции.

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

Технология абстрактных типов данных предполагает:

·	инкапсуляцию (сокрытие деталей реализации внутри типа);

·	полиморфизм (применимость одной операции к разным типам и разный способ вычисления в зависимости от типа);

·	позднее связывание (определение реального типа объекта в момент исполнения);

·	расширяемость (возможность определить новый тип);

·	наследуемость типов (возможность определить новый тип на основе существующего);

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

Итак, объектно-реляционный подход представляет собой расширение классический реляционных СУБД, базирующихся на языке SQL. Язык SQL и, соответственно, реляционная модель, расширяются возможностью вводить абстрактные типы данных и организовывать иерархию типов и данных. Следовательно, обеспечиваются все требования, ассоциируемые с объектно-ориентированной технологией (см. Объектно-ориентированные СУБД), но при этом обеспечивается совместимость со старыми приложениями, инструментами и технологиями.

5.12.4. Реализация объектного подхода в Informix

Informix DS/Universal Data Option - объектно-реляционная СУБД

Informix DS/Universal Data Option представляет собой реализацию объектно-ориентированной технологии на основе встраивания механизма абстрактных типов данных и механизма наследования в популярный и надежный сервер реляционных баз данных Informix Dynamic Server.

Встраиваемая объектно-ориентированная технология была известна на практике по объектно-реляционной СУБД Illustra (позднее, приобретеннной фирмой Informix и называвшейся Informix Illustra) [Stonebraker 96].

Определение новых базовых типов

Informix Universal Data Option позволяет вводить новые базовые типы данных. При этом можно использовать как встроенные в Informix Dynamic Server методы доступа и хранения, так и определять новые. Рассмотрим способ создания новых базовых типов с использованием встроенных механизмов хранения. Создание базовых типов вместе с алгоритмами хранения и доступа будет рассмотрено ниже.

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

Если бы речь шла только про размеры, то алгоритм перевода размеров в единую систему измерений четко известен и в базе данных можно было бы хранить только размеры в метрах и сантиметрах. С ценами сложнее - курс обмена зависит от даты конвертации. И если мы будем искать деталь с минимальной ценой, то надо учитывать текущий курс. ОРСУБД Informix DS/Universal Data Option позволяет построить новый базовый тип данных, основанный на существующем, но обеспечивающий автоматическое преобразование к нужному значению. Сами типы вводятся следующими операторами:

CREATE DISTINCT TYPE usd AS MONEY;

CREATE DISTINCT TYPE dm AS MONEY;

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

CREATE FUNCTION usd_to_dm(v usd) RETURNS dm; . . .

CREATE FUNCTION dm_to_usd(v dm) RETURNS usd; . . .

CREATE IMPLICIT CAST (usd AS dm WITH usd_to_dm);

CREATE IMPLICIT CAST (dm AS usd WITH dm_to_usd);

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

Другой причиной, по которой может возникнуть необходимость во введении нового базового типа данных - это принципиальное отсутствие такого типа. Например, для экспериментальных данных, которые будут храниться в нашей базе данных, недостаточна точность, обеспечиваемая стандартным типом FLOAT. Informix DS/Universal Data Option позволяет ввести новый тип данных FLOAT16, будет использовать для хранения своих значений 16 байт и будет соответствовать нашим требованиям по числу значащих цифр в мантиссе и диапазону порядка:

CREATE OPAQUE TYPE float16 (INTERNALLENGTH=16, ALIGHNMENT=4);

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

{обязательные функции преобразования в строку и обратно}
CREATE FUNCTION float16_out(float16) RETURNING LVARCHAR . . . .;

CREATE FUNCTION float16_in(lvarchar) RETURNING float16 . . . .;

{реализация стандартных операторов ’+’,’-’,’*’,’/’,’>’,’<’ и т.д.}
CREATE FUNCTION Plus(float16, float16) RETURNING float16 . . . .;
CREATE FUNCTION Plus(float16, float) RETURNING float16 . . . .;

. . . . . . .

После того, как все нужные функции определены, можно использовать тип float16 наравне с другими базовыми типами (FLOAT, SMALLFLOAT, INTEGER и т.д.). При этом для хранения, поиска и индексирования используются стандартные механизмы Informix Dynamic Server.

Составные типы данных

Informix DS/Universal Data Option позволяет определять новые составные типы данных. К доступным структурам, которые можно использовать для построения составных типов, относятся:

запись

множество

список

Запись представляет собой возможность ввести именованные поля. Cтруктура запись структуре record в языке Паскаль и struct в языке C/C++. Тип данных со структурой запись вводится оператором:

CREATE ROW TYPE <имя типа> (
<имя поля> <тип поля>, . . .

)

Например:

CREATE ROW TYPE fio_t (
last_name CHAR(40), {фамилия}
first_name CHAR(40), {имя}
second_name CHAR(40) {отчество}

)

Cозданный таким образом составной тип может использоваться наравне и с предопределенными типами для описания колонок в отношении.

CREATE TABLE persons (
tabel_num INTEGER, {табельный номер}
fio fio_t, {ФИО}
. . . . . .

)

Для доступа к отдельным полям внутри типа записи используется традиционный синтаксис - через точку надо указать имя поля:

SELECT tabel_num, fio.last_name, fio.first_name FROM persons

WHERE tabel_num = 157

Множество представляет собой неупорядоченное множество значений. В Informix Universal Data Option используется два варианта реализации структуры множества - set и multiset. Первая структура (будем называть ее просто множеством) не допускает повторений элементов внутри себя. Вторая структура (будем называть ее мультимножеством) допускает повторение элементов. Приведем пример, как можно ввести в таблицу persons в качестве атрибута тип данных “дети” со структурой SET:

CREATE TYPE children_t SET (
fio fio_t,
wasborn DATE

)

CREATE TABLE persons (
tabel_num INTEGER, {табельный номер}
fio fio_t, {ФИО}
children children_t, {дети}
. . . . .

)

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

CREATE TYPE lab_byog_t LIST (
name CHAR(20),
work_from DATETIME YEAR TO DAY,
position CHAR(10)

)

CREATE TABLE persons (
tabel_num INTEGER, {табельный номер}
fio fio_t, {ФИО}
children children_t, {дети}
lab_byog lab_byog_t, {трудовая биография}
. . . . .

)

Наследование типов и данных

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

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

CREATE ROW TYPE employee_t (
tabel_num INTEGER, {табельный номер}
last_name CHAR(40), {фамилия}
first_name CHAR(40), {имя}
second_name CHAR(40), {отчество}
dolgnost CHAR(20) {должность}
base_salary MONEY {оклад}

)

В этом типе данных содержится вся информация, которая может быть интересна отделу кадров предприятия. Однако, как мы выяснили выше, для финансового отдела нужны дополнительные сведения. Для продавцов и инженеров дополнительно должна храиться и другая информация, необходимая для расчета зарплаты. Эту информацию можно учесть, создав типы engineer_t и sale_t как наследники типа employee_t:

CREATE ROW TYPE engineer_t (
bonus DECIMAL (5,2) {премия в процентах}

) UNDER TYPE employee_t

CREATE ROW TYPE sale_t (
comission DECIMAL (5,2), {размер комиссионных в %}
revenue MONEY {сумма заключенных котрактов}

) UNDER TYPE employee_t

Таким образом, имеется 3 типа данных, два из которых (sales_t и engineer_t) являются наследниками одного (employee_t). Если мы используем эти типы для создания таблиц, мы можем создать иерархию данных:

CREATE TABLE employees OF TYPE employee_t;
CREATE TABLE engineers OF TYPE engineer_t UNDER TABLE employees;

CREATE TABLE sales OF TYPE sale_t UNDER TABLE employees;

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

Таблица employees

Фамилия

Имя

Отчество

Должность

Оклад

Степанов

Степан

Степанович

админист.

1700

Кузьмин

Кузьма

Кузьмич

админист.

1800

Таблица Sales

Комиссия

(%)

Сумма

сделок

Иванов

Иван

Иванович

продавец

1000

0.5

500000

Петров

Петр

Петрович

продавец

1000

0.6

400000

Премия

(%)

Таблица engineers

Сидоров

Сидор

Сидорович

инженер

1500

Матвеев

Матвей

Матвеевич

инженер

1600

В более сложных приложениях из таблиц (и соответсвующих им типов) можно организовывать и более сложные, многоуровневые иерархии.

Выборка данных по таблице employees даст возможность увидеть всех сотрудников:

SELECT * FROM employees

Фамилия	Имя	Отчество	Должность	Оклад
Степанов	Степан	Степанович	админист.	1700
Кузьмин	Кузьма	Кузьмич	админист.	1800
Иванов	Иван	Иванович	продавец	1000
Петров	Петр	Петрович	продавец	1000
Сидоров	Сидор	Сидорович	инженер	1500
Матвеев	Матвей	Матвеевич	инженер	1600

Выборка данных по таблице engineers даст возможность увидеть всех инженеров, но только их, причем будут доступны и те поля, которые принадлежат типу engineer_t, но не принадлежат типу-родителю employee_t:

SELECT * FROM engineers

Фамилия	Имя	Отчество	Должность	Оклад	Премия (%)
Сидоров	Сидор	Сидорович	инженер	1500	20
Матвеев	Матвей	Матвеевич	инженер	1600	20

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

SELECT * FROM ONLY(employees)

Фамилия	Имя	Отчество	Должность	Оклад
Степанов	Степан	Степанович	админист.	1700
Кузьмин	Кузьма	Кузьмич	админист.	1800

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

CREATE FUNCTION zarplata(p employee_t) RETURNING MONEY;

RETURN (p.base_salary);

CREATE FUNCTION zarplata(p engineer_t) RETURNING MONEY;

RETURN (p.base_salary + p.base_salary*p.bonus/100);

CREATE FUNCTION zarplata(p sale_t) RETURNING MONEY;

RETURN (p.base_salary + p.comission*p.revenue/100);

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

SELECT zarplata(e) FROM employees e;

Специальные методы хранения, поиска и индексации

Informix DS/Universal Data Option позволяет вводить новые базовые типы данных одновременно с введением специальных алгоритмов хранения, доступа и индексирования, отличных от стандартных, реализованных в сервере.

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

{описание нового типа}
CREATE OPAQUE TYPE picture (
INTERNALLENGTH=VARIABLE,

. . . . . )

{реализация алгоритма записи на диск}
CREATE FUNCTION assign(pic picture) RETURNS POINTER
EXTERNAL NAME ‘/usr/bin/my_assign()’

LANGUAGE C NOT VARIANT;

Если мы хотим для нового типа ввести специальные алгоритмы индексации и поиска, то надо описать следующие функции:

·	my_am_scancost_index - оценка стоимости использования индекса

·	my_am_open_index - открыть внешний файл с индексом

·	my_am_bedinscan_index - начать просмотр по индексу

·	my_am_getnext_index - взять следующий элемент индекса

·	my_am_endscan_index - закончить просмотр индекса

·	my_am_close_index - закрыть внешний файл с индексом

Далее, надо указать, что вводится новый способ индексирования (pic_tree) и разработанные функции будут использоваться для индексирования созданного типа данных:

CREATE ACCESS_METHOD pic_tree(
am_scancost_index = my_am_scancost_index,
am_open_index = my_am_open_index ,
. . . . .

)

Далее, использовать новый индекс можно в команде:

CREATE TABLE photos(
photo picture,
. . . . .
)

CREATE INDEX pic1 ON photos(photo) USING pic_tree;

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

5.12.5. Итак…

Informix DS/Universal Data Option обеспечивает объектно-ориентированную технологию технологию за счет реализации механизмов инкапсуляции, полиморфизма и наследования в среду традиционной реляционной СУБД. Можно спорить о достоинствах или недостатках такого подхода, но реальностью является то, что такая технология нужна, и она реально существует.

Грачев А.Ю. Введение в СУБД Informix