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