5 ms·
SQLite is a relational database. Wouldn't it be a better fit to use a graph-database as the backend for anything "web"? The idea is good, that a web-page shoul
by galaxyLogic 5y ago
SQLite is a relational database. Wouldn't it be a better fit to use a graph-database as the backend for anything "web"?
The idea is good, that a web-page should be generated from some data somewhere. But "web" is much about not a single document but the links between the documents, which allow you to to represent a "semantic net". The data should be about the links between them. Now where is such a database? And how can it "sharded" into multiple databases running in thousands of locations on the internet?
- mikestaub 5y agoOriginTrail.io is using Arangodb.com
- Groxx 5y agoWhat is a graph database? A miserable little pile of joins. Though to be serious: what do you expect a graph database to provide that sqlite cannot / does not do efficiently?
- patates 5y agoTreating links as an entity. In a RDBMS, it is not possible to map a bidirectional relation semantically. It needs to have a direction (you can define it in both directions but then you have 2 relations). You then also need to duplicate link properties (in the join table) or normalize to yet another join table. That has been my pet peeve for a while, and can make it hard to define navigation. Totally not a deal-breaker though. I'd still use sqlite because I ### love it :)
- Groxx 5y agowhy would a direction be necessary? relation_id │ relatee_id ────────────┼─────────── 1 │ x 1 │ y voila, directionless relationships. you can even do N-way, just insert more rows with the same relation-id.
- patates 5y agoTo quote myself: > or normalize to yet another join table This is also semantically incorrect. There needs to be exactly 2 rows there. You cannot in a declarative way, prevent that if you use this approach. It's also a known dilemma, so we don't have to rediscover the wheel here.
- noctune 5y agoWhat if you have something like (relatee_a_id, relatee_b_id, [rest of relation fields ...]) with the constraint that relatee_a_id < relatee_b_id? Granted, it might not be very nice to query.
- patates 5y agoWell, that is what I actually use most of the time, but to nitpick, it still is not declarative, and semantically a and b have no meaning. Just to be clear, I'm saying these are in 99% of the cases not deal breakers but they can be if all you want to do is map graphs (and follow chains through many members), as there is also a significant performance cost to all this abstraction. What you described is, IIRC, also what some file-based graph DBs do in the background with sqlite and abstract away from you (I've seen a couple custom ones in some projects). Left with a RDBMS, you just need to do it yourself and analyze how much it costs.
- Groxx 5y agoCustom indexes or insert triggers can both guarantee that, both in a declarative way (in that they prevent normal "blind" inserts. I'm not entirely sure what you mean by declarative here tbh). Also I have no idea why a separate table is a negative / constraint of some kind. It's a natural way to model it.
- patates 5y ago> no idea why a separate table is a negative When you are following a chain of relations (a vector), you end up losing a lot of performance. > not entirely sure what you mean by declarative Guaranteed by the database semantics, not imperative code even when it runs on the DB. I don't want to come off sounding like I think it's a bad idea to map graphs on RDBMSs, it's just inconvenient sometimes. Usually not enough to complicate your setup, adding another database, but rarely it is. I'm not sure though about the suggested use case here, I really don't know it enough to make a suggestion. In such cases I always pick sqlite or postgres (depending on the client model) though. You can't be too wrong with those, and you are most of the times right :) Not to mention the crazy extensibility of pg and the stuff you can do with it.
- deleted 5y ago[deleted]
- dng88 5y ago
- wheels 5y agoYou can reasonably model graphs in a column-oriented database, but traditional SQL models tend to be horrible performance-wise, because most graph algorithms need fast traversal of edges, and doing a lot of recursive lookups in SQL is impractically slow. It's not that you can't model it, it's just that performance is terrible. For a graph database to be efficient, you need a high degree of locality for edge information (ideally a vector you can simply read out). (Note: I've actually written a graph database from scratch, for exactly these reasons.)
- ahevia 5y agoDo you have examples of Graph DBs that could embedded in a static site as easily as SQLite?
- oever 5y agoModelling transitive edges can be done in Sqlite with recursive common table expressions. The performance for that is probably less than for graphs databases, but Sqlite has many other advantages over graph databases.
- wheels 5y agoUhm, that's basically what I said? Aside from the "other advantages" -- which, yeah, sure. Socket sets have other advantages than hammers, but they're not very good at pounding in nails. You can do it, but it'll suck at it. Also, in this case, "probably less" is "multiple orders of magnitude slower".
- oever 5y agoThe locality that you want for performance is available as row values in sqlite. Sqlite seems to have the parts needed to build a graph db.
- deleted 5y ago[deleted]
- 5y ago
- j-pb 5y agoA whole pile of joins, that naturally arises if you want to combine data from a ton of domains. Triple stores are essentially relational databases in 6th normal form. But relational databases like SQLite don't have good join algorithms to deal with this (they do pairwise joins instead of the worst case optimal ones like Leapfrog-Triejoin or Tetris). They also lack good interfaces for so many joins, you want something more declarative like Datalog/SparQL/GraphQL, than to explicitly write out every join.
- jinjin2 5y agoIt would be interesting to see it implemented on an object database like Realm, rather than on being based on SQL. Seems like it would be a much better fit.
- TuringTest 5y agoMaybe some graph-like layer over SQLite like graphlite would help? https://graphlite.readthedocs.io/en/latest/ https://graphlite.readthedocs.io/en/latest/
- deleted 5y ago[deleted]
- sktrdie 5y agoTake a look at Linked Data Fragments https://linkeddatafragments.org/ https://linkeddatafragments.org/
- galaxyLogic 5y agoInteresting. That looks much like what the proposed idea (using SQLite) is. Use both clients and servers. No?