1 # 2006 September 4

     2 #

     3 # Portions Copyright (c) 2007-2008 Nokia Corporation and/or its subsidiaries. All rights reserved.

     4 #

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

     6 # a legal notice, here is a blessing:

     7 #

     8 #    May you do good and not evil.

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

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

    11 #

    12 #***********************************************************************

    13 # This file implements regression tests for SQLite library.

    14 #

    15 # $Id: misc7.test,v 1.24 2008/08/22 13:57:39 pweilbacher Exp $

    16 

    17 set testdir [file dirname $argv0]

    18 source $testdir/tester.tcl

    19 

    20 do_test misc7-1-misuse {

    21   c_misuse_test

    22 } {}

    23 

    24 do_test misc7-2 {

    25   c_realloc_test

    26 } {}

    27 

    28 do_test misc7-3 {

    29   c_collation_test

    30 } {}

    31 

    32 # Try to open a directory:

    33 # Symbian OS: '/' in the file name replaced with '\\'

    34 do_test misc7-4 {

    35   file delete mydir

    36   file mkdir mydir

    37   set rc [catch {

    38     sqlite3 db2 .\\mydir

    39   } msg]

    40   list $rc $msg

    41 } {1 {unable to open database file}}

    42 

    43 # Try to open a file with a directory where its journal file should be.

    44 # Symbian OS: '/' in the file name replaced with '\\'

    45 do_test misc7-5 {

    46   file delete mydir

    47   file mkdir mydir-journal

    48   sqlite3 db2 .\\mydir

    49   catchsql {

    50     CREATE TABLE abc(a, b, c);

    51   } db2

    52 } {1 {unable to open database file}}

    53 db2 close

    54 

    55 #--------------------------------------------------------------------

    56 # The following tests, misc7-6.* test the libraries behaviour when

    57 # it cannot open a file. To force this condition, we use up all the

    58 # file-descriptors before running sqlite. This probably only works

    59 # on unix.

    60 #

    61 

    62 proc use_up_files {} {

    63   set ret [list]

    64   catch {

    65     while 1 { lappend ret [open test.db] }

    66   }

    67   return $ret

    68 }

    69 

    70 proc do_fileopen_test {prefix sql} {

    71   set fd_list [use_up_files]

    72   set ::go 1

    73   set ::n 1

    74   set ::sql $sql

    75   while {$::go} {

    76     catch {db close}

    77     do_test ${prefix}.${::n} {

    78       set rc [catch {

    79         sqlite db test.db

    80         db eval $::sql

    81       } msg]

    82       if {$rc == 0} {set ::go 0}

    83   

    84       expr {$rc == 0 || ($rc == 1 && [string first unable $msg]==0)}

    85     } 1

    86   

    87     close [lindex $fd_list 0]

    88     set fd_list [lrange $fd_list 1 end]

    89     incr ::n

    90   }

    91   foreach fd $fd_list {

    92     close $fd

    93   }

    94   db close

    95 }

    96 

    97 execsql { CREATE TABLE abc(a PRIMARY KEY, b, c); }

    98 db close

    99 

   100 if {$tcl_platform(platform)!="windows"} {

   101   do_fileopen_test misc7-6.1 {

   102     BEGIN;

   103     INSERT INTO abc VALUES(1, 2, 3);

   104     INSERT INTO abc VALUES(2, 3, 4);

   105     INSERT INTO abc SELECT a+2, b, c FROM abc;

   106     COMMIT;

   107   }

   108   

   109   do_fileopen_test misc7-6.2 {

   110     PRAGMA temp.cache_size = 1000;

   111   }

   112 }

   113 

   114 #

   115 # End of tests for out-of-file-descriptors condition.

   116 #--------------------------------------------------------------------

   117 

   118 sqlite3 db test.db

   119 execsql {

   120   DELETE FROM abc;

   121   INSERT INTO abc VALUES(1, 2, 3);

   122   INSERT INTO abc VALUES(2, 3, 4);

   123   INSERT INTO abc SELECT a+2, b, c FROM abc;

   124 }

   125   

   126 

   127 #--------------------------------------------------------------------

   128 # Test that the sqlite3_busy_timeout call seems to delay approximately

   129 # the right amount of time.

   130 #

   131 do_test misc7-7.0 {

   132   sqlite3 db2 test.db

   133   sqlite3_busy_timeout [sqlite3_connection_pointer db] 2000

   134   execsql {

   135     BEGIN EXCLUSIVE;

   136   } db2

   137 

   138   # Now db2 has an exclusive lock on the database file, and db has

   139   # a busy-timeout of 2000 milliseconds. So check that trying to

   140   # access the database using connection db delays for at least 1500 ms.

   141   #

   142   set tm [time {

   143     set result [catchsql {

   144         SELECT * FROM sqlite_master;

   145       } db]

   146   }]

   147   set delay [lindex $tm 0]  ;# In microseconds

   148   lappend result [expr {$delay>1500000 && $delay<4000000}]

   149 } {1 {database is locked} 1}

   150 db2 close

   151 

   152 #--------------------------------------------------------------------

   153 # Test that nothing goes horribly wrong when attaching a database

   154 # after the omit_readlock pragma has been exercised.

   155 #

   156 do_test misc7-7.1 {

   157   file delete -force test2.db

   158   file delete -force test2.db-journal

   159   execsql {

   160     PRAGMA omit_readlock = 1;

   161     ATTACH 'test2.db' AS aux;

   162     CREATE TABLE aux.hello(world);

   163     SELECT name FROM aux.sqlite_master;

   164   }

   165 } {hello}

   166 do_test misc7-7.2 {

   167   execsql {

   168     DETACH aux;

   169   }

   170 } {}

   171 

   172 # Test the UTF-16 version of the "out of memory" message (used when

   173 # malloc fails during sqlite3_open() ).

   174 #

   175 ifcapable utf16 {

   176   do_test misc7-8 {

   177     encoding convertfrom unicode [sqlite3_errmsg16 0x00000000]

   178   } {out of memory}

   179 }

   180 

   181 do_test misc7-9 {

   182   execsql {

   183     SELECT * 

   184     FROM (SELECT name+1 AS one FROM sqlite_master LIMIT 1 OFFSET 1) 

   185     WHERE one LIKE 'hello%';

   186   }

   187 } {}

   188 

   189 #--------------------------------------------------------------------

   190 # Improve coverage for vtab code.

   191 #

   192 ifcapable vtab {

   193   # Run some debug code to improve reported coverage

   194   #

   195 

   196   # set sqlite_where_trace 1

   197   do_test misc7-10 {

   198     register_echo_module [sqlite3_connection_pointer db]

   199     execsql {

   200       CREATE VIRTUAL TABLE t1 USING echo(abc);

   201       SELECT a FROM t1 WHERE a = 1 ORDER BY b;

   202     }

   203   } {1}

   204   set sqlite_where_trace 0

   205 

   206   # Specify an ORDER BY clause that cannot be indexed.

   207   do_test misc7-11 {

   208     execsql {

   209       SELECT t1.a, t2.a FROM t1, t1 AS t2 ORDER BY 2 LIMIT 1;

   210     }

   211   } {1 1}

   212 

   213   # The whole point of this is to test an error code other than

   214   # SQLITE_NOMEM from the vtab xBestIndex callback.

   215   #

   216   do_ioerr_test misc7-12 -tclprep {

   217     sqlite3 db2 test.db

   218     register_echo_module [sqlite3_connection_pointer db2]

   219     db2 eval {

   220       CREATE TABLE abc(a PRIMARY KEY, b, c);

   221       INSERT INTO abc VALUES(1, 2, 3);

   222       CREATE VIRTUAL TABLE t1 USING echo(abc);

   223     }

   224     db2 close

   225   } -tclbody {

   226     register_echo_module [sqlite3_connection_pointer db]

   227     execsql {SELECT * FROM t1 WHERE a = 1;}

   228   } 

   229 

   230   # The case where the virtual table module returns a very large number

   231   # as the cost of a scan (greater than SQLITE_BIG_DOUBLE in the code).

   232   #

   233   do_test misc7-13 {

   234     sqlite3 db test.db

   235     register_echo_module [sqlite3_connection_pointer db]

   236     set ::echo_module_cost 2.0e+99

   237     execsql {SELECT * FROM t1 WHERE a = 1;}

   238   } {1 2 3}

   239   unset ::echo_module_cost

   240 }

   241 

   242 db close

   243 file delete -force test.db

   244 file delete -force test.db-journal

   245 sqlite3 db test.db

   246 

   247 ifcapable explain {

   248   do_test misc7-14.1 {

   249     execsql {

   250       CREATE TABLE abc(a PRIMARY KEY, b, c);

   251     }

   252     execsql {

   253       EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 WHERE rowid = 1;

   254     }

   255   } {0 0 {TABLE abc AS t2 USING PRIMARY KEY}}

   256   do_test misc7-14.2 {

   257     execsql {

   258       EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 WHERE a = 1;

   259     }

   260   } {0 0 {TABLE abc AS t2 WITH INDEX sqlite_autoindex_abc_1}}

   261   do_test misc7-14.3 {

   262     execsql {

   263       EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 ORDER BY a;

   264     }

   265   } {0 0 {TABLE abc AS t2 WITH INDEX sqlite_autoindex_abc_1 ORDER BY}}

   266 }

   267 

   268 db close

   269 file delete -force test.db

   270 file delete -force test.db-journal

   271 sqlite3 db test.db

   272 

   273 #--------------------------------------------------------------------

   274 # This is all to force the pager_remove_from_stmt_list() function

   275 # (inside pager.c) to remove a pager from the middle of the

   276 # statement-list.

   277 #

   278 do_test misc7-15.1 {

   279   execsql {

   280     PRAGMA cache_size = 10;

   281     BEGIN;

   282     CREATE TABLE abc(a PRIMARY KEY, b, c);

   283     INSERT INTO abc 

   284     VALUES(randstr(100,100), randstr(100,100), randstr(100,100));

   285     INSERT INTO abc SELECT 

   286             randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;

   287     INSERT INTO abc SELECT 

   288             randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;

   289     INSERT INTO abc SELECT 

   290             randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;

   291     INSERT INTO abc SELECT 

   292             randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;

   293     INSERT INTO abc SELECT 

   294             randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;

   295     INSERT INTO abc SELECT 

   296             randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;

   297     INSERT INTO abc SELECT 

   298             randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;

   299     INSERT INTO abc SELECT 

   300             randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;

   301     COMMIT;

   302   }

   303   expr {[file size test.db]>10240}

   304 } {1}

   305 do_test misc7-15.2 {

   306   execsql {

   307     DELETE FROM abc WHERE rowid > 12;

   308     INSERT INTO abc SELECT 

   309             randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;

   310   }

   311 } {}

   312 

   313 db close

   314 file delete -force test.db

   315 file delete -force test.db-journal

   316 sqlite3 db test.db

   317 

   318 do_ioerr_test misc7-16 -sqlprep {

   319    PRAGMA cache_size = 10;

   320    PRAGMA default_cache_size = 10;

   321    CREATE TABLE t3(a, b, UNIQUE(a, b));

   322    INSERT INTO t3 VALUES( randstr(100, 100), randstr(100, 100) );

   323    INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;

   324    INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;

   325    INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;

   326    INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;

   327    INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;

   328    UPDATE t3 

   329    SET b = 'hello world'

   330    WHERE rowid >= (SELECT max(rowid)-1 FROM t3);

   331 } -tclbody {

   332   set rc [catch {db eval {

   333     BEGIN;

   334       PRAGMA cache_size = 10;

   335       INSERT INTO t3 VALUES( randstr(100, 100), randstr(100, 100) );

   336       UPDATE t3 SET a = b;

   337     COMMIT;

   338   }} msg]

   339 

   340   if {!$rc || ($rc && [string first "columns" $msg]==0)} {

   341     set msg

   342   } else {

   343     error $msg

   344   }

   345 }

   346 

   347 sqlite3 db test.db

   348 

   349 do_test misc7-16.X {

   350   execsql {

   351     SELECT count(*) FROM t3;

   352   }

   353 } {32}

   354 

   355 set sqlite_pager_n_sort_bucket 4

   356 do_test misc7-17 {

   357   execsql {

   358     PRAGMA integrity_check;

   359     VACUUM;

   360     PRAGMA integrity_check;

   361   }

   362 } {ok ok}

   363 set sqlite_pager_n_sort_bucket 0

   364 

   365 #----------------------------------------------------------------------

   366 # Test the situation where a hot-journal is discovered but write-access

   367 # to it is denied. This should return SQLITE_BUSY.

   368 #

   369 # These tests do not work on windows due to restrictions in the

   370 # windows file system.

   371 #

   372 # TODO: Not clear why this test is crashing tclsqlite3.exe

   373 #

   374 if {$tcl_platform(platform)!="windows" && $tcl_platform(platform)!="symbian" && $tcl_platform(platform)!="os2"} {

   375 

   376   # Some network filesystems (ex: AFP) do not support setting read-only

   377   # permissions.  Only run these tests if full unix permission setting

   378   # capabilities are supported.

   379   #

   380   file attributes test.db -permissions rw-r--r--

   381   if {[file attributes test.db -permissions]==0644} {

   382 

   383     do_test misc7-17.1 {

   384       execsql {

   385         BEGIN;

   386         DELETE FROM t3 WHERE (oid%3)==0;

   387       }

   388       copy_file test.db bak.db

   389       copy_file test.db-journal bak.db-journal

   390       execsql {

   391         COMMIT;

   392       }

   393     

   394       db close

   395       copy_file bak.db test.db

   396       copy_file bak.db-journal test.db-journal

   397       sqlite3 db test.db

   398     

   399       catch {file attributes test.db-journal -permissions r--------}

   400       catch {file attributes test.db-journal -readonly 1}

   401       catchsql {

   402         SELECT count(*) FROM t3;

   403       }

   404     } {1 {database is locked}}

   405     do_test misc7-17.2 {

   406       # Note that the -readonly flag must be cleared before the -permissions

   407       # are set. Otherwise, when using tcl 8.5 on mac, the fact that the 

   408       # -readonly flag is set causes the attempt to set the permissions

   409       # to fail.

   410       catch {file attributes test.db-journal -readonly 0}

   411       catch {file attributes test.db-journal -permissions rw-------}

   412       catchsql {

   413         SELECT count(*) FROM t3;

   414       }

   415     } {0 32}

   416     

   417     set ::pending_byte_page [expr ($::sqlite_pending_byte / 1024) + 1]

   418     do_test misc7-17.3 {

   419       db eval {

   420         pragma writable_schema = true;

   421         UPDATE sqlite_master 

   422           SET rootpage = $pending_byte_page

   423           WHERE type = 'table' AND name = 't3';

   424       }

   425       execsql {

   426         SELECT rootpage FROM sqlite_master WHERE type = 'table' AND name = 't3';

   427       }

   428     } $::pending_byte_page

   429     

   430     do_test misc7-17.4 {

   431       db close

   432       sqlite3 db test.db

   433       catchsql {

   434         SELECT count(*) FROM t3;

   435       } 

   436     } {1 {database disk image is malformed}}

   437   }

   438 }

   439 

   440 # Ticket #2470

   441 #

   442 do_test misc7-18.1 {

   443   execsql {

   444     CREATE TABLE table_1 (col_10);

   445     CREATE TABLE table_2 (

   446       col_1, col_2, col_3, col_4, col_5,

   447       col_6, col_7, col_8, col_9, col_10

   448     );

   449     SELECT a.col_10

   450     FROM

   451       (SELECT table_1.col_10 AS col_10 FROM table_1) a,

   452       (SELECT table_1.col_10, table_2.col_9 AS qcol_9

   453          FROM table_1, table_2

   454         GROUP BY table_1.col_10, qcol_9);

   455   }

   456 } {}

   457 

   458 # Testing boundary conditions on sqlite3_status()

   459 #

   460 do_test misc7-19.1 {

   461   sqlite3_status -1 0

   462 } {21 0 0}

   463 do_test misc7-19.2 {

   464   sqlite3_status 1000 0

   465 } {21 0 0}

   466 

   467 

   468 # sqlite3_global_recover() is a no-op.  But we might as well test it

   469 # if only to get the test coverage.

   470 #

   471 do_test misc7-20.1 {

   472   sqlite3_global_recover

   473 } {SQLITE_OK}

   474 

   475 

   476 db close

   477 file delete -force test.db

   478 

   479 finish_test