[dw-free] Business statistics

[fu]

[commit: http://hg.dwscoalition.org/dw-free/rev/a0086d1075d6]

http://bugs.dwscoalition.org/show_bug.cgi?id=124

Use the same timestamp for the entire category, to avoid stats being split
across seconds. Also fix the timestamp on old stats in the same category
that were less than a minute apart.

Patch by pauamma.

Files modified:

• bin/upgrading/update-db-general.pl
• cgi-bin/DW/StatStore.pm

--------------------------------------------------------------------------------
diff -r d869e1462b6c -r a0086d1075d6 bin/upgrading/update-db-general.pl
--- a/bin/upgrading/update-db-general.pl	Tue Sep 14 13:45:39 2010 +0800
+++ b/bin/upgrading/update-db-general.pl	Tue Sep 14 14:51:37 2010 +0800
@@ -3895,6 +3895,36 @@ EOF
         do_alter( 'poll2',
                   "ALTER TABLE poll2 ADD COLUMN isanon enum('yes','no') NOT NULL default 'no'");
     }
+
+    # Merge within-category split timestamps
+    if ( table_relevant( "site_stats" )
+         && !check_dbnote( "unsplit_stats_timestamps" ) ) {
+        # Because category+key+time is a UNIQUE key, there's no need to check
+        # for duplicates or inconsistencies. Instead, just rely on mysql
+        # complaining. Update is idempotent, interruptible, and restartable.
+        my $stats = $dbh->selectall_hashref(
+                              qq{ SELECT category_id, insert_time, COUNT(*)
+                                      FROM site_stats
+                                      GROUP BY category_id, insert_time
+                                      ORDER BY category_id ASC,
+                                               insert_time ASC; },
+                              [ qw( category_id insert_time ) ] );
+        die $dbh->errstr if $dbh->err || !defined $stats;
+        foreach my $cat ( keys %$stats ) {
+            my $lasttime;
+            foreach my $time ( sort { $a <=> $b } keys %{$stats->{$cat}} ) {
+                # Arbitrary limit is arbitrary
+                if ( defined $lasttime and $time - $lasttime < 60 ) {
+                    do_sql( qq{ UPDATE site_stats SET insert_time = $lasttime
+                                     WHERE category_id = $cat
+                                           AND insert_time = $time } );
+                } else {
+                    $lasttime = $time;
+                }
+            }
+        }
+        set_dbnote( "unsplit_stats_timestamps", 1 )
+    }
 });
 
 
diff -r d869e1462b6c -r a0086d1075d6 cgi-bin/DW/StatStore.pm
--- a/cgi-bin/DW/StatStore.pm	Tue Sep 14 13:45:39 2010 +0800
+++ b/cgi-bin/DW/StatStore.pm	Tue Sep 14 14:51:37 2010 +0800
@@ -9,7 +9,7 @@
 #      Pau Amma <pauamma@cpan.org>
 #      Afuna <coder.dw@afunamatata.com>
 #
-# Copyright (c) 2009 by Dreamwidth Studios, LLC.
+# Copyright (c) 2009-2010 by Dreamwidth Studios, LLC.
 #
 # This program is free software; you may redistribute it and/or modify it under
 # the same terms as Perl itself.  For a copy of the license, please reference
@@ -27,7 +27,7 @@ DW::StatStore -- Statistics store update
 
   # get pony stats from one day ago
   DW::StatStore->get( 'ponies' );
-  
+
   # get pony stats over the last 30 days
   DW::StatStore->get( 'ponies', 30 );
 
@@ -57,6 +57,10 @@ sub add {
     my $dbh = LJ::get_db_writer()
         or return undef;
 
+    # Using UNIX_TIMESTAMP can cause partial retrievals in get_latest if the
+    # database server clock ticks between keys for the category.
+    my $now = time;
+
     while ( my ( $key, $val ) = splice( @stats, 0, 2 ) ) {
         my $key_id = $class->to_id( $key )
             or next;
@@ -65,8 +69,8 @@ sub add {
         # statistics is not the end of the world
         $dbh->do(
             q{INSERT INTO site_stats (category_id, key_id, insert_time, value)
-              VALUES (?, ?, UNIX_TIMESTAMP(), ?)},
-            undef, $catkey_id, $key_id, $val+0
+              VALUES (?, ?, ?, ?)},
+            undef, $catkey_id, $key_id, $now, $val+0
         );
     }
 
--------------------------------------------------------------------------------