Changeset 29251 for trunk/tools/czartool/CzarDb.pm

Timestamp:

Sep 28, 2010, 11:26:51 AM (16 years ago)

Author:

rhenders

Message:

Now all tables are 'cleaned'. Clunky solution, bu will do for now

File:

• trunk/tools/czartool/CzarDb.pm (modified) (4 diffs)

trunk/tools/czartool/CzarDb.pm

@@ -561 +561 @@
 ###########################################################################
 #
-# Deletes all but one row per interval from all stage tables for all labels between the provided day
+# Deletes all but one row per interval from all tables for the provided date range
+# TODO this is very clumsy, I just have time to thinmk of something more elegant
 #
 ###########################################################################
@@ -574 +575 @@
     my $totalDeleted = undef;
     my $quit = 0;
+    my $query = undef;
     while(!$quit) {
 
@@ -594 +596 @@
                 my ($label) = @{$row};
 
-                my $query = $self->{_db}->prepare(<<SQL);
+                $query = $self->{_db}->prepare(<<SQL);
                 SELECT COUNT(*) 
                     FROM $stage 
@@ -617 +619 @@
                 $totalDeleted += $toDelete;
             }
+
         }
+
+        # servers table
+        my $servers = undef;
+        if ($self->getServers(\$servers)) {
+
+            my $server = undef;
+            my $row = undef;
+            foreach $row ( @{$servers} ) {
+                my ($server) = @{$row};
+
+                $query = $self->{_db}->prepare(<<SQL);
+                SELECT COUNT(*) 
+                    FROM servers 
+                    WHERE timestamp > '$fromTime'
+                    AND timestamp <= '$toTime'
+                    AND server = '$server' 
+SQL
+                $query->execute;
+
+                my $toDelete = scalar $query->fetchrow_array() - 1;
+                if ($toDelete > 0) {
+
+                    $query = $self->{_db}->prepare(<<SQL);
+                    DELETE FROM servers 
+                        WHERE timestamp > '$fromTime' 
+                        AND timestamp <= '$toTime' 
+                        AND server = '$server' ORDER BY timestamp DESC LIMIT $toDelete
+SQL
+                    $query->execute;
+
+                    $totalDeleted += $toDelete;
+                }
+            }
+        }
+
+        # now deal with cluster_space table
+        $query = $self->{_db}->prepare(<<SQL);
+        SELECT COUNT(*) 
+            FROM cluster_space 
+            WHERE timestamp > '$fromTime'
+            AND timestamp <= '$toTime'
+SQL
+        $query->execute;
+
+        my $toDelete = scalar $query->fetchrow_array() - 1;
+        if ($toDelete > 0) {
+
+            $query = $self->{_db}->prepare(<<SQL);
+            DELETE FROM cluster_space 
+                WHERE timestamp > '$fromTime' 
+                AND timestamp <= '$toTime' 
+                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 servers 
+#
+###########################################################################
+sub getServers {
+    my ($self, $servers) = @_;
+
+    my $query = $self->{_db}->prepare(<<SQL);
+    SELECT DISTINCT server
+        FROM servers
+SQL
+
+        if (!$query->execute) {
+
+            return 0;
+        }
+
+    ${$servers} = $query->fetchall_arrayref();
+
+    return 1;
 }