2 // $Id: RatingsDb.php 7967 2011-03-07 13:08:01Z vargenau $
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);
49 //TODO: split class into SQL and metadata backends
50 class RatingsDb extends WikiDB {
52 function RatingsDb() {
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(){
87 static $_theRatingsDb;
89 if (!isset($_theRatingsDb)){
90 $_theRatingsDb = new RatingsDb();
92 //echo "rating db is $_theRatingsDb";
93 return $_theRatingsDb;
97 /// *************************************************************************************
99 // from Reini Urban's RateIt plugin
100 function addRating($rating, $userid, $pagename, $dimension) {
101 if (RATING_STORAGE == 'SQL') {
102 $page = $this->_dbi->getPage($pagename);
103 $current = $page->getCurrentRevision();
104 $rateeversion = $current->getVersion();
105 $this->sql_rate($userid, $pagename, $rateeversion, $dimension, $rating);
107 $this->metadata_set_rating($userid, $pagename, $dimension, $rating);
111 function deleteRating($userid=null, $pagename=null, $dimension=null) {
112 if (is_null($dimension)) $dimension = $this->dimension;
113 if (is_null($userid)) $userid = $this->userid;
114 if (is_null($pagename)) $pagename = $this->pagename;
115 if (RATING_STORAGE == 'SQL') {
116 $this->sql_delete_rating($userid, $pagename, $dimension);
118 $this->metadata_set_rating($userid, $pagename, $dimension, -1);
122 function getRating($userid=null, $pagename=null, $dimension=null) {
123 if (RATING_STORAGE == 'SQL') {
124 $ratings_iter = $this->sql_get_rating($dimension, $userid, $pagename);
125 if ($rating = $ratings_iter->next() and isset($rating['ratingvalue'])) {
126 return $rating['ratingvalue'];
130 return $this->metadata_get_rating($userid, $pagename, $dimension);
134 function getUsersRated($dimension=null, $orderby = null) {
135 if (is_null($dimension)) $dimension = $this->dimension;
136 //if (is_null($userid)) $userid = $this->userid;
137 //if (is_null($pagename)) $pagename = $this->pagename;
138 if (RATING_STORAGE == 'SQL') {
139 $ratings_iter = $this->sql_get_users_rated($dimension, $orderby);
140 if ($rating = $ratings_iter->next() and isset($rating['ratingvalue'])) {
141 return $rating['ratingvalue'];
145 return $this->metadata_get_users_rated($dimension, $orderby);
152 * @param dimension The rating dimension id.
155 * If this is null (or left off), the search for ratings
156 * is not restricted by dimension.
158 * @param rater The page id of the rater, i.e. page doing the rating.
159 * This is a Wiki page id, often of a user page.
162 * If this is null (or left off), the search for ratings
163 * is not restricted by rater.
164 * TODO: Support an array
166 * @param ratee The page id of the ratee, i.e. page being rated.
167 * Example: "DudeWheresMyCar"
169 * If this is null (or left off), the search for ratings
170 * is not restricted by ratee.
172 * @param orderby An order-by clause with fields and (optionally) ASC
174 * Example: "ratingvalue DESC"
176 * If this is null (or left off), the search for ratings
177 * has no guaranteed order
179 * @param pageinfo The type of page that has its info returned (i.e.,
180 * 'pagename', 'hits', and 'pagedata') in the rows.
183 * If this is null (or left off), the info returned
184 * is for the 'ratee' page (i.e., thing being rated).
186 * @return DB iterator with results
188 function get_rating($dimension=null, $rater=null, $ratee=null,
189 $orderby = null, $pageinfo = "ratee") {
190 if (RATING_STORAGE == 'SQL') {
191 $ratings_iter = $this->sql_get_rating($dimension, $rater, $pagename);
192 if ($rating = $ratings_iter->next() and isset($rating['ratingvalue'])) {
193 return $rating['ratingvalue'];
197 return $this->metadata_get_rating($rater, $pagename, $dimension);
201 /* UR: What is this for? NOT USED!
202 Maybe the list of users (ratees) who rated on this page.
204 function get_users_rated($dimension=null, $pagename = null, $orderby = null) {
205 if (RATING_STORAGE == 'SQL') {
206 $ratings_iter = $this->sql_get_users_rated($dimension, $pagename, $orderby);
209 while ($rating = $ratings_iter->next()) {
210 $users[] = $rating['userid'];
214 return $this->metadata_get_users_rated($dimension, $pagename, $orderby);
219 * Like get_rating(), but return a WikiDB_PageIterator
222 function get_rating_page($dimension=null, $rater=null, $ratee=null,
223 $orderby = null, $pageinfo = "ratee") {
224 if (RATING_STORAGE == 'SQL') {
225 return $this->sql_get_rating($dimension, $rater, $ratee, $orderby, $pageinfo);
227 // empty dummy iterator
229 return new WikiDB_Array_PageIterator($pages);
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 dimension The rating dimension id.
243 * @return true upon success
245 function delete_rating($rater, $ratee, $dimension) {
246 if (RATING_STORAGE == 'SQL') {
247 $this->sql_delete_rating($rater, $ratee, $dimension);
249 $this->metadata_set_rating($rater, $ratee, $dimension, -1);
256 * @param rater The page id of the rater, i.e. page doing the rating.
257 * This is a Wiki page id, often of a user page.
258 * @param ratee The page id of the ratee, i.e. page being rated.
259 * @param rateeversion The version of the ratee page.
260 * @param dimension The rating dimension id.
261 * @param rating The rating value (a float).
265 * @return true upon success
267 function rate($rater, $ratee, $rateeversion, $dimension, $rating) {
268 if (RATING_STORAGE == 'SQL') {
269 $page = $this->_dbi->getPage($pagename);
270 $current = $page->getCurrentRevision();
271 $rateeversion = $current->getVersion();
272 $this->sql_rate($userid, $pagename, $rateeversion, $dimension, $rating);
274 $this->metadata_set_rating($userid, $pagename, $dimension, $rating);
278 //function getUsersRated(){}
280 //*******************************************************************************
282 // Use wikilens/RatingsUser.php for the php methods.
285 // Currently we have to call the "suggest" CGI
286 // http://www-users.cs.umn.edu/~karypis/suggest/
287 // until we implement a simple recommendation engine.
288 // Note that "suggest" is only free for non-profit organizations.
289 // I am currently writing a binary CGI mysuggest using suggest, which loads
291 function getPrediction($userid=null, $pagename=null, $dimension=null) {
292 if (is_null($dimension)) $dimension = $this->dimension;
293 if (is_null($userid)) $userid = $this->userid;
294 if (is_null($pagename)) $pagename = $this->pagename;
296 if (RATING_STORAGE == 'SQL') {
297 $dbh = &$this->_sqlbackend;
298 if (isset($pagename))
299 $page = $dbh->_get_pageid($pagename);
303 $user = $dbh->_get_pageid($userid);
307 if (defined('RATING_EXTERNAL') and RATING_EXTERNAL) {
308 // how call mysuggest.exe? as CGI or natively
309 //$rating = HTML::Raw("<!--#include virtual=".RATING_ENGINE." -->");
310 $args = "-u$user -p$page -malpha"; // --top 10
311 if (isset($dimension))
312 $args .= " -d$dimension";
313 $rating = passthru(RATING_EXTERNAL . " $args");
315 $rating = $this->php_prediction($userid, $pagename, $dimension);
321 * Slow item-based recommendation engine, similar to suggest RType=2.
322 * Only the SUGGEST_EstimateAlpha part
323 * Take wikilens/RatingsUser.php for the php methods.
325 function php_prediction($userid=null, $pagename=null, $dimension=null) {
326 if (is_null($dimension)) $dimension = $this->dimension;
327 if (is_null($userid)) $userid = $this->userid;
328 if (is_null($pagename)) $pagename = $this->pagename;
329 if (empty($this->buddies)) {
330 require_once 'lib/wikilens/RatingsUser.php';
331 require_once 'lib/wikilens/Buddy.php';
332 $user = RatingsUserFactory::getUser($userid);
333 $this->buddies = getBuddies($user, $GLOBALS['request']->_dbi);
335 return $user->knn_uu_predict($pagename, $this->buddies, $dimension);
338 function getNumUsers($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 $ratings_iter = $this->sql_get_rating($dimension, null, $pagename,
344 return $ratings_iter->count();
346 if (!$pagename) return 0;
347 $page = $this->_dbi->getPage($pagename);
348 $data = $page->get('rating');
349 if (!empty($data[$dimension]))
350 return count($data[$dimension]);
356 function getAvg($pagename=null, $dimension=null) {
357 if (is_null($dimension)) $dimension = $this->dimension;
358 if (is_null($pagename)) $pagename = $this->pagename;
359 if (RATING_STORAGE == 'SQL') {
360 $dbi = &$this->_sqlbackend;
361 if (isset($pagename) || isset($dimension)) {
364 if (isset($pagename)) {
365 $raterid = $this->_sqlbackend->_get_pageid($pagename, true);
366 $where .= " raterpage=$raterid";
368 if (isset($dimension)) {
369 if (isset($pagename)) $where .= " AND";
370 $where .= " dimension=$dimension";
372 //$dbh = &$this->_dbi;
373 extract($dbi->_table_names);
374 $query = "SELECT AVG(ratingvalue) as avg"
375 . " FROM $rating_tbl r, $page_tbl p "
377 . " GROUP BY raterpage";
378 $result = $dbi->_dbh->query($query);
379 $iter = new $this->iter_class($this, $result);
380 $row = $iter->next();
383 if (!$pagename) return 0;
384 $page = $this->_dbi->getPage($pagename);
385 $data = $page->get('rating');
386 if (!empty($data[$dimension]))
387 // hash of userid => rating
388 return array_sum(array_values($data[$dimension])) / count($data[$dimension]);
393 //*******************************************************************************
398 * @param dimension The rating dimension id.
401 * If this is null (or left off), the search for ratings
402 * is not restricted by dimension.
404 * @param rater The page id of the rater, i.e. page doing the rating.
405 * This is a Wiki page id, often of a user page.
408 * If this is null (or left off), the search for ratings
409 * is not restricted by rater.
410 * TODO: Support an array
412 * @param ratee The page id of the ratee, i.e. page being rated.
413 * Example: "DudeWheresMyCar"
415 * If this is null (or left off), the search for ratings
416 * is not restricted by ratee.
417 * TODO: Support an array
419 * @param orderby An order-by clause with fields and (optionally) ASC
421 * Example: "ratingvalue DESC"
423 * If this is null (or left off), the search for ratings
424 * has no guaranteed order
426 * @param pageinfo The type of page that has its info returned (i.e.,
427 * 'pagename', 'hits', and 'pagedata') in the rows.
430 * If this is null (or left off), the info returned
431 * is for the 'ratee' page (i.e., thing being rated).
433 * @return DB iterator with results
435 function sql_get_rating($dimension=null, $rater=null, $ratee=null,
436 $orderby=null, $pageinfo = "ratee") {
437 if (is_null($dimension)) $dimension = $this->dimension;
438 $result = $this->_sql_get_rating_result($dimension, $rater, $ratee, $orderby, $pageinfo);
439 return new $this->iter_class($this, $result);
442 function sql_get_users_rated($dimension=null, $pagename=null, $orderby=null) {
443 if (is_null($dimension)) $dimension = $this->dimension;
444 $result = $this->_sql_get_rating_result($dimension, null, $pagename, $orderby, "rater");
445 return new $this->iter_class($this, $result);
448 // all users who rated this page resp if null all pages.. needed?
449 function metadata_get_users_rated($dimension=null, $pagename=null, $orderby=null) {
450 if (is_null($dimension)) $dimension = $this->dimension;
454 return new WikiDB_Array_PageIterator($users);
456 $page = $this->_dbi->getPage($pagename);
457 $data = $page->get('rating');
458 if (!empty($data[$dimension])) {
459 //array($userid => (float)$rating);
460 return new WikiDB_Array_PageIterator(array_keys($data[$dimension]));
462 return new WikiDB_Array_PageIterator($users);
467 * @return result ressource, suitable to the iterator
469 function _sql_get_rating_result($dimension=null, $rater=null, $ratee=null,
470 $orderby=null, $pageinfo = "ratee") {
471 // pageinfo must be 'rater' or 'ratee'
472 if (($pageinfo != "ratee") && ($pageinfo != "rater"))
474 $dbi = &$this->_sqlbackend;
477 //$dbh = &$this->_dbi;
478 extract($dbi->_table_names);
479 $where = "WHERE r." . $pageinfo . "page = p.id";
480 if (isset($dimension)) {
481 $where .= " AND dimension=$dimension";
484 $raterid = $dbi->_get_pageid($rater, true);
485 $where .= " AND raterpage=$raterid";
488 if(is_array($ratee)){
490 for($i = 0; $i < count($ratee); $i++){
491 $rateeid = $dbi->_get_pageid($ratee[$i], true);
492 $where .= "rateepage=$rateeid";
493 if($i != (count($ratee) - 1)){
499 $rateeid = $dbi->_get_pageid($ratee, true);
500 $where .= " AND rateepage=$rateeid";
504 if (isset($orderby)) {
505 $orderbyStr = " ORDER BY " . $orderby;
507 if (isset($rater) or isset($ratee)) $what = '*';
508 // same as _get_users_rated_result()
510 $what = 'DISTINCT p.pagename';
511 if ($pageinfo == 'rater')
512 $what = 'DISTINCT p.pagename as userid';
515 $query = "SELECT $what"
516 . " FROM $rating_tbl r, $page_tbl p "
519 $result = $dbi->_dbh->query($query);
526 * @param rater The page id of the rater, i.e. page doing the rating.
527 * This is a Wiki page id, often of a user page.
528 * @param ratee The page id of the ratee, i.e. page being rated.
529 * @param dimension The rating dimension id.
533 * @return true upon success
535 function sql_delete_rating($rater, $ratee, $dimension) {
536 //$dbh = &$this->_dbi;
537 $dbi = &$this->_sqlbackend;
538 extract($dbi->_table_names);
541 $raterid = $dbi->_get_pageid($rater, true);
542 $rateeid = $dbi->_get_pageid($ratee, true);
543 $where = "WHERE raterpage=$raterid and rateepage=$rateeid";
544 if (isset($dimension)) {
545 $where .= " AND dimension=$dimension";
547 $dbi->_dbh->query("DELETE FROM $rating_tbl $where");
555 * @param rater The page id of the rater, i.e. page doing the rating.
556 * This is a Wiki page id, often of a user page.
557 * @param ratee The page id of the ratee, i.e. page being rated.
558 * @param rateeversion The version of the ratee page.
559 * @param dimension The rating dimension id.
560 * @param rating The rating value (a float).
564 * @return true upon success
566 // ($this->userid, $this->pagename, $page->version, $this->dimension, $rating);
567 function sql_rate($rater, $ratee, $rateeversion, $dimension, $rating) {
568 $dbi = &$this->_sqlbackend;
569 extract($dbi->_table_names);
570 if (empty($rating_tbl))
571 $rating_tbl = $this->_dbi->getParam('prefix') . 'rating';
574 $raterid = $dbi->_get_pageid($rater, true);
575 $rateeid = $dbi->_get_pageid($ratee, true);
578 //mysql optimize: REPLACE if raterpage and rateepage are keys
579 $dbi->_dbh->query("DELETE from $rating_tbl WHERE dimension=$dimension AND raterpage=$raterid AND rateepage=$rateeid");
580 $where = "WHERE raterpage='$raterid' AND rateepage='$rateeid'";
581 $insert = "INSERT INTO $rating_tbl (dimension, raterpage, rateepage, ratingvalue, rateeversion)"
582 ." VALUES ('$dimension', $raterid, $rateeid, '$rating', '$rateeversion')";
583 $dbi->_dbh->query($insert);
589 function metadata_get_rating($userid, $pagename, $dimension) {
590 if (!$pagename) return false;
591 $page = $this->_dbi->getPage($pagename);
592 $data = $page->get('rating');
593 if (!empty($data[$dimension][$userid]))
594 return (float)$data[$dimension][$userid];
599 function metadata_set_rating($userid, $pagename, $dimension, $rating = -1) {
600 if (!$pagename) return false;
601 $page = $this->_dbi->getPage($pagename);
602 $data = $page->get('rating');
604 unset($data[$dimension][$userid]);
606 if (empty($data[$dimension]))
607 $data[$dimension] = array($userid => (float)$rating);
609 $data[$dimension][$userid] = (float)$rating;
611 $page->set('rating',$data);
617 class RatingsDB_backend_PearDB
618 extends WikiDB_backend_PearDB {
619 function get_rating($dimension=null, $rater=null, $ratee=null,
620 $orderby=null, $pageinfo = "ratee") {
621 $result = $this->_get_rating_result(
622 $dimension, $rater, $ratee, $orderby, $pageinfo);
623 return new WikiDB_backend_PearDB_generic_iter($this, $result);
626 function get_users_rated($dimension=null, $orderby=null) {
627 $result = $this->_get_users_rated_result(
628 $dimension, $orderby);
629 return new WikiDB_backend_PearDB_generic_iter($this, $result);
632 function get_rating_page($dimension=null, $rater=null, $ratee=null,
633 $orderby=null, $pageinfo = "ratee") {
634 $result = $this->_get_rating_result(
635 $dimension, $rater, $ratee, $orderby, $pageinfo);
636 return new WikiDB_backend_PearDB_iter($this, $result);
639 function _get_rating_result($dimension=null, $rater=null, $ratee=null,
640 $orderby=null, $pageinfo = "ratee") {
641 // pageinfo must be 'rater' or 'ratee'
642 if (($pageinfo != "ratee") && ($pageinfo != "rater"))
646 extract($this->_table_names);
648 $where = "WHERE r." . $pageinfo . "page = p.id";
649 if (isset($dimension)) {
650 $where .= " AND dimension=$dimension";
653 $raterid = $this->_get_pageid($rater, true);
654 $where .= " AND raterpage=$raterid";
657 if(is_array($ratee)){
659 for($i = 0; $i < count($ratee); $i++){
660 $rateeid = $this->_get_pageid($ratee[$i], true);
661 $where .= "rateepage=$rateeid";
662 if($i != (count($ratee) - 1)){
668 $rateeid = $this->_get_pageid($ratee, true);
669 $where .= " AND rateepage=$rateeid";
674 if (isset($orderby)) {
675 $orderbyStr = " ORDER BY " . $orderby;
679 . " FROM $rating_tbl r, $page_tbl p "
683 $result = $dbh->query($query);
688 function _get_users_rated_result($dimension=null, $orderby=null) {
690 extract($this->_table_names);
692 $where = "WHERE p.id=r.raterpage";
693 if (isset($dimension)) {
694 $where .= " AND dimension=$dimension";
697 if (isset($orderby)) {
698 $orderbyStr = " ORDER BY " . $orderby;
701 $query = "SELECT DISTINCT p.pagename"
702 . " FROM $rating_tbl r, $page_tbl p "
706 $result = $dbh->query($query);
710 function delete_rating($rater, $ratee, $dimension) {
712 extract($this->_table_names);
715 $raterid = $this->_get_pageid($rater, true);
716 $rateeid = $this->_get_pageid($ratee, true);
718 $dbh->query("DELETE FROM $rating_tbl WHERE raterpage=$raterid and rateepage=$rateeid and dimension=$dimension");
723 function rate($rater, $ratee, $rateeversion, $dimension, $rating, $isPrivate = 'no') {
725 extract($this->_table_names);
728 $raterid = $this->_get_pageid($rater, true);
729 $rateeid = $this->_get_pageid($ratee, true);
731 $dbh->query("DELETE FROM $rating_tbl WHERE raterpage=$raterid and rateepage=$rateeid and dimension=$dimension and isPrivate='$isPrivate'");
732 // NOTE: Leave tstamp off the insert, and MySQL automatically updates it
733 $dbh->query("INSERT INTO $rating_tbl (dimension, raterpage, rateepage, ratingvalue, rateeversion, isPrivate) VALUES ($dimension, $raterid, $rateeid, $rating, $rateeversion, '$isPrivate')");
744 // c-hanging-comment-ender-p: nil
745 // indent-tabs-mode: nil