Monday, November 19, 2018

#1 2017-07-08 01:55:55 am

Shane Stanley
Member
From:: Australia
Registered: 2002-12-07
Posts: 5471

[ANN]SQLite framework

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>


Shane Stanley <sstanley@myriad-com.com.au>
www.macosxautomation.com/applescript/apps/
latenightsw.com

Offline

 

#2 2017-07-16 06:52:58 pm

Shane Stanley
Member
From:: Australia
Registered: 2002-12-07
Posts: 5471

Re: [ANN]SQLite framework

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/ap … e_Lib.html


Shane Stanley <sstanley@myriad-com.com.au>
www.macosxautomation.com/applescript/apps/
latenightsw.com

Offline

 

#3 2018-08-06 07:21:49 am

technomorph
Member
Registered: 2017-12-14
Posts: 74

Re: [ANN]SQLite framework

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

Applescript:


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

Offline

 

#4 2018-08-06 07:32:39 am

Shane Stanley
Member
From:: Australia
Registered: 2002-12-07
Posts: 5471

Re: [ANN]SQLite framework

This might give you something to work with:

Applescript:

{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}


Shane Stanley <sstanley@myriad-com.com.au>
www.macosxautomation.com/applescript/apps/
latenightsw.com

Offline

 

#5 2018-08-06 08:49:10 pm

technomorph
Member
Registered: 2017-12-14
Posts: 74

Re: [ANN]SQLite framework

Shane Stanley wrote:

This might give you something to work with:

Applescript:

{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}


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:

Applescript:

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:

Applescript:

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}}

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)