SQLite

• https://www.sqlite.org/cli.html (TODO 6)

$ sqlite3 :memory: # or just not pass any argument
$ sqlite3 foo.db 'SELECT * FROM events'
$ sqlite3 foo.db '.dump'

• https://exercism.org/tracks/sqlite
• https://www.sqlite.org/lang.html
• https://www.sqlite.org/lang_attach.html The ATTACH DATABASE statement adds another database file to the current database connection.
• JSON https://www.sqlite.org/json1.html
• wasm https://sqlite.org/wasm/doc/trunk/index.md
  • vanilla JS https://sqlite.org/wasm/doc/trunk/demo-123.md
  • persistence https://sqlite.org/wasm/doc/trunk/persistence.md
• Each table in the FROM clause of a query can use at most one index
  • After it uses one, it needs to SCAN each row for the other index
  • https://www.sqlite.org/optoverview.html#choosing_between_multiple_indexes

  • An index constraint in a WHERE can be escaped with (+) https://www.sqlite.org/optoverview.html#disqualifying_where_clause_terms_using_unary_

    create table ex2(x,y,z);
    create index ex2i1 on ex2(x);
    create index ex2i2 on ex2(y);
    select z
      from ex2
     where +x=5 and y=6;
    

• BLOBs are 35% faster than file system access https://www.sqlite.org/fasterthanfs.html

https://www.sqlite.org/changes.html

• column oriented https://github.com/dgllghr/stanchion
• common extensions https://github.com/nalgeon/sqlean/
  • crypto, fileio, ipaddr, math, regexp, stats, unicode, uuid
  • text: string functions
  • define: user defined functions
  • fuzzy: string matching and phonetics
  • vsv: csv files as virtual tables
• vector search https://github.com/asg017/sqlite-vss
• compression https://github.com/mlin/sqlite_zstd_vfs
• compression https://phiresky.github.io/blog/2022/sqlite-zstd/ https://github.com/phiresky/sqlite-zstd

• fork https://github.com/tursodatabase/libsql
  • (edge db) https://turso.tech/
  • (edge db) https://www.scylladb.com/
  • https://github.com/osquery/osquery
    • https://news.ycombinator.com/item?id=39501281 osquery is a cool project, with a lot of outstanding issues. It has a great deal of technical debt, including performance and security debts that don't receive adequate attention. It also has a huge user community around it, but only a handful of active recurring contributors and companies actually funding development on it (and, even then, the bulk of the development is feature work rather than debt burndown).
• ceph vfs https://docs.ceph.com/en/latest/rados/api/libcephsqlite/
  • this is not: a distributed SQL engine. […] SQLite on RADOS is meant to be accessed by a single SQLite client database connection at a given time.
• gui https://sqlitebrowser.org/
• tool wrapper around https://sqlsync.dev/ https://sqlsync.dev/posts/stop-building-databases/
• sqlite wasm https://github.com/rhashimoto/wa-sqlite
  • officially not on npm https://github.com/rhashimoto/wa-sqlite/issues/12
• https://sqlite-utils.datasette.io/en/stable/cli.html
• https://litestream.io/
  • source https://github.com/benbjohnson/litestream
  • example
    • article https://mtlynch.io/litestream/
    • source https://github.com/mtlynch/logpaste
• liteFS
  • 22 https://fly.io/blog/introducing-litefs/
  • https://fly.io/docs/litefs/

• go https://github.com/mattn/go-sqlite3
• go (pure) https://pkg.go.dev/modernc.org/sqlite
• go https://github.com/crawshaw/sqlite
  • low-level cgo wrapper
  • features not supported by go-sqlite (at least at the time)
    1. streaming blobs
    2. session extension
    3. shared cache

• subselects or WITH RECURSIVE, do not well with UPDATE
• iif() is a sweet ternary operator (sadly not available on postgresql)
• there is a boolean (true/false) but internally is converted to 1/0, there is no type for it
• there is mod(), but % is still an option
• you have = and = for equality and have ! and <> for inequality
• you can do 2 UPDATE passes
  • 1st pass to load intermediate results
  • 2nd pass to cleanup