AryaWu/sqlite

1#2# 2007 November 123#4# The author disclaims copyright to this source code.  In place of5# a legal notice, here is a blessing:6#7#    May you do good and not evil.8#    May you find forgiveness for yourself and forgive others.9#    May you share freely, never taking more than you give.10#11#***********************************************************************12# This file implements regression tests for SQLite library.  The13# focus of this script is making sure that the names of collation14# sequences may be quoted using double quotes in SQL statements.15#16# $Id: collate9.test,v 1.2 2008/07/10 00:32:42 drh Exp $17 18set testdir [file dirname $argv0]19source $testdir/tester.tcl20 21proc reverse_sort {lhs rhs} {22  return [string compare $rhs $lhs]23}24db collate "reverse sort" reverse_sort25 26# This procedure executes the SQL.  Then it checks to see if the OP_Sort27# opcode was executed.  If an OP_Sort did occur, then "sort" is appended28# to the result.  If no OP_Sort happened, then "nosort" is appended.29#30# This procedure is used to check to make sure sorting is or is not31# occurring as expected.32#33proc cksort {sql} {34  set ::sqlite_sort_count 035  set data [execsql $sql]36  if {$::sqlite_sort_count} {set x sort} {set x nosort}37  lappend data $x38  return $data39}40 41# Test plan:42#43#     collate9-1.* - Test collation sequences attached to table columns44#     collate9-2.* - Test collation sequences attached to expressions45#     collate9-3.* - Test collation sequences attached to an index46#     collate9-4.* - Test collation sequences as an argument to REINDEX47#48 49do_test collate9-1.1 {50  execsql {51    CREATE TABLE xy(x COLLATE "reverse sort", y COLLATE binary);52    INSERT INTO xy VALUES('one', 'one');53    INSERT INTO xy VALUES('two', 'two');54    INSERT INTO xy VALUES('three', 'three');55  }56} {}57do_test collate9-1.2 {58  execsql { 59    SELECT x FROM xy ORDER BY x60  }61} {two three one}62do_test collate9-1.3 {63  execsql { 64    SELECT y FROM xy ORDER BY y65  }66} {one three two}67do_test collate9-1.4 {68  cksort { 69    SELECT x FROM xy ORDER BY x70  }71} {two three one sort}72do_test collate9-1.5 {73  execsql { 74    CREATE INDEX xy_i ON xy(x)75  }76} {}77do_test collate9-1.6 {78  cksort { 79    SELECT x FROM xy ORDER BY x80  }81} {two three one nosort}82 83do_test collate9-2.1 {84  execsql { 85    SELECT x, x < 'seven' FROM xy ORDER BY x86  }87} {two 1 three 1 one 0}88do_test collate9-2.2 {89  execsql { 90    SELECT y, y < 'seven' FROM xy ORDER BY x91  }92} {two 0 three 0 one 1}93do_test collate9-2.3 {94  execsql { 95    SELECT y, y COLLATE "reverse sort" < 'seven' FROM xy ORDER BY x96  }97} {two 1 three 1 one 0}98do_test collate9-2.4 {99  execsql {100    SELECT y FROM xy ORDER BY y101  }102} {one three two}103do_test collate9-2.5 {104  execsql {105    SELECT y FROM xy ORDER BY y COLLATE "reverse sort"106  }107} {two three one}108do_test collate9-2.6 {109  execsql {110    SELECT y COLLATE "reverse sort" AS aaa FROM xy ORDER BY aaa111  }112} {two three one}113 114do_test collate9-3.1 {115  execsql {116    CREATE INDEX xy_i2 ON xy(y COLLATE "reverse sort");117  }118} {}119do_test collate9-3.2 {120  cksort { 121    SELECT y FROM xy ORDER BY y 122  }123} {one three two sort}124do_test collate9-3.3 {125  cksort { 126    SELECT y FROM xy ORDER BY y COLLATE "reverse sort"127  }128} {two three one nosort}129do_test collate9-3.4 {130  cksort { 131    SELECT y AS aaa FROM xy ORDER BY aaa132  }133} {one three two sort}134do_test collate9-3.5 {135  cksort { 136    SELECT y COLLATE "reverse sort" AS aaa FROM xy ORDER BY aaa137  }138} {two three one nosort}139 140ifcapable reindex {141  do_test collate9-4.1 {142    execsql {143      REINDEX "reverse sort"144    }145  } {}146 147  # Modify the "reverse sort" collation so that it now sorts in the same148  # order as binary.149  proc reverse_sort {lhs rhs} {150    return [string compare $lhs $rhs]151  }152 153  # The integrity check should now fail because the indexes created using154  # "reverse sort" are no longer in sync with the collation sequence155  # implementation.156  do_test collate9-4.2 {157    expr {"ok" eq [execsql { PRAGMA integrity_check }]}158  } {0}159 160  do_test collate9-4.3 {161    execsql {162      REINDEX "reverse sort"163    }164  } {}165 166  # Integrity check should now pass.167  do_test collate9-4.4 {168    expr {"ok" eq [execsql { PRAGMA integrity_check }]}169  } {1}170 171  do_test collate9-4.5 {172    cksort {173      SELECT x FROM xy ORDER BY x COLLATE "reverse sort"174    }175  } {one three two nosort}176}177 178finish_test179