Using spreadsheets as databases

I tweeted this yesterday:

Rule #1: Don’t use a spreadsheet as a database

Rule #2: Definitely don’t use a spreadsheet as a database

Rule #3: Named ranges and AutoFilters are awesome for use as a quick database (but you didn’t read that)

@djackman replied:

Also, if you’re a multibillion dollar Australian corporation, don’t run your distribution centre database on bloody Microsoft Access.

And @markashworth (private account):

True story. Some very big (huge) companies do this and they use email to distribute their ‘databases’

I’m sure there must be a German word for cry-laughter, when you’re both bemused and sad at the state of things. It needs to describe something that’s both awful, and utterly believeable.

Then @michaeldexter:


I poorly summarised in a tweet, but I use them heavily to track my own collections of pointless stuff. Like LaserDiscs, vintage IT parts, and personal projects. It’s great to be able to filter by whether I’m selling something, the condition of a part, whether things need replacing, or even where something is.

In a past life I feel like I would have used something like FileMaker, or even SQLite3 with a simple Perl-based CLI. But spreadsheets work fine.

When junk blogs stifle troubleshooting

There’s a particular type of junk blog out there that regurgitates posts from other blogs without any original research. They’re not as blatant as those that merely scrape RSS feeds and republish, but they still lift entire posts wholesale and only make token efforts to change the content. I’ve seen blogs even use the same screenshots, right down to the same mis-cropped window or blatant watermark, albeit with a few more JPEG artefacts from several steps of lossy compression along the way. This is unfortunately easy to do with instructional material, as the steps involved in configuring something will necessarily follow a predictable set of steps.

Plagiarism is obviously unethical, but almost as irritating is getting the same repurposed results when you’re trying to solve a problem. If it didn’t help the first time, having it appear another dozen times is a recipe for losing hair.

As an example, it’s been a while since I’ve had to boot Windows Server in safe mode. So I was looking online about how to do it. The first pages of search results are showed the exact same three steps:

  1. Using msconfig after Windows has booted
  2. Using Windows Settings after Windows has booted
  3. Using bcdedit after Windows has booted

See anything wrong with these? If you’re attempting to boot in safe mode, it’s almost certainly because the damn thing won’t boot. Being told once to change a tyre while your car is running is strange. Being told ten times is bananas.

I’m tempted to create a blocklist to feed to uBlock Origin or ABP to filter out these sites.

Owner-occupiers driving house demand

Greg Jericho in the Guardian:

House prices remain a major issue of the economy as once again we see prices in Sydney and Melbourne go up after significant falls. For now the market is being driven by owner-occupiers, and investors remain largely absent. That is good news for first-home buyers who are make up a greater share of buyers than at any time since the GFC.

As it should be. Houses are for living, not speculating. Every property investor has deprived a family of a home they can afford to buy. If you disgaree and think we should all be renters, then by all means advocate for amendments to tenancy legislation to bring Australian states in line with Western Europe.

Smartphone book readers on the train

I was getting the peak-hour train to work this morning, carrying my regular backpack and a new toaster for the office. I was catching up on the release notes for NetBSD 9.0, and playing a little Fate/Grand Order before the latest event finishes. This was a difficult balancing act at first, but I managed to find a corner to sit the bags between stations.

Then I noticed something that if you asked me before jumping on the train I would have given a probability of zero: everyone standing around the same part of the carriage vestibule as me were looking at their phones, but as far as I could tell all of them were reading books. There must have been a dozen of us.

Somehow I found this encouraging. Social media platforms basically treat us like addicts at this stage, so to see people eschewing—gesundheit—them was a pleasant surprise. The sight of it also made me feel calmer by proxy in a weird way, like I was in a hushed library on wheels hurtling through a tunnel and across the Harbour Bridge.

I might even finish my pile of Steven Pinkers and Japanese Light Novels on the train.

What started as investigating /opt on FreeBSD

Last month I wrote a post about symlinking svnlite(1) to /opt/bin/svn on stock FreeBSD, thus allowing Ansible’s subversion module to checkout a repository in a playbook I was writing. This lead to more research into this file system structure than I expected.

Did I get this from Solaris?

This post was SPARC’d—hah—from a discussion with two other Australian BSD gentleman on The Twitters last week. This wasn’t the first awful Solaris joke I’ve made today.

Here’s Ben Woods, whom I had the pleasure of meeting at the FreeBSD miniconf last month at

/opt/ ???? I feel dirty :)

And Jason Tubnor, whom I met at AsiaBSDCon:

Old skool Solaris admins still do

illumos icon

I now felt dirty, and was questioning whether I picked up the /opt habit from using Solaris myself. I did use SunOS briefly at when studying at UniSA, and OpenSolaris was my introduction to ZFS back in the day. I even ran the first closed-source Solaris on my HP Microserver after Oracle sank their teeth into Sun. But I’m fairly sure my usage predates these.

My justification had always been about preventing package manager collisions with my own manually installed tarballs and scripts. The standard place for these is generally /usr/local, but that’s where the BSDs put their packages by default, and macOS Homebrew puts brews. More on that later in this post.

Checking out /opt on the BSDs

This rote behaviour got me curious, so I decided to do a bit of digging and checked out the hier(7) manpage on various operating systems, starting with everyone’s favourites.

Interesting! So wherever I picked this up, it was outside the BSDs. It’s starting to look like it might have been a Solaris-ism after all.

GNU/Linux does mention it

systemd’s file-hierarchy(7) page doesn’t include /opt. But the Linux manpage does mention /opt/, and as a bonus didn’t even tell me to look in the GNU Info page instead. It also mentions another use at the start I didn’t recognise:

/etc/opt Host-specific configuration files for add-on applications installed in /opt.

/opt This directory should contain add-on packages that contain static files.

I use /opt with separate directories for each package I’m installing, so my config would be in /opt/$PACKAGE/etc not in /etc/opt. And I’d put consolidated config into /opt/src.

The manpage also mentioned the Linux Filesystem Hierarchy Standard, which sure enough describes /opt in section 3.13, last updated in 2015:

/opt is reserved for the installation of add-on application software packages. A package to be installed in /opt must locate its static files in a separate /opt/<package> or /opt/<provider> directory tree, where is a name that describes the software package and <provider> is the provider’s LANANA registered name.

The Rationale section has some interesting history:

The use of /opt for add-on software is a well-established practice in the UNIX community. The System V Application Binary Interface [AT&T 1990], based on the System V Interface Definition (Third Edition), provides for an /opt structure very similar to the one defined here.

The Intel Binary Compatibility Standard v. 2 (iBCS2) also provides a similar structure for /opt.

Generally, all data required to support a package on a system must be present within /opt/<package>, including files intended to be copied into /etc/opt/<package> and /var/opt/<package> as well as reserved directories in /opt.

The minor restrictions on distributions using /opt are necessary because conflicts are possible between distribution-installed and locally-installed software, especially in the case of fixed pathnames found in some binary software.

Those possible conflicts are also another reason why I prefer running FreeBSD over other non-BSD operating systems. Keeping installed packages and the base system separate prevents whole classes of problems, and makes the system easier to maintain.

The System V Interface Definition

I was invested in researching this hierarchy, so I went to the source as described in the above Rationale section and downloaded the System V Application Binary Interface documentation from… SCO. That and Sun Microsystems break my heart. Under the File System Stricture and Contents section on page 189:

The directory /opt of the / file system is the point of access to the /opt subtree. This directory subtree contains files installed by add-on application packages. The following describes the structure of the /opt subtree:

  • /opt – The top directory of the /opt subtree.

  • /opt/pkg/bin – Executable files provided by application packages and invoked directly by users.

  • /opt/pkg – Where pkg is the abbreviated name of an add-on software package, contains all the static files installed on the system as part of that package.

Filesystem Hierarchy Standard

I wrote this post a fortnight ago, but left it in drafts so I could clean up the formatting. Since then I was reading this thread on the Gentoo forums about why Portage defaults to /usr, given it’s stated design inspiration was the FreeBSD port system that uses /usr/local. This lead to a PDF of the latest Filesystem Hierarchy Standard. Page 12 stated:

/opt is reserved for the installation of add-on application software packages. A package to be installed in /opt must locate its static files in a separate /opt/<package> or /opt/<provider> directory tree, where <package> is a name that describes the software package and <provider> is the provider’s LANANA registered name.

This to me is key, and a good place to end this post:

Distributions may install software in /opt, but must not modify or delete software installed by the local system administrator without the assent of the local system administrator.


The history for where I started using this is no less vague than when I started, but I love this kind of research. It gets me thinking what other *nix assumptions I’ve been living under over the last decade.

Am I using /opt correctly? I’d say at best, maybe.

STRICT_ALL_TABLES now default in MariaDB

Some tables joining. HAHA! Hah! Hah.

I’ve used MySQL and its offshoots in tandem with Postgres for most of my career and for personal projects, but with the fairly large caveat that it’s running in this mode, defined in my.cnf:

sql-mode = "STRICT_ALL_TABLES"

From the MariaDB docs:

Strict mode. Statements with invalid or missing data are aborted and rolled back. For a non-transactional storage engine with a statement affecting multiple rows, this may mean a partial insert or update if the error is found in a row beyond the first.

I never had the data loss or issues that Postgres friends warned me about, even with large-scale deployments. But I will readily admit that not accepting this strict mode by default made me nervous. Enabling it never made difference in operation, so why not?

(That’s not strictly speaking true, I trialled a few self-hosted blog platforms and wikis back in the day that stopped having problems when I turned this off. But that told me that I shouldn’t be using them).

I noticed recently though that this has changed:

With strict mode set (default from MariaDB 10.2.4), statements that modify tables (either transactional for STRICT_TRANS_TABLES or all for STRICT_ALL_TABLES) will fail, and an error will be returned instead. The IGNORE keyword can be used when strict mode is set to convert the error to a warning.

This is great news, and I’m only a few years late celebrating. The Tyranny of the Default means people weren’t running in this mode before. Being default means more people will be.

3801 is back in Thirlmere

Play 3801 Return to Thirlmere 24 01 2020

I watched so many VHS tapes of this iconic steam locomotive growing up. Perhaps the most memorable was when it ran with the Flying Scotsman 4472 around Australia for the Bicentennial.

She’d been having boiler problems for many years, so it was such a delight to see her steaming again under her own power. Clara and I will need to head back there to check her out once she’s been repainted.

Processing text

An innocuous question from a colleague about what tools I use to process text got me thinking. I don’t have a hard and fast rule for when to preference one method over another; mostly it comes down to:

  • what type of text I’m dealing with
  • where the text is coming from
  • the urgency of the results
  • whether it needs to be reproduced or shared
  • what I feel like doing at the time!

In no particular order, I use these methods:

  • Manual processing, especially if it’s just a few lines or a small file. The law of diminishing returns long taught me that futzing around with a tool can take far longer.

  • In a text editor, such as Vi(m) or perhaps Emacs in the future. This is especially useful for substitutions, or rearranging large blocks of text, or if the lines don’t have a structure you can easily parse or differentiate.

  • Shell scrips or inline Bourne or OpenBSD oksh, using awk, sed, tr, grep/ag, and pipes. I’ve been known to write cruddy, once-off XML parsers in them to process data, which you should never do.

  • LibreOffice spreadsheets. awk is brilliant for processing columns of data, but if you want to do some visual sorting and data selection, sometimes a spreadsheet really is easier. Exporting to csv is also a cinch.

  • SQLite3 databaes. They’re cheap to make and import data into, and then you’ve got standard(ish) SQL queries. I only started doing this recently, but for certain types of data it’s very quick.

  • Perl. I’ve told myself I need to learn Python or improve Ruby, but Perl hashes are stupendously useful and unreasonably flexible for mapping data structures and pulling out relevant information. Plus then I get XML, JSON, YAML, TOML, and other parsers for free.

What’s less clear is where one tool’s domain ends, and another begins. Sets on a Venn diagram would overlap more than not.

Australian FTTN and metadata surveillance

Nico Arboleda reported for CRN Australia:

According to the ACCC’s latest Measuring Broadband Australia report (pdf), FTTN only reaches 81.9 percent of the promised maximum download speeds and 78.4 percent of the promised upload speeds. … FTTN also recorded the longest outages of the three … the ACCC said that many FTTN connections “still don’t come close to performing as promised”.

I can confirm. My 100 Mbps NBN FTTN connection, aside from being an unpaletable alphabet soup of legacy networking abbreviations, barely gets above 62. I’d be sorely tempted to save $15 a month and go for a 50 Mbps plan if it also didn’t bring my uploads in line with what we had in Singapore two decades ago.

And Stilgherrian reported for ZDNet Australia:

The Commonwealth Ombudsman, Michael Manthorpe, has revealed that law enforcement agencies are being given the full URLs of web pages visited by people under investigation. Australia’s mandatory telecommunications data retention scheme was meant to deliver only so-called “metadata” to the cops and spooks. Under the scheme, a warrant is not required. But according to Manthorpe, the “ambiguity around the definition of content” means that agencies might effectively be receiving the content of communications.

There is so much context in a URL, especially if you have an encoded GET request.

Filing both of these under we all told you so!

Both sides to saving money on cafés

Choice in Australia tweeted an infographic yesterday concerning the quantity of coffee one consumes, and how a fraction of that money could be put towards a machine to make them at home or the office. I retweeted it with a comment, and it started a couple of interesting discussions.

The comparison makes sense

Clara and I use a virtual envelope budget system and track expenses down to the cent, so I was completely onboard with the line of reasoning Choice advocates. I identified as early as 2015 that I was spending hundreds of dollars each month on coffee, and cutting down to one a day immediately saved me real money that could be put to better or more productive use.

(Not to mention the environmental cost of all of those takeaway containers that I also learned far too late are mostly unrecyclable. Take reusable cups, people!)

Andrew Fengler who I met at AsiaBSDCon last year summarised the value well:

I think it’s a fair point to bring up though. If cafes are your thing and you don’t mind paying for it, good for you. But for people who haven’t thought about it, the nickel-and-diming of convenience food/drinks can sneak up fast, and coffee is a good way to demonstrate it.

John Roderick would class this as an eel: his term for an inexpensive but recurring expense that creeps up on you and sucks out your finances unless you’re paying attention. I wasn’t for several years, and paid the price.

Compaing it to the bigger picture

But armed with the knowledge of how much it costs, there are a couple of other angles to this.

Cafés make me happy. I love working in them, I enjoy conversing with the baristas who greet me with a smile in the morning, and in the afternoons they force me to leave my desk and go outside for fresh air and a bit of exercise. The calculus of comparing making coffee to cafes is akin to saving money by looking at a tree photo, instead of walking in a forest.

As Andrew did above, Georgina summarised my thoughts better than I could:

Also why is this always about money. I don’t always purchase cafe coffee, but I appreciate that someone created it for me. I am paying for the service as well. If I thought about the monetary value of things all the time then I might go a little mad.

There’s also some context around this in Australia. We’re now living with some of the least affordable housing in the world, both in absolute terms, and relative to wages. The global trend of identifying millennials with smashed avocado toast literally started with journalists in Australia theorising that if people stopped buying frivolous things like food, they could afford a house deposit.

(Choice are a valuable outlet for information, and I’m by no means equating them with this poorly conceived and logically flawed argument thinly disguised as an attempt at humour. But it does go part way to explaining why yonger Australians bristle when they’re faulted for being locked out of home ownership. Never mind the gross tax structure that incentivises parking money in an unproductive asset over performing work, and record low interest rates spawned by a government seemingly allergic to the mere thought of economically rational fiscal policy that even the Reserve Bank is screaming for. That became a rant faster than I intended).

I can’t remember who first proposed the idea, but I mentioned in my original tweet that there are also larger fish to fry when it comes to saving money, such as organising your superannuation, CPF, 401(k), or other retirement fund into one that charges lower fees and behaves ethically. David Willanski from Adelaide mentioned:

True, my current super fund’s fee is fifth the old fee, and my fund is now on track to hit my target before I can access it. Under the old fund I’d have to keep working for 5-10 years after I hit retirement age.

So in conclusion, is a phrase with three words

It’s extrondilarily valuable to keep abreast of where your money is going, and if you’re not tracking your expenses you may be surprised and perhaps a little horrified how much is going to things you may not expect. That said, if you’ve budgeted for it, you also shouldn’t feel guilty about spending money on something if it makes you happy. Which is basically what Andrew said at the start, in far fewer words.