-
Notifications
You must be signed in to change notification settings - Fork 1
sqlite
:sql:
- https://dgl.cx/2020/06/sqlite-json-support
- https://antonz.org/generated-columns/
- https://antonz.org/sqlite-is-not-a-toy-database/
- https://blog.lmorchard.com/2023/05/12/toots-in-sqlite/
- https://xeiaso.net/blog/sqlite-json-munge-2022-01-04/ Bashing JSON into Shape with SQLite
The implementation seems to be creating node tables and an edge table that is basically a join table with (optionally) some extra columns
Most basic approach is self joins
Schema:
CREATE TABLE users (
user_id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE relationships (
from_user INTEGER,
to_user INTEGER,
relationship_type TEXT CHECK(relationship_type IN (’FOLLOW’, ‘FRIEND’)),
FOREIGN KEY (from_user) REFERENCES users(user_id),
FOREIGN KEY (to_user) REFERENCES users(user_id)
);Query:
SELECT u2.name AS friend_name
FROM relationships r
JOIN users u1 ON u1.user_id = r.from_user
JOIN users u2 ON u2.user_id = r.to_user
WHERE r.relationship_type = ‘FRIEND’ AND u1.name = ‘Alice’; Slightly more advanced: using recursive CTE to iterate through relationships
https://sqlite.org/lang_with.html
WITH RECURSIVE connections(user_id, depth) AS (
SELECT to_user, 1 FROM relationships
WHERE from_user = (SELECT user_id FROM users WHERE name = ‘Alice’)
UNION ALL
SELECT r.to_user, c.depth + 1
FROM relationships r
JOIN connections c ON r.from_user = c.user_id
)
SELECT u.name, c.depth
FROM connections c
JOIN users u ON c.user_id = u.user_id;https://www.sqliteforum.com/p/sqlite-and-graph-hybrids
SQLite is a tcl extension: https://www.tcl.tk/community/tcl2017/assets/talk93/Paper.html
- https://sqlitebrowser.org/ - what i have used in the past
- https://sqlitestudio.pl/ - something newer and nicer?
- https://www.visidata.org/ - tui data swiss army knife (learning curve)
- https://litecli.com/ - A command line interface for SQLite with auto-completion and syntax highlighting.
sqlar: SQLite Archive, like zip or tar
https://www.sqlite.org/sqlar.html
It's cool because you can compress and uncompress data in your table, and query your archive to do all kinds of analytics on its contents
The SQLite project has been pretty clear on its goal. It is not to be a client/server database alternative, it's to be an ad-hoc application data file alternative. But it is software and with a bit of hard work, you can make software do amazing things.
Siren Call of SQLite on the Server : https://pid1.dev/posts/siren-call-of-sqlite-on-the-server/
Simple declarative schema migration for SQLite : https://david.rothlis.net/declarative-schema-migration-for-sqlite/
SQLite (read-only) running on a static site https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-github-pages/
Replication: