[pLog-svn] r2752 - plog/trunk
oscar at devel.lifetype.net
oscar at devel.lifetype.net
Wed Jan 4 23:24:21 GMT 2006
Author: oscar
Date: 2006-01-04 23:24:21 +0000 (Wed, 04 Jan 2006)
New Revision: 2752
Modified:
plog/trunk/wizard.php
Log:
upgrade code path partially working, just committing my work in progress...
Modified: plog/trunk/wizard.php
===================================================================
--- plog/trunk/wizard.php 2006-01-04 23:23:30 UTC (rev 2751)
+++ plog/trunk/wizard.php 2006-01-04 23:24:21 UTC (rev 2752)
@@ -69,7 +69,6 @@
$_actionMap["Update1"] = "UpdateStepOne";
$_actionMap["Update2"] = "UpdateStepTwo";
$_actionMap["Update3"] = "UpdateStepThree";
- $_actionMap["Update4"] = "UpdateStepFour";
@@ -467,7 +466,43 @@
// ---
+ // changes needed in 1.1
+ // ---
+$Changes["Articles"] = Array(
+ "ALTER TABLE {dbprefix}articles ADD COLUMN num_comments INTEGER(10) NOT NULL DEFAULT 0",
+ "ALTER TABLE {dbprefix}articles ADD COLUMN num_nonspam_comments INTEGER(10) NOT NULL DEFAULT 0",
+ "ALTER TABLE {dbprefix}articles ADD COLUMN num_trackbacks INTEGER(10) NOT NULL DEFAULT 0",
+ "ALTER TABLE {dbprefix}articles ADD COLUMN num_nonspam_trackbacks INTEGER(10) NOT NULL DEFAULT 0",
+ "ALTER TABLE {dbprefix}articles ADD COLUMN global_category_id INTEGER(10) NOT NULL DEFAULT 0",
+ "ALTER TABLE {dbprefix}articles ADD COLUMN in_summary_page TINYINT(10) NOT NULL DEFAULT 1"
+);
+$Changes["Article Categories"] = Array(
+ "ALTER TABLE {dbprefix}articles_categories ADD COLUMN num_articles INTEGER(10) NOT NULL DEFAULT 0",
+ "ALTER TABLE {dbprefix}articles_categories ADD COLUMN num_published_articles INTEGER(10) NOT NULL DEFAULT 0"
+);
+$Changes["Article Comments"] = Array(
+ "ALTER TABLE {dbprefix}articles_comments ADD COLUMN type INTEGER(2) NOT NULL DEFAULT 1",
+ "ALTER TABLE {dbprefix}articles_comments ADD COLUMN blog_id INTEGER(10) NOT NULL DEFAULT 0",
+);
+$Changes["Blogs"] = Array(
+ "ALTER TABLE {dbprefix}blogs ADD COLUMN create_date TIMESTAMP(14)",
+ "ALTER TABLE {dbprefix}blogs ADD COLUMN last_update_date TIMESTAMP(14)",
+ "ALTER TABLE {dbprefix}blogs ADD COLUMN num_posts INT(10) NOT NULL DEFAULT 0",
+ "ALTER TABLE {dbprefix}blogs ADD COLUMN num_comments INT(10) NOT NULL DEFAULT 0",
+ "ALTER TABLE {dbprefix}blogs ADD COLUMN num_trackbacks INT(10) NOT NULL DEFAULT 0"
+);
+$Changes["Gallery Albums"] = Array(
+ "ALTER TABLE {dbprefix}gallery_albums ADD COLUMN num_resources INT(10)",
+ "ALTER TABLE {dbprefix}gallery_albums ADD COLUMN num_children INT(10)"
+);
+$Changes["Links Categories"] = Array(
+ "ALTER TABLE {dbprefix}mylinks_categories ADD COLUMN num_links INT(10)"
+);
+$Changes["Users"] = Array(
+ "ALTER TABLE {dbprefix}users ADD COLUMN site_admin INT(10)"
+);
+
// ---
// end of changes needed in 1.0
// ---
@@ -1229,73 +1264,74 @@
//
// The following classes take care of updating the database to the last release
//
- // Things that need to be done when going from 0.3.x to 1.0:
+ // Things that need to be done when going from 1.0 to 1.1:
//
- // - add the new tables:
- // plog_article_categories_link
- // plog_custom_fields_definition
- // plog_custom_fields_values
+ // - new tables:
+ // plog_blog_categories
+ // id: int(10)
+ // name: varchar(255)
+ // description: varchar(255)
+ // mangled_name: varchar(255)
+ // properties: text
+ // num_blogs: int(10)
+ // num_active_blogs: int(10)
//
+ // plog_global_articles_categories:
+ // id: int(10)
+ // name: varchar(255)
+ // mangled_name: varchar(255)
+ // num_articles: int(10)
+ // num_active_articles: int(10)
+ // description: varchar(255)
+ // properties: text
+ //
+ // plog_phpbb2_users:
+ // id: int(10)
+ // phpbb_id: int(10)
+ // full_name: varchar(255)
+ // about: text
+ // properties: text
+ // resource_picture_id: int(10)
+ // status: int(10)
+ //
+ // - tables to be removed:
+ // plog_trackbacks
+ //
// - make modifications to some of the old tables:
// plog_articles
- // normalized_text: TEXT
- // normalized_topic: TEXT
- // mangled_topic: TEXT
- // category_id: DROP
- // FULLTEXT(normalized_text)
- // FULLTEXT(normalized_topic)
- // FULLTEXT(normalized_text, normalized_topic)
+ // num_comments: INT(10)
+ // num_nonspam_comments: INT(10)
+ // num_trackbacks: INT(10)
+ // num_nonspam_trackbacks: INT(10)
+ // global_category_id: INT(10)
+ // in_summary_page: TINYINT(1)
+ //
// plog_articles_categories
- // parent_id: INTEGER
- // mangled_name: TEXT
- // properties: TEXT
+ // num_articles: int(10)
+ // num_published_articles: int(10)
+ //
// plog_articles_comments
- // normalized_text: TEXT
- // normalized_topic: TEXT
- // FULLTEXT(normalized_text)
- // FULLTEXT(normalized_topic)
- // FULLTEXT(normalized_text, normalized_topic)
+ // type: int(3)
+ // blog_id: int(10)
+ //
// plog_blogs
- // mangled_blog: TEXT
- // properties: TEXT
+ // create_date: timestamp(14)
+ // last_update_date: timestamp(14)
+ // num_posts: int(10)
+ // num_comments: int(10)
+ // num_trackbacks: int(10)
+ //
// plog_gallery_albums
- // normalized_name: VARCHAR
- // normalized_description: TEXT
- // mangled_name: VARCHAR
- // FULLTEXT(normalized_name)
- // FULLTEXT(normalized_description)
- // FULLTEXT(normalized_name, normalized_description)
- // plog_gallery_resources
- // normalized_description: TEXT
- // FULLTEXT(normalized_description)
- // plog_mylinks
- // properties: TEXT
+ // num_resources: int(10)
+ // num_children: int(10)
+ //
// plog_mylinks_categories
- // properties: TEXT
+ // num_links: int(10)
+ //
// plog_users
- // properties: TEXT
- // full_name: VARCHAR
+ // site_admin: int(10)
//
- // - for all the articles in the db, fill in the normalized_text, normalized_topic and mangled_topic
- // fields using the TextFilter class
- //
- // - for all articles in the db, change the status from a string ('published', 'draft', 'deleted') to
- // an integer (POST_STATUS_{PUBLISHED|DELETED|DRAFT}
- //
- // - for all articles and categories, arrange the plog_article_categories_link table so that everything works
- // as before
- //
- // - for all article categories, fill in the mangled_name field
- //
- // - for all comments, fill in the normalized_text and normalized_topic fields
- //
- // - for all blogs, fill in the mangled_blog field
- //
- // - for all albums, fill in the normalized_name, normalized_description and mangled_name fields
- //
- // - for all resources, fill in the normalized_description field
-
class UpdateStepOne extends Action
{
@@ -1324,7 +1360,7 @@
function perform()
{
// we need to create the new tables here
- $newTables = Array( 21, 22, 23, 24 );
+ $newTables = Array( 25, 26, 27 );
global $Tables;
global $Changes;
@@ -1389,9 +1425,9 @@
}
// ---
- // add the new configuration settings that were added for 1.0
+ // add the new configuration settings that were added for 1.1
// ---
- $newSettings = range( 71, 119 );
+ $newSettings = range( 114, 119 );
foreach( $newSettings as $settingId ) {
$setting = $Inserts[$settingId];
$query = str_replace( "{dbprefix}", $this->_dbPrefix, $setting );
@@ -1425,329 +1461,431 @@
*/
class UpdateStepThree extends Action
{
+ var $message;
+ var $db;
+ var $dbPrefix;
+
function UpdateStepThree( $actionInfo, $httpRequest )
{
$this->Action( $actionInfo, $httpRequest );
-
- // load the current step, if any
- $this->_curStep = $this->_request->getValue( "curStep" );
- $this->_numPosts = $this->_request->getValue( "numPosts" );
-
- //print("cur step = ".$this->_curStep." - num posts = ".$this->_numPosts."<br/>");
}
-
- function getStatusId( $statusStr )
+
+ function updateArticleCounters()
{
- if( $statusStr == 'published' ) return POST_STATUS_PUBLISHED;
- if( $statusStr == 'draft' ) return POST_STATUS_DRAFT;
- if( $statusStr == 'deleted' ) return POST_STATUS_DELETED;
-
- return false;
- }
-
- function perform()
- {
- // get a connection to the db
- $db = connectDb();
- $dbPrefix = getDbPrefix();
- $db->debug=false;
-
- // no errors here
- $errors = false;
-
- // see how many records we have
- $queryCount = "SELECT COUNT(*) AS total FROM {$dbPrefix}articles";
- $result = $db->Execute( $queryCount );
- $row = $result->FetchRow();
- $numRecords = $row["total"];
- $this->_totalPosts = $numRecords;
- // check whether we should use multiple steps
- $multipleSteps = ( $numRecords > WIZARD_MAX_RECORDS_THRESHOLD );
- if( $multipleSteps ) {
- // how many steps do we need?
- $numSteps = ceil( $numRecords / WIZARD_MAX_RECORDS_PER_STEP );
- $this->_numSteps = $numSteps;
- //print("using different steps! numSteps = $numSteps<br/>");
+ include_once( PLOG_CLASS_PATH."class/dao/articles.class.php" );
+
+ $numUpdated = 0;
+
+ // build the queries
+ $query1 = "SELECT article_id, COUNT(*) AS total FROM ".$this->dbPrefix."articles_comments GROUP BY article_id";
+ $query2 = "SELECT article_id, COUNT(*) AS total FROM ".$this->dbPrefix."articles_comments WHERE status = ".POST_STATUS_PUBLISHED." GROUP BY article_id";
+ $query3 = "SELECT article_id, COUNT(*) AS total FROM ".$this->dbPrefix."trackbacks GROUP BY article_id";
+ $query4 = "SELECT id FROM ".$this->dbPrefix."articles";
+
+ // and execute all of them...
+
+ // total number of comments
+ $res1 = $this->db->Execute( $query1 );
+ if( !$res1 ) {
+ $this->message .= "Error performing changes to the articles table (1)";
+ return false;
}
-
- // run the query and loop through the results
- $query = "SELECT * FROM {$dbPrefix}articles";
- if( $multipleSteps ) {
- // generate the LIMIT condition for this page
- $query .= " LIMIT ".$this->_curStep*WIZARD_MAX_RECORDS_PER_STEP.", ".WIZARD_MAX_RECORDS_PER_STEP;
+ $numComments = Array();
+ while( $row = $res1->FetchRow()) {
+ $numComments[$row["article_id"]] = $row["total"];
}
-
- //print("query = $query<br/>");
-
- $result = $db->Execute( $query );
-
- $tf = new TextFilter();
-
- $numPosts = $result->RowCount();
-
- while( $row = $result->FetchRow()) {
- // for each one of the articles, fill in the mangled_topic, normalized_text and normalized_value
- $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 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 || !$res3 )
- $errors = true;*/
+
+ // total number of active comments
+ $res2 = $this->db->Execute( $query2 );
+ if( !$res2 ) {
+ $this->message .= "Error performing changes to the articles table (2)";
+ return false;
}
-
- // check whether we've already done the last step or not
- $lastStepDone = ( $this->_curStep >= $this->_numSteps );
-
- // if error
- if( $errors ) {
- $this->_view = new WizardView( "update2" );
- $this->_view->setErrorMessage( "There was an error updating the articles table." );
- $this->setCommonData();
+ $numActiveComments = Array();
+ while( $row = $res2->FetchRow()) {
+ $numActiveComments[$row["article_id"]] = $row["total"];
+ }
+
+ // number of trackbacks
+ $res3 = $this->db->Execute( $query3 );
+ if( !$res3 ) {
+ $this->message .= "Error performing changes to the articles table (3)";
return false;
}
-
- //$query1 = "ALTER TABLE {$dbPrefix}articles DROP COLUMN old_status, DROP COLUMN category_id";
- //$db->Execute( $query1 );
-
- // if everyhting's fine, say so...
- if( !$multipleSteps || $lastStepDone ) {
- $this->_view = new WizardView( "update3" );
- $this->_view->setValue( "totalPosts", $this->_totalPosts );
+ $numTrackbacks = Array();
+ while( $row = $res3->FetchRow()) {
+ $numTrackbacks[$row["article_id"]] = $row["total"];
+ }
+
+ // article ids
+ $res4 = $this->db->Execute( $query4 );
+ if( !$res4 ) {
+ $this->message .= "Error loading articles (3)";
+ return false;
+ }
+ while( $row = $res4->FetchRow()) {
+ $artId = $row["id"];
+ // load the counters
+ $totalComments = $numComments[$artId];
+ if( $totalComments == '' ) $totalComments = 0;
+ $totalActiveComments = $numActiveComments[$artId];
+ if( $totalActiveComments == '' ) $totalActiveComments = 0;
+ $totalTrackbacks = $numTrackbacks[$artId];
+ if( $totalTrackbacks == '' ) $totalTrackbacks = 0;
+
+ // build the update query
+ $query = "UPDATE ".$this->dbPrefix."articles SET num_comments = {$totalComments},
+ num_nonspam_comments = {$totalActiveComments},
+ num_trackbacks = {$totalTrackbacks},
+ num_nonspam_trackbacks = 0
+ WHERE id = {$artId}";
+
+ // and execute it
+ $result = $this->db->Execute( $query );
+ if( !$result ) {
+ $this->message .= "Error updating article with id {$artId}<br/>";
+ }
+ else
+ $numUpdated++;
}
- else {
- // if we're using multiple steps, show the same page
- $this->_view = new WizardView( "update2" );
- $this->_view->setValue( "numPosts", $numPosts );
- $this->_view->setValue( "curStep", $this->_curStep+1 );
- $this->_view->setValue( "totalPosts", $this->_totalPosts );
- $this->_view->setValue( "numSteps", $this->_numSteps );
- $this->_view->setValue( "multipleSteps", true );
- }
-
+
+ $this->message .= "{$numUpdated} articles updated<br/>";
return true;
}
-
- }
-
- // ---
- // this action will do the same as before but for the rest of the tables that need some data to
- // be normalized/mangled
- // ---
- class UpdateStepFour extends Action
- {
-
- var $dbPrefix;
- var $db;
- var $t;
-
- function validate()
+
+ function updateCategoryCounters()
{
- $this->db = connectDb();
- $this->dbPrefix = getDbPrefix();
- $this->t = new TextFilter();
-
- return true;
- }
-
- //
- // process the article categories
- //
- function updateArticleCategories()
- {
- $dbPrefix = $this->dbPrefix;
- $query = "SELECT * FROM {$dbPrefix}articles_categories";
- $result = $this->db->Execute( $query );
-
- while( $row = $result->FetchRow()) {
+ // 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";
+ // list of categories
+ $query3 = "SELECT id FROM ".$this->dbPrefix."articles_categories";
+
+ // execute the 1st query
+ $res1 = $this->db->Execute( $query1 );
+ if( !$res1 ) {
+ $this->message .= "Error performing changes to the categories table (1)";
+ return false;
+ }
+ $numArticles = Array();
+ while( $row = $res1->FetchRow()) {
+ $numArticles[$row["category_id"]] = $row["total"];
+ }
+
+ // total number of active comments
+ $res2 = $this->db->Execute( $query2 );
+ if( !$res2 ) {
+ $this->message .= "Error performing changes to the categories table (2)";
+ return false;
+ }
+ $numActiveArticles = Array();
+ while( $row = $res2->FetchRow()) {
+ $numActiveArticles[$row["category_id"]] = $row["total"];
+ }
+
+ // load each one of the categories and update them
+ $res3 = $this->db->Execute( $query3 );
+ if( !$res3 ) {
+ $this->message .= "Error loading categories (3)";
+ return false;
+ }
+ while( $row = $res3->FetchRow()) {
$catId = $row["id"];
- $mangledName = $this->t->urlize( $row["name"] );
- $query = "UPDATE {$dbPrefix}articles_categories
- SET mangled_name = '$mangledName', last_modification = last_modification
- WHERE id = $catId";
-
- $res = $this->db->Execute( $query );
-
- // this is not very nice to see but it works... :)
- if( !$res ) {
- $this->message .= "There was an error updating the article categories table.<br/>";
- return false;
+ // load the counters
+ $totalArticles = $numArticles[$catId];
+ if( $totalArticles == '' ) $totalArticles = 0;
+ $totalActiveArticles = $numActiveArticles[$catId];
+ if( $totalActiveArticles == '' ) $totalActiveArticles = 0;
+
+ // build the update query
+ $query = "UPDATE ".$this->dbPrefix."articles_categories SET num_articles = {$totalArticles},
+ num_published_articles = {$totalActiveArticles}
+ WHERE id = {$catId}";
+
+ // and execute it
+ $result = $this->db->Execute( $query );
+ if( !$result ) {
+ $this->message .= "Error updating category with id {$catId}<br/>";
}
+ else
+ $numUpdated++;
}
-
- $this->message .= "Article Categories table updated successfully!<br/>";
-
+
+ $this->message .= "{$numUpdated} categories updated<br/>";
return true;
}
-
- //
- // process the comments
- //
- function updateArticleComments()
+
+ function updateBlogCounters()
{
- $dbPrefix = $this->dbPrefix;
- $query = "SELECT * FROM {$dbPrefix}articles_comments";
- $result = $this->db->Execute( $query );
-
- while( $row = $result->FetchRow()) {
- $commentId = $row["id"];
- $normText = Db::qstr($this->t->normalizeText( $row["text"] ));
- $normTopic = Db::qstr($this->t->normalizeText( $row["topic"] ));
- $query = "UPDATE {$dbPrefix}articles_comments
- SET normalized_text = '$normText', normalized_topic = '$normTopic', date = date
- WHERE id = $commentId";
-
- $res = $this->db->Execute( $query );
-
- if( !$res ) {
- $this->message .= "There was an error updating the comments table.<br/>";
- return false;
+ /**
+ * :TODO:
+ *
+ * The last_update_date and create_date fields must be updated!
+ */
+
+ // number of active articles
+ $query1 = "SELECT blog_id, COUNT(*) AS total FROM ".$this->dbPrefix."articles ".
+ "WHERE status = ".POST_STATUS_PUBLISHED." GROUP BY blog_id";
+ // number of active comments
+ $query2 = "SELECT a.blog_id AS blog_id, COUNT(*) AS total FROM ".$this->dbPrefix."articles_comments c, ".
+ $this->dbPrefix."articles a WHERE a.id = c.article_id AND a.status = ".POST_STATUS_PUBLISHED.
+ " GROUP BY a.blog_id";
+ print($query2);
+ // number of trackbacks
+ $query3 = "SELECT blog_id, COUNT(*) AS total FROM ".$this->dbPrefix."trackbacks t,".
+ $this->dbPrefix."articles a WHERE a.id = t.article_id ".
+ "GROUP BY a.blog_id";
+ print($query3);
+ // list of blog ids
+ $query4 = "SELECT id FROM ".$this->dbPrefix."blogs";
+
+ // execute the 1st query
+ $res1 = $this->db->Execute( $query1 );
+ if( !$res1 ) {
+ $this->message .= "Error performing changes to the blogs table (1)";
+ return false;
+ }
+ $numArticles = Array();
+ while( $row = $res1->FetchRow()) {
+ $numArticles[$row["blog_id"]] = $row["total"];
+ }
+
+ // total number of active comments
+ $res2 = $this->db->Execute( $query2 );
+ if( !$res2 ) {
+ $this->message .= "Error performing changes to the blogs table (2)";
+ return false;
+ }
+ $numActiveArticles = Array();
+ while( $row = $res2->FetchRow()) {
+ $numActiveArticles[$row["blog_id"]] = $row["total"];
+ }
+
+ // total number of trackbacks
+ $res3 = $this->db->Execute( $query3 );
+ if( !$res3 ) {
+ $this->message .= "Error performing changes to the blogs table (3)";
+ return false;
+ }
+ $numTrackbacks = Array();
+ while( $row = $res3->FetchRow()) {
+ $numTrackbacks[$row["blog_id"]] = $row["total"];
+ }
+
+ // load each one of the categories and update them
+ $res4 = $this->db->Execute( $query4 );
+ if( !$res4 ) {
+ $this->message .= "Error loading list of blogs";
+ return false;
+ }
+ 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;
+
+ // build the update query
+ $query = "UPDATE ".$this->dbPrefix."blogs SET num_articles = {$totalArticles},
+ num_comments = {$totalComments},
+ num_trackbacks = {$totalTrackbacks},
+ WHERE id = {$blogId}";
+
+ // and execute it
+ $result = $this->db->Execute( $query );
+ if( !$result == 0 ) {
+ $this->message .= "Error updating blog with id {$catId}<br/>";
}
+ else
+ $numUpdated++;
}
-
- $this->message .= "Comments table updated successfully!<br/>";
-
- return true;
+
+ $this->message .= "{$numUpdated} blogs updated<br/>";
+ return true;
}
-
- //
- // process the blogs
- //
- function updateBlogs()
+
+ function updateTrackbacks()
{
- $dbPrefix = $this->dbPrefix;
- $query = "SELECT * FROM {$dbPrefix}blogs";
- $result = $this->db->Execute( $query );
-
- while( $row = $result->FetchRow()) {
- $blogId = $row["id"];
- $mangledBlog = $this->t->urlize( $row["blog"] );
- $query = "UPDATE {$dbPrefix}blogs
- SET mangled_blog = '$mangledBlog' WHERE id = $blogId";
-
- $res = $this->db->Execute( $query );
-
- if( !$res ) {
- $this->message .= "There wasn an error updaing the blogs table.<br/>";
- return false;
- }
- }
-
- $this->message .= "Blogs table updated successfully!<br/>";
-
+ /**
+ * :TODO:
+ *
+ * move all the trackbacks to the articles_comments table and set the 'type' field
+ * to '3'
+ */
+
return true;
}
-
- //
- // process the albums
- //
- function updateAlbums()
+
+ function updateLinkCategories()
{
- $dbPrefix = $this->dbPrefix;
- $query = "SELECT * FROM {$dbPrefix}gallery_albums";
- $result = $this->db->Execute( $query );
-
- while( $row = $result->FetchRow()) {
- $albumId = $row["id"];
- $mangledName = Db::qstr($this->t->urlize( $row["name"] ));
- $normName = Db::qstr($this->t->normalizeText( $row["name"] ));
- $normDescription = Db::qstr($this->t->normalizeText( $row["description"] ));
- $query = "UPDATE {$dbPrefix}gallery_albums
- SET mangled_name = '$mangledName', normalized_name = '$normName',
- normalized_description = '$normDescription', date = date
- WHERE id = $albumId";
-
- $res = $this->db->Execute( $query );
-
- if( !$res ) {
- $this->message .= "There was an error updating the albums table.<br/>";
- return false;
+ // total number of articles
+ $query1 = "SELECT category_id, COUNT(*) AS total FROM ".$this->dbPrefix."mylinks GROUP BY category_id";
+ // list of categories
+ $query2 = "SELECT id FROM ".$this->dbPrefix."mylinks_categories";
+
+ // execute the 1st query
+ $res1 = $this->db->Execute( $query1 );
+ if( !$res1 ) {
+ $this->message .= "Error performing changes to the link categories table";
+ return false;
+ }
+ $numArticles = Array();
+ while( $row = $res1->FetchRow()) {
+ $numLinks[$row["category_id"]] = $row["total"];
+ }
+
+ // load each one of the categories and update them
+ $res2 = $this->db->Execute( $query2 );
+ if( !$res2 ) {
+ $this->message .= "Error loading link categories";
+ return false;
+ }
+ while( $row = $res2->FetchRow()) {
+ $catId = $row["id"];
+ // load the counters
+ $totalLinks = $numLinks[$catId];
+ if( $totalLinks == '' ) $totalLinks = 0;
+
+ // build the update query
+ $query = "UPDATE ".$this->dbPrefix."mylinks_categories SET num_links = {$totalLinks}
+ WHERE id = {$catId}";
+
+ // and execute it
+ $result = $this->db->Execute( $query );
+ print("cat query = $query<br/>");
+ if( !$result ) {
+ $this->message .= "Error updating links category with id {$catId}<br/>";
}
+ else
+ $numUpdated++;
}
-
- $this->message .= "Resource Albums table updated successfully!<br/>";
-
+
+ $this->message .= "{$numUpdated} links categories updated<br/>";
return true;
}
-
- //
- // and finally, the resources...
- //
- function updateResources()
+
+ function updateComments()
{
- $dbPrefix = $this->dbPrefix;
- $query = "SELECT * FROM {$dbPrefix}gallery_resources";
- $result = $this->db->Execute( $query );
-
- while( $row = $result->FetchRow()) {
- $resId = $row["id"];
- //$normName = $this->t->normalizeText( $row["name"] );
- $normDescription = Db::qstr($this->t->normalizeText( $row["description"] ));
- $query = "UPDATE {$dbPrefix}gallery_resources
- SET normalized_description = '$normDescription', date = date
- WHERE id = $resId";
-
+ // build the query
+ $query1 = "SELECT a.blog_id AS blog_id, c.id AS comment_id
+ FROM ".$this->dbPrefix."articles a, ".$this->dbPrefix."articles_comments c
+ WHERE a.id = c.article_id";
+ $res1 = $this->db->Execute( $query1 );
+ if( !$res1 ) {
+ $this->message .= "Error loading comments (1)<br/>";
+ return false;
+ }
+ $commentBlogIds = Array();
+ while( $row = $res1->FetchRow()) {
+ $commentBlogIds[$row["comment_id"]] = $row["blog_id"];
+ }
+
+ // now process all the comments
+ $query2 = "SELECT id FROM ".$this->dbPrefix."articles_comments";
+ $res2 = $this->db->Execute( $query2 );
+ if( !$res2 ) {
+ $this->message.= "Error loading comments (2)<br/>";
+ return false;
+ }
+ $numUpdated = 0;
+ while( $row = $res2->FetchRow()) {
+ // build the query
+ $commentId = $row["id"];
+ $blogId = $commentBlogIds[$commentId];
+ $query = "UPDATE ".$this->dbPrefix."articles_comments
+ SET blog_id = {$blogId} WHERE id = {$commentId}";
+ // and execute it
$res = $this->db->Execute( $query );
- if( !$res ) {
- $this->message .= "There was an error updating the resources table.<br/>";
- return false;
- }
+ if( !$res )
+ $this->message .= "Error updating comment with id {$commentId}<br/>";
+ else
+ $numUpdated++;
}
-
- $this->message .= "Resources table updated successfully!<br/>";
-
- return true;
-
+
+ $this->message .= "{$numUpdated} comments updated.<br/>";
+
+ return true;
}
-
- //
- // process the articles' text
- //
- function updateArticleText()
+
+ function updateAlbums()
{
- $dbPrefix = $this->dbPrefix;
- // drop unneeded columns from articles table
- $alter_query[] = "ALTER TABLE ".$dbPrefix."articles DROP COLUMN text";
- $alter_query[] = "ALTER TABLE ".$dbPrefix."articles DROP COLUMN topic";
-
- foreach ($alter_query as $value) {
- $this->db->Execute($value);
+ // load the resource counter
+ $query1 = "SELECT album_id, COUNT(*) AS total FROM ".$this->dbPrefix."gallery_resources GROUP BY album_id";
+ // and the child counter
+ $query2 = "SELECT parent_id,COUNT(*) AS total FROM ".$this->dbPrefix."gallery_albums
+ WHERE parent_id > 0 GROUP BY parent_id";
+
+ $res1 = $this->db->Execute( $query1 );
+ if( !$res1 ) {
+ $this->message .= "Error loading resources<br/>";
+ return false;
}
-
-
- $this->message .= "articles_text table updated successfully!<br/>";
-
+ $numResources = Array();
+ while( $row = $res1->FetchRow()) {
+ $numResources[$row["album_id"]] = $row["total"];
+ }
+
+ $res2 = $this->db->Execute( $query2 );
+ if( !$res2 ) {
+ $this->message .= "Error loading albums<br/>";
+ return false;
+ }
+ $numChildren = Array();
+ while( $row = $res2->FetchRow()) {
+ $numChildren[$row["parent_id"]] = $row["total"];
+ }
+
+ // now update all albums
+ $query3 = "SELECT id FROM ".$this->dbPrefix."gallery_albums";
+ $res3 = $this->db->Execute( $query3 );
+ if( !$res3 ) {
+ $this->message .= "Error updating gallery albums<br/>";
+ return false;
+ }
+ $numUpdated = 0;
+ while( $row = $res3->FetchRow()) {
+ $albumId = $row["id"];
+ $resources = $numResources[$albumId];
+ if( $resources == "" ) $resources = 0;
+ $children = $numChildren[$albumId];
+ if( $children == "" ) $children = 0;
+ // build the query
+ $query = "UPDATE ".$this->dbPrefix."gallery_albums
+ SET num_children = {$children}, num_resources = {$resources}
+ WHERE id = {$albumId}";
+ // and execute it
+ $res = $this->db->Execute( $query );
+ if( !$res )
+ $this->message .= "Error updating gallery album with id {$albumId}<br/>";
+ else
+ $numUpdated++;
+ }
+
+ $this->message .= "{$numUpdated} gallery albums updated successfully";
+
return true;
}
- function perform()
- {
- $updaters = Array( "updateArticleCategories", "updateArticleComments",
- "updateBlogs", "updateAlbums", "updateResources", "updateArticleText" );
+ function perform()
+ {
+ // get a connection to the db
+ $this->db = connectDb();
+ $this->dbPrefix = getDbPrefix();
+ $this->db->debug=false;
- // loop through each one of the methods to take care of updating one of the tables
- foreach( $updaters as $method ) {
+ $this->message = "";
+
+ $methods = Array( "updateArticleCounters", "updateCategoryCounters", "updateBlogCounters", "updateTrackbacks",
+ "updateLinkCategories", "updateAlbums", "updateComments" );
+
+ foreach( $methods as $method ) {
$result = $this->$method();
-
+
if( !$result ) {
$this->_view = new WizardView( "update3" );
$this->_view->setErrorMessage( $this->message );
@@ -1757,6 +1895,9 @@
// everything went fine so we can show the final page!
$this->_view = new WizardView( "update4" );
+
+ print("message: ".$this->message);
+
$this->_view->setValue( "message", $this->message );
return true;
More information about the pLog-svn
mailing list