Hi Ben, On 10/17/2016 10:40 PM, Ben Newberg wrote: > First off, my apologies for hijacking this thread.
:-( I just wanted to report a bug... > 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: Which should be quite obvious: Changing sqlite_master just changes what SQLite thinks is in those tables or rather B-Trees. This is a sharp knife and you must know not only what you are doing but also what SQLite is doing internally with that information. For example I tried to get rid of a surrogate key on a table like create table items (id integer primary key, parent, child, order); by dropping the id column from sqlite master, assuming that it was not in the internal representation anyway as the id will be the row number. This was a false assumption. Actually the row number is not written but a NULL value is prepended to every row: https://www.sqlite.org/fileformat2.html#representation_of_sql_tables So to make this modification work I would have to write code to move the data one column to the left. SQLite does not directly support this. And, BTW: my modification caused segfaults when accessing the DB via SQLite. So I ended up renaming the table and recreating it from the old data, which takes up to 2 hours for each client instance which is quite unfortunate. > -- we are still seeing column z, until we vaccum: > sqlite> vacuum; > Error: table vacuum_db.t has 2 columns but 3 values were supplied Most likely this is only because sqlite_master was not reread. The 3rd column is still there despite the vacuum, it just ended up rereading the schema. Probably because vacuum internally copies the whole database and probably is akin to reopening the DB. Greetings, Torsten -- $---+----1----+----2----+----3----+----4----+----5----+----6----+ SCALE GmbH Niederlassung Dresden Torsten Landschoff Pohlandstraße 19 01309 Dresden Tel: +49-351-312002-10 Fax: +49-351-312002-29 SCALE GmbH Registergericht und Sitz: Ingolstadt, HRB 6384 Geschäftsführer: Dr.-Ing. Heiner Müllerschön, Dipl.-Math. Ulrich Franz _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
