modernc.org/cc@v1.0.1/v2/testdata/_sqlite/test/stmt.test (about)

     1  # 2010 February 18
     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  #
    12  # The tests in this file check that SQLite uses (or does not use) a
    13  # statement journal for various SQL statements.
    14  #
    15  
    16  set testdir [file dirname $argv0]
    17  source $testdir/tester.tcl
    18  
    19  do_test stmt-1.1 {
    20    execsql { CREATE TABLE t1(a integer primary key, b INTEGER NOT NULL) }
    21  } {}
    22  
    23  # The following tests verify the method used for the tests in this file -
    24  # that if a statement journal is required by a statement it is opened and
    25  # remains open until the current transaction is committed or rolled back.
    26  #
    27  # This only work if SQLITE_TEMP_STORE!=3
    28  #
    29  if {$::TEMP_STORE==3} {
    30    finish_test
    31    return
    32  }
    33  do_test stmt-1.2 {
    34    set sqlite_open_file_count
    35  } {1}
    36  do_test stmt-1.3 {
    37    execsql {
    38      PRAGMA temp_store = file;
    39      BEGIN;
    40        INSERT INTO t1 VALUES(1, 1);
    41    }
    42    set sqlite_open_file_count
    43  } {2}
    44  do_test stmt-1.4 {
    45    execsql {
    46      INSERT INTO t1 SELECT a+1, b+1 FROM t1;
    47    }
    48    set sqlite_open_file_count
    49    # 2016-03-04: statement-journal open deferred
    50  } {2}
    51  do_test stmt-1.5 {
    52    execsql COMMIT
    53    set sqlite_open_file_count
    54  } {1}
    55  do_test stmt-1.6.1 {
    56    execsql {
    57      BEGIN;
    58        INSERT INTO t1 SELECT a+2, b+2 FROM t1;
    59    }
    60    set sqlite_open_file_count
    61  } {2}
    62  do_test stmt-1.6.2 {
    63    execsql { INSERT INTO t1 SELECT a+4, b+4 FROM t1 }
    64    set sqlite_open_file_count
    65    # 2016-03-04: statement-journal open deferred
    66  } {2}
    67  do_test stmt-1.7 {
    68    execsql COMMIT
    69    set sqlite_open_file_count
    70  } {1}
    71  
    72  
    73  proc filecount {testname sql expected} {
    74    uplevel [list do_test $testname [subst -nocommand {
    75      execsql BEGIN
    76      execsql { $sql }
    77      set ret [set sqlite_open_file_count]
    78      execsql ROLLBACK
    79      set ret
    80    }] $expected]
    81  }
    82  
    83  filecount stmt-2.1 { INSERT INTO t1 VALUES(9, 9)  } 2
    84  filecount stmt-2.2 { REPLACE INTO t1 VALUES(9, 9) } 2
    85  filecount stmt-2.3 { INSERT INTO t1 SELECT 9, 9   } 2
    86  filecount stmt-2.4 { 
    87      INSERT INTO t1 SELECT 9, 9;
    88      INSERT INTO t1 SELECT 10, 10;
    89  } 2
    90  
    91  do_test stmt-2.5 {
    92    execsql { CREATE INDEX i1 ON t1(b) }
    93  } {}
    94  filecount stmt-2.6 { 
    95    REPLACE INTO t1 VALUES(5, 5);
    96    REPLACE INTO t1 VALUES(5, 5); 
    97  } 2
    98  
    99  finish_test