dmBridge PHP API
DMPDODataStore.php
00001 <?php
00002 #
00003 # dmBridge: a data access framework for CONTENTdm(R)
00004 #
00005 # Copyright © 2009, 2010, 2011 Board of Regents of the Nevada System of Higher
00006 # Education, on behalf of the University of Nevada, Las Vegas
00007 #
00008 
00019 abstract class DMPDODataStore {
00020 
00021    protected static $tables = array("comment", "rating", "tag");
00022 
00023    private $tables_exist;
00024 
00025 
00026    protected function __construct() {}
00027    public function __clone() {}
00028    public function __wakeup() {}
00029 
00030 
00031    private function allTablesExist() {
00032       if ($this->tables_exist === null) {
00033          foreach (self::$tables as $t) {
00034             if (!$this->tableExists($t)) {
00035                return false;
00036             }
00037          }
00038       }
00039       $this->tables_exist = true;
00040       return true;
00041    }
00042 
00048    public function isAvailable() {
00049       if (!$this->getConnection()) {
00050          return false;
00051       }
00052       if (!$this->allTablesExist()) {
00053          if (!$this->createtables()) {
00054             return false;
00055          }
00056       }
00057       return true;
00058    }
00059 
00063    public function beginTransaction() {
00064       $this->getConnection()->beginTransaction();
00065    }
00066 
00070    public function commit() {
00071       $this->getConnection()->commit();
00072    }
00073 
00078    public function createTables() {
00079       if ($this->allTablesExist()) {
00080          return;
00081       }
00082 
00083       $sql = file_get_contents(
00084          sprintf("%s/../../includes/database/%s.sql",
00085             dirname(__FILE__),
00086             str_replace("pdo_", "", $this->driver)));
00087       $tmp = explode("-- ||| --", $sql);
00088       array_shift($tmp);
00089       foreach ($tmp as $cmd) {
00090          $stmt = $this->getConnection()->prepare(trim($cmd));
00091          $stmt->execute();
00092       }
00093    }
00094 
00098    public final function setDBName($name) {
00099       unset($this->dbcon);
00100       $this->dbname = $name;
00101    }
00102 
00109    public function read($sql, array $params) {
00110       try {
00111          $stmt = $this->getConnection()->prepare($sql);
00112          $stmt->execute($params);
00113          return $stmt->fetchAll(PDO::FETCH_ASSOC);
00114       } catch (PDOException $e) {
00115          $e = new DMPDOException($e);
00116          throw $e;
00117       }
00118    }
00119 
00123    public function rollBack() {
00124       $this->getConnection()->rollBack();
00125    }
00126 
00130    public function commentTableExists() {
00131       return $this->tableExists('comment');
00132    }
00133 
00137    public function ratingTableExists() {
00138       return $this->tableExists('rating');
00139    }
00140 
00144    public function tagTableExists() {
00145       return $this->tableExists('tag');
00146    }
00147 
00152    private function tableExists($name) {
00153       /* There is no database-independent way to check if a table exists; so
00154         we'll issue a select query and see if it fails. */
00155       try {
00156          $sql = sprintf("SELECT * FROM %s LIMIT 1", $name);
00157          $stmt = $this->getConnection()->prepare($sql);
00158          $stmt->execute();
00159          return true;
00160       } catch (PDOException $e) {
00161          $this->tables_exist = false;
00162          return false;
00163       }
00164    }
00165 
00173    public function write($sql, array $params) {
00174       try {
00175          $stmt = $this->getConnection()->prepare($sql);
00176          $stmt->execute($params);
00177          return $stmt->rowCount();
00178       } catch (PDOException $e) {
00179          $e = new DMPDOException($e);
00180          throw $e;
00181       }
00182    }
00183 
00184    /**************************** COMMENTS *********************************/
00185 
00192    public function getCommentsForQuery(DMCommentQuery $query, $count) {
00193       $select = "SELECT * ";
00194       if ($count) {
00195          $select = "SELECT COUNT(id) AS count ";
00196       }
00197       $from = "FROM comment ";
00198 
00199       $params = $where = $tmp = array();
00200       $i = 0;
00201       if (count($query->getObjects())) {
00202          foreach ($query->getObjects() as $obj) { // object
00203             $tmp[] = sprintf("(alias = :alias%d AND ptr = :ptr%d)", $i, $i);
00204             $params[':alias' . $i] = $obj->getCollection()->getAlias();
00205             $params[':ptr' . $i] = $obj->getPtr();
00206             $i++;
00207          }
00208       } else { // collections
00209          $skip_collections = false;
00210          foreach ($query->getCollections() as $col) {
00211             if ($col->getAlias() == "/dmdefault") {
00212                $skip_collections = true;
00213                break;
00214             }
00215          }
00216          if (!$skip_collections) {
00217             foreach ($query->getCollections() as $col) {
00218                $tmp[] = sprintf("alias = :alias%d", $i);
00219                $params[':alias' . $i] = $col->getAlias();
00220                $i++;
00221             }
00222          }
00223       }
00224       if (count($tmp)) {
00225          $where[] = implode(" OR ", $tmp);
00226       }
00227 
00228       // approved
00229       if ($query->getApproved() == -1) {
00230          $where[] = "is_approved < 1";
00231       } else if ($query->getApproved() == 1) {
00232          $where[] = "is_approved > 0";
00233       }
00234       
00235       // terms
00236       $i = 0;
00237       $tmp = array();
00238       foreach ($query->getPredicates() as $pred) {
00239          $tmp[] = sprintf("text LIKE :term%d", $pred->getField(), $i);
00240          $params[':term' . $i] = "%" . $pred->getString() . "%";
00241          $i++;
00242       }
00243       if (count($tmp)) {
00244          $where[] = implode(" AND ", $tmp);
00245       }
00246 
00247       $order = $limit = $offset = "";
00248       if (!$count) {
00249          // order
00250          if (count($query->getSortFields())) {
00251             $order = " ORDER BY";
00252             $orders = array();
00253             foreach ($query->getSortFields() as $field => $direction) {
00254                $orders[] = sprintf(" %s %s ", $field, $direction);
00255             }
00256             $order .= implode(", ", $orders);
00257          }
00258       
00259          // limit/offset
00260          $limit = sprintf(" LIMIT %d ", $query->getNumResultsPerPage());
00261          $offset = sprintf(" OFFSET %d ", $query->getStart());
00262       }
00263 
00264       $sql = $select . $from . "WHERE " . implode(" AND ", $where)
00265             . $order . $limit . $offset;
00266       $result = $this->read($sql, $params);
00267 
00268       if ($count) {
00269          return $result[0]['count'];
00270       } else {
00271          $comments = array();
00272          foreach ($result as $c) {
00273             try {
00274                $comments[] = $this->loadComment($c['id']);
00275             } catch (DMUnavailableModelException $e) {
00276                // comment is associated with an invalid object; skip it
00277             }
00278          }
00279          return $comments;
00280       }
00281    }
00282 
00291    public final function getApprovedCommentsForObject(
00292          DMObject $obj, $page, $rpp, $sort = true) {
00293       $sql = "SELECT id FROM comment
00294          WHERE alias = :alias AND ptr = :ptr AND is_approved > 0";
00295       if (!is_null($sort)) {
00296          $sql .= " ORDER BY posted_at";
00297          if ($sort === false) {
00298             $sql .= " DESC";
00299          }
00300       }
00301       $sql .= sprintf(" LIMIT %d OFFSET %d",
00302             $rpp,
00303             ($page - 1) * $rpp);
00304       $params = array(
00305          ':alias' => $obj->getCollection()->getAlias(),
00306          ':ptr' => $obj->getPtr()
00307       );
00308       $comments = array();
00309       foreach ($this->read($sql, $params) as $c) {
00310          $comments[] = $this->loadComment($c['id']);
00311       }
00312       return $comments;
00313    }
00314 
00319    public function deleteComment(DMComment $comment) {
00320       $sql = 'DELETE FROM comment WHERE id = :id';
00321       $params = array(':id' => $comment->getID());
00322       return $this->write($sql, $params);
00323    }
00324 
00332    public final function loadComment($id) {
00333       $sql = 'SELECT * FROM comment WHERE id = :id';
00334       $params = array(':id' => $id);
00335       $r = $this->read($sql, $params);
00336       if (count($r)) {
00337          $comment = new DMComment();
00338          $comment->setID($id);
00339          try {
00340             $comment->setEmail($r[0]['email']);
00341          } catch (DMIllegalArgumentException $e) {}
00342          $comment->setName($r[0]['name']);
00343          $comment->setValue($r[0]['text']);
00344          $comment->setApproved($r[0]['is_approved']);
00345          $comment->setTimestamp(new DMDateTime($r[0]['posted_at']));
00346 
00347          $collection = DMCollectionFactory::getCollection($r[0]['alias']);
00348          if ($collection) {
00349             $object = DMObjectFactory::getObject($collection, $r[0]['ptr']);
00350             $comment->setObject($object);
00351             return $comment;
00352          }
00353          return null;
00354       } else {
00355          throw new DMUnavailableModelException(
00356             DMLocalizedString::getString("INVALID_COMMENT"));
00357       }
00358    }
00359 
00366    public final function saveComment(DMComment $comment) {
00367       $sql = "UPDATE comment SET email = :email, text = :text,
00368             name = :name, is_approved = :is_approved
00369          WHERE id = :id";
00370       $params = array(
00371          ':email' => $comment->getEmail(),
00372          ':text' => $comment->getValue(),
00373          ':name' => $comment->getName(),
00374          ':is_approved' => (int) $comment->isApproved(),
00375          ':id' => $comment->getID()
00376       );
00377       return $this->write($sql, $params);
00378    }
00379 
00384    public final function getMeanNumCommentsPerObjectInCollection(
00385          DMCollection $col) {
00386       $num_objects = $col->getNumObjects();
00387       if ($num_objects > 0) {
00388          $cq = new DMCommentQuery($this);
00389          $cq->addCollection($col);
00390          $cq->setApproved(1);
00391          $cq->getSearchResults();
00392          return $cq->getNumResults() / $num_objects;
00393       }
00394       return (float) 0;
00395    }
00396 
00401    public final function getMedianNumCommentsPerObjectInCollection(
00402          DMCollection $col) {
00403       $sql = "SELECT COUNT(id) AS total
00404          FROM comment
00405          WHERE alias = :alias
00406          GROUP BY ptr
00407          ORDER BY total";
00408       $params = array(
00409          'alias' => $col->getAlias()
00410       );
00411       $result = $this->read($sql, $params);
00412 
00413       $counts = array();
00414       $num_counts = $col->getNumObjects() - count($result);
00415       for ($i = 0; $i < $num_counts; $i++) {
00416          $counts[] = 0;
00417       }
00418       foreach ($result as $row) {
00419          $counts[] = $row['total'];
00420       }
00421       if ($num_counts) {
00422          $middle = round($num_counts / 2);
00423          return ($middle - 1 > 0) ? $counts[$middle - 1] : 0;
00424       }
00425       return 0;
00426    }
00427 
00431    public function getNumObjectsWithApprovedComments() {
00432       $sql = 'SELECT COUNT(alias) AS count FROM (
00433             SELECT DISTINCT alias, ptr
00434             FROM comment
00435             WHERE is_approved > 0
00436          ) AS count';
00437       $result = $this->read($sql, array());
00438       return (int) $result[0]['count'];
00439    }
00440 
00446    public final function addObjectComment(DMObject $obj, DMComment $comment) {
00447       // check for duplicates, return true without saving if found
00448       $sql = "SELECT name, email, text
00449          FROM comment WHERE ptr = :ptr AND alias = :alias";
00450       $params = array(
00451          ':ptr' => $obj->getPtr(),
00452          ':alias' => $obj->getCollection()->getAlias()
00453       );
00454       foreach ($this->read($sql, $params) as $r) {
00455          if ($r['name'] == $comment->getName()
00456                && $r['email'] == $comment->getEmail()
00457                && $r['text'] == $comment->getValue()) {
00458             return true;
00459          }
00460       }
00461       // apparently no duplicates, so insert
00462       $params = array(
00463          ':name' => $comment->getName(),
00464          ':email' => $comment->getEmail(),
00465          ':text' => $comment->getValue(),
00466          ':ptr' => $obj->getPtr(),
00467          ':alias' => $obj->getCollection()->getAlias(),
00468          ':is_approved' => (int) $comment->isApproved(),
00469          ':posted_at' => date('Y-m-d H:i:s', time())
00470       );
00471       if ($comment->getID()) {
00472          $sql = "INSERT INTO comment(id, name, email, text, ptr, alias,
00473                is_approved, posted_at)
00474             VALUES(:id, :name, :email, :text, :ptr, :alias, :is_approved,
00475                :posted_at)";
00476          $params[':id'] = $comment->getID();
00477       } else {
00478          $sql = "INSERT INTO comment(name, email, text, ptr, alias,
00479                is_approved, posted_at)
00480             VALUES(:name, :email, :text, :ptr, :alias, :is_approved,
00481                :posted_at)";
00482       }
00483       $this->write($sql, $params);
00484 
00485       // get last-inserted id in a database-independent way
00486       $sql = "SELECT MAX(id) AS id FROM comment";
00487       $result = $this->read($sql, array());
00488       $comment->setID($result[0]['id']);
00489       return true;
00490    }
00491 
00497    public function getObjectsWithMostComments($limit) {
00498       $sql = sprintf('SELECT COUNT(id) AS count, alias, ptr
00499          FROM comment
00500          WHERE is_approved > 0
00501          GROUP BY alias, ptr
00502          ORDER BY count DESC
00503          LIMIT %d',
00504          (int) $limit
00505       );
00506       $objects = array();
00507       foreach ($this->read($sql, array()) as $o) {
00508          try {
00509             $col = DMCollectionFactory::getCollection($o['alias']);
00510             $objects[] = DMObjectFactory::getObject($col, $o['ptr']);
00511          } catch (DMUnavailableModelException $e) {
00512             // invalid object; skip it
00513          }
00514       }
00515       return $objects;
00516    }
00517 
00518    /**************************** RATINGS **********************************/
00519 
00526    public final function getAllRatingsForObject(DMObject $obj) {
00527       $sql = "SELECT value FROM rating WHERE alias = :alias AND ptr = :ptr";
00528       $params = array(
00529          ':alias' => $obj->getCollection()->getAlias(),
00530          ':ptr' => $obj->getPtr()
00531       );
00532       $ratings = array();
00533       foreach ($this->read($sql, $params) as $r) {
00534          $ratings[] = new DMRating($r['value'], 100);
00535       }
00536       return $ratings;
00537    }
00538 
00547    public final function getHighestRatedObjects(array $collections, $page,
00548          $limit, &$total) {
00549       // extract collection aliases from array of DMCollections
00550       $aliases = array();
00551       foreach ($collections as $c) {
00552          $aliases[] = "'" . addslashes($c->getAlias()) . "'";
00553       }
00554 
00555       // get the count
00556       $sql = "SELECT COUNT(id) AS count FROM rating ";
00557       if (count($aliases)) {
00558          $sql .= sprintf("WHERE alias IN (%s) ",
00559                implode(", ", $aliases));
00560       }
00561       $result = $this->read($sql, array());
00562       $total = (int) $result[0]['count'];
00563 
00564       $sql = "SELECT alias, ptr FROM rating ";
00565       if (count($aliases)) {
00566          $sql .= sprintf("WHERE alias IN (%s) ",
00567                implode(", ", $aliases));
00568       }
00569       $offset = ($page - 1) * $limit;
00570       $sql .= sprintf("GROUP BY alias, ptr
00571          ORDER BY AVG(value) DESC
00572          LIMIT %d OFFSET %d",
00573          $limit, $offset);
00574 
00575       $objects = array();
00576       foreach ($this->read($sql, array()) as $r) {
00577          try {
00578             $col = DMCollectionFactory::getCollection($r['alias']);
00579             $objects[] = DMObjectFactory::getObject($col, $r['ptr']);
00580          } catch (DMUnavailableModelException $e) {
00581             // invalid object; skip it
00582          }
00583       }
00584       return $objects;
00585    }
00586 
00591    public function getMeanNumRatingsPerObjectInCollection(DMCollection $col) {
00592       $num_objects = $col->getNumObjects();
00593       if ($num_objects > 0) {
00594          $num_ratings = $this->getNumRatingsInCollection($col);
00595          return $num_ratings / $num_objects;
00596       }
00597       return (float) 0;
00598    }
00599 
00604    public final function getMedianNumRatingsPerObjectInCollection(
00605          DMCollection $col) {
00606       $sql = "SELECT COUNT(id) AS total
00607          FROM rating
00608          WHERE alias = :alias
00609          GROUP BY ptr
00610          ORDER BY total";
00611       $params = array(
00612          'alias' => $col->getAlias()
00613       );
00614       $result = $this->read($sql, $params);
00615 
00616       $counts = array();
00617       $num_counts = $col->getNumObjects() - count($result);
00618       for ($i = 0; $i < $num_counts; $i++) {
00619          $counts[] = 0;
00620       }
00621       foreach ($result as $row) {
00622          $counts[] = $row['total'];
00623       }
00624       if ($num_counts) {
00625          $middle = round($num_counts / 2);
00626          return ($middle - 1 > 0) ? $counts[$middle - 1] : 0;
00627       }
00628       return 0;
00629    }
00630 
00634    public function getNumObjectsWithRatings() {
00635       $sql = 'SELECT COUNT(alias) AS count FROM (
00636             SELECT DISTINCT alias, ptr
00637             FROM rating
00638          ) AS count';
00639       $result = $this->read($sql, array());
00640       return $result[0]['count'];
00641    }
00642 
00647    public final function getNumRatings() {
00648       $sql = "SELECT COUNT(id) AS count FROM rating";
00649       $r = $this->read($sql, array());
00650       return $r[0]['count'];
00651    }
00652 
00658    public final function getNumRatingsForObject(DMObject $obj) {
00659       $sql = "SELECT COUNT(id) AS count FROM rating
00660          WHERE alias = :alias AND ptr = :ptr";
00661       $params = array(
00662          ':alias' => $obj->getCollection()->getAlias(),
00663          ':ptr' => $obj->getPtr()
00664       );
00665       $result = $this->read($sql, $params);
00666       return (int) $result[0]['count'];
00667    }
00668 
00673    public function getNumRatingsInCollection(DMCollection $c) {
00674       $sql = 'SELECT COUNT(id) AS count FROM rating WHERE alias = :alias';
00675       $params = array(':alias' => $c->getAlias());
00676       $r = $this->read($sql, $params);
00677       return $r[0]['count'];
00678    }
00679 
00686    public final function addObjectRating(DMObject $obj, DMRating $rating) {
00687       $params = array(
00688          ':value' => $rating->getNormalizedValue(),
00689          ':ptr' => $obj->getPtr(),
00690          ':alias' => $obj->getCollection()->getAlias(),
00691          ':posted_at' => date('Y-m-d H:i:s', time())
00692       );
00693       if ($rating->getID()) {
00694          $sql = "INSERT INTO rating(id, value, ptr, alias, posted_at)
00695             VALUES(:id, :value, :ptr, :alias, :posted_at)";
00696          $params[':id'] = $rating->getID();
00697       } else {
00698          $sql = "INSERT INTO rating(value, ptr, alias, posted_at)
00699             VALUES(:value, :ptr, :alias, :posted_at)";
00700       }
00701 
00702       return $this->write($sql, $params);
00703    }
00704 
00710    public function getObjectsWithHighestRatings($limit) {
00711       $sql = sprintf('SELECT alias, ptr, AVG(value) AS value
00712          FROM rating
00713          GROUP BY alias, ptr
00714          ORDER BY value DESC
00715          LIMIT %d',
00716          (int) $limit
00717       );
00718       $objects = array();
00719       foreach ($this->read($sql, array()) as $o) {
00720          try {
00721             $col = DMCollectionFactory::getCollection($o['alias']);
00722             $objects[] = DMObjectFactory::getObject($col, $o['ptr']);
00723          } catch (DMUnavailableModelException $e) {
00724             // invalid object; skip it
00725          }
00726       }
00727       return $objects;
00728    }
00729 
00735    public function getObjectsWithLowestRatings($limit) {
00736       $sql = sprintf('SELECT alias, ptr, AVG(value) AS value
00737          FROM rating
00738          GROUP BY alias, ptr
00739          ORDER BY value ASC
00740          LIMIT %d',
00741          (int) $limit
00742       );
00743       $objects = array();
00744       foreach ($this->read($sql, array()) as $o) {
00745          try {
00746             $col = DMCollectionFactory::getCollection($o['alias']);
00747             $objects[] = DMObjectFactory::getObject($col, $o['ptr']);
00748          } catch (DMUnavailableModelException $e) {
00749             // invalid object; skip it
00750          }
00751       }
00752       return $objects;
00753    }
00754 
00760    public function getObjectsWithMostRatings($limit) {
00761       $sql = sprintf('SELECT COUNT(id) AS count, alias, ptr
00762          FROM rating
00763          GROUP BY alias, ptr
00764          ORDER BY count DESC
00765          LIMIT %d',
00766          (int) $limit
00767       );
00768       $objects = array();
00769       foreach ($this->read($sql, array()) as $o) {
00770          try {
00771             $col = DMCollectionFactory::getCollection($o['alias']);
00772             $objects[] = DMObjectFactory::getObject($col, $o['ptr']);
00773          } catch (DMUnavailableModelException $e) {
00774             // invalid object; skip it
00775          }
00776       }
00777       return $objects;
00778    }
00779 
00786    public final function filterObjectsWithRatingsBetween(array $input_objects,
00787          $min, $max) {
00788       $aliases = $ptrs = array();
00789       foreach ($input_objects as $obj) {
00790          if (!$obj instanceof DMObject) {
00791             continue;
00792          }
00793          $aliases[] = "'" . $obj->getCollection()->getAlias() . "'";
00794          $ptrs[] = $obj->getPtr();
00795       }
00796 
00797       $sql = sprintf("SELECT alias, ptr FROM rating
00798          WHERE value >= :min AND value <= :max
00799             AND alias IN (%s) AND ptr IN (%s)",
00800             implode(",", $aliases), implode(",", $ptrs));
00801       $params = array(
00802          ':min' => (int) $min,
00803          ':max' => (int) $max
00804       );
00805       $objects = array();
00806       foreach ($this->read($sql, $params) as $r) {
00807          try {
00808             $col = DMCollectionFactory::getCollection($r['alias']);
00809             $objects[] = DMObjectFactory::getObject($col, $r['ptr']);
00810          } catch (DMUnavailableModelException $e) {
00811             // invalid object; skip it
00812          }
00813       }
00814       return $objects;
00815    }
00816 
00822    public final function getRatingForObject(DMObject $obj) {
00823       $sql = "SELECT AVG(value) AS rating FROM rating
00824          WHERE alias = :alias AND ptr = :ptr";
00825       $params = array(
00826          ':alias' => $obj->getCollection()->getAlias(),
00827          ':ptr' => $obj->getPtr()
00828       );
00829       $r = $this->read($sql, $params);
00830       return $r[0]['rating'];
00831    }
00832 
00833    /****************************** TAGS *********************************/
00834 
00841    function getTagsForQueryAsCounts(DMTagQuery $query, $count) {
00842       $select = "SELECT *, COUNT(id) AS count ";
00843       if ($count) {
00844          $select = "SELECT COUNT(id) AS count ";
00845       }
00846       $from = "FROM tag ";
00847 
00848       $params = $where = $tmp = array();
00849       $i = 0;
00850 
00851       if (count($query->getObjects())) {
00852          foreach ($query->getObjects() as $obj) { // object
00853             $tmp[] = sprintf("(alias = :alias%d AND ptr = :ptr%d)", $i, $i);
00854             $params[':alias' . $i] = $obj->getCollection()->getAlias();
00855             $params[':ptr' . $i] = $obj->getPtr();
00856             $i++;
00857          }
00858       } else { // collections
00859          $skip_collections = false;
00860          foreach ($query->getCollections() as $col) {
00861             if ($col->getAlias() == "/dmdefault") {
00862                $skip_collections = true;
00863                break;
00864             }
00865          }
00866          if (!$skip_collections) {
00867             foreach ($query->getCollections() as $col) {
00868                $tmp[] = sprintf("alias = :alias%d", $i);
00869                $params[':alias' . $i] = $col->getAlias();
00870                $i++;
00871             }
00872          }
00873       }
00874       if (count($tmp)) {
00875          $where[] = implode(" OR ", $tmp);
00876       }
00877 
00878       // approved
00879       if ($query->getApproved() == -1) {
00880          $where[] = "is_approved < 1";
00881       } else if ($query->getApproved() == 1) {
00882          $where[] = "is_approved > 0";
00883       }
00884       
00885       // terms
00886       $i = 0;
00887       $tmp = array();
00888       foreach ($query->getPredicates() as $pred) {
00889          $tmp[] = sprintf("value LIKE :term%d", $i);
00890          $params[':term' . $i] = "%" . $pred->getString() . "%";
00891          $i++;
00892       }
00893       if (count($tmp)) {
00894          $where[] = implode(" AND ", $tmp);
00895       }
00896 
00897       // sort
00898       $group = $order = $limit = $offset = "";
00899       if (!$count) {
00900          $group = " GROUP BY value ";
00901       
00902          // order
00903          $order = "";
00904          if ($query->isSortingByFrequency()) {
00905             foreach ($query->getSortFields() as $field => $direction) {
00906                $order = sprintf(" ORDER BY %s %s ", $field, $direction);
00907             }
00908          } else {
00909             $order = ($this instanceof DMSQLiteDataStore)
00910                ? " ORDER BY RANDOM()" : "ORDER BY RAND()";
00911          }
00912          // limit/offset
00913          $limit = sprintf(" LIMIT %d ", $query->getNumResultsPerPage());
00914          $offset = sprintf(" OFFSET %d ", $query->getStart());
00915       }
00916 
00917       $sql = $select . $from . "WHERE " . implode(" AND ", $where) . $group
00918             . $order . $limit . $offset;
00919 
00920       $tags = array();
00921 
00922       $result = $this->read($sql, $params);
00923 
00924       if ($count) {
00925          return $result[0]['count'];
00926       } else {
00927          foreach ($result as $t) {
00928             $tags[$t['value']] = $t['count'];
00929          }
00930       }
00931       return $tags;
00932    }
00933 
00940    function getTagsForQueryAsObjects(DMTagQuery $query, $count) {
00941       $select = "SELECT * ";
00942       if ($count) {
00943          $select = "SELECT COUNT(id) AS count ";
00944       }
00945       $from = "FROM tag ";
00946 
00947       $params = $where = $tmp = array();
00948       $i = 0;
00949 
00950       if (count($query->getObjects())) {
00951          foreach ($query->getObjects() as $obj) { // object
00952             $tmp[] = sprintf("(alias = :alias%d AND ptr = :ptr%d)", $i, $i);
00953             $params[':alias' . $i] = $obj->getCollection()->getAlias();
00954             $params[':ptr' . $i] = $obj->getPtr();
00955             $i++;
00956          }
00957       } else { // collections
00958          $skip_collections = false;
00959          foreach ($query->getCollections() as $col) {
00960             if ($col->getAlias() == "/dmdefault") {
00961                $skip_collections = true;
00962                break;
00963             }
00964          }
00965          if (!$skip_collections) {
00966             foreach ($query->getCollections() as $col) {
00967                $tmp[] = sprintf("alias = :alias%d", $i);
00968                $params[':alias' . $i] = $col->getAlias();
00969                $i++;
00970             }
00971          }
00972       }
00973       if (count($tmp)) {
00974          $where[] = implode(" OR ", $tmp);
00975       }
00976 
00977       // approved
00978       if ($query->getApproved() == -1) {
00979          $where[] = "is_approved < 1";
00980       } else if ($query->getApproved() == 1) {
00981          $where[] = "is_approved > 0";
00982       }
00983       
00984       // terms
00985       $i = 0;
00986       $tmp = array();
00987       foreach ($query->getPredicates() as $pred) {
00988          $tmp[] = sprintf("value LIKE :term%d", $i);
00989          $params[':term' . $i] = "%" . $pred->getString() . "%";
00990          $i++;
00991       }
00992       if (count($tmp)) {
00993          $where[] = implode(" AND ", $tmp);
00994       }
00995 
00996       // sort
00997       $order = $limit = $offset = "";
00998       if (!$count) {
00999          // order
01000          $order = "";
01001          if ($query->isSortingByFrequency()) {
01002             // order
01003             if (count($query->getSortFields())) {
01004                $order = " ORDER BY";
01005                $orders = array();
01006                foreach ($query->getSortFields() as $field => $direction) {
01007                   $orders[] = sprintf(" %s %s ", $field, $direction);
01008                }
01009                $order .= implode(", ", $orders);
01010             }
01011          } else {
01012             $order = ($this instanceof DMSQLiteDataStore)
01013                ? " ORDER BY RANDOM()" : " ORDER BY RAND()";
01014          }
01015          // limit/offset
01016          $limit = sprintf(" LIMIT %d ", $query->getNumResultsPerPage());
01017          $offset = sprintf(" OFFSET %d ", $query->getStart());
01018       }
01019 
01020       $sql = $select . $from . "WHERE " . implode(" AND ", $where)
01021             . $order . $limit . $offset;
01022 
01023       $tags = array();
01024 
01025       $result = $this->read($sql, $params);
01026 
01027       if ($count) {
01028          return $result[0]['count'];
01029       } else {
01030          foreach ($result as $t) {
01031             try {
01032                $tags[] = $this->loadTag($t['id']);
01033             } catch (DMUnavailableModelException $e) {
01034                // tag is associated with an invalid object; skip it
01035             }
01036          }
01037       }
01038       return $tags;
01039    }
01040 
01047    public function deleteAllTagsWithValue($value) {
01048       $sql = "DELETE FROM tag WHERE UPPER(value) = UPPER(:value)";
01049       $params = array(
01050          'value' => $value
01051       );
01052       return $this->write($sql, $params);
01053    }
01054 
01060    public final function addObjectTag(DMObject $obj, DMTag $tag) {
01061       // check for duplicates, return true without saving if found
01062       $sql = "SELECT COUNT(id) AS count FROM tag
01063          WHERE value = :value AND ptr = :ptr AND alias = :alias";
01064       $params = array(
01065          ':value' => $tag->getValue(),
01066          ':ptr' => $obj->getPtr(),
01067          ':alias' => $obj->getCollection()->getAlias()
01068       );
01069       $result = $this->read($sql, $params);
01070       if ($result[0]['count']) {
01071          return true;
01072       }
01073       // apparently no duplicates, so insert
01074       $params = array(
01075          ':value' => $tag->getValue(),
01076          ':ptr' => $obj->getPtr(),
01077          ':alias' => $obj->getCollection()->getAlias(),
01078          ':is_approved' => (int) $tag->isApproved(),
01079          ':posted_at' => date('Y-m-d H:i:s', time())
01080       );
01081       if ($tag->getID()) {
01082          $sql = "INSERT INTO tag(id, value, ptr, alias, is_approved,
01083             posted_at)
01084             VALUES(:id, :value, :ptr, :alias, :is_approved, :posted_at)";
01085          $params[':id'] = $tag->getID();
01086       } else {
01087          $sql = "INSERT INTO tag(value, ptr, alias, is_approved, posted_at)
01088             VALUES(:value, :ptr, :alias, :is_approved, :posted_at)";
01089       }
01090 
01091       $this->write($sql, $params);
01092 
01093       // get last-inserted id in a database-independent way
01094       $sql = "SELECT MAX(id) AS id FROM tag";
01095       $result = $this->read($sql, array());
01096       $tag->setID($result[0]['id']);
01097       return true;
01098    }
01099 
01104    public function getMeanNumTagsPerObjectInCollection(DMCollection $col) {
01105       $num_objects = $col->getNumObjects();
01106       if ($num_objects > 0) {
01107          $tq = new DMTagQuery($this);
01108          $tq->setApproved(1);
01109          $tq->addCollection($col);
01110          $tq->getSearchResults();
01111          return $tq->getNumResults() / $num_objects;
01112       }
01113       return (float) 0;
01114    }
01115 
01120    public final function getMedianNumTagsPerObjectInCollection(
01121          DMCollection $col) {
01122       $sql = "SELECT COUNT(id) AS total
01123          FROM tag
01124          WHERE alias = :alias
01125          GROUP BY ptr
01126          ORDER BY total";
01127       $params = array(
01128          'alias' => $col->getAlias()
01129       );
01130       $result = $this->read($sql, $params);
01131 
01132       $counts = array();
01133       $num_counts = $col->getNumObjects() - count($result);
01134       for ($i = 0; $i < $num_counts; $i++) {
01135          $counts[] = 0;
01136       }
01137       foreach ($result as $row) {
01138          $counts[] = $row['total'];
01139       }
01140       if ($num_counts) {
01141          $middle = round($num_counts / 2);
01142          return ($middle - 1 > 0) ? $counts[$middle - 1] : 0;
01143       }
01144       return 0;
01145    }
01146 
01150    public function getNumObjectsWithApprovedTags() {
01151       $sql = 'SELECT COUNT(alias) AS count FROM (
01152             SELECT DISTINCT alias, ptr
01153             FROM tag
01154             WHERE is_approved > 0
01155          ) AS count';
01156       $result = $this->read($sql, array());
01157       return $result[0]['count'];
01158    }
01159 
01165    public function getObjectsWithMostTags($limit) {
01166       $sql = sprintf('SELECT COUNT(id) AS count, alias, ptr
01167          FROM tag
01168          WHERE is_approved > 0
01169          GROUP BY alias, ptr
01170          ORDER BY count DESC
01171          LIMIT %d',
01172          (int) $limit
01173       );
01174       $objects = array();
01175       foreach ($this->read($sql, array()) as $o) {
01176          $col = DMCollectionFactory::getCollection($o['alias']);
01177          $objects[] = DMObjectFactory::getObject($col, $o['ptr']);
01178       }
01179       return $objects;
01180    }
01181 
01186    public function deleteTag(DMTag $tag) {
01187       $sql = 'DELETE FROM tag WHERE id = :id';
01188       $params = array(':id' => $tag->getID());
01189       return $this->write($sql, $params);
01190    }
01191 
01197    public final function loadTag($id) {
01198       $sql = 'SELECT * FROM tag WHERE id = :id';
01199       $params = array(':id' => $id);
01200       $r = $this->read($sql, $params);
01201       if (sizeof($r) > 0) {
01202          $tag = new DMTag();
01203          $tag->setID($id);
01204          $tag->setValue($r[0]['value']);
01205          $tag->setTimestamp(new DMDateTime($r[0]['posted_at']));
01206          $tag->setApproved((bool) $r[0]['is_approved']);
01207          $collection = DMCollectionFactory::getCollection($r[0]['alias']);
01208          if ($collection) {
01209             $object = DMObjectFactory::getObject($collection, $r[0]['ptr']);
01210             $tag->setObject($object);
01211             return $tag;
01212          }
01213          return null;
01214       } else {
01215          throw new DMUnavailableModelException(
01216             DMLocalizedString::getString('INVALID_TAG'));
01217       }
01218    }
01219 
01225    public final function saveTag(DMTag $tag) {
01226       $sql = "UPDATE tag SET value = :value, is_approved = :is_approved
01227          WHERE id = :id";
01228       $params = array(
01229          ':value' => $tag->getValue(),
01230          ':is_approved' => (int) $tag->isApproved(),
01231          ':id' => $tag->getID()
01232       );
01233       return $this->write($sql, $params);
01234    }
01235 
01236 }
 All Data Structures Functions Variables