1 # 2006 January 09

     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 testing the server mode of SQLite.

    13 #

    14 # This file is derived from thread1.test

    15 #

    16 # $Id: server1.test,v 1.5 2007/08/29 18:20:17 drh Exp $

    17 

    18 

    19 set testdir [file dirname $argv0]

    20 source $testdir/tester.tcl

    21 

    22 # Skip this whole file if the server testing code is not enabled

    23 #

    24 if {[llength [info command client_step]]==0 || [sqlite3 -has-codec]} {

    25   finish_test

    26   return

    27 }

    28 

    29 # The sample server implementation does not work right when memory

    30 # management is enabled.

    31 #

    32 ifcapable memorymanage {

    33   finish_test

    34   return

    35 }

    36 

    37 # Create some data to work with

    38 #

    39 do_test server1-1.1 {

    40   execsql {

    41     CREATE TABLE t1(a,b);

    42     INSERT INTO t1 VALUES(1,'abcdefgh');

    43     INSERT INTO t1 SELECT a+1, b||b FROM t1;

    44     INSERT INTO t1 SELECT a+2, b||b FROM t1;

    45     INSERT INTO t1 SELECT a+4, b||b FROM t1;

    46     SELECT count(*), max(length(b)) FROM t1;

    47   }

    48 } {8 64}

    49 

    50 # Interleave two threads on read access.  Then make sure a third

    51 # thread can write the database.  In other words:

    52 #

    53 #    read-lock A

    54 #    read-lock B

    55 #    unlock A

    56 #    unlock B

    57 #    write-lock C

    58 #

    59 do_test server1-1.2 {

    60   client_create A test.db

    61   client_create B test.db

    62   client_create C test.db

    63   client_compile A {SELECT a FROM t1}

    64   client_step A

    65   client_result A

    66 } SQLITE_ROW

    67 do_test server1-1.3 {

    68   client_argc A

    69 } 1

    70 do_test server1-1.4 {

    71   client_argv A 0

    72 } 1

    73 do_test server1-1.5 {

    74   client_compile B {SELECT b FROM t1}

    75   client_step B

    76   client_result B

    77 } SQLITE_ROW

    78 do_test server1-1.6 {

    79   client_argc B

    80 } 1

    81 do_test server1-1.7 {

    82   client_argv B 0

    83 } abcdefgh

    84 do_test server1-1.8 {

    85   client_finalize A

    86   client_result A

    87 } SQLITE_OK

    88 do_test server1-1.9 {

    89   client_finalize B

    90   client_result B

    91 } SQLITE_OK

    92 do_test server1-1.10 {

    93   client_compile C {CREATE TABLE t2(x,y)}

    94   client_step C

    95   client_result C

    96 } SQLITE_DONE

    97 do_test server1-1.11 {

    98   client_finalize C

    99   client_result C

   100 } SQLITE_OK

   101 do_test server1-1.12 {

   102   catchsql {SELECT name FROM sqlite_master}

   103   execsql {SELECT name FROM sqlite_master}

   104 } {t1 t2}

   105 

   106 

   107 # Read from table t1.  Do not finalize the statement.  This

   108 # will leave the lock pending.

   109 #

   110 do_test server1-2.1 {

   111   client_halt *

   112   client_create A test.db

   113   client_compile A {SELECT a FROM t1}

   114   client_step A

   115   client_result A

   116 } SQLITE_ROW

   117 

   118 # Read from the same table from another thread.  This is allows.

   119 #

   120 do_test server1-2.2 {

   121   client_create B test.db

   122   client_compile B {SELECT b FROM t1}

   123   client_step B

   124   client_result B

   125 } SQLITE_ROW

   126 

   127 # Write to a different table from another thread.  This is allowed

   128 # because in server mode with a shared cache we have table-level locking.

   129 #

   130 do_test server1-2.3 {

   131   client_create C test.db

   132   client_compile C {INSERT INTO t2 VALUES(98,99)}

   133   client_step C

   134   client_result C

   135   client_finalize C

   136   client_result C

   137 } SQLITE_OK

   138 

   139 # But we cannot insert into table t1 because threads A and B have it locked.

   140 #

   141 do_test server1-2.4 {

   142   client_compile C {INSERT INTO t1 VALUES(98,99)}

   143   client_step C

   144   client_result C

   145   client_finalize C

   146   client_result C

   147 } SQLITE_LOCKED

   148 do_test server1-2.5 {

   149   client_finalize B

   150   client_wait B

   151   client_compile C {INSERT INTO t1 VALUES(98,99)}

   152   client_step C

   153   client_result C

   154   client_finalize C

   155   client_result C

   156 } SQLITE_LOCKED

   157 

   158 # Insert into t1 is successful after finishing the other two threads.

   159 do_test server1-2.6 {

   160   client_finalize A

   161   client_wait A

   162   client_compile C {INSERT INTO t1 VALUES(98,99)}

   163   client_step C

   164   client_result C

   165   client_finalize C

   166   client_result C

   167 } SQLITE_OK

   168 

   169 client_halt *   

   170 sqlite3_enable_shared_cache 0

   171 finish_test