A better way to use SQLite?

Shane, I received your Beta 1 files, and have installed, read, and completed successfully an execute of all of the test scripts you provided:

General sample.scpt
Macscripter read.scpt
Macscripter write.scpt
Safari history.scpt

The documentation seems clear enough to me, and I’m doing some rich-text reformatting of it in an Evernote Note (mostly for my benefit now and future ref), which I’ll share very soon.

Ah, what the heck. In the spirit of a Beta, here’s my alpha/beta EN Note: FMDBAS Framework Notes – Getting Started

  • You can view it as a web page without any Evernote software.
  • If you have Evernote, you can download into your account (but I wouldn’t recommend that yet as it is still a work in progress)
  • It is NOT finished yet. Have not done the Query section.
  • It has NOT been carefully reviewed yet, so I could have unintentionally screwed something up.
  • But if anyone want’s to offer constructive criticism, I’m all ears. :wink:

So far so good. I made the trial lap without any engine failures. :wink:
I think this is going to be great! Easy to use and very powerful.

I’ll start some detailed testing comparisons this weekend with some existing SQL scripts I have.

Thanks Shane. I don’t have time to look into this right now, but it’s definitely bookmarked for future use. There’s sql databases littered all over the mac that I need to access for security or forensics purposes. I normally do that with bash or python scripts, but having this option in AS could be handy.

One of my main uses for AppleScript is to provide simple click-and-go scripts for users to run diagnostic reports on their macs, so wrapping that f/w in a library would be useful, too.

Thanks for taking the time to do all that, @JMichaelTX .

One issue I’m looking for feedback from everyone on is the design. In particular, the decision to replace built-in methods that return boolean values for success or failure with methods that throw an AppleScript error on failure. I think this is more AppleScript style, although there’s a limitation that means all error with have the same number (-10000). Does this make sense to people?

Further, in the case of the update methods, I’ve added boolean versions as well. But I’m wondering if these are just an unnecessary complication. Thoughts?

Lastly, in the next version I’m going to add dedicated AS forms of the +databaseWith… methods. This is partly for consistency, but also for better error trapping. I’m also adding methods for loading a database into memory, and saving a database from memory to a file. My feeling is that that just about covers most bases. So is anything missing?

Note that you can also include a framework directly in a script bundle without a library. You just need to put it in a Frameworks folder inside the bundle’s Contents folder (not inside Resources).

1 Like

Ah! That’s cool. I didn’t know that.

So far, this looks very promising. The ease with which I can generate appleScript lists and records from SQL especially.

You’re saying there may be a library in the future, I’m hoping that includes a full AE Dictionary? That would be great!

Not exactly sure how I would use this to import from a CSV or pipe delimited text file.

Honestly, unlikely. It’s an extra level of overhead, and this is meant to be as fast as possible – and SQL stuff is not exactly for dabblers. It would also be a lot of work, and I’m not sure there’s wide enough appeal to warrant it.

That said, there’s nothing to stop someone else doing just that.

You going to have to convert to a list of lists, and then use executeUpdatesAS:withArguments:. But being able to pass CSV directly is an interesting idea…

As long as you’re thinking about that, it would also be useful to:

  1. Pass TSV (Tab Separated Values)
  2. Pass a path to a file of either TSV or CSV, for either input or output.


BTW, I don’t need a scripting dictionary.

Yes, that would make sense.

That raises issue of what format the file is in, which complicates matters. I suppose I could insist on UTF-8.

I’m good with that. I would think that would cover most use cases.
I can see where this (read/write CSV/TSV files internally) could be extremely useful in migration (export/import) of large blocks of data between apps/databases.

Thanks for considering. BTW, if you need to, you can always delay this stuff to Ver 2. :smile:

I still have misgivings. I mean, it’s only saving two lines of code:

set {theString, theError} to current application's NSString's stringWithContentsOfFile:posixPath encoding:(current application's NSUTF8StringEncoding) |error|:(reference)
if theString is not missing value then error theError's localizedDescription() as text

and the tiniest amount of overhead. And there’s some sense in making sure that part of the process is working before even opening the database.

Interesting, when you use “overhead” that seems to suggest that a dictionary would slow it down, even when the dictionary commands are not being used. Is that correct?


Based on Beta 3 (2017-07-10) running Script Debugger 6.0.5 (6A205) on macOS 10.11.6.

To keep things simple and consistent, why not name TSV methods same as CSV methods:

theDb's executeUpdatesAS:"insert into test values (?, ?, ?, ?)" withTSV:tabbedText
theDb's executeUpdatesAS:"insert into test values (?, ?, ?, ?)" withTSVFromPath:(POSIX path of tempPath) 

But then it occurred to me that maybe you don’t need separate methods for TSV and CSV data:

theDb's executeUpdatesAS:"insert into test values (?, ?, ?, ?)" ¬
  withCSV:tabText commaIs:tab

withCSV: commaIs:

--- USE ---
withText: delim:

--- SO YOU'D HAVE ---
theDb's executeUpdatesAS:"insert into test values (?, ?, ?, ?)" ¬
  withText:tabText delim:tab

I’d also prefer a simpler method for the “from path”:

theDb's executeUpdatesAS:"insert into test values (?, ?, ?, ?)" ¬
  fromPath:(POSIX path of tempPath) delim:","

Your thoughts?

I did it the way I did because I see CSV as a recognized (pseudo) acronym, with TSV less so. But there’s something to be said for brevity, not to mention avoiding the whole issue of spelling separated :slight_smile:.

That’s not going to work. The TSV stuff just breaks fields on tabs – there’s no way of including a tab within a field, for example. But CSV allows escaping of the delimiter, as well as allowing it in quoted fields – the process is more complex. So there has to be some way of knowing what’s wanted.

But there’s certainly value in being able to specify things like pipes and other characters, or even strings, as field delimiters.


FWIW, I’m after any more feedback on terminology. I was initially thinking of releasing it as a framework first and as a library later, but I’m now going to the library stage straight away. It won’t have defined terminology, but it will allow more simplified handler names.

For example, instead of using:

set theDb to current application's FMDatabase's databaseWithPathAS:posixPath
theDb's openWithFlagsAS:(current application's FMDBASOpenReadWriteCreate)
set theList to (theDb's executeQueryAS:"select * from test") as list

It will be like:

set theDb to theLib's makeNewDbWith:posixPath -- actually accepts file, alias, NSURL, or HFS/POSIX path
theDb's openReadWriteCreate()
set theList to (theDb's doQuery:"select * from test") as list

That notwithstanding, if anything in the existing terminology jars, now’s a good time to yell.

I don’t understand. A tab is a character just like a comma, so why can’t it be treated the same in the algorithm?

IAC, you could still have one method. The “delim:” parameter would tell you how to handle the data. If it is a TAB, then use the TAB algorithm.

Here’s a script that compares:

  1. Tab Delimited data using the TSV method
  2. Tab Delimited data using the CSV method
  3. Comman Delimited data using the CSV method with “,”
  4. Semi-colon Delimited data using the CSV method with “;”

BTW, please note the change in parent folder for the DB.
I detest making my desktop cluttered with test files, or really any files.

use AppleScript version "2.4" -- Yosemite (10.10) or later
use framework "Foundation"
use framework "FMDBAS"
use scripting additions

property parentPath : "~/Documents/Test/"
property dbName1 : "TestMultiDelim.db"

-- Tab-separated value
-- leading and trailing linebreaks are trimmed
-- returns and linefeeds are recognised as line/row breaks

set tabbedText to tab & "Two\",\" two" & tab & "Three" & tab & "Four \"four\" four" & linefeed & "Five\",\" five" & tab & "Six" & tab & "Seven" & tab & "Eight" & return & "Nine" & tab & "Ten" & tab & "Eleven" & tab & "Twelve" & linefeed & return

set csvText to "COMMA/CSV,Two\",\" two,Three,\"Four \"\"four\"\" four\"" & linefeed & "Five\",\" five,Six,Seven,Eight" & return & "Nine,\"Ten , ten\",Eleven,Twelve" & linefeed & return

set csvText2 to "SEMI-COLON/CSV;Two\",\" two;Three;\"Four \"\"four\"\" four\"" & linefeed & "Five\";\" five;Six;Seven;Eight" & return & "Nine;\"Ten ; ten\";Eleven;Twelve" & linefeed & return

-- create and open db
set thePath to (current application's NSString's stringWithString:(parentPath & dbName1))'s stringByExpandingTildeInPath()
set theDb to current application's FMDatabase's databaseWithPathAS:thePath
theDb's openWithFlagsAS:(current application's FMDBASOpenReadWriteCreate)

-- create table, update from tabbedText, read contents, then close
theDb's executeUpdateAS:("drop table if exists test")
theDb's executeUpdateAS:"create table test (a, b, c, d)"

theDb's executeUpdatesAS:"insert into test values (?, ?, ?, ?)" withTabSeparatedValues:("TAB/TSV" & tabbedText)

theDb's executeUpdatesAS:"insert into test values (?, ?, ?, ?)" withCSV:("TAB/CSV" & tabbedText) commaIs:tab

theDb's executeUpdatesAS:"insert into test values (?, ?, ?, ?)" withCSV:csvText commaIs:","

theDb's executeUpdatesAS:"insert into test values (?, ?, ?, ?)" withCSV:csvText2 commaIs:";"

set result1 to (theDb's executeQueryAS:"select * from test") as list
theDb's closeAS()

set AppleScript's text item delimiters to linefeed

return result1


The CSV method seems to always strip one pair of quotes (if any) from each cell. Is this by design? Standard CSV rules?

Because there are specific rules for CSV. You’ve seen one example: if a CSV field is completely within quotes, those quotes are treated as escaping quotes only, and are discarded.

You could use a tab as a delimiter in CSV – but that’s quite different from typical tab-delimited text, which is usually made by slicing first into paragraphs, and then dividing each paragraph on each tab, with no escaping allowed.

(Assuming you have a spreadsheet app, try doing some CSV and tab-delimited exports to see the difference.)

No – it means calling a handler via terminology adds some overhead.

But honestly, it’s really very little. I was looking for excuses not to do it… :weary:

1 Like

A post was split to a new topic: JavaScript for Automation access to SQLite

And I ran out.