/ Check-in [6997e00c]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Test case for writing to a WITHOUT ROWID virtual table. The TCLVAR virtual table is modified to add a "fullname" column which is the primary key, and to accept update operations against the primary key.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | writable-vtab-without-rowid
Files: files | file ages | folders
SHA3-256:6997e00c3221f266f4d9187501d8a9e5bafb85551e88a744cdc8ffe3b75ec2a4
User & Date: drh 2017-08-10 17:53:11
Context
2017-08-10
19:10
Add extra tests to this branch. check-in: b0e3b88a user: dan tags: writable-vtab-without-rowid
17:53
Test case for writing to a WITHOUT ROWID virtual table. The TCLVAR virtual table is modified to add a "fullname" column which is the primary key, and to accept update operations against the primary key. check-in: 6997e00c user: drh tags: writable-vtab-without-rowid
15:19
Experimental changes that allow a WITHOUT ROWID virtual table to be writable as long as it has only a single-column PRIMARY KEY. check-in: ab9ee4c1 user: drh tags: writable-vtab-without-rowid
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/test_tclvar.c.

    11     11   *************************************************************************
    12     12   ** Code for testing the virtual table interfaces.  This code
    13     13   ** is not included in the SQLite library.  It is used for automated
    14     14   ** testing of the SQLite library.
    15     15   **
    16     16   ** The emphasis of this file is a virtual table that provides
    17     17   ** access to TCL variables.
           18  +**
           19  +** The TCLVAR eponymous virtual table has a schema like this:
           20  +**
           21  +**    CREATE TABLE tclvar(
           22  +**       name TEXT,       -- base name of the variable:  "x" in "$x(y)"
           23  +**       arrayname TEXT,  -- array index name: "y" in "$x(y)"
           24  +**       value TEXT,      -- the value of the variable 
           25  +**       fullname TEXT,   -- the full name of the variable
           26  +**       PRIMARY KEY(fullname)
           27  +**    ) WITHOUT ROWID;
           28  +**
           29  +** DELETE, INSERT, and UPDATE operations use the "fullname" field to
           30  +** determine the variable to be modified.  Changing "value" to NULL
           31  +** deletes the variable.
           32  +**
           33  +** For SELECT operations, the "name" and "arrayname" fields will always
           34  +** match the "fullname" field.  For DELETE, INSERT, and UPDATE, the
           35  +** "name" and "arrayname" fields are ignored and the variable is modified
           36  +** according to "fullname" and "value" only.
    18     37   */
    19     38   #include "sqliteInt.h"
    20     39   #if defined(INCLUDE_SQLITE_TCL_H)
    21     40   #  include "sqlite_tcl.h"
    22     41   #else
    23     42   #  include "tcl.h"
    24     43   #endif
................................................................................
    63     82     void *pAux,
    64     83     int argc, const char *const*argv,
    65     84     sqlite3_vtab **ppVtab,
    66     85     char **pzErr
    67     86   ){
    68     87     tclvar_vtab *pVtab;
    69     88     static const char zSchema[] = 
    70         -     "CREATE TABLE whatever(name TEXT, arrayname TEXT, value TEXT)";
           89  +     "CREATE TABLE x("
           90  +     "  name TEXT,"                       /* Base name */
           91  +     "  arrayname TEXT,"                  /* Array index */
           92  +     "  value TEXT,"                      /* Value */
           93  +     "  fullname TEXT PRIMARY KEY"        /* base(index) name */
           94  +     ") WITHOUT ROWID";
    71     95     pVtab = sqlite3MallocZero( sizeof(*pVtab) );
    72     96     if( pVtab==0 ) return SQLITE_NOMEM;
    73     97     *ppVtab = &pVtab->base;
    74     98     pVtab->interp = (Tcl_Interp *)pAux;
    75     99     sqlite3_declare_vtab(db, zSchema);
    76    100     return SQLITE_OK;
    77    101   }
................................................................................
   247    271         break;
   248    272       }
   249    273       case 2: {
   250    274         Tcl_Obj *pVal = Tcl_GetVar2Ex(interp, z1, *z2?z2:0, TCL_GLOBAL_ONLY);
   251    275         sqlite3_result_text(ctx, Tcl_GetString(pVal), -1, SQLITE_TRANSIENT);
   252    276         break;
   253    277       }
          278  +    case 3: {
          279  +      char *z3;
          280  +      if( p2 ){
          281  +        z3 = sqlite3_mprintf("%s(%s)", z1, z2);
          282  +        sqlite3_result_text(ctx, z3, -1, sqlite3_free);
          283  +      }else{
          284  +        sqlite3_result_text(ctx, z1, -1, SQLITE_TRANSIENT);
          285  +      }
          286  +      break;
          287  +    }
   254    288     }
   255    289     return SQLITE_OK;
   256    290   }
   257    291   
   258    292   static int tclvarRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
   259    293     *pRowid = 0;
   260    294     return SQLITE_OK;
................................................................................
   371    405       }
   372    406     }
   373    407     pIdxInfo->idxStr = zStr;
   374    408     pIdxInfo->needToFreeIdxStr = 1;
   375    409   
   376    410     return SQLITE_OK;
   377    411   }
          412  +
          413  +/*
          414  +** Invoked for any UPDATE, INSERT, or DELETE against a tclvar table
          415  +*/
          416  +static int tclvarUpdate(
          417  +  sqlite3_vtab *tab,
          418  +  int argc,
          419  +  sqlite3_value **argv,
          420  +  sqlite_int64 *pRowid
          421  +){
          422  +  tclvar_vtab *pTab = (tclvar_vtab*)tab;
          423  +  if( argc==1 ){
          424  +    /* A DELETE operation.  The variable to be deleted is stored in argv[0] */
          425  +    const char *zVar = (const char*)sqlite3_value_text(argv[0]);
          426  +    Tcl_UnsetVar(pTab->interp, zVar, TCL_GLOBAL_ONLY);
          427  +    return SQLITE_OK;
          428  +  }
          429  +  if( sqlite3_value_type(argv[0])==SQLITE_NULL ){
          430  +    /* An INSERT operation */
          431  +    const char *zValue = (const char*)sqlite3_value_text(argv[4]);
          432  +    const char *zName;
          433  +    if( sqlite3_value_type(argv[5])!=SQLITE_TEXT ){
          434  +      tab->zErrMsg = sqlite3_mprintf("the 'fullname' column must be TEXT");
          435  +      return SQLITE_ERROR;
          436  +    }
          437  +    zName = (const char*)sqlite3_value_text(argv[5]);
          438  +    if( zValue ){
          439  +      Tcl_SetVar(pTab->interp, zName, zValue, TCL_GLOBAL_ONLY);
          440  +    }else{
          441  +      Tcl_UnsetVar(pTab->interp, zName, TCL_GLOBAL_ONLY);
          442  +    }
          443  +    return SQLITE_OK;
          444  +  }
          445  +  if( sqlite3_value_type(argv[0])==SQLITE_TEXT
          446  +   && sqlite3_value_type(argv[1])==SQLITE_TEXT
          447  +  ){
          448  +    /* An UPDATE operation */
          449  +    const char *zOldName = (const char*)sqlite3_value_text(argv[0]);
          450  +    const char *zNewName = (const char*)sqlite3_value_text(argv[1]);
          451  +    const char *zValue = (const char*)sqlite3_value_text(argv[4]);
          452  +
          453  +    if( strcmp(zOldName, zNewName)!=0 || zValue==0 ){
          454  +      Tcl_UnsetVar(pTab->interp, zOldName, TCL_GLOBAL_ONLY);
          455  +    }
          456  +    if( zValue!=0 ){
          457  +      Tcl_SetVar(pTab->interp, zNewName, zValue, TCL_GLOBAL_ONLY);
          458  +    }
          459  +    return SQLITE_OK;
          460  +  }
          461  +  tab->zErrMsg = sqlite3_mprintf("prohibited TCL variable change");
          462  +  return SQLITE_ERROR;
          463  +}
   378    464   
   379    465   /*
   380    466   ** A virtual table module that provides read-only access to a
   381    467   ** Tcl global variable namespace.
   382    468   */
   383    469   static sqlite3_module tclvarModule = {
   384    470     0,                         /* iVersion */
................................................................................
   390    476     tclvarOpen,                  /* xOpen - open a cursor */
   391    477     tclvarClose,                 /* xClose - close a cursor */
   392    478     tclvarFilter,                /* xFilter - configure scan constraints */
   393    479     tclvarNext,                  /* xNext - advance a cursor */
   394    480     tclvarEof,                   /* xEof - check for end of scan */
   395    481     tclvarColumn,                /* xColumn - read data */
   396    482     tclvarRowid,                 /* xRowid - read data */
   397         -  0,                           /* xUpdate */
          483  +  tclvarUpdate,                /* xUpdate */
   398    484     0,                           /* xBegin */
   399    485     0,                           /* xSync */
   400    486     0,                           /* xCommit */
   401    487     0,                           /* xRollback */
   402    488     0,                           /* xFindMethod */
   403    489     0,                           /* xRename */
   404    490   };

Changes to test/vtab2.test.

    56     56   } {6}
    57     57   
    58     58   register_tclvar_module [sqlite3_connection_pointer db]
    59     59   do_test vtab2-2.1 {
    60     60     set ::abc 123
    61     61     execsql {
    62     62       CREATE VIRTUAL TABLE vars USING tclvar;
    63         -    SELECT * FROM vars WHERE name='abc';
           63  +    SELECT name, arrayname, value FROM vars WHERE name='abc';
    64     64     }
    65     65   } [list abc "" 123]
    66     66   do_test vtab2-2.2 {
    67     67     set A(1) 1
    68     68     set A(2) 4
    69     69     set A(3) 9
    70     70     execsql {
    71         -    SELECT * FROM vars WHERE name='A';
           71  +    SELECT name, arrayname, value FROM vars WHERE name='A';
    72     72     }
    73     73   } [list A 1 1 A 2 4 A 3 9]
    74     74   unset -nocomplain result
    75     75   unset -nocomplain var
    76     76   set result {}
    77     77   foreach var [lsort [info vars tcl_*]] {
    78     78     catch {lappend result $var [set $var]}

Changes to test/vtabE.test.

    35     35   set vtabE2(c) d
    36     36   
    37     37   do_test vtabE-1 {
    38     38     db eval {
    39     39       CREATE VIRTUAL TABLE t1 USING tclvar;
    40     40       CREATE VIRTUAL TABLE t2 USING tclvar;
    41     41       CREATE TABLE t3(a INTEGER PRIMARY KEY, b);
    42         -    SELECT t1.*, t2.*, abs(t3.b + abs(t2.value + abs(t1.value)))
           42  +    SELECT t1.name, t1.arrayname, t1.value,
           43  +           t2.name, t2.arrayname, t2.value,
           44  +           abs(t3.b + abs(t2.value + abs(t1.value)))
    43     45         FROM t1 LEFT JOIN t2 ON t2.name = t1.arrayname
    44     46              LEFT JOIN t3 ON t3.a=t2.value
    45     47        WHERE t1.name = 'vtabE'
    46     48        ORDER BY t1.value, t2.value;
    47     49     }
    48     50   } {vtabE vtabE1 11 vtabE1 w x {} vtabE vtabE1 11 vtabE1 y z {} vtabE vtabE2 22 vtabE2 a b {} vtabE vtabE2 22 vtabE2 c d {}}
    49     51   
    50     52   finish_test

Changes to test/vtabH.test.

    51     51   
    52     52   #--------------------------------------------------------------------------
    53     53   
    54     54   register_tclvar_module db
    55     55   set ::xyz 10
    56     56   do_execsql_test 2.0 {
    57     57     CREATE VIRTUAL TABLE vars USING tclvar;
    58         -  SELECT * FROM vars WHERE name = 'xyz';
           58  +  SELECT name, arrayname, value FROM vars WHERE name = 'xyz';
    59     59   } {xyz {} 10}
    60     60   
    61     61   set x1 aback
    62     62   set x2 abaft
    63     63   set x3 abandon
    64     64   set x4 abandonint
    65     65   set x5 babble

Added test/vtabJ.test.

            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  +finish_test