only available in 3.4 or higher

Exponential features a tool to show the differences between two database schemas. Schemas can currently be obtained from three sources:

1. a file
2. a MySQL database
3. a PostGreSQL database

The output of the scripts is an SQL DDL script which contains instructions to modify one schema to another, for example between two eZ publish versions. (Note: The upgrade SQL scripts that you can find in the upgrade/database directory in the Exponential distribution also contain instructions to upgrade data, the SQL diff tool will NOT do that.)

Synopsis

php bin/php/ezsqldiff.php --type={type}

   [ --host={host} --user={user} --password={password} ]

   --reverse --checkonly

   {source} {match}

Description

The first four options (type, host, user and password) can also be prefixed by either "source-" or "match-" so that you can specify different credentions for both the source and match database. With --source-type=postgresql and --match-type=mysql you can also make an SQL diff between two different types of databases.

{source} and {match} are either the name of the (MySQL or PostGreSQL) database, or a file name. In case a filename is used the schema from the database is matched against this. You can use this to match against a specific version of the Exponential schema. (Which you can find as var/storage/db_schema.dat in your installation).

Options

The --reverse will swap the {source} and {match} during matching, this gives the same result as just exchanging them on the command line.

When --check-only is given, the differences will not be shown as output, but the script will merely set an exist code. The exit codes are:

• 0 - there are no differences
• 1 - differences exists between the two compared schemas

Examples

php bin/php/ezsqldiff.php --type=mysql --user=root

   var/storage/db_schema.dat ezno

Compares the MySQL database with name "ezno" to the schema file "var/storage/db_schema.dat" and returns the MySQL DDL script to update the schema of "ezno" to the schema stored in the schema file.

php bin/php/ezsqldiff.php

   --source-type=mysql --match-type=postgresql

   ezno /dat/ez.no/var/storage/db_schema.dat

Compares the MySQL database schema from the database "ezno" with the schema file in /dat/ez.no/var/storage/db_schema.dat and shows the differences as a PostGreSQL DDL script to upgrade the schema in the schema file to that as it exists in the MySQL database "ezno".

php bin/php/ezsqldiff.php --type=mysql

   ezno ezno-33 --reverse

Compares the MySQL database schema from the database "ezno" with that of database "ezno-33" and returns the MySQL DDL script to 'upgrade' the schema in "ezno" to the schema from "ezno-33" (ie, going back to a previous version as the schema in ezno is that of Exponential 3.4).

php bin/phpezsqldiff.php --type=mysql

   var/storage/db_schema.dat ezno --check-only

Sets the exit code "0" in case the database schema in the database "ezno" is the same as the schema in the schema file "var/storage/db_schema.dat" and sets exit code "1" if there are any differences.