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>