If you open a CSV and then install a plugin the CSV table vanishes

[simonw]

This is because opening a CSV imports into the temporary in-memory database, and installing a plugin restarts the server.

Could maybe keep track of which CSVs have been opened and re-import them after a restart of the server within the current application session?

Might not be worth fixing this.

[simonw]

This should definitely be fixed. The problem is, how to do it?

The "Open CSV" option loads the CSV into memory. That memory then gets wiped when the server restarts after the plugin is installed.

Three options I can think of:

1. Keep track of the file paths to the CSVs that were opened, and re-open them when the server restarts. Downside here is what if the user has deleted or moved the CSV in the meantime?
2. Figure out a way to dump the in-memory database to a temporary file and then re-open it again when the server restarts
3. Switch temporary to be an on-disk database in a temporary directory as opposed to an in-memory database

[simonw]

Option 2 looks like it might be a good one, thanks to the VACUUM INTO command. This isn't in older versions of SQLite but Datasette Desktop always has SQLite 3.36.0 thanks to https://github.com/indygreg/python-build-standalone/blob/1e58aaf6f1b82a32fabb4bd0ea6535af6afe2718/pythonbuild/downloads.py#L309-L319

Here's a proof of concept:

% ~/.datasette-app/venv/bin/python
Python 3.9.6 (default, Jul 24 2021, 22:49:46) 
[Clang 12.0.1 ] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> conn = sqlite3.connect(":memory:")
>>> conn.execute("select sqlite_version()").fetchall()
[('3.36.0',)]
>>> conn.execute("create table foo (id integer primary key)")
<sqlite3.Cursor object at 0x10f5b1ce0>
>>> conn.execute("insert into foo (id) values (1)")
<sqlite3.Cursor object at 0x10f5b17a0>
>>> conn.execute("insert into foo (id) values (2)")
<sqlite3.Cursor object at 0x10f5b1ce0>
>>> conn.execute("insert into foo (id) values (3)")
<sqlite3.Cursor object at 0x10f5b17a0>
>>> conn.execute("select * from foo").fetchall()
[(1,), (2,), (3,)]
>>> conn.execute("vacuum into '/tmp/backup.db'")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: cannot VACUUM from within a transaction
>>> conn.isolation_level = None
>>> conn.execute("vacuum into '/tmp/backup.db'")
<sqlite3.Cursor object at 0x10f5b1c00>

Having run that I have a file in /tmp/backup.db which looks like this:

~ % sqlite3 /tmp/backup.db .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE foo (id integer primary key);
INSERT INTO foo VALUES(1);
INSERT INTO foo VALUES(2);
INSERT INTO foo VALUES(3);
COMMIT;

[simonw]

I found that fix for sqlite3.OperationalError: cannot VACUUM from within a transaction in ghaering/pysqlite#109 (comment)

[simonw]

But... how would I restore that saved temporary database file back INTO the in-memory database? Could I use vacuum into in the opposite direction?

[simonw]

https://www.sqlite.org/lang_vacuum.html says:

The argument to INTO can be a URI filename if URI filenames are enabled. URL filenames are enabled if any of the following are true:

• The SQLite library was compiled with -DSQLITE_USE_URI=1.
• The sqlite3_config(SQLITE_CONFIG_URI,1) interfaces was invoked at start-time.
• The database connection that is running the VACUUM INTO statement was originally opened using the SQLITE_OPEN_URI flag.

Datasette in https://github.com/simonw/datasette/blob/d57ab156b35ec642549fb69d08279850065027d2/datasette/database.py#L72-L79 does this:

        if self.memory_name:
            uri = "file:{}?mode=memory&cache=shared".format(self.memory_name)
            conn = sqlite3.connect(
                uri,
                uri=True,
                check_same_thread=False,
            )

So it should be possible to use VACUUM INTO to restore that backed up database file to the in-memory temporary database.

[simonw]

Yes, this works:

>>> import sqlite3
>>> backup_db = sqlite3.connect("/tmp/backup.db", uri=True)
>>> conn = sqlite3.connect("file:temporary?mode=memory&cache=shared", uri=True)
>>> backup_db.execute("vacuum into 'file:temporary?mode=memory&cache=shared'")
<sqlite3.Cursor object at 0x11099af80>
>>> conn.execute("select * from foo").fetchall()
[(1,), (2,), (3,)]

The order mattered - I had to open conn before I tried executing vacuum into.

The uri=True argument when opening backup_db was also necessary.

[simonw]

This is going to need some new API endpoints in datasette-app-support:

• /-/dump-temporary-to-file
• /-/restore-temporary-from-file

I'll leave it to the Electron app to define the file - I thought about using tempfile in Python but I don't think that will survive the server restart.