X-Git-Url: https://git.nubati.net/cgi-bin/gitweb.cgi?p=phpfspot.git;a=blobdiff_plain;f=phpfspot.class.php;h=e2b24a861b6296589511f8487c05c112e1af721f;hp=d67c7e39dc4f1b1543296ce209ed7e0071a4beaa;hb=39507eea2eedc1d05d5467ebd14ea8ed8e7c9032;hpb=5ea211de598c1e02a9f5ccb04b83a81ed9b41117 diff --git a/phpfspot.class.php b/phpfspot.class.php index d67c7e3..e2b24a8 100644 --- a/phpfspot.class.php +++ b/phpfspot.class.php @@ -54,6 +54,10 @@ class PHPFSPOT { } $this->db = new PHPFSPOT_DB($this, $this->cfg->fspot_db); + if(!is_writeable($this->cfg->fspot_db)) { + print $this->cfg->fspot_db ." is not writeable for user ". $this->getuid() ."\n"; + exit(1); + } if(!is_writeable(dirname($this->cfg->phpfspot_db))) { print dirname($this->cfg->phpfspot_db) .": directory is not writeable!"; @@ -76,7 +80,8 @@ class PHPFSPOT { require_once "phpfspot_tmpl.php"; $this->tmpl = new PHPFSPOT_TMPL($this); - $this->get_tags(); + /* check if all necessary indices exist */ + $this->checkDbIndices(); session_start(); @@ -196,11 +201,33 @@ class PHPFSPOT { $this->avail_tags = Array(); $count = 0; - $result = $this->db->db_query(" - SELECT id,name - FROM tags - ORDER BY sort_priority ASC - "); + if(isset($this->cfg->show_tags) && !empty($this->cfg->show_tags)) { + $query_str=" + SELECT + DISTINCT t1.id as id, t1.name as name + FROM + photo_tags pt1 + INNER JOIN photo_tags + pt2 ON pt1.photo_id=pt2.photo_id + INNER JOIN tags t1 + ON t1.id=pt1.tag_id + INNER JOIN tags t2 + ON t2.id=pt2.tag_id + WHERE + t2.name IN ('".implode("','",$this->cfg->show_tags)."') + ORDER BY + t1.sort_priority ASC"; + + $result = $this->db->db_query($query_str); + } + else + { + $result = $this->db->db_query(" + SELECT id,name + FROM tags + ORDER BY sort_priority ASC + "); + } while($row = $this->db->db_fetch_object($result)) { @@ -214,13 +241,14 @@ class PHPFSPOT { if(in_array($row['name'], $this->cfg->hide_tags)) continue; - /* if the user has specified to only show certain tags which - are specified in phpfspot's configuration, ignore all others - so they will not be added to the tag list. - */ + /* if you include the following if-clause and the user has specified + to only show certain tags which are specified in phpfspot's + configuration, ignore all others so they will not be added to the + tag list. if(isset($this->cfg->show_tags) && !empty($this->cfg->show_tags) && !in_array($row['name'], $this->cfg->show_tags)) continue; + */ $this->tags[$tag_id] = $tag_name; $this->avail_tags[$count] = $tag_id; @@ -449,6 +477,8 @@ class PHPFSPOT { */ public function getAvailableTags() { + $this->get_tags(); + $output = ""; $result = $this->db->db_query(" @@ -518,6 +548,8 @@ class PHPFSPOT { */ public function getSelectedTags() { + $this->get_tags(); + $output = ""; foreach($this->avail_tags as $tag) { @@ -648,19 +680,23 @@ class PHPFSPOT { /* return a search result */ if(isset($_SESSION['searchfor']) && $_SESSION['searchfor'] != '') { $query_str = " - SELECT DISTINCT photo_id - FROM photo_tags pt + SELECT DISTINCT pt1.photo_id + FROM photo_tags pt1 + INNER JOIN photo_tags pt2 + ON pt1.photo_id=pt2.photo_id + INNER JOIN tags t1 + ON pt1.tag_id=t1.id INNER JOIN photos p - ON p.id=pt.photo_id - INNER JOIN tags t - ON pt.tag_id=t.id - WHERE t.name LIKE '%". $_SESSION['searchfor'] ."%'"; + ON pt1.photo_id=p.id + INNER JOIN tags t2 + ON pt2.tag_id=t2.id + WHERE t1.name LIKE '%". $_SESSION['searchfor'] ."%' "; if(isset($additional_where_cond)) $query_str.= "AND ". $additional_where_cond ." "; if(isset($this->cfg->show_tags) && !empty($this->cfg->show_tags)) { - $query_str.= "AND t.name IN ('".implode("','",$this->cfg->show_tags)."')"; + $query_str.= "AND t2.name IN ('".implode("','",$this->cfg->show_tags)."')"; } if(isset($order_str)) @@ -680,15 +716,18 @@ class PHPFSPOT { $selected.= $tag .","; $selected = substr($selected, 0, strlen($selected)-1); + /* photo has to match at least on of the selected tags */ if($_SESSION['tag_condition'] == 'or') { $query_str = " - SELECT DISTINCT pt.photo_id - FROM photo_tags pt - INNER JOIN photos p - ON p.id=pt.photo_id + SELECT DISTINCT pt1.photo_id + FROM photo_tags pt1 + INNER JOIN photo_tags pt2 + ON pt1.photo_id=pt2.photo_id INNER JOIN tags t - ON pt.tag_id=t.id - WHERE pt.tag_id IN (". $selected .") + ON pt2.tag_id=t.id + INNER JOIN photos p + ON pt1.photo_id=p.id + WHERE pt1.tag_id IN (". $selected .") "; if(isset($additional_where_cond)) $query_str.= "AND ". $additional_where_cond ." "; @@ -700,6 +739,7 @@ class PHPFSPOT { if(isset($order_str)) $query_str.= $order_str; } + /* photo has to match all selected tags */ elseif($_SESSION['tag_condition'] == 'and') { if(count($_SESSION['selected_tags']) >= 32) { @@ -739,10 +779,10 @@ class PHPFSPOT { INNER JOIN photos p ON pt1.photo_id=p.id "; - $query_str.= "WHERE pt1.tag_id=". $_SESSION['selected_tags'][0]; + $query_str.= "WHERE pt2.tag_id=". $_SESSION['selected_tags'][0]." "; for($i = 1; $i < count($_SESSION['selected_tags']); $i++) { $query_str.= " - AND pt". ($i+1) .".tag_id=". $_SESSION['selected_tags'][$i] ." + AND pt". ($i+2) .".tag_id=". $_SESSION['selected_tags'][$i] ." "; } if(isset($additional_where_cond)) @@ -1321,6 +1361,8 @@ class PHPFSPOT { */ public function startSearch($searchfor, $sort_order, $from = 0, $to = 0) { + $this->get_tags(); + $_SESSION['searchfor'] = $searchfor; $_SESSION['sort_order'] = $sort_order; if($from != 0) @@ -2040,6 +2082,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() + } ?>