Now grab/average trusted ratings into this table
*/
- $sql="INSERT INTO user_metric_tmp1_$i
- SELECT user_ratings.user_id,count(*) AS count,
+ if ($j) {
+ $sql="INSERT INTO user_metric_tmp1_$i
+ SELECT user_ratings.user_id,count(*) AS count,
avg(user_metric$j.importance_factor),
avg(user_ratings.rating),0
FROM user_ratings,user_metric$j
WHERE user_ratings.rated_by=user_metric$j.user_id
GROUP BY user_ratings.user_id";
+ } else {
+ $sql="INSERT INTO user_metric_tmp1_$i
+ SELECT user_ratings.user_id,count(*) AS count,
+ 1.6,
+ avg(user_ratings.rating),0
+ FROM user_ratings
+ GROUP BY user_ratings.user_id";
+ }
$res=db_query($sql);
if (!$res) {
exit;
}
- // echo '<BR>Issuing Final Update';
+ //echo '<BR>Issuing Final Update';
+ // Only do final percentile if row count is not zero
+ if (db_result($res,0,0)) {
- /*
- Update with final percentile and importance
- */
- $sql="UPDATE user_metric$i SET
+ /*
+ Update with final percentile and importance
+ */
+ $sql="UPDATE user_metric$i SET
percentile=(100-(100*((ranking::float-1)/". db_result($res,0,0) .")))";
- $res=db_query($sql);
- if (!$res || db_affected_rows($res) < 1) {
+ $res=db_query($sql);
+ if (!$res || db_affected_rows($res) < 1) {
echo "Error in round $i setting percentile: ";
echo '<P>'.$sql.'<P>';
echo db_error();
exit;
- }
- $sql="UPDATE user_metric$i SET
+ }
+ $sql="UPDATE user_metric$i SET
importance_factor=(1+((percentile/100)*.5));";
- $res=db_query($sql);
- if (!$res || db_affected_rows($res) < 1) {
+ $res=db_query($sql);
+ if (!$res || db_affected_rows($res) < 1) {
echo "Error in round $i setting importance factor: ";
echo '<P>'.$sql.'<P>';
echo db_error();
exit;
+ }
}
}
+db_commit();
db_query("DELETE FROM user_metric;");
db_query("INSERT INTO user_metric SELECT * FROM user_metric".($i-1).";");
//echo '<P>'.db_error().'<P>';
-db_commit();
/*
Now run through and drop the tmp tables
*/
$ts_day = date('d', $t);
db_begin();
+db_query("DELETE FROM user_metric_history WHERE month='$ts_month' AND day='$ts_day'");
db_query("
INSERT INTO user_metric_history
SELECT '$ts_month','$ts_day',user_id,ranking,metric
$counts = db_result($rel,0,0);
print "\n\nCounts: ".$counts;
-
db_begin();
#drop the old metrics table
echo db_error();
}
+db_commit();
$sql="INSERT INTO project_metric_weekly_tmp (ranking,percentile,group_id)
SELECT ranking,(100-(100*((ranking-1)/$counts))),group_id
FROM project_metric_weekly_tmp1 ORDER BY ranking ASC";
echo db_error();
}
-db_commit();
echo db_error();
db_drop_sequence_if_exists ("project_metric_weekly_seq") ;
use DBI;
use Time::Local;
use POSIX qw( strftime );
-require("../../../utils/include.pl"); # Include all the predefined functions
+require("/usr/lib/sourceforge/lib/include.pl"); # Include all the predefined functions
$|++;
use DBI;
use Time::Local;
use POSIX qw( strftime );
-require("../../../utils/include.pl"); # Include all the predefined functions
+require("/usr/lib/sourceforge/lib/include.pl"); # Include all the predefined functions
#######################
## CONF VARS
my $verbose = 1;
- my @chronolog_dirs = ("/home/ftp1", "/home/ftp2", "/home/ftp3");
+ my @chronolog_dirs = ("/var/log/sourceforge");
##
#######################
. "AND frs_release.release_id = frs_file.release_id )";
$rel = $dbh->prepare($query);
$rel->execute();
+
while( $filerel = $rel->fetchrow_arrayref() ) {
$file_ident = ${$filerel}[2] . ":" . ${$filerel}[3];
$filerelease{$file_ident} = ${$filerel}[0];
for $chronolog_basedir (@chronolog_dirs) {
-$file = "$chronolog_basedir/$year/" . sprintf("%02d",$month) . "/http_combined_$year"
- . sprintf("%02d%02d", $month, $day) . ".log";
+$file = "$chronolog_basedir/$year/" . sprintf("%02d",$month) . "/"
+ . sprintf("%02d", $day) . "/sourceforge.log";
print "Running year $year, month $month, day $day from \'$file\'\n" if $verbose;
}
while (<LOGFILE>) {
-
$_ =~ m/^([\d\.]+).*\[(.+)\]\s\"GET (.+) HTTP.+(\d\d\d)\s(\d+)/;
$filepath = $3;
$code = $4;
-
if ( $code =~ m/2\d\d/ ) {
$filename = $1;
$file_ident = $basedir . ":" . $filename;
-
if ( $filerelease{$file_ident} ) {
$downloads{$filerelease{$file_ident}}++;
}
## parse each logfile set
-./stats_ftp_logparse.pl $*
-./stats_http_logparse.pl $*
-./stats_sum.pl $*
+/usr/lib/sourceforge/bin/stats_ftp_logparse.pl $*
+/usr/lib/sourceforge/bin/stats_http_logparse.pl $*
+/usr/lib/sourceforge/bin/stats_sum.pl $*
use DBI;
use Time::Local;
use POSIX qw( strftime );
-require("../../../utils/include.pl"); # Include all the predefined functions
+require("/usr/lib/sourceforge/lib/include.pl"); # Include all the predefined functions
$|++;
// populate stats_site table
//
echo "\n\nBeginning stats_site ".date('Ymd H:i:s',time());
-include('include/stats_site.inc');
+include('/usr/lib/sourceforge/lib/stats_site.inc');
site_stats_day($year,$month,$day);
//
*/
require_once('squal_pre.php');
-include_once('include/stats_projects.inc');
+include_once('/usr/lib/sourceforge/lib/stats_projects.inc');
$i=0;
use DBI;
use Time::Local;
use POSIX qw( strftime );
-require("../../../utils/include.pl"); # Include all the predefined functions
+require("/usr/lib/sourceforge/lib/include.pl"); # Include all the predefined functions
#######################
## CONF VARS
my $verbose = 1;
- my $chronolog_basedir = "/home/logs";
- my @webservers = ("old/bush",
- "old/delerium",
- "old/nirvana",
- "old/oakenfold",
- "pr-web1",
- "pr-web2",
- "pr-web3",
- "pr-web4",
- "pr-web5"
- );
+ my $chronolog_basedir = "/var/log";
+ my @webservers = ("sourceforge");
##
#######################
## It's makes things a whole lot faster for us if we cache the filerelease/group info beforehand.
print "Caching group information out of the database..." if $verbose;
-$query = "SELECT group_id,unix_group_name,http_domain FROM groups";
+$query = "SELECT group_id,unix_group_name,http_domain FROM groups WHERE http_domain != ''";
$rel = $dbh->prepare( $query );
$rel->execute();
while( $info = $rel->fetchrow_arrayref() ) {
+ print "\nCaching {${$info}[2]}" if $verbose;
+
$group{${$info}[2]} = ${$info}[0];
}
print " done.\n" if $verbose;
foreach $server ( @webservers ) {
- $file = "$chronolog_basedir/$server/$year/" . sprintf("%02d",$month) . "/" . sprintf("%02d",$day) . "/access_log";
+ $file = "$chronolog_basedir/$server/$year/" . sprintf("%02d",$month) . "/" . sprintf("%02d",$day) . "/sourceforge.log";
if ( -f $file ) {
open(LOGFILE, "< $file" ) || die "Cannot open $file";
print "Begining processing for logfile \'$file\'..." if $verbose;
+ $valid_hits = 0;
+
while (<LOGFILE>) {
chomp($_);
$lines++;
- ## 1=ip 2=date 3=file_uri 4=return_code 5=bytes 6=referer 7=browser 8=domain
- $_ =~ m/^([\d\.]+).*\[(.+)\]\s\"GET (.+) HTTP.+\" (\d\d\d) (\d+|\-) \"([^\"]+)\" \"([^\"]+)\"\ *(.*)$/;
+ ## 1=ip 2=date 3=file_uri 4=return_code 5=bytes 6=referer
+
+ $_ =~ m/^([\d\.]+).*\[(.+)\]\s\"GET (.+) HTTP.+\" (\d\d\d) (\d+|\-) \"([^\"]+)\/\/(.*)\/$\"/;
$filepath = $3;
$code = $4;
- $host = $8;
+ $host = $7;
+ ## strip out everything but domain
+ $host =~ s/\/.*//;
next if ( !($filepath || $code) );
## strip off any GET params.
$filepath =~ s/\?.*$//;
-
## We'll have our pageview filter to allow -> deny.
if ( $filepath =~ m/\/$/ || $filepath !~ m/\.(gif|png|jpg|jpeg|css)$/ ) {
+
if ( $group{$host} && $host ) {
$page_views{$group{$host}}++;
+ $valid_hits++;
}
- $valid_hits++;
}
}
}
empty)
setup_vars
check_server
- admin_regexp=$(echo $sf_ldap_base_dn | sed 's/, */, */g')
+ admin_regexp=$(echo $sf_ldap_base_dn | sed 's/, */, /g')
admin_regexp="^cn=admin, *$admin_regexp"
get_our_entries () {
{ # List candidates...
# 404 Error document
ErrorDocument 404 /404.php
+ LogFormat "%h %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" sourceforge
+ CustomLog "|/usr/sbin/cronolog /var/log/sourceforge/%Y/%m/%d/sourceforge.log" sourceforge
</VirtualHost>
<VirtualHost {ip_address}>
DocumentRoot /usr/lib/sourceforge/cvs
ScriptAlias /cgi-bin/ /usr/lib/sourceforge/cgi-bin/
Alias /cvstarballs/ /var/lib/sourceforge/cvstarballs/
+ LogFormat "%h %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" sourceforge
+ CustomLog "|/usr/sbin/cronolog /var/log/sourceforge/%Y/%m/%d/sourceforge.log" sourceforge
</VirtualHost>
<VirtualHost {ip_address}>
ServerName {download_host}
DocumentRoot /var/lib/sourceforge/download
+ LogFormat "%h %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" sourceforge
+ CustomLog "|/usr/sbin/cronolog /var/log/sourceforge/%Y/%m/%d/sourceforge.log" sourceforge
</VirtualHost>
<VirtualHost {ip_address}>
</Location>
RedirectMatch permanent ^/$ http://{lists_host}/cgi-bin/mailman/listinfo
+ LogFormat "%h %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" sourceforge
+ CustomLog "|/usr/sbin/cronolog /var/log/sourceforge/%Y/%m/%d/sourceforge.log" sourceforge
</VirtualHost>
<VirtualHost {ip_address}:443>
</Location>
RedirectMatch permanent ^/$ https://{lists_host}/cgi-bin/mailman/listinfo
+ LogFormat "%h %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" sourceforge
+ CustomLog "|/usr/sbin/cronolog /var/log/sourceforge/%Y/%m/%d/sourceforge.log" sourceforge
</VirtualHost>
<VirtualHost {ip_address}:443>
# 404 Error document
ErrorDocument 404 /404.php
+ LogFormat "%h %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" sourceforge
+ CustomLog "|/usr/sbin/cronolog /var/log/sourceforge/%Y/%m/%d/sourceforge.log" sourceforge
</VirtualHost>
# This is magic for virtual hosting!
order allow,deny
allow from all
</Directory>
+ LogFormat "%h %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" sourceforge
+ CustomLog "|/usr/sbin/cronolog /var/log/sourceforge/%Y/%m/%d/sourceforge.log" sourceforge
</VirtualHost>
#This may be used
order allow,deny
allow from all
</Directory>
+ LogFormat "%h %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" sourceforge
+ CustomLog "|/usr/sbin/cronolog /var/log/sourceforge/%Y/%m/%d/sourceforge.log" sourceforge
</VirtualHost>
* [Christian] Made news menu themeable
* [Christian] A lot of i18n in forum_utils
* [Christian] You can now customize date format in language tab files
-
- -- Christian Bayle <bayle@debian.org> Tue, 5 Nov 2002 01:46:29 +0100
+ * [Roland] Applied patch #617 from Robert Lamoureux. Stats should now
+ work! Changes are detailed below, with a [Bob] prefix.
+ * [Bob] Fixed initial pass problem with calculate_user_metric.php so
+ user ratings statistics now run.
+ * [Bob] Fixed db_ commit() error in project_weekly_metric.php. Project
+ statistics now work.
+ * [Bob] Fixed stats_http_logparse.pl to look for HTTP access log entries
+ in the right place (/var/log/sourceforge).
+ * [Bob] Changed sf-httpd.conf to have all sourceforge Virtual Web
+ servers log all activity to /var/log/sourceforge. Introduced a
+ dependency on cronolog. The stats Perl files all assume a YYYY/MM/DD
+ directory structure for the log files.
+ * [Bob] Fixed rules file to install cron jobs into /bin directory.
+ * [Bob] Changed sourceforge-db-postgresql cron file to call the stats
+ jobs every night.
+ * [Bob] Changed the views_graph.png and users_graph.png files to
+ generate an empty graph when no data is present rather than a broken
+ image icon. Also, fixed a bug that would break the graph if there was
+ 0 or only 1 row in the database.
+ * [Bob] Changed stats_logparse.sh to have the full path to the scripts.
+ We cannot assume we will be in the right directory when it runs.
+ * [Bob] Fixed relative paths to utils/include.pl since all cronjobs seem
+ to installed in the bin directory. The old path was 3 levels down in
+ the cronjobs directory which is not part of the install hierarchy
+
+ -- Roland Mas <lolando@debian.org> Thu, 7 Nov 2002 13:03:22 +0100
sourceforge (2.6-0+13) experimental; urgency=low
Package: sourceforge-web-apache
Architecture: all
-Depends: sourceforge-common (>=2.6-0+13+) , sourceforge-db-postgresql | sourceforge-db, apache (>= 1.3.9) | apache-ssl (>= 1.3.9), libapache-mod-ssl | apache-ssl, php4, php4-cgi, php4-pgsql, php4-gd, php4-ldap, php4-mcrypt, libmcrypt4 (>=2.4.22-2), perl, perl-suid, libdbi-perl, libdbd-pg-perl, debianutils (>= 1.7), debconf (>= 0.5.00), slapd (>= 2.0.23-3)
+Depends: sourceforge-common (>=2.6-0+13+) , sourceforge-db-postgresql | sourceforge-db, apache (>= 1.3.9) | apache-ssl (>= 1.3.9), libapache-mod-ssl | apache-ssl, php4, php4-cgi, php4-pgsql, php4-gd, php4-ldap, php4-mcrypt, libmcrypt4 (>=2.4.22-2), perl, perl-suid, libdbi-perl, libdbd-pg-perl, debianutils (>= 1.7), debconf (>= 0.5.00), slapd (>= 2.0.23-3), cronolog
Replaces: sourceforge (<= 2.6-0+11)
Provides: sourceforge-web
Conflicts: sourceforge-web
install -m 755 cronjobs/project_weekly_metric.php $(CURDIR)/debian/sourceforge-db-postgresql/usr/lib/sourceforge/bin/
install -m 755 cronjobs/rating_stats.php $(CURDIR)/debian/sourceforge-db-postgresql/usr/lib/sourceforge/bin/
install -m 755 cronjobs/site_stats.php $(CURDIR)/debian/sourceforge-db-postgresql/usr/lib/sourceforge/bin/
+ install -m 755 cronjobs/db_stats_agg.php $(CURDIR)/debian/sourceforge-db-postgresql/usr/lib/sourceforge/bin/
install -m 755 cronjobs/populate_foundries.php $(CURDIR)/debian/sourceforge-db-postgresql/usr/lib/sourceforge/bin/
install -m 755 cronjobs/calculate_user_metric.php $(CURDIR)/debian/sourceforge-db-postgresql/usr/lib/sourceforge/bin/
+ install -m 755 cronjobs/stats/download/stats_ftp_logparse.pl $(CURDIR)/debian/sourceforge-db-postgresql/usr/lib/sourceforge/bin/
+ install -m 755 cronjobs/stats/download/stats_http_logparse.pl $(CURDIR)/debian/sourceforge-db-postgresql/usr/lib/sourceforge/bin/
+ install -m 755 cronjobs/stats/download/stats_logparse.sh $(CURDIR)/debian/sourceforge-db-postgresql/usr/lib/sourceforge/bin/
+ install -m 755 cronjobs/stats/download/stats_sum.pl $(CURDIR)/debian/sourceforge-db-postgresql/usr/lib/sourceforge/bin/
+ install -m 755 cronjobs/stats/subdomains/stats_projects_logparse.pl $(CURDIR)/debian/sourceforge-db-postgresql/usr/lib/sourceforge/bin/
install -m 755 cronjobs/vacuum.php $(CURDIR)/debian/sourceforge-db-postgresql/usr/lib/sourceforge/bin/
- install -m 755 cronjobs/rotate_activity.php $(CURDIR)/debian/sourceforge-db-postgresql/usr/lib/sourceforge/bin/
- #install utils/underworld-root/db_top_groups_calc.pl $(CURDIR)/debian/sourceforge-db-postgresql/usr/lib/sourceforge/bin/db_top_groups_calc.pl
+ install -m 755 cronjobs/stats/include/stats_site.inc $(CURDIR)/debian/sourceforge-db-postgresql/usr/lib/sourceforge/lib/
+ install -m 755 cronjobs/stats/include/stats_projects.inc $(CURDIR)/debian/sourceforge-db-postgresql/usr/lib/sourceforge/lib/
+ install utils/underworld-root/db_top_groups_calc.pl $(CURDIR)/debian/sourceforge-db-postgresql/usr/lib/sourceforge/bin/db_top_groups_calc.pl
#install utils/underworld-root/stats_nightly.sh $(CURDIR)/debian/sourceforge-db-postgresql/usr/lib/sourceforge/bin/stats_nightly.sh
#install utils/underworld-root/db_stats_prepare.pl $(CURDIR)/debian/sourceforge-db-postgresql/usr/lib/sourceforge/bin/db_stats_prepare.pl
#install utils/underworld-root/db_stats_projects_nightly.pl $(CURDIR)/debian/sourceforge-db-postgresql/usr/lib/sourceforge/bin/db_stats_projects_nightly.pl
# Daily aggregating of the numbers
15 0 * * * sourceforge [ -x /usr/lib/sourceforge/bin/site_stats.php ] && /usr/lib/sourceforge/bin/site_stats.php -d include_path=/usr/lib/sourceforge/:/usr/lib/sourceforge/www/include > /dev/null 2>&1
+
+# Daily sweep of the HTTP log files for stats information
+25 0 * * * sourceforge [ -x /usr/lib/sourceforge/bin/stats_logparse.sh] && /usr/lib/sourceforge/bin/stats_logparse.sh > /dev/null 2>&1
+
+# Daily sweep of the stats into final tables
+45 0 * * * sourceforge [ -x /usr/lib/sourceforge/bin/db_stats_agg.php ] && /usr/lib/sourceforge/bin/db_stats_agg.php -d include_path=/usr/lib/sourceforge/:/usr/lib/sourceforge/www/include > /dev/null 2>&1
+
+# Daily sweep of the HTTP log files for project activity
+15 0 * * * sourceforge [ -x /usr/lib/sourceforge/bin/stats_projects_logparse.pl ] && /usr/lib/sourceforge/bin/stats_projects_logparse.pl -d include_path=/usr/lib/sourceforge/:/usr/lib/sourceforge/www/include > /dev/null 2>&1
# $Id: db_top_groups_calc.pl,v 1.9 2000/12/06 19:04:51 tperdue Exp $
#
# use strict;
-use DBI;
+require ('squal_pre.php');
+
+db_begin();
+
use Time::Local;
use POSIX qw( strftime );
require("../include.pl");
-&db_connect();
#my ($sql, $rel);
my ($day_begin, $day_end, $mday, $year, $mon, $week, $day);
# get all groups, and group_names
$query = "SELECT group_id,group_name FROM groups WHERE type=1 AND status='A' AND is_public='1'";
-$rel = $dbh->prepare($query);
-$rel->execute();
-while( ($group_id,$group_name) = $rel->fetchrow() ) {
- $top[$group_id][0] = $group_name;
+$rel = db_query($query);
+
+while( ($group_id,$group_name) = $rel->fetchrow() ) { $top[$group_id][0] = $group_name;
if ( $group_id > $max_group_id ) {
$max_group_id = $group_id;
}
WHERE forum.group_forum_id=forum_group_list.group_forum_id
GROUP BY forum_group_list.group_id
ORDER BY count DESC";
-my $rel = $dbh->prepare($query);
-$rel->execute();
+my $rel = db_query($query);
$currentrank = 1;
while(my ($group_id,$count) = $rel->fetchrow()) {
##
## wrap this process inside a transaction
##
-my $rel = $dbh->do("BEGIN WORK;");
+my $rel = db_query("BEGIN WORK;");
my $query = "DELETE FROM top_group";
-my $rel = $dbh->do($query);
+my $rel = db_query($query);
# store new data
for ($i=1;$i<$max_group_id;$i++) {
."'$top[$i][7]','$top[$i][8]','$top[$i][2]',"
."'0','0','$top[$i][3]','$top[$i][12]','$top[$i][13]','$top[$i][4]',"
."'$top[$i][10]','$top[$i][11]','$top[$i][9]')";
- my $rel = $dbh->prepare($query);
- $rel->execute();
+ my $rel = db_query($query);
# print "Group ID $i: $top[$i][0], $top[$i][1], $top[$i][2], $top[$i][3], $top[$i][4], "
# ."$top[$i][5], $top[$i][6]\n";
##
## wrap this process inside a transaction
##
-my $rel = $dbh->do("COMMIT WORK;");
+my $rel = db_query("COMMIT WORK;");
$ydata2[$i] = $row["new_projects"];
++$i;
}
+//
+// Need at least 2 data points
+//
+if ($i == 0) {
+ $xdata[0] = 0;
+ $xlabel[0] = "";
+ $ydata1[0] = 0;
+ $ydata2[0] = 0;
+
+ $xdata[1] = 1;
+ $xlabel[1] = "";
+ $ydata1[1] = 0;
+ $ydata2[1] = 0;
+}
+if ($i == 1) {
+ $xdata[1] = 1;
+ $xlabel[1] = $xlabel[0];
+ $ydata1[1] = $ydata1[0];
+ $ydata2[1] = $ydata2[0];
+}
+
$graph = new Graph( 750, 550 );
}
$graph = new Graph( 750, 550 );
+//
+// Need at least 2 data points
+//
+if ($i == 0) {
+ $xdata[0] = 0;
+ $xlabel[0] = "";
+ $ydata1[1] = 0;
+ $xdata[1] = 1;
+ $xlabel[1] = "";
+ $ydata1[1] = 0;
+}
+
+if ($i == 1) {
+ $xdata[1] = 1;
+ $xlabel[1] = $xlabel[0];
+ $ydata1[1] = $ydata1[0];
+}
+$graph->SetTitle( "Sourceforge Page Views" );
+$graph->SetSubTitle("Total Page Views (RED) ( $i $grouping )");
$data1 = $graph->AddData( $xdata, $ydata1, $xlabel );
+$graph->LineGraph($data1,'red');
$graph->DrawGrid('gray');
-$graph->LineGraph($data1,'red');
-$graph->SetTitle( "Sourceforge Page Views" );
-$graph->SetSubTitle("Total Page Views (RED) ( $i $grouping )");
$graph->SetxTitle('Date');
$graph->SetyTitle('Views (RED)');
$graph->DrawAxis();