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 | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: | 13a0ea6466b051ea5281865ed5285b8b |
| User & Date: | drh 2019-04-04 14:00:23 |
Context
|
2019-04-04
| ||
| 14:36 | Fix an incorrect comment on a test case for rowid renumbering in VACUUM. check-in: 0d293fb4 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: 13a0ea64 user: drh tags: trunk | |
|
2019-04-03
| ||
| 17:48 | Add a defense-in-depth NEVER() test to the WAL cleanup code. check-in: 8d3af201 user: drh tags: trunk | |
Changes
Changes to src/insert.c.
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_Vacuum) ){
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 ){
|
| |
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 1568 1569 1570 1571 1572 1573 1574 1575 |
/*
** 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_SchemaKnownOk 0x0008 /* 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 */
|
> | |
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
189
190
191
192
193
194
195
196
197
198
199
200
201
...
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
|
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;
saved_mDbFlags = db->mDbFlags;
saved_nChange = db->nChange;
saved_nTotalChange = db->nTotalChange;
saved_mTrace = db->mTrace;
db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks;
db->mDbFlags |= DBFLAG_PreferBuiltin | DBFLAG_Vacuum;
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));
................................................................................
"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;
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.
*/
|
>
>
>
<
<
|
|
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
...
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
|
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));
................................................................................
"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 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 |
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 3 z}
# Was: {1 x 2 z}
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}
# 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.
#
|
< | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
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.
#
|