Changeset 29022

Timestamp:

Aug 23, 2010, 3:40:35 PM (16 years ago)

Author:

rhenders

Message:

Added methods to clean out stage tables

File:

• trunk/tools/czartool/CzarDb.pm (modified) (1 diff)

trunk/tools/czartool/CzarDb.pm

@@ -841 +841 @@
 }
 
+
+
+###########################################################################
+#
+# Deletes all but one row per interval from all stage tables for all labels between the two dates
+#
+###########################################################################
+sub cleanupDateRange {
+    my ($self, $startDay, $endDay, $interval) = @_;
+
+    my $thisDay = $startDay;
+    my $quit = 0;
+    while(!$quit) {
+
+        if (!$self->isBefore($thisDay, $endDay)) {
+        
+            $quit = 1;
+        }
+
+        print "* Running cleanup for $thisDay with an interval of $interval\n";
+        $self->cleanupADay($thisDay, $interval);
+        $thisDay = $self->addInterval($thisDay, "1 DAY");
+
+    }
+}
+###########################################################################
+#
+# Deletes all but one row per interval from all stage tables for all labels between the provided day
+#
+###########################################################################
+sub cleanupADay {
+    my ($self, $startDay, $interval) = @_;
+
+    my $endDay =  $self->addInterval($startDay, "1 DAY");
+
+    my $labels = undef;
+    my $fromTime = $startDay;
+    my $toTime = undef;
+    my $totalDeleted = undef;
+    my $quit = 0;
+    while(!$quit) {
+
+        $toTime = $self->addInterval($fromTime, $interval);
+        if (!$self->isBefore($toTime, $endDay)) {
+        
+            $toTime = $endDay;
+            $quit = 1;
+        }
+   
+        my $stage = undef;
+        $totalDeleted = 0;
+        foreach $stage (@stages) {
+
+            if (!$self->getLabelsInThisTimePeriod($stage, $fromTime, $toTime, \$labels)) {next;}
+
+            my $label = undef;
+            my $row = undef;
+            foreach $row ( @{$labels} ) {
+                my ($label) = @{$row};
+
+                my $query = $self->{_db}->prepare(<<SQL);
+                SELECT COUNT(*) 
+                    FROM $stage 
+                    WHERE timestamp > '$fromTime'
+                    AND timestamp <= '$toTime'
+                    AND label = '$label' 
+SQL
+
+                    $query->execute;
+
+                my $toDelete = scalar $query->fetchrow_array() - 1;
+                if ($toDelete < 1) {next;}
+
+                $query = $self->{_db}->prepare(<<SQL);
+                DELETE FROM $stage 
+                    WHERE timestamp > '$fromTime' 
+                    AND timestamp <= '$toTime' 
+                    AND label = '$label' ORDER BY timestamp DESC LIMIT $toDelete
+SQL
+                    $query->execute;
+
+                $totalDeleted += $toDelete;
+            }
+        }
+        print "   * Deleted $totalDeleted between $fromTime and  $toTime\n";
+        $fromTime = $toTime;
+    }
+}
+
+###########################################################################
+#
+# Returns an array of labels present during the provided time frame 
+#
+###########################################################################
+sub getLabelsInThisTimePeriod {
+    my ($self, $stage, $fromTime, $toTime, $labels) = @_;
+
+    my $query = $self->{_db}->prepare(<<SQL);
+    SELECT DISTINCT label 
+        FROM $stage 
+        WHERE timestamp > '$fromTime' 
+        AND timestamp <= '$toTime';
+SQL
+
+        if (!$query->execute) {
+
+            return 0;
+        }
+
+    ${$labels} = $query->fetchall_arrayref();
+
+    return 1;
+}
+
 1;