If the project involves spending 6 months writing code involving navigating graphs, then SQLite probably isn't the right choice. If it's one or two queries, that's a different situation
Sure, but (and I am apologetic to be labouring on this point) if you have a small one-off blob of data that needs to be processed as a graph, SQL is the worst language to do the processing and a database is the worst place to be doing it. Get it into memory, use a programming language that supports recursive functions.
This is the basic argument here. If the situation is delicate enough we need the database to be doing graph operations, why would we pick SQLite? If it isn't, why would we pick SQL over Python?
I don't think it is bad that SQLite supports this, just ... what are the circumstances where this is a good idea? Is there a reason to do graph-style algorithms in SQL?
The problem that prompted me to make this extension to SQLite was a web-page request, so it needs to be processed with low latency. I initially tried loading the graph into memory and processing it that way. But there are approx 100K nodes in the graph, only a few dozen of which are relevant to the answer. It took a lot of time to pull in 100K nodes from disk. Running the query entirely in SQL is not only much less code to write, debug, and maintain, it is also much faster.
> SQL is the worst language to do the processing and a database is the worst place to be doing it. ... Is there a reason to do graph-style algorithms in SQL?
I don't think it's such a far-off fit from the relational database problem. Any time you have a table with a relation to itself, there's the potential to do graph-style algorithms on that data.
> If the situation is delicate enough we need the database to be doing graph operations, why would we pick SQLite?
Perhaps it has nothing to do with the situation being "delicate" and it is just a simple matter of it being less work and less lines of code to use a graph-style query in SQL, rather than re-implementing the graph algorithms in your application code or having to bring in an entirely new database system just to process one query.
I agree. There is nothing wrong relationally with self-referencing tables. Support for traversing such data models may have been poor in the past but with the support added, what is the problem?
I write SQL for a living and am time woodworker (not good at all). Sometimes I get pieces of wood cut by industrial machines at the shop and sometimes I use my manual saw. I don't have money or space to set up a professional wood cutting machine in my workshop. Hand held saw not the best for cutting wood but sometimes it just makes sense. You are right, graph database things need to be done in a graph database but speaking for myself I have never set up or used a graph database. It is easier (not right) for me to learn recursive statements than to set up and learn to use a graph database.
It's an unlikely comparison in the first place; who is considering, on one hand, using SQLite right on the client device, and on the other, maybe spinning up a graph database that will almost certainly not just run on the client device? I submit that it's next to nobody. You can run SQLite on a server, but nearly nobody does that either (and not without reason). The idea that recursive CTEs are some sort of arcane technique that's impossible to debug is also not my experience with them at all.
