Write sqlite3 in-memory database into file using clsql

181 views Asked by At

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?

2

There are 2 answers

2
CL. On BEST ANSWER

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")
0
Svante On

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.