5 * @author: Dan Frankowski (wikilens group manager), Reini Urban (as plugin)
8 * - fix RATING_STORAGE = WIKIPAGE (dba, file)
10 * - finish mysuggest.c (external engine with data from mysql)
11 * - add the various show modes (esp. TopN queries in PHP)
15 dimension INT(4) NOT NULL,
16 raterpage INT(11) NOT NULL,
17 rateepage INT(11) NOT NULL,
18 ratingvalue FLOAT NOT NULL,
19 rateeversion INT(11) NOT NULL,
20 isPrivate ENUM('yes','no'),
21 tstamp TIMESTAMP(14) NOT NULL,
22 PRIMARY KEY (dimension, raterpage, rateepage)
26 // For other than SQL backends. dba + adodb SQL ratings are allowed but deprecated.
27 // We will probably drop this hack.
28 if (!defined('RATING_STORAGE'))
29 // for DATABASE_TYPE=dba and forced RATING_STORAGE=SQL we must use ADODB,
30 // but this is problematic.
31 define('RATING_STORAGE', $GLOBALS['request']->_dbi->_backend->isSQL() ? 'SQL' : 'WIKIPAGE');
32 //define('RATING_STORAGE','WIKIPAGE'); // not fully supported yet
34 // leave undefined for internal, slow php engine.
35 //if (!defined('RATING_EXTERNAL'))
36 // define('RATING_EXTERNAL',PHPWIKI_DIR . 'suggest.exe');
39 if (!defined('EXPLICIT_RATINGS_DIMENSION'))
40 define('EXPLICIT_RATINGS_DIMENSION', 0);
41 if (!defined('LIST_ITEMS_DIMENSION'))
42 define('LIST_ITEMS_DIMENSION', 1);
43 if (!defined('LIST_OWNER_DIMENSION'))
44 define('LIST_OWNER_DIMENSION', 2);
45 if (!defined('LIST_TYPE_DIMENSION'))
46 define('LIST_TYPE_DIMENSION', 3);
48 //TODO: split class into SQL and metadata backends
49 class RatingsDb extends WikiDB {
51 function RatingsDb() {
53 $this->_dbi = &$request->_dbi;
54 $this->_backend = &$this->_dbi->_backend;
55 $this->dimension = null;
56 if (RATING_STORAGE == 'SQL') {
57 if (isa($this->_backend, 'WikiDB_backend_PearDB')) {
58 $this->_sqlbackend = &$this->_backend;
59 $this->dbtype = "PearDB";
60 } elseif (isa($this->_backend, 'WikiDB_backend_ADODOB')) {
61 $this->_sqlbackend = &$this->_backend;
62 $this->dbtype = "ADODB";
64 include_once("lib/WikiDB/backend/ADODB.php");
65 // It is not possible to decouple a ref from the source again. (4.3.11)
66 // It replaced the main request backend. So we don't initialize _sqlbackend before.
67 //$this->_sqlbackend = clone($this->_backend);
68 $this->_sqlbackend = new WikiDB_backend_ADODB($GLOBALS['DBParams']);
69 $this->dbtype = "ADODB";
71 $this->iter_class = "WikiDB_backend_".$this->dbtype."_generic_iter";
73 extract($this->_sqlbackend->_table_names);
74 if (empty($rating_tbl)) {
75 $rating_tbl = (!empty($GLOBALS['DBParams']['prefix'])
76 ? $GLOBALS['DBParams']['prefix'] : '') . 'rating';
77 $this->_sqlbackend->_table_names['rating_tbl'] = $rating_tbl;
80 $this->iter_class = "WikiDB_Array_PageIterator";
84 // this is a singleton. It ensures there is only 1 ratingsDB.
85 function & getTheRatingsDb(){
86 static $_theRatingsDb;
88 if (!isset($_theRatingsDb)){
89 $_theRatingsDb = new RatingsDb();
91 //echo "rating db is $_theRatingsDb";
92 return $_theRatingsDb;
96 /// *************************************************************************************
98 // from Reini Urban's RateIt plugin
99 function addRating($rating, $userid, $pagename, $dimension) {
100 if (RATING_STORAGE == 'SQL') {
101 $page = $this->_dbi->getPage($pagename);
102 $current = $page->getCurrentRevision();
103 $rateeversion = $current->getVersion();
104 $this->sql_rate($userid, $pagename, $rateeversion, $dimension, $rating);
106 $this->metadata_set_rating($userid, $pagename, $dimension, $rating);
110 function deleteRating($userid=null, $pagename=null, $dimension=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 $this->sql_delete_rating($userid, $pagename, $dimension);
117 $this->metadata_set_rating($userid, $pagename, $dimension, -1);
121 function getRating($userid=null, $pagename=null, $dimension=null) {
122 if (RATING_STORAGE == 'SQL') {
123 $ratings_iter = $this->sql_get_rating($dimension, $userid, $pagename);
124 if ($rating = $ratings_iter->next() and isset($rating['ratingvalue'])) {
125 return $rating['ratingvalue'];
129 return $this->metadata_get_rating($userid, $pagename, $dimension);
133 function getUsersRated($dimension=null, $orderby = null) {
134 if (is_null($dimension)) $dimension = $this->dimension;
135 //if (is_null($userid)) $userid = $this->userid;
136 //if (is_null($pagename)) $pagename = $this->pagename;
137 if (RATING_STORAGE == 'SQL') {
138 $ratings_iter = $this->sql_get_users_rated($dimension, $orderby);
139 if ($rating = $ratings_iter->next() and isset($rating['ratingvalue'])) {
140 return $rating['ratingvalue'];
144 return $this->metadata_get_users_rated($dimension, $orderby);
151 * @param dimension The rating dimension id.
154 * If this is null (or left off), the search for ratings
155 * is not restricted by dimension.
157 * @param rater The page id of the rater, i.e. page doing the rating.
158 * This is a Wiki page id, often of a user page.
161 * If this is null (or left off), the search for ratings
162 * is not restricted by rater.
163 * TODO: Support an array
165 * @param ratee The page id of the ratee, i.e. page being rated.
166 * Example: "DudeWheresMyCar"
168 * If this is null (or left off), the search for ratings
169 * is not restricted by ratee.
171 * @param orderby An order-by clause with fields and (optionally) ASC
173 * Example: "ratingvalue DESC"
175 * If this is null (or left off), the search for ratings
176 * has no guaranteed order
178 * @param pageinfo The type of page that has its info returned (i.e.,
179 * 'pagename', 'hits', and 'pagedata') in the rows.
182 * If this is null (or left off), the info returned
183 * is for the 'ratee' page (i.e., thing being rated).
185 * @return DB iterator with results
187 function get_rating($dimension=null, $rater=null, $ratee=null,
188 $orderby = null, $pageinfo = "ratee") {
189 if (RATING_STORAGE == 'SQL') {
190 $ratings_iter = $this->sql_get_rating($dimension, $rater, $pagename);
191 if ($rating = $ratings_iter->next() and isset($rating['ratingvalue'])) {
192 return $rating['ratingvalue'];
196 return $this->metadata_get_rating($rater, $pagename, $dimension);
200 /* UR: What is this for? NOT USED!
201 Maybe the list of users (ratees) who rated on this page.
203 function get_users_rated($dimension=null, $pagename = null, $orderby = null) {
204 if (RATING_STORAGE == 'SQL') {
205 $ratings_iter = $this->sql_get_users_rated($dimension, $pagename, $orderby);
208 while ($rating = $ratings_iter->next()) {
209 $users[] = $rating['userid'];
213 return $this->metadata_get_users_rated($dimension, $pagename, $orderby);
218 * Like get_rating(), but return a WikiDB_PageIterator
221 function get_rating_page($dimension=null, $rater=null, $ratee=null,
222 $orderby = null, $pageinfo = "ratee") {
223 if (RATING_STORAGE == 'SQL') {
224 return $this->sql_get_rating($dimension, $rater, $ratee, $orderby, $pageinfo);
226 // empty dummy iterator
228 return new WikiDB_Array_PageIterator($pages);
235 * @param rater The page id of the rater, i.e. page doing the rating.
236 * This is a Wiki page id, often of a user page.
237 * @param ratee The page id of the ratee, i.e. page being rated.
238 * @param dimension The rating dimension id.
242 * @return true upon success
244 function delete_rating($rater, $ratee, $dimension) {
245 if (RATING_STORAGE == 'SQL') {
246 $this->sql_delete_rating($rater, $ratee, $dimension);
248 $this->metadata_set_rating($rater, $ratee, $dimension, -1);
255 * @param rater The page id of the rater, i.e. page doing the rating.
256 * This is a Wiki page id, often of a user page.
257 * @param ratee The page id of the ratee, i.e. page being rated.
258 * @param rateeversion The version of the ratee page.
259 * @param dimension The rating dimension id.
260 * @param rating The rating value (a float).
264 * @return true upon success
266 function rate($rater, $ratee, $rateeversion, $dimension, $rating) {
267 if (RATING_STORAGE == 'SQL') {
268 $page = $this->_dbi->getPage($pagename);
269 $current = $page->getCurrentRevision();
270 $rateeversion = $current->getVersion();
271 $this->sql_rate($userid, $pagename, $rateeversion, $dimension, $rating);
273 $this->metadata_set_rating($userid, $pagename, $dimension, $rating);
277 //function getUsersRated(){}
279 //*******************************************************************************
281 // Use wikilens/RatingsUser.php for the php methods.
284 // Currently we have to call the "suggest" CGI
285 // http://www-users.cs.umn.edu/~karypis/suggest/
286 // until we implement a simple recommendation engine.
287 // Note that "suggest" is only free for non-profit organizations.
288 // I am currently writing a binary CGI mysuggest using suggest, which loads
290 function getPrediction($userid=null, $pagename=null, $dimension=null) {
291 if (is_null($dimension)) $dimension = $this->dimension;
292 if (is_null($userid)) $userid = $this->userid;
293 if (is_null($pagename)) $pagename = $this->pagename;
295 if (RATING_STORAGE == 'SQL') {
296 $dbh = &$this->_sqlbackend;
297 if (isset($pagename))
298 $page = $dbh->_get_pageid($pagename);
302 $user = $dbh->_get_pageid($userid);
306 if (defined('RATING_EXTERNAL') and RATING_EXTERNAL) {
307 // how call mysuggest.exe? as CGI or natively
308 //$rating = HTML::Raw("<!--#include virtual=".RATING_ENGINE." -->");
309 $args = "-u$user -p$page -malpha"; // --top 10
310 if (isset($dimension))
311 $args .= " -d$dimension";
312 $rating = passthru(RATING_EXTERNAL . " $args");
314 $rating = $this->php_prediction($userid, $pagename, $dimension);
320 * Slow item-based recommendation engine, similar to suggest RType=2.
321 * Only the SUGGEST_EstimateAlpha part
322 * Take wikilens/RatingsUser.php for the php methods.
324 function php_prediction($userid=null, $pagename=null, $dimension=null) {
325 if (is_null($dimension)) $dimension = $this->dimension;
326 if (is_null($userid)) $userid = $this->userid;
327 if (is_null($pagename)) $pagename = $this->pagename;
328 if (empty($this->buddies)) {
329 require_once("lib/wikilens/RatingsUser.php");
330 require_once("lib/wikilens/Buddy.php");
331 $user = RatingsUserFactory::getUser($userid);
332 $this->buddies = getBuddies($user, $GLOBALS['request']->_dbi);
334 return $user->knn_uu_predict($pagename, $this->buddies, $dimension);
337 function getNumUsers($pagename=null, $dimension=null) {
338 if (is_null($dimension)) $dimension = $this->dimension;
339 if (is_null($pagename)) $pagename = $this->pagename;
340 if (RATING_STORAGE == 'SQL') {
341 $ratings_iter = $this->sql_get_rating($dimension, null, $pagename,
343 return $ratings_iter->count();
345 if (!$pagename) return 0;
346 $page = $this->_dbi->getPage($pagename);
347 $data = $page->get('rating');
348 if (!empty($data[$dimension]))
349 return count($data[$dimension]);
355 function getAvg($pagename=null, $dimension=null) {
356 if (is_null($dimension)) $dimension = $this->dimension;
357 if (is_null($pagename)) $pagename = $this->pagename;
358 if (RATING_STORAGE == 'SQL') {
359 $dbi = &$this->_sqlbackend;
360 if (isset($pagename) || isset($dimension)) {
363 if (isset($pagename)) {
364 $raterid = $this->_sqlbackend->_get_pageid($pagename, true);
365 $where .= " raterpage=$raterid";
367 if (isset($dimension)) {
368 if (isset($pagename)) $where .= " AND";
369 $where .= " dimension=$dimension";
371 //$dbh = &$this->_dbi;
372 extract($dbi->_table_names);
373 $query = "SELECT AVG(ratingvalue) as avg"
374 . " FROM $rating_tbl r, $page_tbl p "
376 . " GROUP BY raterpage";
377 $result = $dbi->_dbh->query($query);
378 $iter = new $this->iter_class($this, $result);
379 $row = $iter->next();
382 if (!$pagename) return 0;
383 $page = $this->_dbi->getPage($pagename);
384 $data = $page->get('rating');
385 if (!empty($data[$dimension]))
386 // hash of userid => rating
387 return array_sum(array_values($data[$dimension])) / count($data[$dimension]);
392 //*******************************************************************************
397 * @param dimension The rating dimension id.
400 * If this is null (or left off), the search for ratings
401 * is not restricted by dimension.
403 * @param rater The page id of the rater, i.e. page doing the rating.
404 * This is a Wiki page id, often of a user page.
407 * If this is null (or left off), the search for ratings
408 * is not restricted by rater.
409 * TODO: Support an array
411 * @param ratee The page id of the ratee, i.e. page being rated.
412 * Example: "DudeWheresMyCar"
414 * If this is null (or left off), the search for ratings
415 * is not restricted by ratee.
416 * TODO: Support an array
418 * @param orderby An order-by clause with fields and (optionally) ASC
420 * Example: "ratingvalue DESC"
422 * If this is null (or left off), the search for ratings
423 * has no guaranteed order
425 * @param pageinfo The type of page that has its info returned (i.e.,
426 * 'pagename', 'hits', and 'pagedata') in the rows.
429 * If this is null (or left off), the info returned
430 * is for the 'ratee' page (i.e., thing being rated).
432 * @return DB iterator with results
434 function sql_get_rating($dimension=null, $rater=null, $ratee=null,
435 $orderby=null, $pageinfo = "ratee") {
436 if (is_null($dimension)) $dimension = $this->dimension;
437 $result = $this->_sql_get_rating_result($dimension, $rater, $ratee, $orderby, $pageinfo);
438 return new $this->iter_class($this, $result);
441 function sql_get_users_rated($dimension=null, $pagename=null, $orderby=null) {
442 if (is_null($dimension)) $dimension = $this->dimension;
443 $result = $this->_sql_get_rating_result($dimension, null, $pagename, $orderby, "rater");
444 return new $this->iter_class($this, $result);
447 // all users who rated this page resp if null all pages.. needed?
448 function metadata_get_users_rated($dimension=null, $pagename=null, $orderby=null) {
449 if (is_null($dimension)) $dimension = $this->dimension;
453 return new WikiDB_Array_PageIterator($users);
455 $page = $this->_dbi->getPage($pagename);
456 $data = $page->get('rating');
457 if (!empty($data[$dimension])) {
458 //array($userid => (float)$rating);
459 return new WikiDB_Array_PageIterator(array_keys($data[$dimension]));
461 return new WikiDB_Array_PageIterator($users);
466 * @return result ressource, suitable to the iterator
468 function _sql_get_rating_result($dimension=null, $rater=null, $ratee=null,
469 $orderby=null, $pageinfo = "ratee") {
470 // pageinfo must be 'rater' or 'ratee'
471 if (($pageinfo != "ratee") && ($pageinfo != "rater"))
473 $dbi = &$this->_sqlbackend;
476 //$dbh = &$this->_dbi;
477 extract($dbi->_table_names);
478 $where = "WHERE r." . $pageinfo . "page = p.id";
479 if (isset($dimension)) {
480 $where .= " AND dimension=$dimension";
483 $raterid = $dbi->_get_pageid($rater, true);
484 $where .= " AND raterpage=$raterid";
487 if(is_array($ratee)){
489 for($i = 0; $i < count($ratee); $i++){
490 $rateeid = $dbi->_get_pageid($ratee[$i], true);
491 $where .= "rateepage=$rateeid";
492 if($i != (count($ratee) - 1)){
498 $rateeid = $dbi->_get_pageid($ratee, true);
499 $where .= " AND rateepage=$rateeid";
503 if (isset($orderby)) {
504 $orderbyStr = " ORDER BY " . $orderby;
506 if (isset($rater) or isset($ratee)) $what = '*';
507 // same as _get_users_rated_result()
509 $what = 'DISTINCT p.pagename';
510 if ($pageinfo == 'rater')
511 $what = 'DISTINCT p.pagename as userid';
514 $query = "SELECT $what"
515 . " FROM $rating_tbl r, $page_tbl p "
518 $result = $dbi->_dbh->query($query);
525 * @param rater The page id of the rater, i.e. page doing the rating.
526 * This is a Wiki page id, often of a user page.
527 * @param ratee The page id of the ratee, i.e. page being rated.
528 * @param dimension The rating dimension id.
532 * @return true upon success
534 function sql_delete_rating($rater, $ratee, $dimension) {
535 //$dbh = &$this->_dbi;
536 $dbi = &$this->_sqlbackend;
537 extract($dbi->_table_names);
540 $raterid = $dbi->_get_pageid($rater, true);
541 $rateeid = $dbi->_get_pageid($ratee, true);
542 $where = "WHERE raterpage=$raterid and rateepage=$rateeid";
543 if (isset($dimension)) {
544 $where .= " AND dimension=$dimension";
546 $dbi->_dbh->query("DELETE FROM $rating_tbl $where");
554 * @param rater The page id of the rater, i.e. page doing the rating.
555 * This is a Wiki page id, often of a user page.
556 * @param ratee The page id of the ratee, i.e. page being rated.
557 * @param rateeversion The version of the ratee page.
558 * @param dimension The rating dimension id.
559 * @param rating The rating value (a float).
563 * @return true upon success
565 // ($this->userid, $this->pagename, $page->version, $this->dimension, $rating);
566 function sql_rate($rater, $ratee, $rateeversion, $dimension, $rating) {
567 $dbi = &$this->_sqlbackend;
568 extract($dbi->_table_names);
569 if (empty($rating_tbl))
570 $rating_tbl = $this->_dbi->getParam('prefix') . 'rating';
573 $raterid = $dbi->_get_pageid($rater, true);
574 $rateeid = $dbi->_get_pageid($ratee, true);
577 //mysql optimize: REPLACE if raterpage and rateepage are keys
578 $dbi->_dbh->query("DELETE from $rating_tbl WHERE dimension=$dimension AND raterpage=$raterid AND rateepage=$rateeid");
579 $where = "WHERE raterpage='$raterid' AND rateepage='$rateeid'";
580 $insert = "INSERT INTO $rating_tbl (dimension, raterpage, rateepage, ratingvalue, rateeversion)"
581 ." VALUES ('$dimension', $raterid, $rateeid, '$rating', '$rateeversion')";
582 $dbi->_dbh->query($insert);
588 function metadata_get_rating($userid, $pagename, $dimension) {
589 if (!$pagename) return false;
590 $page = $this->_dbi->getPage($pagename);
591 $data = $page->get('rating');
592 if (!empty($data[$dimension][$userid]))
593 return (float)$data[$dimension][$userid];
598 function metadata_set_rating($userid, $pagename, $dimension, $rating = -1) {
599 if (!$pagename) return false;
600 $page = $this->_dbi->getPage($pagename);
601 $data = $page->get('rating');
603 unset($data[$dimension][$userid]);
605 if (empty($data[$dimension]))
606 $data[$dimension] = array($userid => (float)$rating);
608 $data[$dimension][$userid] = (float)$rating;
610 $page->set('rating',$data);
616 class RatingsDB_backend_PearDB
617 extends WikiDB_backend_PearDB {
618 function get_rating($dimension=null, $rater=null, $ratee=null,
619 $orderby=null, $pageinfo = "ratee") {
620 $result = $this->_get_rating_result(
621 $dimension, $rater, $ratee, $orderby, $pageinfo);
622 return new WikiDB_backend_PearDB_generic_iter($this, $result);
625 function get_users_rated($dimension=null, $orderby=null) {
626 $result = $this->_get_users_rated_result(
627 $dimension, $orderby);
628 return new WikiDB_backend_PearDB_generic_iter($this, $result);
631 function get_rating_page($dimension=null, $rater=null, $ratee=null,
632 $orderby=null, $pageinfo = "ratee") {
633 $result = $this->_get_rating_result(
634 $dimension, $rater, $ratee, $orderby, $pageinfo);
635 return new WikiDB_backend_PearDB_iter($this, $result);
638 function _get_rating_result($dimension=null, $rater=null, $ratee=null,
639 $orderby=null, $pageinfo = "ratee") {
640 // pageinfo must be 'rater' or 'ratee'
641 if (($pageinfo != "ratee") && ($pageinfo != "rater"))
645 extract($this->_table_names);
647 $where = "WHERE r." . $pageinfo . "page = p.id";
648 if (isset($dimension)) {
649 $where .= " AND dimension=$dimension";
652 $raterid = $this->_get_pageid($rater, true);
653 $where .= " AND raterpage=$raterid";
656 if(is_array($ratee)){
658 for($i = 0; $i < count($ratee); $i++){
659 $rateeid = $this->_get_pageid($ratee[$i], true);
660 $where .= "rateepage=$rateeid";
661 if($i != (count($ratee) - 1)){
667 $rateeid = $this->_get_pageid($ratee, true);
668 $where .= " AND rateepage=$rateeid";
673 if (isset($orderby)) {
674 $orderbyStr = " ORDER BY " . $orderby;
678 . " FROM $rating_tbl r, $page_tbl p "
682 $result = $dbh->query($query);
687 function _get_users_rated_result($dimension=null, $orderby=null) {
689 extract($this->_table_names);
691 $where = "WHERE p.id=r.raterpage";
692 if (isset($dimension)) {
693 $where .= " AND dimension=$dimension";
696 if (isset($orderby)) {
697 $orderbyStr = " ORDER BY " . $orderby;
700 $query = "SELECT DISTINCT p.pagename"
701 . " FROM $rating_tbl r, $page_tbl p "
705 $result = $dbh->query($query);
709 function delete_rating($rater, $ratee, $dimension) {
711 extract($this->_table_names);
714 $raterid = $this->_get_pageid($rater, true);
715 $rateeid = $this->_get_pageid($ratee, true);
717 $dbh->query("DELETE FROM $rating_tbl WHERE raterpage=$raterid and rateepage=$rateeid and dimension=$dimension");
722 function rate($rater, $ratee, $rateeversion, $dimension, $rating, $isPrivate = 'no') {
724 extract($this->_table_names);
727 $raterid = $this->_get_pageid($rater, true);
728 $rateeid = $this->_get_pageid($ratee, true);
730 $dbh->query("DELETE FROM $rating_tbl WHERE raterpage=$raterid and rateepage=$rateeid and dimension=$dimension and isPrivate='$isPrivate'");
731 // NOTE: Leave tstamp off the insert, and MySQL automatically updates it
732 $dbh->query("INSERT INTO $rating_tbl (dimension, raterpage, rateepage, ratingvalue, rateeversion, isPrivate) VALUES ($dimension, $raterid, $rateeid, $rating, $rateeversion, '$isPrivate')");
743 // c-hanging-comment-ender-p: nil
744 // indent-tabs-mode: nil