config/updatedb.php

   1 #!/usr/bin/php -q
   2 <?php
   3 /* Script for updating the database structure.
   4 */
   5
   6 // Initialisation
   7 require_once("Console/Getopt.php");
   8 require_once("diogenes/diogenes.database-creator.inc.php");
   9
  10 /** The DiogenesDbInit class handles database upgrades between
  11  *  Diogenes versions.
  12  */
  13 class DiogenesDbInit extends DiogenesDatabaseCreator
  14 {
  15   /** database versions history */
  16   var $versions = array("0.9.9.3", "0.9.10", "0.9.12", "0.9.15", "0.9.16", "0.9.16+0.9.17pre15", "0.9.16+0.9.17pre19", "0.9.16+0.9.17pre21", "0.9.20");
  17
  18   /**
  19    * Upgrades the database from one version to the next
  20    *
  21    * @param $newversion
  22    */
  23   function upgradeDb($newversion)
  24   {
  25     // pre-upgrade master tables
  26     $this->info("* Pre-upgrading master tables : diogenes_*");
  27     $this->preupgradeMaster($newversion);
  28
  29     // upgrade barrels
  30     $res = $this->dbh->query("select alias from diogenes_site");
  31     while (list($alias) = mysql_fetch_row($res))
  32     {
  33       $this->info("* Upgrading barrel '$alias'");
  34       $this->upgradeBarrel($alias, $newversion);
  35     }
  36     mysql_free_result($res);
  37
  38     // upgrade master tables
  39     $this->info("* Upgrading master tables : diogenes_*");
  40     $this->upgradeMaster($newversion);
  41     exit(1);
  42   }
  43
  44
  45   /** Upgrades the master (i.e. common) tables before touching the barrel tables.
  46    *
  47    * @param $newversion
  48    */
  49   function preupgradeMaster($newversion)
  50   {
  51     switch($newversion) {
  52     case "0.9.16+0.9.17pre15";
  53       $this->info(" - adding 'barrel' field to 'diogenes_options' table");
  54       $this->dbh->query("ALTER TABLE `diogenes_option` ADD `barrel` VARCHAR( 16 ) NOT NULL FIRST;");
  55       $this->dbh->query(
  56         "ALTER TABLE `diogenes_option` DROP PRIMARY KEY ,
  57          ADD PRIMARY KEY ( `barrel` , `name` )");
  58       break;
  59
  60     default:
  61       break;
  62     }
  63   }
  64
  65
  66   /** Upgrade a barrel's tables
  67    *
  68    * @param $alias
  69    * @param $newversion
  70    */
  71   function upgradeBarrel($alias, $newversion)
  72   {
  73 #    $this->info("Processing : {$alias}_menu, {$alias}_page and {$alias}_option");
  74
  75     switch($newversion) {
  76     case "0.9.10":
  77       $this->info(" - upgrading : {$alias}_menu");
  78       // these field where NULL, change to NOT NULL
  79       $this->dbh->query("ALTER TABLE `{$alias}_menu` CHANGE `link` `link` TEXT NOT NULL");
  80       $this->dbh->query("ALTER TABLE `{$alias}_menu` CHANGE `ordre` `ordre` SMALLINT( 6 ) UNSIGNED NOT NULL");
  81       $this->dbh->query("ALTER TABLE `{$alias}_menu` CHANGE `MIDpere` `MIDpere` SMALLINT( 6 ) UNSIGNED NOT NULL");
  82
  83       // break down old 'link' column into 'link' and 'PID'
  84       $this->dbh->query("ALTER TABLE `{$alias}_menu` ADD `PID` SMALLINT( 6 ) UNSIGNED NOT NULL");
  85       $res2 = $this->dbh->query("select MID,link from {$alias}_menu");
  86       while (list($MID,$link) = mysql_fetch_row($res2)) {
  87         switch (substr($link,0,3)) {
  88         case "PI:":
  89           $pid = substr($link,3);
  90           $this->dbh->query("UPDATE `{$alias}_menu` SET link='',PID='$pid' WHERE MID='$MID'");
  91           break;
  92         case "SE:":
  93           $adr = substr($link,3);
  94           $this->dbh->query("UPDATE `{$alias}_menu` SET link='$adr' WHERE MID='$MID'");
  95           break;
  96         }
  97       }
  98       mysql_free_result($res2);
  99
 100       $this->info(" - creating : {$alias}_option");
 101       $this->dbh->query("CREATE TABLE `{$alias}_option` (name VARCHAR( 32 ) NOT NULL, value TEXT NOT NULL, PRIMARY KEY (`name`)) TYPE=MyISAM;");
 102
 103       $this->info(" - registering title, description and keywords");
 104       $res2 = $this->dbh->query("select title,description,keywords from diogenes_site where alias='$alias'");
 105       list($title,$description,$keywords) = mysql_fetch_row($res2);
 106       $this->dbh->query("replace into `{$alias}_option` set name='title',value='$title'");
 107       $this->dbh->query("replace into `{$alias}_option` set name='description',value='$description'");
 108       $this->dbh->query("replace into `{$alias}_option` set name='keywords',value='$keywords'");
 109       break;
 110
 111     case "0.9.12":
 112       $this->info(" - adding 'template' field");
 113       $this->dbh->query("ALTER TABLE `{$alias}_page` ADD `template` VARCHAR(255) NOT NULL");
 114       $this->info(" - dropping 'hits' field");
 115       $this->dbh->query("ALTER TABLE `{$alias}_page` DROP `hits`");
 116       $this->info(" - replacing 'visible' field by 'status'");
 117       $this->dbh->query("ALTER TABLE `{$alias}_page` DROP `visible`");
 118       $this->dbh->query("ALTER TABLE `{$alias}_page` ADD `status` tinyint(1) unsigned NOT NULL");
 119       $this->info(" - modifying 'perms' and 'wperms' to add 'forbidden' access");
 120       $this->dbh->query("ALTER TABLE `{$alias}_page` CHANGE `perms` `perms` ENUM( 'public', 'auth', 'user', 'admin', 'forbidden' ) DEFAULT 'public' NOT NULL");
 121       $this->dbh->query("ALTER TABLE `{$alias}_page` CHANGE `wperms` `wperms` ENUM( 'public', 'auth', 'user', 'admin', 'forbidden' ) DEFAULT 'admin' NOT NULL");
 122       break;
 123
 124     case "0.9.16":
 125       $this->info(" - changing page id fields to INT UNSIGNED");
 126       $this->dbh->query("ALTER TABLE `{$alias}_page` CHANGE `PID` `PID` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT");
 127       $this->dbh->query("ALTER TABLE `{$alias}_menu` CHANGE `PID` `PID` INT( 10 ) UNSIGNED NOT NULL default '0'");
 128       $this->info(" - changing menu id fields to INT UNSIGNED");
 129       $this->dbh->query("ALTER TABLE `{$alias}_menu` CHANGE `MID` `MID` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT");
 130       $this->dbh->query("ALTER TABLE `{$alias}_menu` CHANGE `MIDpere` `MIDpere` INT( 10 ) UNSIGNED NOT NULL default '0'");
 131       $this->dbh->query("ALTER TABLE `{$alias}_menu` CHANGE `ordre` `ordre` INT( 10 ) UNSIGNED NOT NULL default '0'");
 132       break;
 133
 134     case "0.9.16+0.9.17pre15":
 135       $this->info(" - merging '{$alias}_option' into 'diogenes_options'");
 136       $res = $this->dbh->query("select name,value from `{$alias}_option`");
 137       while (list($o_name,$o_value) = mysql_fetch_row($res))
 138       {
 139         $this->dbh->query("insert into `diogenes_option` set barrel='$alias',name='$o_name',value='".addslashes($o_value)."'");
 140       }
 141       mysql_free_result($res);
 142       $this->info(" - dropping '{$alias}_option'");
 143       $this->dbh->query("drop table `{$alias}_option`");
 144
 145       $this->info(" - adding 'parent' field to `{$alias}_page`");
 146       $this->dbh->query("ALTER TABLE `{$alias}_page` ADD `parent` INT( 10 ) UNSIGNED NOT NULL default '0' AFTER `PID`");
 147       $res = $this->dbh->query("select PID from `{$alias}_page` where location=''");
 148       list($homepage) = mysql_fetch_row($res);
 149       $this->dbh->query("update `{$alias}_page` set parent='$homepage' where location!=''");
 150
 151       $this->info(" - ordering `{$alias}_page` entries by `location`");
 152       $this->dbh->query("ALTER TABLE `{$alias}_page` CHANGE `location` `location` VARCHAR( 255 ) NOT NULL");
 153       $this->dbh->query("ALTER TABLE `{$alias}_page` ORDER BY `location`");
 154       break;
 155
 156     default:
 157       $this->info(" - no changes needed.");
 158       break;
 159     }
 160   }
 161
 162
 163   /** Upgrades the master (i.e. common) tables after the barrel tables have been updated.
 164    *
 165    * @param $newversion
 166    */
 167   function upgradeMaster($newversion)
 168   {
 169     // upgrade master tables
 170     switch($newversion) {
 171     case "0.9.10":
 172       $this->info(" - dropping fields : title, description, keywords");
 173       $this->dbh->query("ALTER TABLE `diogenes_site` DROP `title`");
 174       $this->dbh->query("ALTER TABLE `diogenes_site` DROP `description`");
 175       $this->dbh->query("ALTER TABLE `diogenes_site` DROP `keywords`");
 176       break;
 177
 178     case "0.9.15":
 179       $this->info(" - adding field : email");
 180       $this->dbh->query("ALTER TABLE `diogenes_auth` ADD `email` VARCHAR( 255 ) NOT NULL");
 181       break;
 182
 183     case "0.9.16":
 184       $this->info(" - changing user id fields to INT UNSIGNED");
 185       $this->dbh->query("ALTER TABLE `diogenes_auth` CHANGE `user_id` `user_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT");
 186       $this->dbh->query("ALTER TABLE `diogenes_logsessions` CHANGE `uid` `uid` INT(10) UNSIGNED NOT NULL default '0'");
 187       $this->dbh->query("ALTER TABLE `diogenes_logsessions` CHANGE `suid` `suid` INT(10) UNSIGNED NOT NULL default '0'");
 188       $this->dbh->query("ALTER TABLE `diogenes_perm` CHANGE `uid` `uid` INT(10) UNSIGNED NOT NULL default '0'");
 189       break;
 190
 191     case "0.9.16+0.9.17pre15":
 192       $this->info(" - creating 'diogenes_plugin' table");
 193       $this->dbh->query(
 194         "CREATE TABLE `diogenes_plugin` (
 195            `plugin` varchar(32) NOT NULL default '',
 196            `barrel` varchar(16) NOT NULL default '',
 197            `page` int(10) unsigned NOT NULL default '0',
 198            `pos` int(10) unsigned NOT NULL default '0',
 199            `params` text NOT NULL,
 200             PRIMARY KEY  (`plugin`,`barrel`,`page`),
 201             KEY `pos` (`pos`)
 202           ) TYPE=MyISAM;");
 203       break;
 204
 205     case "0.9.16+0.9.17pre19":
 206       $this->info(" - changing id of `diogenes_logsessions` to INT(10) UNSIGNED");
 207       $this->dbh->query("ALTER TABLE `diogenes_logsessions` CHANGE `id` `id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT");
 208       break;
 209
 210     case "0.9.16+0.9.17pre21":
 211       $this->dbh->query("INSERT INTO diogenes_logactions VALUES (13, 'barrel_options', 'the barrel options were updated');");
 212       $this->dbh->query("INSERT INTO diogenes_logactions VALUES (14, 'barrel_plugins', 'the barrel plugins were modified');");
 213       $this->dbh->query("INSERT INTO diogenes_logactions VALUES (15, 'page_props', 'the page properties were updated');");
 214       $this->dbh->query("INSERT INTO diogenes_logactions VALUES (16, 'page_plugins', 'the page plugins were modified');");
 215       break;
 216
 217     case "0.9.20":
 218       $this->info(" - adding 'status' field to 'diogenes_plugin' table");
 219       $this->dbh->query("ALTER TABLE `diogenes_plugin` ADD `status` INT( 1 ) UNSIGNED NOT NULL default '0'");
 220       $this->dbh->query("update diogenes_plugin set status=0 where page=0");
 221       $this->dbh->query("update diogenes_plugin set status=1 where page!=0");
 222       $this->info(" - removing 'pos' field to 'diogenes_plugin' table");
 223       $this->dbh->query("ALTER TABLE `diogenes_plugin` DROP `pos`");
 224       break;
 225
 226     default:
 227       $this->info(" - no changes needed.");
 228       break;
 229     }
 230   }
 231 }
 232
 233 /*
 234  * Main routine
 235 */
 236 $creator = new DiogenesDbInit("diogenes_option");
 237 $creator->parseOptions($argv, "diogenes", "localhost", "diogenes", "");
 238 $creator->run();
 239
 240 ?>