2 rcs_id('$Id: RatingsDb.php,v 1.5 2004-07-08 13:50:33 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 {
35 function RatingsDb() {
37 $this->_dbi = &$request->_dbi;
38 $this->_backend = &$this->_dbi->_backend;
39 if (isa($this->_backend, 'WikiDB_backend_PearDB'))
40 $this->dbtype = "PearDB";
42 $this->dbtype = "ADODB";
43 $this->iter_class = "WikiDB_backend_".$this->dbtype."_generic_iter";
45 extract($this->_backend->_table_names);
46 if (empty($rating_tbl)) {
47 $rating_tbl = (!empty($GLOBALS['DBParams']['prefix'])
48 ? $GLOBALS['DBParams']['prefix'] : '') . 'rating';
49 $request->_dbi->_backend->_table_names['rating_tbl'] = $rating_tbl;
53 // this is a singleton. It ensures there is only 1 ratingsDB.
54 function &getTheRatingsDb(){
55 static $_theRatingsDb;
57 if (!isset($_theRatingsDb)){
58 $_theRatingsDb = new RatingsDb();
60 //echo "rating db is $_theRatingsDb";
61 return $_theRatingsDb;
65 /// *************************************************************************************
67 // from Reini Urban's RateIt plugin
68 function addRating($rating, $userid, $pagename, $dimension) {
69 if (RATING_STORAGE == 'SQL') {
70 $page = $this->_dbi->getPage($pagename);
71 $current = $page->getCurrentRevision();
72 $rateeversion = $current->getVersion();
73 $this->sql_rate($userid, $pagename, $rateeversion, $dimension, $rating);
75 $this->metadata_set_rating($userid, $pagename, $dimension, $rating);
79 function deleteRating($userid=null, $pagename=null, $dimension=null) {
80 if (is_null($dimension)) $dimension = $this->dimension;
81 if (is_null($userid)) $userid = $this->userid;
82 if (is_null($pagename)) $pagename = $this->pagename;
83 if (RATING_STORAGE == 'SQL') {
84 $this->sql_delete_rating($userid, $pagename, $dimension);
86 $this->metadata_set_rating($userid, $pagename, $dimension, -1);
90 function getRating($userid=null, $pagename=null, $dimension=null) {
91 if (RATING_STORAGE == 'SQL') {
92 $ratings_iter = $this->sql_get_rating($dimension, $userid, $pagename);
93 if ($rating = $ratings_iter->next()) {
94 return $rating['ratingvalue'];
98 return $this->metadata_get_rating($userid, $pagename, $dimension);
102 function getUsersRated($dimension=null, $orderby = null) {
103 if (is_null($dimension)) $dimension = $this->dimension;
104 if (is_null($userid)) $userid = $this->userid;
105 if (is_null($pagename)) $pagename = $this->pagename;
106 if (RATING_STORAGE == 'SQL') {
107 $ratings_iter = $this->sql_get_users_rated($dimension, $orderby);
108 if ($rating = $ratings_iter->next()) {
109 return $rating['ratingvalue'];
113 return $this->metadata_get_users_rated($dimension, $orderby);
121 * @param dimension The rating dimension id.
124 * If this is null (or left off), the search for ratings
125 * is not restricted by dimension.
127 * @param rater The page id of the rater, i.e. page doing the rating.
128 * This is a Wiki page id, often of a user page.
131 * If this is null (or left off), the search for ratings
132 * is not restricted by rater.
133 * TODO: Support an array
135 * @param ratee The page id of the ratee, i.e. page being rated.
136 * Example: "DudeWheresMyCar"
138 * If this is null (or left off), the search for ratings
139 * is not restricted by ratee.
141 * @param orderby An order-by clause with fields and (optionally) ASC
143 * Example: "ratingvalue DESC"
145 * If this is null (or left off), the search for ratings
146 * has no guaranteed order
148 * @param pageinfo The type of page that has its info returned (i.e.,
149 * 'pagename', 'hits', and 'pagedata') in the rows.
152 * If this is null (or left off), the info returned
153 * is for the 'ratee' page (i.e., thing being rated).
155 * @return DB iterator with results
157 function get_rating($dimension=null, $rater=null, $ratee=null,
158 $orderby = null, $pageinfo = "ratee") {
159 if (RATING_STORAGE == 'SQL') {
160 $ratings_iter = $this->sql_get_rating($dimension, $rater, $pagename);
161 if ($rating = $ratings_iter->next()) {
162 return $rating['ratingvalue'];
165 // return $ratings_iter;
167 return $this->metadata_get_rating($rater, $pagename, $dimension);
170 return $this->_backend->get_rating($dimension, $rater, $ratee,
171 $orderby, $pageinfo);
175 function get_users_rated($dimension=null, $orderby = null) {
176 if (RATING_STORAGE == 'SQL') {
177 $ratings_iter = $this->sql_get_users_rated($dimension, $orderby);
178 if ($rating = $ratings_iter->next()) {
179 return $rating['ratingvalue'];
183 return $this->metadata_get_users_rated($dimension, $orderby);
186 return $this->_backend->get_users_rated($dimension, $orderby);
191 * Like get_rating(), but return a WikiDB_PageIterator
194 function get_rating_page($dimension=null, $rater=null, $ratee=null,
195 $orderby = null, $pageinfo = "ratee") {
196 $result = $this->_backend->get_rating_page($dimension, $rater, $ratee,
197 $orderby, $pageinfo);
198 return new WikiDB_PageIterator($this, $result);
204 * @param rater The page id of the rater, i.e. page doing the rating.
205 * This is a Wiki page id, often of a user page.
206 * @param ratee The page id of the ratee, i.e. page being rated.
207 * @param dimension The rating dimension id.
211 * @return true upon success
213 function delete_rating($rater, $ratee, $dimension) {
214 if (RATING_STORAGE == 'SQL') {
215 $this->sql_delete_rating($userid, $pagename, $dimension);
217 $this->metadata_set_rating($userid, $pagename, $dimension, -1);
220 return $this->_backend->delete_rating($rater, $ratee, $dimension);
227 * @param rater The page id of the rater, i.e. page doing the rating.
228 * This is a Wiki page id, often of a user page.
229 * @param ratee The page id of the ratee, i.e. page being rated.
230 * @param rateeversion The version of the ratee page.
231 * @param dimension The rating dimension id.
232 * @param rating The rating value (a float).
236 * @return true upon success
238 function rate($rater, $ratee, $rateeversion, $dimension, $rating) {
239 if (RATING_STORAGE == 'SQL') {
240 $page = $this->_dbi->getPage($pagename);
241 $current = $page->getCurrentRevision();
242 $rateeversion = $current->getVersion();
243 $this->sql_rate($userid, $pagename, $rateeversion, $dimension, $rating);
245 $this->metadata_set_rating($userid, $pagename, $dimension, $rating);
248 return $this->_backend->rate($rater, $ratee, $rateeversion, $dimension, $rating);
252 //function getUsersRated(){}
254 //*******************************************************************************
256 // Use wikilens/RatingsUser.php for the php methods.
259 // Currently we have to call the "suggest" CGI
260 // http://www-users.cs.umn.edu/~karypis/suggest/
261 // until we implement a simple recommendation engine.
262 // Note that "suggest" is only free for non-profit organizations.
263 // I am currently writing a binary CGI using suggest, which loads
265 function getPrediction($userid=null, $pagename=null, $dimension=null) {
266 if (is_null($dimension)) $dimension = $this->dimension;
267 if (is_null($userid)) $userid = $this->userid;
268 if (is_null($pagename)) $pagename = $this->pagename;
270 if (RATING_STORAGE == 'SQL') {
271 $dbi = &$this->_dbi->_backend;
272 if (isset($pagename))
273 $page = $dbi->_get_pageid($pagename);
277 $user = $dbi->_get_pageid($userid);
281 if (defined('RATING_EXTERNAL') and RATING_EXTERNAL) {
282 // how call suggest.exe? as CGI or natively
283 //$rating = HTML::Raw("<!--#include virtual=".RATING_ENGINE." -->");
284 $args = "-u$user -p$page -malpha"; // --top 10
285 if (isset($dimension))
286 $args .= " -d$dimension";
287 $rating = passthru(RATING_EXTERNAL . " $args");
289 $rating = $this->php_prediction($userid, $pagename, $dimension);
295 * TODO: slow item-based recommendation engine, similar to suggest RType=2.
296 * Only the SUGGEST_EstimateAlpha part
297 * Take wikilens/RatingsUser.php for the php methods.
299 function php_prediction($userid=null, $pagename=null, $dimension=null) {
300 if (is_null($dimension)) $dimension = $this->dimension;
301 if (is_null($userid)) $userid = $this->userid;
302 if (is_null($pagename)) $pagename = $this->pagename;
303 if (RATING_STORAGE == 'SQL') {
311 function getNumUsers($pagename=null, $dimension=null) {
312 if (is_null($dimension)) $dimension = $this->dimension;
313 if (is_null($pagename)) $pagename = $this->pagename;
314 if (RATING_STORAGE == 'SQL') {
315 $ratings_iter = $this->sql_get_rating($dimension, null, $pagename,
317 return $ratings_iter->count();
319 $page = $this->_dbi->getPage($pagename);
320 $data = $page->get('rating');
321 if (!empty($data[$dimension]))
322 return count($data[$dimension]);
327 // TODO: metadata method
328 function getAvg($pagename=null, $dimension=null) {
329 if (is_null($dimension)) $dimension = $this->dimension;
330 if (is_null($pagename)) $pagename = $this->pagename;
331 if (RATING_STORAGE == 'SQL') {
332 $dbi = &$this->_dbi->_backend;
334 if (isset($pagename)) {
335 $raterid = $dbi->_get_pageid($pagename, true);
336 $where .= " AND raterpage=$raterid";
338 if (isset($dimension)) {
339 $where .= " AND dimension=$dimension";
341 //$dbh = &$this->_dbi;
342 extract($dbi->_table_names);
343 $query = "SELECT AVG(ratingvalue) as avg"
344 . " FROM $rating_tbl r, $page_tbl p "
345 . $where. " GROUP BY raterpage";
346 $result = $dbi->_dbh->query($query);
347 $iter = new $this->iter_class($this,$result);
348 $row = $iter->next();
354 //*******************************************************************************
359 * @param dimension The rating dimension id.
362 * If this is null (or left off), the search for ratings
363 * is not restricted by dimension.
365 * @param rater The page id of the rater, i.e. page doing the rating.
366 * This is a Wiki page id, often of a user page.
369 * If this is null (or left off), the search for ratings
370 * is not restricted by rater.
371 * TODO: Support an array
373 * @param ratee The page id of the ratee, i.e. page being rated.
374 * Example: "DudeWheresMyCar"
376 * If this is null (or left off), the search for ratings
377 * is not restricted by ratee.
378 * TODO: Support an array
380 * @param orderby An order-by clause with fields and (optionally) ASC
382 * Example: "ratingvalue DESC"
384 * If this is null (or left off), the search for ratings
385 * has no guaranteed order
387 * @param pageinfo The type of page that has its info returned (i.e.,
388 * 'pagename', 'hits', and 'pagedata') in the rows.
391 * If this is null (or left off), the info returned
392 * is for the 'ratee' page (i.e., thing being rated).
394 * @return DB iterator with results
396 function sql_get_rating($dimension=null, $rater=null, $ratee=null,
397 $orderby=null, $pageinfo = "ratee") {
398 if (empty($dimension)) $dimension=null;
399 $result = $this->_sql_get_rating_result($dimension, $rater, $ratee, $orderby, $pageinfo);
400 return new $this->iter_class($this, $result);
403 function sql_get_users_rated($dimension=null, $orderby=null) {
404 if (empty($dimension)) $dimension=null;
405 $result = $this->_sql_get_rating_result($dimension, null, null, $orderby, "rater");
406 return new $this->iter_class($this, $result);
411 * @return result ressource, suitable to the iterator
413 function _sql_get_rating_result($dimension=null, $rater=null, $ratee=null,
414 $orderby=null, $pageinfo = "ratee") {
415 // pageinfo must be 'rater' or 'ratee'
416 if (($pageinfo != "ratee") && ($pageinfo != "rater"))
418 $dbi = &$this->_dbi->_backend;
419 //$dbh = &$this->_dbi;
420 extract($dbi->_table_names);
421 $where = "WHERE r." . $pageinfo . "page = p.id";
422 if (isset($dimension)) {
423 $where .= " AND dimension=$dimension";
426 $raterid = $dbi->_get_pageid($rater, true);
427 $where .= " AND raterpage=$raterid";
430 if(is_array($ratee)){
432 for($i = 0; $i < count($ratee); $i++){
433 $rateeid = $dbi->_get_pageid($ratee[$i], true);
434 $where .= "rateepage=$rateeid";
435 if($i != (count($ratee) - 1)){
441 $rateeid = $dbi->_get_pageid($ratee, true);
442 $where .= " AND rateepage=$rateeid";
446 if (isset($orderby)) {
447 $orderbyStr = " ORDER BY " . $orderby;
449 if (isset($rater) or isset($ratee)) $what = '*';
450 // same as _get_users_rated_result()
451 else $what = 'DISTINCT p.pagename';
453 $query = "SELECT $what"
454 . " FROM $rating_tbl r, $page_tbl p "
457 $result = $dbi->_dbh->query($query);
464 * @param rater The page id of the rater, i.e. page doing the rating.
465 * This is a Wiki page id, often of a user page.
466 * @param ratee The page id of the ratee, i.e. page being rated.
467 * @param dimension The rating dimension id.
471 * @return true upon success
473 function sql_delete_rating($rater, $ratee, $dimension) {
474 //$dbh = &$this->_dbi;
475 $dbi = &$this->_dbi->_backend;
476 extract($dbi->_table_names);
479 $raterid = $dbi->_get_pageid($rater, true);
480 $rateeid = $dbi->_get_pageid($ratee, true);
481 $where = "WHERE raterpage=$raterid and rateepage=$rateeid";
482 if (isset($dimension)) {
483 $where .= " AND dimension=$dimension";
485 $dbi->_dbh->query("DELETE FROM $rating_tbl $where");
493 * @param rater The page id of the rater, i.e. page doing the rating.
494 * This is a Wiki page id, often of a user page.
495 * @param ratee The page id of the ratee, i.e. page being rated.
496 * @param rateeversion The version of the ratee page.
497 * @param dimension The rating dimension id.
498 * @param rating The rating value (a float).
502 * @return true upon success
504 // ($this->userid, $this->pagename, $this->dimension, $rating);
505 function sql_rate($rater, $ratee, $rateeversion, $dimension, $rating) {
506 $dbi = &$this->_dbi->_backend;
507 extract($dbi->_table_names);
508 if (empty($rating_tbl))
509 $rating_tbl = $this->_dbi->getParam('prefix') . 'rating';
512 $raterid = $dbi->_get_pageid($rater, true);
513 $rateeid = $dbi->_get_pageid($ratee, true);
516 //we changed back to delete and insert because update didn't work if it was a new rating
518 $dbi->_dbh->query("DELETE from $rating_tbl WHERE dimension=$dimension AND raterpage=$raterid AND rateepage=$rateeid");
519 $where = "WHERE raterpage='$raterid' AND rateepage='$rateeid'";
521 $insert = "INSERT INTO $rating_tbl (dimension, raterpage, rateepage, ratingvalue, rateeversion) VALUES ('$dimension', $raterid, $rateeid, '$rating', '$rateeversion')";
522 $dbi->_dbh->query($insert);
528 function metadata_get_rating($userid, $pagename, $dimension) {
529 $page = $this->_dbi->getPage($pagename);
530 $data = $page->get('rating');
531 if (!empty($data[$dimension][$userid]))
532 return (float)$data[$dimension][$userid];
537 function metadata_set_rating($userid, $pagename, $dimension, $rating = -1) {
538 $page = $this->_dbi->getPage($pagename);
539 $data = $page->get('rating');
541 unset($data[$dimension][$userid]);
543 if (empty($data[$dimension][$userid]))
544 $data[$dimension] = array($userid => (float)$rating);
546 $data[$dimension][$userid] = $rating;
548 $page->set('rating',$data);
554 class RatingsDB_backend_PearDB
555 extends WikiDB_backend_PearDB {
556 function get_rating($dimension=null, $rater=null, $ratee=null,
557 $orderby=null, $pageinfo = "ratee") {
558 $result = $this->_get_rating_result(
559 $dimension, $rater, $ratee, $orderby, $pageinfo);
560 return new WikiDB_backend_PearDB_generic_iter($this, $result);
563 function get_users_rated($dimension=null, $orderby=null) {
564 $result = $this->_get_users_rated_result(
565 $dimension, $orderby);
566 return new WikiDB_backend_PearDB_generic_iter($this, $result);
569 function get_rating_page($dimension=null, $rater=null, $ratee=null,
570 $orderby=null, $pageinfo = "ratee") {
571 $result = $this->_get_rating_result(
572 $dimension, $rater, $ratee, $orderby, $pageinfo);
573 return new WikiDB_backend_PearDB_iter($this, $result);
576 function _get_rating_result($dimension=null, $rater=null, $ratee=null,
577 $orderby=null, $pageinfo = "ratee") {
578 // pageinfo must be 'rater' or 'ratee'
579 if (($pageinfo != "ratee") && ($pageinfo != "rater"))
583 extract($this->_table_names);
585 $where = "WHERE r." . $pageinfo . "page = p.id";
586 if (isset($dimension)) {
587 $where .= " AND dimension=$dimension";
590 $raterid = $this->_get_pageid($rater, true);
591 $where .= " AND raterpage=$raterid";
594 if(is_array($ratee)){
596 for($i = 0; $i < count($ratee); $i++){
597 $rateeid = $this->_get_pageid($ratee[$i], true);
598 $where .= "rateepage=$rateeid";
599 if($i != (count($ratee) - 1)){
605 $rateeid = $this->_get_pageid($ratee, true);
606 $where .= " AND rateepage=$rateeid";
611 if (isset($orderby)) {
612 $orderbyStr = " ORDER BY " . $orderby;
616 . " FROM $rating_tbl r, $page_tbl p "
620 $result = $dbh->query($query);
625 function _get_users_rated_result($dimension=null, $orderby=null) {
627 extract($this->_table_names);
629 $where = "WHERE p.id=r.raterpage";
630 if (isset($dimension)) {
631 $where .= " AND dimension=$dimension";
634 if (isset($orderby)) {
635 $orderbyStr = " ORDER BY " . $orderby;
638 $query = "SELECT DISTINCT p.pagename"
639 . " FROM $rating_tbl r, $page_tbl p "
643 $result = $dbh->query($query);
647 function delete_rating($rater, $ratee, $dimension) {
649 extract($this->_table_names);
652 $raterid = $this->_get_pageid($rater, true);
653 $rateeid = $this->_get_pageid($ratee, true);
655 $dbh->query("DELETE FROM $rating_tbl WHERE raterpage=$raterid and rateepage=$rateeid and dimension=$dimension");
660 function rate($rater, $ratee, $rateeversion, $dimension, $rating, $isPrivate = 'no') {
662 extract($this->_table_names);
665 $raterid = $this->_get_pageid($rater, true);
666 $rateeid = $this->_get_pageid($ratee, true);
668 $dbh->query("DELETE FROM $rating_tbl WHERE raterpage=$raterid and rateepage=$rateeid and dimension=$dimension and isPrivate='$isPrivate'");
669 // NOTE: Leave tstamp off the insert, and MySQL automatically updates it
670 $dbh->query("INSERT INTO $rating_tbl (dimension, raterpage, rateepage, ratingvalue, rateeversion, isPrivate) VALUES ($dimension, $raterid, $rateeid, $rating, $rateeversion, '$isPrivate')");
677 // $Log: not supported by cvs2svn $
678 // Revision 1.4 2004/07/07 19:47:36 dfrankow
679 // Fixes to get PreferencesApp to work-- thanks syilek
681 // Revision 1.3 2004/06/30 20:05:36 dfrankow
682 // + Add getTheRatingsDb() singleton.
683 // + Remove defaulting of dimension, userid, pagename in getRating--
685 // + Fix typo in get_rating.
686 // + Fix _sql_get_rating_result
687 // + Fix sql_rate(). It's now not transactionally safe yet, but at least it
690 // Revision 1.2 2004/06/19 10:22:41 rurban
691 // outcomment the pear specific methods to let all pages load
693 // Revision 1.1 2004/06/18 14:42:17 rurban
694 // added wikilens libs (not yet merged good enough, some work for DanFr)
701 // c-hanging-comment-ender-p: nil
702 // indent-tabs-mode: nil