On 6/8/62 17:26, Olivier Mascia wrote:
On one database instance, a .dump command gives me (among many other lines)
things like:
INSERT INTO STATISTICS VALUES(11237795927160,11868);
while the output of .recover command gives me things this way:
INSERT INTO 'STATISTICS'('_rowid_', STATDATE, DISKUSED) VALUES( 1,
11237795927160, 11868 );
I'm wondering why these differences in the way to construct the instructions to
rebuild a sound database instance. What are the (probably rightful) motivations?
1) Why 'STATISTICS' (and not STATISTICS as in .dump command)? If escaping
wanted, why not double quotes instead of single quotes?
2) Why do the insert statement prefer to name and repeat, ad nausea, the column
names on each insert when, apparently, the shortcut syntax capitalizing on the
known column order in the schema might seem much less verbose?
On the real DB I quickly tested .recover on (with no reason, I have nothing to
recover, just testing the feature) I had an issue while rebuilding a new DB
from the script made by .recover. I got foreign key constraint failures (which
I have not yet traced exactly).
sqlite> .once system.sql
sqlite> .recover
sqlite3 recover.db
sqlite> .read system.sql
Error: near line 14658: FOREIGN KEY constraint failed
Error: near line 14659: FOREIGN KEY constraint failed
Error: near line 14660: FOREIGN KEY constraint failed
Thanks for the report. Looks like the .dump command adds "PRAGMA
foreign_keys=OFF;" to the output to avoid this. .recover now does this too:
https://sqlite.org/src/info/bfc29e62eff0ed00
Dan.
sqlite> .q
While doing the same kind of work around .dump worked nicely:
sqlite> .once systemd.sql
sqlite> .dump
sqlite3 dump.db
sqlite> .read systemd.sql
sqlite> .q
The source test db passes successfully those tests:
sqlite> pragma integrity_check;
integrity_check
ok
sqlite> pragma foreign_key_check;
sqlite> .dbconfig
enable_fkey on
enable_trigger on
fts3_tokenizer off
load_extension on
no_ckpt_on_close off
enable_qpsg off
trigger_eqp off
reset_database off
defensive off
writable_schema off
legacy_alter_table off
dqs_dml off
dqs_ddl off
Again, I have no recovery to attempt for now. I was just exercising the
.recover feature for learning, using a db I'm not suspecting of anything.
—
Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten
Grüßen,
Olivier Mascia
_______________________________________________
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