2 rcs_id('$Id: RatingsDb.php,v 1.6 2004-07-08 19:04:45 rurban 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 //TODO: split into SQL and metadata backends
34 class RatingsDb extends WikiDB {
36 function RatingsDb() {
38 $this->_dbi = &$request->_dbi;
39 $this->_backend = &$this->_dbi->_backend;
40 $this->dimension = null;
41 if (RATING_STORAGE == 'SQL') {
42 if (isa($this->_backend, 'WikiDB_backend_PearDB'))
43 $this->dbtype = "PearDB";
45 $this->dbtype = "ADODB";
46 $this->iter_class = "WikiDB_backend_".$this->dbtype."_generic_iter";
48 extract($this->_backend->_table_names);
49 if (empty($rating_tbl)) {
50 $rating_tbl = (!empty($GLOBALS['DBParams']['prefix'])
51 ? $GLOBALS['DBParams']['prefix'] : '') . 'rating';
52 $request->_dbi->_backend->_table_names['rating_tbl'] = $rating_tbl;
55 $this->iter_class = "WikiDB_Array_PageIterator";
59 // this is a singleton. It ensures there is only 1 ratingsDB.
60 function &getTheRatingsDb(){
61 static $_theRatingsDb;
63 if (!isset($_theRatingsDb)){
64 $_theRatingsDb = new RatingsDb();
66 //echo "rating db is $_theRatingsDb";
67 return $_theRatingsDb;
71 /// *************************************************************************************
73 // from Reini Urban's RateIt plugin
74 function addRating($rating, $userid, $pagename, $dimension) {
75 if (RATING_STORAGE == 'SQL') {
76 $page = $this->_dbi->getPage($pagename);
77 $current = $page->getCurrentRevision();
78 $rateeversion = $current->getVersion();
79 $this->sql_rate($userid, $pagename, $rateeversion, $dimension, $rating);
81 $this->metadata_set_rating($userid, $pagename, $dimension, $rating);
85 function deleteRating($userid=null, $pagename=null, $dimension=null) {
86 if (is_null($dimension)) $dimension = $this->dimension;
87 if (is_null($userid)) $userid = $this->userid;
88 if (is_null($pagename)) $pagename = $this->pagename;
89 if (RATING_STORAGE == 'SQL') {
90 $this->sql_delete_rating($userid, $pagename, $dimension);
92 $this->metadata_set_rating($userid, $pagename, $dimension, -1);
96 function getRating($userid=null, $pagename=null, $dimension=null) {
97 if (RATING_STORAGE == 'SQL') {
98 $ratings_iter = $this->sql_get_rating($dimension, $userid, $pagename);
99 if ($rating = $ratings_iter->next() and isset($rating['ratingvalue'])) {
100 return $rating['ratingvalue'];
104 return $this->metadata_get_rating($userid, $pagename, $dimension);
108 function getUsersRated($dimension=null, $orderby = null) {
109 if (is_null($dimension)) $dimension = $this->dimension;
110 if (is_null($userid)) $userid = $this->userid;
111 if (is_null($pagename)) $pagename = $this->pagename;
112 if (RATING_STORAGE == 'SQL') {
113 $ratings_iter = $this->sql_get_users_rated($dimension, $orderby);
114 if ($rating = $ratings_iter->next() and isset($rating['ratingvalue'])) {
115 return $rating['ratingvalue'];
119 return $this->metadata_get_users_rated($dimension, $orderby);
127 * @param dimension The rating dimension id.
130 * If this is null (or left off), the search for ratings
131 * is not restricted by dimension.
133 * @param rater The page id of the rater, i.e. page doing the rating.
134 * This is a Wiki page id, often of a user page.
137 * If this is null (or left off), the search for ratings
138 * is not restricted by rater.
139 * TODO: Support an array
141 * @param ratee The page id of the ratee, i.e. page being rated.
142 * Example: "DudeWheresMyCar"
144 * If this is null (or left off), the search for ratings
145 * is not restricted by ratee.
147 * @param orderby An order-by clause with fields and (optionally) ASC
149 * Example: "ratingvalue DESC"
151 * If this is null (or left off), the search for ratings
152 * has no guaranteed order
154 * @param pageinfo The type of page that has its info returned (i.e.,
155 * 'pagename', 'hits', and 'pagedata') in the rows.
158 * If this is null (or left off), the info returned
159 * is for the 'ratee' page (i.e., thing being rated).
161 * @return DB iterator with results
163 function get_rating($dimension=null, $rater=null, $ratee=null,
164 $orderby = null, $pageinfo = "ratee") {
165 if (RATING_STORAGE == 'SQL') {
166 $ratings_iter = $this->sql_get_rating($dimension, $rater, $pagename);
167 if ($rating = $ratings_iter->next() and isset($rating['ratingvalue'])) {
168 return $rating['ratingvalue'];
172 return $this->metadata_get_rating($rater, $pagename, $dimension);
175 return $this->_backend->get_rating($dimension, $rater, $ratee,
176 $orderby, $pageinfo);
180 function get_users_rated($dimension=null, $orderby = null) {
181 if (RATING_STORAGE == 'SQL') {
182 $ratings_iter = $this->sql_get_users_rated($dimension, $orderby);
183 if ($rating = $ratings_iter->next() and isset($rating['ratingvalue'])) {
184 return $rating['ratingvalue'];
188 return $this->metadata_get_users_rated($dimension, $orderby);
191 return $this->_backend->get_users_rated($dimension, $orderby);
196 * Like get_rating(), but return a WikiDB_PageIterator
199 function get_rating_page($dimension=null, $rater=null, $ratee=null,
200 $orderby = null, $pageinfo = "ratee") {
201 if (RATING_STORAGE == 'SQL') {
202 return $this->sql_get_rating($dimension, $rater, $ratee, $orderby, $pageinfo);
204 // empty dummy iterator
206 return new WikiDB_Array_PageIterator($pages);
213 * @param rater The page id of the rater, i.e. page doing the rating.
214 * This is a Wiki page id, often of a user page.
215 * @param ratee The page id of the ratee, i.e. page being rated.
216 * @param dimension The rating dimension id.
220 * @return true upon success
222 function delete_rating($rater, $ratee, $dimension) {
223 if (RATING_STORAGE == 'SQL') {
224 $this->sql_delete_rating($userid, $pagename, $dimension);
226 $this->metadata_set_rating($userid, $pagename, $dimension, -1);
229 return $this->_backend->delete_rating($rater, $ratee, $dimension);
236 * @param rater The page id of the rater, i.e. page doing the rating.
237 * This is a Wiki page id, often of a user page.
238 * @param ratee The page id of the ratee, i.e. page being rated.
239 * @param rateeversion The version of the ratee page.
240 * @param dimension The rating dimension id.
241 * @param rating The rating value (a float).
245 * @return true upon success
247 function rate($rater, $ratee, $rateeversion, $dimension, $rating) {
248 if (RATING_STORAGE == 'SQL') {
249 $page = $this->_dbi->getPage($pagename);
250 $current = $page->getCurrentRevision();
251 $rateeversion = $current->getVersion();
252 $this->sql_rate($userid, $pagename, $rateeversion, $dimension, $rating);
254 $this->metadata_set_rating($userid, $pagename, $dimension, $rating);
257 return $this->_backend->rate($rater, $ratee, $rateeversion, $dimension, $rating);
261 //function getUsersRated(){}
263 //*******************************************************************************
265 // Use wikilens/RatingsUser.php for the php methods.
268 // Currently we have to call the "suggest" CGI
269 // http://www-users.cs.umn.edu/~karypis/suggest/
270 // until we implement a simple recommendation engine.
271 // Note that "suggest" is only free for non-profit organizations.
272 // I am currently writing a binary CGI using suggest, which loads
274 function getPrediction($userid=null, $pagename=null, $dimension=null) {
275 if (is_null($dimension)) $dimension = $this->dimension;
276 if (is_null($userid)) $userid = $this->userid;
277 if (is_null($pagename)) $pagename = $this->pagename;
279 if (RATING_STORAGE == 'SQL') {
280 $dbi = &$this->_dbi->_backend;
281 if (isset($pagename))
282 $page = $dbi->_get_pageid($pagename);
286 $user = $dbi->_get_pageid($userid);
290 if (defined('RATING_EXTERNAL') and RATING_EXTERNAL) {
291 // how call suggest.exe? as CGI or natively
292 //$rating = HTML::Raw("<!--#include virtual=".RATING_ENGINE." -->");
293 $args = "-u$user -p$page -malpha"; // --top 10
294 if (isset($dimension))
295 $args .= " -d$dimension";
296 $rating = passthru(RATING_EXTERNAL . " $args");
298 $rating = $this->php_prediction($userid, $pagename, $dimension);
304 * TODO: slow item-based recommendation engine, similar to suggest RType=2.
305 * Only the SUGGEST_EstimateAlpha part
306 * Take wikilens/RatingsUser.php for the php methods.
308 function php_prediction($userid=null, $pagename=null, $dimension=null) {
309 if (is_null($dimension)) $dimension = $this->dimension;
310 if (is_null($userid)) $userid = $this->userid;
311 if (is_null($pagename)) $pagename = $this->pagename;
312 if (RATING_STORAGE == 'SQL') {
320 function getNumUsers($pagename=null, $dimension=null) {
321 if (is_null($dimension)) $dimension = $this->dimension;
322 if (is_null($pagename)) $pagename = $this->pagename;
323 if (RATING_STORAGE == 'SQL') {
324 $ratings_iter = $this->sql_get_rating($dimension, null, $pagename,
326 return $ratings_iter->count();
328 if (!$pagename) return 0;
329 $page = $this->_dbi->getPage($pagename);
330 $data = $page->get('rating');
331 if (!empty($data[$dimension]))
332 return count($data[$dimension]);
337 // TODO: metadata method
338 function getAvg($pagename=null, $dimension=null) {
339 if (is_null($dimension)) $dimension = $this->dimension;
340 if (is_null($pagename)) $pagename = $this->pagename;
341 if (RATING_STORAGE == 'SQL') {
342 $dbi = &$this->_dbi->_backend;
344 if (isset($pagename)) {
345 $raterid = $dbi->_get_pageid($pagename, true);
346 $where .= " AND raterpage=$raterid";
348 if (isset($dimension)) {
349 $where .= " AND dimension=$dimension";
351 //$dbh = &$this->_dbi;
352 extract($dbi->_table_names);
353 $query = "SELECT AVG(ratingvalue) as avg"
354 . " FROM $rating_tbl r, $page_tbl p "
355 . $where. " GROUP BY raterpage";
356 $result = $dbi->_dbh->query($query);
357 $iter = new $this->iter_class($this,$result);
358 $row = $iter->next();
361 if (!$pagename) return 0;
365 //*******************************************************************************
370 * @param dimension The rating dimension id.
373 * If this is null (or left off), the search for ratings
374 * is not restricted by dimension.
376 * @param rater The page id of the rater, i.e. page doing the rating.
377 * This is a Wiki page id, often of a user page.
380 * If this is null (or left off), the search for ratings
381 * is not restricted by rater.
382 * TODO: Support an array
384 * @param ratee The page id of the ratee, i.e. page being rated.
385 * Example: "DudeWheresMyCar"
387 * If this is null (or left off), the search for ratings
388 * is not restricted by ratee.
389 * TODO: Support an array
391 * @param orderby An order-by clause with fields and (optionally) ASC
393 * Example: "ratingvalue DESC"
395 * If this is null (or left off), the search for ratings
396 * has no guaranteed order
398 * @param pageinfo The type of page that has its info returned (i.e.,
399 * 'pagename', 'hits', and 'pagedata') in the rows.
402 * If this is null (or left off), the info returned
403 * is for the 'ratee' page (i.e., thing being rated).
405 * @return DB iterator with results
407 function sql_get_rating($dimension=null, $rater=null, $ratee=null,
408 $orderby=null, $pageinfo = "ratee") {
409 if (empty($dimension)) $dimension=null;
410 $result = $this->_sql_get_rating_result($dimension, $rater, $ratee, $orderby, $pageinfo);
411 return new $this->iter_class($this, $result);
414 function sql_get_users_rated($dimension=null, $orderby=null) {
415 if (empty($dimension)) $dimension=null;
416 $result = $this->_sql_get_rating_result($dimension, null, null, $orderby, "rater");
417 return new $this->iter_class($this, $result);
422 * @return result ressource, suitable to the iterator
424 function _sql_get_rating_result($dimension=null, $rater=null, $ratee=null,
425 $orderby=null, $pageinfo = "ratee") {
426 // pageinfo must be 'rater' or 'ratee'
427 if (($pageinfo != "ratee") && ($pageinfo != "rater"))
429 $dbi = &$this->_dbi->_backend;
430 //$dbh = &$this->_dbi;
431 extract($dbi->_table_names);
432 $where = "WHERE r." . $pageinfo . "page = p.id";
433 if (isset($dimension)) {
434 $where .= " AND dimension=$dimension";
437 $raterid = $dbi->_get_pageid($rater, true);
438 $where .= " AND raterpage=$raterid";
441 if(is_array($ratee)){
443 for($i = 0; $i < count($ratee); $i++){
444 $rateeid = $dbi->_get_pageid($ratee[$i], true);
445 $where .= "rateepage=$rateeid";
446 if($i != (count($ratee) - 1)){
452 $rateeid = $dbi->_get_pageid($ratee, true);
453 $where .= " AND rateepage=$rateeid";
457 if (isset($orderby)) {
458 $orderbyStr = " ORDER BY " . $orderby;
460 if (isset($rater) or isset($ratee)) $what = '*';
461 // same as _get_users_rated_result()
462 else $what = 'DISTINCT p.pagename';
464 $query = "SELECT $what"
465 . " FROM $rating_tbl r, $page_tbl p "
468 $result = $dbi->_dbh->query($query);
475 * @param rater The page id of the rater, i.e. page doing the rating.
476 * This is a Wiki page id, often of a user page.
477 * @param ratee The page id of the ratee, i.e. page being rated.
478 * @param dimension The rating dimension id.
482 * @return true upon success
484 function sql_delete_rating($rater, $ratee, $dimension) {
485 //$dbh = &$this->_dbi;
486 $dbi = &$this->_dbi->_backend;
487 extract($dbi->_table_names);
490 $raterid = $dbi->_get_pageid($rater, true);
491 $rateeid = $dbi->_get_pageid($ratee, true);
492 $where = "WHERE raterpage=$raterid and rateepage=$rateeid";
493 if (isset($dimension)) {
494 $where .= " AND dimension=$dimension";
496 $dbi->_dbh->query("DELETE FROM $rating_tbl $where");
504 * @param rater The page id of the rater, i.e. page doing the rating.
505 * This is a Wiki page id, often of a user page.
506 * @param ratee The page id of the ratee, i.e. page being rated.
507 * @param rateeversion The version of the ratee page.
508 * @param dimension The rating dimension id.
509 * @param rating The rating value (a float).
513 * @return true upon success
515 // ($this->userid, $this->pagename, $this->dimension, $rating);
516 function sql_rate($rater, $ratee, $rateeversion, $dimension, $rating) {
517 $dbi = &$this->_dbi->_backend;
518 extract($dbi->_table_names);
519 if (empty($rating_tbl))
520 $rating_tbl = $this->_dbi->getParam('prefix') . 'rating';
523 $raterid = $dbi->_get_pageid($rater, true);
524 $rateeid = $dbi->_get_pageid($ratee, true);
527 //we changed back to delete and insert because update didn't work if it was a new rating
529 $dbi->_dbh->query("DELETE from $rating_tbl WHERE dimension=$dimension AND raterpage=$raterid AND rateepage=$rateeid");
530 $where = "WHERE raterpage='$raterid' AND rateepage='$rateeid'";
532 $insert = "INSERT INTO $rating_tbl (dimension, raterpage, rateepage, ratingvalue, rateeversion) VALUES ('$dimension', $raterid, $rateeid, '$rating', '$rateeversion')";
533 $dbi->_dbh->query($insert);
539 function metadata_get_rating($userid, $pagename, $dimension) {
542 $data = $page->get('rating');
543 if (!empty($data[$dimension][$userid]))
544 return (float)$data[$dimension][$userid];
549 function metadata_set_rating($userid, $pagename, $dimension, $rating = -1) {
550 $page = $this->_dbi->getPage($pagename);
551 $data = $page->get('rating');
553 unset($data[$dimension][$userid]);
555 if (empty($data[$dimension][$userid]))
556 $data[$dimension] = array($userid => (float)$rating);
558 $data[$dimension][$userid] = $rating;
560 $page->set('rating',$data);
566 class RatingsDB_backend_PearDB
567 extends WikiDB_backend_PearDB {
568 function get_rating($dimension=null, $rater=null, $ratee=null,
569 $orderby=null, $pageinfo = "ratee") {
570 $result = $this->_get_rating_result(
571 $dimension, $rater, $ratee, $orderby, $pageinfo);
572 return new WikiDB_backend_PearDB_generic_iter($this, $result);
575 function get_users_rated($dimension=null, $orderby=null) {
576 $result = $this->_get_users_rated_result(
577 $dimension, $orderby);
578 return new WikiDB_backend_PearDB_generic_iter($this, $result);
581 function get_rating_page($dimension=null, $rater=null, $ratee=null,
582 $orderby=null, $pageinfo = "ratee") {
583 $result = $this->_get_rating_result(
584 $dimension, $rater, $ratee, $orderby, $pageinfo);
585 return new WikiDB_backend_PearDB_iter($this, $result);
588 function _get_rating_result($dimension=null, $rater=null, $ratee=null,
589 $orderby=null, $pageinfo = "ratee") {
590 // pageinfo must be 'rater' or 'ratee'
591 if (($pageinfo != "ratee") && ($pageinfo != "rater"))
595 extract($this->_table_names);
597 $where = "WHERE r." . $pageinfo . "page = p.id";
598 if (isset($dimension)) {
599 $where .= " AND dimension=$dimension";
602 $raterid = $this->_get_pageid($rater, true);
603 $where .= " AND raterpage=$raterid";
606 if(is_array($ratee)){
608 for($i = 0; $i < count($ratee); $i++){
609 $rateeid = $this->_get_pageid($ratee[$i], true);
610 $where .= "rateepage=$rateeid";
611 if($i != (count($ratee) - 1)){
617 $rateeid = $this->_get_pageid($ratee, true);
618 $where .= " AND rateepage=$rateeid";
623 if (isset($orderby)) {
624 $orderbyStr = " ORDER BY " . $orderby;
628 . " FROM $rating_tbl r, $page_tbl p "
632 $result = $dbh->query($query);
637 function _get_users_rated_result($dimension=null, $orderby=null) {
639 extract($this->_table_names);
641 $where = "WHERE p.id=r.raterpage";
642 if (isset($dimension)) {
643 $where .= " AND dimension=$dimension";
646 if (isset($orderby)) {
647 $orderbyStr = " ORDER BY " . $orderby;
650 $query = "SELECT DISTINCT p.pagename"
651 . " FROM $rating_tbl r, $page_tbl p "
655 $result = $dbh->query($query);
659 function delete_rating($rater, $ratee, $dimension) {
661 extract($this->_table_names);
664 $raterid = $this->_get_pageid($rater, true);
665 $rateeid = $this->_get_pageid($ratee, true);
667 $dbh->query("DELETE FROM $rating_tbl WHERE raterpage=$raterid and rateepage=$rateeid and dimension=$dimension");
672 function rate($rater, $ratee, $rateeversion, $dimension, $rating, $isPrivate = 'no') {
674 extract($this->_table_names);
677 $raterid = $this->_get_pageid($rater, true);
678 $rateeid = $this->_get_pageid($ratee, true);
680 $dbh->query("DELETE FROM $rating_tbl WHERE raterpage=$raterid and rateepage=$rateeid and dimension=$dimension and isPrivate='$isPrivate'");
681 // NOTE: Leave tstamp off the insert, and MySQL automatically updates it
682 $dbh->query("INSERT INTO $rating_tbl (dimension, raterpage, rateepage, ratingvalue, rateeversion, isPrivate) VALUES ($dimension, $raterid, $rateeid, $rating, $rateeversion, '$isPrivate')");
689 // $Log: not supported by cvs2svn $
690 // Revision 1.5 2004/07/08 13:50:33 rurban
691 // various unit test fixes: print error backtrace on _DEBUG_TRACE; allusers fix; new PHPWIKI_NOMAIN constant for omitting the mainloop
693 // Revision 1.4 2004/07/07 19:47:36 dfrankow
694 // Fixes to get PreferencesApp to work-- thanks syilek
696 // Revision 1.3 2004/06/30 20:05:36 dfrankow
697 // + Add getTheRatingsDb() singleton.
698 // + Remove defaulting of dimension, userid, pagename in getRating--
700 // + Fix typo in get_rating.
701 // + Fix _sql_get_rating_result
702 // + Fix sql_rate(). It's now not transactionally safe yet, but at least it
705 // Revision 1.2 2004/06/19 10:22:41 rurban
706 // outcomment the pear specific methods to let all pages load
708 // Revision 1.1 2004/06/18 14:42:17 rurban
709 // added wikilens libs (not yet merged good enough, some work for DanFr)
716 // c-hanging-comment-ender-p: nil
717 // indent-tabs-mode: nil