SQLite Lib2 released

AppleScript SQLite Lib
#33

Version 1.1.0 introduced the following change: When a command results in an error, the library will attempt to close the database and clear the database variable before the error is returned to your script.

Could I please request that in future versions that behaviour be optional or something? It causes problems for me.

I don’t think an error with one command is necessarily fatal to the whole script. In one of my scripts, for instance, I have a feature that allows a user to enter an sqlite query in a dialog, and then SQLite Lib uses that as the sql string parameter to run a query command and display the results using Myriad Tables Lib. It runs in a repeat loop so you can run as many queries on the same db as you like. If one query fails, I just display an error alert and the user should then be allowed to just run another query, but since v1.1.0 the db is closed and the variable that points to it cleared thus any subsequent queries throw an error. There are other similar examples.

I suppose a work around for me might be to have a function to reopen the db which I can call from the on error part of a try block if the error code indicates that its an error with SQLite Lib. But to make that elegant would involve lots of try blocks and conditional error handling, or in other words much more code. :blush:

In contrast, the problem that is being solved by the change in v1.1.0 never seemed like much of a problem to me. I called a function in the on quit handler which would close the db if it was open and I basically wrapped the contents of the on run handler in a try block which would call the on quit handler if a fatal error was encountered, so either way the clean up function would get called.

#34

Is there some reason you can’t trap the error, and reopen the database in such cases?

#35

I could, but the reasons I don’t are twofold.

First, my applet keeps track of where the database is saved and provides a menu of things that can be done with it, but most of the logic is in my own custom libraries which include lots of different handlers that in turn call into SQLiteLib2. The handlers in my custom libraries each have a parameter so that the applet can pass them a reference to the open database. so to catch the error and reopen the database I’d have to catch the error in the applet, because only it knows the location of the database, and then reopen the database and store the reference to it in the variable in the applet. That’s not as graceful for the user as, for instance, catching the error in the specific handler where it is thrown because throwing the error up to the applet means the user is effectively yanked out of whatever context thery were just in.

And second, to do it gracefully would mean having to test for and catch db errors in lots of places in my code, whereas the code required to ensure the db is closed if a fatal error is thrown is a lot less.

Really the redeeming part is to try and write code that won’t throw an error in SQLiteLib, and I’m getting better at doing that, which is why it’s taken me a while to make the feature request, but it would be nice to have the reassurance that my script can go on even if an error is encountered. :blush:

#36

I’ve had a look at the code, and making it an option is a bit tricky. I’ll keep it in mind for the next update, although honestly I don’t have one planned at the moment.

You can, however, execute the query handler directly. You will need to include framework use statements:

use framework "Foundation"
use framework "FMDBAS"

And then, assuming scriptDb is the variable containing the Db:

	set theResult to scriptDb's realDb's executeQueryAS:sqlString withArguments:withArgs dateColumns:dateCols boolColumns:boolCols rowsAs:rowType

The rowType variable can be an empty string, which matches a return type of list of lists. The other parameters should be self-explanatory.

You’ll need to make a copy of the FMDBAS framework somewhere the script can see it, too.

#37

There are two issues with this script.

First, after stepping through it a few times in debug mode, it won’t compile, but if I switch out of debug mode it will.

Second, I can’t get it to work. I’m getting and “unknown database error 10000” whatever that is.

Here’s what I’m trying to do:

I have a list of about 10k email addresses (with name and subject).

I have an email account that gets hundreds of emails per day, most from senders in that list. I want to compare a list of new emails to the 10k names, and delete all the ones that are in the long list.

(For various reasons I can’t use normal junkmail filters).

When it’s up and running I’ll query the db for messages that have the same emailAddress and senderName and flag those for deletion. (I’m doing it now with appleScript lists and it takes about 20 minutes).

use AppleScript version "2.5" -- El Capitan (10.11) or later
use script "SQLite Lib2" version "1.0.0"
use scripting additions

set newEmailData to {{"hft@em.harborfreight.com", "Harbor Freight Tools ", "Big Purchases Made Easy with Equal Monthly Payments"}, ¬
   {"officedepot@e.officedepot.com", "\"Office Depot\" ", "👨‍👩‍👧‍👦Go Big For Your Dads & Grads🎓"}, ¬
   {"no_reply@email.apple.com", "Apple  ", "Your receipt from Apple."}, ¬
   {"specials@reader.macsales.com", "OWC Larry ", "☀️🖥️Macs + More! Save BIG on Apple Laptops, iMacs, iPhones, drives, upgrades + so much more…"}, ¬
   {"specials@reader.macsales.com", "OWC Larry ", "☀️🖥️Macs + More! Save BIG on Apple Laptops, iMacs, iPhones, drives, upgrades + so much more…"}, ¬
   {"noreply@redditmail.com", "Reddit ", "\"Days before Rep. Mike Nearman helped protest...\""}, ¬
   {"News@email.americastestkitchen.com", "\"America's Test Kitchen\" ", "\"What's for dinner?\" It just tastes better without the pressure."}, ¬
   {"RYOBI.News@email.ryobitools.com", "\"RYOBI\" ", "RYOBI DAYS Deals on Combo Kits!"}, ¬
   {"newsdigest@insideapple.apple.com", "Apple News ", "He wanted to put on a Nas concert. He ended up being held captive."}, ¬
   {"extras@antiquesroadshow.wgbh.org", "\"Antiques Roadshow\" ", "Deadline Monday: Last chance to enter the 2021 Appraisal Contest!"}}
repeat with x from 1 to 10
   --set item 3 of item x of newEmailData to (x as text)
end repeat
set emailDBPath to "~/Desktop/email-2.db"

try
   set emailDB to open db in file emailDBPath with can create
   
   update db emailDB ¬
      sql string ("drop table if exists test")
   update db emailDB sql string "create table standings (EmailAddress, SenderName, messageSubject)"
   
   update db emailDB ¬
      sql string ("create table test (EmailAddress, SenderName, messageSubject)")
   --begin transaction db emailDB
   
   batch update db emailDB ¬
      sql string "insert into standings values (?, ?, ?)" with arguments newEmailData
   close db emailDB
on error errMess number errNum
   close db emailDB
   display dialog errMess & " (" & errNum & ")"
   error number -128
end try
#38

I’m guessing you meant to also have a “drop table if exists standings” in there.

The 10000 is just AppleScript’s work.

#39

embarrassingly, after I posted that I figured out that in that script “text” and “standings” are the names of the table in the db.

I would love to see more examples of SQL syntax with sql lite 2.

I have gone to https://sqlite.org/ and gotten lots of syntax examples but translating those into SQLL2 syntax is problematic.

I’m not sure what information belongs in the sql string and what should go with arguments, for example.

#40

Also, do any of the pragma or view commands function?

#41

The library uses SQLite3 — the 2 refers to the library version.

To be clear, macOS includes an SQLite3 library, libsqlite3.dylib. This is what the OS uses, but it has a C interface, and can’t be used directly from AS. The FMDB framework wraps that C-code in Objective-C. However, it’s not ideal for ASObjC: using it would involve repeat loops for results, and some of it is async. So the FMDBAS framework extends it and makes it more useable from AppleScript. This is then packed in the library bundle, which adds terminology and some other AppleScript house-keeping.

But you’re ultimately calling the same SQLite3 code as anything on your Mac.

#42

So for instance, I have a library with a queryDB handler as follows:

use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions
use OBUtility : script "OB Utilities" version "1.0"
use script "SQLite Lib2" version "1.1.1"
use script "Myriad Tables Lib" version "1.0.10"

on queryDB(thisDB)
	set theQuery to ""
	repeat
-- the following function calls a handler from another library that uses Dialog Toolkit Plus to get the user to input text into a text field
		set theResult to returnOBValue of OBUtility without backButton given prompt:"Enter your query.", title:"Query Database", cancelButtonName:"Done", nextButtonName:"Query", defaultValue:theQuery
		if userCancelled of theResult then exit repeat
		set theQuery to valueReturned of theResult
		try
			set queryResult to query db thisDB sql string theQuery
			if queryResult is {} then set queryResult to {{}}
			displayTable for queryResult with hiddenCancelButton and emptySelectionAllowed given prompt:"Here are the results of your query.", title:"Query Result", OKButtonName:"Done"
		on error errorMessage number errorNumber
			display alert "Invalid query" message "Your query " & theQuery & " threw an error." & return & return & "The error message was:" & return & errorMessage
-- by now thisDB will no longer be a valid reference to an open database
-- as SQLite Lib2 will have automatically closed it
-- is there a way to reopen the db using the old reference to the open database (without knowing the path to the db)?
		end try
	end repeat
end queryDB

on displayTable -- with a bunch of parameters
-- displays the given table data using Myriad Tables Lib
end displayTable

That handler gets passed a reference to an open db, but doesn’t have knowledge of the file path to the db.

Is there a way that the handler can re-open the db in the event of an error so that the handler and script can continue to run?

The other thing is that after the db is closed on error, the variable that previously contained the reference to the open db is not set to missing value. I have a closeDB() handler that runs before the applet quits and it checks to see if the db reference is missing value (because that’s what it’s set to before the db is opened), and if it’s not missing value it tries to close the db. But if it’s been closed automatically by Sqlite Lib2 on error then there’s no way of me testing that it seems. It would help if the library set the old references to missing value somehow so I could test to see if it’s still open or not.

#43

Not without knowing the path. Just pass the path as well as the db.

The value returned is actually a script object with a property called realDb, which contains the ASObjC reference to the database. This property is set to missing value when the database is closed.

#44

So could I do something like this?

on closeDB(theDB)
if theDB is not missing value and readDb of theDB is not missing value then close db theDB
end closeDB
#45

Yes, I think that should do it. Easy enough to test…

1 Like
#46

Not sure I completely understand this, but I have an applet that opens the db on launch, stays open to do stuff repeatedly with the db, and then closes it on quit. Is that going to be a problem? Will that cause memory leaks? It doesn’t seem to be causing any problems so far, but it’s still being developed and hasn’t been thoroughly put through its paces yet. Opening and closing the db before and after each query would take a whole lot of re-working so I’m hoping that won’t be necessary. :sweat_smile::crossed_fingers:t4:

#47

Tested it, and seems it doesn’t work. Got the following error:

Can’t make readDB into type reference. Error number -1700

The statement that threw the error was “if theDB’s readDB is missing value then”. Script Debugger had this to say:

Target: Script Debugger.
Event: get readDB.
Error: unable to coerce the data to the desired type (errAECoercionFail:-1700).

#48

No.

You may have to do it with debugging off.

#49

I was doing it with debugging off. Embarrassingly, I’ve never worked out how to use SD debugging so I continue to use my cave-person debugging methods. :slightly_smiling_face:

#50

My typo — that should be realDb, not readDb.

1 Like
#51

Okay, works as expected now. :grin::+1:t3: I thought readDB was a funny name for the property when I was typing it and something in the back of my head was raising a flag because I’ve seen realDB written quite a few times but didn’t catch it.

#52

What UTI can I use to identify the database files created by SQLite Lib2? If I do:

choose file of type {“public.database”}

I’m not able to see the database files created by SQLite Lib2.

Same goes if I try the uti “vnd.sqlite3”.

Incidentally I’ve downloaded from the Mac App Store the app Ridill SQLIte which can open, view and query sqlite databases and it correctly identifies the files created by SQLite Lib2 as sqlite databases and has become the default app to open them and not other databases. I think it did that without intervention by me but it’s possible that I did an open with that app on the first one but have forgotten it.

Alternatively, the other way of approaching this would be to ask how I would go about associating a file with a particular UTI? Let’s say I tinkered around with my applet’s info.plist file to export my own custom UTI, how would I tell macOS that a particular file conformed to the UTI? Is there a way I can tell the OS that the files I’ve created with SQLite Lib2 conform to public.database?