Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
preliminary vnstat->sqlite converter
#!/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";
}
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment