Move bundled copy of sqlite one level deeper to better separate it...

third_party/sqlite/test/collate3.test

-# 2001 September 15
-#
-# 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.  The
-# focus of this script is page cache subsystem.
-#
-# $Id: collate3.test,v 1.13 2008/08/20 16:35:10 drh Exp $
-
-set testdir [file dirname $argv0]
-source $testdir/tester.tcl
-
-#
-# Tests are organised as follows:
-#
-# collate3.1.* - Errors related to unknown collation sequences.
-# collate3.2.* - Errors related to undefined collation sequences.
-# collate3.3.* - Writing to a table that has an index with an undefined c.s.
-# collate3.4.* - Misc errors.
-# collate3.5.* - Collation factory.
-#
-
-#
-# These tests ensure that when a user executes a statement with an 
-# unknown collation sequence an error is returned.
-#
-do_test collate3-1.0 {
-  execsql {
-    CREATE TABLE collate3t1(c1);
-  }
-} {}
-do_test collate3-1.1 {
-  catchsql {
-    SELECT * FROM collate3t1 ORDER BY 1 collate garbage;
-  }
-} {1 {no such collation sequence: garbage}}
-do_test collate3-1.2 {
-  catchsql {
-    CREATE TABLE collate3t2(c1 collate garbage);
-  }
-} {1 {no such collation sequence: garbage}}
-do_test collate3-1.3 {
-  catchsql {
-    CREATE INDEX collate3i1 ON collate3t1(c1 COLLATE garbage);
-  }
-} {1 {no such collation sequence: garbage}}
-
-execsql {
-  DROP TABLE collate3t1;
-}
-
-#
-# Create a table with a default collation sequence, then close
-# and re-open the database without re-registering the collation
-# sequence. Then make sure the library stops us from using
-# the collation sequence in:
-# * an explicitly collated ORDER BY
-# * an ORDER BY that uses the default collation sequence
-# * an expression (=)
-# * a CREATE TABLE statement
-# * a CREATE INDEX statement that uses a default collation sequence
-# * a GROUP BY that uses the default collation sequence
-# * a SELECT DISTINCT that uses the default collation sequence
-# * Compound SELECTs that uses the default collation sequence
-# * An ORDER BY on a compound SELECT with an explicit ORDER BY.
-#
-do_test collate3-2.0 {
-  db collate string_compare {string compare}
-  execsql {
-    CREATE TABLE collate3t1(c1 COLLATE string_compare, c2);
-  }
-  db close
-  sqlite3 db test.db
-  expr 0
-} 0
-do_test collate3-2.1 {
-  catchsql {
-    SELECT * FROM collate3t1 ORDER BY 1 COLLATE string_compare;
-  }
-} {1 {no such collation sequence: string_compare}} 
-do_test collate3-2.2 {
-  catchsql {
-    SELECT * FROM collate3t1 ORDER BY c1;
-  }
-} {1 {no such collation sequence: string_compare}} 
-do_test collate3-2.3 {
-  catchsql {
-    SELECT * FROM collate3t1 WHERE c1 = 'xxx';
-  }
-} {1 {no such collation sequence: string_compare}} 
-do_test collate3-2.4 {
-  catchsql {
-    CREATE TABLE collate3t2(c1 COLLATE string_compare);
-  }
-} {1 {no such collation sequence: string_compare}} 
-do_test collate3-2.5 {
-  catchsql {
-    CREATE INDEX collate3t1_i1 ON collate3t1(c1);
-  }
-} {1 {no such collation sequence: string_compare}}
-do_test collate3-2.6 {
-  catchsql {
-    SELECT * FROM collate3t1;
-  }
-} {0 {}}
-do_test collate3-2.7.1 {
-  catchsql {
-    SELECT count(*) FROM collate3t1 GROUP BY c1;
-  }
-} {1 {no such collation sequence: string_compare}} 
-# do_test collate3-2.7.2 {
-#   catchsql {
-#     SELECT * FROM collate3t1 GROUP BY c1;
-#   }
-# } {1 {GROUP BY may only be used on aggregate queries}}
-do_test collate3-2.7.2 {
-  catchsql {
-    SELECT * FROM collate3t1 GROUP BY c1;
-  }
-} {1 {no such collation sequence: string_compare}} 
-do_test collate3-2.8 {
-  catchsql {
-    SELECT DISTINCT c1 FROM collate3t1;
-  }
-} {1 {no such collation sequence: string_compare}} 
-
-ifcapable compound {
-  do_test collate3-2.9 {
-    catchsql {
-      SELECT c1 FROM collate3t1 UNION SELECT c1 FROM collate3t1;
-    }
-  } {1 {no such collation sequence: string_compare}} 
-  do_test collate3-2.10 {
-    catchsql {
-      SELECT c1 FROM collate3t1 EXCEPT SELECT c1 FROM collate3t1;
-    }
-  } {1 {no such collation sequence: string_compare}} 
-  do_test collate3-2.11 {
-    catchsql {
-      SELECT c1 FROM collate3t1 INTERSECT SELECT c1 FROM collate3t1;
-    }
-  } {1 {no such collation sequence: string_compare}} 
-  do_test collate3-2.12 {
-    catchsql {
-      SELECT c1 FROM collate3t1 UNION ALL SELECT c1 FROM collate3t1;
-    }
-  } {0 {}}
-  do_test collate3-2.13 {
-    catchsql {
-      SELECT 10 UNION ALL SELECT 20 ORDER BY 1 COLLATE string_compare;
-    }
-  } {1 {no such collation sequence: string_compare}} 
-  do_test collate3-2.14 {
-    catchsql {
-      SELECT 10 INTERSECT SELECT 20 ORDER BY 1 COLLATE string_compare;
-    }
-  } {1 {no such collation sequence: string_compare}} 
-  do_test collate3-2.15 {
-    catchsql {
-      SELECT 10 EXCEPT SELECT 20 ORDER BY 1 COLLATE string_compare;
-    }
-  } {1 {no such collation sequence: string_compare}} 
-  do_test collate3-2.16 {
-    catchsql {
-      SELECT 10 UNION SELECT 20 ORDER BY 1 COLLATE string_compare;
-    }
-  } {1 {no such collation sequence: string_compare}} 
-  do_test collate3-2.17 {
-    catchsql {
-      SELECT c1 FROM collate3t1 UNION ALL SELECT c1 FROM collate3t1 ORDER BY 1;
-    }
-  } {1 {no such collation sequence: string_compare}} 
-} ;# ifcapable compound
-
-#
-# Create an index that uses a collation sequence then close and
-# re-open the database without re-registering the collation
-# sequence. Then check that for the table with the index 
-# * An INSERT fails,
-# * An UPDATE on the column with the index fails,
-# * An UPDATE on a different column succeeds.
-# * A DELETE with a WHERE clause fails
-# * A DELETE without a WHERE clause succeeds
-#
-# Also, ensure that the restrictions tested by collate3-2.* still
-# apply after the index has been created.
-#
-do_test collate3-3.0 {
-  db collate string_compare {string compare}
-  execsql {
-    CREATE INDEX collate3t1_i1 ON collate3t1(c1);
-    INSERT INTO collate3t1 VALUES('xxx', 'yyy');
-  }
-  db close
-  sqlite3 db test.db
-  expr 0
-} 0
-db eval {select * from collate3t1}
-do_test collate3-3.1 {
-  catchsql {
-    INSERT INTO collate3t1 VALUES('xxx', 0);
-  }
-} {1 {no such collation sequence: string_compare}} 
-do_test collate3-3.2 {
-  catchsql {
-    UPDATE collate3t1 SET c1 = 'xxx';
-  }
-} {1 {no such collation sequence: string_compare}} 
-do_test collate3-3.3 {
-  catchsql {
-    UPDATE collate3t1 SET c2 = 'xxx';
-  }
-} {0 {}}
-do_test collate3-3.4 {
-  catchsql {
-    DELETE FROM collate3t1 WHERE 1;
-  }
-} {1 {no such collation sequence: string_compare}} 
-do_test collate3-3.5 {
-  catchsql {
-    SELECT * FROM collate3t1;
-  }
-} {0 {xxx xxx}}
-do_test collate3-3.6 {
-  catchsql {
-    DELETE FROM collate3t1;
-  }
-} {0 {}}
-ifcapable {integrityck} {
-  do_test collate3-3.8 {
-    catchsql {
-      PRAGMA integrity_check
-    }
-  } {1 {no such collation sequence: string_compare}}
-}
-do_test collate3-3.9 {
-  catchsql {
-    SELECT * FROM collate3t1;
-  }
-} {0 {}}
-do_test collate3-3.10 {
-  catchsql {
-    SELECT * FROM collate3t1 ORDER BY 1 COLLATE string_compare;
-  }
-} {1 {no such collation sequence: string_compare}} 
-do_test collate3-3.11 {
-  catchsql {
-    SELECT * FROM collate3t1 ORDER BY c1;
-  }
-} {1 {no such collation sequence: string_compare}} 
-do_test collate3-3.12 {
-  catchsql {
-    SELECT * FROM collate3t1 WHERE c1 = 'xxx';
-  }
-} {1 {no such collation sequence: string_compare}} 
-do_test collate3-3.13 {
-  catchsql {
-    CREATE TABLE collate3t2(c1 COLLATE string_compare);
-  }
-} {1 {no such collation sequence: string_compare}} 
-do_test collate3-3.14 {
-  catchsql {
-    CREATE INDEX collate3t1_i2 ON collate3t1(c1);
-  }
-} {1 {no such collation sequence: string_compare}} 
-do_test collate3-3.15 {
-  execsql {
-    DROP TABLE collate3t1;
-  }
-} {}
-
-# Check we can create an index that uses an explicit collation 
-# sequence and then close and re-open the database.
-do_test collate3-4.6 {
-  db collate user_defined "string compare"
-  execsql {
-    CREATE TABLE collate3t1(a, b);
-    INSERT INTO collate3t1 VALUES('hello', NULL);
-    CREATE INDEX collate3i1 ON collate3t1(a COLLATE user_defined);
-  }
-} {}
-do_test collate3-4.7 {
-  db close
-  sqlite3 db test.db
-  catchsql {
-    SELECT * FROM collate3t1 ORDER BY a COLLATE user_defined;
-  }
-} {1 {no such collation sequence: user_defined}}
-do_test collate3-4.8.1 {
-  db collate user_defined "string compare"
-  catchsql {
-    SELECT * FROM collate3t1 ORDER BY a COLLATE user_defined;
-  }
-} {0 {hello {}}}
-do_test collate3-4.8.2 {
-  db close
-  lindex [catch {
-    sqlite3 db test.db
-  }] 0
-} {0}
-do_test collate3-4.8.3 {
-  execsql {
-    DROP TABLE collate3t1;
-  }
-} {}
-
-# Compare strings as numbers.
-proc numeric_compare {lhs rhs} {
-  if {$rhs > $lhs} {
-    set res -1
-  } else {
-    set res [expr ($lhs > $rhs)?1:0]
-  }
-  return $res
-}
-
-# Check we can create a view that uses an explicit collation 
-# sequence and then close and re-open the database.
-ifcapable view {
-do_test collate3-4.9 {
-  db collate user_defined numeric_compare
-  execsql {
-    CREATE TABLE collate3t1(a, b);
-    INSERT INTO collate3t1 VALUES('2', NULL);
-    INSERT INTO collate3t1 VALUES('101', NULL);
-    INSERT INTO collate3t1 VALUES('12', NULL);
-    CREATE VIEW collate3v1 AS SELECT * FROM collate3t1 
-        ORDER BY 1 COLLATE user_defined;
-    SELECT * FROM collate3v1;
-  }
-} {2 {} 12 {} 101 {}}
-do_test collate3-4.10 {
-  db close
-  sqlite3 db test.db
-  catchsql {
-    SELECT * FROM collate3v1;
-  }
-} {1 {no such collation sequence: user_defined}}
-do_test collate3-4.11 {
-  db collate user_defined numeric_compare
-  catchsql {
-    SELECT * FROM collate3v1;
-  }
-} {0 {2 {} 12 {} 101 {}}}
-do_test collate3-4.12 {
-  execsql {
-    DROP TABLE collate3t1;
-  }
-} {}
-} ;# ifcapable view
-
-#
-# Test the collation factory. In the code, the "no such collation sequence"
-# message is only generated in two places. So these tests just test that
-# the collation factory can be called once from each of those points.
-#
-do_test collate3-5.0 {
-  catchsql {
-    CREATE TABLE collate3t1(a);
-    INSERT INTO collate3t1 VALUES(10);
-    SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;
-  }
-} {1 {no such collation sequence: unk}}
-do_test collate3-5.1 {
-  set ::cfact_cnt 0
-  proc cfact {nm} {
-    db collate $nm {string compare}
-    incr ::cfact_cnt
-  }
-  db collation_needed cfact
-} {}
-do_test collate3-5.2 {
-  catchsql {
-    SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;
-  }
-} {0 10}
-do_test collate3-5.3 {
-  set ::cfact_cnt
-} {1}
-do_test collate3-5.4 {
-  catchsql {
-    SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;
-  }
-} {0 10}
-do_test collate3-5.5 {
-  set ::cfact_cnt
-} {1}
-do_test collate3-5.6 {
-  catchsql {
-    SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;
-  }
-} {0 10}
-do_test collate3-5.7 {
-  execsql {
-    DROP TABLE collate3t1;
-    CREATE TABLE collate3t1(a COLLATE unk);
-  }
-  db close
-  sqlite3 db test.db
-  catchsql {
-    SELECT a FROM collate3t1 ORDER BY 1;
-  }
-} {1 {no such collation sequence: unk}}
-do_test collate3-5.8 {
-  set ::cfact_cnt 0
-  proc cfact {nm} {
-    db collate $nm {string compare}
-    incr ::cfact_cnt
-  }
-  db collation_needed cfact
-  catchsql {
-    SELECT a FROM collate3t1 ORDER BY 1;
-  }
-} {0 {}}
-
-do_test collate3-5.9 {
-  execsql {
-    DROP TABLE collate3t1;
-  }
-} {}
-
-finish_test