Delphi и Interbase: Часть II, запросы и метаданные

Традиционное лирическое отступление

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

* При попытке открытия созданной базы прога валится в эксепшн «Operation cancelled at user’s request». Это связано с тем, что проект писался для Delphi 5.0, и в нём используется стандартное окно для подключения к базе. В версии 7.0 (в которой и возникала эта ошибка) стандартный диалог перекочевал в модуль DbLogDlg, который надо было просто внести в uses.
* При попытке создания или открытия базы получаем сообщение «unavailable resource». Это связано с изобилием различных версий ib (и его клонов). В частности, Firebird 1.5 не понимает прямое указание файла на диске. Если вы напишите IbDatabase1.DatabaseName := ‘c:\test.gdb’ – вы рискуете нарваться именно на такую ошибку. Правильнее было бы писать 'localhost:c:\test.gdb'. В некоторых версиях библиотек IBX & FIB вы можете указать принудительно, какой используется протокол, и является ли запрашиваемый файл локальным. В “голой” Delphi 7.0 (без апдейтов) на версии Firebird 1.5.3 – подобная ошибка гарантирована. Чтобы её избежать, надо просто полностью указывать путь (даже локальный) в имени базы данных.


О чём пойдёт речь далее. Во-первых, об обещанных метаданных, т.е. о том, как именно IB хранит сведения о структуре базы, как эти сведения из него выковырять, и для чего эти сведения нужны. А для этого нам понадобится «во-вторых - научится строить запросы к БД и как-то отображать их результаты. Для простоты будет использоваться (и наращиваться) пример из предыдущей статьи. Как и ранее, изложение будет строится по принципу «давайте поглядим, что это мы тут понаписали». Т.е. сначала будет приводится код, а потом объяснение, как это всё живёт. Материал не был и не будет систематическим изложением чего-либо, если по ходу работы я буду касаться вещей, о которых ещё не говорил – то по возможности буду останавливаться и говорить.
Я сильно подозреваю, что материал будет выложен пока не весь, потому что чисто физически подготовить и вывалить всё сразу не хватает ни рук, ни времени. Пока будет выложено неполное описание системных таблиц, которое будет закончено где-то через неделю, и потом пойдёт речь уже о том, что как и что на основе этих таблиц можно получить. Также готовится третья часть, в которой по шагам расписано создание простейшего приложения на Delphi, работающего с СУБД Firebird, от постановки задачи, проектировая схемы БД, и до методов реализации пользовательского интерфейса. Ну а пока...

Таки метаданные

Основной таблицей является rdb$relations. Основной в том смысле, что она содержит имена всех других таблиц, и зная одно это единственное имя, из БД можно достать буквально всё – все метаданные и сами данные, которые она содержит. (Многие поля, да и целые таблицы, используются только для внутренних процедур самого IB, практическое их применение с точки зрения разработки приложений мне неизвестно, и такие поля/таблицы я буду пропускать. Если кто-то захочет что-то поправить/дополнить – добро пожаловать). Удобства ради описания системных таблиц будут приведены в алфавитном порядке. Собственно говоря, материал ниже представляет собой достаточно вольный перевод из Language Reference, оставшемуся в наследство ещё от IB6.0, и на 99% он продолжает оставаться корректным и по сей день, но несоответствия возможны, особенно там, где дело касается различных бранчей различных клонов. Если у вас возникли подозрения о несоответсвии классического описания тому конкретному билду, которую вы используете, следует внимательно читать идущие с ним Release Notes. В свою очередь я буду благодарен за сведения о подобных несоответсвиях, и по мере накопления, буду стараться отражать их здесь.

rdb$character_sets

В этой таблице хранятся все поддерживаемые базой наборы символов (чарсеты). Эта информация иногда бывает реально необходима, например, с вашей БД работают клиенты из разных стран (т.е. с разной локализацией ПО), и при подключении им необходимо выбрать из списка свою кодировку. Где этот список взять? Именно здесь.
Поле Значение
RDB$CHARACTER_SET_NAME Наименование набора символов
RDB$DEFAULT_COLLATE_NAME Наименование режима сортировки
RDB$CHARACTER_SET_ID Идентификатор набора символов
RDB$BYTES_PER_CHARACTER Кол-во байтов на символ

Следует обратить внимание на последнее поле. Оно помогает определить, сколько места на самом деле будет занимать в базе объявленный тип строковых данных. Люди, уверенные, что char (10) занимает 10 байт –не совсем правы, всё зависит от того, какой набор символов указан для этого типа. В кодировке win1251 или ASCII десятисимвольная строка действительно займёт 10 байт (на самом деле – чуть больше, но я сознательно упрощаю), а вот в UNICODE, для которого на хранение символа отводится 3 байта – уже 30.
Каждый набор символов может предоставлять несколько режимов сортировки (collation), и они хранятся в следующей простенькой таблице, а именно:

rdb$collations

Поле Значение
RDB$COLLATION_NAME Наименование режима сортировки
RDB$COLLATION_ID Идентификатор режима сортировки
RDB$CHARACTER_SET_ID Идентификатор набора символов

Если вы посмотрите на данные, которые содержит эта таблица, вам станет понятна разница между COLLATE, указываемым при созданнии символьного поля, и LC_CTYPE, указываемым в подключении к базе. И то и другое, зачастую, содержит одно и то же выражение, например, …=win1251, но в первом случае это режим сортировки, который будет использоватся при указании в запросе SELECT оператора ORDER_BY, а во втором случае – это указание набора символов, которые, собственно, и будут сортироватся. В данных этой таблицы вы увидите, что, например, одному набору символов CYRL («досовская» кодировка, в винде эквивалентно codepage=866) соответствуют аж три режима сортировки: CYRL, PDOX_CYRL и DB_RUS.

rdb$field_dimensions

Как уже упоминалось, здесь для каждого поля c типом данных массив можно узнать размерности и границы этого массива.
Поле Значение
RDB$FIELD_NAME Имя домена
RDB$DIMENSION Индекс размерности, нумерация с 0
RDB$LOWER_BOUND Нижняя граница
RDB$UPPER_BOUND Верхняя граница.

Чтобы сразу всё стало понятно, приведу пример значений в этой таблице для домена NEW_DOMAIN, объявленного как массив [0{2,0{5].
RDB$FIELD_NAME RDB$DIMENSION RDB$LOWER_BOUND RDB$UPPER_BOUND
NEW_DOMAIN 0 0 3
NEW_DOMAIN 1 0 5

Вообще говоря, использование массивов в качестве типов данных противоречит самой концепции реляционных баз, т.к. любая таблица, по сути, уже является массивом. Отображение этих типов, как правило, не поддерживает ни одна штатная (да и не только) утилита. Но тем не менее, функционал для работы с массивами есть, и в некоторых очень специфических случаях (например, хранение результатов каких-то измерений, хранение векторных фигур в виде массива их относительных координат) это тип данных может пригодится. Но следует учитывать, что в IB массив – это, скорее, эмуляция типа данных, а не самостоятельный тип данных в чистом виде, такой как varchar или integer, т.к. при создании поля типа «массив» реальным типом этого поля будет BLOB.

rdb$fields

Вопреки названию, здесь живут не поля, а домены. В этом легко убедиться, если вы создадите в том же IBExpert’е новый домен – он появится именно здесь. Поэтому во избежание путаницы, применительно к метаданным IB, FIELD я буду называть доменом, а RELATION_FIELD – полем. Кстати говоря, даже если при создании нового поля в таблице вы не указываете домен, IB создаст его сам, обзовёт каким-нибудь малопонятным именем, типа RDB$1, и засунет его сюда. И в конечном итоге ваше «бездоменное» поле, на уровне метаданных, всё равно будет ссылаться на сгенерированный автоматически домен! Это одна из причин, почему хорошей практикой считается создавать домены ДЛЯ ВСЕХ создаваемых полей самому.
Поле Значение
RDB$FIELD_NAME Имя домена
RDB$VALIDATION_SOURCE Проверка (CHECK), введённая при создании домена
RDB$COMPUTED_SOURCE Выражение для автоматического вычисления поля
RDB$DEFAULT_SOURCE Выражение для генерации значения по умолчанию
RDB$FIELD_LENGTH Длина поля
RDB$FIELD_SCALE Кол-во знаков после запятой для десятичных типов
RDB$FIELD_TYPE Тип поля:

* 261 - BLOB
* 14 - CHAR
* 40 - CSTRING
* 11 - DFLOAT
* 27 - DOUBLE
* 10 - FLOAT
* 16 - INT64
* 8 - INTEGER
* 9 - QUAD
* 7 - SMALLINT
* 12 - DATE (Диалект 3)
* 13 - TIME
* 35 - TIMESTAMP
* 37 - VARCHAR

RDB$FIELD_SUB_TYPE Подтип поля (например, BLOB-поля могут быть текстовыми и бинарными)
RDB$DESCRIPTION Описание, задаваемое при создании поля
RDB$SYSTEM_FLAG Для системных записей = 1
RDB$SEGMENT_LENGTH Длина сегмента для BLOB-полей
RDB$DIMENSIONS пределяет размерность для массивов. (Для массива [0{2,0{5] будет равен двум). Границы массива можно посмотреть в таблице rdb$dimensions (см. Ниже)
RDB$NULL_FLAG Равно единице, если домен NOT NULL
RDB$CHARACTER_LENGTH Для строковых типов – кол-во символов
RDB$COLLATION_ID Идентификатор режима сортировки
RDB$CHARACTER_SET_ID Идентификатор набора символов
RDB$FIELD_PRECISION Точность числа (для вещественных типов)

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

SELECT rdb$field_name
FROM rdb$fields
WHERE rdb$system_flag = 0

Получение списка всех доменов созданных через CREATE DOMAIN (т.е. кроме системных и созданных автоматически)

SELECT rdb$field_name
FROM rdb$fields
WHERE rdb$field_name NOT STARTING WITH "RDB$"


Изврат, используемый во втором запросе – единственный известный мне способ отделить домены, созданные «руками», через оператор CREATE DOMAIN, от доменов, созданных самим IB. На практике я встречался с ситуацией, когда разработчики все «свои» домены начинали с определённого префикса, например, «T_» или «D_», и вытаскивали нужные значения по этому префиксу. Чтобы не наступить на грабли, следует чётко понимать, что поле rdb$system_flag будет равно единице только для тех доменов, которые используются для описания таблиц метаданных, все остальные домены, в т.ч. и созданные автоматом и, в некоторой степени вроде как «системные», с точки зрения IB таковыми не являются – для них для всех системный флаг будет сброшен в ноль. Также, во втором запросе проверять rdb$system_flag на 1 не имеет смысла, потому что все системные домены, для которых флаг взведён в 1, всегда начинаются с ‘RDB$’, а такая проверка у нас и так есть.

rdb$relation_fields

Здесь хранится список полей для всех таблиц (отношений), перечисленных в таблице rdb$relations.
Поле Значение
RDB$FIELD_NAME Имя поле. Комбинация значений в RDB$FIELD_NAME и следующем, RDB$RELATION_NAME, всегда уникальна.
RDB$RELATION_NAME Имя таблицы (отношения)
RDB$FIELD_SOURCE Содержит имя домена из RDB$FIELDS, если поле основано на домене (Как уже говорилось, на самом деле оно всегда на нём основано)
RDB$BASE_FIELD Для просмотров: определяет имя поля, на котором оно основано.
RDB$FIELD_POSITION Порядковый номер поля. Определяет порядок следования полей при запросах вида SELECT *. Значение может повторяться даже в рамках одной таблицы (соответственно, в каком порядке будут выводится такие поля - не знает никто)
RDB$VIEW_CONTEXT Для просмотров: имя таблицы, содержащей поле, указанное в RDB$BASE_FIELD (короче говоря, пара RDB$BASE_FIELD и RDB$VIEW_CONTEXT определяют поле и таблицу, используемые в SELECT просмотра для данного поля в RDB$RELATION_FIELDS)
RDB$DESCRIPTION Описание, задаваемое при создании поля
RDB$SYSTEM_FLAG Для системных записей = 1
RDB$SECURITY_CLASS Используется для назначение прав, ссылается на RDB$SECURITY_CLASSES
RDB$NULL_FLAG Указывает, что поле может содержать NULL
RDB$DEFAULT_SOURCE Значение по умолчанию, задаваемое SQL-выражением
RDB$COLLATION_ID Идентификатор режима сортировки из RDB$COLLATIONS

Примеры запросов:
Назначение запроса Текст запроса
Получение списка всех полей по нужной таблице (просмотру)


SELECT rdb$field_name
FROM rdb$relation_fields
WHERE UpCase (rdb$relation_name) = 'NEW_TABLE'


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

rdb$relation

Как уже было сказано, эту таблицу можно считать основной.
Поле Значение
RDB$VIEW_BLR Содержит откомпилированный текст просмотра (VIEW)
RDB$VIEW_SOURCE Содержит исходный текст просмотра, DDL.
RDB$DESCRIPTION Описание таблицы (заполняется DBA при создании)
RDB$RELATION_ID Идентификатор записи, индексированное поле
RDB$FORMAT Счётчик изменений метаданных
RDB$SYSTEM_FLAG Для системных таблиц равен 1 (вообще – не равен 0, но на практике – равен 1).
RDB$FIELD_ID Идентификатор записи для таблицы rdb$fields
RDB$RELATION_NAME Имя отношения (relation) – т.е. имя таблицы или просмотра
RDB$OWNER_NAME Создатель/владелец записи

Очень интересное поле – это RDB$FORMAT, который представляет собой счётчик на количество изменений метаданных. (по некоторым источникам, перестройка индексов также увеличивает этот счётчик – но врать не буду – не проверял). По нему можно узнать, сколько раз менялась структура любой таблицы после последнего BACKUP/RESTORE.
Примеры запросов:
Назначение запроса Текст запроса
Получение списка всех таблиц и просмотров (включая системные)

SELECT rdb$relation_name
FROM rdb$relations

Получение списка только таблиц (включая системные)

SELECT rdb$relation_name
FROM rdb$relations
WHERE rdb$view_blr is null

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

SELECT rdb$relation_name
FROM rdb$relations
WHERE rdb$view_blr IS NULL
AND rdb$system_flag <> 1


rdb$function_arguments

Эта таблица содержит список аргументов для каждой определённой пользователем функции (UDF):
Поле Значение
RDB$FUNCTION_NAME Уникальное имя функции
RDB$ARGUMENT_POSITION Порядковый номер данного аргумента при вызове функции
RDB$MECHANISM Механизм передачи параметров, 0 - по значению или 1 - по ссылке (в терминах Delphi, для возвращаемых, var - параметров, значение будет равно 1). По сути, данный параметр определяет, является ли данный аргумент входящим (INPUT) или возвращаемым (OUTPUT) параметром.
RDB$FIELD_TYPE Тип данных:

* 261 - BLOB
* 14 - CHAR
* 40 - CSTRING
* 11 - DFLOAT
* 27 - DOUBLE
* 10 - FLOAT
* 16 - INT64
* 8 - INTEGER
* 9 - QUAD
* 7 - SMALLINT
* 12 - DATE (Диалект 3)
* 13 - TIME
* 35 - TIMESTAMP
* 37 - VARCHAR

RDB$FIELD_SCALE Множитель для целочисленных параметров. При передаче в функцию каждый параметр умножается на 10 в степени, равной данному значению. Есстественно, что по умолчанию это значение всегда равно 0 :).
RDB$FIELD_LENGTH Размер аргумента в байтах. Не имеет смысла для CHAR, VARCHAR и CSTRING. Для BLOB всегда равен 8 (надо понимать, что блобы всегда передаются по ссылке, и здесь 8 - это размер указателя, pointer). Для остальных типов соответствует размеру, занимаемому ими в памяти, например для SMALLINT = 2, INTEGER = 4, INT64 = 8.
RDB$FIELD_SUB_TYPE Используется для SMALLINT, INTEGER и INT64. Если равно 0 или NULL, то подтип равен типу, т.е. RDB$FIELD_TYPE. При значении 1 целые передаются, как NUMERIC, при 2 - как DECIMAL.
RDB$CHARACTER_SET_ID Идентификатор набора символов
RDB$FIELD_PRECISION Определяет точность для типов DECIMAL и NUMERIC.

rdb$functions

Эта таблица содержит список всех функций, определённых пользователеми (UDF):
Поле Значение
RDB$FUNCTION_NAME Уникальное имя функции
RDB$DESCRIPTION Пользовательское функции
RDB$MODULE_NAME Имя библиотеки (DLL), в которой находится функция
RDB$ENTRYPOINT Имя функции в DLL, "точка входа", которое может быть отличным от RDB$FUCTION_NAME
RDB$RETURN_ARGUMENT Если функция возвращает результат, то это поле содержит номер возвращаемого аргумента, значение которого будет трактоваться, как результат функции
RDB$SYSTEM_FLAG Равен единице для системных функций

rdb$generators

Список всех генераторов в системе:
Поле Значение
RDB$GENERATOR_NAME Уникальное имя генератора
RDB$GENERATOR_ID Уникальный идентификатор генератора, назначаемый системой
RDB$SYSTEM_FLAG Равен единице для системных генераторов

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

rdb$procedure_parameters

По аналогии с rdb$function_srguments - таблица, в которой "живут" все параметры для хранимых процедур:
Поле Значение
RDB$PARAMETER_NAME Имя параметра
RDB$PROCEDURE_NAME Имя процедуры, в которой он объявлен
RDB$PARAMETER_NUMBER Порядковый номер параметра
RDB$PARAMETER_TYPE Тип параметра: 0 - входящий, 1 - возвращаемый
RDB$FIELD_SOURCE Тип данных, ссылка на RDB$FIELDS.RDB$FIELD_NAME, по сути - имя домена (в т.ч. - сгенерированного автоматически)
RDB$DESCRIPTION Описание пользователя
RDB$SYSTEM_FLAG Равен единице для системных генераторов

rdb$procedures

Таблица, в которой "живут" все хранимые процедуры.
Поле Значение
RDB$PROCEDURE_NAME Имя процедуры
RDB$PROCEDURE_ID Идентификатор процедуры
RDB$PROCEDURE_INPUTS Количество входящих параметров
PROCEDURE_OUTPUTS Количество возвращаемых параметров
RDB$DESCRIPTION Описание пользователя
RDB$PROCEDURE_SOURCE Исходный текст процедуры (SQL)
RDB$SECURITY_CLASS Дескриптор карты прав
RDB$OWNER_NAME Имя владельца
RDB$SYSTEM_FLAG Равен единице для системных генераторов