[ANN]SQLite framework

Scripting Forums AppleScript | Mac OS X
#1

I’ve put together a framework for SQLite scripting. If you’re interested, you can read more here: <macscripter.net/viewtopic.php?pid=190716#p190716>

#2

So this project morphed into SQLite Lib, a free scripting library, which is now available.

SQLite Lib offers a more efficient and faster alternative than using the command-line tool sqlite3 via do shell script. It requires OS X 10.10 or later.

You can read about it and download it here: www.macosxautomation.com/applescript/apps/SQLite_Lib.html

#3

from :

theDb’s doUpdate:“insert into test values (:a, :b, :c, :d)” parameterRecord:{a:1, b:2, c:3, d:4}

is there any way to insert values from a record that is missing one of the column headers?

I tried:
theDb’s doUpdate:“insert into test values (:a, :b, :c, :d)” parameterRecord:{a:1, b:2, d:4}
and that errored

and this:
theDb’s doUpdate:“insert into test values (:a, :b, :d)” parameterRecord:{a:1, b:2, d:4}
and that errored as well

is there a quick way to add in to my list of Records. KeyNames that are missing from a
master list of KeyNames that I have. And insert:
missingKeyName:null

I guess I can loop through my list. Get all the keys from the item.
Compare that to my MasterKeyList and come up with a missingKeyList.
Loop thru that missingKeyList and create new records and add them?

Is there a quicker way?

oh i’m found this as an alternative.
It won’t have them as records. But my list will be in the order from the MasterKeyList
and I can then use the MasterKeyList as my tableHeader


set myLISTWITHMISSING to (SMSForder's subarraysFrom:myLIST usingKeys:MasterKeyList outKeys:(missing value) |error|:(missing value))
#4

This might give you something to work with:

{a:1, b:2, d:4} & {a:missing value, b:missing value, c:missing value, d:missing value}
--> {a:1, b:2, d:4, c:missing value}
#5

thanks!

I’ve just tried a few tests. I have nested records. And it doesn’t seem to work.
Damn why did i go the nested records route.I thought it would help make things easier.
I guess I just need to drill down to the sub records which is fine as I’m already doing that in my current processing:

set testMASTER to {abgroup:{a:missing value, b:missing value}, cdgroup:{c:missing value, d:missing value}}
set abMASTER to {abgroup:{a:missing value, b:missing value}}
set cdMASTER to {cdgroup:{c:missing value, d:missing value}}
set testINSERT to {abgroup:{a:1, b:2}, cdgroup:{d:4}}
set abINSERT to {abgroup:{a:1, b:2}}
set cdINSERT to {cdgroup:{d:4}}

set test03 to testINSERT & testMASTER
--> {abgroup:{a:1, b:2}, cdgroup:{d:4}} -- NOPE
set test03A to {abgroup:{a:1, b:2}} & testMASTER
--> {abgroup:{a:1, b:2}, cdgroup:{c:missing value, d:missing value}}  -- OK but partial
set test03B to {cdgroup:{d:4}} & testMASTER
--> {cdgroup:{d:4}, abgroup:{a:missing value, b:missing value}} -- replaces cdgroup completely
set test03C to {cdgroup:{d:4}} & test03A
--> {cdgroup:{d:4}, abgroup:{a:1, b:2}}  -- replaces cdgroup completely
set test04 to test03A & {cdgroup:{d:4}}
--> {abgroup:{a:1, b:2}, cdgroup:{c:missing value, d:missing value}} -- doesn't change the cdgroup

set test05AB to testINSERT & abMASTER
--> {abgroup:{a:1, b:2}, cdgroup:{d:4}}
set test05CD to testINSERT & cdMASTER
--> {abgroup:{a:1, b:2}, cdgroup:{d:4}}
set test06AB to abINSERT & abMASTER
--> {abgroup:{a:1, b:2}}
set test06CD to cdINSERT & cdMASTER
--> {cdgroup:{d:4}}

set test07AB to abINSERT & testMASTER
--> {abgroup:{a:1, b:2}, cdgroup:{c:missing value, d:missing value}}
set test07CD to cdINSERT & testMASTER
--> {cdgroup:{d:4}, abgroup:{a:missing value, b:missing value}}
set test0701 to test07AB & test07CD
--> {abgroup:{a:1, b:2}, cdgroup:{c:missing value, d:missing value}}
set test0703 to test07CD & test07AB
--> {cdgroup:{d:4}, abgroup:{a:missing value, b:missing value}}

OK i figured out how to get down to the subrecords and insert:

set cdSUBMASTER to {c:missing value, d:missing value}
set testINSERT to {abgroup:{a:1, b:2}, cdgroup:{d:4}}

set cdgroup in testINSERT to cdgroup in testINSERT & cdSUBMASTER
--> {abgroup:{a:1, b:2}, cdgroup:{d:4, c:missing value}}
#6

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.

#7

Thanks a lot Shane for creating this for our community and for let us know!
L.

#8

I’ve uploaded version 1.1.0 of SQLite Lib2. There’s no change in SQL functionality, but the library now tries to close a database when a command generates an error. This should mean less having to quit and relaunch when errors happen writing scripts.