Very recently I faced a big problem where I had a older DB working with new version of files. To be precise, I was working with Magento 1.8 files with Magento 1.6 database filled with various products and customer information. And frankly speaking, it landed me into deep water as various stuffs like shopping cart, checkout process, reIndexing services, catalog price updation etc started to give errors. It all happened just because new magento files are expecting new fields in the DB. So, I wrote a piece of code to match the original DB (v1.8) with my current working DB (v1.6). And very soon I was able to track down the table differences in two databases and I just manually added those extra columns which the current DB (v1.6) was lacking. Soon, all the errors on the site mostly vanished.
Manually column addition is a bad idea because it does not create the Indexes which already exist in correct/Original DB. For that, I'll be writing another script. Till that time, check out the php code for this simple Database comparison.
Download table_comparison.php
This solution works the following way ::
1. We connect to 2 Databases within a script. If the server allows multiple DB connection on a single script, then there won't be any problem. Otherwise we need to use a fourth parameter (true) with 2nd mysql_connect() onwards.
mysql_connect( db_host, db_username, db_password, true );
2. We use a "SHOW TABLE" query to fetch all the tables from a Database and store them in an Array. This way we create 2 arrays, first one holding all the table names from DB1 and the second one holding all table names from DB2. So, if a DB has 300 tables and another has 400 tables, that differences can be easily found by counting elements in each array.
3. Now, we would iterate through the first array (holding table names from correct database DB1); taking each table name we would run a "DESCRIBE table_name" SQL and fetch all the column names and store them in an array. Now we would do this for each table in each database and then we would match column names.
4. Reporting would be a very essential thing. We need to use various flags/counters/Strings etc to keep track of changes and use them in generating the report.
Check out some screenshots showing the output ::
Manually column addition is a bad idea because it does not create the Indexes which already exist in correct/Original DB. For that, I'll be writing another script. Till that time, check out the php code for this simple Database comparison.
Download table_comparison.php
This solution works the following way ::
1. We connect to 2 Databases within a script. If the server allows multiple DB connection on a single script, then there won't be any problem. Otherwise we need to use a fourth parameter (true) with 2nd mysql_connect() onwards.
mysql_connect( db_host, db_username, db_password, true );
2. We use a "SHOW TABLE" query to fetch all the tables from a Database and store them in an Array. This way we create 2 arrays, first one holding all the table names from DB1 and the second one holding all table names from DB2. So, if a DB has 300 tables and another has 400 tables, that differences can be easily found by counting elements in each array.
3. Now, we would iterate through the first array (holding table names from correct database DB1); taking each table name we would run a "DESCRIBE table_name" SQL and fetch all the column names and store them in an array. Now we would do this for each table in each database and then we would match column names.
4. Reporting would be a very essential thing. We need to use various flags/counters/Strings etc to keep track of changes and use them in generating the report.
Check out some screenshots showing the output ::
No comments:
Post a Comment