From c74be2d676afe1304f499a94175183cd76d01d33 Mon Sep 17 00:00:00 2001 From: konrad Date: Sun, 21 Oct 2007 12:05:38 +0000 Subject: [PATCH] database fix git-svn-id: https://silmor.de/svn/softmagic/smoke/trunk@56 6e3c4bff-ac9f-4ac1-96c5-d2ea494d3e33 --- www/inc/db.php | 8 ++++---- www/inc/db_mysql.php | 25 ++++++++++++++++++++++--- www/inc/db_scheme.php | 28 +++++++++++++++++++++------- www/inc/event.php | 12 ++++++------ 4 files changed, 53 insertions(+), 20 deletions(-) diff --git a/www/inc/db.php b/www/inc/db.php index 09727b8..db5cb1e 100644 --- a/www/inc/db.php +++ b/www/inc/db.php @@ -53,7 +53,7 @@ abstract class DbEngine /**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; returns array of rows, which are in *_fetch_array format; returns false on error*/ public abstract function select($table,$cols,$where); - /**insert values into a table*/ + /**insert values into a table; returns false on failure, the new primary key if a sequence was set, true otherwise*/ public abstract function insert($table,array $values); /**update database values*/ @@ -69,7 +69,7 @@ abstract class DbEngine 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), autoint32 (auto-incrementing int), autoint64, string:$length (text up to 255 chars, length is optional, default is 255; VARCHAR($length)), text (unlimited text)*/ + types that must be understood are: int32 (INTEGER), int64 (LONG INTEGER), seq32 (auto-incrementing int), seq64, 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")return "INTEGER"; @@ -96,7 +96,7 @@ abstract class DbEngine if($tpa[0]=="foreignkey"){ if(count($tpa)<3) return false; - return "REFERENCES $tpa[1]($tpa[2])"; + return "REFERENCES ".$this->tableName($tpa[1])."($tpa[2])"; } if($tpa[0]=="defaultint"){ if(count($tpa)<2) @@ -236,7 +236,7 @@ abstract class DbEngine $tabs=$dbScheme->tableNames(); for($i=0;$icreateTable($tabs[$i],$dbScheme->tableDefinition($tabs[$i]))){ - print("DB Error: ".$this->lastError()); + print("DB Error while creating ".$tabs[$i].": ".$this->lastError()); $this->rollbackTransaction(); die("Unable to create database."); } diff --git a/www/inc/db_mysql.php b/www/inc/db_mysql.php index 56e3141..842a9a4 100644 --- a/www/inc/db_mysql.php +++ b/www/inc/db_mysql.php @@ -98,8 +98,8 @@ class MysqlEngine extends DbEngine { if($type=="int32")return "INT"; if($type=="int64")return "BIGINT"; - if($type=="autoint32")return "INT AUTO_INCREMENT"; - if($type=="autoint64")return "BIGINT AUTO_INCREMENT"; + if($type=="seq32")return "INT AUTO_INCREMENT"; + if($type=="seq64")return "BIGINT AUTO_INCREMENT"; if($type=="text")return "TEXT"; $tpa=explode(":",$type); if($tpa[0]=="string"){ @@ -116,11 +116,30 @@ class MysqlEngine extends DbEngine { //FIXME: currently MySQL does not mark columns for indexing, since the syntax is somewhat different --> this needs to be fixed! if($flag=="index")return ""; + //fallback to SQL standard + return parent::columnFlag($flag); } public function insert($table,array $values) { - return mysql_query($this->sqlInsert($table,$values)); + $res=mysql_query($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=mysql_query("select LAST_INSERT_ID()"); + if(mysql_num_rows($res)>0){ + $row=mysql_fetch_array($res); + $ret=$row[0]; + }else{ + $ret=true; + } + mysql_free_result($res); + return $ret; + }else{ + return true; + } } public function update($table,array $values,$where) diff --git a/www/inc/db_scheme.php b/www/inc/db_scheme.php index 3b4e3f4..a2fabcc 100644 --- a/www/inc/db_scheme.php +++ b/www/inc/db_scheme.php @@ -51,7 +51,7 @@ class DbScheme { ); //event $this->scheme["event"]=array( - "eventid" => array("int32","primarykey"), + "eventid" => array("seq32","primarykey"), //display data "title" => array("string","notnull"), "artist" => array("string","notnull"), @@ -69,7 +69,7 @@ class DbScheme { ); //customer $this->scheme["customer"]=array( - "customerid" => array("int32","primarykey"), + "customerid" => array("seq32","primarykey"), //contact data "name" => array("string",notnull), "address" => array("string"), @@ -81,7 +81,7 @@ class DbScheme { ); //orders by customers $this->scheme["order"]=array( - "orderid" => array("int32","primarykey"), + "orderid" => array("seq32","primarykey"), //customer "customerid" => array("int32","foreignkey:customer:customerid"), //seller (_online for web forms) @@ -102,7 +102,7 @@ class DbScheme { ); //tickets $this->scheme["ticket"]=array( - "ticketid" => array("int64","primarykey"), + "ticketid" => array("seq64","primarykey"), "eventid" => array("int32","foreignkey:event:eventid"), //initially a copy from event, can be adjusted by seller "price" => array("int32","notnull"), @@ -136,14 +136,16 @@ class DbScheme { ); //buying tickets $this->scheme["cart_ticket"]=array( - "ctid" => array("int64","primarykey"), + "ctid" => array("seq64","primarykey"), + "cartid" => array("string:32","notnull","foreignkey:cart:cartid"), //tickets in the cart "eventid" => array("int32","foreignkey:event:eventid"), "amount" => array("int32","notnull"), ); //buying vouchers $this->scheme["cart_voucher"]=array( - "cvid" => array("int64","primarykey"), + "cvid" => array("seq64","primarykey"), + "cartid" => array("string:32","notnull","foreignkey:cart:cartid"), //voucher value "value" => array("int32","notnull") ); @@ -197,13 +199,25 @@ class DbScheme { return false; $tpa=explode(":",$this->scheme[$tab][$col][0]); switch($tpa[0]){ - case "int32":case "autoint32":case "int64":case "autoint64": + case "int32":case "seq32":case "int64":case "seq64": return true; default: return false; } } + /**returns the sequence column name if the table has a sequence, false otherwise*/ + public function hasSequence($tab) + { + if(!isset($this->scheme[$tab])) + return false; + foreach($this->scheme[$tab] as $cl => $def){ + if($def[0] == "seq32" || $def[0] == "seq64") + return $cl; + } + return false; + } + /**returns true if the given column is of a string type*/ public function isStringColumn($tab,$col) { diff --git a/www/inc/event.php b/www/inc/event.php index ccd3186..2cbec6e 100644 --- a/www/inc/event.php +++ b/www/inc/event.php @@ -253,13 +253,13 @@ function setEventXml($xmldata) else $data["cancelreason"]=false; if($eventid=="new"){ - //create new ID - $res=$db->select("event","max(eventid)",""); - if(count($res)==0)$eventid=0; - else $eventid=$res[0][0]+1; //create event - $data["eventid"]=$eventid; - $db->insert("event",$data); + $eventid=$db->insert("event",$data); + if($eventid===false){ + header("X-MagicSmoke-Status: Error"); + echo "Error accessing database."; + return; + } }else{ //check ID $eventid=$eventid+0; -- 1.7.2.5