[pLog-svn] r2345 - plog/trunk
oscar at devel.plogworld.net
oscar at devel.plogworld.net
Mon Jul 18 07:37:27 GMT 2005
Author: oscar
Date: 2005-07-18 07:37:26 +0000 (Mon, 18 Jul 2005)
New Revision: 2345
Modified:
plog/trunk/wizard.php
Log:
new database structure in 1.1... I have de-normalized quite a few fields (mostly
the fields that count items in the relationship such as the number of items
that an album has, or the number of articles in a category), hoping that this
will simplify most of the SQL queries (we can get rid of most JOIN operations!)
and that things will get a bit faster, which they should. This also means
that all these new fields should be updated accordingly and that some
addXXX() or updateXXX() methods from the DAO classes will need an extra
SQL query to update them.
Modified: plog/trunk/wizard.php
===================================================================
--- plog/trunk/wizard.php 2005-07-17 18:19:22 UTC (rev 2344)
+++ plog/trunk/wizard.php 2005-07-18 07:37:26 UTC (rev 2345)
@@ -85,6 +85,8 @@
num_reads int(10) default '0',
properties TEXT NOT NULL default '',
slug varchar(255) NOT NULL,
+ num_comments int(10) NOT NULL default '0', // new in 1.1
+ num_nospam_comments int(10) NOT NULL default '0', // new in 1.1
PRIMARY KEY (id),
KEY num_reads (num_reads),
KEY category_id (category_id),
@@ -107,6 +109,8 @@
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, // new in 1.1
+ num_published_articles int(10) NOT NULL default 0, // new in 1.1
PRIMARY KEY (id),
KEY parent_id (parent_id),
KEY blog_id (blog_id),
@@ -117,6 +121,7 @@
$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', // new in 1.1
topic text NOT NULL,
text text,
date timestamp(14) NOT NULL,
@@ -135,6 +140,8 @@
PRIMARY KEY (id),
KEY parent_id (parent_id),
KEY article_id (article_id),
+ KEY blog_id (blog_id), // new in 1.1
+ KEY article_id_blog_id(article_id,blog_id), // new in 1.1
FULLTEXT KEY normalized_fields (normalized_text,normalized_topic),
FULLTEXT KEY normalized_text (normalized_text),
FULLTEXT KEY normalized_topic (normalized_topic)
@@ -163,6 +170,9 @@
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, // new in 1.1
+ last_update_date TIMESTAMP(14) NOT NULL, // new in 1.1
+ modification_date TIMESTAMP(14) NOT NULL, // new in 1.1
PRIMARY KEY (id),
KEY owner_id (owner_id),
KEY mangled_blog (mangled_blog),
@@ -192,30 +202,11 @@
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', // new in 1.1
PRIMARY KEY (id),
KEY blog_id (blog_id)
) TYPE=MyISAM;";
- $Tables[7]["desc"] = "MyRecent";
- $Tables[7]["code"] = "CREATE TABLE {dbprefix}myrecent (
- id int(10) unsigned NOT NULL auto_increment,
- category_id int(10) unsigned NOT NULL default '0',
- name varchar(255) NOT NULL default '',
- text text NOT NULL,
- user_id int(10) unsigned NOT NULL default '0',
- blog_id int(10) unsigned NOT NULL default '0',
- PRIMARY KEY (id)
-) TYPE=MyISAM;";
-
- $Tables[8]["desc"] = "MyRecent categories";
- $Tables[8]["code"] = "CREATE TABLE {dbprefix}myrecent_categories (
- id int(10) unsigned NOT NULL auto_increment,
- name varchar(255) NOT NULL default '',
- blog_id int(10) unsigned NOT NULL default '0',
- last_modification timestamp(14) NOT NULL,
- PRIMARY KEY (id)
-) TYPE=MyISAM;";
-
$Tables[9]["desc"] = "Permissions";
$Tables[9]["code"] = "CREATE TABLE {dbprefix}permissions (
id int(10) unsigned NOT NULL auto_increment,
@@ -238,20 +229,6 @@
KEY blog_id_article_id (blog_id, article_id)
) TYPE=MyISAM;";
- $Tables[11]["desc"] = "Trackbacks";
- $Tables[11]["code"] = "CREATE TABLE {dbprefix}trackbacks (
- id int(10) NOT NULL auto_increment,
- url text NOT NULL,
- title varchar(255) default '',
- article_id int(10) NOT NULL default '0',
- excerpt varchar(255) default '',
- blog_name varchar(255) default '',
- date timestamp(14) NOT NULL,
- properties TEXT NOT NULL DEFAULT '',
- 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,
@@ -352,6 +329,7 @@
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', // new in 1.1
PRIMARY KEY (id),
KEY parent_id (parent_id),
KEY owner_id (owner_id),
@@ -451,6 +429,7 @@
UNIQUE KEY phpbb_id(phpbb_id)
) TYPE=MyISAM;";
+ /*** 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,
@@ -458,105 +437,14 @@
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',
PRIMARY KEY (id),
KEY mangled_name(mangled_name)
) TYPE=MyISAM;";
-
// ---
- // changes needed to update from 0.3 to 1.0
- $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 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 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 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 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 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 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 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 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 ''",
- "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)");
-
// ---
-
- // ---
// end of changes needed in 1.0
// ---
@@ -682,8 +570,8 @@
{
$config = new ConfigFileStorage();
// open a connection to the database
- //$db = NewADOConnection('mysql');
- $db = PDb::getDriver('mysql');
+ //$db = NewADOConnection('mysql');
+ $db = PDb::getDriver('mysql');
if ( $selectDatabase ) {
$res = $db->Connect($config->getValue( "db_host" ), $config->getValue( "db_username" ), $config->getValue( "db_password" ), $config->getValue( "db_database" ));
} else {
More information about the pLog-svn
mailing list