limbo/testing/glob.test

#!/usr/bin/env tclsh

set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_execsql_test glob-fn {
    select name, glob('sweat*', name) from products;
} {hat|0
cap|0
shirt|0
sweater|1
sweatshirt|1
shorts|0
jeans|0
sneakers|0
boots|0
coat|0
accessories|0}

do_execsql_test where-glob {
    select * from products where name glob 'sweat*';
} {4|sweater|25.0
5|sweatshirt|74.0}

do_execsql_test where-glob-question-mark {
    select * from products where name glob 'sweat?r';
} {4|sweater|25.0}

do_execsql_test where-glob-fn {
    select * from products where glob('sweat*', name)=1
} {4|sweater|25.0
5|sweatshirt|74.0}

do_execsql_test where-not-glob-and {
    select * from products where name not glob 'sweat*' and price >= 70.0;
} {1|hat|79.0
2|cap|82.0
6|shorts|70.0
7|jeans|78.0
8|sneakers|82.0
11|accessories|81.0}

do_execsql_test where-glob-or {
    select * from products where name glob 'sweat*' or price >= 80.0;
} {2|cap|82.0
4|sweater|25.0
5|sweatshirt|74.0
8|sneakers|82.0
11|accessories|81.0}

do_execsql_test where-glob-another-column {
    select first_name, last_name from users where last_name glob first_name;
} {James|James
Daniel|Daniel
Taylor|Taylor}

do_execsql_test where-glob-another-column-prefix {
    select first_name, last_name from users where last_name glob concat(first_name, '*');
} {James|James
Daniel|Daniel
William|Williams
John|Johnson
Taylor|Taylor
John|Johnson
Stephen|Stephens
Robert|Roberts}

do_execsql_test where-glob-impossible {
    select * from products where 'foobar' glob 'fooba';
} {}

do_execsql_test_on_specific_db {:memory:} glob-null-other-types {
    DROP TABLE IF EXISTS t0;
    CREATE TABLE IF NOT EXISTS t0 (c0 REAL);
    UPDATE t0 SET c0='C2IS*24', c0=0Xffffffffbfc4330f, c0=0.6463854797956918 WHERE ((((((((t0.c0)AND(t0.c0)))AND(0.23913649834358142)))OR(CASE t0.c0  WHEN t0.c0 THEN 'j2' WHEN t0.c0 THEN t0.c0 WHEN t0.c0 THEN t0.c0 END)))OR(((((((((t0.c0)AND(t0.c0)))AND(t0.c0)))OR(t0.c0)))AND(t0.c0)))); 
    INSERT INTO t0 VALUES (NULL);
    INSERT INTO t0 VALUES ('0&');
    UPDATE t0 SET c0=2352448 WHERE ((((t0.c0)GLOB(t0.c0))) NOT NULL);
    SELECT * from t0;
} {
    {}
    2352448.0
}

foreach {testnum pattern text ans} {
   1  abcdefg   abcdefg   1
   2  abcdefG   abcdefg   0
   3  abcdef    abcdefg   0
   4  abcdefgh  abcdefg   0
   5  abcdef?   abcdefg   1
   6  abcdef?   abcdef    0
   7  abcdef?   abcdefgh  0
   8  abcdefg   abcdef?   0
   9  abcdef?   abcdef?   1
  10  abc/def   abc/def   1
  11  abc//def  abc/def   0
  12  */abc/*   x/abc/y   1
  13  */abc/*   /abc/     1
  16  */abc/*   x///a/ab/abc   0
  17  */abc/*   x//a/ab/abc/   1
  16  */abc/*   x///a/ab/abc   0
  17  */abc/*   x//a/ab/abc/   1
  18  **/abc/** x//a/ab/abc/   1
  19  *?/abc/*? x//a/ab/abc/y  1
  20  ?*/abc/?* x//a/ab/abc/y  1
  21  {abc[cde]efg}   abcbefg  0
  22  {abc[cde]efg}   abccefg  1
  23  {abc[cde]efg}   abcdefg  1
  24  {abc[cde]efg}   abceefg  1
  25  {abc[cde]efg}   abcfefg  0
  26  {abc[^cde]efg}  abcbefg  1
  27  {abc[^cde]efg}  abccefg  0
  28  {abc[^cde]efg}  abcdefg  0
  29  {abc[^cde]efg}  abceefg  0
  30  {abc[^cde]efg}  abcfefg  1
  31  {abc[c-e]efg}   abcbefg  0
  32  {abc[c-e]efg}   abccefg  1
  33  {abc[c-e]efg}   abcdefg  1
  34  {abc[c-e]efg}   abceefg  1
  35  {abc[c-e]efg}   abcfefg  0
  36  {abc[^c-e]efg}  abcbefg  1
  37  {abc[^c-e]efg}  abccefg  0
  38  {abc[^c-e]efg}  abcdefg  0
  39  {abc[^c-e]efg}  abceefg  0
  40  {abc[^c-e]efg}  abcfefg  1
  41  {abc[c-e]efg}   abc-efg  0
  42  {abc[-ce]efg}   abc-efg  1
  43  {abc[ce-]efg}   abc-efg  1
  44  {abc[][*?]efg}  {abc]efg} 1
  45  {abc[][*?]efg}  {abc*efg} 1
  46  {abc[][*?]efg}  {abc?efg} 1
  47  {abc[][*?]efg}  {abc[efg} 1
  48  {abc[^][*?]efg} {abc]efg} 0
  49  {abc[^][*?]efg} {abc*efg} 0
  50  {abc[^][*?]efg} {abc?efg} 0
  51  {abc[^][*?]efg} {abc[efg} 0
  52  {abc[^][*?]efg} {abcdefg} 1
  53  {*[xyz]efg}     {abcxefg} 1
  54  {*[xyz]efg}     {abcwefg} 0
  55  {[-c]}          {c} 1
  56  {[-c]}          {-} 1
  57  {[-c]}          {x} 0
} {
  do_execsql_test glob-$testnum.1 "SELECT glob ( '$pattern' , '$text' )" $::ans
}


foreach {testnum pattern text ans} {  
    1  {abc[}   {abc[} 0
    2  {abc[}   {abc} 0
    3  {a]b}    {a]b} 1
    4  {a]b}    {a[b} 0
} {
  do_execsql_test glob-unenclosed-$testnum.1 "SELECT glob ( '$pattern' , '$text' )" $::ans
}