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

     1  # 2016-06-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  # 
    12  # Test cases for CSV virtual table.
    13  
    14  set testdir [file dirname $argv0]
    15  source $testdir/tester.tcl
    16  set testprefix csv01
    17  
    18  ifcapable !vtab||!cte { finish_test ; return }
    19  
    20  load_static_extension db csv
    21  
    22  do_execsql_test 1.0 {
    23    CREATE VIRTUAL TABLE temp.t1 USING csv(
    24      data=
    25  '1,2,3,4
    26  5,6,7,8
    27  9,10,11,12
    28  13,14,15,16
    29  ',
    30      columns=4
    31    );
    32    SELECT * FROM t1 WHERE c1=10;
    33  } {9 10 11 12}
    34  do_execsql_test 1.1 {
    35    SELECT * FROM t1 WHERE c1='10';
    36  } {9 10 11 12}
    37  do_execsql_test 1.2 {
    38    SELECT rowid FROM t1;
    39  } {1 2 3 4}
    40  
    41  do_execsql_test 2.0 {
    42    DROP TABLE t1;
    43    CREATE VIRTUAL TABLE temp.t2 USING csv(
    44      data=
    45  '1,2,3,4
    46  5,6,7,8
    47  9,10,11,12
    48  13,14,15,16
    49  ',
    50      columns=4,
    51      schema='CREATE TABLE t2(a INT, b TEXT, c REAL, d BLOB)'
    52    );
    53    SELECT * FROM t2 WHERE a=9;
    54  } {9 10 11 12}
    55  do_execsql_test 2.1 {
    56    SELECT * FROM t2 WHERE b=10;
    57  } {9 10 11 12}
    58  do_execsql_test 2.2 {
    59    SELECT * FROM t2 WHERE c=11;
    60  } {9 10 11 12}
    61  do_execsql_test 2.3 {
    62    SELECT * FROM t2 WHERE d=12;
    63  } {}
    64  do_execsql_test 2.4 {
    65    SELECT * FROM t2 WHERE d='12';
    66  } {9 10 11 12}
    67  do_execsql_test 2.5 {
    68    SELECT * FROM t2 WHERE a='9';
    69  } {9 10 11 12}
    70  
    71  do_execsql_test 3.0 {
    72    DROP TABLE t2;
    73    CREATE VIRTUAL TABLE temp.t3 USING csv(
    74      data=
    75  '1,2,3,4
    76  5,6,7,8
    77  9,10,11,12
    78  13,14,15,16
    79  ',
    80      columns=4,
    81      schema=
    82        'CREATE TABLE t3(a PRIMARY KEY,b TEXT,c TEXT,d TEXT) WITHOUT ROWID',
    83      testflags=1
    84    );
    85    SELECT a FROM t3 WHERE b=6 OR c=7 OR d=12 ORDER BY +a;
    86  } {5 9}
    87  do_execsql_test 3.1 {
    88    SELECT a FROM t3 WHERE +b=6 OR c=7 OR d=12 ORDER BY +a;
    89  } {5 9}
    90  
    91  # The rowid column is not visible on a WITHOUT ROWID virtual table
    92  do_catchsql_test 3.2 {
    93    SELECT rowid, a FROM t3;
    94  } {1 {no such column: rowid}}
    95  
    96  # Multi-column WITHOUT ROWID virtual tables may not be writable.
    97  do_catchsql_test 4.0 {
    98    DROP TABLE t3;
    99    CREATE VIRTUAL TABLE temp.t4 USING csv_wr(
   100      data=
   101  '1,2,3,4
   102  5,6,7,8
   103  9,10,11,12
   104  13,14,15,16',
   105      columns=4,
   106      schema=
   107        'CREATE TABLE t3(a,b,c,d,PRIMARY KEY(a,b)) WITHOUT ROWID',
   108      testflags=1
   109    );
   110  } {1 {vtable constructor failed: t4}}
   111  
   112  # WITHOUT ROWID tables with a single-column PRIMARY KEY may be writable.
   113  do_catchsql_test 4.1 {
   114    DROP TABLE IF EXISTS t4;
   115    CREATE VIRTUAL TABLE temp.t4 USING csv_wr(
   116      data=
   117  '1,2,3,4
   118  5,6,7,8
   119  9,10,11,12
   120  13,14,15,16',
   121      columns=4,
   122      schema=
   123        'CREATE TABLE t3(a,b,c,d,PRIMARY KEY(b)) WITHOUT ROWID',
   124      testflags=1
   125    );
   126  } {0 {}}
   127  
   128  do_catchsql_test 4.2 {
   129    DROP TABLE IF EXISTS t5;
   130    CREATE VIRTUAL TABLE temp.t5 USING csv_wr(
   131        data=
   132        '1,2,3,4
   133        5,6,7,8
   134        9,10,11,12
   135        13,14,15,16',
   136        columns=4,
   137        schema=
   138        'CREATE TABLE t3(a,b,c,d) WITHOUT ROWID',
   139        testflags=1
   140        );
   141  } {1 {vtable constructor failed: t5}}
   142  
   143  
   144  finish_test