Location: PHPKode > projects > Open Media Collectors Database > functions/database.php
<?php
/* 	
 	Open Media Collectors Database
	Copyright (C) 2001,2006 by Jason Pell

	This program is free software; you can redistribute it and/or
	modify it under the terms of the GNU General Public License
	as published by the Free Software Foundation; either version 2
	of the License, or (at your option) any later version.

	This program is distributed in the hope that it will be useful,
	but WITHOUT ANY WARRANTY; without even the implied warranty of
	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
	GNU General Public License for more details.

	You should have received a copy of the GNU General Public License
	along with this program; if not, write to the Free Software
	Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
*/

$_OPENDB_DB_CONNECTED = NULL;

function init_db_connection()
{
	$dbserver_conf_r = get_opendb_config_var('db_server');
	if(is_array($dbserver_conf_r))
	{
		return db_connect(
				$dbserver_conf_r['host'], 
				$dbserver_conf_r['username'], 
				$dbserver_conf_r['passwd'], 
				$dbserver_conf_r['dbname']);
	}
	
	//else
	return FALSE;
}

function is_db_connected()
{
	global $_OPENDB_DB_CONNECTED;
	
	if(!is_bool($_OPENDB_DB_CONNECTED))
	{
		$_OPENDB_DB_CONNECTED = (init_db_connection()!==FALSE);
	}
	
	return $_OPENDB_DB_CONNECTED; 
}

/**
* @param $sql
*/
function opendb_pre_query($sql)
{
	$dbserver_conf_r = get_opendb_config_var('db_server');
	if(strlen($dbserver_conf_r['table_prefix'])>0)
	{
		$sql = parse_sql_statement($sql, $dbserver_conf_r['table_prefix']);
	}
	
	if($dbserver_conf_r['debug-sql'] === TRUE)
	{
		echo('<p class="debug-sql">SQL: '.$sql.'</p>');
	}
	
	return $sql;
}

/*
* This function is designed to collapse repeated whitespace,
* and \t \r \n, to a single space character.  This is except
* where that text is enclosed in single quotes, as in a SQL
* statement, where all whitespace is maintained.
* 
* this function could definately have been simplified to a regular
* expression - but it was felt necessary to try and avoid a regular
* expression for performance reasons.
*/
function remove_sql_ws($sql)
{
	$sql2 = $sql;
	$sql = '';
	
	$single_quote=FALSE;
	for($i=0; $i<strlen($sql2); $i++)
	{
		switch(substr($sql2,$i,1))
		{
			case ' ':
			case "\t":
			case "\n":
			case "\r":
				if($i==0 || $single_quote!==FALSE)
					$sql .= substr($sql2,$i,1);
				else if(strlen($sql)==0 || substr($sql,-1) !== ' ')
				{
					// Ignore all other types of whitespace, and only insert single spaces.
					$sql .= ' ';
				}
				break;
				
			case '\'':
				// Check for whether quote has been escaped or not.
				if($i==0 || substr($sql2,$i-1,1) != '\\') 
					$single_quote = !$single_quote;
					
				$sql .= substr($sql2,$i,1);
				break;
				
			default:
				$sql .= substr($sql2,$i,1);
		}
	}
	return $sql;
}

/**
	Only supports LEFT JOIN - no other join syntax. 

	Any other JOIN use will have to be added to this script to
	enable proper prefixing of table names.
*/
function parse_sql_statement($sql, $prefix)
{
	// Match all whitespace and convert to single character
	// space.  Maintain whitespace within strings enclosed
	// in single quotes, so that INSERTS,UPDATES, etc will
	// still work properly.
	$sql = remove_sql_ws($sql);
	
	// A copy of $sql variable for parsing only!
	$upper_sql = strtoupper($sql);

	if(substr($upper_sql,0,7) == 'SELECT ')
	{
		$start_idx = strpos($upper_sql, 'FROM ', $start_idx);
		if($start_idx !== FALSE)
		{
			$start_idx += 5;//5="FROM "
			
			// LEFT JOIN at the moment - will add more if required.
			$end_idx = strpos($upper_sql, 'LEFT JOIN ', $start_idx);
			if($end_idx !== FALSE)
			{
				$tmp_end_idx = $end_idx;
				while($tmp_end_idx !== FALSE)
				{
					$left_join_end_idx = strpos($upper_sql, 'ON', $tmp_end_idx);
					if($left_join_end_idx === FALSE)
					{
						$left_join_end_idx = strpos($upper_sql, 'USING', $tmp_end_idx);
					}
					
					// Nothing else we can do if it does not match.
					if($left_join_end_idx === FALSE)
						$left_join_end_idx = strlen($upper_sql);

					// Now we have to add the prefix to the LEFT JOIN table.
			    	$sql = 
						substr($sql, 0, $tmp_end_idx+10)//10="LEFT JOIN "
						.$prefix
						.trim(substr($sql,$tmp_end_idx+10,$left_join_end_idx-($tmp_end_idx+10)))
						.' '
						.substr($sql, $left_join_end_idx);
				
					// Its too complicated to work out where we are in $upper_sql compared to $sql, so
					// lets just reassign in this case.
					$upper_sql = strtoupper($sql);
				
					$tmp_end_idx = strpos($upper_sql, 'LEFT JOIN ', $left_join_end_idx+strlen($prefix));
				}
			}		
			else
			{ 
				$end_idx = strpos($upper_sql, 'WHERE ', $start_idx);
				if($end_idx === FALSE)
				{
					$end_idx = strpos($upper_sql, 'GROUP BY ', $start_idx);
					if($end_idx === FALSE)
					{
						$end_idx = strpos($upper_sql, 'HAVING ', $start_idx);
						if($end_idx === FALSE)
						{
							$end_idx = strpos($upper_sql, 'ORDER BY ', $start_idx);
							if($end_idx === FALSE)
							{
								$end_idx = strpos($upper_sql, 'LIMIT ', $start_idx);
							}
						}							
					}
				}
			}
			
			//if still FALSE, then assume nothing but a FROM clause.
			if($end_idx === FALSE)
			{
				$end_idx = strlen($upper_sql);
			}
			
			$from_clause = trim(substr($sql,$start_idx,$end_idx-$start_idx));
			
			$starts_ends_with_brackets = FALSE;
			if(starts_with($from_clause, "(") && ends_with($from_clause, ")"))
			{
				$starts_ends_with_brackets = TRUE;
				$from_clause = substr($from_clause,1,-1);
			}

			$array_of_tables = explode(',', $from_clause);
			
			// Reset from clause.
			$from_clause = '';
			while (list (, $table) = each($array_of_tables)) 
			{
				if(strlen($from_clause)>0)
					$from_clause .= ', ';
				$from_clause .= $prefix.trim($table);
			}
			
			if($starts_ends_with_brackets)
			{
				$from_clause = '('.$from_clause.')';
			}
			
			return substr($sql,0,$start_idx)
					.' '
					.$from_clause
					.' '
					.substr($sql, $end_idx);
		}				
	}
	else if(substr($upper_sql,0,12) == 'INSERT INTO ')
	{
		$end_idx = strpos($upper_sql,'(');
		if($end_idx!==FALSE)
		{
			return 'INSERT INTO '.$prefix
								.trim(substr($sql,12,$end_idx-12))
								.' '
								.substr($sql,$end_idx);  // 12 = "INSERT INTO "
		}
	}
	else if(substr($upper_sql,0,7) == 'UPDATE ')
	{
		$end_idx = strpos($upper_sql,'SET ');
		if($end_idx!==FALSE)
		{
			return 'UPDATE '.$prefix
							.trim(substr($sql,7,$end_idx-7))
							.' '
							.substr($sql,$end_idx); // 7 == "UPDATE "
		}
	}
	else if(substr($upper_sql,0,12) == 'DELETE FROM ')
	{
		$end_idx = strpos($upper_sql,'WHERE ');
		// No restriction, all records deleted.
		if($end_idx === FALSE)
			$end_idx = strlen($upper_sql);
		
		return 'DELETE FROM '.$prefix
							.trim(substr($sql, 12, $end_idx-12))
							.' '
							.substr($sql, $end_idx);//12="DELETE FROM "
	}
	else if(substr($upper_sql,0,13) != 'UNLOCK TABLES' && substr($upper_sql,0,12) == 'LOCK TABLES ')
	{
		// NOTE: assume that LOCK tables statement will encompass the whole $upper_sql text.
		$tables_r = explode(',', substr($sql, 12));//LOCK TABLES	
		if(is_array($tables_r))
		{
			$query = '';
			while(list($key, $table) = each($tables_r))
			{
				if(strlen($query)>0)
					$query .= ', ';
						
				$query .= $prefix.trim($table);
			}
			
			return 'LOCK TABLES '.$query;
		}
	}
	else if(substr($upper_sql,0,13) == 'CREATE TABLE ')
	{
		$end_idx = strpos($upper_sql,'(');
		if($end_idx!==FALSE)
		{
			return 'CREATE TABLE '.$prefix
								.trim(substr($sql,13,$end_idx-13))
								.' '
								.substr($sql,$end_idx);  // 13 = "CREATE TABLE "
		}
	}
	else if(substr($upper_sql,0,12) == 'ALTER TABLE ')
	{
		if( ($end_idx = strpos($upper_sql,'ADD ',12))!==FALSE)
			$end_idx += 3; // 'ADD'
		else if( ($end_idx = strpos($upper_sql,'DROP ',12))!==FALSE)
			$end_idx += 4; // 'DROP'
		else if( ($end_idx = strpos($upper_sql,'CHANGE ',12))!==FALSE)
			$end_idx += 6; // 'CHANGE'
		else if( ($end_idx = strpos($upper_sql,'ALTER ',12))!==FALSE)
			$end_idx += 5; // 'ALTER'
		else if( ($end_idx = strpos($upper_sql,'MODIFY ',12))!==FALSE)
			$end_idx += 6; // 'MODIFY'
		else if( ($end_idx = strpos($upper_sql,'RENAME ',12))!==FALSE)
			$end_idx += 6; // 'RENAME'
		else if( ($end_idx = strpos($upper_sql,'ORDER BY ',12))!==FALSE)
			$end_idx += 8; // 'ORDER BY'

		if($end_idx!==FALSE)
		{
			return 'ALTER TABLE '.$prefix
								.trim(substr($sql,12,$end_idx-12))
								.' '
								.trim(substr($sql,$end_idx));  // 12 = "ALTER TABLE "
		}
	}
	else if(substr($upper_sql,0,11) == 'DROP TABLE ')
	{
		$start_idx = 11; // 11 = "DROP TABLE "
		if(strpos($upper_sql,'IF EXISTS ', 11) !== FALSE)
			$start_idx = 21; // 21 = "DROP TABLE IF EXISTS "
			
		$end_idx = strpos($upper_sql,';');
		if($end_idx===FALSE)
			$end_idx = strlen($upper_sql);

		return 	substr($upper_sql,0,$start_idx)
				.$prefix
				.trim(substr($sql,$start_idx,$end_idx-$start_idx))
				.substr($sql,$end_idx);  // 12 = "CREATE TABLE "
	}
	else if(substr($upper_sql,0,13) == 'RENAME TABLE ') // Only supports ONE table rename!!!
	{
		$to_idx = strpos($upper_sql,'TO');
		$from_name = trim(substr($sql,13,$to_idx-13));
		$to_name = trim(substr($sql,$to_idx+2));
			
		return 'RENAME TABLE '
				.$prefix.$from_name
				.' TO '
				.$prefix.$to_name;  // 13 = "RENAME TABLE "
	}
	else if(substr($upper_sql,0,9) == 'DESCRIBE ') // Only supports ONE table rename!!!
	{
		$from_name = trim(substr($sql,9));
		
		return 'DESCRIBE '.$prefix.$from_name;
	}
	else if(substr($upper_sql,0,24) == 'SHOW TABLE STATUS LIKE \'') // SHOW TABLE STATUS LIKE 'item_instance'
	{
		$table_name = trim(substr($sql,24));
		return 'SHOW TABLE STATUS LIKE \''.$prefix.$table_name;
	}
	else if(substr($upper_sql,0,23) == 'SHOW FULL COLUMNS FROM ') // SHOW FULL COLUMNS FROM item_instance
	{
		$table_name = trim(substr($sql,23));
		return 'SHOW FULL COLUMNS FROM '.$prefix.$table_name;
	}
	else if(substr($upper_sql,0,18) == 'SHOW COLUMNS FROM ') // SHOW COLUMNS FROM item_instance
	{
		$table_name = trim(substr($sql,18));
		return 'SHOW COLUMNS FROM '.$prefix.$table_name;
	}
	else//cannot parse - so return original $sql as last resort.
	{
		return $sql;
	}
}

/**
	This function should be updated every time a new table is added.
*/
function fetch_opendb_table_list_r()
{
  	$tables = array(
                's_item_type', 's_item_type_group', 's_item_type_group_rltshp', 's_attribute_type',
				's_attribute_type_lookup', 's_item_attribute_type', 's_status_type',
				's_address_type', 's_addr_attribute_type_rltshp', 's_site_plugin',
				's_site_plugin_conf', 's_site_plugin_input_field', 's_site_plugin_s_attribute_type_map',
				's_site_plugin_s_attribute_type_lookup_map', 's_site_plugin_link',
                's_config_group', 's_config_group_item', 's_config_group_item_var',
                's_title_display_mask', 's_title_display_mask_item',
                's_item_listing_conf', 's_item_listing_column_conf',
				'user', 'user_address',	'user_address_attribute', 'review', 'item',
				'item_instance', 'item_attribute', 'borrowed_item', 
				'announcement', 'import_cache', 'file_cache', 'php_session',
				's_file_type_content_group', 's_file_type', 's_file_type_extension', 
				's_language', 's_language_var', 's_table_language_var', 's_opendb_release',
  				'item_instance_relationship', 'mailbox', 's_role', 's_permission', 's_role_permission'
			);
  	
    return $tables;
}
?>
Return current item: Open Media Collectors Database