SQLite Forum

Temp database
Login

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.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 [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.

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] [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.