John,
Have you tested the Unix command that is in your "do shell script" Applescript command (that is, the "sqlite3 . . . " command) at a command prompt in Terminal? If you cannot get your Unix command to work correctly in Terminal, then it is unlikely that it will work correctly in a "do shell script" command in Applescript, either.
You might need more than just one Unix command to be able to do what you are trying to do. If that turns out to be the case, then you can collect those Unix commands and put them into a text file, thereby making a simple Unix _script_ out of them. You would then replace the single command (the sqlite3 command) that is currently in your "do shell script" statement with the name of the Unix script file. But, you can--and SHOULD--test the script at the Unix command prompt in Terminal first, before trying to make it work in an Applescript script. Be sure you remember to make the Unix script file "executable" or it will not work! There are plenty of basic Unix references that cover making a file executable and writing Unix shell scripts, so I will not repeat that here.
Just as a test, I tried to open the "Envelope Index" file (for my own Mail app) in a simple SQL application (SQLite Database Browser 2.0 b1), but I could not get the file to open in that app. The Envelope Index file seems to be a binary file. (I tried to copy the first part of the file to my screen in the Terminal app.) So, your problem may be that the Applescript and the underlying Unix command (the sqlite3 command) are expecting an ASCII text file (or Unicode format file, etc.) but that the file is actually a binary file, instead. In other words, it might be an "ASCII vs. binary" type of problem that you are experiencing. This is somewhat analogous to Apple's changing the format of .plist files from ASCII to binary awhile back (current OSX versions still seem to support both formats). In the case of a .plist file, it is possible to transform a binary .plist file into a corresponding ASCII .plist file, using Unix commands. Unfortunately, I do not know if there is a similar way to transform a Mail database file, assuming that that is your problem.
There are some "gotcha's," or caveats, to watch out for when using "do shell script." Some that I can recall off the top of my head are as follows:
o The (Unix) starting directory is DIFFERENT for "do shell script" than it is for the same user at an Unix prompt in Terminal. (In Terminal, the starting directory is the Home directory of the user, which is /Users/a_user/. If I recall, for "do shell script," it is "/" which is the root filesystem directory.)
o The shell (the command interpreter) MIGHT be different in "do shell script" than it is in Terminal. This might or might not matter, but it is worth considering whenever you are having problems with "do shell script." You can use an Unix command to display the current shell being used, to see what it is in both contexts. You can also use a (different) Unix command to CHANGE the current shell to be whatever you want it to be, at the start of a Unix script. That script then can be invoked either from the command line in Terminal or from a "do shell script" command in an Applescript script.
o Watch out for line endings! (Carriage Return only, vs. Line Feed only, vs. Carriage Return Line Feed)
Be sure to read Technical Note TN2065. It contains some additional "gotcha's" along with directions on how to use "do shell script:"
Technical Note TN2065: do shell script in AppleScript
Be especially certain to read the parts of the above technical note that talk about line endings; I think this could be your problem!
Good luck; please keep us updated on your progress (or lack of it), here on this list.
-B.
On Oct 28, 2013, at 5:39 PM, John wrote: I am trying to pull some data from the sqlite database Mail uses.
However, the in_reply_to field of the messages table is NULL for every
value when it should return the message id of the original message which was replied to. set databasePath to POSIX path of (path to library folder from user domain as text) & "Mail/V2/MailData/Envelope Index"
set theReply to (do shell script "sqlite3 " & quoted form of databasePath & " \"SELECT DISTINCT in_reply_to from messages;\"")
However, I can extract the value of the header with something like this:
tell application "Mail"
set aMessage to first item of (get selection)
try
set inReplyTo to extract address from (content of aMessage's header "in-reply-to")
on error
set inReplyTo to "NULL"
end try
end tell
Where can I find the value of in_reply_to field within the Envelope Index database?
|