auto create index on photo_tags

fix sqlite query


git-svn-id: file:///var/lib/svn/phpfspot/trunk@312 fa6a889d-dae6-447d-9e79-4ba9a3039384

1 files changed, 26 insertions, 4 deletions

diff --git a/phpfspot.class.php b/phpfspot.class.php
index 8272252..11b16fd 100644
--- a/phpfspot.class.php
+++ b/phpfspot.class.php
@@ -76,6 +76,9 @@ class PHPFSPOT {
       require_once "phpfspot_tmpl.php";
       $this->tmpl = new PHPFSPOT_TMPL($this);
 
+      /* check if all necessary indices exist */
+      $this->checkDbIndices();
+
       $this->get_tags();
 
       session_start();
@@ -199,7 +202,7 @@ class PHPFSPOT {
       if(isset($this->cfg->show_tags) && !empty($this->cfg->show_tags)) {
          $query_str="
             SELECT
-               DISTINCT t1.id,t1.name
+               DISTINCT t1.id as id, t1.name as name
             FROM  
                photo_tags pt1
             INNER JOIN photo_tags
@@ -209,11 +212,10 @@ class PHPFSPOT {
             INNER JOIN tags t2
                ON t2.id=pt2.tag_id
             WHERE
-               t2.name IN  ('
-                  ".implode("','",$this->cfg->show_tags)."
-               ')
+               t2.name IN  ('".implode("','",$this->cfg->show_tags)."')
             ORDER BY
                t1.sort_priority ASC";
+
          $result = $this->db->db_query($query_str);
       }
       else
@@ -2064,6 +2066,26 @@ class PHPFSPOT {
 
    } // check_readable()
 
+   /**
+    * check if all needed indices are present
+    *
+    * this function checks, if some needed indices are already
+    * present, or if not, create them on the fly. they are
+    * necessary to speed up some queries like that one look for
+    * all tags, when show_tags is specified in the configuration.
+    */
+   private function checkDbIndices()
+   {
+      $result = $this->db->db_exec("
+         CREATE INDEX IF NOT EXISTS
+            phototag
+         ON
+            photo_tags
+               (photo_id, tag_id)
+      ");
+
+   } // checkDbIndices()
+
 }
 
 ?>