Re: [sqlite] Innovative examples / user stories

[David Baird]

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

Re: [sqlite] Innovative examples / user stories

[Zack Jarrett]

I write GUI automation scripts using the AppleScript language and the  
System Events application agent on Mac OS X 10.4 and 10.5.  These  
scripts are used to QA test Mac software.  My scripts need to write  
applications states to disk, read/write preferences, read input data,  
and compare output of an application to known good states.  All of  
these values are stored in SQLite databases.  I query the data by  
forming sqlite3 command line application commands as strings in  
AppleScript and passing those strings to the shell.

Prior to using SQLite I was using AppleScript's read/write to file  
operations and found them lacking in flexibility and elegance.  I  
investigated using Apple's application agent "Database Events" (which  
uses SQLite in some measure) but found its query language to be  
severely limiting.  It is ever so much nicer to be able to query my  
data using regular old SQL.  Another benefit is that when debugging  
scripts I can easily look into what the script is writing to disk.

I don't know if it's entirely unusual, but SQLite is incredibly useful  
for my purpose.  At some point in the future I plan on having  
AppleScript build it's own scripts in situ (something which it does  
not do well natively) by pulling command strings (based on specific,  
dynamic conditions) out of a SQLite db.

Zack Jarrett

On Feb 11, 2008, at 10:51 AM, Lars Aronsson wrote:

> Is there any documentation of how people use SQLite in odd ways in
> their everyday activities?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Innovative examples / user stories

[Hakki Dogusan]

Hi,

(Sorry, I lost original mail to reply)

Mohd Radzi Ibrahim yazmış:
> [snipped usage example]
> 
> - Original Message - 
> From: "Lars Aronsson" <[EMAIL PROTECTED]>
> To: 
> Sent: Tuesday, February 12, 2008 1:51 AM
> Subject: [sqlite] Innovative examples / user stories
> 
> 
>> Is there any documentation of how people use SQLite in odd ways in
>> their everyday activities?  
>> [snip]
>>

I'm using sqlite3 as a configuration file and snippet code(Lua) storage 
in CairoPad[1] application. My idea was users of CairoPad could share 
their code with others just transferring one sqlite db file (sharing is 
not implemented yet).

[1] http://www.dynaset.org/dogusanh/download.html#cairopad

>>
>> -- 
>>  Lars Aronsson ([EMAIL PROTECTED])
>>  Aronsson Datateknik - http://aronsson.se

--
Regards,
Hakki Dogusan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Innovative examples / user stories

[Mohd Radzi Ibrahim]

Hi,

I used SQLite to move "data-cube" from server to client. The server hosts 
data in MS SQL databases. There is a server-app that run query based on some 
input send by client program. The server then run MSSQL query and generate a 
SQLite db consisting of some tables (fact, dimensions, etc), zip it and send 
back to the client. The client program then allows user to manipulate 
dimensions (to maybe slice/dice the result) without having to access the 
huge databases on the server anymore...


best regards,
radzi.


- Original Message - 
From: "Lars Aronsson" <[EMAIL PROTECTED]>
To: 
Sent: Tuesday, February 12, 2008 1:51 AM
Subject: [sqlite] Innovative examples / user stories


>
> Is there any documentation of how people use SQLite in odd ways in
> their everyday activities?  For example, do you e-mail SQLite DB
> files between you, as if they were Excel spreadsheets?  Or do you
> distribute SQLite database files via BitTorrent?  Even with multi
> table databases? That would be a kind of database use that was
> unheard of in the Oracle/DB2 era, but it certainly should be
> possible with SQLite.  SQLite databases files could be used in a
> "seti @ home" kind of application, where a screensaver downloads
> an existing DB file, processes it for some hours, and then uploads
> the resulting DB file again.  But are any such applications or
> user stories known? Is there a list already?
>
> I've read the Wikipedia article and its list of Google Gears,
> Android, Mac OS X Tiger, etc.  I've also seen the "well-known
> users of SQLite" page on sqlite.org/famous.html and the "When to
> use SQLite" page.
>
> I've used Oracle since version 7 and MySQL since 3.23 (which isn't
> very long at all, but anyway).  The switch from Oracle to MySQL
> was made possible for many people because their database was
> embedded behind a web application anyway (look, no DBA!), so full
> transaction handling wasn't really needed.  That shift in usage
> pattern opened up for a simpler and more affordable solution.
> Many other such technology shifts are described in an old book
> titled "The Innovator's Dilemma".
>
>
> -- 
>  Lars Aronsson ([EMAIL PROTECTED])
>  Aronsson Datateknik - http://aronsson.se
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Innovative examples / user stories

[drh]

Lars Aronsson <[EMAIL PROTECTED]> wrote:
> Is there any documentation of how people use SQLite in odd ways in 
> their everyday activities? 

Did you see

   http://www.sqlite.org/whentouse.html

The document above is not exactly what you are asking for
since it does not list real-world examples, but it does contain
several suggestions on how to best use SQLite.

--
D. Richard Hipp <[EMAIL PROTECTED]>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users