On 8 July 2010 02:20, Jon Polfer <jpol...@forceamerica.com> wrote: > I'm currently running SQLite 3.5.9. > > I've been experimenting around with temporary views, and discovered > that: > > a) They don't appear in sqlite_master after you create them; they do > however appear in a (I believe undocumented) table called > sqlite_temp_master that I found by running an EXPLAIN on the CREATE > TEMPORARY VIEW. > > CREATE TEMPORARY VIEW test_view AS SELECT 1 + 1 AS two; > SELECT count(*) from sqlite_master where type = 'view' and name = > 'test_view'; -- returns 0 > SELECT count(*) from sqlite_temp_master where type = 'view' and name = > 'test_view'; -- returns 1 > > > b) You can create another view with the same name that is non-temporary > (CREATE VIEW test_view AS SELECT 2 + 2 AS four). > > CREATE TEMPORARY VIEW test_view AS SELECT 1 + 1 AS two; > CREATE VIEW test_view AS SELECT 2 + 2 AS four; > > NB: One can then drop the test_view twice. > > c) It appears that, if you have a temporary view and a non-temporary > view of the same name, the temporary view is used, regardless of order > they were created in. > > CREATE VIEW test_view AS SELECT 2 + 2 AS four; > CREATE TEMPORARY VIEW test_view AS SELECT 1 + 1 as two; > SELECT * FROM test_view; -- returns 2 > DROP VIEW test_view; > DROP VIEW test_view; > CREATE TEMPORARY VIEW test_view AS SELECT 1 + 1 as two; > CREATE VIEW test_view AS SELECT 2 + 2 AS four; > SELECT * FROM test_view; -- returns 2 > > > Letter a) surprises me; can I count on sqlite_temp_master being around > for a while?
At least while you have temp data (tables, views etc) > > Letter b) feels like a bug. Has this been fixed? I don't think it's a bug > > Letter c) makes me wonder: is there is a way to reference both views? qualify with the db name - main or temp: SQLite version 3.6.11 Enter ".help" for instructions sqlite> sqlite> CREATE TEMPORARY VIEW test_view AS SELECT 1 + 1 as two; sqlite> CREATE VIEW test_view AS SELECT 2 + 2 AS four; sqlite> SELECT * FROM main.test_view; 4 sqlite> SELECT * FROM temp.test_view; 2 sqlite> > > -Jon > Regards, Simon _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
