MetriVis Database

Overview

This page contains detailed information about the structure of the database that is used by MetriVis.

Database Structure

MetriVis uses SQLite and has a very simple database structure, it consists of following dbs:

label: saves the names of the data columns (describtive), consists of:

       CREATE TABLE label (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT,
            attributes TEXT
            );

Where attributes are not used at the moment.

data: one big table saving all x/y data. First column determines the time (x axis) saved as unix-time. All following collumns, labled as m1..m[i]..m[n] save data points (y axis). Where n determines the number of data plots, i the id in the label database.
```
     CREATE TABLE data (
           time_slice FLOAT PRIMARY KEY,  m1    FLOAT,
           m2    FLOAT,
           m3    FLOAT,
           m4    FLOAT,
           m5    FLOAT,
           m6    FLOAT,
           ...
           ...
           );
     
```

time_line_label: saves describtive names for time line databases. Consists of:

     CREATE TABLE time_line_label (
                 id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT,
                attributes TEXT
        );

Where attributes are not used at the moment.

lime_line_data_[i]: saves the time line information. Each entry represents a time line event. It uses the consists of:

     CREATE TABLE time_line_data_0 (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                start_time FLOAT,
                stop_time FLOAT,
                min_amplitude FLOAT,
                max_amplitude FLOAT,
                title TEXT,
                type TEXT,
                author TEXT,
                description TEXT,
                metrics TEXT,
                latitude FLOAT,
                longitude FLOAT,
                zoom INTEGER
                );

Converting Data: CSV to SQLite

MetriVis comes with a simple perl script that let's the user convert their CSV (coma separated values) data to a MetriVis compatible sqlite3 database. You can browse this script here or download it via subversion from:

https://metrivis.svn.sourceforge.net/svnroot/metrivis/code/db_generation/csv_sqlite.pl

This script will read all csv data in the directory of the script and write them into a sqlite3 database called metrivis.db. At the moment this script is optimized to read in netflow data at the ETH Zurich. This data has a format similiar to this:

some_seed, start_time, stop_time, mbuf, netflow_metric_name1, netflow_metric_name2, ..
        0,  134123410, 134123420,    0,                    4,            246294264, ..
        0,  134123420, 134123430,    0,                    5,            246134264, ..
        0,  134123430, 134123440,    0,                   32,            246231568, ..

The script just reads this csv data file and creates entries according to the header of the file into the label database. For each netflow_metric_name[n] there is one entry in the label database and in the data database. For the label database only the columns starting from offset 4 are important, skip the others. The following code snippet shows this:

  # >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
  # >>>>>>>>>>>> ENTER YOUR CODE HERE:
  # >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
  #
  # In our csv file, skip the first 4 columns, because they do not contain
  # any important data. This may be different in your csv file. Enter the 
  # column number with the first x/y plot data.
  my $offset = 4;

Overview | Download | User Manual | Development