PHP SQLDIFF, a.k.a. SQLDiff

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.

SQLDiff is available at SourceForge.

Click to see larger version

Either approach means I end up with several copies of what is essentially the same table and this always leads to the question:

What Changed?

Trying to answer this question can be really tedious and error prone -- that's why I wrote SQLDiff. What we all want is a simple interface that shows just the columns that changed and just the rows that changed between two tables.


Click to see larger version

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


Click to see larger version
Click to see larger version

So that's what SQLDiff does. You provide two tables, a master table -- a table that has not changed and a modified table -- which has changed (compared to the master). It will figure out what's changed and display it to you as sets of additions to the master, deletions from the master and columns which have changed in the master table.

If the two tables do not match (i.e. the column names do not match), SQLDiff will show you what columns they have in common and you can choose to do your diff on just those. Even when the two tables are identical in format, it's useful to restrict the columns compared and show fewer changes. If you know column 'A' has changed for every row and you don't really care about it, comparing on all the non-A columns can reduce the number of changes you see.


Click to see larger version

Databases Supported

SQLDiff is a PHP application. It makes use of PEAR::DB for its database access and this means that most common databases should work. This application has been tested with Mysql, Postgres and Sybase. As long as PEAR::DB has complete support for your type of database (Oracle, Postgres, DB2 etc), SQLDiff should work without modification.

Configuration

SQLDiff reads a local PHP configuration file which controls a number of things:

More complexity and control is provided by projects which are 'named' configurations which allow one installation of the application to provide many different views of the data. Read this for details on installation and configuration.

Click to see larger version

Security

SQLDiff can be used to modify the master table. Malicious users might attempt to construct a scenario that could modify or delete data unexpectedly. Here are a few (but hardly all) ideas how you might protect your data:

Take the time to protect access to your databases. You have been warned.



Direct questions to: Terry Gliedt tpg@hps.com (Include 'SQLDiff' in the subject line so my SPAM filter lets your message through.)

This is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation. See http://www.gnu.org/copyleft/gpl.html