Re: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.
I'd love to do fts2_1, because it implies fts1_1, but, really, 2_1 implies that the data would be backward-compatible, and maybe there's just a new feature exposed or something. -scott On 8/14/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote: > > +1 for fts3 or fts2_1 :-) > > --- > We're Hiring! Seeking a passionate developer to join our team building > products. Position is in the Washington D.C. metro area. If interested > contact [EMAIL PROTECTED] > > -Original Message- > From: Scott Hess [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 14, 2007 8:22 PM > To: [EMAIL PROTECTED] > Cc: sqlite-users@sqlite.org > Subject: Re: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY. > > On 8/14/07, [EMAIL PROTECTED] <[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. > > Ooh, I think that tips me away from fixing fts2, because it's scary > and Google Gears disables PRAGMA. > > At least Joe threw in a vote for just versioning things to fts3 - > anyone want to vote against? > > -scott > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.
+1 for fts3 or fts2_1 :-) --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Scott Hess [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 14, 2007 8:22 PM To: [EMAIL PROTECTED] Cc: sqlite-users@sqlite.org Subject: Re: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY. On 8/14/07, [EMAIL PROTECTED] <[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. Ooh, I think that tips me away from fixing fts2, because it's scary and Google Gears disables PRAGMA. At least Joe threw in a vote for just versioning things to fts3 - anyone want to vote against? -scott - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.
"Scott Hess" <[EMAIL PROTECTED]> wrote: > > > > 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. > > Ooh, I think that tips me away from fixing fts2, because it's scary > and Google Gears disables PRAGMA. > > At least Joe threw in a vote for just versioning things to fts3 - > anyone want to vote against? > +1 in favor of fts3. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.
On 8/14/07, [EMAIL PROTECTED] <[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. Ooh, I think that tips me away from fixing fts2, because it's scary and Google Gears disables PRAGMA. At least Joe threw in a vote for just versioning things to fts3 - anyone want to vote against? -scott - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.
--- [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]
-
Re: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.
--- Scott Hess <[EMAIL PROTECTED]> wrote: > This may mean that I'll need to branch fts2 to fts3 and deprecate > fts1/2 as being not safe for use. If the code is going to have to > create new tables and populate them, then there's not a lot of gain > versus just having the developer do that. Is it a good thing to still call the upgraded module "fts2" if its schema is not backwards compatible with older versions of sqlite/fts2? This is similar in spirit to the sqlite 3.x file format change that was later reverted. Just playing the devil's advocate - I don't use fts. Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase. http://farechase.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.
"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. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
