Embedded procedural programming inside SQL Server Databases

Let’s suppose one is tasked with a complex set of calculations where the input data loads from a database and the output data saves to a database. This has come up a number of times in my consulting work. There are 2 approaches:

  1. Use language of choice, read data, perform calculations, write data
  2. Work entirely within the database

This latter has the obvious benefits of skipping the data transfer times. This is also where it’s easy to use the first approach, only to run into problems as the amount of data is increased.

Now for simpler tasks I’ve often chosen approach 2. A simpler task, in this case, is when the computation is question can be expressed as an INSERT or UPDATE query. This works when one needs to produce statistics like mean and standard deviation that can be easily expressed as aggregates on the input data. And when something is more complicated than that I have chosen option 1.

One issue with more complicated programming within a database is that there is no agreed upon common portable language. I have client whose databases include MariaDB and SQL Server and thus there is no way to develop code that will work on both of those platforms. I was thinking the other day, wouldn’t it be great if there was an agreed upon embedded language, in addition to whatever legacy languages are support(PL/SQL, T-SQL). Maybe Lua would be a good choice. It’s already available to Postgresql as pllua-ng. Perhaps Python would be good as well.

MariaDB can understand a subset of PL/SQL and (currently small) T-SQL which is nice. Postgresql can understand a subset of PL/SQL. Although I don’t expect Oracle or SQL Server to make changes in this regard, it would be nice if a team created a language that could be added to both that could be used to write portable procedural code.

Interchange formats

I don’t like JSON, or YAML. Moving on.

Actually I’ve been exploring binary interchange formats. I am not the first to note that converting data from binary, to text, and back, is often suboptimal, especially for the not-uncommon use case of floating point numbers. With some projects I’ve been working with there is a lot of interchange with delimited files (CSV, TSV, PSV) and JSON. These suffer from the creation of the text stream only to have it parsed back into binary formats. These formats can be useful because they are human readable so being able to inspect them is helpful in a development stage but for interchange they are suboptimal.

Here are some alternatives I’ve been investigating:

  • MessagePack – lots of language support
  • CBOR – lots of language support
  • RION – good design goals, only available for JAVA
  • BSON – used by MongoDB and not much else

IRT, CAT, and related Packages In R

I’ve been using some of these a lot for some psychometrics software developmont work.

Enabling DNSSEC on Debian and Ubuntu (with systemd-resolved)

Using systemd-resolved opens up some options that may be easier to configure then in the past.

Enabling DNSSEC:

  • systemd-resolve –status
    • look for “DNSSEC setting: no”
  • sudo mkdir -p /etc/systemd/resolved.conf.d
  • sudo nano /etc/systemd/resolved.conf.d/dnssec.conf
    • enter:
      • [Resolve]
        DNSSEC=true
  • sudo systemctl restart systemd-resolved
  • systemd-resolve –status
    • look for “DNSSEC setting: yes”

Some Ubuntu 18.04 problems fixed today

I’ve drafted but not published a long list of problems I’ve experienced with Ubuntu 18.04. In short it’s been one of the worst desktop releases of Ubuntu I’ve used. My experience on servers has been fine. Anyway I haven’t looked into the details of these patches but:

  • libwebkit2gtk-4.0-37:amd64 (2.22.6-0ubuntu0.18.04.1)
  • libjavascriptcoregtk-4.0-18:amd64 (2.22.6-0ubuntu0.18.04.1)

These package releases seem to have fixed some of my issues that have been present since May 2018. In particular Liferea has been broken on my desktop for that long. Better late than never I guess.

Shopping for a wired router

I’ve often run a wired router at the front of my network (well immediately behind the modem). The only other topology has been a linux server at the front of the network. Then I like a network behind that where a wireless router can live. I keep my wireless router turned off when not in use, which is an uncommon setup it seems. Anyway, for 1 network I’m managing I’m considering replacing the linux system with a wired router. Wired routers are not super popular for SOHO uses as mos tpeople seem to prefer a wireless router to do everything. Because there is not a huge market options can be limited. Here are some products I’ve investigated.