From 59d83e816a00b8d50515d1aa2b5de84484452425 Mon Sep 17 00:00:00 2001 From: konrad Date: Sat, 22 May 2010 12:41:23 +0000 Subject: [PATCH] added support for PostgreSQL git-svn-id: https://silmor.de/svn/softmagic/pack/trunk@466 6e3c4bff-ac9f-4ac1-96c5-d2ea494d3e33 --- phpbase/autoload.php | 1 + phpbase/db.php | 13 +-- phpbase/db_mysql.php | 7 +- phpbase/db_pgsql.php | 280 ++++++++++++++++++++++++++++++++++++++++++++++++++ 4 files changed, 286 insertions(+), 15 deletions(-) create mode 100644 phpbase/db_pgsql.php diff --git a/phpbase/autoload.php b/phpbase/autoload.php index fc84f0b..a7e52de 100644 --- a/phpbase/autoload.php +++ b/phpbase/autoload.php @@ -12,6 +12,7 @@ $d=dirname(__FILE__); $AUTOCLASS["DbEngine"]=$d.'/db.php'; $AUTOCLASS["MysqlEngine"]=$d.'/db_mysql.php'; +$AUTOCLASS["PGsqlEngine"]=$d.'/db_pgsql.php'; $AUTOCLASS["WobTable"]= $d."/table.php"; $AUTOCLASS["WobSchemaBase"]=$d."/schema.php"; $AUTOCLASS["WobTransactionBase"]=$d."/transaction.php"; diff --git a/phpbase/db.php b/phpbase/db.php index 7898940..b0235b9 100644 --- a/phpbase/db.php +++ b/phpbase/db.php @@ -116,7 +116,7 @@ abstract class DbEngine if($tpa[0]=="defaultint"){ if(count($tpa)<2) return "DEFAULT NULL"; - return "DEFAULT $tpa[1]"; + return "DEFAULT ".$this->escapeInt($tpa[1]); } if($tpa[0]=="defaultstr"){ if(count($tpa)<2) @@ -583,17 +583,6 @@ abstract class DbEngine fclose($fd); $this->unlockDB(); } - - - /**creates a log entry, the array must supply values for the moneylog table; time, username are generated automatically, the log entry is delivered via the second argument*/ - public function mkLog(array $vals,$logtext) - { - global $session; - $vals["logtime"]=time(); - $vals["uname"]=$session->getUser(); - $vals["log"]=$logtext; - $this->insert("moneylog",$vals); - } }; ?> \ No newline at end of file diff --git a/phpbase/db_mysql.php b/phpbase/db_mysql.php index 3e4e07b..9b1af51 100644 --- a/phpbase/db_mysql.php +++ b/phpbase/db_mysql.php @@ -62,9 +62,10 @@ class MysqlEngine extends DbEngine $this->dbhdl=mysqli_connect($this->server,$this->user,$this->pass,$this->dbname); if($this->dbhdl===false) die("Unable to connect to database system. Giving up."); - //select Unicode; TODO: fix it to be configurable - if(mysqli_query($this->dbhdl,"SET NAMES 'utf8'")===false) - die("Cannot set DB character set to utf-8, aborting."); + //select Unicode or whatever charset is configured + $cs=$this->escapeString($this->charset); + if(mysqli_query($this->dbhdl,"SET NAMES ".$cs)===false) + die("Cannot set DB character set to ".$cs.", aborting."); //make sure the DB is transaction safe if(mysqli_query($this->dbhdl,"SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE")===false) die("Cannot make this database transaction safe, aborting"); diff --git a/phpbase/db_pgsql.php b/phpbase/db_pgsql.php new file mode 100644 index 0000000..9eac6b1 --- /dev/null +++ b/phpbase/db_pgsql.php @@ -0,0 +1,280 @@ +connstr=$connstring; + } + + /**make sure it shuts down*/ + public function __destruct() + { + //fail on the side of caution: roll back unless already released + if($this->intrans) + @$this->rollbackTransaction(); + } + + /**set a table-name prefix for the database*/ + public function setPrefix($pre) + { + $this->prefix=$pre; + } + + /**set the default charset for tables and connections*/ + public function setCharacterSet($c) + { + $this->charset=$c; + } + + public function tryConnect() + { + //connect + $this->dbhdl=pg_connect($this->connstr); + if($this->dbhdl===false) + die("Unable to connect to database system. Giving up."); + //select Unicode; TODO: fix it to be configurable + if(pg_set_client_encoding($this->charset)!=0) + die("Cannot set character set to ".$this->charset.", aborting."); + //make sure the DB is transaction safe + if(@pg_query($this->dbhdl,"SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE")===false) + die("Cannot make this database transaction safe, aborting"); + } + + public function hasTable($tnm) + { + global $dbScheme; + if(!$dbScheme->hasTable($tnm))return false; + $res=@pg_query($this->dbhdl,"select * from ".$this->tableName($tnm)." where 1=2"); + if($res===false)return false; + pg_free_result($res); + return true; + } + public function beginTransaction() + { + $res=@pg_query($this->dbhdl,$this->sqlBeginTransaction()); + if($res===false) + $this->intrans=false; + else{ + $this->intrans=pg_result_status($res)==PGSQL_COMMAND_OK; + pg_free_result($res); + } + return $this->intrans; + } + + public function commitTransaction() + { + if(!$this->intrans)return false; + //otherwise commit + $this->intrans=false; + $res=@pg_query($this->dbhdl,$this->sqlCommitTransaction()); + if($res!==false){ + pg_free_result($res); + return true; + }else + return false; + } + + public function rollbackTransaction() + { + $this->intrans=false; + //don't ask, just do (also catches implicit transactions) + $res=@pg_query($this->dbhdl,$this->sqlRollbackTransaction()); + if($res!==false)pg_free_result($res); + } + + protected function lockDB($wl) + { + global $dbScheme; + if(!$this->intrans)$this->beginTransaction(); + $req="LOCK TABLE "; + $i=0; + foreach($dbScheme->backupTables() as $tab){ + if($i)$req.=","; + $i++; + $req.=$this->tableName($tab); + } + $req.=" IN ACCESS EXCLUSIVE"; + $res=@pg_query($this->dbhdl,$req); + if($res!==false)pg_free_result($res); + } + + public function sqlBeginTransaction(){return "BEGIN";} + + public function sqlCommitTransaction(){return "COMMIT";} + + public function sqlRollbackTransaction(){return "ROLLBACK";} + + + public function select($table,$cols,$where="",$orderby="") + { + //actual select + $query=$this->sqlSelect($table,$cols,$where,$orderby); + if($this->transmode)$query.=" FOR UPDATE"; + $res=@pg_query($this->dbhdl,$query); + if($res===false)return false; + //get column names and types + $nf=pg_num_fields($res); + $fnames=array(); + global $dbScheme; + for($i=0;$i<$nf;$i++) + $fnames[$i]=pg_field_name($res,$i); + //convert data + $nr=pg_num_rows($res); + $ret=array(); + for($i=0;$i<$nr;$i++){ + $row=pg_fetch_row($res); + $crw=array(); + for($j=0;$j<$nf;$j++){ + $fn=$fnames[$j]; + if($dbScheme->isIntColumn($table,$fn))$crw[$j]=$row[$j]+0;else + if($dbScheme->isBlobColumn($table,$fn))$crw[$j]=pg_unescape_bytea($row[$j]);else + if($dbScheme->isBoolColumn($table,$fn))$crw[$j]=$row[$j]=='t'; + else $crw[$j]=$row[$j]; + $crw[$fn]=$crw[$j]; + } + $ret[]=$crw; + } + //free up resources and return + pg_free_result($res); + return $ret; + } + + protected function createTable($tn,$t) + { + $sql=$this->sqlCreateTable($tn,$t); + $res=@pg_query($this->dbhdl,$sql); + if($res!==false){ + pg_free_result($res); + return true; + }else + return false; + } + + protected function sqlCreateTable($tn,$t) + { + //use standard SQL + $ret=parent::sqlCreateTable($tn,$t); + //append indizes + foreach($t as $col=>$def){ + if(in_array("index",$def)){ + $ftn=$this->tableName($tn); + $ret.=";CREATE INDEX ".$ftn."_".$col."_idx ON " + .$ftn."(".$col.")"; + } + } + return $ret; + } + + protected function tableName($tn) + { + return $this->prefix.$tn; + } + + protected function dataType($type) + { + if($type=="int32"||$type=="enum"||$type=="enum32")return "INTEGER"; + if($type=="int64"||$type=="enum64")return "BIGINT"; + if($type=="seq32")return "SERIAL"; + if($type=="seq64")return "BIGSERIAL"; + if($type=="text")return "TEXT"; + if($type=="blob")return "BYTEA"; //max.4GB of blob + $tpa=explode(":",$type); + if($tpa[0]=="string"){ + if(isset($tpa[1])) + return "VARCHAR(".$tpa[1].")"; + else + return "VARCHAR"; + } + //fallback to SQL standard + return parent::dataType($type); + } + + protected function columnFlag($flag,$col,$table) + { + //PostgreSQL does not mark columns for indexing directly + if($flag=="index"){ + return ""; + } + //fallback to SQL standard + return parent::columnFlag($flag,$col,$table); + } + + public function insert($table,array $values) + { + $this->transmode=true; + $res=@pg_query($this->dbhdl,$this->sqlInsert($table,$values)); + if($res===false)return false; + pg_free_result($res); + global $dbScheme; + $seq=$dbScheme->hasSequence($table); + if($seq!==false){ + if(isset($values[$seq]))return $values[$seq]; + $res=@pg_query($this->dbhdl,"select lastval()"); + if(pg_num_rows($res)>0){ + $row=pg_fetch_array($res); + $ret=$row[0]; + }else{ + $ret=true; + } + pg_free_result($res); + return $ret; + }else{ + return true; + } + } + + public function update($table,array $values,$where) + { + $this->transmode=true; + $res=@pg_query($this->dbhdl,$this->sqlUpdate($table,$values,$where)); + if($res!==false){ + $ret=pg_affected_rows($res); + pg_free_result($res); + return $ret; + }else return false; + } + + public function deleteRows($table,$where) + { + $this->transmode=true; + $res=@pg_query($this->dbhdl,$this->sqlDelete($table,$where)); + if($res!==false){ + $ret=pg_affected_rows($res); + pg_free_result($res); + return $ret; + }else return false; + } + + public function lastError() + { + return pg_last_error($this->dbhdl); + } + + /**escapes strings; it uses mysqli_escape_string and encloses the value in ''*/ + public function escapeString($s) + { + if($s === false||$s===null) return "NULL"; + return "'".pg_escape_string($this->dbhdl,$s)."'"; + } + + /**escapes blobs; it uses mysqli_escape_string and encloses the value in '' - blobs are binary strings in MySQL*/ + public function escapeBlob($s) + { + if($s === false||$s===null) return "NULL"; + return "'".pg_escape_bytea($this->dbhdl,$s)."'"; + } +}; + +?> \ No newline at end of file -- 1.7.2.5