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.
249 * @return true upon success
251 public function delete_rating($rater, $ratee, $dimension)
253 if (RATING_STORAGE == 'SQL') {
254 $this->sql_delete_rating($rater, $ratee, $dimension);
256 $this->metadata_set_rating($rater, $ratee, $dimension, -1);
263 * @param rater The page id of the rater, i.e. page doing the rating.
264 * This is a Wiki page id, often of a user page.
265 * @param ratee The page id of the ratee, i.e. page being rated.
266 * @param rateeversion The version of the ratee page.
267 * @param dimension The rating dimension id.
268 * @param rating The rating value (a float).
270 * @return true upon success
272 public function rate($rater, $ratee, $rateeversion, $dimension, $rating)
274 if (RATING_STORAGE == 'SQL') {
275 $page = $this->_dbi->getPage($pagename);
276 $current = $page->getCurrentRevision();
277 $rateeversion = $current->getVersion();
278 $this->sql_rate($userid, $pagename, $rateeversion, $dimension, $rating);
280 $this->metadata_set_rating($userid, $pagename, $dimension, $rating);
284 //function getUsersRated(){}
286 //*******************************************************************************
288 // Use wikilens/RatingsUser.php for the php methods.
291 // Currently we have to call the "suggest" CGI
292 // http://www-users.cs.umn.edu/~karypis/suggest/
293 // until we implement a simple recommendation engine.
294 // Note that "suggest" is only free for non-profit organizations.
295 // I am currently writing a binary CGI mysuggest using suggest, which loads
297 function getPrediction($userid = null, $pagename = null, $dimension = null)
299 if (is_null($dimension)) $dimension = $this->dimension;
300 if (is_null($userid)) $userid = $this->userid;
301 if (is_null($pagename)) $pagename = $this->pagename;
303 if (RATING_STORAGE == 'SQL') {
304 $dbh = &$this->_sqlbackend;
305 if (isset($pagename))
306 $page = $dbh->_get_pageid($pagename);
310 $user = $dbh->_get_pageid($userid);
314 if (defined('RATING_EXTERNAL') and RATING_EXTERNAL) {
315 // how call mysuggest.exe? as CGI or natively
316 //$rating = HTML::Raw("<!--#include virtual=".RATING_ENGINE." -->");
317 $args = "-u$user -p$page -malpha"; // --top 10
318 if (isset($dimension))
319 $args .= " -d$dimension";
320 $rating = passthru(RATING_EXTERNAL . " $args");
322 $rating = $this->php_prediction($userid, $pagename, $dimension);
328 * Slow item-based recommendation engine, similar to suggest RType=2.
329 * Only the SUGGEST_EstimateAlpha part
330 * Take wikilens/RatingsUser.php for the php methods.
332 function php_prediction($userid = null, $pagename = null, $dimension = null)
334 if (is_null($dimension)) $dimension = $this->dimension;
335 if (is_null($userid)) $userid = $this->userid;
336 if (is_null($pagename)) $pagename = $this->pagename;
337 if (empty($this->buddies)) {
338 require_once 'lib/wikilens/RatingsUser.php';
339 require_once 'lib/wikilens/Buddy.php';
340 $user = RatingsUserFactory::getUser($userid);
341 $this->buddies = getBuddies($user, $GLOBALS['request']->_dbi);
343 return $user->knn_uu_predict($pagename, $this->buddies, $dimension);
346 function getNumUsers($pagename = null, $dimension = null)
348 if (is_null($dimension)) $dimension = $this->dimension;
349 if (is_null($pagename)) $pagename = $this->pagename;
350 if (RATING_STORAGE == 'SQL') {
351 $ratings_iter = $this->sql_get_rating($dimension, null, $pagename,
353 return $ratings_iter->count();
355 if (!$pagename) return 0;
356 $page = $this->_dbi->getPage($pagename);
357 $data = $page->get('rating');
358 if (!empty($data[$dimension]))
359 return count($data[$dimension]);
365 function getAvg($pagename = null, $dimension = null)
367 if (is_null($dimension)) $dimension = $this->dimension;
368 if (is_null($pagename)) $pagename = $this->pagename;
369 if (RATING_STORAGE == 'SQL') {
370 $dbi = &$this->_sqlbackend;
371 if (isset($pagename) || isset($dimension)) {
374 if (isset($pagename)) {
375 if (defined('FUSIONFORGE') and FUSIONFORGE) {
376 $rateeid = $this->_sqlbackend->_get_pageid($pagename, true);
377 $where .= " rateepage=$rateeid";
379 $raterid = $this->_sqlbackend->_get_pageid($pagename, true);
380 $where .= " raterpage=$raterid";
383 if (isset($dimension)) {
384 if (isset($pagename)) $where .= " AND";
385 $where .= " dimension=$dimension";
387 extract($dbi->_table_names);
388 if (defined('FUSIONFORGE') and FUSIONFORGE) {
389 $query = "SELECT AVG(ratingvalue) as avg FROM $rating_tbl " . $where;
391 $query = "SELECT AVG(ratingvalue) as avg FROM $rating_tbl r, $page_tbl p " . $where . " GROUP BY raterpage";
393 $result = $dbi->_dbh->query($query);
394 $iter = new $this->iter_class($this, $result);
395 $row = $iter->next();
398 if (!$pagename) return 0;
399 $page = $this->_dbi->getPage($pagename);
400 $data = $page->get('rating');
401 if (!empty($data[$dimension]))
402 // hash of userid => rating
403 return array_sum(array_values($data[$dimension])) / count($data[$dimension]);
409 //*******************************************************************************
414 * @param dimension The rating dimension id.
417 * If this is null (or left off), the search for ratings
418 * is not restricted by dimension.
420 * @param int $rater The page id of the rater, i.e. page doing the rating.
421 * This is a Wiki page id, often of a user page.
424 * If this is null (or left off), the search for ratings
425 * is not restricted by rater.
426 * TODO: Support an array
428 * @param int $ratee The page id of the ratee, i.e. page being rated.
429 * Example: "DudeWheresMyCar"
431 * If this is null (or left off), the search for ratings
432 * is not restricted by ratee.
433 * TODO: Support an array
435 * @param string $orderby An order-by clause with fields and (optionally) ASC
437 * Example: "ratingvalue DESC"
439 * If this is null (or left off), the search for ratings
440 * has no guaranteed order
442 * @param string $pageinfo The type of page that has its info returned (i.e.,
443 * 'pagename', 'hits', and 'pagedata') in the rows.
446 * If this is null (or left off), the info returned
447 * is for the 'ratee' page (i.e., thing being rated).
449 * @return DB iterator with results
452 function sql_get_rating($dimension = null, $rater = null, $ratee = null,
453 $orderby = null, $pageinfo = "ratee")
455 if (is_null($dimension)) $dimension = $this->dimension;
456 $result = $this->_sql_get_rating_result($dimension, $rater, $ratee, $orderby, $pageinfo);
457 return new $this->iter_class($this, $result);
460 function sql_get_users_rated($dimension = null, $pagename = null, $orderby = null)
462 if (is_null($dimension)) $dimension = $this->dimension;
463 $result = $this->_sql_get_rating_result($dimension, null, $pagename, $orderby, "rater");
464 return new $this->iter_class($this, $result);
467 // all users who rated this page resp if null all pages.. needed?
468 function metadata_get_users_rated($dimension = null, $pagename = null, $orderby = null)
470 if (is_null($dimension)) $dimension = $this->dimension;
474 return new WikiDB_Array_PageIterator($users);
476 $page = $this->_dbi->getPage($pagename);
477 $data = $page->get('rating');
478 if (!empty($data[$dimension])) {
479 //array($userid => (float)$rating);
480 return new WikiDB_Array_PageIterator(array_keys($data[$dimension]));
482 return new WikiDB_Array_PageIterator($users);
486 * @return result ressource, suitable to the iterator
488 private function _sql_get_rating_result($dimension = null, $rater = null, $ratee = null,
489 $orderby = null, $pageinfo = "ratee")
491 // pageinfo must be 'rater' or 'ratee'
492 if (($pageinfo != "ratee") && ($pageinfo != "rater"))
494 $dbi = &$this->_sqlbackend;
497 //$dbh = &$this->_dbi;
498 extract($dbi->_table_names);
499 $where = "WHERE r." . $pageinfo . "page = p.id";
500 if (isset($dimension)) {
501 $where .= " AND dimension=$dimension";
504 $raterid = $dbi->_get_pageid($rater, true);
505 $where .= " AND raterpage=$raterid";
508 if (is_array($ratee)) {
510 for ($i = 0; $i < count($ratee); $i++) {
511 $rateeid = $dbi->_get_pageid($ratee[$i], true);
512 $where .= "rateepage=$rateeid";
513 if ($i != (count($ratee) - 1)) {
519 $rateeid = $dbi->_get_pageid($ratee, true);
520 $where .= " AND rateepage=$rateeid";
524 if (isset($orderby)) {
525 $orderbyStr = " ORDER BY " . $orderby;
527 if (isset($rater) or isset($ratee)) $what = '*';
528 // same as _get_users_rated_result()
530 $what = 'DISTINCT p.pagename';
531 if ($pageinfo == 'rater')
532 $what = 'DISTINCT p.pagename as userid';
535 $query = "SELECT $what"
536 . " FROM $rating_tbl r, $page_tbl p "
539 $result = $dbi->_dbh->query($query);
546 * @param rater The page id of the rater, i.e. page doing the rating.
547 * This is a Wiki page id, often of a user page.
548 * @param ratee The page id of the ratee, i.e. page being rated.
549 * @param dimension The rating dimension id.
551 * @return true upon success
553 public function sql_delete_rating($rater, $ratee, $dimension)
555 //$dbh = &$this->_dbi;
556 $dbi = &$this->_sqlbackend;
557 extract($dbi->_table_names);
560 $raterid = $dbi->_get_pageid($rater, true);
561 $rateeid = $dbi->_get_pageid($ratee, true);
562 $where = "WHERE raterpage=$raterid and rateepage=$rateeid";
563 if (isset($dimension)) {
564 $where .= " AND dimension=$dimension";
566 $dbi->_dbh->query("DELETE FROM $rating_tbl $where");
574 * @param rater The page id of the rater, i.e. page doing the rating.
575 * This is a Wiki page id, often of a user page.
576 * @param ratee The page id of the ratee, i.e. page being rated.
577 * @param rateeversion The version of the ratee page.
578 * @param dimension The rating dimension id.
579 * @param rating The rating value (a float).
581 * @return true upon success
583 public function sql_rate($rater, $ratee, $rateeversion, $dimension, $rating)
585 $dbi = &$this->_sqlbackend;
586 extract($dbi->_table_names);
587 if (empty($rating_tbl))
588 $rating_tbl = $this->_dbi->getParam('prefix') . 'rating';
591 $raterid = $dbi->_get_pageid($rater, true);
592 $rateeid = $dbi->_get_pageid($ratee, true);
595 //mysql optimize: REPLACE if raterpage and rateepage are keys
596 $dbi->_dbh->query("DELETE from $rating_tbl WHERE dimension=$dimension AND raterpage=$raterid AND rateepage=$rateeid");
597 $where = "WHERE raterpage='$raterid' AND rateepage='$rateeid'";
598 $insert = "INSERT INTO $rating_tbl (dimension, raterpage, rateepage, ratingvalue, rateeversion)"
599 . " VALUES ('$dimension', $raterid, $rateeid, '$rating', '$rateeversion')";
600 $dbi->_dbh->query($insert);
606 function metadata_get_rating($userid, $pagename, $dimension)
608 if (!$pagename) return false;
609 $page = $this->_dbi->getPage($pagename);
610 $data = $page->get('rating');
611 if (!empty($data[$dimension][$userid]))
612 return (float)$data[$dimension][$userid];
617 function metadata_set_rating($userid, $pagename, $dimension, $rating = -1)
619 if (!$pagename) return false;
620 $page = $this->_dbi->getPage($pagename);
621 $data = $page->get('rating');
623 unset($data[$dimension][$userid]);
625 if (empty($data[$dimension]))
626 $data[$dimension] = array($userid => (float)$rating);
628 $data[$dimension][$userid] = (float)$rating;
630 $page->set('rating', $data);
639 // c-hanging-comment-ender-p: nil
640 // indent-tabs-mode: nil