[diogenes.git] / config / updatedb.php

#!/usr/bin/php -q
<?php
/* Script for updating the database structure.
*/

// Initialisation
require_once("Console/Getopt.php");
require_once("diogenes/diogenes.database-creator.inc.php");

/** The DiogenesDbInit class handles database upgrades between 
 *  Diogenes versions.
 */
class DiogenesDbInit extends DiogenesDatabaseCreator
{
  /** database versions history */
  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");

  /**
   * Upgrades the database from one version to the next
   * 
   * @param $newversion
   */
  function upgradeDb($newversion)
  {
    // pre-upgrade master tables
    $this->info("* Pre-upgrading master tables : diogenes_*");
    $this->preupgradeMaster($newversion);
  
    // upgrade barrels
    $res = $this->dbh->query("select alias from diogenes_site");
    while (list($alias) = mysql_fetch_row($res))
    {   
      $this->info("* Upgrading barrel '$alias'");    
      $this->upgradeBarrel($alias, $newversion);
    }
    mysql_free_result($res);

    // upgrade master tables
    $this->info("* Upgrading master tables : diogenes_*");
    $this->upgradeMaster($newversion);
  }

  
  /** Upgrades the master (i.e. common) tables before touching the barrel tables.
   *
   * @param $newversion
   */  
  function preupgradeMaster($newversion)
  {
    switch($newversion) {
    case "0.9.16+0.9.17pre15";
      $this->info(" - adding 'barrel' field to 'diogenes_options' table");
      $this->dbh->query("ALTER TABLE `diogenes_option` ADD `barrel` VARCHAR( 16 ) NOT NULL FIRST;");
      $this->dbh->query(
        "ALTER TABLE `diogenes_option` DROP PRIMARY KEY ,
         ADD PRIMARY KEY ( `barrel` , `name` )");
      break;
      
    default:
      break;
    }
  }

  
  /** Upgrade a barrel's tables
   *
   * @param $alias
   * @param $newversion
   */
  function upgradeBarrel($alias, $newversion)
  {
#    $this->info("Processing : {$alias}_menu, {$alias}_page and {$alias}_option");

    switch($newversion) {
    case "0.9.10":
      $this->info(" - upgrading : {$alias}_menu");
      // these field where NULL, change to NOT NULL
      $this->dbh->query("ALTER TABLE `{$alias}_menu` CHANGE `link` `link` TEXT NOT NULL");
      $this->dbh->query("ALTER TABLE `{$alias}_menu` CHANGE `ordre` `ordre` SMALLINT( 6 ) UNSIGNED NOT NULL");
      $this->dbh->query("ALTER TABLE `{$alias}_menu` CHANGE `MIDpere` `MIDpere` SMALLINT( 6 ) UNSIGNED NOT NULL");
    
      // break down old 'link' column into 'link' and 'PID'
      $this->dbh->query("ALTER TABLE `{$alias}_menu` ADD `PID` SMALLINT( 6 ) UNSIGNED NOT NULL");
      $res2 = $this->dbh->query("select MID,link from {$alias}_menu");
      while (list($MID,$link) = mysql_fetch_row($res2)) {
        switch (substr($link,0,3)) {
        case "PI:":
          $pid = substr($link,3);
          $this->dbh->query("UPDATE `{$alias}_menu` SET link='',PID='$pid' WHERE MID='$MID'");
          break;
        case "SE:":
          $adr = substr($link,3);
          $this->dbh->query("UPDATE `{$alias}_menu` SET link='$adr' WHERE MID='$MID'");
          break;
        }
      }
      mysql_free_result($res2);
    
      $this->info(" - creating : {$alias}_option");
      $this->dbh->query("CREATE TABLE `{$alias}_option` (name VARCHAR( 32 ) NOT NULL, value TEXT NOT NULL, PRIMARY KEY (`name`)) TYPE=MyISAM;");    
    
      $this->info(" - registering title, description and keywords");
      $res2 = $this->dbh->query("select title,description,keywords from diogenes_site where alias='$alias'");
      list($title,$description,$keywords) = mysql_fetch_row($res2);
      $this->dbh->query("replace into `{$alias}_option` set name='title',value='$title'");
      $this->dbh->query("replace into `{$alias}_option` set name='description',value='$description'");
      $this->dbh->query("replace into `{$alias}_option` set name='keywords',value='$keywords'");
      break;
      
    case "0.9.12":
      $this->info(" - adding 'template' field");
      $this->dbh->query("ALTER TABLE `{$alias}_page` ADD `template` VARCHAR(255) NOT NULL");
      $this->info(" - dropping 'hits' field");
      $this->dbh->query("ALTER TABLE `{$alias}_page` DROP `hits`");
      $this->info(" - replacing 'visible' field by 'status'");
      $this->dbh->query("ALTER TABLE `{$alias}_page` DROP `visible`");
      $this->dbh->query("ALTER TABLE `{$alias}_page` ADD `status` tinyint(1) unsigned NOT NULL");
      $this->info(" - modifying 'perms' and 'wperms' to add 'forbidden' access");
      $this->dbh->query("ALTER TABLE `{$alias}_page` CHANGE `perms` `perms` ENUM( 'public', 'auth', 'user', 'admin', 'forbidden' ) DEFAULT 'public' NOT NULL");
      $this->dbh->query("ALTER TABLE `{$alias}_page` CHANGE `wperms` `wperms` ENUM( 'public', 'auth', 'user', 'admin', 'forbidden' ) DEFAULT 'admin' NOT NULL");
      break;
    
    case "0.9.16":
      $this->info(" - changing page id fields to INT UNSIGNED");
      $this->dbh->query("ALTER TABLE `{$alias}_page` CHANGE `PID` `PID` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT");
      $this->dbh->query("ALTER TABLE `{$alias}_menu` CHANGE `PID` `PID` INT( 10 ) UNSIGNED NOT NULL default '0'");
      $this->info(" - changing menu id fields to INT UNSIGNED");
      $this->dbh->query("ALTER TABLE `{$alias}_menu` CHANGE `MID` `MID` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT");
      $this->dbh->query("ALTER TABLE `{$alias}_menu` CHANGE `MIDpere` `MIDpere` INT( 10 ) UNSIGNED NOT NULL default '0'");
      $this->dbh->query("ALTER TABLE `{$alias}_menu` CHANGE `ordre` `ordre` INT( 10 ) UNSIGNED NOT NULL default '0'");
      break;

    case "0.9.16+0.9.17pre15":
      $this->info(" - merging '{$alias}_option' into 'diogenes_options'");
      $res = $this->dbh->query("select name,value from `{$alias}_option`");
      while (list($o_name,$o_value) = mysql_fetch_row($res)) 
      {
        $this->dbh->query("insert into `diogenes_option` set barrel='$alias',name='$o_name',value='".addslashes($o_value)."'");
      }
      mysql_free_result($res);
      $this->info(" - dropping '{$alias}_option'");
      $this->dbh->query("drop table `{$alias}_option`");

      $this->info(" - adding 'parent' field to `{$alias}_page`");
      $this->dbh->query("ALTER TABLE `{$alias}_page` ADD `parent` INT( 10 ) UNSIGNED NOT NULL default '0' AFTER `PID`");
      $res = $this->dbh->query("select PID from `{$alias}_page` where location=''");
      list($homepage) = mysql_fetch_row($res);
      $this->dbh->query("update `{$alias}_page` set parent='$homepage' where location!=''");
    
      $this->info(" - ordering `{$alias}_page` entries by `location`");
      $this->dbh->query("ALTER TABLE `{$alias}_page` CHANGE `location` `location` VARCHAR( 255 ) NOT NULL");
      $this->dbh->query("ALTER TABLE `{$alias}_page` ORDER BY `location`");
      break;
      
    default:
      $this->info(" - no changes needed.");
      break;
    }
  }

  
  /** Upgrades the master (i.e. common) tables after the barrel tables have been updated.
   *
   * @param $newversion
   */
  function upgradeMaster($newversion)
  {
    // upgrade master tables
    switch($newversion) {
    case "0.9.10":
      $this->info(" - dropping fields : title, description, keywords");
      $this->dbh->query("ALTER TABLE `diogenes_site` DROP `title`");
      $this->dbh->query("ALTER TABLE `diogenes_site` DROP `description`");
      $this->dbh->query("ALTER TABLE `diogenes_site` DROP `keywords`");
      break;

    case "0.9.15":
      $this->info(" - adding field : email");
      $this->dbh->query("ALTER TABLE `diogenes_auth` ADD `email` VARCHAR( 255 ) NOT NULL");
      break;
      
    case "0.9.16":
      $this->info(" - changing user id fields to INT UNSIGNED");
      $this->dbh->query("ALTER TABLE `diogenes_auth` CHANGE `user_id` `user_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT");
      $this->dbh->query("ALTER TABLE `diogenes_logsessions` CHANGE `uid` `uid` INT(10) UNSIGNED NOT NULL default '0'");
      $this->dbh->query("ALTER TABLE `diogenes_logsessions` CHANGE `suid` `suid` INT(10) UNSIGNED NOT NULL default '0'");
      $this->dbh->query("ALTER TABLE `diogenes_perm` CHANGE `uid` `uid` INT(10) UNSIGNED NOT NULL default '0'");
      break;
      
    case "0.9.16+0.9.17pre15":
      $this->info(" - creating 'diogenes_plugin' table");
      $this->dbh->query(
        "CREATE TABLE `diogenes_plugin` (
           `plugin` varchar(32) NOT NULL default '',
           `barrel` varchar(16) NOT NULL default '',
           `page` int(10) unsigned NOT NULL default '0',
           `pos` int(10) unsigned NOT NULL default '0',
           `params` text NOT NULL,
            PRIMARY KEY  (`plugin`,`barrel`,`page`),
            KEY `pos` (`pos`)
          ) TYPE=MyISAM;");
      break;

    case "0.9.16+0.9.17pre19":
      $this->info(" - changing id of `diogenes_logsessions` to INT(10) UNSIGNED");
      $this->dbh->query("ALTER TABLE `diogenes_logsessions` CHANGE `id` `id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT");
      break;
     
    case "0.9.16+0.9.17pre21":      
      $this->dbh->query("INSERT INTO diogenes_logactions VALUES (13, 'barrel_options', 'the barrel options were updated');");
      $this->dbh->query("INSERT INTO diogenes_logactions VALUES (14, 'barrel_plugins', 'the barrel plugins were modified');");
      $this->dbh->query("INSERT INTO diogenes_logactions VALUES (15, 'page_props', 'the page properties were updated');");
      $this->dbh->query("INSERT INTO diogenes_logactions VALUES (16, 'page_plugins', 'the page plugins were modified');");
      break;

    default:
      $this->info(" - no changes needed.");
      break;
    }
  }
}

/*
 * Main routine
*/
$creator = new DiogenesDbInit("diogenes_option");
$creator->parseOptions($argv, "diogenes", "localhost", "diogenes", "");
$creator->run();

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