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:

  sql source: 
    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

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