Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Arrange for sqlite3_last_insert_rowid() to work with virtual tables. (CVS 3259) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
afa39a46320e9996a5478ea6e19eb4c2 |
User & Date: | danielk1977 2006-06-16 06:17:47.000 |
Context
2006-06-16
| ||
08:01 | Add some tests (and fixes) for virtual tables and the authorization callback. Still more to come. (CVS 3260) (check-in: 9497c66e55 user: danielk1977 tags: trunk) | |
06:17 | Arrange for sqlite3_last_insert_rowid() to work with virtual tables. (CVS 3259) (check-in: afa39a4632 user: danielk1977 tags: trunk) | |
2006-06-15
| ||
16:26 | Fix type in test_schema.c. (CVS 3258) (check-in: d65d83d383 user: danielk1977 tags: trunk) | |
Changes
Changes to src/insert.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** 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 INSERT statements in SQLite. ** | | | 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 INSERT statements in SQLite. ** ** $Id: insert.c,v 1.168 2006/06/16 06:17:47 danielk1977 Exp $ */ #include "sqliteInt.h" /* ** Set P3 of the most recently inserted opcode to a column affinity ** string for index pIdx. A column affinity string has one character ** for each column in the table, according to the affinity of the column: |
︙ | ︙ | |||
628 629 630 631 632 633 634 | } /* Generate code to check constraints and generate index keys and ** do the insertion. */ #ifndef SQLITE_OMIT_VIRTUALTABLE if( IsVirtual(pTab) ){ | | | 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 | } /* Generate code to check constraints and generate index keys and ** do the insertion. */ #ifndef SQLITE_OMIT_VIRTUALTABLE if( IsVirtual(pTab) ){ sqlite3VdbeOp3(v, OP_VUpdate, 1, pTab->nCol+2, (const char*)pTab->pVtab, P3_VTAB); }else #endif { sqlite3GenerateConstraintChecks(pParse, pTab, base, 0, keyColumn>=0, 0, onError, endOfLoop); sqlite3CompleteInsertion(pParse, pTab, base, 0,0,0, |
︙ | ︙ |
Changes to src/sqlite.h.in.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This header file defines the interface that the SQLite library ** presents to client programs. ** | | | 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 header file defines the interface that the SQLite library ** presents to client programs. ** ** @(#) $Id: sqlite.h.in,v 1.178 2006/06/16 06:17:47 danielk1977 Exp $ */ #ifndef _SQLITE3_H_ #define _SQLITE3_H_ #include <stdarg.h> /* Needed for the definition of va_list */ /* ** Make sure we can call this stuff from C++. |
︙ | ︙ | |||
1548 1549 1550 1551 1552 1553 1554 | int (*xOpen)(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor); int (*xClose)(sqlite3_vtab_cursor*); int (*xFilter)(sqlite3_vtab_cursor*, int idxNum, const char *idxStr, int argc, sqlite3_value **argv); int (*xNext)(sqlite3_vtab_cursor*); int (*xColumn)(sqlite3_vtab_cursor*, sqlite3_context*, int); int (*xRowid)(sqlite3_vtab_cursor*, sqlite_int64 *pRowid); | | | 1548 1549 1550 1551 1552 1553 1554 1555 1556 1557 1558 1559 1560 1561 1562 | int (*xOpen)(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor); int (*xClose)(sqlite3_vtab_cursor*); int (*xFilter)(sqlite3_vtab_cursor*, int idxNum, const char *idxStr, int argc, sqlite3_value **argv); int (*xNext)(sqlite3_vtab_cursor*); int (*xColumn)(sqlite3_vtab_cursor*, sqlite3_context*, int); int (*xRowid)(sqlite3_vtab_cursor*, sqlite_int64 *pRowid); int (*xUpdate)(sqlite3_vtab *, int, sqlite3_value **, sqlite_int64 *); int (*xBegin)(sqlite3_vtab *pVTab); int (*xSync)(sqlite3_vtab *pVTab); int (*xCommit)(sqlite3_vtab *pVTab); int (*xRollback)(sqlite3_vtab *pVTab); int (*xIsInTrans)(sqlite3_vtab *pVTab); }; |
︙ | ︙ |
Changes to src/test8.c.
︙ | ︙ | |||
9 10 11 12 13 14 15 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** Code for testing the virtual table interfaces. This code ** is not included in the SQLite library. It is used for automated ** testing of the SQLite library. ** | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** Code for testing the virtual table interfaces. This code ** is not included in the SQLite library. It is used for automated ** testing of the SQLite library. ** ** $Id: test8.c,v 1.24 2006/06/16 06:17:47 danielk1977 Exp $ */ #include "sqliteInt.h" #include "tcl.h" #include "os.h" #include <stdlib.h> #include <string.h> |
︙ | ︙ | |||
508 509 510 511 512 513 514 | ** INTEGER NULL (nCol args) UPDATE (do not set rowid) ** INTEGER INTEGER (nCol args) UPDATE (with SET rowid = <arg1>) ** ** NULL NULL (nCol args) INSERT INTO (automatic rowid value) ** NULL INTEGER (nCol args) INSERT (incl. rowid value) ** */ | | > > > > > | 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 | ** INTEGER NULL (nCol args) UPDATE (do not set rowid) ** INTEGER INTEGER (nCol args) UPDATE (with SET rowid = <arg1>) ** ** NULL NULL (nCol args) INSERT INTO (automatic rowid value) ** NULL INTEGER (nCol args) INSERT (incl. rowid value) ** */ int echoUpdate( sqlite3_vtab *tab, int nData, sqlite3_value **apData, sqlite_int64 *pRowid ){ echo_vtab *pVtab = (echo_vtab *)tab; sqlite3 *db = pVtab->db; int rc = SQLITE_OK; sqlite3_stmt *pStmt; char *z = 0; /* SQL statement to execute */ int bindArgZero = 0; /* True to bind apData[0] to sql var no. nData */ |
︙ | ︙ | |||
553 554 555 556 557 558 559 | } /* If the first argument is NULL and there are more than two args, INSERT */ else if( nData>2 && sqlite3_value_type(apData[0])==SQLITE_NULL ){ int ii; char *zInsert = 0; char *zValues = 0; | | | 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 | } /* If the first argument is NULL and there are more than two args, INSERT */ else if( nData>2 && sqlite3_value_type(apData[0])==SQLITE_NULL ){ int ii; char *zInsert = 0; char *zValues = 0; zInsert = sqlite3_mprintf("INSERT OR REPLACE INTO %Q (", pVtab->zTableName); if( sqlite3_value_type(apData[1])==SQLITE_INTEGER ){ bindArgOne = 1; zValues = sqlite3_mprintf("?"); string_concat(&zInsert, "rowid", 0); } |
︙ | ︙ | |||
597 598 599 600 601 602 603 604 605 606 607 608 609 610 | } for(i=2; i<nData; i++){ if( apData[i] ) sqlite3_bind_value(pStmt, i, apData[i]); } sqlite3_step(pStmt); rc = sqlite3_finalize(pStmt); } return rc; } /* ** A virtual table module that merely echos method calls into TCL ** variables. | > > > > | 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 | } for(i=2; i<nData; i++){ if( apData[i] ) sqlite3_bind_value(pStmt, i, apData[i]); } sqlite3_step(pStmt); rc = sqlite3_finalize(pStmt); } if( pRowid && rc==SQLITE_OK ){ *pRowid = sqlite3_last_insert_rowid(db); } return rc; } /* ** A virtual table module that merely echos method calls into TCL ** variables. |
︙ | ︙ |
Changes to src/test_schema.c.
︙ | ︙ | |||
9 10 11 12 13 14 15 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** Code for testing the virtual table interfaces. This code ** is not included in the SQLite library. It is used for automated ** testing of the SQLite library. ** | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** Code for testing the virtual table interfaces. This code ** is not included in the SQLite library. It is used for automated ** testing of the SQLite library. ** ** $Id: test_schema.c,v 1.3 2006/06/16 06:17:47 danielk1977 Exp $ */ /* The code in this file defines a sqlite3 module that provides ** a read-only view of the current database schema. There is one ** row in the schema table for each column in the database. */ #define SCHEMA \ |
︙ | ︙ | |||
297 298 299 300 301 302 303 304 305 306 307 308 309 310 | Tcl_Obj *CONST objv[] /* Command arguments */ ){ sqlite3 *db; if( objc!=2 ){ Tcl_WrongNumArgs(interp, 1, objv, "DB"); return TCL_ERROR; } #ifndef SQLITE_OMIT_VIRTUALTABLE sqlite3_create_module(db, "schema", &schemaModule, 0); #endif return TCL_OK; } /* | > | 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 | Tcl_Obj *CONST objv[] /* Command arguments */ ){ sqlite3 *db; if( objc!=2 ){ Tcl_WrongNumArgs(interp, 1, objv, "DB"); return TCL_ERROR; } if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR; #ifndef SQLITE_OMIT_VIRTUALTABLE sqlite3_create_module(db, "schema", &schemaModule, 0); #endif return TCL_OK; } /* |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
39 40 41 42 43 44 45 | ** ** Various scripts scan this source file in order to generate HTML ** documentation, headers files, or other derived files. The formatting ** of the code in this file is, therefore, important. See other comments ** in this file for details. If in doubt, do not deviate from existing ** commenting and indentation practices when changing or adding code. ** | | | 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | ** ** Various scripts scan this source file in order to generate HTML ** documentation, headers files, or other derived files. The formatting ** of the code in this file is, therefore, important. See other comments ** in this file for details. If in doubt, do not deviate from existing ** commenting and indentation practices when changing or adding code. ** ** $Id: vdbe.c,v 1.562 2006/06/16 06:17:47 danielk1977 Exp $ */ #include "sqliteInt.h" #include "os.h" #include <ctype.h> #include "vdbeInt.h" /* |
︙ | ︙ | |||
3265 3266 3267 3268 3269 3270 3271 3272 3273 3274 3275 3276 3277 3278 | ** be an integer. The stack is popped twice by this instruction. ** ** If the OPFLAG_NCHANGE flag of P2 is set, then the row change count is ** incremented (otherwise not). If the OPFLAG_LASTROWID flag of P2 is set, ** then rowid is stored for subsequent return by the ** sqlite3_last_insert_rowid() function (otherwise it's unmodified). ** ** This instruction only works on tables. The equivalent instruction ** for indices is OP_IdxInsert. */ case OP_Insert: { /* no-push */ Mem *pNos = &pTos[-1]; int i = pOp->p1; Cursor *pC; | > > > > | 3265 3266 3267 3268 3269 3270 3271 3272 3273 3274 3275 3276 3277 3278 3279 3280 3281 3282 | ** be an integer. The stack is popped twice by this instruction. ** ** If the OPFLAG_NCHANGE flag of P2 is set, then the row change count is ** incremented (otherwise not). If the OPFLAG_LASTROWID flag of P2 is set, ** then rowid is stored for subsequent return by the ** sqlite3_last_insert_rowid() function (otherwise it's unmodified). ** ** Parameter P3 may point to a string containing the table-name, or ** may be NULL. If it is not NULL, then the update-hook ** (sqlite3.xUpdateCallback) is invoked following a successful insert. ** ** This instruction only works on tables. The equivalent instruction ** for indices is OP_IdxInsert. */ case OP_Insert: { /* no-push */ Mem *pNos = &pTos[-1]; int i = pOp->p1; Cursor *pC; |
︙ | ︙ | |||
4769 4770 4771 4772 4773 4774 4775 | pTos->n = 0; break; } #endif /* SQLITE_OMIT_VIRTUALTABLE */ #ifndef SQLITE_OMIT_VIRTUALTABLE | | | 4773 4774 4775 4776 4777 4778 4779 4780 4781 4782 4783 4784 4785 4786 4787 | pTos->n = 0; break; } #endif /* SQLITE_OMIT_VIRTUALTABLE */ #ifndef SQLITE_OMIT_VIRTUALTABLE /* Opcode: VUpdate P1 P2 P3 ** ** P3 is a pointer to a virtual table object, an sqlite3_vtab structure. ** This opcode invokes the corresponding xUpdate method. P2 values ** are taken from the stack to pass to the xUpdate invocation. The ** value on the top of the stack corresponds to the p2th element ** of the argv array passed to xUpdate. ** |
︙ | ︙ | |||
4791 4792 4793 4794 4795 4796 4797 4798 4799 4800 4801 4802 4803 4804 4805 4806 4807 4808 4809 4810 4811 4812 4813 4814 | ** where i>=2 is a NULL pointer (that is to say if argv[i]==NULL ** which is different from if sqlite3_value_type(argv[i])==SQLITE_NULL) ** that means to preserve a copy of that element. In other words, ** copy the corresponding value from the argv[0] row into the new row. ** ** If P2==1 then no insert is performed. argv[0] is the rowid of ** a row to delete. */ case OP_VUpdate: { /* no-push */ sqlite3_vtab *pVtab = (sqlite3_vtab *)(pOp->p3); sqlite3_module *pModule = (sqlite3_module *)pVtab->pModule; int nArg = pOp->p2; assert( pOp->p3type==P3_VTAB ); if( pModule->xUpdate==0 ){ sqlite3SetString(&p->zErrMsg, "read-only table", 0); rc = SQLITE_ERROR; }else{ int i; Mem **apArg = p->apArg; Mem *pX = &pTos[1-nArg]; for(i = 0; i<nArg; i++, pX++){ apArg[i] = pX->flags ? storeTypeInfo(pX,0), pX : 0; } if( sqlite3SafetyOff(db) ) goto abort_due_to_misuse; | > > > > > | > > > > | 4795 4796 4797 4798 4799 4800 4801 4802 4803 4804 4805 4806 4807 4808 4809 4810 4811 4812 4813 4814 4815 4816 4817 4818 4819 4820 4821 4822 4823 4824 4825 4826 4827 4828 4829 4830 4831 4832 4833 4834 4835 4836 | ** where i>=2 is a NULL pointer (that is to say if argv[i]==NULL ** which is different from if sqlite3_value_type(argv[i])==SQLITE_NULL) ** that means to preserve a copy of that element. In other words, ** copy the corresponding value from the argv[0] row into the new row. ** ** If P2==1 then no insert is performed. argv[0] is the rowid of ** a row to delete. ** ** P1 is a boolean flag. If it is set to true and the xUpdate call ** is successful, then the value returned by sqlite3_last_insert_rowid() ** is set to the value of the rowid for the row just inserted. */ case OP_VUpdate: { /* no-push */ sqlite3_vtab *pVtab = (sqlite3_vtab *)(pOp->p3); sqlite3_module *pModule = (sqlite3_module *)pVtab->pModule; int nArg = pOp->p2; assert( pOp->p3type==P3_VTAB ); if( pModule->xUpdate==0 ){ sqlite3SetString(&p->zErrMsg, "read-only table", 0); rc = SQLITE_ERROR; }else{ int i; sqlite_int64 rowid; Mem **apArg = p->apArg; Mem *pX = &pTos[1-nArg]; for(i = 0; i<nArg; i++, pX++){ apArg[i] = pX->flags ? storeTypeInfo(pX,0), pX : 0; } if( sqlite3SafetyOff(db) ) goto abort_due_to_misuse; rc = pModule->xUpdate(pVtab, nArg, apArg, &rowid); if( sqlite3SafetyOn(db) ) goto abort_due_to_misuse; if( pOp->p1 && rc==SQLITE_OK ){ assert( nArg>1 && apArg[0] && (apArg[0]->flags&MEM_Null) ); db->lastRowid = rowid; } } popStack(&pTos, nArg); break; } #endif /* SQLITE_OMIT_VIRTUALTABLE */ /* An other opcode is illegal... |
︙ | ︙ |
Changes to test/vtab1.test.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 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. # | | > < | 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 | # 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.18 2006/06/16 06:17:47 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !vtab { finish_test return } #---------------------------------------------------------------------- # Organization of tests in this file: # # vtab1-1.*: Error conditions and other issues surrounding creation/connection # of a virtual module. # vtab1-2.*: Test sqlite3_declare_vtab() and the xConnect/xDisconnect methods. # vtab1-3.*: Table scans and WHERE clauses. # vtab1-4.*: Table scans and ORDER BY clauses. # vtab1-5.*: Test queries that include joins. This brings the # sqlite3_index_info.estimatedCost variable into play. # vtab1-6.*: Test UPDATE/INSERT/DELETE on vtables. # # This file uses the "echo" module (see src/test8.c). Refer to comments # in that file for the special behaviour of the Tcl $echo_module variable. # # TODO: # * How to test the sqlite3_index_constraint_usage.omit field? # * vtab1-5.* # #---------------------------------------------------------------------- # Test cases vtab1.1.* # |
︙ | ︙ | |||
483 484 485 486 487 488 489 | {DELETE FROM techo WHERE (oid % 3) = 0} \ {UPDATE techo set rowid = 100 WHERE rowid = 1} \ {INSERT INTO techo(a, b) VALUES('hello', 'world')} \ {DELETE FROM techo} \ ] { execsql $stmt execsql $stmt db2 | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 | {DELETE FROM techo WHERE (oid % 3) = 0} \ {UPDATE techo set rowid = 100 WHERE rowid = 1} \ {INSERT INTO techo(a, b) VALUES('hello', 'world')} \ {DELETE FROM techo} \ ] { execsql $stmt execsql $stmt db2 check_echo_table vtab1-6.8.[incr tn] } db2 close #---------------------------------------------------------------------- # Test cases vtab1-7 tests that the value returned by # sqlite3_last_insert_rowid() is set correctly when rows are inserted # into virtual tables. do_test vtab1.7-1 { execsql { CREATE TABLE real_abc(a PRIMARY KEY, b, c); CREATE VIRTUAL TABLE echo_abc USING echo(real_abc); } } {} do_test vtab1.7-2 { execsql { INSERT INTO echo_abc VALUES(1, 2, 3); SELECT last_insert_rowid(); } } {1} do_test vtab1.7-3 { execsql { INSERT INTO echo_abc(rowid) VALUES(31427); SELECT last_insert_rowid(); } } {31427} do_test vtab1.7-4 { execsql { INSERT INTO echo_abc SELECT a||'.v2', b, c FROM echo_abc; SELECT last_insert_rowid(); } } {31429} do_test vtab1.7-5 { execsql { SELECT rowid, a, b, c FROM echo_abc } } [list 1 1 2 3 \ 31427 {} {} {} \ 31428 1.v2 2 3 \ 31429 {} {} {} \ ] # Now test that DELETE and UPDATE operations do not modify the value. do_test vtab1.7-6 { execsql { UPDATE echo_abc SET c = 5 WHERE b = 2; SELECT last_insert_rowid(); } } {31429} do_test vtab1.7-7 { execsql { UPDATE echo_abc SET rowid = 5 WHERE rowid = 1; SELECT last_insert_rowid(); } } {31429} do_test vtab1.7-8 { execsql { DELETE FROM echo_abc WHERE b = 2; SELECT last_insert_rowid(); } } {31429} do_test vtab1.7-9 { execsql { SELECT rowid, a, b, c FROM echo_abc } } [list 31427 {} {} {} \ 31429 {} {} {} \ ] do_test vtab1.7-10 { execsql { DELETE FROM echo_abc WHERE b = 2; SELECT last_insert_rowid(); } } {31429} do_test vtab1.7-11 { execsql { SELECT rowid, a, b, c FROM real_abc } } [list 31427 {} {} {} \ 31429 {} {} {} \ ] do_test vtab1.7-12 { execsql { DELETE FROM echo_abc; SELECT last_insert_rowid(); } } {31429} do_test vtab1.7-13 { execsql { SELECT rowid, a, b, c FROM real_abc } } {} finish_test |