# 2018 May 19
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
#
package require sqlite3
package require Pgtcl
set db [pg_connect -conninfo "dbname=postgres user=postgres password=postgres"]
sqlite3 sqlite ""
proc execsql {sql} {
set lSql [list]
set frag ""
while {[string length $sql]>0} {
set i [string first ";" $sql]
if {$i>=0} {
append frag [string range $sql 0 $i]
set sql [string range $sql $i+1 end]
if {[sqlite complete $frag]} {
lappend lSql $frag
set frag ""
}
} else {
set frag $sql
set sql ""
}
}
if {$frag != ""} {
lappend lSql $frag
}
#puts $lSql
set ret ""
foreach stmt $lSql {
set res [pg_exec $::db $stmt]
set err [pg_result $res -error]
if {$err!=""} { error $err }
for {set i 0} {$i < [pg_result $res -numTuples]} {incr i} {
if {$i==0} {
set ret [pg_result $res -getTuple 0]
} else {
append ret " [pg_result $res -getTuple $i]"
}
# lappend ret {*}[pg_result $res -getTuple $i]
}
pg_result $res -clear
}
set ret
}
proc execsql_test {tn sql} {
set res [execsql $sql]
puts $::fd "do_execsql_test $tn {"
puts $::fd " [string trim $sql]"
puts $::fd "} {$res}"
puts $::fd ""
}
proc start_test {name date} {
set dir [file dirname $::argv0]
set output [file join $dir $name.test]
set ::fd [open $output w]
puts $::fd [string trimleft "
# $date
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
####################################################
# DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED!
####################################################
"]
puts $::fd {set testdir [file dirname $argv0]}
puts $::fd {source $testdir/tester.tcl}
puts $::fd "set testprefix $name"
puts $::fd ""
}
proc -- {args} {
puts $::fd "# $args"
}
proc ========== {args} {
puts $::fd "#[string repeat = 74]"
puts $::fd ""
}
proc finish_test {} {
puts $::fd finish_test
close $::fd
}
#=========================================================================
start_test window2 "2018 May 19"
execsql_test 1.0 {
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER);
INSERT INTO t1 VALUES(1, 'odd', 'one', 1);
INSERT INTO t1 VALUES(2, 'even', 'two', 2);
INSERT INTO t1 VALUES(3, 'odd', 'three', 3);
INSERT INTO t1 VALUES(4, 'even', 'four', 4);
INSERT INTO t1 VALUES(5, 'odd', 'five', 5);
INSERT INTO t1 VALUES(6, 'even', 'six', 6);
}
execsql_test 1.1 {
SELECT c, sum(d) OVER (PARTITION BY b ORDER BY c) FROM t1;
}
execsql_test 1.2 {
SELECT sum(d) OVER () FROM t1;
}
execsql_test 1.3 {
SELECT sum(d) OVER (PARTITION BY b) FROM t1;
}
puts $::fd finish_test
==========
execsql_test 2.1 {
SELECT a, sum(d) OVER (
PARTITION BY b ORDER BY d
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) FROM t1
}
execsql_test 2.2 {
SELECT a, sum(d) OVER (
ORDER BY b
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) FROM t1
}
execsql_test 2.3 {
SELECT a, sum(d) OVER (
ORDER BY d
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) FROM t1
}
execsql_test 2.4 {
SELECT a, sum(d) OVER (
ORDER BY d
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) FROM t1
}
execsql_test 2.5 {
SELECT a, sum(d) OVER (
ORDER BY d
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
) FROM t1
}
finish_test