On 2016/10/17 10:40 PM, Ben Newberg wrote:
First off, my apologies for hijacking this thread.
But I've seen some strange things when messing with pragma writable_schema.
It appears all bets are off?
example: create a table of columns (x, y, z), and fill it with values.
then, modify sqlite_master to take out column z.
let's say later on down the line, you either add a column using the
pragma writable_schema, or the ALTER TABLE.
What happens is the new column is filled with the old column's values://...
Correct. Note that what you see is not "how it works", this is just one
possible outcome, a corruption opportunity that happens to go o.k.
Imagine a train on a circuit track (perhaps a plastic-track toy train is
the best example here). If you remove some rails in a straight section,
and the ground is firm, the train /might/ keep going in a straight line
and end up back on its tracks after the removed section. If later you
replace the track, or place a new different track in the same place, the
train may just recover its complete original operation. If you remove a
curved section however, and/or replace with a different section, the
train may get completely derailed.
What you have done here is messed with the tracks of a train, but not
enough to completely derail (corrupt) it yet... and then replaced them
regaining original form. The fact it still works is a matter of fortune
or happenstance of the specific changes and not a contractual obligation
of functioning - all bets are indeed off once you manually change the
The only reason the writable schema pragma exists is that sometimes, in
special circumstances, you may want to make a change that either cannot
be done through standard API, or you want to make the schema readable to
an earlier version (perhaps even to try recover a broken database or
such) - it is in no way a means of achieving standard functionality,
hence carrying the warning. Usually it's safe when there's no data in
the DB yet, but once there is a running train (data) and you manually
adjust the tracks (schema), all data guarantees are void.
For almost any change to the DB you can fathom, there exists (or can be
devised) some SQL script that will achieve the same without resorting to
sqlite-users mailing list