Monthly Archives: April 2018

How to use CockroachDB with Emacs SQL Mode

old skool

(Image courtesy Sajith T S under Creative Commons license.)

In this post I’ll describe how to hack up the Postgres config for sql.el to work with CockroachDB (aka “CRDB”).

In the future I’d like to submit code to sql.el to make CRDB a fully-supported option, but for now this is what I’ve been using.

Note that these instructions assume you are running a secure local cluster. If you are running insecure, you can avoid all of this and just M-x sql-postgres and use most of the defaults, modulo port numbers and such. It uses psql on the backend which works because CRDB speaks the Postgres wire format.

However, once you get up and running for real and want to use a secure network connection, it’s easier to use the cockroach sql client. That’s what we’ll configure in this post.

(It may be possible to configure psql to use the CRDB certs, but I don’t know since I haven’t looked into it. Also, keep in mind that I have not tested this setup over the network yet – only on my local machine.)

Step 1. Modify basic config

Since the client is invoked by comint as two “words”, cockroach sql, you have to mess with the options a bit.

First set the cockroach binary:

(setq sql-postgres-program "cockroach")

Then invoke the SQL client with the first arg, and pass options in with the rest. The certs directory is where your encryption certificates are stored. Since this is an ephemeral local cluster I’m using the temp directory.

(setq sql-postgres-options
      '("sql" "--certs-dir=/tmp/certs"))

Finally the login params are pretty standard. My local clusters are not usually long-lived, so I just use the “root” user. This would not be recommended on real systems of course.

(setq sql-postgres-login-params
      '((user :default "root")
        (database :default "")
        (server :default "localhost")
        (port :default 26500)))

Step 2. Modify the Postgres “product” to work with CRDB

Sql.el calls each of its supported databases “products”. for whatever reason.

In any case, here’s how to modify the Postgres product to work for CRDB.

First we need a new function to talk to comint.el. (See the bottom of this post for the definition since it’s longer and not interesting.)

(sql-set-product-feature 'postgres
                         :sqli-comint-func #'sql-comint-cockroach)

The usual comint prompt regexp things. This one isn’t that well tested but works on my machine ™ … so far.

(sql-set-product-feature 'postgres
                         :prompt-regexp "^[a-z]+\@[a-zA-Z0-9\.-_]+:[0-9]+/\\([a-z]+\\)?> ")

I don’t really know what this does. The CRDB prompt is not necessarily of a fixed length so it doesn’t really apply. It seems to have no effect, I just cargo culted it from some other DBs. Probably not needed.

(sql-set-product-feature 'postgres
                         :prompt-length 0)

Regexp to match CRDB’s little continuation marker thingy:

(sql-set-product-feature 'postgres
                         :prompt-cont-regexp "^ +-> ")

Set the “end of a SQL statement” character to the semicolon. Some of the other DBs have some pretty fancy settings here, but this seems to mostly work.

(sql-set-product-feature 'postgres
                         :terminator '(";" . ";"))

Command to show all the tables in the current database.

(sql-set-product-feature 'postgres
                         :list-all "SHOW TABLES;")

And finally, this is the comint function we need to work with CRDB:

(defun sql-comint-cockroach (product options)
  "Create comint buffer and connect to CockroachDB."
  (let ((params
         (append
          (if (not (= 0 sql-port))
              (list "--port" (number-to-string sql-port)))
          (if (not (string= "" sql-user))
              (list "--user" sql-user))
          (if (not (string= "" sql-server))
              (list "--host" sql-server))
          options
          (if (not (string= "" sql-database))
              (list sql-database)))))
    (sql-comint product params)))

It’s all about the BATNA

8109693804_d735a19f95_z
 
(Image courtesy Ismael Celis under Creative Commons license.)

It seems like there is a constant stream of articles being turned out about how we’re all going to be working in Amazon fulfillment centers and holding in our pee for 12 hours while we dry-swallow bottles of Aleve and live in fear of our slave-driving lower-level warehouse managers.

You can read a lot of these types of articles on sites like the Verge for some reason. (I am beginning to think of them – at least in part – as “nominally ‘tech’ but actually ‘tech pessimism'” sites.)

Meanwhile, there is another – perhaps-less-frequent but still influential – stream of articles about how companies “can’t find” good employees, they “can’t hire”, millennials want “too much” from their employers, Americans “won’t work hard” and “don’t have the necessary skills” for “the future” ™, and so on.

You can probably read these articles in the Wall Street Journal.

The NY Times, that bourgeois rag, will happily run both types of article. (Parts of its demographic hold both views, in some cases simultaneously, and hey, the ads pay either way.)

Unfortunately there is an important concept taken from business negotiation called BATNA that is almost never even mentioned in either type of article – even though it usually explains the behaviors chronicled in the article! I could almost forgive this if the writer had studied journalism and not economics (although not really), but if they have any economics or business background at all it’s just criminal.

What is BATNA though, really? Well you can read the wiki article for more information, but it is an acronym that means “Best Alternative To Negotiated Agreement”. In other words, it’s a way of thinking during any type of negotiation about questions of the form “What’s my next best option if this deal falls through?”

For example, if you are an employer with a lot of cash on the balance sheet you can afford to wait a few quarters (or years) until employee wages come down to a level you find more appealing, maybe. If you are a wage-earning employee, you probably cannot. (Not to mention that it’s probably cheaper for companies to have their PR people push articles in the WSJ about how hard it is to hire than it is to just raise wages until hiring picks up.)

P.S. Special thanks to Andrew Kraft, who gave a great talk on BATNA and other related topics a few years back at AppNexus. Without his talk, I might never have heard of this magical acronym.)

Thoughts on Rewrites

As a user, when I hear engineers start talking about doing a rewrite of an application or an API that I depend on, I get nervous. A rewrite almost never results in something better for me.

Based on personal experience, I have some (possibly unfair) opinions:

  • Rewrites are almost always about the engineering organization
  • They are almost never about the end users
  • Inside any given organization, it’s very difficult for people to understand this because their salary depends on them not understanding it
  • Attempts at rewriting really large apps rarely get to a state of “fully done”, so the engineers may end up with a Lava Layer anyway
  • Except now users are angry because features they depended on are gone

Why am I writing this? Because I’m still mad they took away my Opera.

Until recently, I’d been using Opera for over a decade. By the time Opera 12 came out, it was amazing. It had everything I needed. It was lightweight, and could run on computers with less than a gig of RAM. With all of the keyboard shortcuts enabled, I could slice and dice my way through any website. I could browse the web for hours without removing my hands from the keyboard, popping open tabs, saving pages for later reference, downloading files. It was amazing.

Oh, and Opera also had a good email client built in. It was, like the browser part, lightweight and fast, with keyboard shortcuts for almost everything. It also read RSS feeds. Oh, and newsgroups too. It had great tagging and search, so you could really organize the information coming into your world.

Then they decided to take it all away. They didn’t want to maintain their own rendering engine anymore. They let go of most of the core rendering engine developers and decided to focus on making Yet Another Chromium Skin ™. No mail reader. Most of the keyboard shortcuts gone. Runs like shit (or not at all) in computers with 1 gig of RAM.

I realize I got exactly what I paid for. But if you are wondering why users get twitchy when engineers and PMs start talking about rewrites, wonder no longer.

After Opera stopped getting maintenance, I switched back to Firefox, and fell in love with Pentadactyl, the greatest “make my browser act like Vim” addon that ever was.

Can you guess what happened next? Yep, they decided to rewrite everything and break the addon APIs. I know they had some good reasons, but those reasons meant the end of my beloved Penta. Now I am back to using Firefox with Vimium (like an animal), and I suppose I should be grateful to have even that.

And don’t get me started on my experiences with “REST APIs”, especially in a B2B environment.

Related:

Set up Gnus on Windows

There are many “set up Gnus to read email from Emacs on Windows” posts. This one is mine. Unlike the 10,000 others on the internet, this one actually worked for me.

A nice thing is that, with a few tweaks, this setup also works on UNIX-like machines.

PREREQUISITES

OVERVIEW

At a high level, the way this all works is that:

  • A mail server is out there on the interwebs somewhere
  • stunnel runs locally, and creates an encrypted “tunnel” between a port on the mail server and a port on the local machine

  • Emacs (Gnus) connects to the local port and fetches mail from there (as far as it knows)

STEP 1. INSTALL AND CONFIGURE STUNNEL

Download and install stunnel for Windows:
https://www.stunnel.org/downloads.html

I use Fastmail, so the following configuration worked for me. I put it in the file ‘C:/Users/rml/_stunnel.conf’.

# Windows stunnel config

# 1. GLOBAL OPTIONS

debug = 7
output = C:/Users/rml/Desktop/stunnel.log

# 2. SERVICE-LEVEL OPTIONS

[IMAP (rmloveland@fastmail.fm) Incoming]
client = yes
accept = 127.0.0.1:143
connect = mail.messagingengine.com:993

[SMTP (rmloveland@fastmail.fm) Outgoing]
client = yes
accept = 127.0.0.1:465
connect = mail.messagingengine.com:465

If memory serves, you will need to do some messing around with stunnel to get it to read from a config file other than the default. Luckily it puts a little icon in the notification tray that you can right-click to get it to do things such as edit the config file or view the log file. From there, you should be able to get the config in shape as shown above.

In the particular case of Fastmail, you’ll need to set up an app password via its web UI. See your email provider’s documentation for more information.

STEP 2. CONFIGURE GNUS

On the Emacs side, we need Gnus to ask the right port on the local machine for mail. Here’s what I did:

(setq send-mail-function 'smtpmail-send-it
message-send-mail-function 'smtpmail-send-it
smtpmail-smtp-server "localhost"
smtpmail-smtp-service 465
smtpmail-stream-type nil
smtpmail-default-smtp-server "localhost")

This is the part of your Gnus config that tells it how to talk to stunnel; all of the other Gnus things are beyond the scope of this article. If you need more Gnus info, you should be able to get something going using the EmacsWiki:
https://www.emacswiki.org/emacs/CategoryGnus