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 tracks.
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 schema fiddling.
HTH Ryan _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
