Taking advantage of the sql backend.

[ryneeverett]

There was a great discussion between @thehunmonkgroup and @djmitche in the last community meeting which I think deserves a place for continued discussion. The context (my interpretation and paraphrasing):

• Currently, taskchampion stores tasks in basically a two column table with the uuid and the json blob of task data.
• Chad asked: Wouldn't it be more efficient if the task fields were stored in individual columns so that we could use WHERE clauses to filter task data.
• Dustin answered: There's no performance advantage to be gained there by taskwarrior, because the taskwarrior cli's filtering capabilities do not align with sql filtering capabilities.
• Dustin further elaborated that his position is that such changes to the storage backend need to serve a use case of an existing client. Since taskwarrior doesn't really have a use case for such a change, we're waiting for a use case to emerge from a future client.

[djmitche]

Thanks for capturing this! It was an interesting conversation!

That's a good summary of the situation. TaskChampion already has a fair bit of functionality that's not used by Taskwarrior (handling UDAs, tags, etc.) for similar reasons -- TW already has an implementation, with its own peculiarities, of that functionality. Re-implementing that precisely would be very difficult to do, and have a low pay-off. That was, in fact, the background for #372 -- TW needs that "low level" interface.

I also don't want to close down options for other storage backends -- maybe a web application would want to use MongoDB or Azure Tables or something like that? If that feature request arrived after we had selected a filtering API that wasn't a good match, that would be a shame.

Chad suggested that maybe a filtering approach could be developed where the storage backend only applies the filters it understands. That might be a good way to get started on this. For example, filtering by tags is probably pretty common and could be supported by several backends -- but maybe that Azure Tables backend could just say "nope" to all filtering requests.

BTW, it's Dustin, or just @djmitche is fine.

[ryneeverett]

BTW, it's Dustin, or just @djmitche is fine.

Wow, I must have been distracted! Hopefully that's the only error. Fixed in an edit.

[ryneeverett]

Chad suggested that maybe a filtering approach could be developed where the storage backend only applies the filters it understands. That might be a good way to get started on this.

An interesting aspect of this idea is that it suggests a way towards a more structured sql backend even if taskwarrior remains the only taskchampion client. Under this approach, use of certain filters may end up having a noticeable performance disparity over others, which could generate demand for taskwarrior to add alternative "sql-friendly" filtering capabilities. We end up in the same place and by the same rationale: storing tasks in tabular format to serve user demand and fix measurable performance issues.

[djmitche]

#403 is also about other storage backends, but brings up the important point of DependencyMap -- TC, in order to generate BLOCKING and BLOCKED virtual tags, must scan all (pending) tasks. This is also the sort of thing that a DB backend is pretty good at, although the way we store this data (in the form of dep_<uuid>:x key:value pairs) is probably not a DBMS's favorite.

It's worth noting that Taskwarrior doesn't use the DependencyMap, but implements essentially the same thing in C++, after loading pending tasks. So at the moment, even when it only wants one task, it's important that it load all pending tasks just to generate the correct virtual tags.