Changeset 29060 for branches/sc_branches/trunkTest/tools/czartool/CzarDb.pm

Timestamp:

Aug 26, 2010, 9:18:39 AM (16 years ago)

Author:

Serge CHASTEL

Message:

Merging trunk in branch

Location:

branches/sc_branches/trunkTest

Files:

• . (modified) (1 prop)
• tools/czartool/CzarDb.pm (modified) (6 diffs)

branches/sc_branches/trunkTest/tools/czartool/CzarDb.pm

@@ -1 @@
-#!/usr/bin/perl i-w
+#!/usr/bin/perl -w
 
 package czartool::CzarDb;
@@ -6 +6 @@
 use strict;
 
-my @stages = ("chip", "cam", "fake", "warp", "stack", "diff", "magic", "magicDS", "dist"); # TODO put elsewhere
+my @stages = ("burntool", "chip", "cam", "fake", "warp", "stack", "diff", "magic", "magicDS", "dist"); # TODO put elsewhere
 
 use base 'czartool::MySQLDb';
@@ -491 +491 @@
 }
 
+
+###########################################################################
+#
+# 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;
+}
+
 ###########################################################################
 #
@@ -500 +613 @@
 
     my $currentRevision = -1;
-    my $latestRevision = 10;
-
-    while ($currentRevision != $latestRevision) {
+
+    while (1) {
 
         $currentRevision = $self->getRevision();
@@ -517 +629 @@
         elsif ($currentRevision == 8) {$self->createRevision_9();}
         elsif ($currentRevision == 9) {$self->createRevision_10();}
+        elsif ($currentRevision == 10) {$self->createRevision_11();}
+        else {last;}
     }
 }
@@ -841 +955 @@
 }
 
+#######################################################################################
+# 
+# Create revision 11 of the database 
+#
+#######################################################################################
+sub createRevision_11 {
+    my ($self) = @_;
+
+    print "* Creating revision 11 of '$self->{_dbName}'\n";
+
+    # same shape as other stage tables to enable easy update
+    my $query = $self->{_db}->prepare(<<SQL);
+    CREATE TABLE burntool (
+            timestamp TIMESTAMP DEFAULT NOW(),
+            label VARCHAR(128) DEFAULT "NONE", 
+            pending BIGINT NOT NULL,
+            processed BIGINT NOT NULL,
+            faults BIGINT NOT NULL);
+SQL
+
+      $query->execute;
+        $query = $self->{_db}->prepare(<<SQL);
+        CREATE INDEX burntoolIndex ON burntool (timestamp, label);
+SQL
+
+      $query->execute;
+
+    $self->setRevision(11);
+}
+
+
+
 1;