SQLite DB generated by TaxaDB must be stored in writable directories

[tbigot]

Hi,

It seems it occurred with recent taxadb, but the databases are created in WAL mode. Hence, there is no way to open them if they are located in a read-only directory.

example:

$ sqlite3 taxadb_gb.sqlite 
SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
sqlite> .schema
Error: unable to open database file
sqlite>
$ chmod +w .
$ sqlite3 taxadb_gb.sqlite 
SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE "taxa" ("ncbi_taxid" INTEGER NOT NULL PRIMARY KEY, "parent_taxid" INTEGER NOT NULL, "tax_name" VARCHAR(255) NOT NULL, "lineage_level" VARCHAR(255) NOT NULL);
CREATE TABLE "accession" ("id" INTEGER NOT NULL PRIMARY KEY, "taxid_id" INTEGER NOT NULL, "accession" VARCHAR(255) NOT NULL, FOREIGN KEY ("taxid_id") REFERENCES "taxa" ("ncbi_taxid"));
CREATE INDEX "accession_taxid_id" ON "accession" ("taxid_id");
CREATE UNIQUE INDEX "accession_accession" ON "accession" ("accession");

i have to delete the embedded journal in write mode:

PRAGMA journal_mode=DELETE;

$ chmod -w .
$ sqlite3 taxadb_gb.sqlite 
SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE "taxa" ("ncbi_taxid" INTEGER NOT NULL PRIMARY KEY, "parent_taxid" INTEGER NOT NULL, "tax_name" VARCHAR(255) NOT NULL, "lineage_level" VARCHAR(255) NOT NULL);
CREATE TABLE "accession" ("id" INTEGER NOT NULL PRIMARY KEY, "taxid_id" INTEGER NOT NULL, "accession" VARCHAR(255) NOT NULL, FOREIGN KEY ("taxid_id") REFERENCES "taxa" ("ncbi_taxid"));
CREATE INDEX "accession_taxid_id" ON "accession" ("taxid_id");
CREATE UNIQUE INDEX "accession_accession" ON "accession" ("accession");
sqlite>

I think, according to the regular usage of a taxadb database (read-only), the WAL mode should not be used.

Reference:
https://www.sqlite.org/wal.html#readonly

[tbigot]

If I remove the WAL mode, I can access the database via sqlite3 tool, even if the database file is not writable.

But it’s not possible from Python using taxadb: I get the hollowing error:

Can't create database object: attempt to write a readonly database

[tbigot]

It seems to be caused by

return pw.SqliteDatabase(self.get('dbname'),
                                     pragmas={'journal_mode': 'wal',
                                              'cache_size': -1 * 64000})

here:

taxadb/taxadb/schema.py

Line 160 in 4484b04

return pw.SqliteDatabase(self.get('dbname'),

Could there be an option to deactivate wal mode?