Implement relevance ranked boolean searching on a full text index of

item and comment data.  Whew!

It's not pretty yet.  And you have to manually update the index
currently in admin/maintenance.  But it works.

4 files changed, 204 insertions, 0 deletions

diff --git a/modules/search/helpers/search.php b/modules/search/helpers/search.php
new file mode 100644
index 00000000..02c5f184
--- /dev/null
+++ b/modules/search/helpers/search.php
@@ -0,0 +1,77 @@
+<?php defined("SYSPATH") or die("No direct script access.");
+/**
+ * Gallery - a web based photo album viewer and editor
+ * Copyright (C) 2000-2008 Bharat Mediratta
+ *
+ * This program is free software; you can redistribute it and/or modify
+ * it under the terms of the GNU General Public License as published by
+ * the Free Software Foundation; either version 2 of the License, or (at
+ * your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * General Public License for more details.
+ *
+ * You should have received a copy of the GNU General Public License
+ * along with this program; if not, write to the Free Software
+ * Foundation, Inc., 51 Franklin Street - Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+class search_Core {
+  static function search($q, $limit, $offset) {
+    $db = Database::instance();
+    $q = $db->escape_str($q);
+
+    if (!user::active()->admin) {
+      foreach (user::group_ids() as $id) {
+        $fields[] = "`view_$id` = " . access::ALLOW;
+      }
+      $accesS_sql = "AND (" . join(" AND ", $fields) . ")";
+    } else {
+      $access_sql = "";
+    }
+
+    // Count the total number of rows.  We can't do this with our regular query because of the
+    // limit statement.  It's possible that if we get rid of the limit (but keep the offset) on
+    // the 2nd query and combine the two, it might be faster than making 2 separate queries.
+    $count_query = "SELECT COUNT(*) AS C " .
+      "FROM `items` JOIN `search_records` ON (`items`.`id` = `search_records`.`item_id`) " .
+      "WHERE MATCH(`search_records`.`data`) AGAINST ('$q' IN BOOLEAN MODE) " .
+      $access_sql;
+    $count = $db->query($count_query)->current()->C;
+
+    $query = "SELECT `items`.*, MATCH(`search_records`.`data`) AGAINST ('$q') AS `score` " .
+      "FROM `items` JOIN `search_records` ON (`items`.`id` = `search_records`.`item_id`) " .
+      "WHERE MATCH(`search_records`.`data`) AGAINST ('$q' IN BOOLEAN MODE) " .
+      $access_sql .
+      "ORDER BY `score` DESC " .
+      "LIMIT $offset, $limit";
+
+    return array($count, new ORM_Iterator(ORM::factory("item"), $db->query($query)));
+  }
+
+  static function check_index() {
+    if ($count = ORM::factory("search_record")->where("dirty", 1)->count_all()) {
+      site_status::warning(
+        t("Your search index needs to be updated.  %link_startFix this now%link_end",
+          array("link_start" => "<a href=\"" .
+                url::site("admin/maintenance/start/search_task::rebuild_index?csrf=__CSRF__") .
+                   "\" class=\"gDialogLink\">",
+                   "link_end" => "</a>")),
+        "search_index_out_of_date");
+    }
+  }
+
+  static function update_record($record) {
+    $data = array();
+    foreach (module::installed() as $module_name => $module_info) {
+      $class_name = "{$module_name}_search";
+      if (method_exists($class_name, "item_index_data")) {
+        $data[] = call_user_func(array($class_name, "item_index_data"), $record->item());
+      }
+    }
+    $record->data = join(" ", $data);
+    $record->dirty = 0;
+    $record->save();
+  }
+}
diff --git a/modules/search/helpers/search_event.php b/modules/search/helpers/search_event.php
new file mode 100644
index 00000000..a29d301b
--- /dev/null
+++ b/modules/search/helpers/search_event.php
@@ -0,0 +1,49 @@
+<?php defined("SYSPATH") or die("No direct script access.");
+/**
+ * Gallery - a web based photo album viewer and editor
+ * Copyright (C) 2000-2008 Bharat Mediratta
+ *
+ * This program is free software; you can redistribute it and/or modify
+ * it under the terms of the GNU General Public License as published by
+ * the Free Software Foundation; either version 2 of the License, or (at
+ * your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * General Public License for more details.
+ *
+ * You should have received a copy of the GNU General Public License
+ * along with this program; if not, write to the Free Software
+ * Foundation, Inc., 51 Franklin Street - Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+class search_event_Core {
+  static function item_created($item) {
+    ORM::factory("search_record")->item_id($item->id)->save();
+  }
+
+  static function item_updated($old_item, $new_item) {
+    Database::instance()
+      ->from("search_records")
+      ->set("dirty", 1)
+      ->where("item_id", $new_item->id)
+      ->update();
+  }
+
+  static function item_before_delete($item) {
+    ORM::factory("item_id", $item->id)->delete_all();
+  }
+
+  static function item_related_update($item) {
+    Database::instance()
+      ->from("search_records")
+      ->set("dirty", 1)
+      ->where("item_id", $item->id)
+      ->update();
+  }
+
+  static function item_related_update_batch($sql) {
+    $db = Database::instance();
+    $db->query("UPDATE `search_records` SET `dirty` = 1 WHERE item_id IN ($sql)");
+  }
+}
diff --git a/modules/search/helpers/search_installer.php b/modules/search/helpers/search_installer.php
index 7dc4c39a..ae8eacd3 100644
--- a/modules/search/helpers/search_installer.php
+++ b/modules/search/helpers/search_installer.php
@@ -20,12 +20,29 @@
 class search_installer {
   static function install() {
     $version = module::get_version("search");
+    $db = Database::instance();
     if ($version == 0) {
+      $db->query("CREATE TABLE `search_records` (
+                   `id` int(9) NOT NULL auto_increment,
+                   `item_id` int(9),
+                   `dirty` boolean default 1,
+                   `data` LONGTEXT default NULL,
+                   PRIMARY KEY (`id`),
+                   FULLTEXT INDEX (`data`))
+                 ENGINE=MyISAM DEFAULT CHARSET=utf8;");
+
+      // populate the index with dirty records
+      $db->query("insert into `search_records` (`item_id`) SELECT `id` FROM `items`");
       module::set_version("search", 1);
+
+      search::check_index();
     }
   }
 
   static function uninstall() {
+    $db = Database::instance();
+    $db->query("DROP TABLE `search_records`");
+    site_status::clear("search_index_out_of_date");
     module::delete("search");
   }
 }
diff --git a/modules/search/helpers/search_task.php b/modules/search/helpers/search_task.php
new file mode 100644
index 00000000..5291a27b
--- /dev/null
+++ b/modules/search/helpers/search_task.php
@@ -0,0 +1,61 @@
+
+<?php defined("SYSPATH") or die("No direct script access.");
+/**
+ * Gallery - a web based photo album viewer and editor
+ * Copyright (C) 2000-2008 Bharat Mediratta
+ *
+ * This program is free software; you can redistribute it and/or modify
+ * it under the terms of the GNU General Public License as published by
+ * the Free Software Foundation; either version 2 of the License, or (at
+ * your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * General Public License for more details.
+ *
+ * You should have received a copy of the GNU General Public License
+ * along with this program; if not, write to the Free Software
+ * Foundation, Inc., 51 Franklin Street - Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+class search_task_Core {
+  static function available_tasks() {
+    list ($remaining, $total, $percent) = self::_get_stats();
+    return array(Task_Definition::factory()
+                 ->callback("search_task::update_index")
+                 ->name(t("Update Search Index"))
+                 ->description($remaining ?
+                               t("Search index is %percent% up-to-date",
+                                 array("percent" => $percent))
+                               : t("Search index is up to date"))
+                 ->severity($remaining ? log::WARNING : log::SUCCESS));
+  }
+
+  static function update_index($task) {
+    $completed = $task->get("completed", 0);
+    foreach (ORM::factory("search_record")->where("dirty", 1)->limit(2)->find_all() as $record) {
+      search::update_record($record);
+      $completed++;
+    }
+    $task->set("completed", $completed);
+
+    list ($remaining, $total, $percent) = self::_get_stats();
+    $task->percent_complete = round(100 * $completed / ($remaining + $completed));
+
+    $task->status = t("%done records records updated, index is %percent% up-to-date",
+                      array("done" => $completed, "percent" => $percent));
+
+    if ($remaining == 0) {
+      $task->done = true;
+      $task->state = "success";
+      site_status::clear("search_index_out_of_date");
+    }
+  }
+
+  private static function _get_stats() {
+    $remaining = ORM::factory("search_record")->where("dirty", 1)->count_all();
+    $total = ORM::factory("search_record")->count_all();
+    $percent = round(100 * ($total - $remaining) / $total);
+    return array($remaining, $total, $percent);
+  }
+}