<?
////////////////////////////////////////////////////////////////////////////////////////
// Class: DbConnector
// Purpose: Connect to a database, MySQL version
///////////////////////////////////////////////////////////////////////////////////////
require_once('config.php');

class DbConnector
{
	public $theQuery;
	public $link;
	private $sql_file = "includes/InitialDatabase.sql";

	// Function: __construct, Purpose: Connect to the database ***
	public function __construct()
	{
		// Connect to the database
		$this->link = mysqli_connect(DB_SERVER, DB_USER, DB_PASS) or die("Could not connect to the database. ");
		
		if (!mysqli_select_db($this->link, DB_NAME))
		{
			echo "<p>Databe ".DB_NAME." does not exists. Creating database...</p>";
			$newdbresult = $this->buildDB(DB_NAME);
			if ($newdbresult === true)
			{
				echo "<p>Database ".DB_NAME." created succesfully.</p>";
			}
			else
			{
				die("<p>$newdbresult</p><p>Creation of database ".DB_NAME." failed.</p>");
			}
		}
		mysqli_query($this->link, "SET NAMES 'utf8'");
		mysqli_query($this->link, "SET sql_mode = ''");
	}

	// Function: __destruct, Purpose: Close the connection ***
	public function __destruct() {
		mysqli_close($this->link);
	}

	// Function: query, Purpose: Execute a database query ***
	public function query($query)
	{
		$this->theQuery = $query;
		return mysqli_query($this->link, $query);
	}

	// Function: fetchArray, Purpose: Get array of query results ***
	public function fetchArray($result)
	{
		return mysqli_fetch_array($result);
	}

	// Function: fetchAssoc, Purpose: Get array of query results ***
	public function fetchAssoc($result)
	{
		return mysqli_fetch_assoc($result);
	}

	// Function: getQuery, Purpose: Returns the last database query, for debugging ***
	public function getQuery()
	{
		return $this->theQuery;
	}

	// Function: getNumRows, Purpose: Return row count, MySQL version ***
	public function getNumRows($result)
	{
		return mysqli_num_rows($result);
	}
	
	// Function: getNumRows, Purpose: Return row count, MySQL version ***
	public function getAffectedRows()
	{
		return mysqli_affected_rows($this->link);
	}
	
	// Function: getLastInsertedId, Purpose: Retrieves the ID generated for an AUTO_INCREMENT column by the previous query ***
	public function getLastInsertedId()
	{
		return mysqli_insert_id($this->link);
	}

	
	// Built the database from scratch
	private function buildDB($db)
	{
		$error = "";
		mysqli_query($this->link, "CREATE DATABASE $db DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
		if (mysqli_error($this->link)!='')
		{
			$error = mysqli_error($this->link);
			return $error;
		}
		mysqli_select_db($this->link, $db);
		mysqli_query($this->link, "SET NAMES 'utf8'");		
		
		$queries_array = $this->getSQLQueries($this->sql_file);
		
		foreach ($queries_array as $query)
		{
			if ($query!="")
			{
				mysqli_query($this->link, $query);				
				if (($error=mysqli_error($this->link))!="")
				{
					mysqli_query($this->link, "DROP DATABASE $db");
					
					return $error;
				}
			}
		}
		
		return true;
	}

	// Reads the $sql_file with the dbms schema and returns an array with the sql queries splitted and cleared from comments
	private function getSQLQueries($sql_file)
	{
		$sql_queries = file_get_contents($sql_file);
		$sql_queries = $this->removeSQLRemarks($sql_queries);
		$sql_queries = $this->removeSQLComments($sql_queries);
		$sql_queries = $this->splitSQLFile($sql_queries, ';');
		
		return $sql_queries;
	}

	// removeSQLComments will strip the sql comment lines defined with slash and asterisk  (/* ... */) out of an uploaded sql file
	private function removeSQLComments($sql)
	{
	   $lines = explode("\n", $sql);
	   $sql = "";

	   // try to keep mem. use down
	   $linecount = count($lines);
		$output = "";
	   
	   $in_comment = false;
	   for($i = 0; $i < $linecount; $i++)
	   {
		  if( preg_match("/^\/\*/", preg_quote($lines[$i])) )
		  {
			 $in_comment = true;
		  }

		  if( !$in_comment )
		  {
			 $output .= $lines[$i] . "\n";
		  }

		  if( preg_match("/\*\/$/", preg_quote($lines[$i])) )
		  {
			 $in_comment = false;
		  }
	   }

	   return $output;
	}

	// removeSQLRemarks will strip the sql comment lines starting with # or -- out of an uploaded sql file
	private function removeSQLRemarks($sql)
	{
	   $lines = explode("\n", $sql);

	   // try to keep mem. use down
	   $sql = "";

	   $linecount = count($lines);
	   $output = "";

	   for ($i = 0; $i < $linecount; $i++)
	   {
		  if (($i != ($linecount - 1)) || (strlen($lines[$i]) > 0))
		  {
			 if (!preg_match("/^#/", preg_quote($lines[$i])) and !preg_match("/^\-\-/", preg_quote($lines[$i])))
			 {
				$output .= $lines[$i] . "\n";
			 }
			 else
			 {
				$output .= "\n";
			 }
			 // Trading a bit of speed for lower mem. use here.
			 $lines[$i] = "";
		  }
	   }

	   return $output;

	}

	// splitSQLFile will split an uploaded sql file into single sql statements.
	private function splitSQLFile($sql, $delimiter)
	{
	   // Split up our string into "possible" SQL statements.
	   $tokens = explode($delimiter, $sql);

	   // try to save mem.
	   $sql = "";
	   $output = array();

	   // we don't actually care about the matches preg gives us.
	   $matches = array();

	   // this is faster than calling count($oktens) every time thru the loop.
	   $token_count = count($tokens);
	   for ($i = 0; $i < $token_count; $i++)
	   {
		  // Don't wanna add an empty string as the last thing in the array.
		  if (($i != ($token_count - 1)) || (strlen($tokens[$i] > 0)))
		  {
			 // This is the total number of single quotes in the token.
			 $total_quotes = preg_match_all("/'/", $tokens[$i], $matches);
			 // Counts single quotes that are preceded by an odd number of backslashes,
			 // which means they're escaped quotes.
			 $escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $tokens[$i], $matches);

			 $unescaped_quotes = $total_quotes - $escaped_quotes;

			 // If the number of unescaped quotes is even, then the delimiter did NOT occur inside a string literal.
			 if (($unescaped_quotes % 2) == 0)
			 {
				// It's a complete sql statement.
				$output[] = $tokens[$i];
				// save memory.
				$tokens[$i] = "";
			 }
			 else
			 {
				// incomplete sql statement. keep adding tokens until we have a complete one.
				// $temp will hold what we have so far.
				$temp = $tokens[$i] . $delimiter;
				// save memory..
				$tokens[$i] = "";

				// Do we have a complete statement yet?
				$complete_stmt = false;

				for ($j = $i + 1; (!$complete_stmt && ($j < $token_count)); $j++)
				{
				   // This is the total number of single quotes in the token.
				   $total_quotes = preg_match_all("/'/", $tokens[$j], $matches);
				   // Counts single quotes that are preceded by an odd number of backslashes,
				   // which means they're escaped quotes.
				   $escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $tokens[$j], $matches);

				   $unescaped_quotes = $total_quotes - $escaped_quotes;

				   if (($unescaped_quotes % 2) == 1)
				   {
					  // odd number of unescaped quotes. In combination with the previous incomplete
					  // statement(s), we now have a complete statement. (2 odds always make an even)
					  $output[] = $temp . $tokens[$j];

					  // save memory.
					  $tokens[$j] = "";
					  $temp = "";

					  // exit the loop.
					  $complete_stmt = true;
					  // make sure the outer loop continues at the right point.
					  $i = $j;
				   }
				   else
				   {
					  // even number of unescaped quotes. We still don't have a complete statement.
					  // (1 odd and 1 even always make an odd)
					  $temp .= $tokens[$j] . $delimiter;
					  // save memory.
					  $tokens[$j] = "";
				   }

				} // for..
			 } // else
		  }
	   }

	   return $output;
	}
}
?>