Автоматизированное документирование баз данных на Markdown

Введение

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

Для решения данной задачи разработаны два PHP-скрипта, предназначенные для работы с PostgreSQL. Эти скрипты выполняют две основные функции:

1.  Сравнение старой и новой структуры базы данных с выявлением добавленных, удалённых и изменённых таблиц.

2.  Создание Markdown-документации, которая содержит подробное описание назначения таблиц и характеристик их полей, что делает изменения в структуре базы данных прозрачными для разработчиков.

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

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

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

Описание первого скрипта: анализ структуры базы данных

Функциональность

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

Этапы работы скрипта

1. Подключение к базе данных

На первом этапе скрипт устанавливает соединение с базой данных PostgreSQL, используя библиотеку PDO (PHP Data Objects). PDO обеспечивает унифицированный интерфейс для работы с базами данных и позволяет задавать параметры подключения, такие как хост, порт, имя базы данных, логин и пароль пользователя:

$dsn = "pgsql:host=localhost;port=5432;dbname=name;"; $user = "user"; $password = "password"; $pdo = new PDO($dsn, $user, $password, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

Если подключение не удаётся, скрипт выводит соответствующее сообщение об ошибке.

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

2. Запрос информации о структуре базы данных

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

• Название таблиц.

• Название столбцов.

• Типы данных (например, text, integer, boolean).

• Возможность содержать NULL.

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

• Максимальную длину для строковых типов данных или точность для числовых типов.

SELECT     table_name,     column_name,     data_type,     is_nullable,     character_maximum_length,     numeric_precision,     numeric_scale,     column_default FROM     information_schema.columns WHERE     table_schema = 'public' ORDER BY     table_name, ordinal_position;

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

3. Обработка полученных данных

После выполнения SQL-запроса скрипт обрабатывает результаты:

• Преобразование значений. Системные значения преобразуются в более удобочитаемый формат. Например, поле is_nullable со значением YES заменяется на «Да», а NO – на «Нет».

• Добавление описаний. Скрипт поддерживает добавление пользовательских описаний для полей. Например, для столбца id можно задать описание «Идентификатор», а для name – «Наименование». Эти описания могут быть заданы в виде массива или загружены из внешнего файла.

$row['is_nullable'] = ($row['is_nullable'] === 'YES') ? "Да" : "Нет"; $maxLen = !empty($row['character_maximum_length'])     ? $row['character_maximum_length']     : $row['numeric_precision']; $row['max_len'] = $maxLen ?? '-';

4. Группировка данных по таблицам

Извлечённые данные группируются по таблицам, чтобы каждая таблица представлялась отдельным разделом в итоговом Markdown-документе. Это позволяет структурировать вывод и делает его более понятным.

$grouped = []; foreach ($results as $row) {     $grouped[$row['table_name']][] = $row; }

5. Формирование Markdown-документа

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

Пример таблицы в Markdown:

| Название поля | Тип поля | По умолчанию | Может NULL | Макс. длина | Описание | |---------------|----------|--------------|------------|-------------|----------| | id            | integer  | NULL         | Нет        | -           |  Идентификатор | | name          | text     | NULL         | Да         | 255         |  Наименование |

Пример генерации таблицы в PHP:

$mdOutput[] = "| Название поля | Тип поля | По умолчанию | Может NULL | Макс. длина | Описание |\n"; $mdOutput[] = "|---------------|----------|--------------|------------|-------------|----------|\n"; foreach ($rows as $row) {     $mdOutput[] = "| " . ($row['column_name'] ?: '-') . " | " .         ($row['data_type'] ?: '-') . " | " .         ($row['column_default'] ?: '-') . " | " .         ($row['is_nullable'] ?: '-') . " | " .         ($row['max_len'] ?: '-') . " | " .         ($description ?: '-') . " |\n"; }

6. Сохранение результата в файл

После генерации содержимого Markdown-документа скрипт сохраняет его в файл с именем database_structure_new.md. Этот файл является готовой технической документацией и может быть использован для анализа базы данных или её сопровождения.

file_put_contents('database_structure_new.md', implode("", $mdOutput));

Итог

// Настройка подключения к базе данных PostgreSQL $dsn = "pgsql:host=localhost;port=5432;dbname=name;"; // Укажите имя вашей базы данных $user = "user"; // Укажите ваше имя пользователя $password = "password"; // Укажите ваш пароль  try {     // Создаем объект подключения к базе данных     $pdo = new PDO($dsn, $user, $password, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);      // SQL-запрос для извлечения структуры всех таблиц     $query = "     SELECT         table_name,         column_name,         data_type,         is_nullable,         character_maximum_length,         numeric_precision,         numeric_scale,         column_default     FROM         information_schema.columns     WHERE          table_schema = 'public'     ORDER BY          table_name, ordinal_position";      // Выполнение запроса и получение результатов     $stmt = $pdo->query($query);     $results = $stmt->fetchAll(PDO::FETCH_ASSOC);      // Пост-обработка данных и замена значений для удобства     $mdOutput = []; // Массив для формирования вывода в Markdown      $descriptions = [         'id' => 'Идентификатор',         'name' => 'Наименование',         'ordernum' => 'Порядковый номер',         // Добавьте описания для других ключевых полей     ];      // Преобразование значений в более читаемый формат     foreach ($results as &$row) {         $row['is_nullable'] = ($row['is_nullable'] === 'YES') ? "Да" : "Нет";         $maxLen = !empty($row['character_maximum_length']) ? $row['character_maximum_length'] : $row['numeric_precision'];         $row['max_len'] = $maxLen ?? '-';     }      // Группировка результатов по именам таблиц     $grouped = [];     foreach ($results as $row) {         $grouped[$row['table_name']][] = $row;     }      // Формирование выводных данных в формате Markdown     foreach ($grouped as $tableName => $rows) {         $mdOutput[] = "## Таблица " . $tableName . "\n"; // Название таблицы         $mdOutput[] = "**Описание**\n\n"; // Заголовок "Описание"         $mdOutput[] = "| Название поля | Тип поля | По умолчанию | Может NULL | Макс. длина | Описание |\n"; // Заголовок таблицы         $mdOutput[] = "|---------------|----------|--------------|------------|-------------|----------|\n"; // Разделитель          $idRows = [];         $otherRows = [];          // Разделяем строки: сначала идем по строкам с id         foreach ($rows as $row) {             if (($row['column_name'] === 'id') || ($row['column_name'] === 'Id')) {                 array_unshift($idRows, $row);             } elseif ((str_ends_with($row['column_name'], 'id'))                 || (str_ends_with($row['column_name'], 'Id'))                 || ($row['data_type'] === 'uuid')) {                 $idRows[] = $row;             } else {                 $otherRows[] = $row;             }         }          // Объединяем строки: сначала с id, затем остальные         $sortedRows = array_merge($idRows, $otherRows);          foreach ($sortedRows as $row) {             $description = (str_ends_with($row['column_name'], 'id'))                 ? 'Идентификатор таблицы ' . substr($row['column_name'], 0, -2)                 : ($descriptions[$row['column_name']] ?? '');              // Формирование строки с данными для таблицы             $mdOutput[] = "| " . ($row['column_name'] ?: '-')                 . " | " . ($row['data_type'] ?: '-')                 . " | " . ($row['column_default'] ?: '-')                 . " | " . ($row['is_nullable'] ?: '-')                 . " | " . ($row['max_len'] ?: '-')                 . " | " . $description . " |\n";         }         $mdOutput[] = "\n"; // Переход на новую строку     }      // Сохранение результата в файл Markdown     $mdFileName = 'database_structure_new.md';     file_put_contents($mdFileName, implode("", $mdOutput));      echo "Данные успешно сохранены в файл " . $mdFileName . "\n";  } catch (PDOException $e) {     // Обработка ошибок подключения или выполнения запроса     echo "Ошибка подключения или выполнения: " . $e->getMessage(); }

Описание второго скрипта: анализ изменений в структуре базы данных

Этот скрипт предназначен для анализа изменений в структуре базы данных, представленной в формате Markdown, с выделением добавленных, удалённых и изменённых таблиц. Он выполняет несколько этапов:

1. Анализ структуры таблиц из новых и старых файлов

2. Определение изменений: добавленных, удалённых и обновлённых строк

3. Генерация файлов с изменениями

Этапы работы скрипта:

1. Функции для работы с таблицами

addRowsInTable — функция принимает массив данных о поле таблицы и возвращает строку в формате Markdown.
addNameAndHeaderTable — функция добавляет заголовок таблицы и её описание.
sortedRows — сортирует массив строк таблицы так, чтобы поля id или заканчивающиеся на id шли в начале.

2. Парсинг данных из Markdown

markDownToArray — функция преобразует содержимое Markdown-файла в ассоциативный массив.

3. Основной блок скрипта

1. Чтение новых и старых файлов

   • Считывание нового файла:

   $newMdContent = file_get_contents(‘database_structure_new.md’);

   list($newTables,$newDescriptions) = markDownToArray($newMdContent);

   • Считывание старых файлов и их обработка.

2. Сравнение данных

   • Вычисление удалённых, добавленных и изменённых таблиц.

   • Пример: сравнение массивов $oldTables и $newTables.

3. Сохранение результатов

   • Генерация Markdown-файлов для каждой категории изменений (новые, удалённые, изменённые таблицы).

Итог

/**  * Добавляем строки в таблицу  *  * @param array $row: строка  * @return string: строки таблицы  */ function addRowsInTable(array $row): string {     return "| " . ($row['Название поля'] ?? '-') . " | "         . ($row['Тип поля'] ?? '-') . " | "         . ($row['По умолчанию'] ?? '-') . " | "         . ($row['Может NULL'] ?? '-') . " | "         . ($row['Макс. длина'] ?? '-') . " | "         . ($row['Описание'] ?? '-') . " |\n"; }  /**  * Добавляем название и описание таблицы  *  * @param $tableName: название таблицы  * @param string $descriptions: описание  * @return string: первые 5 строк таблицы  */ function addNameAndHeaderTable(string $tableName, string $descriptions = "**Описание**"): string {     return "## Таблица " . $tableName . "\n" .          $descriptions . "\n\n" .          "| Название поля | Тип поля | По умолчанию | Может NULL | Макс. длина | Описание |\n" .          "|---------------|----------|--------------|------------|-------------|----------|\n"; }  /**  * Сортировка строк таблицы по id  *  * @param $rows: строка  * @return array: отсортированная таблица  */ function sortedRows(array $rows): array {     $idRows = [];     $otherRows = [];      // Разделение строк по наличию id на конце     foreach ($rows as $row) {         if (($row['Название поля'] === 'id') || ($row['Название поля'] === 'Id')) {             array_unshift($idRows, $row);         } elseif ((str_ends_with($row['Название поля'], 'id'))             || (str_ends_with($row['Название поля'], 'Id'))             || ($row['Тип поля'] === 'uuid')) {             $idRows[] = $row;         } else {             $otherRows[] = $row;         }     }      // Объединяем строки, начиная с id строк     return array_merge($idRows, $otherRows); }  /**  * Преобразование MarkDown в ассоциативный массив  *  * @param string $mdContent: таблицы файла  * @return array[]: ассоциативные массивы с таблицами и их данными, описанием  */ function markDownToArray(string $mdContent): array {     $tables = [];     $descriptions = [];      // Используем регулярные выражения для поиска всех таблиц     $pattern = '/(## Таблица .+?)(?=\n## Таблица |\z)/s';     preg_match_all($pattern, $mdContent, $matches);      // Обработка каждой найденной таблицы     foreach ($matches[0] as $table) {         $lines = array_map('trim', explode("\n", trim($table)));          // Если таблица короче 4 строк - пропускаем         if (count($lines) < 4) {             continue;         }          // Заголовок таблицы и описание         $tableName = str_replace(["## Таблица ", " +"], '', $lines[0]);         $description = trim($lines[1]); // Описание таблицы          // Извлечение заголовков         $headers = array_filter(array_map('trim', explode('|', $lines[3])));          // Проверка корректности заголовков         if (empty($headers)) {             echo "Ошибка: не удалось извлечь заголовки для таблицы " . $tableName . "\n";             continue;         }          // Извлечение строк данных         $rows = [];         for ($i = 5; $i < count($lines); $i++) {             if (!empty(trim($lines[$i]))) {                 $row = array_map('trim', explode('|', $lines[$i])); // удаляем пробелы                 if (count($row) > 1) {                     // Создаем ассоциативный массив, используя заголовки как ключи                     $rows[] = array_combine($headers, array_slice($row, 1, -1));                 }             }         }          // Добавление таблиц и их описаний         $tables[$tableName] = $rows;         $descriptions[$tableName] = $description;     }      return [$tables, $descriptions]; }  // Извлекаем данные из последнего обновления БД $newMdContent = file_get_contents('database_structure_new.md'); list($newTables, $newDescriptions) = markDownToArray($newMdContent);  // Путь к папке со старыми файлами $oldFilesDirectory = './files';  $allOldTables = []; $allOldData = []; $oldDescriptions = [];  // Обработка каждого старого файла отдельно try {     $files = @scandir($oldFilesDirectory);      if ($files === false) {         throw new Exception("Произошла ошибка: не удалось найти директорию.");     }      // Удаление спец. пунктов '.' и '..' для проверки на пустоту     $filteredFiles = array_diff($files, array('.', '..'));      if (count($filteredFiles) === 0) {         throw new Exception("Произошла ошибка: директория пуста.");     }      foreach ($files as $fileName) {         if (str_ends_with($fileName, '.md')) {             $filePath = $oldFilesDirectory . '/' . $fileName;             $oldMdContent = file_get_contents($filePath);             list($oldTables, $oldDescriptions) = markDownToArray($oldMdContent);              $updatedTables = [];             $updatedDescriptions = [];              // Сохраняем названия всех старых таблиц             foreach ($oldTables as $oldTableName => $oldRows) {                 $allOldTables[] = $oldTableName;                 $oldGroup = $oldRows;                  // Совмещение таблиц, если таблица есть в обоих наборах данных                 if (isset($newTables[$oldTableName])) {                     $newGroup = $newTables[$oldTableName];                      // Выявляем только новые строки, отсутствующие в строй таблице                     $newRows = array_udiff($newGroup, $oldGroup, function ($new, $old) {                         return strcmp($new['Название поля'], $old['Название поля']);                     });                      // Объединяем старую и новую таблицы                     $combinedTable = array_merge($oldGroup, $newRows);                      $updatedTables[$oldTableName] = $combinedTable;                 } else {                     $updatedTables[$oldTableName] = $oldGroup;                 }                 $updatedDescriptions[$oldTableName] = $oldDescriptions[$oldTableName] ?? '';                  // Здесь мы добавляем данные каждой таблицы из старых файлов в $allOldData                 $allOldData[] = [                     'name' => $oldTableName,                     'data' => $oldRows,                     'description' => $oldDescriptions[$oldTableName] ?? ''                 ];             }              // Подготовка выходного содержимого             $finalMdOutput = [];             foreach ($updatedTables as $tableName => $data) {                 $finalMdOutput[] = addNameAndHeaderTable($tableName, $updatedDescriptions[$tableName]);                  $sortedRows = sortedRows($data);                  foreach ($sortedRows as $row) {                     $finalMdOutput[] = addRowsInTable($row);                 }                 $finalMdOutput[] = "\n";             }              $updatedFileName = $oldFilesDirectory . '/updated_' . $fileName;             file_put_contents($updatedFileName, implode("", $finalMdOutput));              echo "Изменения сохранены в файл " . $updatedFileName . "\n";         }     }      // Также найдём и сохраним уникальные таблицы, которые были удалены и недостающие в новом (УДАЛЕННЫЕ ТАБЛИЦЫ)     $removedTablesOutput = [];      // Итерация по всем старым таблицам, чтобы найти те, которые отсутствуют в обновлении     foreach ($allOldData as $oldTable) {         $tableName = $oldTable['name'];          // Проверяем, присутствует ли таблица в новых данных         if (!isset($newTables[$tableName])) {             // Таблица была удалена, добавляем её в выходной массив             $removedTablesOutput[] = addNameAndHeaderTable($tableName, $oldTable['description']);              $sortedRows = sortedRows($oldTable['data']);              // Добавляем каждую строку данных таблицы             foreach ($sortedRows as $row) {                 $removedTablesOutput[] = addRowsInTable($row);             }             $removedTablesOutput[] = "\n";         }     }      // Записываем удаленные таблицы в файл     $removedMdFileName = 'removed_old_structure.md';     file_put_contents($removedMdFileName, implode("", $removedTablesOutput));      echo "Таблицы, которые были удалены, сохранены в файл " . $removedMdFileName . "\n";      // Теперь найдем таблицы, которые уникальны для нового и не присутствуют ни в одном из старых (НОВЫЕ ТАБЛИЦЫ)     $newTablesOutput = [];      // Итерация по всем новым таблицам, чтобы найти те, которые отсутствуют в старых данных     foreach ($newTables as $tableName => $newRows) {         // Проверяем, присутствует ли таблица в старых данных         if (!in_array($tableName, $allOldTables)) {             // Таблица была удалена, добавляем её в выходной массив             $newTablesOutput[] = addNameAndHeaderTable($tableName);              // Добавляем каждую строку данных таблицы             foreach ($newRows as $row) {                 $newTablesOutput[] = addRowsInTable($row);             }             $newTablesOutput[] = "\n";         }     }      // Запись новых данных в файл     $newMdFileName = 'add_new_structure.md';     file_put_contents($newMdFileName, implode("", $newTablesOutput));      echo "Новые таблицы сохранены в файл " . $newMdFileName . "\n";  } catch (Exception $exception) {     echo $exception->getMessage();

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

• Автоматизация: исключает ручное сравнение структур.

• Гибкость: поддерживает работу с несколькими файлами.

• Чёткая структура: отчёты в формате Markdown легко читать и анализировать.

Внедрение скриптов в проект

Скрипты для анализа и описания базы данных можно упростить в использовании, интегрировав их в проект. Это особенно удобно, если проект использует фреймворк, поддерживающий команды из консоли, например, Symfony. Если же такого фреймворка нет, можно создать свою CLI-команду. Ниже приведены оба варианта внедрения.

1. Интеграция в проект с использованием Symfony

1.1 Создание команды

Для создания команды используем встроенную возможность Symfony. Выполняем команду:

php bin/console make:command app:describe-database

1.2 Реализация команды

После выполнения команды откроется файл сгенерированного класса команды, например:

src/Command/DescribeDatabaseCommand.php.

Добавляем логику скрипта в метод execute():

namespace App\Command;  use Symfony\Component\Console\Attribute\AsCommand; use Symfony\Component\Console\Command\Command; use Symfony\Component\Console\Input\InputInterface; use Symfony\Component\Console\Output\OutputInterface;  #[AsCommand(     name: 'app:describe-database',     description: 'Генерирует описание структуры базы данных', )] class DescribeDatabaseCommand extends Command {     protected function execute(InputInterface $input, OutputInterface $output): int     {         // Вставляем вашу логику для описания базы данных         $output->writeln('Начало описания базы данных...');                  // Здесь можно перенести основной код скрипта         // Например:         describeDatabase(); // Вызов основной функции, содержащей логику анализа          $output->writeln('Описание базы данных завершено.');                  return Command::SUCCESS;     } }  // Функция для описания базы данных function describeDatabase() {     echo "Описание структуры базы данных...\n";     // Логика анализа структуры базы     echo "Описание завершено.\n"; }

1.3 Запуск команды

Для выполнения команды из консоли достаточно запустить:

php bin/console app:describe-database

2. Создание независимой CLI-команды

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

2.1 Создание скрипта

Создаём файл describe_db.php в корне проекта со следующим содержимым:

#!/usr/bin/env php <?php  // Основная логика скрипта function describeDatabase() {     echo "Начало описания базы данных...\n";          // Вставьте вашу логику скрипта     echo "Описание базы данных завершено.\n"; }  // Запуск функции описания describeDatabase();

2.2 Добавление разрешений на выполнение

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

chmod +x describe_db.php

2.3 Запуск скрипта

Теперь скрипт можно запускать из командной строки:

./describe_db.php

Важные замечания

Шебанг (shebang)

Первая строка #!/usr/bin/env php указывает, что для выполнения скрипта нужно использовать интерпретатор PHP. Это работает только в Unix-подобных системах (Linux, macOS). В Windows эта строка игнорируется, но не вызывает ошибок.

Платформенная совместимость

Если проект используется на Windows, запуск скрипта будет отличаться. Вместо выполнения через ./describe_db.php нужно использовать:

php describe_db.php

Преимущества подхода

o         Вариант с Symfony автоматически интегрируется в существующую архитектуру проекта.

o         Независимый CLI-скрипт подходит для проектов без фреймворка, а также для системных утилит.

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

Заключение

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