Update: One of my assumptions below was wrong. Our Macintosh build (which this user was using) in fact did not have data_deleted.db3 as part of the install. It was being created and populated on startup. So my best guess is that the file was created, and then the transaction that creates all the tables failed somehow, and left the file with zero bytes. So the lessons learned (aside from checking assumptions) is that a SQLite file starts with zero-bytes when you open/create it, and SQLite is happy to close it in that state if you haven't effectively written anything to it (and happy to open it again in that state). It's still a mystery why the table creation transaction might have failed, but that's at least an understandable cause of the problem.
I have a program that uses SQLite (amalgamation file version 3.9.1 compiled as part of my C++ application). I use two .db3 files. One is called data.db3 and the other is called data_deleted.db3. When the program is installed both of these files are in the program's data directory. They initially contain no records, but because of database structure information, indexes, etc. data.db3 starts at 70k and data_deleted.db3 starts at 20k. The program opens data.db3 at startup and keeps it open for the entire session. At various times when the user deletes data, the program attaches data_deleted.db3 and copies records from data.db3 into data_deleted.db3, and then detaches data_deleted.db3.
A user got into a situation where data_deleted.db3 had zero bytes in it, and I'm trying to imagine how this could have happened. My understanding is that no matter what I do in terms of opening, closing, attaching, detaching, writing to, reading from, vacuuming, or anything else I might do with data_deleted.db3 through the SQLite API, the database should maintain its integrity, and certainly shouldn't suddenly get wiped of all content.
So I'm just asking if this sounds at all familiar to anyone with SQLite experience. Any clues appreciated.
