From e2b5d5fc3b775c8fbb898d0c498e90f483977be5 Mon Sep 17 00:00:00 2001 From: konrad Date: Wed, 19 Aug 2009 12:34:39 +0000 Subject: [PATCH] *implemented database upgrade *some minor schema fixes git-svn-id: https://silmor.de/svn/softmagic/smoke/trunk@337 6e3c4bff-ac9f-4ac1-96c5-d2ea494d3e33 --- wob/event.wolf | 2 +- wob/order.wolf | 8 +- wob/user.wolf | 2 + www/admin.php | 18 ++ www/config.php.template | 9 + www/inc/db/autoload.php | 1 + www/inc/db/db.php | 2 +- www/inc/db/db_mysql.php | 2 +- www/inc/db/dbupgrade.php | 382 +++++++++++++++++++++++++++++++++++++++++++ www/inc/machine/session.php | 10 +- 10 files changed, 427 insertions(+), 9 deletions(-) create mode 100644 www/inc/db/dbupgrade.php diff --git a/wob/event.wolf b/wob/event.wolf index aead5b1..6431aef 100644 --- a/wob/event.wolf +++ b/wob/event.wolf @@ -28,7 +28,7 @@ - + diff --git a/wob/order.wolf b/wob/order.wolf index 6507c4a..60c5ffd 100644 --- a/wob/order.wolf +++ b/wob/order.wolf @@ -16,7 +16,9 @@
- + + +
@@ -25,7 +27,7 @@ - + @@ -78,7 +80,7 @@ - +
diff --git a/wob/user.wolf b/wob/user.wolf index e2545e9..555f34d 100644 --- a/wob/user.wolf +++ b/wob/user.wolf @@ -44,6 +44,8 @@ + +
diff --git a/www/admin.php b/www/admin.php index 5f53c01..ba0deeb 100644 --- a/www/admin.php +++ b/www/admin.php @@ -89,6 +89,24 @@ if(isset($_POST["restorenow"])){ } ?> +

Restore from Old Database (MagicSmoke 1.x)

+ + + +Enter "upgrade" here: + +

+Warning: don't attempt to upgrade data unless you really mean it! Upgrade deletes all data from the new database (MagicSmoke2) and overwrites it with the old data (MagicSmoke1). This re-uses the credentials from the configuration.

+ +Upgrading Database...\n"; + DBUpgrade::upgrade(); +} ?> + + +If you want to upgrade from an old database version, please uncomment the $olddb-configuration in config.php.

+ +

Checking for Admin Users

diff --git a/www/config.php.template b/www/config.php.template index 68916e9..36dc7cd 100644 --- a/www/config.php.template +++ b/www/config.php.template @@ -33,6 +33,15 @@ $db->setCharacterSet("utf8"); //change the passcode before using this on a production system!!! $db->setAdminPassCode("Admin","SmokeInMyEye"); +//use this if you want to upgrade data from an old (MagicSmoke 1.x) DB instance +/* +$olddb = new MysqlEngine("localhost","smoke",""); +$olddb->setDbName("smoke"); +$olddb->setPrefix("smoke_"); +$olddb->setStorageEngine("InnoDB"); +$olddb->setCharacterSet("utf8"); +*/ + //////////// //Dedicated Client Configuration diff --git a/www/inc/db/autoload.php b/www/inc/db/autoload.php index f33f429..0ed67ca 100644 --- a/www/inc/db/autoload.php +++ b/www/inc/db/autoload.php @@ -13,6 +13,7 @@ $AUTOCLASS["DbEngine"]='./inc/db/db.php'; $AUTOCLASS["MysqlEngine"]='./inc/db/db_mysql.php'; $AUTOCLASS["BarcodeTable"]='./inc/db/barcodetable.php'; +$AUTOCLASS["DBUpgrade"]='./inc/db/dbupgrade.php'; include('./inc/db/db_scheme.php'); ?> \ No newline at end of file diff --git a/www/inc/db/db.php b/www/inc/db/db.php index 555b3f5..5131687 100644 --- a/www/inc/db/db.php +++ b/www/inc/db/db.php @@ -61,7 +61,7 @@ abstract class DbEngine 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=""); + 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); diff --git a/www/inc/db/db_mysql.php b/www/inc/db/db_mysql.php index 50e4f22..83d13a8 100644 --- a/www/inc/db/db_mysql.php +++ b/www/inc/db/db_mysql.php @@ -112,7 +112,7 @@ class MysqlEngine extends DbEngine public function sqlRollbackTransaction(){return "ROLLBACK";} - public function select($table,$cols,$where,$orderby="") + public function select($table,$cols,$where="",$orderby="") { $query=$this->sqlSelect($table,$cols,$where,$orderby); $res=mysqli_query($this->dbhdl,$query); diff --git a/www/inc/db/dbupgrade.php b/www/inc/db/dbupgrade.php new file mode 100644 index 0000000..98ec1fd --- /dev/null +++ b/www/inc/db/dbupgrade.php @@ -0,0 +1,382 @@ +, (C) 2009 +// +// Copyright: See README/COPYING files that come with this distribution +// +// + +/**performs an upgrade from a 1.x database to a current 2.x database*/ +class DBUpgrade +{ + /**performs the upgrade*/ + static public function upgrade() + { + global $olddb,$db; + $olddb->tryConnect(); + + echo "Start.
Checking DB Version...\n"; + if(!$olddb->hasTable("config")) + die("DB does not have MagicSmoke tables."); + if(substr($olddb->getConfig("MagicSmokeVersion"),0,3)!="00.") + die("This is not a MagicSmoke 1.x DB."); + + self::cleanout(); + + self::copytable("config"); + $db->update("config",array("cval"=>$db->needVersion()),"ckey='MagicSmokeVersion'"); + + self::copytable("template"); + + self::copytable("host"); + self::copytable("user","users"); + self::copytable("userhost","userhosts"); + self::correctusers(); + + //customer/address/contact + self::copycustomer(); + + //room/event/ + self::copytable("room"); + self::copyevents(); + + //orders + self::copyorders(); + + //audit + self::copyaudit(); + + self::fprint("
Done.

\n"); + } + + /**helper: copies a table 1:1*/ + static private function copytable($table,$otable="") + { + if($otable=="")$otable=$table; + self::fprint("
Copying OLD:$otable to NEW:$table...\n"); + global $dbScheme,$olddb,$db; + $res=$olddb->select($otable,"*"); + foreach($res as $row){ + self::insert($table,$row); + } + } + + /**helper: inserts or updates data, depending on whether the target already exists*/ + static private function insert($table,$row) + { + global $db,$dbScheme; + //does it exist? + $w=""; + $pk=$dbScheme->primaryKeyColumns($table); + foreach($pk as $p){ + if($w!="")$w.=" AND "; + $w.=$p."=".$db->escapeColumn($table,$p,$row[$p]); + } + $res=$db->select($table,"*",$w); + //no:insert, yes:update + if(count($res)<1) + return $db->insert($table,$row); + else + return $db->update($table,$row,$w); + } + + /**helper: resets the target database to the state it was in when freshly installed*/ + static private function cleanout() + { + global $db,$dbScheme; + self::fprint("
Deleting Target DB...\n"); + foreach($dbScheme->tableNames() as $t) + $db->deleteRows($t,"1=1"); + self::fprint("
Resetting presets...\n"); + foreach($dbScheme->tableNames() as $t) + foreach($dbScheme->tableDefaults($t) as $row) + $db->insert($t,$row); + } + + /**helper: works on the freshly copied target DB, corrects changes in user and host storage*/ + static private function correctusers() + { + global $db,$olddb; + //delete obsolete pseudohosts + $db->deleteRows("userhost","host='_anon' OR host='_online'"); + $db->deleteRows("host","hostname='_anon' OR hostname='_online'"); + //hash user passwords + $res=$db->select("user","uname,passwd"); + foreach($res as $row){ + $db->update("user",array("passwd"=>self::hashPasswd($row["passwd"])), + "uname=".$db->escapeColumn("user","passwd",$row["uname"])); + } + //hash host passwords + $res=$db->select("host","hostname,hostkey"); + foreach($res as $row){ + if($db->isNull($row["hostkey"]))continue; + $db->update("host",array("hostkey"=>self::hashPasswd($row["hostkey"])), + "hostname=".$db->escapeColumn("host","hostname",$row["hostname"])); + } + //create admins + $res=$olddb->select("userrole","uname","role='_admin'"); + foreach($res as $row) + $db->insert("userrole",array("uname"=>$row["uname"],"role"=>"_admin")); + } + + /**helper: converts a plain text password into salted SHA1 form*/ + static private function hashPasswd($p) + { + $s=getSalt(); + return $s." ".sha1($s.$p); + } + + /**helper: copies customer data and attempts to properly parse and convert it on the way*/ + static private function copycustomer() + { + self::fprint("
Converting Customer Data...\n"); + global $olddb,$db,$dbScheme; + $cust=$olddb->select("customer","*"); + $webu=$olddb->select("webuser","*"); + //create dummy contact type + $res=$db->select("contacttype","contacttypeid","contacttype='anything'"); + if(count($res)>0)$dct=$res[0]["contacttypeid"]; + else $dct=$db->insert("contacttype",array("contacttype"=>"anything")); + //copy base data + $titles=array("Mr.","Mr","Mister","Ms","Ms.","Miss","Mrs.","Mrs", + "Dame","Lady","Lord","Dr.","Dr","Sir","Professor","Prof.", + "Herr","Frau","Firma","Fa.","Familie","Fam."); + foreach($cust as $c){ + $ci=array("customerid"=>$c["customerid"],"comments"=>$c["comments"], + "title"=>"","name"=>$c["name"],"firstname"=>""); + //parse name + $ns=explode(" ",$c["name"]); + while(in_array($ns[0],$titles)) + $ci["title"].=array_shift($ns); + $nm=implode(" ",$ns); + $ns=explode(",",$nm); + if(count($ns)==2){ + $ci["name"]=$ns[0]; + $ci["firstname"]=$ns[1]; + }else $ci["name"]=$nm; + self::insert("customer",$ci); + //copy/parse address + self::mkaddress($c["customerid"],$c["address"]); + //copy contact data (linewise) + $cds=explode("\n",$c["contact"]); + foreach($cds as $cd){ + $cd=trim($cd); + if($cd=="")continue; + $db->insert("contact",array("customerid"=>$c["customerid"],"contacttypeid"=>$dct,"contact"=>$cd)); + } + } + //merge in webuser data + foreach($webu as $wu){ + $db->update("customer",array("email"=>$wu["email"],"passwd"=>$wu["passwd"]), + "customerid=".$wu["customerid"]); + } + } + + /**helper for customer and order data: create an address, or return false if it was empty*/ + static private function mkaddress($custid,$addr,$name="") + { + global $db,$olddb,$dbScheme; + if(trim($addr)!=""){ + //get name + if($name==""){ + $res=$olddb->select("customer","name","customerid=$custid"); + $name=$res[0]["name"]; + } + //parse address + $adr=explode("\n",$addr); + $ai=array("customerid"=>$custid,"name"=>$name,"addr1"=>"", + "city"=>"","zipcode"=>"","countryid"=>"de"); + do{ + if(count($adr)==0)break; + $l=trim(array_pop($adr)); + //eliminate trailing empty lines + if($l=="")continue; + //zip code alone? + if(ereg("^[0-9]+$",$l)){ + $ai["zipcode"]=$l; + continue; + } + //city plus zipcode? or only city? + $x=explode(" ",$l); + if(ereg("^[0-9]+$",$x[0])){ + $ai["zipcode"]=$x[0]; + $l=trim(substr($l,strlen($x[0]))); + } + $ai["city"]=$l; + break; + }while(true); + if(count($adr)>0)$ai["addr1"]=array_pop($adr); + if(count($adr)>0)$ai["addr2"]=implode("; ",$adr); + return $db->insert("address",$ai); + }else return false; + } + + /**helper for copyevents: create or retrieve artist*/ + static private function mkartist($a) + { + global $db; + $res=$db->select("artist","artistid","artistname=".$db->escapeColumn("artist","artistname",$a)); + if(count($res)>0)return $res[0]["artistid"]; + return $db->insert("artist",array("artistname"=>$a,"description"=>"","comment"=>"")); + } + + /**helper: copies and converts event data, creates artists on the fly*/ + static private function copyevents() + { + self::fprint("
Converting Event data...\n"); + global $db,$olddb,$dbScheme; + //get default price category + $res=$db->select("pricecategory","*","name='normal'"); + if(count($res)<0) + die("internal error: price category 'normal' does not exist, something is wrong with the configuration"); + $prc=$res[0]["pricecategoryid"]; + //copy actual events + $evts=$olddb->select("event","*"); + foreach($evts as $evt){ + //create artist + $evt["artistid"]=self::mkartist($evt["artist"]); + unset($evt["artist"]); + //create price + $db->insert("eventprice",array("eventid"=>$evt["eventid"],"pricecategoryid"=>$prc, + "maxavailable"=>$evt["capacity"],"price"=>$evt["defaultprice"], + "flags"=>"")); + unset($evt["defaultprice"]); + //convert and copy data + $evt["comment"]=$evt["description"]; + $evt["flags"]="";$evt["tax"]=0; + $evt["iscancelled"]=($evt["cancelreason"]!=""); + self::insert("event",$evt); + } + } + + /**helper: copies tickets, vouchers, orders*/ + static private function copyorders() + { + global $db,$olddb,$dbScheme; + //get default price category + $res=$db->select("pricecategory","*","name='normal'"); + if(count($res)<0) + die("internal error: price category 'normal' does not exist, something is wrong with the configuration"); + $prc=$res[0]["pricecategoryid"]; + + //shipping + self::fprint("
Converting Shipping setup...\n"); + //make sure anyshipping flag exists + $res=$db->select("config","*","ckey='Flag anyshipping'"); + if(count($res)<1){ + $db->insert("config",array("ckey"=>"Flag anyshipping","cval"=>"user can use any shipping type")); + } + //copy + $ships=$olddb->select("shipping","*"); + foreach($ships as $ship){ + $flag=""; + if(!$ship["canuseweb"])$flag="-web"; + if(!$ship["canallusers"])$flag.=" +anyshipping"; + unset($ship["canuseweb"]);unset($ship["canallusers"]); + $ship["flags"]=trim($flag); + self::insert("shipping",$ship); + } + + //orders + self::fprint("
Converting Order data...\n"); + $orders=$olddb->select("order","*"); + foreach($orders as $ord){ + //process invoiceaddr + $res=$db->select("address","addressid","customerid=".$ord["customerid"]); + if(count($res)>0)$ord["invoiceaddress"]=$res[0]["addressid"]; + //process deliveryaddr + if(!$db->isNull($ord["deliveryaddress"])){ + $a=self::mkaddress($ord["customerid"],$ord["deliveryaddress"]); + if($a===false)unset($ord["deliveryaddress"]); + else $ord["deliveryaddress"]=$a; + }else + unset($ord["deliveryaddress"]); + //create + self::insert("order",$ord); + } + + //tickets + self::fprint("
Converting Ticket data...\n"); + $ticks=$olddb->select("ticket","*"); + foreach($ticks as $tick){ + //insert new stuff + $tick["pricecategoryid"]=$prc; + //correct status constants that have changed + if($tick["status"]==0x301)$tick["status"]=0x311; + if($tick["status"]==0x302)$tick["status"]=0x312; + //delete obsolete stuff + unset($tick["reservedby"]); + unset($tick["reservetimeout"]); + //finally insert + self::insert("ticket",$tick); + } + + //vouchers + self::copytable("voucher"); + } + + private static function copyaudit() + { + global $db,$olddb,$dbScheme; + //audit data... + self::fprint("
Converting audit log data...\n"); + $logs=$olddb->select("moneylog","*","","logid"); + foreach($logs as $log){ + //order + if(!$db->isNull($log["orderid"])){ + $res=$db->select("order","*","orderid=".$log["orderid"]); + if(count($res)<1)continue; + $ao=array( + "orderid"=>$log["orderid"], + "customerid"=>$res[0]["customerid"], + "soldby"=>$res[0]["soldby"], + "invoiceaddress"=>$res[0]["invoiceaddress"], + "deliveryaddress"=>$res[0]["deliveryaddress"], + "status"=>$res[0]["status"], + "ordertime"=>$res[0]["ordertime"], + "senttime"=>$res[0]["senttime"], + "comments"=>$res[0]["comments"], + "amountpaid"=>$log["orderpaid"], + "shippingcosts"=>$res[0]["shippingcosts"], + "shippingtype"=>$res[0]["shippingtype"], + "audittime"=>$log["logtime"], + "audituname"=>$log["uname"], + "audittransaction"=>"1:".$log["log"], + "paytype"=>"cash", + "paydata"=>"move: ".$log["moved"]."; due: ".$log["orderdue"] + ); + $db->insert("order_audit",$ao); + } + //voucher + if(!$db->isNull($log["voucherid"])){ + $res=$db->select("voucher","*","voucherid=".$db->escapeString($log["voucherid"])); + if(count($res)<1)continue; + $av=array( + "voucherid"=>$res[0]["voucherid"], + "price"=>$res[0]["price"], + "orderid"=>$res[0]["orderid"], + "isused"=>$res[0]["isused"], + "value"=>$log["vouchervalue"], + "audittime"=>$log["logtime"], + "audituname"=>$log["uname"], + "audittransaction"=>"1:".$log["log"] + ); + $db->insert("voucher_audit",$av); + } + } + } + + /**prints string and flushes the buffer, so that the browser sees where we are*/ + static public function fprint($s) + { + echo $s; + ob_flush();flush(); + } +}; + +?> \ No newline at end of file diff --git a/www/inc/machine/session.php b/www/inc/machine/session.php index 9e89db6..357a301 100644 --- a/www/inc/machine/session.php +++ b/www/inc/machine/session.php @@ -225,11 +225,15 @@ class DummyWebSession extends Session { public function __construct() { + //there is no real user for web (so far) $this->user="(web)"; - //fake admin, because web pages do their own checks - //TODO: do something more sensible (eg. _web role) - $this->roles=array("_admin"); + //fake web role, web pages check for it + $this->roles=array("_web"); + //get rights $this->rights=array(); + $res2=$db->select("roleright","rightname","rolename=".$db->escapeString($res[$i][0])); + for($j=0;$jrights[]=$res2[$j][0]; } } -- 1.7.2.5