SQLite Lib2 released

AppleScript SQLite Lib
#1

I’ve released the first version of SQLite Lib2, a script library for managing SQLite databases from AppleScript.

Functionally it is similar to the existing SQLite Lib, but it includes its own terminology dictionary. It also requires macOS 10.11 or later.

You can download the library and documentation here:

https://latenightsw.com/support/freeware/

Note that since Mojave, scripts must be edited in Script Debugger. And Catalina users should read the documentation for details of how to deal with Gatekeeper changes.

3 Likes
A better way to use SQLite?
#2

This looks very cool, Shane, thanks. And it’s just in time, I’ve got a project in Database Events that is starting to really slow down.

I’m looking for good SQLLite tutorials and documentation that would be most compatible with using this.

I’ve found some, but are there any you recommend?

(My current project: Building an Academy Awards and Emmy Awards database with nominees and winner in every category since the start)

#3

I’m only a minor dabbler — someone else might chime in.

#4

Impressive.

I also like the CSV support, although neither Numbers nor Excel open your sample CSV correctly when naming the file “TestCSV.csv”. Not even with Excel’s Data / Text to Columns… command it can be scanned correctly.

#5

Yes, it doesn’t seem to like a single double-quote at the beginning of a field.

The csv-parsing is code I added, and I think I was stressing it at the time I wrote that example – it would probably make more sense to use something more, um, normal (if there is such a thing as normal CSV).

#6

Thanks, Shane. This will be very useful.

#7

Shane, I know there are many flavors of CSV, but if there is one standard used the most worldwide it is probably Excel. So if you could make your CSV export compatible with Excel that would really be great!

#8

It’s not actually an export. The script contains a small CSV-style string, which it then saves to disk (to demonstrate loading CSV from both a string and a file). The string deliberately pushes the boundaries of what’s acceptable to demonstrate that the import parsing is very flexible.

The underlying framework, and hence this lib, do not support CSV export. Feel free to log a request, but I’m not sure it would be a priority. I think this is more a tool for maintaining and querying databases, rather than exporting their contents as a whole.

#9

Quick question, while debugging this I encountered a “database is locked” error. I think the script stopped before a commit command. But I couldn’t figure out how to unlock it. I just put it in the trash and started over.

Is there a better way?

#10

OK, thanks. Didn’t know that.

Maybe some CSV guru will jump in here and contribute a handler to do a CSV export. :wink:

#11

You can use a try block. But the open db command should usually re-open a locked db. Otherwise try quitting and relaunching SD.

#12

I don’t think it’s an SD issue. I think it’s SQLLite.

Basically if a script is interrupted before it commits then the record (or something) gets locked. You can still open and close the database, you just can’t do anything with it.

#13

It’s just a file lock, similar to leaving a file open with open for access. Quitting Script Debugger (or the host app) should unlock it.

#14

OK, but, as with open for access, is there a way to unlock a database in this state?

#15

None that I’m aware of, other than a quit and relaunch.

#16

Should I be able to open a Database Events database file using SQLite Lib2?

#17

You should be able to open it, but you’re unlikely to be able to make any sense of it.

#18

When I say “open it” what I mean is be able to make queries and access and store data, as if it were created with SQL l2.

#19

This is a real drag for me. Every time I try to start working with this, I end up having to quit and relaunch numerous times. If there’s one feature I’d love to see it would be a way to simply close the database without having to relaunch.

#20

That’s what I thought — the answer to that is no.