SQLite4
Check-in [874278817a]
Not logged in

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

Overview
SHA1 Hash:874278817a744e5331f45fcd51209542a1dbdb45
Date: 2013-07-31 14:38:32
User: dan
Comment:Allow writing to the sqlite_kvstore if "PRAGMA writable_schema" is set.
Tags And Properties
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/delete.c

308
309
310
311
312
313
314

315
316
317
318
319
320
321
#ifndef SQLITE4_OMIT_TRUNCATE_OPTIMIZATION
  /* Special case: A DELETE without a WHERE clause deletes everything.
  ** It is easier just to erase the whole table. Prior to version 3.6.5,
  ** this optimization caused the row change count (the value returned by 
  ** API function sqlite4_count_changes) to be set incorrectly.  */
  if( rcauth==SQLITE4_OK && pWhere==0 && !pTrigger && !IsVirtual(pTab) 
   && 0==sqlite4FkRequired(pParse, pTab, 0)

  ){
    Index *pIdx;                  /* For looping over indices of the table */
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      assert( pIdx->pSchema==pTab->pSchema );
      sqlite4VdbeAddOp2(v, OP_Clear, pIdx->tnum, iDb);
      if( pIdx->eIndexType==SQLITE4_INDEX_PRIMARYKEY ){
        sqlite4VdbeChangeP5(v, OPFLAG_NCHANGE);







>







308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
#ifndef SQLITE4_OMIT_TRUNCATE_OPTIMIZATION
  /* Special case: A DELETE without a WHERE clause deletes everything.
  ** It is easier just to erase the whole table. Prior to version 3.6.5,
  ** this optimization caused the row change count (the value returned by 
  ** API function sqlite4_count_changes) to be set incorrectly.  */
  if( rcauth==SQLITE4_OK && pWhere==0 && !pTrigger && !IsVirtual(pTab) 
   && 0==sqlite4FkRequired(pParse, pTab, 0)
   && !IsKvstore(pTab)
  ){
    Index *pIdx;                  /* For looping over indices of the table */
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      assert( pIdx->pSchema==pTab->pSchema );
      sqlite4VdbeAddOp2(v, OP_Clear, pIdx->tnum, iDb);
      if( pIdx->eIndexType==SQLITE4_INDEX_PRIMARYKEY ){
        sqlite4VdbeChangeP5(v, OPFLAG_NCHANGE);

Changes to src/insert.c

1282
1283
1284
1285
1286
1287
1288







1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303

1304
1305
1306
1307
1308
1309
1310
....
1324
1325
1326
1327
1328
1329
1330

1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
....
1423
1424
1425
1426
1427
1428
1429




1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440

    /* If regKey is 0, pIdx will not be updated. */
    if( regKey==0 ) continue;

    /* Create an index key. Primary key indexes consists of just the primary
    ** key values. Other indexes consists of the indexed columns followed by
    ** the primary key values.  */







    nTmpReg = 1 + pIdx->nColumn + (pIdx==pPk ? 0 : pPk->nColumn);
    regTmp = sqlite4GetTempRange(pParse, nTmpReg);
    regPk = regTmp + nTmpReg - 1;

    for(i=0; i<pIdx->nColumn; i++){
      int idx = pIdx->aiColumn[i];
      sqlite4VdbeAddOp2(v, OP_SCopy, regContent+idx, regTmp+i);
    }
    if( pIdx!=pPk ){
      for(i=0; i<pPk->nColumn; i++){
        int idx = pPk->aiColumn[i];
        sqlite4VdbeAddOp2(v, OP_SCopy, regContent+idx, regTmp+i+pIdx->nColumn);
      }
    }
    sqlite4VdbeAddOp4Int(v, OP_MakeKey, regTmp, nTmpReg-1, regKey, iIdx);

    VdbeComment((v, "key for %s", pIdx->zName));

    /* If Index.onError==OE_None, then pIdx is not a UNIQUE or PRIMARY KEY 
    ** index. In this case there is no need to test the index for uniqueness
    ** - all that is required is to populate the regKey register. Jump 
    ** to the next iteration of the loop if this is the case.  */
    onError = pIdx->onError;
................................................................................
        if( onError==OE_Ignore ) onError = OE_Replace;
        else if( onError==OE_Fail ) onError = OE_Abort;
      }

      iLabel = sqlite4VdbeMakeLabel(v);
      if( pIdx!=pPk ){
        sqlite4VdbeAddOp3(v, OP_IsNull, regTmp, iLabel, pIdx->nColumn);

      }
      if( regOldKey && pIdx==pPk ){
        sqlite4VdbeAddOp3(v, OP_Eq, regOldKey, iLabel, regKey);
      }
      sqlite4VdbeAddOp4(v, OP_Blob, nPkRoot, regPk, 0, (char*)aPkRoot, nPkRoot);
      sqlite4VdbeAddOp4Int(v, OP_IsUnique, iIdx, iLabel, regKey, regPk);
      if( regOldKey && pIdx!=pPk ){
        sqlite4VdbeAddOp3(v, OP_Eq, regOldKey, iLabel, regPk);
      }
      
      switch( onError ){
        case OE_Rollback:
................................................................................

  /* Generate code to serialize array of registers into a database record. 
  ** This OP_MakeRecord also serves to apply affinities to the array of
  ** input registers at regContent. For this reason it must be executed 
  ** before any MakeRecord instructions used to create covering index
  ** records.  */
  regRec = sqlite4GetTempReg(pParse);




  sqlite4VdbeAddOp3(v, OP_MakeRecord, regContent, pTab->nCol, regRec);
  sqlite4TableAffinityStr(v, pTab);
  sqlite4ExprCacheAffinityChange(pParse, regContent, pTab->nCol);

  regCover = sqlite4GetTempReg(pParse);

  /* Write the entry to each index. */
  for(i=0, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
    assert( pIdx->eIndexType!=SQLITE4_INDEX_PRIMARYKEY || aRegIdx[i] );
    if( pIdx->eIndexType==SQLITE4_INDEX_FTS5 ){
      int iPK;







>
>
>
>
>
>
>
|
|
|

|
|
|
|
|
|
|
|
|
|
|
>







 







>




<







 







>
>
>
>
|
|
|
|







1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
....
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343

1344
1345
1346
1347
1348
1349
1350
....
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452

    /* If regKey is 0, pIdx will not be updated. */
    if( regKey==0 ) continue;

    /* Create an index key. Primary key indexes consists of just the primary
    ** key values. Other indexes consists of the indexed columns followed by
    ** the primary key values.  */
    if( pIdx->tnum==KVSTORE_ROOT ){
      /* If this is the sqlite_kvstore PK index, interpret the value
      ** specified for column "key" as a blob and use it as the index key. */
      sqlite4VdbeAddOp2(v, OP_SCopy, regContent, regKey);
      sqlite4VdbeAddOp1(v, OP_ToBlob, regKey);
      regPk = regKey;
    }else{
      nTmpReg = 1 + pIdx->nColumn + (pIdx==pPk ? 0 : pPk->nColumn);
      regTmp = sqlite4GetTempRange(pParse, nTmpReg);
      regPk = regTmp + nTmpReg - 1;

      for(i=0; i<pIdx->nColumn; i++){
        int idx = pIdx->aiColumn[i];
        sqlite4VdbeAddOp2(v, OP_SCopy, regContent+idx, regTmp+i);
      }
      if( pIdx!=pPk ){
        for(i=0; i<pPk->nColumn; i++){
          int idx = pPk->aiColumn[i];
          sqlite4VdbeAddOp2(v, OP_SCopy, regContent+idx,regTmp+i+pIdx->nColumn);
        }
      }
      sqlite4VdbeAddOp4Int(v, OP_MakeKey, regTmp, nTmpReg-1, regKey, iIdx);
    }
    VdbeComment((v, "key for %s", pIdx->zName));

    /* If Index.onError==OE_None, then pIdx is not a UNIQUE or PRIMARY KEY 
    ** index. In this case there is no need to test the index for uniqueness
    ** - all that is required is to populate the regKey register. Jump 
    ** to the next iteration of the loop if this is the case.  */
    onError = pIdx->onError;
................................................................................
        if( onError==OE_Ignore ) onError = OE_Replace;
        else if( onError==OE_Fail ) onError = OE_Abort;
      }

      iLabel = sqlite4VdbeMakeLabel(v);
      if( pIdx!=pPk ){
        sqlite4VdbeAddOp3(v, OP_IsNull, regTmp, iLabel, pIdx->nColumn);
        sqlite4VdbeAddOp4(v, OP_Blob, nPkRoot, regPk, 0,(char*)aPkRoot,nPkRoot);
      }
      if( regOldKey && pIdx==pPk ){
        sqlite4VdbeAddOp3(v, OP_Eq, regOldKey, iLabel, regKey);
      }

      sqlite4VdbeAddOp4Int(v, OP_IsUnique, iIdx, iLabel, regKey, regPk);
      if( regOldKey && pIdx!=pPk ){
        sqlite4VdbeAddOp3(v, OP_Eq, regOldKey, iLabel, regPk);
      }
      
      switch( onError ){
        case OE_Rollback:
................................................................................

  /* Generate code to serialize array of registers into a database record. 
  ** This OP_MakeRecord also serves to apply affinities to the array of
  ** input registers at regContent. For this reason it must be executed 
  ** before any MakeRecord instructions used to create covering index
  ** records.  */
  regRec = sqlite4GetTempReg(pParse);
  if( IsKvstore(pTab) ){
    sqlite4VdbeAddOp2(v, OP_SCopy, regContent+1, regRec);
    sqlite4VdbeAddOp1(v, OP_ToBlob, regRec);
  }else{
    sqlite4VdbeAddOp3(v, OP_MakeRecord, regContent, pTab->nCol, regRec);
    sqlite4TableAffinityStr(v, pTab);
    sqlite4ExprCacheAffinityChange(pParse, regContent, pTab->nCol);
  }
  regCover = sqlite4GetTempReg(pParse);

  /* Write the entry to each index. */
  for(i=0, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
    assert( pIdx->eIndexType!=SQLITE4_INDEX_PRIMARYKEY || aRegIdx[i] );
    if( pIdx->eIndexType==SQLITE4_INDEX_FTS5 ){
      int iPK;

Changes to src/update.c

441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
...
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
  sqlite4VdbeAddOp3(v, OP_Null, 0, regNew, regNew+pTab->nCol-1);
  for(i=0; i<pTab->nCol; i++){
    j = aXRef[i];
    if( j>=0 ){
      sqlite4ExprCode(pParse, pChanges->a[j].pExpr, regNew+i);
    }else if( 0==(tmask&TRIGGER_BEFORE) || i>31 || (newmask&(1<<i)) ){
      /* This branch loads the value of a column that will not be changed 
       ** into a register. This is done if there are no BEFORE triggers, or
       ** if there are one or more BEFORE triggers that use this value via
       ** a new.* reference in a trigger program.
       */
      testcase( i==31 );
      testcase( i==32 );
      sqlite4VdbeAddOp3(v, OP_Column, iCur+iPk, i, regNew+i);
      sqlite4ColumnDefault(v, pTab, i, regNew+i);
    }
  }
  if( bImplicitPk ){
    sqlite4VdbeAddOp2(v, OP_Rowid, iCur+iPk, regNew-1);
  }

  /* Fire any BEFORE UPDATE triggers. This happens before constraints are
................................................................................
    /* If it did not delete it, the row-trigger may still have modified 
    ** some of the columns of the row being updated. Load the values for 
    ** all columns not modified by the update statement into their 
    ** registers in case this has happened.
    */
    for(i=0; i<pTab->nCol; i++){
      if( aXRef[i]<0 ){
        sqlite4VdbeAddOp3(v, OP_Column, iCur+iPk, i, regNew+i);
        sqlite4ColumnDefault(v, pTab, i, regNew+i);
      }
    }
  }

  if( !isView ){
    int j1;                       /* Address of jump instruction */








|
|
|
|


|
<







 







|
<







441
442
443
444
445
446
447
448
449
450
451
452
453
454

455
456
457
458
459
460
461
...
478
479
480
481
482
483
484
485

486
487
488
489
490
491
492
  sqlite4VdbeAddOp3(v, OP_Null, 0, regNew, regNew+pTab->nCol-1);
  for(i=0; i<pTab->nCol; i++){
    j = aXRef[i];
    if( j>=0 ){
      sqlite4ExprCode(pParse, pChanges->a[j].pExpr, regNew+i);
    }else if( 0==(tmask&TRIGGER_BEFORE) || i>31 || (newmask&(1<<i)) ){
      /* This branch loads the value of a column that will not be changed 
      ** into a register. This is done if there are no BEFORE triggers, or
      ** if there are one or more BEFORE triggers that use this value via
      ** a new.* reference in a trigger program.
      */
      testcase( i==31 );
      testcase( i==32 );
      sqlite4ExprCodeGetColumnOfTable(v, pTab, iCur+iPk, i, regNew+i);

    }
  }
  if( bImplicitPk ){
    sqlite4VdbeAddOp2(v, OP_Rowid, iCur+iPk, regNew-1);
  }

  /* Fire any BEFORE UPDATE triggers. This happens before constraints are
................................................................................
    /* If it did not delete it, the row-trigger may still have modified 
    ** some of the columns of the row being updated. Load the values for 
    ** all columns not modified by the update statement into their 
    ** registers in case this has happened.
    */
    for(i=0; i<pTab->nCol; i++){
      if( aXRef[i]<0 ){
        sqlite4ExprCodeGetColumnOfTable(v, pTab, iCur+iPk, i, regNew+i);

      }
    }
  }

  if( !isView ){
    int j1;                       /* Address of jump instruction */

Changes to src/vdbe.c

3046
3047
3048
3049
3050
3051
3052

3053
3054
3055
3056
3057
3058
3059
3060
3061
3062
3063

3064
3065

3066
3067
3068
3069
3070


3071
3072


3073
3074
3075
3076
3077
3078
3079
3080
3081

3082
3083
3084
3085
3086
3087
3088
case OP_IsUnique: {        /* jump, in3 */
  VdbeCursor *pC;
  Mem *pProbe;
  Mem *pOut;
  int iOut;
  int nShort;
  int dir;

  u64 dummy;

  KVByteArray const *aKey;        /* Key read from cursor */
  KVSize nKey;                    /* Size of aKey in bytes */

  assert( pOp->p4type==P4_INT32 );

  pProbe = &aMem[pOp->p3];
  pC = p->apCsr[pOp->p1];
  pC->rowChnged = 1;
  pOut = (pOp->p4.i==0 ? 0 : &aMem[pOp->p4.i]);

  assert( pOut==0 || (pOut->flags & MEM_Blob) );


  nShort = sqlite4VdbeShortKey((u8 *)pProbe->z, pProbe->n, 
      pC->pKeyInfo->nField - pC->pKeyInfo->nPK, 0
  );
  assert( nShort<=pProbe->n );
  assert( (nShort==pProbe->n)==(pC->pKeyInfo->nPK==0) );



  dir = (pC->pKeyInfo->nPK==0 ? 0 : 1);


  rc = sqlite4KVCursorSeek(pC->pKVCur, (u8 *)pProbe->z, nShort, dir);

  if( rc==SQLITE4_OK && pOut ){
    sqlite4VdbeMemCopy(pOut, pProbe);
  }else if( rc==SQLITE4_NOTFOUND ){
    rc = SQLITE4_OK;
    pc = pOp->p2-1;
  }else if( rc==SQLITE4_INEXACT ){
    assert( nShort<pProbe->n );

    rc = sqlite4KVCursorKey(pC->pKVCur, &aKey, &nKey);
    if( rc==SQLITE4_OK ){
      if( nKey<nShort || memcmp(pProbe->z, aKey, nShort) ){
        pc = pOp->p2-1;
      }else if( pOut ){
        iOut = sqlite4GetVarint64((u8 *)pOut->z, pOut->n, &dummy);
        rc = sqlite4VdbeMemGrow(pOut, iOut+(nKey - nShort), 1);







>











>
|

>
|
|
|
|
|
>
>
|
<
>
>









>







3046
3047
3048
3049
3050
3051
3052
3053
3054
3055
3056
3057
3058
3059
3060
3061
3062
3063
3064
3065
3066
3067
3068
3069
3070
3071
3072
3073
3074
3075
3076

3077
3078
3079
3080
3081
3082
3083
3084
3085
3086
3087
3088
3089
3090
3091
3092
3093
3094
3095
case OP_IsUnique: {        /* jump, in3 */
  VdbeCursor *pC;
  Mem *pProbe;
  Mem *pOut;
  int iOut;
  int nShort;
  int dir;
  int bPk;
  u64 dummy;

  KVByteArray const *aKey;        /* Key read from cursor */
  KVSize nKey;                    /* Size of aKey in bytes */

  assert( pOp->p4type==P4_INT32 );

  pProbe = &aMem[pOp->p3];
  pC = p->apCsr[pOp->p1];
  pC->rowChnged = 1;
  pOut = (pOp->p4.i==0 ? 0 : &aMem[pOp->p4.i]);
  bPk = (pC->pKeyInfo->nPK==0);
  assert( pOut==0 || (pOut->flags & MEM_Blob) || bPk );

  if( bPk==0 ){
    nShort = sqlite4VdbeShortKey((u8 *)pProbe->z, pProbe->n, 
        pC->pKeyInfo->nField - pC->pKeyInfo->nPK, 0
        );
    assert( nShort<=pProbe->n );
    assert( (nShort==pProbe->n)==(pC->pKeyInfo->nPK==0) );
  }else{
    nShort = pProbe->n;
  }


  dir = !bPk;      /* "dir = (bPk ? 0 : 1);" */
  rc = sqlite4KVCursorSeek(pC->pKVCur, (u8 *)pProbe->z, nShort, dir);

  if( rc==SQLITE4_OK && pOut ){
    sqlite4VdbeMemCopy(pOut, pProbe);
  }else if( rc==SQLITE4_NOTFOUND ){
    rc = SQLITE4_OK;
    pc = pOp->p2-1;
  }else if( rc==SQLITE4_INEXACT ){
    assert( nShort<pProbe->n );
    assert( bPk==0 );
    rc = sqlite4KVCursorKey(pC->pKVCur, &aKey, &nKey);
    if( rc==SQLITE4_OK ){
      if( nKey<nShort || memcmp(pProbe->z, aKey, nShort) ){
        pc = pOp->p2-1;
      }else if( pOut ){
        iOut = sqlite4GetVarint64((u8 *)pOut->z, pOut->n, &dummy);
        rc = sqlite4VdbeMemGrow(pOut, iOut+(nKey - nShort), 1);

Added test/kvstore2.test































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
# 2013 Jul 31
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the SELECT statement.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set ::testprefix kvstore2


do_execsql_test 1.0 { PRAGMA writable_schema = 1; } {}

do_catchsql_test 1.1 {
  INSERT INTO sqlite_kvstore VALUES(NULL, 'helloworld');
} {1 {sqlite_kvstore.key may not be NULL}}

do_execsql_test 1.2 {
  INSERT INTO sqlite_kvstore VALUES('abc', 'helloworld');
  SELECT count(*) FROM sqlite_kvstore;
} {1}

do_execsql_test 1.3 {
  SELECT CAST(key AS text), CAST(value AS text) FROM sqlite_kvstore;
} {abc helloworld}

do_execsql_test 1.4 {
  SELECT quote(key), quote(value) FROM sqlite_kvstore;
} {x'616263' x'68656c6c6f776f726c64'}

do_catchsql_test 1.5 {
  INSERT INTO sqlite_kvstore VALUES('abc', 'def');
} {1 {PRIMARY KEY must be unique}}

do_execsql_test 1.6 {
  REPLACE INTO sqlite_kvstore VALUES('abc', 'def');
  SELECT cast(key AS text), cast(value AS text) FROM sqlite_kvstore;
} {abc def}

do_execsql_test 1.7 {
  INSERT INTO sqlite_kvstore VALUES(123, 456.789);
  SELECT cast(key AS text), cast(value AS text) FROM sqlite_kvstore;
} {123 456.789 abc def}

do_execsql_test 1.8 {
  SELECT quote(key), quote(value) FROM sqlite_kvstore;
} {x'313233' x'3435362e373839' x'616263' x'646566'}

do_execsql_test 1.9 {
  UPDATE sqlite_kvstore SET value = x'AABBCC' WHERE key = x'616263';
  SELECT quote(key), quote(value) FROM sqlite_kvstore;
} {x'313233' x'3435362e373839' x'616263' x'aabbcc'}

do_execsql_test 1.10 {
  UPDATE sqlite_kvstore SET value = 111;
  SELECT quote(key), quote(value) FROM sqlite_kvstore;
} {x'313233' x'313131' x'616263' x'313131'}

do_execsql_test 1.11 {
  DELETE FROM sqlite_kvstore WHERE key = x'313233';
  SELECT quote(key), quote(value) FROM sqlite_kvstore;
} {x'616263' x'313131'}

do_execsql_test 1.12 {
  DELETE FROM sqlite_kvstore;
  SELECT quote(key), quote(value) FROM sqlite_kvstore;
} {}


finish_test

Changes to test/permutations.test

184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
  func.test func2.test func3.test 
  fuzz.test fuzz2.test 
  in.test in2.test in3.test in4.test
  index.test index2.test index3.test index4.test 
  insert.test insert2.test insert3.test insert5.test
  join.test join2.test join3.test join4.test join5.test join6.test
  keyword1.test
  kvstore.test
  laststmtchanges.test
  limit.test
  like.test like2.test
  main.test
  manydb.test
  misc5.test misc6.test
  misuse.test







|







184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
  func.test func2.test func3.test 
  fuzz.test fuzz2.test 
  in.test in2.test in3.test in4.test
  index.test index2.test index3.test index4.test 
  insert.test insert2.test insert3.test insert5.test
  join.test join2.test join3.test join4.test join5.test join6.test
  keyword1.test
  kvstore.test kvstore2.test
  laststmtchanges.test
  limit.test
  like.test like2.test
  main.test
  manydb.test
  misc5.test misc6.test
  misuse.test