|
|
Posted by windandwaves on 08/20/07 20:29
Hi Folk
What do you think about this database class? Do you have any feedback
or ideas on how it could be improved?
Thank you
Nicolaas
class dbClass {
/*
const = costant
private = only available within class
protected = only available within class and related classes
to force to slave (if appropiate), choose "false" for
overideToMaster variable
enter values below
*/
public $errorString, $debug;
protected $firstWord, $databaseLink;
private $timeCriticalTablesArray = array("temp2"); // tables that
should always be done on the master
private $databaseArray = array(
"MASTER" => array("serverName"=>'xxx', "username"=>'xxx-1',
"password"=>'xxx', "databaseName"=>'xxx-1'),
"SLAVE" => array("serverName"=>'xxx', "username"=>'xxx-2',
"password"=>'xxx', "databaseName"=>'xxx-2')
);
private $sqlFirstWordIsSafeArray = array("SELECT"); //THESE HAVE TO
BE UPPERCASE!!!!!!!!!!!!!
private $masterDigitsArray = array(0,2,4,6,8); //change the ratio
between servers - the more numbers, the more work for the master
const maxTries = 3; //number of connection tries before an error is
returned
//initialisation, debugging and destruction
function __construct() {
$this->debugLine .= 'Starting DB class:<hr />';
}
function __destruct() {
//mysql_close($this->databaseLink);
$this->debugLine .= '<hr />Closeing Object:<br />';
}
public function getDebugInfo() {
echo "<p>".$this->debugLine."</p>";
if($this->errorString) {
echo '<p style="color: red;">'.$this->errorString.'</p>';
}
$this->debugLine = '';
$this->errorString = '';
}
//run queries
public function runQuery($queryType = "", $sqlString,
$overideToMaster = 0) {
//determine query type if not set
if(!($this->databaseLink = $this->createConnection($sqlString,
$overideToMaster))) {
return false;
}
//retrieve results
$this->debugLine .= ', running: <i style="font-size: 0.8em;">'.
$sqlString.'</i>';
if(!($result = mysql_query($sqlString, $this->databaseLink))) {
$this->errorString = ", error in SQL statement: ".mysql_error().'';
return false;
}
switch($queryType) {
case "value":
$data = $this->getFirstCell($result); break;
case "rows":
$data = $this->getRows($result); break;
case "oneRow":
$data = $this->getOneRow($result); break;
case "insert":
$data = $this->getUpdateQuery($result); break;
default:
$data = $result;
}
//mysql_free_result($result);
return $data;
}
private function getUpdateQuery($result) {
// runs any update query and returns the newly inserted row ID
(autonumber) for an insert
if(mysql_insert_id()) {
return mysql_insert_id();
}
elseif(mysql_affected_rows($this->databaseLink)){
return mysql_affected_rows($this->databaseLink);
}
else {
$this->errorString .= "<br />Maybe no data was affected";
return true;
}
}
private function getFirstCell($result) {
// gets the first cell of the first row (useful for SELECT COUNT(*)
FROM MYTABLE WHERE ID = 123)
if (mysql_num_rows($result) != 1) {
$this->debugLine .= ", the getFirstCell function could not be
executed because it did not have one line";
return false;
}
return mysql_result($result, 0,0);
}
private function getRows($result) {
//returns a multidimensional array
$manyRowArray = array();
while($row = mysql_fetch_assoc($result)) {
$manyRowArray[] = $row;
}
return $manyRowArray;
}
private function getOneRow($result) {
$row = array();
$row = mysql_fetch_assoc($result);
return $row;
}
//database connection
private function createConnection($sqlString, $overideToMaster = 0,
$lastTry = "", $counter = 0) {
//works on the assumption that the SLAVE will be used,
// but only if it passes all the tests
//ratios can be adjusted wtih the oddEvenRatio
$connection = false;
$this->selectedDatabase = "MASTER";
$this->debugLine .= " --- DB connection: ";
if($counter > self::maxTries) {
$this->errorString = "Can not connect to database.";
return 0;
}
elseif($overideToMaster) {
//do nothing
$this->debugLine .= ', M: overideToMaster = '.$overideToMaster;
}
elseif($lastTry == "SLAVE") {
//do nothing
$this->debugLine .= ', M: tried Slave last';
}
elseif($this->isTimeCriticalQuery($sqlString)) {
//do nothing
$this->debugLine .= ', M: time critical table present';
}
elseif(!$this->sqlFirstWordIsSafe($sqlString)) {
//do nothing
$this->debugLine .= ', M: first word is not save';
}
elseif($this->isMasterDigit() && overideToMaster !== false) {
//do nothing
$this->debugLine .= ', M: masterDigit';
}
else {
$this->selectedDatabase = "SLAVE";
}
$dbDetails = $this->databaseArray[$this->selectedDatabase];
$this->debugLine .= ', attempt connection to: '.$this-
>selectedDatabase;
if ( $this->databaseLink = mysql_connect($dbDetails["serverName"],
$dbDetails["username"], $dbDetails["password"], $counter) ) {
if(mysql_select_db($dbDetails["databaseName"], $this-
>databaseLink)) {
return $this->databaseLink;
}
else {
$this->errorString .= ', could not select database';
}
}
else {
$this->errorString .= ', could not create database link';
}
//creates a new link if the first one could not be established! JUST
IN CASE (counter > 0)
$counter++; //count the amount of connection attempts
$this->debugLine .= ', trying for '.$counter.'<sup>th</sup> time to
connect to database';
$lastTry = $this->selectedDatabase;
return $this->createConnection($sqlString, $overideToMaster,
$lastTry, $counter);
}
private function sqlFirstWordIsSafe($sqlString) {
// selects the first word of the query. (e.g. SELECT, INSERT,
etc...) and compares it to a list of exclusions
// needs to be checked if this will work OK in real life by looking
at all the options
$this->firstWord = strtoupper(substr(trim($sqlString), 0,
strpos(trim($sqlString), " ")));
return in_array($this->firstWord, $this->sqlFirstWordIsSafeArray);
}
private function isMasterDigit() {
// here you can work out some ratio for sending queries to Master or
Slave.
// This is the way to adjust the load between the servers
return in_array(intval(substr(time(),-1))+0, $this-
>masterDigitsArray);
}
private function isTimeCriticalQuery($sqlString) {
// Any queries that contain a reference to the any of the
timeCriticalTablesArray will always be send to the master
foreach($this->timeCriticalTablesArray as $value) {
if (stripos($sqlString, $value)!== false) {
return 1;
}
}
return 0;
}
}
Navigation:
[Reply to this message]
|