From c3f571c01404553ae5c6bf9e7e7041407702c514 Mon Sep 17 00:00:00 2001 From: konrad Date: Sat, 15 Mar 2008 18:49:47 +0000 Subject: [PATCH] fixed DB issues: *foreign keys and indexes for MySQL *correct column type for ticketIDs git-svn-id: https://silmor.de/svn/softmagic/smoke/trunk@115 6e3c4bff-ac9f-4ac1-96c5-d2ea494d3e33 --- www/inc/db/db.php | 16 ++++++++++++++-- www/inc/db/db_mysql.php | 35 +++++++++++++++++++++++++++++++---- www/inc/db/db_scheme.php | 14 +++++++------- 3 files changed, 52 insertions(+), 13 deletions(-) diff --git a/www/inc/db/db.php b/www/inc/db/db.php index 66b7bc7..cb3886e 100644 --- a/www/inc/db/db.php +++ b/www/inc/db/db.php @@ -86,7 +86,7 @@ abstract class DbEngine /**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) + protected function columnFlag($flag,$col) { if($flag=="primarykey")return "PRIMARY KEY"; if($flag=="notnull")return "NOT NULL"; @@ -129,13 +129,20 @@ abstract class DbEngine $ret.=$this->dataType($def[0])." "; //get flags for($i=0;$icolumnFlag($def[$i])." "; + $ret.=$this->columnFlag($def[$i],$col)." "; } } + $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*/ + protected function createTableExtras($tablename,$table) + { + return ""; + } + /**creates primary key statement for sqlCreateTable*/ protected function sqlCreateTablePrimaryKey(array $cols) { @@ -277,6 +284,11 @@ abstract class DbEngine } //insert some defaults $this->insert("config",array("ckey"=>"MagicSmokeVersion","cval"=>$this->needVersion())); + $this->insert("config",array("ckey"=>"ValidVouchers","cval"=>"10 20 25 50")); + $this->insert("config",array("ckey"=>"OrderStop","cval"=>"24")); + $this->insert("config",array("ckey"=>"SaleStop","cval"=>"0")); + $this->insert("config",array("ckey"=>"TicketIDChars","cval"=>"10")); + $this->insert("config",array("ckey"=>"VoucherIDChars","cval"=>"10")); $this->insert("host",array("hostname"=>"_any")); $this->insert("host",array("hostname"=>"_anon")); $this->insert("host",array("hostname"=>"_online")); diff --git a/www/inc/db/db_mysql.php b/www/inc/db/db_mysql.php index 8b23ce8..e233f89 100644 --- a/www/inc/db/db_mysql.php +++ b/www/inc/db/db_mysql.php @@ -12,6 +12,8 @@ class MysqlEngine extends DbEngine private $dbname=""; private $engine="InnoDB"; private $charset="utf8"; + //used for table creation to work around mysql bugs + private $tableappend; /**initialize driver*/ public function __construct($server,$user,$pass) @@ -39,6 +41,12 @@ class MysqlEngine extends DbEngine $this->engine=$e; } + /**set the default charset for tables and connections*/ + public function setCharacterSet($c) + { + $this->charset=$c; + } + public function tryConnect() { //connect @@ -89,7 +97,15 @@ class MysqlEngine extends DbEngine protected function createTable($tn,$t) { - return mysqli_query($this->dbhdl,$this->sqlCreateTable($tn,$t)." engine=".$this->engine); + $this->tableappend=""; + $sql=$this->sqlCreateTable($tn,$t)." engine=".$this->engine." default charset=".$this->charset; + print("
\n$sql\n
\n"); + return mysqli_query($this->dbhdl,$sql); + } + + protected function createTableExtras($tablename,$table) + { + return $this->tableappend; } protected function tableName($tn) @@ -116,12 +132,23 @@ class MysqlEngine extends DbEngine return parent::dataType($type); } - protected function columnFlag($flag) + protected function columnFlag($flag,$col) { //FIXME: currently MySQL does not mark columns for indexing, since the syntax is somewhat different --> this needs to be fixed! - if($flag=="index")return ""; + 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); + return parent::columnFlag($flag,$col); } public function insert($table,array $values) diff --git a/www/inc/db/db_scheme.php b/www/inc/db/db_scheme.php index 77d5bbd..5838628 100644 --- a/www/inc/db/db_scheme.php +++ b/www/inc/db/db_scheme.php @@ -23,7 +23,7 @@ class DbScheme { $this->scheme["users"]=array( "uname" => array("string:64","primarykey"), "passwd" => array("string","notnull"), - //more detailed data that can be displayed to customers + //more detailed data that can be displayed to admins "description" => array("text") ); $this->scheme["userrole"]=array( @@ -98,7 +98,7 @@ class DbScheme { ); //orders by customers $this->scheme["order"]=array( - "orderid" => array("seq32","primarykey"), + "orderid" => array("int32","primarykey"), //customer "customerid" => array("int32","foreignkey:customer:customerid"), //seller (_online for web forms) @@ -119,7 +119,7 @@ class DbScheme { ); //tickets $this->scheme["ticket"]=array( - "ticketid" => array("seq64","primarykey"), + "ticketid" => array("string:32","primarykey"), "eventid" => array("int32","foreignkey:event:eventid"), //initially a copy from event, can be adjusted by seller "price" => array("int32","notnull"), @@ -129,15 +129,15 @@ class DbScheme { "reservedby" => array("string:64","foreignkey:users:uname"), "reservetimeout" => array("int32"), //sold to someone (may be NULL for direct sales or reserves) - "oderid" => array("int32","foreignkey:orders:orderid") + "orderid" => array("int32","foreignkey:order:orderid") ); //vouchers and re-imbursments $this->scheme["voucher"]=array( - //a 16char code (code39: case-insensitive letters+digits) for the voucher) - "voucherid" => array("string:16","primarykey"), + //a 8-32 char code (code39: case-insensitive letters+digits) for the voucher) + "voucherid" => array("string:32","primarykey"), //if ordered: order-info "price" => array("int32","notnull"), - "oderid" => array("int32","foreignkey:orders:orderid"), + "orderid" => array("int32","foreignkey:order:orderid"), //unix-timestamp of original sales date/time "salestime" => array("int32","notnull"), //remaining value in cents -- 1.7.2.5