Introduce a relative_path_cache column in the items table. This lets

us avoid doing lots of MPTT lookups to find the parent path when we're
trying to generate thumbnails, etc.  Invalidate the cache at all the
right times.  This greatly reduces our query count on album page views.

This fixes ticket #40.

3 files changed, 17 insertions, 11 deletions

diff --git a/core/helpers/core_installer.php b/core/helpers/core_installer.php
index 9f7c3f22..55306eaa 100644
--- a/core/helpers/core_installer.php
+++ b/core/helpers/core_installer.php
@@ -75,6 +75,7 @@ class core_installer {
                    `view_count` int(9) default 0,
                    `width` int(9) default NULL,
                    `rand_key` float default NULL,
+                   `relative_path_cache` varchar(255) default NULL,
                    `sort_column` varchar(64) default NULL,
                    `sort_order` char(4) default 'ASC',
                    `weight` int(9) NOT NULL default 0,
diff --git a/core/models/item.php b/core/models/item.php
index 940eeab7..5b989b0c 100644
--- a/core/models/item.php
+++ b/core/models/item.php
@@ -19,7 +19,6 @@
  */
 class Item_Model extends ORM_MPTT {
   protected $children = 'items';
-  private $relative_path = null;
   private $view_restrictions = null;
   protected $sorting = array();
 
@@ -137,12 +136,16 @@ class Item_Model extends ORM_MPTT {
     $original_thumb_path = $this->thumb_path();
 
     parent::move_to($target, true);
-    $this->relative_path = null;
+    $this->relative_path_cache = null;
 
     rename($original_path, $this->file_path());
     if ($this->is_album()) {
       @rename(dirname($original_resize_path), dirname($this->resize_path()));
       @rename(dirname($original_thumb_path), dirname($this->thumb_path()));
+      Database::instance()
+        ->update("items",
+                 array("relative_path_cache" => null),
+                 array("left >" => $this->left, "right <" => $this->right));
     } else {
       @rename($original_resize_path, $this->resize_path());
       @rename($original_thumb_path, $this->thumb_path());
@@ -250,21 +253,22 @@ class Item_Model extends ORM_MPTT {
    * @return string
    */
   public function relative_path() {
-    if (empty($this->relative_path)) {
+    if (!isset($this->relative_path_cache)) {
       $paths = array();
       foreach (Database::instance()
                ->select("name")
                ->from("items")
-               ->where("`left` <= {$this->left}")
-               ->where("`right` >= {$this->right}")
-               ->where("id <> 1")
+               ->where("left <=", $this->left)
+               ->where("right >=", $this->right)
+               ->where("id <>", 1)
                ->orderby("left", "ASC")
                ->get() as $row) {
         $paths[] = $row->name;
       }
-      $this->relative_path = implode($paths, "/");
+      $this->relative_path_cache = implode($paths, "/");
+      $this->save();
     }
-    return $this->relative_path;
+    return $this->relative_path_cache;
   }
 
   /**
@@ -290,7 +294,7 @@ class Item_Model extends ORM_MPTT {
   public function __set($column, $value) {
     if ($column == "name") {
       // Clear the relative path as it is no longer valid.
-      $this->relative_path = null;
+      $this->relative_path_cache = null;
     }
     parent::__set($column, $value);
   }
diff --git a/installer/install.sql b/installer/install.sql
index 62240b50..2891df0f 100644
--- a/installer/install.sql
+++ b/installer/install.sql
@@ -142,6 +142,7 @@ CREATE TABLE {items} (
   `view_count` int(9) default '0',
   `width` int(9) default NULL,
   `rand_key` float default NULL,
+  `relative_path_cache` varchar(255) default NULL,
   `sort_column` varchar(64) default NULL,
   `sort_order` char(4) default 'ASC',
   `weight` int(9) NOT NULL default '0',
@@ -153,7 +154,7 @@ CREATE TABLE {items} (
   KEY `random` (`rand_key`)
 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
 SET character_set_client = @saved_cs_client;
-INSERT INTO {items} VALUES (NULL,NULL,UNIX_TIMESTAMP(),'',NULL,1,1,1,NULL,NULL,NULL,0,NULL,NULL,1,2,NULL,NULL,1,'Gallery','album',UNIX_TIMESTAMP(),0,NULL,NULL,'weight','ASC',1,1,1);
+INSERT INTO {items} VALUES (NULL,NULL,UNIX_TIMESTAMP(),'',NULL,1,1,1,NULL,NULL,NULL,0,NULL,NULL,1,2,NULL,NULL,1,'Gallery','album',UNIX_TIMESTAMP(),0,NULL,NULL,NULL,'weight','ASC',1,1,1);
 DROP TABLE IF EXISTS {items_tags};
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
@@ -332,4 +333,4 @@ CREATE TABLE {vars} (
   UNIQUE KEY `module_name` (`module_name`,`name`)
 ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
 SET character_set_client = @saved_cs_client;
-INSERT INTO {vars} VALUES (1,'core','active_site_theme','default'),(2,'core','active_admin_theme','admin_default'),(3,'core','page_size','9'),(4,'core','thumb_size','200'),(5,'core','resize_size','640'),(6,'core','default_locale','en_US'),(7,'core','blocks_dashboard_sidebar','a:4:{i:1712034437;a:2:{i:0;s:4:\"core\";i:1;s:11:\"block_adder\";}i:283628656;a:2:{i:0;s:4:\"core\";i:1;s:5:\"stats\";}i:1436662733;a:2:{i:0;s:4:\"core\";i:1;s:13:\"platform_info\";}i:1831514378;a:2:{i:0;s:4:\"core\";i:1;s:12:\"project_news\";}}'),(8,'core','blocks_dashboard_center','a:4:{i:186640350;a:2:{i:0;s:4:\"core\";i:1;s:7:\"welcome\";}i:1060186635;a:2:{i:0;s:4:\"core\";i:1;s:12:\"photo_stream\";}i:769529551;a:2:{i:0;s:4:\"core\";i:1;s:11:\"log_entries\";}i:1182339620;a:2:{i:0;s:7:\"comment\";i:1;s:15:\"recent_comments\";}}'),(9,'core','version','3.0 pre-beta svn'),(10,'core','choose_default_tookit','1'),(11,'comment','spam_caught','0');
+INSERT INTO {vars} VALUES (1,'core','active_site_theme','default'),(2,'core','active_admin_theme','admin_default'),(3,'core','page_size','9'),(4,'core','thumb_size','200'),(5,'core','resize_size','640'),(6,'core','default_locale','en_US'),(7,'core','blocks_dashboard_sidebar','a:4:{i:1229952929;a:2:{i:0;s:4:\"core\";i:1;s:11:\"block_adder\";}i:837192806;a:2:{i:0;s:4:\"core\";i:1;s:5:\"stats\";}i:1511984720;a:2:{i:0;s:4:\"core\";i:1;s:13:\"platform_info\";}i:784311393;a:2:{i:0;s:4:\"core\";i:1;s:12:\"project_news\";}}'),(8,'core','blocks_dashboard_center','a:4:{i:1049359538;a:2:{i:0;s:4:\"core\";i:1;s:7:\"welcome\";}i:84529933;a:2:{i:0;s:4:\"core\";i:1;s:12:\"photo_stream\";}i:1690694961;a:2:{i:0;s:4:\"core\";i:1;s:11:\"log_entries\";}i:728059697;a:2:{i:0;s:7:\"comment\";i:1;s:15:\"recent_comments\";}}'),(9,'core','version','3.0 pre-beta svn'),(10,'core','choose_default_tookit','1'),(11,'comment','spam_caught','0');