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] [source]
what is the string for opening/attaching a temporary database?
sqlite doesn't, to the best of my knowledge, have any APIs for managing temp databases. It has temp tables:
create temp table x ...;
Edit: that said, try
"" instead of
":memory:". IIRC that works like memory but uses a temp database.
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.
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.
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.
Thanks Keith. I need clarification on a some aspects:
1.In a new CLI session,
returns nothing. (If I assign to it, it returns what I assigned). How do I find the current/default location?
2.Consider this session:
sqlite> attach ':memory:' as AUX;
sqlite> attach '' as TMP;
sqlite> .mode column
sqlite> select * from pragma_database_list();
seq name file
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?
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.