Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Only preserve rowid values for VACUUM INTO. Keep the legacy behavior of renumbering rowids for ordinary VACUUM. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
13a0ea6466b051ea5281865ed5285b8b |
User & Date: | drh 2019-04-04 14:00:23.452 |
Context
2019-04-04
| ||
14:36 | Fix an incorrect comment on a test case for rowid renumbering in VACUUM. (check-in: 0d293fb43f user: drh tags: trunk) | |
14:00 | Only preserve rowid values for VACUUM INTO. Keep the legacy behavior of renumbering rowids for ordinary VACUUM. (check-in: 13a0ea6466 user: drh tags: trunk) | |
2019-04-03
| ||
17:48 | Add a defense-in-depth NEVER() test to the WAL cleanup code. (check-in: 8d3af2010f user: drh tags: trunk) | |
Changes
Changes to src/insert.c.
︙ | ︙ | |||
2348 2349 2350 2351 2352 2353 2354 | addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid); sqlite3VdbeVerifyAbortable(v, onError); addr2 = sqlite3VdbeAddOp3(v, OP_NotExists, iDest, 0, regRowid); VdbeCoverage(v); sqlite3RowidConstraint(pParse, onError, pDest); sqlite3VdbeJumpHere(v, addr2); autoIncStep(pParse, regAutoinc, regRowid); | | | 2348 2349 2350 2351 2352 2353 2354 2355 2356 2357 2358 2359 2360 2361 2362 | addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid); sqlite3VdbeVerifyAbortable(v, onError); addr2 = sqlite3VdbeAddOp3(v, OP_NotExists, iDest, 0, regRowid); VdbeCoverage(v); sqlite3RowidConstraint(pParse, onError, pDest); sqlite3VdbeJumpHere(v, addr2); autoIncStep(pParse, regAutoinc, regRowid); }else if( pDest->pIndex==0 && !(db->mDbFlags & DBFLAG_VacuumInto) ){ addr1 = sqlite3VdbeAddOp2(v, OP_NewRowid, iDest, regRowid); }else{ addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid); assert( (pDest->tabFlags & TF_Autoincrement)==0 ); } sqlite3VdbeAddOp3(v, OP_RowData, iSrc, regData, 1); if( db->mDbFlags & DBFLAG_Vacuum ){ |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1561 1562 1563 1564 1565 1566 1567 | /* ** Allowed values for sqlite3.mDbFlags */ #define DBFLAG_SchemaChange 0x0001 /* Uncommitted Hash table changes */ #define DBFLAG_PreferBuiltin 0x0002 /* Preference to built-in funcs */ #define DBFLAG_Vacuum 0x0004 /* Currently in a VACUUM */ | > | | 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 | /* ** Allowed values for sqlite3.mDbFlags */ #define DBFLAG_SchemaChange 0x0001 /* Uncommitted Hash table changes */ #define DBFLAG_PreferBuiltin 0x0002 /* Preference to built-in funcs */ #define DBFLAG_Vacuum 0x0004 /* Currently in a VACUUM */ #define DBFLAG_VacuumInto 0x0008 /* Currently running VACUUM INTO */ #define DBFLAG_SchemaKnownOk 0x0010 /* Schema is known to be valid */ /* ** Bits of the sqlite3.dbOptFlags field that are used by the ** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface to ** selectively disable various optimizations. */ #define SQLITE_QueryFlattener 0x0001 /* Query flattening */ |
︙ | ︙ |
Changes to src/vacuum.c.
︙ | ︙ | |||
166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 | return SQLITE_ERROR; } if( db->nVdbeActive>1 ){ sqlite3SetString(pzErrMsg, db,"cannot VACUUM - SQL statements in progress"); return SQLITE_ERROR; } saved_openFlags = db->openFlags; if( pOut ){ if( sqlite3_value_type(pOut)!=SQLITE_TEXT ){ sqlite3SetString(pzErrMsg, db, "non-text filename"); return SQLITE_ERROR; } zOut = (const char*)sqlite3_value_text(pOut); db->openFlags &= ~SQLITE_OPEN_READONLY; db->openFlags |= SQLITE_OPEN_CREATE|SQLITE_OPEN_READWRITE; }else{ zOut = ""; } /* Save the current value of the database flags so that it can be ** restored before returning. Then set the writable-schema flag, and ** disable CHECK and foreign key constraints. */ saved_flags = db->flags; | > > > < < | 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 | return SQLITE_ERROR; } if( db->nVdbeActive>1 ){ sqlite3SetString(pzErrMsg, db,"cannot VACUUM - SQL statements in progress"); return SQLITE_ERROR; } saved_openFlags = db->openFlags; saved_mDbFlags = db->mDbFlags; if( pOut ){ if( sqlite3_value_type(pOut)!=SQLITE_TEXT ){ sqlite3SetString(pzErrMsg, db, "non-text filename"); return SQLITE_ERROR; } zOut = (const char*)sqlite3_value_text(pOut); db->openFlags &= ~SQLITE_OPEN_READONLY; db->openFlags |= SQLITE_OPEN_CREATE|SQLITE_OPEN_READWRITE; db->mDbFlags |= DBFLAG_PreferBuiltin | DBFLAG_Vacuum | DBFLAG_VacuumInto; }else{ zOut = ""; db->mDbFlags |= DBFLAG_PreferBuiltin | DBFLAG_Vacuum; } /* Save the current value of the database flags so that it can be ** restored before returning. Then set the writable-schema flag, and ** disable CHECK and foreign key constraints. */ saved_flags = db->flags; saved_nChange = db->nChange; saved_nTotalChange = db->nTotalChange; saved_mTrace = db->mTrace; db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks; db->flags &= ~(u64)(SQLITE_ForeignKeys | SQLITE_ReverseOrder | SQLITE_Defensive | SQLITE_CountRows); db->mTrace = 0; zDbMain = db->aDb[iDb].zDbSName; pMain = db->aDb[iDb].pBt; isMemDb = sqlite3PagerIsMemdb(sqlite3BtreePager(pMain)); |
︙ | ︙ | |||
303 304 305 306 307 308 309 | "SELECT'INSERT INTO vacuum_db.'||quote(name)" "||' SELECT*FROM\"%w\".'||quote(name)" "FROM vacuum_db.sqlite_master " "WHERE type='table'AND coalesce(rootpage,1)>0", zDbMain ); assert( (db->mDbFlags & DBFLAG_Vacuum)!=0 ); | | | 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 | "SELECT'INSERT INTO vacuum_db.'||quote(name)" "||' SELECT*FROM\"%w\".'||quote(name)" "FROM vacuum_db.sqlite_master " "WHERE type='table'AND coalesce(rootpage,1)>0", zDbMain ); assert( (db->mDbFlags & DBFLAG_Vacuum)!=0 ); db->mDbFlags &= ~(DBFLAG_Vacuum|DBFLAG_VacuumInto); if( rc!=SQLITE_OK ) goto end_of_vacuum; /* Copy the triggers, views, and virtual tables from the main database ** over to the temporary database. None of these objects has any ** associated storage, so all we have to do is copy their entries ** from the SQLITE_MASTER table. */ |
︙ | ︙ |
Changes to test/e_vacuum.test.
︙ | ︙ | |||
228 229 230 231 232 233 234 | INSERT INTO t4(x) VALUES('z'); DELETE FROM t4 WHERE x = 'y'; SELECT rowid, x FROM t4; } {1 x 3 z} do_execsql_test e_vacuum-3.1.2 { VACUUM; SELECT rowid, x FROM t4; | | < > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 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 | INSERT INTO t4(x) VALUES('z'); DELETE FROM t4 WHERE x = 'y'; SELECT rowid, x FROM t4; } {1 x 3 z} do_execsql_test e_vacuum-3.1.2 { VACUUM; SELECT rowid, x FROM t4; } {1 x 2 z} # Rowids are preserved if an INTEGER PRIMARY KEY is used do_execsql_test e_vacuum-3.1.3 { CREATE TABLE t5(x, y INTEGER PRIMARY KEY); INSERT INTO t5(x) VALUES('x'); INSERT INTO t5(x) VALUES('y'); INSERT INTO t5(x) VALUES('z'); DELETE FROM t5 WHERE x = 'y'; SELECT rowid, x FROM t5; } {1 x 3 z} do_execsql_test e_vacuum-3.1.4 { VACUUM; SELECT rowid, x FROM t5; } {1 x 3 z} # Rowid is preserved for VACUUM INTO do_execsql_test e_vacuum-3.1.5 { DROP TABLE t5; CREATE TABLE t5(x); INSERT INTO t5(x) VALUES('x'); INSERT INTO t5(x) VALUES('y'); INSERT INTO t5(x) VALUES('z'); DELETE FROM t5 WHERE x = 'y'; SELECT rowid, x FROM t5; } {1 x 3 z} forcedelete test2.db do_execsql_test e_vacuum-3.1.6 { VACUUM INTO 'test2.db'; ATTACH 'test2.db' AS aux1; SELECT rowid, x FROM aux1.t5; DETACH aux1; } {1 x 3 z} # Rowids are renumbered even if the table being vacuumed # has indexes. do_execsql_test e_vacuum-3.1.7 { DROP TABLE t5; CREATE TABLE t5(x,y,z); INSERT INTO t5(x) VALUES('x'); INSERT INTO t5(x) VALUES('y'); INSERT INTO t5(x) VALUES('z'); UPDATE t5 SET y=x, z=random(); DELETE FROM t5 WHERE x = 'y'; CREATE INDEX t5x ON t5(x); CREATE UNIQUE INDEX t5y ON t5(y); CREATE INDEX t5zxy ON t5(z,x,y); SELECT rowid, x FROM t5; } {1 x 3 z} do_execsql_test e_vacuum-3.1.8 { VACUUM; SELECT rowid, x FROM t5; } {1 x 3 z} # EVIDENCE-OF: R-49563-33883 A VACUUM will fail if there is an open # transaction, or if there are one or more active SQL statements when it # is run. # |
︙ | ︙ |