2 rcs_id('$Id: RatingsDb.php,v 1.3 2004-06-30 20:05:36 dfrankow 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;
269 $dbi = &$this->_dbi->_backend;
270 if (isset($pagename))
271 $page = $dbi->_get_pageid($pagename);
274 $user = $dbi->_get_pageid($userid);
279 if (defined('RATING_EXTERNAL') and RATING_EXTERNAL) {
280 // how call suggest.exe? as CGI or natively
281 //$rating = HTML::Raw("<!--#include virtual=".RATING_ENGINE." -->");
282 $args = "-u$user -p$page -malpha"; // --top 10
283 if (isset($dimension))
284 $args .= " -d$dimension";
285 $rating = passthru(RATING_EXTERNAL . " $args");
287 $rating = $this->php_prediction($userid, $pagename, $dimension);
293 * TODO: slow item-based recommendation engine, similar to suggest RType=2.
294 * Only the SUGGEST_EstimateAlpha part
295 * Take wikilens/RatingsUser.php for the php methods.
297 function php_prediction($userid=null, $pagename=null, $dimension=null) {
298 if (is_null($dimension)) $dimension = $this->dimension;
299 if (is_null($userid)) $userid = $this->userid;
300 if (is_null($pagename)) $pagename = $this->pagename;
301 if (RATING_STORAGE == 'SQL') {
309 function getNumUsers($pagename=null, $dimension=null) {
310 if (is_null($dimension)) $dimension = $this->dimension;
311 if (is_null($pagename)) $pagename = $this->pagename;
312 if (RATING_STORAGE == 'SQL') {
313 $ratings_iter = $this->sql_get_rating($dimension, null, $pagename,
315 return $ratings_iter->count();
317 $page = $this->_dbi->getPage($pagename);
318 $data = $page->get('rating');
319 if (!empty($data[$dimension]))
320 return count($data[$dimension]);
325 // TODO: metadata method
326 function getAvg($pagename=null, $dimension=null) {
327 if (is_null($dimension)) $dimension = $this->dimension;
328 if (is_null($pagename)) $pagename = $this->pagename;
329 if (RATING_STORAGE == 'SQL') {
330 $dbi = &$this->_dbi->_backend;
332 if (isset($pagename)) {
333 $raterid = $dbi->_get_pageid($pagename, true);
334 $where .= " AND raterpage=$raterid";
336 if (isset($dimension)) {
337 $where .= " AND dimension=$dimension";
339 //$dbh = &$this->_dbi;
340 extract($dbi->_table_names);
341 $query = "SELECT AVG(ratingvalue) as avg"
342 . " FROM $rating_tbl r, $page_tbl p "
343 . $where. " GROUP BY raterpage";
344 $result = $dbi->_dbh->query($query);
345 $iter = new $this->iter_class($this,$result);
346 $row = $iter->next();
352 //*******************************************************************************
357 * @param dimension The rating dimension id.
360 * If this is null (or left off), the search for ratings
361 * is not restricted by dimension.
363 * @param rater The page id of the rater, i.e. page doing the rating.
364 * This is a Wiki page id, often of a user page.
367 * If this is null (or left off), the search for ratings
368 * is not restricted by rater.
369 * TODO: Support an array
371 * @param ratee The page id of the ratee, i.e. page being rated.
372 * Example: "DudeWheresMyCar"
374 * If this is null (or left off), the search for ratings
375 * is not restricted by ratee.
376 * TODO: Support an array
378 * @param orderby An order-by clause with fields and (optionally) ASC
380 * Example: "ratingvalue DESC"
382 * If this is null (or left off), the search for ratings
383 * has no guaranteed order
385 * @param pageinfo The type of page that has its info returned (i.e.,
386 * 'pagename', 'hits', and 'pagedata') in the rows.
389 * If this is null (or left off), the info returned
390 * is for the 'ratee' page (i.e., thing being rated).
392 * @return DB iterator with results
394 function sql_get_rating($dimension=null, $rater=null, $ratee=null,
395 $orderby=null, $pageinfo = "ratee") {
396 if (empty($dimension)) $dimension=null;
397 $result = $this->_sql_get_rating_result($dimension, $rater, $ratee, $orderby, $pageinfo);
398 return new $this->iter_class($this, $result);
401 function sql_get_users_rated($dimension=null, $orderby=null) {
402 if (empty($dimension)) $dimension=null;
403 $result = $this->_sql_get_rating_result($dimension, null, null, $orderby, "rater");
404 return new $this->iter_class($this, $result);
409 * @return result ressource, suitable to the iterator
411 function _sql_get_rating_result($dimension=null, $rater=null, $ratee=null,
412 $orderby=null, $pageinfo = "ratee") {
413 // pageinfo must be 'rater' or 'ratee'
414 if (($pageinfo != "ratee") && ($pageinfo != "rater"))
416 $dbi = &$this->_dbi->_backend;
417 //$dbh = &$this->_dbi;
418 extract($dbi->_table_names);
419 $where = "WHERE r." . $pageinfo . "page = p.id";
420 if (isset($dimension)) {
421 $where .= " AND dimension=$dimension";
424 $raterid = $dbi->_get_pageid($rater, true);
425 $where .= " AND raterpage=$raterid";
428 $rateeid = $dbi->_get_pageid($ratee, true);
429 $where .= " AND rateepage=$rateeid";
432 if (isset($orderby)) {
433 $orderbyStr = " ORDER BY " . $orderby;
435 if (isset($rater) or isset($ratee)) $what = '*';
436 // same as _get_users_rated_result()
437 else $what = 'DISTINCT p.pagename, r.ratingvalue, r.dimension';
439 $query = "SELECT $what"
440 . " FROM $rating_tbl r, $page_tbl p "
443 $result = $dbi->_dbh->query($query);
450 * @param rater The page id of the rater, i.e. page doing the rating.
451 * This is a Wiki page id, often of a user page.
452 * @param ratee The page id of the ratee, i.e. page being rated.
453 * @param dimension The rating dimension id.
457 * @return true upon success
459 function sql_delete_rating($rater, $ratee, $dimension) {
460 //$dbh = &$this->_dbi;
461 $dbi = &$this->_dbi->_backend;
462 extract($dbi->_table_names);
465 $raterid = $dbi->_get_pageid($rater, true);
466 $rateeid = $dbi->_get_pageid($ratee, true);
467 $where = "WHERE raterpage=$raterid and rateepage=$rateeid";
468 if (isset($dimension)) {
469 $where .= " AND dimension=$dimension";
471 $dbi->_dbh->query("DELETE FROM $rating_tbl $where");
479 * @param rater The page id of the rater, i.e. page doing the rating.
480 * This is a Wiki page id, often of a user page.
481 * @param ratee The page id of the ratee, i.e. page being rated.
482 * @param rateeversion The version of the ratee page.
483 * @param dimension The rating dimension id.
484 * @param rating The rating value (a float).
488 * @return true upon success
490 // ($this->userid, $this->pagename, $this->dimension, $rating);
491 function sql_rate($rater, $ratee, $rateeversion, $dimension, $rating) {
492 $dbi = &$this->_dbi->_backend;
493 extract($dbi->_table_names);
494 if (empty($rating_tbl))
495 $rating_tbl = $this->_dbi->getParam('prefix') . 'rating';
498 $raterid = $dbi->_get_pageid($rater, true);
499 $rateeid = $dbi->_get_pageid($ratee, true);
502 //we changed back to delete and insert because update didn't work if it was a new rating
504 $dbi->_dbh->query("DELETE from $rating_tbl WHERE dimension=$dimension AND raterpage=$raterid AND rateepage=$rateeid");
505 $where = "WHERE raterpage='$raterid' AND rateepage='$rateeid'";
507 $insert = "INSERT INTO $rating_tbl (dimension, raterpage, rateepage, ratingvalue, rateeversion) VALUES ('$dimension', $raterid, $rateeid, '$rating', '$rateeversion')";
508 $dbi->_dbh->query($insert);
514 function metadata_get_rating($userid, $pagename, $dimension) {
515 $page = $this->_dbi->getPage($pagename);
516 $data = $page->get('rating');
517 if (!empty($data[$dimension][$userid]))
518 return (float)$data[$dimension][$userid];
523 function metadata_set_rating($userid, $pagename, $dimension, $rating = -1) {
524 $page = $this->_dbi->getPage($pagename);
525 $data = $page->get('rating');
527 unset($data[$dimension][$userid]);
529 if (empty($data[$dimension][$userid]))
530 $data[$dimension] = array($userid => (float)$rating);
532 $data[$dimension][$userid] = $rating;
534 $page->set('rating',$data);
540 class RatingsDB_backend_PearDB
541 extends WikiDB_backend_PearDB {
542 function get_rating($dimension=null, $rater=null, $ratee=null,
543 $orderby=null, $pageinfo = "ratee") {
544 $result = $this->_get_rating_result(
545 $dimension, $rater, $ratee, $orderby, $pageinfo);
546 return new WikiDB_backend_PearDB_generic_iter($this, $result);
549 function get_users_rated($dimension=null, $orderby=null) {
550 $result = $this->_get_users_rated_result(
551 $dimension, $orderby);
552 return new WikiDB_backend_PearDB_generic_iter($this, $result);
555 function get_rating_page($dimension=null, $rater=null, $ratee=null,
556 $orderby=null, $pageinfo = "ratee") {
557 $result = $this->_get_rating_result(
558 $dimension, $rater, $ratee, $orderby, $pageinfo);
559 return new WikiDB_backend_PearDB_iter($this, $result);
562 function _get_rating_result($dimension=null, $rater=null, $ratee=null,
563 $orderby=null, $pageinfo = "ratee") {
564 // pageinfo must be 'rater' or 'ratee'
565 if (($pageinfo != "ratee") && ($pageinfo != "rater"))
569 extract($this->_table_names);
571 $where = "WHERE r." . $pageinfo . "page = p.id";
572 if (isset($dimension)) {
573 $where .= " AND dimension=$dimension";
576 $raterid = $this->_get_pageid($rater, true);
577 $where .= " AND raterpage=$raterid";
580 if(is_array($ratee)){
582 for($i = 0; $i < count($ratee); $i++){
583 $rateeid = $this->_get_pageid($ratee[$i], true);
584 $where .= "rateepage=$rateeid";
585 if($i != (count($ratee) - 1)){
591 $rateeid = $this->_get_pageid($ratee, true);
592 $where .= " AND rateepage=$rateeid";
597 if (isset($orderby)) {
598 $orderbyStr = " ORDER BY " . $orderby;
602 . " FROM $rating_tbl r, $page_tbl p "
606 $result = $dbh->query($query);
611 function _get_users_rated_result($dimension=null, $orderby=null) {
613 extract($this->_table_names);
615 $where = "WHERE p.id=r.raterpage";
616 if (isset($dimension)) {
617 $where .= " AND dimension=$dimension";
620 if (isset($orderby)) {
621 $orderbyStr = " ORDER BY " . $orderby;
624 $query = "SELECT DISTINCT p.pagename"
625 . " FROM $rating_tbl r, $page_tbl p "
629 $result = $dbh->query($query);
633 function delete_rating($rater, $ratee, $dimension) {
635 extract($this->_table_names);
638 $raterid = $this->_get_pageid($rater, true);
639 $rateeid = $this->_get_pageid($ratee, true);
641 $dbh->query("DELETE FROM $rating_tbl WHERE raterpage=$raterid and rateepage=$rateeid and dimension=$dimension");
646 function rate($rater, $ratee, $rateeversion, $dimension, $rating, $isPrivate = 'no') {
648 extract($this->_table_names);
651 $raterid = $this->_get_pageid($rater, true);
652 $rateeid = $this->_get_pageid($ratee, true);
654 $dbh->query("DELETE FROM $rating_tbl WHERE raterpage=$raterid and rateepage=$rateeid and dimension=$dimension and isPrivate='$isPrivate'");
655 // NOTE: Leave tstamp off the insert, and MySQL automatically updates it
656 $dbh->query("INSERT INTO $rating_tbl (dimension, raterpage, rateepage, ratingvalue, rateeversion, isPrivate) VALUES ($dimension, $raterid, $rateeid, $rating, $rateeversion, '$isPrivate')");
663 // $Log: not supported by cvs2svn $
664 // Revision 1.2 2004/06/19 10:22:41 rurban
665 // outcomment the pear specific methods to let all pages load
667 // Revision 1.1 2004/06/18 14:42:17 rurban
668 // added wikilens libs (not yet merged good enough, some work for DanFr)
675 // c-hanging-comment-ender-p: nil
676 // indent-tabs-mode: nil