[pLog-svn] r623 - plog/trunk
oscar at devel.plogworld.net
oscar at devel.plogworld.net
Thu Dec 30 14:58:33 GMT 2004
Author: oscar
Date: 2004-12-30 14:58:33 +0000 (Thu, 30 Dec 2004)
New Revision: 623
Modified:
plog/trunk/wizard.php
Log:
added additional indexes/keys to all the tables that need them, both when they are created as new and when we upgrade from 0.3.x to 1.0. Also, reworked a bit the bit that upgrades to 1.0. Please test and report any problems!
Modified: plog/trunk/wizard.php
===================================================================
--- plog/trunk/wizard.php 2004-12-30 14:13:09 UTC (rev 622)
+++ plog/trunk/wizard.php 2004-12-30 14:58:33 UTC (rev 623)
@@ -7,7 +7,7 @@
//
// enable this for debugging purposes
//
- define( "DB_WIZARD_DEBUG", false );
+ define( "DB_WIZARD_DEBUG", true );
// many hosts don't have this enabled and we, for the time being, need it...
ini_set("arg_seperator.output", "&");
@@ -67,7 +67,14 @@
num_reads int(10) default '0',
properties TEXT NOT NULL default '',
slug varchar(255) NOT NULL,
- PRIMARY KEY (id)
+ 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";
@@ -82,7 +89,10 @@
description TEXT NOT NULL DEFAULT '',
properties text NOT NULL default '',
mangled_name varchar(255) NOT NULL default '',
- PRIMARY KEY (id)
+ PRIMARY KEY (id),
+ KEY parent_id (parent_id),
+ KEY blog_id (blog_id),
+ KEY mangled_name (mangled_name)
) TYPE=MyISAM;";
$Tables[2]["desc"] = "Comments";
@@ -103,7 +113,12 @@
properties TEXT NOT NULL DEFAULT '',
normalized_text TEXT NOT NULL DEFAULT '',
normalized_topic TEXT NOT NULL DEFAULT '',
- PRIMARY KEY (id)
+ PRIMARY KEY (id),
+ KEY parent_id (parent_id),
+ KEY article_id (article_id),
+ FULLTEXT KEY normalized_fields (normalized_text,normalized_topic),
+ FULLTEXT KEY normalized_text (normalized_text),
+ FULLTEXT KEY normalized_topic (normalized_topic)
) TYPE=MyISAM;";
$Tables[3]["desc"] = "Notifications";
@@ -112,7 +127,10 @@
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)
+ PRIMARY KEY (id),
+ KEY article_id (article_id),
+ KEY user_id (user_id),
+ KEY blog_id (blog_id)
) TYPE=MyISAM;";
$Tables[4]["desc"] = "Blogs";
@@ -125,7 +143,9 @@
mangled_blog varchar(50) NOT NULL default '',
status int(4) NOT NULL default '1',
show_in_summary int(4) not null default '1',
- PRIMARY KEY (id)
+ PRIMARY KEY (id),
+ KEY owner_id (owner_id),
+ KEY mangled_blog (mangled_blog)
) TYPE=MyISAM;";
$Tables[5]["desc"] = "MyLinks";
@@ -139,7 +159,9 @@
rss_feed varchar(255) not null default '',
date timestamp(14) not null,
properties TEXT NOT NULL DEFAULT '',
- PRIMARY KEY (id)
+ PRIMARY KEY (id),
+ KEY blog_id (blog_id),
+ KEY category_id (category_id)
) TYPE=MyISAM;";
$Tables[6]["desc"] = "MyLinks Categories";
@@ -149,7 +171,8 @@
blog_id int(10) NOT NULL default '0',
last_modification timestamp(14) NOT NULL,
properties TEXT NOT NULL DEFAULT '',
- PRIMARY KEY (id)
+ PRIMARY KEY (id),
+ KEY blog_id (blog_id)
) TYPE=MyISAM;";
$Tables[7]["desc"] = "MyRecent";
@@ -180,7 +203,7 @@
PRIMARY KEY (id)
) TYPE=MyISAM;";
- $Tables[10]["desc"] = "Referers";
+ $Tables[10]["desc"] = "Referrers";
$Tables[10]["code"] = "CREATE TABLE {dbprefix}referers (
id int(10) NOT NULL auto_increment,
url text NOT NULL,
@@ -188,7 +211,10 @@
blog_id int(10) NOT NULL default '0',
hits int(10) default '1',
last_date timestamp(14),
- PRIMARY KEY (id)
+ 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[11]["desc"] = "Trackbacks";
@@ -201,13 +227,14 @@
blog_name varchar(255) default '',
date timestamp(14) NOT NULL,
properties TEXT NOT NULL DEFAULT '',
- PRIMARY KEY (id)
+ PRIMARY KEY (id),
+ KEY article_id (article_id)
) TYPE=MyISAM;";
$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 '' UNIQUE,
+ 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 '',
@@ -215,7 +242,8 @@
properties TEXT NOT NULL default '',
status integer(4) NOT NULL DEFAULT 1,
resource_picture_id integer(10) NOT NULL DEFAULT 0,
- PRIMARY KEY (id)
+ PRIMARY KEY (id),
+ UNIQUE KEY user (user)
) TYPE=MyISAM;";
$Tables[13]["desc"] = "Permissions";
@@ -224,7 +252,10 @@
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)
+ PRIMARY KEY (id),
+ KEY user_id (user_id),
+ KEY blog_id (blog_id),
+ KEY user_id_permission_id (user_id,permission_id)
) TYPE=MyISAM;";
@@ -237,14 +268,15 @@
PRIMARY KEY (id,config_key)
) TYPE=MyISAM;";
- $Tables[15]["desc"] = "Blocked hosts";
+ $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)
+ PRIMARY KEY (id),
+ KEY blog_id (blog_id)
) TYPE=MyISAM;";
$Tables[16]["desc"] = "Blocked content";
@@ -257,7 +289,8 @@
list_type int(1) default '1',
mask int(2) default '0',
host varchar(15) default '0.0.0.0',
- PRIMARY KEY (id)
+ PRIMARY KEY (id),
+ KEY blog_id (blog_id)
) TYPE=MyISAM;";
$Tables[17]["desc"] = "Gallery Resources";
@@ -276,6 +309,11 @@
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;";
@@ -294,6 +332,10 @@
normalized_name varchar(255) NOT NULL default '',
mangled_name varchar(255) NOT NULL default '',
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)
@@ -305,7 +347,8 @@
blog_id int(10) unsigned default NULL,
total_spam int(10) unsigned default NULL,
total_nonspam int(10) unsigned default NULL,
- PRIMARY KEY (id)
+ PRIMARY KEY (id),
+ KEY blog_id (blog_id)
) TYPE=MyISAM;";
$Tables[20]["desc"] = "Bayesian Filter Information";
@@ -338,7 +381,8 @@
date TIMESTAMP(14),
searchable TINYINT(1) default 1,
hidden TINYINT(1) default 1,
- PRIMARY KEY (id,field_name)
+ PRIMARY KEY (id,field_name),
+ KEY blog_id (blog_id)
) TYPE=MyISAM;";
$Tables[23]["desc"] = "Custom fields that have been assigned to articles";
@@ -350,7 +394,11 @@
blog_id int(10) default NULL,
article_id int(10) default NULL,
PRIMARY KEY (id),
- FULLTEXT KEY normalized_value (normalized_value)
+ 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[24]["desc"] = "Text of the articles";
@@ -363,6 +411,7 @@
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))";
@@ -371,52 +420,94 @@
// ---
// changes needed to update from 0.3 to 1.0
- $Changes["Articles"] = Array( "ALTER TABLE {dbprefix}articles ADD COLUMN normalized_text TEXT NOT NULL DEFAULT ''",
- "ALTER TABLE {dbprefix}articles ADD COLUMN normalized_topic TEXT NOT NULL DEFAULT ''",
- "ALTER TABLE {dbprefix}articles ADD COLUMN mangled_topic TEXT NOT NULL DEFAULT ''",
- "ALTER TABLE {dbprefix}articles ADD FULLTEXT normalized_text(normalized_text)",
- "ALTER TABLE {dbprefix}articles ADD FULLTEXT normalized_topic(normalized_topic)",
- "ALTER TABLE {dbprefix}articles ADD FULLTEXT normalized_text_topic(normalized_topic,normalized_text)",
- "ALTER TABLE {dbprefix}articles CHANGE status old_status ENUM('published', 'draft', 'deleted' )",
+ $Changes["Articles"] = Array( "ALTER TABLE {dbprefix}articles CHANGE status old_status ENUM('published', 'draft', 'deleted' )",
"ALTER TABLE {dbprefix}articles ADD COLUMN status INTEGER(5) NOT NULL DEFAULT 1",
- "ALTER TABLE {dbprefix}articles ADD COLUMN slug VARCHAR(255) NOT NULL DEFAULT ''" );
+ "ALTER TABLE {dbprefix}articles ADD COLUMN slug VARCHAR(255) NOT NULL DEFAULT ''",
+ "ALTER TABLE {dbprefix}articles ADD KEY num_reads(num_reads)",
+ "ALTER TABLE {dbprefix}articles ADD KEY category_id(category_id)",
+ "ALTER TABLE {dbprefix}articles ADD KEY blog_id(blog_id)",
+ "ALTER TABLE {dbprefix}articles ADD KEY slug(slug)",
+ "ALTER TABLE {dbprefix}articles ADD KEY user_id(user_id)",
+ "ALTER TABLE {dbprefix}articles ADD KEY blog_id_slug(blog_id,slug)",
+ "ALTER TABLE {dbprefix}articles ADD KEY blog_id_slug_category_id(blog_id,slug,category_id)");
$Changes["Article Categories"] = Array( "ALTER TABLE {dbprefix}articles_categories ADD COLUMN parent_id INTEGER(10) NOT NULL DEFAULT 0",
- "ALTER TABLE {dbprefix}articles_categories ADD COLUMN mangled_name TEXT NOT NULL DEFAULT ''",
+ "ALTER TABLE {dbprefix}articles_categories ADD COLUMN mangled_name VARCHAR(255) NOT NULL DEFAULT ''",
"ALTER TABLE {dbprefix}articles_categories ADD COLUMN properties TEXT NOT NULL DEFAULT ''",
- "ALTER TABLE {dbprefix}articles_categories ADD COLUMN description TEXT NOT NULL DEFAULT ''" );
+ "ALTER TABLE {dbprefix}articles_categories ADD COLUMN description TEXT NOT NULL DEFAULT ''",
+ "ALTER TABLE {dbprefix}articles_categories ADD KEY parent_id(parent_id)",
+ "ALTER TABLE {dbprefix}articles_categories ADD KEY blog_id(blog_id)",
+ "ALTER TABLE {dbprefix}articles_categories ADD KEY mangled_name(mangled_name)");
+
$Changes["User Comments"] = Array( "ALTER TABLE {dbprefix}articles_comments ADD COLUMN normalized_text TEXT NOT NULL DEFAULT ''",
"ALTER TABLE {dbprefix}articles_comments ADD COLUMN normalized_topic TEXT NOT NULL DEFAULT ''",
"ALTER TABLE {dbprefix}articles_comments ADD FULLTEXT normalized_text(normalized_text)",
"ALTER TABLE {dbprefix}articles_comments ADD FULLTEXT normalized_topic(normalized_topic)",
- "ALTER TABLE {dbprefix}articles_comments ADD FULLTEXT normalized_text_topic(normalized_topic,normalized_text)" );
+ "ALTER TABLE {dbprefix}articles_comments ADD FULLTEXT normalized_text_topic(normalized_topic,normalized_text)",
+ "ALTER TABLE {dbprefix}articles_comments ADD KEY parent_id(parent_id)",
+ "ALTER TABLE {dbprefix}articles_comments ADD KEY article_id(article_id)");
+
+ $Changes["Notifications"] = Array( "ALTER TABLE {dbprefix}articles_notifications ADD KEY article_id(article_id)",
+ "ALTER TABLE {dbprefix}articles_notifications ADD KEY user_id(user_id)",
+ "ALTER TABLE {dbprefix}articles_notifications ADD KEY blog_id(blog_id)");
$Changes["Blogs"] = Array( "ALTER TABLE {dbprefix}blogs ADD COLUMN mangled_blog VARCHAR(50) NOT NULL DEFAULT ''",
"ALTER TABLE {dbprefix}blogs ADD COLUMN properties TEXT NOT NULL DEFAULT ''",
"ALTER TABLE {dbprefix}blogs ADD COLUMN status INTEGER(4) NOT NULL DEFAULT '1'",
- "ALTER TABLE {dbprefix}blogs ADD COLUMN show_in_summary INTEGER(4) NOT NULL DEFAULT '1'" );
+ "ALTER TABLE {dbprefix}blogs ADD COLUMN show_in_summary INTEGER(4) NOT NULL DEFAULT '1'",
+ "ALTER TABLE {dbprefix}blogs ADD KEY owner_id(owner_id)",
+ "ALTER TABLE {dbprefix}blogs ADD KEY mangled_blog(mangled_blog)");
+
+ $Changes["Referrers"] = Array( "ALTER TABLE {dbprefix}referers ADD KEY article_id(article_id)",
+ "ALTER TABLE {dbprefix}referers ADD KEY blog_id(blog_id)",
+ "ALTER TABLE {dbprefix}referers ADD KEY blog_id_article_id (blog_id, article_id)");
+
+ $Changes["Trackbacks"] = Array( "ALTER TABLE {dbprefix}trackbacks ADD KEY article_id(article_id)" );
+
+ $Changes["Filtered Content"] = Array( "ALTER TABLE {dbprefix}filtered_content ADD KEY blog_id(blog_id)" );
+
+ $Changes["Blocked hosts"] = Array( "ALTER TABLE {dbprefix}host_blocking_rules ADD KEY blog_id(blog_id)" );
$Changes["Gallery Albums"] = Array( "ALTER TABLE {dbprefix}gallery_albums ADD COLUMN normalized_name VARCHAR(255) NOT NULL DEFAULT ''",
"ALTER TABLE {dbprefix}gallery_albums ADD COLUMN normalized_description TEXT NOT NULL DEFAULT ''",
"ALTER TABLE {dbprefix}gallery_albums ADD COLUMN mangled_name VARCHAR(255) NOT NULL DEFAULT ''",
"ALTER TABLE {dbprefix}gallery_albums ADD FULLTEXT normalized_name(normalized_name)",
"ALTER TABLE {dbprefix}gallery_albums ADD FULLTEXT normalized_description(normalized_description)",
- "ALTER TABLe {dbprefix}gallery_albums ADD FULLTEXT normalized_name_description(normalized_name, normalized_description)");
+ "ALTER TABLE {dbprefix}gallery_albums ADD FULLTEXT normalized_name_description(normalized_name, normalized_description)",
+ "ALTER TABLE {dbprefix}gallery_albums ADD KEY parent_id(parent_id)",
+ "ALTER TABLE {dbprefix}gallery_albums ADD KEY owner_id(owner_id)",
+ "ALTER TABLE {dbprefix}gallery_albums ADD KEY mangled_name(mangled_name)",
+ "ALTER TABLE {dbprefix}gallery_albums ADD KEY owner_id_mangled_name(owner_id,mangled_name)");
$Changes["Gallery Resources"] = Array( "ALTER TABLE {dbprefix}gallery_resources ADD COLUMN normalized_description TEXT NOT NULL DEFAULT ''",
- "ALTER TABLE {dbprefix}gallery_resources ADD FULLTEXT normalized_description(normalized_description)");
+ "ALTER TABLE {dbprefix}gallery_resources ADD FULLTEXT normalized_description(normalized_description)",
+ "ALTER TABLE {dbprefix}gallery_resources ADD KEY album_id(album_id)",
+ "ALTER TABLE {dbprefix}gallery_resources ADD KEY owner_id(owner_id)",
+ "ALTER TABLE {dbprefix}gallery_resources ADD KEY file_name(file_name)",
+ "ALTER TABLE {dbprefix}gallery_resources ADD KEY album_id_owner_id(album_id,owner_id)",
+ "ALTER TABLE {dbprefix}gallery_resources ADD KEY resource_type(resource_type)");
+
$Changes["Links"] = Array( "ALTER TABLE {dbprefix}mylinks ADD COLUMN properties TEXT NOT NULL DEFAULT ''",
"ALTER TABLE {dbprefix}mylinks ADD COLUMN date TIMESTAMP(14)",
- "ALTER TABLE {dbprefix}mylinks ADD COLUMN rss_feed VARCHAR(255) NOT NULL DEFAULT ''");
+ "ALTER TABLE {dbprefix}mylinks ADD COLUMN rss_feed VARCHAR(255) NOT NULL DEFAULT ''",
+ "ALTER TABLE {dbprefix}mylinks ADD KEY blog_id(blog_id)",
+ "ALTER TABLE {dbprefix}mylinks ADD KEY category_id(category_id)");
- $Changes["Links Categories"] = Array( "ALTER TABLE {dbprefix}mylinks_categories ADD COLUMN properties TEXT NOT NULL DEFAULT ''" );
+ $Changes["Links Categories"] = Array( "ALTER TABLE {dbprefix}mylinks_categories ADD COLUMN properties TEXT NOT NULL DEFAULT ''",
+ "ALTER TABLE {dbprefix}mylinks_categories ADD KEY blog_id(blog_id)");
$Changes["Users"] = Array( "ALTER TABLE {dbprefix}users ADD COLUMN full_name VARCHAR(255) NOT NULL DEFAULT ''",
"ALTER TABLE {dbprefix}users ADD COLUMN properties TEXT NOT NULL DEFAULT ''",
"ALTER TABLE {dbprefix}users ADD COLUMN resource_picture_id INTEGER(10) NOT NULL DEFAULT 0",
"ALTER TABLE {dbprefix}users ADD COLUMN status INTEGER(4) NOT NULL DEFAULT 1" );
+
+ $Changes["Bayesian Filter data table"] = Array( "ALTER TABLE {dbprefix}bayesian_filter_info ADD KEY blog_id(blog_id)" );
+
+ $Changes["Permissions"] = Array( "ALTER TABLE {dbprefix}users_permissions ADD KEY user_id(user_id)",
+ "ALTER TABLE {dbprefix}users_permissions ADD KEY blog_id(blog_id)",
+ "ALTER TABLE {dbprefix}users_permissions ADD KEY user_id_permission_id(user_id,permission_id)");
// ---
@@ -1364,26 +1455,31 @@
while( $row = $result->FetchRow()) {
// for each one of the articles, fill in the mangled_topic, normalized_text and normalized_value
- $normalizedTopic = $tf->normalizeText( $row["topic"] );
- $normalizedText = $tf->normalizeText( $row["text"] );
- $mangledTopic = $tf->urlize( $row["topic"] );
+ $normalizedTopic = Db::qstr($tf->normalizeText( $row["topic"] ));
+ $postTopic = Db::qstr($row["topic"]);
+ $normalizedText = Db::qstr($tf->normalizeText( $row["text"] ));
+ $postText = Db::qstr($row["text"]);
+ $mangledTopic = Db::qstr($tf->urlize( $row["topic"] ));
$status = $this->getStatusId( $row["old_status"] );
$artId = $row["id"];
$catId = $row["category_id"];
- $query = "UPDATE {$dbPrefix}articles SET normalized_topic = '$normalizedTopic',
- normalized_text = '$normalizedText',
- mangled_topic = '$mangledTopic', date = date, status = $status
+ $query = "UPDATE {$dbPrefix}articles SET slug = '$mangledTopic', date = date, status = $status
WHERE id = $artId";
$query2= "INSERT INTO {$dbPrefix}article_categories_link(article_id, category_id)
VALUES ( $artId, $catId )";
+
+ $query3= "INSERT INTO {$dbPrefix}articles_text (article_id,text,topic,normalized_text,normalized_topic,mangled_topic)
+ VALUES('$artId','$postText','$postTopic','$normalizedTopic','$normalizedText','$mangledTopic')";
+
// execute the two queries
$res = $db->Execute( $query );
$res2 = $db->Execute( $query2 );
+ $res3 = $db->Execute( $query3 );
- if( !$res || !$res2 )
+ if( !$res || !$res2 || !$res3 )
$errors = true;
}
@@ -1583,22 +1679,9 @@
function updateArticleText()
{
$dbPrefix = $this->dbPrefix;
- $query = "INSERT INTO ".$dbPrefix."articles_text(article_id,text,topic,normalized_text,normalized_topic,mangled_topic)".
- " SELECT old.id,old.text,old.topic,old.normalized_text,old.normalized_topic,old.mangled_topic ".
- "FROM ".$dbPrefix."articles as old";
- $result = $this->db->Execute( $query );
-
- if( !$result ) {
- $this->message .= "There was an error updating the articles_text table.<br/>";
- return false;
- }
-
// drop unneeded columns from articles table
$alter_query[] = "ALTER TABLE ".$dbPrefix."articles DROP COLUMN text";
$alter_query[] = "ALTER TABLE ".$dbPrefix."articles DROP COLUMN topic";
- $alter_query[] = "ALTER TABLE ".$dbPrefix."articles DROP COLUMN normalized_text";
- $alter_query[] = "ALTER TABLE ".$dbPrefix."articles DROP COLUMN normalized_topic";
- $alter_query[] = "ALTER TABLE ".$dbPrefix."articles DROP COLUMN mangled_topic";
foreach ($alter_query as $value) {
$this->db->Execute($value);
More information about the pLog-svn
mailing list