Any interest in a series on using sqlite3 with Keyboard Maestro?

In the beginning, all my macros used variables, text, and big ugly regex to process out what I needed to get to. Over time, I realized that this was the hard way of doing things: While functional, debugging was hard, and any little change in the macro could require rewriting a lot of regular expressions.

Then, as I was working on an update to Quick Web Search, I had a thought to see what might be possible with sqlite3, a free database app included with every Mac. At the time, I knew a reasonable amount of simple SQL, but nothing about integrating sqlite3 with Keyboard Maestro. To say I struggled would be an understatement.

But once I got my head around things, the end result was a remarkable improvement. What had been literally dozens of regular expressions to add, remove, and modify shortcuts in the list became a few very simple SQL calls to the database, with just a bit of regex to format the data in the right format. And things that were slow to do with certain huge variables would happen instantly, or nearly so, when queried from the database.

Databases are also a great way to store lots of user settings in a permanent manner. Instead of creating multiple globals, or an array-like global that needs regex processing to use and update, a simple table can keep your user's preferences and provide simple updating. When the macro launches, read the settings and split them into instance variables. If they get changed, write them out to the database, etc.

I'm also using sqlite3 in MacroBackerUpper, and coming updates to both Macro Usage Counter and The Decrufter will add database functionality, to improve speed and offer additional features.

I have a tutorial series in mind that would walk through the process of setting up a new macro that involved the use of a multi-table sqlite3 database, but before I spend any time on it, I'm curious if there's enough interest to make it worthwhile.

If you'd like to see such a tutorial series, let me know.

-rob.

13 Likes

I'd be very interested in such a tutorial Rob, I know next to nothing about using sqlite3 but have wondered for some time about the potential for using it with KM, but the biggest hurdle is knowing where to start.

Would you also cover the basics of setting up the database for use with KM?

Hi Rob, count me in. I have seen the remarkable feat of your MacroBackerUpper in action and I am very interested. All the best.

1 Like

I have in mind a project and I would be very grateful if I could build on your experiences in using sqlite rather than have to start from scratch. So - yes please and thanks for all your brilliant contributions to the KM-o-sphere :grinning:

2 Likes

I am also interested! Thanks!

I started using SQLite3 databases last fall and it has been a game changer for me. That being said, my knowledge and use of them is pretty basic, so I’d be interested in learning more as well.

Alright, that's good enough for me, I'll get started :). The thought is that the series will cover everything required to do the following:

  • Set up a multiple-table database
  • Get data into the database
  • Interact with (add, delete, modify, etc.) records in the database
  • Get data out of the database (reporting, variable setting)
  • Assorted tips and tricks for making it as easy as possible to work with sqlite3

The end result will be a usable database-driven macro, though the project I have in mind probably isn't one that anyone would actually use in the real world. But hopefully by building it, you'll gain the knowledge you need to use a database in your macros, for those times it makes sense.

No promise on timing for the full series, but I'll start working on it today.

-rob.

6 Likes

this sounds awesome. I have a lot of macros that would benefit from using a "real" database, but i'm just using lists right now, super interesting in seeing what you have to share :slight_smile:

Part One is now posted—please post any questions or comments there, not here.

-rob.

4 Likes