Write database open in memory to disk (SQLite)

[jcma]

Hello everybody,

I am working with SQLite databases in memory (it is faster to work this way), but I need to save the database on disk so as not to lose data, and I have a question:

Is it possible to write a database created in memory to disk?

Thanks and regards,

[ptrex]

@jcma

I would not advise to work like this.

Because when the power drops you will lose all data.

Better way is to create a DB on the disk. And allocate more memory space to do the transactions in.

Look for :

PRAGMA default_cache_size; 
PRAGMA default_cache_size = Number-of-pages;

Query or change the maximum number of database disk pages that SQLite will hold in memory at once. Each page uses 1K on disk and about 1.5K in memory. This pragma works like the cache_size pragma with the additional feature that it changes the cache size persistently. With this pragma, you can set the cache size once and that setting is retained and reused every time you reopen the database.

And

PRAGMA page_size; 
PRAGMA page_size = bytes;

Query or set the page-size of the database. The page-size may only be set if the database has not yet been created. The page size must be a power of two greater than or equal to 512 and less than or equal to 8192. The upper limit may be modified by setting the value of macro SQLITE_MAX_PAGE_SIZE during compilation. The maximum upper bound is 32768.

And

PRAGMA temp_store; 
PRAGMA temp_store = DEFAULT; (0) 
PRAGMA temp_store = FILE; (1) 
PRAGMA temp_store = MEMORY; (2)

Query or change the setting of the "temp_store" parameter. When temp_store is DEFAULT (0), the compile-time C preprocessor macro TEMP_STORE is used to determine where temporary tables and indices are stored. When temp_store is MEMORY (2) temporary tables and indices are kept in memory. When temp_store is FILE (1) temporary tables and indices are stored in a file. The temp_store_directory pragma can be used to specify the directory containing this file. FILE is specified. When the temp_store setting is changed, all existing temporary tables, indices, triggers, and views are immediately deleted.

Regards

ptrex

[jcma]

@jcma

I would not advise to work like this.

Because when the power drops you will lose all data.

Better way is to create a DB on the disk. And allocate more memory space to do the transactions in.

Look for :

PRAGMA default_cache_size; 
PRAGMA default_cache_size = Number-of-pages;

Query or change the maximum number of database disk pages that SQLite will hold in memory at once. Each page uses 1K on disk and about 1.5K in memory. This pragma works like the cache_size pragma with the additional feature that it changes the cache size persistently. With this pragma, you can set the cache size once and that setting is retained and reused every time you reopen the database.

And

PRAGMA page_size; 
PRAGMA page_size = bytes;

Query or set the page-size of the database. The page-size may only be set if the database has not yet been created. The page size must be a power of two greater than or equal to 512 and less than or equal to 8192. The upper limit may be modified by setting the value of macro SQLITE_MAX_PAGE_SIZE during compilation. The maximum upper bound is 32768.

And

PRAGMA temp_store; 
PRAGMA temp_store = DEFAULT; (0) 
PRAGMA temp_store = FILE; (1) 
PRAGMA temp_store = MEMORY; (2)

Query or change the setting of the "temp_store" parameter. When temp_store is DEFAULT (0), the compile-time C preprocessor macro TEMP_STORE is used to determine where temporary tables and indices are stored. When temp_store is MEMORY (2) temporary tables and indices are kept in memory. When temp_store is FILE (1) temporary tables and indices are stored in a file. The temp_store_directory pragma can be used to specify the directory containing this file. FILE is specified. When the temp_store setting is changed, all existing temporary tables, indices, triggers, and views are immediately deleted.

Regards

ptrex

Thanks for the answer, proves this way.

Greetings

[Confuzzled]

Doesn't closing the database flush it to disk?