sqlite3/test-sqlite3.tcl

   1 # A simple test of the "big" sqlite3 extension
   2
   3 set auto_path [list . {*}$auto_path]
   4
   5 package require sqlite3
   6
   7 # Create an in-memory database and add some data
   8 sqlite3 db :memory:
   9 db eval {CREATE TABLE history (type, time, value)}
  10 foreach t [range 1 50] {
  11     set temp [rand 100]
  12     db eval {INSERT INTO history (type, time, value) VALUES ('temp', :t, :temp)}
  13 }
  14 foreach t [range 2 50 2] {
  15     set v $([rand 200] / 10.0 + 5)
  16     db eval {INSERT INTO history (type, time, value) VALUES ('voltage', :t, :v)}
  17 }
  18
  19 # Output some data in SVG format.
  20 puts "\nSVG Example\n"
  21
  22 set points {}
  23 db eval {SELECT time,value FROM history
  24          WHERE (time >= 10 and time <= 30) and type = 'voltage'
  25          ORDER BY time DESC} row {
  26     lappend points $row(time),$row(value)
  27 }
  28 puts "<polyline points=\"$points\" />"
  29
  30 # And tabular format with a self outer join
  31 puts "\nTabular Self Outer Join Example\n"
  32
  33 proc showrow {args} {
  34     puts [format "%-12s %-12s %-12s" {*}$args]
  35 }
  36
  37 showrow Time Temp Voltage
  38 showrow ---- ---- -------
  39 db eval {SELECT * FROM (SELECT time, value AS temp FROM history WHERE type = 'temp') AS A
  40         LEFT OUTER JOIN (SELECT time, value AS voltage FROM history WHERE type = 'voltage') AS B
  41         USING (time)
  42         WHERE time >= 10 AND time <= 30
  43         ORDER BY time} row {
  44     showrow $row(time) $row(temp) $row(voltage)
  45 }
  46 set maxtemp [db eval {SELECT max(value) FROM history WHERE type = 'temp'}]
  47 set maxvolt [db eval {SELECT max(value) AS maxvolt FROM history WHERE type = 'voltage'}]
  48 showrow ---- ---- -------
  49 showrow max $maxtemp $maxvolt
  50
  51 db close