PugSQL: new Python/SQL bridge

One of the themes on this blog is “I want a really simple interface to databases for Python”. But then I’m never happy with the options. ORMs are too magic, Records is weird and limited, etc etc.

A friend of mine has written a new library: PugSQL. It’s inspired by Closure’s HugSQL. You write templated SQL statements in separate files, anything you want; selects, updates, etc. PugSQL wraps those so you can easily fill in the template parameters, call the SQL, and process the results as Python objects.

It seems like a good approach to me. Very little magic. Direct access to the actual SQL. You’ll probably end up writing some boring simple SQL that some ORM could have auto-generated but then again you’ll never be unpleasantly surprised, either.

I don’t understanding looping with Promises

Did some hair-pulling work with Javascript Promises recently. I’m calling a paginated database API (CloudKit JS), so I want to fetch N results at a time until there are no other results to fetch. In simple synchronous programming you’d do this with a while loop. But Promises can’t be reused or iterated in this way, so instead you do something that amounts to tail recursion. The Cloudkit docs have a handy example:

database.performQuery(query, { resultsLimit: 10 }).then(function(response) {
    if (response.hasErrors) {
        // Insert error handling
        throw response.errors[0];
    } else {
        // Insert successfully fetched record code
        if(response.moreComing) {
            return database.performQuery(response);

I use code much like this for real and it works. But it’s complicated! database.performQuery is an asynchronous method in CloudKit JS, it is a Promise. This code creates a new query promise, then chains it via .then() to some synchronous code that processes the response. The last thing the processing code does is check if there’s more results to come (the pagination API) and if so it creates a new Promise and returns it. Which causes the Promise execution framework in the browser to go to resolve that new promise next.

It feels very much like tail recursion. I get conflicting answers on whether it genuinely is recursive, in the sense of some Javascript stack or Promises chain growing larger and larger with each request. It’d be simple enough to do basic tail recursion optimization to eliminate that growth, just no idea if implementations do it.

My problem

So I’ve got code working. My problem is the moment I try to do something more complicated, I get lost. As a practical example; what if I want to inject a one second delay between each call to that performQuery() function? I can make a new promise with setTimeout() and chain it in there, but half the time I type the code to do that I get it wrong. I got lucky and it worked once and I don’t really understand why. Then try passing arbitrary parameters conveniently through the call chain; you can, but it’s hard because Promises only take one argument to pass along so you have to stuff your parameters into a new Object. And I haven’t even tried to reason about getting error handling right, although I think if you’re naive and don’t do much of anything then the default reject/catch behavior will probably work.


There’s a new hotness coming in Javascript with the async and await keywords along with async interation. Here’s another example of it in use. I haven’t ever used this stuff myself and am not sure if it works yet in browsers.

Update: some advice on Twitter to use async/await, that it has decent browser support.

Thinking asynchronously

This is an unsatisfying blog post where I investigated a problem and don’t really have a solution. Honestly, most of the time I do async programming in any language I feel the same way. Python, Javascript, even select()-based C code back in the day. My model of programming computers is so heavily based on imperative execution I always feel all twisted when I program asynchronously.

I need to do a series of exercises fully in an async model and get comfortable. Either that or find a good Javascript wrapper for Promises that provides higher level components like “iterate this set of promises” and the like.

Android new user notes

After twelve years of iPhone use I made the jump last week to Android phones. I’ve been curious to see how the other half lived and Google had a one day sale on the Pixel 3 that was enough to make the jump. (The new Pixel 3a just released is a hell of a good price.) This marks a slow migration for me away from Apple products. They aren’t quite the quality innovator they used to be, and their closed approach to ecosystems has always bummed me out.

So far I like the Android quite a bit. Everything I expect from a fancy phone works. And I’m enjoying discovering new things. I think part of the fun here is just I’m revisiting how I use my phone from the bottom up; maybe I’d think it was juts as neat if I were switching to iOS for the first time.

Anyway, here’s a bunch of thoughts I collected as I learned the new platform


The first setup where it copies from the iPhone is slick. Particularly like how it matches the apps you have. Sadly it can’t copy app data or state like logins.

Pretty much every single app I cared about on my iPhone also had an Android version. About 75% of those were found by the migration tool, the rest I had to install by hand. I only had to buy one app; Paprika, the recipe software. Most software I pay for has an account subscription model like Spotify, not a one-time app purchase model. The one app I could not get on Android was Overcast, no surprise given its author’s Apple fanboy status. Fortunately there’s lots of other podcast players for Android. PocketCasts is probably the best choice. I’m trying Podcast Addict for now but it’s a pretty rough UI to love.

It’s nice being in Google‘s loving embrace; Gmail, Photos, Drive, etc all well integrated. Apple’s policies are better for users since Apple isn’t an advertising company. But their services like iCloud are not very good, I’m really glad to not be living under iCloud anymore. Google’s services are good and I’ve been using them for years, nice to have a phone that wants to integrate with them.

I like the home screen! I particularly like the ability to have widgets from apps draw little decorative things on the home screen. I also like the back button. This bit of UI is a source of confusion for many, and often myself, because it can both go back in a single app and also go back between apps. But it mostly seems to do what I mean when I press it. The shortcuts for switching between apps are good too.

Android apps are small! They are 1/3-1/5 the size of their equivalent iOS apps. For an extreme example, Facebook is 450 MB on iOS and maybe 50MB on Android. It’s common to see many Android apps < 10MB download. I haven’t read a completely convincing explanation why, but see here for some ideas. There’s also a new tool to make builds even smaller. The smaller size definitely results in noticeable bandwidth and storage savings; maybe RAM too.

The 1Password integration in the OS is terrific, particularly with apps. Way better than what iOS has tried to kludge together. More generally the integration between apps in general is quite nice on Android, it’s fairly easy to share data from one app to another in a way that never seems to work on the iPhone. The “intents” system lets apps publish APIs for other apps to use. There’s even complex automation for chaining apps together. iOS has something similar now in Shortcuts, but I’ve never used it.

Android has a file system! You can browse it with Amaze File Manager. Apps naturally share files through it, like Sonos playing files Podcast Addict downloaded. You can also bridge out to Google Drive fairly simply and then have the same files on your desktop computer. iOS has always had a notion of files too but they are well hidden and seldom (never?) cross-app.

I can run a real Firefox! With real Firefox addons! None of this “must use Apple’s embedded WebKit” crap. Firefox on iOS is a heroic effort and has a lot of useful Firefoxy features, but when you pull the mask away it’s still Old Man Smithers underneath. There’s something to be said for platform unity, but there’s something to be said for competition too.

Notifications are awfully spammy. Every app (say, Amazon Store) wants to send you notifications all the time. Even with something where it could be useful (say, Washington Post) it’s mostly annoying. It takes some looking but you can extensively customize notification behavior. Some apps have an in-app bunch of settings. And the system has Notifications settings on a per-app basis. Each app has notifications one of many channels (often only one) and for each channel you can configure whether it makes sounds, vibrates, etc.

The phone definitely feels more hacker-friendly. In particular there’s a reasonable chance interesting apps are open source, like GPS Logger. I can’t think of the last open source iOS app I saw. Also I like that I can realistically write code for my Android phone using any computer, not just MacOS. My brief look at Android Studio is encouraging, it seems like a solid IDE.

I gather Android version proliferation is a real problem. I’ve got Pie, which is version 9 of the OS. The development kit tells me if I write code for this version less than 1% of users will be able to be run it. Version 6 only has 63% of users! Also from what I see online the UI changes radically between versions. Always improving, hopefully, but change has a cost.

I also gather vendor Android modification is a real problem. Carrier-sold phones in particular have all sorts of crap on them, as do a bunch of the cheaper Android phones. Some of that crap is locked and hard to remove. I bought a Pixel because I definitely didn’t want to mess with Verizon or Xiaomi’s idea of how they could make an extra $20 off me. The only other brand of phone I’d consider buying is a Samsung Galaxy Note.

I haven’t gotten too deep into lower level system hacking, but it’s nice that Android lets you do it. Even without rooting the device. The is pretty good if you want a quick look at the (non-root) filesystem and some easy upload / download. The GSam Battery Monitor is amazing if you want low level battery watching stats. Even includes using some just-short-of-root debugging interfaces for extra detailed stats.

Android Auto seems fairly good. Not quite as slick as Apple CarPlay, but then not as proprietary either. (Apple for years prevented any other maps from displaying in CarPlay, for example.) One nice thing is you can run Auto even when you’re not plugged into a car display, it has a car-optimized display right on the phone screen. Good for testing or using in an older car without the ability to plug a phone in.

Pixel 3 hardware

In general the hardware is lovely. Flat slab of glass, no distinguishing features except an ugly bright orange button (a feature of the pink-backed phone). Under the hood the Pixel 3 has gotten some criticism for being underpowered for the price but I don’t have any perception of that. The camera is as lovely as people say. Coming from an iPhone I don’t notice the lack of an SD card although in retrospect I wish I had one. Also baffled why there’s no dual SIM; so useful! The new eSIM alternative is proving to be an inadequate kludge.

Cables continue to be a problem. The Pixel 3 has one USB-C port and nothing else (like a goddamn Macbook.) Worse though is I don’t have a USB-C port on any computers here, and they don’t ship with an adapter to a male USB-A connector. They do have an adapter for a female USB-a port, which you use when syncing two phones together, but I had to order the rest. Nevermind that most every USB charger, automobile port, radio port, etc is not USB-C. Argh. At least it’s not fully proprietary like Lightning.

It gets trickier with charging. For wired charging Pixel 3 supports 18 Watt “rapid charging” which apparently is USB-PD, an open standard only now being implemented (Apple’s new phones use it too). AFAICT USB-PD is a USB-C only thing in practice, older type A cables don’t allow USB-PD 2.0 or above. Older fast chargers on a type A cable use Qualcomm’s Quick Charge or some other proprietary thing; the best I’ve been able to do is 8W from them, maybe 10.

It gets more confusing with wireless charging. Google has a proprietary system that allows 10W wireless charging. Other standard wireless chargers (there are such things?!) are limited to 5W. In theory third parties can build 10W compatible wireless chargers but there aren’t any yet.

Of course there’s no analog headphone jack, which is as obnoxious as I feared. But I can live with it. Also the Pixel 3 apparently has no capacity to send HDMI out a wire. The USB-C adapter for headphones works OK but has different wiring from Apple’s proprietary, so I can’t use a full Apple headset with it. The supplied wired headset is fine, the fit sure is better for me than Airpods. Also the sound output is way louder than the iPhone’s ├Žnemic headphone out.

I don’t much care for the fingerprint sensor in back. It means you have to pick the phone up to unlock it. Also the sensor is not accessible on the Pixel Stand; you have to pick up the phone. Don’t miss not having a hardware button in front for the home screen though. And the sensor itself works well. It doubles as a shortcut for “show notifications” too if you turn that feature on.


I like my new Android phone! I was going to spend weeks migrating; I ended up switching over the day I got it set up and haven’t missed the iPhone once. In most ways it seems to be fuctionally equivalent to an iPhone. I’m surprised the UI polish is better than I feared. And I like the relative openness and hack-friendliness of the Android.

My next goal is to start writing apps for the thing. Will no doubt have lots of notes when I get to that.

CloudKit JS: hasErrors vs isError

Well this is a nuisance. A lot of CloudKit requests have two different ways of signaling errors when you make Javascript calls to the iCloud API.

The request can look like it succeeded and returned a Response object of some sort. But instead of the response you want it can have the hasErrors flag set, in which case there’s a whole array of errors objects. This method is what’s shown in example docs.

The request can also just fail and the method returns a CKError object instead. This object has an isError property, also an isCKError. I’m not positive but I think this mostly catches client-side programming errors, like when I put in an integer where a string array was expected.

In neither case does the Promise reject; it resolves with some sort of error status. So for now every call to a Promise in CloudKit JS I make has right after it:

        if (response.hasErrors) {
        } else if (response.isError) {

I wonder if there’s a better idiom.

Dismal programming language law: no matter how many language features there are for signaling errors out of band: Exceptions, Promises rejects, etc.. Programmers will ignore those and return error values in-band instead.

Installing esridump on Windows

I just wrote these notes up for a friend. esridump is a Python tool that will take the URL from an ESRI ArcGIS REST server and dump all the data in it to a GeoJSON file. It works by doing REST queries on the predicate “where 1=1;”, the only subtle thing is you have to paginate the requests because there’s a limit to the number of results from one query. It’s a nice tool; there are several others floating around out there like it.

Installing it is basically as simple as “pip install esridump”. It has very few dependencies, no GDAL or other GIS giant libraries. But I wrote these detailed notes for a friend of mine:


  1. Install Python 3.7.3. Make sure to click “Add Python 3.7 to PATH” when installing.
  2. open a cmd shell, everything else is in there
  3. cd Documents
  4. mkdir pyesri
  5. cd pyesri
  6. python -m venv venv
  7. venv\Scripts\activate.bat
  8. pip install esridump
  9. venv\scripts\esri2geojson.exe http://SERVER-URL/ out.json

GPS Logger app for Android

I’ve made the switch from iOS to Android. One thing that means is I need a new GPS tracker; my own Wanderings app is iOS only. I may end up writing an Android custom app myself to do that.

For now I’m using the excellent GPS Logger app. It’s a very flexible passive location tracker, open source, lots of control over exactly what kind of data to log and where to upload it to.

It has three sources of location updates:

  1. GPS/GNSS locations. Honest to goodness satellite fixes
  2. Network locations. Info based on wifi and cell tower fixes
  3. Passive locations. Any time any other app gets a location, GPS Logger gets a copy

I currently have #2 only turned on and it seems very low battery impact, certainly OK to leave on for 24 hours. Once a minute it wakes up, asks the phone where it is, records the data into an internal log file, then goes to sleep. The location data seems pretty inaccurate though, I need to look closer.

I tried #1 for awhile and it seems higher power impact; not sure you can leave it running for 24 hours. (Update: it’s not nearly as bad as I first thought, and might be fine for daily use. Even once a minute.) Data is quite accurate but demanding. It wakes up the GPS chip, waits for 1-2 minutes for a good fix, then records the location. You can also tell it to leave the GPS chip on always, but there’s a warning about “dramatically decrease the battery life”.

#3 seems like a freebie, of course you should use it. I only have it turned off because I’m trying to keep it simple so I understand what GPS Logger is doing itself.

They also have abilities to filter points based on reported accuracy of the fix, and how far you moved. And there’s a way to tell it only to log if the accelerometer detects the phone is moving.

What I haven’t found in GPS Logger is anything like the “significant location changes” API that Wanderings uses in iOS. Nor anything like geofences / regions. Those may be in the Android API and just not in this app.

Update: after a few days playing around with GPS Logger I have some extra notes.

I’ve been running the GPS Logger app on Android in full logging mode for a few days now and its impact on battery life is neglible. My whole phone only uses ~40-50% of its battery in 24 hours. 0.1% of that is attributed to GPS Logger itself. 42% is the wifi radio, 25% if the screen, 10% is the OS.

GPS Logger is currently logging my position once a minute. I’ve told it to use both GPS and “network locations” (wifi/cell). It seems to request both types of position at once and almost always the network location answers first, so it never logs the GPS. I’ve run it in GPS-only mode too to get real GPS fixes, those often take 20+ seconds and don’t work great indoors.

The most problematic thing is that GPS Logger doesn’t log points at all for hours. AFAICT this is a significant problem in Android phones in general, that background apps often don’t run when the phone is in “doze mode”. It mostly happens in the middle of the night where I’m definitely not using the phone so it might be the “right thing” but it’s a problem. I’ve read various hacks and workarounds, see the GPS Logger FAQ. GPS Logger has a gimmick where it constantly has a notification on screen to try to stay more lively.

Funny thing; some apps I don’t even actually use are consuming more battery than GPS Logger. Like Maps.Me was using 2% a day until I went in and told it to be “very low power”; I haven’t even launched it! Google Fi also uses 1% even though I’m not using it.


After getting NextCloud set up I was uncomfortable with how complicated it was. So I gave SyncThing a try and it’s much more to my liking. It’s a much simpler system. NextCloud has a full database and users and passwords and APIs and stuff. SyncThing just synchronizes folders. In that way it’s a lot like Unison, or FreeFileSync. (More complicated than rsync, since it maintains state between synchronization runs). But it’s built for network synchronizing.

Installation is simple. Every machine that wants to sync a folder runs the SyncThing binary in the background. That publishes a nice Web UI on port 8384, also an application specific socket on port 22000. Getting two machines to sync is a little weird. Every machine has a name and you can just tell machine 2 “sync with machine 1”. A dialog pops up on machine 1 then to authorize the connection. No visible passwords, but presumably some public key crypto under the hood. Once the machines are talking to each other you then set up synchronizing folders.

I may have made things complicated myself but the folder setup seems complicated. For every new machine I add to my sync cluster I had to go through each folder and share it. There’s something called an “introducer” where one machine will offer a list of folders to the other machine, but I either don’t understand it or broke it somehow.

I also made things more complicated than necessary by disabling machine discovery. I mentioned way above machines have names; how do you find a machine on the Internet given its name? There’s some global discovery protocol built under the hood, a peer to peer network sharing names. It’s quite neat but I’d rather not be quite so promiscuous so I turned it off. You can manually configure via IP addresses if you want. (They also have a relay network for cases where two machines are so firewalled they can’t connect to each other. Neat!)

I haven’t stressed the sync algorithm too hard but it seems to be working for me. It even does something reasonable for conflicts.

I took a quick look at GitHub and the primary author is Jakob Borg, aka calmh. He also has a consulting business called Kastelo. He seems super nice and helpful on the community forum answering lots of questions. Development started late 2013.

All in all I’m quite impressed with SyncThing. It’s simpler and less featurful than DropBox/NextCloud/ownCloud, which is exactly what I want. Keep two folders in sync, that’s all I want.

(A thorough blog post would include an investigation of Resilio, formely BitTorrent Sync. By all accounts SyncThing is quite similar to it.)