Artifact
d4675c11f0c83f6517356072dcd7eef1fe72defa:
- File
pages/inmemorydb.in
— part of check-in
[2ed85cc08c]
at
2012-05-28 17:05:12
on branch trunk
— Documentation on the unicode61 tokenizer and the ability to use shared-cache
with in-memory databases.
(user:
drh
size: 4968)
<title>In-Memory Databases</title>
<tcl>hd_keywords {in-memory database} {in-memory databases} {memory}</tcl>
<h1 align="center">In-Memory Databases</h1>
<p>An SQLite database is normally stored in a single ordinary disk
file. However, in certain circumstances, the database might be stored in
memory.</p>
<p>The most common way to force an SQLite database to exist purely
in memory is to open the database using the special filename
"<b>:memory:</b>". In other words, instead of passing the name of
a real disk file into one of the [sqlite3_open()], [sqlite3_open16()], or
[sqlite3_open_v2()] functions, pass in the string ":memory:". For
example:</p>
<blockquote><pre>
rc = sqlite3_open(":memory:", &db);
</pre></blockquote>
<p>When this is done, no disk file is opened.
Instead, a new database is created
purely in memory. The database ceases to exist as soon as the database
connection is closed. Every :memory: database is distinct from every
other. So, opening two database connections each with the filename
":memory:" will create two independent in-memory databases.</p>
<p>The special filename ":memory:" can be used anywhere that a database
filename is permitted. For example, it can be used as the
<i>filename</i> in an [ATTACH] command:</p>
<blockquote><pre>
ATTACH DATABASE ':memory:' AS aux1;
</pre></blockquote>
<p>Note that in order for the special ":memory:" name to apply and to
create a pure in-memory database, there must be no additional text in the
filename. Thus, a disk-based database can be created in a file by prepending
a pathname, like this: "./:memory:".</p>
<p>The special ":memory:" filename also works when using [URI filenames].
For example:
<blockquote><pre>
rc = sqlite3_open("file::memory:", &db);
</pre></blockquote>
Or,
<blockquote><pre>
ATTACH DATABASE 'file::memory:' AS aux1;
</pre></blockquote>
<tcl>hd_fragment sharedmemdb {in-memory shared cache database}</tcl>
<h2>In-memory Databases And Shared Cache</h2>
<p>In-memory databases are allowed to use [shared cache] if they are
opened using a [URI filename]. If the unadorned ":memory:" name is used
to specify the in-memory database, then that database always has a private
cache and is this only visible to the database connection that originally
opened it. However, the same in-memory database can be opened by two or
more database connections as follows:
<blockquote><pre>
rc = sqlite3_open("file::memory:?cache=shared", &db);
</pre></blockquote>
Or,
<blockquote><pre>
ATTACH DATABASE 'file::memory:?cache=shared' AS aux1;
</pre></blockquote>
<p>This allows separate database connections to share the same
in-memory database. Of course, all database connections sharing the
in-memory database need to be in the same process. The database is
automatically deleted and memory is reclaimed when the last connection
to the database closes.
<p>If two or more distinct but shareable in-memory databases are needed
in a single process, then the [coreqp | mode=memory] query parameter can
be used with a [URI filename] to create a named in-memory database:
<blockquote><pre>
rc = sqlite3_open("file:memdb1?mode=memory&cache=shared", &db);
</pre></blockquote>
Or,
<blockquote><pre>
ATTACH DATABASE 'file:memdb1?mode=memory&cache=shared' AS aux1;
</pre></blockquote>
<p>When an in-memory database is named in this way, it will only share its
cache with another connection that uses exactly the same name.
<tcl>hd_fragment temp_db {temporary tables} {temporary databases}</tcl>
<h2>Temporary Databases</h2>
<p>When the name of the database file handed to [sqlite3_open()] or to
[ATTACH] is an empty string, then a new temporary file is created to hold
the database.</p>
<blockquote><pre>
rc = sqlite3_open("", &db);
</pre></blockquote>
<blockquote><pre>
ATTACH DATABASE '' AS aux2;
</pre></blockquote>
<p>A different temporary file is created each time, so that just like as
with the special ":memory:" string, two database connections to temporary
databases each have their own private database. Temporary databases are
automatically deleted when the connection that created them closes.</p>
<p>Even though a disk file is allocated for each temporary database, in
practice the temporary database usually resides in the in-memory pager
cache and hence is very little difference between a pure in-memory database
created by ":memory:" and a temporary database created by an empty filename.
The sole difference is that a ":memory:" database must remain in memory
at all times whereas parts of a temporary database might be flushed to
disk if database becomes large or if SQLite comes under memory pressure.</p>
<p>The previous paragraphs describe the behavior of temporary databases
under the default SQLite configuration. An application can use the
[temp_store pragma] and the [SQLITE_TEMP_STORE] compile-time parameter to
force temporary databases to behave as pure in-memory databases, if desired.
</p>