2 rcs_id('$Id: RatingsDb.php,v 1.2 2004-06-19 10:22:41 rurban Exp $');
5 * @author: Dan Frankowski (wikilens author), Reini Urban (as plugin)
8 * - fix RATING_STORAGE = WIKIPAGE
10 * - finish mysuggest.c (external engine with data from mysql)
11 * - add php_prediction
12 * - add the various show modes (esp. TopN queries in PHP)
16 dimension INT(4) NOT NULL,
17 raterpage INT(11) NOT NULL,
18 rateepage INT(11) NOT NULL,
19 ratingvalue FLOAT NOT NULL,
20 rateeversion INT(11) NOT NULL,
21 isPrivate ENUM('yes','no'),
22 tstamp TIMESTAMP(14) NOT NULL,
23 PRIMARY KEY (dimension, raterpage, rateepage)
27 //define('RATING_STORAGE','WIKIPAGE'); // not fully supported yet
28 define('RATING_STORAGE','SQL'); // only for mysql yet.
29 // leave undefined for internal, slow php engine.
30 //define('RATING_EXTERNAL',PHPWIKI_DIR . 'suggest.exe');
32 class RatingsDb extends WikiDB {
34 function RatingsDb() {
36 $this->_dbi = &$request->_dbi;
37 $this->_backend = &$this->_dbi->_backend;
38 if (isa($this->_backend, 'WikiDB_backend_PearDB'))
39 $this->dbtype = "PearDB";
41 $this->dbtype = "ADODB";
42 $this->iter_class = "WikiDB_backend_".$this->dbtype."_generic_iter";
44 extract($this->_backend->_table_names);
45 if (empty($rating_tbl)) {
46 $rating_tbl = (!empty($GLOBALS['DBParams']['prefix'])
47 ? $GLOBALS['DBParams']['prefix'] : '') . 'rating';
48 $request->_dbi->_backend->_table_names['rating_tbl'] = $rating_tbl;
52 /// *************************************************************************************
54 // from Reini Urban's RateIt plugin
56 function addRating($rating, $userid, $pagename, $dimension) {
57 if (RATING_STORAGE == 'SQL') {
58 $page = $this->_dbi->getPage($pagename);
59 $current = $page->getCurrentRevision();
60 $rateeversion = $current->getVersion();
61 $this->sql_rate($userid, $pagename, $rateeversion, $dimension, $rating);
63 $this->metadata_set_rating($userid, $pagename, $dimension, $rating);
67 function deleteRating($userid=null, $pagename=null, $dimension=null) {
68 if (is_null($dimension)) $dimension = $this->dimension;
69 if (is_null($userid)) $userid = $this->userid;
70 if (is_null($pagename)) $pagename = $this->pagename;
71 if (RATING_STORAGE == 'SQL') {
72 $this->sql_delete_rating($userid, $pagename, $dimension);
74 $this->metadata_set_rating($userid, $pagename, $dimension, -1);
78 function getRating($userid=null, $pagename=null, $dimension=null) {
79 if (is_null($dimension)) $dimension = $this->dimension;
80 if (is_null($userid)) $userid = $this->userid;
81 if (is_null($pagename)) $pagename = $this->pagename;
82 if (RATING_STORAGE == 'SQL') {
83 $ratings_iter = $this->sql_get_rating($dimension, $userid, $pagename);
84 if ($rating = $ratings_iter->next()) {
85 return $rating['ratingvalue'];
89 return $this->metadata_get_rating($userid, $pagename, $dimension);
93 function getUsersRated($dimension=null, $orderby = null) {
94 if (is_null($dimension)) $dimension = $this->dimension;
95 if (is_null($userid)) $userid = $this->userid;
96 if (is_null($pagename)) $pagename = $this->pagename;
97 if (RATING_STORAGE == 'SQL') {
98 $ratings_iter = $this->sql_get_users_rated($dimension, $orderby);
99 if ($rating = $ratings_iter->next()) {
100 return $rating['ratingvalue'];
104 return $this->metadata_get_users_rated($dimension, $orderby);
112 * @param dimension The rating dimension id.
115 * If this is null (or left off), the search for ratings
116 * is not restricted by dimension.
118 * @param rater The page id of the rater, i.e. page doing the rating.
119 * This is a Wiki page id, often of a user page.
122 * If this is null (or left off), the search for ratings
123 * is not restricted by rater.
124 * TODO: Support an array
126 * @param ratee The page id of the ratee, i.e. page being rated.
127 * Example: "DudeWheresMyCar"
129 * If this is null (or left off), the search for ratings
130 * is not restricted by ratee.
132 * @param orderby An order-by clause with fields and (optionally) ASC
134 * Example: "ratingvalue DESC"
136 * If this is null (or left off), the search for ratings
137 * has no guaranteed order
139 * @param pageinfo The type of page that has its info returned (i.e.,
140 * 'pagename', 'hits', and 'pagedata') in the rows.
143 * If this is null (or left off), the info returned
144 * is for the 'ratee' page (i.e., thing being rated).
146 * @return DB iterator with results
148 function get_rating($dimension=null, $rater=null, $ratee=null,
149 $orderby = null, $pageinfo = "ratee") {
150 if (RATING_STORAGE == 'SQL') {
151 $ratings_iter = $this->sql_get_rating($dimension, $userid, $pagename);
152 if ($rating = $ratings_iter->next()) {
153 return $rating['ratingvalue'];
157 return $this->metadata_get_rating($userid, $pagename, $dimension);
160 return $this->_backend->get_rating($dimension, $rater, $ratee,
161 $orderby, $pageinfo);
165 function get_users_rated($dimension=null, $orderby = null) {
166 if (RATING_STORAGE == 'SQL') {
167 $ratings_iter = $this->sql_get_users_rated($dimension, $orderby);
168 if ($rating = $ratings_iter->next()) {
169 return $rating['ratingvalue'];
173 return $this->metadata_get_users_rated($dimension, $orderby);
176 return $this->_backend->get_users_rated($dimension, $orderby);
181 * Like get_rating(), but return a WikiDB_PageIterator
184 function get_rating_page($dimension=null, $rater=null, $ratee=null,
185 $orderby = null, $pageinfo = "ratee") {
186 $result = $this->_backend->get_rating_page($dimension, $rater, $ratee,
187 $orderby, $pageinfo);
188 return new WikiDB_PageIterator($this, $result);
194 * @param rater The page id of the rater, i.e. page doing the rating.
195 * This is a Wiki page id, often of a user page.
196 * @param ratee The page id of the ratee, i.e. page being rated.
197 * @param dimension The rating dimension id.
201 * @return true upon success
203 function delete_rating($rater, $ratee, $dimension) {
204 if (RATING_STORAGE == 'SQL') {
205 $this->sql_delete_rating($userid, $pagename, $dimension);
207 $this->metadata_set_rating($userid, $pagename, $dimension, -1);
210 return $this->_backend->delete_rating($rater, $ratee, $dimension);
217 * @param rater The page id of the rater, i.e. page doing the rating.
218 * This is a Wiki page id, often of a user page.
219 * @param ratee The page id of the ratee, i.e. page being rated.
220 * @param rateeversion The version of the ratee page.
221 * @param dimension The rating dimension id.
222 * @param rating The rating value (a float).
226 * @return true upon success
228 function rate($rater, $ratee, $rateeversion, $dimension, $rating) {
229 if (RATING_STORAGE == 'SQL') {
230 $page = $this->_dbi->getPage($pagename);
231 $current = $page->getCurrentRevision();
232 $rateeversion = $current->getVersion();
233 $this->sql_rate($userid, $pagename, $rateeversion, $dimension, $rating);
235 $this->metadata_set_rating($userid, $pagename, $dimension, $rating);
238 return $this->_backend->rate($rater, $ratee, $rateeversion, $dimension, $rating);
242 //function getUsersRated(){}
244 //*******************************************************************************
246 // Use wikilens/RatingsUser.php for the php methods.
249 // Currently we have to call the "suggest" CGI
250 // http://www-users.cs.umn.edu/~karypis/suggest/
251 // until we implement a simple recommendation engine.
252 // Note that "suggest" is only free for non-profit organizations.
253 // I am currently writing a binary CGI using suggest, which loads
255 function getPrediction($userid=null, $pagename=null, $dimension=null) {
256 if (is_null($dimension)) $dimension = $this->dimension;
257 if (is_null($userid)) $userid = $this->userid;
258 if (is_null($pagename)) $pagename = $this->pagename;
259 $dbi = &$this->_dbi->_backend;
260 if (isset($pagename))
261 $page = $dbi->_get_pageid($pagename);
264 $user = $dbi->_get_pageid($userid);
269 if (defined('RATING_EXTERNAL') and RATING_EXTERNAL) {
270 // how call suggest.exe? as CGI or natively
271 //$rating = HTML::Raw("<!--#include virtual=".RATING_ENGINE." -->");
272 $args = "-u$user -p$page -malpha"; // --top 10
273 if (isset($dimension))
274 $args .= " -d$dimension";
275 $rating = passthru(RATING_EXTERNAL . " $args");
277 $rating = $this->php_prediction($userid, $pagename, $dimension);
283 * TODO: slow item-based recommendation engine, similar to suggest RType=2.
284 * Only the SUGGEST_EstimateAlpha part
285 * Take wikilens/RatingsUser.php for the php methods.
287 function php_prediction($userid=null, $pagename=null, $dimension=null) {
288 if (is_null($dimension)) $dimension = $this->dimension;
289 if (is_null($userid)) $userid = $this->userid;
290 if (is_null($pagename)) $pagename = $this->pagename;
291 if (RATING_STORAGE == 'SQL') {
299 function getNumUsers($pagename=null, $dimension=null) {
300 if (is_null($dimension)) $dimension = $this->dimension;
301 if (is_null($pagename)) $pagename = $this->pagename;
302 if (RATING_STORAGE == 'SQL') {
303 $ratings_iter = $this->sql_get_rating($dimension, null, $pagename,
305 return $ratings_iter->count();
307 $page = $this->_dbi->getPage($pagename);
308 $data = $page->get('rating');
309 if (!empty($data[$dimension]))
310 return count($data[$dimension]);
315 // TODO: metadata method
316 function getAvg($pagename=null, $dimension=null) {
317 if (is_null($dimension)) $dimension = $this->dimension;
318 if (is_null($pagename)) $pagename = $this->pagename;
319 if (RATING_STORAGE == 'SQL') {
320 $dbi = &$this->_dbi->_backend;
322 if (isset($pagename)) {
323 $raterid = $dbi->_get_pageid($pagename, true);
324 $where .= " AND raterpage=$raterid";
326 if (isset($dimension)) {
327 $where .= " AND dimension=$dimension";
329 //$dbh = &$this->_dbi;
330 extract($dbi->_table_names);
331 $query = "SELECT AVG(ratingvalue) as avg"
332 . " FROM $rating_tbl r, $page_tbl p "
333 . $where. " GROUP BY raterpage";
334 $result = $dbi->_dbh->query($query);
335 $iter = new $this->iter_class($this,$result);
336 $row = $iter->next();
342 //*******************************************************************************
347 * @param dimension The rating dimension id.
350 * If this is null (or left off), the search for ratings
351 * is not restricted by dimension.
353 * @param rater The page id of the rater, i.e. page doing the rating.
354 * This is a Wiki page id, often of a user page.
357 * If this is null (or left off), the search for ratings
358 * is not restricted by rater.
359 * TODO: Support an array
361 * @param ratee The page id of the ratee, i.e. page being rated.
362 * Example: "DudeWheresMyCar"
364 * If this is null (or left off), the search for ratings
365 * is not restricted by ratee.
366 * TODO: Support an array
368 * @param orderby An order-by clause with fields and (optionally) ASC
370 * Example: "ratingvalue DESC"
372 * If this is null (or left off), the search for ratings
373 * has no guaranteed order
375 * @param pageinfo The type of page that has its info returned (i.e.,
376 * 'pagename', 'hits', and 'pagedata') in the rows.
379 * If this is null (or left off), the info returned
380 * is for the 'ratee' page (i.e., thing being rated).
382 * @return DB iterator with results
384 function sql_get_rating($dimension=null, $rater=null, $ratee=null,
385 $orderby=null, $pageinfo = "ratee") {
386 if (empty($dimension)) $dimension=null;
387 $result = $this->_sql_get_rating_result($dimension, $rater, $ratee, $orderby, $pageinfo);
388 return new $this->iter_class($this, $result);
391 function sql_get_users_rated($dimension=null, $orderby=null) {
392 if (empty($dimension)) $dimension=null;
393 $result = $this->_sql_get_rating_result($dimension, null, null, $orderby, "rater");
394 return new $this->iter_class($this, $result);
399 * @return result ressource, suitable to the iterator
401 function _sql_get_rating_result($dimension=null, $rater=null, $ratee=null,
402 $orderby=null, $pageinfo = "ratee") {
403 // pageinfo must be 'rater' or 'ratee'
404 if (($pageinfo != "ratee") && ($pageinfo != "rater"))
407 $dbi = &$this->_dbi->_backend;
408 //$dbh = &$this->_dbi;
409 extract($dbi->_table_names);
410 $where = "WHERE r." . $pageinfo . "page = p.id";
411 if (isset($dimension)) {
412 $where .= " AND dimension=$dimension";
415 $raterid = $dbi->_get_pageid($rater, true);
416 $where .= " AND raterpage=$raterid";
419 $rateeid = $dbi->_get_pageid($ratee, true);
420 $where .= " AND rateepage=$rateeid";
423 if (isset($orderby)) {
424 $orderbyStr = " ORDER BY " . $orderby;
426 if (isset($rater) or isset($ratee)) $what = '*';
427 // same as _get_users_rated_result()
428 else $what = 'DISTINCT p.pagename';
430 $query = "SELECT $what"
431 . " FROM $rating_tbl r, $page_tbl p "
435 $result = $dbi->_dbh->query($query);
442 * @param rater The page id of the rater, i.e. page doing the rating.
443 * This is a Wiki page id, often of a user page.
444 * @param ratee The page id of the ratee, i.e. page being rated.
445 * @param dimension The rating dimension id.
449 * @return true upon success
451 function sql_delete_rating($rater, $ratee, $dimension) {
452 //$dbh = &$this->_dbi;
453 $dbi = &$this->_dbi->_backend;
454 extract($dbi->_table_names);
457 $raterid = $dbi->_get_pageid($rater, true);
458 $rateeid = $dbi->_get_pageid($ratee, true);
459 $where = "WHERE raterpage=$raterid and rateepage=$rateeid";
460 if (isset($dimension)) {
461 $where .= " AND dimension=$dimension";
463 $dbi->_dbh->query("DELETE FROM $rating_tbl $where");
471 * @param rater The page id of the rater, i.e. page doing the rating.
472 * This is a Wiki page id, often of a user page.
473 * @param ratee The page id of the ratee, i.e. page being rated.
474 * @param rateeversion The version of the ratee page.
475 * @param dimension The rating dimension id.
476 * @param rating The rating value (a float).
480 * @return true upon success
482 // ($this->userid, $this->pagename, $this->dimension, $rating);
483 function sql_rate($rater, $ratee, $rateeversion, $dimension, $rating) {
484 $dbi = &$this->_dbi->_backend;
485 extract($dbi->_table_names);
486 if (empty($rating_tbl))
487 $rating_tbl = $this->_dbi->getParam('prefix') . 'rating';
490 $raterid = $dbi->_get_pageid($rater, true);
491 $rateeid = $dbi->_get_pageid($ratee, true);
494 $where = "WHERE raterpage='$raterid' AND rateepage='$rateeid'";
495 if (isset($dimension)) $where .= " AND dimension='$dimension'";
496 // atomic transaction:
497 $dbi->_dbh->query("UPDATE $rating_tbl SET ratingvalue='$rating', rateeversion='$rateeversion' $where");
500 $dbi->_dbh->query("DELETE FROM $rating_tbl $where");
501 // NOTE: Leave tstamp off the insert, and MySQL automatically updates it (only if MySQL is used)
502 $dbi->_dbh->query("INSERT INTO $rating_tbl (dimension, raterpage, rateepage, ratingvalue, rateeversion) VALUES ('$dimension', $raterid, $rateeid, '$rating', '$rateeversion')");
508 function metadata_get_rating($userid, $pagename, $dimension) {
509 $page = $this->_dbi->getPage($pagename);
510 $data = $page->get('rating');
511 if (!empty($data[$dimension][$userid]))
512 return (float)$data[$dimension][$userid];
517 function metadata_set_rating($userid, $pagename, $dimension, $rating = -1) {
518 $page = $this->_dbi->getPage($pagename);
519 $data = $page->get('rating');
521 unset($data[$dimension][$userid]);
523 if (empty($data[$dimension][$userid]))
524 $data[$dimension] = array($userid => (float)$rating);
526 $data[$dimension][$userid] = $rating;
528 $page->set('rating',$data);
534 class RatingsDB_backend_PearDB
535 extends WikiDB_backend_PearDB {
536 function get_rating($dimension=null, $rater=null, $ratee=null,
537 $orderby=null, $pageinfo = "ratee") {
538 $result = $this->_get_rating_result(
539 $dimension, $rater, $ratee, $orderby, $pageinfo);
540 return new WikiDB_backend_PearDB_generic_iter($this, $result);
543 function get_users_rated($dimension=null, $orderby=null) {
544 $result = $this->_get_users_rated_result(
545 $dimension, $orderby);
546 return new WikiDB_backend_PearDB_generic_iter($this, $result);
549 function get_rating_page($dimension=null, $rater=null, $ratee=null,
550 $orderby=null, $pageinfo = "ratee") {
551 $result = $this->_get_rating_result(
552 $dimension, $rater, $ratee, $orderby, $pageinfo);
553 return new WikiDB_backend_PearDB_iter($this, $result);
556 function _get_rating_result($dimension=null, $rater=null, $ratee=null,
557 $orderby=null, $pageinfo = "ratee") {
558 // pageinfo must be 'rater' or 'ratee'
559 if (($pageinfo != "ratee") && ($pageinfo != "rater"))
563 extract($this->_table_names);
565 $where = "WHERE r." . $pageinfo . "page = p.id";
566 if (isset($dimension)) {
567 $where .= " AND dimension=$dimension";
570 $raterid = $this->_get_pageid($rater, true);
571 $where .= " AND raterpage=$raterid";
574 if(is_array($ratee)){
576 for($i = 0; $i < count($ratee); $i++){
577 $rateeid = $this->_get_pageid($ratee[$i], true);
578 $where .= "rateepage=$rateeid";
579 if($i != (count($ratee) - 1)){
585 $rateeid = $this->_get_pageid($ratee, true);
586 $where .= " AND rateepage=$rateeid";
591 if (isset($orderby)) {
592 $orderbyStr = " ORDER BY " . $orderby;
596 . " FROM $rating_tbl r, $page_tbl p "
600 $result = $dbh->query($query);
605 function _get_users_rated_result($dimension=null, $orderby=null) {
607 extract($this->_table_names);
609 $where = "WHERE p.id=r.raterpage";
610 if (isset($dimension)) {
611 $where .= " AND dimension=$dimension";
614 if (isset($orderby)) {
615 $orderbyStr = " ORDER BY " . $orderby;
618 $query = "SELECT DISTINCT p.pagename"
619 . " FROM $rating_tbl r, $page_tbl p "
623 $result = $dbh->query($query);
627 function delete_rating($rater, $ratee, $dimension) {
629 extract($this->_table_names);
632 $raterid = $this->_get_pageid($rater, true);
633 $rateeid = $this->_get_pageid($ratee, true);
635 $dbh->query("DELETE FROM $rating_tbl WHERE raterpage=$raterid and rateepage=$rateeid and dimension=$dimension");
640 function rate($rater, $ratee, $rateeversion, $dimension, $rating, $isPrivate = 'no') {
642 extract($this->_table_names);
645 $raterid = $this->_get_pageid($rater, true);
646 $rateeid = $this->_get_pageid($ratee, true);
648 $dbh->query("DELETE FROM $rating_tbl WHERE raterpage=$raterid and rateepage=$rateeid and dimension=$dimension and isPrivate='$isPrivate'");
649 // NOTE: Leave tstamp off the insert, and MySQL automatically updates it
650 $dbh->query("INSERT INTO $rating_tbl (dimension, raterpage, rateepage, ratingvalue, rateeversion, isPrivate) VALUES ($dimension, $raterid, $rateeid, $rating, $rateeversion, '$isPrivate')");
657 // $Log: not supported by cvs2svn $
658 // Revision 1.1 2004/06/18 14:42:17 rurban
659 // added wikilens libs (not yet merged good enough, some work for DanFr)
666 // c-hanging-comment-ender-p: nil
667 // indent-tabs-mode: nil