<?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> ";
$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> ";
}
else
{
$anchor_string .= "<IMG style=\"visibility:hidden; width:16px;\" src=\"./themes/".$theme."/images/transparent.png\" /> ";
$anchor_string .= "<IMG style=\"visibility:hidden; width:16px;\" src=\"./themes/".$theme."/images/transparent.png\" /> ";
}
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> ";
}
else
{
$anchor_string .= '<a class="current_page_number">'. $x .'</a> ';
}
}
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> ";
$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> ";
}
else
{
$anchor_string .= "<IMG style=\"visibility:hidden; width:16px;\" src=\"./themes/".$theme."/images/transparent.png\" /> ";
$anchor_string .= "<IMG style=\"visibility:hidden; width:16px;\" src=\"./themes/".$theme."/images/transparent.png\" /> ";
}
}
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));
}
}
?>