Location: PHPKode > projects > EXtensible Production Suite (XPS) > xpsdev/install/controls/database_control.php
<?php
class Database_Control
{
	private $Database;
	private $Message_Control;
	private $Setting_Control;

 	public function __construct($SC, $MC)
 	{
 		require_once( MODELS . '/database.php');
		$this->Message_Control = $MC;
		$this->Setting_Control = $SC;

		$this->Database = new Database($this->create_dsn(),
									   $this->Setting_Control->get_setting('db_username'),
									   $this->Setting_Control->get_setting('db_password'),
									   $this->Setting_Control->get_setting('db_type'),
									   $this->Setting_Control->get_setting('db_table_prefix'));
 	}

/*******************************************************
* <p>check_existing_db</p>
*
* @access   public
* @author 	rennemannt <hide@address.com>
* @param	array [$params] array of parameters.
* @return	Smarty response
********************************************************/
public function drop_database(&$Smarty)
{
    $results = array();
    $sub_results = array();
    $params = array();
    //$params = $this->Setting_Control->get_settings();
    //Setup a connection to the information_schema.
    $tmp_params = $this->Setting_Control->get_settings();
    foreach($tmp_params as $key => $val)
    {
        $params[$key] = $val['value'];
    }
    $this->Database = new Database($this->create_dsn($params),
                                   $params['db_username'],
                                   $params['db_password'],
                                   $params['db_type'],
                                   $this->Setting_Control->get_setting('db_table_prefix'),
                                   $this->Setting_Control->get_setting('tablespace_username'),
								   $params['db_host']);

    //Drop XPS database.
    $error_count = 0;
	$old_oracle_file_path_fetch = array();

	if($params['db_type'] == 'oracle')
	{
		$old_oracle_file_path_fetch = $this->Database->get_recordset('oracle_file_path', array('TABLESPACE_NAME' => $params['db_name']));
	}

   
	$this->Database->execute_sql('drop_database');
	$this->Database->execute_sql('drop_connection_user');
	$this->Database->execute('COMMIT');
	
	if($params['db_type'] == 'oracle')
	{
		if(sizeof($old_oracle_file_path_fetch) > 0)
		{
			if(!@unlink($old_oracle_file_path_fetch[0]['FILE_NAME']))
			{
				echo 'Deletion of '.$params['oracle_path'].' failed. <br />  Most likely Oracle is still attempting to access the file.<br /> 
					Manually delete your dbf file before proceeding.';
			}
		}
	}
	
	$sub_results = array(   'message' => $this->Database->ERROR,
                                        'error_count' => $error_count
                                    );
        $results['drop_database'] = array('description' => 'Drop XPS database',
                                            'error_count' => $sub_results['error_count'],
                                            'message' => $sub_results['message']
                                            );

    $Smarty->assign('db_processes', $results);
    return true;
}

/*******************************************************
* <p>install_database</p>
*
* @access   public
* @author 	rennemannt <hide@address.com>
* @param	array [$params] array of parameters.
* @param	&$Smarty
* @return	boolean true/false.
********************************************************/
public function install_database($params, &$Smarty)
{
    $results = array();
    $sub_results = array();
    $params2 = array();

    if(sizeof($params) < 8)
    {
        $params = array();
        $tmp_params = $this->Setting_Control->get_settings();
        foreach($tmp_params as $key => $val)
        {
            $params[$key] = $val['value'];
        }
    }

    $params2 = $params;
    $params2['db_name'] = 'information_schema'; //Setup a connection to the information_schema.

    $this->Database = new Database($this->create_dsn($params2),
                                   $params['db_username'],
                                   $params['db_password'],
                                   $params['db_type'],
                                   $this->Setting_Control->get_setting('db_table_prefix'),
								   $this->Setting_Control->get_setting('tablespace_username'),
								   $params['db_host'],
                                   $this->Setting_Control->get_setting('tablespace_password'),
								   array('oracle_path' => $this->Setting_Control->get_setting('oracle_path'),
								   		'oracle_size' => $this->Setting_Control->get_setting('oracle_size'),
										'oracle_autoextend' => $this->Setting_Control->get_setting('oracle_autoextend')));

    //Create XPS database.
    $sub_results = $this->create_database($params);
    $results['create_database'] = array('description' => 'Create XPS database',
                                        'error_count' => $sub_results['error_count'],
                                        'message' => $sub_results['message']
                                        );
	
	//create the tablespace user, grant the new user full power over the new tablespace
	$table_statements = array('create_connection_user', 'grant_connection_user_permissions');
	foreach($table_statements as $table_statement)
    {
        $sub_results = $this->execute_statement($table_statement);
        $results[$table_statement] = array(  'description' => ucfirst(str_replace('_',' ',$table_statement)),
                                        'error_count' => $sub_results['error_count'],
                                        'message' => $sub_results['message']
                                        );
    }
    //Reset database connection to connect as the new user.

	$this->Database = new Database($this->create_dsn($params),
                                   $this->Setting_Control->get_setting('tablespace_username'),
                                   $this->Setting_Control->get_setting('tablespace_password'),
                                   $params['db_type'],
                                   $this->Setting_Control->get_setting('db_table_prefix'));

    //Create tables under the tablespace user's name.
    $table_statements = array('create_xps_user', 'create_xps_module', 'create_xps_widget', 'create_xps_module_install_log', 'create_xps_link', 'create_xps_user_widget_pref');
    foreach($table_statements as $table_statement)
    {
        $sub_results = $this->execute_statement($table_statement);
        $results[$table_statement] = array(  'description' => ucfirst(str_replace('_',' ',$table_statement)),
                                        'error_count' => $sub_results['error_count'],
                                        'message' => $sub_results['message']
                                        );
    }

    //Create constraints.
    $constraint_statements = array('create_constraint_fk_mod2wid', 'create_constraint_fk_log2mod', 'create_constraint_fk_xuwp2user', 'create_constraint_fk_xuwp2wid');
    foreach($constraint_statements as $constraint_statement)
    {
        $sub_results = $this->execute_statement($constraint_statement);
        $results[$constraint_statement] = array(  'description' => ucfirst(str_replace('_',' ',$constraint_statement)),
                                        'error_count' => $sub_results['error_count'],
                                        'message' => $sub_results['message']
                                        );
    }

    //Insert defaults.
    $insert_statements = array('insert_main_module','insert_navigation');
    foreach($insert_statements as $insert_statement)
    {
        $sub_results = $this->execute_statement($insert_statement);
        $results[$insert_statement] = array(  'description' => ucfirst(str_replace('_',' ',$insert_statement)),
                                        'error_count' => $sub_results['error_count'],
                                        'message' => $sub_results['message']
                                        );
    }
    $Smarty->assign('db_processes', $results);
    return true;
}

/*******************************************************
* <p>create_database</p>
*
* @access   public
* @author 	rennemannt <hide@address.com>
* @param	array [$params] array of parameters.
* @return	void
********************************************************/
public function create_database($params)
{
    $error_count = 0;
	$this->Database->execute_sql('create_database');
    If($this->Database->ERROR != '') $error_count = -1;

    return array(   'message' => $this->Database->ERROR,
                    'error_count' => $error_count
                );
}

/*******************************************************
* <p>execute_statement</p>
*
* @access   public
* @author 	rennemannt <hide@address.com>
* @param	$statement_id
* @return	void
********************************************************/
public function execute_statement($statement_id)
{
    $error_count = 0;
    $this->Database->execute_sql($statement_id);
    if($this->Database->ERROR != '') $error_count = -1;

    return array(   'message' => $this->Database->ERROR,
                    'error_count' => $error_count
                );
}

/*******************************************************
* <p>test_db_connection</p>
*
* @access   public
* @author 	rennemannt <hide@address.com>
* @param	array [$params] array of parameters.
* @return	test_results string
********************************************************/
public function test_db_connection($params, $info_schema=false)
{
    $test_results = array();
    if($info_schema)
    {
        $params['db_name'] = 'information_schema'; //This allows us to just test the connection to the database host.
    }
    $test_results = $this->Database->test_db_connection($this->create_dsn($params),$params);

    if(substr_count($test_results['message'], 'Unknown database') > 0)
    {
        $test_results['message'] = 'The <b>' . $params['db_name'] . '</b> database was not found!';
    }

    return $test_results;
}

	private function create_dsn($params=array())
	{

//print_r($params);
		$dsn = '';
        if(sizeof($params) > 0)
        {
            switch($params['db_type'])
            {
                case 'mysql':
                    $dsn = 'mysql:dbname='.$params['db_name'].';host='.$params['db_host'].';';
                    break;

                case 'oracle':
                    $dsn = 'oci:dbname=//'.$params['db_host'].':'.$params['oracle_port'].'/'.$params['oracle_listener'];
                    break;

                default:
                    die($this->Message_Control->get_message('database_cannot_connect'));

            }
        }
		return $dsn;
	}
	/**
	* Builds the page number list for navigation
	*
	* @access	public
	* @param	$id The identification value for the SQL statement
	* @param	$params The parameters to be bound to the SQL statement
	* @return	$anchor_string a list of anchor tags
	*/
	public function get_page_numbers($id, $file_location='core', $params=array())
	{

		$count = $this->Database->get_count($file_location, $params);

		$num_pages = ceil(($count / $params['LIMIT']));
		$current_page = (($params['OFFSET'] / $params['LIMIT'])+1);

	 	$upper_bound = '';
	 	$lower_bound = '';

		$upper_bound = $current_page + 2;
		$lower_bound = $current_page - 2;

 	 	if($num_pages > 5)
 	 	{
			if($upper_bound > $num_pages)
			{
				$overflow = ($upper_bound - $num_pages);
				$lower_bound -= $overflow;
				$upper_bound = $num_pages;
			}
			elseif($lower_bound < 1)
			{
				$overflow = (1 - $lower_bound);
			 	$upper_bound += $overflow;
				$lower_bound = 1;
			}
		}
		else
		{
			$lower_bound = 1;
			$upper_bound = $num_pages;
		}


		if(isset($params['js_funct']))
		{
			$js_funct = $params['js_funct'];
		}
		else
		{
			$js_funct = 'launchCoreModule';
		}

		if(isset($params['mod_id']))
		{
			$mod_id = $params['mod_id'];
		}
		else
		{
			$mod_id = 'UserAdmin';
		}

		if(isset($params['ORDER_BY']))
		{
			$order_by = $params['ORDER_BY'];
		}
		else
		{
			$order_by = 'User_ID';
		}

		$theme = $this->Setting_Control->get_setting('current_theme');

		$first_icon		= "<IMG title=\"First Page (1)\" style=\"border:none;\" src=\"./themes/".$theme."/images/first.png\" /></acronym>";
		$previous_icon	= "<IMG title=\"Previous Page\" style=\"border:none;\" src=\"./themes/".$theme."/images/previous.png\" /></acronym>";
		$next_icon		= "<IMG title=\"Next Page\" style=\"border:none;\" src=\"./themes/".$theme."/images/next.png\" /></acronym>";
		$last_icon		= "<IMG title=\"Last Page (".$num_pages.")\" style=\"border:none;\" src=\"./themes/".$theme."/images/last.png\" /></acronym>";

		$anchor_string = '';
	 	if($num_pages > 1)
	 	{
			if($current_page != 1)
		 	{
		 		$anchor_string .= "<a href=\"javascript:void(0)\" onclick=\"$js_funct('$mod_id','&ORDER_BY=$order_by&OFFSET=0')\">".$first_icon."</a>&nbsp;";
				$anchor_string .= "<a href=\"javascript:void(0)\" onclick=\"$js_funct('$mod_id','&ORDER_BY=$order_by&OFFSET=".(($current_page-2) * $params['LIMIT'])."')\">".$previous_icon."</a>&nbsp;";
		 	}
			else
			{
				$anchor_string .= "<IMG style=\"visibility:hidden; width:16px;\" src=\"./themes/".$theme."/images/transparent.png\" />&nbsp;";
				$anchor_string .= "<IMG style=\"visibility:hidden; width:16px;\" src=\"./themes/".$theme."/images/transparent.png\" />&nbsp;";
			}

		 	for($x=$lower_bound; $x<=$upper_bound; $x++)
		 	{
		 		if($x != $current_page)
		 		{
		 		 	$anchor_string .= "<a class=\"page_number\" href=\"javascript:void(0)\" onclick=\"$js_funct('$mod_id','&ORDER_BY=$order_by&OFFSET=".(($x-1) * $params['LIMIT'])."')\">".$x."</a>&nbsp;";
				}
				else
				{
				 	$anchor_string .= '<a class="current_page_number">'. $x .'</a>&nbsp;';
				}
		 	}


			if($current_page != $num_pages)
			{
				$anchor_string .= "<a href=\"javascript:void(0)\" onclick=\"$js_funct('$mod_id','&ORDER_BY=$order_by&OFFSET=".(($current_page) * $params['LIMIT'])."')\">".$next_icon."</a>&nbsp;";
				$anchor_string .= "<a href=\"javascript:void(0)\" onclick=\"$js_funct('$mod_id','&ORDER_BY=$order_by&OFFSET=".(($num_pages - 1) *$params['LIMIT'])."')\">".$last_icon."</a>&nbsp;";
			}
			else
			{
				$anchor_string .= "<IMG style=\"visibility:hidden; width:16px;\" src=\"./themes/".$theme."/images/transparent.png\" />&nbsp;";
				$anchor_string .= "<IMG style=\"visibility:hidden; width:16px;\" src=\"./themes/".$theme."/images/transparent.png\" />&nbsp;";
			}
		}
	 	return $anchor_string;
	}

	/**
	* Creates a unique id based on the current time
	*
	* @access	public
	* @author	Travis Rennemann <hide@address.com>
	* @return	$guid the generated unique id
	*/
	public function get_unique_id()
	{
		$time = mktime();
        $guid = $_SESSION['USER_ID'] . '-' . gmstrftime('%y%m%d-%H%M-%S', $time);

        while( stristr( $this->guid_list, $guid))
		{
            $time++;
            $guid = $_SESSION['USER_ID'] . '-' . gmstrftime('%y%m%d-%H%M-%S', $time);
        }
        $this->guid_list .= $guid;
        return $guid;
	}

	/**
	* Executs an INSERT, UPATE, or DELETE statement in the database
	* @access public
	* @param $statement the PDO prepared statement
	* @author Brian Cook <hide@address.com>
	* @return boolean
	*/
	public function execute_sql($id, $params=array())
	{
	 	$bool = $this->Database->execute_sql($id, $params);
	 	return $bool;
	}

	public function execute_sql_line($line)
	{
	 	return $this->Database->execute_sql_line($line);
	}

	/**
	* 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, $file_location='core', $params=array())
	{
		$return = $this->Database->get_recordset($id, $file_location, $params);
		return $return;
	}

	//might be used for file i/o
	public function get_stream_contents($id, $params=array())
	{
		return $this->Database->fetch_statement($id, $params);
	}

	/*************************************
	* Retrieves the x,y coordinates of a widget
	*
	* @access	public
	* @param	$id the id of the widget
	* @author	Brian Cook <hide@address.com>
	* @return	$position the x,y coordinates of the widget
	*************************************/
	public function get_position($id)
	{
		$params = array();
		$params['WIDGET_ID'] = $id;
		return $this->Database->get_position($params);
	}

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

		return $this->Database->set_position($params);
	}
	
	public function check_db_exists($db_name)
	{
		return $this->Database->get_recordset('detect_database', array('TABLESPACE_NAME' => $db_name));
	}
}
?>
Return current item: EXtensible Production Suite (XPS)