IPP Software Navigation Tools IPP Links Communication Pan-STARRS Links

Ignore:
Timestamp:
Aug 19, 2010, 6:50:13 PM (16 years ago)
Author:
eugene
Message:

updates from trunk

File:
1 edited

Legend:

Unmodified
Added
Removed

  • branches/eam_branches/ipp-20100621/tools/czartool/CzarDb.pm

    r28794 r28980  
    1616
    1717    # Call the constructor of the parent class, Person.
    18     my $self = $class->SUPER::new( $_[1], $_[2], $_[3], $_[4],  $_[5], $_[6]);
     18    my $self = $class->SUPER::new($_[1], $_[2], $_[3], $_[4],  $_[5], $_[6]);
    1919
    2020    bless $self, $class;
    … …  
    6666
    6767       $query->execute;
     68}
     69
     70###########################################################################
     71#
     72# Gets revert status for this stage
     73#
     74###########################################################################
     75sub getRevertStatus {
     76    my ($self, $stage, $reverting) = @_;
     77
     78    my $query = $self->{_db}->prepare(<<SQL);
     79    SELECT reverting
     80        FROM reverts
     81         WHERE stage LIKE '$stage';
     82SQL
     83
     84    $query->execute;
     85    return scalar $query->fetchrow_array();
     86}
     87
     88###########################################################################
     89#
     90# Updates revert status for this stage
     91#
     92###########################################################################
     93sub updateRevertStatus {
     94    my ($self, $stage, $reverting) = @_;
     95
     96    my $query = $self->{_db}->prepare(<<SQL);
     97     UPDATE reverts
     98         SET reverting = $reverting
     99         WHERE stage LIKE '$stage';
     100SQL
     101
     102    $query->execute;
    68103}
    69104
    … …  
    117152    }
    118153}
     154
     155###########################################################################
     156#
     157# Updates hosts table
     158#
     159###########################################################################
     160sub updateHost {
     161    my ($self, $host, $total, $available, $used, $readable, $writable) = @_;
     162
     163    my $query = $self->{_db}->prepare(<<SQL);
     164    DELETE FROM hosts WHERE host LIKE '$host';
     165SQL
     166
     167    $query->execute;
     168    $query = $self->{_db}->prepare(<<SQL);
     169    INSERT INTO hosts
     170        (host, total, available, used, readable, writable)
     171        VALUES
     172        ('$host', $total, $available, $used, $readable, $writable);
     173SQL
     174    $query->execute;
     175
     176}
     177
     178###########################################################################
     179#
     180# Inserts new cluster space info
     181#
     182###########################################################################
     183sub insertNewClusterSpace {
     184    my ($self, $total, $available, $used, $hostsOver98) = @_;
     185
     186    my $query = $self->{_db}->prepare(<<SQL);
     187    INSERT INTO cluster_space
     188        (total, available, used, hostsOver98)
     189        VALUES
     190        ( $total, $available, $used, $hostsOver98);
     191SQL
     192
     193        $query->execute;
     194}
     195
    119196###########################################################################
    120197#
    … …  
    123200###########################################################################
    124201sub insertNewTimeData {
    125     my ($self, $stage, $label, $pending, $processed, $faults, $reverting) = @_;
     202    my ($self, $stage, $label, $pending, $processed, $faults) = @_;
    126203
    127204    my $query = $self->{_db}->prepare(<<SQL);
    128205    INSERT INTO $stage
    129         (label, pending, processed, faults, reverting)
     206        (label, pending, processed, faults)
    130207        VALUES
    131         ('$label', $pending, $processed, $faults, $reverting);
     208        ('$label', $pending, $processed, $faults);
    132209SQL
    133210
    … …  
    158235SQL
    159236
    160 
    161 
    162237    if (!$query->execute) {return undef;}
    163238
    164     my $minProcessed;
     239    my $minProcessed = undef;
    165240
    166241    ($minProcessed, ${$maxY}, ${$minY}, ${$maxX}, ${$minX}, ${$timeDiff}) = $query->fetchrow_array();
    167242
    168     if (!$minProcessed) {return undef;}
     243    if (!defined $minProcessed) {return undef;}
    169244
    170245    $query = $self->{_db}->prepare(<<SQL);
    … …  
    273348###########################################################################
    274349#
     350# Gets host data and stores it to temp file
     351#
     352###########################################################################
     353sub createHostsData {
     354    my ($self, $limit) = @_;
     355
     356    my $dataFile = "/tmp/czarplot_hosts_space.dat";
     357    open (GNUDAT, ">$dataFile");
     358
     359    my $query = $self->{_db}->prepare(<<SQL);
     360    SELECT
     361        host, used, available, writable, readable
     362        FROM hosts
     363        ORDER BY host;
     364SQL
     365
     366    $query->execute;
     367
     368    my $underLimit;
     369
     370    # loop round results
     371    while (my @row = $query->fetchrow_array()) {
     372
     373        my ($host, $used, $available, $writable, $readable) = @row;
     374   
     375        if (($used/($used+$available))*100 > $limit) {$underLimit = 0;}
     376        else {$underLimit = 1;}
     377
     378        # Col 1: host
     379        print GNUDAT "$host";
     380       
     381        # Col 2: available, readable used space under limit
     382        if ($readable && $underLimit) {print GNUDAT " $used";}
     383        else {print GNUDAT " 0";}
     384
     385        # Col 3: available, readable used space OVER limit
     386        if ($readable && !$underLimit) {print GNUDAT " $used";}
     387        else {print GNUDAT " 0";}
     388
     389        # Col 4: NOT available used space
     390        if (!$readable) {print GNUDAT " $used";}
     391        else {print GNUDAT " 0";}
     392
     393        # Col 5: available writable space
     394        if($writable) {print GNUDAT " $available";}
     395        else {print GNUDAT " 0";}
     396
     397        # Col 6: NOT available writable space
     398        if(!$writable) {print GNUDAT " $available";}
     399        else {print GNUDAT " 0";}
     400
     401        print GNUDAT "\n";
     402    }
     403    close(GNUDAT);
     404
     405    return $dataFile;
     406}
     407
     408###########################################################################
     409#
     410# Gets total used cluster space as a percentage
     411#
     412############################################################################
     413sub getTotalClusterStorageAsPercentage {
     414    my ($self) = @_;
     415
     416    my $query = $self->{_db}->prepare(<<SQL);
     417    SELECT used/total*100
     418        FROM cluster_space
     419        ORDER BY timestamp DESC LIMIT 1;
     420SQL
     421    $query->execute;
     422    return scalar $query->fetchrow_array();
     423}
     424
     425###########################################################################
     426#
     427# Gets time series data for cluster storage and saves it to file
     428#
     429###########################################################################
     430sub createStorageTimeSeriesData {
     431    my ($self, $fromTime, $toTime, $minX, $maxX, $minY, $maxY, $timeDiff) = @_;
     432
     433    my $dataFile = "/tmp/czarplot_gnuplot_storage_timeseries.dat";
     434    open (GNUDAT, ">$dataFile");
     435
     436    my $query = $self->{_db}->prepare(<<SQL);
     437    SELECT
     438        MAX(available), MIN(available), 
     439        DATE_FORMAT(MAX(timestamp),'$self->{_dateFormat}'),
     440        DATE_FORMAT(MIN(timestamp),'$self->{_dateFormat}'),
     441        TIME_TO_SEC(TIMEDIFF(max(timestamp ), min(timestamp)))
     442            FROM cluster_space
     443            WHERE timestamp >= '$fromTime' AND timestamp <= '$toTime';
     444SQL
     445
     446
     447    if (!$query->execute) {return undef;}
     448
     449    (${$maxY}, ${$minY}, ${$maxX}, ${$minX}, ${$timeDiff}) = $query->fetchrow_array();
     450
     451
     452    $query = $self->{_db}->prepare(<<SQL);
     453    SELECT
     454        DATE_FORMAT(timestamp, '$self->{_dateFormat}'), available
     455        FROM cluster_space
     456        WHERE timestamp >= '$fromTime' AND timestamp <= '$toTime'
     457        ORDER BY timestamp;
     458SQL
     459
     460    $query->execute;
     461
     462    # loop round results
     463    while (my @row = $query->fetchrow_array()) {
     464
     465        my ($timestamp, $available) = @row;
     466        print GNUDAT "$timestamp $available\n";
     467    }
     468    close(GNUDAT);
     469
     470    return $dataFile;
     471}
     472###########################################################################
     473#
    275474# Determines how much has been processed in the provided interval of time
    276475# (format: 1 HOUR, 1 MINUTE 1 DAY etc)
    … …  
    301500
    302501    my $currentRevision = -1;
    303     my $latestRevision = 7;
     502    my $latestRevision = 10;
    304503
    305504    while ($currentRevision != $latestRevision) {
    … …  
    315514        elsif ($currentRevision == 5) {$self->createRevision_6();}
    316515        elsif ($currentRevision == 6) {$self->createRevision_7();}
    317     }
     516        elsif ($currentRevision == 7) {$self->createRevision_8();}
     517        elsif ($currentRevision == 8) {$self->createRevision_9();}
     518        elsif ($currentRevision == 9) {$self->createRevision_10();}
     519    }
     520}
     521
     522#######################################################################################
     523#
     524# Sets current revision of ippToPsps database
     525#
     526#######################################################################################
     527sub setRevision {
     528    my ($self, $revision) = @_;
     529
     530    my $query = $self->{_db}->prepare(<<SQL);
     531    INSERT INTO revision (revision) VALUES ($revision);
     532SQL
     533    $query->execute;
     534}
     535
     536#######################################################################################
     537#
     538# Gets current revision of ippToPsps database
     539#
     540#######################################################################################
     541sub getRevision {
     542    my ($self) = @_;
     543
     544    if (!$self->SUPER::doesTableExist("revision")) {return 0;}
     545
     546    my $query = $self->{_db}->prepare(<<SQL);
     547    SELECT revision
     548        FROM revision
     549        ORDER BY revision DESC LIMIT 1;
     550SQL
     551
     552    $query->execute;
     553    my @row = $query->fetchrow_array();
     554
     555    return $row[0];
    318556}
    319557
    … …  
    500738#######################################################################################
    501739#
    502 # Sets current revision of ippToPsps database
    503 #
    504 #######################################################################################
    505 sub setRevision {
    506     my ($self, $revision) = @_;
    507 
    508     my $query = $self->{_db}->prepare(<<SQL);
    509     INSERT INTO revision (revision) VALUES ($revision);
    510 SQL
    511     $query->execute;
    512 }
    513 
    514 #######################################################################################
    515 #
    516 # Gets current revision of ippToPsps database
    517 #
    518 #######################################################################################
    519 sub getRevision {
    520     my ($self) = @_;
    521 
    522     if (!$self->SUPER::doesTableExist("revision")) {return 0;}
    523 
    524     my $query = $self->{_db}->prepare(<<SQL);
    525     SELECT revision
    526         FROM revision
    527         ORDER BY revision DESC LIMIT 1;
    528 SQL
    529 
    530     $query->execute;
    531     my @row = $query->fetchrow_array();
    532 
    533     return $row[0];
     740# Create revision 8 of the database
     741#
     742#######################################################################################
     743sub createRevision_8 {
     744    my ($self) = @_;
     745
     746    print "* Creating revision 8 of '$self->{_dbName}'\n";
     747
     748    # drop reverting column from all stages tables
     749    my $stage = undef;
     750    foreach $stage (@stages) {
     751
     752        my $query = $self->{_db}->prepare(<<SQL);
     753        ALTER TABLE $stage
     754            DROP COLUMN reverting;
     755SQL
     756
     757    $query->execute;
     758    }
     759
     760    # create new 'revert' table
     761    my $query = $self->{_db}->prepare(<<SQL);
     762    CREATE TABLE reverts (
     763            stage VARCHAR(128),
     764            reverting TINYINT);
     765SQL
     766
     767    $query->execute;
     768
     769    # insert stages into revert table
     770    foreach $stage (@stages) {
     771        my $query = $self->{_db}->prepare(<<SQL);
     772        INSERT INTO reverts
     773            (stage, reverting)
     774            VALUES
     775            ('$stage', 0);
     776SQL
     777       $query->execute;
     778    }
     779
     780    $self->setRevision(8);
     781}
     782
     783#######################################################################################
     784#
     785# Create revision 9 of the database
     786#
     787#######################################################################################
     788sub createRevision_9 {
     789    my ($self) = @_;
     790
     791    print "* Creating revision 9 of '$self->{_dbName}'\n";
     792
     793    my $query = $self->{_db}->prepare(<<SQL);
     794    CREATE TABLE cluster_space (
     795            timestamp TIMESTAMP DEFAULT NOW(),
     796            total FLOAT,
     797            available FLOAT,
     798            used FLOAT,
     799            hostsOver98 SMALLINT);
     800SQL
     801
     802      $query->execute;
     803    $query = $self->{_db}->prepare(<<SQL);
     804    CREATE TABLE hosts (
     805            host VARCHAR(128),
     806            total FLOAT,
     807            available FLOAT,
     808            used FLOAT);
     809SQL
     810
     811      $query->execute;
     812
     813        $query = $self->{_db}->prepare(<<SQL);
     814        CREATE INDEX clusterSpaceIndex ON cluster_space (timestamp);
     815SQL
     816
     817      $query->execute;
     818
     819    $self->setRevision(9);
     820}
     821
     822#######################################################################################
     823#
     824# Create revision 10 of the database
     825#
     826#######################################################################################
     827sub createRevision_10 {
     828    my ($self) = @_;
     829
     830    print "* Creating revision 10 of '$self->{_dbName}'\n";
     831
     832    my $query = $self->{_db}->prepare(<<SQL);
     833    ALTER TABLE hosts
     834        ADD COLUMN writable TINYINT NOT NULL DEFAULT 0,
     835        ADD COLUMN readable TINYINT NOT NULL DEFAULT 0;
     836SQL
     837
     838    $query->execute;
     839
     840    $self->setRevision(10);
    534841}
    535842
Note: See TracChangeset for help on using the changeset viewer.