# 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; } ========== execsql_test 2.1 { SELECT a, sum(d) OVER ( ORDER BY d ROWS BETWEEN 1000 PRECEDING AND 1 FOLLOWING ) FROM t1 } execsql_test 2.2 { SELECT a, sum(d) OVER ( ORDER BY d ROWS BETWEEN 1000 PRECEDING AND 1000 FOLLOWING ) FROM t1 } execsql_test 2.3 { SELECT a, sum(d) OVER ( ORDER BY d ROWS BETWEEN 1 PRECEDING AND 1000 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 0 FOLLOWING ) FROM t1 } execsql_test 2.6 { SELECT a, sum(d) OVER ( PARTITION BY b ORDER BY d ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) FROM t1 } execsql_test 2.7 { SELECT a, sum(d) OVER ( PARTITION BY b ORDER BY d ROWS BETWEEN 0 PRECEDING AND 0 FOLLOWING ) FROM t1 } execsql_test 2.8 { SELECT a, sum(d) OVER ( ORDER BY d ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING ) FROM t1 } execsql_test 2.9 { SELECT a, sum(d) OVER ( ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING ) FROM t1 } execsql_test 2.10 { SELECT a, sum(d) OVER ( ORDER BY d ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING ) FROM t1 } execsql_test 2.11 { SELECT a, sum(d) OVER ( ORDER BY d ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) FROM t1 } execsql_test 2.13 { SELECT a, sum(d) OVER ( ORDER BY d ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING ) FROM t1 } execsql_test 2.14 { SELECT a, sum(d) OVER ( ORDER BY d ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING ) FROM t1 } execsql_test 2.15 { SELECT a, sum(d) OVER ( PARTITION BY b ORDER BY d ROWS BETWEEN 1 PRECEDING AND 0 PRECEDING ) FROM t1 } execsql_test 2.16 { SELECT a, sum(d) OVER ( PARTITION BY b ORDER BY d ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) FROM t1 } execsql_test 2.17 { SELECT a, sum(d) OVER ( PARTITION BY b ORDER BY d ROWS BETWEEN 1 PRECEDING AND 2 PRECEDING ) FROM t1 } execsql_test 2.18 { SELECT a, sum(d) OVER ( PARTITION BY b ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING ) FROM t1 } execsql_test 2.19 { SELECT a, sum(d) OVER ( PARTITION BY b ORDER BY d ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING ) FROM t1 } execsql_test 2.20 { SELECT a, sum(d) OVER ( ORDER BY d ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING ) FROM t1 } execsql_test 2.21 { SELECT a, sum(d) OVER ( ORDER BY d ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING ) FROM t1 } execsql_test 2.22 { SELECT a, sum(d) OVER ( PARTITION BY b ORDER BY d ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING ) FROM t1 } execsql_test 2.23 { SELECT a, sum(d) OVER ( ORDER BY d ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t1 } execsql_test 2.24 { SELECT a, sum(d) OVER ( PARTITION BY a%2 ORDER BY d ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t1 } execsql_test 2.25 { SELECT a, sum(d) OVER ( ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t1 } execsql_test 2.26 { SELECT a, sum(d) OVER ( PARTITION BY b ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t1 } execsql_test 2.27 { SELECT a, sum(d) OVER ( ORDER BY d ROWS BETWEEN CURRENT ROW AND CURRENT ROW ) FROM t1 } execsql_test 2.28 { SELECT a, sum(d) OVER ( PARTITION BY b ORDER BY d ROWS BETWEEN CURRENT ROW AND CURRENT ROW ) FROM t1 } execsql_test 2.29 { SELECT a, sum(d) OVER ( ORDER BY d RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t1 } execsql_test 2.30 { SELECT a, sum(d) OVER ( ORDER BY b RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t1 } ========== puts $::fd finish_test ========== # execsql_test 3.1 { # SELECT a, sum(d) OVER ( # PARTITION BY b ORDER BY d # RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING # ) FROM t1 # } # # execsql_test 3.2 { # SELECT a, sum(d) OVER ( # ORDER BY b # RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING # ) FROM t1 # } # # execsql_test 3.3 { # SELECT a, sum(d) OVER ( # ORDER BY d # ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING # ) FROM t1 # } finish_test