Using ATTACH statements with SQLiteLib2?

AppleScript SQLite Lib
#1

Hi Shane,
Before I get too far down the rabbit hole I’m currently staring into, does SQLiteLib2 support the use of SQLite ATTACH statements to construct queries that span multiple databases? I’ve got 3 years of data in 3 separate databases and I’d like to run SQLite queries that compares data across all three of them. I have a valid SQLIte query that checks out in dB Browser, but when try running it in SQLiteLib I get an error that “there seem to be no columns in this result.” When I take out the ATTACH statements and run the query on only one database, it returns the expected result.

Again, before I dig too deeply into trying to figure this out…is this even possible in SQLiteLib?

Thanks!
Matt

#2

SQLiteLib just calls Apple’s version of SQLite, so the answer really is yes if Apple’s SQLite does, and no if it doesn’t. Sorry I can’t be more helpful, but my own use of SQLite is very lite indeed.

#3

Hi Shane – solved! I had been using SQLiteLib2’s “query” command, but it turns out that’s not the way to do it.

I was looking through your documentation again to see if I was missing something, and it occurred to me that a command like “ATTACH”, while not a command that writes or updates anything to the database itself, is more of a precursor to a “query” rather than a query itself. I work pretty exclusively with read-only databases, trying to use AppleScript to peer over the shoulder of another program that’s doing the writing to the database.

It turns out that the way to use ATTACH is with either “update” (if you’re attaching one database) or “execute statements on dB” if you need to attach several, with each ATTACH statement separated by a semicolon. E.g.:

property db2023 : “/path/to/database2023.sqlite”
property db2022 : “/path/to/database2022.sqlite”
property db2021 : “/path/to/database2021.sqlite”

set theDb to open db in file db2023
set theStatements to (execute statements on db theDb sql string “ATTACH DATABASE '” & db2021 & “’ AS db2021; ATTACH DATABASE '” & db2022 & “’ AS db2022”)

Once you’ve done that, you can then construct a query that spans all three databases. In my case, pulling race timing data from 3 separate databases associated with 3 separate events. E.g:

set theQuery to (query db theDb sql string “SELECT Levels.Level, ROUND(AVG(FinishTime), 4) AS AverageFinishTime FROM (SELECT LevelID, FinishTime FROM db2021.bigChart UNION ALL SELECT LevelID, FinishTime FROM db2022.bigChart UNION ALL SELECT LevelID, FinishTime FROM bigChart) AS CombinedData JOIN Levels ON CombinedData.LevelID = Levels.LevelID WHERE Levels.LevelID != 9 GROUP BY Levels.Level ORDER BY AverageFinishTime ASC;”)

Now, I don’t know if this is a SQLite quirk that the correct command to use an ATTACH statement is “update” or “execute transaction”, but I would note that the ATTACH statement APPEARS to be “non destructive,” in the sense that even though we’re supposedly writing to the database (“update” implies you’re modifying the database to new values), using SQLiteLib this way does NOT seem to actually write to the database file on disk. In my case, that’s a good thing, since in my use case, I am trying very hard not to interfere with the behavior of the program that’s actually writing to the database.

Glad to have this extra feature available to me, and I hope others can use it, too! And Shane, thanks for a great little tool. Being able to work so efficiently with SQLite in AppleScript has been an absolute game changer for the kinds of data analysis I enjoy doing!

M2