A Supernaturalist and a Physicalist Swap Accounts of the Universe

A supernaturalist S and a physicalist P swap accounts of the existence of the universe we observe.

The supernatural account (S) of “something exists and behaves reliably enough for us to theorise about it” might go like this:
S: Something exists that is eternal and able to create a reliable universe which we can theorise about.
P: “How can that be?”
S: I don't know, it's supernatural.
P: Well that's not very satisfactory! Here's my account.

The physicalist account (P) of “something exists and behaves reliably enough for us to theorise about it” might go like this:
P: Something exists that is …[insert P's detail here]…
S: “How can that be?”
P: As a physicalist, I have a proper explanation. Look, here's my explanation, E, showing how it follows mathematically from certain equations and conservation laws.
S: “Nice. 2 questions though. (1) May I read your explanation E? and (2) does it include an explanation of how come these conservation laws and equations hold?”
P: Regarding (1) no you can't see explanation E because no-one has written it yet. And (2) it probably won't include that, we usually just accept them as a given.
S: Ok. Let's pass over the fact that no-one has written explanation E yet. When someone does write E, the account of “these conservation laws and equations hold” will be supernatural. Look, here's my account:

S2: Something exists that is eternal and able to create a reliable universe which instantiates these conservation laws and conforms to these equations.
P: “How can that be?”
S2: I don't know, it's supernatural.
P: Well that's not very satisfactory! Here's my account.

P2: Something exists, I express no opinion on whether it's eternal or finite, but it instantiates these conservation laws and conforms to these equations.
S: “How can that be?”
P2: It just does. That's the theory.
S: What makes this account physicalist rather than supernatural?
P2: Because I don't invoke anything supernatural.
S: But your account includes conservation laws and the instantiation of equations for which you have given no physicalist account. Ergo your account of them is super-physical. Or, as we say in everyday English, supernatural.
P2: That is not what we usually mean by supernatural.
S: Hmmm. I thought “not explained by physical laws” was what you did mean by supernatural, as opposed to physicalist?
P2: You can't expect the physical laws themselves to be explained in terms of physical laws.
S: Okay. To me, “we explain the existence of the universe in terms of physical laws which, in turn, we do not explain in terms of physical laws” seems like a long-winded way to say “we do not explain the existence of the universe in terms of physical laws.”

Pretending that SQLite has Stored Procedures and Functions

SQLite is marvellous. The fact that it doesn't have SQL syntax for stored procs and functions is usually not a handicap because it has an interface for the consuming application to register functions, which means you get to write your functions in your preferred programming language. Win all round.

But sometimes you do wish you could do it in SQL.

The lack of Stored Procedures is usually ok—you can just use scripts. You may wish you had variables, but you can create a one-row temporary table for those. Working around the lack of functions is harder, but here's an example of how you can do it in extremis

Drop Table if Exists Args ; Create Table Args as Select 5.5 as Base, 4 as Exponent ;

Drop View If Exists Power;
Create View Power As
    WITH RECURSIVE pow(exponent, exponent_remainder, base, result) as (
        --FIRST EXPRESSION
        SELECT exponent, exponent-1 , base, base
        FROM Args

        union all
        --SECOND EXPRESSION
        select Args.exponent, pow.exponent_remainder -1, pow.base, pow.result * pow.base
        from Args
        join pow on Args.exponent = pow.exponent
        where pow.exponent_remainder >= 0
    )
    select pow.result
    from pow
    where pow.exponent_remainder = 0;

and now you ‘call the function’ with:

Update Args set Base=2.5, Exponent=5; Select Result from Power;

The elements of the workaround are:

  1. A one-row table for function arguments
  2. A view which can refer to the arguments table and do the calculation. Since you can use CTEs to do recursion, you could in principle programming anything this way.

In similar style, here's an Exponential function which lets you specify how many significant digits you want the result to, default to about 7 digits of accuracy. This time we call the Args (X,Y,Z,p4,p5,…)

Drop Table if Exists Args ;
Create Table Args as Select 1 as X, 2 as Y, 3 as Z, 4 as p4, 5 as p5, 6 as  p6;

Drop View If Exists Exp;
Create View Exp As
    WITH RECURSIVE exp1(X, N, term, approx, accuracy ) as (
        --FIRST EXPRESSION
        SELECT X, 1, X, 1+X, Max(Min(Y, 1),0.00000000000000001)   FROM Args

        Union All
        --SECOND EXPRESSION
        Select X, N + 1, term * X / (N + 1), approx + term * X / (N + 1), accuracy
        From exp1
        Where  term / approx > accuracy Or N <3
    )
    Select approx as Result From exp1 Order By N Desc Limit 1;

And then:

Update Args Set X=22.0, Y=0.00000000000001;
Select * from Exp;
#
# > 3584912846.1315813 # Exp(22) correct to 14 digits

Links

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