Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add the ability to INSERT or UPDATE using the "rowid" column even on tables that lack an integer primary key. (CVS 991) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
903adbe802ca0e987b867e5ad33aae02 |
User & Date: | drh 2003-06-01 01:10:33.000 |
Context
2003-06-02
| ||
06:15 | Additions and corrections to vdbe documentation comments. (CVS 992) (check-in: b648987de3 user: jplyon tags: trunk) | |
2003-06-01
| ||
01:10 | Add the ability to INSERT or UPDATE using the "rowid" column even on tables that lack an integer primary key. (CVS 991) (check-in: 903adbe802 user: drh tags: trunk) | |
2003-05-31
| ||
16:21 | Tighter binding of views, triggers, and indices to their respective databases. Ticket #323. Much more testing needs to be done to the sqliteFix...() routines in attach.c. (CVS 990) (check-in: 7202d4f1a8 user: drh tags: trunk) | |
Changes
Changes to src/btree.h.
︙ | ︙ | |||
9 10 11 12 13 14 15 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** This header file defines the interface that the sqlite B-Tree file ** subsystem. See comments in the source code for a detailed description ** of what each interface routine does. ** | | | 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. ** ************************************************************************* ** This header file defines the interface that the sqlite B-Tree file ** subsystem. See comments in the source code for a detailed description ** of what each interface routine does. ** ** @(#) $Id: btree.h,v 1.35 2003/06/01 01:10:33 drh Exp $ */ #ifndef _BTREE_H_ #define _BTREE_H_ /* ** Forward declarations of structure */ |
︙ | ︙ | |||
91 92 93 94 95 96 97 | /* ** The number of 4-byte "meta" values contained on the first page of each ** database file. */ #define SQLITE_N_BTREE_META 10 int sqliteBtreeOpen(const char *zFilename, int mode, int nPg, Btree **ppBtree); | | | 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 | /* ** The number of 4-byte "meta" values contained on the first page of each ** database file. */ #define SQLITE_N_BTREE_META 10 int sqliteBtreeOpen(const char *zFilename, int mode, int nPg, Btree **ppBtree); int sqliteRbtreeOpen(const char *zFilename, int mode, int nPg, Btree **ppBtree); #define btOps(pBt) (*((BtOps **)(pBt))) #define btCOps(pCur) (*((BtCursorOps **)(pCur))) #define sqliteBtreeClose(pBt) (btOps(pBt)->Close(pBt)) #define sqliteBtreeSetCacheSize(pBt, sz) (btOps(pBt)->SetCacheSize(pBt, sz)) #define sqliteBtreeSetSafetyLevel(pBt, sl) (btOps(pBt)->SetSafetyLevel(pBt, sl)) |
︙ | ︙ |
Changes to src/btree_rb.c.
1 2 3 4 5 6 7 8 9 10 11 | /* ** 2003 Feb 4 ** ** 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. ** ************************************************************************* | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | /* ** 2003 Feb 4 ** ** 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. ** ************************************************************************* ** $Id: btree_rb.c,v 1.11 2003/06/01 01:10:33 drh Exp $ ** ** This file implements an in-core database using Red-Black balanced ** binary trees. ** ** It was contributed to SQLite by anonymous on 2003-Feb-04 23:24:49 UTC. */ #include "btree.h" |
︙ | ︙ | |||
576 577 578 579 580 581 582 | } } int sqliteRbtreeOpen( const char *zFilename, int mode, int nPg, | | > | 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 | } } int sqliteRbtreeOpen( const char *zFilename, int mode, int nPg, Btree **ppBtree ){ Rbtree **ppRbtree = (Rbtree**)ppBtree; *ppRbtree = (Rbtree *)sqliteMalloc(sizeof(Rbtree)); sqliteHashInit(&(*ppRbtree)->tblHash, SQLITE_HASH_INT, 0); /* Create a binary tree for the SQLITE_MASTER table at location 2 */ btreeCreateTable(*ppRbtree, 2); (*ppRbtree)->next_idx = 3; (*ppRbtree)->pOps = &sqliteRbtreeOps; |
︙ | ︙ |
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.86 2003/06/01 01:10:33 drh Exp $ */ #include "sqliteInt.h" /* ** This routine is call to handle SQL of the following forms: ** ** insert into TABLE (IDLIST) values(EXPRLIST) |
︙ | ︙ | |||
289 290 291 292 293 294 295 | if( j==pTab->iPKey ){ keyColumn = i; } break; } } if( j>=pTab->nCol ){ | > > > | | | | > | 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 | if( j==pTab->iPKey ){ keyColumn = i; } break; } } if( j>=pTab->nCol ){ if( sqliteIsRowid(pColumn->a[i].zName) ){ keyColumn = i; }else{ sqliteErrorMsg(pParse, "table %S has no column named %s", pTabList, 0, pColumn->a[i].zName); pParse->nErr++; goto insert_cleanup; } } } } /* If there is no IDLIST term but the table has an integer primary ** key, the set the keyColumn variable to the primary key column index ** in the original table definition. |
︙ | ︙ | |||
478 479 480 481 482 483 484 | sqliteExprCode(pParse, pList->a[j].pExpr); } } /* Generate code to check constraints and generate index keys and ** do the insertion. */ | | > | 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 | sqliteExprCode(pParse, pList->a[j].pExpr); } } /* Generate code to check constraints and generate index keys and ** do the insertion. */ sqliteGenerateConstraintChecks(pParse, pTab, base, 0, keyColumn>=0, 0, onError, endOfLoop); sqliteCompleteInsertion(pParse, pTab, base, 0,0,0, after_triggers ? newIdx : -1); } /* Update the count of rows that are inserted */ if( (db->flags & SQLITE_CountRows)!=0 ){ |
︙ | ︙ | |||
656 657 658 659 660 661 662 | assert( pTab->pSelect==0 ); /* This table is not a VIEW */ nCol = pTab->nCol; /* Test all NOT NULL constraints. */ for(i=0; i<nCol; i++){ if( i==pTab->iPKey ){ | < | 661 662 663 664 665 666 667 668 669 670 671 672 673 674 | assert( pTab->pSelect==0 ); /* This table is not a VIEW */ nCol = pTab->nCol; /* Test all NOT NULL constraints. */ for(i=0; i<nCol; i++){ if( i==pTab->iPKey ){ continue; } onError = pTab->aCol[i].notNull; if( onError==OE_None ) continue; if( overrideError!=OE_Default ){ onError = overrideError; }else if( pParse->db->onError!=OE_Default ){ |
︙ | ︙ | |||
710 711 712 713 714 715 716 | /* If we have an INTEGER PRIMARY KEY, make sure the primary key ** of the new record does not previously exist. Except, if this ** is an UPDATE and the primary key is not changing, that is OK. ** Also, if the conflict resolution policy is REPLACE, then we ** can skip this test. */ | | > > > > > < | 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 | /* If we have an INTEGER PRIMARY KEY, make sure the primary key ** of the new record does not previously exist. Except, if this ** is an UPDATE and the primary key is not changing, that is OK. ** Also, if the conflict resolution policy is REPLACE, then we ** can skip this test. */ if( /* (recnoChng || !isUpdate) && pTab->iPKey>=0 */ recnoChng ){ onError = pTab->keyConf; if( overrideError!=OE_Default ){ onError = overrideError; }else if( pParse->db->onError!=OE_Default ){ onError = pParse->db->onError; }else if( onError==OE_Default ){ onError = OE_Abort; } if( onError!=OE_Replace ){ if( isUpdate ){ sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1); sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1); jumpInst1 = sqliteVdbeAddOp(v, OP_Eq, 0, 0); } sqliteVdbeAddOp(v, OP_Dup, nCol, 1); jumpInst2 = sqliteVdbeAddOp(v, OP_NotExists, base, 0); switch( onError ){ default: { onError = OE_Abort; /* Fall thru into the next case */ } case OE_Rollback: case OE_Abort: case OE_Fail: { sqliteVdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, onError); sqliteVdbeChangeP3(v, -1, "PRIMARY KEY must be unique", P3_STATIC); break; } case OE_Ignore: { sqliteVdbeAddOp(v, OP_Pop, nCol+1+hasTwoRecnos, 0); sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest); break; } } contAddr = sqliteVdbeCurrentAddr(v); sqliteVdbeChangeP2(v, jumpInst2, contAddr); if( isUpdate ){ sqliteVdbeChangeP2(v, jumpInst1, contAddr); sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1); sqliteVdbeAddOp(v, OP_MoveTo, base, 0); |
︙ | ︙ |
Changes to src/update.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 UPDATE statements. ** | | | 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.67 2003/06/01 01:10:33 drh Exp $ */ #include "sqliteInt.h" /* ** Process an UPDATE statement. ** ** UPDATE OR IGNORE table_wxyz SET a=b, c=d WHERE e<5 AND f NOT NULL; |
︙ | ︙ | |||
124 125 126 127 128 129 130 | pRecnoExpr = pChanges->a[i].pExpr; } aXRef[j] = i; break; } } if( j>=pTab->nCol ){ | > > > > | | > | 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 | pRecnoExpr = pChanges->a[i].pExpr; } aXRef[j] = i; break; } } if( j>=pTab->nCol ){ if( sqliteIsRowid(pChanges->a[i].zName) ){ chngRecno = 1; pRecnoExpr = pChanges->a[i].pExpr; }else{ sqliteErrorMsg(pParse, "no such column: %s", pChanges->a[i].zName); goto update_cleanup; } } #ifndef SQLITE_OMIT_AUTHORIZATION { int rc; rc = sqliteAuthCheck(pParse, SQLITE_UPDATE, pTab->zName, pTab->aCol[j].zName, db->aDb[pTab->iDb].zName); if( rc==SQLITE_DENY ){ |
︙ | ︙ |
Changes to test/rowid.test.
︙ | ︙ | |||
8 9 10 11 12 13 14 | # 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 magic ROWID column that is # found on all tables. # | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | # 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 magic ROWID column that is # found on all tables. # # $Id: rowid.test,v 1.11 2003/06/01 01:10:33 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Basic ROWID functionality tests. # do_test rowid-1.1 { |
︙ | ︙ | |||
83 84 85 86 87 88 89 | do_test rowid-1.9 { global x2rowid set v [execsql {SELECT x, _rowid_ FROM t1 order by x}] set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)] expr {$v==$v2} } {1} | | > | < | > > > | < | > > > | < < > > > > | < < > > > > | < | > > > | < | > > | | | 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 | do_test rowid-1.9 { global x2rowid set v [execsql {SELECT x, _rowid_ FROM t1 order by x}] set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)] expr {$v==$v2} } {1} # We can insert or update the ROWID column. # do_test rowid-2.1 { catchsql { INSERT INTO t1(rowid,x,y) VALUES(1234,5,6); SELECT rowid, * FROM t1; } } {0 {1 1 2 2 3 4 1234 5 6}} do_test rowid-2.2 { catchsql { UPDATE t1 SET rowid=12345 WHERE x==1; SELECT rowid, * FROM t1 } } {0 {2 3 4 1234 5 6 12345 1 2}} do_test rowid-2.3 { catchsql { INSERT INTO t1(y,x,oid) VALUES(8,7,1235); SELECT rowid, * FROM t1 WHERE rowid>1000; } } {0 {1234 5 6 1235 7 8 12345 1 2}} do_test rowid-2.4 { catchsql { UPDATE t1 SET oid=12346 WHERE x==1; SELECT rowid, * FROM t1; } } {0 {2 3 4 1234 5 6 1235 7 8 12346 1 2}} do_test rowid-2.5 { catchsql { INSERT INTO t1(x,_rowid_,y) VALUES(9,1236,10); SELECT rowid, * FROM t1 WHERE rowid>1000; } } {0 {1234 5 6 1235 7 8 1236 9 10 12346 1 2}} do_test rowid-2.6 { catchsql { UPDATE t1 SET _rowid_=12347 WHERE x==1; SELECT rowid, * FROM t1 WHERE rowid>1000; } } {0 {1234 5 6 1235 7 8 1236 9 10 12347 1 2}} # But we can use ROWID in the WHERE clause of an UPDATE that does not # change the ROWID. # do_test rowid-2.7 { global x2rowid set sql "UPDATE t1 SET x=2 WHERE OID==$x2rowid(3)" execsql $sql execsql {SELECT x FROM t1 ORDER BY x} } {1 2 5 7 9} do_test rowid-2.8 { global x2rowid set sql "UPDATE t1 SET x=3 WHERE _rowid_==$x2rowid(3)" execsql $sql execsql {SELECT x FROM t1 ORDER BY x} } {1 3 5 7 9} # We cannot index by ROWID # do_test rowid-2.9 { set v [catch {execsql {CREATE INDEX idxt1 ON t1(rowid)}} msg] lappend v $msg } {1 {table t1 has no column named rowid}} |
︙ | ︙ |