On Sun, 15 Oct 2017 18:36:56 -0700 (MST), Fiona <cxfhn1...@gmail.com> wrote:
> Thanks for noticing that problem! Follow your instructions, now I'm sure > it's all because my db file is corrupted. Is there anything I can do to fix > it? > > Integrity check result: > <http://sqlite.1065341.n5.nabble.com/file/t8403/integrity_check.jpg> I can think of three options: 1- Rebuild the database from the original input, with the schema improvements suggested in this thread 2- Restore a recent backup, then import the data into a new database with the correct schema [*]. 3- the recipe that Simon gave to retrieve as much of the contents as possible using the .dump command and build a new database from the dump file, then import the data into a new database with the correct schema [*]. [*] The script for importing data from a database with the old schema into a database with a better schema loks like this: sqlite3 newdb.sqite <script.sql script.sql contains: ================ CREATE TABLE t1 ( t1_id TEXT , t1_blob BLOB , PRIMARY KEY (t1_id) ); -- CREATE other tables, indexes, triggers, views etc. ATTACH 'olddb.sqlite' AS olddb; INSERT OR REPLACE INTO main.t1 (t1_id,t1_blob) SELECT t1_id,t1_blob FROM olddb.t1; -- similar for other tables. ================ In this example, the order of columns in the old database can be: CREATE TABLE t1 ( t1_blob BLOB , t1_id TEXT -- should be before the blob , PRIMARY KEY (t1_id) ); The procedure will adjust that. The INSERT OR REPLACE takes care of any keys in the old database that would violate a unique constraint in the new schema (duplicate rows). -- Regards, Kees Nuyt _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
