> sqlite> CREATE TABLE abc2(a, b, c); > > sqlite> CREATE VIEW abc2_v AS SELECT * FROM abc2; > sqlite> SELECT * FROM abc2_v; > > sqlite> ALTER TABLE abc2 ADD COLUMN d; > > ================================================================ > I would expect the view to give 4 columns after the alter, not 3. > as the following direct select shows.
I started out by explaining why SQLite was not doing what you expected, and then I found out that there does in fact seem to be a bug afoot. There are two reasonable behaviors here: 1. Preserving the "*"ness, so that adding a column to the relevant source table will add the column to the view 2. Expand the '*' at view-creation-time, so that adding columns to the source table(s) does not affect the view (I'm pretty sure PostgreSQL works like this) It looks like SQLite mostly does the first one. However, if a table is altered, the schema cache is not flushed. Watch! D:\>sqlite3 tmp.db SQLite version 3.5.6 Enter ".help" for instructions sqlite> .headers on sqlite> create table abc2 (a,b,c); sqlite> insert into abc2 values(1,2,3); sqlite> create view abc2_v as select * from abc2; sqlite> select * from abc2_v; a|b|c 1|2|3 sqlite> alter table abc2 add column d; sqlite> select * from abc2_v; a|b|c 1|2|3 As you can see, SQLite hasn't realized that abc2_v needs to be updated. However, if I open another command prompt and run: D:\>sqlite3 tmp.db SQLite version 3.5.6 Enter ".help" for instructions sqlite> create table xyz(a); This increments the schema version, which will invalidate the schema cache (and any prepared statements, but that doesn't really apply to sqlite3.) So, back in the original terminal: sqlite> select * from abc2_v; a|b|c|d 1|2|3| Looking at the 'alter2.test' source at http://www.sqlite.org/cvstrac/fileview?f=sqlite/test/alter2.test&v=1.13, I can see that the "alter_table" function appears to open a dedicated connection to the database, which means when the "execsql" function is then called, it is not on the same connection as the "alter_table" one and cannot take advantage of the database cache. I think we might need an alter2b.test, and maybe even an alternot2b.test (ba-dum-pshh!) -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
