|
From: Paul S. O. <ps...@us...> - 2001-12-24 14:04:03
|
Update of /cvsroot/phpbb/phpBB2/includes
In directory usw-pr-cvs1:/tmp/cvs-serv21471/includes
Modified Files:
prune.php
Log Message:
Introduced code for cleaning up search_wordmatch and search_wordlist tables upon prune.
Index: prune.php
===================================================================
RCS file: /cvsroot/phpbb/phpBB2/includes/prune.php,v
retrieving revision 1.8
retrieving revision 1.9
diff -C2 -r1.8 -r1.9
*** prune.php 2001/09/06 00:29:07 1.8
--- prune.php 2001/12/24 14:04:00 1.9
***************
*** 87,91 ****
$sql = "DELETE FROM " . TOPICS_TABLE . "
! WHERE " . $sql_topics;
if(!$result = $db->sql_query($sql))
{
--- 87,91 ----
$sql = "DELETE FROM " . TOPICS_TABLE . "
! WHERE $sql_topics";
if(!$result = $db->sql_query($sql))
{
***************
*** 94,112 ****
$sql = "DELETE FROM " . POSTS_TABLE . "
! WHERE " . $sql_post;
! if(!$result = $db->sql_query($sql, BEGIN_TRANSACTION))
{
- message_die(GENERAL_ERROR, "Couldn't delete post_text during prune.", "", __LINE__, __FILE__, $sql);
- }
- else
- {
$sql = "DELETE FROM " . POSTS_TEXT_TABLE . "
! WHERE " . $sql_post;
! if(!$result = $db->sql_query($sql, END_TRANSACTION))
{
message_die(GENERAL_ERROR, "Couldn't delete post during prune.", "", __LINE__, __FILE__, $sql);
}
}
$sql = "UPDATE " . FORUMS_TABLE . "
SET forum_topics = forum_topics - $pruned_topics, forum_posts = forum_posts - $pruned_posts
--- 94,204 ----
$sql = "DELETE FROM " . POSTS_TABLE . "
! WHERE $sql_post";
! if( $result = $db->sql_query($sql, BEGIN_TRANSACTION) )
{
$sql = "DELETE FROM " . POSTS_TEXT_TABLE . "
! WHERE $sql_post";
! if( $result = $db->sql_query($sql) )
{
+ $sql = "DELETE FROM " . SEARCH_MATCH_TABLE . "
+ WHERE $sql_post";
+ if( !$result = $db->sql_query($sql, END_TRANSACTION) )
+ {
+ message_die(GENERAL_ERROR, "Couldn't delete search matches", "", __LINE__, __FILE__, $sql);
+ }
+ }
+ else
+ {
message_die(GENERAL_ERROR, "Couldn't delete post during prune.", "", __LINE__, __FILE__, $sql);
}
}
+ else
+ {
+ message_die(GENERAL_ERROR, "Couldn't delete post_text during prune.", "", __LINE__, __FILE__, $sql);
+ }
+
+ //
+ // Remove any words in search wordlist which no longer
+ // appear in posts
+ //
+ switch(SQL_LAYER)
+ {
+ case 'postgresql':
+ $sql = "DELETE FROM " . SEARCH_WORD_TABLE . "
+ WHERE word_id NOT IN (
+ SELECT word_id
+ FROM " . SEARCH_MATCH_TABLE . "
+ GROUP BY word_id)";
+ $result = $db->sql_query($sql);
+ if( !$result )
+ {
+ message_die(GENERAL_ERROR, "Couldn't delete old words from word table", __LINE__, __FILE__, $sql);
+ }
+ break;
+
+ case 'oracle':
+ $sql = "DELETE FROM " . SEARCH_WORD_TABLE . "
+ WHERE word_id IN (
+ SELECT w.word_id
+ FROM " . SEARCH_WORD_TABLE . " w, " . SEARCH_MATCH_TABLE . " m
+ WHERE w.word_id = m.word_id(+)
+ AND m.word_id IS NULL)";
+ $result = $db->sql_query($sql);
+ if( !$result )
+ {
+ message_die(GENERAL_ERROR, "Couldn't delete old words from word table", __LINE__, __FILE__, $sql);
+ }
+ break;
+
+ case 'mssql':
+ case 'msaccess':
+ $sql = "DELETE FROM " . SEARCH_WORD_TABLE . "
+ WHERE word_id IN (
+ SELECT w.word_id
+ FROM " . SEARCH_WORD_TABLE . " w
+ LEFT JOIN " . SEARCH_MATCH_TABLE . " m ON m.word_id = w.word_id
+ WHERE m.word_id IS NULL)";
+ $result = $db->sql_query($sql);
+ if( !$result )
+ {
+ message_die(GENERAL_ERROR, "Couldn't delete old words from word table", __LINE__, __FILE__, $sql);
+ }
+ break;
+ case 'mysql':
+ case 'mysql4':
+ // 0.07s
+ $sql = "SELECT w.word_id
+ FROM " . SEARCH_WORD_TABLE . " w
+ LEFT JOIN " . SEARCH_MATCH_TABLE . " m ON m.word_id = w.word_id
+ WHERE m.word_id IS NULL";
+ if( $result = $db->sql_query($sql) )
+ {
+ if( $db->sql_numrows($result) )
+ {
+ $rowset = array();
+ while( $row = $db->sql_fetchrow($result) )
+ {
+ $rowset[] = $row['word_id'];
+ }
+
+ $word_id_sql = implode(", ", $rowset);
+
+ if( $word_id_sql )
+ {
+ // 0.07s (about 15-20 words)
+ $sql = "DELETE FROM " . SEARCH_WORD_TABLE . "
+ WHERE word_id IN ($word_id_sql)";
+ $result = $db->sql_query($sql);
+ if( !$result )
+ {
+ message_die(GENERAL_ERROR, "Couldn't delete word list entry", "", __LINE__, __FILE__, $sql);
+ }
+ }
+ }
+ }
+ break;
+ }
+
$sql = "UPDATE " . FORUMS_TABLE . "
SET forum_topics = forum_topics - $pruned_topics, forum_posts = forum_posts - $pruned_posts
***************
*** 117,129 ****
}
! $returnval = array (
"topics" => $pruned_topics,
"posts" => $pruned_posts);
-
- return $returnval;
}
else
{
! return (array("topics" => 0, "posts" => 0));
}
}
--- 209,221 ----
}
! return array (
"topics" => $pruned_topics,
"posts" => $pruned_posts);
}
else
{
! return array(
! "topics" => 0,
! "posts" => 0);
}
}
|