Overall objective
This series is designed to help those looking to use sqlite3 databases with their Keyboard Maestro macros. It assumes minimal to no knowledge of sqlite3, and I'll do my best to clearly explain things as I go.
If you have questions, please ask and I'll do my best to clarify.
Part One
Part Two
Part Three
Part Four: You're reading it!
Overview of Part Four
This final part is the shortest of the four. We'll implement a Settings table in the database, and show how to integrate it into the demo Sales Tracking database. The final part of this section is a wrap up of all four parts, highlighting some key points to keep in mind as you work with Keyboard Maestro and sqlite3.
Please download and install the Part Four macro collection, which adds one more macro to the group, and makes some changes in a couple other macros:
____ KMsqlite3 tutorial - Part Four Macros.kmmacros (166 KB)
If you customized the last macro, those changes will be lost—but not anything you did to the database, obviously, as these macros use that same database.
Adding a Settings table
While you can use global variables to store user settings, if you're working with a database anyway, you might as well store them there. They're more portable (they move where the database moves), and they don't clutter a user's global variables—you need keep just one there, the location of the database.
To integrate database-stored settings, we'll need a new table (Settings, of course). But how do you get this into the database, and how do you store values with it? The basic flow I use in my macros goes like this:
- Macro is launched
- Check for the database location variable
- If that exists, check if the Settings table is there
- If it's not, recreate it and tell the user
- Read the settings (either the defaults or the existing values) into instance variables
- Any time a setting is changed, write it back to the database and update the instance variable
In the demo macro, the first changes to accommodate the Settings table are in the "1 - Sales Tracking" macro. If you run it, you'll see a notification letting you know that the settings couldn't be found, and that they had been recreated. You'll next see the Action Manager, with a new entry to handle preferences. For now, end the macro and look at the "1 - Sales Tracking" macro in the Keyboard Maestro editor.
Macro: 1 - Sales Tracking
There's a new orange box here (several, actually). The first one contains some odd-looking SQL:
SELECT CASE WHEN EXISTS (
SELECT 1 FROM sqlite_schema WHERE type='table' AND name='Settings' LIMIT 1
) THEN 1 ELSE 'no match' END;
This command queries the sqlite_schema, which is a table that stores the schema—a description of all the other tables—in the database. It asks that table to extract 1 from the table named Settings. If the table exists, it returns 1, but it otherwise returns no match. The macro checks the returned value to see if it's no match. If it is, it means that Settings doesn't exist and needs to be created. (I realize this is redundant—I could just check to see if it was "not 1." I think this way reads a bit easier for the demo.)
The next orange box is what creates the missing Settings table, and it takes advantage of the ability to assign a default value to a field in sqlite3:
CREATE TABLE IF NOT EXISTS "Settings" (
"Company_Name" TEXT DEFAULT 'ACME Inc.',
"Name_Order" TEXT DEFAULT 'Last, First',
"Default_Year" TEXT DEFAULT 2023
);
INSERT INTO Settings DEFAULT VALUES;
What's important to realize is that just because a field has a default value doesn't mean you've created data when you create the table—you haven't. The last line, which inserts the default values into the Settings table, is critically important, because that's exactly what it does: Creates a row in the database with your default values.
That's the end of the "if no Settings" section; the next orange box is what I call a blob splitter: It reads all the settings in as a chunk, then splits the blob into separate instance variables, one per field from the Settings table. (Today I decided to use #•# as my delimiter, just to show you you can use whatever you want.) The other thing to notice here is that there's no Set Variable local_theSQL holding the SQL commands. That's because there's just one very simple command: SELECT * FROM Settings;, so I put it directly in the call to the SQL Runner subroutine.
The blob splitting happens in the next step, which should look familiar after Part Three: It's a regular expression that splits the incoming data by delimiter, and stores each value in an instance variable.
In a real production version of this macro, you'd probably want to ask the user if they'd moved the database file, or offer them the chance to set the prefs as they're created. But for purposes of the demo, we're just assuming something happened and they get the default set back.
Those are the only changes needed in the main macro to create and use the Settings table. The Action Manager macro has a simple change that adds a new entry to the list:
Run the macro and select that option from the Action Manager, and you'll see the very simplistic preferences setting dialog:
The values displayed are the current values from the Settings table; change any you like and click OK; a notification will let you know they were changed. (Again, there's nothing here to confirm the changes actually took, but that's something you should do.) If you then go to change the settings again, you'll see the values you set are now the ones displayed. Exit the macro and open "Action: Preferences" in the editor.
Macro: Action: Preferences
There's nothing overly tricky here, though I do a bit of extra work to present the "name order" option correctly: I set a variable based on the current setting such that that setting is the one that shows up first in the pop-up menu.
After that comes the first orange box, which is another example of protecting the apostrophes. This one is slightly different, though—I don't output the changes back to the source, but to a "SQL" version of the variable. I do this often when I want to use the original values elsewhere, but still need to massage the text into SQL format.
The next orange box updates all three instance variables to reflect the new settings the user just set. If someone changes a setting, you should write it to the database and update the instance variable as soon as practical after the user makes the change—you don't want the two things out of sync.
Speaking of updating the database, that's what the last box does, in a straightforward manner. Dealing with Settings tables is easy because they're all one-row tables where there's no relationship amongst the fields. You'll rarely, if ever, use a WHERE statement with a settings database.
And that's a wrap on using a Settings database. Obviously, in a real database, you'd then hook those settings into activities—referencing the proper instance variables to reflect the settings the user has made. For a more advanced look at a settings table in a production macro, look at the Settings Manager in MacroBackerUpper.
Conclusion
I realize I've written a lot of words on this subject, but the objective was to give anyone who has a decent amount of Keyboard Maestro knowledge the details they'd need to start using sqlite3 databases with their macros. To do that necessitated a lot of detail, which I tried to break up into manageable pieces.
A very important thing to remember is this: My way, as shown in this series, is not the only way, not the best way, and possibly not even the right way to use sqlite3 databases in Keyboard Maestro. I'm mostly self-taught on integrating the two, and I'm sure I could do many things more efficiently and/or more safely than I do, so I'm positive there's room for improvement.
But I know what's here works, and can serve as a solid background to start working on your own database-enabled macros. I hope you found it useful.
-rob.
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Tips, tricks, and things to ponder
ChatGTP
I told you ChatGTP was really good at sqlite3 stuff, and that it had helped me with the complex merge of group names into the macros database that I wanted to do. If you'd like to see how I talk with ChatGTP, here's that full conversation. You can see how it sometimes takes additional prodding to get what you want, but in the end, it seems ChatGPT always gets there.
Use a SQL Runner subroutine
You'l call it so often, you'll find it invaluable. And with a logging variable and field for log comments, it can be a very helpful debugging tool. Keep in mind, as seen in today's part, you don't have to always set up a variable to hold the SQL. Short simple queries can be entered directly in the subroutine's call box. (Long ones can, too, but I like the readability of putting those in a variable.)
Remove large instance variables when done
If you load up an instance variable with a lot of data for an import, for instance, it's good practice to clear that variable when you're done with it.
You can batch SQL commands
Although I generally put one set of SQL commands in each call to the SQL Runner, you can string commands together, as seen above: The SQL that creates and populates the settings table is two separate SQL commands. If you do this, each separate command must end with a semicolon (`;``).
Trust, but verify
To save time, I trusted and did not verify in the demo macro. In a real macro, you should verify your database changes. I will admit, though, that I'm horrid about this, because they "just work" so well.
Take advantage of subroutines
I did not build this demo macro in an efficient manner. I built it for ease of building (on my end) and ease of reading (on your end). If you'll recall, lots of things happen repeatedly: I extract two things from the database and merge them into one, i.e. Bike Rack [P014]. Instead of repeating those routines every time, they should be set up as a callable subroutine that just gets passed the fields to collect, and then the subroutine would do all the SQL and text work.
Same thing with quoting apostrophes: This could be a subroutine that gets passed the variable and returns the two-quote form of it. I'm sure there are other opportunities like that in the macro, as I didn't spend any time worrying about that while writing it.
Use a debug variable and the Log action
At the start of your macro, create some instance debug variable that's set to false. Then add Log calls that are on Switch/Case statements so they only trigger when the debug variable is true. And if you use subroutines, pass a log message when activating them that tells you where you came from—this will make debugging oh so much simpler.
If inputting data, quote the quote
Remember that a ' will kill your import; they need to be ''.
Numeric fields don't need quotes
If you're updating text data in a field, you must quote your data, i.e. 'new value'. But if it's a numeric field, leave the quotes out, i.e. 23.3.
Tail the log
Huh? What? It sounds weird, but it's just an easy way to see Keyboard Maestro engine log entries in real time. In Terminal, use this command:
tail -f ~/Library/Logs/Keyboard\ Maestro/Engine.log
Press Control-C when you're done.
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━