From 15fddad98a3fa4cade243f0ac3e97e4500aa8385 Mon Sep 17 00:00:00 2001 From: konrad Date: Sun, 25 Apr 2010 09:59:37 +0000 Subject: [PATCH] move DB driver to pack; enable DB transactions in WOC-transactions git-svn-id: https://silmor.de/svn/softmagic/pack/trunk@438 6e3c4bff-ac9f-4ac1-96c5-d2ea494d3e33 --- doc/woc.html | 7 +- phpbase/autoload.php | 3 + phpbase/db.php | 598 +++++++++++++++++++++++++++++++++++++++++++++++ phpbase/db_mysql.php | 278 ++++++++++++++++++++++ phpbase/transaction.php | 4 +- woc/htmlout.cpp | 2 + woc/phpout.cpp | 3 +- woc/processor.cpp | 7 + woc/processor.h | 10 +- 9 files changed, 904 insertions(+), 8 deletions(-) create mode 100644 phpbase/db.php create mode 100644 phpbase/db_mysql.php diff --git a/doc/woc.html b/doc/woc.html index c0046ea..e7a0f3a 100644 --- a/doc/woc.html +++ b/doc/woc.html @@ -85,10 +85,10 @@ The Database Abstraction Layer is the servers lower bound towards the database. One of the first statements is the manifest for the database schema:
-<DataBase instance="dbInst" schema="dbSchema" version="00.01"/>
+<DataBase instance="dbInst" schema="dbSchema" version="00.01" defaultUpdating="no"/>
 
-The attribute "instance" tells woc what variable name it should use for the database connection instance, likewise "schema" tells woc which variable represents the database schema (usually an instance of WobSchema, which is generated by woc). The "version" attribute tells woc which database schema version is described in this wolf file.

+The attribute "instance" tells woc what variable name it should use for the database connection instance, likewise "schema" tells woc which variable represents the database schema (usually an instance of WobSchema, which is generated by woc). The "version" attribute tells woc which database schema version is described in this wolf file. The "defaultUpdating" attribute tells woc whether transactions are normally assumed to write to the database.

For each database table woc needs a description of that table: @@ -223,7 +223,7 @@ Classes can be documented by adding Doc subtags. Properties can be documented by

Transactions

-<Transaction name="GetTicket">
+<Transaction name="GetTicket" updating="yes">
   <Input>
     <Var name="ticketid" type="astring"/>
     <Call lang="php" method="getTicketFunction"/>
@@ -241,6 +241,7 @@ Transaction attributes:
 AttributeDescription
 namethe name of the transaction
 mode(optional, default=checked) can be used to require less restrictive privileges, see below for values
+updatingoptional, tells woc whether the transaction is to be considered writing to the database - the default is given in the global "Database" tag.
 

Transaction modes: diff --git a/phpbase/autoload.php b/phpbase/autoload.php index be145de..a5d9cc3 100644 --- a/phpbase/autoload.php +++ b/phpbase/autoload.php @@ -10,11 +10,14 @@ // $d=dirname(__FILE__); +$AUTOCLASS["DbEngine"]=$d.'/db.php'; +$AUTOCLASS["MysqlEngine"]=$d.'/db_mysql.php'; $AUTOCLASS["WobTable"]= $d."/table.php"; $AUTOCLASS["WobSchemaBase"]=$d."/schema.php"; $AUTOCLASS["WobTransactionBase"]=$d."/transaction.php"; $AUTOCLASS["WobXmlException"]=$d."/exception.php"; $AUTOCLASS["WObject"]=$d."/object.php"; +unset($d); function __autoload($cname) { diff --git a/phpbase/db.php b/phpbase/db.php new file mode 100644 index 0000000..ea4a663 --- /dev/null +++ b/phpbase/db.php @@ -0,0 +1,598 @@ +adminuser=$u; + $this->adminpass=$p; + } + + /**check admin credentials*/ + public function checkAdmin() + { + global $_SERVER; + if(!$this->canAdministrate())return false; + if(!isset($_SERVER["PHP_AUTH_USER"]) || !isset($_SERVER["PHP_AUTH_PW"])){ + return false; + } + return $_SERVER["PHP_AUTH_USER"]==$this->adminuser && $_SERVER["PHP_AUTH_PW"]==$this->adminpass; + } + + /**returns whether a passcode is known and admin.php may be used*/ + public function canAdministrate() + { + return $this->adminpass!==false && $this->adminuser!==false; + } + + /**returns the version of the DB layout that is required by this version of Magic Smoke*/ + public function needVersion() + { + global $dbScheme; + return $dbScheme->version(); + } + + /**returns whether the table exists; must be implemented by driver*/ + public abstract function hasTable($tablename); + + /**begins a transaction; must be implemented by driver; use sqlBeginTransaction to create the SQL statement!*/ + public abstract function beginTransaction(); + + /**ends a transaction successfully; must be implemented by driver; returns true on success; use sqlCommitTransaction to create the SQL statement!*/ + public abstract function commitTransaction(); + + /**ends a transaction with a rollback; must be implemented by driver; returns true on success; use sqlRollbackTransaction to create the SQL statement!*/ + public abstract function rollbackTransaction(); + + /**locks the database - only used by the backup functions; if necessary should also start a transaction; default just starts a transaction*/ + protected function lockDB($writelock){$this->beginTransaction();} + + /**unlocks the database - only used by the backup functions; if necessary should also commit a transaction; default just commits*/ + protected function unlockDB(){$this->commitTransaction();} + + /**gets some data from the database; $table is the name of the table, $cols is the list of columns to return or "*" for all, $where is the where clause of the SQL-statement, $orderby may contain additional ORDER BY or GROUP BY clauses; returns array of rows, which are in *_fetch_array format; returns false on error; use sqlSelect to create the SQL statement!; make sure that NULL values are returned as PHP value null (most DB drivers already do this)*/ + public abstract function select($table,$cols,$where="",$orderby=""); + + /**insert values into a table; returns false on failure, the new primary key if a sequence was set, true otherwise; use sqlInsert to create the SQL statement!*/ + public abstract function insert($table,array $values); + + /**update database values; returns how many rows have been changed or false for failure; use sqlUpdate to create the SQL statement!*/ + public abstract function update($table,array $values,$where); + + /**delete database values; returns the amount of rows deleted or false if an error occurred; use sqlDelete to create the SQL statement!*/ + public abstract function deleteRows($table,$where); + + /**creates a table; the argument is an array of the form "col-name" => array("col-type", "flags"...); use sqlCreateTable() etc. to create the actual statement*/ + protected abstract function createTable($tablename,$table); + + /**transform an internally used table name to the actual table name in the DB; the default implementation returns exactly what it gets*/ + protected function tableName($tname){return $tname;} + + /**returns the correct type name for the required abstract data type; + types that must be understood are: int32 (INTEGER), int64 (LONG INTEGER), seq32 (auto-incrementing int), seq64, bool (boolean), string:$length (text up to 255 chars, length is optional, default is 255; VARCHAR($length)), text (unlimited text)*/ + protected function dataType($type) + { + if($type=="int32"||$type=="enum"||$type=="enum32")return "INTEGER"; + if($type=="int64"||$type=="enum64")return "LONG INTEGER"; + if($type=="bool")return "BOOLEAN"; + $tpa=explode(":",$type); + if($tpa[0]=="string"){ + if(isset($tpa[1])) + return "VARCHAR(".$tpa[1].")"; + else + return "VARCHAR(255)"; + } + return false; + } + + /**returns the correct name/coding of a flag: + primarykey, notnull, unique (implies notnull), foreignkey:$table:$col, defaultint:$val, defaultstr:$val, index*/ + protected function columnFlag($flag,$col,$table) + { + if($flag=="primarykey")return "PRIMARY KEY"; + if($flag=="null")return "NULL"; + if($flag=="notnull")return "NOT NULL"; + if($flag=="unique")return "NOT NULL UNIQUE"; + if($flag=="index")return "INDEX"; + $tpa=explode(":",$flag); + if($tpa[0]=="foreignkey"){ + if(count($tpa)<3) + return false; + return "REFERENCES ".$this->tableName($tpa[1])."($tpa[2])"; + } + if($tpa[0]=="defaultint"){ + if(count($tpa)<2) + return "DEFAULT NULL"; + return "DEFAULT $tpa[1]"; + } + if($tpa[0]=="defaultstr"){ + if(count($tpa)<2) + return "DEFAULT NULL"; + return "DEFAULT ".$this->escapeString($tpa[1]); + } + if($tpa[0]=="defaultbool"){ + if(count($tpa)<2) + return "DEFAULT NULL"; + return "DEFAULT ".$this->escapeBool($tpa[1]); + } + if($tpa[0]=="default"){ + if(count($tpa)<2) + return "DEFAULT NULL"; + return "DEFAULT ".$this->escapeColumn($table,$col,$tpa[1]); + } + } + + /**creates a SQL92 statement for creating a table; overwrite this to implement DB specific syntax*/ + protected function sqlCreateTable($tablename,$table) + { + $ret="CREATE TABLE ".$this->tableName($tablename)." ("; + $cm=""; + reset($table); + while(list($col,$def)=each($table)){ + $ret.=$cm;$cm=","; + //check whether this is a special column + if(substr($col,0,1)==":"){ + if($col==":primarykey")$ret.=$this->sqlCreateTablePrimaryKey($def); + else die("Unknown special column ".$col." while creating table ".$tablename); + }else{ + //column name + $ret.=$col." "; + //get type + $ret.=$this->dataType($def[0])." "; + //get flags + for($i=0;$icolumnFlag($def[$i],$col,$tablename)." "; + } + } + $ret.=$this->createTableExtras($tablename,$table); + $ret.=")"; + return $ret; + } + + /**This function can be used to amend the column definitions of a table; if overwritten it must return a string; overwrite this to implement DB specific syntax*/ + protected function createTableExtras($tablename,$table) + { + return ""; + } + + /**creates primary key statement for sqlCreateTable; overwrite this to implement DB specific syntax*/ + protected function sqlCreateTablePrimaryKey(array $cols) + { + $ret="PRIMARY KEY("; + for($i=0;$i0)$ret.=","; + $ret.=$cols[$i]; + } + $ret.=")"; + return $ret; + } + + /**creates a SQL92 statement for selects; overwrite this to implement DB specific syntax; the concrete DB implementation should append "for update" to the select statement if $this->transmode is true and the DB supports it*/ + public function sqlSelect($table,$cols,$where,$orderby) + { + $query="SELECT $cols FROM ".$this->tableName($table); + if($where!="")$query.=" WHERE ".$where; + if($orderby!="")$query.=" ".$orderby; + return $query; + } + + /**creates a SQL92 statement for inserts; overwrite this to implement DB specific syntax*/ + protected function sqlInsert($table,array $values) + { + global $dbScheme; + $ret="INSERT INTO ".$this->tableName($table)." ("; + reset($values); + $cm=""; + $val=") VALUES ("; + while(list($k,$v)=each($values)){ + //make sure the column exists, ignore the riff-raff + if(!$dbScheme->tableHasColumn($table,$k))continue; + $ret.=$cm;$val.=$cm;$cm=","; + //append column name + $ret.=$k; + //append value + if($dbScheme->isIntColumn($table,$k)) + $val.=$this->escapeInt($v); + else + if($dbScheme->isStringColumn($table,$k)) + $val.=$this->escapeString($v); + else + if($dbScheme->isBlobColumn($table,$k)) + $val.=$this->escapeBlob($v); + else + if($dbScheme->isBoolColumn($table,$k)) + $val.=$this->escapeBool($v); + else + //don't know how to escape it... + $val.="NULL"; + } + $ret.=$val.")"; +// print $ret; + return $ret; + } + + /**creates a SQL92 statement for deletes; overwrite this to implement DB specific syntax*/ + protected function sqlDelete($table,$where) + { + return "DELETE FROM ".$this->tableName($table)." WHERE ".$where; + } + + /**creates a SQL92 statement for updates; overwrite this to implement DB specific syntax*/ + protected function sqlUpdate($table,array $values,$where) + { + global $dbScheme; + $ret="UPDATE ".$this->tableName($table)." SET "; + reset($values); + $cm=""; + while(list($k,$v)=each($values)){ + //make sure the column exists, ignore the riff-raff + if(!$dbScheme->tableHasColumn($table,$k))continue; + $ret.=$cm;$cm=","; + //append column name + $ret.=$k."="; + //append value + if($dbScheme->isIntColumn($table,$k)) + $ret.=$this->escapeInt($v); + else + if($dbScheme->isStringColumn($table,$k)) + $ret.=$this->escapeString($v); + else + if($dbScheme->isBlobColumn($table,$k)) + $ret.=$this->escapeBlob($v); + else + if($dbScheme->isBoolColumn($table,$k)) + $ret.=$this->escapeBool($v); + else + //don't know how to escape it... + $ret.="NULL"; + } + $ret.=" WHERE ".$where; + //print $ret; + return $ret; + } + + /**returns the SQL92 statement for beginning a transaction; overwrite this to implement DB specific syntax*/ + public function sqlBeginTransaction() + { + return "BEGIN TRANSACTION"; + } + + /**returns the SQL92 statement for committing a transaction; overwrite this to implement DB specific syntax*/ + public function sqlCommitTransaction() + { + return "COMMIT TRANSACTION"; + } + + /**returns the SQL92 statement for rolling a transaction back; overwrite this to implement DB specific syntax*/ + public function sqlRollbackTransaction() + { + return "ROLLBACK TRANSACTION"; + } + + /**escapes integers; the default implementation just makes sure it is an int (false and null are translated to NULL)*/ + public function escapeInt($i) + { + if($i === false || $i === null)return "NULL"; + return round($i + 0); + } + + /**escapes a list of integers; uses escapeInt for each element; automatically adds parentheses*/ + public function escapeIntList(array $il) + { + $r="("; + $b=false; + foreach($il as $i){ + if($b)$r.=","; + else $b=true; + $r.=$this->escapeInt($i); + } + $r.=")"; + return $r; + } + + /**escapes strings; the default uses addslashes and encloses the value in ''; it is recommended to overwrite this with the proper escaping procedure for the target DB (false and null are translated to NULL)*/ + public function escapeString($s) + { + if($s === false || $s === null) return "NULL"; + return "'".addslashes($s)."'"; + } + + /**escapes blobs; the default uses addslashes and encloses the value in ''; it is recommended to overwrite this with the proper escaping procedure for the target DB*/ + public function escapeBlob($s) + { + if($s === false || $s===null) return "NULL"; + return "'".addslashes($s)."'"; + } + + /**escapes a boolean value; the default translates 0, '0', 'f', 'false', 'n', 'no', false to FALSE and numerics !=0, 't', 'true', 'y', 'yes', true to TRUE; any other value (incl. the PHP constant null) is translated to NULL*/ + public function escapeBool($s) + { + if($s===null)return "NULL"; + if(is_numeric($s)){ + if(($s+0)!=0)return "TRUE"; + else return "FALSE"; + } + if($s===false)return "FALSE"; + if($s===true)return "TRUE"; + switch(strtolower($s)){ + case "t":case "true":case "yes":case "y": + return "TRUE"; + case "f":case "false":case "n":case "no": + return "FALSE"; + default: + return "NULL"; + } + } + + /**generic escape routine: queries the schema for the correct escape mechanism and then returns the appropriately escaped value*/ + public function escapeColumn($table,$col,$val) + { + global $dbScheme; + if($dbScheme->isIntColumn($table,$col))return $this->escapeInt($val); + if($dbScheme->isStringColumn($table,$col))return $this->escapeString($val); + if($dbScheme->isBlobColumn($table,$col))return $this->escapeBlob($val); + if($dbScheme->isBoolColumn($table,$col))return $this->escapeBool($val); + //fallback: NULL + return "NULL"; + } + + /**returns a configuration setting*/ + public function getConfig($key) + { + $mar=$this->select("config","cval","ckey=".$this->escapeString($key)); + if(count($mar)>0)return $mar[0][0]; + return false; + } + + /**sets a config setting*/ + public function setConfig($key,$val) + { + $this->beginTransaction(); + $mar=$this->select("config","cval","ckey=".$this->escapeString($key)); + if(count($mar)>0)$this->update("config",array("cval"=>$val),"ckey=".$this->escapeString($key)); + else $this->insert("config",array("ckey"=>$key,"cval"=>$val)); + $this->commitTransaction(); + } + + /**tries to find out whether the connected DB version is usable*/ + public function canUseDb() + { + if(!$this->hasTable("config")) + return false; + return $this->getConfig("MagicSmokeVersion")==$this->needVersion(); + } + + /**creates the database, used by admin.php only!!*/ + public function createDb() + { + global $dbScheme; + $this->beginTransaction(); + //iterate DB schema and create tables + $tabs=$dbScheme->tableNames(); + for($i=0;$icreateTable($tabs[$i],$dbScheme->tableDefinition($tabs[$i]))){ + print("DB Error while creating ".$tabs[$i].": ".$this->lastError()."

\n"); + print("Last statement was: ".$this->sqlCreateTable($tabs[$i],$dbScheme->tableDefinition($tabs[$i]))."

\n"); + $this->rollbackTransaction(); + die("Unable to create database."); + } + //insert defaults + foreach($dbScheme->tableDefaults($tabs[$i]) as $def){ + $this->insert($tabs[$i],$def); + } + } + //close transaction + $this->commitTransaction(); + } + + /**shows how the database would be created, used by admin.php only!!*/ + public function showCreateDb() + { + global $dbScheme; + print("

Database Creation SQL Script

\n
\n");
+		print(htmlentities($this->sqlBeginTransaction()).";\n");
+		//iterate DB schema and create tables
+		$tabs=$dbScheme->tableNames();
+		for($i=0;$isqlCreateTable($tabs[$i],$dbScheme->tableDefinition($tabs[$i]))).";\n");
+			//insert defaults
+			foreach($dbScheme->tableDefaults($tabs[$i]) as $def){
+				print(htmlentities($this->sqlInsert($tabs[$i],$def)).";\n");
+			}
+		}
+		//close transaction
+		print(htmlentities($this->sqlCommitTransaction()).";\n
\n"); + } + + /**returns the error string of the last operation*/ + public abstract function lastError(); + + /**returns whether the result value is NULL; the default interprets only the special value null as NULL*/ + public function isNull($val) + { + if($val===null)return true; + else return false; + } + + /**returns whether the DB driver currently believes to be in RW transaction mode*/ + public function transactionIsUpdating() + { + return $this->transmode; + } + + /**set the transaction mode: true if it is an updating transaction*/ + public function setTransactionUpdating($tm) + { + $this->transmode=$tm!=false; + } + + /**helper: encode backup data for transport*/ + private function escapeBackup($tab,$col,$val) + { + //check for NULL + if($this->isNull($val))return "NULL NULL"; + //get type + global $dbScheme; + if($dbScheme->isIntColumn($tab,$col))return "int ".($val+0); + if($dbScheme->isBoolColumn($tab,$col)){ + if($val)return "bool 1"; + else return "bool 0"; + } + //string and blob are base64 encoded + return "str ".base64_encode($val); + } + + /**dump a backup (returned as string)*/ + public function dumpBackup() + { + global $dbScheme; + //make sure nobody else can disturb us (read lock) + $this->lockDB(false); + //dump basic stuff + $ret="startbackup\n"; + $ret.="backupversion 0\n"; + $ret.="dbversion ".$dbScheme->version()."\n"; + //go through backup tables + foreach($dbScheme->backupTables() as $tab){ + $ret.="table ".$tab."\n"; + //get columns + $cols=$dbScheme->tableColumns($tab); + //go through rows + $res=$this->select($tab,"*","1=1"); + foreach($res as $row){ + foreach($cols as $col){ + $val=$row[$col]; + $ret.="value $col ".$this->escapeBackup($tab,$col,$val)."\n"; + } + $ret.="insert\n"; + } + } + $ret.="endofbackup\n"; + //release lock & commit + $this->unlockDB(); + + return $ret; + } + + /**helper: decode backup data from transport format*/ + private function unescapeBackup($fmt,$val) + { + switch($fmt){ + case "NULL":return null; + case "int":return $val+0; + case "str":return base64_decode($val); + case "bool":return $val+0; + default: + print("Warning: encountered unknown data encoding \"".htmlspecialchars($fmt)."\". Using NULL instead.
\n"); + return null; + } + } + + /**helper: inserts data from backup*/ + private function backupInsert($table,$data,$overwrite) + { + global $dbScheme; + //get primary keys + $pk=$dbScheme->primaryKeyColumns($table); + //scan whether data is existent + $pks="";$q=""; + foreach($pk as $p){ + if($pks!="")$pks.=","; + $pks.=$p; + if($q!="")$q.=" AND "; +// print("pk $p "); + $q.=$p."=".$this->escapeColumn($table,$p,$data[$p]); + } +// print("pk=$pks q=$q
"); + $res=$this->select($table,$pks,$q); + if(count($res)>0){ + if($overwrite){ + $b=$this->update($table,$data,$q); + print("update data: ".($b===false?"failed":"success")."
\n"); + }else{ + print("ignoring existing row
\n"); + } + }else{ + $b=$this->insert($table,$data); + print("insert data: ".($b===false?"failed":"success")."
\n"); + } + } + + /**called from admin.php: restore a backup*/ + public function restoreData($file,$overwrite) + { + //prep + $this->lockDB(true); + $data=array(); + $table=""; + //open file + $fd=fopen($file,"r"); + while(!feof($fd)){ + $line=trim(fgets($fd)); + if($line=="")continue; + $cmd=explode(" ",$line); + switch($cmd[0]){ + case "startbackup": + case "backupversion": + case "dbversion": + /*currently ignored, maybe we do something with it later*/ + break; + case "endofbackup": + print("Reached End of Backup.
\n"); + break 2; + case "table": + $data=array(); + $table=$cmd[1]; + if(!$this->hasTable($table)){ + print("Switching to non-existing table ".htmlspecialchars($table)." - ignoring data that follows.
\n"); + $table=""; + }else + print("Switching to table ".htmlspecialchars($table)."
\n"); + break; + case "value": + $data[$cmd[1]]=$this->unescapeBackup($cmd[2],$cmd[3]); + break; + case "insert": + if($table==""){ + print("Warning: insert on non-existing table, ignoring data.
\n"); + }else{ + $this->backupInsert($table,$data,$overwrite); + } + break; + default: + print("Warning: unknown statement \"".htmlspecialchars($cmd[0])."\" in backup file. Ignoring it.
\n"); + break; + } + } + 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 new file mode 100644 index 0000000..3e4e07b --- /dev/null +++ b/phpbase/db_mysql.php @@ -0,0 +1,278 @@ +user=$user; + $this->server=$server; + $this->pass=$pass; + } + + /**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 name of the database to be used*/ + public function setDbName($dbn) + { + $this->dbname=$dbn; + } + + /**set the name of the storage engine to be used on DB creation*/ + public function setStorageEngine($e) + { + $this->engine=$e; + } + + /**set the default charset for tables and connections*/ + public function setCharacterSet($c) + { + $this->charset=$c; + } + + public function tryConnect() + { + //connect + $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."); + //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"); + } + + public function hasTable($tnm) + { + global $dbScheme; + if(!$dbScheme->hasTable($tnm))return false; + $res=mysqli_query($this->dbhdl,"select * from ".$this->tableName($tnm)." where 1=2"); + if($res===false)return false; + mysqli_free_result($res); + return true; + } + public function beginTransaction() + { + $this->intrans=mysqli_query($this->dbhdl,$this->sqlBeginTransaction()); + return $this->intrans; + } + + public function commitTransaction() + { + if(!$this->intrans)return false; + //otherwise commit + $this->intrans=false; + return mysqli_query($this->dbhdl,$this->sqlCommitTransaction()); + } + + public function rollbackTransaction() + { + $this->intrans=false; + //don't ask, just do (also catches implicit transactions) + return mysqli_query($this->dbhdl,$this->sqlRollbackTransaction()); + } + + protected function lockDB($wl) + { + global $dbScheme; + $req="SET autocommit = 0 ; LOCK TABLES "; + $i=0; + foreach($dbScheme->backupTables() as $tab){ + if($i)$req.=","; + $i++; + $req.=$this->tableName($tab); + if($wl)$req.=" WRITE"; + else $req.=" READ"; + } + mysqli_query($this->dbhdl,$req); + } + + protected function unlockDB() + { + mysqli_query($this->dbhdl,"UNLOCK TABLES"); + } + + public function sqlBeginTransaction(){return "BEGIN";} + + public function sqlCommitTransaction(){return "COMMIT";} + + public function sqlRollbackTransaction(){return "ROLLBACK";} + + + public function select($table,$cols,$where="",$orderby="") + { + $query=$this->sqlSelect($table,$cols,$where,$orderby); + if($this->transmode)$query.=" FOR UPDATE"; + $res=mysqli_query($this->dbhdl,$query); + if($res===false)return false; + $nr=mysqli_num_rows($res); + $ret=array(); + for($i=0;$i<$nr;$i++){ + $ret[]=mysqli_fetch_array($res,MYSQLI_BOTH); + } + mysqli_free_result($res); + return $ret; + } + + protected function createTable($tn,$t) + { + $sql=$this->sqlCreateTable($tn,$t); + return mysqli_query($this->dbhdl,$sql); + } + + protected function sqlCreateTable($tn,$t) + { + $this->tableappend=""; + $sql=parent::sqlCreateTable($tn,$t)." engine=".$this->engine." default charset=".$this->charset; +// print("
\n$sql\n
\n"); + return $sql; + } + + protected function createTableExtras($tablename,$table) + { + //warning: this is rather dangerous, but safe at the moment since there is only + // one user of this function + return $this->tableappend; + } + + protected function tableName($tn) + { + return $this->prefix.$tn; + } + + protected function dataType($type) + { + if($type=="int32"||$type=="enum"||$type=="enum32")return "INT"; + if($type=="int64"||$type=="enum64")return "BIGINT"; + if($type=="seq32")return "INT AUTO_INCREMENT"; + if($type=="seq64")return "BIGINT AUTO_INCREMENT"; + if($type=="text")return "TEXT"; + if($type=="blob")return "MEDIUMBLOB"; //max.16MB of blob + $tpa=explode(":",$type); + if($tpa[0]=="string"){ + if(isset($tpa[1])) + return "VARCHAR(".$tpa[1].")"; + else + return "VARCHAR(255)"; + } + //fallback to SQL standard + return parent::dataType($type); + } + + protected function columnFlag($flag,$col,$table) + { + //MySQL does not mark columns for indexing directly, instead the index is appended to the table definition + if($flag=="index"){ + $this->tableappend.=", INDEX(".$col.")"; + return ""; + } + //MySQL::InnoDB is buggy, it accepts references only as appendix + $tpa=explode(":",$flag); + if($tpa[0]=="foreignkey"){ + if(count($tpa)<3) + return false; + $this->tableappend.=", FOREIGN KEY (".$col.") REFERENCES ".$this->tableName($tpa[1])."($tpa[2])"; + return ""; + } + //fallback to SQL standard + return parent::columnFlag($flag,$col,$table); + } + + public function insert($table,array $values) + { + $this->transmode=true; + $res=mysqli_query($this->dbhdl,$this->sqlInsert($table,$values)); + if($res===false)return false; + global $dbScheme; + $seq=$dbScheme->hasSequence($table); + if($seq!==false){ + if(isset($values[$seq]))return $values[$seq]; + $res=mysqli_query($this->dbhdl,"select LAST_INSERT_ID()"); + if(mysqli_num_rows($res)>0){ + $row=mysqli_fetch_array($res); + $ret=$row[0]; + }else{ + $ret=true; + } + mysqli_free_result($res); + return $ret; + }else{ + return true; + } + } + + public function update($table,array $values,$where) + { + $this->transmode=true; + $res=mysqli_query($this->dbhdl,$this->sqlUpdate($table,$values,$where)); + if($res!==false)return mysqli_affected_rows($this->dbhdl); + else return false; + } + + public function deleteRows($table,$where) + { + $this->transmode=true; + $b=mysqli_query($this->dbhdl,$this->sqlDelete($table,$where)); +// echo mysqli_error($this->dbhdl); + if($b!==false)return mysqli_affected_rows($this->dbhdl); + else return false; + } + + public function lastError() + { + return mysqli_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 "'".mysqli_real_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 "'".mysqli_real_escape_string($this->dbhdl,$s)."'"; + } + + /**escapes booleans, overwrites the orignal to use "0" and "1" instead of "FALSE" and "TRUE"*/ + public function escapeBool($b) + { + $r=DbEngine::escapeBool($b); + if($r=="TRUE")return "1"; + if($r=="FALSE")return "0"; + return $r; + } +}; + +?> \ No newline at end of file diff --git a/phpbase/transaction.php b/phpbase/transaction.php index bfc1915..1c01d02 100644 --- a/phpbase/transaction.php +++ b/phpbase/transaction.php @@ -95,8 +95,8 @@ class WobTransactionBase { $this->abortWithError(tr("Transaction not implemented.")); } - /**stub: overwrite this to implement a real transaction start action (eg. sending the DB a "BEGIN TRANSACTION" statement)*/ - protected function startTransaction(){} + /**stub: overwrite this to implement a real transaction start action (eg. sending the DB a "BEGIN TRANSACTION" statement); the $updating parameter is set to true for transactions marked as updating*/ + protected function startTransaction($updating){} /**stub: overwrite this to implement a real transaction commit action (eg. sending the DB a "COMMIT TRANSACTION" statement)*/ protected function commitTransaction(){} /**stub: overwrite this to implement a real transaction abort action (eg. sending the DB a "ROLLBACK TRANSACTION" statement)*/ diff --git a/woc/htmlout.cpp b/woc/htmlout.cpp index 3ef5503..60ef9bf 100644 --- a/woc/htmlout.cpp +++ b/woc/htmlout.cpp @@ -55,6 +55,7 @@ WocHtmlOut::WocHtmlOut(QDomElement&el) inf+="Database Instance Object: "+woc->dbInst()+"
"; inf+="Database Schema Object: "+woc->dbSchema()+"
"; + inf+="Database default access mode: "+QString(woc->dbUpdatingDefault()?"updating":"reading")+"
"; inf+="Database Schema Version: "+woc->dbVersion()+"

"; m_index.write(inf.toAscii()); @@ -339,6 +340,7 @@ void WocHtmlOut::newTransaction(const WocTransaction&trn) default:hcd+="Ooops. Unknown Mode.";break; } hcd+="

\n"; + hcd+="

Database access mode: "+QString(trn.isDbUpdating()?"updating":"reading")+"

\n"; //docu for(int i=0;i\n"; diff --git a/woc/phpout.cpp b/woc/phpout.cpp index c29fc78..b7be4dc 100644 --- a/woc/phpout.cpp +++ b/woc/phpout.cpp @@ -798,7 +798,8 @@ void WocPHPServerOut::newTransaction(const WocTransaction&trn) tf.write(code.toAscii()); //request handler: - code="public function handleRequest(){\n\t$this->startTransaction();\n"; + code="public function handleRequest(){\n"; + code+="\t$this->startTransaction("+QString(trn.isDbUpdating()?"true":"false")+");\n"; //security handling switch(trn.authMode()){ diff --git a/woc/processor.cpp b/woc/processor.cpp index 8da690c..e8184be 100644 --- a/woc/processor.cpp +++ b/woc/processor.cpp @@ -51,6 +51,7 @@ WocProcessor::WocProcessor() m_svnExe="svn"; m_dbInst="dbInst"; m_dbSchema="dbSchema"; + m_dbUpd=false; m_error=false; m_projname="WobProject"; @@ -140,6 +141,8 @@ bool WocProcessor::processFile(QString fn) m_dbSchema=el.attribute("schema","dbSchema"); if(el.hasAttribute("version")) m_dbVer=el.attribute("version"); + if(el.hasAttribute("defaultUpdating")) + m_dbUpd=str2bool(el.attribute("defaultUpdating")); QList nl=elementsByTagName(el,"AuditTables"); for(int i=0;idbUpdatingDefault(); //input tag QList nl=elementsByTagName(root,"Input"); for(int i=0;i m_call; QList >m_input,m_output; @@ -434,6 +437,9 @@ class WocProcessor:public QObject /**returns the qualified names of all privileges*/ QStringList privilegeNames()const; + + /**returns the default for the database "updating" attribute*/ + bool dbUpdatingDefault()const{return m_dbUpd;} signals: void sfinalize(); void newClass(const WocClass&); @@ -446,7 +452,7 @@ class WocProcessor:public QObject QString m_svnTarget,m_svnRev,m_svnExe,m_svnRoot,m_svnUrl; QString m_dbInst,m_dbSchema,m_dbVer; QStringList m_docstrings; - bool m_error; + bool m_error,m_dbUpd; QList m_tables; QList m_classes; -- 1.7.2.5