9. Домены (DOMAIN)

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

У домена есть обязательные и необязательные атрибуты. Тип данных является обязательным атрибутом. Необязательные атрибуты: значение по умолчанию, ограничение NOT NULL, условие CHECK, набор символов (для символьных типов данных и текстовых BLOB), порядок сортировки (для символьных типов данных).

При определении столбца на основе домена можно переопределить некоторые атрибуты, унаследованные от домена.

Таблица 9.1 Правила переопределения атрибутов домена при определении столбца

Атрибут	Возможность переопределения	Описание
Тип данных	Нет
Значение по умолчанию	Есть
Набор символов	Есть	Можно использовать для восстановления значений базы данных по умолчанию для столбца.
Порядок сортировки	Есть
Условие CHECK	Есть	Для добавления нового условия можно использовать предложения CHECK в операторах CREATE и ALTER на уровне таблицы.
Ограничение NOT NULL	Нет	Часто лучше не указывать NOT NULL при определении домена, а добавить его при определении столбца, если это необходимо.

9.1. Создание домена

Для создания нового домена в базе данных используется оператор CREATE DOMAIN. Синтаксис оператора представлен в листинге :

Листинг 9.1 Синтаксис оператора создания домена CREATE DOMAIN

CREATE DOMAIN <имя домена> [AS] <тип данных>
   [DEFAULT {<литерал> | NULL | <контекстная переменная>}]
   [NOT NULL]
   [CHECK (<условие домена>)]
   [CHARACTER SET <набор символов> [COLLATE <порядок сортировки>] ];

<тип данных> ::=
   <скалярный тип данных> | <тип BLOB> | <массив>

<скалярный тип данных> ::=
  SMALLINT | INT[EGER] | BIGINT | INT128
| REAL | FLOAT [(точность)] | DOUBLE PRECISION
| DECFLOAT [(точность)]
| BOOLEAN
| DATE
| TIME [{WITHOUT | WITH} TIME ZONE]
| TIMESTAMP [{WITHOUT | WITH} TIME ZONE]
| {DECIMAL | DEC | NUMERIC} [(точность [, масштаб])]
| {VARCHAR | {CHAR | CHARACTER} VARYING} (длина)
   [CHARACTER SET <набор символов>]
| {CHAR | CHARACTER} [(длина)] [CHARACTER SET <набор символов>]
| {NCHAR | NATIONAL {CHARACTER | CHAR}} VARYING (длина)
| {NCHAR | NATIONAL {CHARACTER | CHAR}} [(длина)]
| BINARY [(длина)]
| {VARBINARY | BINARY VARYING} (длина)

<тип BLOB> ::=
  BLOB [SUB_TYPE {<номер подтипа> | <имя подтипа>}]
  [SEGMENT SIZE <размер сегмента>] [CHARACTER SET <набор символов>]
| BLOB [(<размер сегмента> [, <номер подтипа>])]
| BLOB [(, <номер подтипа>)]

<массив> ::=
   <скалярный тип данных> <размерность массива>

<размерность массива> ::= '[' [m:]n [,[m:]n ...] ']'

<условие домена> ::= {
   <значение> <оператор сравнения> <значение>
 | <значение> [NOT] IN ({<значение> [, <значение> ...] | <поиск одного>})
 | <значение> [NOT] BETWEEN <значение> AND <значение>
 | <значение> [NOT] LIKE <шаблон> [ESCAPE <символ>]
 | <значение> [NOT] SIMILAR TO <значение> [ESCAPE <значение>]
 | <значение> IS [NOT] NULL
 | <значение> IS [NOT] DISTINCT FROM <значение>
 | <значение> <оператор сравнения> {ALL | SOME | ANY} (<поиск одного>)
 | [NOT] EXISTS (<поиск многих>)
 | [NOT] SINGULAR (<поиск многих>)
 | <значение> [NOT] CONTAINING <значение>
 | <значение> [NOT] STARTING [WITH] <значение>
 | (<условие домена>)
 | NOT <условие домена>
 | <условие домена> OR <условие домена>
 | <условие домена> AND <условие домена>
}

<значение> ::=
  VALUE [[<элемент массива>]]
| <литерал>
| <контекстная переменная>
| <выражение>
| NULL
| NEXT VALUE FOR <имя генератора>
| GEN_ID(<имя генератора>, <значение>)
| CAST(<значение> AS <тип данных>)
| (<выбор одного>)
| <обычная встроенная функция> (<параметры>)
| <агрегатная функция в операторе SELECT>
| <функция UDF> [(<параметр> [, <параметр> ...])]

<оператор сравнения> ::=
  <> | != | ^= | ~= | = | < | > | <= | >=
| !< | ^< | ~< | !> | ^> | ~>

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

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

Примечание

Создавать домен может администратор и пользователь с привилегией CREATE DOMAIN.

Задание типа данных

Тип данных задается следующей синтаксической конструкцией (листинг ):

Листинг 9.2 Синтаксис задания типа данных

<тип данных> ::=
   <скалярный тип данных> | <тип BLOB> | <массив>

<скалярный тип данных> ::=
  SMALLINT | INT[EGER] | BIGINT | INT128
| REAL | FLOAT [(точность)] | DOUBLE PRECISION
| DECFLOAT [(точность)]
| BOOLEAN
| DATE
| TIME [{WITHOUT | WITH} TIME ZONE]
| TIMESTAMP [{WITHOUT | WITH} TIME ZONE]
| {DECIMAL | DEC | NUMERIC} [(точность [, масштаб])]
| {VARCHAR | {CHAR | CHARACTER} VARYING} (длина)
   [CHARACTER SET <набор символов>]
| {CHAR | CHARACTER} [(длина)] [CHARACTER SET <набор символов>]
| {NCHAR | NATIONAL {CHARACTER | CHAR}} VARYING (длина)
| {NCHAR | NATIONAL {CHARACTER | CHAR}} [(длина)]
| BINARY [(длина)]
| {VARBINARY | BINARY VARYING} (длина)

<тип BLOB> ::=
  BLOB [SUB_TYPE {<номер подтипа> | <имя подтипа>}]
  [SEGMENT SIZE <размер сегмента>] [CHARACTER SET <набор символов>]
| BLOB [(<размер сегмента> [, <номер подтипа>])]
| BLOB [(, <номер подтипа>)]

<массив> ::=
   <скалярный тип данных> <размерность массива>

<размерность массива> ::= '[' [m:]n [,[m:]n ...] ']'

Массивы

Если домен должен быть массивом, базовым типом может быть любой тип данных SQL, кроме BLOB и массива. Размерность массива указывается в квадратных скобках. Для каждой размерности массива одно или два целых числа определяют нижнюю и верхнюю границы диапазона его индексов. Нижняя граница является неявной (предполагается значение 1), и требуется указать только верхнюю. Для явного определения диапазона можно использовать два числа, разделенных двоеточием (':'), где второе число больше первого. Границы могут быть меньше нуля, при условии, что верхняя граница больше нижней.

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

Размерности проверяются только в том случае, если массив действительно существует. Это означает, что сообщения об ошибках, связанных с размерностью, не будут возвращены, если выбор определенного элемента ничего не дает или если поле массива является NULL.

Массив, как и тип данных BLOB, не хранится непосредственно в строке таблицы. Строка содержит лишь ссылку на страницу базы данных, где располагаются элементы массива. Элементы массива располагаются в других страницах базы данных.

Строковые типы

С помощью предложения CHARACTER SET можно указать набор символов для типов CHAR, VARCHAR и BLOB SUB_TYPE TEXT. Если набор символов не указан, то будет использоваться набор символов, указанный для базы данных DEFAULT CHARACTER SET. Если для базы данных не определён набор символов по умолчанию, то при создании символьного домена по применится набор символов NONE.

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

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

Необязательное предложение DEFAULT определяет значение по умолчанию для домена. Это значение будет добавлено в столбец, основанный на этом домене, при выполнении оператора INSERT, если в операторе DML для него не указано значение.

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

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

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

Значение NOT NULL

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

Любая попытка поместить в такой столбец пустое значение в операторе INSERT или изменить на NULL существующее значение в операторе UPDATE приводит к исключению базы данных.

Примечание

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

Условие домена

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

Условие - это логическое выражение (также называемое предикатом), которое может возвращать результаты TRUE, FALSE и UNKNOWN. Условие считается выполненным, если предикат возвращает значение TRUE или UNKNOWN. Если предикат возвращает FALSE, то условие не выполнено. Подробнее обо всех предикатах рассказано в главе 3.

Условие домена может быть достаточно сложным. Его синтаксис показан в листинге .

Листинг 9.3 Синтаксис задания условий домена

<условие домена> ::= {
   <значение> <оператор сравнения> <значение>
 | <значение> [NOT] IN ({<значение> [, <значение> ...] | <поиск одного>})
 | <значение> [NOT] BETWEEN <значение> AND <значение>
 | <значение> [NOT] LIKE <шаблон> [ESCAPE <символ>]
 | <значение> [NOT] SIMILAR TO <значение> [ESCAPE <значение>]
 | <значение> IS [NOT] NULL
 | <значение> IS [NOT] DISTINCT FROM <значение>
 | <значение> <оператор сравнения> {ALL | SOME | ANY} (<поиск одного>)
 | [NOT] EXISTS (<поиск многих>)
 | [NOT] SINGULAR (<поиск многих>)
 | <значение> [NOT] CONTAINING <значение>
 | <значение> [NOT] STARTING [WITH] <значение>
 | (<условие домена>)
 | NOT <условие домена>
 | <условие домена> OR <условие домена>
 | <условие домена> AND <условие домена>
}

<значение> ::=
  VALUE [[<элемент массива>]]
| <литерал>
| <контекстная переменная>
| <выражение>
| NULL
| NEXT VALUE FOR <имя генератора>
| GEN_ID(<имя генератора>, <значение>)
| CAST(<значение> AS <тип данных>)
| (<выбор одного>)
| <обычная встроенная функция> (<параметры>)
| <агрегатная функция в операторе SELECT>
| <функция UDF> [(<параметр> [, <параметр> ...])]

<оператор сравнения> ::=
  <> | != | ^= | ~= | = | < | > | <= | >=
| !< | ^< | ~< | !> | ^> | ~>

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

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

Поиск многих — оператор SELECT, возвращающий ноль или произвольное количество значений нескольких столбцов.

Ключевое слово VALUE

В значении нельзя использовать имена доменов или столбцов таблиц. В качестве одного из элементов значения может использоваться ключевое слово VALUE.

Ключевое слово VALUE является заменителем имени столбца, который при создании таблицы будет основан на данном домене. Обычно в условиях домена VALUE помещается в левой части оператора, но допустимо также помещение его в качестве, например, элемента выражения, и в правую часть. К этому ключевому слову можно применять функцию UPPER, переводящую все буквы в добавляемом/изменяемом значении столбца, основанного на данном домене в верхний регистр. Допустимо также использование функции преобразования типов данных CAST, функции выделения подстроки SUBSTRING, функции удаления начальных и конечных пробелов TRIM и других встроенных функций.

Если домен описывает массив, то, как обычно, после ключевого слова VALUE в квадратных скобках нужно указать индекс (индексы) конкретного элемента массива.

Любое выражение может содержать ключевое слово VALUE.

В варианте VALUE <оператор> <значение> помещаемое значение в столбец, основанный на этом домене, сравнивается с некоторым литералом или выражением. Чтобы значение было помещено в столбец, сравнение должно давать значение «истина».

Пример 1.

Пример описания условия домена для числового столбца:

CHECK (VALUE >= 18)

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

Пример 2.

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

CHECK (SUBSTRING(VALUE FROM 1 FOR 1) = SUBSTRING(VALUE FROM 2 FOR 1));

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

CHECK (SUBSTRING(UPPER(VALUE) FROM 1 FOR 1) =
       SUBSTRING(UPPER(VALUE) FROM 2 FOR 1));

С тем же результатом в предыдущем выражении можно вместо встроенной функции UPPER использовать функцию LOWER.

Следует помнить, что в некоторых вариантах проверки условия CHECK, если для соответствующего столбца допустимо и пустое значение, то проверка на NULL должна быть выполнена как отдельная часть проверки условия. Иначе в некоторых случаях будет возвращено значение UNKNOWN, что вызовет исключение базы данных. Проверку на пустое значение нужно соединить операцией дизъюнкции (ключевое слово OR) с основной содержательной проверкой:

CHECK ((VALUE IS NULL) OR (<остальные виды проверок>))

Пример 3.

Создание домена, который может принимать значения 'Да' и 'Нет'.

CREATE DOMAIN D_BOOLEAN AS CHAR(3)
CHECK (VALUE IN ('Да' , 'Нет' ));

Пример 4.

Создание домена со значением по умолчанию.

CREATE DOMAIN D_DATE AS DATE
DEFAULT CURRENT_DATE
NOT NULL;

Ключевое слово COLLATE

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

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

9.2. Изменение домена

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

Листинг 9.4 Синтаксис оператора изменения домена ALTER DOMAIN

ALTER DOMAIN <имя>
   [TO <новое имя>]
   [{ SET DEFAULT {<литерал> | NULL | <контекстная переменная>}
    | DROP DEFAULT}]
   [{ SET | DROP} NOT NULL]
   [{ ADD [CONSTRAINT] CHECK (<условие домена>)
    | DROP CONSTRAINT}]
   [TYPE <тип данных> [CHARACTER SET <набор символов> [COLLATE <порядок сортировки>]]];

В одном операторе ALTER DOMAIN можно выполнить любое количество изменений домена.

Примечание

Изменить существующий домен может владелец домена (его создатель), пользователь с административными привилегиями или пользователь с привилегией ALTER ANY DOMAIN.

Изменение имени

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

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

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

Предложение DROP DEFAULT удаляет существующее значение по умолчанию. Значением по умолчанию в этом случае неявно становится значение NULL.

Условие CHECK

Предложение ADD [CONSTRAINT] CHECK добавляет условие домена. Если у домена уже существует условие, то вначале его нужно удалить при помощи предложения DROP CONSTRAINT иначе вы получите сообщение об ошибке.

Предложение DROP CONSTRAINT удаляет существующее ограничение CHECK домена. Если домен не содержит ограничения с таким именем, то выполнение подобного оператора не вызовет сообщения об ошибке.

Изменение типа домена

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

При изменении атрибутов домена существующий PSQL может стать невалидным. Информация о том, что модуль PSQL стал невалидным отразится в поле RDB$VALID_BLR в системных таблицах RDB$PROCEDURES, RDB$FUNCTIONS и RDB$TRIGGERS. RDB$VALID_BLR устанавливается в 0, если код стал невалидным в результате изменения домена.

create domain d1 varchar(30) character set UTF8 collate UNICODE;
alter domain d1 type varchar(30) character set UTF8 collate UCS_BASIC;

Ограничение NOT NULL

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

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

Предложение DROP NOT NULL удаляет ограничение NOT NULL для домена. Явное ограничение NOT NULL на столбце, основанном на домене, перекрывает характеристику домена. В этом случае снятие ограничения NOT NULL с домена не распространится на столбец.

Пример изменения домена

Пример. В этом примере выполняются радикальные изменения в описании характеристик домена. Все это делается в одном операторе:

ALTER DOMAIN D099
  DROP DEFAULT
  SET DEFAULT USER
  DROP CONSTRAINT
  ADD CONSTRAINT
     CHECK (SUBSTRING(UPPER(VALUE) FROM 1 FOR 1) =
            SUBSTRING(UPPER(VALUE) FROM 2 FOR 1));

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

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

9.3. Удаление домена

Для удаления домена используется оператор DROP DOMAIN. Его синтаксис представлен в листинге .

Листинг 9.5 Синтаксис оператора удаления домена DROP DOMAIN

DROP DOMAIN <имя домена>;

Оператор DROP DOMAIN удаляет существующий в базе данных домен. Невозможно удалить домен, если на него ссылаются столбцы таблиц базы данных или он используется в каком-либо модуле PSQL. Чтобы удалить используемый домен, необходимо удалить все столбцы, ссылающиеся на него, и удалить все ссылки на домен из модулей PSQL.

Примечание

Удалить существующий домен может владелец домена (его создатель), пользователь с административными привилегиями или пользователь с привилегией DROP ANY DOMAIN.

Пример. Чтобы удалить домен CODCOUNTRY, нужно выполнить оператор:

DROP DOMAIN CODCOUNTRY;

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

9.4. Примечание домена

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

Листинг 9.6 Синтаксис оператора примечания домена COMMENT ON DOMAIN

COMMENT ON
   DOMAIN <имя домена> IS {'<текст примечания>' | NULL};

Текст примечания любого домена можно изменять произвольное количество раз при выполнении оператора COMMENT ON DOMAIN. Значение NULL удаляет существующее примечание. Примечание домена может служить средством документирования разрабатываемой программной системы.

Примечание

Выполнить оператор COMMENT ON DOMAIN могут администраторы, владельцы домена или пользователи с привилегией ALTER ANY DOMAIN.