Best way to set up and query a simple database on Mac

Hi all- I’m looking for advice on the best way to build a simple database out of information generated every day from about half a dozen iOS and MacOS apps, mainly about time use. I want to generate a dashboard and trigger some actions based on the data.

I know AppleScript and Keyboard Maestro, and I can use them to do almost everything I want. I can collect the data I need as CSV or JSON files and load it into lists of records (with dictionaries). As far as I can tell, neither Applescript nor KM make it easy to query those records without a lot of repeat loops.

Essentially all I want is a statement like this:

Set DashboardValue to
[sum, average, min, max, or count] of Field A for records
where Field B = X and Date is between Y and Z

Is there an easy way to do that in AppleScript or KM? Or is there a tool I can learn just enough of to solve my problem:

  • SQL
  • A scripting language (JavaScript, Python, etc.)
  • A webapp (Airtable, FireBase, etc.)
  • A spreadsheet (Numbers or Excel) or other Mac app.

Other than spreadsheets, I don’t have any experience with these other languages or platforms. All of these could solve my problem. Does anyone have a view as to what the most painless path to take?

Thanks, Saul

You should check out Shane’s SQL libraries. Best thing about them is if you have any questions you can come here for answers!

latenightsw.com/support/freeware/

SQLite Lib 1.0.0 and SQLite Lib2 1.1.0

SQLite Lib2 and SQLite Lib are libraries for fast and efficient SQLite programming from AppleScript, based on the well-known open-source FMDB Framework. If you ever need to access SQLite databases, or need a simple database for your scripts, one of these is the answer. Functionally similar, SQLite Lib2 requires macOS 10.11 or later and includes a terminology dictionary, whereas SQLite Lib uses traditional handlers, and works under macOS 10.10 and later. Note that scripts using SQLite Lib and SQLit Lib2 cannot be edited in Script Editor in Mojave or later because of security settings. You need to use Script Debugger. See Catalina Security and Script Libraries for Catalina installation details. (Updated July 17, 2020.)

1 Like

Hi, I use Filemaker Pro for all my db needs. It’s very Applescriptable and has a large ecosystem supporting it.

Filemaker Pro is very scriptable but as of the last few years the emphasis there is more corporate clients and app development and server. It is more difficult and expensive to buy a single-user license these days. So if you are looking for a cheap way to do this, forget about FMPro.

1 Like

I’m a bit late to this thread (ok, super late), but it sounds like my app Panorama is exactly what you were looking for. It’s RAM based so is super fast for analyzing data, and it will import both CSV and JSON files.

Panorama has a lot of features, so let me direct you to the Summary Workshop, which is pretty much exactly the dashboard you are looking for:

If you are interested I would also suggest watching the introductory movie on the main home page, and of course there is a free demo. And the pricing is waaaaay more affordable than FileMaker.

What’s the AppleScript support in Panorama like? Does it have a full appleScript dictionary?

Panorama itself includes an extensible, recordable programming language and user interface builder with a long history (over 30 years) – to the extent that many of the programs features are actually built in it’s own language. So it didn’t really make sense to duplicate that functionality in AppleScript. But AppleScript does have full access to the power of Panorama because you can embed Panorama code right into an AppleScript. So anything that can be done in Panorama’s programming language can be done in AppleScript. That goes in reverse as well – you can embed AppleScript code into Panorama code. You can also embed shell scripts, Python, Ruby, Perl or PHP.

Here’s the relevant help page:

Probably not too useful for anyone on this forum, but the upcoming 10.2 version of Panorama will also support the X-callback URL protocol, both inbound and outbound.

P,S. If you check out the endorsements on the ProVUE home page, you’ll see a name that everyone on this forum will recognize.

Thanks, and here’s some friendly feedback from a potential customer: From the start one of the goals of appleScript is to eliminate the need for scripters to learn a different scripting language for every application they use. So it would make sense to AppleScripters to implement fully native appleScript language support.

I’ve looked at Panorama several times over the years (going back to Mac OS Classic) but without native AppleScript I’ve always found a different solution.

Currently I’m using SQLite via Shane’s SQLite Libraries, because of the native appleScript support.

In a world of infinite resources, I would totally agree with you. Unfortunately (and I do think it is unfortunate), the universe of AppleScripters is pretty small. Panorama’s programming language is pretty rich, with hundreds of different verbs. We simply don’t have the resources to duplicate all that effort all over again for what unfortunately would probably be only dozens of potential customers. Not just coding resources, but documentation and support.

eliminate the need for scripters to learn a different scripting language for every application they use

In spite of this goal, in my experience unfortunately there is still a huge learning experience each time you start scripting a new app. There’s so much variability in how different apps implement their scripting interfaces, and usually there is little to no documentation. To me the best part of Script Debugger is the exploration tools that at least make it possible to discover how each app reacts to scripting.

In any case, I made the decision that rather than unleashing another inconsistent poorly documented scripting interface on the world, we would provide a bridge to out already complete and well documented language. Of course that isn’t going to be a good fit for everyone, I just wanted to make people aware of the option (Mark Aldritt had mentioned to me in a private conversation that there was some database related discussion here, so that’s what inspired my original post).

I appreciate the effort to make all this possible, and to document it so well.

I used Panorama many years ago—perhaps before OSX? So very, very fast.

Ray

Yes those are definitely issues for appleScript.

As for numbers, some of us work for large organizations with numerous mac work stations, and while there may only be a few appleScripters, we’ll be writing for a larger number of users.

(In my case I had my scripts installed on several hundred users macs, now maybe 100)

Thanks! :grinning:

I used Panorama many years ago—perhaps before OSX? So very, very fast.

The first release of Panorama was in 1988, so it’s quite possible you were using it long before OS X. And it was an offshoot of OverVUE, which we started shipping in August 1984, I think it was the 7th app available for the Mac. Of course now the app is native on macOS, and we’ll have M1 support soon (though some operations are already faster on Rosetta 2 than on Intel machines).

If you’re application was small enough that you could get it working with SQLite, that’s probably the right choice. Certainly licensing Panorama for several hundred computers would have been a lot more expensive than using SQLite. On the flip side Panorama has a lot of capabilities that any version of SQL can’t touch, like multi-level undo (even for operations that change the database structure), using arbitrary formulas for searching (including regular expressions and phonetic searches) without any performance hit, instantly being able to merge and split fields even when full of data, crosstabs, charts etc. It’s super fast because it’s RAM based, but it still can handle a lot of data – we heard from one researching using it for some sort of Covid research that was working with 62Gb data sets (and that’s all data, since Panorama doesn’t use indexes). Panorama also has a complete user interface, so even though any Panorama operation can be automated, the easy UI is available for one-off tasks that aren’t worth being automated. By the way, everything in Panorama can be automated – not just data operations, but also database configuration and even graphical layout.

A heads up, if any of you are seriously interested in Panorama we’re going to be participating in the SummerFest sale which I think starts in a week or two (this is a biannual promotion done by about a dozen Mac indie developers). I’ll try to remember to post a link to that here when that sale starts.

This sale is available for the next 12 days.

https://www.artisanalsoftwarefestival.com