<?php
/* 	OpenDb - Open Media Lending Database
	Copyright (C) 2001,2002 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.
*/

/*
* 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.
*/
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);
	
	$start_idx = 0;
	
	// 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));
			
			// fix for prefixing tables where FROM clause is surrounded with ( and )
			if(starts_with($from_clause, '(') && ends_with($from_clause, ')'))
			{
				$start_idx ++;
				$end_idx --;
				
				$from_clause = substr($from_clause, 1, strlen($from_clause)-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);
			}

			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//cannot parse - so return original $sql as last resort.
		return $sql;
}

/**
* @param $sql
* @param $do_prefix	Whether to prefix the 
* @param $alt_db_server	Specify alternate $CONFIG_VARS database config 
*		source.  Any details not specified will be obtained from 
*		$CONFIG_VARS['db_server.?????'] configuration. All variables
*		available for include/config.php $CONFIG_VARS['db_server.?????'],
*		are legal for the $CONFIG_VARS[$alt_db_server.'.?????'] as well.
*/
function run_opendb_query($sql, $do_prefix=TRUE, $alt_db_server=NULL)
{
	// from config.php
	global $CONFIG_VARS;
	
	// Specify a different set of database information, $CONFIG_VARS[$alt_db_server.'.??????']
	if(strlen($alt_db_server)>0)
		$db_server = $alt_db_server;
	else
		$db_server = 'db_server';
		
	$connectlink = @mysql_connect($CONFIG_VARS[$db_server.'.host'],
   				$CONFIG_VARS[$db_server.'.username'],
   				$CONFIG_VARS[$db_server.'.passwd']);
	
	if($connectlink)
	{ 	
		if(!@mysql_select_db($CONFIG_VARS[$db_server.'.dbname'], $connectlink))
		{
			//echo(mysql_error());
			return FALSE;
		}
	}
	else
	{
		$error = mysql_error();
		if(strlen($error)>0)
			die($error);
		else
			die("User ".$CONFIG_VARS[$db_server.'.username']."@".$CONFIG_VARS[$db_server.'.host']." could not connect.");
	}
	
	// Only parse string if $do_prefix is not false AND a prefix actually defined!
	if($do_prefix && strlen($CONFIG_VARS[$db_server.'.table_prefix'])>0)
	{
		$sql = parse_sql_statement($sql, $CONFIG_VARS[$db_server.'.table_prefix']);
	}
	
	if($CONFIG_VARS[$db_server.'.debug-sql'] === TRUE)
		echo("<p>SQL: ".$sql."</p>");

	return mysql_query($sql);
}
?>