Posted By

coevolve on 03/05/10

Tagged

Versions (?)

Last Edited at 03/05/10 05:59am

Statistics

Viewed 566 times

Favorited by 0 user(s)

LoneDatabaseClass

/ Published in: PHP

Highlighted TextExpand

LoneDatabaseClass * PHP SQL class for MySQL, Oracle and MS SQL
ref: lonedb  ver: 0.5  date:20100305
Copyright (c) 2009 Milan Stankovic
semidot@gmail.com, coevolvesoft@gmail.com
License: MIT, http://www.opensource.org/licenses/mit-license.php

____________________________________________________________________________
todo:
ora: bind and execute always
ora: insert -add sequence name

____________________________________________________________________________
changes:

20100305
stable mssql support
20100120
mssql support
20091210
merged my and ora versions

____________________________________________________________________________
public:

$con, $rcount, $keys, $mode
select($table, $cols = '*', $where = null, $order = null, $opt = 0)
insert($table, $values, $cols = null, $filename='', $filefield='')
delete($table, $where = null)
update($table, $where, $values, $filename='', $filefield='')
selectAssociative($table, $cols = '*', $where = null, $order = null, $opt = 0)
getResult()
setConnection($host, $user, $pass, $dbname)
setDatabase($dbname)
disconnect()
cleanup($data, $write = false)
errDetails($rsc=null)

____________________________________________________________________________
private:

$db_host, $db_user, $db_pass, $db_name, $result, $oracon, $orast
connect()
tableExists($table)
cleanupSlashes($data, $write = false)
ms_ : select(), update(), insert(), delete(), selectAssociative()
my_ : select(), update(), insert(), delete(), selectAssociative()
ora_ : select(), update(), insert(), delete(), selectAssociative(), updateFile(), insertFile()

____________________________________________________________________________
notes:

All methods return true if completed successfully and false if an error occurred, except getResult and cleanup.
On setConnection, setDatabase, insert, delete and update, result is empty array, on disconnect result is intact.
For update and insert, filename and filefield used in oracle mode for file handling (bind mode).

____________________________________________________________________________

class LoneDatabaseClass {

var $db_host = '';			// use setConnection()
    var $db_user = '';
    var $db_pass = '';
    var $db_name = '';
    var $result = array();		// use getResult()
    var $con = false;			// connection active flag
    var $rcount = -1;
    var $keys = array();	
    var $oracon = null;
    var $orast = null;
    var $mode = '';

function update($table, $where, $values, $filename='', $filefield='') {
		// required: table: tabel name
		//		where: expresion as a string
		//		values: values as array, column names as keys
		//		filename, filefield: oracle file handling

if (!$this->tableExists($table)) { return false; }
		if ($where=='' || empty($values)) { return false; }
		if ($this->mode=='mysql') {
			return $this->my_update($table, $where, $values);
		}
		else if ($this->mode=='mssql') {
			return $this->ms_update($table, $where, $values);
		}
		if ($this->mode=='oracle') {
			if ($filename=='') {
				return $this->ora_update($table, $where, $values);
			}
			else {
				return $this->ora_updateFile($table, $where, $values, $filename, $filefield);
			}
		}
		return false;
	}

function delete($table, $where = null) {

if (!$this->tableExists($table)) { return false; }
		if ($where == null) { return false; }
		if ($this->mode=='mysql') {
			return $this->my_delete($table, $where);
		}
		else if ($this->mode=='mssql') {
			return $this->ms_delete($table, $where);
		}
		else if ($this->mode=='oracle') {
			return $this->ora_delete($table, $where);
		}
		return false;
	}

function selectAssociative($table, $keycolumn, $cols = '*', $where = null, $order = null, $opt = 0) {

if (!$this->tableExists($table)) { return false; }
                $q = 'SELECT '.$cols.' FROM '.$table;
                if ($where != null) { $q .= ' WHERE '.$where; }
                if ($order != null) { $q .= ' ORDER BY '.$order; }
		if ($this->mode=='mysql') {
			return $this->my_selectAssociative($q, $keycolumn);
		}
		else if ($this->mode=='oracle') {
			return $this->ora_selectAssociative($q, $keycolumn, $opt);
		}
		else if ($this->mode=='mssql') {
			return $this->ms_selectAssociative($q, $keycolumn, $table);
		}		
		return false;
	}

// ____________________________________________________________________________

function ms_select($q) {			// mssql select

//echo $q;

$query = mssql_query($q);
 if (!$query) { return false; }
		
 $this->rcount = mssql_num_rows($query);
		if ($this->rcount < 1) { return true; }

for ($i = 0; $i < $this->rcount; $i++) {
 	 $r = mssql_fetch_array($query, MSSQL_ASSOC);
 	 $this->keys = array_keys($r);
		 $kcount = count($this->keys);
 for ($x = 0; $x < $kcount; $x++) {

//$v= iconv('UCS-2LE', 'UTF-8//IGNORE', $r[$this->keys[$x]]);

$v = $r[$this->keys[$x]];
				
                if($this->rcount > 1) { $this->result[$i][$this->keys[$x]] = $v; }
                else { $this->result[$this->keys[$x]] = $r[$this->keys[$x]]; }
            }
        }
        return true;
	}

function ms_insert($table, $values, $cols) {			// mssql insert
    
        $q = 'INSERT INTO '.$table;
        if ($cols != null) { $q .= ' ('.$cols.')'; }
        $values = implode(',',$values);
        $q .= ' VALUES ('.$values.')';
        $ok = mssql_query($q);
        return $ok;
	}

function ms_selectAssociative($q, $keycolumn, $table) {			// mssql select associative

$query = mssql_query($q);
 if (!$query) { return false;}
 $this->rcount = mssql_num_rows($query);
		if ($this->rcount < 1) { return true; }
		$doconv = $this->columnsToConvert($table);
 for ($i = 0; $i < $this->rcount; $i++) {
 	$r = mssql_fetch_array($query, MSSQL_ASSOC);
 	$this->keys = array_keys($r);
			if (!in_array($keycolumn, $this->keys)) { return false; }
			$kcount = count($this->keys);
 for ($x = 0; $x < $kcount; $x++) {
				if ($doconv[$this->keys[$x]]) {
					$v= iconv('UCS-2LE', 'UTF-8//IGNORE', $r[$this->keys[$x]]);
				}
 else {
 	$v = $r[$this->keys[$x]];
				}
				$this->result[$r[$keycolumn]][$this->keys[$x]] = $v;
 }
 }
 return true;
	}

function ms_delete($table, $where) {					// mssql delete
	
		$q = 'DELETE FROM '.$table.' WHERE '.$where;
        $ok = mssql_query($q);
        return $ok;
	}	
	
	// ____________________________________________________________________________

function my_selectAssociative($q, $keycolumn) {			// mysql select associative

$query = mysql_query('SET CHARACTER SET utf8;');
 $query = mysql_query($q);
 if (!$query) { return false;}
 $this->rcount = mysql_num_rows($query);
		if ($this->rcount < 1) { return true; }
 for ($i = 0; $i < $this->rcount; $i++) {
 	$r = mysql_fetch_array($query);
 	$this->keys = array_keys($r);
			if (!in_array($keycolumn, $this->keys)) { return false; }
			$kcount = count($this->keys);
 for ($x = 0; $x < $kcount; $x++) {
 if (!is_int($this->keys[$x])) { // Sanitizes keys so only alphavalues are allowed
 $this->result[$r[$keycolumn]][$this->keys[$x]] = $r[$this->keys[$x]];
 }
				else {
					unset($this->keys[$x]);
				}
 }
 }
 return true;
	}

function my_delete($table, $where) {					// mysql delete
	
		$q = 'DELETE FROM '.$table.' WHERE '.$where;
		$ok = mysql_query('SET CHARACTER SET utf8;');
        if ($ok) { $ok = mysql_query($q); }
        return $ok;
	}
	
	// ____________________________________________________________________________

function ora_select($q, $opt) {													// oracle select

$this->orast = oci_parse($this->oracon, $q);
		if (!$this->orast) { $this->errDetails($this->oracon); return false; }
		if (!oci_execute($this->orast)) { $this->errDetails($this->orast); return false; }
		$oramode = OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS;
		if ($opt!=0) { $oramode = $opt; }
 $this->rcount = 0;
		while ($r = oci_fetch_array($this->orast, $oramode)) {
			if (empty($this->keys)) {
 		$this->keys = array_keys($r);
				$kcount = count($this->keys);
			}
 for ($x = 0; $x < $kcount; $x++) {
 if (is_int($this->keys[$x])) { return false; } // only alphavalues are allowed, OCI_ASSOC
 $this->result[$this->rcount][$this->keys[$x]] = $r[$this->keys[$x]];
 }
			$this->rcount++;
 }
		if($this->rcount == 1) { // columns as keys on one
			$t = $this->result[0];
			$this->result = array();
 for ($x = 0; $x < $kcount; $x++) {
 	$this->result[$this->keys[$x]]=$t[$this->keys[$x]];
			}
		}
 return true;
	}

function ora_update($table, $where, $values) {									// oracle update

$keys = array_keys($values);
		$vcount = count($values);
		$q = '';	
 for ($i = 0; $i < $vcount; $i++) {
 if ($q==''){ $q = $keys[$i].'='.$values[$keys[$i]]; }
 else { $q .= ','.$keys[$i].'='.$values[$keys[$i]]; }
 }
		$q = 'UPDATE '.$table.' SET '.$q;
 $q .= ' WHERE '.$where;
		$this->orast = oci_parse($this->oracon, $q);
		if (!$this->orast) { $this->errDetails($this->oracon); return false; }
		if (!oci_execute($this->orast)) { $this->errDetails($this->orast); return false; }
 return true;	
	}
	
	function ora_updateFile($table, $where, $values, $filename, $filefield) {		// oracle update file

$keys = array_keys($values);
		$vcount = count($values);
		$q = '';
 for ($i = 0; $i < $vcount; $i++) {
 	$pair = $keys[$i].'= '.$values[$keys[$i]];
 	if ($keys[$i]==$filefield) { $pair=$filefield.'= empty_blob()'; }
 if ($q==''){ $q = $pair; }
 else { $q .= ','.$pair; }
 }
		$q = 'UPDATE '.$table.' SET '.$q;
		$q .= ' WHERE '.$where;
		$q .= ' returning '.$filefield.' into :blobdata';	
		$this->orast = oci_parse($this->oracon, $q);
		if (!$this->orast) { $this->errDetails($this->oracon); return false; }
		$lob = oci_new_descriptor($this->oracon, OCI_D_LOB);
		oci_bind_by_name($this->orast, ':blobdata', $lob, -1, OCI_B_BLOB);
		if (!oci_execute($this->orast, OCI_DEFAULT)) { $this->errDetails($this->orast); return false; }
		$lob->import($filename);
		oci_commit($this->oracon);
		$lob->close();
		return true;
	}

function ora_selectAssociative($q, $keycolumn, $opt) {										// oracle select associative

$this->orast = oci_parse($this->oracon, $q);
		if (!$this->orast) { $this->errDetails($this->oracon); return false; }
		if (!oci_execute($this->orast)) { $this->errDetails($this->orast); return false; }
		$oramode = OCI_ASSOC+OCI_RETURN_NULLS;
		if ($opt!=0) { $oramode = $opt; }
 $this->rcount = 0;		
		while ($r = oci_fetch_array($this->orast, $oramode)) {
			if (empty($this->keys)) {
 		$this->keys = array_keys($r);
				$kcount = count($this->keys);
				if (!in_array($keycolumn, $this->keys)) { return false; }
			}
 for ($x = 0; $x < $kcount; $x++) {
 if (is_int($this->keys[$x])) { return false; } // only alphavalues are allowed, OCI_ASSOC
				$this->result[$r[$keycolumn]][$this->keys[$x]] = $r[$this->keys[$x]];
			}
			$this->rcount++;
		}	
 return true;
	}

$this->mode = $dbmode;
        if ($this->con) {
			$ok = $this->disconnect();
			if(!$ok) { return false; }
		}
		$this->oracon = null;
		$this->orast = null;
        $this->con = false;
		$this->rcount = -1;
		$this->keys = array();
        $this->result = array();
		$this->db_host = $host;
		$this->db_user = $user;
		$this->db_pass = $pass;
        $this->db_name = $dbname;
		$this->result = array();
        $ok = $this->connect();  // $this->con==true on success
		return $ok;
    }

function setDatabase($dbname) {
    	// change database
    	global $dbmode;
		
		$this->mode = $dbmode;	
        if ($this->con) {
			$ok = $this->disconnect();
			if(!$ok) return false;
		}
        $this->con = false;
		$this->rcount = -1;
		$this->keys = array();
        $this->result = array();
        $this->db_name = $dbname;
        $ok = $this->connect();  // $this->con==true on success
		return $ok;
    }

function disconnect() {

$ok = false;
		if ($this->mode=='mysql') {
			$ok = mysql_close();
		}
		else if ($this->mode=='mssql') {
			$ok = mssql_close();
		}
		else if ($this->mode=='oracle') {
			if ($this->orast) { $ok = oci_free_statement($this->orast); }
			if ($ok && $this->oracon) { $ok = oci_close($this->oracon); }
		}
		$this->con = !$ok;
		return $ok;
	}
	
    function connect() {

if ($this->db_host=='' || $this->db_user=='' || $this->db_pass=='' || $this->db_name=='') { return false; }
		
		$ok = false;
		if ($this->mode=='mysql') {
	        $dbh = mysql_connect($this->db_host,$this->db_user,$this->db_pass);
	        if ($dbh) { $ok = mysql_select_db($this->db_name, $dbh); }
		}
		else if ($this->mode=='oracle') {
			$this->oracon = oci_connect($this->db_user,$this->db_pass,$this->db_host.'/'.$this->db_name);
        	if (!$this->oracon) { $this->errDetails(); }
			else { $ok = true; }
		}
		else if ($this->mode=='mssql') {
	        $dbh = mssql_connect($this->db_host,$this->db_user,$this->db_pass);
	        if ($dbh) { $ok = mssql_select_db($this->db_name, $dbh); }
		}
		$this->con = $ok;
		return $ok;
    }

function tableExists($table) {

if (!$this->con || $table=='') { return false; }    	
    	$this->result = array();  // resets
    	$this->rcount = -1;
    	$this->keys = array();
		
		$ok = false;

if ($this->mode=='mysql') {
    		$q = 'SHOW TABLES FROM '.$this->db_name.' LIKE "'.$table.'"';
	        $q = mysql_query($q);
	        if ($q) {
	            if (mysql_num_rows($q)==1) { $ok = true; }
	        }    		
		}
		else if ($this->mode=='oracle') {
			$q = "select * from all_objects where object_type in ('TABLE','VIEW') and object_name = '$table'";
			$this->orast = oci_parse($this->oracon, $q);
			if (!$this->orast) { $this->errDetails($this->oracon); }
			else if (!oci_execute($this->orast)) { $this->errDetails($this->orast); }
			else { $ok = true; }
		}
		else if ($this->mode=='mssql') {
			if (strpos($table, 'INNER JOIN')!=-1) { return true;}
			$table = str_replace(array('[',']'), '', $table);
			$q = "SELECT name FROM sys.sysobjects WHERE name='$table' AND OBJECTPROPERTY(id,'IsUserTable')=1";
	        $q = mssql_query($q);
	        if ($q) {
	            if (mssql_num_rows($q)==1) { $ok = true; }
			}
		}
        return $ok;
    }

// ____________________________________________________________________________

// ____________________________________________________________________________
	// MS SQL UCS-2 text read/write,
	// declare fields in model: convert(varbinary(2*size), fieldname) as fieldname
	
	function columnsToConvert($table) {
		// result as [ sColname=>bDoConv ]
		global $fields;
		
		if (isset($fields[$table])) {
			$cols = array_keys($fields[$table]);
		}
		else {
			return array('itemkey'=>false, 'value'=>true);
		}
		$ret = array();
		foreach ($cols as $k=>$f) {
			if ($fields[$table][$f]['type']=='ntext') {
				$ret[$f] = true;
			}
			else {
				$ret[$f] = false;
			}
		}
		return $ret;
	}

/* not used */
	
	function columnsDeclare($table) {
		// result as [ sColname=>declaration ]
		global $fields;
		
		if (isset($fields[$table])) {
			$cols = array_keys($fields[$table]);
		}
		else {
			return array('itemkey'=>'itemkey', 'value'=>'convert(varbinary(200), value) as value');
		}
		$ret = array();
		foreach ($cols as $k=>$f) {
			$ret[$f] = $fields[$table][$f]['colname'];
		}
		return $ret;		
	}
	
	function encUCS2($str) {
 	$ucs2 = iconv('UTF-8', 'UCS-2LE', $str);
		// converting UCS-2 string into "binary" hexadecimal form
		$arr = unpack('H*hex', $ucs2);
		return "0x{$arr['hex']}"; // include it in sql statement without quotes		
	}
	
	// iconv('UCS-2LE', 'UTF-8', $str) to decode
		
	function ucs2html($str) {
		$str=trim($str);
		$len=strlen($str);
		$html='';
		for($i=0;$i<$len;$i+=2)
		$html.='&#'.hexdec(dechex(ord($str[$i+1])).sprintf("%02s",dechex(ord($str[$i])))).';';
		return($html);
	}
	
	function hex2utf($UtfCharInHex) {
		$OutputChar = "";
		$UtfCharInDec = hexdec($UtfCharInHex);
		if($UtfCharInDec < 128) $OutputChar .= chr($UtfCharInDec);
		else if($UtfCharInDec < 2048) $OutputChar .= chr(($UtfCharInDec >> 6) + 192) . chr(($UtfCharInDec & 63) + 128);
		else if($UtfCharInDec < 65536) $OutputChar .= chr(($UtfCharInDec >> 12) + 224) . chr((($UtfCharInDec >> 6) & 63) + 128) . chr(($UtfCharInDec & 63) + 128);
		else if($UtfCharInDec < 2097152) $OutputChar .= chr($UtfCharInDec >> 18 + 240) . chr((($UtfCharInDec >> 12) & 63) + 128) . chr(($UtfCharInDec >> 6) & 63 + 128) . chr($UtfCharInDec & 63 + 128);
		return $OutputChar;
	}	
	
}

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.