SQLite Questions

AppleScript SQLite Lib
#1

This question may be more of an SQL query syntax question that SQLite Lib or AppleScript, but I have two databases where the records in each consists of three text fields: email address, name, text.

One database has 10k records. The other 50-200.

I need to find out which records are in both.

Right now I’m planning on stepping through each address in the smaller, and query the larger, but I’m worried that will take forever.

Is there any SQL command that shows the common records between two databases? (it could also be two tables within the same db)?

#2

I suspect you’re looking for SQLite’s INTERSECT.

#3

Yes, Intersect is what I was looking for… but I can’t get it to work.

Below is a longish script (probably too long to display) that’s mostly based on the SQLite Lib2 sample script. I’ve tried adding a handler with an intersect command, and it doesn’t work reliably. Right now it’s just returning the first query. I’ve gotten it to return some of the intersected data, along with some non-intersected data, but never the exact intersect.

(* This is a simple example of how to create a database, write some data to it, and then query it *)

use AppleScript version "2.5" -- El Capitan (10.11) or later
use script "SQLite Lib2" version "1.0.0"
use scripting additions
property theSQLString : ""
set dbFile to "~/desktop/TestDB2.db"
set tableName to "standings"
set updateTableName to "UpdatedStandings"

set theData to {¬
   {"Christopher", "Froome", "Britain", 1}, ¬
   {"Fabio", "Aru", "Italy", 2}, ¬
   {"Romain", "Bardet", "France", 3}, ¬
   {"Rigoberto", "Uran", "Colombia", 4}, ¬
   {"Jakob", "Fuglsang", "Denmark", 5} ¬
      }
set moreData to {¬
   {"Christopher", "Froome", "Britain", 1}, ¬
   {"Fabio", "Aru", "Italy", 2}, ¬
   {"Egan", "Bernal", "Colombia", 6}, ¬
   {"Geraint", "Thomas", "Great Britain", 7}, ¬
   {"Steven", "Kruijswijk", "New Zealand", 8}, ¬
   {"Emanuel", "Buchmann", "Germany", 9}, ¬
   {"Julian", "Alaphilippe", "France", 10} ¬
      }


set theColumns to {"firstname", "lastname", "country", "position integer"}

set openDB to CreateOpenDB(dbFile, tableName, theColumns)
set entireTable to RetrieveEntireTable(dbFile, tableName)
ClearTable(dbFile, tableName)

set clearedTable to RetrieveEntireTable(dbFile, tableName)
set enteredData to EnterDataAsList(dbFile, tableName, theColumns, theData)
set entireTable to RetrieveEntireTable(dbFile, tableName)
--ClearTable(dbFile, tableName)
set result1 to FindEntryInColumn(dbFile, tableName, "country", {"Italy"})
set result2 to FindEntryInColumn(dbFile, tableName, "country", {"Italy", "France"})

set result3 to DeleteRecordsInColumn(dbFile, tableName, "country", {"Italy"})
set result4 to FindEntryInColumn(dbFile, tableName, "country", {"Italy"})
set enteredData to EnterDataAsList(dbFile, tableName, theColumns, theData)

set result5 to FindEntryInColumn(dbFile, tableName, "country", {"Italy", "France"})

set result6 to DeleteRecordsInColumn(dbFile, tableName, "country", {"Italy", "France"})
set result7 to FindEntryInColumn(dbFile, tableName, "country", {"Italy", "France"})
set result8 to EnterAnItem(dbFile, tableName, theColumns, item 1 of result1)
set result9 to FindEntryInColumn(dbFile, tableName, "country", {"Italy", "France"})
ClearTable(dbFile, tableName)
set enteredData to EnterDataAsList(dbFile, tableName, theColumns, theData)

AddTableToDB(dbFile, updateTableName, theColumns)
set enteredData to EnterDataAsList(dbFile, updateTableName, theColumns, moreData)
set updatedTable to RetrieveEntireTable(dbFile, updateTableName)

set commonData to TableInterSect(dbFile, tableName, updateTableName)

return {result1, result2, result3, result4}


on TableInterSect(dbFile, firstTable, secondTable)
   
   --SELECT column1 [, column2 ]
   --FROM table1 [, table2 ]
   --[WHERE condition]
   --   
   --INTERSECT
   --   
   --SELECT column1 [, column2 ]
   --FROM table1 [, table2 ]
   --[WHERE condition]
   
   set firstQuery to "select * from " & firstTable
   set secondQuery to "select * from " & secondTable
   set sqlString to firstQuery & "; intersect; " & secondQuery
   
   
   set theDb to open db in file ¬
      dbFile with can create
   --   begin transaction db theDb
   
   try
      set commonData to query db theDb ¬
         sql string sqlString
   on error errMsg number errNum
      close db theDb
      display dialog ("Error Number: " & errNum as text) & return & return & "Error Message: " & return & return & "\"" & errMsg & "\""
      return {errMsg, errNum}
   end try
   return commonData
   
   
end TableInterSect


on DeleteRecordsInColumn(dbFile, tableName, columnName, textToFind)
   local theSQLString, argMarkers, columnName, textToFind
   if class of textToFind is text then set textToFind to {textToFind}
   set argMarkers to generatePlaceHolders(count of textToFind)
   
   set theSQLString to "delete from " & tableName & " where " & columnName & " in (" & argMarkers & ")"
   
   set theDb to open db in file ¬
      dbFile with can create
   
   try
      update db theDb ¬
         sql string theSQLString ¬
         with arguments textToFind ¬
         --result type list of lists
      set foundRecords to FindEntryInColumn(dbFile, tableName, columnName, textToFind)
      close db theDb
   on error errMsg number errNum
      close db theDb
      display dialog ("Error Number: " & errNum as text) & return & return & "Error Message: " & return & return & "\"" & errMsg & "\""
      return {errMsg, errNum}
   end try
   
end DeleteRecordsInColumn

on FindEntryInColumn(dbFile, tableName, columnName, textToFind)
   if class of textToFind is text then set textToFind to {textToFind}
   set argMarkers to generatePlaceHolders(count of textToFind)
   
   set theSQLString to "select * from " & tableName & " where " & columnName & " in (" & argMarkers & ")"
   
   set theDb to open db in file ¬
      dbFile with can create
   try
      set foundRecords to query db theDb ¬
         sql string theSQLString ¬
         with arguments textToFind ¬
         result type list of lists
      close db theDb
   on error errMsg number errNum
      close db theDb
      display dialog ("Error Number: " & errNum as text) & return & return & "Error Message: " & return & return & "\"" & errMsg & "\""
      return {errMsg, errNum}
   end try
   return foundRecords
end FindEntryInColumn

on CreateOpenDB(dbFile, tableName, theColumns)
   -- create and open db
   -- create table, clearing it if necessary   set saveTID to AppleScript's text item delimiters
   set saveTID to AppleScript's text item delimiters
   
   set AppleScript's text item delimiters to {", "}
   set theColumns to theColumns as text
   set AppleScript's text item delimiters to saveTID
   
   set theDb to ¬
      open db in file dbFile ¬
         with can create
   try
      update db theDb ¬
         sql string ("drop table if exists " & tableName)
      update db theDb ¬
         sql string ("create table " & tableName & " (" & theColumns & ")")
   on error errMsg number errNum
      display dialog ("Error Message: \"" & errMsg & "\"" & return & return & " Number: " & errNum as text)
      close db theDb
      return {errMsg, errNum}
   end try
   return theDb
end CreateOpenDB

on AddTableToDB(dbFile, tableName, theColumns)
   -- create and open db
   -- create table, clearing it if necessary   set saveTID to AppleScript's text item delimiters
   set saveTID to AppleScript's text item delimiters
   
   set AppleScript's text item delimiters to {", "}
   set theColumns to theColumns as text
   set AppleScript's text item delimiters to saveTID
   
   set theDb to ¬
      open db in file dbFile ¬
         with can create
   try
      update db theDb ¬
         sql string ("drop table if exists " & tableName)
      update db theDb ¬
         sql string ("create table " & tableName & " (" & theColumns & ")")
   on error errMsg number errNum
      display dialog ("Error Message: \"" & errMsg & "\"" & return & return & " Number: " & errNum as text)
      close db theDb
      return {errMsg, errNum}
   end try
   return theDb
end AddTableToDB

on EnterAnItem(dbFile, tableName, theColumns, anEntry)
   local theSQLString
   set argPlaceHolders to generatePlaceHolders(count of theColumns)
   
   set theSQLString to "insert into " & tableName & " values (" & argPlaceHolders & ")"
   
   set theDb to open db in file ¬
      dbFile with can create
   try
      update db theDb ¬
         sql string theSQLString ¬
         with arguments anEntry
      close db theDb
   on error errMsg number errNum
      close db theDb
      display dialog ("Error Number: " & errNum as text) & return & return & "Error Message: " & return & return & "\"" & errMsg & "\""
      return {errMsg, errNum}
   end try
   return theDb
   
end EnterAnItem

on EnterDataInDB(dbFile, tableName, theColumns, theData)
   local theSQLString
   set argPlaceHolders to generatePlaceHolders(count of theColumns)
   
   set theSQLString to "insert into " & tableName & " values (" & argPlaceHolders & ")"
   
   set theDb to open db in file ¬
      dbFile with can create
   try
      repeat with anEntry in theData
         update db theDb ¬
            sql string theSQLString ¬
            with arguments anEntry
      end repeat
      close db theDb
   on error errMsg number errNum
      close db theDb
      display dialog ("Error Number: " & errNum as text) & return & return & "Error Message: " & return & return & "\"" & errMsg & "\""
      return {errMsg, errNum}
   end try
   return theDb
end EnterDataInDB

on RetrieveEntireTable(dbFile, tableName)
   try
      set theDb to open db in file ¬
         dbFile with can create
      --   begin transaction db theDb
      set entireTable to query db theDb ¬
         sql string ("select * from " & tableName)
   on error errMsg number errNum
      close db theDb
      display dialog ("Error Number: " & errNum as text) & return & return & "Error Message: " & return & return & "\"" & errMsg & "\""
      return {errMsg, errNum}
   end try
   return entireTable
end RetrieveEntireTable

on ClearTable(dbFile, tableName)
   
   set theDb to open db in file ¬
      dbFile with can create
   
   -- clear database and start again
   try
      update db theDb ¬
         sql string ("delete from " & tableName)
   on error errMsg number errNum
      close db theDb
      display dialog ("Error Number: " & errNum as text) & return & return & "Error Message: " & return & return & "\"" & errMsg & "\""
      return {errMsg, errNum}
   end try
end ClearTable

on EnterDataAsList(dbFile, tableName, theColumns, theData)
   -- enter data as a batch (list of lists)
   local argPlaceHolders, sqlString
   set argPlaceHolders to generatePlaceHolders(count of theColumns)
   
   set sqlString to "insert into " & tableName & " values (" & argPlaceHolders & ")"
   
   set theDb to open db in file ¬
      dbFile with can create
   try
      batch update db theDb ¬
         sql string sqlString ¬
         with arguments theData
   on error errMsg number errNum
      close db theDb
      display dialog ("Error Number: " & errNum as text) & return & return & "Error Message: " & return & return & "\"" & errMsg & "\""
      return {errMsg, errNum}
   end try
   return theDb
end EnterDataAsList

to generatePlaceHolders(placeHolderCount)
   set argPlaceHolders to {}
   repeat (placeHolderCount) times
      set the end of argPlaceHolders to "?"
   end repeat
   set saveTID to AppleScript's text item delimiters
   set AppleScript's text item delimiters to {", "}
   set argPlaceHolders to argPlaceHolders as text
   set AppleScript's text item delimiters to saveTID
   return argPlaceHolders
end generatePlaceHolders
#4

To my surprise it looks like the entire script posted. (Maybe something was fixed?)

FWIW, if you have a minute, Shane, maybe you could look through it and see if there are any glaring errors or confirm I’m on the right track?

#5

This is confusing. To troubleshoot the intersect handler I had it execute the two queries separately and I got the correct result for each.

The I put back the intersect query and returned all three results.

Here’s the strange part. The first statement returns the correct result, all entries in the first table.

The second statement returns the first two entries from the first table and all five entries from the second table!?

The intersect statement returns all entries in the first table??

What am I missing here?

on TableInterSect(dbFile, firstTable, secondTable)
   
   --SELECT column1 [, column2 ]
   --FROM table1 [, table2 ]
   --[WHERE condition]
   --   
   --INTERSECT
   --   
   --SELECT column1 [, column2 ]
   --FROM table1 [, table2 ]
   --[WHERE condition]
   
   set firstQuery to "select * from " & firstTable
   set secondQuery to "select * from " & secondTable
   set sqlString to firstQuery & "; intersect; " & secondQuery
   
   
   set theDb to open db in file ¬
      dbFile with can create
   
   try
      set firstStatement to query db theDb ¬
         sql string firstQuery
      set secondStatement to query db theDb ¬
         sql string secondQuery
      
      
      set intersectResult to query db theDb ¬
         sql string sqlString
      
      close db theDb
      --   return {commonData, firstStatement, secondStatement}
   on error errMsg number errNum
      close db theDb
      display dialog ("Error Number: " & errNum as text) & return & return & "Error Message: " & return & return & "\"" & errMsg & "\""
      return {errMsg, errNum}
   end try
   return {intersectResult, firstStatement, secondStatement}
end TableInterSect
#6

My use, and therefore understanding, of SQLite is pretty basic. Hopefully someone more knowledgeable will chime in.

1 Like
#7

So it turns out that I don’t really need the intersect command. I can step through the 100 or so new items and queery if each one is in the 10k or so item db, and the entire operation takes seconds. (Using appleScript and lists it was taking the better part of an hour, if it didn’t crash).

But now I have a Myriad Tables question.

I have a list of four items. The first three are text that I want to display in table. The fourth is a reference, which will be used to copy or delete from the source, but won’t and can’t be displayed in the table.

I thought it was possible for Myriad Tables to take a list of lists and not display the last item.

Am I missing something?

#8

It is: see the dictionary entry for row template. But you may end up with problems if you try to use the references in the values returned (if I remember correctly, this happened to you before).

#9

That may have happened before… I do so many MT scripts, I kind of lost track.

So, here’s my solution: Before putting the list of lists into an MT Table, ill put it in a SQL Lite db. Then I’ll remove the column with the references.

When I get the user response from MT I’ll use that to query the db for the references.

BTW, in case you haven’t noticed, I am using the hell out out of:
Dialog Toolkit Plus
FileManagerLib
Myriad Tables Lib
SQLite Lib2

And I’m starting to go through all my old scripts and replace Properties with PrefsStorageLib

#10

Well that didn’t work. SQLite dislikes references as much as Myriad Tables. But, what did work was simply getting the id as text and using that to build a new reference when needed.

The script is done and working, although I have a few tweaks to workout. And now with all the SQLite Lib2 calls in handlers, it’s pretty damn easy to build and use databases.

Thanks!

#11

I do have another question.

When a db is loaded in memory from a handler, can you access it from another handler or from a script?

I can’t return “imDb” variable from a handler and use it in another, so it seems that any reference to the db in memory has to be local to the handler?

set imDb to open db in file dbFile ¬
with can write and load into memory
end
#12

Just make imDb a global or property of the script.

That may be problematic in debugging mode, because it’s a script object containing an ASObjC pointer. But it should work fine otherwise.

#13

I gather you’ve found another solution to your problem, but the following might be a possible avenue to explore if you were interested in an sql solution.

Assuming your db has 2 tables (table1 and table2) each with 3 columns (name, email, text), and that if the email addresses match the rest of the records would likely match.

I believe you might be able to do something like this:

set theResult to query db theDB sql string “select table1.name, table1.email, table1.text, table2.name, table2.email, table2.text from table1 inner join table2 on table1.email = table2.email”

I haven’t tested it, sorry, but you might be able to play around with that and joins to get what you’re after. The intersect command sounds more apt to what you’re after but I don’t have much experience with it. Whereas I use joins a lot and I’m pretty sure they could accomplish what you’re looking for.

#14

Thank you, I’ll have a look. My current solution is to iterate through the list from table two querying if it’s table 1.

To my surprise, it only takes a few seconds.