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 die($sql);
00267       $result = $this->read($sql, $params);
00268 
00269       if ($count) {
00270          return $result[0]['count'];
00271       } else {
00272          $comments = array();
00273          foreach ($result as $c) {
00274             $comments[] = $this->loadComment($c['id']);
00275          }
00276          return $comments;
00277       }
00278    }
00279 
00288    public final function getApprovedCommentsForObject(
00289          DMObject $obj, $page, $rpp, $sort = true) {
00290       $sql = "SELECT id FROM comment
00291          WHERE alias = :alias AND ptr = :ptr AND is_approved > 0";
00292       if (!is_null($sort)) {
00293          $sql .= " ORDER BY posted_at";
00294          if ($sort === false) {
00295             $sql .= " DESC";
00296          }
00297       }
00298       $sql .= sprintf(" LIMIT %d OFFSET %d",
00299             $rpp,
00300             ($page - 1) * $rpp);
00301       $params = array(
00302          ':alias' => $obj->getCollection()->getAlias(),
00303          ':ptr' => $obj->getPtr()
00304       );
00305       $comments = array();
00306       foreach ($this->read($sql, $params) as $c) {
00307          $comments[] = $this->loadComment($c['id']);
00308       }
00309       return $comments;
00310    }
00311 
00316    public function deleteComment(DMComment $comment) {
00317       $sql = 'DELETE FROM comment WHERE id = :id';
00318       $params = array(':id' => $comment->getID());
00319       return $this->write($sql, $params);
00320    }
00321 
00329    public final function loadComment($id) {
00330       $sql = 'SELECT * FROM comment WHERE id = :id';
00331       $params = array(':id' => $id);
00332       $r = $this->read($sql, $params);
00333       if (count($r)) {
00334          $comment = new DMComment();
00335          $comment->setID($id);
00336          try {
00337             $comment->setEmail($r[0]['email']);
00338          } catch (DMIllegalArgumentException $e) {}
00339          $comment->setName($r[0]['name']);
00340          $comment->setValue($r[0]['text']);
00341          $comment->setApproved($r[0]['is_approved']);
00342          $comment->setTimestamp(new DMDateTime($r[0]['posted_at']));
00343 
00344          $collection = DMCollectionFactory::getCollection($r[0]['alias']);
00345          if ($collection) {
00346             $object = DMObjectFactory::getObject($collection, $r[0]['ptr']);
00347             $comment->setObject($object);
00348             return $comment;
00349          }
00350          return null;
00351       } else {
00352          throw new DMUnavailableModelException(
00353             DMLocalizedString::getString("INVALID_COMMENT"));
00354       }
00355    }
00356 
00363    public final function saveComment(DMComment $comment) {
00364       $sql = "UPDATE comment SET email = :email, text = :text,
00365             name = :name, is_approved = :is_approved
00366          WHERE id = :id";
00367       $params = array(
00368          ':email' => $comment->getEmail(),
00369          ':text' => $comment->getValue(),
00370          ':name' => $comment->getName(),
00371          ':is_approved' => (int) $comment->isApproved(),
00372          ':id' => $comment->getID()
00373       );
00374       return $this->write($sql, $params);
00375    }
00376 
00381    public final function getMeanNumCommentsPerObjectInCollection(
00382          DMCollection $col) {
00383       $num_objects = $col->getNumObjects();
00384       if ($num_objects > 0) {
00385          $cq = new DMCommentQuery($this);
00386          $cq->addCollection($col);
00387          $cq->setApproved(1);
00388          $cq->getSearchResults();
00389          return $cq->getNumResults() / $num_objects;
00390       }
00391       return (float) 0;
00392    }
00393 
00398    public final function getMedianNumCommentsPerObjectInCollection(
00399          DMCollection $col) {
00400       $sql = "SELECT COUNT(id) AS total
00401          FROM comment
00402          WHERE alias = :alias
00403          GROUP BY ptr
00404          ORDER BY total";
00405       $params = array(
00406          'alias' => $col->getAlias()
00407       );
00408       $result = $this->read($sql, $params);
00409 
00410       $counts = array();
00411       $num_counts = $col->getNumObjects() - count($result);
00412       for ($i = 0; $i < $num_counts; $i++) {
00413          $counts[] = 0;
00414       }
00415       foreach ($result as $row) {
00416          $counts[] = $row['total'];
00417       }
00418       if ($num_counts) {
00419          $middle = round($num_counts / 2);
00420          return $counts[$middle - 1];
00421       }
00422       return 0;
00423    }
00424 
00428    public function getNumObjectsWithApprovedComments() {
00429       $sql = 'SELECT COUNT(alias) AS count FROM (
00430             SELECT DISTINCT alias, ptr
00431             FROM comment
00432             WHERE is_approved > 0
00433          ) AS count';
00434       $result = $this->read($sql, array());
00435       return (int) $result[0]['count'];
00436    }
00437 
00443    public final function addObjectComment(DMObject $obj, DMComment $comment) {
00444       // check for duplicates, return true without saving if found
00445       $sql = "SELECT name, email, text
00446          FROM comment WHERE ptr = :ptr AND alias = :alias";
00447       $params = array(
00448          ':ptr' => $obj->getPtr(),
00449          ':alias' => $obj->getCollection()->getAlias()
00450       );
00451       foreach ($this->read($sql, $params) as $r) {
00452          if ($r['name'] == $comment->getName()
00453                && $r['email'] == $comment->getEmail()
00454                && $r['text'] == $comment->getValue()) {
00455             return true;
00456          }
00457       }
00458       // apparently no duplicates, so insert
00459       $params = array(
00460          ':name' => $comment->getName(),
00461          ':email' => $comment->getEmail(),
00462          ':text' => $comment->getValue(),
00463          ':ptr' => $obj->getPtr(),
00464          ':alias' => $obj->getCollection()->getAlias(),
00465          ':is_approved' => (int) $comment->isApproved(),
00466          ':posted_at' => date('Y-m-d H:i:s', time())
00467       );
00468       if ($comment->getID()) {
00469          $sql = "INSERT INTO comment(id, name, email, text, ptr, alias,
00470                is_approved, posted_at)
00471             VALUES(:id, :name, :email, :text, :ptr, :alias, :is_approved,
00472                :posted_at)";
00473          $params[':id'] = $comment->getID();
00474       } else {
00475          $sql = "INSERT INTO comment(name, email, text, ptr, alias,
00476                is_approved, posted_at)
00477             VALUES(:name, :email, :text, :ptr, :alias, :is_approved,
00478                :posted_at)";
00479       }
00480       $this->write($sql, $params);
00481 
00482       // get last-inserted id in a database-independent way
00483       $sql = "SELECT MAX(id) AS id FROM comment";
00484       $result = $this->read($sql, array());
00485       $comment->setID($result[0]['id']);
00486       return true;
00487    }
00488 
00494    public function getObjectsWithMostComments($limit) {
00495       $sql = sprintf('SELECT COUNT(id) AS count, alias, ptr
00496          FROM comment
00497          WHERE is_approved > 0
00498          GROUP BY alias, ptr
00499          ORDER BY count DESC
00500          LIMIT %d',
00501          (int) $limit
00502       );
00503       $objects = array();
00504       foreach ($this->read($sql, array()) as $o) {
00505          $col = DMCollectionFactory::getCollection($o['alias']);
00506          $obj = DMObjectFactory::getObject($col, $o['ptr']);
00507          $objects[] = $obj;
00508       }
00509       return $objects;
00510    }
00511 
00512    /**************************** RATINGS **********************************/
00513 
00520    public final function getAllRatingsForObject(DMObject $obj) {
00521       $sql = "SELECT value FROM rating WHERE alias = :alias AND ptr = :ptr";
00522       $params = array(
00523          ':alias' => $obj->getCollection()->getAlias(),
00524          ':ptr' => $obj->getPtr()
00525       );
00526       $ratings = array();
00527       foreach ($this->read($sql, $params) as $r) {
00528          $ratings[] = new DMRating($r['value'], 100);
00529       }
00530       return $ratings;
00531    }
00532 
00541    public final function getHighestRatedObjects(array $collections, $page,
00542          $limit, &$total) {
00543       // extract collection aliases from array of DMCollections
00544       $aliases = array();
00545       foreach ($collections as $c) {
00546          $aliases[] = "'" . addslashes($c->getAlias()) . "'";
00547       }
00548 
00549       // get the count
00550       $sql = "SELECT COUNT(id) AS count FROM rating ";
00551       if (count($aliases)) {
00552          $sql .= sprintf("WHERE alias IN (%s) ",
00553                implode(", ", $aliases));
00554       }
00555       $result = $this->read($sql, array());
00556       $total = (int) $result[0]['count'];
00557 
00558       $sql = "SELECT alias, ptr FROM rating ";
00559       if (count($aliases)) {
00560          $sql .= sprintf("WHERE alias IN (%s) ",
00561                implode(", ", $aliases));
00562       }
00563       $offset = ($page - 1) * $limit;
00564       $sql .= sprintf("GROUP BY alias, ptr
00565          ORDER BY AVG(value) DESC
00566          LIMIT %d OFFSET %d",
00567          $limit, $offset);
00568 
00569       $objects = array();
00570       foreach ($this->read($sql, array()) as $r) {
00571          $obj = DMObjectFactory::getObject(
00572                DMCollectionFactory::getCollection($r['alias']), $r['ptr']);
00573          if ($obj) {
00574             $objects[] = $obj;
00575          }
00576       }
00577       return $objects;
00578    }
00579 
00584    public function getMeanNumRatingsPerObjectInCollection(DMCollection $col) {
00585       $num_objects = $col->getNumObjects();
00586       if ($num_objects > 0) {
00587          $num_ratings = $this->getNumRatingsInCollection($col);
00588          return $num_ratings / $num_objects;
00589       }
00590       return (float) 0;
00591    }
00592 
00597    public final function getMedianNumRatingsPerObjectInCollection(
00598          DMCollection $col) {
00599       $sql = "SELECT COUNT(id) AS total
00600          FROM rating
00601          WHERE alias = :alias
00602          GROUP BY ptr
00603          ORDER BY total";
00604       $params = array(
00605          'alias' => $col->getAlias()
00606       );
00607       $result = $this->read($sql, $params);
00608 
00609       $counts = array();
00610       $num_counts = $col->getNumObjects() - count($result);
00611       for ($i = 0; $i < $num_counts; $i++) {
00612          $counts[] = 0;
00613       }
00614       foreach ($result as $row) {
00615          $counts[] = $row['total'];
00616       }
00617       if ($num_counts) {
00618          $middle = round($num_counts / 2);
00619          return $counts[$middle - 1];
00620       }
00621       return 0;
00622    }
00623 
00627    public function getNumObjectsWithRatings() {
00628       $sql = 'SELECT COUNT(alias) AS count FROM (
00629             SELECT DISTINCT alias, ptr
00630             FROM rating
00631          ) AS count';
00632       $result = $this->read($sql, array());
00633       return $result[0]['count'];
00634    }
00635 
00640    public final function getNumRatings() {
00641       $sql = "SELECT COUNT(id) AS count FROM rating";
00642       $r = $this->read($sql, array());
00643       return $r[0]['count'];
00644    }
00645 
00651    public final function getNumRatingsForObject(DMObject $obj) {
00652       $sql = "SELECT COUNT(id) AS count FROM rating
00653          WHERE alias = :alias AND ptr = :ptr";
00654       $params = array(
00655          ':alias' => $obj->getCollection()->getAlias(),
00656          ':ptr' => $obj->getPtr()
00657       );
00658       $result = $this->read($sql, $params);
00659       return (int) $result[0]['count'];
00660    }
00661 
00666    public function getNumRatingsInCollection(DMCollection $c) {
00667       $sql = 'SELECT COUNT(id) AS count FROM rating WHERE alias = :alias';
00668       $params = array(':alias' => $c->getAlias());
00669       $r = $this->read($sql, $params);
00670       return $r[0]['count'];
00671    }
00672 
00679    public final function addObjectRating(DMObject $obj, DMRating $rating) {
00680       $params = array(
00681          ':value' => $rating->getNormalizedValue(),
00682          ':ptr' => $obj->getPtr(),
00683          ':alias' => $obj->getCollection()->getAlias(),
00684          ':posted_at' => date('Y-m-d H:i:s', time())
00685       );
00686       if ($rating->getID()) {
00687          $sql = "INSERT INTO rating(id, value, ptr, alias, posted_at)
00688             VALUES(:id, :value, :ptr, :alias, :posted_at)";
00689          $params[':id'] = $rating->getID();
00690       } else {
00691          $sql = "INSERT INTO rating(value, ptr, alias, posted_at)
00692             VALUES(:value, :ptr, :alias, :posted_at)";
00693       }
00694 
00695       return $this->write($sql, $params);
00696    }
00697 
00703    public function getObjectsWithHighestRatings($limit) {
00704       $sql = sprintf('SELECT alias, ptr, AVG(value) AS value
00705          FROM rating
00706          GROUP BY alias, ptr
00707          ORDER BY value DESC
00708          LIMIT %d',
00709          (int) $limit
00710       );
00711       $objects = array();
00712       foreach ($this->read($sql, array()) as $o) {
00713          $col = DMCollectionFactory::getCollection($o['alias']);
00714          $objects[] = DMObjectFactory::getObject($col, $o['ptr']);
00715       }
00716       return $objects;
00717    }
00718 
00724    public function getObjectsWithLowestRatings($limit) {
00725       $sql = sprintf('SELECT alias, ptr, AVG(value) AS value
00726          FROM rating
00727          GROUP BY alias, ptr
00728          ORDER BY value ASC
00729          LIMIT %d',
00730          (int) $limit
00731       );
00732       $objects = array();
00733       foreach ($this->read($sql, array()) as $o) {
00734          $col = DMCollectionFactory::getCollection($o['alias']);
00735          $objects[] = DMObjectFactory::getObject($col, $o['ptr']);
00736       }
00737       return $objects;
00738    }
00739 
00745    public function getObjectsWithMostRatings($limit) {
00746       $sql = sprintf('SELECT COUNT(id) AS count, alias, ptr
00747          FROM rating
00748          GROUP BY alias, ptr
00749          ORDER BY count DESC
00750          LIMIT %d',
00751          (int) $limit
00752       );
00753       $objects = array();
00754       foreach ($this->read($sql, array()) as $o) {
00755          $col = DMCollectionFactory::getCollection($o['alias']);
00756          $objects[] = DMObjectFactory::getObject($col, $o['ptr']);
00757       }
00758       return $objects;
00759    }
00760 
00767    public final function filterObjectsWithRatingsBetween(array $input_objects,
00768          $min, $max) {
00769       $aliases = $ptrs = array();
00770       foreach ($input_objects as $obj) {
00771          if (!$obj instanceof DMObject) {
00772             continue;
00773          }
00774          $aliases[] = "'" . $obj->getCollection()->getAlias() . "'";
00775          $ptrs[] = $obj->getPtr();
00776       }
00777 
00778       $sql = sprintf("SELECT alias, ptr FROM rating
00779          WHERE value >= :min AND value <= :max
00780             AND alias IN (%s) AND ptr IN (%s)",
00781             implode(",", $aliases), implode(",", $ptrs));
00782       $params = array(
00783          ':min' => (int) $min,
00784          ':max' => (int) $max
00785       );
00786       $objects = array();
00787       foreach ($this->read($sql, $params) as $r) {
00788          $objects[] = DMObjectFactory::getObject(
00789                DMCollectionFactory::getCollection($r['alias']), $r['ptr']);
00790       }
00791       return $objects;
00792    }
00793 
00799    public final function getRatingForObject(DMObject $obj) {
00800       $sql = "SELECT AVG(value) AS rating FROM rating
00801          WHERE alias = :alias AND ptr = :ptr";
00802       $params = array(
00803          ':alias' => $obj->getCollection()->getAlias(),
00804          ':ptr' => $obj->getPtr()
00805       );
00806       $r = $this->read($sql, $params);
00807       return $r[0]['rating'];
00808    }
00809 
00810    /****************************** TAGS *********************************/
00811 
00818    function getTagsForQueryAsCounts(DMTagQuery $query, $count) {
00819       $select = "SELECT *, COUNT(id) AS count ";
00820       if ($count) {
00821          $select = "SELECT COUNT(id) AS count ";
00822       }
00823       $from = "FROM tag ";
00824 
00825       $params = $where = $tmp = array();
00826       $i = 0;
00827 
00828       if (count($query->getObjects())) {
00829          foreach ($query->getObjects() as $obj) { // object
00830             $tmp[] = sprintf("(alias = :alias%d AND ptr = :ptr%d)", $i, $i);
00831             $params[':alias' . $i] = $obj->getCollection()->getAlias();
00832             $params[':ptr' . $i] = $obj->getPtr();
00833             $i++;
00834          }
00835       } else { // collections
00836          $skip_collections = false;
00837          foreach ($query->getCollections() as $col) {
00838             if ($col->getAlias() == "/dmdefault") {
00839                $skip_collections = true;
00840                break;
00841             }
00842          }
00843          if (!$skip_collections) {
00844             foreach ($query->getCollections() as $col) {
00845                $tmp[] = sprintf("alias = :alias%d", $i);
00846                $params[':alias' . $i] = $col->getAlias();
00847                $i++;
00848             }
00849          }
00850       }
00851       if (count($tmp)) {
00852          $where[] = implode(" OR ", $tmp);
00853       }
00854 
00855       // approved
00856       if ($query->getApproved() == -1) {
00857          $where[] = "is_approved < 1";
00858       } else if ($query->getApproved() == 1) {
00859          $where[] = "is_approved > 0";
00860       }
00861       
00862       // terms
00863       $i = 0;
00864       $tmp = array();
00865       foreach ($query->getPredicates() as $pred) {
00866          $tmp[] = sprintf("value LIKE :term%d", $i);
00867          $params[':term' . $i] = "%" . $pred->getString() . "%";
00868          $i++;
00869       }
00870       if (count($tmp)) {
00871          $where[] = implode(" AND ", $tmp);
00872       }
00873 
00874       // sort
00875       $group = $order = $limit = $offset = "";
00876       if (!$count) {
00877          $group = " GROUP BY value ";
00878       
00879          // order
00880          $order = "";
00881          if ($query->isSortingByFrequency()) {
00882             foreach ($query->getSortFields() as $field => $direction) {
00883                $order = sprintf(" ORDER BY %s %s ", $field, $direction);
00884             }
00885          } else {
00886             $order = ($this instanceof DMSQLiteDataStore)
00887                ? " ORDER BY RANDOM()" : "ORDER BY RAND()";
00888          }
00889          // limit/offset
00890          $limit = sprintf(" LIMIT %d ", $query->getNumResultsPerPage());
00891          $offset = sprintf(" OFFSET %d ", $query->getStart());
00892       }
00893 
00894       $sql = $select . $from . "WHERE " . implode(" AND ", $where) . $group
00895             . $order . $limit . $offset;
00896 
00897       $tags = array();
00898 
00899       $result = $this->read($sql, $params);
00900 
00901       if ($count) {
00902          return $result[0]['count'];
00903       } else {
00904          foreach ($result as $t) {
00905             $tags[$t['value']] = $t['count'];
00906          }
00907       }
00908       return $tags;
00909    }
00910 
00917    function getTagsForQueryAsObjects(DMTagQuery $query, $count) {
00918       $select = "SELECT * ";
00919       if ($count) {
00920          $select = "SELECT COUNT(id) AS count ";
00921       }
00922       $from = "FROM tag ";
00923 
00924       $params = $where = $tmp = array();
00925       $i = 0;
00926 
00927       if (count($query->getObjects())) {
00928          foreach ($query->getObjects() as $obj) { // object
00929             $tmp[] = sprintf("(alias = :alias%d AND ptr = :ptr%d)", $i, $i);
00930             $params[':alias' . $i] = $obj->getCollection()->getAlias();
00931             $params[':ptr' . $i] = $obj->getPtr();
00932             $i++;
00933          }
00934       } else { // collections
00935          $skip_collections = false;
00936          foreach ($query->getCollections() as $col) {
00937             if ($col->getAlias() == "/dmdefault") {
00938                $skip_collections = true;
00939                break;
00940             }
00941          }
00942          if (!$skip_collections) {
00943             foreach ($query->getCollections() as $col) {
00944                $tmp[] = sprintf("alias = :alias%d", $i);
00945                $params[':alias' . $i] = $col->getAlias();
00946                $i++;
00947             }
00948          }
00949       }
00950       if (count($tmp)) {
00951          $where[] = implode(" OR ", $tmp);
00952       }
00953 
00954       // approved
00955       if ($query->getApproved() == -1) {
00956          $where[] = "is_approved < 1";
00957       } else if ($query->getApproved() == 1) {
00958          $where[] = "is_approved > 0";
00959       }
00960       
00961       // terms
00962       $i = 0;
00963       $tmp = array();
00964       foreach ($query->getPredicates() as $pred) {
00965          $tmp[] = sprintf("value LIKE :term%d", $i);
00966          $params[':term' . $i] = "%" . $pred->getString() . "%";
00967          $i++;
00968       }
00969       if (count($tmp)) {
00970          $where[] = implode(" AND ", $tmp);
00971       }
00972 
00973       // sort
00974       $order = $limit = $offset = "";
00975       if (!$count) {
00976          // order
00977          $order = "";
00978          if ($query->isSortingByFrequency()) {
00979             // order
00980             if (count($query->getSortFields())) {
00981                $order = " ORDER BY";
00982                $orders = array();
00983                foreach ($query->getSortFields() as $field => $direction) {
00984                   $orders[] = sprintf(" %s %s ", $field, $direction);
00985                }
00986                $order .= implode(", ", $orders);
00987             }
00988          } else {
00989             $order = ($this instanceof DMSQLiteDataStore)
00990                ? " ORDER BY RANDOM()" : " ORDER BY RAND()";
00991          }
00992          // limit/offset
00993          $limit = sprintf(" LIMIT %d ", $query->getNumResultsPerPage());
00994          $offset = sprintf(" OFFSET %d ", $query->getStart());
00995       }
00996 
00997       $sql = $select . $from . "WHERE " . implode(" AND ", $where)
00998             . $order . $limit . $offset;
00999 
01000       $tags = array();
01001 
01002       $result = $this->read($sql, $params);
01003 
01004       if ($count) {
01005          return $result[0]['count'];
01006       } else {
01007          foreach ($result as $t) {
01008             $tags[] = $this->loadTag($t['id']);
01009          }
01010       }
01011       return $tags;
01012    }
01013 
01020    public function deleteAllTagsWithValue($value) {
01021       $sql = "DELETE FROM tag WHERE UPPER(value) = UPPER(:value)";
01022       $params = array(
01023          'value' => $value
01024       );
01025       return $this->write($sql, $params);
01026    }
01027 
01033    public final function addObjectTag(DMObject $obj, DMTag $tag) {
01034       // check for duplicates, return true without saving if found
01035       $sql = "SELECT COUNT(id) AS count FROM tag
01036          WHERE value = :value AND ptr = :ptr AND alias = :alias";
01037       $params = array(
01038          ':value' => $tag->getValue(),
01039          ':ptr' => $obj->getPtr(),
01040          ':alias' => $obj->getCollection()->getAlias()
01041       );
01042       $result = $this->read($sql, $params);
01043       if ($result[0]['count']) {
01044          return true;
01045       }
01046       // apparently no duplicates, so insert
01047       $params = array(
01048          ':value' => $tag->getValue(),
01049          ':ptr' => $obj->getPtr(),
01050          ':alias' => $obj->getCollection()->getAlias(),
01051          ':is_approved' => (int) $tag->isApproved(),
01052          ':posted_at' => date('Y-m-d H:i:s', time())
01053       );
01054       if ($tag->getID()) {
01055          $sql = "INSERT INTO tag(id, value, ptr, alias, is_approved,
01056             posted_at)
01057             VALUES(:id, :value, :ptr, :alias, :is_approved, :posted_at)";
01058          $params[':id'] = $tag->getID();
01059       } else {
01060          $sql = "INSERT INTO tag(value, ptr, alias, is_approved, posted_at)
01061             VALUES(:value, :ptr, :alias, :is_approved, :posted_at)";
01062       }
01063 
01064       $this->write($sql, $params);
01065 
01066       // get last-inserted id in a database-independent way
01067       $sql = "SELECT MAX(id) AS id FROM tag";
01068       $result = $this->read($sql, array());
01069       $tag->setID($result[0]['id']);
01070       return true;
01071    }
01072 
01077    public function getMeanNumTagsPerObjectInCollection(DMCollection $col) {
01078       $num_objects = $col->getNumObjects();
01079       if ($num_objects > 0) {
01080          $tq = new DMTagQuery($this);
01081          $tq->setApproved(1);
01082          $tq->addCollection($col);
01083          $tq->getSearchResults();
01084          return $tq->getNumResults() / $num_objects;
01085       }
01086       return (float) 0;
01087    }
01088 
01093    public final function getMedianNumTagsPerObjectInCollection(
01094          DMCollection $col) {
01095       $sql = "SELECT COUNT(id) AS total
01096          FROM tag
01097          WHERE alias = :alias
01098          GROUP BY ptr
01099          ORDER BY total";
01100       $params = array(
01101          'alias' => $col->getAlias()
01102       );
01103       $result = $this->read($sql, $params);
01104 
01105       $counts = array();
01106       $num_counts = $col->getNumObjects() - count($result);
01107       for ($i = 0; $i < $num_counts; $i++) {
01108          $counts[] = 0;
01109       }
01110       foreach ($result as $row) {
01111          $counts[] = $row['total'];
01112       }
01113       if ($num_counts) {
01114          $middle = round($num_counts / 2);
01115          return $counts[$middle - 1];
01116       }
01117       return 0;
01118    }
01119 
01123    public function getNumObjectsWithApprovedTags() {
01124       $sql = 'SELECT COUNT(alias) AS count FROM (
01125             SELECT DISTINCT alias, ptr
01126             FROM tag
01127             WHERE is_approved > 0
01128          ) AS count';
01129       $result = $this->read($sql, array());
01130       return $result[0]['count'];
01131    }
01132 
01138    public function getObjectsWithMostTags($limit) {
01139       $sql = sprintf('SELECT COUNT(id) AS count, alias, ptr
01140          FROM tag
01141          WHERE is_approved > 0
01142          GROUP BY alias, ptr
01143          ORDER BY count DESC
01144          LIMIT %d',
01145          (int) $limit
01146       );
01147       $objects = array();
01148       foreach ($this->read($sql, array()) as $o) {
01149          $col = DMCollectionFactory::getCollection($o['alias']);
01150          $objects[] = DMObjectFactory::getObject($col, $o['ptr']);
01151       }
01152       return $objects;
01153    }
01154 
01159    public function deleteTag(DMTag $tag) {
01160       $sql = 'DELETE FROM tag WHERE id = :id';
01161       $params = array(':id' => $tag->getID());
01162       return $this->write($sql, $params);
01163    }
01164 
01170    public final function loadTag($id) {
01171       $sql = 'SELECT * FROM tag WHERE id = :id';
01172       $params = array(':id' => $id);
01173       $r = $this->read($sql, $params);
01174       if (sizeof($r) > 0) {
01175          $tag = new DMTag();
01176          $tag->setID($id);
01177          $tag->setValue($r[0]['value']);
01178          $tag->setTimestamp(new DMDateTime($r[0]['posted_at']));
01179          $tag->setApproved((bool) $r[0]['is_approved']);
01180          $collection = DMCollectionFactory::getCollection($r[0]['alias']);
01181          if ($collection) {
01182             $object = DMObjectFactory::getObject($collection, $r[0]['ptr']);
01183             $tag->setObject($object);
01184             return $tag;
01185          }
01186          return null;
01187       } else {
01188          throw new DMUnavailableModelException(
01189             DMLocalizedString::getString('INVALID_TAG'));
01190       }
01191    }
01192 
01198    public final function saveTag(DMTag $tag) {
01199       $sql = "UPDATE tag SET value = :value, is_approved = :is_approved
01200          WHERE id = :id";
01201       $params = array(
01202          ':value' => $tag->getValue(),
01203          ':is_approved' => (int) $tag->isApproved(),
01204          ':id' => $tag->getID()
01205       );
01206       return $this->write($sql, $params);
01207    }
01208 
01209 }
 All Data Structures Functions Variables