Re: [sqlite] Temp views and sqlite_temp_master
On Wed, Jul 07, 2010 at 08:20:13PM -0500, Jon Polfer scratched on the wall: > 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. http://www.sqlite.org/faq.html#q7 http://www.sqlite.org/sqlite.html Not obvious, but there. > b) You can create another view with the same name that is non-temporary > (CREATE VIEW test_view AS SELECT 2 + 2 AS four). All temp items go into a different database named "temp". Object names are only unique within a database. > 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. Yes. The search pattern for an unqualified identifier is always: 1) temp database 2) main database (opened with sqlite3_open()) 3) any others, in "slot" order (opened with ATTACH). These will fill up in order (assuming no DETACH commands are run). If you want to access a specific object, just qualify it. > Letter a) surprises me; can I count on sqlite_temp_master being around > for a while? Yes, it is just as standard as sqlite_master. > Letter b) feels like a bug. Has this been fixed? Not if you understand how it works. Not fixing what isn't broken. > Letter c) makes me wonder: is there is a way to reference both views? As others have answered, just qualify the identifier with a database name. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Temp views and sqlite_temp_master
On 8 July 2010 02:20, Jon Polfer 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
[sqlite] Temp views and sqlite_temp_master
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? Letter b) feels like a bug. Has this been fixed? Letter c) makes me wonder: is there is a way to reference both views? -Jon __ Jon Polfer Project Engineer - High Level Software Engineering Office Phone: 262-832-0049 (Ext. 5 for Jon Polfer) Fax: E-mail: jpol...@forceamerica.com FORCE America Inc. W229 N1433 Westwood Drive, Suite 200 Waukesha, WI 53186 www.forceamerica.com The Leading Innovator in Mobile Hydraulic Solutions The information contained in this message and any attachment may be proprietary, confidential, and privileged or subject to the work product doctrine and thus protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify me immediately by replying to this message and deleting it and all copies and backups thereof. Thank you. Disclaimer added by CodeTwo Exchange Rules http://www.codetwo.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
