<?php
/* Class to handle searches for pictures and table of contents.
*
* Written by: Chris Studholme
* Copyright: GPL (http://www.fsf.org/copyleft/gpl.html)
* $Id: PictureFinder.php,v 1.24 2003/05/15 19:17:33 cstudhol Exp $
*/
class sql_query_complex {
var $tables=array(); // master array of tables
var $tid=0; // current temporary table id
var $subqueries=array(); // master array of subqueries
var $sid=0; // current subquery id
function join($table, // name of table
$tabledef, // definition of table (false => same as table)
$using, // string or array of fields that can be joined
$where=false, // string or array of conjuncts
$need_null=false // LEFT/RIGHT join to preserve nulls
) {
// make sure parameters are sane
if (strcasecmp($table,$tabledef)==0)
$tabledef=false;
if (!is_array($where))
$where = $where ? array($where) : array();
if (!$using)
die("sql_query_complex: need at least one field to join on!");
if (!is_array($using))
$using = array($using);
if ($this->tables[$table]) {
// table already exists, make sure our new definition matches
if ($this->tables[$table]["tabledef"]!=$tabledef)
die("sql_query_complex: tabledef must match");
// update need_null
$this->tables[$table]["need_null"] =
$this->tables[$table]["need_null"]||$need_null;
// merge using
$this->tables[$table]["using"] =
array_merge($this->tables[$table]["using"],$using);
// merge where conjuncts
$this->tables[$table]["where"] =
array_merge($this->tables[$table]["where"],$where);
}
else {
// new table
$this->tables[$table]["tabledef"] = $tabledef;
$this->tables[$table]["using"] = $using;
$this->tables[$table]["need_null"] = $need_null;
$this->tables[$table]["where"] = $where;
}
}
// returns unique table name
function unique_table() {
return "t".($this->tid++);
}
// add subquery and return tabledef for it
function add_subquery($subquery) {
$this->subqueries[$this->sid] = $subquery;
return "sub".($this->sid++);
}
// returns "FROM ... WHERE ..." parts of query
// returns false if query cannot be generated
function get_query() {
// create FROM section
$query = "FROM";
$table_used = array();
// find first table
reset($this->tables);
while (list($table,$arr)=each($this->tables)) {
if (!$arr["need_null"]) {
if ($arr["tabledef"])
$query .= " ".$arr["tabledef"]." AS";
$query .= " ".$table;
$available = $arr["using"];
$table_used[$table] = true;
break;
}
}
if (count($table_used)==0)
return false;
// add remaining tables
while (count($table_used)<count($this->tables)) {
reset($this->tables);
$failed=true;
while (list($table,$arr)=each($this->tables)) {
if (!$table_used[$table]) {
$using = array_intersect($arr["using"],$available);
if (count($using)>0) {
if ($arr["need_null"])
$query.= " LEFT";
$query.=" JOIN";
if ($arr["tabledef"])
$query .= " ".$arr["tabledef"]." AS";
$query .= " ".$table;
$query .= " USING (".$using[0];
for ($i=1; $i<count($using); ++$i)
$query .= ",".$using[$i];
$query .= ")";
$available = array_merge($available,$arr["using"]);
$table_used[$table] = true;
$failed=false;
}
}
}
if ($failed)
return false;
}
// create WHERE section
$and = false;
reset($this->tables);
while (list($table,$arr)=each($this->tables)) {
for ($i=0; $i<count($arr["where"]); ++$i) {
if ($and)
$where .= " AND";
$where .= " ".$arr["where"][$i];
$and=true;
}
}
if ($where)
$query .= " WHERE".$where;
return $query;
}
}
class PictureFinder {
// orderings
var $order_toc="toc.cid,toc.seq";
var $order_pictures="toc_pictures.seq,pictures.date,picid";
var $order_pictures_all="pictures.date DESC,picid";
var $order_captions="date_update DESC";
// database connection
var $conn;
// desired picture type (a bit) (0 for some/any, -1 for none/any)
var $type=0;
// the following lists of ids are all arrays
var $wordids_require = array(); // required keywords (all of them)
var $wordids_deny = array(); // denied keywords (any of them)
var $wordids_search = array(); // keywords to search for
var $wordids_search_all = false; // require all keywords
var $personids_search = array(); // persons to search for
var $personids_search_all = false; // require all persons
var $cid=0; // only pictures with captions posted by this cookie holder
var $collid=0; // just this collection
var $picid=0; // just this picture
var $count_captions=false; // do we want caption counts?
// cached data from database
var $toc_details=false; // dates,name,description,etc.
// constructor
function PictureFinder(&$conn) {
$this->conn =& $conn;
}
/******** methods to limit results ********/
/* Find pictures featuring at least one of the specified people. If
* $all==true, the only pictures containing all of the specified people
* are found. $personids can either be a comma seperated list (string)
* or a simple array. This method overwrites any previous call to it.
*/
function searchPersons($personids,$all=false) {
if (!is_array($personids)) $personids = CSV2array_int($personids);
$this->personids_search = $personids;
$this->personids_search_all = $all;
}
/* Find pictures with at least one of the specified keywords attached. If
* $all==true, the only pictures with all of the specified keywords attached
* are found. $wordids can either be a comma seperated list (string)
* or a simple array. This method overwrites any previous call to it.
*/
function searchKeywords($wordids,$all=false) {
if (!is_array($wordids)) $wordids = CSV2array_int($wordids);
$this->wordids_search = $wordids;
$this->wordids_search_all = $all;
}
/* Restrict results to pictures with all of the specified keywords attached.
* $wordids can either be a comma seperated list (string) or a simple array.
* Multiple calls to this function will result in the wordid lists being
* merged together.
*/
function requireKeywords($wordids) {
if (!is_array($wordids)) $wordids = CSV2array_int($wordids);
$this->wordids_require = array_merge($this->wordids_require,$wordids);
}
/* Don't include any pictures that have any of the specified keywords
* attached in the results. $wordids can either be a comma seperated
* list (string) or a simple array. Multiple calls to this function will
* result in the wordid lists being merged together.
*/
function denyKeywords($wordids) {
if (!is_array($wordids)) $wordids = CSV2array_int($wordids);
$this->wordids_deny = array_merge($this->wordids_deny,$wordids);
}
/* Restrict results to pictures of a specified type. The $type parameter
* should be one of the following:
* exactly one bit set - photos of corresponding type
* 0 - any but at least one photo type (default)
* -1 - any photo (even ones with not types)
*/
function requireType($type) {
$this->type = $type;
}
/* Restrict results to pictures with captions posted by the specified cookie.
*/
function setCaptionCookie($cid) {
$this->cid = $cid;
}
/* Restrict results to pictures from the specified collection.
*/
function setCollection($collid) {
$this->collid = $collid;
}
/* Restrict results to a single picture. Note that if the picture does not
* meet the other criteria specified, the result set will be empty.
*/
function setPicture($picid) {
$this->picid = $picid;
}
/* Specify that the number of captions posted in a collection should be
* counted and returned when querying table of contents records.
*/
function countCaptions() {
$this->count_captions=true;
}
/******** methods to query results ********/
/* Get an array of public, non-favorite collections. Empty collections
* and collections that are owned by some user will not be returned.
* Hidden (non-public) collections will only be returned if $hidden==true.
* If only a subset of the table of contents is required, $start (zero-based)
* and $length may be specified. This function returns a simple array
* of associative arrays containing the following keys:
* collid, name, description,
* date_start, date_end, date_create, date_update,
* npictures, and ncaptions (maybe)
*/
function get_toc_public($start=0,$length=0,$hidden=false) {
$join = new sql_query_complex();
$where = array("toc.cid IS NULL");
if (!$hidden)
$where[] = "forall=1";
$join->join("toc",false,"collid",$where);
$join->join("toc_pictures",false,array("collid","picid"));
$this->add_restrict_and_search($join);
if ($this->count_captions) {
$join->join("picture_captions",false,array("picid"),false,true);
$query = "SELECT collid,forall,toc.cid,toc.seq".
",COUNT(DISTINCT toc_pictures.picid) AS npictures".
",COUNT(picture_captions.cid) AS ncaptions".
" ".$join->get_query().
" GROUP BY toc.cid,toc.seq,collid,forall".
" ORDER BY ".$this->order_toc;
}
else {
$query = "SELECT collid,forall,toc.cid,toc.seq".
",COUNT(DISTINCT toc_pictures.picid) AS npictures".
" ".$join->get_query().
" GROUP BY toc.cid,toc.seq,collid,forall".
" ORDER BY ".$this->order_toc;
}
$toc_details = $this->read_toc_details();
$result = $this->conn->query_cache($query);
$toc = array();
$count=0;
while ($arr=$result->fetch_assoc()) {
if ($count>=$start) {
$collid = $arr["collid"];
$toc[] =
array("collid"=>$collid,
"date_start"=>$toc_details[$collid]["date_start"],
"date_end"=>$toc_details[$collid]["date_end"],
"date_update"=>$toc_details[$collid]["date_update"],
"date_create"=>$toc_details[$collid]["date_create"],
"name"=>$toc_details[$collid]["name"],
"description"=>$toc_details[$collid]["description"],
"npictures"=>$arr["npictures"],
"ncaptions"=>$arr["ncaptions"],
"forall"=>$arr["forall"]);
}
if (($length>0)&&(count($toc)>=$length))
break;
++$count;
}
return $toc;
}
/* Get an array of empty, non-favorite collections. This array will
* only include collections that really have no pictures in them, and
* not collections that appear to have no pictures as a result of current
* search parameters or user restrictions. Also, collections that are
* owned by some user (favorites) are not returned. This function returns
* a simple array of associative arrays containing the following keys:
* collid, name, description, date_start, date_end
*/
function get_toc_empty() {
$query = "SELECT collid,forall".
" FROM toc_pictures RIGHT JOIN toc USING (collid)".
" WHERE picid IS NULL AND toc.cid IS NULL".
" ORDER BY ".$this->order_toc;
$result = $this->conn->query($query);
$toc_details = $this->read_toc_details();
$empty=array();
while ($arr=$result->fetch_assoc()) {
$details = $toc_details[$arr["collid"]];
$arr["name"] = $details["name"];
$arr["description"] = $details["description"];
$arr["date_start"] = $details["date_start"];
$arr["date_end"] = $details["date_end"];
$empty[] = $arr;
}
return $empty;
}
/* Get an array of favorite collections that are either public or owned
* by the specified $cid. If $cid==false, then only public collections
* are returned. Empty collections are only returned if the cid matches.
* This function returns a simple array of associative arrays containing
* the following keys:
* collid, name, description, cid, forall,
* date_start, date_end, date_create, date_update,
* npictures, and ncaptions (maybe)
*/
function get_toc_cid($cid=false) {
//if (!$this->toc)
// $this->read_toc();
$result=array();
/*
reset($this->toc);
while (list($key,$arr)=each($this->toc)) {
if (($arr["cid"])&&($arr["npictures"])&&
(($arr["forall"])||($cid==$arr["cid"])))
$result[] = $arr;
}
*/
return $result;
}
/* Get details for the selected collection or the collection associated
* with the selected picture. If neither a collection nor a picture has
* been selected, an empty array is returned. This function returns an
* associative array with the following keys:
* collid, name, description, photographer, date_start, date_end, cid, forall
*/
function get_collection_details() {
if ($this->collid)
$query = "SELECT collid,date_start,date_end,forall,cid,name,description,photographer".
" FROM toc WHERE collid=".$this->collid;
else if ($this->picid)
$query = "SELECT collid,date_start,date_end,forall,name,description,photographer".
" FROM toc JOIN toc_pictures USING (collid)".
" WHERE picid=".$this->picid." AND cid IS NULL";
else
return array();
$result = $this->conn->query($query);
$details = $result->fetch_assoc();
if ($details["name"]) {
$details["name"] = unserialize($details["name"]);
$details["description"] = unserialize($details["description"]);
}
else {
$names = query_toc_old_names(array($details["collid"]));
$details["name"] = $names[$details["collid"]]["name"];
$details["description"] = $names[$details["collid"]]["description"];
}
return $details;
}
/* Get details for previous and next collection. A specific collection
* has to have been selected or this method returns an empty array.
* The returned simple array has exactly two elements. The first is
* the previous collection's details while the second is the next
* collection's details. Each entry is an associative array with the
* following keys:
* collid, name, description, date_start, date_end
* If $cid is provided, then private collections owned by $cid may be
* returned. If $hidden==true, then hidden (non-owned) collections
* may be returned.
*/
function get_collection_previous_next($cid=false,$hidden=false) {
if (!$this->collid)
return array();
$join = new sql_query_complex();
$where = "forall=1";
if ($hidden)
$where .= " OR toc.cid IS NULL";
if ($cid)
$where .= " OR toc.cid=".$cid;
$join->join("toc",false,"collid","(".$where.")");
$join->join("toc_pictures",false,array("collid","picid"));
$this->add_restrict_and_search($join);
// query to generate list of accessable collections
$query = "SELECT DISTINCT collid,forall,toc.cid,toc.seq".
" ".$join->get_query().
" ORDER BY ".$this->order_toc;
$result = $this->conn->query_cache($query);
$prevnext = array(array(),array());
$current = false;
while ($arr=$result->fetch_assoc()) {
if ($arr["collid"]==$this->collid) {
// found $current
$current=$arr;
}
else if ($current==false) {
// haven't found $current yet, use $arr as next
$prevnext[1] = $arr;
}
else {
// already found $current, use $arr as previous
$prevnext[0] = $arr;
break;
}
}
// check that previous, current, and next all have the same cid
if ($prevnext[0]["cid"]!=$current["cid"])
$prevnext[0] = array();
if ($prevnext[1]["cid"]!=$current["cid"])
$prevnext[1] = array();
// fill in details
$collids = array();
if ($prevnext[0])
$collids[] = $prevnext[0]["collid"];
if ($prevnext[1])
$collids[] = $prevnext[1]["collid"];
if (count($collids)>0) {
$details = $this->read_toc_details($collids);
if ($prevnext[0])
$prevnext[0] =
array_merge($prevnext[0],$details[$prevnext[0]["collid"]]);
if ($prevnext[1])
$prevnext[1] =
array_merge($prevnext[1],$details[$prevnext[1]["collid"]]);
}
return $prevnext;
}
/* Get array of pictures. This method returns a simple array of associative
* arrays with the following keys:
* picid, type, path_thumb, path_original, date, count_view, addr_view,
* needs_update
*/
function get_pictures() {
$join = new sql_query_complex();
$join->join("pictures",false,"picid",
($this->picid?"picid=".$this->picid:false));
if ($this->collid)
$join->join("toc_pictures",false,array("picid","collid"),
"collid=".$this->collid);
$this->add_restrict_and_search($join);
$query = "SELECT picid,type,path_thumb,path_original,date".
",date_update>date_update_image AS needs_update".
",count_view,addr_view ".$join->get_query();
if (!$this->picid) {
// sorting
if ($this->collid)
$query .= " ORDER BY ".$this->order_pictures;
else
$query .= " ORDER BY ".$this->order_pictures_all;
}
$result = $this->conn->query_cache($query);
return $result->fetch_table();
}
/* Get details for a single picture. If a single picture has not been
* specified, this method returns false. Otherwise, an associative
* array with the following keys is returned:
* picid, type, path_thumb, path_original, date, count_view, addr_view
* Note that picture details are returned regardless of whether the picture
* is in the result set or not.
*/
function get_picture_details() {
if (!$this->picid)
return false;
$query = "SELECT * FROM pictures WHERE picid=".$this->picid;
$result = $this->conn->query($query);
return $result->fetch_assoc();
}
/* Get previous and next picture ids. If no picture has been specified or
* if the picture is not accessable, then false is returned. Otherwise,
* a simple array with exactly two elements is returned. The first is the
* picid of the previous photo and the second is the picid of the next
* photo.
*/
function get_picture_previous_next() {
if (!$this->picid)
return false;
$join = new sql_query_complex();
// this join should not be needed, but for some reason, without it,
// complicated queries can take a really long time
$join->join("pictures",false,array("picid"));
if ($this->collid)
$join->join("toc_pictures",false,array("picid","collid"),
"collid=".$this->collid);
$this->add_restrict_and_search($join);
$query = "SELECT picid ".$join->get_query();
// sorting
if ($this->collid)
$query .= " ORDER BY ".$this->order_pictures;
else
$query .= " ORDER BY ".$this->order_pictures_all;
$result = $this->conn->query_cache($query);
$prevnext = array(array(),array());
$current = false;
while ($arr=$result->fetch_assoc()) {
if ($this->picid==$arr["picid"])
$current = $arr["picid"];
else if (!$current)
$prevnext[0] = $arr["picid"]; // current photo not found yet
else {
$prevnext[1] = $arr["picid"];
break;
}
}
if (!$current)
return false;
return $prevnext;
}
/* Get all captions attached to photos in the result set. This method
* returns a simple array of associative arrays containing the following
* keys:
* picid, cid, name, caption
*/
function get_captions() {
if ($this->picid) {
$query = "SELECT * FROM picture_captions".
" WHERE picid=".$this->picid." ORDER BY date_update DESC";
}
else {
$join = new sql_query_complex();
$join->join("picture_captions",false,array("picid"));
if ($this->collid)
$join->join("toc_pictures",false,array("picid","collid"),
"collid=".$this->collid);
$this->add_restrict_and_search($join);
$query = $join->get_query();
$query = "SELECT picid,cid,name,caption ".$query.
" ORDER BY picid,date_update DESC";
}
$result = $this->conn->query($query);
return $result->fetch_table();
}
/* Get list of persons appearing in at least one photo in the result set.
* This method returns a simple array of associative arrays containing
* the following keys:
* personid, name_last, name_first, nosearch, location
*/
function get_persons() {
if ($this->picid) {
$query = "SELECT *".
" FROM picture_persons JOIN persons USING (personid)".
" WHERE picid=".$this->picid." ORDER BY location,name_last,name_first";
$result = $this->conn->query($query);
return $result->fetch_table();
}
else {
$join = new sql_query_complex();
$join->join("persons",false,array("personid"));
if ($this->collid)
$join->join("toc_pictures",false,array("picid","collid"),
"collid=".$this->collid);
$this->add_restrict_and_search($join);
$query = $join->get_query();
if (!$query) {
$join->join("picture_persons",false,array("personid","picid"));
$query = $join->get_query();
}
$query = "SELECT DISTINCT personid,name_last,name_first,nosearch".
" ".$query." ORDER BY name_last, name_first";
$result = $this->conn->query_cache($query);
return $result->fetch_table();
}
}
/* Get list of persons appearing in at least one photo in the specified
* collection. If no collection has been specified, an empty array is
* returned. Otherwise, the array that is returned is a simple array of
* personid values.
*/
function get_persons_collection() {
if (!$this->collid)
return array();
$query = "SELECT DISTINCT personid".
" FROM picture_persons JOIN toc_pictures USING (picid)".
" WHERE collid=".$this->collid;
$result = $this->conn->query($query);
return $result->fetch_table(false,"personid");
}
/* Get list of keywords attached to at least one photo in result set.
* This method returns a simple array of associative arrays containing:
* wordid, word, nosearch, noprefs
*/
function get_keywords() {
if ($this->picid) {
$query = "SELECT *".
" FROM picture_keywords JOIN keywords USING (wordid)".
" WHERE picid=".$this->picid;
$result = $this->conn->query($query);
}
else {
$join = new sql_query_complex();
$join->join("keywords",false,array("wordid"));
if ($this->collid)
$join->join("toc_pictures",false,array("picid","collid"),
"collid=".$this->collid);
$this->add_restrict_and_search($join);
$query = $join->get_query();
if (!$query) {
$join->join("picture_keywords",false,array("wordid","picid"));
$query = $join->get_query();
}
$query = "SELECT DISTINCT keywords.wordid,keywords.word,keywords.lang".
",keywords.nosearch,keywords.nopref".
" ".$query;
$result = $this->conn->query_cache($query);
}
$words = array();
while ($arr=$result->fetch_assoc()) {
if (!$words[$arr["wordid"]]) {
$words[$arr["wordid"]] = array("wordid"=>$arr["wordid"],
"nosearch"=>$arr["nosearch"],
"nopref"=>$arr["nopref"]);
}
if (!$arr["lang"]) {
$words[$arr["wordid"]]["word"] = unserialize($arr["word"]);
$words[$arr["wordid"]]["_new_type"] = true;
}
else if (!$words[$arr["wordid"]]["_new_type"]) {
$words[$arr["wordid"]]["word"][$arr["lang"]] = $arr["word"];
}
}
return array_values($words);
}
/**** private methods ****/
// join restrictions and search parameters to $join
function add_restrict_and_search(&$join) {
global $conn;
// specific picture types only
if ($this->type>0) {
$join->join("pictures",false,array("picid"),
$conn->bit_set("type",$this->type));
}
else if ($this->type==0) {
$join->join("pictures",false,array("picid"),"type!=0");
}
// only pictures with captions posted by a specific cookie holder
if ($this->cid>0) {
$join->join("picture_captions",false,array("picid"),
"cid=".$this->cid);
}
// search for specific people
if (count($this->personids_search)>0) {
if ((count($this->personids_search)==1)||
($this->personids_search_all)) {
for ($i=0; $i<count($this->personids_search); ++$i) {
$table = $join->unique_table();
$join->join($table,"picture_persons",array("picid"),
$table.".personid=".$this->personids_search[$i]);
}
}
else {
$table = $join->unique_table();
$join->join($table,"picture_persons",array("picid"),
$table.".personid IN (".
array2csv($this->personids_search).")");
}
}
// search for specific keywords
if (count($this->wordids_search)>0) {
if ((count($this->wordids_search)==1)||
($this->wordids_search_all)) {
for ($i=0; $i<count($this->wordids_search); ++$i) {
$table = $join->unique_table();
$join->join($table,"picture_keywords",array("picid"),
$table.".wordid=".$this->wordids_search[$i]);
}
}
else {
$table = $join->unique_table();
$join->join($table,"picture_keywords",array("picid"),
$table.".wordid IN (".
array2csv($this->wordids_search).")");
}
}
// require specific keywords
if (count($this->wordids_require)>0) {
for ($i=0; $i<count($this->wordids_require); ++$i) {
$table = $join->unique_table();
$join->join($table,"picture_keywords",array("picid"),
$table.".wordid=".$this->wordids_require[$i]);
}
}
// exclude denied keywords (make sure this one is last!)
if (count($this->wordids_deny)>0) {
if (count($this->wordids_deny)==1)
$query = "SELECT picid,wordid FROM picture_keywords".
" WHERE wordid=".$this->wordids_deny[0];
else
$query = "SELECT picid,wordid FROM picture_keywords".
" WHERE wordid IN (".array2CSV_int($this->wordids_deny).")";
$table = $join->unique_table();
$join->join($table,"(".$query.")",array("picid"),
$table.".wordid IS NULL",true);
}
}
/* read table of contents details:
* collid, name, description, cid, forall,
* date_start, date_end, date_create, date_update
*/
function read_toc_details($collids=array()) {
if ($this->toc_details)
return $this->toc_details;
if (count($collids)>10)
$collids=array();
$query = "SELECT collid,cid,forall,name,description".
",date_start,date_end,date_create,date_update".
" FROM toc";
if (count($collids)>0)
$query .= " WHERE collid IN (".array2CSV_int($collids).")";
$result = $this->conn->query($query);
$details = $result->fetch_table("collid");
$need_old = false;
// unserialize names
reset($details);
while (list($collid,$record)=each($details)) {
if ($record["name"]) {
$details[$collid]["name"] = unserialize($record["name"]);
$details[$collid]["description"] = unserialize($record["description"]);
}
else
$need_old=true;
}
if ($need_old) {
$names = query_toc_old_names(count($collids)>0?$collids:false);
reset($details);
while (list($collid,$record)=each($details)) {
if (!$record["name"]) {
$details[$collid]["name"] = $names[$collid]["name"];
$details[$collid]["description"] = $names[$collid]["description"];
}
}
}
if (count($collids)==0)
$this->toc_details = $details;
return $details;
}
}
?>
