Compalex: сравнение схем двух баз данных

    Предположим, у вас есть prod и test базы данных. В какой-то момент разработчик внес изменения в тестовую базу, но забыл внести эти изменения в боевую базу. Если это часто используемая таблица, то ситуация очень быстро становится очевидной, так как в логах появятся ошибки в SQL-запросах и вам начинает звонить начальник с упреками «какого @#$%».

    Но иногда изменения затрагивают редко используемые таблицы, либо изменения на первый взгляд не совсем очевидны (например, кто-то изменил длину поля VARCHAR и у вас стали обрезаться строки, или кто-то добавил индекс, из-за которого запросы на тестовой базе выполняются на порядок быстрее).

    Еще вариант — вы провели обновление ПО и у вас все перестало работать. Куча непонятных ошибок на пустом месте, приложение лежит, пользователи не довольны.

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



    Что приходит на ум? Делаем dump структуры одной базы, потом другой. Запускаем diff-утилиту и она показывает строки, которые отличаются в двух дампах. Для небольших изменений это может сработать. Но, во-первых, это несколько утомительно, во-вторых, может получиться ситуация как на скриншоте, когда в одной базе отсутствует таблица и diff начинает сравнивать разные таблицы.



    Столкнувшись с указанными проблемами, я написал небольшой php-скрипт (https://github.com/dlevsha/compalex), который работает с MySQL, MS SQL Server, PostgreSQL (в планах поддержка Oracle) и позволяет сравнить две базы данных. Скрипт не тянет за собой никаких зависимостей, что упрощает его установку и поддержку.

    Для работы необходимо установить сам скрипт (предполагается что php >= 5.4 с поддержкой PDO у вас уже установлен, если нет — то в debian / ubuntu это делается одной строчкой aptitude install php5, единственное — смотрите, чтобы был установлен php версии не ниже 5.4)

    $ git clone https://github.com/dlevsha/compalex.git
    $ cd compalex
    


    Открываем .environment в папке проекта и правим параметры.

    [ Main settings ]
    ; Possible DATABASE_DRIVER: 'mysql', 'pgsql', 'dblib'.
    ; Please use 'dblib' for Microsoft SQL Server
    DATABASE_DRIVER = mysql
    DATABASE_ENCODING = utf8
    SAMPLE_DATA_LENGTH = 100
    
    [ Primary connection params ]
    DATABASE_HOST = localhost
    DATABASE_NAME = compalex_dev
    DATABASE_USER = login
    DATABASE_PASSWORD = password
    DATABASE_DESCRIPTION = Developer database
    
    [ Secondary connection params ]
    DATABASE_HOST_SECONDARY = localhost
    DATABASE_NAME_SECONDARY = compalex_prod
    DATABASE_USER_SECONDARY = login
    DATABASE_PASSWORD_SECONDARY = password
    DATABASE_DESCRIPTION_SECONDARY = Production database
    


    Выбираем драйвер DATABASE_DRIVER из поддерживаемых 'mysql', 'pgsql', 'dblib'

    Правим настройки, отвечающие за подключение к первой и второй базе данных соответственно

    DATABASE_HOST = localhost
    DATABASE_NAME = compalex_dev
    DATABASE_USER = root
    DATABASE_PASSWORD = password
    


    Для того, чтобы не запутаться где какая база данных — даем им имена в параметрах DATABASE_DESCRIPTION

    После этого, находясь в папке проекта, запускаем web-сервер

    $ php -S localhost:8000
    


    и открываем в браузере http://localhost:8000/

    Должна отобразиться схема-сравнение, состоящая из двух колонок, в соответствии с указанными параметрами баз данных.



    Назначения элементов изображено на схеме.

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

    Для этих целей я рекомендую использовать консольный браузер eLinks, который поддерживает HTML-форматирование и цвета в разметке.

    Необходимо установить скрипт на сервере, запустить web-сервер (как это описано выше) и выполнить:

    $ elinks http://localhost:8000
    


    Получим примерно следующее:



    Более подробная информация на сайте (на английском) http://compalex.net/.

    Здесь можно попробовать как все это работает http://demo.compalex.net/.
    Поддержать автора
    Поделиться публикацией

    Комментарии 27

      +2
      Doctrine\DBAL\Schema\Comparator
        +2
        А не проще хранить каноническую схему базы данных в репозитарии вместе с кодом, а при выкатке новой версии запускать синхронизатор схемы, который вычислит разницу между канонической схемой и реальной, и выдаст (или автоматически выполнит) нужные SQL-операторы, чтобы привести базу к нужному состоянию?
          +5
          В описанном Вами случае правильнее использовать миграцию баз данных, которая описывает состояние баз данных на определенный момент времени и позволяет откатить базу данных на определенную точку.

          Данный скрипт не про это: он для наглядного представления разницы в двух базах, а не для приведения двух баз к одному состоянию.
            +1
            Да это понятно. Просто обе проблемы, которые вы описали, можно решить полностью автоматически, без человеческого фактора, без риска чего-нибудь упустить.
              0
              речь идет о том, что подобная ситуация не должна вообще происходить, потому что в приличных местах используют миграции, живущие вместе с кодом. Хотя, конечно, понятно, что она в реальной жизни будет происходить регулярно, как только появится выделеннный DBA.

                0
                Поделитесь, как организуется миграция?
                У меня работает несколько команд разработчиков над разными модулями, и я спрашивал у разных разработчиков, как организовать сверку схем, но никто не предложил что-то полностью автоматическое. В результате сделали в репозитории папки со скриптами, которые содержат изменения в схеме.
            0
            В SQLYog можно сравнить структуры
              +3
              SQLYog:
              — платная
              — только для MySQL
              — только под Windows

              Compalex (мой скрипт):
              — бесплатный
              — поддерживает MySQL, PostgreSQL и MSSQL (в перспективе Oracle)
              — устанавливается везде где есть php (а он есть практически под любую платформу)
                0
                Postgres какой версии поддерживает?

                hstore, jsonb, всякие vartype[] понимает?
                  0
                  hstore, jsonb, всякие vartype[] — насколько я понимаю это типы полей — должен понимать.
              0
              А сложно доделать сравнение имеющейся базы с .sql файлом, создающим её? Но без выполнения этого .sql файла. А то часто инсталлер расходится с имеющейся базой, а по патчам довольно муторно восстанавливать структуру.
                +1
                Вы можете просто создать еще одну базу и слить туда свой инсталяционный скрипт.

                CREATE DATABASE new_database;
                source /[path_to_file]
                


                … сравниваем с имеющейся базой

                DROP DATABASE new_database;
                
                  0
                  Думаю для таких случаев, было бы хорошо иметь поддержку SQLite
                0
                Дополнительно, бывает полезно сравнить индексы, триггеры. Для MS SQL часто «разъезжаются» job's
                  0
                  Индексы можно сравнивать уже сейчас.

                  Триггеры — думаю можно добавить.

                  +3
                  Обычно это Navicat-ом делаю.

                  1) Вам стоит пересмотреть шрифты. Это же не нью йорк таймс. Используйте что-то более современное без засечек, а для листинга полей программерский моноширный шрифт вроде courier new.

                  2) Строки коннектов нужно дать возможность ввести на лету в форму или просто input:text. Не всегда работа идет с одним и тем же проектом, иногда нужно просто сравнить что-то мелкое и забыть. Без создания конфигов и пр.

                  Спасибо, попробую воспользоваться в будущем.
                    +1
                    При всем уважении, это похоже на инструмент для решения симптомов, но не проблемы (расхождение схемы данных).

                    Но даже без учета этого не легче ли было сделать консольное приложение? которое, кстати, может спрашивать реквизиты подключения к БД без необходимости хардкода их для скрипта.
                      0
                      Добавил в текст статьи вариант использования скрипта в консоли.
                      0
                      sqlyog уже не раз юзал для этих целей. navicat по-моему тоже умеет. скармливаешь системе 2 конекшена и все. ну и она не только показывает дифы, а ещё и может применить их
                        0
                        Пользовались для этого Toad.

                        Текст хранимых процедур анализируете?
                          0
                          Поддержка сравнения функций и процедур есть, но она не очень подходит для анализа больших функций.
                          Думаю в будущем прикрутить для этого полноценный diff
                          0
                          Еще тогда вам feature request: сравнение ENGINE, CHARSET, COLLATE.

                          А в целом действительно полезная утилитка (особенно для консоли).
                            0
                            Сравнивать схемы (помимо прочего) можно еще в DBeaver — бесплатное, опен-сорсное, мультиплатформенное и мультибазное.
                            Фича правда пока сыровата, в частности не умеет генерить diff-DDL. Но если будут реквесты — доделается.
                              0
                              Вот кстати, отдельная утилита сравнения гораздо привлекательней во многих случаях, чем некий (любой) DB manager, который еще надо установить на свой/рабочий ПК (а иногда и купить).

                              И тем более привлекательна консольная версия, против всяких GUI.
                              0
                              Я бы сказал — в некоторых случаях.
                              Разных административных операций над базой существуют тысячи, часто они пересекаются по функционалу. Ставить тысячи маленьких утилит, каждую со своими граблями и косяками — тоже не всегда удобно.
                              Про конкретно обсуждаемый случай — по мне сравнение схем БД в гуе куда удобней чем в консоли. Равно как и мёрж текстовых дифов. Только для БД еще нужна уйма конфигурации.
                                0
                                Используем Doctrine Mogrations для работы уже давно
                                Случая с расхождением баз в деве и на продакшене ни разу не встречали
                                Ну только если забыли выполнить миграцию при выводе на продакшен.
                                Но это решилось автоматическим деплоиментом через Capistrano.
                                Причина вашей ситуации скорее системная и решать ее надо системно, а не писать инструменты для исправления.

                                Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                                Самое читаемое