SQLView Installation and Configuration

Installation

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

Configuration Files

SQLView 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. .../sqlview/?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['difftype'] = 'mysql'; $LDB['diffhost'] = 'localhost'; $LDB['diffdb'] = 'production'; $LDB['diffuser'] = ''; $LDB['diffpass'] = ''; $LDB['difftable'] = ''; $LOCALOPT['promptfor'] = array('user', 'pass', 'table'); // Anyone can play $LDB['allow_list'] = array(); // Change nothing $LOCALOPT['addrecord'] = FALSE; $LOCALOPT['copyrecord'] = FALSE; $LOCALOPT['delrecord'] = FALSE; $LOCALOPT['updaterecord'] = FALSE;

When starting SQLView the user will be prompted for the database userid, password and table. Anyone may invoke the application. No changes may be made to the 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');

OVERRIDES - Determining Your Database Schema

Sometimes your database type is not well supported by PEAR::DB. If SQLView cannot determine the schema of your database, some functionality is lost. In this case you can provide your own PHP function to determine the schema for your database. Simply define a function with the name 'OVERRIDE_DESC_' + your database type (e.g. OVERRIDE_DESC_sqlite). A convenient place to put this code is in your local_config.php file. Here is example code that I use for SQLite databases:

//--------------------------------------------------------------- // OVERRIDE_DESC_sqlite - DESCRIBE for sqlite // Since PEAR::DB is broken, I do it myself // // Parameters: // tbl - name of table // db = name of database // // Returns: // Array of array of DESCRIBE details like PEAR::DB tableInfo() //--------------------------------------------------------------- function OVERRIDE_DESC_sqlite($tbl, $db) { $tinfo = array(); // Recent PHP has a better function if (function_exists('sqlite_fetch_column_types')) { $dbhandle = sqlite_open($db); // Open database $cols = sqlite_fetch_column_types($tbl, $dbhandle, SQLITE_ASSOC); $n = 0; foreach ($cols as $column => $type) { $tinfo[$n]['table'] = $tbl; $tinfo[$n]['name'] = $column; $tinfo[$n]['type'] = $type; $tinfo[$n]['len'] = ''; $tinfo[$n]['flags'] = ''; $n++; } return $tinfo; } // Fallback, get schema from SQLITE_MASTER table $sql = "SELECT sql FROM SQLITE_MASTER WHERE tbl_name='$tbl'"; $result = $dbh->query($sql); if (DB::isError($result)) { nice_PEAR_exit($result, "SQL command failed: $sql
\n"); } $row = $result->fetchRow(DB_FETCHMODE_ASSOC); if (! $row) { redmsg("No SQLITE SCHEMA found for table '$tbl'"); return FALSE; } list($col,$val) = each($row); $lines = explode("\n",$val); // First, parse output to pick out the primary key $pkey = ''; foreach ($lines as $line) { if (preg_match('/PRIMARY KEY .(\w+)/', $line, $matches)) { $pkey = $matches[1]; break; } } // Pick out the columns and definitions $n = 0; reset($lines); foreach ($lines as $line) { if (preg_match('/^CREATE TABLE/', $line)) { continue; } if (preg_match('/PRIMARY KEY/', $line)) { continue; } // This is a column and type and maybe length if (preg_match('/^\s+(\S+)\s+(\S+)/', $line, $matches)) { $tinfo[$n]['table'] = $tbl; $tinfo[$n]['name'] = $matches[1]; $matches[2] = str_replace(',','',$matches[2]); // Remove trailing , if (preg_match('/(\w+).(\d+)./', $matches[2], $typematches)) { $tinfo[$n]['type'] = $typematches[1]; $tinfo[$n]['len'] = $typematches[2]; } else { $tinfo[$n]['type'] = $matches[2]; $tinfo[$n]['len'] = ''; } if ($matches[1] == $pkey) { $tinfo[$n]['flags'] = 'not_null primary_key'; } else { $tinfo[$n]['flags'] = ''; } $n++; } } if (count($tinfo)) { return $tinfo; } return FALSE; }

OVERRIDES - More Control of the HTML

This topic could conceivably grow to allow the local installation to control all sorts of details of what is shown. For now we have just one OVERRIDE - to allow one to control the table information (e.g. the columns in the table). You provide a function in your local configuration file. The name of each function and parameters passed to it are specified here. If you define these functions, they get called and replace or or allow you to modify what SQLView generates.

OVERRIDE_info($tbl, $desctbl)

This gets called when you select the 'DESC tablename' in the header or when you do not specify a table name at startup. In this latter case you are presented with a list of all the tables in the database and a link to the DESCRIBE information for the table. In either case, if the function OVERRIDE_info is defined, it is called in place of the default code provided by SQLView. This code should generate the entire table description. The default is a <TABLE> showing the column name, special flags (e.g. PRI) and the definition of the column (e.g. STRING). Your OVERRIDE_info() could add to this or replace it with something more to your needs. Here is example code that I use:

//--------------------------------------------------------------- // OVERRIDE_info - Local version of info // Print body of HTML page to show table details. // // Parameters: // tbl - name of table // desctbl - array of descibe information // // Returns: // HTML for page or empty string saying we did not handle this //--------------------------------------------------------------- function OVERRIDE_info($tbl, $desctbl) { global $dbh; // Get phenovars information on this table for each variable // Hard code connection details. // If not exactly what we handle, return nothing and take default if ($_SESSION['sql_db'] != 'forms') { return ''; } $dbhsave = $dbh; // Save existing db connection db_connect('mysql', 'localhost', 'myuid', 'mypw', 'commentary'); $sql_query = "SELECT description FROM phenovars WHERE " . "dbtable='$tbl' AND variable='"; // Show DESC information we cached earlier $html = <<<END <h2 align="center">$tbl with Phenovars Description</h2> <table class="info" align="center" width="90\%"> <tr> <th class="infohdr">Field Name</th> <th class="infohdr">Value</th> <th class="infohdr">Data Type</th> <th class="infohdr">Phenovars Description</th> </tr> END; // Generate a row of name, type, null, key, & default values $prikey = $desctbl['_prikey_']; while ($key = key($desctbl)) { next ($desctbl); if ($key == '_inc_') { continue; } // Ignore special keys if ($key == '_prikey_') { continue; } if ($key == $prikey) { $c = 'infopri'; $p = 'PRI'; } else { $c = 'info'; $p = ''; } // Attempt to get first description from phenovars // Show error or result $result = $dbh->limitQuery($sql_query . $key . "'", 0, 1); if (DB::isError($result)) { $phenodesc = $result->getMessage() . "<br/>" . $result->getDebugInfo(); } else { $r = $result->fetchRow(DB_FETCHMODE_ASSOC); $phenodesc = $r['description']; } $html .= "<tr>\n" . " <td class=\"$c\">   <b>$key</b> </td>\n" . " <td class=\"info\">  " . strtoupper($desctbl[$key]) . " </td>\n" . " <td class=\"info\">  $p </td>\n" . " <td class=\"info\">  $phenodesc </td>\n" . "</tr>\n"; } $html .= "</table>\n"; $dbh = $dbhsave; // Put database back return $html; }

Projects

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 SQLView 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 view a table relating to personal issues. Another project might allow operations to see data in a table related to 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/SQLView_projects';

In this case a project named 'people' would resolve to a configuration file '/home/http/conf/sqlview_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 SQLView.

Projects - a Complete Example

Lets assume you want to provide easy access to personal information for HR. Here's a local_config.php file:

$LOCALOPT['title'] = "HR Data"; $LDB['defaulttype'] = 'pgsql'; $LDB['defaulthost'] = 'localhost'; $LDB['defaultdb'] = 'hr'; $LDB['defaultuser'] = ''; $LDB['defaultpass'] = ''; $LDB['defaulttable'] = ''; $LOCALOPT['promptfor'] = array('user', 'pass', 'table'); $LDB['allow_list'] = array('hrchief', 'hrlead', 'hr1', 'hr2'); $LOCALOPT['addrecord'] = FALSE; $LOCALOPT['copyrecord'] = FALSE; $LOCALOPT['delrecord'] = FALSE; $LOCALOPT['updaterecord'] = FALSE; $LDB['projects_dir'] = '/home/http/conf/sqlview_projects';

In this case we provide general read-only access to four users from HR. They must know the database user, password and table they are interested in.

Now we define a project 'hrchange' which allows two people in HR to update just a key table in the database. This is file '/home/http/conf/sqlview_projects/hrchange.php'.

$LOCALOPT['title'] = "HRCHANGE: Update Details in PEOPLE"; $LDB['defaultuser'] = 'hrmaster'; $LDB['defaultpass'] = '^%y#zz.4'; $LDB['defaulttable'] = 'people'; $LDB['allow_list'] = array('hrchief', 'hrlead'); $LOCALOPT['promptfor'] = array(); $LOCALOPT['updaterecord'] = TRUE;

The URL for this project is /sqlview/index.php?project=hrchange. Only two individuals can make changes to the single table 'people'. They cannot add or delete records. Notice we chose to override only a few values in local_config. The $LOCALOPT['updaterecord'] value was changed to TRUE to allow updating but we 'inherit' settings (FALSE) for the other $LOCALOPT[*record] variables. This example does not prompt for any connection information.

In this example we define a second project 'hrdeladd' which allows just one person to add or delete records to a key table in the database. This is file '/home/http/conf/sqlview_projects/hrdeladd.php';

$LOCALOPT['title'] = "HRDELADD: Add/Remove Details in PEOPLE"; $LDB['defaultuser'] = 'hrmaster'; $LDB['defaultpass'] = '^%y#zz.4'; $LDB['defaulttable'] = 'people'; $LDB['allow_list'] = array('hrchief'); $LOCALOPT['promptfor'] = array(); $LOCALOPT['addrecord'] = TRUE; $LOCALOPT['copyrecord'] = TRUE; $LOCALOPT['delrecord'] = TRUE; $LOCALOPT['updaterecord'] = TRUE;

This is fine as far as it goes, but there's an extra step here - the user still is clicking on a button to get to the job at hand. Adding the following to the bottom of hrdeladd.php, the user will go straight to the screen showing database data.

// 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['defaulttype']; $_SESSION['sql_host'] = $LDB['defaulthost']; $_SESSION['sql_db'] = $LDB['defaultdb']; $_SESSION['sql_user'] = $LDB['defaultuser']; $_SESSION['sql_pass'] = $LDB['defaultpass']; $_SESSION['sql_table'] = $LDB['defaulttable']; $_SESSION['cmd'] = "SELECT personid,name,fname,birthday,sex from $_SESSION['sql_table']"; $_SESSION['rowinc'] = 10; // Redirect directly to display.php, avoiding prompt to user global $cid; header("Location: display.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 "display.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.

Adding this code to project files and specifying all the connection information is very handy. Adding one last variable to the project file can prevent the user from seeing any other table - thereby locking the user so only the one table is viewable.

$LOCALOPT['donotwander'] = TRUE;


Direct questions to: Terry Gliedt tpg@hps.com (Include 'SQLView' 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