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. Variables are easy to do: create a one-row temporary table and call it args or var. Working around the lack of Functions seems harder but in fact, you can program functions with Views. You can use CTEs in a View definition, so you can build up complex calculations. And CTEs allow recursion so you have Turing completeness.

As an example, the Exponent function as a View:

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

Postgres : Using Integrated Security or ‘passwordless’ login on Windows on localhost or AD Domains

…is slightly less straightforward than you might hope, but helpfully more flexible. For MS SQL Server, integrated security implies that windows user are magically also SQL users and that no password or username is needed to login. But also, that you can no longer choose which user you login as. Postgres is more configurable and more complex. You can specify which users use SSPI and which postgres user(s) each windows user can login as. You can specify, for instance, that you are allowed to use SSPI to login as the postgres superuser.

Here is how you can login with integrated security, as the user postgres, whilst still being able to login as a different user with a password.

  1. Locate and open for editing two files: your pg_hba.conf and pg_ident.conf files. Find them both in the same directory in e.g.
    C:\Program Files\PostgreSQL\data\pg96 or
    C:\Program Files\PostgreSQL\10\data\
  2. In pg_ident.conf add a line to map your windows login, in user@machinename format, to the postgres user named postgres. You can also add other users. Here's what my lines look like:
    # MAPNAME       SYSTEM-USERNAME         PG-USERNAME
    MapForSSPI     chris@YOURMACHINENAMEHERE    chris
    MapForSSPI     chris@YOURMACHINENAMEHERE    postgres

    (In normal unix style, the columns are separated by any amount of space or tab).

  3. In pg_hba.conf, add lines that allow user postgres to login with integrated security, whilst still allowing all other users to login with passwords. Again, you can add lines for other users too. Don't forget to put lines targetting specific users above the catchall lines otherwise they will never be reached.
    # TYPE  DATABASE        USER            ADDRESS                 METHOD
    
    #== lines for specific users for SSPI (or anything else) BEFORE the catchall lines ==
    # IPv4 local connections for SSPI:
    host    all             postgres        127.0.0.1/32            sspi 	map=MapForSSPI
    host    all             chris           127.0.0.1/32            sspi 	map=MapForSSPI
    # IPv6 local connections for SSPI:
    host    all             postgres        ::1/128                 sspi 	map=MapForSSPI
    host    all             chris           ::1/128                 sspi 	map=MapForSSPI
    #===================================================================================
    
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            scram-sha-256
    # IPv6 local connections:
    host    all             all             ::1/128                 scram-sha-256
    
  4. Restart the Postgres service, for instance with a powershell command
    Restart-Service 'PostgreSQL 9.6 Server'
  5. Trying logging in as user postgres:
    • psql -h localhost -U postgres
  6. Trying logging in as some other user:
    • psql -h localhost -U someotherusercreatedwithcreaterole
      and you should be prompted for a password (unless you already mastered the pgpass.conf file)

Logging in without specifying a user name

You might expect that SSPI implies not having to specify a username. You would be wrong. Postgres still requires you specify a username when using SSPI, and, as above, allows you to choose which username.

You can however login without a username—with or without SSPI—if there is a postgres user (i.e., a role with LOGIN privilege) with your Windows username (just the name, without the @machinename).

By combining this with the SSPI map above you can then login without typing username or password.

Integrated Security in .Net connection strings

Having done the above I can now use either of

"Host=localhost;Database=MyDb;Integrated Security=True;Username=postgres"
"Host=localhost;Database=MyDb;Integrated Security=True;Username=chris"

as a connection string for the npgsql Ado.Net driver

Reference

https://www.postgresql.org/docs/9.4/static/auth-pg-hba-conf.html and subsequent pages on Authentication methods and the pg_ident.conf file.

Caveats

Why does the title of this post say 'localhost or AD domains'? Because SSPI only works on Windows; and only on either localhost, or a Windows Domain environment as per https://wiki.postgresql.org/wiki/Configuring_for_single_sign-on_using_SSPI_on_Windows

Which brings us to the alternative that does work remotely without Domain servers: putting passwords in the pgpass.conf file.

Postgres ‘Passwordless’ Login

Storing passwords in plaintext on a windows machine is largely a no-no in most peoples eyes. Unixland is more accepting of it, perhaps because they habitually expect file permissions  to deny access to unauthorised users. And don't expect to have virusses scanning their machines.

psql.exe on Windows will look for a %appdata%\PostGres\pgpass.conf file (or $env:AppData\PostGres\pgpass.conf for PowerShellers) and will parse lines in this format:

hostname:port:database:username:password

See https://www.postgresql.org/docs/9.1/static/libpq-pgpass.html for some wildcard options such as

localhost:*:*:chris:mydevpassword

Your plaintext password is then protected by Windows file permissions. Which should be fine for passwords to non-production servers.

Postgres quick start for SQL Server / T-SQL Developers

After 17 years on T-SQL, I at last started working on projects using Postgres. Here are the first things I needed for the transition.

  • Do this first: https://wiki.postgresql.org/wiki/First_steps as it answers first questions about connections, databases and login.
  • Postgres replaces logins and database users and groups with a single concept: ROLE.
    • Permit a ROLE to login with the With Login clause when you Create or Alter it:
      Create Role MyNameHere With Login.
    • Grant it access to a database with Grant Connect: Grant Connect on Database dbname TO roleName.
    • Make it a group with – well, it already is a group. Add other roles to it with Grant Role1 to Role2. I think this approach works out well both for evolving and managing users & groups.
  • No NVarchar is needed, just text. The default installation uses UTF-8. Text is the usual idiom over VarChar(n), allegedly it performs marginally better.
  • 'String concatenation is done with ' || ' a double pipe '.
  • Date & Time: use timestamp for datetime/datetime2. Read Date/Time Types for more on dates, times and intervals.
  • Use semicolon terminators nearly everywhere:
    If ... Then ... ; End If ; T-SQL doesn't need them but Postgres demands them.
  • Before version 11, use Function not Procedure (use returns void if there is no return value). From version 11, you can Create Procedure() but note brackets are needed even for no parameters. A Procedure is called with CALL.
  • A typical definition:
    Create or Replace Function procedureName( forId int, newName varchar(20) )
    Returns int -- use returns void for no return
    As $$
    Declare
      localvariable int;
      othervariable varchar(10);
    Begin
     Insert into mytable (id,name) values (forId, newName) On Conflict (id) Do Update Set Name=NewName ;
    End $$;
    
  • Ooh, did you notice the 'Create or Replace' syntax, and how Postgres has a really good Upsert syntax – Insert On Conflict Update ?
  • Postgres does function overloads, so to drop a function you must give the signature: Drop function functionname(int)
  • Function and Procedural code is strictly separated from “pure” SQL in a way that T-SQL just doesn't bother with, and is typically written in plpgsql script. Declare variables in one block before Begin. For interactive work you can use an Anonymous Function block starting with DO :
    Select 'This line is plain SQL' ;
    Do $$
    Declare
      anumber integer ;
      astring varchar(100);
    Begin
    If 1=1 Then
     Raise Notice 'This block is plpgsql' ;
    End If;
    End $$ ;
    Select 'This line is SQL again' ;
    

    In fact, Postgres functions are defined in a string. The $$ delimiter is not a special syntax for functions, it is the postgres syntax for a literal string constant in which you don't have to escape any special characters at all. So that's handy for multiline strings and quotes.

  • Functions can be defined in other languages than plpgsql. For javascript, for example, see https://github.com/plv8/plv8.
  • If you don't need variables other than parameters, or control statements you can write a routine in pure sql by specifying:
    Create or Replace Procedure ProcName(Date onDate)
    Language Sql
    As $$
    Select * from LatestNews where PublishTimeStamp::Date = onDate
    $$;
    
  • The double colon :: is the cast/conversion operator.
  • Whereas the T-SQLer does everything in T-SQL, other database systems use non-sql commands for common tasks. For working at a command-line, learn about psql meta-commands. Start with \c to change database, \l to list databases, \d to list relations (ie tables), and \? to list other meta commands.
  • Variables: are only available inside plpgsql (or other language) code, not in plain SQL. Just use a plain identifier myvariablename with no @ symbol or other decoration. BUT as a consequence you must avoid variable names in a query that are the same as a column name in the same query. BUT BUT in Ado.Net Commands with Parameters, still use the @parametername syntax as you would for SQL Server
  • Postgres has probably stayed ahead of of T-SQL in keeping close to the SQL standard syntax, though they both become more, not less, standards-compliant with each version. The docs go into detail on deviations. But this means that much of your code for databases, schemas, users, tables, views, etc can be translated fairly quickly.
  • Replace Identity with the SQL Standard GENERATED BY DEFAULT AS IDENTITY for Postgres 10 onwards. (You will see Serial in older versions). For more complex options, Postgres uses SQL Sequences.
  • The Nuget package for Ado.Net, including for .Net core, is Npgsql
  • Scan these

Converting T-SQL to Postgres

Here's my initial search/replace list for converting existing code:

Search Replace
@variableName _VariableName
but stay with @variable for .Net clients using Npgsql
NVarChar Text
datetime
datetime2
timestamp
Identity Generated By Default As Identity
Before postgres 10 use Serial
Raise Raise Exception
Print Raise Notice
Select Top 10 ... Select ... Fetch First 10 Rows Only ;
Insert Into table Select ... Insert into Table (COLNAMES) Select ...
If Then Begin … End
Else Begin … End
If Then … ;
Else … ; End If ;
Create Table #Name Create Temporary Table _Name
IsNull Coalesce
UniqueIdentifier uuid
NewId() Before version 13, first websearch for Create Extension "uuid-ossp" to install the guid extension to a database. Then you can use uuid_generate_v4() and other uuid functions
Alter Function|Procedure Create or Replace Function

Authorization

Usually you will connect to postgres specifying a username. The psql.exe commandline tool will default it to your OS login username. You can avoid passwords in scripts that use psql by putting them in the pgpass.conf file).

If you want to set up integrated security, the limitation is that for computers not in a Domain it only works on localhost. The instructions at Postgres using Integrated Security on Windows take about 5 minutes for the localhost case, and include a link to the extra steps for the Domain case.

GUI Admin

Your replacement for SQL Server Manager is the pgAdmin GUI which gets you nicely off the ground with a live monitoring dashboard.

Command Line and Scripting

psql -h localhost -d myDB -c 'Select Current_User'

runs the quoted command. Note -h for “host”, not -s for “server”. Use psql --help to see more options. You can also use the pipe:

echo 'Select Version(), Current_Database();
Select Current_TimeStamp' | psql -h localhost

For large data dumps, pg_dump and pg_restore are designed to generate consistent backups of entire databases or selected tables —data or DDL or both—without blocking other users.

There are more command line utilities