471,887 Members | 1,396 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,887 software developers and data experts.

database class

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</suptime 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;
}

}

Aug 20 '07 #1
0 1298

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Johan | last post: by
2 posts views Thread by Bryan | last post: by
35 posts views Thread by Terry Jolly | last post: by
3 posts views Thread by josh.kuo | last post: by
5 posts views Thread by Slant | last post: by
22 posts views Thread by amygdala | last post: by
reply views Thread by YellowAndGreen | last post: by
reply views Thread by zermasroor | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.