SQL in WeeWiki

This page aims to outline some small SQL tricks in WeeWiki.

WeeWiki bundles a version of the SQLite Janet bindings, so this allows WeeWiki to query sqlite databases (include itself) and dynamically produce content.

Generating a Page Index

This is the script that can be used to generate an index of all the wiki pages:

(defn mkindex ()
  (var db (ww-db))
  (each page
      (sqlite3/eval
       db
       (string "SELECT key FROM wiki "
               "WHERE key NOT LIKE \"@%\" "
               "ORDER BY key COLLATE NOCASE;"))
    (do
      # handle index page differently
      (if (= (get page "key") "index")
        (org (string "[["
                     (string webroot "/index.html")
                     "][index]]"))
        (ref (get page "key") (get page "key")))
      (org "\n"))))

It performs a query on the wiki table, returning all the page names, while also ignore the non-page keys (keys that start with '@' are treated as variables). It then uses this with the ref function (defined in weewiki.janet) to create a reference for each page.

Accessing WeeWiki Variables

WeeWiki can access itself (the currently opened database) with the function ww-db. This can then be used with any of the SQLite3 janet functions.

My WeeWiki has a special variable called "@life". The function below will query this page, and print the meaning of life (displayed below):

(do
  (def db (ww-db))
  (def val
    (sqlite3/eval
     db
     "SELECT value FROM wiki where key==\"@life\""))
  (print "The meaning of life is: ")
  (print (get (get val 0) "value")))

The meaning of life is: 42

Parsing Other Pages

Along the same line, the following code will use orginstead of print to parse org code found in the ars brevis page:

(do
  (def db (ww-db))
  (def val
    (sqlite3/eval
     db
     "SELECT value FROM wiki where key==\"ars_brevis\""))
  (org (get (get val 0) "value")))

Ars Brevis

A Grook by By Piet Hein

There is

one art,

no more,

no less:

to do

all things

with art-

lessness.

Iterating over values

One can also iterate over values. For instance, this will print the first 25 linked pages in wikilinks in alphabetical order:

(do
  (def db (ww-db))
  (def values
    (sqlite3/eval
     db
     (string
      "SELECT filename FROM wikilinks "
      "ORDER BY filename "
      "COLLATE NOCASE LIMIT 25;")))

  (each file values
    (org
     (string (get file "filename") "\n\n"))))

antilp.org

ars_brevis.org

bigverb.org

bitlets.org

blurb.org

breathing_cards.org

btprnt.org

btprnt_program.org

caisaichoh.org

CATFMAS.org

cfloop.org

convergence.org

dcblocker.org

dither.org

equipment.org

ergo_id.org

event_graph.org

fmkik.org

fmpair.org

gpd2.org

grid_tutorial.org

hclock.org

index.org

keys2db.org

keywords.org

---

home | index