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