4 * Copyright 1999-2001 (c) VA Linux Systems
5 * Copyright 2003 (c) FusionForge, LLC
6 * Copyright 2009 (c) Roland Mas
8 * This file is part of FusionForge.
10 * FusionForge is free software; you can redistribute it and/or modify
11 * it under the terms of the GNU General Public License as published by
12 * the Free Software Foundation; either version 2 of the License, or
13 * (at your option) any later version.
15 * FusionForge is distributed in the hope that it will be useful,
16 * but WITHOUT ANY WARRANTY; without even the implied warranty of
17 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
18 * GNU General Public License for more details.
20 * You should have received a copy of the GNU General Public License
21 * along with FusionForge; if not, write to the Free Software
22 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 US
26 Before running the first time, execute initializing SQL:
28 CREATE TABLE user_metric_history(
35 CREATE INDEX user_metric_history_date_userid
36 ON user_metric_history(month,day,user_id);
39 Nightly cron script to calculate the peer ratings
41 The process starts with a seed group of users who are "trusted"
44 After you are rated N times highly by other users, you can become trusted
45 and your ratings of others will begin to count
47 Your rating is affected by how many times you are rated by others and
48 how highly they rate you and how highly rated they are
50 How highly rated they are is affected by how many times they're rated
51 and how highly rated they are and so on up the chain
53 For now, this process will run 8 times to get the calculations refined
54 As more users are added, it may have to be run more
56 Because of this circular dependency, the numbers are never "right", but
57 after a few runs, they should be refined "enough" to give us
58 what we want - a list of the top users on the site.
61 require dirname(__FILE__).'/../www/env.inc.php';
62 require $gfwww.'include/squal_pre.php';
63 require $gfcommon.'include/cron_utils.php';
70 db_query_params ('DELETE FROM user_metric0',
75 if ($sys_database_type != 'mysql') {
76 db_query_params ('select setval($1,1)',
77 array('user_metric0_pk_seq')) ;
82 db_query_params ('INSERT INTO user_metric0
83 (user_id,times_ranked,avg_raters_importance,avg_rating,metric,percentile,importance_factor)
84 SELECT user_id,5,1.25,3,0,0,1.25
87 user_group.group_id=$1
88 AND user_group.admin_flags=$2',
89 array ($sys_peer_rating_group,
95 db_query_params ('UPDATE user_metric0 SET ranking=ranking-1',
99 if ($sys_database_type == 'mysql') {
101 SELECT count(*) FROM user_metric0 INTO @total;
102 UPDATE user_metric0 SET
103 metric=(log(times_ranked)*avg_rating),
104 percentile=(100-(100*(ranking-1.0)/@total));";
113 db_query_params ('UPDATE user_metric0 SET metric=(log(times_ranked::float)*avg_rating::float)::float, percentile=(100-(100*((ranking::float-1)/(select count(*) from user_metric0))))::float',
118 if ($sys_database_type == 'mysql') {
119 $sql="UPDATE user_metric0 SET importance_factor=(1+((percentile/100.0)*.5));";
120 db_query_mysql($sql);
122 db_query_params ('UPDATE user_metric0 SET importance_factor=(1+((percentile::float/100)*.5))::float',
128 db_query_params ('SELECT * INTO TEMPORARY TABLE user_metric_cur FROM user_metric0',
132 for ($i=1; $i<9; $i++) {
136 Set up an interim table to grab and average all trusted result
138 db_drop_table_if_exists ("user_metric_tmp_next");
139 db_drop_table_if_exists ("user_metric_next");
141 $res = db_query_params ('CREATE TEMPORARY TABLE user_metric_tmp_next (
142 user_id int not null default 0,
143 times_ranked float(8) null default 0,
144 avg_raters_importance float(8) not null default 0,
145 avg_rating float(8) not null default 0,
146 metric float(8) not null default 0)',
149 $err .= "Error in round $i creating table: " . db_error();
154 Now grab/average trusted ratings into this table
157 $res = db_query_params ('INSERT INTO user_metric_tmp_next
158 SELECT user_ratings.user_id,count(*) AS count,
159 avg(user_metric_cur.importance_factor),
160 avg(user_ratings.rating),0
161 FROM user_ratings,user_metric_cur
162 WHERE user_ratings.rated_by=user_metric_cur.user_id
163 GROUP BY user_ratings.user_id',
166 $err .= "Error in round $i inserting average ratings: " . db_error();
172 Now calculate the metric on the temp table
174 This metric will be used in the next step to calculate ranking and importance
177 $res = db_query_params ('UPDATE user_metric_tmp_next SET metric=(log(times_ranked)*avg_raters_importance*avg_rating)',
180 $err .= "Error in round $i calculating metric: " . db_error();
185 $res = db_query_params ('DELETE FROM user_metric_tmp_next WHERE metric < $1',
186 array ($threshhold)) ;
188 $err .= "Error in round $i deleting < threshhold ids: " . db_error();
194 Now we need to carry forward trusted IDs from the last round into this
195 Round, as prior round people may not have been ranked enough times by
196 new people in this round to stay in
199 $res = db_query_params ('INSERT INTO user_metric_tmp_next
200 SELECT user_id,times_ranked,avg_raters_importance,avg_rating,metric
203 (SELECT user_id FROM user_metric_tmp_next
204 WHERE user_metric_tmp_next.user_id=user_metric_cur.user_id)',
207 $err .= "Error in round $i inserting final data: " . db_error();
212 Now calculate the metric for this round
214 Create the final table, then insert the data
217 db_drop_table_if_exists ("user_metric_next");
218 db_drop_sequence_if_exists ("user_metric_next_ranking_seq");
220 $res = db_query_params ('CREATE TEMPORARY TABLE user_metric_next (
222 user_id int not null default 0,
223 times_ranked int not null default 0,
224 avg_raters_importance float(8) not null default 0,
225 avg_rating float(8) not null default 0,
226 metric float(8) not null default 0,
227 percentile float(8) not null default 0,
228 importance_factor float(8) not null default 0)',
232 $err .= "Error in round $i creating table: " . db_error();
237 Insert the data in ranked order
240 $res = db_query_params ('INSERT INTO user_metric_next (user_id,times_ranked,avg_raters_importance,avg_rating,metric)
241 SELECT user_id,times_ranked,avg_raters_importance,avg_rating,metric
242 FROM user_metric_tmp_next
243 ORDER BY metric DESC',
246 $err .= "Error in round $i inserting final data: " . db_error();
251 Get the row count so we can calc the percentile below
253 $res = db_query_params ('SELECT COUNT(*) FROM user_metric_next',
256 $err .= "Error in round $i getting row count: " . db_error();
260 // Only do final percentile if row count is not zero
261 if (db_result($res,0,0)) {
264 Update with final percentile and importance
266 if ($sys_database_type == 'mysql') {
267 $sql="UPDATE user_metric_next SET
268 percentile=(100-(100*((ranking-1.0)/". db_result($res,0,0) .")))";
269 $res = db_query_mysql ($sql);
271 $res = db_query_params ('UPDATE user_metric_next SET
272 percentile=(100-(100*((ranking::float-1)/$1)))',
273 array (db_result($res,0,0))) ;
275 if (!$res || db_affected_rows($res) < 1) {
276 $err .= "Error in round $i setting percentile: " . db_error();
279 $res = db_query_params ('UPDATE user_metric_next SET
280 importance_factor=(1+((percentile/100)*.5))',
282 if (!$res || db_affected_rows($res) < 1) {
283 $err .= "Error in round $i setting importance factor: " . db_error();
288 db_drop_table_if_exists ("user_metric_tmp_cur");
289 db_drop_table_if_exists ("user_metric_cur");
290 db_query_params ('SELECT * INTO user_metric_cur FROM user_metric_next',
292 db_query_params ('SELECT * INTO user_metric_tmp_cur FROM user_metric_tmp_next',
294 db_drop_table_if_exists ("user_metric_tmp_next");
295 db_drop_table_if_exists ("user_metric_next");
298 db_query_params ('DELETE FROM user_metric',
300 db_query_params ('INSERT INTO user_metric SELECT * FROM user_metric_cur',
303 db_drop_table_if_exists ("user_metric_tmp_cur");
304 db_drop_table_if_exists ("user_metric_cur");
309 $ts_month = date('Ym', $t);
310 $ts_day = date('d', $t);
313 db_query_params ('DELETE FROM user_metric_history WHERE month=$1 AND day=$2',
318 INSERT INTO user_metric_history
319 SELECT $1,$2,user_id,ranking,metric