Support opening Replica in read-only mode

[djmitche]

Lots of access to the Replica is only reading, not writing. SQLite can be quite a bit faster in that circumstance. Also, SQLite writes to the database itself when opened in read-write mode (GothenburgBitFactory/taskwarrior#3418).

Let's allow opening a Replica in read-only mode.

[felixschurk]

Am I right in the understanding that following code opens the database?

taskchampion/taskchampion/src/storage/sqlite.rs

Lines 80 to 155 in f4e1679

	impl SqliteStorage {
	pub fn new<P: AsRef<Path>>(directory: P, create_if_missing: bool) -> Result<SqliteStorage> {
	if create_if_missing {
	// Ensure parent folder exists
	std::fs::create_dir_all(&directory)?;
	}
	// Open (or create) database
	let db_file = directory.as_ref().join("taskchampion.sqlite3");
	let mut flags = OpenFlags::default();
	// default contains SQLITE_OPEN_CREATE, so remove it if we are not to
	// create a DB when missing.
	if !create_if_missing {
	flags.remove(OpenFlags::SQLITE_OPEN_CREATE);
	}
	let con = Connection::open_with_flags(db_file, flags)?;
	// Initialize database
	con.query_row("PRAGMA journal_mode=WAL", [], |_row| Ok(()))
	.context("Setting journal_mode=WAL")?;
	let create_tables = vec![
	"CREATE TABLE IF NOT EXISTS operations (id INTEGER PRIMARY KEY AUTOINCREMENT, data STRING);",
	"CREATE TABLE IF NOT EXISTS sync_meta (key STRING PRIMARY KEY, value STRING);",
	"CREATE TABLE IF NOT EXISTS tasks (uuid STRING PRIMARY KEY, data STRING);",
	"CREATE TABLE IF NOT EXISTS working_set (id INTEGER PRIMARY KEY, uuid STRING);",
	];
	for q in create_tables {
	con.execute(q, []).context("Creating table")?;
	}
	// -- At this point the DB schema is that of TaskChampion 0.8.0.
	// Check for and add the `operations.uuid` column.
	if !Self::has_column(&con, "operations", "uuid")? {
	con.execute(
	r#"ALTER TABLE operations ADD COLUMN uuid GENERATED ALWAYS AS (
	coalesce(json_extract(data, "$.Update.uuid"),
	json_extract(data, "$.Create.uuid"),
	json_extract(data, "$.Delete.uuid"))) VIRTUAL"#,
	[],
	)
	.context("Adding operations.uuid")?;
	con.execute("CREATE INDEX operations_by_uuid ON operations (uuid)", [])
	.context("Creating operations_by_uuid")?;
	}
	if !Self::has_column(&con, "operations", "synced")? {
	con.execute(
	"ALTER TABLE operations ADD COLUMN synced bool DEFAULT false",
	[],
	)
	.context("Adding operations.synced")?;
	con.execute(
	"CREATE INDEX operations_by_synced ON operations (synced)",
	[],
	)
	.context("Creating operations_by_synced")?;
	}
	Ok(SqliteStorage { con })
	}
	fn has_column(con: &Connection, table: &str, column: &str) -> Result<bool> {
	let res: u32 = con
	.query_row(
	"SELECT COUNT(*) AS c FROM pragma_table_xinfo(?) WHERE name=?",
	[table, column],
	|r| r.get(0),
	)
	.with_context(|| format!("Checking for {}.{}", table, column))?;
	Ok(res > 0)
	}
	}

How is the connection process between taskwarrior and taskchampion for opening the database? Because for my understanding one could open all the task report commands the DB in read-only. But I do not really understand how the behavior is implemented.

[djmitche]

Yes, and in particular Connection::open_with_flags is the call to rusqlite.

To fix, I think we would add a boolean to StorageConfig::OnDisk, then set that value via the C++ bridge functions.

A possible tricky bit will be in Taskwarrior: sometimes it writes during report commands, when it applies garbage collection. I think there's already a flag for that in the Command structure, so probably that could be handled correctly, but I haven't looked.

[djmitche]

...but of course, changing StorageConfig is a semver-breaking change, which would put us right onto 2.0.0! That's not the end of the world, but I'll think about whether there's another option.