Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | New ROWIDs are numbered sequentially. (CVS 383) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
1686196a8aea326f616bc8205df99cd8 |
User & Date: | drh 2002-02-19 22:42:05.000 |
Context
2002-02-19
| ||
22:43 | Fix a comment. (CVS 384) (check-in: 953928537c user: drh tags: trunk) | |
22:42 | New ROWIDs are numbered sequentially. (CVS 383) (check-in: 1686196a8a user: drh tags: trunk) | |
15:00 | Optimize simple min() and max() queries. (CVS 382) (check-in: cc5abfe392 user: drh tags: trunk) | |
Changes
Changes to src/main.c.
︙ | ︙ | |||
10 11 12 13 14 15 16 | ** ************************************************************************* ** Main file for the SQLite library. The routines in this file ** implement the programmer interface to the library. Routines in ** other files are for internal use by SQLite and should not be ** accessed by users of the library. ** | | | 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | ** ************************************************************************* ** Main file for the SQLite library. The routines in this file ** implement the programmer interface to the library. Routines in ** other files are for internal use by SQLite and should not be ** accessed by users of the library. ** ** $Id: main.c,v 1.60 2002/02/19 22:42:05 drh Exp $ */ #include "sqliteInt.h" #include "os.h" /* ** This is the callback routine for the code that initializes the ** database. See sqliteInit() below for additional information. |
︙ | ︙ | |||
271 272 273 274 275 276 277 | db = sqliteMalloc( sizeof(sqlite) ); if( pzErrMsg ) *pzErrMsg = 0; if( db==0 ) goto no_mem_on_open; sqliteHashInit(&db->tblHash, SQLITE_HASH_STRING, 0); sqliteHashInit(&db->idxHash, SQLITE_HASH_STRING, 0); sqliteHashInit(&db->tblDrop, SQLITE_HASH_POINTER, 0); sqliteHashInit(&db->idxDrop, SQLITE_HASH_POINTER, 0); | < > | 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 | db = sqliteMalloc( sizeof(sqlite) ); if( pzErrMsg ) *pzErrMsg = 0; if( db==0 ) goto no_mem_on_open; sqliteHashInit(&db->tblHash, SQLITE_HASH_STRING, 0); sqliteHashInit(&db->idxHash, SQLITE_HASH_STRING, 0); sqliteHashInit(&db->tblDrop, SQLITE_HASH_POINTER, 0); sqliteHashInit(&db->idxDrop, SQLITE_HASH_POINTER, 0); db->onError = OE_Default; db->priorNewRowid = 0; /* Open the backend database driver */ rc = sqliteBtreeOpen(zFilename, mode, MAX_PAGES, &db->pBe); if( rc!=SQLITE_OK ){ switch( rc ){ default: { sqliteSetString(pzErrMsg, "unable to open database: ", zFilename, 0); |
︙ | ︙ |
Changes to src/select.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 SELECT 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 SELECT statements in SQLite. ** ** $Id: select.c,v 1.63 2002/02/19 22:42:05 drh Exp $ */ #include "sqliteInt.h" /* ** Allocate a new Select structure and return a pointer to that ** structure. */ |
︙ | ︙ | |||
779 780 781 782 783 784 785 | if( v==0 ) return 0; if( eDest==SRT_Callback ){ generateColumnNames(pParse, p->pSrc, p->pEList); } /* Begin generating code */ | | > | < < < > > > > > > > > | 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 | if( v==0 ) return 0; if( eDest==SRT_Callback ){ generateColumnNames(pParse, p->pSrc, p->pEList); } /* Begin generating code */ if( !pParse->schemaVerified && (pParse->db->flags & SQLITE_InTrans)==0 ){ sqliteVdbeAddOp(v, OP_VerifyCookie, pParse->db->schema_cookie, 0); pParse->schemaVerified = 1; } openOp = pTab->isTemp ? OP_OpenAux : OP_Open; base = pParse->nTab; sqliteVdbeAddOp(v, openOp, base, pTab->tnum); sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC); if( pIdx==0 ){ sqliteVdbeAddOp(v, seekOp, base, 0); }else{ sqliteVdbeAddOp(v, openOp, base+1, pIdx->tnum); sqliteVdbeChangeP3(v, -1, pIdx->zName, P3_STATIC); sqliteVdbeAddOp(v, seekOp, base+1, 0); sqliteVdbeAddOp(v, OP_IdxRecno, base+1, 0); sqliteVdbeAddOp(v, OP_Close, base+1, 0); sqliteVdbeAddOp(v, OP_MoveTo, base, 0); } eList.nExpr = 1; memset(&eListItem, 0, sizeof(eListItem)); eList.a = &eListItem; eList.a[0].pExpr = pExpr; cont = sqliteVdbeMakeLabel(v); selectInnerLoop(pParse, &eList, base, 1, 0, -1, eDest, iParm, cont, cont); sqliteVdbeResolveLabel(v, cont); sqliteVdbeAddOp(v, OP_Close, base, 0); return 1; } |
︙ | ︙ | |||
1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 | } } /* Check for the special case of a min() or max() function by itself ** in the result set. */ if( simpleMinMaxQuery(pParse, p, eDest, iParm) ){ goto select_end; } /* Begin generating code. */ v = sqliteGetVdbe(pParse); if( v==0 ) goto select_end; | > | 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 | } } /* Check for the special case of a min() or max() function by itself ** in the result set. */ if( simpleMinMaxQuery(pParse, p, eDest, iParm) ){ rc = 0; goto select_end; } /* Begin generating code. */ v = sqliteGetVdbe(pParse); if( v==0 ) goto select_end; |
︙ | ︙ |
Changes to src/sqliteInt.h.
1 2 3 4 5 6 7 8 9 10 11 12 13 | /* ** 2001 September 15 ** ** 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. ** ************************************************************************* ** Internal interface definitions for SQLite. ** | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | /* ** 2001 September 15 ** ** 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. ** ************************************************************************* ** Internal interface definitions for SQLite. ** ** @(#) $Id: sqliteInt.h,v 1.88 2002/02/19 22:42:05 drh Exp $ */ #include "sqlite.h" #include "hash.h" #include "vdbe.h" #include "parse.h" #include "btree.h" #include <stdio.h> |
︙ | ︙ | |||
178 179 180 181 182 183 184 | void *pBusyArg; /* 1st Argument to the busy callback */ int (*xBusyCallback)(void *,const char*,int); /* The busy callback */ Hash tblHash; /* All tables indexed by name */ Hash idxHash; /* All (named) indices indexed by name */ Hash tblDrop; /* Uncommitted DROP TABLEs */ Hash idxDrop; /* Uncommitted DROP INDEXs */ int lastRowid; /* ROWID of most recent insert */ | | | 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 | void *pBusyArg; /* 1st Argument to the busy callback */ int (*xBusyCallback)(void *,const char*,int); /* The busy callback */ Hash tblHash; /* All tables indexed by name */ Hash idxHash; /* All (named) indices indexed by name */ Hash tblDrop; /* Uncommitted DROP TABLEs */ Hash idxDrop; /* Uncommitted DROP INDEXs */ int lastRowid; /* ROWID of most recent insert */ int priorNewRowid; /* Last randomly generated ROWID */ int onError; /* Default conflict algorithm */ }; /* ** Possible values for the sqlite.flags. */ #define SQLITE_VdbeTrace 0x00000001 /* True to trace VDBE execution */ |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
26 27 28 29 30 31 32 | ** type to the other occurs as necessary. ** ** Most of the code in this file is taken up by the sqliteVdbeExec() ** function which does the work of interpreting a VDBE program. ** But other routines are also provided to help in building up ** a program instruction by instruction. ** | | | 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | ** type to the other occurs as necessary. ** ** Most of the code in this file is taken up by the sqliteVdbeExec() ** function which does the work of interpreting a VDBE program. ** But other routines are also provided to help in building up ** a program instruction by instruction. ** ** $Id: vdbe.c,v 1.120 2002/02/19 22:42:05 drh Exp $ */ #include "sqliteInt.h" #include <ctype.h> /* ** The following global variable is incremented every time a cursor ** moves, either by the OP_MoveTo or the OP_Next opcode. The test |
︙ | ︙ | |||
67 68 69 70 71 72 73 74 75 76 77 78 79 80 | */ struct Cursor { BtCursor *pCursor; /* The cursor structure of the backend */ int lastRecno; /* Last recno from a Next or NextIdx operation */ Bool recnoIsValid; /* True if lastRecno is valid */ Bool keyAsData; /* The OP_Column command works on key instead of data */ Bool atFirst; /* True if pointing to first entry */ Btree *pBt; /* Separate file holding temporary table */ }; typedef struct Cursor Cursor; /* ** A sorter builds a list of elements to be sorted. Each element of ** the list is an instance of the following structure. | > | 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 | */ struct Cursor { BtCursor *pCursor; /* The cursor structure of the backend */ int lastRecno; /* Last recno from a Next or NextIdx operation */ Bool recnoIsValid; /* True if lastRecno is valid */ Bool keyAsData; /* The OP_Column command works on key instead of data */ Bool atFirst; /* True if pointing to first entry */ Bool useRandomRowid; /* Generate new record numbers semi-randomly */ Btree *pBt; /* Separate file holding temporary table */ }; typedef struct Cursor Cursor; /* ** A sorter builds a list of elements to be sorted. Each element of ** the list is an instance of the following structure. |
︙ | ︙ | |||
2909 2910 2911 2912 2913 2914 2915 | case OP_NewRecno: { int i = pOp->p1; int v = 0; Cursor *pC; if( VERIFY( i<0 || i>=p->nCursor || ) (pC = &p->aCsr[i])->pCursor==0 ){ v = 0; }else{ | > > > > > > | > | > | | | > > > > > > > > > > > > > > > | > | | | > | | | | | | | | | | | | > | 2910 2911 2912 2913 2914 2915 2916 2917 2918 2919 2920 2921 2922 2923 2924 2925 2926 2927 2928 2929 2930 2931 2932 2933 2934 2935 2936 2937 2938 2939 2940 2941 2942 2943 2944 2945 2946 2947 2948 2949 2950 2951 2952 2953 2954 2955 2956 2957 2958 2959 2960 2961 2962 2963 2964 2965 2966 2967 2968 2969 2970 2971 2972 2973 2974 2975 2976 2977 2978 2979 2980 2981 2982 2983 2984 2985 2986 2987 2988 2989 2990 | case OP_NewRecno: { int i = pOp->p1; int v = 0; Cursor *pC; if( VERIFY( i<0 || i>=p->nCursor || ) (pC = &p->aCsr[i])->pCursor==0 ){ v = 0; }else{ /* The next rowid or record number (different terms for the same ** thing) is obtained in a two-step algorithm. ** ** First we attempt to find the largest existing rowid and add one ** to that. But if the largest existing rowid is already the maximum ** positive integer, we have to fall through to the second ** probabilistic algorithm ** ** The second algorithm is to select a rowid at random and see if ** it already exists in the table. If it does not exist, we have ** succeeded. If the random rowid does exist, we select a new one ** and try again, up to 1000 times. ** ** For a table with less than 2 billion entries, the probability ** of not finding a unused rowid is about 1.0e-300. This is a ** non-zero probability, but it is still vanishingly small and should ** never cause a problem. You are much, much more likely to have a ** hardware failure than for this algorithm to fail. ** ** The analysis in the previous paragraph assumes that you have a good ** source of random numbers. Is a library function like lrand48() ** good enough? Maybe. Maybe not. It's hard to know whether there ** might be subtle bugs is some implementations of lrand48() that ** could cause problems. To avoid uncertainty, SQLite uses its own ** random number generator based on the RC4 algorithm. ** ** To promote locality of reference for repetitive inserts, the ** first few attempts at chosing a random rowid pick values just a little ** larger than the previous rowid. This has been shown experimentally ** to double the speed of the COPY operation. */ int res, rx, cnt, x; cnt = 0; if( !pC->useRandomRowid ){ rx = sqliteBtreeLast(pC->pCursor, &res); if( res ){ v = 1; }else{ sqliteBtreeKey(pC->pCursor, 0, sizeof(v), (void*)&v); v = keyToInt(v); if( v==0x7fffffff ){ pC->useRandomRowid = 1; }else{ v++; } } } if( pC->useRandomRowid ){ v = db->priorNewRowid; cnt = 0; do{ if( v==0 || cnt>2 ){ v = sqliteRandomInteger(); if( cnt<5 ) v &= 0xffffff; }else{ v += sqliteRandomByte() + 1; } if( v==0 ) continue; x = intToKey(v); rx = sqliteBtreeMoveto(pC->pCursor, &x, sizeof(int), &res); cnt++; }while( cnt<1000 && rx==SQLITE_OK && res==0 ); db->priorNewRowid = v; if( rx==SQLITE_OK && res==0 ){ rc = SQLITE_FULL; goto abort_due_to_error; } } } VERIFY( NeedStack(p, p->tos+1); ) p->tos++; aStack[p->tos].i = v; aStack[p->tos].flags = STK_Int; break; |
︙ | ︙ |
Changes to test/intpkey.test.
︙ | ︙ | |||
9 10 11 12 13 14 15 | # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests for the special processing associated # with INTEGER PRIMARY KEY columns. # | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests for the special processing associated # with INTEGER PRIMARY KEY columns. # # $Id: intpkey.test,v 1.8 2002/02/19 22:42:06 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a table with a primary key and a datatype other than # integer # |
︙ | ︙ | |||
107 108 109 110 111 112 113 | } } {hello one second} # Try to change the ROWID for the new entry. # do_test intpkey-1.11 { execsql { | | | | | | 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 | } } {hello one second} # Try to change the ROWID for the new entry. # do_test intpkey-1.11 { execsql { UPDATE t1 SET a=4 WHERE b='one'; SELECT * FROM t1; } } {4 one two 5 hello world 6 second entry} # Make sure SELECT statements are able to use the primary key column # as an index. # do_test intpkey-1.12 { execsql { SELECT * FROM t1 WHERE a==4; } } {4 one two} # Try to insert a non-integer value into the primary key field. This # should result in a data type mismatch. # do_test intpkey-1.13 { set r [catch {execsql { INSERT INTO t1 VALUES('x','y','z'); |
︙ | ︙ | |||
144 145 146 147 148 149 150 | set r [catch {execsql { INSERT INTO t1 VALUES(-3,'y','z'); }} msg] lappend r $msg } {0 {}} do_test intpkey-1.16 { execsql {SELECT * FROM t1} | | | 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 | set r [catch {execsql { INSERT INTO t1 VALUES(-3,'y','z'); }} msg] lappend r $msg } {0 {}} do_test intpkey-1.16 { execsql {SELECT * FROM t1} } {-3 y z 4 one two 5 hello world 6 second entry} #### INDICES # Check to make sure indices work correctly with integer primary keys # do_test intpkey-2.1 { execsql { CREATE INDEX i1 ON t1(b); |
︙ | ︙ | |||
186 187 188 189 190 191 192 | SELECT * FROM t1 WHERE b=='y'; } } {8 y z} do_test intpkey-2.3 { execsql { SELECT rowid, * FROM t1; } | | | | | | | | | 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 | SELECT * FROM t1 WHERE b=='y'; } } {8 y z} do_test intpkey-2.3 { execsql { SELECT rowid, * FROM t1; } } {4 4 one two 5 5 hello world 6 6 second entry 8 8 y z} do_test intpkey-2.4 { execsql { SELECT rowid, * FROM t1 WHERE b<'second' } } {5 5 hello world 4 4 one two} do_test intpkey-2.4.1 { execsql { SELECT rowid, * FROM t1 WHERE 'second'>b } } {5 5 hello world 4 4 one two} do_test intpkey-2.4.2 { execsql { SELECT rowid, * FROM t1 WHERE 8>rowid AND 'second'>b } } {4 4 one two 5 5 hello world} do_test intpkey-2.4.3 { execsql { SELECT rowid, * FROM t1 WHERE 8>rowid AND 'second'>b AND 0<rowid } } {4 4 one two 5 5 hello world} do_test intpkey-2.5 { execsql { SELECT rowid, * FROM t1 WHERE b>'a' } } {5 5 hello world 4 4 one two 6 6 second entry 8 8 y z} do_test intpkey-2.6 { execsql { DELETE FROM t1 WHERE rowid=4; SELECT * FROM t1 WHERE b>'a'; } } {5 hello world 6 second entry 8 y z} do_test intpkey-2.7 { execsql { UPDATE t1 SET a=-4 WHERE rowid=8; SELECT * FROM t1 WHERE b>'a'; |
︙ | ︙ |
Added test/minmax.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 80 81 82 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 | # 2001 September 15 # # 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 SELECT statements that contain # aggregate min() and max() functions and which are handled as # as a special case. # # $Id: minmax.test,v 1.1 2002/02/19 22:42:06 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl do_test minmax-1.0 { execsql { BEGIN; CREATE TABLE t1(x, y); INSERT INTO t1 VALUES(1,1); INSERT INTO t1 VALUES(2,2); INSERT INTO t1 VALUES(3,2); INSERT INTO t1 VALUES(4,3); INSERT INTO t1 VALUES(5,3); INSERT INTO t1 VALUES(6,3); INSERT INTO t1 VALUES(7,3); INSERT INTO t1 VALUES(8,4); INSERT INTO t1 VALUES(9,4); INSERT INTO t1 VALUES(10,4); INSERT INTO t1 VALUES(11,4); INSERT INTO t1 VALUES(12,4); INSERT INTO t1 VALUES(13,4); INSERT INTO t1 VALUES(14,4); INSERT INTO t1 VALUES(15,4); INSERT INTO t1 VALUES(16,5); INSERT INTO t1 VALUES(17,5); INSERT INTO t1 VALUES(18,5); INSERT INTO t1 VALUES(19,5); INSERT INTO t1 VALUES(20,5); COMMIT; SELECT DISTINCT y FROM t1 ORDER BY y; } } {1 2 3 4 5} do_test minmax-1.1 { set sqlite_search_count 0 execsql {SELECT min(x) FROM t1} } {1} do_test minmax-1.2 { set sqlite_search_count } {19} do_test minmax-1.3 { set sqlite_search_count 0 execsql {SELECT max(x) FROM t1} } {20} do_test minmax-1.4 { set sqlite_search_count } {19} do_test minmax-1.5 { execsql {CREATE INDEX t1i1 ON t1(x)} set sqlite_search_count 0 execsql {SELECT min(x) FROM t1} } {1} do_test minmax-1.6 { set sqlite_search_count } {1} do_test minmax-1.7 { set sqlite_search_count 0 execsql {SELECT max(x) FROM t1} } {20} do_test minmax-1.8 { set sqlite_search_count } {1} do_test minmax-1.9 { set sqlite_search_count 0 execsql {SELECT max(y) FROM t1} } {5} do_test minmax-1.10 { set sqlite_search_count } {19} do_test minmax-2.0 { execsql { CREATE TABLE t2(a INTEGER PRIMARY KEY, b); INSERT INTO t2 SELECT * FROM t1; } set sqlite_search_count 0 execsql {SELECT min(a) FROM t2} } {1} do_test minmax-2.1 { set sqlite_search_count } {0} do_test minmax-2.2 { set sqlite_search_count 0 execsql {SELECT max(a) FROM t2} } {20} do_test minmax-2.3 { set sqlite_search_count } {0} do_test minmax-3.0 { execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)} set sqlite_search_count 0 execsql {SELECT max(a) FROM t2} } {21} do_test minmax-3.1 { set sqlite_search_count } {0} do_test minmax-3.2 { execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)} set sqlite_search_count 0 execsql { SELECT b FROM t2 WHERE a=(SELECT max(a) FROM t2) } } {999} do_test minmax-3.3 { set sqlite_search_count } {0} finish_test |
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.8 2002/02/19 22:42:06 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Basic ROWID functionality tests. # do_test rowid-1.1 { |
︙ | ︙ | |||
151 152 153 154 155 156 157 | # Columns defined in the CREATE statement override the buildin ROWID # column names. # do_test rowid-3.1 { execsql { CREATE TABLE t2(rowid int, x int, y int); | | | | | | 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 | # Columns defined in the CREATE statement override the buildin ROWID # column names. # do_test rowid-3.1 { execsql { CREATE TABLE t2(rowid int, x int, y int); INSERT INTO t2 VALUES(0,2,3); INSERT INTO t2 VALUES(4,5,6); INSERT INTO t2 VALUES(7,8,9); SELECT * FROM t2 ORDER BY x; } } {0 2 3 4 5 6 7 8 9} do_test rowid-3.2 { execsql {SELECT * FROM t2 ORDER BY rowid} } {0 2 3 4 5 6 7 8 9} do_test rowid-3.3 { execsql {SELECT rowid, x, y FROM t2 ORDER BY rowid} } {0 2 3 4 5 6 7 8 9} do_test rowid-3.4 { set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}] foreach {a b c d e f} $r1 {} set r2 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY x DESC}] foreach {u v w x y z} $r2 {} expr {$u==$e && $w==$c && $y==$a} } {1} |
︙ | ︙ | |||
267 268 269 270 271 272 273 | # do_test rowid-6.1 { execsql { SELECT x FROM t1 } } {1 2 3 4 5 6 7 8} do_test rowid-6.2 { | | | > > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 | # do_test rowid-6.1 { execsql { SELECT x FROM t1 } } {1 2 3 4 5 6 7 8} do_test rowid-6.2 { for {set ::norow 1} {1} {incr ::norow} { if {[execsql "SELECT x FROM t1 WHERE rowid=$::norow"]==""} break } execsql [subst { DELETE FROM t1 WHERE rowid=$::norow }] } {} do_test rowid-6.3 { execsql { SELECT x FROM t1 } } {1 2 3 4 5 6 7 8} # Beginning with version 2.3.4, SQLite computes rowids of new rows by # finding the maximum current rowid and adding one. It falls back to # the old random algorithm if the maximum rowid is the largest integer. # The following tests are for this new behavior. # do_test rowid-7.0 { execsql { DELETE FROM t1; DROP TABLE t2; DROP INDEX idxt1; INSERT INTO t1 VALUES(1,2); SELECT rowid, * FROM t1; } } {1 1 2} do_test rowid-7.1 { execsql { INSERT INTO t1 VALUES(99,100); SELECT rowid,* FROM t1 } } {1 1 2 2 99 100} do_test rowid-7.2 { execsql { CREATE TABLE t2(a INTEGER PRIMARY KEY, b); INSERT INTO t2(b) VALUES(55); SELECT * FROM t2; } } {1 55} do_test rowid-7.3 { execsql { INSERT INTO t2(b) VALUES(66); SELECT * FROM t2; } } {1 55 2 66} do_test rowid-7.4 { execsql { INSERT INTO t2(a,b) VALUES(1000000,77); INSERT INTO t2(b) VALUES(88); SELECT * FROM t2; } } {1 55 2 66 1000000 77 1000001 88} do_test rowid-7.5 { execsql { INSERT INTO t2(a,b) VALUES(2147483647,99); INSERT INTO t2(b) VALUES(11); SELECT b FROM t2 ORDER BY b; } } {11 55 66 77 88 99} do_test rowid-7.6 { execsql { SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647); } } {11} do_test rowid-7.7 { execsql { INSERT INTO t2(b) VALUES(22); INSERT INTO t2(b) VALUES(33); INSERT INTO t2(b) VALUES(44); INSERT INTO t2(b) VALUES(55); SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647) ORDER BY b; } } {11 22 33 44 55} do_test rowid-7.8 { execsql { DELETE FROM t2 WHERE a!=2; INSERT INTO t2(b) VALUES(111); SELECT * FROM t2; } } {2 66 3 111} finish_test |
Changes to www/c_interface.tcl.
1 2 3 | # # Run this Tcl script to generate the sqlite.html file. # | | | 1 2 3 4 5 6 7 8 9 10 11 | # # Run this Tcl script to generate the sqlite.html file. # set rcsid {$Id: c_interface.tcl,v 1.24 2002/02/19 22:42:06 drh Exp $} puts {<html> <head> <title>The C language interface to the SQLite library</title> </head> <body bgcolor=white> <h1 align=center> |
︙ | ︙ | |||
374 375 376 377 378 379 380 | header file that comes in the source tree.</p> <h2>The ROWID of the most recent insert</h2> <p>Every row of an SQLite table has a unique integer key. If the table has a column labeled INTEGER PRIMARY KEY, then that column servers as the key. If there is no INTEGER PRIMARY KEY column then | | | 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 | header file that comes in the source tree.</p> <h2>The ROWID of the most recent insert</h2> <p>Every row of an SQLite table has a unique integer key. If the table has a column labeled INTEGER PRIMARY KEY, then that column servers as the key. If there is no INTEGER PRIMARY KEY column then the key is a unique integer. The key for a row can be accessed in a SELECT statement or used in a WHERE or ORDER BY clause using any of the names "ROWID", "OID", or "_ROWID_".</p> <p>When you do an insert into a table that does not have an INTEGER PRIMARY KEY column, or if the table does have an INTEGER PRIMARY KEY but the value for that column is not specified in the VALUES clause of the insert, then the key is automatically generated. You can find the value of the key |
︙ | ︙ |
Changes to www/changes.tcl.
︙ | ︙ | |||
16 17 18 19 20 21 22 23 24 25 26 27 28 29 | puts "<DT><B>$date</B></DT>" puts "<DD><P><UL>$desc</UL></P></DD>" } chng {2002 Feb * (2.3.4)} { <li>Change the name of the sanity_check PRAGMA to <b>integrity_check</b> and make it available in all compiles.</li> } chng {2002 Feb 18 (2.3.3)} { <li>Allow identifiers to be quoted in square brackets, for compatibility with MS-Access.</li> <li>Added support for sub-queries in the FROM clause of a SELECT.</li> <li>More efficient implementation of sqliteFileExists() under Windows. | > > > | 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | puts "<DT><B>$date</B></DT>" puts "<DD><P><UL>$desc</UL></P></DD>" } chng {2002 Feb * (2.3.4)} { <li>Change the name of the sanity_check PRAGMA to <b>integrity_check</b> and make it available in all compiles.</li> <li>SELECT min() or max() of an indexed column with no WHERE or GROUP BY clause is handled as a special case which avoids a complete table scan.</li> <li>Automatically generated ROWIDs are now sequential.</li> } chng {2002 Feb 18 (2.3.3)} { <li>Allow identifiers to be quoted in square brackets, for compatibility with MS-Access.</li> <li>Added support for sub-queries in the FROM clause of a SELECT.</li> <li>More efficient implementation of sqliteFileExists() under Windows. |
︙ | ︙ |
Changes to www/faq.tcl.
1 2 3 | # # Run this script to generated a faq.html output file # | | | 1 2 3 4 5 6 7 8 9 10 11 | # # Run this script to generated a faq.html output file # set rcsid {$Id: faq.tcl,v 1.8 2002/02/19 22:42:06 drh Exp $} puts {<html> <head> <title>SQLite Frequently Asked Questions</title> </head> <body bgcolor="white"> <h1 align="center">Frequently Asked Questions</h1> |
︙ | ︙ | |||
23 24 25 26 27 28 29 | ############# # Enter questions and answers here. faq { How do I create an AUTOINCREMENT field. } { | | > | | > > > > > | | > | > < > < < < < > < < | < < < < < < < < < < | < < < < < | < < | | | | | | < < < | | < < < < | | 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 | ############# # Enter questions and answers here. faq { How do I create an AUTOINCREMENT field. } { <p>Short answer: A column declared INTEGER PRIMARY KEY will autoincrement.</p> <p>Here is the long answer: Beginning with version SQLite 2.3.4, If you declare a column of a table to be INTEGER PRIMARY KEY, then whenever you insert a NULL into that column of the table, the NULL is automatically converted into an integer which is one greater than the largest value of that column over all other rows in the table, or 1 if the table is empty. For example, suppose you have a table like this: <blockquote><pre> CREATE TABLE t1( a INTEGER PRIMARY KEY, b INTEGER ); </pre></blockquote> <p>With this table, the statement</p> <blockquote><pre> INSERT INTO t1 VALUES(NULL,123); </pre></blockquote> <p>is logically equivalent to saying:</p> <blockquote><pre> INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123); </pre></blockquote> <p>For SQLite version 2.2.0 through 2.3.3, if you insert a NULL into an INTEGER PRIMARY KEY column, the NULL will be changed to a unique integer, but it will a semi-random integer. Unique keys generated this way will not be sequential. For SQLite version 2.3.4 and beyond, the unique keys will be sequential until the largest key reaches a value of 2147483647. That is the largest 32-bit signed integer and cannot be incremented, so subsequent insert attempts will revert to the semi-random key generation algorithm of SQLite version 2.3.3 and earlier.</p> <p>Beginning with version 2.2.3, there is a new API function named <b>sqlite_last_insert_rowid()</b> which will return the integer key for the most recent insert operation. See the API documentation for details.</p> } |
︙ | ︙ |
Changes to www/lang.tcl.
1 2 3 | # # Run this Tcl script to generate the sqlite.html file. # | | | 1 2 3 4 5 6 7 8 9 10 11 | # # Run this Tcl script to generate the sqlite.html file. # set rcsid {$Id: lang.tcl,v 1.26 2002/02/19 22:42:06 drh Exp $} puts {<html> <head> <title>Query Language Understood By SQLite</title> </head> <body bgcolor=white> <h1 align=center> |
︙ | ︙ | |||
288 289 290 291 292 293 294 | on the primary key. However, if primary key is on a single column that has datatype INTEGER, then that column is used internally as the actual key of the B-Tree for the table. This means that the column may only hold unique integer values. (Except for this one case, SQLite ignores the datatype specification of columns and allows any kind of data to be put in a column regardless of its declared datatype.) If a table does not have an INTEGER PRIMARY KEY column, | | | 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 | on the primary key. However, if primary key is on a single column that has datatype INTEGER, then that column is used internally as the actual key of the B-Tree for the table. This means that the column may only hold unique integer values. (Except for this one case, SQLite ignores the datatype specification of columns and allows any kind of data to be put in a column regardless of its declared datatype.) If a table does not have an INTEGER PRIMARY KEY column, then the B-Tree key will be a automatically generated integer. The B-Tree key for a row can always be accessed using one of the special names "<b>ROWID</b>", "<b>OID</b>", or "<b>_ROWID_</b>". This is true regardless of whether or not there is an INTEGER PRIMARY KEY.</p> <p>If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE" and "TABLE" then the table that is created is only visible to the |
︙ | ︙ |