Comparing tables structure fromdifferent databases (versions)

[pilou2]

Comparing files with a tool like Winmerge is easy but when it comes to database...

To compare structures changes in Zen Cart tables between two ZC versions or between a default version and a cart with lots of plugins can be pretty hard.

I found a way to compare two tables using MySQL derivated tables. It might be usefull to others, so here it is:
This query compares tables 'customers' from two different databases (here 'vanilla' and 'prod').

SELECT column_name,ordinal_position,data_type,column_type FROM 
( SELECT column_name,ordinal_position, data_type,column_type,COUNT(1) rowcount FROM information_schema.columns WHERE 
( (table_schema='vanilla' AND table_name='customers') OR (table_schema='prod' AND table_name='customers') ) 
GROUP BY column_name,ordinal_position, data_type,column_type HAVING COUNT(1)=1 ) A;

Try it by changing tables and databases names.
The result is a table containing differences bettween the two compared tables.

By the way I would like to know how do you do this kind of comparaison. Is there a specialized tool I don't know of?
Tested on ZC 1.5.8a database, PHP 8.1 and MySQL 8.034

[pilou2]

...
I found a way to compare two tables using MySQL derivated tables. It might be usefull to others, so here it is:
This query compares tables 'customers' from two different databases (here 'vanilla' and 'prod').

SELECT column_name,ordinal_position,data_type,column_type FROM 
( SELECT column_name,ordinal_position, data_type,column_type,COUNT(1) rowcount FROM information_schema.columns WHERE 
( (table_schema='vanilla' AND table_name='customers') OR (table_schema='prod' AND table_name='customers') ) 
GROUP BY column_name,ordinal_position, data_type,column_type HAVING COUNT(1)=1 ) A;

...

Well it seems I was tired when I did this post... I use derived table and not 'derivated'... And the SQL query is not optimized at all, there are some useless stuff left over...
The following query returns differences between tables 'products' in databases 'vanilla' and 'prod' based on GROUP BY parameters:

SELECT db as 'Database',column_name,ordinal_position,data_type,column_type FROM 
( SELECT table_schema as db,column_name,ordinal_position,data_type,column_type FROM information_schema.columns WHERE 
( (table_schema='vanilla' AND table_name='products') OR (table_schema='prod' AND table_name='products') ) 
GROUP BY column_name,data_type,column_type HAVING COUNT(1)=1 ) as A 
ORDER BY ordinal_position ASC;

[swguy]

phpMyAdmin has an option to export STRUCTURE only. This will allow you to do a quick comparison.

[pilou2]

phpMyAdmin has an option to export STRUCTURE only. This will allow you to do a quick comparison.

I actually use this function for backup of structure (for developpement) and comparaison too in combinaison with WinMerge. It is probably the quickest way I know to compare but I am looking for a more 'direct' way that could be incorporated in a script too.
The SQL query although powerfull needs to be tweaked for each type of comparaison, which makes it a little bit complicated to use. I am now thinking that a PHP script with parameters for the query is the way to go. I just need time to code something...