Fennel wiki: sqlite

SQLite is a great little database.

Here's a tool that reads a list of URLs from a file, fetches their contents and turns them into plain text with pandoc, and puts their contents in an SQLite database where it can then be queried. It requires pandoc and lua-sql:

$ sudo apt install pandoc lua-sql-sqlite3 # prerequisites

(local sqlite (require :luasql.sqlite3))

(fn init []
  (case (io.open "db.sqlite3")
    f (do (f:close) (: (sqlite.sqlite3) :connect "db.sqlite3"))
    _ (-> (: (sqlite.sqlite3) :connect "db.sqlite3")
          (doto (: :execute "CREATE VIRTUAL TABLE pages USING fts5(url, body);")))))

(fn index [file]
  (let [db (init)
        pandoc-cmd "pandoc -f html -t plain %s"]
    (each [line (io.lines file)]
      (let [url (line:match "([^ ]+)")]
        (with-open [pandoc (assert (io.popen (pandoc-cmd:format url)))]
          (db:execute (: "INSERT INTO pages VALUES ('%s', '%s')" :format
                         (db:escape url) (db:escape (pandoc:read :*all)))))))))

(fn search [terms]
  (let [db (init)
        query (: "SELECT * FROM pages('%s');" :format (db:escape terms))
        cursor (assert (db:execute query))]
    (each [url contents #(cursor:fetch)]
      (print url) (print contents))))

(case arg
  ["index" file] (index file)
  ["search" query] (search query))

For a more fleshed-out implementation of this idea, see bookmarks.