SQLite

Check-in [81c5a5b48b]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 81c5a5b48b05186a1690198092ac92006d9f7020
User & Date: danielk1977 2006-06-17 03:27:22.000
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: 48d297c561 user: danielk1977 tags: trunk)
03:27
Fixes for UPDATE statements on virtual tables. (CVS 3263) (check-in: 81c5a5b48b user: danielk1977 tags: trunk)
2006-06-16
21:13
Rework the way UPDATE works for virtual tables. (CVS 3262) (check-in: 2119e7bf55 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/update.c.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    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 */







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    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 */
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
  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{







|







567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
  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{
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
  */
  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 */







|













602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
  */
  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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2006 June 10
**
** 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 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.
*/













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2006 June 10
**
** 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 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.
*/
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
    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;







|







518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
    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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2006 June 10
#
# 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 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













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2006 June 10
#
# 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 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
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
        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 {







<
<







405
406
407
408
409
410
411


412
413
414
415
416
417
418
        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 {
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
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}







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







431
432
433
434
435
436
437


438



439
440
441

442
443
444
445
446
447
448
449
450
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}
581
582
583
584
585
586
587
588
589
590
  }
} {31429}
do_test vtab1.7-13 {
  execsql {
    SELECT rowid, a, b, c FROM real_abc
  }
} {}
} ;# END if 0

finish_test







<


574
575
576
577
578
579
580

581
582
  }
} {31429}
do_test vtab1.7-13 {
  execsql {
    SELECT rowid, a, b, c FROM real_abc
  }
} {}


finish_test