--- [EMAIL PROTECTED] wrote: > "Scott Hess" <[EMAIL PROTECTED]> wrote: > > I was getting ready to checkin the rowid-versus-fts2 fix, and wanted > > to add one last bit, to upgrade older tables. > > > > Unfortunately, code of the form: > > > > ALTER TABLE x_segments ADD id INTEGER PRIMARY KEY; > > > > is documented as not supported. > > http://www.sqlite.org/lang_altertable.html . As far as I can tell, > > this means that there is no option to do a cheap schema upgrade to get > > the correct semantics. Am I missing a trick? > > It appears that you can set > > PRAGMA writable_schema=ON; > > Then do a manual UPDATE of the sqlite_master table to insert > an "id INTEGER PRIMARY KEY" into the SQL for the table definition. > I tried it and it seems to work. But it is dangerous. If you > mess up, you corrupt the database file.
As long as we're on the topic of writable_schema = ON hacks, it seems you can have many tables/indexes point to the same underlying pages of another table/index. Of course it won't survive a VACUUM, and you'll have problems with pragma integrity_check, but what the heck... $ ./sqlite3 hack.db SQLite version 3.4.2 Enter ".help" for instructions sqlite> CREATE TABLE abc(a, b, c); sqlite> CREATE INDEX abc_i on abc(c, a); sqlite> insert into abc values(4,5,6); sqlite> insert into abc values(1,2,3); sqlite> insert into abc values(9,8,7); sqlite> pragma writable_schema=on; sqlite> .header on sqlite> select * from sqlite_master; type|name|tbl_name|rootpage|sql table|abc|abc|2|CREATE TABLE abc(a, b, c) index|abc_i|abc|3|CREATE INDEX abc_i on abc(c, a) Create an "alias" table and index sharing the data of the other table via the same rootpage... sqlite> insert into sqlite_master values('table','xyz','xyz',2,'CREATE TABLE xyz(x, y, z, id INTEGER PRIMARY KEY)'); sqlite> insert into sqlite_master values('index','xyz_i','xyz',3,'CREATE INDEX xyz_i on xyz(z, x)'); sqlite> select * from abc; a|b|c 4|5|6 1|2|3 9|8|7 sqlite> select * from sqlite_master; type|name|tbl_name|rootpage|sql table|abc|abc|2|CREATE TABLE abc(a, b, c) index|abc_i|abc|3|CREATE INDEX abc_i on abc(c, a) table|xyz|xyz|2|CREATE TABLE xyz(x, y, z, id INTEGER PRIMARY KEY) index|xyz_i|xyz|3|CREATE INDEX xyz_i on xyz(z, x) sqlite> .q # is there another way to force a reload on the schema from # the sqlite3 shell? $ ./sqlite3 hack.db SQLite version 3.4.2 Enter ".help" for instructions Notice the same data in the "aliased" table xyz... sqlite> select * from xyz; 4|5|6|1 1|2|3|2 9|8|7|3 sqlite> select * from abc; 4|5|6 1|2|3 9|8|7 sqlite> .dump BEGIN TRANSACTION; CREATE TABLE abc(a, b, c); INSERT INTO "abc" VALUES(4,5,6); INSERT INTO "abc" VALUES(1,2,3); INSERT INTO "abc" VALUES(9,8,7); CREATE TABLE xyz(x, y, z, id INTEGER PRIMARY KEY); INSERT INTO "xyz" VALUES(4,5,6,1); INSERT INTO "xyz" VALUES(1,2,3,2); INSERT INTO "xyz" VALUES(9,8,7,3); CREATE INDEX abc_i on abc(c, a); CREATE INDEX xyz_i on xyz(z, x); COMMIT; Notice that changing one table affects the other, since they are sharing underlying data... sqlite> insert into abc values(2,3,4); sqlite> delete from xyz where x=1; sqlite> .dump BEGIN TRANSACTION; CREATE TABLE abc(a, b, c); INSERT INTO "abc" VALUES(4,5,6); INSERT INTO "abc" VALUES(9,8,7); INSERT INTO "abc" VALUES(2,3,4); CREATE TABLE xyz(x, y, z, id INTEGER PRIMARY KEY); INSERT INTO "xyz" VALUES(4,5,6,1); INSERT INTO "xyz" VALUES(9,8,7,3); INSERT INTO "xyz" VALUES(2,3,4,4); CREATE INDEX abc_i on abc(c, a); CREATE INDEX xyz_i on xyz(z, x); COMMIT; sqlite> select * from abc order by c, a; 2|3|4 4|5|6 9|8|7 sqlite> select * from xyz order by z, x; 2|3|4|4 4|5|6|1 9|8|7|3 Oh well, it was good while it lasted... sqlite> pragma integrity_check; *** in database main *** List of tree roots: 2nd reference to page 3 List of tree roots: 2nd reference to page 2 ____________________________________________________________________________________ Pinpoint customers who are looking for what you sell. http://searchmarketing.yahoo.com/ ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------