$ duckdb -c "
FROM read_parquet([
  'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2023-01.parquet',
  'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2023-02.parquet',
  'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2023-03.parquet',
  'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2023-04.parquet',
  'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2023-05.parquet',
  'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2023-06.parquet',
  'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2023-07.parquet',
  'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2023-08.parquet',
  'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2023-09.parquet',
  'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2023-10.parquet',
  'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2023-11.parquet',
  'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2023-12.parquet'
  ])
SELECT
  COUNT(*) AS n_rides_2024
"
┌──────────────┐
│ n_rides_2024 │
│    int64     │
├──────────────┤
│     38310226 │
└──────────────┘

library(stringi)
library(purrr)
library(tibble)

# find all the Drops
fils <- list.files(pdir, pattern = "index.md", full.names = TRUE, recursive = TRUE) 

# read them int
fils |> 
  map(stri_read_lines) -> drops

# get the dates of the posts
days <- stri_match_first_regex(fils, "([[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2})")[,2]

# get the title of the posts
titles <- map_chr(drops, head, 1)

# get the sections in the posts
sections <- trimws(map_chr(drops, \(.x) .x[2]))

# collapse the lines in each post into one giant string
markdown <- map_chr(drops, paste0, collapse = "\n")

# we're going to extract the URLs that are in the posts
url_regex <- "https?://(?:www\\.)?[-a-zA-Z0-9@:%._\\+~#=]{1,256}\\.[a-zA-Z0-9()]{1,6}\\b(?:[-a-zA-Z0-9()@:%_\\+.~#?&//=]*)"

stri_match_all_regex(markdown, url_regex, omit_no_match = TRUE, multi_line = TRUE) |> 
  lapply(
    stri_replace_last_regex, r"(\).|\)$)", ""
  ) |> 
  lapply(
    unique
  ) |> 
  lapply(
    stri_subset_regex, "twitter|substack|youtube.com/watch$", negate = TRUE # ignore crufty ones
  ) |> 
  lapply(
    stri_replace_all_fixed, "http://", "https://"
  ) -> urls

# save it out
jsonlite::stream_out(
  tibble(
    day = days,
    title = titles,
    sections = sections,
    md = markdown,
    urls = urls
  ),
  gzfile("/tmp/drops.json.gz")
)

$ duckdb drops.db
> INSTALL fts; -- install the full text search extension
> LOAD fts; -- load it
> CREATE TABLE posts AS (FROM read_ndjson('drops.json.gz')); -- create table from the json
> .schema
CREATE TABLE posts("day" DATE, title VARCHAR, sections VARCHAR, md VARCHAR, urls VARCHAR[]);
> PRAGMA create_fts_index('posts', 'title', 'md'); -- setup fts indices
> .schema
CREATE TABLE fts_main_posts.dict(termid BIGINT, term VARCHAR, df BIGINT);
CREATE TABLE fts_main_posts.docs(docid BIGINT, "name" VARCHAR, len BIGINT);
CREATE TABLE fts_main_posts.fields(fieldid BIGINT, field VARCHAR);
CREATE TABLE posts("day" DATE, title VARCHAR, sections VARCHAR, md VARCHAR, urls VARCHAR[]);
CREATE TABLE fts_main_posts.stats(num_docs BIGINT, avgdl DOUBLE);
CREATE TABLE fts_main_posts.stopwords(sw VARCHAR);
CREATE TABLE fts_main_posts.terms(docid BIGINT, fieldid BIGINT, termid BIGINT);

location ~* /data {
  add_header "Access-Control-Allow-Origin" "*";
  add_header "Access-Control-Expose-Headers" "Content-Range";
  sendfile on;
  tcp_nopush on;
  add_header Accept-Ranges bytes;
  proxy_force_ranges on;
  proxy_http_version 1.1;
  proxy_set_header Range $http_range;
  proxy_set_header If-Range $http_if_range;
}

$ duckdb
> INSTALL fts;
> LOAD fts;
> ATTACH 'https://rud.is/data/drops.db' AS drops_db;
> USE drops_db;
> .schema
CREATE TABLE fts_main_posts.dict(termid BIGINT, term VARCHAR, df BIGINT);
CREATE TABLE fts_main_posts.docs(docid BIGINT, "name" VARCHAR, len BIGINT);
CREATE TABLE fts_main_posts.fields(fieldid BIGINT, field VARCHAR);
CREATE TABLE posts("day" DATE, title VARCHAR, sections VARCHAR, md VARCHAR, urls VARCHAR[]);
CREATE TABLE fts_main_posts.stats(num_docs BIGINT, avgdl DOUBLE);
CREATE TABLE fts_main_posts.stopwords(sw VARCHAR);
CREATE TABLE fts_main_posts.terms(docid BIGINT, fieldid BIGINT, termid BIGINT);
>.mode json
>
FROM (
  FROM posts
  SELECT *, fts_main_posts.match_bm25(
    title,
    'atproto'
  ) AS score
) sq
SELECT sections, score
WHERE score IS NOT NULL
ORDER BY score DESC
LIMIT 2;
[
  {"sections":"atproto/bsky Deep Dive; Ethernet Is Old; Stocky","score":3.1507045410668577},
  {"sections":"goat; sampler; 2024 CSS Survey Results","score":3.124673589581529}
]

$ npm create vite@latest duckdb-drops -- --template lit-ts

$ npm create vite@latest

import * as duckdb from "@duckdb/duckdb-wasm";
import duckdb_wasm from "@duckdb/duckdb-wasm/dist/duckdb-mvp.wasm?url";
import mvp_worker from "@duckdb/duckdb-wasm/dist/duckdb-browser-mvp.worker.js?url";
import duckdb_wasm_eh from "@duckdb/duckdb-wasm/dist/duckdb-eh.wasm?url";
import eh_worker from "@duckdb/duckdb-wasm/dist/duckdb-browser-eh.worker.js?url";

const MANUAL_BUNDLES: duckdb.DuckDBBundles = {
  mvp: {
    mainModule: duckdb_wasm,
    mainWorker: mvp_worker,
  },
  eh: {
    mainModule: duckdb_wasm_eh,
    mainWorker: eh_worker,
  },
};

// …

// in Lit, this will fire up duckdb when the element is on the page
async firstUpdated() {
  this.isLoading = true;
  try {
    const bundle = await duckdb.selectBundle(MANUAL_BUNDLES);
    const worker = new Worker(bundle.mainWorker!);
    const logger = new duckdb.ConsoleLogger();
    this.db = new duckdb.AsyncDuckDB(logger, worker);
    await this.db.instantiate(bundle.mainModule, bundle.pthreadWorker);
    const c = await this.db.connect();
    await c.query(`
      INSTALL fts;
      LOAD fts;
      ATTACH 'https://rud.is/data/drops.db' AS drops_db;
      USE drops_db;
    `);
    this.dbConn = c;
    await this.performSearch(""); // init the full table listing
  } finally {
    this.isLoading = false;
  }
}

// table results are either "everything" or the ones from the full text search
async performSearch(query: string) {
  const searchSQL = query.trim()
    ? `
      FROM posts
      SELECT day, title, sections, md, urls, fts_main_posts.match_bm25(title, '${query}') AS score
      WHERE fts_main_posts.match_bm25(title, '${query}') IS NOT NULL
      ORDER BY score DESC;
    `
    : `
      FROM posts
      SELECT day, title, sections, md, urls
      ORDER BY day DESC;
    `;

  const res = await this.dbConn.query(searchSQL);
  this.posts = await Promise.all(
    res.toArray().map(async (row: any) => {
      const post: PostRecord = {
        day: new Date(row.day).toISOString().split("T")[0],
        title: row.title,
        sections: row.sections,
        md: row.md,
        urls: Array.from(row.urls),
        score: row.score,
        renderedMd: await marked(row.md), // Pre-render the markdown b/c while the async render works in dev mode vite whines abt it in build
      };
      return post;
    }),
  );
}