/ Check-in [2164031b]
Login

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

Overview
Comment:Convert the LSM1 virtual table to be WITHOUT ROWID and get UPDATE and DELETE operations working on it.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 2164031b509dc6eae367ffb9d915f3e1d33d26210506b2de8b2bfca38c53465f
User & Date: drh 2017-08-11 12:49:59
Context
2017-08-11
13:51
New test cases for LSM1. check-in: cb0c49cb user: drh tags: trunk
12:49
Convert the LSM1 virtual table to be WITHOUT ROWID and get UPDATE and DELETE operations working on it. check-in: 2164031b user: drh tags: trunk
03:47
Simplification to the like optimization logic. Remove unnecessary branches. check-in: 9466d952 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to ext/lsm1/lsm_vtab.c.

   239    239     }
   240    240   
   241    241     memset(&sql, 0, sizeof(sql));
   242    242     lsm1VblobAppendText(&sql, "CREATE TABLE x(");
   243    243     lsm1VblobAppendText(&sql, argv[4]);
   244    244     lsm1VblobAppendText(&sql, " ");
   245    245     lsm1VblobAppendText(&sql, argv[5]);
          246  +  lsm1VblobAppendText(&sql, " PRIMARY KEY");
   246    247     for(i=6; i<argc; i++){
   247    248       lsm1VblobAppendText(&sql, ", ");
   248    249       lsm1VblobAppendText(&sql, argv[i]);
   249    250       pNew->nVal++;
   250    251     }
   251    252     lsm1VblobAppendText(&sql, 
   252         -      ", lsm1_command HIDDEN, lsm1_key HIDDEN, lsm1_value HIDDEN)");
          253  +      ", lsm1_command HIDDEN"
          254  +      ", lsm1_key HIDDEN"
          255  +      ", lsm1_value HIDDEN) WITHOUT ROWID");
   253    256     lsm1VblobAppend(&sql, (u8*)"", 1);
   254    257     if( sql.errNoMem ){
   255    258       rc = SQLITE_NOMEM;
   256    259       goto connect_failed;
   257    260     }
   258    261     rc = sqlite3_declare_vtab(db, (const char*)sql.a);
   259    262     sqlite3_free(sql.a);
................................................................................
   664    667         default: {
   665    668            /* A NULL.  Do nothing */
   666    669         }
   667    670       }
   668    671     }
   669    672     return SQLITE_OK;
   670    673   }
          674  +
          675  +/* Parameter "pValue" contains an SQL value that is to be used as
          676  +** a key in an LSM table.  The type of the key is determined by
          677  +** "keyType".  Extract the raw bytes used for the key in LSM1.
          678  +*/
          679  +static void lsm1KeyFromValue(
          680  +  int keyType,                 /* The key type */
          681  +  sqlite3_value *pValue,       /* The key value */
          682  +  u8 *pBuf,                    /* Storage space for a generated key */
          683  +  const u8 **ppKey,            /* OUT: the bytes of the key */
          684  +  int *pnKey                   /* OUT: size of the key */
          685  +){
          686  +  if( keyType==SQLITE_BLOB ){
          687  +    *ppKey = (const u8*)sqlite3_value_blob(pValue);
          688  +    *pnKey = sqlite3_value_bytes(pValue);
          689  +  }else if( keyType==SQLITE_TEXT ){
          690  +    *ppKey = (const u8*)sqlite3_value_text(pValue);
          691  +    *pnKey = sqlite3_value_bytes(pValue);
          692  +  }else{
          693  +    sqlite3_int64 v = sqlite3_value_int64(pValue);
          694  +    if( v<0 ) v = 0;
          695  +    *pnKey = lsm1PutVarint64(pBuf, v);
          696  +    *ppKey = pBuf;
          697  +  }
          698  +}
   671    699   
   672    700   /* Move to the first row to return.
   673    701   */
   674    702   static int lsm1Filter(
   675    703     sqlite3_vtab_cursor *pVtabCursor, 
   676    704     int idxNum, const char *idxStr,
   677    705     int argc, sqlite3_value **argv
   678    706   ){
   679    707     lsm1_cursor *pCur = (lsm1_cursor *)pVtabCursor;
   680    708     lsm1_vtab *pTab = (lsm1_vtab*)(pCur->base.pVtab);
   681    709     int rc = LSM_OK;
   682    710     int seekType = -1;
   683         -  const void *pVal = 0;
          711  +  const u8 *pVal = 0;
   684    712     int nVal;
   685    713     u8 keyType = pTab->keyType;
   686    714     u8 aKey1[16];
   687    715   
   688    716     pCur->atEof = 1;
   689    717     sqlite3_free(pCur->pKey2);
   690    718     pCur->pKey2 = 0;
   691    719     if( idxNum<99 ){
   692         -    if( keyType==SQLITE_BLOB ){
   693         -      pVal = sqlite3_value_blob(argv[0]);
   694         -      nVal = sqlite3_value_bytes(argv[0]);
   695         -    }else if( keyType==SQLITE_TEXT ){
   696         -      pVal = sqlite3_value_text(argv[0]);
   697         -      nVal = sqlite3_value_bytes(argv[0]);
   698         -    }else{
   699         -      sqlite3_int64 v = sqlite3_value_int64(argv[0]);
   700         -      if( v<0 ) v = 0;
   701         -      nVal = lsm1PutVarint64(aKey1, v);
   702         -      pVal = aKey1;
   703         -    }
          720  +    lsm1KeyFromValue(keyType, argv[0], aKey1, &pVal, &nVal);
   704    721     }
   705    722     switch( idxNum ){
   706    723       case 0: {   /* key==argv[0] */
   707    724         assert( argc==1 );
   708    725         seekType = LSM_SEEK_EQ;
   709    726         pCur->isDesc = 0;
   710    727         pCur->bUnique = 1;
................................................................................
   866    883   int lsm1Update(
   867    884     sqlite3_vtab *pVTab,
   868    885     int argc,
   869    886     sqlite3_value **argv,
   870    887     sqlite_int64 *pRowid
   871    888   ){
   872    889     lsm1_vtab *p = (lsm1_vtab*)pVTab;
   873         -  int nKey;
          890  +  int nKey, nKey2;
   874    891     int i;
   875    892     int rc = LSM_OK;
   876         -  unsigned char *pKey;
          893  +  const u8 *pKey, *pKey2;
   877    894     unsigned char aKey[16];
   878    895     unsigned char pSpace[16];
   879    896     lsm1_vblob val;
   880    897   
   881    898     if( argc==1 ){
   882         -    pVTab->zErrMsg = sqlite3_mprintf("cannot DELETE");
   883         -    return SQLITE_ERROR;
          899  +    /* DELETE the record whose key is argv[0] */
          900  +    lsm1KeyFromValue(p->keyType, argv[0], aKey, &pKey, &nKey);
          901  +    lsm_delete(p->pDb, pKey, nKey);
          902  +    return SQLITE_OK;
   884    903     }
          904  +
   885    905     if( sqlite3_value_type(argv[0])!=SQLITE_NULL ){
   886         -    pVTab->zErrMsg = sqlite3_mprintf("cannot UPDATE");
   887         -    return SQLITE_ERROR;
          906  +    /* An UPDATE */
          907  +    lsm1KeyFromValue(p->keyType, argv[0], aKey, &pKey, &nKey);
          908  +    lsm1KeyFromValue(p->keyType, argv[1], pSpace, &pKey2, &nKey2);
          909  +    if( nKey!=nKey2 || memcmp(pKey, pKey2, nKey)!=0 ){
          910  +      /* The UPDATE changes the PRIMARY KEY value.  DELETE the old key */
          911  +      lsm_delete(p->pDb, pKey, nKey);
          912  +    }
          913  +    /* Fall through into the INSERT case to complete the UPDATE */
   888    914     }
   889    915   
   890    916     /* "INSERT INTO tab(lsm1_command) VALUES('....')" is used to implement
   891    917     ** special commands.
   892    918     */
   893    919     if( sqlite3_value_type(argv[3+p->nVal])!=SQLITE_NULL ){
   894    920       return SQLITE_OK;
   895    921     }
   896         -  if( p->keyType==SQLITE_BLOB ){
   897         -    pKey = (u8*)sqlite3_value_blob(argv[2]);
   898         -    nKey = sqlite3_value_bytes(argv[2]);
   899         -  }else if( p->keyType==SQLITE_TEXT ){
   900         -    pKey = (u8*)sqlite3_value_text(argv[2]);
   901         -    nKey = sqlite3_value_bytes(argv[2]);
   902         -  }else{
   903         -    sqlite3_int64 v = sqlite3_value_int64(argv[2]);
   904         -    if( v>=0 ){
   905         -      nKey = lsm1PutVarint64(aKey, (sqlite3_uint64)v);
   906         -      pKey = aKey;
   907         -    }else{
   908         -      pVTab->zErrMsg = sqlite3_mprintf("key must be non-negative");
   909         -      return SQLITE_ERROR;
   910         -    }
   911         -  }
          922  +  lsm1KeyFromValue(p->keyType, argv[2], aKey, &pKey, &nKey);
   912    923     memset(&val, 0, sizeof(val));
   913    924     for(i=0; i<p->nVal; i++){
   914    925       sqlite3_value *pArg = argv[3+i];
   915    926       u8 eType = sqlite3_value_type(pArg);
   916    927       switch( eType ){
   917    928         case SQLITE_NULL: {
   918    929           lsm1VblobAppendVarint(&val, SQLITE_NULL);

Changes to ext/lsm1/test/lsm1_simple.test.

    19     19   
    20     20   forcedelete testlsm.db
    21     21   
    22     22   do_execsql_test 1.0 {
    23     23     CREATE VIRTUAL TABLE x1 USING lsm1(testlsm.db,a,UINT,b,c,d);
    24     24     PRAGMA table_info(x1);
    25     25   } {
    26         -  0 a UINT 0 {} 0 
           26  +  0 a UINT 1 {} 1 
    27     27     1 b {} 0 {} 0 
    28     28     2 c {} 0 {} 0 
    29     29     3 d {} 0 {} 0
    30     30   }
    31     31   
    32     32   do_execsql_test 1.1 {
    33     33     INSERT INTO x1(a,b,c,d) VALUES(15, 11, 22, 33),(8,'banjo',x'333231',NULL),
    34     34         (12,NULL,3.25,-559281390);
    35     35     SELECT a, quote(b), quote(c), quote(d) FROM x1;
    36     36   } {8 'banjo' X'333231' NULL 12 NULL 3.25 -559281390 15 11 22 33}
    37     37   
    38         -do_catchsql_test 1.2 {
           38  +do_execsql_test 1.2 {
    39     39     UPDATE x1 SET d = d+1.0 WHERE a=15;
    40         -} {1 {cannot UPDATE}}
           40  +  SELECT a, quote(b), quote(c), quote(d) FROM x1;
           41  +} {8 'banjo' X'333231' NULL 12 NULL 3.25 -559281390 15 11 22 34.0}
    41     42   
    42         -do_catchsql_test 1.3 {
           43  +do_execsql_test 1.3 {
    43     44     DELETE FROM x1 WHERE a=15;
    44         -} {1 {cannot DELETE}}
           45  +  SELECT a, quote(b), quote(c), quote(d) FROM x1;
           46  +} {8 'banjo' X'333231' NULL 12 NULL 3.25 -559281390}
    45     47   
    46     48   do_test 1.4 {
    47     49     lsort [glob testlsm.db*]
    48     50   } {testlsm.db testlsm.db-log testlsm.db-shm}
    49     51   
    50     52   db close
    51     53   do_test 1.5 {
    52     54     lsort [glob testlsm.db*]
    53     55   } {testlsm.db}
    54     56   
    55     57   finish_test