RE: database abstraction layer

["Daevid Vincent" <daevid@xxxxxxxxxx>]

> -----Original Message-----
> From: Lars Nielsen [mailto:lars@xxxxxxxxxx] 
> Sent: Tuesday, February 02, 2010 12:24 PM
> To: php-general@xxxxxxxxxxxxx
> Subject:  database abstraction layer
> 
> Hi List
> 
> I am trying to make a Database Abstraction Layer so I can which the DB
> of my application between MySQL and Postgresql. I have been looking at
> the way phpBB does it, and it seems that it is only then php-functions
> which are different. The SQL seems to be the same.
> 
> Is it save to assume that I can use the same SQL, or should i 
> make some
> exceptions?
> 
> Regards 
> Lars Nielsen

There are differences in the actual schema between mySQL and Postgress.

At least there were a few years back when we looked at converting. In the
end, we decided it was too much hassle to switch all our code and database
tables, so just coughed up the licensing for mysql (we were shipping mysql
on our appliance).

So, before you jump into writing all this code, I would first try to make
your app run in postgress and find out about which mySQL statements are
'extensions' to ANSI standard. Case sensitivity was a huge issue for us, as
in one of those RDBMS was very particular about it. There were some other
issues that I can't remember ATM, but perhaps they've been addressed by
now.

One thing I would maybe suggest is (what I do), write a wrapper around your
wrapper -- AKA "Double Bag It". :)

Here at Panasonic Avionics (PAC) we use the PEAR::DB class (the really old
version) since we have to interface with mySQL, SQL Server, Oracle (two
versions). That's where PEAR::DB comes in. However, it's very crude and you
have a lot of redundant code in every page. Like this:
http://pear.php.net/manual/en/package.database.db.intro-fetch.php
You always have to open a connection, test for errors, do the query, test
for errors, fetch the rows, etc..

When I came on board a year ago, I put an end to that micky mouse crap. I
wrote a nice db.inc.php wrapper that handles all that sort of thing, and
then pumps it up like it's on steroids. I added auto-reconnect in case the
connection dropped. I added color-coded SQL output with substitution for
the '?'. I added a last_insert_it() routine which is proprietary to mySQL
BTW (speaking of incompatibilities). I added routines to get an Enum
column, or to get a simple array pairing, etc. It can even force reads from
slave and writes to master! It pretty much kicks ass.

Just simply do this:

$myfoo = sql_query('agis_core', 'SELECT * FROM foo WHERE bar = ?', $bar);

All the minutia is handled for you and $myfoo is now an array of your
results. :)

So, now we code using my wrapper and should we want to switch out the DBAL
later to a more modern one, we just change OUR wrapper calls. There is
minimal overhead, and the pros FAR outweigh any cons.

I've attached it here.

We have another config.inc.php that has the DB settings for each
DEV/TEST/PROD master/slave servers (as they are all different accounts for
security reasons. So just make one with entries like this:

// AGISCore Database DEV Master mySQL:
$global_db_dsn_agis_core_master = array(
	'phptype'  => 'mysql',
	'username' => 'RWMaster',
	'password' => 'rwmaster',
	'hostspec' => '10.10.10.2:3306',
	'database' => 'agis_core',
	'persistent' => TRUE
	);

// AGISCore Database DEV Slave mySQL:
$global_db_dsn_agis_core_slave = array(
	'phptype'  => 'mysql',
	'username' => 'ROSlave',
	'password' => 'roslave',
	'hostspec' => '10.10.10.3:3306',
	'database' => 'agis_core',
	'persistent' => TRUE
	);


$GLOBALS['DB_CONNECTIONS'] is a singleton (sans the class overhead) so that
you always get the same handle for each database call and don't spawn new
ones each time. Nice. :)

<?php
/**
 * All of the database wrapper functions
 *
 * This is a wrapper around the PEAR::DB class. It provides many enhancements including
 * a singleton for database handle connections, retries for connections, debugging with ? substitutions,
 * handy routines to populate arrays, select boxes, IN() statements, etc. It can do SQL timing profiling.
 * There are routines for INSERT and UPDATE by simply passing in an array of key/value pairs.
 *
 * Confidential property of Panasonic Avionics. Do not copy or distribute.
 * @copyright	2006-2010 Panasonic Avionics. All rights reserved.
 * @category	CategoryName
 * @package	PackageName
 * @see
 * @since		DART2
 * @author		Daevid Vincent <daevid.vincent@xxxxxxxxxxxxxx>
 * @date		Created: 2009-01-20
 * @version	CVS: $Id: db.inc.php,v 1.39 2010/01/29 01:35:30 vincentd Exp $
 */
require_once '/usr/share/php/DB.php';

$SQL_OPTION['noHTML'] 	 = false;
$SQL_OPTION['fullQuery'] = true;
$SQL_OPTION['useLogger'] = false;
$SQL_OPTION['profile'] 	 = 0;
$SQL_OPTION['debug'] 	 = false;
$SQL_OPTION['outfile'] 	 = false; //set this to a filename, and use $show_sql in your queries and they'll go to this file.

$GLOBALS['DB_CONNECTIONS'] = array(); //this will hold each db connection so we'll only create one at a time. like a singleton.

/**
* A wrapper around the SQL query function that provides many extra features.
*
* Handles the $db handle, errors and echoing of the SQL query itself
* and stores any errors in the $GLOBALS['SQL_ERROR_STRING'];
*
* NOTE: PEAR:DB has many shortcomings, one of which is that the key of the returned hash will be lowercased!
*
* Prepared statements can use ?, !, & -- see http://pear.php.net/manual/en/package.database.db.intro-execute.php for more information.
*
* @access 	public
* @return 	mixed a hash of data, a result set handle pointer or false
* @param 	string $database the database to connect to (agis_core) is the default
* @param 	string $sql The SQL query to be executed, this can be SELECT, INSERT, UPDATE or DELETE amongst others.
* @param 	array $sqlvalues The sqlvalues to the $sql. One element per ? is required if this parameter is used.
* @param 	boolean $show_sql output the $sql to the display (for debugging purposes usually). false by default.
* @param	array $parameters
* 			int db_fetch_mode (DB_FETCHMODE_ORDERED, DB_FETCHMODE_ASSOC, DB_FETCHMODE_OBJECT)
* 			boolean $show_errors output any errors encountered to the display (for debugging purposes usually). true by default.
* 			boolean $execute useful for debuging when you don't want the SQL command to actually execute, but you may want to see the query passed i.e. SQL_QUERY($sql, true, true, false); true by default.
* 			boolean $no_html when using the function in console scripts to strip off HTML tags.
* 			int $parameters['profile'] detail level (1-3) to output the SQL to /tmp/SQL_profile.txt.
* 					profile SQL statements in varying detail levels.
*						Detail Levels:
*	 						1 = m/d/y/ h:i:s
*	 						2 = SQL timing
*	 						3 = filename
* @see		sql_connect()
* @author 	Daevid Vincent [daevid.vincent@xxxxxxxxxxxxxx]
* @date    	06/04/09
* @todo		Intercept SELECT vs. INSERT/UPDATE/DELETE and connect to SLAVE vs MASTER respectively
*/
function sql_query($database='agis_core', $sql, $sqlvalues=array(), $show_sql=false, $parameters=array())
{
	if (!isset($parameters['db_fetch_mode'])) 	$parameters['db_fetch_mode'] = DB_FETCHMODE_ASSOC;  //DB_FETCHMODE_ORDERED is the other common one to use
	if (!isset($parameters['show_errors'])) 	$parameters['show_errors']   = true;
	if (!isset($parameters['execute'])) 		$parameters['execute'] 		 = true;
	if (!isset($parameters['no_html'])) 		$parameters['no_html'] 		 = false;
	if (!isset($parameters['profile'])) 		$parameters['profile'] 		 = 0;
	//var_dump($parameters);
	//$show_sql = true;

	//[dv] The PEAR::DB library is so horribly stupid that if you don't have any '?' but you do pass in $sqlvalues,
	//     your result set will be an indexed array instead of a k:v hash -- regardless of forcing DB_FETCHMODE_ASSOC. UGH!
	if (is_null($sqlvalues) || strpos($sql, '?') === false)
		$sqlvalues = array();
	elseif (!empty($sqlvalues) && !is_array($sqlvalues))
		$sqlvalues = array($sqlvalues);
	//var_dump($sqlvalues);

	global $SQL_OPTION;

	//[dv] the preg_replace will magically strip out the spaces, newlines, tabs and other funky chars to make one nice string.
	$sql = preg_replace("/\s+/",' ', (preg_replace("/\s/",' ',trim($sql))) );

	if ($SQL_OPTION['debug'] || $show_sql)
	{
		if ($parameters['no_html'] || $SQL_OPTION['noHTML'] || $SQL_OPTION['outfile']) $sql = str_replace( array("\n", "\r", '  '), ' ', $sql);

		$out_sql = '['.$database.'] '.sql_substitute_values($sql, $sqlvalues);
		if ($SQL_OPTION['outfile'])
			file_put_contents($SQL_OPTION['outfile'], 'SQL: '.$out_sql."\n", (FILE_APPEND | LOCK_EX) );
		elseif ($parameters['no_html'] || $SQL_OPTION['noHTML'])
			echo "SQL: ".$out_sql."\n";
		else
		{
			$out_sql = sql_print($out_sql);
			echo $out_sql;
		}
	}

	if ($parameters['execute'] === true)
	{
		// execute query only if it appears to be safe.
		if ( ($error_str = sql_is_safe_query($sql)) === TRUE )
		{
			if (!sql_connect($database,false)) return false; //this loads $GLOBALS['DB_CONNECTION'][$database]

			//start profile stuff for this query
			if ($SQL_OPTION['profile'] > 0) $parameters['profile'] = $SQL_OPTION['profile'];
			if ($parameters['profile'] > 0)
			{
				if (!$handle = fopen("/tmp/SQL_profile.txt", 'a'))
				{
					echo "unable to open file /tmp/SQL_profile.txt\n";
					$parameters['profile'] = 0;
				}
				$text = date("[m/d/y h:i:s ");
				if ($parameters['profile'] >= 2) $sql_start = microtime(true); //start timer
			}

			//[dv] PEAR::DB is so LAME! that we have to FORCE the mode here,
			//despite the fact that it should allow it to be passed as a parameter. What Garbage.
			//http://pear.php.net/manual/en/package.database.db.db-common.setfetchmode.php
			$GLOBALS['DB_CONNECTION'][$database]->setFetchMode($parameters['db_fetch_mode']);

			//determine if we need to do a 'query' or a 'getAll'
			//so first grab the very first word of the $sql (stripping out newlines)
			preg_match('/^\(?(\w+) /', str_replace( array("\n", "\r", '  '), ' ', $sql), $matches);
			$first_word = strtolower($matches[1]);
			//echo "first_word = $first_word<br/>\n";
			if (in_array($first_word, array('select','show','explain')))
			{
				$result =& $GLOBALS['DB_CONNECTION'][$database]->getAll($sql, $sqlvalues, $parameters['db_fetch_mode']);
			}
			elseif ( in_array($first_word, array('insert','update','create', 'drop', 'delete','set','/*!40014','start')) or in_array(strtolower($sql), array('commit','rollback')))
			{
				//TODO: eventually this should do something like this:
				//if ('agis_core' == $database)
				//	$result =& $GLOBALS['DB_CONNECTION']['agis_core_master']->query($sql,$sqlvalues);
				//else
				$result =& $GLOBALS['DB_CONNECTION'][$database]->query($sql, $sqlvalues);
			}
			else
			{
				notification_table('error',"db.inc.php::sql_query() does not know how to handle '<b>".$first_word."</b>'<br/>\n".sql_print($sql));
				backtrace(true);
				exit; //we purposely exit here because any SQL after this point might give unexpected results.
			}

			if (DB::isError($result))
			{
				//var_dump($result);
				preg_match("/\[nativecode=(\d+) \*\* (.*)\]/", $result->getUserInfo(), $errormatch);
				$error = ' Error '.$errormatch[1]."<br/>\n".$errormatch[2]."<br/>\n";
				$result = FALSE;
			}

			//end of profiling stuff for this query
			if ($parameters['profile'] > 0)
			{
				if ($parameters['profile'] >= 2) $text .= number_format( (microtime(true) - $sql_start), 4 ).'s'; //end timer

				//we do this here so as not to upset the timer too much
				if ($parameters['profile'] >= 3)
				{
					$text .= ' '.$_SERVER['SCRIPT_FILENAME'];
					$traceArray = debug_backtrace();
					$text .= ' '.$traceArray[1]['file'].' ('.$traceArray[1]['line'].')';
					$text = str_replace('/public_html/', '', $text);
				}

				$sql = str_replace("\n", ' ', $sql);
				$sql = preg_replace('/\s+/',' ', $sql);
				if (!fwrite($handle, $text.'] '.$sql."\n"))
				{
					echo "unable to write to file /tmp/SQL_profile.txt\n";
				}

				@fclose($handle);
			}
		}
		else
		{
			$error = "Malformed query (".$error_str."). Execution blocked.";
			$result = FALSE; // indicate that we failed
		}

		if ($result === false)
		{
			$GLOBALS['SQL_ERROR_STRING'] = '';

			// if error has not been set, then we have a 'regular' mysql error. Otherwise it is a potentially malicious query.
			/*
			if(!isset($error))
			{
				$error = mysql_error($GLOBALS['DB_CONNECTION']);
                $errno = mysql_errno($GLOBALS['DB_CONNECTION']);
			}
			else $errno = 0; // not 'regular' mysql error? well, we need some error code anyway.
			*/

			// trim to size if necessary
			if(!$SQL_OPTION['fullQuery']) $error = substr($error,0,100)."...";

			if ($parameters['show_errors'])
			{
				if ($parameters['no_html'] || $SQL_OPTION['noHTML']) $sql = preg_replace("/\s+/",' ', (preg_replace("/\s/",' ',$sql)) );

				if (!$out_sql) $out_sql = sql_print($sql, $sqlvalues, $database);
				$GLOBALS['SQL_ERROR_STRING'] .= "<B><U>SQL ERROR</U> ::</B>".$error."<br/><font SIZE='-2'>".$out_sql."</font>".backtrace(false);

				//TODO: [dv] is there a way to determine if we're in a CGI vs. Web page?
				if ($parameters['no_html'] || $SQL_OPTION['noHTML'])
				{
					//$GLOBALS['SQL_ERROR_STRING'] = preg_replace("/\s+/",' ', (preg_replace("/\s/",' ',$GLOBALS['SQL_ERROR_STRING'])) );
					echo strip_tags($GLOBALS['SQL_ERROR_STRING'])."\n";
				}
				else
					notification_table('error', $GLOBALS['SQL_ERROR_STRING']);
					//echo "<PRE STYLE='text-align: left; border: thin solid Red; padding: 5px;'><font CLASS='error'>".$GLOBALS['SQL_ERROR_STRING']."</font></PRE><BR>\n";

				if ($SQL_OPTION['outfile'])
				{
					//echo "Dumping error to outfile: ".$SQL_OPTION['outfile']."\n";
					file_put_contents($SQL_OPTION['outfile'], strip_tags($GLOBALS['SQL_ERROR_STRING']."\n"), (FILE_APPEND | LOCK_EX) );
				}

				//if ($SQL_OPTION['useLogger']) logger(strip_tags($GLOBALS['SQL_ERROR_STRING']));
			} //if ($parameters['show_errors'])
		} //if (!$result)

		//TODO: [dv] this should work, we just have to go audit the code for all the LIMIT 1 areas as they use [0] probably still...
		//now check if there was a LIMIT 1
		//if ($result && $first_word == 'select' && is_array($result) && preg_match('/( limit 1$)/i', $sql)) $result =& $result[0]; //return the first row as a convenience

		return $result;
	}
	else
	{
		if ($show_sql)
		{
			global $SQL_INT;
			echo "<font color='#0000FF' style='background-color:#ffffff;'><B>DEBUG SQL[".($SQL_INT-1)."]:</font> <font color='#FF0000'>Not Executed</B></font><br/>\n";
		}
	}

	return true;
}

/**
* Output the HTML debugging string in color coded glory for a sql query
* This is very nice for being able to see many SQL queries
*
* @access 	public
* @return 	string HTML color coded string of the input $query.
* @param 	string $query The SQL query to be executed.
* @param 	string $database (null) an optional string to print as the database
* @see		sql_substitute_values()
* @author 	Daevid Vincent [daevid.vincent@xxxxxxxxxxxxxx]
* @date    	05/27/09
* @todo	 	highlight SQL functions.
*/
function sql_print($query, $sqlvalues=array(), $database=null)
{
	if (!$query) return false;
	$query = sql_substitute_values($query, $sqlvalues);

	global $SQL_INT;
	if( !isset($SQL_INT) ) $SQL_INT = 0;

	//[dv] I like my version better...
	//require_once 'classes/geshi/geshi.php';
	//$geshi =& new GeSHi($query, 'sql');
	//echo $geshi->parse_code();
	//return;

	$query = str_replace( array("\n", "\r", '  '), ' ', $query);

	//[dv] this has to come first or you will have goofy results later.

	//[dv] work in progress. was trying to show comments in gray...
	//		I think I need to use a sprintf() or maybe preg_replace_callback() to sequence the COMMENT_BLOCK_Xs
	/*
	$comment_pattern = "/(\/\*.*?\*\/)/i";
	preg_match_all($comment_pattern, $query, $comments);
	unset($comments[0]);
	//print_r($comments);
	$query = preg_replace($comment_pattern, "COMMENT_BLOCK", $query, -1);
	*/

	//[dv] TODO: UGH this number one is causing me lots of grief... why can't i figure out the regex to use?
	//highlight numbers
	//$query = preg_replace("/[\s=](\d+)\s/", "<font color='#FF6600'>$1</font>", $query, -1);

	//underline tables/databases but nothing in single quote marks as those are strings
	//FIXME: [dv] this will match something like "MYFUNCTION(table_name.column)" and print it later as "MYFUNCTION table_name.column)"
	//			   Note how the first ( is missing after the MYFUNCTION name
	$query = preg_replace("/[^']`?\b(\w+)\.`?/", " <u>$1</u>.", $query, -1);

	//highlight strings between quote marks
	$query = preg_replace("/['\"]([^'\"]*)['\"]/i", "'<font color='#FF6600'>$1</font>'", $query, -1);

	//highlight functions
	$query = preg_replace("/(\w+)\s?\(/", "<font color='#CC00FF'>".strtoupper('\\1')."</font>(", $query, -1);

	$query = str_ireplace(
							array (
									'*',
									'SELECT ',
									' GROUP BY ',
									'UPDATE ',
									'DELETE ',
									'INSERT ',
									'INTO ',
									'VALUES ',
									'FROM ',
									'LEFT ',
									'JOIN ',
									'WHERE ',
									'LIMIT ',
									'ORDER BY ',
									' AND ',
									' OR ',  //[dv] note the space. otherwise you match to 'colOR' ;-)
									' DESC',
									' ASC',
									' ON ',
									' AS ',
									' NULL'
								  ),
							array (
									"<font color='#FF6600'><b>*</b></font>",
									"<font color='#00AA00'><b>SELECT </b></font>",
									"<font color='#00AA00'><b> GROUP BY </b></font>",
									"<font color='#00AA00'><b>UPDATE </b></font>",
									"<font color='#00AA00'><b>DELETE </b></font>",
									"<font color='#00AA00'><b>INSERT </b></font>",
									"<font color='#00AA00'><b>INTO </b></font>",
									"<font color='#00AA00'><b>VALUES </b></font>",
									"<font color='#00AA00'><b>FROM </b></font>",
									"<font color='#00CC00'><b>LEFT </b></font>",
									"<font color='#00CC00'><b>JOIN </b></font>",
									"<font color='#00AA00'><b>WHERE </b></font>",
									"<font color='#00AA00'><b>LIMIT </b></font>",
									"<font color='#00AA00'><b>ORDER BY</b> </font>",
									"<font color='#0000AA'> <b>AND</b> </font>",
									"<font color='#0000AA'> <b>OR</b> </font>",
									"<font color='#0000AA'> <b>DESC</b></font>",
									"<font color='#0000AA'> <b>ASC</b></font>",
									"<font color='#00DD00'> <b>ON</b> </font>",
									"<font color='#0000AA'> <b>AS</b> </font>",
									"<font color='#FF00FF'> <b>NULL</b></font>"
								 ),
							$query
						  );

	//[dv] work in progress. was trying to show comments in gray...
	/*
	if ($comments[1])
	{
		foreach($comments[1] as $c)
		{
			$cb[] = 'COMMENT_BLOCK';
			$crepl[] = "<font color='#dddddd'>".$c."</font>";
		}
		//sadly this doesn't do a 1:1 match in each array. it matches COMMENT_BLOCK then quits after $crepl[0]
		$query = str_replace($cb, $crepl, $query);
	}
	*/

	$query = "<font color='#0000FF' style='background-color:#ffffff;'><B>DEBUG SQL[".$SQL_INT++."]:</B> ".$query."<font color='#FF0000'>;</font></font><br/>\n";
	if ($database) $query = '['.$database.'] '.$query;
	return $query;
}

/**
* Substitutes the sqlvalues into the query for debugging purposes
*
* @access 	public
* @return 	string
* @param 	string $query The SQL query
* @param 	array $sqlvalues the sqlvalues to be substituted into the $query
* @see		sql_print()
* @author 	Daevid Vincent [daevid.vincent@xxxxxxxxxxxxxx]
* @date    	05/27/09
* @todo		put the DB_common::quoteSmart() around the sqlvalues
*/
function sql_substitute_values($query, $sqlvalues=array())
{
	if (!$sqlvalues || count($sqlvalues) < 1) return $query;

	$query = str_replace('?', "'%s'", $query);
	//TODO: wedge in http://pear.php.net/manual/en/package.database.db.db-common.quotesmart.php here for each parameter
	//so the debug is more in line with what the real $query should be.
	return vsprintf($query, $sqlvalues);
}

/**
 * @return	int	Number of rows in the result set
 * @access public
 * @param	object $result result set
 * @author Daevid Vincent [daevid.vincent@xxxxxxxxxxxxxx]
 */
function sql_num_rows($result)
{
	if ($result)
		return $result->numRows();
	else
		return false;
}

/**
* A wrapper around the sql_query function to return an array of key/value pairs.
*
* This is very useful for those tables that are simply a key/value and you'd like it in an array
* then you can just reference the array and save yourself a JOIN perhaps.
*
* If only one column is given, then the values are a sequential array.
* If two columns are returned, then they are mapped as $col[0] => $col[1]
*
* @access  public
* @return 	array of key/value pairs.
* @param 	string $sql The SQL SELECT query to be executed in an order such as "SELECT id, name FROM foo ORDER BY name"
* @param 	boolean $show_sql output the $sql to the display (for debugging purposes usually). false by default.
* @param 	array $parameters (see sql_query() for available key/value pairs)
* @see		sql_query()
* @author 	Daevid Vincent [daevid.vincent@xxxxxxxxxxxxxx]
* @date    	05/28/09
*/
function sql_query_array_pair($database='agis_core', $sql, $sqlvalues=array(), $show_sql=false, $parameters=array())
{
	$parameters['db_fetch_mode'] = DB_FETCHMODE_ORDERED; //this has to be an ordered array as we don't know the hash keys in an associative one

	if ($result = sql_query($database, $sql, array(), $show_sql, $parameters))
	{
		foreach($result as $key => $value)
		{
			if (!$value[1])
				$tmpArray[] = $value[0];
			else
				$tmpArray[ $value[0] ] = $value[1];
		}
		return $tmpArray;
	}
	return false;
}

/**
 * This will return the last inserted ID -- from a mySQL database only
 *
 * @return	int	Insert ID of last insert action
 * @access public
 * @author Daevid Vincent [daevid.vincent@xxxxxxxxxxxxxx]
 * @see		sql_insert()
 */
function sql_insert_id()
{
	//not sure the PEAR equivallent for this one?
	//http://pear.php.net/manual/en/package.database.db.php
	//http://us3.php.net/manual/en/function.mysql-insert-id.php
	//could also use a raw query: 'SELECT LAST_INSERT_ID()'
	return @mysql_insert_id();
}


/**
 * @return	int	Number of affected rows
 * @param 	string the database to connect to (agis_core_master) is the default
 * @access public
 * @author Daevid Vincent [daevid.vincent@xxxxxxxxxxxxxx]
 */
function sql_affected_rows($database='agis_core_master')
{
	//http://pear.php.net/manual/en/package.database.db.db-common.affectedrows.php
	return $GLOBALS['DB_CONNECTION'][$database]->affectedRows();
}

/**
 * Free up a mysql pointer
 *
 * @access 	public
 * @param		object $result result set
 * @author 	Daevid Vincent [daevid.vincent@xxxxxxxxxxxxxx]
 */
function sql_free($result)
{
	//http://pear.php.net/manual/en/package.database.db.db-result.free.php
	if ($result)
		return $result->free();
	else
		return false;
}

/**
 * perform the smart quoting for SQL queries
 *
 * @param string $s the string to be quoted
 * @param boolean $trim trim leading and trailing space (true)
 * @param string $database a database connection to use ('agis_core')
 * @author Daevid Vincent [daevid.vincent@xxxxxxxxxxxxxx]
 * @return string
 */
function sql_escape($s, $trim=true, $database='agis_core')
{
	if ($trim) $s = trim($s);

	return sql_connect($database)->quoteSmart($s);
}

/**
 * Outputs the error message from a failed SQL query/command/connection.
 *
 * @access public
 * @return	void
 * @param 	object $db_resource the result of a sql_connect() or a sql_query $result
 * 			if a string (such as 'agis_core') is passed in, it is automatically converted to the corresponding singleton object $GLOBALS['DB_CONNECTION'][$db_resource].
 * @author Daevid Vincent [daevid.vincent@xxxxxxxxxxxxxx]
 * @see 	sql_connect(), sql_query()
 * @date	2010-01-12
 */
function sql_db_error($db_resource)
{
	//var_dump($db_resource);

	if (is_string($db_resource)) $db_resource = $GLOBALS['DB_CONNECTION'][$db_resource];

	if (!$db_resource) return "Invalid resource in sql_db_error().<br/>\n";

	$error = '';
	if (PEAR::isError($db_resource))
	{
	    $error .= '<b>Standard Message:</b> '.$db_resource->getMessage()."<br/>\n";
	    $error .= '<b>Standard Code:</b> '.$db_resource->getCode()."<br/>\n";
	    if ($_SESSION['DEVELOPMENT'])
	    {
		    $error .= '<b>DBMS/User Message:</b> '.$db_resource->getUserInfo()."<br/>\n";
		    //$error .= '<b>DBMS/Debug Message:</b> '.$db_resource->getDebugInfo()."<br/>\n";
		    backtrace();
	    }
	}

	return $error;
}

/**
 * Make a connection to a RDBMS and Database. Defaults to agis_core (slave).
 * Note: dev/test/production is determined from the config.inc.php file that resides on each server.
 *
 * @access	public
 * @param 	string $database The RDBMS to connect to (reliability, [agis_core], agis_core_master, wfdmt, arinc_symonty, pcube, fmr, product_safety, fogbugz)
 * @param  boolean $show_error (true) show an error message on screen or not.
 * @return	PEAR DB::connect handle/object which is also set in $GLOBALS['DB_CONNECTION'][$database] or false if unable to connect
 * @author Daevid Vincent [daevid.vincent@xxxxxxxxxxxxxx]
 * @see		sql_query()
 */
function sql_connect($database='agis_core', $show_error=true)
{
	if (!$database) return false;

	$database = strtolower($database);

	// add a singleton snippet to not reconnect if the data connection object already exists (see sql_escape() for usage)
	if (is_object($GLOBALS['DB_CONNECTION'][$database])) return $GLOBALS['DB_CONNECTION'][$database];


    $tries = 5;
    for($i = 1; $i <= $tries; $i++)
	{
        switch ($database)
        {
        	case 'agis_core': 		 $GLOBALS['DB_CONNECTION'][$database] = connect_to_db_agis_core(false, false); break 2;
        	case 'agis_core_master': $GLOBALS['DB_CONNECTION'][$database] = connect_to_db_agis_core_master(false); break 2;
        	case 'reliability': 	 $GLOBALS['DB_CONNECTION'][$database] = connect_to_db_reliability(); break 2;
        	case 'wfdmt': 			 $GLOBALS['DB_CONNECTION'][$database] = connect_to_db_wfdmt(); break 2;
        	case 'arinc_symonty':  	 $GLOBALS['DB_CONNECTION'][$database] = connect_to_db_arinc_symonty(); break 2;
        	case 'pcube': 			 $GLOBALS['DB_CONNECTION'][$database] = connect_to_db_pcube(); break 2;
        	case 'fmr': 			 $GLOBALS['DB_CONNECTION'][$database] = connect_to_db_fmr(); break 2;
        	case 'product_safety':   $GLOBALS['DB_CONNECTION'][$database] = connect_to_db_product_safety(); break 2;
        	case 'synergy_master':	 $GLOBALS['DB_CONNECTION'][$database] = connect_to_db_synergy_master(); break 2;
        	case 'synergy_reference':$GLOBALS['DB_CONNECTION'][$database] = connect_to_db_synergy_reference(); break 2;
        	case 'fogbugz': 		 $GLOBALS['DB_CONNECTION'][$database] = connect_to_db_fogbugz(); break 2;
        	case 'pana_session_pw':	 $GLOBALS['DB_CONNECTION'][$database] = connect_to_db_pana_session_pw(); break 2;
        	case 'pana_session_db':	 $GLOBALS['DB_CONNECTION'][$database] = connect_to_db_pana_session_db(); break 2;

        	default:				 if ($_SESSION['DEVELOPMENT'])
        							 {
	        							 notification_table('error', "No such database named '<b>".substr($database, 0, 30)."...</b>' Perhaps you have ommited the first sql_query() parameter?<br/>\n");
	        							 backtrace(true);
        							 }
        							 else
        							 	notification_table('error', "No such database. This looks like a SQL query instead.<br/>\n");

	        						return false;
        							break 2;
        }

		$error .= "Unable to connect to ".$database." RDBMS. Retrying [$i/$tries] in 5 seconds.<br/>\n";
        sleep(5);
    }

	if ( !is_object($GLOBALS['DB_CONNECTION'][$database]) )
	{
		$error .= 'Could not connect to '.$database." server. Aborting.<br/>\n";
		if ($show_error) notification_table('error', $error);
		return false;
	}

	if ( PEAR::isError($GLOBALS['DB_CONNECTION'][$database]) )
	{
		//TODO: we should log this connection failure (and the others like this) to syslog.
		if ($show_error) notification_table('error', sql_db_error($GLOBALS['DB_CONNECTION'][$database]));
		return false;
	}

	return $GLOBALS['DB_CONNECTION'][$database];
}

/**
 * We are (currently) trying to prevent just one trivial type of sql injection.
 * Namely, the one that attempts to end query with a ; and then add an extra query
 * to the end. This is a common technique, and the one that is easiest to detect.
 *
 * First, we watch for unbalanced quotes. If any are found, the query is invalid anyway
 * and thus will not be allowed to run.
 *
 * Second, I can't think of a single valid use of a semicolon outside the literals
 * enclosed into ''. Semicolons will be alloedd in those literals, but not outside.
 *
 * Single quotes that are in the literals and have been SQL_ESCAPE()'d are treated properly,
 * that is as a single character within the literal. So are the backslashed-escaped chars.
 *
 * Any other additions are welcome, but this is at least a good start.
 */
function sql_is_safe_query($q)
{
	$len = strlen($q);
	$inside = false; // inside a literal (enclosed by '')
	$ret = true; // query assumed good unless we can prove otherwise.
	for ($i = 0; $i < $len; $i++)
	{
		$more = ($i < ($len - 1)); // we have at least one more character
		switch($q[$i])
		{
		 case "\\":
		 	//[krogebry]	Why would there be a test for '$inside' here?
			//				anything after a \ should be an escaped char, that's what \ does.
			#if($inside && $more)
			#{
				$i++; // whatever follows MUST be an escaped character.
				#continue;
			#}
			break;

		 case "'":
			// we are inside the string and came up with a properly-escaped quote
			#if($inside && $more && ($q[$i+1] == "'")){
			if($inside && $more && $q[$i-1] == "\\" ){
				$i++;
				continue;
			}
			$inside = !$inside;
			break;

		 case ";":
			// semicolons outside literals are not permitted.
			if(!$inside){
				$ret = "Semicolon is used to chain queries. Please, do not do that.";
				break 2;
			}

		}// switch()
	}

	if ($inside) $ret = "Unbalanced single quotes";

	#print "Ret: [$ret]<br/>\n";
	return $ret;
}

/**
* Dynamically generates a select box from a SQL query.
*
* The SELECT must return between two and three items.
* first is the VALUE the second is the text to display and optional third is shown in parenthesis
* <SELECT><OPTTION VALUE=''></SELECT> form element prefilled in
*
* Tooltips do NOT work in IE. sorry. blame Microsoft for not following W3 standards...
*
* @access 	public
* @return 	int the number of rows in the SELECT box or false.
* @param 	int $size usually 1, but the select box can be any height.
* @param 	string $name the NAME='$name' parameter of a SELECT tag.
* @param 	string $database the database to connect to (agis_core) is the default
* @param 	string $sql The SQL query to be executed, this can be SELECT, INSERT, UPDATE or DELETE amongst others.
* @param 	array $sqlvalues The sqlvalues to the $sql. One element per ? is required if this parameter is used.
* @param 	mixed $blank (boolean) add the extra 'empty' <OPTION VALUE=''>. string will set the text of the empty option.
* @param 	boolean $auto onChange will cause a form submit if true.
* @param 	string $MatchToThis sometimes it is useful to match $name to something like $_GET['name'] instead. it is array safe too!
* @param 	string $extratags Any extra CLASS='' or MULTIPLE or whatever to put in the <SELECT ...> tag.
* @see		select_box_array()
* @author 	Daevid Vincent [daevid.vincent@xxxxxxxxxxxxxx]
* @date    	2010-01-20
*/
function select_box_sql($size, $name, $database='agis_core', $sql, $sqlvalues=array(), $blank = false, $auto = false, $MatchToThis = false, $extratags = false)
{
	$parameters['db_fetch_mode'] = DB_FETCHMODE_ORDERED; //this has to be an ordered array as we don't know the hash keys in an associative one
	$rslt = sql_query($database, $sql, $sqlvalues, false, $parameters);
	if (is_array($rslt))
	{
		global $$name;
		if (intval($size) < 1) $size = 1;
		if ($MatchToThis === false) $MatchToThis = $$name;

		echo "\n<select size='".$size."' name=\"".$name."\" id=\"".$name."\"";
		if ($auto) echo " onChange=\"this.form.submit(); return true;\"";
		if ($size > 1) echo " onmouseover='topSelectedOption(this);'";
		if ($extratags) echo " ".$extratags;
		echo ">\n";

		if (count($rslt) > 0)
		{
			if ($blank && is_bool($blank) ) { echo "\t<option value=''></option>\n"; }
			elseif ($blank && is_string($blank)) { echo "\t<option value=''>".$blank."</option>\n"; }

			$items = 0;
			foreach ($rslt as $key => $value)
			{
				@list($key, $text, $description) = $value;

				$items++;
				// Check for selectbox sub-headings.
				if ( 0 == strncmp( $text, "---", 3 ) )
				{
					echo "\t<option value='' disabled class='selectbox-ghosted'>".stripslashes($text)."</option>\n";
				}
				else
				{
					echo "\t<option value=\"".$key."\"";
					if (!selected_if_in_array($MatchToThis, $key))
						if ((string)$key == (string)$MatchToThis) echo " selected";
					echo ">";
					echo stripslashes($text);
					if ($description) echo " (".stripslashes($description).")";
					echo "</option>\n";
				}
			}
		}

		echo "</select>\n";

		return $items;
	}
	else echo "Selectbox cannot be built because of an invalid SQL query.\n";

	return false;
}

/**
* returns a string that can be appended to an SQL statement to form the ORDER BY portion.
*
* if you want to sort by 'name' in descending order, then simply use 'name_DESC',
* conversely, 'name_ASC' would sort in ascending order. The order of the elements in the array
* will determine the order they are appended together.
*
* @access 	public
* @return 	string of the form ' ORDER BY element[1], element[2], element[3]'...
* @param 	$orderBy false, string, or array of elements like so: [sort_by] => Array ( [1] => name_DESC [2] => id [3] => price )
* @param 	$default a string to use as the default ORDER BY column
* @author  Daevid Vincent [daevid.vincent@xxxxxxxxxxxxxx]
* @date    	03/03/06
*/
function parse_order_by_array($orderBy = false, $default = false)
{
	$sql = ' ORDER BY ';

	if (!is_array($orderBy))
	{
		//[dv] is_string() is not enough, as empty values are coming across as strings according to var_dump()
		if (strlen($orderBy) > 1)
			return $sql.$orderBy;
		elseif (is_string($default))
			return $sql.$default;
		else
			return false;
	}

	foreach ($orderBy as $o)
		$tmp[] = str_replace('_', ' ', $o);

	return $sql.implode(', ',$tmp);
}

/**
* Builds the WHERE portion of a SQL statement using the keywords in various columns with wildcard support.
*
* @return 	string SQL statement fragment
* @param	mixed $words either a string of words space deliminated or an array of words
* @param	array $columns an array of table.column names to search the $words in. Use % as a wildcard for example pass in 'username%' or '%username%'.
* @param	boolean $and (true) whether the words have to be ANDed or ORed together.
* @author 	Daevid Vincent [daevid.vincent@xxxxxxxxxxxxxx]
* @date    	05/10/07
* @todo		This should handle +, - and "" just like google or yahoo or other search engines do.
*/
function keyword_filter($words, $columns, $and = true)
{
	// this maybe useful
	// http://wiki.ittoolbox.com/index.php/Code:Translate_Boolean_Query_to_SQL_select_statement
	// http://www.ibiblio.org/adriane/queries/
	// http://www.zend.com/zend/tut/tutorial-ferrara1.php?article=tutorial-ferrara1&kind=t&id=8238&open=1&anc=0&view=1

	// http://evolt.org/article/Boolean_Fulltext_Searching_with_PHP_and_MySQL/18/15665/index.html
	// http://www.databasejournal.com/features/mysql/article.php/3512461

	// this would be great, but the dumb-asses don't work with InnoDB tables. GRRR!
	// http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html
	//$sql .= " AND MATCH (".implode(',',$columns).") AGAINST ('".implode(' ',$words)."' IN BOOLEAN MODE)";

	if (!is_array($columns) or !$words) return;

	if (is_string($words))
		$words = preg_split("/\s+/",$words, -1, PREG_SPLIT_NO_EMPTY);

	if(count($words) < 1) return '';

	if ($and) //AND the words together
	{
		$sql = " AND ";
		$sqlArray = array();
		foreach($words as $word)
		{
			$tmp = array();
			foreach($columns as $field)
			{
				$col = str_replace('%','',$field);
				//[dv] read the http://php.net/preg_replace carefully. You must use this format,
				//	   because otherwise $words that are digits will cause undesired results.
				$myword = preg_replace("/(%)?([\w\.]+)(%)?/", "\${1}".$word."\${3}", $field );
				$tmp[] = $col." LIKE '".SQL_ESCAPE($myword)."'";
			}
			$sqlArray[] = " (".implode(" OR ",$tmp).") ";
		}
		$sql .= implode(" AND ", $sqlArray);
	}
	else //OR the words together
	{
		$sql = " AND ( ";
		$sqlArray = array();
		foreach($columns as $field)
		{
			$col = str_replace('%','',$field);

			$tmp = array();
			foreach($words as $word)
			{
				//[dv] read the http://php.net/preg_replace carefully. You must use this format,
				//	   because otherwise $words that are digits will cause undesired results.
				$myword = preg_replace("/(%)?([\w\.]+)(%)?/", "\${1}".$word."\${3}", $field );
				$tmp[] = $col." LIKE '".SQL_ESCAPE($myword)."'";
			}
			$sqlArray[] = "(".implode(" OR ",$tmp).") ";
		}
		$sql .= implode(" OR ", $sqlArray);
		$sql .= ") ";
	}

	return $sql;
}

/**
* returns an array of ENUM values from a table/column.
*
* @access 	public
* @return 	array of enum values
* @param 	string $database the database to connect to (agis_core) is the default
* @param 	string $table the name of the table to query
* @param 	string $column the name of the enum column to query
* @param	boolean $sorted by default the results are sorted otherwise they are in the order of the enum schema
* @param	boolean $indexed by default the key/value are the same string. if true, then key is an integer.
* @author  Daevid Vincent [daevid.vincent@xxxxxxxxxxxxxx]
* @date    	05/27/09
* @see 		select_box_array()
*/
function sql_enum_values_array($database='agis_core', $table, $column, $sorted = true, $indexed = false)
{
	$parameters['db_fetch_mode'] = DB_FETCHMODE_ORDERED; //this has to be an ordered array as we don't know the hash keys in an associative one
	if ( $dbQuery = sql_query($database, "SHOW COLUMNS FROM ".$table." LIKE '".$column."'", null, false, $parameters) )
	{
		$EnumArray = array();
		$EnumValues = $dbQuery[0][1];
		$EnumValues = substr($EnumValues, 6, strlen($EnumValues)-8);
		$EnumValues = str_replace("','",",",$EnumValues);

		if ($indexed)
		{
			$EnumArray = explode(",",$EnumValues);
			if ($sorted) sort($EnumArray);
		}
		else
		{
			$tmp = explode(",",$EnumValues);
			foreach($tmp as $k => $v) $EnumArray[$v] = $v;
			if ($sorted) ksort($EnumArray);
		}

		return $EnumArray;
	}
	return false;
}

/**
* Insert a single row into a $database.$table from an array hash of column => value pairs
*
* Usually these are the form field names corresponding to the equivallent SQL database column/field name.
* Use the string 'null' to insert a true NULL.
*
* @access 	public
* @return 	mixed inserted ID on success or result on failure
* @param 	string $database the database to connect to (agis_core) is the default
* @param 	string $table the name of the table to insert into
* @param 	hash $rows hash of column => value pairs (optionally columns validated against $validate_columns)
* @param 	array $valid_columns array of column/field names. Also useful to limit SQL to certain forced columns to prevent unwanted tampering with 'default' columns for example.
* @author  Daevid Vincent [daevid.vincent@xxxxxxxxxxxxxx]
* @date    	2010-01-20
* @see 		sql_update(), sql_insert_id()
*/
function sql_insert($database='agis_core_master', $table, $rows, $valid_columns=null)
{
	ksort($rows); //not required, just easier to debug and find appropriate keys.

	$validate_columns = (is_array($valid_columns)) ? true : false;

	$temp = array();
	$arrays = array();
	foreach ($rows as $column => $val)
	{
		if (is_array($val))
		{
			//if we only have one element in the array, then count it as any other $val
			if (count($val) == 1)
				$val = $val[0];
			else
			{
				$arrays[$column] = $val;
				unset($rows[$column]);
				continue;
			}
		}

		if ($validate_columns && !in_array($column, $valid_columns))
		{
			unset($rows[$column]);
			echo "\n<br/>sql_insert() <b>".$column."</b> is not in the valid_columns list</b>";
			continue;
		}

		$val = trim($val);
		if (!$val)
		{
			unset($rows[$column]);
			continue;
		}

		if (strtolower($val) == 'null')
			$temp[$column] = 'NULL';
		else
			$temp[$column] = "'".mysql_escape_string($val)."'";
	}

	$values = implode(', ',$temp);
	$columns = "`".implode("`, `", array_keys($rows))."`";
	$sql = "INSERT INTO `".$table."` (".$columns.") VALUES (".$values.")";
	//echo $sql;

	if (count($arrays))
	{
		echo "\n<br/>sql_insert() has arrays with multiple elements that need to be handled still: <b>".implode(', ', array_keys($arrays))."</b>";
		foreach ($arrays as $a) var_dump($a);
	}

	$result = sql_query($database, $sql, null, false);
	if ($result)
    {
    	$iid = sql_insert_id();
    	if (is_numeric($iid)) return $iid;
    }

    return $result;
}


/**
* Update rows in $database.$table from an array hash of column => value pairs
*
* Usually these are the form field names corresponding to the equivallent SQL database column/field name.
* Use the string 'null' to insert a true NULL.
*
* @access 	public
* @return 	mixed 	affected rows on success or result on failure
* @param 	string 	$database the database to connect to (agis_core) is the default
* @param 	string 	$table the name of the table to insert into
* @param 	hash 	$rows hash of column => value pairs (optionally columns validated against $validate_columns)
* @param 	mixed	hash of ID column/field name and record ID value [such as array('id_foo' => 69)] OR string to craft custom WHERE clause
* @param 	array 	$valid_columns array of column/field names. Also useful to limit SQL to certain forced columns to prevent unwanted tampering with 'default' columns for example.
* @author  Daevid Vincent [daevid.vincent@xxxxxxxxxxxxxx]
* @date    	2010-01-20
* @see 		sql_insert()
*/
function sql_update($database='agis_core_master', $table, $rows, $where, $single=true, $valid_columns=null)
{
	ksort($rows); //not required, just easier to debug and find appropriate keys.

	$validate_columns = (is_array($valid_columns)) ? true : false;

	$temp = array();
	$arrays = array();
	foreach ($rows as $column => $val)
	{
		if (is_array($val))
		{
			//if we only have one element in the array, then count it as any other $val
			if (count($val) == 1)
				$val = $val[0];
			else
			{
				$arrays[$column] = $val;
				unset($rows[$column]);
				continue;
			}
		}

		if ($validate_columns && !in_array($column, $valid_columns))
		{
			unset($rows[$column]);
			echo "\n<br/>sql_update() <b>".$column."</b> is not in the valid_columns list</b>";
			continue;
		}

		$val = trim($val);
		if (!isset($val))
		{
			unset($rows[$column]);
			continue;
		}

		if (strtolower($val) == 'null')
			$temp[$column] = '`'.$column."` = NULL";
		else
			$temp[$column] = '`'.$column."` = '".mysql_escape_string($val)."'";
	}

	$sql = "UPDATE `".$table."` SET ".implode(', ', $temp);

	if (is_array($where))
	{
		foreach ($where as $c => $v)
		$w[] = '`'.$c."` = '".mysql_escape_string($v)."'";
		$sql .= " WHERE ".implode(' AND ', $w);
	}
	else $sql .= ' '.$where;

	if ($single) $sql .= ' LIMIT 1';

	if (count($arrays))
	{
		echo "\n<br/>sql_update() has arrays with multiple elements that need to be handled still: <b>".implode(', ', array_keys($arrays))."</b>";
		foreach ($arrays as $a) var_dump($a);
	}

	$result = sql_query($database, $sql, null, false);
    if ($result)
    {
    	$ar = sql_affected_rows($database);
    	if (is_numeric($ar)) return $ar;
    }

    return $result;
}

/**
* Given a single dimension array, it will sql_escape and quote all the values,
* returning as a string suitable for a SQL IN() call.
*
* @access 	public
* @return 	string 	string of the form "'foo,'bar','bee','boo'"
* @param 	array 	$inarray a single dimension array (not hash) of values to quote/escape for an IN() function
* @author  Daevid Vincent [daevid.vincent@xxxxxxxxxxxxxx]
* @date    	2009-01-19
*/
function sql_make_IN_from_array($inarray)
{
	if (!is_array($inarray)) return '-1';

	foreach ($inarray as $value)
		$tmp[] = sql_escape($value);

	return implode(', ',$tmp);
}

function connect_to_db_reliability() {
// RELIABILITY DATA BASE
//		'persistent'  => TRUE,## caused connection problems

	if ($GLOBALS['DB_CONNECTIONS']['reliability']) return $GLOBALS['DB_CONNECTIONS']['reliability'];

	global $global_db_dsn_reliability;

	$options = array(
		'debug'	      => 0,
		'persistent'  => FALSE,
		'portability' => DB_PORTABILITY_ALL
	);

	$db_connection =& DB::connect($global_db_dsn_reliability, $options);
	if ( PEAR::isError( $db_connection ) )
		die( sql_db_error($db_connection) );

	$GLOBALS['DB_CONNECTIONS']['reliability'] = $db_connection;
	$GLOBALS['DB_CONNECTIONS']['reliability']->setFetchMode(DB_FETCHMODE_ASSOC);

	return $GLOBALS['DB_CONNECTIONS']['reliability'];
}

/**
 * Connect to the agis_core database
 *
 * @param boolean $use_master use the master rather than the slave (false)
 * @param boolean $die if there is a PEAR error, PHP dies on the spot. (true)
 * @return PEAR::DB object
 */
function connect_to_db_agis_core($use_master=FALSE, $die=true) {

	if ($use_master == TRUE) return connect_to_db_agis_core_master();

	if ($GLOBALS['DB_CONNECTIONS']['agis_core_slave']) return $GLOBALS['DB_CONNECTIONS']['agis_core_slave'];

	global $global_db_dsn_agis_core_slave;

	$options = array(
		'debug'	      => 0,
		'persistent'  => TRUE,
		'portability' => DB_PORTABILITY_ALL
	);

	$db_connection =& DB::connect($global_db_dsn_agis_core_slave, $options);
	if ( PEAR::isError( $db_connection ) )
	{
		//TODO: we should log this connection failure (and the others like this) to syslog.
		//if ($die !== false) die( $db_connection->getMessage() );
		if ($die !== false) die( sql_db_error($db_connection) );
	}

	$GLOBALS['DB_CONNECTIONS']['agis_core_slave'] = $db_connection;
	if ($die !== false)
		$GLOBALS['DB_CONNECTIONS']['agis_core_slave']->setFetchMode(DB_FETCHMODE_ASSOC);

	return $GLOBALS['DB_CONNECTIONS']['agis_core_slave'];
}

/**
 * Connect to the agis_core database
 *
 * @param boolean $die if there is a PEAR error, PHP dies on the spot. (true)
 * @return PEAR::DB object
 */
function connect_to_db_agis_core_master($die=true) {

	if ($GLOBALS['DB_CONNECTIONS']['agis_core_master']) return $GLOBALS['DB_CONNECTIONS']['agis_core_master'];

	global $global_db_dsn_agis_core_master;

	$options = array(
		'debug'	      => 0,
		'persistent'  => TRUE,
		'portability' => DB_PORTABILITY_ALL
	);

	$db_connection =& DB::connect($global_db_dsn_agis_core_master, $options);
	if ( PEAR::isError( $db_connection ) )
	{
		//TODO: we should log this connection failure (and the others like this) to syslog.
		//if ($die !== false) die( $db_connection->getMessage() );
		if ($die !== false) die( sql_db_error($db_connection) );
	}

	$GLOBALS['DB_CONNECTIONS']['agis_core_master'] = $db_connection;
	if ($die !== false)
		$GLOBALS['DB_CONNECTIONS']['agis_core_master']->setFetchMode(DB_FETCHMODE_ASSOC);

	return $GLOBALS['DB_CONNECTIONS']['agis_core_master'];
}

function connect_to_db_wfdmt() {
// wFDMT data base (tool that creates fdmt.dat, fdmt.xml)
	if ($GLOBALS['DB_CONNECTIONS']['wfdmt']) return $GLOBALS['DB_CONNECTIONS']['wfdmt'];

	global $global_db_dsn_wfdmt;

	$options = array(
		'debug'	      => 0,
		'persistent'  => TRUE,
		'portability' => DB_PORTABILITY_ALL
	);

	$db_connection =& DB::connect($global_db_dsn_wfdmt, $options);
	if ( PEAR::isError( $db_connection ) )
		die( sql_db_error($db_connection) );

	$GLOBALS['DB_CONNECTIONS']['wfdmt'] = $db_connection;
	$GLOBALS['DB_CONNECTIONS']['wfdmt']->setFetchMode(DB_FETCHMODE_ASSOC);

	return $GLOBALS['DB_CONNECTIONS']['wfdmt'];
}

function connect_to_db_arinc_symonty() {
// SYMONTEK DATABASE FOR SMS/EMAIL FROM ARINC
	if ($GLOBALS['DB_CONNECTIONS']['arinc_symonty']) return $GLOBALS['DB_CONNECTIONS']['arinc_symonty'];

	global $global_db_dsn_arinc_symonty;

	$options = array(
		'debug'	   => 0,
		'persistent'  => FALSE,
		'portability' => DB_PORTABILITY_ALL
	);

	$db_connection =& DB::connect($global_db_dsn_arinc_symonty, $options);
	if ( PEAR::isError( $db_connection ) )
		die( sql_db_error($db_connection) );

	$GLOBALS['DB_CONNECTIONS']['arinc_symonty'] = $db_connection;
	$GLOBALS['DB_CONNECTIONS']['arinc_symonty']->setFetchMode(DB_FETCHMODE_ASSOC);

	return $GLOBALS['DB_CONNECTIONS']['arinc_symonty'];
}

function connect_to_db_pcube() {
// PCUBE DATA BASE FOR RELIABILITY (older oracle 8)

	if ($GLOBALS['DB_CONNECTIONS']['pcube']) return $GLOBALS['DB_CONNECTIONS']['pcube'];

	global $global_db_dsn_pcube;

	$options = array(
		'debug'       => 0,
		'persistent'  => FALSE,
		'portability' => DB_PORTABILITY_ALL
		);

	$db_connection =& DB::connect($global_db_dsn_pcube, $options);
	if ( PEAR::isError( $db_connection ) )
		die( sql_db_error($db_connection) );

	$GLOBALS['DB_CONNECTIONS']['pcube'] = $db_connection;
	$GLOBALS['DB_CONNECTIONS']['pcube']->setFetchMode(DB_FETCHMODE_ASSOC);

	return $GLOBALS['DB_CONNECTIONS']['pcube'];
}

function connect_to_db_fmr() {
// FMR DATA BASE - MMS Cabin Log Defects And Resolutions
//'persistent'  => TRUE,## May cause connection problems like reliability?
	if ($GLOBALS['DB_CONNECTIONS']['fmr']) return $GLOBALS['DB_CONNECTIONS']['fmr'];

	global $global_db_dsn_fmr;

	$options = array(
		'debug'	      => 0,
		'persistent'  => FALSE,
		'portability' => DB_PORTABILITY_ALL
	);

	$db_connection =& DB::connect($global_db_dsn_fmr, $options);
	if ( PEAR::isError( $db_connection ) )
		die( sql_db_error($db_connection) );

	$GLOBALS['DB_CONNECTIONS']['fmr'] = $db_connection;
	$GLOBALS['DB_CONNECTIONS']['fmr']->setFetchMode(DB_FETCHMODE_ASSOC);

	return $GLOBALS['DB_CONNECTIONS']['fmr'];
}

function connect_to_db_fogbugz() {
	if ($GLOBALS['DB_CONNECTIONS']['fogbugz']) return $GLOBALS['DB_CONNECTIONS']['fogbugz'];

	global $global_db_dsn_fogbugz;

	$options = array(
		'debug'	      => 0,
		'persistent'  => FALSE,
		'portability' => DB_PORTABILITY_ALL
	);

	$db_connection =& DB::connect($global_db_dsn_fogbugz, $options);
	if ( PEAR::isError( $db_connection ) )
		die( sql_db_error($db_connection) );

	$GLOBALS['DB_CONNECTIONS']['fogbugz'] =& $db_connection;
	$GLOBALS['DB_CONNECTIONS']['fogbugz']->setFetchMode(DB_FETCHMODE_ASSOC);

	return $GLOBALS['DB_CONNECTIONS']['fogbugz'];
}

function connect_to_db_product_safety() {
	if ($GLOBALS['DB_CONNECTIONS']['product_safety']) return $GLOBALS['DB_CONNECTIONS']['product_safety'];

	global $global_db_dsn_product_safety;

	$options = array(
		'debug'	      => 0,
		'persistent'  => FALSE,
		'portability' => DB_PORTABILITY_ALL
	);

	$db_connection =& DB::connect($global_db_dsn_product_safety, $options);
	if ( PEAR::isError( $db_connection ) )
		die( sql_db_error($db_connection) );

	$GLOBALS['DB_CONNECTIONS']['product_safety'] = $db_connection;
	$GLOBALS['DB_CONNECTIONS']['product_safety']->setFetchMode(DB_FETCHMODE_ASSOC);

	return $GLOBALS['DB_CONNECTIONS']['product_safety'];
}

function connect_to_db_synergy_master() {
// myIFE Submit Issue to synergy_master/Telelogic

	if ($GLOBALS['DB_CONNECTIONS']['synergy_master']) return $GLOBALS['DB_CONNECTIONS']['synergy_master'];

	global $global_db_dsn_synergy_master;

	$options = array(
		'debug'       => 0,
		'persistent'  => FALSE,
		'portability' => DB_PORTABILITY_ALL
		);

	$db_connection =& DB::connect($global_db_dsn_synergy_master, $options);
	if ( PEAR::isError( $db_connection ) )
		die( sql_db_error($db_connection) );

	$GLOBALS['DB_CONNECTIONS']['synergy_master'] = $db_connection;
	$GLOBALS['DB_CONNECTIONS']['synergy_master']->setFetchMode(DB_FETCHMODE_ASSOC);

	return $GLOBALS['DB_CONNECTIONS']['synergy_master'];
}

function connect_to_db_synergy_reference() {
// myIFE Submit Issue to synergy_reference/Telelogic

	if ($GLOBALS['DB_CONNECTIONS']['synergy_reference']) return $GLOBALS['DB_CONNECTIONS']['synergy_reference'];

	global $global_db_dsn_synergy_reference;

	$options = array(
		'debug'       => 0,
		'persistent'  => FALSE,
		'portability' => DB_PORTABILITY_ALL
		);

	$db_connection =& DB::connect($global_db_dsn_synergy_reference, $options);
	if ( PEAR::isError( $db_connection ) )
		die( sql_db_error($db_connection) );

	$GLOBALS['DB_CONNECTIONS']['synergy_reference'] = $db_connection;
	$GLOBALS['DB_CONNECTIONS']['synergy_reference']->setFetchMode(DB_FETCHMODE_ASSOC);

	return $GLOBALS['DB_CONNECTIONS']['synergy_reference'];
}

/**
	connect_to_db_pana_session_pw()

	Connects to the password database, which holds
	users (as opposed to the session database,
	which holds sessions).

	This is just a utility function for internal
	use.  You probably don't need to use this functiion.

	Args: None.
	Returns:The PEAR::DB $db_connection.  It will die()
			with an error message if there was a db error.
*/
function connect_to_db_pana_session_pw() {

	if ($GLOBALS['DB_CONNECTIONS']['pana_session_password_db']) return $GLOBALS['DB_CONNECTIONS']['pana_session_password_db'];

	/* Look it up in BlackComb: */

	/* NOTE: Connecting as type "mssql" did not work for me.
		That was on my WinXP workstation.  Switching it to
		ODBC (below) made it work.  I think things will
		be different under Linux, though.
	$pana_session_password_dsn = array( 'phptype' => 'odbc',
		  'dbsyntax' => 'mssql',
		  'database' => 'DRIVER={SQL Server};SERVER=WEBDEV',
		  'username' => 'agis',
		  'password' => 'Please do not forget the AGIS SQL Server Password',
		  'persistent' => TRUE  );
	*/
	global $pana_session_password_dsn;

	$options = array(
		'debug'       => 0,
		'persistent'  => FALSE,
		'portability' => DB_PORTABILITY_ALL,
	);

	$db_connection =& DB::connect($pana_session_password_dsn, $options);
	if ( PEAR::isError( $db_connection ) )
		die( sql_db_error($db_connection) );

	$GLOBALS['DB_CONNECTIONS']['pana_session_password_db'] = $db_connection;
	$GLOBALS['DB_CONNECTIONS']['pana_session_password_db']->setFetchMode(DB_FETCHMODE_ASSOC);

	return $GLOBALS['DB_CONNECTIONS']['pana_session_password_db'];
}

/**
	connect_to_db_pana_session_db()

	Connects to the session database, which holds
	session data (as opposed to the password
	database(s), which holds users).

	This is just a utility function for internal
	use.  You probably don't need to use this function,
	but you may need to edit it to match the settings
	for your particular environment.

	Args: None.
	Returns:The PEAR::DB $db_connection.  It
			will die() with an error message if there was a db error.
*/
function connect_to_db_pana_session_db() {

	if ($GLOBALS['DB_CONNECTIONS']['pana_session_db']) return $GLOBALS['DB_CONNECTIONS']['pana_session_db'];
	/*
	$dsn = array(
		'phptype'  => 'mysql',
		'username' => 'root',
		'password' => 'noway',
		'hostspec' => 'dev-agis03',
		'database' => 'agis_core_0_0_2_3f',
	); */
	global $pana_session_session_dsn;

	$options = array(
		'debug'       => 0,
		'persistent'  => TRUE,
		'portability' => DB_PORTABILITY_ALL,
	);

	$db_connection =& DB::connect($pana_session_session_dsn, $options);
	if ( PEAR::isError( $db_connection ) )
		die( sql_db_error($db_connection) );

	$GLOBALS['DB_CONNECTIONS']['pana_session_db'] = $db_connection;
	$GLOBALS['DB_CONNECTIONS']['pana_session_db']->setFetchMode(DB_FETCHMODE_ASSOC);

	return $GLOBALS['DB_CONNECTIONS']['pana_session_db'];
}
?>

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php