preliminary vnstat->sqlite converter

dbdump_to_sqlite.pl

#!/usr/bin/perl

use strict;
use DBI;
use XML::Simple;
use Getopt::Mixed;

# static vars to emulate boolean behaviour.
use constant false => 0;
use constant true  => 1;

# globals
my $basePath       = "/home/prae/stats.praeclan.com/";
my $dbPath         = $basePath . "vnstat/sqlite/vnstat.db";
my $xmlPath        = $basePath . "vnstat/dbdump/xml/031210_dbdump.xml";
my $xml            = new XML::Simple (KeyAttr=>[]);
my @validTypes     = ('hours', 'days', 'months', 'years');
my @validFunctions = ('formatxml', 'table', 'db');
my $debug          = false;
my $debugLevel     = "NONE";

main();

# Main program execution method.
sub main {
    my ($type, $function) = tokenizeArguments();
    loadFunction(($type, $function));

    exit(0);
}

# Build the parameters using Getopt::Mixed
sub tokenizeArguments {
    
    # Define default values for the functions, if either of -t or -f are ommitted.
    my ($functionType, $function) = ("days", "formatxml");

    Getopt::Mixed::init( q{x=s xmlpath>x
        d=s      dbpath>d
        t=s      type>t
        f=s      function>f
        h        help>h
        debug=s
    } );

    while ( my($option, $value, $pretty) = Getopt::Mixed::nextOption()) {
        OPTION: {
                # Allow this option to poll from a remote location (SSH only for now), or from the given path, depending on keyword).
                $xmlPath       = $value if $option eq 'x';
                $dbPath        = $value if $option eq 'd';
                $functionType  = $value if $option eq 't';
                $function      = $value if $option eq 'f';
                if ($option eq 'debug') {
                    $debug         = true;
                    $debugLevel    = $value;
                }
                printHelp()             if $option eq 'h';
        }
    }
    debug(("DEBUG", "functionType equals: $functionType"));
    debug(("DEBUG", "function equals: $function"));

    (defined $function && grep $_ eq $function, @validFunctions) ? return ($functionType, $function) : printHelp();
}

# Load the appropriate function.
sub loadFunction {
    my ($type, $function) = @_;

    formatXML($type)    if $function eq 'formatxml';
    updateDaysTable()    if $function eq 'db'    && $type eq 'days';
    updateHoursTable()   if $function eq 'db'    && $type eq 'hours';
}

sub updateHoursTable {
    my ($element, $db, $row, $xmlData, $date, $dayKey, $count, $keyLookup, $insertQuery, $updateQuery, $hour, $hourLookup, $hourExistence);
    my ($tmpKey, $tmpDate) = (0, "");
    
    $db = DBI->connect( "dbi:SQLite:" . $dbPath, "", "", { RaiseError => 1, AutoCommit => 1 } ) || die "Unable to connect (error: $DBI::errstr), stopped";
    $xmlData = $xml->XMLin($xmlPath);
    $count = $db->prepare("SELECT COUNT(*) FROM hours WHERE day_key = ?");
    $keyLookup = $db->prepare(
        "SELECT key FROM days WHERE date = date(?)"
    );
    $hourLookup = $db->prepare(
        "SELECT COUNT(*) FROM hours WHERE hour = ? AND day_key = ?"
    );
    
    $insertQuery = $db->prepare(
        "INSERT INTO hours (hour, date, txbytes, rxbytes, day_key) VALUES (?, date(?), ?, ?, ?)"
    );

    $updateQuery = $db->prepare(
        "UPDATE hours SET txbytes=?, rxbytes=? WHERE hour = ? AND day_key = ?"
    );

    foreach $element ( reverse(@{$xmlData->{interface}->{traffic}->{hours}->{hour}}) ) {
        $hour = 23 - $element->{id};
        $date = $element->{date}->{year} . "-" . $element->{date}->{month} . "-" . $element->{date}->{day};
        
        # Lookup the new day_key
        $keyLookup->execute($date);
        $dayKey = $keyLookup->fetch;
        $dayKey = $dayKey->[0];
        
        # Lookup this hour's existence.
        $hourLookup->execute($hour, $dayKey);
        $hourExistence = $hourLookup->fetch;
        $hourExistence = $hourExistence->[0];
        
        # If the key has changed, notify it if it's being debugged.
        if ($dayKey != $tmpKey && $tmpKey != 0) {
            $count->execute($tmpKey);
            $row = $count->fetch;
            debug(("DEBUG","Shifted to a new day_key ($dayKey) [$date]; here's the count of known hours for the previous day_key ($tmpKey) [$tmpDate]: " . $row->[0]));
            $tmpKey  = $dayKey;
            $tmpDate = $date;
        } elsif ($tmpKey == 0) {
            $tmpKey = $dayKey;
            $tmpDate = $date;
        }
        
        if ($hourExistence == 0) {
            debug(("DEBUG","Inserted a new hour ($hour) for $date ($dayKey)"));
            $insertQuery->execute($hour, $date, $element->{tx}*1024, $element->{rx}*1024, $dayKey);
        } else {
            debug(("DEBUG","Updated hour ($hour) for $date ($dayKey)"));
            $updateQuery->execute($element->{tx}*1024, $element->{rx}*1024, $hour, $dayKey);
        }
    }

    $count->execute($dayKey);
    $row = $count->fetch;
    debug(("DEBUG","Count of known hours for the current day_key ($dayKey) [$date]: $row->[0]"));
}

sub updateDaysTable {
    my ($element, $db, $row, $xmlData, $date, $counter, $count, $lookup, $insertQuery, $updateQuery, $newKey);

    $db = DBI->connect( "dbi:SQLite:" . $dbPath, "", "", { RaiseError => 1, AutoCommit => 1 } ) || die "Unable to connect (error: $DBI::errstr), stopped";
    $xmlData = $xml->XMLin($xmlPath);

    $count = $db->prepare("SELECT COUNT(*) FROM days");
    $count->execute();
    $row = $count->fetch;

    $lookup = $db->prepare(
        "SELECT COUNT(*), key FROM days WHERE date = date(?)"
    );

    $insertQuery = $db->prepare(
        "INSERT INTO days (date, txbytes, rxbytes) VALUES (date(?), ?, ?)"
    );

    $updateQuery = $db->prepare(
        "UPDATE days set txbytes=?, rxbytes=? WHERE key = ?"
    );

    debug(("DEBUG", "Count from days table (before): " . $row->[0]));

    foreach $element ( reverse(@{$xmlData->{interface}->{traffic}->{days}->{day}}) ) {
        $date = $element->{date}->{year} . "-" . $element->{date}->{month} . "-" . $element->{date}->{day};
        $lookup->execute($date);
        $counter = $lookup->fetch;
        $counter->[0];
        
        if ($counter->[0] == 0) {
            $insertQuery->execute($date, $element->{tx}*1024, $element->{rx}*1024);
            $newKey =  $db->sqlite_last_insert_rowid();
            debug(("DEBUG","Inserted a new day, $date ($newKey)"));
        } else {
            debug(("DEBUG","Updated day, $date ($counter->[1])"));
            $updateQuery->execute($element->{tx}*1024, $element->{rx}*1024, $counter->[1]);
        }
    }
    $count->execute();
    $row = $count->fetch;
    debug(("DEBUG","Count from days table (after): " . $row->[0]));

}

sub formatXML {
    my $type = shift;
    my ($element, $txBytes, $txKB, $txMB, $txGB, $rxBytes, $rxKB, $rxMB, $rxGB, $date, $xmlData, $xmlArray, $hour);
    $xmlData = $xml->XMLin($xmlPath);

    $xmlArray = $xmlData->{interface}->{traffic}->{hours}->{hour}  if $type eq 'hours';
    $xmlArray = $xmlData->{interface}->{traffic}->{days}->{day}    if $type eq 'days';

    # Print out the header.
    print "                 bytes                     || kilobytes                 || megabytes                 || gigabytes\n";
    print "                 tx          | rx          || tx          | rx          || tx          | rx          || tx          | rx \n";
    foreach $element ( @{$xmlArray} ) {
        $hour = " (" . (23 - $element->{id}) . " " x (2 - (length (23 - $element->{id}))) . ")" if $type eq 'hours';
        $hour = "     "                                                                         if $type eq 'days';

        $txKB = $element->{tx};
        $txBytes = $txKB*1024;
        $txMB = sprintf("%.4f", $txKB/1024);
        $txGB = sprintf("%.4f", $txMB/1024);

        $rxKB = $element->{rx};
        $rxBytes = $rxKB*1024;
        $rxMB = sprintf("%.4f", $rxKB/1024);
        $rxGB = sprintf("%.4f", $rxMB/1024);

        $date = $element->{date}->{month} . "." . $element->{date}->{day} . "." . $element->{date}->{year} . $hour;
        print $date, ": ", $txBytes, " " x (12 - (length $txBytes)), "| ", $rxBytes, " " x (11 - (length $rxBytes)), " || ";
        print $txKB, " " x (12 - (length $txKB)), "| ", $rxKB, " " x (11 - (length $rxKB)), " || ";
        print $txMB, " " x (12 - (length $txMB)), "| ", $rxMB, " " x (11 - (length $rxMB)), " || ";
        print $txGB, " " x (12 - (length $txGB)), "| ", $rxGB, "\n";
    }
}

# Print out the usage statement.
sub printHelp {
    print "This is to be the help message.\n";
    exit(0);
}

# Print out debugging information if 'debug' is specified on the CLI.
sub debug {
    my ($level, $message) = @_;
    if ($debug == true && $level eq $debugLevel) {
        print "[", $level, "] ", $message, "\n";
    }

    exit(8) if $level eq "ERROR";
}

sqlite_schema.sql

BEGIN TRANSACTION;
CREATE TABLE days(key INTEGER PRIMARY KEY, date DATE, txbytes BIGINT, rxbytes BIGINT);
CREATE TABLE months(key INTEGER PRIMARY KEY, month SMALLINT, year SMALLINT, txbytes BIGINT, rxbytes BIGINT);
CREATE TABLE years(key INTEGER PRIMARY KEY, year SMALLINT, txbytes BIGINT, rxbytes BIGINT);
CREATE TABLE hours(key INTEGER PRIMARY KEY, hour SMALLINT, date DATE, txbytes BIGINT, rxbytes BIGINT, day_key INTEGER CONSTRAINT fk_day_key REFERENCES days(key) ON DELETE CASCADE);
COMMIT;
CREATE TRIGGER fki_hour_day_key
BEFORE INSERT ON hours
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'INSERT ON table "hours" violates foreign key constraint "fk_day_key"')
WHERE (SELECT key from days where key = NEW.day_key) IS NULL;
END;
CREATE TRIGGER fku_hour_day_key
BEFORE UPDATE ON hours
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'UPDATE ON table "hours" violates foreign key constraint "fk_day_key"')
WHERE (SELECT key from days where key = NEW.day_key) IS NULL;
END;
CREATE TRIGGER fkd_hour_day_key
BEFORE DELETE ON days
FOR EACH ROW BEGIN
DELETE FROM hours WHERE day_key = OLD.key;
END;