On 14 Oct 2016, Simon Slavin wrote: > The problem is that SQLite doesn't expect you to make manual changes to > sqlite_master. It doesn't reread the schema to execute every command.
That is factually correct as evidenced by this example: ======= snip ======== (loco2-precise)torsten.landschoff@horatio:~$ /opt/sqlite-dev/bin/sqlite3 example.db SQLite version 3.14.2 2016-09-12 18:50:49 Enter ".help" for usage hints. sqlite> create table foo (name varchar(80), address varchar(32)); sqlite> insert into foo values (null, 'N/A'); sqlite> pragma writable_schema=on; sqlite> update sqlite_master set sql=replace(sql, 'varchar(80),', 'varchar(80) not null,') where name='foo'; sqlite> pragma writable_schema=off; sqlite> insert into foo values (null, 'Hamburg'); sqlite> .schema foo CREATE TABLE foo (name varchar(80) not null, address varchar(32)); sqlite> ======= snip ======== > diagnostic tool, please try closing the file and reopening it after you've > changed sqlite_master. I did. If you look at the original email, I ran a separate shell command to ensure that the table is reread. What I did not mention was that I checked that the constraint actually works for newly inserted data: ======= snip ======== (loco2-precise)torsten.landschoff@horatio:~$ /opt/sqlite-dev/bin/sqlite3 example.db [...] sqlite> insert into foo values (null, 'Frankfurt'); Error: NOT NULL constraint failed: foo.name ======= snip ======== > I'm betting that the integrity check works after that. Fine. Let's say $100? As above I already did that in the original email. But here it is again: ======= snip ======== (loco2-precise)torsten.landschoff@horatio:~$ /opt/sqlite-dev/bin/sqlite3 example.db [...] sqlite> select * from foo where name is null; |N/A |Hamburg sqlite> pragma integrity_check; ok ======= snip ======== I would have expected the integrity_check to report both rows having NULL in the name column. In fact it does if there is an index on that table: ======= snip ======== sqlite> create index ix_demo on foo(address); sqlite> pragma integrity_check; NULL value in foo.name NULL value in foo.name ======= snip ======== > It might also be possible to force re-reading of sqlite_master using the > following command: > > ANALYZE sqlite_master Maybe. I did not care or check before. Let's see: ======= snip ======== (loco2-precise)torsten.landschoff@horatio:~$ rm example.db (loco2-precise)torsten.landschoff@horatio:~$ /opt/sqlite-dev/bin/sqlite3 example.db SQLite version 3.14.2 2016-09-12 18:50:49 Enter ".help" for usage hints. sqlite> create table foo (name varchar(80), address varchar(32)); sqlite> insert into foo values (null, 'N/A'); sqlite> pragma writable_schema=on; sqlite> update sqlite_master set sql=replace(sql, 'varchar(80),', 'varchar(80) not null,') where name='foo'; sqlite> insert into foo values (null, 'Hamburg'); sqlite> analyze sqlite_master; sqlite> insert into foo values (null, 'Berlin'); sqlite> ======= snip ======== So no, it does not make any difference (as I would have expected). The only reference to "analyze sqlite_master" in the documentation that I remember is here: https://www.sqlite.org/lang_analyze.html Quote: | An application can force the query planner to reread the statistics tables by | running ANALYZE sqlite_master. > I'm not clear on why this works, though, so don't take my word for it. One > thing I believe definitely causes rereading of the entire schema would be to > perform any change of schema: > > CREATE dummy_for_reread(t TEXT); > DROP TABLE dummy_for_reread; Interesting idea. I tried this: ======= snip ======== sqlite> CREATE TABLE dummy_for_reread(t TEXT); sqlite> DROP TABLE dummy_for_reread; sqlite> insert into foo values (null, 'Munich'); sqlite> ======= snip ======== So no, it does not cause a reread of the schema. > Again, please test this rather than assuming it. I've not seen it > documented, > I've only seen it in use as an undocumented hack. Better tell them that their hack is not working. ;-) So much about my attempt to report a bug. If you don't want to believe my report, then don't. The problem does not really affect me as I just added a manual check for NULL values after my database upgrade, which is probably much more efficient than running pragma integrity_check over the whole database. I just wanted to give back by telling about a problem in the code. BTW: Here is the reason in code: // source: http://sqlite.org/src/artifact/c8b499756658cb8b82cfdbb5845c22cf11f297aa?ln=1402 case PragTyp_INTEGRITY_CHECK: { int i, j, addr, mxErr; // ... /* Do an integrity check on each database file */ for(i=0; i<db->nDb; i++){ // ... /* Make sure all the indices are constructed correctly. */ for(x=sqliteHashFirst(pTbls); x && !isQuick; x=sqliteHashNext(x)){ // ... if( pTab->pIndex==0 ) continue; // ... /* Verify that all NOT NULL columns really are NOT NULL */ for(j=0; j<pTab->nCol; j++){ // ... It appears to me that the row based primary key index is not counted as an index by pTab->pIndex so the NOT NULL checks are skipped without index. Greetings, Torsten -- $---+----1----+----2----+----3----+----4----+----5----+----6----+ SCALE GmbH Niederlassung Dresden Torsten Landschoff Pohlandstraße 19 01309 Dresden Tel: +49-351-312002-10 Fax: +49-351-312002-29 SCALE GmbH Registergericht und Sitz: Ingolstadt, HRB 6384 Geschäftsführer: Dr.-Ing. Heiner Müllerschön, Dipl.-Math. Ulrich Franz _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
