Temp database
(1) By anonymous on 2021-10-15 08:57:04 [link] [source]
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) By Stephan Beal (stephan) on 2021-10-15 09:34:57 in reply to 1 updated by 2.1 [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 ...; ```
(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.
(3) By anonymous on 2021-10-15 09:50:25 in reply to 2.1 [link] [source]
Perfect! Thank you.
(4) By Keith Medcalf (kmedcalf) on 2021-10-16 02:41:49 in reply to 1 [link] [source]
''
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] [source]
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.
(6) By Keith Medcalf (kmedcalf) on 2021-10-16 03:16:12 in reply to 5 [link] [source]
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 [link] [source]
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:
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
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] [source]
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.