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.

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

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.

We know what we want, but it's 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 you'll 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 you've 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 you've got a few tens of thousands of rows, SQLDiff should be satisfactory, but if you've got a few million rows, well... good luck. There's 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 what's 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.
  
  

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.

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:

• Header of the prompt on the first page
• Default values for each database (type, host, userid etc)
• What details the user is prompted for as shown below where everything but the table names is taken care of. You may vary this as you'd like.
• Enable/disable update, add or delete of rows. Your ultimate security is based on the authorization for the SQL user being used.
• Background colors used in the HTML

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.

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:

• Use a secure protocol (https) when using SQLDiff.
  
• Protect uses of SQLDiff with web server access controls like those described at the Apache site.
  
• Make sure the database userid being used has the minimal capabilities required. For instance, don't use a userid which can delete rows if you do not want to enable delete for a table.
  

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