19.02.2025 07:39
PPR 1 3400 Источник

Введение

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

Для решения данной задачи разработаны два 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-скрипт подходит для проектов без фреймворка, а также для системных утилит.

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

Заключение

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

Комментарии (1)


  1. cross_join
    20.02.2025 14:25
    #27947884

    "Смешались в кучу кони, люди..." (с) Храните структурированные метаданные в структурированном же виде (таблицы с историей изменений, XML или JSON с подключенной схемой), сравнивайте их (N vs N-1). В маркдаун или еще куда-то выводите результаты, да хоть в Word.