gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/createtab.test (about)

     1  # 2007 May 02
     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 file is testing that it is OK to create new tables
    13  # and indices while creating existing tables and indices.
    14  #
    15  
    16  set testdir [file dirname $argv0]
    17  source $testdir/tester.tcl
    18  
    19  ifcapable autovacuum {
    20    set upperBound 2
    21  } else {
    22    set upperBound 0
    23  }
    24  
    25  # Run these tests for all possible values of autovacuum.
    26  #
    27  for {set av 0} {$av<=$upperBound} {incr av} {
    28    db close
    29    forcedelete test.db test.db-journal
    30    sqlite3 db test.db
    31  
    32    # Create a table that spans multiple pages.  It is important
    33    # that part of the database be in pages beyond the root page.
    34    #
    35    do_test createtab-$av.1 {
    36      execsql "PRAGMA auto_vacuum=$av"
    37      execsql {
    38        PRAGMA page_size=1024;
    39        CREATE TABLE t1(x INTEGER PRIMARY KEY, y);
    40        INSERT INTO t1 VALUES(1, hex(randomblob(200)));
    41        INSERT INTO t1 VALUES(2, hex(randomblob(200)));
    42        INSERT INTO t1 VALUES(3, hex(randomblob(200)));
    43        INSERT INTO t1 VALUES(4, hex(randomblob(200)));
    44        SELECT count(*) FROM t1;
    45      }
    46    } {4}
    47  
    48    set isUtf16 0
    49    ifcapable utf16 { 
    50      set isUtf16 [expr {[execsql {PRAGMA encoding}] != "UTF-8"}]
    51    }
    52  
    53    do_test createtab-$av.2 {
    54      file size test.db
    55    } [expr {1024*(4+($av!=0)+(${isUtf16}*2))}]
    56    
    57    # Start reading the table
    58    #
    59    do_test createtab-$av.3 {
    60      set STMT [sqlite3_prepare db {SELECT x FROM t1} -1 TAIL]
    61      sqlite3_step $STMT
    62    } {SQLITE_ROW}
    63    do_test createtab-$av.4 {
    64      sqlite3_column_int $STMT 0
    65    } {1}
    66    
    67    # While still reading the table, create a new table.
    68    #
    69    do_test createtab-$av.5 {
    70      execsql {
    71        CREATE TABLE t2(a,b);
    72        INSERT INTO t2 VALUES(1,2);
    73        SELECT * FROM t2;
    74      }
    75    } {1 2}
    76    
    77    # Continue reading the original table.
    78    #
    79    do_test createtab-$av.6 {
    80      sqlite3_column_int $STMT 0
    81    } {1}
    82    do_test createtab-$av.7 {
    83      sqlite3_step $STMT
    84    } {SQLITE_ROW}
    85    do_test createtab-$av.8 {
    86      sqlite3_column_int $STMT 0
    87    } {2}
    88    
    89    # Do another cycle of creating a new database table while contining
    90    # to read the original table.
    91    #
    92    do_test createtab-$av.11 {
    93      execsql {
    94        CREATE TABLE t3(a,b);
    95        INSERT INTO t3 VALUES(4,5);
    96        SELECT * FROM t3;
    97      }
    98    } {4 5}
    99    do_test createtab-$av.12 {
   100      sqlite3_column_int $STMT 0
   101    } {2}
   102    do_test createtab-$av.13 {
   103      sqlite3_step $STMT
   104    } {SQLITE_ROW}
   105    do_test createtab-$av.14 {
   106      sqlite3_column_int $STMT 0
   107    } {3}
   108    
   109    # One more cycle.
   110    #
   111    do_test createtab-$av.21 {
   112      execsql {
   113        CREATE TABLE t4(a,b);
   114        INSERT INTO t4 VALUES('abc','xyz');
   115        SELECT * FROM t4;
   116      }
   117    } {abc xyz}
   118    do_test createtab-$av.22 {
   119      sqlite3_column_int $STMT 0
   120    } {3}
   121    do_test createtab-$av.23 {
   122      sqlite3_step $STMT
   123    } {SQLITE_ROW}
   124    do_test createtab-$av.24 {
   125      sqlite3_column_int $STMT 0
   126    } {4}
   127    
   128    # Finish reading.  Do an integrity check on the database.
   129    #
   130    do_test createtab-$av.30 {
   131      sqlite3_step $STMT
   132    } {SQLITE_DONE}
   133    do_test createtab-$av.31 {
   134      sqlite3_finalize $STMT
   135    } {SQLITE_OK}
   136    do_test createtab-$av.32 {
   137      execsql {
   138        SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1
   139      }
   140    } {t1 t2 t3 t4}
   141    integrity_check createtab-$av.40
   142  
   143  }
   144  
   145  # 2019-03-31 Ensure that a proper error is returned for an index
   146  # with too many columns.
   147  #
   148  do_test createtab-3.1 {
   149    db eval {DROP TABLE IF EXISTS t1;}
   150    set sql "CREATE TABLE t1(x,UNIQUE(x[string repeat ,x 100000]))"
   151    catchsql $sql
   152  } {1 {too many columns in index}}
   153    
   154  finish_test