PHP SQLDiff 2.2

PHP SQLDiff is a Web application that shows the difference between two SQL database tables.

If you manage your database tables like I do, you also make copious copies of the tables. When I go to make any serious change to a table, I copy the table to another database and make my changes there. In other cases my users make changes to their tables using SQLView. Of course, I keep backups of their tables elsewhere.

We know what we want, but its not always an easy thing to get. Here are a few things to keep in mind:

Just as with any diff, the more that changed, the harder it is to see what really happened. SQLDiff provides scrolled sets of changed rows, but any way you cut it, a million changes is beyond what anyone can reasonably manage.

The tables should have a primary key. SQLDiff does allow you to specify the column to use for the comparator. This means, however, that if your tables do not have the same primary key, then SQLDiff will not actually allow you to make changes. If you choose the wrong comparator column or the data for the column is not unique, you can serious mess up your tables.

The two tables must be basically the same. That is, they must have the same column names and the datatypes for the columns must match. SQLDiff can compare tables that do not have exactly the same columns, but youll have to choose what columns to compare. It will also pretend that columns of the same name, but with a different datatype are the same. This is not always true, so you should be particularly careful when making changes to unlike tables. In any case, the more the tables differ, the more differences are found until you can hardly figure out what happened.

Having said that, there are times when the change is that youve added or deleted a column or two. So SQLDiff will detect this and let you choose what columns you want to compare. In any case you still must have primary keys that match.

Really really large tables can take a really really long time to diff. In order to do the compare, we must read the entire table and that can be pretty hard on your systems. If youve got a few tens of thousands of rows, SQLDiff should be satisfactory, but if youve got a few million rows, well... good luck. Theres a good chance PHP will run out of memory and depending on your web server configuration, you might not get anything back so you can tell whats going on. In general you will not be happy when comparing really large tables.

Once the diff is known, you can make changes so certain (or all) rows or columns get updated in the master table from the modified table. Before actually commiting a database change, use the new Show SQL button and see if the SQL is reasonable.

Databases are seldom static and SQLDiff does not even pretend to protect you. If you do a diff on a pair of tables and one of then changes afterwards and then you try to synchronize them with SQLDiff... well, you could be surprised.