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

     1  # 2017-08-10
     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 tests of writing to WITHOUT ROWID virtual tables
    12  # using the tclvar eponymous virtual table.
    13  #
    14  
    15  set testdir [file dirname $argv0]
    16  source $testdir/tester.tcl
    17  set testprefix vtabJ
    18  
    19  ifcapable !vtab {
    20    finish_test
    21    return
    22  }
    23  
    24  register_tclvar_module db
    25  
    26  unset -nocomplain vtabJ
    27  do_test 100 {
    28    set vtabJ(1) this
    29    set vtabJ(two) is
    30    set vtabJ(3) {a test}
    31    db eval {
    32      SELECT fullname, value FROM tclvar WHERE name='vtabJ' ORDER BY fullname;
    33    }
    34  } {vtabJ(1) this vtabJ(3) {a test} vtabJ(two) is}
    35  
    36  do_execsql_test 110 {
    37    INSERT INTO tclvar(fullname, value)
    38      VALUES('vtabJ(4)',4),('vtabJ(five)',555);
    39    SELECT fullname, value FROM tclvar WHERE name='vtabJ' ORDER BY fullname;
    40  } {vtabJ(1) this vtabJ(3) {a test} vtabJ(4) 4 vtabJ(five) 555 vtabJ(two) is}
    41  do_test 111 {
    42    set res {}
    43    foreach vname [lsort [array names vtabJ]] {
    44      lappend res vtabJ($vname) $vtabJ($vname)
    45    }
    46    set res
    47  } {vtabJ(1) this vtabJ(3) {a test} vtabJ(4) 4 vtabJ(five) 555 vtabJ(two) is}
    48  
    49  do_test 120 {
    50    db eval {
    51      INSERT INTO tclvar(fullname, value) VALUES('vtabJ(4)',444);
    52    }
    53    set vtabJ(4)
    54  } {444}
    55  
    56  do_test 130 {
    57    db eval {
    58      INSERT INTO tclvar(fullname, value) VALUES('vtabJ(4)',NULL);
    59    }
    60    info exists vtabJ(4)
    61  } {0}
    62  
    63  do_test 140 {
    64    db eval {
    65      UPDATE tclvar SET value=55 WHERE fullname='vtabJ(five)';
    66    }
    67    set vtabJ(five)
    68  } {55}
    69  
    70  do_test 150 {
    71    db eval {
    72      UPDATE tclvar SET fullname='vtabJ(5)' WHERE fullname='vtabJ(five)';
    73    }
    74    set vtabJ(5)
    75  } {55}
    76  do_test 151 {
    77    info exists vtabJ(five)
    78  } {0}
    79  do_test 152 {
    80    set res {}
    81    foreach vname [lsort [array names vtabJ]] {
    82      lappend res vtabJ($vname) $vtabJ($vname)
    83    }
    84    set res
    85  } {vtabJ(1) this vtabJ(3) {a test} vtabJ(5) 55 vtabJ(two) is}
    86  
    87  do_execsql_test 160 {
    88    SELECT fullname FROM tclvar WHERE arrayname='two'
    89  } {vtabJ(two)}
    90  do_execsql_test 161 {
    91    DELETE FROM tclvar WHERE arrayname='two';
    92    SELECT fullname, value FROM tclvar WHERE name='vtabJ' ORDER BY fullname;
    93  } {vtabJ(1) this vtabJ(3) {a test} vtabJ(5) 55}
    94  do_test 162 {
    95    set res {}
    96    foreach vname [lsort [array names vtabJ]] {
    97      lappend res vtabJ($vname) $vtabJ($vname)
    98    }
    99    set res
   100  } {vtabJ(1) this vtabJ(3) {a test} vtabJ(5) 55}
   101  
   102  # Try to trick the module into updating the same variable twice for a
   103  # single UPDATE statement.
   104  #
   105  do_execsql_test 171 {
   106    INSERT INTO tclvar(fullname, value) VALUES('xx', 'a');
   107    SELECT name, value FROM tclvar where name = 'xx';
   108  } {xx a}
   109  do_execsql_test 172 {
   110    UPDATE tclvar SET value = value || 't' 
   111    WHERE name = 'xx' OR name = 'x'||'x';
   112    SELECT name, value FROM tclvar where name = 'xx';
   113  } {xx at}
   114  do_execsql_test 173 {
   115    UPDATE tclvar SET value = value || 't' 
   116    WHERE name = 'xx' OR name BETWEEN 'xx' AND 'xx';
   117    SELECT name, value FROM tclvar where name = 'xx';
   118  } {xx att}
   119  
   120  do_execsql_test 181 {
   121    DELETE FROM tclvar WHERE name BETWEEN 'xx' AND 'xx' OR name='xx';
   122    SELECT name, value FROM tclvar where name = 'xx';
   123  } {}
   124  
   125  #-------------------------------------------------------------------------
   126  
   127  do_execsql_test 200 {
   128    CREATE TABLE var(k TEXT, v TEXT);
   129    INSERT INTO var VALUES('testvar1', 10);
   130    INSERT INTO var VALUES('testvar2', 20);
   131    INSERT INTO var VALUES('testvar3', 30);
   132  }
   133  
   134  do_test 210 {
   135    foreach {testvar1 testvar2 testvar3} {1 2 3} {}
   136    execsql {
   137      UPDATE tclvar SET value = var.v FROM var WHERE name = var.k;
   138    }
   139    list $testvar1 $testvar2 $testvar3
   140  } {10 20 30}
   141  
   142  do_test 220 {
   143    execsql {
   144      CREATE TABLE nam(k TEXT, v TEXT);
   145      INSERT INTO nam VALUES('testvar1', 'tv1');
   146      INSERT INTO nam VALUES('testvar2', 'tv2');
   147      INSERT INTO nam VALUES('testvar3', 'tv3');
   148      UPDATE tclvar SET fullname = nam.v FROM nam WHERE name = nam.k;
   149    }
   150    list $tv1 $tv2 $tv3
   151  } {10 20 30}
   152  
   153  
   154  finish_test