Refactor the exif module to denormalize the stored data. This way we

have a single exif_record for each item instead of 1 per key.  It's
about 5x faster to scan photos this way.

5 files changed, 29 insertions, 47 deletions

diff --git a/modules/exif/helpers/exif.php b/modules/exif/helpers/exif.php
index 8cb9c551..583a61ee 100644
--- a/modules/exif/helpers/exif.php
+++ b/modules/exif/helpers/exif.php
@@ -26,9 +26,7 @@ class exif_Core {
   protected static $exif_keys;
 
   static function extract($item) {
-    $db = Database::instance();
-    $db->delete("exif_keys", array("item_id" => $item->id));
-
+    $keys = array();
     // Only try to extract EXIF from photos
     if ($item->is_photo() && $item->mime_type == "image/jpeg") {
       $data = array();
@@ -41,7 +39,7 @@ class exif_Core {
             if (function_exists("mb_detect_encoding") && mb_detect_encoding($value) != "UTF-8") {
               $value = utf8_encode($value);
             }
-            $data[] = sprintf("(%d, '%s', '%s')", $item->id, $field, $db->escape_str($value));
+            $keys[$field] = utf8::clean($value);
 
             if ($field == "DateTime") {
               $item->captured = strtotime($value);
@@ -58,13 +56,10 @@ class exif_Core {
         foreach (array("Keywords" => "2#025", "Caption" => "2#120") as $keyword => $iptc_key) {
           if (!empty($iptc[$iptc_key])) {
             $value = implode(" ", $iptc[$iptc_key]);
-            if (mb_detect_encoding($value) != "UTF-8") {
+            if (function_exists("mb_detect_encoding") && mb_detect_encoding($value) != "UTF-8") {
               $value = utf8_encode($value);
             }
-            $data[] = sprintf(
-              "(%d, '%s', '%s')",
-              $item->id, $keyword,
-              $db->escape_str($value));
+            $keys[$keyword] = utf8::clean($value);
 
             if ($keyword == "Caption" && !$item->description) {
               $item->description = $value;
@@ -72,14 +67,6 @@ class exif_Core {
           }
         }
       }
-
-      // ORM and Database::insert() do not handle inserting multiple rows at once, so do it
-      // the hard way.
-      if ($data) {
-        $query = "INSERT INTO {exif_keys} (`item_id`, `name`, `value`) VALUES" .
-          join(",", $data);
-        $db->query($query);
-      }
     }
     $item->save();
 
@@ -87,18 +74,21 @@ class exif_Core {
     if (!$record->loaded) {
       $record->item_id = $item->id;
     }
+    $record->data = serialize($keys);
+    $record->key_count = count($keys);
     $record->dirty = 0;
     $record->save();
   }
 
   static function get($item) {
     $exif = array();
-    $keys = ORM::factory("exif_key")
+    $record = ORM::factory("exif_record")
       ->where("item_id", $item->id)
-      ->find_all();
+      ->find();
     $definitions = self::_keys();
-    foreach ($keys as $key) {
-      $exif[] = array("caption" => $definitions[$key->name][2], "value" => $key->value);
+    $keys = unserialize($record->data);
+    foreach ($keys as $key => $value) {
+      $exif[] = array("caption" => $definitions[$key][2], "value" => $value);
     }
 
     return $exif;
diff --git a/modules/exif/helpers/exif_event.php b/modules/exif/helpers/exif_event.php
index afda70d1..58db2e57 100644
--- a/modules/exif/helpers/exif_event.php
+++ b/modules/exif/helpers/exif_event.php
@@ -23,11 +23,6 @@ class exif_event_Core {
   }
 
   static function item_before_delete($item) {
-    ORM::factory("exif_key")
-      ->where("item_id", $item->id)
-      ->delete_all();
-    ORM::factory("exif_record")
-      ->where("item_id", $item->id)
-      ->delete_all();
+    Database::instance()->delete("exif_records", array("item_id" => $item_id));
   }
 }
diff --git a/modules/exif/helpers/exif_installer.php b/modules/exif/helpers/exif_installer.php
index 4cd1a6b2..b68af794 100644
--- a/modules/exif/helpers/exif_installer.php
+++ b/modules/exif/helpers/exif_installer.php
@@ -23,16 +23,11 @@ class exif_installer {
 
     if ($version == 0) {
       $db = Database::instance();
-      $db->query("CREATE TABLE IF NOT EXISTS {exif_keys} (
-                   `id` int(9) NOT NULL auto_increment,
-                   `item_id` int(9) NOT NULL,
-                   `name` varchar(64) NOT NULL,
-                   `value` varchar(1536) NOT NULL,
-                   PRIMARY KEY (`id`))
-                 ENGINE=InnoDB DEFAULT CHARSET=utf8;");
       $db->query("CREATE TABLE IF NOT EXISTS {exif_records} (
                    `id` int(9) NOT NULL auto_increment,
-                   `item_id` int(9) NOT NULL,
+                   `item_id` INTEGER(9) NOT NULL,
+                   `key_count` INTEGER(9) default 0,
+                   `data` TEXT,
                    `dirty` BOOLEAN default 1,
                    PRIMARY KEY (`id`),
                    KEY(`item_id`))
@@ -43,7 +38,6 @@ class exif_installer {
 
   static function uninstall() {
     $db = Database::instance();
-    $db->query("DROP TABLE IF EXISTS {exif_keys};");
     $db->query("DROP TABLE IF EXISTS {exif_records};");
     module::delete("exif");
   }
diff --git a/modules/exif/helpers/exif_task.php b/modules/exif/helpers/exif_task.php
index b6599395..3ca849f9 100644
--- a/modules/exif/helpers/exif_task.php
+++ b/modules/exif/helpers/exif_task.php
@@ -25,14 +25,14 @@ class exif_task_Core {
     $db->query(
       "DELETE FROM {exif_records} " .
       "WHERE {exif_records}.`item_id` NOT IN " .
-      "(SELECT `id` FROM {items})");
+      "(SELECT `id` FROM {items} WHERE {items}.`type` = 'photo')");
 
     // Insert missing exif_records
     $db->query(
-      "INSERT INTO {exif_records}(`item_id`) (" .
-      " SELECT {items}.`id` FROM {items} " .
+      "INSERT INTO {exif_records}(`item_id`) " .
+      "(SELECT {items}.`id` FROM {items} " .
       " LEFT JOIN {exif_records} ON ({exif_records}.`item_id` = {items}.`id`) " .
-      " WHERE {exif_records}.`id` IS NULL)");
+      " WHERE {items}.`type` = 'photo' AND {exif_records}.`id` IS NULL)");
 
     list ($remaining, $total, $percent) = self::_get_stats();
     return array(Task_Definition::factory()
@@ -53,9 +53,9 @@ class exif_task_Core {
     foreach (ORM::factory("item")
              ->join("exif_records", "items.id", "exif_records.item_id")
              ->where("exif_records.dirty", 1)
-             ->limit(10)
+             ->limit(100)
              ->find_all() as $item) {
-      if (microtime(true) - $start > 1) {
+      if (microtime(true) - $start > 1.5) {
         break;
       }
 
@@ -82,7 +82,7 @@ class exif_task_Core {
 
   private static function _get_stats() {
     $missing_exif = ORM::factory("exif_record")->where("dirty", 1)->count_all();
-    $total_items = ORM::factory("item")->count_all();
+    $total_items = ORM::factory("item")->where("type", "photo")->count_all();
     return array($missing_exif, $total_items,
                  round(100 * (($total_items - $missing_exif) / $total_items)));
   }
diff --git a/modules/exif/helpers/exif_theme.php b/modules/exif/helpers/exif_theme.php
index 432bca40..0bd113ca 100644
--- a/modules/exif/helpers/exif_theme.php
+++ b/modules/exif/helpers/exif_theme.php
@@ -21,10 +21,13 @@ class exif_theme_Core {
   static function sidebar_bottom($theme) {
     $item = $theme->item();
     if ($item && $item->is_photo()) {
-      $exif_count = Database::instance()
-        ->count_records("exif_keys", array("item_id" => $item->id));
-
-      if (!empty($exif_count)) {
+      if (Database::instance()
+          ->select("key_count")
+          ->from("exif_records")
+          ->where("item_id", $item->id)
+          ->get()
+          ->current()
+          ->key_count) {
         $view = new View("exif_sidebar.html");
         $view->item = $item;
         return $view;