> -----Original Message----- > From: Lars Nielsen [mailto:l...@mit-web.dk] > Sent: Tuesday, February 02, 2010 12:24 PM > To: php-general@lists.php.net > Subject: [PHP] 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.vinc...@panasonic.aero> * @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.vinc...@panasonic.aero] * @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.vinc...@panasonic.aero] * @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.vinc...@panasonic.aero] * @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.vinc...@panasonic.aero] */ 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.vinc...@panasonic.aero] * @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.vinc...@panasonic.aero] * @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.vinc...@panasonic.aero] */ 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.vinc...@panasonic.aero] */ 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.vinc...@panasonic.aero] * @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.vinc...@panasonic.aero] * @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.vinc...@panasonic.aero] * @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.vinc...@panasonic.aero] * @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.vinc...@panasonic.aero] * @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.vinc...@panasonic.aero] * @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.vinc...@panasonic.aero] * @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.vinc...@panasonic.aero] * @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.vinc...@panasonic.aero] * @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.vinc...@panasonic.aero] * @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
