#!/usr/bin/env tclsh

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

do_execsql_test like-fn {
    select name, like('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-like {
    select * from products where name like 'sweat%';
} {4|sweater|25.0
5|sweatshirt|74.0}

do_execsql_test where-like-case-insensitive {
    select * from products where name like 'SWEAT%';
} {4|sweater|25.0
5|sweatshirt|74.0}

do_execsql_test where-like-underscore {
    select * from products where name like 'sweat_r';
} {4|sweater|25.0}

do_execsql_test where-like-underscore-case-insensitive {
    select * from products where name like 'SwEaT_R';
} {4|sweater|25.0}

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

do_execsql_test where-not-like-and {
    select * from products where name not like '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-like-or {
    select * from products where name like '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-like-another-column {
    select first_name, last_name from users where last_name like first_name;
} {James|James
Daniel|Daniel
Taylor|Taylor}

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

do_execsql_test where-like-another-column-prefix {
    select count(*) from users where last_name like 'Pe#rry' escape '#';
} {19}

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

do_execsql_test like-with-backslash {
  select like('\%A', '\A')
} {1}

do_execsql_test like-with-dollar {
  select like('A$%', 'A$')
} {1}

do_execsql_test like-with-dot {
  select like('%a.a', 'aaaa')
} {0}

do_execsql_test like-fn-esc-1  { 
    SELECT like('abcX%', 'abc%' , 'X') 
} 1
do_execsql_test like-fn-esc-2  { 
    SELECT like('abcX%', 'abc5' , 'X') 
} 0
do_execsql_test like-fn-esc-3  { 
    SELECT like('abcX%', 'abc', 'X') 
} 0
do_execsql_test like-fn-esc-4  { 
    SELECT like('abcX%', 'abcX%', 'X') 
} 0
do_execsql_test like-fn-esc-5  { 
    SELECT like('abcX%', 'abc%%', 'X') 
} 0

do_execsql_test like-fn-esc-6  { 
    SELECT like('abcX_', 'abc_' , 'X') 
} 1
do_execsql_test like-fn-esc-7  { 
    SELECT like('abcX_', 'abc5' , 'X') 
} 0
do_execsql_test like-fn-esc-8  { 
    SELECT like('abcX_', 'abc'  , 'X') 
} 0
do_execsql_test like-fn-esc-9  { 
    SELECT like('abcX_', 'abcX_', 'X') 
} 0
do_execsql_test like-fn-esc-10 { 
    SELECT like('abcX_', 'abc__', 'X') 
} 0

do_execsql_test like-fn-esc-11 { 
    SELECT like('abcXX', 'abcX' , 'X') 
} 1
do_execsql_test like-fn-esc-12 { 
    SELECT like('abcXX', 'abc5' , 'X') 
} 0
do_execsql_test like-fn-esc-13 { 
    SELECT like('abcXX', 'abc'  , 'X') 
} 0
do_execsql_test like-fn-esc-14 { 
    SELECT like('abcXX', 'abcXX', 'X') 
} 0