SQLite Forum

New version of app, more columns for same table

New version of app, more columns for same table

(1) By anonymous on 2021-06-27 17:22:58 [link] [source]


I am doing a thesis on carving SQLite records, and I found a particular case in an iOS message database. I have found records that appear in a table that has more columns than mentioned in the binary record. For example, a table which has 10 columns and which contains records which have 10 columns, but also records which have only 8 columns (from a potential previous version of the application) and whose last 2 fields are filled by default? I wonder how SQLite handles these cases, how it knows that it has to introduce these records in this table (other than by its positioning in the schema) and how it fills in the missing fields? Since I am only relying on the fact that the records must have the same number and types of columns to go to a given table, my records from an old version are not written to the table because they do not contain the same number of columns ...

Thank you so much.

(2) By Larry Brasfield (larrybr) on 2021-06-27 17:39:51 in reply to 1 [link] [source]

I had some difficulty deciding whether this is a bona fide inquiry or the output of a gibberish generator automaton. Assuming the former and that your question is about storage format, I would think your answer lies in the documented Database File Format.

(3) By anonymous on 2021-06-27 19:00:42 in reply to 2 [link] [source]

Haha it's humiliating.. Thank you for your answer..

(4) By Larry Brasfield (larrybr) on 2021-06-27 19:44:03 in reply to 3 [link] [source]

Sorry -- that was not my intention. The question was unclear for multiple reasons.

Section 2.1 in the page I linked says: "A record might have fewer values than the number of columns in the corresponding table. This can happen, for example, after an ALTER TABLE ... ADD COLUMN SQL statement has increased the number of columns in the table schema without modifying preexisting rows in the table. Missing values at the end of the record are filled in using the default value for the corresponding columns defined in the table schema."

That seems to touch upon several elements within your first post.

(5) By anonymous on 2021-06-27 19:51:07 in reply to 4 [link] [source]

Yes sorry my English is bad. I found that Section after your reply and it clarifies all my doubts!

(8) By anonymous on 2021-06-28 16:36:55 in reply to 2 [source]

No content here, but I just have to say I find this reply the most amusing response to any internet question, ever. Well done!

(6) By Richard Damon (RichardDamon) on 2021-06-27 20:03:39 in reply to 1 [link] [source]

What you seem to be missing is that when you read the row that physically has fewer columns written in it than the current table schema defines, that SQLite will add in those columns with the default values. In fact, my understanding is that for most purposes, unless you low-level read the raw database and go around SQLite itself, you can't tell by reading it that the values aren't there.

I think the one exception is that you can detect that the data isn't there by reading the record, changing the default with an alter table, and read the record again. Columns with no value and just getting the default will change here, while once the record actually get re-written, the value will stick to the default value written when the record was written.

(7) By anonymous on 2021-06-27 21:19:48 in reply to 6 [link] [source]

Yes so the fact that I retrieve records based on their length no longer works because I do not take into account any default values ​​which are not visible in binary but only when reading the database itself. Thank you!