1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
|
# 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
|
