Changeset 683

Timestamp:

12/28/06 21:50:43 (2 years ago)

Author:

Connor

Message:

Lots of changes to allow for fulltext searching, indexing now disabled and tables removed for mysql(i), and fulltext indexes added, search.php majorly reworked to support fulltext and to not cache quicksearches.

Files:

• branches/punbb-1.3-dev/extras/db_update.php (modified) (1 diff)
• branches/punbb-1.3-dev/upload/admin/maintenance.php (modified) (2 diffs)
• branches/punbb-1.3-dev/upload/delete.php (modified) (1 diff)
• branches/punbb-1.3-dev/upload/edit.php (modified) (2 diffs)
• branches/punbb-1.3-dev/upload/include/common.php (modified) (1 diff)
• branches/punbb-1.3-dev/upload/include/common_admin.php (modified) (3 diffs)
• branches/punbb-1.3-dev/upload/include/functions.php (modified) (4 diffs)
• branches/punbb-1.3-dev/upload/install.php (modified) (6 diffs)
• branches/punbb-1.3-dev/upload/moderate.php (modified) (3 diffs)
• branches/punbb-1.3-dev/upload/post.php (modified) (3 diffs)
• branches/punbb-1.3-dev/upload/profile.php (modified) (1 diff)
• branches/punbb-1.3-dev/upload/search.php (modified) (11 diffs)

branches/punbb-1.3-dev/extras/db_update.php

@@ -327 +327 @@
-                $db->query('ALTER TABLE '.$db->prefix.'online ADD UNIQUE INDEX '.$db->prefix.'online_user_id_ident_idx (user_id,ident)') or error('Unable to alter DB structure.', __FILE__, __LINE__, $db->error());
-        
+        // Get rid of the old search tables for mysql/mysqli
+        if (($db_type == 'mysql' || $db_type == 'mysqli') && $db->table_exists($db_prefix.'search_cache') && $db->table_exists($db_prefix.'search_matches') && $db->table_exists($db_prefix.'search_words'))
+        {
+                $db->query('DROP TABLE '.$db->prefix.'search_cache, '.$db->prefix.'search_matches, '.$db->prefix.'search_words;') or error('Unable to drop search tables.', __FILE__, __LINE__, $db->error());
+        }
+        
+        // Add fulltext indexes for mysql/mysqli
+        if (($db_type == 'mysql' || $db_type == 'mysqli') && $db->index_exists($db->prefix.'posts', $db->prefix.'posts_message_idx'))
+                $db->query('ALTER TABLE '.$db_prefix.'posts ADD FULLTEXT '.$db_prefix.'posts_message_idx(message)') or error('Unable to alter DB structure.', __FILE__, __LINE__, $db->error());
+        if (($db_type == 'mysql' || $db_type == 'mysqli') && $db->index_exists($db->prefix.'topics', $db->prefix.'topics_subject_idx'))
+                $db->query('ALTER TABLE '.$db_prefix.'topics ADD FULLTEXT '.$db_prefix.'topics_subject_idx(subject)') or error('Unable to alter DB structure.', __FILE__, __LINE__, $db->error());
+        
-        // Finally, we update the version number
-        $db->query('UPDATE '.$db->prefix.'config SET conf_value=\''.$update_to.'\' WHERE conf_name=\'o_cur_version\'') or error('Unable to update version.', __FILE__, __LINE__, $db->error());

branches/punbb-1.3-dev/upload/admin/maintenance.php

@@ -34 +34 @@
-if ($pun_user['g_id'] > PUN_ADMIN)
-        message($lang_common['No permission']);
+        
+if ($db_type == 'mysql' || $db_type == 'mysqli')
+        message($lang_common['Bad request']);
-
-// Load the admin.php language file
@@ -54 +57 @@
-                confirm_referrer('admin/maintenance.php');
-
-
-
-
-
+
+
-
-                // Reset the sequence for the search words (not needed for SQLite)
-
-
-
-
-
-
-
-
-
-
+
+
-        }
-

branches/punbb-1.3-dev/upload/delete.php

@@ -70 +70 @@
-                confirm_referrer('delete.php');
-
-
+
+
-
-        if ($is_topic_post)

branches/punbb-1.3-dev/upload/edit.php

@@ -111 +111 @@
-                $edited_sql = (!isset($_POST['silent']) || !$is_admmod) ? $edited_sql = ', edited='.time().', edited_by=\''.$db->escape($pun_user['username']).'\'' : '';
-
-
+
+
-
-                if ($can_edit_subject)
@@ -119 +120 @@
-
-                        // We changed the subject, so we need to take that into account when we update the search words
-
+
+
-                }
-
+ if ($db_type != 'mysql' && $db_type != 'mysqli')
-                        update_search_index('edit', $id, $message);
-

branches/punbb-1.3-dev/upload/include/common.php

@@ -28 +28 @@
-// This displays all executed queries in the page footer.
-// DO NOT enable this in a production environment!
-//
+
-
-if (!defined('PUN_ROOT'))

branches/punbb-1.3-dev/upload/include/common_admin.php

@@ -33 +33 @@
-function generate_admin_menu()
-{
-
+, $db_type
-
-        $is_admin = $pun_user['g_id'] == PUN_ADMIN ? true : false;
@@ -78 +78 @@
-                $adnav_links[] = '<li'.((PUN_PAGE == 'admin-bans') ? ' class="isactive">' : '>').'<a href="'.pun_link($pun_url['admin_bans']).'">'.$lang_admin['Bans'].'</a></li>';
-                $adnav_links[] = '<li'.((PUN_PAGE == 'admin-prune') ? ' class="isactive">' : '>').'<a href="'.pun_link($pun_url['admin_prune']).'">'.$lang_admin['Prune'].'</a></li>';
-
+
+
-                $adnav_links[] = '<li'.((PUN_PAGE == 'admin-reports') ? ' class="isactive">' : '>').'<a href="'.pun_link($pun_url['admin_reports']).'">'.$lang_admin['Reports'].'</a></li>';
-                $adnav_links[] = '<li'.((PUN_PAGE == 'admin-extensions') ? ' class="isactive">' : '>').'<a href="'.pun_link($pun_url['admin_extensions']).'">'.$lang_admin['Extensions'].'</a></li>';
@@ -160 +161 @@
-
-                        // We removed a bunch of posts, so now we have to update the search index
-
-
+
+
+
+
+
-                }
-        }

branches/punbb-1.3-dev/upload/include/functions.php

@@ -455 +455 @@
-function delete_topic($topic_id)
-{
-
+, $db_type
-
-        // Delete the topic and any redirect topics
@@ -469 +469 @@
-        if ($post_ids != '')
-        {
-
+
+
-
-                // Delete posts in topic
@@ -485 +486 @@
-function delete_post($post_id, $topic_id)
-{
-
+, $db_type
-
-        $result = $db->query('SELECT id, poster, posted FROM '.$db->prefix.'posts WHERE topic_id='.$topic_id.' ORDER BY id DESC LIMIT 2') or error('Unable to fetch post info', __FILE__, __LINE__, $db->error());
@@ -494 +495 @@
-        $db->query('DELETE FROM '.$db->prefix.'posts WHERE id='.$post_id) or error('Unable to delete post', __FILE__, __LINE__, $db->error());
-
-
+
+
-
-        // Count number of replies in the topic

branches/punbb-1.3-dev/upload/install.php

@@ -969 +969 @@
-        switch ($db_type)
-        {
-                case 'mysql':
-                case 'mysqli':
-                        $sql = 'CREATE TABLE '.$db_prefix."search_cache (
-                                        id INT(10) UNSIGNED NOT NULL DEFAULT 0,
-                                        ident VARCHAR(200) NOT NULL DEFAULT '',
-                                        search_data TEXT,
-                                        PRIMARY KEY (id)
-                                        ) TYPE=MyISAM;";
-                        break;
-
-                case 'pgsql':
-                        $sql = 'CREATE TABLE '.$db_prefix."search_cache (
@@ -998 +988 @@
-        }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
-                case 'pgsql':
-                        $sql = 'CREATE TABLE '.$db_prefix."search_matches (
@@ -1030 +1012 @@
-        }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
-                case 'pgsql':
-                        $sql = 'CREATE TABLE '.$db_prefix."search_words (
@@ -1064 +1037 @@
-        }
-
-
+
+
-
-
@@ -1311 +1285 @@
-                        $queries[] = 'ALTER TABLE '.$db_prefix.'posts ADD INDEX '.$db_prefix.'posts_topic_id_idx(topic_id)';
-                        $queries[] = 'ALTER TABLE '.$db_prefix.'posts ADD INDEX '.$db_prefix.'posts_multi_idx(poster_id, topic_id)';
+                        $queries[] = 'ALTER TABLE '.$db_prefix.'posts ADD FULLTEXT '.$db_prefix.'posts_message_idx(message)';
-                        $queries[] = 'ALTER TABLE '.$db_prefix.'reports ADD INDEX '.$db_prefix.'reports_zapped_idx(zapped)';
-                        $queries[] = 'ALTER TABLE '.$db_prefix.'search_matches ADD INDEX '.$db_prefix.'search_matches_word_id_idx(word_id)';
-                        $queries[] = 'ALTER TABLE '.$db_prefix.'search_matches ADD INDEX '.$db_prefix.'search_matches_post_id_idx(post_id)';
-                        $queries[] = 'ALTER TABLE '.$db_prefix.'topics ADD INDEX '.$db_prefix.'topics_forum_id_idx(forum_id)';
-                        $queries[] = 'ALTER TABLE '.$db_prefix.'topics ADD INDEX '.$db_prefix.'topics_moved_to_idx(moved_to)';
+                        $queries[] = 'ALTER TABLE '.$db_prefix.'topics ADD FULLTEXT '.$db_prefix.'topics_subject_idx(subject)';
-                        $queries[] = 'ALTER TABLE '.$db_prefix.'users ADD INDEX '.$db_prefix.'users_registered_idx(registered)';
-                        $queries[] = 'ALTER TABLE '.$db_prefix.'search_cache ADD INDEX '.$db_prefix.'search_cache_ident_idx(ident(8))';
-                        $queries[] = 'ALTER TABLE '.$db_prefix.'users ADD INDEX '.$db_prefix.'users_username_idx(username(8))';
-                        break;
@@ -1455 +1428 @@
-
-        // Add new post to search table
-
-
-
+
+
+
+
+
+
-        $db->query('INSERT INTO '.$db_prefix."ranks (rank, min_posts) VALUES('New member', 0)")
-                or error('Unable to insert into table '.$db_prefix.'ranks. Please check your configuration and try again.');

branches/punbb-1.3-dev/upload/moderate.php

@@ -104 +104 @@
-                        $db->query('DELETE FROM '.$db->prefix.'posts WHERE id IN('.$posts.')') or error('Unable to delete posts', __FILE__, __LINE__, $db->error());
-
-
-
+
+
+
+
+
-
-                        // Get last_post, last_post_id, and last_poster for the topic after deletion
@@ -453 +456 @@
-                        message($lang_common['Bad request']);
-
-
+
+
-
-                // Delete the topics and any redirect topics
@@ -469 +473 @@
-
-                // We have to check that we actually have a list of post ID's since we could be deleting just a redirect topic
-
+ && $db_type != 'mysql' && $db_type != 'mysqli'
-                        strip_search_index($post_ids);
-

branches/punbb-1.3-dev/upload/post.php

@@ -161 +161 @@
-        }
-
-
-
+
+       
-
-        $hide_smilies = isset($_POST['hide_smilies']) ? 1 : 0;
@@ -204 +204 @@
-                        $db->query('UPDATE '.$db->prefix.'topics SET num_replies='.$num_replies.', last_post='.$now.', last_post_id='.$new_pid.', last_poster=\''.$db->escape($username).'\' WHERE id='.$tid) or error('Unable to update topic', __FILE__, __LINE__, $db->error());
-
-
+
+
-
-                        update_forum($cur_posting['id']);
@@ -313 +314 @@
-                        $db->query('UPDATE '.$db->prefix.'topics SET last_post_id='.$new_pid.' WHERE id='.$new_tid) or error('Unable to update topic', __FILE__, __LINE__, $db->error());
-
-
+
+
-
-                        update_forum($fid);

branches/punbb-1.3-dev/upload/profile.php

@@ -582 +582 @@
-                if (isset($_POST['delete_posts']))
-                {
-
+
+
-                        @set_time_limit(0);
-

branches/punbb-1.3-dev/upload/search.php

@@ -48 +48 @@
-if (isset($_GET['action']) || isset($_GET['search_id']))
-{
+        //
+        // Grab and validate all the submitted data.
+        //
+        
-        $action = (isset($_GET['action'])) ? $_GET['action'] : null;
-        $forum = (isset($_GET['forum'])) ? intval($_GET['forum']) : -1;
@@ -57 +61 @@
-        {
-                $search_id = intval($_GET['search_id']);
+                if ($db_type == 'mysql' || $db_type == 'mysqli')
+                        message($lang_common['Bad request']);
-                if ($search_id < 1)
-                        message($lang_common['Bad request']);
@@ -95 +101 @@
-        }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
-                $keyword_results = $author_results = array();
-
@@ -123 +114 @@
-                $forum_sql = ($forum != -1) ? ' AND t.forum_id = '.$forum : '';
-
-
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-                                {
-// Strip out excessive whitespace
-
-
-$keywords_array = explode(' ', $keywords
+$num_chars = pun_strlen($word);
+
+
+       unset($keywords_array[$i]
-                                }
-
+
+
+
+
+
+
+
+
+
+
+
+
-                                {
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
-                                        {
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
+
-                                                {
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-                                                        {
-
-
-
-
-
-
-
-
-
+
+
-                                                        }
-                                                        else
-                                                        {
-                                                                $cur_word = str_replace('*', '%', $cur_word);
-                                                                $sql = 'SELECT m.post_id FROM '.$db->prefix.'search_words AS w INNER JOIN '.$db->prefix.'search_matches AS m ON m.word_id = w.id WHERE w.word LIKE \''.$cur_word.'\''.$search_in_cond;
-                                                        }
-
-                                                        $result = $db->query($sql, true) or error('Unable to search for posts', __FILE__, __LINE__, $db->error());
-
-                                                        $row = array();
-                                                        while ($temp = $db->fetch_row($result))
-                                                        {
-                                                                $row[$temp[0]] = 1;
-
-                                                                if (!$word_count)
-                                                                        $result_list[$temp[0]] = 1;
-                                                                else if ($match_type == 'or')
-                                                                        $result_list[$temp[0]] = 1;
-                                                                else if ($match_type == 'not')
-                                                                        $result_list[$temp[0]] = 0;
-                                                        }
-
-                                                        if ($match_type == 'and' && $word_count)
-                                                        {
-                                                                @reset($result_list);
-                                                                while (list($post_id,) = @each($result_list))
-                                                                {
-                                                                        if (!isset($row[$post_id]))
-                                                                                $result_list[$post_id] = 0;
-                                                                }
-                                                        }
-
-                                                        ++$word_count;
-                                                        $db->free_result($result);
-
-                                                        break;
-                                                }
+
+                                                ++$word_count;
+                                                $db->free_result($result);
+
+                                                break;
-                                        }
-                                }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-