1 # 2001 September 15

     2 #

     3 # The author disclaims copyright to this source code.  In place of

     4 # a legal notice, here is a blessing:

     5 #

     6 #    May you do good and not evil.

     7 #    May you find forgiveness for yourself and forgive others.

     8 #    May you share freely, never taking more than you give.

     9 #

    10 #***********************************************************************

    11 # This file implements regression tests for SQLite library.  The

    12 # focus of this script is page cache subsystem.

    13 #

    14 # $Id: collate3.test,v 1.13 2008/08/20 16:35:10 drh Exp $

    15 

    16 set testdir [file dirname $argv0]

    17 source $testdir/tester.tcl

    18 

    19 #

    20 # Tests are organised as follows:

    21 #

    22 # collate3.1.* - Errors related to unknown collation sequences.

    23 # collate3.2.* - Errors related to undefined collation sequences.

    24 # collate3.3.* - Writing to a table that has an index with an undefined c.s.

    25 # collate3.4.* - Misc errors.

    26 # collate3.5.* - Collation factory.

    27 #

    28 

    29 #

    30 # These tests ensure that when a user executes a statement with an 

    31 # unknown collation sequence an error is returned.

    32 #

    33 do_test collate3-1.0 {

    34   execsql {

    35     CREATE TABLE collate3t1(c1);

    36   }

    37 } {}

    38 do_test collate3-1.1 {

    39   catchsql {

    40     SELECT * FROM collate3t1 ORDER BY 1 collate garbage;

    41   }

    42 } {1 {no such collation sequence: garbage}}

    43 do_test collate3-1.2 {

    44   catchsql {

    45     CREATE TABLE collate3t2(c1 collate garbage);

    46   }

    47 } {1 {no such collation sequence: garbage}}

    48 do_test collate3-1.3 {

    49   catchsql {

    50     CREATE INDEX collate3i1 ON collate3t1(c1 COLLATE garbage);

    51   }

    52 } {1 {no such collation sequence: garbage}}

    53 

    54 execsql {

    55   DROP TABLE collate3t1;

    56 }

    57 

    58 #

    59 # Create a table with a default collation sequence, then close

    60 # and re-open the database without re-registering the collation

    61 # sequence. Then make sure the library stops us from using

    62 # the collation sequence in:

    63 # * an explicitly collated ORDER BY

    64 # * an ORDER BY that uses the default collation sequence

    65 # * an expression (=)

    66 # * a CREATE TABLE statement

    67 # * a CREATE INDEX statement that uses a default collation sequence

    68 # * a GROUP BY that uses the default collation sequence

    69 # * a SELECT DISTINCT that uses the default collation sequence

    70 # * Compound SELECTs that uses the default collation sequence

    71 # * An ORDER BY on a compound SELECT with an explicit ORDER BY.

    72 #

    73 do_test collate3-2.0 {

    74   db collate string_compare {string compare}

    75   execsql {

    76     CREATE TABLE collate3t1(c1 COLLATE string_compare, c2);

    77   }

    78   db close

    79   sqlite3 db test.db

    80   expr 0

    81 } 0

    82 do_test collate3-2.1 {

    83   catchsql {

    84     SELECT * FROM collate3t1 ORDER BY 1 COLLATE string_compare;

    85   }

    86 } {1 {no such collation sequence: string_compare}} 

    87 do_test collate3-2.2 {

    88   catchsql {

    89     SELECT * FROM collate3t1 ORDER BY c1;

    90   }

    91 } {1 {no such collation sequence: string_compare}} 

    92 do_test collate3-2.3 {

    93   catchsql {

    94     SELECT * FROM collate3t1 WHERE c1 = 'xxx';

    95   }

    96 } {1 {no such collation sequence: string_compare}} 

    97 do_test collate3-2.4 {

    98   catchsql {

    99     CREATE TABLE collate3t2(c1 COLLATE string_compare);

   100   }

   101 } {1 {no such collation sequence: string_compare}} 

   102 do_test collate3-2.5 {

   103   catchsql {

   104     CREATE INDEX collate3t1_i1 ON collate3t1(c1);

   105   }

   106 } {1 {no such collation sequence: string_compare}}

   107 do_test collate3-2.6 {

   108   catchsql {

   109     SELECT * FROM collate3t1;

   110   }

   111 } {0 {}}

   112 do_test collate3-2.7.1 {

   113   catchsql {

   114     SELECT count(*) FROM collate3t1 GROUP BY c1;

   115   }

   116 } {1 {no such collation sequence: string_compare}} 

   117 # do_test collate3-2.7.2 {

   118 #   catchsql {

   119 #     SELECT * FROM collate3t1 GROUP BY c1;

   120 #   }

   121 # } {1 {GROUP BY may only be used on aggregate queries}}

   122 do_test collate3-2.7.2 {

   123   catchsql {

   124     SELECT * FROM collate3t1 GROUP BY c1;

   125   }

   126 } {1 {no such collation sequence: string_compare}} 

   127 do_test collate3-2.8 {

   128   catchsql {

   129     SELECT DISTINCT c1 FROM collate3t1;

   130   }

   131 } {1 {no such collation sequence: string_compare}} 

   132 

   133 ifcapable compound {

   134   do_test collate3-2.9 {

   135     catchsql {

   136       SELECT c1 FROM collate3t1 UNION SELECT c1 FROM collate3t1;

   137     }

   138   } {1 {no such collation sequence: string_compare}} 

   139   do_test collate3-2.10 {

   140     catchsql {

   141       SELECT c1 FROM collate3t1 EXCEPT SELECT c1 FROM collate3t1;

   142     }

   143   } {1 {no such collation sequence: string_compare}} 

   144   do_test collate3-2.11 {

   145     catchsql {

   146       SELECT c1 FROM collate3t1 INTERSECT SELECT c1 FROM collate3t1;

   147     }

   148   } {1 {no such collation sequence: string_compare}} 

   149   do_test collate3-2.12 {

   150     catchsql {

   151       SELECT c1 FROM collate3t1 UNION ALL SELECT c1 FROM collate3t1;

   152     }

   153   } {0 {}}

   154   do_test collate3-2.13 {

   155     catchsql {

   156       SELECT 10 UNION ALL SELECT 20 ORDER BY 1 COLLATE string_compare;

   157     }

   158   } {1 {no such collation sequence: string_compare}} 

   159   do_test collate3-2.14 {

   160     catchsql {

   161       SELECT 10 INTERSECT SELECT 20 ORDER BY 1 COLLATE string_compare;

   162     }

   163   } {1 {no such collation sequence: string_compare}} 

   164   do_test collate3-2.15 {

   165     catchsql {

   166       SELECT 10 EXCEPT SELECT 20 ORDER BY 1 COLLATE string_compare;

   167     }

   168   } {1 {no such collation sequence: string_compare}} 

   169   do_test collate3-2.16 {

   170     catchsql {

   171       SELECT 10 UNION SELECT 20 ORDER BY 1 COLLATE string_compare;

   172     }

   173   } {1 {no such collation sequence: string_compare}} 

   174   do_test collate3-2.17 {

   175     catchsql {

   176       SELECT c1 FROM collate3t1 UNION ALL SELECT c1 FROM collate3t1 ORDER BY 1;

   177     }

   178   } {1 {no such collation sequence: string_compare}} 

   179 } ;# ifcapable compound

   180 

   181 #

   182 # Create an index that uses a collation sequence then close and

   183 # re-open the database without re-registering the collation

   184 # sequence. Then check that for the table with the index 

   185 # * An INSERT fails,

   186 # * An UPDATE on the column with the index fails,

   187 # * An UPDATE on a different column succeeds.

   188 # * A DELETE with a WHERE clause fails

   189 # * A DELETE without a WHERE clause succeeds

   190 #

   191 # Also, ensure that the restrictions tested by collate3-2.* still

   192 # apply after the index has been created.

   193 #

   194 do_test collate3-3.0 {

   195   db collate string_compare {string compare}

   196   execsql {

   197     CREATE INDEX collate3t1_i1 ON collate3t1(c1);

   198     INSERT INTO collate3t1 VALUES('xxx', 'yyy');

   199   }

   200   db close

   201   sqlite3 db test.db

   202   expr 0

   203 } 0

   204 db eval {select * from collate3t1}

   205 do_test collate3-3.1 {

   206   catchsql {

   207     INSERT INTO collate3t1 VALUES('xxx', 0);

   208   }

   209 } {1 {no such collation sequence: string_compare}} 

   210 do_test collate3-3.2 {

   211   catchsql {

   212     UPDATE collate3t1 SET c1 = 'xxx';

   213   }

   214 } {1 {no such collation sequence: string_compare}} 

   215 do_test collate3-3.3 {

   216   catchsql {

   217     UPDATE collate3t1 SET c2 = 'xxx';

   218   }

   219 } {0 {}}

   220 do_test collate3-3.4 {

   221   catchsql {

   222     DELETE FROM collate3t1 WHERE 1;

   223   }

   224 } {1 {no such collation sequence: string_compare}} 

   225 do_test collate3-3.5 {

   226   catchsql {

   227     SELECT * FROM collate3t1;

   228   }

   229 } {0 {xxx xxx}}

   230 do_test collate3-3.6 {

   231   catchsql {

   232     DELETE FROM collate3t1;

   233   }

   234 } {0 {}}

   235 ifcapable {integrityck} {

   236   do_test collate3-3.8 {

   237     catchsql {

   238       PRAGMA integrity_check

   239     }

   240   } {1 {no such collation sequence: string_compare}}

   241 }

   242 do_test collate3-3.9 {

   243   catchsql {

   244     SELECT * FROM collate3t1;

   245   }

   246 } {0 {}}

   247 do_test collate3-3.10 {

   248   catchsql {

   249     SELECT * FROM collate3t1 ORDER BY 1 COLLATE string_compare;

   250   }

   251 } {1 {no such collation sequence: string_compare}} 

   252 do_test collate3-3.11 {

   253   catchsql {

   254     SELECT * FROM collate3t1 ORDER BY c1;

   255   }

   256 } {1 {no such collation sequence: string_compare}} 

   257 do_test collate3-3.12 {

   258   catchsql {

   259     SELECT * FROM collate3t1 WHERE c1 = 'xxx';

   260   }

   261 } {1 {no such collation sequence: string_compare}} 

   262 do_test collate3-3.13 {

   263   catchsql {

   264     CREATE TABLE collate3t2(c1 COLLATE string_compare);

   265   }

   266 } {1 {no such collation sequence: string_compare}} 

   267 do_test collate3-3.14 {

   268   catchsql {

   269     CREATE INDEX collate3t1_i2 ON collate3t1(c1);

   270   }

   271 } {1 {no such collation sequence: string_compare}} 

   272 do_test collate3-3.15 {

   273   execsql {

   274     DROP TABLE collate3t1;

   275   }

   276 } {}

   277 

   278 # Check we can create an index that uses an explicit collation 

   279 # sequence and then close and re-open the database.

   280 do_test collate3-4.6 {

   281   db collate user_defined "string compare"

   282   execsql {

   283     CREATE TABLE collate3t1(a, b);

   284     INSERT INTO collate3t1 VALUES('hello', NULL);

   285     CREATE INDEX collate3i1 ON collate3t1(a COLLATE user_defined);

   286   }

   287 } {}

   288 do_test collate3-4.7 {

   289   db close

   290   sqlite3 db test.db

   291   catchsql {

   292     SELECT * FROM collate3t1 ORDER BY a COLLATE user_defined;

   293   }

   294 } {1 {no such collation sequence: user_defined}}

   295 do_test collate3-4.8.1 {

   296   db collate user_defined "string compare"

   297   catchsql {

   298     SELECT * FROM collate3t1 ORDER BY a COLLATE user_defined;

   299   }

   300 } {0 {hello {}}}

   301 do_test collate3-4.8.2 {

   302   db close

   303   lindex [catch {

   304     sqlite3 db test.db

   305   }] 0

   306 } {0}

   307 do_test collate3-4.8.3 {

   308   execsql {

   309     DROP TABLE collate3t1;

   310   }

   311 } {}

   312 

   313 # Compare strings as numbers.

   314 proc numeric_compare {lhs rhs} {

   315   if {$rhs > $lhs} {

   316     set res -1

   317   } else {

   318     set res [expr ($lhs > $rhs)?1:0]

   319   }

   320   return $res

   321 }

   322 

   323 # Check we can create a view that uses an explicit collation 

   324 # sequence and then close and re-open the database.

   325 ifcapable view {

   326 do_test collate3-4.9 {

   327   db collate user_defined numeric_compare

   328   execsql {

   329     CREATE TABLE collate3t1(a, b);

   330     INSERT INTO collate3t1 VALUES('2', NULL);

   331     INSERT INTO collate3t1 VALUES('101', NULL);

   332     INSERT INTO collate3t1 VALUES('12', NULL);

   333     CREATE VIEW collate3v1 AS SELECT * FROM collate3t1 

   334         ORDER BY 1 COLLATE user_defined;

   335     SELECT * FROM collate3v1;

   336   }

   337 } {2 {} 12 {} 101 {}}

   338 do_test collate3-4.10 {

   339   db close

   340   sqlite3 db test.db

   341   catchsql {

   342     SELECT * FROM collate3v1;

   343   }

   344 } {1 {no such collation sequence: user_defined}}

   345 do_test collate3-4.11 {

   346   db collate user_defined numeric_compare

   347   catchsql {

   348     SELECT * FROM collate3v1;

   349   }

   350 } {0 {2 {} 12 {} 101 {}}}

   351 do_test collate3-4.12 {

   352   execsql {

   353     DROP TABLE collate3t1;

   354   }

   355 } {}

   356 } ;# ifcapable view

   357 

   358 #

   359 # Test the collation factory. In the code, the "no such collation sequence"

   360 # message is only generated in two places. So these tests just test that

   361 # the collation factory can be called once from each of those points.

   362 #

   363 do_test collate3-5.0 {

   364   catchsql {

   365     CREATE TABLE collate3t1(a);

   366     INSERT INTO collate3t1 VALUES(10);

   367     SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;

   368   }

   369 } {1 {no such collation sequence: unk}}

   370 do_test collate3-5.1 {

   371   set ::cfact_cnt 0

   372   proc cfact {nm} {

   373     db collate $nm {string compare}

   374     incr ::cfact_cnt

   375   }

   376   db collation_needed cfact

   377 } {}

   378 do_test collate3-5.2 {

   379   catchsql {

   380     SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;

   381   }

   382 } {0 10}

   383 do_test collate3-5.3 {

   384   set ::cfact_cnt

   385 } {1}

   386 do_test collate3-5.4 {

   387   catchsql {

   388     SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;

   389   }

   390 } {0 10}

   391 do_test collate3-5.5 {

   392   set ::cfact_cnt

   393 } {1}

   394 do_test collate3-5.6 {

   395   catchsql {

   396     SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;

   397   }

   398 } {0 10}

   399 do_test collate3-5.7 {

   400   execsql {

   401     DROP TABLE collate3t1;

   402     CREATE TABLE collate3t1(a COLLATE unk);

   403   }

   404   db close

   405   sqlite3 db test.db

   406   catchsql {

   407     SELECT a FROM collate3t1 ORDER BY 1;

   408   }

   409 } {1 {no such collation sequence: unk}}

   410 do_test collate3-5.8 {

   411   set ::cfact_cnt 0

   412   proc cfact {nm} {

   413     db collate $nm {string compare}

   414     incr ::cfact_cnt

   415   }

   416   db collation_needed cfact

   417   catchsql {

   418     SELECT a FROM collate3t1 ORDER BY 1;

   419   }

   420 } {0 {}}

   421 

   422 do_test collate3-5.9 {

   423   execsql {

   424     DROP TABLE collate3t1;

   425   }

   426 } {}

   427 

   428 finish_test