iterdump issues

[ghaering]

From GerhardHaering on January 28, 2010 10:28:36

Reported by Roger Binns:

• Views need to emitted in the order they are defined since
  they can reference each other

• Foreign key support needs to be disabled while loading
  data

• ANALYZE should be emitted at the very end not the
  beginning, and only for tables which are part of the dump

• sqlite_stat2 is also an analyze result table

• sqlite_sequence should be filled in with correct values
  rather than just deleting the contents which will break
  any existing tables and result in incorrect values from
  the dump

• Virtual tables are not supported

• The database is not locked while dumping which will lead

  to exceptions and a bad dump if the contents are changed
  during a dump

• You can't request a subset of tables (eg if you are just trying to dump a
  FTS3 virtual table which also includes 3 dependent real tables)

• SQLite dump doesn't do this but APSW, MySQL etc do. They
  drop existing table/view/index etc of the same name first.

Original issue: http://code.google.com/p/pysqlite/issues/detail?id=10

[ghaering]

From pri...@gmail.com on February 06, 2010 06:47:41

I dump tables with pysqlite, and the sequence table is dumped correctly:

DELETE FROM sqlite_sequence;
INSERT INTO "sqlite_sequence" VALUES('paths',4);
INSERT INTO "sqlite_sequence" VALUES('logs',5);
INSERT INTO "sqlite_sequence" VALUES('blocks',2);

[ghaering]

From g...@ghaering.de on September 23, 2010 03:39:30

Owner: g...@ghaering.de

[ghaering]

From rogerbinns on November 13, 2010 17:40:18

Response to comment 1 by @prirun:

That is not correct! Consider the dump having tables 'paths', 'logs' and 'blocks' while the existing database also has 'sites'. Emptying sqlite_sequence will cause the info for 'sites' to be lost.

You could argue that the dump must only be restored in an empty database but there is no such claim in the documentation nor is there any validation of emptyness (ie if you accidentally restored in a non-empty database and caused this problem there would be absolutely no indication until after you find you are getting reused sequence values.

The correct thing to do is not a blanket delete, but instead this sequence for your example above:

DELETE FROM "sqlite_sequence" WHERE name='paths';
INSERT INTO "sqlite_sequence" VALUES('paths',4);
DELETE FROM "sqlite_sequence" WHERE name='logs';
INSERT INTO "sqlite_sequence" VALUES('logs',5);
DELETE FROM "sqlite_sequence" WHERE name='blocks';
INSERT INTO "sqlite_sequence" VALUES('blocks',2);

There are a lot of nitty gritty details you have to get right to get a robust dump. I invite you to try one from APSW to see how careful it is. (It also comments its output!)

[ghaering]

From pri...@gmail.com on November 13, 2010 19:20:18

Good point Roger - thanks!

[ghaering]

See function command_dump() in apsw's tools/shell.py for code to borrow.

[ghaering]

Will not be updated anytime soon and stay with whatever bugs it has. If there are any improvements in the Python standard library version of it that I can port over to this project, please reopen.