AppleScript and SQLite
AppleScript and SQLite
- Subject: AppleScript and SQLite
- From: Shane Stanley <email@hidden>
- Date: Fri, 7 Jul 2017 14:29:36 +1000
I've been playing with SQLite. It's quite usable from AppleScript via the
command line tool sqlite3 and "do shell script", but it seems to me that this
approach has several drawbacks from AppleScript.
The biggest issue is that everything is reduced to text. So you can end up
doing a lot of text manipulation just to get stuff in and out. This is
especially the case where there are a lot of results. And while it's not slow,
it could be faster.
There is a third-party framework, FMDB, that puts an Objective-C wrapper around
SQLite, so it can be called from AppleScriptObjC. Unfortunately some of it is
lower level and more complicated than would be ideal for AppleScript.
So I've written a new framework, FMDBAS. This is the FMDB framework wrapped in
a more AppleScript-friendly face (but maintaining access to the more advanced
stuff for those who need it). Initial tests show it's faster -- in some cases
considerably so. It's main advantages are:
* Easy use of SQL ? placeholders, so you can optionally pass parameters as
lists containing differing classes. You can handle classes such as dates and
images much more easily.
* Query results are returned in either arrays/lists or dictionaries/records for
each row, so there's no need to massage a string to extract the results.
* Query results are returned initially as Cocoa arrays. You can coerce them to
AppleScript lists (a simple "as list"), but in the case of large results it's
often quicker to deal with the raw arrays. And because arrays are returned as a
simple pointer, large result sets are returned much, much quicker.
* Support for transactions. You can batch updates together for greater
efficiency.
* Lower overhead than shell, and combined with transactions this means there's
less need to build long statements to perform things like multiple insertions.
* Better error handling.
There are a few cons:
* You need to add the framework to your system. I may wrap it in a library at
some stage.
* You need at least a basic understanding of AppleScriptObjC syntax to use it.
* It won't work under versions earlier than 10.10 at this stage.
It's still at the early testing stage, and I'm looking for interested
volunteers familiar with SQLite. If that's you, please email me off-list.
if you go here <http://forum.latenightsw.com/t/a-better-way-to-use-sqlite/663>
you can see examples of its use and how it compares with using "do shell
script". These are tiny examples, but the first case is about 10x faster, and
the second closer to 100x faster.
--
Shane Stanley <email@hidden>
<www.macosxautomation.com/applescript/apps/>, <latenightsw.com>
_______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/archives/applescript-users
This email sent to email@hidden