Compalex: comparing schemas of two databases

Suppose you have a prod and test database. At some point, the developer made changes to the test base, but forgot to make these changes to the combat base. If this is a frequently used table, then the situation very quickly becomes obvious, since errors will appear in the logs in the SQL queries and the boss will start calling you with reproaches for “what # # $%”.

But sometimes the changes affect rarely used tables, either the changes are not at all obvious at first glance (for example, someone changed the length of the VARCHAR field and your lines were truncated, or someone added an index that makes queries on the test database run on order faster).

Another option - you have updated the software and everything has stopped working for you. A bunch of incomprehensible errors from scratch, the application is lying, users are not happy.
')
In such cases, it is very useful to see how the bases differ and draw the appropriate conclusions.



What comes to mind? We do dump structures of one base, then another. Run the diff utility and it shows the lines that differ in the two dumps. For small changes this might work. But, firstly, it is somewhat tedious, and secondly, the situation can turn out as in the screenshot when there is no table in one database and the diff begins to compare different tables.



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

To work, you need to install the script itself (it is assumed that php> = 5.4 with PDO support is already installed, if not, then in debian / ubuntu this is done with a single line aptitude install php5 , the only thing is that you should have php version not lower than 5.4 installed)

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

Open the .environment in the project folder and edit the parameters.

 [ 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 

Select the DATABASE_DRIVER driver from the supported 'mysql', 'pgsql', 'dblib'

Rule settings responsible for connecting to the first and second databases, respectively

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

In order not to get confused where is which database - give them names in the parameters DATABASE_DESCRIPTION

After that, being in the project folder, we launch the web-server

 $ php -S localhost:8000 

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

A comparison diagram consisting of two columns should be displayed, in accordance with the specified database parameters.



The purpose of the elements shown in the diagram.

Sometimes it is not possible to organize a direct local connection to the databases and changes need to be viewed directly on the server from the console.

For these purposes, I recommend using the console browser eLinks , which supports HTML formatting and colors in the markup.

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

 $ elinks http://localhost:8000 

We get about the following:



More information on the website (in English) http://compalex.net/ .

Here you can try how it all works http://demo.compalex.net/ .