/ Check-in [81c5a5b4]
Login

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

Overview
Comment:Fixes for UPDATE statements on virtual tables. (CVS 3263)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:81c5a5b48b05186a1690198092ac92006d9f7020
User & Date: danielk1977 2006-06-17 03:27:22
Context
2006-06-17
06:31
When updating a view, invoke the authorization callback for reading the view before setting the authorization-context to the view name. (CVS 3264) check-in: 48d297c5 user: danielk1977 tags: trunk
03:27
Fixes for UPDATE statements on virtual tables. (CVS 3263) check-in: 81c5a5b4 user: danielk1977 tags: trunk
2006-06-16
21:13
Rework the way UPDATE works for virtual tables. (CVS 3262) check-in: 2119e7bf user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/update.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
...
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle UPDATE statements.
**
** $Id: update.c,v 1.127 2006/06/16 21:13:22 drh Exp $
*/
#include "sqliteInt.h"

/* Forward declaration */
static void updateVirtualTable(
  Parse *pParse,       /* The parsing context */
  SrcList *pSrc,       /* The virtual table to be modified */
................................................................................
  int addr;                 /* Address of top of loop */

  /* Construct the SELECT statement that will find the new values for
  ** all updated rows. 
  */
  pEList = sqlite3ExprListAppend(0, sqlite3CreateIdExpr("_rowid_"), 0);
  if( pRowid ){
    pEList = sqlite3ExprListAppend(pEList, pRowid, 0);
  }
  for(i=0; i<pTab->nCol; i++){
    if( i==pTab->iPKey ){
      pExpr = sqlite3Expr(TK_NULL, 0, 0, 0);
    }else if( aXRef[i]>=0 ){
      pExpr = sqlite3ExprDup(pChanges->a[aXRef[i]].pExpr);
    }else{
................................................................................
  */
  sqlite3VdbeAddOp(v, OP_Rewind, ephemTab, 0);
  addr = sqlite3VdbeCurrentAddr(v);
  sqlite3VdbeAddOp(v, OP_Column,  ephemTab, 0);
  if( pRowid ){
    sqlite3VdbeAddOp(v, OP_Column, ephemTab, 1);
  }else{
    sqlite3VdbeAddOp(v, OP_Dup, 1, 0);
  }
  for(i=0; i<pTab->nCol; i++){
    sqlite3VdbeAddOp(v, OP_Column, ephemTab, i+1+(pRowid!=0));
  }
  sqlite3VdbeOp3(v, OP_VUpdate, 0, pTab->nCol+2, 
                     (const char*)pTab->pVtab, P3_VTAB);
  sqlite3VdbeAddOp(v, OP_Next, ephemTab, addr);
  sqlite3VdbeAddOp(v, OP_Close, ephemTab, 0);

  /* Cleanup */
  sqlite3SelectDelete(pSelect);  
}
#endif /* SQLITE_OMIT_VIRTUALTABLE */







|







 







|







 







|













8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
...
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle UPDATE statements.
**
** $Id: update.c,v 1.128 2006/06/17 03:27:22 danielk1977 Exp $
*/
#include "sqliteInt.h"

/* Forward declaration */
static void updateVirtualTable(
  Parse *pParse,       /* The parsing context */
  SrcList *pSrc,       /* The virtual table to be modified */
................................................................................
  int addr;                 /* Address of top of loop */

  /* Construct the SELECT statement that will find the new values for
  ** all updated rows. 
  */
  pEList = sqlite3ExprListAppend(0, sqlite3CreateIdExpr("_rowid_"), 0);
  if( pRowid ){
    pEList = sqlite3ExprListAppend(pEList, sqlite3ExprDup(pRowid), 0);
  }
  for(i=0; i<pTab->nCol; i++){
    if( i==pTab->iPKey ){
      pExpr = sqlite3Expr(TK_NULL, 0, 0, 0);
    }else if( aXRef[i]>=0 ){
      pExpr = sqlite3ExprDup(pChanges->a[aXRef[i]].pExpr);
    }else{
................................................................................
  */
  sqlite3VdbeAddOp(v, OP_Rewind, ephemTab, 0);
  addr = sqlite3VdbeCurrentAddr(v);
  sqlite3VdbeAddOp(v, OP_Column,  ephemTab, 0);
  if( pRowid ){
    sqlite3VdbeAddOp(v, OP_Column, ephemTab, 1);
  }else{
    sqlite3VdbeAddOp(v, OP_Dup, 0, 0);
  }
  for(i=0; i<pTab->nCol; i++){
    sqlite3VdbeAddOp(v, OP_Column, ephemTab, i+1+(pRowid!=0));
  }
  sqlite3VdbeOp3(v, OP_VUpdate, 0, pTab->nCol+2, 
                     (const char*)pTab->pVtab, P3_VTAB);
  sqlite3VdbeAddOp(v, OP_Next, ephemTab, addr);
  sqlite3VdbeAddOp(v, OP_Close, ephemTab, 0);

  /* Cleanup */
  sqlite3SelectDelete(pSelect);  
}
#endif /* SQLITE_OMIT_VIRTUALTABLE */

Changes to src/vtab.c.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
**    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 contains code used to help implement virtual tables.
**
** $Id: vtab.c,v 1.15 2006/06/16 16:08:55 danielk1977 Exp $
*/
#ifndef SQLITE_OMIT_VIRTUALTABLE
#include "sqliteInt.h"

/*
** External API function used to create a new virtual-table module.
*/
................................................................................
    rc = pModule->xBegin(pVtab);
    if( rc!=SQLITE_OK ){
      return rc;
    }

    /* Grow the sqlite3.aVTrans array if required */
    if( (db->nVTrans%ARRAY_INCR)==0 ){
      sqlite3_vtab *aVTrans;
      int nBytes = sizeof(sqlite3_vtab *) * (db->nVTrans + ARRAY_INCR);
      aVTrans = sqliteRealloc((void *)db->aVTrans, nBytes);
      if( !aVTrans ){
        return SQLITE_NOMEM;
      }
      memset(&aVTrans[db->nVTrans], 0, sizeof(sqlite3_vtab *)*ARRAY_INCR);
      db->aVTrans = aVTrans;







|







 







|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
**    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 contains code used to help implement virtual tables.
**
** $Id: vtab.c,v 1.16 2006/06/17 03:27:23 danielk1977 Exp $
*/
#ifndef SQLITE_OMIT_VIRTUALTABLE
#include "sqliteInt.h"

/*
** External API function used to create a new virtual-table module.
*/
................................................................................
    rc = pModule->xBegin(pVtab);
    if( rc!=SQLITE_OK ){
      return rc;
    }

    /* Grow the sqlite3.aVTrans array if required */
    if( (db->nVTrans%ARRAY_INCR)==0 ){
      sqlite3_vtab **aVTrans;
      int nBytes = sizeof(sqlite3_vtab *) * (db->nVTrans + ARRAY_INCR);
      aVTrans = sqliteRealloc((void *)db->aVTrans, nBytes);
      if( !aVTrans ){
        return SQLITE_NOMEM;
      }
      memset(&aVTrans[db->nVTrans], 0, sizeof(sqlite3_vtab *)*ARRAY_INCR);
      db->aVTrans = aVTrans;

Changes to test/vtab1.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
...
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449

450
451
452
453
454
455
456
457
...
581
582
583
584
585
586
587
588
589
590
#    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 creating and dropping virtual tables.
#
# $Id: vtab1.test,v 1.19 2006/06/16 21:13:23 drh Exp $

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

ifcapable !vtab {
  finish_test
  return
................................................................................
        xFilter    {SELECT rowid, * FROM 'treal'} ]

execsql {
  DROP TABLE t1;
  DROP TABLE treal;
}

if 0 {   # FIXME

#----------------------------------------------------------------------
# Test cases vtab1-6 test INSERT, UPDATE and DELETE operations 
# on virtual tables.
do_test vtab1-6-1 {
  execsql { SELECT sql FROM sqlite_master }
} {}
do_test vtab1-6-2 {
................................................................................
do_test vtab1-6-4 {
  execsql {
    UPDATE techo SET a = 5;
    SELECT * FROM techo;
  }
} {5 2 3}

# TODO: This causes a crash at present.
#
# do_test vtab1-6-5 {
# explain {
#    UPDATE techo set a = a||b||c;
#  }
#  execsql {
#    UPDATE techo set a = a||b||c;
#    SELECT * FROM techo;
#  }

#} {523 2 3}

do_test vtab1-6-6 {
  execsql {
    UPDATE techo set rowid = 10;
    SELECT rowid FROM techo;
  }
} {10}
................................................................................
  }
} {31429}
do_test vtab1.7-13 {
  execsql {
    SELECT rowid, a, b, c FROM real_abc
  }
} {}
} ;# END if 0

finish_test







|







 







<
<







 







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







 







<


7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
405
406
407
408
409
410
411


412
413
414
415
416
417
418
...
431
432
433
434
435
436
437


438



439
440
441

442
443
444
445
446
447
448
449
450
...
574
575
576
577
578
579
580

581
582
#    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 creating and dropping virtual tables.
#
# $Id: vtab1.test,v 1.20 2006/06/17 03:27:23 danielk1977 Exp $

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

ifcapable !vtab {
  finish_test
  return
................................................................................
        xFilter    {SELECT rowid, * FROM 'treal'} ]

execsql {
  DROP TABLE t1;
  DROP TABLE treal;
}



#----------------------------------------------------------------------
# Test cases vtab1-6 test INSERT, UPDATE and DELETE operations 
# on virtual tables.
do_test vtab1-6-1 {
  execsql { SELECT sql FROM sqlite_master }
} {}
do_test vtab1-6-2 {
................................................................................
do_test vtab1-6-4 {
  execsql {
    UPDATE techo SET a = 5;
    SELECT * FROM techo;
  }
} {5 2 3}



do_test vtab1-6-5 {



 execsql {
   UPDATE techo set a = a||b||c;
   SELECT * FROM techo;

 }
} {523 2 3}

do_test vtab1-6-6 {
  execsql {
    UPDATE techo set rowid = 10;
    SELECT rowid FROM techo;
  }
} {10}
................................................................................
  }
} {31429}
do_test vtab1.7-13 {
  execsql {
    SELECT rowid, a, b, c FROM real_abc
  }
} {}


finish_test