Как получить информацию о структуре БД для документации

Создаём документацию БД и словарь данных информационной системы своими руками. Полезные SQL-скрипты и приемы документирования базы данных

Введение

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

Это поможет любому участнику команды, а в первую очередь системному аналитику, решить такие задачи:

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

Какие метаданные полезно знать о своей БД?

При работе над любым ИТ-проектом полезно иметь возможность посмотреть на данные своих систем с высоты птичьего полёта.

Вот какая информация пригодится вам в первую очередь.

Физическая структура данных: объекты БД (в первую очередь — таблицы и поля, их наименования, типы данных, допустимые диапазоны значений, ключи, а также — пользователи, индексы, представления и т. п.)
Объём данных, частота их обновления, дата последнего обновления БД
Модель данных (логическая, концептуальная) — ключевые атрибуты и сущности, связь между ними
Смысл и значение данных, объяснение основных сущностей на языке бизнеса
Распределение данных (количество пустых полей, кардинальность (количество уникальных и повторяющихся значений)
Ответственный за данные — кто может пояснить смысл данных или помочь исправить найденные проблемы в них

Такую информацию называют метаданными, и её классический пример — это словарь данных.

Что такое словарь данных и зачем он нужен

Мы будем подразумевать под «словарем данных» (англ. — data dictionary) справочник или централизованное описание метаданных, дающее представление о структуре и содержании данных. У этого термина есть и другое значение, в рамках данной статьи не используемое: словарём данных называют технику моделирования, дополняющую требования при проектировании информационных систем, в культуре Systems Analysis and Design.

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

Почему так редко задумываются о документировании БД?

Это самая неинтересная для программистов часть

Ведение проектной и эксплуатационной документации требует дисциплины и времени, которого обычно и так мало.

На старте проекта БД создаётся по интуиции и видению разработчиков

В маленьких проектах, на старте, при реализации прототипов и MVP команда разработки редко задумывается о БД в терминах, отличных от физической реализации. Зачастую разработчик просто сразу создаёт таблицы, а дальше как пойдёт. В хорошей ситуации есть описание предметной области, возможно даже ER-диаграмма от аналитика с описанием основных сущностей (не всех!).

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

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

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

Задачи по изменению структуры данных неявно вытекают из других задач на доработку системы, и именно требования к данным не всегда хорошо описаны. Обновления в структуру БД и состав полей вносятся неявно в рамках решения более масштабной задачи. Особенно при обновлении, доработках уже существующей много лет системы. Вносимые в БД изменения обусловлены разнородными и многочисленными доработками в разных модулях системы, которыми занимаются разные разработчики. Ведь суть задачи, над которой работает разработчик, обычно не в изменении данных, в реализации какой-то новой фичи. В итоге исходные требования на изменение структуры данных разбросаны по множеству user story, change request-ов, и просто в рабочей переписке. В лучшем случае это упомянуто в нескольких разделах ТЗ / Use Cases, предоставленных в разные периоды времени.

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

Какие средства документирования можно применять для описания БД?

Инструмент, тип документации	Какую информацию можно найти	Примеры и комментарии	Что почитать
Специальные платформы управления данными Data Management.	•‎ Каталог данных (Data Catalog) — единый источник сведений о всех информационных активах организации. •‎ Список полей и атрибутов •‎ Метаданные — например, информацию о размере таблиц, дате последнего обновления •‎ Информацию об ответственных лицах •‎ Бизнес-глоссарий	Дороги в применении, используются в крупных только в больших компаниях, актуальны для проектов создания DWH (централизованное персистентное хранение данных всего предприятия для аналитики).	Что такое каталог данных? Видео от DIS Group Комплексная платформа управления данными от компании Юнидата
Сгенерированные отчёты с использованием специальных внешних сервисов и утилит или инструментов автосборки документации на основе комментариев в коде БД.	•‎ Дают понимание физической модели данных, схемы данных БД — таблиц, полей и атрибутов, и т.д. •‎ Включают информацию об объектах, помимо таблиц (индексах, представлениях, и т.п.).	Использование дополнительных утилит, не всегда приемлемо с точки зрения безопасности. Автосборка требует высокой дисциплины разработчиков и QC, и подходит, если команда придерживается практики DocsAsCode для важной документации (что встречается в зрелых продуктовых компаниях).	Анализ средств документирования баз данных Обсуждение способов сборки документации по базе данных
Полная модель данных на концептуальном, логическом, физическом уровне (как минимум, одном из них). Включая: •‎ Состав таблиц •‎ Типы данных •‎ Связи между сущностями и атрибутами	Наглядно показывают связи между таблицами.. Обычно встроены в современные средства работы с БД и позволяют сгенерировать DDL для БД.	Дороги в применении, используются в крупных компаниях. И даже при этом, фактически, применяются только для части системы.	CASE-средства проектирования баз данных
Специальные диаграммы: •‎ ER диаграммы, •‎ Диаграммы классов UML •‎ Другие виды диаграмм	Сущности, атрибуты и связи между ними. Логическая связь между данными.	Нужно понимать контекст создания диаграмм, так как они редко являются исчерпывающими. Если диаграммы разрабатывались для постановки задачи в средствах визуализации, не интегрированных с СУБД, то они могут не отражать физическую схему данных и не конвертируются напрямую в SQL код.	Методика построения ER-диаграммы для базы данных Просмотр модели данных готовой БД
Разные разделы документов требований к разрабатываемой системе.	Допустимые значения, смысл данных	Разбросаны по разным UC, главам документа Технического задания, приложениям и т.д.	Алгоритм описания функциональных требований в формате Use Case-ов
Наборы данных, применяемые для тестирования.	Допустимые значения, форматы данных.	Некомфортны для чтения.	Создание тестовых данных

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

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

Способ	Описание	Пример
Использовать специализированные инструменты, обеспечивающие возможность просмотра объектов БД.	Способ позволит визуально быстро понять, какие объекты есть в вашей базе данных. Можное осмотреть список таблиц и о каждой из них узнать что-то из контекстного меню или другой части внутри GUI используемого средства. Однако, получать, хранить и передавать информацию в такой форме неудобно.	Документация к редактору объектов DBeaver
Вывести информацию об объектах БД с помощью соответствующих SQL запросов к служебным таблицам.	В РСУБД существуют служебные таблицы, которые содержат интересующие нас сведения об объектах базы данных. Вы можете строить несложные SQL запросы к служебным таблицам и системным каталогам БД (помимо таблиц со схемой, среди служебных таблиц обычно есть много интересного).	Использование схемы данных в SQL Server Примеры запросов для получения схемы данных SQL Server Пример запроса к специальной таблице в SQLite Пример запроса к системному каталогу PostgreSQL
Применять встроенные или внешние инструменты моделирования БД.	Позволяют наглядно увидеть связи между таблицами, это комфортно и информативно. Современные средства работы с БД обычно имеют такие инструменты.	ER диаграммы, встроенные в инструмент работы с РСУБД DBeaber Генерация таблиц БД из ER диаграммы

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

Как получить информацию о БД

Как быстро получить словарь данных для популярных БД?

Несколько слов по данному вопросу от Александра Кротова.

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

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

Что делать в такой ситуации? Первое, что пришло бы мне в голову, это провести для всех баз данных так называемый «реверс-инжиниринг» (reverse engineering). Можно было бы изучить имеющиеся данные и их структуру, взять могучее универсальное средство моделирования, построить ER-модели, сформировать текстовое описание и приступить к более глубокому анализу полученных материалов. Но здесь мы можем натолкнуться на ряд проблем: универсальные средства моделирования, способные работать с разными СУБД, стоят, как правило, дорого, и далеко не каждый работодатель будет готов потратить деньги на приобретение подобного программного продукта. А вероятность того, что в процессе инвентаризации информационных ресурсов придётся столкнуться с так называемым «зоопарком», то есть разбродом и шатанием в платформах, СУБД и их версиях, достаточно высока. Иначе и задача наведения порядка в информационном хозяйстве вообще вряд ли бы возникла.

И вот, в ситуации, когда «зоопарк» есть, а актуальной документации и подходящего инструмента для её создания нет, можно пойти по другому пути и попробовать сформировать необходимую для анализа документацию на основе так называемых «словарей данных» — служебных таблиц, в которых реляционная (и не только) СУБД хранит описание своих объектов — таблиц, полей, представлений, ключей, индексов и многого другого. В том или ином виде такие словари должны быть в любой СУБД, хотя структура их может сильно различаться. В общем, я решил посмотреть, как подобные словари устроены, взяв для примера несколько популярных СУБД: MySQL, PostgreSQL, Oracle, SQLite, MS Access. А задачу я себе поставил простую: для каждой СУБД написать SQL-запрос, который сформирует описание таблиц и полей выбранных баз данных. Только хотел бы предупредить, что для разных версий одной и той же СУБД запросы могут немного отличаться, но, думаю, это и так очевидно.

Словарь данных для MySQL

Начал я с MySQL. Здесь всё оказалось достаточно просто. Можно использовать стандартную схему под названием INFORMATION_SCHEMA (говорят, даже ANSI на неё свой стандарт оформила для разных СУБД). Схема содержит таблицы, в которых можно легко найти все необходимые метаданные. В нашем случае достаточно соединить таблицы TABLES (таблицы) и COLUMNS (поля) по именам схемы и таблицы, выбрать нужные атрибуты, присвоить понятные псевдонимы, задать условия выборки (например, по имени схемы и типу объектов, как на примере ниже). Получится примерно такой запрос:

SELECT
  c.table_name as "Таблица",
  t.table_comment as "Комментарий к таблице",
  c.ordinal_position as "№ п.п",
  c.column_name as "Поле",
  c.column_comment as "Комментарий к полю",
  c.column_type as "Тип",
  CASE WHEN c.column_key = 'PRI' THEN 'PK' END as "Ключ",
  c.is_nullable as "NULL"

FROM
  information_schema.columns c
  JOIN information_schema.tables t ON c.table_name = t.table_name
  AND c.table_schema = t.table_schema

WHERE
  t.table_type = 'BASE TABLE'
  AND t.table_schema = '<имя схемы, если надо>'

ORDER BY
  c.table_name,
  c.ordinal_position;

Получим результат вот такого вида

(показано на фрагменте отчёта для учебной БД «Студенты»):

Таблица	Комментарий к таблице	№ п.п	Поле	Комментарий к полю	Тип	Ключ	NULL
EMPLOYEE	Сотрудники	1	ID	Идентификатор	int(11)	PK	NO
EMPLOYEE	Сотрудники	2	LAST_NAME	Фамилия	varchar(45)		NO
EMPLOYEE	Сотрудники	3	FIRST_NAME	Имя	varchar(45)		NO
EMPLOYEE	Сотрудники	4	MIDDLE_NAME	Отчество	varchar(45)		YES
EMPLOYEE	Сотрудники	5	BIRTHDAY	День рождения	date		YES
GRADE	Оценки	1	ID	Идентификатор	int(11)	PK	NO
GRADE	Оценки	2	STUDENT_ID	ИД студента в группе	int(11)		YES
GRADE	Оценки	3	MODULE_NUM	Номер модуля	int(11)		YES
GRADE	Оценки	4	GRADE	Оценка	int(11)		YES
GROUP_ST	Учебные группы	1	ID	Идентификатор	int(11)	PK	NO
GROUP_ST	Учебные группы	2	GROUP_CODE	Код группы	varchar(45)		YES
GROUP_ST	Учебные группы	3	TEACHER_ID	ИД сотрудника-преподавателя	int(11)		YES
GROUP_ST	Учебные группы	4	CURATOR_ID	ИД сотрудника-куратора	int(11)		YES
STUDENT	Студенты	1	ID	Идентификатор	int(11)	PK	NO
STUDENT	Студенты	2	LAST_NAME	Фамилия	varchar(45)		NO
STUDENT	Студенты	3	FIRST_NAME	Имя	varchar(45)		NO
STUDENT	Студенты	4	MIDDLE_NAME	Отчество	varchar(45)		YES
STUDENT	Студенты	5	BIRTHDAY	День рождения	date		YES

Единственное, надо не забывать, что запрос вернёт только те объекты БД, на просмотр которых у текущего пользователя есть привилегии. И ещё я бы обратил внимание на атрибут ORDINAL_POSITION, который был использован в составе оператора ORDER BY после TABLE_NAME (имя таблицы). Атрибут предназначен для хранения порядка полей, заданного в DDL-скрипте при создании таблицы (оператор CREATE TABLE) или установленного разработчиком позже (кстати, возможность изменять порядок полей после создания таблицы реализована далеко не во всех СУБД, MySQL здесь обогнал многих). Но зачем хранить исходный порядок полей, если теория реляционных баз данных утверждает, что порядок полей в таблице не несёт никакой смысловой нагрузки? Полагаю, эта возможность реализована исключительно для удобства пользователя. Разработчик, создавая таблицу, задаёт порядок полей не просто так, а на основе определённой бизнес-логики, и вправе ожидать, что выполнив запрос типа SELECT * FROM … без явно указанного порядка полей, или создав отчёт на основе словаря данных, как мы это сделали выше, он получит поля в порядке, соответствующем заложенной логике, а не как попало. Аналогичные по сути атрибуты с порядковым номером поля в таблице, есть и в других СУБД, что мы увидим ниже.

Словарь данных для PostgreSQL

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

Используемые служебные таблицы:

pg_namespace (пространства имён).
pg_class (таблицы и родственные объекты).
pg_attribute (поля таблиц).
pg_type (типы данных).
pg_description (комментарии к объектам).

А запрос вышел следующий:

SELECT
  c.relname as "Таблица",
  (
    SELECT
      td.description
    FROM
      pg_catalog.pg_description td
    WHERE
      td.objoid = a.attrelid
      AND td.objsubid = 0
  ) as "Комментарий к таблице",
  a.attnum as "№ п/п",
  a.attname as "Поле",
  ad.description as "Комментарий к полю",
  pt.typname as "Тип",
  CASE WHEN a.atttypmod = -1 THEN NULL ELSE a.atttypmod END "Размер",
  a.attnotnull as "NULL",
  CASE WHEN a.attnum IN(
    SELECT
      UNNEST(cn.conkey)
    FROM
      pg_catalog.pg_constraint cn
    WHERE
      cn.conrelid = a.attrelid
      AND cn.contype LIKE 'p'
  ) THEN 'PK' END as "Ключ"
FROM
  pg_catalog.pg_attribute a
  JOIN pg_catalog.pg_type pt ON a.atttypid = pt.oid
  JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
  JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
  LEFT JOIN pg_catalog.pg_description ad ON ad.objoid = a.attrelid
  AND ad.objsubid = a.attnum
WHERE
  a.attnum > 0
  AND n.nspname = 'public'
  and c.reltype <> 0
ORDER BY
  c.relname,
  a.attnum;

Не уверен, что запрос оптимален, но поставленную задачу выполняет — формирует нужный отчёт по интересующим нас объектам базы данных (не буду уже одинаковые таблички с результатами приводить). Прокомментирую некоторые моменты.

Вложенный SELECT для извлечения комментария к таблице. Проблема в том, что комментарии ко всем объектам (создаются DDL-директивой COMMENT) в PostgreSQL, в отличие от рассмотренного примера для MySQL, хранятся в одной общей таблице PG_DESCRIPTION. И если для полей комментарии можно легко получить через оператор JOIN, соединив таблицы pg_attribute и pg_description через уникальный идентификатор поля, то комментарий для таблицы простым JOIN-ом получить не выходит, так как он в данном случае общий для всех полей каждой таблицы. Но вложенный SELECT с уточнением, что нам нужен комментарий к самой таблице (условие objsubid = 0) позволяет это сделать.

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

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

Словарь данных для БД Oracle

Немного помучившись с PostgreSQL, к Oracle я подошёл с некоторой опаской, ожидая ещё более сложной структуры словаря данных. Но разработчики Oracle, к счастью, позаботились о нас заранее: создали в схеме SYS целый набор представлений, который позволяет получить всю необходимую информацию по объектам базы данных. Целевой отчёт, например, можно получить, соединив представления ALL_TAB_COLS (поля), ALL_TAB_COMMENTS (комментарии к таблицам) и ALL_COL_COMMENTS (комментарии к полям). Запрос может выглядеть так:

SELECT
  cl.owner as "Владелец",
  cl.table_name as "Таблица",
  tc.comments as "Комментарий к таблице",
  cl.internal_column_id as "№ п/п",
  cl.column_name as "Поле",
  cl.data_type as "Тип",
  cl.data_length as "Размер",
  cl.data_precision as "Точность",
  cl.nullable as "NULL",
  cm.comments as "Комментарий к полю"

FROM
  sys.all_tab_cols cl
  JOIN sys.all_col_comments cm ON cl.owner = cm.owner
  AND cl.table_name = cm.table_name
  AND cl.column_name = cm.column_name
  JOIN sys.all_tab_comments tc ON cl.owner = tc.owner
  AND cl.table_name = tc.table_name

WHERE
  cl.owner = <имя схемы, если надо>

ORDER BY
  cl.table_name,
  cl.internal_column_id;

Всё довольно просто, но на один момент я бы всё-таки хотел обратить внимание. Если нам требуется получить отчёт для одной конкретной схемы, то можно зайти в базу данных под логином владельца этой схемы и вместо представлений с префиксом «all» использовать аналогичные представления с префиксом «user». Тогда все упоминания схемы из операторов SELECT, FROM и WHERE можно будет удалить, а сам запрос будет выглядеть более лаконично:

SELECT
  cl.table_name as "Таблица",
  tc.comments as "Комментарий к таблице",
  cl.internal_column_id as "№ п/п",
  cl.column_name as "Поле",
  cl.data_type as "Тип",
  cl.data_length as "Размер",
  cl.data_precision as "Точность",
  cl.nullable as "NULL",
  cm.comments as "Комментарий к полю"

FROM
  sys.user_tab_cols cl
  JOIN sys.user_col_comments cm ON cl.table_name = cm.table_name
  AND cl.column_name = cm.column_name
  JOIN sys.user_tab_comments tc ON cl.table_name = tc.table_name

ORDER BY
  cl.table_name,
  cl.internal_column_id

Словарь данных для MS Access

Хотел дополнить картину другими оказавшимися под рукой СУБД, но потерпел частичную неудачу. В MS Access, например, можно легко получить список пользовательских таблиц. Вот так:

SELECT
  *

FROM
  MSysObjects o

WHERE
  o.Flags = 0
  AND o.Type = 1

ORDER BY
  o.NAME;

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

Словарь данных для SQLite

Похоже получилось и с SQLite. Можно получить список таблиц:

SELECT
  *

FROM
  sqlite_master m

WHERE
  m.type = 'table';

Для каждой таблицы можно получить DDL-скрипт CREATE TABLE (поле sqlite_master.sql). Но имена и типы полей для всех таблиц, похоже, можно извлечь только парсингом этого скрипта. Или, как и в предыдущем случае, я не нашёл более удачного решения.

Резюме

Дальше исследование можно было бы продолжать, но у Александра закончились доступные для опытов СУБД. Да и вывод уже вполне очевиден: если нужна документация на базы данных, но нет подходящих инструментов, как правило, можно извлечь нужные сведения непосредственно из самой базы данных, причём не обязательно ограничиваясь основными типами объектов, как сделали мы. Дальше можно подключить другие методы сбора данных, такие как интервьюирование специалистов, анкетирование пользователей, непосредственное участие в процессах или наблюдение за ними — и можно приступать к полноценному анализу полученных материалов.

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

Получить информацию о словаре данных и схеме данных для БД, разработанной на популярных РСУБД: MySQL, PostgreSQL, Oracle, SQLite и MSAccess
Понять актуальность имеющейся документации на БД
Провести инвентаризацию информационных ресурсов
Создать простейший прототип витрины для мониторинга состояния БД на проекте

Автор: systems.education

← Предыдущая статья

Рекомендации при работе с PostgreSQL

Следующая статья →

Как устроен PostgreSQL