SQLDiff Installation and Configuration


Installation is quite straight forward. The application is distributed as a compressed tar file (.tgz). Un-tar this (e.g. tar xzf sqldiff-2.0.tgz) in your HTDOCS directory (name will be 'sqldiff', although you may rename this as you wish). Now configure the application by creating your local configuration file.

Configuration Files

SQLDiff reads a local PHP configuration file by the name of local_config.php. You create this file the first time by copying it from model_config.php. Subsequent releases of this application will provide new versions of model_config.php. Be sure that you compare your local_config.php with the new model_config.php and make the necessary changes to your local_config.php. The local_config file controls a number of things:

Since this is coded in PHP and is part of the local installation, any of the features could be controlled based on parameters passed to the initial program (e.g. .../sqldiff/?what2diff=dev) or can vary based on environment variables like REMOTE_USER etc. Keep in mind that the local_config file is written in PHP and you must follow PHP syntax. This means it is very flexible and allows you to tailor the environment extensively, but also means small errors in local_config.php can render the application useless.

local_config Variables

Some of the more interesting variables in the local_config.php file that you will want to set include:

For example, here's a local_config that controls just the basics:

$LOCALOPT['title'] = "Just the Bare Minimum"; // Connection details $LDB['difftype1'] = 'mysql'; $LDB['diffhost1'] = 'localhost'; $LDB['diffdb1'] = 'production'; $LDB['diffuser1'] = ''; $LDB['diffpass1'] = ''; $LDB['difftable1'] = ''; $LDB['difftable2'] = ''; $LDB['difftype2'] = $LDB[difftype1]; $LDB['diffhost2'] = $LDB[diffhost1]; $LDB['diffdb2'] = $LDB[diffdb1]; $LDB['diffuser2'] = $LDB[diffuser1]; $LDB['diffpass2'] = $LDB[diffpass1]; $LOCALOPT['promptfor'] = array('user', 'pass', 'table'); // Anyone can play $LDB['allow_list'] = array(); // Change nothing $LOCALOPT['delrecord'] = FALSE; $LOCALOPT['updaterecord'] = FALSE; $LOCALOPT['insertrecord'] = FALSE;

When starting SQLDiff the user will be prompted for the database userid, password and tables for both the master and modified tables. Anyone may invoke the application. No changes may be made to the master table.

This configuration is pretty wide open. Anyone can see data in any table, as long as they know a database userid and password. Consider adding web server access controls so you at least limit who can 'walk up to the door'. Even better, limit who may use the application by setting:

$LDB['allow_list'] = array('tom', 'pete', 'joann', 'mary');


In release 2.0 we introduced the concept of a 'project'. This is simply a name which identifies yet another configuration file, in addition to the site defaults (local_config.php). This allows you to define a named set of control values. You invoke a project by just passing the parameter 'project=name' on the URL when SQLDiff is invoked.

Projects allow you to create one installation of the application and yet provide tailored versions for differing sets of your users. For instance, you might have one project for your HR people so they can see differences in two tables relating to personal issues. Another project might show operations differences in two tables related to reports created for access controls. Using projects keeps each independent of the other and can even be set up so the user only provides a password (or even nothing).

The project name identifies a PHP file in $LDB[projects_dir] which is included after the application starts. To the local_config shown above we need to add:

$LDB['projects_dir'] = '/home/http/conf/sqldiff_projects';

In this case a project named 'people' would resolve to a configuration file '/home/http/conf/sqldiff_projects/people.php'. Details in this file are the same as for local_config. Keep in mind variables set in a project file override whatever is set in local_config.

Note that projects are a luxury. You can always provide all the database connection details manually and get the same results. Projects were invented to make it easier to provide 'canned sets' of invocations of SQLDiff.

Projects - a Complete Example

Lets assume you want to provide a project to compare two tables containing details on people. Perhaps a development project is about to be put into production and you want to verify the development and production tables are in sync. Here's a local_config.php file:

$LOCALOPT['title'] = "Developers Setup, Compare to Production"; $LDB['difftype1'] = 'pgsql'; $LDB['diffhost1'] = 'localhost'; $LDB['diffdb1'] = 'dev'; $LDB['diffuser1'] = ''; $LDB['diffpass1'] = ''; $LDB['difftable1'] = ''; $LDB['difftable2'] = ''; $LDB['difftype2'] = 'mysql'; $LDB['diffhost2'] = $LDB[diffhost1]; $LDB['diffdb2'] = 'prod' $LDB['diffuser2'] = $LDB[diffuser1]; $LDB['diffpass2'] = $LDB[diffpass1]; $LOCALOPT['promptfor'] = array('user', 'pass', 'table'); $LDB['allow_list'] = array('tom', 'pete', 'joann', 'mary'); $LOCALOPT['delrecord'] = TRUE; $LOCALOPT['updaterecord'] = TRUE; $LOCALOPT['insertrecord'] = TRUE; $LDB['projects_dir'] = '/home/http/conf/sqldiff_projects';

In this case we compare tables in a 'dev' and 'prod' database, Note the master is 'dev' and that the database types differ (MySQL and Postgres). All four users can make changes to the master (dev) table.

Now we define a project 'names2prod' which allows us to compare and update just one table in the 'prod' (not dev) database. This is file '/home/http/conf/sqldiff_projects/names2prod.php'.

$LOCALOPT['title'] = "NAMES2PROD: Verify Names is Correct in Prod"; // Connection details $LDB['difftype1'] = 'mysql'; $LDB['diffhost1'] = 'localhost'; $LDB['diffdb1'] = 'prod'; $LDB['diffuser1'] = 'webapp'; $LDB['diffpass1'] = ''; $LDB['difftable1'] = 'names'; $LDB['difftype2'] = 'pgsql'; $LDB['diffhost2'] = $LDB['diffhost1']; $LDB['diffdb2'] = 'dev'; $LDB['diffuser2'] = 'test'; $LDB['diffpass2'] = $LDB['diffpass1']; $LDB['difftable2'] = $LDB['difftable1']; $LDB['allow_list'] = array('mary'); $LOCALOPT['promptfor'] = array('pass'); $LOCALOPT['delrecord'] = FALSE; $LOCALOPT['insertrecord'] = FALSE;

The URL for this project is /sqldiff/index.php?project=names2prod. Only the user 'mary' may use this and we have restricted this to the table 'names' in the prod database which may only be updated. Nothing may be deleted or removed from the prod version of the table. Notice we chose to override most values in local_config. The $LOCALOPT[updaterecord] value was 'inherited' from local_config and since it was not set in names2prod, it remained TRUE.

This example prompts for the password of the two tables. It does not even show what the table names are. If we modify this with:

$LDB['diffpass1'] = '*%SeK+22'; $LDB['diffpass2'] = 'simple'; $LOCALOPT['promptfor'] = array();

then the user would only be prompted with a button 'Compare Tables'. We can go even further and skip this prompt altogether. Adding the following to the bottom of names2prod.php, the user will go straight to the screen showing the differences after clicking on the project URL:

// Only redirect if this is the entry point if (! strstr($_SERVER['SCRIPT_FILENAME'],'index.php')) { return; } // See if the user is allowed to use this configuration (redirect skips this) verify_access($_SESSION['project']); // Set variables referenced by display.php $_SESSION['sql_type'] = $LDB['difftype']; $_SESSION['sql_host'] = $LDB['diffhost']; $_SESSION['sql_db'] = $LDB['diffdb']; $_SESSION['sql_user'] = $LDB['diffuser']; $_SESSION['sql_pass'] = $LDB['diffpass']; $_SESSION['sql_table'] = $LDB['difftable']; $_SESSION['sql_type2'] = $LDB['difftype2']; $_SESSION['sql_host2'] = $LDB['diffhost2']; $_SESSION['sql_db2'] = $LDB['diffdb2']; $_SESSION['sql_user2'] = $LDB['diffuser2']; $_SESSION['sql_pass2'] = $LDB['diffpass2']; $_SESSION['sql_table2'] = $LDB['difftable2']; // Redirect directly to diff.php, avoiding prompt to user global $cid; header("Location: diff.php?cid=$cid"); exit;

This code must be added to the bottom of the project file. The first PHP statement (if (! strstr...) prevents this from being executed anywhere, except at the entry point to the application (index.php). We call verify_access() to make sure only users in $LDB[allow_list] are allowed to invoke this. This is followed by a series of assignments saving the database connection information to be saved in the session.

The end of this code is to redirect to "diff.php" and do the difference immediately, without a prompt. The parameter "cid=$cid" must be provided so the application does not lose it's session variables. It looks a little complex, but this code never changes, so all you need do is copy/paste this into your project file.

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