2 rcs_id('$Id: RatingsDb.php,v 1.8 2004-07-20 18:00:50 dfrankow Exp $');
5 * @author: Dan Frankowski (wikilens author), Reini Urban (as plugin)
8 * - fix RATING_STORAGE = WIKIPAGE
10 * - finish mysuggest.c (external engine with data from mysql)
11 * - add php_prediction
12 * - add the various show modes (esp. TopN queries in PHP)
16 dimension INT(4) NOT NULL,
17 raterpage INT(11) NOT NULL,
18 rateepage INT(11) NOT NULL,
19 ratingvalue FLOAT NOT NULL,
20 rateeversion INT(11) NOT NULL,
21 isPrivate ENUM('yes','no'),
22 tstamp TIMESTAMP(14) NOT NULL,
23 PRIMARY KEY (dimension, raterpage, rateepage)
27 //define('RATING_STORAGE','WIKIPAGE'); // not fully supported yet
28 define('RATING_STORAGE','SQL'); // only for mysql yet.
29 // leave undefined for internal, slow php engine.
30 //define('RATING_EXTERNAL',PHPWIKI_DIR . 'suggest.exe');
33 define('EXPLICIT_RATINGS_DIMENSION', 0);
35 //TODO: split into SQL and metadata backends
36 class RatingsDb extends WikiDB {
38 function RatingsDb() {
40 $this->_dbi = &$request->_dbi;
41 $this->_backend = &$this->_dbi->_backend;
42 $this->dimension = null;
43 if (RATING_STORAGE == 'SQL') {
44 if (isa($this->_backend, 'WikiDB_backend_PearDB'))
45 $this->dbtype = "PearDB";
47 $this->dbtype = "ADODB";
48 $this->iter_class = "WikiDB_backend_".$this->dbtype."_generic_iter";
50 extract($this->_backend->_table_names);
51 if (empty($rating_tbl)) {
52 $rating_tbl = (!empty($GLOBALS['DBParams']['prefix'])
53 ? $GLOBALS['DBParams']['prefix'] : '') . 'rating';
54 $request->_dbi->_backend->_table_names['rating_tbl'] = $rating_tbl;
57 $this->iter_class = "WikiDB_Array_PageIterator";
61 // this is a singleton. It ensures there is only 1 ratingsDB.
62 function &getTheRatingsDb(){
63 static $_theRatingsDb;
65 if (!isset($_theRatingsDb)){
66 $_theRatingsDb = new RatingsDb();
68 //echo "rating db is $_theRatingsDb";
69 return $_theRatingsDb;
73 /// *************************************************************************************
75 // from Reini Urban's RateIt plugin
76 function addRating($rating, $userid, $pagename, $dimension) {
77 if (RATING_STORAGE == 'SQL') {
78 $page = $this->_dbi->getPage($pagename);
79 $current = $page->getCurrentRevision();
80 $rateeversion = $current->getVersion();
81 $this->sql_rate($userid, $pagename, $rateeversion, $dimension, $rating);
83 $this->metadata_set_rating($userid, $pagename, $dimension, $rating);
87 function deleteRating($userid=null, $pagename=null, $dimension=null) {
88 if (is_null($dimension)) $dimension = $this->dimension;
89 if (is_null($userid)) $userid = $this->userid;
90 if (is_null($pagename)) $pagename = $this->pagename;
91 if (RATING_STORAGE == 'SQL') {
92 $this->sql_delete_rating($userid, $pagename, $dimension);
94 $this->metadata_set_rating($userid, $pagename, $dimension, -1);
98 function getRating($userid=null, $pagename=null, $dimension=null) {
99 if (RATING_STORAGE == 'SQL') {
100 $ratings_iter = $this->sql_get_rating($dimension, $userid, $pagename);
101 if ($rating = $ratings_iter->next() and isset($rating['ratingvalue'])) {
102 return $rating['ratingvalue'];
106 return $this->metadata_get_rating($userid, $pagename, $dimension);
110 function getUsersRated($dimension=null, $orderby = null) {
111 if (is_null($dimension)) $dimension = $this->dimension;
112 if (is_null($userid)) $userid = $this->userid;
113 if (is_null($pagename)) $pagename = $this->pagename;
114 if (RATING_STORAGE == 'SQL') {
115 $ratings_iter = $this->sql_get_users_rated($dimension, $orderby);
116 if ($rating = $ratings_iter->next() and isset($rating['ratingvalue'])) {
117 return $rating['ratingvalue'];
121 return $this->metadata_get_users_rated($dimension, $orderby);
129 * @param dimension The rating dimension id.
132 * If this is null (or left off), the search for ratings
133 * is not restricted by dimension.
135 * @param rater The page id of the rater, i.e. page doing the rating.
136 * This is a Wiki page id, often of a user page.
139 * If this is null (or left off), the search for ratings
140 * is not restricted by rater.
141 * TODO: Support an array
143 * @param ratee The page id of the ratee, i.e. page being rated.
144 * Example: "DudeWheresMyCar"
146 * If this is null (or left off), the search for ratings
147 * is not restricted by ratee.
149 * @param orderby An order-by clause with fields and (optionally) ASC
151 * Example: "ratingvalue DESC"
153 * If this is null (or left off), the search for ratings
154 * has no guaranteed order
156 * @param pageinfo The type of page that has its info returned (i.e.,
157 * 'pagename', 'hits', and 'pagedata') in the rows.
160 * If this is null (or left off), the info returned
161 * is for the 'ratee' page (i.e., thing being rated).
163 * @return DB iterator with results
165 function get_rating($dimension=null, $rater=null, $ratee=null,
166 $orderby = null, $pageinfo = "ratee") {
167 if (RATING_STORAGE == 'SQL') {
168 $ratings_iter = $this->sql_get_rating($dimension, $rater, $pagename);
169 if ($rating = $ratings_iter->next() and isset($rating['ratingvalue'])) {
170 return $rating['ratingvalue'];
174 return $this->metadata_get_rating($rater, $pagename, $dimension);
177 return $this->_backend->get_rating($dimension, $rater, $ratee,
178 $orderby, $pageinfo);
182 function get_users_rated($dimension=null, $orderby = null) {
183 if (RATING_STORAGE == 'SQL') {
184 $ratings_iter = $this->sql_get_users_rated($dimension, $orderby);
185 if ($rating = $ratings_iter->next() and isset($rating['ratingvalue'])) {
186 return $rating['ratingvalue'];
190 return $this->metadata_get_users_rated($dimension, $orderby);
193 return $this->_backend->get_users_rated($dimension, $orderby);
198 * Like get_rating(), but return a WikiDB_PageIterator
201 function get_rating_page($dimension=null, $rater=null, $ratee=null,
202 $orderby = null, $pageinfo = "ratee") {
203 if (RATING_STORAGE == 'SQL') {
204 return $this->sql_get_rating($dimension, $rater, $ratee, $orderby, $pageinfo);
206 // empty dummy iterator
208 return new WikiDB_Array_PageIterator($pages);
215 * @param rater The page id of the rater, i.e. page doing the rating.
216 * This is a Wiki page id, often of a user page.
217 * @param ratee The page id of the ratee, i.e. page being rated.
218 * @param dimension The rating dimension id.
222 * @return true upon success
224 function delete_rating($rater, $ratee, $dimension) {
225 if (RATING_STORAGE == 'SQL') {
226 $this->sql_delete_rating($rater, $ratee, $dimension);
228 $this->metadata_set_rating($rater, $ratee, $dimension, -1);
231 return $this->_backend->delete_rating($rater, $ratee, $dimension);
238 * @param rater The page id of the rater, i.e. page doing the rating.
239 * This is a Wiki page id, often of a user page.
240 * @param ratee The page id of the ratee, i.e. page being rated.
241 * @param rateeversion The version of the ratee page.
242 * @param dimension The rating dimension id.
243 * @param rating The rating value (a float).
247 * @return true upon success
249 function rate($rater, $ratee, $rateeversion, $dimension, $rating) {
250 if (RATING_STORAGE == 'SQL') {
251 $page = $this->_dbi->getPage($pagename);
252 $current = $page->getCurrentRevision();
253 $rateeversion = $current->getVersion();
254 $this->sql_rate($userid, $pagename, $rateeversion, $dimension, $rating);
256 $this->metadata_set_rating($userid, $pagename, $dimension, $rating);
259 return $this->_backend->rate($rater, $ratee, $rateeversion, $dimension, $rating);
263 //function getUsersRated(){}
265 //*******************************************************************************
267 // Use wikilens/RatingsUser.php for the php methods.
270 // Currently we have to call the "suggest" CGI
271 // http://www-users.cs.umn.edu/~karypis/suggest/
272 // until we implement a simple recommendation engine.
273 // Note that "suggest" is only free for non-profit organizations.
274 // I am currently writing a binary CGI using suggest, which loads
276 function getPrediction($userid=null, $pagename=null, $dimension=null) {
277 if (is_null($dimension)) $dimension = $this->dimension;
278 if (is_null($userid)) $userid = $this->userid;
279 if (is_null($pagename)) $pagename = $this->pagename;
281 if (RATING_STORAGE == 'SQL') {
282 $dbi = &$this->_dbi->_backend;
283 if (isset($pagename))
284 $page = $dbi->_get_pageid($pagename);
288 $user = $dbi->_get_pageid($userid);
292 if (defined('RATING_EXTERNAL') and RATING_EXTERNAL) {
293 // how call suggest.exe? as CGI or natively
294 //$rating = HTML::Raw("<!--#include virtual=".RATING_ENGINE." -->");
295 $args = "-u$user -p$page -malpha"; // --top 10
296 if (isset($dimension))
297 $args .= " -d$dimension";
298 $rating = passthru(RATING_EXTERNAL . " $args");
300 $rating = $this->php_prediction($userid, $pagename, $dimension);
306 * TODO: slow item-based recommendation engine, similar to suggest RType=2.
307 * Only the SUGGEST_EstimateAlpha part
308 * Take wikilens/RatingsUser.php for the php methods.
310 function php_prediction($userid=null, $pagename=null, $dimension=null) {
311 if (is_null($dimension)) $dimension = $this->dimension;
312 if (is_null($userid)) $userid = $this->userid;
313 if (is_null($pagename)) $pagename = $this->pagename;
314 if (RATING_STORAGE == 'SQL') {
322 function getNumUsers($pagename=null, $dimension=null) {
323 if (is_null($dimension)) $dimension = $this->dimension;
324 if (is_null($pagename)) $pagename = $this->pagename;
325 if (RATING_STORAGE == 'SQL') {
326 $ratings_iter = $this->sql_get_rating($dimension, null, $pagename,
328 return $ratings_iter->count();
330 if (!$pagename) return 0;
331 $page = $this->_dbi->getPage($pagename);
332 $data = $page->get('rating');
333 if (!empty($data[$dimension]))
334 return count($data[$dimension]);
339 // TODO: metadata method
340 function getAvg($pagename=null, $dimension=null) {
341 if (is_null($dimension)) $dimension = $this->dimension;
342 if (is_null($pagename)) $pagename = $this->pagename;
343 if (RATING_STORAGE == 'SQL') {
344 $dbi = &$this->_dbi->_backend;
346 if (isset($pagename)) {
347 $raterid = $dbi->_get_pageid($pagename, true);
348 $where .= " AND raterpage=$raterid";
350 if (isset($dimension)) {
351 $where .= " AND dimension=$dimension";
353 //$dbh = &$this->_dbi;
354 extract($dbi->_table_names);
355 $query = "SELECT AVG(ratingvalue) as avg"
356 . " FROM $rating_tbl r, $page_tbl p "
357 . $where. " GROUP BY raterpage";
358 $result = $dbi->_dbh->query($query);
359 $iter = new $this->iter_class($this,$result);
360 $row = $iter->next();
363 if (!$pagename) return 0;
367 //*******************************************************************************
372 * @param dimension The rating dimension id.
375 * If this is null (or left off), the search for ratings
376 * is not restricted by dimension.
378 * @param rater The page id of the rater, i.e. page doing the rating.
379 * This is a Wiki page id, often of a user page.
382 * If this is null (or left off), the search for ratings
383 * is not restricted by rater.
384 * TODO: Support an array
386 * @param ratee The page id of the ratee, i.e. page being rated.
387 * Example: "DudeWheresMyCar"
389 * If this is null (or left off), the search for ratings
390 * is not restricted by ratee.
391 * TODO: Support an array
393 * @param orderby An order-by clause with fields and (optionally) ASC
395 * Example: "ratingvalue DESC"
397 * If this is null (or left off), the search for ratings
398 * has no guaranteed order
400 * @param pageinfo The type of page that has its info returned (i.e.,
401 * 'pagename', 'hits', and 'pagedata') in the rows.
404 * If this is null (or left off), the info returned
405 * is for the 'ratee' page (i.e., thing being rated).
407 * @return DB iterator with results
409 function sql_get_rating($dimension=null, $rater=null, $ratee=null,
410 $orderby=null, $pageinfo = "ratee") {
411 if (empty($dimension)) $dimension=null;
412 $result = $this->_sql_get_rating_result($dimension, $rater, $ratee, $orderby, $pageinfo);
413 return new $this->iter_class($this, $result);
416 function sql_get_users_rated($dimension=null, $orderby=null) {
417 if (empty($dimension)) $dimension=null;
418 $result = $this->_sql_get_rating_result($dimension, null, null, $orderby, "rater");
419 return new $this->iter_class($this, $result);
424 * @return result ressource, suitable to the iterator
426 function _sql_get_rating_result($dimension=null, $rater=null, $ratee=null,
427 $orderby=null, $pageinfo = "ratee") {
428 // pageinfo must be 'rater' or 'ratee'
429 if (($pageinfo != "ratee") && ($pageinfo != "rater"))
431 $dbi = &$this->_dbi->_backend;
432 //$dbh = &$this->_dbi;
433 extract($dbi->_table_names);
434 $where = "WHERE r." . $pageinfo . "page = p.id";
435 if (isset($dimension)) {
436 $where .= " AND dimension=$dimension";
439 $raterid = $dbi->_get_pageid($rater, true);
440 $where .= " AND raterpage=$raterid";
443 if(is_array($ratee)){
445 for($i = 0; $i < count($ratee); $i++){
446 $rateeid = $dbi->_get_pageid($ratee[$i], true);
447 $where .= "rateepage=$rateeid";
448 if($i != (count($ratee) - 1)){
454 $rateeid = $dbi->_get_pageid($ratee, true);
455 $where .= " AND rateepage=$rateeid";
459 if (isset($orderby)) {
460 $orderbyStr = " ORDER BY " . $orderby;
462 if (isset($rater) or isset($ratee)) $what = '*';
463 // same as _get_users_rated_result()
464 else $what = 'DISTINCT p.pagename';
466 $query = "SELECT $what"
467 . " FROM $rating_tbl r, $page_tbl p "
470 $result = $dbi->_dbh->query($query);
477 * @param rater The page id of the rater, i.e. page doing the rating.
478 * This is a Wiki page id, often of a user page.
479 * @param ratee The page id of the ratee, i.e. page being rated.
480 * @param dimension The rating dimension id.
484 * @return true upon success
486 function sql_delete_rating($rater, $ratee, $dimension) {
487 //$dbh = &$this->_dbi;
488 $dbi = &$this->_dbi->_backend;
489 extract($dbi->_table_names);
492 $raterid = $dbi->_get_pageid($rater, true);
493 $rateeid = $dbi->_get_pageid($ratee, true);
494 $where = "WHERE raterpage=$raterid and rateepage=$rateeid";
495 if (isset($dimension)) {
496 $where .= " AND dimension=$dimension";
498 $dbi->_dbh->query("DELETE FROM $rating_tbl $where");
506 * @param rater The page id of the rater, i.e. page doing the rating.
507 * This is a Wiki page id, often of a user page.
508 * @param ratee The page id of the ratee, i.e. page being rated.
509 * @param rateeversion The version of the ratee page.
510 * @param dimension The rating dimension id.
511 * @param rating The rating value (a float).
515 * @return true upon success
517 // ($this->userid, $this->pagename, $this->dimension, $rating);
518 function sql_rate($rater, $ratee, $rateeversion, $dimension, $rating) {
519 $dbi = &$this->_dbi->_backend;
520 extract($dbi->_table_names);
521 if (empty($rating_tbl))
522 $rating_tbl = $this->_dbi->getParam('prefix') . 'rating';
525 $raterid = $dbi->_get_pageid($rater, true);
526 $rateeid = $dbi->_get_pageid($ratee, true);
529 //we changed back to delete and insert because update didn't work if it was a new rating
531 $dbi->_dbh->query("DELETE from $rating_tbl WHERE dimension=$dimension AND raterpage=$raterid AND rateepage=$rateeid");
532 $where = "WHERE raterpage='$raterid' AND rateepage='$rateeid'";
534 $insert = "INSERT INTO $rating_tbl (dimension, raterpage, rateepage, ratingvalue, rateeversion) VALUES ('$dimension', $raterid, $rateeid, '$rating', '$rateeversion')";
535 $dbi->_dbh->query($insert);
541 function metadata_get_rating($userid, $pagename, $dimension) {
542 if (!$pagename) return false;
543 $page = $this->_dbi->getPage($pagename);
544 $data = $page->get('rating');
545 if (!empty($data[$dimension][$userid]))
546 return (float)$data[$dimension][$userid];
551 function metadata_set_rating($userid, $pagename, $dimension, $rating = -1) {
552 if (!$pagename) return false;
553 $page = $this->_dbi->getPage($pagename);
554 $data = $page->get('rating');
556 unset($data[$dimension][$userid]);
558 if (empty($data[$dimension][$userid]))
559 $data[$dimension] = array($userid => (float)$rating);
561 $data[$dimension][$userid] = $rating;
563 $page->set('rating',$data);
569 class RatingsDB_backend_PearDB
570 extends WikiDB_backend_PearDB {
571 function get_rating($dimension=null, $rater=null, $ratee=null,
572 $orderby=null, $pageinfo = "ratee") {
573 $result = $this->_get_rating_result(
574 $dimension, $rater, $ratee, $orderby, $pageinfo);
575 return new WikiDB_backend_PearDB_generic_iter($this, $result);
578 function get_users_rated($dimension=null, $orderby=null) {
579 $result = $this->_get_users_rated_result(
580 $dimension, $orderby);
581 return new WikiDB_backend_PearDB_generic_iter($this, $result);
584 function get_rating_page($dimension=null, $rater=null, $ratee=null,
585 $orderby=null, $pageinfo = "ratee") {
586 $result = $this->_get_rating_result(
587 $dimension, $rater, $ratee, $orderby, $pageinfo);
588 return new WikiDB_backend_PearDB_iter($this, $result);
591 function _get_rating_result($dimension=null, $rater=null, $ratee=null,
592 $orderby=null, $pageinfo = "ratee") {
593 // pageinfo must be 'rater' or 'ratee'
594 if (($pageinfo != "ratee") && ($pageinfo != "rater"))
598 extract($this->_table_names);
600 $where = "WHERE r." . $pageinfo . "page = p.id";
601 if (isset($dimension)) {
602 $where .= " AND dimension=$dimension";
605 $raterid = $this->_get_pageid($rater, true);
606 $where .= " AND raterpage=$raterid";
609 if(is_array($ratee)){
611 for($i = 0; $i < count($ratee); $i++){
612 $rateeid = $this->_get_pageid($ratee[$i], true);
613 $where .= "rateepage=$rateeid";
614 if($i != (count($ratee) - 1)){
620 $rateeid = $this->_get_pageid($ratee, true);
621 $where .= " AND rateepage=$rateeid";
626 if (isset($orderby)) {
627 $orderbyStr = " ORDER BY " . $orderby;
631 . " FROM $rating_tbl r, $page_tbl p "
635 $result = $dbh->query($query);
640 function _get_users_rated_result($dimension=null, $orderby=null) {
642 extract($this->_table_names);
644 $where = "WHERE p.id=r.raterpage";
645 if (isset($dimension)) {
646 $where .= " AND dimension=$dimension";
649 if (isset($orderby)) {
650 $orderbyStr = " ORDER BY " . $orderby;
653 $query = "SELECT DISTINCT p.pagename"
654 . " FROM $rating_tbl r, $page_tbl p "
658 $result = $dbh->query($query);
662 function delete_rating($rater, $ratee, $dimension) {
664 extract($this->_table_names);
667 $raterid = $this->_get_pageid($rater, true);
668 $rateeid = $this->_get_pageid($ratee, true);
670 $dbh->query("DELETE FROM $rating_tbl WHERE raterpage=$raterid and rateepage=$rateeid and dimension=$dimension");
675 function rate($rater, $ratee, $rateeversion, $dimension, $rating, $isPrivate = 'no') {
677 extract($this->_table_names);
680 $raterid = $this->_get_pageid($rater, true);
681 $rateeid = $this->_get_pageid($ratee, true);
683 $dbh->query("DELETE FROM $rating_tbl WHERE raterpage=$raterid and rateepage=$rateeid and dimension=$dimension and isPrivate='$isPrivate'");
684 // NOTE: Leave tstamp off the insert, and MySQL automatically updates it
685 $dbh->query("INSERT INTO $rating_tbl (dimension, raterpage, rateepage, ratingvalue, rateeversion, isPrivate) VALUES ($dimension, $raterid, $rateeid, $rating, $rateeversion, '$isPrivate')");
692 // $Log: not supported by cvs2svn $
693 // Revision 1.7 2004/07/08 19:14:57 rurban
694 // more metadata fixes
696 // Revision 1.6 2004/07/08 19:04:45 rurban
697 // more unittest fixes (file backend, metadata RatingsDb)
699 // Revision 1.5 2004/07/08 13:50:33 rurban
700 // various unit test fixes: print error backtrace on _DEBUG_TRACE; allusers fix; new PHPWIKI_NOMAIN constant for omitting the mainloop
702 // Revision 1.4 2004/07/07 19:47:36 dfrankow
703 // Fixes to get PreferencesApp to work-- thanks syilek
705 // Revision 1.3 2004/06/30 20:05:36 dfrankow
706 // + Add getTheRatingsDb() singleton.
707 // + Remove defaulting of dimension, userid, pagename in getRating--
709 // + Fix typo in get_rating.
710 // + Fix _sql_get_rating_result
711 // + Fix sql_rate(). It's now not transactionally safe yet, but at least it
714 // Revision 1.2 2004/06/19 10:22:41 rurban
715 // outcomment the pear specific methods to let all pages load
717 // Revision 1.1 2004/06/18 14:42:17 rurban
718 // added wikilens libs (not yet merged good enough, some work for DanFr)
725 // c-hanging-comment-ender-p: nil
726 // indent-tabs-mode: nil