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
