Port the "full" sqlite3 extension to Jim Tcl

Signed-off-by: Steve Bennett <steveb@workware.net.au>

1 files changed, 51 insertions, 0 deletions

diff --git a/sqlite3/test-sqlite3.tcl b/sqlite3/test-sqlite3.tcl
new file mode 100644
index 0000000..cd13cf4
--- /dev/null
+++ b/sqlite3/test-sqlite3.tcl
@@ -0,0 +1,51 @@
+# A simple test of the "big" sqlite3 extension
+
+lappend auto_path .
+
+package require sqlite3
+
+# Create an in-memory database and add some data
+sqlite3 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