--- [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]
-----------------------------------------------------------------------------
