3 * Copyright © 2004,2007 Reini Urban
4 * Copyright © 2010 $ThePhpWikiProgrammingTeam
6 * This file is part of PhpWiki.
8 * PhpWiki is free software; you can redistribute it and/or modify
9 * it under the terms of the GNU General Public License as published by
10 * the Free Software Foundation; either version 2 of the License, or
11 * (at your option) any later version.
13 * PhpWiki is distributed in the hope that it will be useful,
14 * but WITHOUT ANY WARRANTY; without even the implied warranty of
15 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 * GNU General Public License for more details.
18 * You should have received a copy of the GNU General Public License along
19 * with PhpWiki; if not, write to the Free Software Foundation, Inc.,
20 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
22 * SPDX-License-Identifier: GPL-2.0-or-later
27 * @author: Dan Frankowski (wikilens group manager), Reini Urban (as plugin)
30 * - fix RATING_STORAGE = WIKIPAGE (dba, file)
32 * - finish mysuggest.c (external engine with data from mysql)
33 * - add the various show modes (esp. TopN queries in PHP)
38 dimension INT(4) NOT NULL,
39 raterpage INT(11) NOT NULL,
40 rateepage INT(11) NOT NULL,
41 ratingvalue FLOAT NOT NULL,
42 rateeversion INT(11) NOT NULL,
43 isPrivate ENUM('yes','no'),
44 tstamp TIMESTAMP(14) NOT NULL,
45 PRIMARY KEY (dimension, raterpage, rateepage)
50 * @var WikiRequest $request
54 // For other than SQL backends. dba + adodb SQL ratings are allowed but deprecated.
55 // We will probably drop this hack.
56 if (!defined('RATING_STORAGE'))
57 // for DATABASE_TYPE=dba and forced RATING_STORAGE=SQL we must use ADODB,
58 // but this is problematic.
59 define('RATING_STORAGE', $request->_dbi->_backend->isSQL() ? 'SQL' : 'WIKIPAGE');
60 //define('RATING_STORAGE','WIKIPAGE'); // not fully supported yet
62 // leave undefined for internal, slow php engine.
63 //if (!defined('RATING_EXTERNAL'))
64 // define('RATING_EXTERNAL',PHPWIKI_DIR . 'suggest.exe');
67 if (!defined('EXPLICIT_RATINGS_DIMENSION'))
68 define('EXPLICIT_RATINGS_DIMENSION', 0);
69 if (!defined('LIST_ITEMS_DIMENSION'))
70 define('LIST_ITEMS_DIMENSION', 1);
71 if (!defined('LIST_OWNER_DIMENSION'))
72 define('LIST_OWNER_DIMENSION', 2);
73 if (!defined('LIST_TYPE_DIMENSION'))
74 define('LIST_TYPE_DIMENSION', 3);
76 //TODO: split class into SQL and metadata backends
77 class RatingsDb extends WikiDB
80 function __construct()
83 $this->_dbi = &$request->_dbi;
84 $this->_backend = &$this->_dbi->_backend;
85 $this->dimension = null;
86 if (RATING_STORAGE == 'SQL') {
87 if (is_a($this->_backend, 'WikiDB_backend_PearDB')) {
88 $this->_sqlbackend = &$this->_backend;
89 $this->dbtype = "PearDB";
90 } elseif (is_a($this->_backend, 'WikiDB_backend_ADODOB')) {
91 $this->_sqlbackend = &$this->_backend;
92 $this->dbtype = "ADODB";
93 } elseif (is_a($this->_backend, 'WikiDB_backend_PDO')) {
94 $this->_sqlbackend = &$this->_backend;
95 $this->dbtype = "PDO";
97 include_once 'lib/WikiDB/backend/ADODB.php';
98 // It is not possible to decouple a ref from the source again. (4.3.11)
99 // It replaced the main request backend. So we don't initialize _sqlbackend before.
100 //$this->_sqlbackend = clone($this->_backend);
101 $this->_sqlbackend = new WikiDB_backend_ADODB($GLOBALS['DBParams']);
102 $this->dbtype = "ADODB";
104 $this->iter_class = "WikiDB_backend_" . $this->dbtype . "_generic_iter";
106 extract($this->_sqlbackend->_table_names);
107 if (empty($rating_tbl)) {
108 $rating_tbl = (!empty($GLOBALS['DBParams']['prefix'])
109 ? $GLOBALS['DBParams']['prefix'] : '') . 'rating';
110 $this->_sqlbackend->_table_names['rating_tbl'] = $rating_tbl;
113 $this->iter_class = "WikiDB_Array_PageIterator";
117 // this is a singleton. It ensures there is only 1 ratingsDB.
118 static function & getTheRatingsDb()
120 static $_theRatingsDb;
122 if (!isset($_theRatingsDb)) {
123 $_theRatingsDb = new RatingsDb();
125 //echo "rating db is $_theRatingsDb";
126 return $_theRatingsDb;
130 /// *************************************************************************************
132 // from Reini Urban's RateIt plugin
133 function addRating($rating, $userid, $pagename, $dimension)
135 if (RATING_STORAGE == 'SQL') {
136 $page = $this->_dbi->getPage($pagename);
137 $current = $page->getCurrentRevision();
138 $rateeversion = $current->getVersion();
139 $this->sql_rate($userid, $pagename, $rateeversion, $dimension, $rating);
141 $this->metadata_set_rating($userid, $pagename, $dimension, $rating);
145 function deleteRating($userid = null, $pagename = null, $dimension = null)
147 if (is_null($dimension)) $dimension = $this->dimension;
148 if (is_null($userid)) $userid = $this->userid;
149 if (is_null($pagename)) $pagename = $this->pagename;
150 if (RATING_STORAGE == 'SQL') {
151 $this->sql_delete_rating($userid, $pagename, $dimension);
153 $this->metadata_set_rating($userid, $pagename, $dimension, -1);
157 function getRating($userid = null, $pagename = null, $dimension = null)
159 if (RATING_STORAGE == 'SQL') {
160 $ratings_iter = $this->sql_get_rating($dimension, $userid, $pagename);
161 if ($rating = $ratings_iter->next() and isset($rating['ratingvalue'])) {
162 return $rating['ratingvalue'];
166 return $this->metadata_get_rating($userid, $pagename, $dimension);
170 function getUsersRated($dimension = null, $orderby = null)
172 if (is_null($dimension)) $dimension = $this->dimension;
173 //if (is_null($userid)) $userid = $this->userid;
174 //if (is_null($pagename)) $pagename = $this->pagename;
175 if (RATING_STORAGE == 'SQL') {
176 $ratings_iter = $this->sql_get_users_rated($dimension, $orderby);
177 if ($rating = $ratings_iter->next() and isset($rating['ratingvalue'])) {
178 return $rating['ratingvalue'];
182 return $this->metadata_get_users_rated($dimension, $orderby);
189 * @param dimension The rating dimension id.
192 * If this is null (or left off), the search for ratings
193 * is not restricted by dimension.
195 * @param rater The page id of the rater, i.e. page doing the rating.
196 * This is a Wiki page id, often of a user page.
199 * If this is null (or left off), the search for ratings
200 * is not restricted by rater.
201 * TODO: Support an array
203 * @param ratee The page id of the ratee, i.e. page being rated.
204 * Example: "DudeWheresMyCar"
206 * If this is null (or left off), the search for ratings
207 * is not restricted by ratee.
209 * @param orderby An order-by clause with fields and (optionally) ASC
211 * Example: "ratingvalue DESC"
213 * If this is null (or left off), the search for ratings
214 * has no guaranteed order
216 * @param pageinfo The type of page that has its info returned (i.e.,
217 * 'pagename', 'hits', and 'pagedata') in the rows.
220 * If this is null (or left off), the info returned
221 * is for the 'ratee' page (i.e., thing being rated).
223 * @return DB iterator with results
225 function get_rating($dimension = null, $rater = null, $ratee = null,
226 $orderby = null, $pageinfo = "ratee")
228 if (RATING_STORAGE == 'SQL') {
229 $ratings_iter = $this->sql_get_rating($dimension, $rater, $pagename);
230 if ($rating = $ratings_iter->next() and isset($rating['ratingvalue'])) {
231 return $rating['ratingvalue'];
235 return $this->metadata_get_rating($rater, $pagename, $dimension);
239 /* UR: What is this for? NOT USED!
240 Maybe the list of users (ratees) who rated on this page.
242 function get_users_rated($dimension = null, $pagename = null, $orderby = null)
244 if (RATING_STORAGE == 'SQL') {
245 $ratings_iter = $this->sql_get_users_rated($dimension, $pagename, $orderby);
248 while ($rating = $ratings_iter->next()) {
249 $users[] = $rating['userid'];
253 return $this->metadata_get_users_rated($dimension, $pagename, $orderby);
258 * Like get_rating(), but return a WikiDB_PageIterator
261 function get_rating_page($dimension = null, $rater = null, $ratee = null,
262 $orderby = null, $pageinfo = "ratee")
264 if (RATING_STORAGE == 'SQL') {
265 return $this->sql_get_rating($dimension, $rater, $ratee, $orderby, $pageinfo);
267 // empty dummy iterator
269 return new WikiDB_Array_PageIterator($pages);
276 * @param rater The page id of the rater, i.e. page doing the rating.
277 * This is a Wiki page id, often of a user page.
278 * @param ratee The page id of the ratee, i.e. page being rated.
279 * @param dimension The rating dimension id.
281 * @return true upon success
283 public function delete_rating($rater, $ratee, $dimension)
285 if (RATING_STORAGE == 'SQL') {
286 $this->sql_delete_rating($rater, $ratee, $dimension);
288 $this->metadata_set_rating($rater, $ratee, $dimension, -1);
295 * @param rater The page id of the rater, i.e. page doing the rating.
296 * This is a Wiki page id, often of a user page.
297 * @param ratee The page id of the ratee, i.e. page being rated.
298 * @param rateeversion The version of the ratee page.
299 * @param dimension The rating dimension id.
300 * @param rating The rating value (a float).
302 * @return true upon success
304 public function rate($rater, $ratee, $rateeversion, $dimension, $rating)
306 if (RATING_STORAGE == 'SQL') {
307 $page = $this->_dbi->getPage($pagename);
308 $current = $page->getCurrentRevision();
309 $rateeversion = $current->getVersion();
310 $this->sql_rate($userid, $pagename, $rateeversion, $dimension, $rating);
312 $this->metadata_set_rating($userid, $pagename, $dimension, $rating);
316 //function getUsersRated(){}
318 //*******************************************************************************
320 // Use wikilens/RatingsUser.php for the php methods.
323 // Currently we have to call the "suggest" CGI
324 // http://www-users.cs.umn.edu/~karypis/suggest/
325 // until we implement a simple recommendation engine.
326 // Note that "suggest" is only free for non-profit organizations.
327 // I am currently writing a binary CGI mysuggest using suggest, which loads
329 function getPrediction($userid = null, $pagename = null, $dimension = null)
331 if (is_null($dimension)) $dimension = $this->dimension;
332 if (is_null($userid)) $userid = $this->userid;
333 if (is_null($pagename)) $pagename = $this->pagename;
335 if (RATING_STORAGE == 'SQL') {
336 $dbh = &$this->_sqlbackend;
337 if (isset($pagename))
338 $page = $dbh->_get_pageid($pagename);
342 $user = $dbh->_get_pageid($userid);
346 if (defined('RATING_EXTERNAL') and RATING_EXTERNAL) {
347 // how call mysuggest.exe? as CGI or natively
348 //$rating = HTML::raw("<!--#include virtual=".RATING_ENGINE." -->");
349 $args = "-u$user -p$page -malpha"; // --top 10
350 if (isset($dimension))
351 $args .= " -d$dimension";
352 $rating = passthru(RATING_EXTERNAL . " $args");
354 $rating = $this->php_prediction($userid, $pagename, $dimension);
360 * Slow item-based recommendation engine, similar to suggest RType=2.
361 * Only the SUGGEST_EstimateAlpha part
362 * Take wikilens/RatingsUser.php for the php methods.
364 function php_prediction($userid = null, $pagename = null, $dimension = null)
367 * @var WikiRequest $request
371 if (is_null($dimension)) $dimension = $this->dimension;
372 if (is_null($userid)) $userid = $this->userid;
373 if (is_null($pagename)) $pagename = $this->pagename;
374 if (empty($this->buddies)) {
375 require_once 'lib/wikilens/RatingsUser.php';
376 require_once 'lib/wikilens/Buddy.php';
377 $user = RatingsUserFactory::getUser($userid);
378 $this->buddies = getBuddies($user, $request->_dbi);
380 return $user->knn_uu_predict($pagename, $this->buddies, $dimension);
383 function getNumUsers($pagename = null, $dimension = null)
385 if (is_null($dimension)) $dimension = $this->dimension;
386 if (is_null($pagename)) $pagename = $this->pagename;
387 if (RATING_STORAGE == 'SQL') {
388 $ratings_iter = $this->sql_get_rating($dimension, null, $pagename,
390 return $ratings_iter->count();
392 if (!$pagename) return 0;
393 $page = $this->_dbi->getPage($pagename);
394 $data = $page->get('rating');
395 if (!empty($data[$dimension]))
396 return count($data[$dimension]);
402 function getAvg($pagename = null, $dimension = null)
404 if (is_null($dimension)) $dimension = $this->dimension;
405 if (is_null($pagename)) $pagename = $this->pagename;
406 if (RATING_STORAGE == 'SQL') {
407 $dbi = &$this->_sqlbackend;
408 if (isset($pagename) || isset($dimension)) {
411 if (isset($pagename)) {
412 if (defined('FUSIONFORGE') && FUSIONFORGE) {
413 $rateeid = $this->_sqlbackend->_get_pageid($pagename, true);
414 $where .= " rateepage=$rateeid";
416 $raterid = $this->_sqlbackend->_get_pageid($pagename, true);
417 $where .= " raterpage=$raterid";
420 if (isset($dimension)) {
421 if (isset($pagename)) $where .= " AND";
422 $where .= " dimension=$dimension";
424 extract($dbi->_table_names);
425 if (defined('FUSIONFORGE') && FUSIONFORGE) {
426 $query = "SELECT AVG(ratingvalue) as avg FROM $rating_tbl " . $where;
428 $query = "SELECT AVG(ratingvalue) as avg FROM $rating_tbl r, $page_tbl p " . $where . " GROUP BY raterpage";
430 $result = $dbi->_dbh->query($query);
431 $iter = new $this->iter_class($this, $result);
432 $row = $iter->next();
433 return is_array($row) ? $row['avg'] : 0;
437 $page = $this->_dbi->getPage($pagename);
438 $data = $page->get('rating');
439 if (!empty($data[$dimension]))
440 // hash of userid => rating
441 return array_sum(array_values($data[$dimension])) / count($data[$dimension]);
447 //*******************************************************************************
452 * @param dimension The rating dimension id.
455 * If this is null (or left off), the search for ratings
456 * is not restricted by dimension.
458 * @param int $rater The page id of the rater, i.e. page doing the rating.
459 * This is a Wiki page id, often of a user page.
462 * If this is null (or left off), the search for ratings
463 * is not restricted by rater.
464 * TODO: Support an array
466 * @param int $ratee The page id of the ratee, i.e. page being rated.
467 * Example: "DudeWheresMyCar"
469 * If this is null (or left off), the search for ratings
470 * is not restricted by ratee.
471 * TODO: Support an array
473 * @param string $orderby An order-by clause with fields and (optionally) ASC
475 * Example: "ratingvalue DESC"
477 * If this is null (or left off), the search for ratings
478 * has no guaranteed order
480 * @param string $pageinfo The type of page that has its info returned (i.e.,
481 * 'pagename', 'hits', and 'pagedata') in the rows.
484 * If this is null (or left off), the info returned
485 * is for the 'ratee' page (i.e., thing being rated).
487 * @return DB iterator with results
489 function sql_get_rating($dimension = null, $rater = null, $ratee = null,
490 $orderby = null, $pageinfo = "ratee")
492 if (is_null($dimension)) $dimension = $this->dimension;
493 $result = $this->_sql_get_rating_result($dimension, $rater, $ratee, $orderby, $pageinfo);
494 return new $this->iter_class($this, $result);
497 function sql_get_users_rated($dimension = null, $pagename = null, $orderby = null)
499 if (is_null($dimension)) $dimension = $this->dimension;
500 $result = $this->_sql_get_rating_result($dimension, null, $pagename, $orderby, "rater");
501 return new $this->iter_class($this, $result);
504 // all users who rated this page resp if null all pages.. needed?
505 function metadata_get_users_rated($dimension = null, $pagename = null, $orderby = null)
507 if (is_null($dimension)) $dimension = $this->dimension;
511 return new WikiDB_Array_PageIterator($users);
513 $page = $this->_dbi->getPage($pagename);
514 $data = $page->get('rating');
515 if (!empty($data[$dimension])) {
516 //array($userid => (float)$rating);
517 return new WikiDB_Array_PageIterator(array_keys($data[$dimension]));
519 return new WikiDB_Array_PageIterator($users);
523 * @return result ressource, suitable to the iterator
525 private function _sql_get_rating_result($dimension = null, $rater = null, $ratee = null,
526 $orderby = null, $pageinfo = "ratee")
528 // pageinfo must be 'rater' or 'ratee'
529 if (($pageinfo != "ratee") && ($pageinfo != "rater"))
531 $dbi = &$this->_sqlbackend;
534 //$dbh = &$this->_dbi;
535 extract($dbi->_table_names);
536 $where = "WHERE r." . $pageinfo . "page = p.id";
537 if (isset($dimension)) {
538 $where .= " AND dimension=$dimension";
541 $raterid = $dbi->_get_pageid($rater, true);
542 $where .= " AND raterpage=$raterid";
545 if (is_array($ratee)) {
547 for ($i = 0; $i < count($ratee); $i++) {
548 $rateeid = $dbi->_get_pageid($ratee[$i], true);
549 $where .= "rateepage=$rateeid";
550 if ($i != (count($ratee) - 1)) {
556 $rateeid = $dbi->_get_pageid($ratee, true);
557 $where .= " AND rateepage=$rateeid";
561 if (isset($orderby)) {
562 $orderbyStr = " ORDER BY " . $orderby;
564 if (isset($rater) or isset($ratee)) $what = '*';
565 // same as _get_users_rated_result()
567 $what = 'DISTINCT p.pagename';
568 if ($pageinfo == 'rater')
569 $what = 'DISTINCT p.pagename as userid';
572 $query = "SELECT $what"
573 . " FROM $rating_tbl r, $page_tbl p "
576 $result = $dbi->_dbh->query($query);
583 * @param rater The page id of the rater, i.e. page doing the rating.
584 * This is a Wiki page id, often of a user page.
585 * @param ratee The page id of the ratee, i.e. page being rated.
586 * @param dimension The rating dimension id.
588 * @return true upon success
590 public function sql_delete_rating($rater, $ratee, $dimension)
592 //$dbh = &$this->_dbi;
593 $dbi = &$this->_sqlbackend;
594 extract($dbi->_table_names);
597 $raterid = $dbi->_get_pageid($rater, true);
598 $rateeid = $dbi->_get_pageid($ratee, true);
599 $where = "WHERE raterpage=$raterid and rateepage=$rateeid";
600 if (isset($dimension)) {
601 $where .= " AND dimension=$dimension";
603 $dbi->_dbh->query("DELETE FROM $rating_tbl $where");
611 * @param rater The page id of the rater, i.e. page doing the rating.
612 * This is a Wiki page id, often of a user page.
613 * @param ratee The page id of the ratee, i.e. page being rated.
614 * @param rateeversion The version of the ratee page.
615 * @param dimension The rating dimension id.
616 * @param rating The rating value (a float).
618 * @return true upon success
620 public function sql_rate($rater, $ratee, $rateeversion, $dimension, $rating)
622 $dbi = &$this->_sqlbackend;
623 extract($dbi->_table_names);
624 if (empty($rating_tbl))
625 $rating_tbl = $this->_dbi->getParam('prefix') . 'rating';
628 $raterid = $dbi->_get_pageid($rater, true);
629 $rateeid = $dbi->_get_pageid($ratee, true);
632 //mysql optimize: REPLACE if raterpage and rateepage are keys
633 $dbi->_dbh->query("DELETE from $rating_tbl WHERE dimension=$dimension AND raterpage=$raterid AND rateepage=$rateeid");
634 $where = "WHERE raterpage='$raterid' AND rateepage='$rateeid'";
635 $insert = "INSERT INTO $rating_tbl (dimension, raterpage, rateepage, ratingvalue, rateeversion)"
636 . " VALUES ('$dimension', $raterid, $rateeid, '$rating', '$rateeversion')";
637 $dbi->_dbh->query($insert);
643 function metadata_get_rating($userid, $pagename, $dimension)
645 if (!$pagename) return false;
646 $page = $this->_dbi->getPage($pagename);
647 $data = $page->get('rating');
648 if (!empty($data[$dimension][$userid]))
649 return (float)$data[$dimension][$userid];
654 function metadata_set_rating($userid, $pagename, $dimension, $rating = -1)
656 if (!$pagename) return;
657 $page = $this->_dbi->getPage($pagename);
658 $data = $page->get('rating');
660 unset($data[$dimension][$userid]);
662 if (empty($data[$dimension]))
663 $data[$dimension] = array($userid => (float)$rating);
665 $data[$dimension][$userid] = (float)$rating;
667 $page->set('rating', $data);