Temp database
(1) By anonymous on 2021-10-15 08:57:04 [link]
If possible, how do I open a temp database? I specify ':memory:' for an in-memory database; what is the string for opening/attaching a temporary database?
(2.1) By Stephan Beal (stephan) on 2021-10-15 09:35:50 edited from 2.0 in reply to 1 [link]
> what is the string for opening/attaching a temporary database? <s>sqlite doesn't, to the best of my knowledge, have any APIs for managing temp databases. It has temp tables:</s> ``` create temp table x ...; ``` Edit: that said, try `""` instead of `":memory:"`. IIRC that works like memory but uses a temp database.
(3) By anonymous on 2021-10-15 09:50:25 in reply to 2.1 [link]
Perfect! Thank you.
(4) By Keith Medcalf (kmedcalf) on 2021-10-16 02:41:49 in reply to 1 [link]
'' That is, you can specify a file name, the special name ':memory:', or an empty string. Specifying a filename opens that file as a database. Specifying the special name ':memory:' opens a database that resides purely in memory (as in Volatile RAM). Specifying nothing for the filename (an empty text string) causes a "temporary file" to be created and then opened as a database. Once the "temporary file" (database) connection is closed, the "temporary file" is deleted. A 'named file' database may be accessed simultaneously by multiple connections, however, a ':memory:' or '' (temporary) database may only be exclusively accessed by the connection which created it -- it cannot be accessed by multiple connections. You will note that *shared-cache* is a method of **emulating** multiple connections, however, there is only one single connection to the database.
(5) By Keith Medcalf (kmedcalf) on 2021-10-16 03:04:58 in reply to 4 [link]
You will note that when you specify a filename ('filename.db' for example) then that file is opened and used as the database. When you specify a filename ':memory:' then a database exclusive to that connection is created entirely in memory. No part of it will reside on disk. If you create a temporary database by specifying a blank filename (ie, '') then the database will be created and treated according to the TEMP_STORE currently set. If TEMP_STORE is currently set to memory only, then there is no difference between a ':memory:' database and a '' database. In the default case, a '' (temporary) database resides in a temporary disk file, however, since the disk file is temporary, data will not be written to disk until memory is exhausted. <https://sqlite.org/pragma.html#pragma_temp_store>
(6) By Keith Medcalf (kmedcalf) on 2021-10-16 03:16:12 in reply to 5 [link]
Also note that if you change TEMP_STORE, then the TEMP database will be cleared, however, ':memory:' and '' databases and attachments will not be cleared.
(7) By anonymous on 2021-10-16 07:50:43 in reply to 5
Thanks Keith. I need clarification on a some aspects: 1.In a new CLI session, > PRAGMA temp_store_directory; returns nothing. (If I assign to it, it returns what I assigned). How do I find the current/default location? 2.Consider this session: <code>sqlite> attach ':memory:' as AUX; sqlite> attach '' as TMP; sqlite> .mode column sqlite> select * from pragma_database_list(); seq name file 0 main 2 AUX 3 TMP</code> A. Why do I never find 1 in the seq column? (first column) B. The file column values is identical for :memory: and '' - why? 3.As with :memory: I can attach as many '' (temp) databases as long as the name/alias is unique. Correct?
(8) By Keith Medcalf (kmedcalf) on 2021-10-16 09:10:52 in reply to 7 [link]
> How do I find the current/default location? This is Operating System dependent. <https://sqlite.org/tempfiles.html> Section 5 > Why do I never find 1 in the seq column? (first column) seq 0 is always the main database -- the one opened when the connection was created. seq 1 is always the temp database -- which may not exist if it has never been used. seq 2 and after are the currently attached databases in order of attachment. > As with :memory: I can attach as many '' (temp) databases as long as the name/alias is unique. Correct? Yes. And each so attached database is unique to the connection.