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
