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);
153 * @param dimension The rating dimension id.
156 * If this is null (or left off), the search for ratings
157 * is not restricted by dimension.
159 * @param rater The page id of the rater, i.e. page doing the rating.
160 * This is a Wiki page id, often of a user page.
163 * If this is null (or left off), the search for ratings
164 * is not restricted by rater.
165 * TODO: Support an array
167 * @param ratee The page id of the ratee, i.e. page being rated.
168 * Example: "DudeWheresMyCar"
170 * If this is null (or left off), the search for ratings
171 * is not restricted by ratee.
173 * @param orderby An order-by clause with fields and (optionally) ASC
175 * Example: "ratingvalue DESC"
177 * If this is null (or left off), the search for ratings
178 * has no guaranteed order
180 * @param pageinfo The type of page that has its info returned (i.e.,
181 * 'pagename', 'hits', and 'pagedata') in the rows.
184 * If this is null (or left off), the info returned
185 * is for the 'ratee' page (i.e., thing being rated).
187 * @return DB iterator with results
189 function get_rating($dimension=null, $rater=null, $ratee=null,
190 $orderby = null, $pageinfo = "ratee") {
191 if (RATING_STORAGE == 'SQL') {
192 $ratings_iter = $this->sql_get_rating($dimension, $rater, $pagename);
193 if ($rating = $ratings_iter->next() and isset($rating['ratingvalue'])) {
194 return $rating['ratingvalue'];
198 return $this->metadata_get_rating($rater, $pagename, $dimension);
202 /* UR: What is this for? NOT USED!
203 Maybe the list of users (ratees) who rated on this page.
205 function get_users_rated($dimension=null, $pagename = null, $orderby = null) {
206 if (RATING_STORAGE == 'SQL') {
207 $ratings_iter = $this->sql_get_users_rated($dimension, $pagename, $orderby);
210 while ($rating = $ratings_iter->next()) {
211 $users[] = $rating['userid'];
215 return $this->metadata_get_users_rated($dimension, $pagename, $orderby);
220 * Like get_rating(), but return a WikiDB_PageIterator
223 function get_rating_page($dimension=null, $rater=null, $ratee=null,
224 $orderby = null, $pageinfo = "ratee") {
225 if (RATING_STORAGE == 'SQL') {
226 return $this->sql_get_rating($dimension, $rater, $ratee, $orderby, $pageinfo);
228 // empty dummy iterator
230 return new WikiDB_Array_PageIterator($pages);
237 * @param rater The page id of the rater, i.e. page doing the rating.
238 * This is a Wiki page id, often of a user page.
239 * @param ratee The page id of the ratee, i.e. page being rated.
240 * @param dimension The rating dimension id.
244 * @return true upon success
246 function delete_rating($rater, $ratee, $dimension) {
247 if (RATING_STORAGE == 'SQL') {
248 $this->sql_delete_rating($rater, $ratee, $dimension);
250 $this->metadata_set_rating($rater, $ratee, $dimension, -1);
257 * @param rater The page id of the rater, i.e. page doing the rating.
258 * This is a Wiki page id, often of a user page.
259 * @param ratee The page id of the ratee, i.e. page being rated.
260 * @param rateeversion The version of the ratee page.
261 * @param dimension The rating dimension id.
262 * @param rating The rating value (a float).
266 * @return true upon success
268 function rate($rater, $ratee, $rateeversion, $dimension, $rating) {
269 if (RATING_STORAGE == 'SQL') {
270 $page = $this->_dbi->getPage($pagename);
271 $current = $page->getCurrentRevision();
272 $rateeversion = $current->getVersion();
273 $this->sql_rate($userid, $pagename, $rateeversion, $dimension, $rating);
275 $this->metadata_set_rating($userid, $pagename, $dimension, $rating);
279 //function getUsersRated(){}
281 //*******************************************************************************
283 // Use wikilens/RatingsUser.php for the php methods.
286 // Currently we have to call the "suggest" CGI
287 // http://www-users.cs.umn.edu/~karypis/suggest/
288 // until we implement a simple recommendation engine.
289 // Note that "suggest" is only free for non-profit organizations.
290 // I am currently writing a binary CGI mysuggest using suggest, which loads
292 function getPrediction($userid=null, $pagename=null, $dimension=null) {
293 if (is_null($dimension)) $dimension = $this->dimension;
294 if (is_null($userid)) $userid = $this->userid;
295 if (is_null($pagename)) $pagename = $this->pagename;
297 if (RATING_STORAGE == 'SQL') {
298 $dbh = &$this->_sqlbackend;
299 if (isset($pagename))
300 $page = $dbh->_get_pageid($pagename);
304 $user = $dbh->_get_pageid($userid);
308 if (defined('RATING_EXTERNAL') and RATING_EXTERNAL) {
309 // how call mysuggest.exe? as CGI or natively
310 //$rating = HTML::Raw("<!--#include virtual=".RATING_ENGINE." -->");
311 $args = "-u$user -p$page -malpha"; // --top 10
312 if (isset($dimension))
313 $args .= " -d$dimension";
314 $rating = passthru(RATING_EXTERNAL . " $args");
316 $rating = $this->php_prediction($userid, $pagename, $dimension);
322 * Slow item-based recommendation engine, similar to suggest RType=2.
323 * Only the SUGGEST_EstimateAlpha part
324 * Take wikilens/RatingsUser.php for the php methods.
326 function php_prediction($userid=null, $pagename=null, $dimension=null) {
327 if (is_null($dimension)) $dimension = $this->dimension;
328 if (is_null($userid)) $userid = $this->userid;
329 if (is_null($pagename)) $pagename = $this->pagename;
330 if (empty($this->buddies)) {
331 require_once("lib/wikilens/RatingsUser.php");
332 require_once("lib/wikilens/Buddy.php");
333 $user = RatingsUserFactory::getUser($userid);
334 $this->buddies = getBuddies($user, $GLOBALS['request']->_dbi);
336 return $user->knn_uu_predict($pagename, $this->buddies, $dimension);
339 function getNumUsers($pagename=null, $dimension=null) {
340 if (is_null($dimension)) $dimension = $this->dimension;
341 if (is_null($pagename)) $pagename = $this->pagename;
342 if (RATING_STORAGE == 'SQL') {
343 $ratings_iter = $this->sql_get_rating($dimension, null, $pagename,
345 return $ratings_iter->count();
347 if (!$pagename) return 0;
348 $page = $this->_dbi->getPage($pagename);
349 $data = $page->get('rating');
350 if (!empty($data[$dimension]))
351 return count($data[$dimension]);
357 function getAvg($pagename=null, $dimension=null) {
358 if (is_null($dimension)) $dimension = $this->dimension;
359 if (is_null($pagename)) $pagename = $this->pagename;
360 if (RATING_STORAGE == 'SQL') {
361 $dbi = &$this->_sqlbackend;
362 if (isset($pagename) || isset($dimension)) {
365 if (isset($pagename)) {
366 $raterid = $this->_sqlbackend->_get_pageid($pagename, true);
367 $where .= " raterpage=$raterid";
369 if (isset($dimension)) {
370 if (isset($pagename)) $where .= " AND";
371 $where .= " dimension=$dimension";
373 //$dbh = &$this->_dbi;
374 extract($dbi->_table_names);
375 $query = "SELECT AVG(ratingvalue) as avg"
376 . " FROM $rating_tbl r, $page_tbl p "
378 . " GROUP BY raterpage";
379 $result = $dbi->_dbh->query($query);
380 $iter = new $this->iter_class($this, $result);
381 $row = $iter->next();
384 if (!$pagename) return 0;
385 $page = $this->_dbi->getPage($pagename);
386 $data = $page->get('rating');
387 if (!empty($data[$dimension]))
388 // hash of userid => rating
389 return array_sum(array_values($data[$dimension])) / count($data[$dimension]);
394 //*******************************************************************************
399 * @param dimension The rating dimension id.
402 * If this is null (or left off), the search for ratings
403 * is not restricted by dimension.
405 * @param rater The page id of the rater, i.e. page doing the rating.
406 * This is a Wiki page id, often of a user page.
409 * If this is null (or left off), the search for ratings
410 * is not restricted by rater.
411 * TODO: Support an array
413 * @param ratee The page id of the ratee, i.e. page being rated.
414 * Example: "DudeWheresMyCar"
416 * If this is null (or left off), the search for ratings
417 * is not restricted by ratee.
418 * TODO: Support an array
420 * @param orderby An order-by clause with fields and (optionally) ASC
422 * Example: "ratingvalue DESC"
424 * If this is null (or left off), the search for ratings
425 * has no guaranteed order
427 * @param pageinfo The type of page that has its info returned (i.e.,
428 * 'pagename', 'hits', and 'pagedata') in the rows.
431 * If this is null (or left off), the info returned
432 * is for the 'ratee' page (i.e., thing being rated).
434 * @return DB iterator with results
436 function sql_get_rating($dimension=null, $rater=null, $ratee=null,
437 $orderby=null, $pageinfo = "ratee") {
438 if (is_null($dimension)) $dimension = $this->dimension;
439 $result = $this->_sql_get_rating_result($dimension, $rater, $ratee, $orderby, $pageinfo);
440 return new $this->iter_class($this, $result);
443 function sql_get_users_rated($dimension=null, $pagename=null, $orderby=null) {
444 if (is_null($dimension)) $dimension = $this->dimension;
445 $result = $this->_sql_get_rating_result($dimension, null, $pagename, $orderby, "rater");
446 return new $this->iter_class($this, $result);
449 // all users who rated this page resp if null all pages.. needed?
450 function metadata_get_users_rated($dimension=null, $pagename=null, $orderby=null) {
451 if (is_null($dimension)) $dimension = $this->dimension;
455 return new WikiDB_Array_PageIterator($users);
457 $page = $this->_dbi->getPage($pagename);
458 $data = $page->get('rating');
459 if (!empty($data[$dimension])) {
460 //array($userid => (float)$rating);
461 return new WikiDB_Array_PageIterator(array_keys($data[$dimension]));
463 return new WikiDB_Array_PageIterator($users);
468 * @return result ressource, suitable to the iterator
470 function _sql_get_rating_result($dimension=null, $rater=null, $ratee=null,
471 $orderby=null, $pageinfo = "ratee") {
472 // pageinfo must be 'rater' or 'ratee'
473 if (($pageinfo != "ratee") && ($pageinfo != "rater"))
475 $dbi = &$this->_sqlbackend;
478 //$dbh = &$this->_dbi;
479 extract($dbi->_table_names);
480 $where = "WHERE r." . $pageinfo . "page = p.id";
481 if (isset($dimension)) {
482 $where .= " AND dimension=$dimension";
485 $raterid = $dbi->_get_pageid($rater, true);
486 $where .= " AND raterpage=$raterid";
489 if(is_array($ratee)){
491 for($i = 0; $i < count($ratee); $i++){
492 $rateeid = $dbi->_get_pageid($ratee[$i], true);
493 $where .= "rateepage=$rateeid";
494 if($i != (count($ratee) - 1)){
500 $rateeid = $dbi->_get_pageid($ratee, true);
501 $where .= " AND rateepage=$rateeid";
505 if (isset($orderby)) {
506 $orderbyStr = " ORDER BY " . $orderby;
508 if (isset($rater) or isset($ratee)) $what = '*';
509 // same as _get_users_rated_result()
511 $what = 'DISTINCT p.pagename';
512 if ($pageinfo == 'rater')
513 $what = 'DISTINCT p.pagename as userid';
516 $query = "SELECT $what"
517 . " FROM $rating_tbl r, $page_tbl p "
520 $result = $dbi->_dbh->query($query);
527 * @param rater The page id of the rater, i.e. page doing the rating.
528 * This is a Wiki page id, often of a user page.
529 * @param ratee The page id of the ratee, i.e. page being rated.
530 * @param dimension The rating dimension id.
534 * @return true upon success
536 function sql_delete_rating($rater, $ratee, $dimension) {
537 //$dbh = &$this->_dbi;
538 $dbi = &$this->_sqlbackend;
539 extract($dbi->_table_names);
542 $raterid = $dbi->_get_pageid($rater, true);
543 $rateeid = $dbi->_get_pageid($ratee, true);
544 $where = "WHERE raterpage=$raterid and rateepage=$rateeid";
545 if (isset($dimension)) {
546 $where .= " AND dimension=$dimension";
548 $dbi->_dbh->query("DELETE FROM $rating_tbl $where");
556 * @param rater The page id of the rater, i.e. page doing the rating.
557 * This is a Wiki page id, often of a user page.
558 * @param ratee The page id of the ratee, i.e. page being rated.
559 * @param rateeversion The version of the ratee page.
560 * @param dimension The rating dimension id.
561 * @param rating The rating value (a float).
565 * @return true upon success
567 // ($this->userid, $this->pagename, $page->version, $this->dimension, $rating);
568 function sql_rate($rater, $ratee, $rateeversion, $dimension, $rating) {
569 $dbi = &$this->_sqlbackend;
570 extract($dbi->_table_names);
571 if (empty($rating_tbl))
572 $rating_tbl = $this->_dbi->getParam('prefix') . 'rating';
575 $raterid = $dbi->_get_pageid($rater, true);
576 $rateeid = $dbi->_get_pageid($ratee, true);
579 //mysql optimize: REPLACE if raterpage and rateepage are keys
580 $dbi->_dbh->query("DELETE from $rating_tbl WHERE dimension=$dimension AND raterpage=$raterid AND rateepage=$rateeid");
581 $where = "WHERE raterpage='$raterid' AND rateepage='$rateeid'";
582 $insert = "INSERT INTO $rating_tbl (dimension, raterpage, rateepage, ratingvalue, rateeversion)"
583 ." VALUES ('$dimension', $raterid, $rateeid, '$rating', '$rateeversion')";
584 $dbi->_dbh->query($insert);
590 function metadata_get_rating($userid, $pagename, $dimension) {
591 if (!$pagename) return false;
592 $page = $this->_dbi->getPage($pagename);
593 $data = $page->get('rating');
594 if (!empty($data[$dimension][$userid]))
595 return (float)$data[$dimension][$userid];
600 function metadata_set_rating($userid, $pagename, $dimension, $rating = -1) {
601 if (!$pagename) return false;
602 $page = $this->_dbi->getPage($pagename);
603 $data = $page->get('rating');
605 unset($data[$dimension][$userid]);
607 if (empty($data[$dimension]))
608 $data[$dimension] = array($userid => (float)$rating);
610 $data[$dimension][$userid] = (float)$rating;
612 $page->set('rating',$data);
618 class RatingsDB_backend_PearDB
619 extends WikiDB_backend_PearDB {
620 function get_rating($dimension=null, $rater=null, $ratee=null,
621 $orderby=null, $pageinfo = "ratee") {
622 $result = $this->_get_rating_result(
623 $dimension, $rater, $ratee, $orderby, $pageinfo);
624 return new WikiDB_backend_PearDB_generic_iter($this, $result);
627 function get_users_rated($dimension=null, $orderby=null) {
628 $result = $this->_get_users_rated_result(
629 $dimension, $orderby);
630 return new WikiDB_backend_PearDB_generic_iter($this, $result);
633 function get_rating_page($dimension=null, $rater=null, $ratee=null,
634 $orderby=null, $pageinfo = "ratee") {
635 $result = $this->_get_rating_result(
636 $dimension, $rater, $ratee, $orderby, $pageinfo);
637 return new WikiDB_backend_PearDB_iter($this, $result);
640 function _get_rating_result($dimension=null, $rater=null, $ratee=null,
641 $orderby=null, $pageinfo = "ratee") {
642 // pageinfo must be 'rater' or 'ratee'
643 if (($pageinfo != "ratee") && ($pageinfo != "rater"))
647 extract($this->_table_names);
649 $where = "WHERE r." . $pageinfo . "page = p.id";
650 if (isset($dimension)) {
651 $where .= " AND dimension=$dimension";
654 $raterid = $this->_get_pageid($rater, true);
655 $where .= " AND raterpage=$raterid";
658 if(is_array($ratee)){
660 for($i = 0; $i < count($ratee); $i++){
661 $rateeid = $this->_get_pageid($ratee[$i], true);
662 $where .= "rateepage=$rateeid";
663 if($i != (count($ratee) - 1)){
669 $rateeid = $this->_get_pageid($ratee, true);
670 $where .= " AND rateepage=$rateeid";
675 if (isset($orderby)) {
676 $orderbyStr = " ORDER BY " . $orderby;
680 . " FROM $rating_tbl r, $page_tbl p "
684 $result = $dbh->query($query);
689 function _get_users_rated_result($dimension=null, $orderby=null) {
691 extract($this->_table_names);
693 $where = "WHERE p.id=r.raterpage";
694 if (isset($dimension)) {
695 $where .= " AND dimension=$dimension";
698 if (isset($orderby)) {
699 $orderbyStr = " ORDER BY " . $orderby;
702 $query = "SELECT DISTINCT p.pagename"
703 . " FROM $rating_tbl r, $page_tbl p "
707 $result = $dbh->query($query);
711 function delete_rating($rater, $ratee, $dimension) {
713 extract($this->_table_names);
716 $raterid = $this->_get_pageid($rater, true);
717 $rateeid = $this->_get_pageid($ratee, true);
719 $dbh->query("DELETE FROM $rating_tbl WHERE raterpage=$raterid and rateepage=$rateeid and dimension=$dimension");
724 function rate($rater, $ratee, $rateeversion, $dimension, $rating, $isPrivate = 'no') {
726 extract($this->_table_names);
729 $raterid = $this->_get_pageid($rater, true);
730 $rateeid = $this->_get_pageid($ratee, true);
732 $dbh->query("DELETE FROM $rating_tbl WHERE raterpage=$raterid and rateepage=$rateeid and dimension=$dimension and isPrivate='$isPrivate'");
733 // NOTE: Leave tstamp off the insert, and MySQL automatically updates it
734 $dbh->query("INSERT INTO $rating_tbl (dimension, raterpage, rateepage, ratingvalue, rateeversion, isPrivate) VALUES ($dimension, $raterid, $rateeid, $rating, $rateeversion, '$isPrivate')");
745 // c-hanging-comment-ender-p: nil
746 // indent-tabs-mode: nil