Write sqlite3 in-memory database into file using clsql

Question

As the question indicates. I created an in-memory database using ":memory:" and clsql:with-database to increase write/insert-query performance. But in the end I do want to have a permanent copy of the filled database on my hard drive.

It should look something like this:

(clsql:with-database (db (":memory:") :database-type :sqlite3)
  ;;entering db-scheme
  ;;entering a bunch of data
  (magically-write-database-to-file db file-path))

How can I achieve this?

score 4 · Accepted Answer · answered Dec 04 '14 at 09:04

4

If you do not care about data consistency before the database creation has finished, just use a normal database file and configure it to disable transactions and disk synchronization:

(execute-command "PRAGMA journal_mode = OFF")
(execute-command "PRAGMA synchronous = OFF")

answered Dec 04 '14 at 09:04

CL.

173,858
17
217
259

that significantly increased the write/insert speed. I'd be curious about an explanation though. – Sim Dec 04 '14 at 10:47
`synchronous` ensures that data has been written to disk when the transaction completes. The journal is needed to roll back a failed or interrupted transaction. Without these, interrupting or aborting any database write will leave you with a corrupted database. – CL. Dec 04 '14 at 19:23

score 1 · Answer 2 · answered Dec 04 '14 at 02:49

I think that you just need to create the tables with create-view-from-class, then call update-records-from-instance on your objects.

I am not sure, though, whether the creation of an explicit in-memory database first really makes sense. You could just create a collection of objects first, then put them into the database with update-records-from-instance in one go. The "view classes" of CLSQL are really just ordinary classes with some information about how to save/load them. There is no magic going on when you just change the objects without saving.

Write sqlite3 in-memory database into file using clsql

2 Answers2