On Feb 11, 2008 10:51 AM, Lars Aronsson <[EMAIL PROTECTED]> wrote:
> Is there any documentation of how people use SQLite in odd ways in
> their everyday activities?
I used to work with the NS-2 network simulator as an undergrad at NMT
for some experiments we were doing in wireless power control systems.
After frustrations with parsing NS-2 data files, I decided to start
adding my own hooks to their code which would print simulation results
in somewhat normalized ASCII tabular formats. Then I imported the
tables into an SQLite database (because it is fast and extremely easy
to setup/use). This was heaven compared to what we were doing before.
Our schema looked something like this (probably there should be more
indexes or primary keys...):
CREATE TABLE ns_phytx (
time REAL,
pktid INTEGER,
nodeid INTEGER,
pkttype STRING,
source INTEGER,
dest INTEGER,
txpwr REAL,
bytes INTEGER,
duration REAL,
x REAL,
y REAL,
PRIMARY KEY (pktid)
);
CREATE TABLE ns_phyrx (
time REAL,
pktid INTEGER,
nodeid INTEGER,
rxpwr REAL,
x REAL,
y REAL,
PRIMARY KEY (pktid, nodeid)
);
CREATE TABLE ns_macrx (
time REAL,
pktid INTEGER,
nodeid INTEGER,
min_SIR REAL,
PRIMARY KEY (pktid, nodeid)
);
CREATE TABLE ns_collision (
time REAL,
nodeid INTEGER,
pktid1 INTEGER,
pktid2 INTEGER,
type INTEGER
);
CREATE TABLE ns_interference (
time REAL,
nodeid INTEGER,
total_power REAL,
accepted_power REAL
);
-- possibly with more tables, depending on what types of simulation
data we were working with
SQLite allowed us to easily and quickly perform aggregations and
joins, and then we could takes those results and do further analysis
and visualization using Octave/MATLAB, GNUPLOT, and other tools.
For example, you can easily get a link gain by joining the ns_phytx
and ns_phyrx tables on pktid. Scrounging around, I found some queries
like this (embedded in a bash script):
function q3() {
title="Histogram of collisions at node $nodeid"
echo -n "q3 [query]"
sqlite3 z.db << EOF > $filename-1
.separator ' '
.mode list
SELECT ROUND(c.time/1 - 0.5) * 1 AS histtime, COUNT(*)
FROM
ns_collision AS c
INNER JOIN
ns_phytx AS t1
ON c.pktid1 = t1.pktid
INNER JOIN
ns_phytx AS t2
ON c.pktid2 = t2.pktid
WHERE
c.nodeid = $nodeid AND
c.type = 1
GROUP BY
histtime;
EOF
echo -n " [gnuplot]"
gnuplot << EOF
set term postscript eps
set xrange [$starttime:$endtime]
set yrange [0:$collscale]
set title "$title"
set output '$filename.eps'
plot \
'$filename-1' using (\$1):(\$2) \
t "sum over 1 second intervals" w impulses
EOF
echo " $title"
}
I don't really hear a lot of people talking about how they used
databases to collect and assist with analysis of simulation results.
Has anyone else been doing something like this? I think SQL is
excellent for this.
-David
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
