[pLog-svn] r3153 - in plog/trunk: . install

oscar at devel.lifetype.net oscar at devel.lifetype.net
Wed Mar 29 16:54:09 GMT 2006


Author: oscar
Date: 2006-03-29 16:54:08 +0000 (Wed, 29 Mar 2006)
New Revision: 3153

Modified:
   plog/trunk/install/changes_10_11.properties.php
   plog/trunk/install/dbschemas.properties.php
   plog/trunk/wizard.php
Log:
Big change for the summary, as I have converted all table schemas to adodb's own data definition language based on "data dictionaries": http://phplens.com/lens/adodb/docs-datadict.htm. This allows us to use one single language to define db schemas regardless of the underlying rdbms, and the database-specific driver will take care of generating the right code.
This also means that upgrading is now easier, since this ddl also allows the driver to automatically generate the code needed to ugprade from an older schema to a new one, instead of hardcoding everything by hand (painful) I've tested upgrading from 1.0.x and seems to work fine but please let me know if there's any problem. I know this is a big change this late but I have been meaning to do this for a while...


Modified: plog/trunk/install/changes_10_11.properties.php
===================================================================
--- plog/trunk/install/changes_10_11.properties.php	2006-03-29 16:49:04 UTC (rev 3152)
+++ plog/trunk/install/changes_10_11.properties.php	2006-03-29 16:54:08 UTC (rev 3153)
@@ -1,8 +1,7 @@
 <?php
 
 $Changes["Deletions"] = Array(
-    "ALTER TABLE {dbprefix}articles DROP COLUMN category_id",
-    "ALTER TABLE {dbprefix}blogs DROP COLUMN show_in_summary",
+    "ALTER TABLE {dbprefix}articles DROP COLUMN category_id"
 );
 
 $Changes["Articles"] = Array(    

Modified: plog/trunk/install/dbschemas.properties.php
===================================================================
--- plog/trunk/install/dbschemas.properties.php	2006-03-29 16:49:04 UTC (rev 3152)
+++ plog/trunk/install/dbschemas.properties.php	2006-03-29 16:54:08 UTC (rev 3153)
@@ -1,396 +1,348 @@
 <?php
-
-    $Tables[0]["desc"] = "Articles";
-    $Tables[0]["code"] = "CREATE TABLE {dbprefix}articles (
-  id int(10) unsigned NOT NULL auto_increment,
-  category_id int(10) unsigned NOT NULL default '0',
-  date timestamp(14) NOT NULL,
-  modification_date timestamp(14) NOT NULL,
-  user_id int(10) unsigned NOT NULL default '0',
-  blog_id int(10) unsigned NOT NULL default '0',
-  status INTEGER(5) NOT NULL default 1,
-  num_reads int(10) default '0',
-  properties TEXT NOT NULL default '',
-  slug varchar(255) NOT NULL,
-  num_comments int(10) NOT NULL default '0', 
-  num_nonspam_comments int(10) NOT NULL default '0', 
-  num_trackbacks int(10) NOT NULL default '0',
-  num_nonspam_trackbacks int(10) NOT NULL default '0',
-  global_category_id int(10) NOT NULL default '0',
-  in_summary_page TINYINT(1) NOT NULL DEFAULT '1',  
-  PRIMARY KEY (id),
-  KEY num_reads (num_reads),
-  KEY category_id (category_id),
-  KEY blog_id (blog_id),
-  KEY user_id (user_id),
-  KEY slug (slug),
-  KEY blog_id_slug (blog_id,slug),
-  KEY blog_id_slug_category_id (blog_id,slug,category_id)
-) TYPE=MyISAM;";
 
-    $Tables[1]["desc"] = "Article categories";
-    $Tables[1]["code"] = "CREATE TABLE {dbprefix}articles_categories (
-  id int(10) unsigned NOT NULL auto_increment,
-  name varchar(255) NOT NULL default '',
-  url varchar(255) NOT NULL default '',
-  blog_id int(10) unsigned NOT NULL default '0',
-  last_modification timestamp(14) NOT NULL,
-  in_main_page TINYINT(1) NOT NULL DEFAULT '1',
-  parent_id INTEGER(10) NOT NULL DEFAULT '0',
-  description TEXT NOT NULL DEFAULT '',
-  properties text NOT NULL default '',
-  mangled_name varchar(255) NOT NULL default '',
-  num_articles int(10) NOT NULL default 0,
-  num_published_articles int(10) NOT NULL default 0,
-  PRIMARY KEY  (id),
-  KEY parent_id (parent_id),
-  KEY blog_id (blog_id),
-  KEY mangled_name (mangled_name)
-) TYPE=MyISAM;";
+$Tables["articles"] = "
+  id I(10) UNSIGNED NOTNULL AUTOINCREMENT PRIMARY,
+  category_id I(10) UNSIGNED NOTNULL DEFAULT '0',
+  date T(14) NOTNULL,
+  modification_date T(14) NOTNULL,
+  user_id I(10) UNSIGNED NOTNULL DEFAULT '0',
+  blog_id I(10) UNSIGNED NOTNULL DEFAULT '0',
+  status I(5) NOTNULL DEFAULT 1,
+  num_reads I(10) DEFAULT '0',
+  properties X NOTNULL DEFAULT '',
+  slug C(255) NOTNULL,
+  num_comments I(10) NOTNULL DEFAULT '0', 
+  num_nonspam_comments I(10) NOTNULL DEFAULT '0', 
+  num_trackbacks I(10) NOTNULL DEFAULT '0',
+  num_nonspam_trackbacks I(10) NOTNULL DEFAULT '0',
+  global_category_id I(10) NOTNULL DEFAULT '0',
+  in_summary_page I1 NOTNULL DEFAULT '1',
+  INDEX num_reads (num_reads),
+  INDEX category_id (category_id),
+  INDEX blog_id (blog_id),
+  INDEX user_id (user_id),
+  INDEX slug (slug),
+  INDEX blog_id_slug (blog_id,slug),
+  INDEX blog_id_slug_category_id (blog_id, slug, category_id)
+  INDEX global_category_status (global_category_id, status)  
+";
 
-    $Tables[2]["desc"] = "Comments";
-    $Tables[2]["code"] = "CREATE TABLE {dbprefix}articles_comments (
-  id int(10) unsigned NOT NULL auto_increment,
-  article_id int(10) unsigned NOT NULL default '0',
-  blog_id int(10) unsigned NOT NULL default '0',
-  topic text NOT NULL,
-  text text,
-  date timestamp(14) NOT NULL,
-  user_email varchar(255) default '',
-  user_url varchar(255) default '',
-  user_name varchar(255) NOT NULL default 'Anonymous',
-  parent_id int(10) unsigned default '0',
-  client_ip varchar(15) default '0.0.0.0',
-  send_notification tinyint(1) default '0',
-  status tinyint(2) default '1',
-  spam_rate float default '0',
-  properties TEXT NOT NULL DEFAULT '',
-  normalized_text TEXT NOT NULL DEFAULT '',
-  normalized_topic TEXT NOT NULL DEFAULT '',
-  type int(3) NOT NULL DEFAULT '0',  
-  PRIMARY KEY  (id),
-  KEY parent_id (parent_id),
-  KEY article_id (article_id),
-  KEY blog_id (blog_id),
-  KEY article_id_blog_id(article_id,blog_id),
-  FULLTEXT KEY normalized_fields (normalized_text,normalized_topic),
-  FULLTEXT KEY normalized_text (normalized_text),
-  FULLTEXT KEY normalized_topic (normalized_topic)
-) TYPE=MyISAM;";
+$Tables["articles_categories"] = "
+  id I(10) UNSIGNED NOTNULL AUTOINCREMENT PRIMARY,
+  name C(255) NOTNULL DEFAULT '',
+  url C(255) NOTNULL DEFAULT '',
+  blog_id I(10) UNSIGNED NOTNULL DEFAULT '0',
+  last_modification T(14) NOTNULL,
+  in_main_page I1 NOTNULL DEFAULT '1',
+  parent_id I(10) NOTNULL DEFAULT '0',
+  description X NOTNULL DEFAULT '',
+  properties X NOTNULL DEFAULT '',
+  mangled_name C(255) NOTNULL DEFAULT '',
+  num_articles I(10) NOTNULL DEFAULT 0,
+  num_published_articles I(10) NOTNULL DEFAULT 0
+  INDEX parent_id (parent_id),
+  INDEX blog_id (blog_id),
+  INDEX mangled_name (mangled_name)
+";
 
-    $Tables[3]["desc"] = "Notifications";
-    $Tables[3]["code"] = "CREATE TABLE {dbprefix}articles_notifications (
-  id int(10) NOT NULL auto_increment,
-  blog_id int(10) NOT NULL default '0',
-  user_id int(10) NOT NULL default '0',
-  article_id int(10) NOT NULL default '0',
-  PRIMARY KEY  (id),
-  KEY article_id (article_id),
-  KEY user_id (user_id),
-  KEY blog_id (blog_id)
-) TYPE=MyISAM;";
 
-    $Tables[4]["desc"] = "Blogs";
-    $Tables[4]["code"] = "CREATE TABLE {dbprefix}blogs (
-  id int(10) unsigned NOT NULL auto_increment,
-  blog varchar(50) NOT NULL default '',
-  owner_id int(10) unsigned NOT NULL default '0',
-  blog_category_id int(10) unsigned NOT NULL default '0',
-  about text,
-  settings text NOT NULL,
-  mangled_blog varchar(50) NOT NULL default '',
-  status int(4) NOT NULL default '1',
-  show_in_summary int(4) not null default '1',
-  create_date TIMESTAMP(14) NOT NULL,
-  last_update_date TIMESTAMP(14) NOT NULL,
-  num_posts int(10) NOT NULL DEFAULT '0',
-  num_comments int(10) NOT NULL DEFAULT '0',
-  num_trackbacks int(10) NOT NULL DEFAULT '0',
-  custom_domain varchar(50),
-  PRIMARY KEY  (id),
-  KEY owner_id (owner_id),
-  KEY mangled_blog (mangled_blog),
-  KEY blog_category_id(blog_category_id)
-) TYPE=MyISAM;";
+$Tables["articles_comments"] = "
+  id I(10) UNSIGNED NOTNULL AUTOINCREMENT PRIMARY,
+  article_id I(10) UNSIGNED NOTNULL DEFAULT 0,
+  blog_id I(10) UNSIGNED NOTNULL DEFAULT 0,
+  topic X NOTNULL,
+  text X,
+  date T(14) NOTNULL,
+  user_email C(255) DEFAULT '',
+  user_url C(255) DEFAULT '',
+  user_name C(255) NOTNULL DEFAULT Anonymous,
+  parent_id I(10) UNSIGNED DEFAULT '0',
+  client_ip varchar(15) DEFAULT 0.0.0.0,
+  send_notification I1 DEFAULT '0',
+  status I1(2) DEFAULT '1',
+  spam_rate float DEFAULT '0',
+  properties X NOTNULL DEFAULT '',
+  normalized_text X NOTNULL DEFAULT '',
+  normalized_topic X NOTNULL DEFAULT '',
+  type I(3) NOTNULL DEFAULT '1',  
+  INDEX parent_id (parent_id),
+  INDEX article_id (article_id),
+  INDEX blog_id (blog_id),
+  INDEX article_id_blog_id(article_id,blog_id),
+  FULLTEXT normalized_fields (normalized_text,normalized_topic),
+  FULLTEXT normalized_text (normalized_text),
+  FULLTEXT normalized_topic (normalized_topic)
+";
 
-    $Tables[5]["desc"] = "MyLinks";
-    $Tables[5]["code"] = "CREATE TABLE {dbprefix}mylinks (
-  id int(10) unsigned NOT NULL auto_increment,
-  category_id int(10) unsigned NOT NULL default '0',
-  url varchar(255) NOT NULL default '',
-  name varchar(100) default '',
-  description text NOT NULL,
-  blog_id int(10) unsigned NOT NULL default '0',
-  rss_feed varchar(255) not null default '',
-  date timestamp(14) not null,
-  properties TEXT NOT NULL DEFAULT '',
-  PRIMARY KEY  (id),
-  KEY blog_id (blog_id),
-  KEY category_id (category_id)
-) TYPE=MyISAM;";
+$Tables["articles_notifications"] = "
+  id I(10) NOTNULL AUTOINCREMENT PRIMARY,
+  blog_id I(10) NOTNULL DEFAULT '0',
+  user_id I(10) NOTNULL DEFAULT '0',
+  article_id I(10) NOTNULL DEFAULT '0',
+  INDEX article_id (article_id),
+  INDEX user_id (user_id),
+  INDEX blog_id (blog_id)
+";
 
-    $Tables[6]["desc"] = "MyLinks Categories";
-    $Tables[6]["code"] = "CREATE TABLE {dbprefix}mylinks_categories (
-  id int(10) NOT NULL auto_increment,
-  name varchar(100) NOT NULL default '',
-  blog_id int(10) NOT NULL default '0',
-  last_modification timestamp(14) NOT NULL,
-  properties TEXT NOT NULL DEFAULT '',
-  num_links int(10) NOT NULL default '0',
-  PRIMARY KEY  (id),
-  KEY blog_id (blog_id)
-) TYPE=MyISAM;";
+$Tables["blogs"] = "
+  id I(10) UNSIGNED NOTNULL AUTOINCREMENT INDEX PRIMARY,
+  blog varchar(50) NOTNULL DEFAULT '',
+  owner_id I(10) UNSIGNED NOTNULL DEFAULT '0',
+  blog_category_id I(10) UNSIGNED NOTNULL DEFAULT '0',
+  about X,
+  settings X NOTNULL,
+  mangled_blog varchar(50) NOTNULL DEFAULT '',
+  status I(4) NOTNULL DEFAULT '1',
+  show_in_summary I(4) NOTNULL DEFAULT '1',
+  create_date T(14) NOTNULL,
+  last_update_date T(14) NOTNULL,
+  num_posts I(10) NOTNULL DEFAULT '0',
+  num_comments I(10) NOTNULL DEFAULT '0',
+  num_trackbacks I(10) NOTNULL DEFAULT '0',
+  custom_domain C(50), 
+  INDEX owner_id (owner_id),
+  INDEX mangled_blog (mangled_blog),
+  INDEX blog_category_id(blog_category_id),
+  INDEX custom_domain(custom_domain)
+";
 
-    $Tables[9]["desc"] = "Permissions";
-    $Tables[9]["code"] = "CREATE TABLE {dbprefix}permissions (
-  id int(10) unsigned NOT NULL auto_increment,
-  permission varchar(25) NOT NULL default '',
-  description varchar(100) NOT NULL default '',
-  PRIMARY KEY  (id)
-) TYPE=MyISAM;";
+$Tables["mylinks"] = "
+  id I(10) UNSIGNED NOTNULL AUTOINCREMENT PRIMARY,
+  category_id I(10) UNSIGNED NOTNULL DEFAULT '0',
+  url C(255) NOTNULL DEFAULT '',
+  name varchar(100) DEFAULT '',
+  description X NOTNULL,
+  blog_id I(10) UNSIGNED NOTNULL DEFAULT '0',
+  rss_feed C(255) NOTNULL DEFAULT '',
+  date T(14) NOTNULL,
+  properties X NOTNULL DEFAULT '',
+  INDEX blog_id (blog_id),
+  INDEX category_id (category_id)
+";
 
-    $Tables[10]["desc"] = "Referrers";
-    $Tables[10]["code"] = "CREATE TABLE {dbprefix}referers (
-  id int(10) NOT NULL auto_increment,
-  url text NOT NULL,
-  article_id int(10) NOT NULL default '0',
-  blog_id int(10) NOT NULL default '0',
-  hits int(10) default '1',
-  last_date timestamp(14),
-  PRIMARY KEY  (id),
-  KEY article_id (article_id),
-  KEY blog_id (blog_id),
-  KEY blog_id_article_id (blog_id, article_id)
-) TYPE=MyISAM;";
+$Tables["mylinks_categories"] = "
+  id I(10) NOTNULL AUTOINCREMENT PRIMARY,
+  name varchar(100) NOTNULL DEFAULT '',
+  blog_id I(10) NOTNULL DEFAULT '0',
+  last_modification T(14) NOTNULL,
+  properties X NOTNULL DEFAULT '',
+  num_links I(10) NOTNULL DEFAULT '0',
+  INDEX blog_id (blog_id)
+";
 
-    $Tables[12]["desc"] = "Users";
-    $Tables[12]["code"] = "CREATE TABLE {dbprefix}users (
-  id int(10) unsigned NOT NULL auto_increment,
-  user varchar(15) NOT NULL default '',
-  password varchar(32) NOT NULL default '',
-  email varchar(255) NOT NULL default '',
-  full_name varchar(255) NOT NULL default '',
-  about text,
-  properties TEXT NOT NULL default '',
-  status integer(4) NOT NULL DEFAULT 1,
-  resource_picture_id integer(10) NOT NULL DEFAULT 0,
-  site_admin int(10) NOT NULL default '0',
-  PRIMARY KEY  (id),
-  UNIQUE KEY user (user)
-) TYPE=MyISAM;";
+$Tables["permissions"] = "
+  id I(10) UNSIGNED NOTNULL AUTOINCREMENT PRIMARY,
+  permission varchar(25) NOTNULL DEFAULT '',
+  description varchar(100) NOTNULL DEFAULT ''
+";
 
-    $Tables[13]["desc"] = "Permissions";
-    $Tables[13]["code"] = "CREATE TABLE {dbprefix}users_permissions (
-  id int(10) unsigned NOT NULL auto_increment,
-  user_id int(10) unsigned NOT NULL default '0',
-  blog_id int(10) unsigned NOT NULL default '0',
-  permission_id int(10) unsigned NOT NULL default '0',
-  PRIMARY KEY  (id),
-  KEY user_id (user_id),
-  KEY blog_id (blog_id),
-  KEY user_id_permission_id (user_id,permission_id)
-) TYPE=MyISAM;";
+$Tables["referers"] = "
+  id I(10) NOTNULL AUTOINCREMENT PRIMARY,
+  url X NOTNULL,
+  article_id I(10) NOTNULL DEFAULT '0',
+  blog_id I(10) NOTNULL DEFAULT '0',
+  hits I(10) DEFAULT '1',
+  last_date T(14),
+  INDEX article_id (article_id),
+  INDEX blog_id (blog_id),
+  INDEX blog_id_article_id (blog_id, article_id)
+";
 
+$Tables["users"] = "
+  id I(10) UNSIGNED NOTNULL AUTOINCREMENT PRIMARY,
+  user varchar(15) NOTNULL DEFAULT '',
+  password varchar(32) NOTNULL DEFAULT '',
+  email C(255) NOTNULL DEFAULT '',
+  full_name C(255) NOTNULL DEFAULT '',
+  about X,
+  properties X NOTNULL DEFAULT '',
+  status I(4) NOTNULL DEFAULT 1,
+  resource_picture_id I(10) NOTNULL DEFAULT 0,
+  site_admin I(10) NOTNULL DEFAULT '0',
+  UNIQUE user (user)
+";
 
-   $Tables[14]["desc"] = "Configuration";
-   $Tables[14]["code"] = "CREATE TABLE {dbprefix}config (
-   id int(10) NOT NULL auto_increment,
-   config_key varchar(255) NOT NULL default '',
-   config_value text NOT NULL,
-   value_type int(3) default '0',
-   PRIMARY KEY  (id,config_key)
-   ) TYPE=MyISAM;";
+$Tables["users_permissions"] = "
+  id I(10) UNSIGNED NOTNULL AUTOINCREMENT PRIMARY,
+  user_id I(10) UNSIGNED NOTNULL DEFAULT '0',
+  blog_id I(10) UNSIGNED NOTNULL DEFAULT '0',
+  permission_id I(10) UNSIGNED NOTNULL DEFAULT '0',
+  INDEX user_id (user_id),
+  INDEX blog_id (blog_id),
+  INDEX user_id_permission_id (user_id,permission_id)
+";
 
-   $Tables[15]["desc"] = "Filtered Content";
-   $Tables[15]["code"] = "CREATE TABLE {dbprefix}filtered_content (
-   id int(10) NOT NULL auto_increment,
-   reg_exp text,
-   blog_id int(10) NOT NULL default '0',
-   reason text,
-   date timestamp(14) NOT NULL,
-   PRIMARY KEY  (id),
-   KEY blog_id (blog_id)
-   ) TYPE=MyISAM;";
 
-   $Tables[16]["desc"] = "Blocked content";
-   $Tables[16]["code"] = "CREATE TABLE {dbprefix}host_blocking_rules (
-   id int(10) NOT NULL auto_increment,
-   reason text,
-   date timestamp(14) NOT NULL,
-   blog_id int(10) NOT NULL default '0',
-   block_type int(1) default '1',
-   list_type int(1) default '1',
-   mask int(2) default '0',
-   host varchar(15) default '0.0.0.0',
-   PRIMARY KEY  (id),
-   KEY blog_id (blog_id)
-   ) TYPE=MyISAM;";
+$Tables["config"] = "
+   id I(10) NOTNULL AUTOINCREMENT PRIMARY,
+   config_key C(255) NOTNULL DEFAULT '' PRIMARY,
+   config_value X NOTNULL,
+   value_type I(3) DEFAULT '0'
+";
 
-   $Tables[17]["desc"] = "Gallery Resources";
-   $Tables[17]["code"] = "CREATE TABLE {dbprefix}gallery_resources (
-   id int(10) NOT NULL auto_increment,
-   owner_id int(10) NOT NULL default '0',
-   album_id int(10) NOT NULL default '0',
-   description text,
-   date timestamp(14) NOT NULL,
-   flags int(10) default '0',
-   resource_type int(3) default NULL,
-   file_path varchar(255) default '',
-   file_name varchar(255) default '',
-   file_size int(20) not null default 0,
-   metadata text,
-   thumbnail_format varchar(4) not null default 'same',
-   normalized_description text NOT NULL default '',
-   properties TEXT NOT NULL DEFAULT '',
-   PRIMARY KEY  (id),
-   KEY album_id (album_id),
-   KEY owner_id (owner_id),
-   KEY file_name (file_name),
-   KEY album_id_owner_id (album_id, owner_id),
-   KEY resource_type (resource_type),
-   FULLTEXT KEY normalized_description (normalized_description)
-   ) TYPE=MyISAM;";
+$Tables["filtered_content"] = "
+   id I(10) NOTNULL AUTOINCREMENT PRIMARY,
+   reg_exp X,
+   blog_id I(10) NOTNULL DEFAULT '0',
+   reason X,
+   date T(14) NOTNULL,
+   INDEX blog_id (blog_id)
+";
 
-   $Tables[18]["desc"] = "Gallery Albums";
-   $Tables[18]["code"] = "CREATE TABLE {dbprefix}gallery_albums (
-   id int(10) NOT NULL auto_increment,
-   owner_id int(10) NOT NULL default '0',
-   description text NOT NULL,
-   name varchar(255) NOT NULL default '',
-   flags int(10) NOT NULL default '0',
-   parent_id int(10) NOT NULL default '0',
-   date timestamp(14) NOT NULL,
-   properties text NOT NULL DEFAULT '',
-   show_album TINYINT(1) DEFAULT 1,
-   normalized_description text NOT NULL default '',
-   normalized_name varchar(255) NOT NULL default '',
-   mangled_name varchar(255) NOT NULL default '',
-   num_resources int(10) NOT NULL default '0',
-   num_children int(10) NOT NULL default '0',
-   PRIMARY KEY  (id),
-   KEY parent_id (parent_id),
-   KEY owner_id (owner_id),
-   KEY mangled_name (mangled_name),
-   KEY owner_id_mangled_name (owner_id, mangled_name),
-   FULLTEXT KEY normalized_name (normalized_name),
-   FULLTEXT KEY normalized_description (normalized_description),
-   FULLTEXT KEY normalized_fields (normalized_name, normalized_description)
-   ) TYPE=MyISAM;";
+$Tables["host_blocking_rules"] = "
+   id I(10) NOTNULL AUTOINCREMENT PRIMARY,
+   reason X,
+   date T(14) NOTNULL,
+   blog_id I(10) NOTNULL DEFAULT '0',
+   block_type I(1) DEFAULT '1',
+   list_type I(1) DEFAULT '1',
+   mask I(2) DEFAULT '0',
+   host varchar(15) DEFAULT '0.0.0.0',
+   INDEX blog_id (blog_id)
+";
 
-   $Tables[19]["desc"] = "Bayesian Filter data table";
-   $Tables[19]["code"] = "CREATE TABLE {dbprefix}bayesian_filter_info (
-   id int(10) unsigned NOT NULL auto_increment,
-   blog_id int(10) unsigned default NULL,
-   total_spam int(10) unsigned default NULL,
-   total_nonspam int(10) unsigned default NULL,
-   PRIMARY KEY  (id),
-   KEY blog_id (blog_id)
-   ) TYPE=MyISAM;";
+$Tables["gallery_resources"] = "
+   id I(10) NOTNULL AUTOINCREMENT PRIMARY,
+   owner_id I(10) NOTNULL DEFAULT '0',
+   album_id I(10) NOTNULL DEFAULT '0',
+   description X,
+   date T(14) NOTNULL,
+   flags I(10) DEFAULT '0',
+   resource_type I(3) DEFAULT NULL,
+   file_path C(255) DEFAULT '',
+   file_name C(255) DEFAULT '',
+   file_size I(20) NOTNULL DEFAULT 0,
+   metadata X,
+   thumbnail_format varchar(4) NOTNULL DEFAULT 'same',
+   normalized_description X NOTNULL DEFAULT '',
+   properties X NOTNULL DEFAULT '',
+   INDEX album_id (album_id),
+   INDEX owner_id (owner_id),
+   INDEX file_name (file_name),
+   INDEX album_id_owner_id (album_id, owner_id),
+   INDEX resource_type (resource_type),
+   FULLTEXT normalized_description (normalized_description)
+";
 
-   $Tables[20]["desc"] = "Bayesian Filter Information";
-   $Tables[20]["code"] = "CREATE TABLE {dbprefix}bayesian_tokens (
-   id int(10) unsigned NOT NULL auto_increment,
-   blog_id int(10) unsigned default NULL,
-   token char(100) default NULL,
-   spam_occurrences int(10) unsigned default NULL,
-   nonspam_occurrences int(10) unsigned default NULL,
-   prob float default NULL,
-   PRIMARY KEY  (id),
-   KEY blog_id (blog_id),
-   KEY token (token)
-   ) TYPE=MyISAM;";
+$Tables["gallery_albums"] = "
+   id I(10) NOTNULL AUTOINCREMENT PRIMARY,
+   owner_id I(10) NOTNULL DEFAULT '0',
+   description X NOTNULL,
+   name C(255) NOTNULL DEFAULT '',
+   flags I(10) NOTNULL DEFAULT '0',
+   parent_id I(10) NOTNULL DEFAULT '0',
+   date T(14) NOTNULL,
+   properties X NOTNULL DEFAULT '',
+   show_album I1 DEFAULT 1,
+   normalized_description X NOTNULL DEFAULT '',
+   normalized_name C(255) NOTNULL DEFAULT '',
+   mangled_name C(255) NOTNULL DEFAULT '',
+   num_resources I(10) NOTNULL DEFAULT '0',
+   num_children I(10) NOTNULL DEFAULT '0',
+   INDEX parent_id (parent_id),
+   INDEX owner_id (owner_id),
+   INDEX mangled_name (mangled_name),
+   INDEX owner_id_mangled_name (owner_id, mangled_name),
+   FULLTEXT normalized_name (normalized_name),
+   FULLTEXT normalized_description (normalized_description),
+   FULLTEXT normalized_fields (normalized_name, normalized_description)
+";
 
-   $Tables[21]["desc"] = "Map of categories to articles";
-   $Tables[21]["code"] = "CREATE TABLE {dbprefix}article_categories_link(
-   article_id int(10) NOT NULL,
-   category_id int(10) NOT NULL,
-   PRIMARY KEY (article_id, category_id)
-   ) TYPE=MyISAM;";
+$Tables["bayesian_filter_info"] = "
+   id I(10) UNSIGNED NOTNULL AUTOINCREMENT PRIMARY,
+   blog_id I(10) UNSIGNED DEFAULT NULL,
+   total_spam I(10) UNSIGNED DEFAULT NULL,
+   total_nonspam I(10) UNSIGNED DEFAULT NULL,
+   INDEX blog_id (blog_id)
+";
 
-   $Tables[22]["desc"] = "Definition of custom fields";
-   $Tables[22]["code"] = "CREATE TABLE {dbprefix}custom_fields_definition (
-   id int(10) NOT NULL auto_increment,
-   field_name varchar(255) NOT NULL default '',
-   field_description text NOT NULL,
-   field_type int(2) NOT NULL default '1',
-   blog_id int(10) NOT NULL default '0',
-   date TIMESTAMP(14),
-   searchable TINYINT(1) default 1,
-   hidden TINYINT(1) default 1,
-   PRIMARY KEY  (id,field_name),
-   KEY blog_id (blog_id)
-   ) TYPE=MyISAM;";
+$Tables["bayesian_tokens"] = "
+   id I(10) UNSIGNED NOTNULL AUTOINCREMENT PRIMARY,
+   blog_id I(10) UNSIGNED DEFAULT NULL,
+   token char(100) DEFAULT NULL,
+   spam_occurrences I(10) UNSIGNED DEFAULT NULL,
+   nonspam_occurrences I(10) UNSIGNED DEFAULT NULL,
+   prob F DEFAULT NULL,
+   INDEX blog_id (blog_id),
+   INDEX token (token)
+";
 
-   $Tables[23]["desc"] = "Custom fields that have been assigned to articles";
-   $Tables[23]["code"] = "CREATE TABLE {dbprefix}custom_fields_values (
-   id int(10) NOT NULL auto_increment,
-   field_id int(10) NOT NULL default '0',
-   field_value text NOT NULL,
-   normalized_value text NOT NULL,
-   blog_id int(10) default NULL,
-   article_id int(10) default NULL,
-   PRIMARY KEY  (id),
-   FULLTEXT KEY normalized_value (normalized_value),
-   KEY blog_id (blog_id),
-   KEY article_id (article_id),
-   KEY field_id (field_id),
-   KEY blog_id_article_id (blog_id, article_id)
-   ) TYPE=MyISAM;";
+$Tables["article_categories_link"] = "
+   article_id I(10) NOTNULL PRIMARY,
+   category_id I(10) NOTNULL PRIMARY
+";
 
-   $Tables[24]["desc"] = "Text of the articles";
-   $Tables[24]["code"] = "CREATE TABLE {dbprefix}articles_text (
-   id int(10) NOT NULL auto_increment,
-   article_id int(10) NOT NULL DEFAULT 0,
-   text TEXT NOT NULL DEFAULT '',
-   topic TEXT NOT NULL DEFAULT '',
-   normalized_text TEXT NOT NULL DEFAULT '',
-   normalized_topic TEXT NOT NULL DEFAULT '',
-   mangled_topic text NOT NULL,
-   PRIMARY KEY (id),
-   KEY article_id (article_id),
-   FULLTEXT KEY normalized_text (normalized_text),
-   FULLTEXT KEY normalized_topic (normalized_topic),
-   FULLTEXT KEY normalized_fields (normalized_text, normalized_topic)
-   ) TYPE=MyISAM;";
+$Tables["custom_fields_definition"] = "
+   id I(10) NOTNULL AUTOINCREMENT PRIMARY,
+   field_name C(255) NOTNULL DEFAULT '' PRIMARY,
+   field_description X NOTNULL,
+   field_type I(2) NOTNULL DEFAULT '1',
+   blog_id I(10) NOTNULL DEFAULT '0',
+   date T(14),
+   searchable I1 DEFAULT 1,
+   hidden I1 DEFAULT 1,
+   INDEX blog_id (blog_id)
+";
+
+$Tables["custom_fields_values"] = "
+   id I(10) NOTNULL AUTOINCREMENT PRIMARY,
+   field_id I(10) NOTNULL DEFAULT '0',
+   field_value X NOTNULL,
+   normalized_value X NOTNULL,
+   blog_id I(10) DEFAULT NULL,
+   article_id I(10) DEFAULT NULL,
+   FULLTEXT normalized_value (normalized_value),
+   INDEX blog_id (blog_id),
+   INDEX article_id (article_id),
+   INDEX field_id (field_id),
+   INDEX blog_id_article_id (blog_id, article_id)
+";
+
+$Tables["articles_text"] = "
+   id I(10) NOTNULL AUTOINCREMENT PRIMARY,
+   article_id I(10) NOTNULL DEFAULT 0,
+   text X NOTNULL DEFAULT '',
+   topic X NOTNULL DEFAULT '',
+   normalized_text X NOTNULL DEFAULT '',
+   normalized_topic X NOTNULL DEFAULT '',
+   mangled_topic X NOTNULL,
+   INDEX article_id (article_id),
+   FULLTEXT normalized_text (normalized_text),
+   FULLTEXT normalized_topic (normalized_topic),
+   FULLTEXT normalized_fields (normalized_text, normalized_topic)
+";
    
-   $Tables[25]["desc"] = "PhpBB2 User Integration Table";
-   $Tables[25]["code"] = "CREATE TABLE {dbprefix}phpbb2_users (
-   id int(10) unsigned NOT NULL auto_increment,
-   phpbb_id int(10) unsigned NOT NULL,
-   full_name varchar(255) NOT NULL default '',
-   about text,
-   properties TEXT NOT NULL default '',
-   resource_picture_id integer(10) NOT NULL DEFAULT 0,
-   status INTEGER(10) NOT NULL DEFAULT 0,
-   PRIMARY KEY  (id),
-   UNIQUE KEY phpbb_id(phpbb_id)
-   ) TYPE=MyISAM;";
+$Tables["phpbb2_users"] = "
+   id I(10) UNSIGNED NOTNULL AUTOINCREMENT PRIMARY,
+   phpbb_id I(10) UNSIGNED NOTNULL,
+   full_name C(255) NOTNULL DEFAULT '',
+   about X,
+   properties X NOTNULL DEFAULT '',
+   resource_picture_id I(10) NOTNULL DEFAULT 0,
+   status I(10) NOTNULL DEFAULT 0,
+   UNIQUE phpbb_id(phpbb_id)
+";
    
-   /*** new in 1.1 ***/   
-   $Tables[26]["desc"] = "Blog categories";
-   $Tables[26]["code"] = "CREATE TABLE {dbprefix}blog_categories (
-   id int(10) unsigned NOT NULL auto_increment,
-   name varchar(255) NOT NULL default '',
-   description varchar(255) NOT NULL default '',
-   mangled_name varchar(255) NOT NULL default '',
-   properties TEXT NOT NULL DEFAULT '',
-   num_blogs int(10) NOT NULL default '0',
-   num_active_blogs int(10) NOT NULL default '0',
-   PRIMARY KEY (id),
-   KEY mangled_name(mangled_name)
-   ) TYPE=MyISAM;";
+$Tables["blog_categories"] = "
+   id I(10) UNSIGNED NOTNULL AUTOINCREMENT PRIMARY,
+   name C(255) NOTNULL DEFAULT '',
+   description C(255) NOTNULL DEFAULT '',
+   mangled_name C(255) NOTNULL DEFAULT '',
+   properties X NOTNULL DEFAULT '',
+   num_blogs I(10) NOTNULL DEFAULT '0',
+   num_active_blogs I(10) NOTNULL DEFAULT '0',
+   INDEX mangled_name(mangled_name)
+";
    
-   $Tables[27]["desc"] = "Global Article categories";
-   $Tables[27]["code"] = "CREATE TABLE {dbprefix}global_articles_categories (
-   id int(10) unsigned NOT NULL auto_increment,
-   name varchar(255) NOT NULL default '',
-   description varchar(255) NOT NULL default '',
-   mangled_name varchar(255) NOT NULL default '',
-   properties TEXT NOT NULL DEFAULT '',
-   num_articles int(10) NOT NULL default '0',
-   num_active_articles int(10) NOT NULL default '0',
-   PRIMARY KEY (id),
-   KEY mangled_name(mangled_name)
-   ) TYPE=MyISAM;";
+$Tables["global_articles_categories"] = "
+   id I(10) UNSIGNED NOTNULL AUTOINCREMENT PRIMARY,
+   name C(255) NOTNULL DEFAULT '',
+   description C(255) NOTNULL DEFAULT '',
+   mangled_name C(255) NOTNULL DEFAULT '',
+   properties X NOTNULL DEFAULT '',
+   num_articles I(10) NOTNULL DEFAULT '0',
+   num_active_articles I(10) NOTNULL DEFAULT '0',
+   INDEX mangled_name(mangled_name)
+";
 
 ?>
\ No newline at end of file

Modified: plog/trunk/wizard.php
===================================================================
--- plog/trunk/wizard.php	2006-03-29 16:49:04 UTC (rev 3152)
+++ plog/trunk/wizard.php	2006-03-29 16:54:08 UTC (rev 3153)
@@ -68,8 +68,6 @@
 
     define( "TEMP_FOLDER", "./tmp" );
 
-    // sql querys used to create the tables
-
     // maps used to map requests with actions
     $_actionMap["Checks"] = "WizardChecks";
     $_actionMap["Default"] = "WizardChecks";
@@ -82,6 +80,13 @@
     $_actionMap["Update1"] = "UpdateStepOne";
     $_actionMap["Update2"] = "UpdateStepTwo";
     $_actionMap["Update3"] = "UpdateStepThree";
+    
+    //
+    // array with tables added in 1.1
+    //
+    $tables_11_new = Array(
+      "phpbb2_users",  "blog_categories", "global_articles_categories"
+    );
 
     /**
      * Open a connection to the database
@@ -587,18 +592,32 @@
 
             // reconnect using the new database.
             $config = new ConfigFileStorage();
-            $this->_db->Connect($config->getValue( "db_host" ), $config->getValue( "db_username" ), $config->getValue( "db_password" ), $config->getValue( "db_database" ));
+            $this->_db->Connect( $config->getValue( "db_host" ), 
+                                 $config->getValue( "db_username" ), 
+                                 $config->getValue( "db_password" ), 
+                                 $config->getValue( "db_database" ));
+                                 
+            // create a data dictionary to give us the right sql code needed to create the tables
+            $dict = NewDataDictionary( $this->_db );
 
             // create the tables
-            $errors = false;
-            foreach( $Tables as $table ) {
-                $query = str_replace( "{dbprefix}", $this->_dbPrefix, $table["code"] );
-                if( $this->_db->Execute( $query ))
-                    $message .= "Table <strong>".$table["desc"]."</strong> created successfully.<br/>";
+            $errors = false;            
+            
+            foreach( $Tables as $name => $table ) {
+                $sqlarray = $dict->CreateTableSQL( $this->_dbPrefix.$name, $table );
+                
+                // each table may need more than one sql query because of indexes, triggers, etc...
+                $ok = true;
+                foreach( $sqlarray as $sql ) {
+                    $ok = ( $ok && $this->_db->Execute( $sql ));
+                }
+                
+                if( $ok )
+                    $message .= "Table <strong>$name</strong> created successfully.<br/>";
                 else {
-                    $message .= "Error creating table: ".$this->_db->ErrorMsg()."<br/>";
+                    $message .= "Error creating table $name: ".$this->_db->ErrorMsg()."<br/>";
                     $errors = true;
-                }
+                }                    
             }
 
             if( $errors ) {
@@ -939,11 +958,7 @@
 
         function perform()
         {
-            // we need to create the new tables here
-            $newTables = Array( 25, 26, 27 );
-
             global $Tables;
-            global $Changes;
             global $Inserts;
 
             // connect to the db
@@ -962,43 +977,64 @@
             // ---
             // create the new tables in 1.0
             // ---
-            // loop only through the new tables
-            foreach( $newTables as $tableId ) {
-                // get info about the table we'd like to use
-                $table = $Tables[$tableId];
-                // and run the query
-                $query = str_replace( "{dbprefix}", $this->_dbPrefix, $table["code"] );
-                if( $this->_db->Execute( $query ))
-                    $message .= "New table <strong>".$table["desc"]."</strong> created successfully.<br/>";
+            /*global $tables_11_new;
+            
+            $dict = NewDataDictionary( $this->_db );            
+            
+            $errors = false;
+            foreach( $tables_11_new as $name => $table ) {
+            
+                $sqlarray = $dict->CreateTableSQL( $this->_dbPrefix.$name, $table );
+            
+                // each table may need more than one sql query because of indexes, triggers, etc...
+                $ok = true;
+                foreach( $sqlarray as $sql ) {
+                    $ok = ( $ok && $this->_db->Execute( $sql ));
+                }
+                
+                if( $ok )
+                    $message .= "Table <strong>$name</strong> created successfully.<br/>";
                 else {
-                    $message .= "Error creating table: ".$this->_db->ErrorMsg()."<br/>";
+                    $message .= "Error creating table $name: ".$this->_db->ErrorMsg()."<br/>";
                     $errors = true;
-                }
+                } 
             }
+            
             if( !$errors ) {
                 $message .= "** New tables created successfully **<br/><br/>";
-            }
+            }*/
 
             // ---
-            // make changes to the tables that need changes
+            // make changes to the tables that need changes, but leave it up to the data dictionary
+            // to take care of the changes
             // ---
-            foreach( $Changes as $tableId => $changeTable) {
-                $errorMessage = "";            
-                foreach( $changeTable as $changeCode ) {
+            $dict = NewDataDictionary( $this->_db );            
+            $errors = false;
+            foreach( $Tables as $name => $table ) {
+                $errorMessage = "";
+                
+                // generate the code with the changes for the table
+                $sqlarray = $dict->ChangeTableSQL( $this->_dbPrefix.$name, $table );
+                
+                //print("<hr/><pre>");
+                foreach( $sqlarray as $sql ) {
+                    //print($sql."<br/>");
                     // and run the query
-                    $query = str_replace( "{dbprefix}", $this->_dbPrefix, $changeCode );
-                    if( !$this->_db->Execute( $query )) {
-                        if($tableId != "Deletions"){
+                    if( !$this->_db->Execute( $sql )) {
+                        /*if($tableId != "Deletions"){
                             $errors = true;                    
                             $errorMessage .= $this->_db->ErrorMsg()."<br/>";
-                        }
+                        }*/
+                        $errors = true;
+                        $errorMessage .= $this->_db->ErrorMsg()."<br/>";
                     }
+                    //print("</pre>");
                 }
 
                 if( !$errors )
-                    $message .= "Changes to table <strong>$tableId</strong> executed successfully.<br/>";
+                    $message .= "Changes to table <strong>$name</strong> executed successfully.<br/>";
                 else {
-                    $message .= "Error modifying table $tableId: ".$errorMessage;
+                    $message .= "Error modifying table $name: ".$errorMessage;
                 }
             }
 
@@ -1177,8 +1213,8 @@
             // total number of articles
             $query1 = "SELECT category_id, COUNT(*) AS total FROM ".$this->dbPrefix."article_categories_link GROUP BY category_id";
             // number of active articles
-            $query2 = "SELECT category_id, COUNT(*) AS total FROM ".$this->dbPrefix."article_categories_link l, ".$this->dbPrefix."articles a 
-                       WHERE a.id = l.article_id AND a.status = ".POST_STATUS_PUBLISHED." GROUP BY category_id";
+            $query2 = "SELECT l.category_id AS category_id, COUNT(*) AS total FROM ".$this->dbPrefix."article_categories_link l, ".
+                       $this->dbPrefix."articles a WHERE a.id = l.article_id AND a.status = ".POST_STATUS_PUBLISHED." GROUP BY l.category_id";
             // list of categories
             $query3 = "SELECT id FROM ".$this->dbPrefix."articles_categories";
                       
@@ -1210,6 +1246,7 @@
                 $this->message .= "Error loading categories (3)";
                 return false;
             }            
+            $numUpdated = 0;
             while( $row = $res3->FetchRow()) {
                 $catId = $row["id"];
                 // load the counters
@@ -1282,9 +1319,9 @@
                 $this->message .= "Error performing changes to the blogs table (2)";
                 return false;
             }
-            $numActiveArticles = Array();
+            $numComments = Array();
             while( $row = $res2->FetchRow()) {
-                $numActiveArticles[$row["blog_id"]] = $row["total"];
+                $numComments[$row["blog_id"]] = $row["total"];
             }
             
             // total number of trackbacks
@@ -1316,35 +1353,36 @@
             if( !$res4 ) {
                 $this->message .= "Error loading list of blogs";
                 return false;
-            }            
+            }
+            $numUpdated=0;
             while( $row = $res4->FetchRow()) {
                 $blogId = $row["id"];
                 // load the counters
-                $totalArticles = $numArticles[$blogId];
-                if( $totalArticles == '' ) $totalArticles = 0;
-                $totalComments = $numComments[$blogId];
-                if( $totalComments == '' ) $totalComments = 0;
-                $totalTrackbacks = $numTrackbacks[$blogId];
-                if( $totalTrackbacks == '' ) $totalTrackbacks = 0;                
-                $createDate = $createDates[$blogId];
-                if( $createDate == "" ) {
+                isset( $numArticles[$blogId] ) ? $totalArticles = $numArticles[$blogId] : $totalArticles = 0;
+                isset( $numComments[$blogId] ) ? $totalComments = $numComments[$blogId] : $totalComments = 0;
+                isset( $numTrackbacks[$blogId] ) ? $totalTrackbacks = $numTrackbacks[$blogId] :$totalTrackbacks = 0;
+                
+                if( isset( $createDates[$blogId] ))
+                    $createDate = $createDates[$blogId];
+                else {
                     $t = new Timestamp();
                     $createDate = $t->getTimestamp();
                 }
-                $lastUpdateDate = $lastUpdateDates[$blogId];
-                if( $lastUpdateDate == "" ) {
+                if( isset( $lastUpdateDates[$blogId] ))
+                    $lastUpdateDate = $lastUpdateDates[$blogId];
+                else {
                     $t = new Timestamp();
                     $lastUpdateDate = $t->getTimestamp();
                 }
                                 
                 // build the update query
-                print("blog update query=$query<br/>");
                 $query = "UPDATE ".$this->dbPrefix."blogs SET num_posts = {$totalArticles},
                           num_comments = {$totalComments},
                           num_trackbacks = {$totalTrackbacks},
                           create_date = '{$createDate}',
                           last_update_date = '{$lastUpdateDate}'
                           WHERE id = {$blogId}";
+                print("blog update query=$query<br/>");                          
                           
                 // and execute it
                 $result = $this->db->Execute( $query );
@@ -1381,7 +1419,7 @@
             
             // process all trackbacks and insert them again to the comments table
             include_once( PLOG_CLASS_PATH."class/data/textfilter.class.php" );
-            $totalAdded++;
+            $totalAdded=0;
             while( $row = $result->FetchRow()) {
                 // build the insert query
                 $insert = "INSERT INTO ".$this->dbPrefix."articles_comments
@@ -1429,12 +1467,12 @@
             if( !$res2 ) {
                 $this->message .= "Error loading link categories";
                 return false;
-            }            
+            }
+            $numUpdated = 0;
             while( $row = $res2->FetchRow()) {
                 $catId = $row["id"];
                 // load the counters
-                $totalLinks = $numLinks[$catId];
-                if( $totalLinks == '' ) $totalLinks = 0;
+                isset( $numLinks[$catId] ) ? $totalLinks = $numLinks[$catId] : $totalLinks = 0;
                 
                 // build the update query
                 $query = "UPDATE ".$this->dbPrefix."mylinks_categories SET num_links = {$totalLinks}, last_modification = last_modification
@@ -1535,10 +1573,9 @@
             $numUpdated = 0;
             while( $row = $res3->FetchRow()) {
                 $albumId = $row["id"];
-                $resources = $numResources[$albumId];
-                if( $resources == "" ) $resources = 0;
-                $children = $numChildren[$albumId];
-                if( $children == "" ) $children = 0;                
+                isset( $numResources[$albumId] ) ? $resources = $numResources[$albumId] : $resources = 0;
+                isset( $numChildren[$albumId]) ? $children = $numChildren[$albumId] : $children = 0;
+                
                 // build the query
                 $query = "UPDATE ".$this->dbPrefix."gallery_albums
                           SET num_children = {$children}, num_resources = {$resources}, date = date



More information about the pLog-svn mailing list