Unable to modify database after copying from taskwarrior

[Gordi42]

Description

When copying the taskchampion.sqlite3 database from taskwarrior on Linux to taskchamp on iOS, taskchamp is unable to modify the database. Existing tasks cannot be updated, and new tasks cannot be created. Taskchamp can still read the database, but any attempt to modify it results in an error without further details.

Steps to Reproduce

1. Create a new database in taskchamp and add a task.
2. Copy the taskchampion.sqlite3 file from the iCloud drive to the $HOME/.task/ folder on a Linux machine.
3. Run taskwarrior, e.g. task next
4. Copy the updated taskchampion.sqlite3 file back to the iCloud drive.
5. Open taskchamp and attempt to add a new task.

Observed Behavior

• taskchamp shows an error when trying to add or modify tasks.
• the database is readable in taskchamp but not writable.

Environment

• taskchamp App Version: 1.2
• taskwarrior Version: 3.3.0
• iOS Version: 18.1.1

Additional Notes

I don't think that this is a linux specific problem, but don't have a Mac at hand to test. Maybe this is caused by a version conflict? It would be nice to make this work, as this would allow for automated syncing with linux by using the new iCloud Drive support in rclone.

[marriagav]

Interesting, this to me seems like a permissions issue rather than version (being able to read but not write).

I cannot reproduce this but I do not have a Linux system to try. I will try to use the same version you specified above and see if it causes any issues.

Will update when I have the chance. Other than that is there any other weird behavior? Are you unable to modify the db from Taskwarrior or only Taskchamp?

Just to be sure I understand, the app works fine until you modify a task from taskwarrior and then that's when the issue starts happening?

If that's the case, did you update the task database file path in the .taskrc file to point towards the iCloud location before running the taskwarrior command?

[Gordi42]

I don't think that this is a permission issue. I just gave the file 777 before copying it back to the icloud, but the problem persists.

Here is a log of what I am doing:

❯ cp ~/icloud/taskchamp/task/taskchampion.sqlite3 ~/.task/taskchampion.sqlite3
❯ task next

ID Age  Description             Urg 
 1 6min new task from taskchamp    0

1 task
❯ cp ~/.task/taskchampion.sqlite3 ~/icloud/taskchamp/task/taskchampion.sqlite3

Taskwarrior itself seems to work fine. In the above example i have not modified any task with taskwarrior, but only inspected the tasks. When modifying tasks or adding new tasks, these modifications will be visible in the taskcamp app but the problems in the app are the same: After copying the file back to the icloud and starting the taskchamp app, I cannot modify or add tasks to the database, e.g. marking a task as done would result in an error with the message Task failed to update. Please try again. and adding a new task result in an error with message Task failed to create. Please try again.

When running the taskchamp app with the new updated file, the sqlite file seems to be opened in WAL mode, as a write ahead log file, and a shared memory file are created in the icloud drive:

❯ ls ~/icloud/taskchamp/task
taskchampion.sqlite3  taskchampion.sqlite3-shm  taskchampion.sqlite3-wal

I also did a comparison of the sqlite file before and after running taskwarrior:
Before running taskwarrior:

❯ sqlite3 ~/icloud/taskchamp/task/taskchampion.sqlite3
SQLite version 3.45.1 2024-01-30 16:01:20
Enter ".help" for usage hints.
sqlite> .tables
operations   sync_meta    tasks        working_set
sqlite> .schema
CREATE TABLE operations (id INTEGER PRIMARY KEY AUTOINCREMENT, data STRING);
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE sync_meta (key STRING PRIMARY KEY, value STRING);
CREATE TABLE tasks (uuid STRING PRIMARY KEY, data STRING);
CREATE TABLE working_set (id INTEGER PRIMARY KEY, uuid STRING);
sqlite> .indices
sqlite_autoindex_sync_meta_1  sqlite_autoindex_tasks_1
sqlite> SELECT * FROM tasks;
560576d9-614a-44d3-8a2e-5c312911935e|{"entry":"1735808708.0","status":"pending","description":"new task from taskchamp","modified":"1735808708.0"}

After running taskwarrior:

❯ sqlite3 ~/.task/taskchampion.sqlite3
SQLite version 3.45.1 2024-01-30 16:01:20
Enter ".help" for usage hints.
sqlite> .tables
operations   sync_meta    tasks        version      working_set
sqlite> .schema
CREATE TABLE operations (id INTEGER PRIMARY KEY AUTOINCREMENT, data STRING, uuid GENERATED ALWAYS AS (
                coalesce(json_extract(data, "$.Update.uuid"),
                         json_extract(data, "$.Create.uuid"),
                         json_extract(data, "$.Delete.uuid"))) VIRTUAL, synced bool DEFAULT false);
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE sync_meta (key STRING PRIMARY KEY, value STRING);
CREATE TABLE tasks (uuid STRING PRIMARY KEY, data STRING);
CREATE TABLE working_set (id INTEGER PRIMARY KEY, uuid STRING);
CREATE INDEX operations_by_uuid ON operations (uuid);
CREATE INDEX operations_by_synced ON operations (synced);
CREATE TABLE version (
                singleton INTEGER PRIMARY KEY CHECK (singleton = 0),
                major INTEGER,
                minor INTEGER);
sqlite> .indices
operations_by_synced          sqlite_autoindex_sync_meta_1
operations_by_uuid            sqlite_autoindex_tasks_1
sqlite> SELECT * FROM tasks;
560576d9-614a-44d3-8a2e-5c312911935e|{"entry":"1735808708.0","status":"pending","description":"new task from taskchamp","modified":"1735808708.0"}
sqlite> SELECT * FROM version;
0|0|1

So there are quite a few differences, I guess that the problems are caused by the different function signatures in the schemas. Is this signature difference on purpose, or a version problem?

I did not update the .taskrc app. But I think that this shouldn't matter as long as I do the copying manually?

[marriagav]

After updating to newest taskwarrior version on MacOS (taskwarrior 3.3.0 and ios 18.1.1) I am experiencing issues as well. You are right that it seems like they introduced some changes to the sqlite file, thanks for pointing that out.

I will investigate this further when I find some time, in the meantime, would you be able to try running with a previous taskwarrior 3 version? 3.1.0 seemed to be working well for me.

[Gordi42]

Yes, it works with version 3.1.0 :)

[marriagav]

That's great to here :) I'll re-open this as this is something I should definitely fix.

It's great to hear that someone made it work for Linux too! Did you do anything special other than using rclone?

[Gordi42]

I am using the latest version of rclone (v1.69.0-DEV). The idea would be to mount iCloud with rclone like this:

mkdir ~/icloud
rclone mount icloud: ~/icloud --vfs-cache-mode full --daemon

Afterward, you could, for example, point the Taskwarrior data folder to the iCloud mount by modifying the following line in .taskrc:

data.location=~/icloud/taskchamp/tasks/

Alternatively, you could create a script to synchronize the Taskwarrior data folder with the iCloud mount.

The issue is that, as of now, rclone does not seem to support uploading files to app-folders in iCloud Drive (e.g., /taskchamp). I have created an issue in the rclone repository regarding this (rclone/rclone#8285).

As a workaround, you could create another sync folder in iCloud Drive and manually move the data file from the sync folder to the correct location using the Files app. However, this is not an ideal solution, especially if, like me, you frequently switch between devices.

I am not familiar with Swift and do not know how easy this would be to implement, but perhaps the app could include an option to manually specify the path to the data file. This could circumvent the issue entirely.