Reads about right. I'm writing the below step-by-step thing as much to test my own knowledge of what's going on as to help, so knowledgeable folks please correct me where I mess up.
The short version is: don't mess with writable schema unless you have no other choice. (Which you probably do) As Firefox has said "this may void your warranty" http://www.sqlite.org/pragma.html#pragma_writable_schema "Warning: misuse of this pragma can easily result in a corrupt database file." So, following your specific test there... When you update sqlite_master you're literally just changing a value in that table, you're not re-parsing it. It treats it just like any other table update. So the schema cookie in the file header isn't updated, nor are any data pages associated with table t. So when you do the next select * statement, your session sees that the schema cookie is the same as what it saw before and keeps the old schema in memory, no need to parse it all every single statement. The data pages in the file also haven't changed for table t, so everything matches nicely and comes out as if you hadn't done the sqlite_master change at all. When you try to vacuum, it re-parses things and sees that there is an unrecoverable issue, so fails. But gracefully fails and doesn't alter the existing file. http://www.sqlite.org/fileformat2.html The file format is such that as long as you start at the correct root page of a table, you don't need the schema to read the actual values. Since SQLite lets you put different data types into any declared field type, the data types are stored in the data section for every record. So you can get all the data just fine, the number of fields just might not jive with what you've been told to expect from the contents of sqlite_master. So your next select expects 2 fields, and (I guess) stops there, even though the data pages say that there are 3 fields. The alter table command also only updates sqlite_master. None of the data pages are updated until you start doing update or insert statements. So in your specific case, the add field statement just makes the number of fields in the schema jive with the number of fields in the data again. When you do your insert there, the number of fields in the schema, the statement, and the data pages all match up, so it doesn't re-write anything that was already there, and thus adds in the new record just fine. Nothing poped up as corrupt in your test except for the vacuum attempt, but that's just because of the ordering and what you did with the schema. Without trying very hard though you can mess things up real easy, especially if you change the rootpage field. It's a powerful tool. But just as though you were handed an industrial circular saw to use by hand, read the manual before use, and practice on something you don't mind destroying. And most importantly: if you can do the job with the nice, sturdy, guarded, documented, comparatively safe table saw there that's still under warranty, use that instead of the monster hand held one. Now I'm gonna go back to lurking in the corner... -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Ben Newberg Sent: Monday, October 17, 2016 4:41 PM To: SQLite mailing list Subject: Re: [sqlite] pragma integrity_check doesn't check constraints without indices First off, my apologies for hijacking this thread. But I've seen some strange things when messing with pragma writable_schema. It appears all bets are off? example: create a table of columns (x, y, z), and fill it with values. then, modify sqlite_master to take out column z. let's say later on down the line, you either add a column using the pragma writable_schema, or the ALTER TABLE. What happens is the new column is filled with the old column's values: SQLite version 3.14.2 2016-09-12 18:50:49 Enter ".help" for usage hints. sqlite> .headers on sqlite> create table t (x, y, z); sqlite> insert into t values (1, 'a', 'A'), (2, 'b', 'B'), (3, 'c', 'C'); sqlite> select * from t; x|y|z 1|a|A 2|b|B 3|c|C sqlite> pragma writable_schema = 1; sqlite> update sqlite_master set sql = 'create table t (x, y)' where name = 't'; sqlite> select * from t; x|y|z 1|a|A 2|b|B 3|c|C -- we are still seeing column z, until we vaccum: sqlite> vacuum; Error: table vacuum_db.t has 2 columns but 3 values were supplied sqlite> select * from t; x|y 1|a 2|b 3|c sqlite> alter table t add column w; sqlite> insert into t values (4, 'd', 'D'); sqlite> select * from t; x|y|w 1|a|A 2|b|B 3|c|C 4|d|D sqlite> On Mon, Oct 17, 2016 at 4:03 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 17 Oct 2016, at 8:17am, Torsten Landschoff <torsten.landsch...@scale.eu> > wrote: > > > So much about my attempt to report a bug. If you don't want to believe my > > report, then don't. > > Sorry, just to make it clear, I'm just a fellow-user of SQLite. I'm not > on the development team. And I totally believe what you wrote. One of > more of the development team might be figuring out the problem right now. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
