Compalex: comparing the schemas of two databases

let's Say you have a prod and test database. At some point the developer made changes to the test database but forgot to make these changes to the combat base. If this is a commonly used table, the situation quickly becomes obvious, as the logs show errors in SQL queries, and you begin to call the chief with reproaches, "what the @#$%".

But sometimes the change involves a rarely used table, make any changes at first glance not completely obvious (e.g., someone changed the length of the field VARCHAR and you have clipped the line, or someone added an index, which makes the queries on test database run much faster).

Another option — you had the software upgrade and you have all stopped working. A bunch of unexplained errors on blank space app is, users are not satisfied.

In such cases it is very useful to see what is the difference between the base and draw the appropriate conclusions.



What comes to mind? Do a dump of the structure of one database, then the other. Run the diff utility and it shows lines that differ in two dumps. For small changes it could work. But, first, it is somewhat tedious, and secondly, you may have a situation as in the screenshot, when one database table is missing and the diff begins to compare different tables.



Faced with these problems, I wrote a small php script (https://github.com/dlevsha/compalex), which works with MySQL, MS SQL Server, PostgreSQL (plan to support Oracle) and allows you to compare two databases. The script does not pull any dependencies, simplifying installation and support.

To work you need to install the script itself (assuming that php > = 5.4 with support for PDO you have already installed, if not, then in debian / ubuntu this is done with one line of aptitude install php5, the only — look, you to installed php 5.4 or later version)

the

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

Open .environment in the project folder and edit parameters.

the

[ 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

Choose a driver DATABASE_DRIVER supported 'mysql', 'pgsql', 'dblib'

Rule settings are responsible for connecting to the first and second database respectively.

the

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

In order to avoid confusion where some of the database — give them the names of the parameters DATABASE_DESCRIPTION

Then, while in your project directory, run the web server

the

$ php -S localhost:8000

and open in browser http://localhost:8000/

You should see a schema-comparison, consisting of two columns, in accordance with the specified parameters databases.



The destination of the elements depicted on the diagram.

Sometimes there is no possibility to organize a direct local connection to databases and the changes you want to see directly on the server from the console.

For these purposes I recommend to use a console browser eLinks that supports HTML formatting and colors in the markup.

You need to install a script on the server, start the web server (as described above) and execute:

the

$ elinks http://localhost:8000

Get something like the following:



More information on the website (in English) http://compalex.net/.
Here you can try how it works http://demo.compalex.net/.

Article based on information from habrahabr.ru