4 * @author: Dan Frankowski (wikilens group manager), Reini Urban (as plugin)
7 * - fix RATING_STORAGE = WIKIPAGE (dba, file)
9 * - finish mysuggest.c (external engine with data from mysql)
10 * - add the various show modes (esp. TopN queries in PHP)
14 dimension INT(4) NOT NULL,
15 raterpage INT(11) NOT NULL,
16 rateepage INT(11) NOT NULL,
17 ratingvalue FLOAT NOT NULL,
18 rateeversion INT(11) NOT NULL,
19 isPrivate ENUM('yes','no'),
20 tstamp TIMESTAMP(14) NOT NULL,
21 PRIMARY KEY (dimension, raterpage, rateepage)
25 // For other than SQL backends. dba + adodb SQL ratings are allowed but deprecated.
26 // We will probably drop this hack.
27 if (!defined('RATING_STORAGE'))
28 // for DATABASE_TYPE=dba and forced RATING_STORAGE=SQL we must use ADODB,
29 // but this is problematic.
30 define('RATING_STORAGE', $GLOBALS['request']->_dbi->_backend->isSQL() ? 'SQL' : 'WIKIPAGE');
31 //define('RATING_STORAGE','WIKIPAGE'); // not fully supported yet
33 // leave undefined for internal, slow php engine.
34 //if (!defined('RATING_EXTERNAL'))
35 // define('RATING_EXTERNAL',PHPWIKI_DIR . 'suggest.exe');
38 if (!defined('EXPLICIT_RATINGS_DIMENSION'))
39 define('EXPLICIT_RATINGS_DIMENSION', 0);
40 if (!defined('LIST_ITEMS_DIMENSION'))
41 define('LIST_ITEMS_DIMENSION', 1);
42 if (!defined('LIST_OWNER_DIMENSION'))
43 define('LIST_OWNER_DIMENSION', 2);
44 if (!defined('LIST_TYPE_DIMENSION'))
45 define('LIST_TYPE_DIMENSION', 3);
47 //TODO: split class into SQL and metadata backends
48 class RatingsDb extends WikiDB
54 $this->_dbi = &$request->_dbi;
55 $this->_backend = &$this->_dbi->_backend;
56 $this->dimension = null;
57 if (RATING_STORAGE == 'SQL') {
58 if (isa($this->_backend, 'WikiDB_backend_PearDB')) {
59 $this->_sqlbackend = &$this->_backend;
60 $this->dbtype = "PearDB";
61 } elseif (isa($this->_backend, 'WikiDB_backend_ADODOB')) {
62 $this->_sqlbackend = &$this->_backend;
63 $this->dbtype = "ADODB";
65 include_once 'lib/WikiDB/backend/ADODB.php';
66 // It is not possible to decouple a ref from the source again. (4.3.11)
67 // It replaced the main request backend. So we don't initialize _sqlbackend before.
68 //$this->_sqlbackend = clone($this->_backend);
69 $this->_sqlbackend = new WikiDB_backend_ADODB($GLOBALS['DBParams']);
70 $this->dbtype = "ADODB";
72 $this->iter_class = "WikiDB_backend_" . $this->dbtype . "_generic_iter";
74 extract($this->_sqlbackend->_table_names);
75 if (empty($rating_tbl)) {
76 $rating_tbl = (!empty($GLOBALS['DBParams']['prefix'])
77 ? $GLOBALS['DBParams']['prefix'] : '') . 'rating';
78 $this->_sqlbackend->_table_names['rating_tbl'] = $rating_tbl;
81 $this->iter_class = "WikiDB_Array_PageIterator";
85 // this is a singleton. It ensures there is only 1 ratingsDB.
86 function & getTheRatingsDb()
88 static $_theRatingsDb;
90 if (!isset($_theRatingsDb)) {
91 $_theRatingsDb = new RatingsDb();
93 //echo "rating db is $_theRatingsDb";
94 return $_theRatingsDb;
98 /// *************************************************************************************
100 // from Reini Urban's RateIt plugin
101 function addRating($rating, $userid, $pagename, $dimension)
103 if (RATING_STORAGE == 'SQL') {
104 $page = $this->_dbi->getPage($pagename);
105 $current = $page->getCurrentRevision();
106 $rateeversion = $current->getVersion();
107 $this->sql_rate($userid, $pagename, $rateeversion, $dimension, $rating);
109 $this->metadata_set_rating($userid, $pagename, $dimension, $rating);
113 function deleteRating($userid = null, $pagename = null, $dimension = null)
115 if (is_null($dimension)) $dimension = $this->dimension;
116 if (is_null($userid)) $userid = $this->userid;
117 if (is_null($pagename)) $pagename = $this->pagename;
118 if (RATING_STORAGE == 'SQL') {
119 $this->sql_delete_rating($userid, $pagename, $dimension);
121 $this->metadata_set_rating($userid, $pagename, $dimension, -1);
125 function getRating($userid = null, $pagename = null, $dimension = null)
127 if (RATING_STORAGE == 'SQL') {
128 $ratings_iter = $this->sql_get_rating($dimension, $userid, $pagename);
129 if ($rating = $ratings_iter->next() and isset($rating['ratingvalue'])) {
130 return $rating['ratingvalue'];
134 return $this->metadata_get_rating($userid, $pagename, $dimension);
138 function getUsersRated($dimension = null, $orderby = null)
140 if (is_null($dimension)) $dimension = $this->dimension;
141 //if (is_null($userid)) $userid = $this->userid;
142 //if (is_null($pagename)) $pagename = $this->pagename;
143 if (RATING_STORAGE == 'SQL') {
144 $ratings_iter = $this->sql_get_users_rated($dimension, $orderby);
145 if ($rating = $ratings_iter->next() and isset($rating['ratingvalue'])) {
146 return $rating['ratingvalue'];
150 return $this->metadata_get_users_rated($dimension, $orderby);
157 * @param dimension The rating dimension id.
160 * If this is null (or left off), the search for ratings
161 * is not restricted by dimension.
163 * @param rater The page id of the rater, i.e. page doing the rating.
164 * This is a Wiki page id, often of a user page.
167 * If this is null (or left off), the search for ratings
168 * is not restricted by rater.
169 * TODO: Support an array
171 * @param ratee The page id of the ratee, i.e. page being rated.
172 * Example: "DudeWheresMyCar"
174 * If this is null (or left off), the search for ratings
175 * is not restricted by ratee.
177 * @param orderby An order-by clause with fields and (optionally) ASC
179 * Example: "ratingvalue DESC"
181 * If this is null (or left off), the search for ratings
182 * has no guaranteed order
184 * @param pageinfo The type of page that has its info returned (i.e.,
185 * 'pagename', 'hits', and 'pagedata') in the rows.
188 * If this is null (or left off), the info returned
189 * is for the 'ratee' page (i.e., thing being rated).
191 * @return DB iterator with results
193 function get_rating($dimension = null, $rater = null, $ratee = null,
194 $orderby = null, $pageinfo = "ratee")
196 if (RATING_STORAGE == 'SQL') {
197 $ratings_iter = $this->sql_get_rating($dimension, $rater, $pagename);
198 if ($rating = $ratings_iter->next() and isset($rating['ratingvalue'])) {
199 return $rating['ratingvalue'];
203 return $this->metadata_get_rating($rater, $pagename, $dimension);
207 /* UR: What is this for? NOT USED!
208 Maybe the list of users (ratees) who rated on this page.
210 function get_users_rated($dimension = null, $pagename = null, $orderby = null)
212 if (RATING_STORAGE == 'SQL') {
213 $ratings_iter = $this->sql_get_users_rated($dimension, $pagename, $orderby);
216 while ($rating = $ratings_iter->next()) {
217 $users[] = $rating['userid'];
221 return $this->metadata_get_users_rated($dimension, $pagename, $orderby);
226 * Like get_rating(), but return a WikiDB_PageIterator
229 function get_rating_page($dimension = null, $rater = null, $ratee = null,
230 $orderby = null, $pageinfo = "ratee")
232 if (RATING_STORAGE == 'SQL') {
233 return $this->sql_get_rating($dimension, $rater, $ratee, $orderby, $pageinfo);
235 // empty dummy iterator
237 return new WikiDB_Array_PageIterator($pages);
244 * @param rater The page id of the rater, i.e. page doing the rating.
245 * This is a Wiki page id, often of a user page.
246 * @param ratee The page id of the ratee, i.e. page being rated.
247 * @param dimension The rating dimension id.
251 * @return true upon success
253 function delete_rating($rater, $ratee, $dimension)
255 if (RATING_STORAGE == 'SQL') {
256 $this->sql_delete_rating($rater, $ratee, $dimension);
258 $this->metadata_set_rating($rater, $ratee, $dimension, -1);
265 * @param rater The page id of the rater, i.e. page doing the rating.
266 * This is a Wiki page id, often of a user page.
267 * @param ratee The page id of the ratee, i.e. page being rated.
268 * @param rateeversion The version of the ratee page.
269 * @param dimension The rating dimension id.
270 * @param rating The rating value (a float).
274 * @return true upon success
276 function rate($rater, $ratee, $rateeversion, $dimension, $rating)
278 if (RATING_STORAGE == 'SQL') {
279 $page = $this->_dbi->getPage($pagename);
280 $current = $page->getCurrentRevision();
281 $rateeversion = $current->getVersion();
282 $this->sql_rate($userid, $pagename, $rateeversion, $dimension, $rating);
284 $this->metadata_set_rating($userid, $pagename, $dimension, $rating);
288 //function getUsersRated(){}
290 //*******************************************************************************
292 // Use wikilens/RatingsUser.php for the php methods.
295 // Currently we have to call the "suggest" CGI
296 // http://www-users.cs.umn.edu/~karypis/suggest/
297 // until we implement a simple recommendation engine.
298 // Note that "suggest" is only free for non-profit organizations.
299 // I am currently writing a binary CGI mysuggest using suggest, which loads
301 function getPrediction($userid = null, $pagename = null, $dimension = null)
303 if (is_null($dimension)) $dimension = $this->dimension;
304 if (is_null($userid)) $userid = $this->userid;
305 if (is_null($pagename)) $pagename = $this->pagename;
307 if (RATING_STORAGE == 'SQL') {
308 $dbh = &$this->_sqlbackend;
309 if (isset($pagename))
310 $page = $dbh->_get_pageid($pagename);
314 $user = $dbh->_get_pageid($userid);
318 if (defined('RATING_EXTERNAL') and RATING_EXTERNAL) {
319 // how call mysuggest.exe? as CGI or natively
320 //$rating = HTML::Raw("<!--#include virtual=".RATING_ENGINE." -->");
321 $args = "-u$user -p$page -malpha"; // --top 10
322 if (isset($dimension))
323 $args .= " -d$dimension";
324 $rating = passthru(RATING_EXTERNAL . " $args");
326 $rating = $this->php_prediction($userid, $pagename, $dimension);
332 * Slow item-based recommendation engine, similar to suggest RType=2.
333 * Only the SUGGEST_EstimateAlpha part
334 * Take wikilens/RatingsUser.php for the php methods.
336 function php_prediction($userid = null, $pagename = null, $dimension = null)
338 if (is_null($dimension)) $dimension = $this->dimension;
339 if (is_null($userid)) $userid = $this->userid;
340 if (is_null($pagename)) $pagename = $this->pagename;
341 if (empty($this->buddies)) {
342 require_once 'lib/wikilens/RatingsUser.php';
343 require_once 'lib/wikilens/Buddy.php';
344 $user = RatingsUserFactory::getUser($userid);
345 $this->buddies = getBuddies($user, $GLOBALS['request']->_dbi);
347 return $user->knn_uu_predict($pagename, $this->buddies, $dimension);
350 function getNumUsers($pagename = null, $dimension = null)
352 if (is_null($dimension)) $dimension = $this->dimension;
353 if (is_null($pagename)) $pagename = $this->pagename;
354 if (RATING_STORAGE == 'SQL') {
355 $ratings_iter = $this->sql_get_rating($dimension, null, $pagename,
357 return $ratings_iter->count();
359 if (!$pagename) return 0;
360 $page = $this->_dbi->getPage($pagename);
361 $data = $page->get('rating');
362 if (!empty($data[$dimension]))
363 return count($data[$dimension]);
369 function getAvg($pagename = null, $dimension = null)
371 if (is_null($dimension)) $dimension = $this->dimension;
372 if (is_null($pagename)) $pagename = $this->pagename;
373 if (RATING_STORAGE == 'SQL') {
374 $dbi = &$this->_sqlbackend;
375 if (isset($pagename) || isset($dimension)) {
378 if (isset($pagename)) {
379 $raterid = $this->_sqlbackend->_get_pageid($pagename, true);
380 $where .= " raterpage=$raterid";
382 if (isset($dimension)) {
383 if (isset($pagename)) $where .= " AND";
384 $where .= " dimension=$dimension";
386 //$dbh = &$this->_dbi;
387 extract($dbi->_table_names);
388 $query = "SELECT AVG(ratingvalue) as avg"
389 . " FROM $rating_tbl r, $page_tbl p "
391 . " GROUP BY raterpage";
392 $result = $dbi->_dbh->query($query);
393 $iter = new $this->iter_class($this, $result);
394 $row = $iter->next();
397 if (!$pagename) return 0;
398 $page = $this->_dbi->getPage($pagename);
399 $data = $page->get('rating');
400 if (!empty($data[$dimension]))
401 // hash of userid => rating
402 return array_sum(array_values($data[$dimension])) / count($data[$dimension]);
408 //*******************************************************************************
413 * @param dimension The rating dimension id.
416 * If this is null (or left off), the search for ratings
417 * is not restricted by dimension.
419 * @param rater The page id of the rater, i.e. page doing the rating.
420 * This is a Wiki page id, often of a user page.
423 * If this is null (or left off), the search for ratings
424 * is not restricted by rater.
425 * TODO: Support an array
427 * @param ratee The page id of the ratee, i.e. page being rated.
428 * Example: "DudeWheresMyCar"
430 * If this is null (or left off), the search for ratings
431 * is not restricted by ratee.
432 * TODO: Support an array
434 * @param orderby An order-by clause with fields and (optionally) ASC
436 * Example: "ratingvalue DESC"
438 * If this is null (or left off), the search for ratings
439 * has no guaranteed order
441 * @param pageinfo The type of page that has its info returned (i.e.,
442 * 'pagename', 'hits', and 'pagedata') in the rows.
445 * If this is null (or left off), the info returned
446 * is for the 'ratee' page (i.e., thing being rated).
448 * @return DB iterator with results
450 function sql_get_rating($dimension = null, $rater = null, $ratee = null,
451 $orderby = null, $pageinfo = "ratee")
453 if (is_null($dimension)) $dimension = $this->dimension;
454 $result = $this->_sql_get_rating_result($dimension, $rater, $ratee, $orderby, $pageinfo);
455 return new $this->iter_class($this, $result);
458 function sql_get_users_rated($dimension = null, $pagename = null, $orderby = null)
460 if (is_null($dimension)) $dimension = $this->dimension;
461 $result = $this->_sql_get_rating_result($dimension, null, $pagename, $orderby, "rater");
462 return new $this->iter_class($this, $result);
465 // all users who rated this page resp if null all pages.. needed?
466 function metadata_get_users_rated($dimension = null, $pagename = null, $orderby = null)
468 if (is_null($dimension)) $dimension = $this->dimension;
472 return new WikiDB_Array_PageIterator($users);
474 $page = $this->_dbi->getPage($pagename);
475 $data = $page->get('rating');
476 if (!empty($data[$dimension])) {
477 //array($userid => (float)$rating);
478 return new WikiDB_Array_PageIterator(array_keys($data[$dimension]));
480 return new WikiDB_Array_PageIterator($users);
485 * @return result ressource, suitable to the iterator
487 function _sql_get_rating_result($dimension = null, $rater = null, $ratee = null,
488 $orderby = null, $pageinfo = "ratee")
490 // pageinfo must be 'rater' or 'ratee'
491 if (($pageinfo != "ratee") && ($pageinfo != "rater"))
493 $dbi = &$this->_sqlbackend;
496 //$dbh = &$this->_dbi;
497 extract($dbi->_table_names);
498 $where = "WHERE r." . $pageinfo . "page = p.id";
499 if (isset($dimension)) {
500 $where .= " AND dimension=$dimension";
503 $raterid = $dbi->_get_pageid($rater, true);
504 $where .= " AND raterpage=$raterid";
507 if (is_array($ratee)) {
509 for ($i = 0; $i < count($ratee); $i++) {
510 $rateeid = $dbi->_get_pageid($ratee[$i], true);
511 $where .= "rateepage=$rateeid";
512 if ($i != (count($ratee) - 1)) {
518 $rateeid = $dbi->_get_pageid($ratee, true);
519 $where .= " AND rateepage=$rateeid";
523 if (isset($orderby)) {
524 $orderbyStr = " ORDER BY " . $orderby;
526 if (isset($rater) or isset($ratee)) $what = '*';
527 // same as _get_users_rated_result()
529 $what = 'DISTINCT p.pagename';
530 if ($pageinfo == 'rater')
531 $what = 'DISTINCT p.pagename as userid';
534 $query = "SELECT $what"
535 . " FROM $rating_tbl r, $page_tbl p "
538 $result = $dbi->_dbh->query($query);
545 * @param rater The page id of the rater, i.e. page doing the rating.
546 * This is a Wiki page id, often of a user page.
547 * @param ratee The page id of the ratee, i.e. page being rated.
548 * @param dimension The rating dimension id.
552 * @return true upon success
554 function sql_delete_rating($rater, $ratee, $dimension)
556 //$dbh = &$this->_dbi;
557 $dbi = &$this->_sqlbackend;
558 extract($dbi->_table_names);
561 $raterid = $dbi->_get_pageid($rater, true);
562 $rateeid = $dbi->_get_pageid($ratee, true);
563 $where = "WHERE raterpage=$raterid and rateepage=$rateeid";
564 if (isset($dimension)) {
565 $where .= " AND dimension=$dimension";
567 $dbi->_dbh->query("DELETE FROM $rating_tbl $where");
575 * @param rater The page id of the rater, i.e. page doing the rating.
576 * This is a Wiki page id, often of a user page.
577 * @param ratee The page id of the ratee, i.e. page being rated.
578 * @param rateeversion The version of the ratee page.
579 * @param dimension The rating dimension id.
580 * @param rating The rating value (a float).
584 * @return true upon success
586 // ($this->userid, $this->pagename, $page->version, $this->dimension, $rating);
587 function sql_rate($rater, $ratee, $rateeversion, $dimension, $rating)
589 $dbi = &$this->_sqlbackend;
590 extract($dbi->_table_names);
591 if (empty($rating_tbl))
592 $rating_tbl = $this->_dbi->getParam('prefix') . 'rating';
595 $raterid = $dbi->_get_pageid($rater, true);
596 $rateeid = $dbi->_get_pageid($ratee, true);
599 //mysql optimize: REPLACE if raterpage and rateepage are keys
600 $dbi->_dbh->query("DELETE from $rating_tbl WHERE dimension=$dimension AND raterpage=$raterid AND rateepage=$rateeid");
601 $where = "WHERE raterpage='$raterid' AND rateepage='$rateeid'";
602 $insert = "INSERT INTO $rating_tbl (dimension, raterpage, rateepage, ratingvalue, rateeversion)"
603 . " VALUES ('$dimension', $raterid, $rateeid, '$rating', '$rateeversion')";
604 $dbi->_dbh->query($insert);
610 function metadata_get_rating($userid, $pagename, $dimension)
612 if (!$pagename) return false;
613 $page = $this->_dbi->getPage($pagename);
614 $data = $page->get('rating');
615 if (!empty($data[$dimension][$userid]))
616 return (float)$data[$dimension][$userid];
621 function metadata_set_rating($userid, $pagename, $dimension, $rating = -1)
623 if (!$pagename) return false;
624 $page = $this->_dbi->getPage($pagename);
625 $data = $page->get('rating');
627 unset($data[$dimension][$userid]);
629 if (empty($data[$dimension]))
630 $data[$dimension] = array($userid => (float)$rating);
632 $data[$dimension][$userid] = (float)$rating;
634 $page->set('rating', $data);
640 class RatingsDB_backend_PearDB
641 extends WikiDB_backend_PearDB {
642 function get_rating($dimension=null, $rater=null, $ratee=null,
643 $orderby=null, $pageinfo = "ratee") {
644 $result = $this->_get_rating_result(
645 $dimension, $rater, $ratee, $orderby, $pageinfo);
646 return new WikiDB_backend_PearDB_generic_iter($this, $result);
649 function get_users_rated($dimension=null, $orderby=null) {
650 $result = $this->_get_users_rated_result(
651 $dimension, $orderby);
652 return new WikiDB_backend_PearDB_generic_iter($this, $result);
655 function get_rating_page($dimension=null, $rater=null, $ratee=null,
656 $orderby=null, $pageinfo = "ratee") {
657 $result = $this->_get_rating_result(
658 $dimension, $rater, $ratee, $orderby, $pageinfo);
659 return new WikiDB_backend_PearDB_iter($this, $result);
662 function _get_rating_result($dimension=null, $rater=null, $ratee=null,
663 $orderby=null, $pageinfo = "ratee") {
664 // pageinfo must be 'rater' or 'ratee'
665 if (($pageinfo != "ratee") && ($pageinfo != "rater"))
669 extract($this->_table_names);
671 $where = "WHERE r." . $pageinfo . "page = p.id";
672 if (isset($dimension)) {
673 $where .= " AND dimension=$dimension";
676 $raterid = $this->_get_pageid($rater, true);
677 $where .= " AND raterpage=$raterid";
680 if(is_array($ratee)){
682 for($i = 0; $i < count($ratee); $i++){
683 $rateeid = $this->_get_pageid($ratee[$i], true);
684 $where .= "rateepage=$rateeid";
685 if($i != (count($ratee) - 1)){
691 $rateeid = $this->_get_pageid($ratee, true);
692 $where .= " AND rateepage=$rateeid";
697 if (isset($orderby)) {
698 $orderbyStr = " ORDER BY " . $orderby;
702 . " FROM $rating_tbl r, $page_tbl p "
706 $result = $dbh->query($query);
711 function _get_users_rated_result($dimension=null, $orderby=null) {
713 extract($this->_table_names);
715 $where = "WHERE p.id=r.raterpage";
716 if (isset($dimension)) {
717 $where .= " AND dimension=$dimension";
720 if (isset($orderby)) {
721 $orderbyStr = " ORDER BY " . $orderby;
724 $query = "SELECT DISTINCT p.pagename"
725 . " FROM $rating_tbl r, $page_tbl p "
729 $result = $dbh->query($query);
733 function delete_rating($rater, $ratee, $dimension) {
735 extract($this->_table_names);
738 $raterid = $this->_get_pageid($rater, true);
739 $rateeid = $this->_get_pageid($ratee, true);
741 $dbh->query("DELETE FROM $rating_tbl WHERE raterpage=$raterid and rateepage=$rateeid and dimension=$dimension");
746 function rate($rater, $ratee, $rateeversion, $dimension, $rating, $isPrivate = 'no') {
748 extract($this->_table_names);
751 $raterid = $this->_get_pageid($rater, true);
752 $rateeid = $this->_get_pageid($ratee, true);
754 $dbh->query("DELETE FROM $rating_tbl WHERE raterpage=$raterid and rateepage=$rateeid and dimension=$dimension and isPrivate='$isPrivate'");
755 // NOTE: Leave tstamp off the insert, and MySQL automatically updates it
756 $dbh->query("INSERT INTO $rating_tbl (dimension, raterpage, rateepage, ratingvalue, rateeversion, isPrivate) VALUES ($dimension, $raterid, $rateeid, $rating, $rateeversion, '$isPrivate')");
767 // c-hanging-comment-ender-p: nil
768 // indent-tabs-mode: nil