Location: PHPKode > projects > EXtensible Production Suite (XPS) > xpsdev/install/models/database.php
<?php
//========================================================================
// XPS
//========================================================================

class Database
{

    private $conn; //PDO connection
    private $dsn;
    private $user;
    private $password;
    private $xps_user;
    private $xps_host;
    private $xps_password;
	private $db_type;
	private $oracle_options;
    private $prefix;
	private $trans;
	public  $ERROR;

    function __construct($dsn='', $user='', $password='', $db_type='', $prefix='', $xps_user='', $xps_host='', $xps_password='', $oracle_options=array())
	{
        $this->dsn = $dsn;
        $this->user = $user;
        $this->password = $password;
        $this->xps_user = $xps_user;
        $this->xps_host = $xps_host;
        $this->xps_password = $xps_password;
        $this->db_type = $db_type;
        $this->oracle_options = $oracle_options;
        $this->prefix = $prefix;
        $this->conn = '';
        $trans = true;
        $ERROR = '';
	}

    public function __destruct() {
        $this->conn = null;
    }

/*******************************************************
* <p>test_db_connection</p>
*
* @access   public
* @author 	rennemannt <hide@address.com>
* @param	$dsn
* @param	array [$params] array of parameters.
* @return	test_results string
********************************************************/
public function test_db_connection($dsn, $params)
{
    $test_results = array();
    try
    {
        $this->conn = new PDO($dsn, $params['db_username'], $params['db_password']);
        $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  //throws any error as an exception
        $test_results['message'] = 'Successfully connected to database host!'; //TODO: get this message from english.xml
        $test_results['error_count'] = 0;
    }
    catch (PDOException $e)
    {
        $test_results['message'] = $e->getMessage();
        $test_results['error_count'] = 1;
    }
    return $test_results;
}

    private function connect()
	{
        if($this->conn === '')
        {
            try
            {
                $this->conn = new PDO($this->dsn, $this->user, $this->password);
                $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  //throws any error as an exception
            }
            catch (PDOException $e)
            {
                $this->ERROR .= $e->getMessage() . '<br/>';
                die($this->ERROR);
            }
        }
    }

    function disconnect()
	{
        $this->conn = null;
    }

	/**
	* Does an INSERT, UPDATE, or DELETE on the database
	*
	* @access public
	* @param $statement the PDO prepared statement
	* @author Brian Cook <hide@address.com>
	* @return boolean
	*/
    public function execute($statement)
	{
        $this->connect();
    	try
		{
	        // if statement is a string it needs to be "prepared"
	        $this->conn->beginTransaction();

			if(is_string($statement))
			{
	            $statement = $this->conn->prepare($statement);
	        }
			$statement->execute();
			$this->conn->commit();
	        return true;
        }
		catch (PDOException $e)
		{
			$this->ERROR = $e->getMessage() . '<br/>';
			$this->conn->rollBack();

			return false;
		}
    }

	/**
	* Does a SELECT statement on the database
	*
	* @access public
	* @param $statement the PDO prepared statement
	* @author Brian Cook <hide@address.com>
	* @return $recordset the SELECTed recordset.
	*/
    private function select($statement)
	{
        $this->connect();
        //returns recordset
        try
		{
            if(is_string($statement))
			{ //SQL
                $statement = $this->conn->prepare($statement);
            }
            $statement->execute();
            return $statement->fetchAll();
        }
		catch (PDOException $e)
		{
            $this->ERROR .= $e->getMessage().'<br />';
            return false;
        }
    }

	/**
	* Retrieves an SQL statement from an XML file.
	*
	* @access private
	* @param $id the identification value for the SQL statement.
	* @author Brian Cook <hide@address.com>
	* @return $sql_statement_node->item(0)->nodeValue the SQL statement.
	*/
	private function get_sql_statement($id)
	{
		$dom = new DOMDocument;
		$dom->preserveWhiteSpace = false;
		$dom->load(CONFIGS . '/sql_statements.xml');

		$xpath = new DOMXPath($dom);

		$sql_statement_node = $xpath->query('//sql_statement[@id="'.$id.'"]/sql[@db_lang="'.$this->db_type.'"]');

		if((count($sql_statement_node) <= 0) or ($sql_statement_node->item(0) == NULL))
		{
				$sql_statement_node = $xpath->query('//sql_statement[@id="'.$id.'"]/sql[@db_lang="default"]');
		}

		switch($this->db_type)
		{
		 	case 'oracle':
				$replacement = $this->prefix . '.';
		 		break;

		 	default:
				$replacement = '';
				break;
		}

		if(count($sql_statement_node) > 0)
	 	{
			$sql = $sql_statement_node->item(0)->nodeValue;
			$sql = str_replace('{table_prefix}', $replacement, $sql);
			$sql = str_replace('{tablespace}', $this->prefix, $sql);
			$sql = str_replace('{user}', $this->xps_user, $sql);
			$sql = str_replace('{host}', $this->xps_host, $sql);
			$sql = str_replace('{password}', $this->xps_password, $sql);
			if(sizeof($this->oracle_options) > 0)
			{
				$sql = str_replace('{oracle_path}', $this->oracle_options['oracle_path'], $sql);
				$sql = str_replace('{oracle_size}', $this->oracle_options['oracle_size'], $sql);
				$sql = str_replace('{oracle_autoextend}', $this->oracle_options['oracle_autoextend'], $sql);
			}
		
			return $sql;
	 	}
	 	else
	 	{
	 	 	//return //insert code to get error message here
	 		return false;
	 	}
	}

    /**
	* Prepares a SQL statement to be executed by PDO
	*
	* @access public
	* @param $sql The SQL statement
	* @param $params An array of parameters to bind to the values in the SQL statement
	* @author heidtc <hide@address.com>, Brian Cook <hide@address.com>
	* @return $statement The prepared statement
	*/
	private function get_prepared_statement($sql, $params=array())
	{
        $this->connect();

		$statement = $this->conn->prepare($sql);

	 	foreach($params as $key => $val)
	 	{
			$statement->bindParam(':'.$key, $$val);//the double $ is on purpose
	 	}

		foreach($params as $val)
	 	{
			$$val = $val;//the double $ is on purpose
	 	}

		return $statement;
	}

	/**
	* Retrieves a count() for the number of records that would be found by executing a SQL statement
	*
	* @access public
	* @param $id The identification of the SQL statement stored in an XML file
	* @param $params An array of parameters to bind to the values in the SQL statement
	* @author Brian Cook <hide@address.com>
	* @return $rs[0]['RECORD_COUNT'] The integer value for the count.
	*/
	public function get_count($file_location, $params=array())
	{
		$sql = $this->get_sql_statement('count_table', $file_location);

		$new_sql = str_replace('{table_name}', $params['table_name'], $sql);

		$statement = $this->get_prepared_statement($new_sql, $params);
		$rs = $this->select($statement);

		return $rs[0]['SOME_COUNT'];
	}

	/**
	* Gets a recordset.
	*
	* @access public
	* @param $id the identification value for the SQL statement.
	* @param $params an associative array of table names for keys with values.
	* @author Brian Cook <hide@address.com>
	* @return recordset the database recordset.
	*/
	public function get_recordset($id, $params=array())
	{

		$sql = $this->get_sql_statement($id);

		if((array_key_exists('LIMIT', $params)) and (array_key_exists('OFFSET', $params)))
		{
			$sql = $this->set_offset($sql, $params);
			unset($params['LIMIT'], $params['OFFSET'], $params['ORDER_BY']);
		}

		$statement = $this->get_prepared_statement($sql, $params);

		$recordset = $this->select($statement);

		$recordset = $this->format_recordset($recordset);

		return $recordset;
	}

	/**
	* Executes a predefined SQL statement.
	*
	* @access private
	* @param $id the identification value for the SQL statement.
	* @param $params an associative array of table names for keys with values.
	* @author Brian Cook <hide@address.com>
	* @return boolean true if no errors and false if error encountered.
	*/
	public function execute_sql($id, $params=array())
	{
		$db_lang = '';
		$sql = $this->get_sql_statement($id);

        if(!empty($params))
        {
            $params = $this->format_params($params);
        }
		$statement = $this->get_prepared_statement($sql, $params);
		$bool = $this->execute($statement);

		return $bool;
	}

	//this might be used for file i/o
	public function fetch_statement($id, $params=array())
	{
	 	$output = array();
		$sql = $this->get_sql_statement($id);

		$statement = $this->get_prepared_statement($sql, $params);

	 	$bool = $this->execute($statement);
	 	$row = $statement->fetch();

		$output = stream_get_contents($row[0]);

	 	return $output;
	 }

	/*************************************
	* Sets the x,y coordinates of a widget
	*
	* @access	public
	* @param	$params['WIDGET_ID'] the id of the widget
	* @param	$params['POSITION_X'] the x coordinate of the widget
	* @param	$params['POSITION_Y'] the y coordinate of the widget
	* @return	boolean
	*************************************/
	public function set_position($params=array())
	{
		$sql = $this->get_sql_statement('set_position', 'core');

	 	$statement = $this->get_prepared_statement($sql, $params);

	 	$bool = $this->execute($statement);

	 	return $bool;
	}

	/*************************************
	* Sets the offset for page numbering
	*
	* @access	public
	* @param	$params['LIMIT'] the limit of items per page
	* @param	$params['OFFSET'] determines current page being viewed
	* @param	$sql The SQL statement
	* @return	modified $sql
	*************************************/
	public function set_offset($sql, $params)
	{
		$new_sql = str_replace('{LIMIT}', $params['LIMIT'], $sql);
		$new_sql = str_replace('{OFFSET}', $params['OFFSET'], $new_sql);
		$new_sql = str_replace('{ORDER_BY}', $params['ORDER_BY'], $new_sql);

		return $new_sql;
	}

	/*************************************
	* Strips html markup from params before inserting into a database
	*
	* @access	private
	* @author	Brian Cook < hide@address.com >
	* @param	$params an array of values to be bound in a sql statement
	* @return	$params the modified params array
	*************************************/
	private function format_params($params)
	{
		foreach($params as $key => $val)
		{
		 	$params[$key] = htmlentities($val);
		}
		return $params;
	}

	/*************************************
	* Converts all recordset HTML entities to their applicable characters
	*
	* @access	private
	* @author	Brian Cook < hide@address.com >
	* @param	$recordset a list of records returned from the database
	* @return	$recordset the modified recordset array
	*************************************/
	private function format_recordset($recordset)
	{
		if(is_array($recordset))
		{
			foreach($recordset as $key => $val)
			{
				foreach($val as $key1 => $val1)
				{
			 		$recordset[$key][$key1] = html_entity_decode($val1);
				}
			}
		}
		return $recordset;
	}

	public function execute_sql_line($sql)
	{
	 	$statement = $this->get_prepared_statement($sql, array());
		return $this->execute($statement);
	}
}
?>
Return current item: EXtensible Production Suite (XPS)