<?php
/**
 * db_functions.php - Like the name suggests, all DB functions.
 *
 * @author $Author: dtong $
 * @version $Id: db_functions.php,v 1.5 2011/02/10 05:49:56 dtong Exp $
 * @copyright Copyright (c) 2011, Tiller Software Co., Ltd.
*/
if (!defined('INSTALL_PROCESS')) exit('No direct script access allowed');

class db_functions {
	private $connect = FALSE;
	//try to pick the non-generic names
	private $schema_dirty_tables = array('ci_sessions', 'course_sis', 'resource_url', 'unit_search_cache1', 'user_course');

	//Connect to a database
	function connect($user, $password, $host, $schema=false) {
		$this->connect = @mysql_connect($host, $user, $password);
		if ( $schema === false ) {
			return $this->connect;
		}
		if ( mysql_select_db($schema, $this->connect) ) {
			return $this->connect;
		}
		$this->connect = false;
		return false;
	}

	//Selects the default schema
	function select_db($schema, $connect=false) {
		if ( !$connect ) {
			$connect = $this->connect;
		}
		if ( $connect ) {
			return  mysql_select_db($schema, $connect);
		}
		return false;
	}

	//Run various sqls to check for schema access
	function test_db_access($connect=false) {
		if ( !$connect ) {
			$connect = $this->connect;
		}
		if ( !$connect ) {
			return false;
		}
		//This should test all necessary db permissions
		$sql = array();
		$table = '_todcm_test_please_delete_';
		$sql[] = "drop table if exists $table";
		$sql[] = "create table $table (id int)";
		$sql[] = "alter table $table modify column id varchar(10)";
		$sql[] = "insert into $table values ('1')";
		$sql[] = "update $table set id='2'";
		$sql[] = "select * from $table";
		$sql[] = "drop table $table";
		foreach ($sql as $sql_str) {
			$ret = @mysql_query($sql_str);
			if ( !$ret ) {
				//Try to cleanup anyway
				@mysql_query('drop table $table', $connect);
				return false;
			}
		}
		return true;
	}

	//Checks a sample of the TODCM tables and see is the schema populated already
	function is_schema_dirty($schema, $connect=false) {
		if ( !$connect ) {
			$connect = $this->connect;
		}
		if ( !$connect ) {
			return false;
		}
		$this->select_db($schema, $connect);
		$sql = 'show tables';
		$resource = mysql_query($sql, $connect);
		$tables = array();
		if ( $resource ) {
			while ( $row = mysql_fetch_row($resource) ) {
				$tables[] = $row[0];
			}
		}
		$match_count = 0;
		$match_total = count($this->schema_dirty_tables);

		foreach ($this->schema_dirty_tables as $check_table) {
			if ( $match_count >= $match_total ) {
				break;
			}
			foreach ($tables as $db_table) {
				//Not doing a string comparison or use array search so this will continue to work when we add DB table prefix support
				if ( strpos($db_table, $check_table) !== false ) {
					$match_count++;
					break;
				}
			}
		}

		//4 out of 5 matches is dirty
		if ( $match_count >= ($match_total-1) ) {
			return '1';
		}
		return '0';
	}

	//For running DB queries from the DB script
	function todcm_query($sql, $connect) {
		$sql = trim($sql);
		$pos = stripos($sql, 'alter');
		$return_true = false;
		if ( $pos === 0 ) {
			//Some alter statements results error due to unspported DB constraints with MyISAM.
			//So, ignore all alters
			$return_true = true;
		}
		$ret = mysql_query($sql, $connect);
		if ( $return_true ) {
			return true;
		}
		return $ret;
	}

	private function r_user_exists($db_username) {
		$sql = "select user from mysql.user where user='$db_username'";
		$resource = mysql_query($sql, $this->connect);
		if ( mysql_num_rows($resource) > 0 )
			return true;
		return false;
	}

	private function r_set_password($db_username, $db_password) {
		//$sql = "update mysql.user set password=PASSWORD('{$db_password}') where user='{$db_username}'";
		$sql = "set password for '$db_username' = password('$db_password')";
		return mysql_query($sql, $this->connect);
	}

	private function r_create_user($db_username, $db_password) {
		$sql = "create user '$db_username' identified by '$db_password'";
		return mysql_query($sql, $this->connect);
	}

	private function r_create_schema($db_schema) {
		$sql = "create database $db_schema CHARACTER SET " . MYSQL_CHARSET . " COLLATE " . MYSQL_COLLATE;
		return mysql_query($sql, $this->connect);
	}

	private function r_assign_privillage($db_username, $db_schema) {
		$sql = "grant all privileges on {$db_schema}.* to '{$db_username}'";
		$ret = mysql_query($sql, $this->connect);
		@mysql_query('flush privileges', $this->connect);
		return $ret;
	}

	function set_todcm_password($user, $password, $cms_password_key, $connect) {
		$password = md5("{$password}{$cms_password_key}");
		$sql = "update user set password='{$password}' where username='$user'";
		return mysql_query($sql, $connect);
	}

	function setup_user($db_username, $db_password, $db_schema, $root_connection) {
		$this->connect = $root_connection;
		//Process user account
		if ( $db_username != 'root' ) {
			if ( $this->r_user_exists($db_username) ) {
				if (!$this->r_set_password($db_username, $db_password)) {
					return false;
				}
			}
			else {
				if ( !$this->r_create_user($db_username, $db_password) ) {
					return false;
				}
			}
		}
		//Process schema
		if ( !$this->select_db($db_schema) ) {
			if ( !$this->r_create_schema($db_schema) ) {
				return false;
			}
		}

		//Assign privillages. Always assign the privillages no matter what happened above.
		if ( $db_username != 'root' ) {
			if ( !$this->r_assign_privillage($db_username, $db_schema) ) {
				return false;
			}
		}
		return true;
	}
/*
	function set_key_check($connection, $action) {
		$sql = 'set foreign_key_checks=0';
		if ( $action ) {
			$sql = 'set foreign_key_checks=1';
		}
		@mysql_query($sql, $connection);
	}
*/
}