The Arcology Garden

Archive

I use sqlite in Arcology and I think actually it's a pretty neat database if you can live with the tradeoffs. This page lays out some of the calculations and query types which are supported in sqlite these days. A lot of them I didn't know about, including things like JSON partial indices which I have been a huge booster of PostgreSQL for.

• importing CSVs and exporting from the console

• json_extract

• CTEs including across database files

• percentile functions

• virtual tables instead of imports:

  create virtual table temp.blocks_csv using vsv(
      filename="ipblocks.csv",
      schema="create table x(network text, geoname_id integer, registered_country_geoname_id integer, represented_country_geoname_id integer, is_anonymous_proxy integer, is_satellite_provider integer, postal_code text, latitude real, longitude real, accuracy_radius integer)",
      columns=10,
      header=on,
      nulls=on
  );

• text search

  create virtual table books_fts
  using fts5(title, author, publisher);
  
  insert into books_fts
  select title, author, publisher from books;