A better way to create test data? - Proposal/Proof of concept

[bkleinen]

As I work quite a lot with scripts based on things.py, I've created a better way to create test data:

https://github.com/bkleinen/things-py-testfixture

Let me know what you think of it and if I should put more work into it and / or create a pull request.

As i use some approaches that do not match the things.py style, as using pytest as testframework and introducing a bit more than just a forth source file and a second class, there may be some need for discussion
on how to integrate it in the api.

[mikez]

@bkleinen Thank you for sharing the proof of concept.

One recurring friction for contributors has been the need to swap out their current Things.app database with the test one (and back again). Thus, I was particularly concerned reading "I wasn't able to restore my db backup after following [the instructions]." Did you ever find out what the cloud restoration did differently?

If we had an easy way to modify the test database through the UI – without needing to backup our own private database – that would make testing significantly easier I think.

Intuitively, I like your idea of storing the raw SQLite SQL dump (i.e. the raw list of SQL statements needed to recreate the database). It would be helpful for diffing the test database git log and also simple edits. The downside is that contributor edits that don't modify the test database directly through the UI become more fragile... i.e., the contributor might miss to edit certain things in the SQL, or the Things.app would not accept that modified file for various reasons which the contributor won't detect as they never try to open it with the app. Another downside, as you already noted, is the maintenance burden... for every minor SQL schema change we might need to rethink the entire database creation setup. That's why I like @AlexanderWillner's elegant testing approach of making edits directly in the UI and testing off of a real working .sqlite file — rather than trying to reverse-engineer and keep recreating the Things.app to-do creation process.

[bkleinen]

The downside is that contributor edits that don't modify the test database directly through the UI become more fragile
hm. I think it's quite to the contrary. Proper test fixtures recreate the test data for every run, putting only the data into the db the test needs directly. Thus every test or every test suite can have it's own setup, resulting in more robust tests and easy test data creation/extension. As for creating test data using the ui have a look at the compare with the makefile - you make the change in the ui and get the sql statements via diffing the dumps. As we never actually write data to the real database, I don't see why the generated test db needs to be opened with the things app. It's also quite easy to use the hand-crafted test db in the old tests and use different fixtures for other tests.

I don't see why a modified db wouldn't open, at least when using the exact sql statements taken from the diffs.
But as said, I see no need for that.
As for why it worked from the cloud - culturedcode seems to have a proprietary sync mechanism, I assume no db file swapping involved. maybe it would have worked if I swapped more of the dir tree (I'm positive that three years ago swapping main.sqlite was sufficient).

is the maintenance burden... for every minor SQL schema change we might need to rethink the entire database creation setup.

• if the change affects things.py, changes will be needed, otherwise (like if the change in the further obscure part of the ridiculous 41 fields in TMTask) the test databases simply will not need to be updated.

• ps: also note that with using the temporary database and explicit fixtures, the sql dumps that are the basis for this will never be changed by the tests, as the fixtures load the needed data only into the temporary test databases.

[mikez]

I'm positive that three years ago swapping main.sqlite was sufficient

The last time I checked was about a year ago. This sounds to me like a serious issue if a simple swapping of the database doesn't work anymore. By extension, does that mean creating a test database from a SQL dump and opening that doesn't work either?

maintenance cost [...]

Here's what I've understood of your approach... If Things.app (the software) and its database schema doesn't change, then having a custom create_task or recreating a database from SQL sounds like a great approach. However, if the database schema changes slightly (including if one of those obscure fields gets added or modified), then the entire database creation process would need to change too, no? This makes it high maintenance for me... vs only caring about specific columns.

I don't see why a modified db wouldn't open, at least when using the exact sql statements taken from the diffs.

Example scenario: The Things test database is at version 25. User does a diff with their Things database which as at version 26 (unbeknownst to them). They insert the SQL diff. Tests pass fine. However, version 25 and version 26 might represent that specific field differently. Had the user opened the test database themselves, it would have converted it to version 26 first.

[bkleinen]

I see. I guess I did not sufficiently consider the fact that you do not have control over the database.

(But, as I said, the current dump/copy would not be changed by /for any new test case, test data would
only be added to temporary test databases which are discarded after each test run)

I gave it some more thought, still think that SQLAlchemy would introduce too much complexity, and
database.py is in fact a kind of ORM, or at least dict-from-relational mapping -

so, if the test data creation would carefully mirror what database.py does, I think the maintenance could be reduced to cases where

• the change affects the pythonapi, then database.py needs to be adapted anyway and the testcases should just import these changes
• new/other default values need to be added to a table. if carefully designed, this would only be in one place.

I personally like the latter better than the current situation, as it would be a matter of minutes to add new test data (like for instance assuring that the status is actually picked up correctly - there is no test for that). (update: but head of main has the necessary test data, I've just looked at the data in the released version before)
Also, after creating new test data, no old tests need to be adapted to reflect the changes in the global db. (as is currently done in 20 lines on top of the tests, see below).

I'll add an example for a new test for the status mapping next. The existing tests can stay as they are.

I'm not sure though if I like my first attempt to extract the mapping information (like 0 = 'incomplete for status)
and consolidate it in one place. here's a pr just to my own repo to see all changes: https://github.com/bkleinen/things.py/pull/1/files

lines in https://github.com/thingsapi/things.py/blob/main/tests/test_things.py to ease test updates after
adding new test data:

# AW: to be continued, helps updating the test expectations when modifying the DB
HEADINGS = 3
INBOX = 2
TRASHED_TODOS = 2
TRASHED_PROJECTS = 1
TRASHED_CANCELLED = 1
TRASHED_COMPLETED = 1
TRASHED_PROJECT_TODOS = 1
TRASHED_PROJECT_TRASHED_TODOS = 1
TRASHED = (
    TRASHED_TODOS
    + TRASHED_PROJECTS
    + TRASHED_CANCELLED
    + TRASHED_COMPLETED
    + TRASHED_PROJECT_TRASHED_TODOS
)
PROJECTS = 4
UPCOMING = 1
DEADLINE_PAST = 3
DEADLINE_FUTURE = 1
DEADLINE = DEADLINE_PAST + DEADLINE_FUTURE
TODAY_PROJECTS = 1
TODAY_TASKS = 4
TODAY = TODAY_PROJECTS + TODAY_TASKS

[mikez]

To clarify: in your approach, if the Things.app Database changes from version 25 to version 26... would we need to update the test database creation process? (Assuming database.py still works with version 26 and needs no update. Also, to clarify, our database.py only needs to know about a few columns, not all of them, this makes it easier to maintain.)