Installing and using SQLite extensions on macOs (and maybe Windows & Linux too)

Installing and using SQLite extensions on macOs

SQLite is brilliant and … lite. Deliberately. Even for maths functions the view is “it's really, really easy to add extensions and we don't want to bloat the core.”

This is fine if you are used to C development. This page is for if you aren't. The first section is specific to macOs, which is the “hardest” case. Linux and Windows are easier and you can skip the first section.

1. For macOs: Be able to load SQLite extensions

  1. Install SQLite from homebrew, because the apple-shipped SQLite will probably not allow you to load extensions. If you try to load an extension, you will just get a "not authorized" error.
    brew install sqlite

    Note that homebrew tells you that it has not put sqlite in the path because the apple-shipped version is in the path. Fix this either by editing your profile file to extend the path, or else by adding a link to the updated sqlite3 in /usr/local/bin:

    ln -s /usr/local/opt/sqlite/bin/sqlite3 /usr/local/bin/
  2. Now if you start sqlite3 you should see a new improved version number:
    SQLite version 3.33.0 2020-08-14 13:23:32
    Enter ".help" for usage hints.
    

2. Download a Loadable Module

  1. We'll take Spatialite as a great example. Get the .dylib file (macOs) or .dll (for Windows or Linux) or .so file (for Linux) for your extension and confirm it is somewhere you can find it. For Windows the homepage has links to 7z archive file containing the loadable module and sqlite.exe too. For macOs:
    brew install libspatialite
    # ... lots of homebrew output ...
    
    ls /usr/local/lib/*spatialite*
    # /usr/local/lib/libspatialite.dylib
    # /usr/local/lib/mod_spatialite.dylib
    # /usr/local/lib/libspatialite.7.dylib
    # /usr/local/lib/mod_spatialite.7.dylib
    
  2. Add the directory /usr/local/lib to your LD_PATH if it isn't already there. (The alternative to this step is, in the next step, to use the absolute path to load the module.)
    • echo $LD_PATH #Check if you already have it
    • export LD_PATH="$LD_PATH:/usr/local/lib"
    • Edit your profile file to make the change repeatable. For instance:
    • zsh : echo 'export LD_PATH="$LD_PATH:/usr/local/lib"' >> ~/.zshrc
    • bash: echo 'export LD_PATH="$LD_PATH:/usr/local/lib"' >> ~/.bash_profile
    • fish: echo 'set -x LD_PATH "$LD_PATH:/usr/local/lib"' >> ~/.config/fish/config.fish
  3. Start sqlite again and now load the module. There are two ways to do it, either should work:
    • Either with .load :
      .load mod_spatialite.dylib #*if you set LD_PATH above*
      .load /full/path/to/the/file/mod_spatialite.dylib # *if you didn't*
    • Or with Select:

      Select load_extension('mod_spatialite');

    Either way you should now be able to select the spatialite version number:

    select spatialite_version() ;
    # 4.3.0a
    

3. Other Extensions

Spatialite was the easy example because there are pre-compiled binaries available for all platforms. Other extensions mostly exist as .c files. But good news! Many of them are single files and easy to compile and install.

  1. Download some extensions, usually as a single .c file

    For instance, look at https://www.sqlite.org/contrib and notice extension-functions.c at the bottom of the page. Let's install this, which has common maths, string and stats functions functions such as power(), exp(), reverse(), stdev() :

  2. Having downloaded the .c file, compile it in your download directory.

    For macOs:

    gcc -g -fPIC -dynamiclib extension-functions.c -o extension-functions.dylib
    

    For Windows, use one of:

    gcc -g -shared YourCode.c -o YourCode.dll
    cl YourCode.c -link -dll -out:YourCode.dll
    

    For linux/*nix:

    gcc -g -fPIC -shared YourCode.c -o YourCode.so
    
  3. Copy it to your lib directory and use it. e.g. for macOs:
    mv extension-functions.dylib /usr/local/lib/
    sqlite3
    > .load extension-functions.dylib
    Select sqrt(10), log(exp(2)) ;
    # sqrt(10)          log( exp(2) )
    # ----------------  -------------
    # 3.16227766016838  2.0
    

Even more extensions

There are more extensions in the SQLite repository which you can download and install from the .c file in the same way. https://sqlite.org/src/file/ext/misc/ includes files for json & csv formatting, regex, uuids, and other requirements.

If you have a not-latest version of SQLite installed, you may need the advice on Forums - How do I compile the newest .... I ended up with:

for f in *.c
  set b (basename -s .c $f)
  gcc -g -fPIC  -DSQLITE_INNOCUOUS=0 -DSQLITE_VTAB_INNOCUOUS=0  -DSQLITE_VTAB_DIRECTONLY=0  -DSQLITE_SUBTYPE=0 -dynamiclib $f -o $b.dylib
  rm -r $b.dylib.dSYM
end

4. Making it permanent-ish

You can use the file ~/.sqliterc to permanently include your loaded functions. Here's mine:

.headers ON
.mode column
.load extension-functions.dylib

For the same functionality on another machine, you must replicate these steps. The sqlite developers' solution would be, compile your own distribution of sqlite with all the bits you want.

References

fish shell quickstart for converting bash scripts

After some years of bash and PowerShell, and some hours of using fish, I've realised that expansion & predictive typeahead are good features in a shell, whereas “be a great programming language” is less important than I thought: because there is no need to write scripts in the language of your shell.

Fish has slicker typeahead and expansions than bash or even PowerShell. But to switch to a fish shell, you do still have to convert your profile & start-up scripts. So here's my quick-start guide for converting bash to fish.

  • Do this first: at the fish prompt type help. Behold! the fish documentation in your browser is much easier to search than man pages are.
  • Calmly accept that fish uses set var value instead of var=value. Roll your eyes if it helps.
  • Use end everywhere that bash has fi, done, esac, braces {} etc. e.g. function definition is done with function ... end. The keywords do and then are redundant everywhere, just remove them. else has a semicolon after it. case requires a leading switch(expr).
  • There is no [[ condition ]] but [ ... ] or test ... work. Type help test to see all the file and numeric tests you expect, such as if [ -f filename ] etc. string and regex conditionals are done with the string match command (see below). You can replace [[ -f this && -z that || -z other ]] with [ -f this -a -z that -o -z other ] but see below for how fish can also replace || and && constructions with or and and statements.
  • But first! type help string to see the marvels of proper built-in string commands.
  • Replace function parameters $*, $1, $2 etc with $argv, $argv[1], $argv[2] etc. If that makes you scowl, then type help argparse. See! That's much better than kludging about in bash.
  • Remove the $ from $(subcommand) leaving just (subcommand). Inside quotes, take the subcommand outside the quote: "Today is $(date)" becomes "Today is "(date). (Recall that quotes in bash & fish don't work at all like quotes in most programming languages. Quote marks are not token delimiters and a"bc"d is a valid single token and is parsed identically to each of abcd , "abcd", abc'd').
  • Replace heredocs with multi-line literal strings and standard piping syntax. However, note that if you pipe or read to a variable, the default multiline behaviour is to split on newline and generate an array. Defeat this by piping through string split0 – see https://fishshell.com/docs/current/index.html#command-substitution

Search-and-replace Script Snippets

Here is my hit-list of things to search and replace to convert a bash shell to fish. These resolved almost all of my issues in converting a few hundred lines of bash script to fish.

FromToNotes
var=valueset var value
export var=valueset -x var value
export -f functionnameredundant.Just remove it
alias abbr='commandstring'(no change)alias syntax is accepted as an abbreviation for a function definition since fish 3
command $(subshell commmand)
command `subshell commmand`
command (subshell command)
OR
command (subshell commmand | string split0)
Just remove the $ but keep the ()

See below for when you want to add string split0
command "$(subshell commmand)"command (subshell command)Remove both the $ and the quotes ""to make this work
if [[ condition ]] ; then this ; else that ; fiif [ condition ] ; this ; else ; that ; endSee below for more on Fish's multine and and or syntax.
if [[ number != number ]] ; then this ; else that ; fiif [ number -ne number ] ; this ; else ; that ; endSee below for more on Fish's multine and and or syntax.
while condition ; do something ; donewhile condition ; something ; end
$*$argv
$1, $2$argv[1], $argv[2]But see help argparse
if [[ testthis =~ substring ]] if string match -q '*substring*' testthisstring match without -r does glob style testing
if [[ testthis =~ regexpattern ]] if string match -rq regexpattern testthisstring match with -r does regex testing
[ guardcondition ] && command
[ guardcondition ] || command
works as isBut see or and and below for when it's more complex
var=${this:-$that}if set -q this ; set var $this ; else ; set var $that ; end
cat > outfile <<< "heredoc"
cat > outfile <<< "multiline … heredoc"
echo "multiline … heredoc" | cat > outfile no heredocs, but multiline strings are fine
NB printf is better than echo for anything complicated, in any shell.
if [[ -z $this && $that=~$pattern ]]if [ -z $this ] ; and string match -rq $pattern $that ;
content=$(curl $url)set content (curl $url | string split0)without the pipe to string split0, content will be split on newlines to an array of lines.

Fish's multine and and or syntax

Fish has a multiline and and or syntax that may be clearer than && and || in both conditionals and guarded commands. It is less terse.

[ condition ]
and do this
or do that

That said, && and || are still valid in commands :

[ condition ] && do this || do that

Other gotchas

  • You may have to read up on how fish does parameter expansion, and especially handling spaces, differently to bash.
  • Pipe & subcommand output to multiline strings or arrays: set x (cat myfile.txt) will set x to an array of the lines of myfile.txt. To keep x as a single multine string, use string split0 : set x (cat myfile.txt | string split0)

Official tips for new fishers:

See the FAQ at https://fishshell.com/docs/3.0/faq.html

Choosing an Affordable Mechanical Keyboard

tl;dr: KeyChron are brilliant and cheaper than the alternatives.

My nephew was foolish enough to ask about the keyboard he spotted in a photo of my desktop so, armed with my Keychron K2, I explained what led me to it.

I got out kitchen scales and improvised some 2-10 gramme weights and carefully weighed the actuation force on my MacBook and on my Logitech K480. I found that the MacBook is approx 60grammes and that the K480—which I find too much hard work to type on—is about 70g. To me, the difference feels much more than that, but that's what the scales told me. (And yes, the A-level physicists amongst you will know that by gramme I mean, milliNewton)

I deduced that any switch of 60g or below, I would be happy with.
I then noted that the Gateron Blue is allegedly noisy (fine if you don't share a room with someone, anti-social if you do); and I examined the graphs of the actuation curves. I would have preferred the ‘clicky’ buckling spring feel of the Blue but I've used a noisy keyboard before and feel the noise can be a real downer. I definitely didn't want the linear feel which the Red and Black have (allegedly gamers like it, but I don't) so that left me with the Brown ‘Tactile’ which is in between.

I went for aluminium backlit because boo to plastic (don't kid yourself though; I do not know whether the carbon footprint of producing the aluminium keyboard is any less than that of the plastic). Nightlife without backlighting is grim, it is must-have for me. My other must-haves are Mac keycaps, and either the “tenkeyless” keyset or the full 101 keys, because I hate not having page up/down and home/end on the main keyboard.

I agonised over K1 or K2 and went for K2 because K1 didn't have the Brown switches. But … having become a closet Apple fanboi I am now disappointed that the K2 feels much higher off the desk that the Apple ultra-flat style and I may yet buy a K1 instead. Or a wrist rest.

I assure you however that the K2 is the best keyboard I've had for years. I had an IBM buckling spring keyboard for while in the 1990s, and a noisy Cherry for a while last year. The KeyChron has the same solid feel at the IBM had, but with the Brown switches is lighter and feels slightly less industrial. It is really nice, impressively priced, and I very much like it.

It will make you want to type long letters, or—should you happen to have WhatsApp Web on your computer—really really long messages to your nephew about how good your keyboard is. 🤷

Also 10% off! https://keychronwireless.refr.cc/chriscarroll

I did spend a while looking at other keyboards, mostly mechanical, but wasn't willing to pay the enormous prices. KeyChron is a half or even a third of the price of other good mechanical keyboards. Seemed a no-brainer to me.

Customise Macos XQuartz : xinitrc doesn’t work

If you installed XQuartz and are, for instance, irritated by the small white xterm window you get, you might try customising it in the usual way by editting an .xinitrc file. If only.

Instead, try this:

defaults read org.macosforge.xquartz.X11

to see all the settings; or to permanently change the startup xterm window, something like:

defaults write org.macosforge.xquartz.X11 app_to_run \
 "/opt/X11/bin/xterm -fa Monaco -fs 12 -fg green -bg black -sb -sl 1000"

Or, if you have installed a better bash with homebrew, then e.g. :

defaults write org.macosforge.xquartz.X11 app_to_run \
  "/opt/X11/bin/xterm -fa Monaco -fs 12 -fg green -bg black -sb -sl 1000 -ls /usr/local/bin/bash"

You can check your syntax before writing the default just by running your quoted command in a terminal, and then watch as XQuartz opens and xterm runs your shell:

~/Source/Repos/VMs] /opt/X11/bin/xterm -fa Monaco -fs 12 -fg green \
    -bg black -sb -sl 1000 -ls /usr/local/bin/bash

To set the default for a new xterm window from the XQuartz Application menu, the menu itself lets you edit the command.

In short, read the FAQ : https://www.xquartz.org/FAQs.html.