52 lines
1.5 KiB
Tcl
52 lines
1.5 KiB
Tcl
# A simple test of the "big" sqlite extension
|
|
|
|
set auto_path [list . {*}$auto_path]
|
|
|
|
package require sqlite
|
|
|
|
# Create an in-memory database and add some data
|
|
sqlite db :memory:
|
|
db eval {CREATE TABLE history (type, time, value)}
|
|
foreach t [range 1 50] {
|
|
set temp [rand 100]
|
|
db eval {INSERT INTO history (type, time, value) VALUES ('temp', :t, :temp)}
|
|
}
|
|
foreach t [range 2 50 2] {
|
|
set v $([rand 200] / 10.0 + 5)
|
|
db eval {INSERT INTO history (type, time, value) VALUES ('voltage', :t, :v)}
|
|
}
|
|
|
|
# Output some data in SVG format.
|
|
puts "\nSVG Example\n"
|
|
|
|
set points {}
|
|
db eval {SELECT time,value FROM history
|
|
WHERE (time >= 10 and time <= 30) and type = 'voltage'
|
|
ORDER BY time DESC} row {
|
|
lappend points $row(time),$row(value)
|
|
}
|
|
puts "<polyline points=\"$points\" />"
|
|
|
|
# And tabular format with a self outer join
|
|
puts "\nTabular Self Outer Join Example\n"
|
|
|
|
proc showrow {args} {
|
|
puts [format "%-12s %-12s %-12s" {*}$args]
|
|
}
|
|
|
|
showrow Time Temp Voltage
|
|
showrow ---- ---- -------
|
|
db eval {SELECT * FROM (SELECT time, value AS temp FROM history WHERE type = 'temp') AS A
|
|
LEFT OUTER JOIN (SELECT time, value AS voltage FROM history WHERE type = 'voltage') AS B
|
|
USING (time)
|
|
WHERE time >= 10 AND time <= 30
|
|
ORDER BY time} row {
|
|
showrow $row(time) $row(temp) $row(voltage)
|
|
}
|
|
set maxtemp [db eval {SELECT max(value) FROM history WHERE type = 'temp'}]
|
|
set maxvolt [db eval {SELECT max(value) AS maxvolt FROM history WHERE type = 'voltage'}]
|
|
showrow ---- ---- -------
|
|
showrow max $maxtemp $maxvolt
|
|
|
|
db close
|