Статья
Версия для печати
Обсудить на форуме
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_NAMERDB$DIMENSIONRDB$LOWER_BOUNDRDB$UPPER_BOUND
NEW_DOMAIN003
NEW_DOMAIN105

Вообще говоря, использование массивов в качестве типов данных противоречит самой концепции реляционных баз, т.к. любая таблица, по сути, уже является массивом. Отображение этих типов, как правило, не поддерживает ни одна штатная (да и не только) утилита. Но тем не менее, функционал для работы с массивами есть, и в некоторых очень специфических случаях (например, хранение результатов каких-то измерений, хранение векторных фигур в виде массива их относительных координат) это тип данных может пригодится. Но следует учитывать, что в 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Равен единице для системных генераторов
Версия для печати
Обсудить на форуме