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 {
50 function RatingsDb() {
52 $this->_dbi = &$request->_dbi;
53 $this->_backend = &$this->_dbi->_backend;
54 $this->dimension = null;
55 if (RATING_STORAGE == 'SQL') {
56 if (isa($this->_backend, 'WikiDB_backend_PearDB')) {
57 $this->_sqlbackend = &$this->_backend;
58 $this->dbtype = "PearDB";
59 } elseif (isa($this->_backend, 'WikiDB_backend_ADODOB')) {
60 $this->_sqlbackend = &$this->_backend;
61 $this->dbtype = "ADODB";
63 include_once 'lib/WikiDB/backend/ADODB.php';
64 // It is not possible to decouple a ref from the source again. (4.3.11)
65 // It replaced the main request backend. So we don't initialize _sqlbackend before.
66 //$this->_sqlbackend = clone($this->_backend);
67 $this->_sqlbackend = new WikiDB_backend_ADODB($GLOBALS['DBParams']);
68 $this->dbtype = "ADODB";
70 $this->iter_class = "WikiDB_backend_".$this->dbtype."_generic_iter";
72 extract($this->_sqlbackend->_table_names);
73 if (empty($rating_tbl)) {
74 $rating_tbl = (!empty($GLOBALS['DBParams']['prefix'])
75 ? $GLOBALS['DBParams']['prefix'] : '') . 'rating';
76 $this->_sqlbackend->_table_names['rating_tbl'] = $rating_tbl;
79 $this->iter_class = "WikiDB_Array_PageIterator";
83 // this is a singleton. It ensures there is only 1 ratingsDB.
84 function & getTheRatingsDb(){
85 static $_theRatingsDb;
87 if (!isset($_theRatingsDb)){
88 $_theRatingsDb = new RatingsDb();
90 //echo "rating db is $_theRatingsDb";
91 return $_theRatingsDb;
95 /// *************************************************************************************
97 // from Reini Urban's RateIt plugin
98 function addRating($rating, $userid, $pagename, $dimension) {
99 if (RATING_STORAGE == 'SQL') {
100 $page = $this->_dbi->getPage($pagename);
101 $current = $page->getCurrentRevision();
102 $rateeversion = $current->getVersion();
103 $this->sql_rate($userid, $pagename, $rateeversion, $dimension, $rating);
105 $this->metadata_set_rating($userid, $pagename, $dimension, $rating);
109 function deleteRating($userid=null, $pagename=null, $dimension=null) {
110 if (is_null($dimension)) $dimension = $this->dimension;
111 if (is_null($userid)) $userid = $this->userid;
112 if (is_null($pagename)) $pagename = $this->pagename;
113 if (RATING_STORAGE == 'SQL') {
114 $this->sql_delete_rating($userid, $pagename, $dimension);
116 $this->metadata_set_rating($userid, $pagename, $dimension, -1);
120 function getRating($userid=null, $pagename=null, $dimension=null) {
121 if (RATING_STORAGE == 'SQL') {
122 $ratings_iter = $this->sql_get_rating($dimension, $userid, $pagename);
123 if ($rating = $ratings_iter->next() and isset($rating['ratingvalue'])) {
124 return $rating['ratingvalue'];
128 return $this->metadata_get_rating($userid, $pagename, $dimension);
132 function getUsersRated($dimension=null, $orderby = null) {
133 if (is_null($dimension)) $dimension = $this->dimension;
134 //if (is_null($userid)) $userid = $this->userid;
135 //if (is_null($pagename)) $pagename = $this->pagename;
136 if (RATING_STORAGE == 'SQL') {
137 $ratings_iter = $this->sql_get_users_rated($dimension, $orderby);
138 if ($rating = $ratings_iter->next() and isset($rating['ratingvalue'])) {
139 return $rating['ratingvalue'];
143 return $this->metadata_get_users_rated($dimension, $orderby);
150 * @param dimension The rating dimension id.
153 * If this is null (or left off), the search for ratings
154 * is not restricted by dimension.
156 * @param rater The page id of the rater, i.e. page doing the rating.
157 * This is a Wiki page id, often of a user page.
160 * If this is null (or left off), the search for ratings
161 * is not restricted by rater.
162 * TODO: Support an array
164 * @param ratee The page id of the ratee, i.e. page being rated.
165 * Example: "DudeWheresMyCar"
167 * If this is null (or left off), the search for ratings
168 * is not restricted by ratee.
170 * @param orderby An order-by clause with fields and (optionally) ASC
172 * Example: "ratingvalue DESC"
174 * If this is null (or left off), the search for ratings
175 * has no guaranteed order
177 * @param pageinfo The type of page that has its info returned (i.e.,
178 * 'pagename', 'hits', and 'pagedata') in the rows.
181 * If this is null (or left off), the info returned
182 * is for the 'ratee' page (i.e., thing being rated).
184 * @return DB iterator with results
186 function get_rating($dimension=null, $rater=null, $ratee=null,
187 $orderby = null, $pageinfo = "ratee") {
188 if (RATING_STORAGE == 'SQL') {
189 $ratings_iter = $this->sql_get_rating($dimension, $rater, $pagename);
190 if ($rating = $ratings_iter->next() and isset($rating['ratingvalue'])) {
191 return $rating['ratingvalue'];
195 return $this->metadata_get_rating($rater, $pagename, $dimension);
199 /* UR: What is this for? NOT USED!
200 Maybe the list of users (ratees) who rated on this page.
202 function get_users_rated($dimension=null, $pagename = null, $orderby = null) {
203 if (RATING_STORAGE == 'SQL') {
204 $ratings_iter = $this->sql_get_users_rated($dimension, $pagename, $orderby);
207 while ($rating = $ratings_iter->next()) {
208 $users[] = $rating['userid'];
212 return $this->metadata_get_users_rated($dimension, $pagename, $orderby);
217 * Like get_rating(), but return a WikiDB_PageIterator
220 function get_rating_page($dimension=null, $rater=null, $ratee=null,
221 $orderby = null, $pageinfo = "ratee") {
222 if (RATING_STORAGE == 'SQL') {
223 return $this->sql_get_rating($dimension, $rater, $ratee, $orderby, $pageinfo);
225 // empty dummy iterator
227 return new WikiDB_Array_PageIterator($pages);
234 * @param rater The page id of the rater, i.e. page doing the rating.
235 * This is a Wiki page id, often of a user page.
236 * @param ratee The page id of the ratee, i.e. page being rated.
237 * @param dimension The rating dimension id.
241 * @return true upon success
243 function delete_rating($rater, $ratee, $dimension) {
244 if (RATING_STORAGE == 'SQL') {
245 $this->sql_delete_rating($rater, $ratee, $dimension);
247 $this->metadata_set_rating($rater, $ratee, $dimension, -1);
254 * @param rater The page id of the rater, i.e. page doing the rating.
255 * This is a Wiki page id, often of a user page.
256 * @param ratee The page id of the ratee, i.e. page being rated.
257 * @param rateeversion The version of the ratee page.
258 * @param dimension The rating dimension id.
259 * @param rating The rating value (a float).
263 * @return true upon success
265 function rate($rater, $ratee, $rateeversion, $dimension, $rating) {
266 if (RATING_STORAGE == 'SQL') {
267 $page = $this->_dbi->getPage($pagename);
268 $current = $page->getCurrentRevision();
269 $rateeversion = $current->getVersion();
270 $this->sql_rate($userid, $pagename, $rateeversion, $dimension, $rating);
272 $this->metadata_set_rating($userid, $pagename, $dimension, $rating);
276 //function getUsersRated(){}
278 //*******************************************************************************
280 // Use wikilens/RatingsUser.php for the php methods.
283 // Currently we have to call the "suggest" CGI
284 // http://www-users.cs.umn.edu/~karypis/suggest/
285 // until we implement a simple recommendation engine.
286 // Note that "suggest" is only free for non-profit organizations.
287 // I am currently writing a binary CGI mysuggest using suggest, which loads
289 function getPrediction($userid=null, $pagename=null, $dimension=null) {
290 if (is_null($dimension)) $dimension = $this->dimension;
291 if (is_null($userid)) $userid = $this->userid;
292 if (is_null($pagename)) $pagename = $this->pagename;
294 if (RATING_STORAGE == 'SQL') {
295 $dbh = &$this->_sqlbackend;
296 if (isset($pagename))
297 $page = $dbh->_get_pageid($pagename);
301 $user = $dbh->_get_pageid($userid);
305 if (defined('RATING_EXTERNAL') and RATING_EXTERNAL) {
306 // how call mysuggest.exe? as CGI or natively
307 //$rating = HTML::Raw("<!--#include virtual=".RATING_ENGINE." -->");
308 $args = "-u$user -p$page -malpha"; // --top 10
309 if (isset($dimension))
310 $args .= " -d$dimension";
311 $rating = passthru(RATING_EXTERNAL . " $args");
313 $rating = $this->php_prediction($userid, $pagename, $dimension);
319 * Slow item-based recommendation engine, similar to suggest RType=2.
320 * Only the SUGGEST_EstimateAlpha part
321 * Take wikilens/RatingsUser.php for the php methods.
323 function php_prediction($userid=null, $pagename=null, $dimension=null) {
324 if (is_null($dimension)) $dimension = $this->dimension;
325 if (is_null($userid)) $userid = $this->userid;
326 if (is_null($pagename)) $pagename = $this->pagename;
327 if (empty($this->buddies)) {
328 require_once 'lib/wikilens/RatingsUser.php';
329 require_once 'lib/wikilens/Buddy.php';
330 $user = RatingsUserFactory::getUser($userid);
331 $this->buddies = getBuddies($user, $GLOBALS['request']->_dbi);
333 return $user->knn_uu_predict($pagename, $this->buddies, $dimension);
336 function getNumUsers($pagename=null, $dimension=null) {
337 if (is_null($dimension)) $dimension = $this->dimension;
338 if (is_null($pagename)) $pagename = $this->pagename;
339 if (RATING_STORAGE == 'SQL') {
340 $ratings_iter = $this->sql_get_rating($dimension, null, $pagename,
342 return $ratings_iter->count();
344 if (!$pagename) return 0;
345 $page = $this->_dbi->getPage($pagename);
346 $data = $page->get('rating');
347 if (!empty($data[$dimension]))
348 return count($data[$dimension]);
354 function getAvg($pagename=null, $dimension=null) {
355 if (is_null($dimension)) $dimension = $this->dimension;
356 if (is_null($pagename)) $pagename = $this->pagename;
357 if (RATING_STORAGE == 'SQL') {
358 $dbi = &$this->_sqlbackend;
359 if (isset($pagename) || isset($dimension)) {
362 if (isset($pagename)) {
363 $raterid = $this->_sqlbackend->_get_pageid($pagename, true);
364 $where .= " raterpage=$raterid";
366 if (isset($dimension)) {
367 if (isset($pagename)) $where .= " AND";
368 $where .= " dimension=$dimension";
370 //$dbh = &$this->_dbi;
371 extract($dbi->_table_names);
372 $query = "SELECT AVG(ratingvalue) as avg"
373 . " FROM $rating_tbl r, $page_tbl p "
375 . " GROUP BY raterpage";
376 $result = $dbi->_dbh->query($query);
377 $iter = new $this->iter_class($this, $result);
378 $row = $iter->next();
381 if (!$pagename) return 0;
382 $page = $this->_dbi->getPage($pagename);
383 $data = $page->get('rating');
384 if (!empty($data[$dimension]))
385 // hash of userid => rating
386 return array_sum(array_values($data[$dimension])) / count($data[$dimension]);
391 //*******************************************************************************
396 * @param dimension The rating dimension id.
399 * If this is null (or left off), the search for ratings
400 * is not restricted by dimension.
402 * @param rater The page id of the rater, i.e. page doing the rating.
403 * This is a Wiki page id, often of a user page.
406 * If this is null (or left off), the search for ratings
407 * is not restricted by rater.
408 * TODO: Support an array
410 * @param ratee The page id of the ratee, i.e. page being rated.
411 * Example: "DudeWheresMyCar"
413 * If this is null (or left off), the search for ratings
414 * is not restricted by ratee.
415 * TODO: Support an array
417 * @param orderby An order-by clause with fields and (optionally) ASC
419 * Example: "ratingvalue DESC"
421 * If this is null (or left off), the search for ratings
422 * has no guaranteed order
424 * @param pageinfo The type of page that has its info returned (i.e.,
425 * 'pagename', 'hits', and 'pagedata') in the rows.
428 * If this is null (or left off), the info returned
429 * is for the 'ratee' page (i.e., thing being rated).
431 * @return DB iterator with results
433 function sql_get_rating($dimension=null, $rater=null, $ratee=null,
434 $orderby=null, $pageinfo = "ratee") {
435 if (is_null($dimension)) $dimension = $this->dimension;
436 $result = $this->_sql_get_rating_result($dimension, $rater, $ratee, $orderby, $pageinfo);
437 return new $this->iter_class($this, $result);
440 function sql_get_users_rated($dimension=null, $pagename=null, $orderby=null) {
441 if (is_null($dimension)) $dimension = $this->dimension;
442 $result = $this->_sql_get_rating_result($dimension, null, $pagename, $orderby, "rater");
443 return new $this->iter_class($this, $result);
446 // all users who rated this page resp if null all pages.. needed?
447 function metadata_get_users_rated($dimension=null, $pagename=null, $orderby=null) {
448 if (is_null($dimension)) $dimension = $this->dimension;
452 return new WikiDB_Array_PageIterator($users);
454 $page = $this->_dbi->getPage($pagename);
455 $data = $page->get('rating');
456 if (!empty($data[$dimension])) {
457 //array($userid => (float)$rating);
458 return new WikiDB_Array_PageIterator(array_keys($data[$dimension]));
460 return new WikiDB_Array_PageIterator($users);
465 * @return result ressource, suitable to the iterator
467 function _sql_get_rating_result($dimension=null, $rater=null, $ratee=null,
468 $orderby=null, $pageinfo = "ratee") {
469 // pageinfo must be 'rater' or 'ratee'
470 if (($pageinfo != "ratee") && ($pageinfo != "rater"))
472 $dbi = &$this->_sqlbackend;
475 //$dbh = &$this->_dbi;
476 extract($dbi->_table_names);
477 $where = "WHERE r." . $pageinfo . "page = p.id";
478 if (isset($dimension)) {
479 $where .= " AND dimension=$dimension";
482 $raterid = $dbi->_get_pageid($rater, true);
483 $where .= " AND raterpage=$raterid";
486 if(is_array($ratee)){
488 for($i = 0; $i < count($ratee); $i++){
489 $rateeid = $dbi->_get_pageid($ratee[$i], true);
490 $where .= "rateepage=$rateeid";
491 if($i != (count($ratee) - 1)){
497 $rateeid = $dbi->_get_pageid($ratee, true);
498 $where .= " AND rateepage=$rateeid";
502 if (isset($orderby)) {
503 $orderbyStr = " ORDER BY " . $orderby;
505 if (isset($rater) or isset($ratee)) $what = '*';
506 // same as _get_users_rated_result()
508 $what = 'DISTINCT p.pagename';
509 if ($pageinfo == 'rater')
510 $what = 'DISTINCT p.pagename as userid';
513 $query = "SELECT $what"
514 . " FROM $rating_tbl r, $page_tbl p "
517 $result = $dbi->_dbh->query($query);
524 * @param rater The page id of the rater, i.e. page doing the rating.
525 * This is a Wiki page id, often of a user page.
526 * @param ratee The page id of the ratee, i.e. page being rated.
527 * @param dimension The rating dimension id.
531 * @return true upon success
533 function sql_delete_rating($rater, $ratee, $dimension) {
534 //$dbh = &$this->_dbi;
535 $dbi = &$this->_sqlbackend;
536 extract($dbi->_table_names);
539 $raterid = $dbi->_get_pageid($rater, true);
540 $rateeid = $dbi->_get_pageid($ratee, true);
541 $where = "WHERE raterpage=$raterid and rateepage=$rateeid";
542 if (isset($dimension)) {
543 $where .= " AND dimension=$dimension";
545 $dbi->_dbh->query("DELETE FROM $rating_tbl $where");
553 * @param rater The page id of the rater, i.e. page doing the rating.
554 * This is a Wiki page id, often of a user page.
555 * @param ratee The page id of the ratee, i.e. page being rated.
556 * @param rateeversion The version of the ratee page.
557 * @param dimension The rating dimension id.
558 * @param rating The rating value (a float).
562 * @return true upon success
564 // ($this->userid, $this->pagename, $page->version, $this->dimension, $rating);
565 function sql_rate($rater, $ratee, $rateeversion, $dimension, $rating) {
566 $dbi = &$this->_sqlbackend;
567 extract($dbi->_table_names);
568 if (empty($rating_tbl))
569 $rating_tbl = $this->_dbi->getParam('prefix') . 'rating';
572 $raterid = $dbi->_get_pageid($rater, true);
573 $rateeid = $dbi->_get_pageid($ratee, true);
576 //mysql optimize: REPLACE if raterpage and rateepage are keys
577 $dbi->_dbh->query("DELETE from $rating_tbl WHERE dimension=$dimension AND raterpage=$raterid AND rateepage=$rateeid");
578 $where = "WHERE raterpage='$raterid' AND rateepage='$rateeid'";
579 $insert = "INSERT INTO $rating_tbl (dimension, raterpage, rateepage, ratingvalue, rateeversion)"
580 ." VALUES ('$dimension', $raterid, $rateeid, '$rating', '$rateeversion')";
581 $dbi->_dbh->query($insert);
587 function metadata_get_rating($userid, $pagename, $dimension) {
588 if (!$pagename) return false;
589 $page = $this->_dbi->getPage($pagename);
590 $data = $page->get('rating');
591 if (!empty($data[$dimension][$userid]))
592 return (float)$data[$dimension][$userid];
597 function metadata_set_rating($userid, $pagename, $dimension, $rating = -1) {
598 if (!$pagename) return false;
599 $page = $this->_dbi->getPage($pagename);
600 $data = $page->get('rating');
602 unset($data[$dimension][$userid]);
604 if (empty($data[$dimension]))
605 $data[$dimension] = array($userid => (float)$rating);
607 $data[$dimension][$userid] = (float)$rating;
609 $page->set('rating',$data);
615 class RatingsDB_backend_PearDB
616 extends WikiDB_backend_PearDB {
617 function get_rating($dimension=null, $rater=null, $ratee=null,
618 $orderby=null, $pageinfo = "ratee") {
619 $result = $this->_get_rating_result(
620 $dimension, $rater, $ratee, $orderby, $pageinfo);
621 return new WikiDB_backend_PearDB_generic_iter($this, $result);
624 function get_users_rated($dimension=null, $orderby=null) {
625 $result = $this->_get_users_rated_result(
626 $dimension, $orderby);
627 return new WikiDB_backend_PearDB_generic_iter($this, $result);
630 function get_rating_page($dimension=null, $rater=null, $ratee=null,
631 $orderby=null, $pageinfo = "ratee") {
632 $result = $this->_get_rating_result(
633 $dimension, $rater, $ratee, $orderby, $pageinfo);
634 return new WikiDB_backend_PearDB_iter($this, $result);
637 function _get_rating_result($dimension=null, $rater=null, $ratee=null,
638 $orderby=null, $pageinfo = "ratee") {
639 // pageinfo must be 'rater' or 'ratee'
640 if (($pageinfo != "ratee") && ($pageinfo != "rater"))
644 extract($this->_table_names);
646 $where = "WHERE r." . $pageinfo . "page = p.id";
647 if (isset($dimension)) {
648 $where .= " AND dimension=$dimension";
651 $raterid = $this->_get_pageid($rater, true);
652 $where .= " AND raterpage=$raterid";
655 if(is_array($ratee)){
657 for($i = 0; $i < count($ratee); $i++){
658 $rateeid = $this->_get_pageid($ratee[$i], true);
659 $where .= "rateepage=$rateeid";
660 if($i != (count($ratee) - 1)){
666 $rateeid = $this->_get_pageid($ratee, true);
667 $where .= " AND rateepage=$rateeid";
672 if (isset($orderby)) {
673 $orderbyStr = " ORDER BY " . $orderby;
677 . " FROM $rating_tbl r, $page_tbl p "
681 $result = $dbh->query($query);
686 function _get_users_rated_result($dimension=null, $orderby=null) {
688 extract($this->_table_names);
690 $where = "WHERE p.id=r.raterpage";
691 if (isset($dimension)) {
692 $where .= " AND dimension=$dimension";
695 if (isset($orderby)) {
696 $orderbyStr = " ORDER BY " . $orderby;
699 $query = "SELECT DISTINCT p.pagename"
700 . " FROM $rating_tbl r, $page_tbl p "
704 $result = $dbh->query($query);
708 function delete_rating($rater, $ratee, $dimension) {
710 extract($this->_table_names);
713 $raterid = $this->_get_pageid($rater, true);
714 $rateeid = $this->_get_pageid($ratee, true);
716 $dbh->query("DELETE FROM $rating_tbl WHERE raterpage=$raterid and rateepage=$rateeid and dimension=$dimension");
721 function rate($rater, $ratee, $rateeversion, $dimension, $rating, $isPrivate = 'no') {
723 extract($this->_table_names);
726 $raterid = $this->_get_pageid($rater, true);
727 $rateeid = $this->_get_pageid($ratee, true);
729 $dbh->query("DELETE FROM $rating_tbl WHERE raterpage=$raterid and rateepage=$rateeid and dimension=$dimension and isPrivate='$isPrivate'");
730 // NOTE: Leave tstamp off the insert, and MySQL automatically updates it
731 $dbh->query("INSERT INTO $rating_tbl (dimension, raterpage, rateepage, ratingvalue, rateeversion, isPrivate) VALUES ($dimension, $raterid, $rateeid, $rating, $rateeversion, '$isPrivate')");
742 // c-hanging-comment-ender-p: nil
743 // indent-tabs-mode: nil