Mail to sqlite problem

vince · November 24, 2006, 10:48pm

Hi,
I have the rough start of a script which takes the selected mail messages, gets data from mail on sender, subj, date, content, and then puts that data into a sqlite database and moves the selected message to the trash. The great advantage to doing this is that the script doesn’t need to open any database like filemaker, etc. The disadvantage is that you have to use shell to send the data to sqlite3. Therein is the problem. The script seems to work fine for TEXT emails, but to get the unix shell command correct, I have to search by char for any single quotes and turn them into single curly-quotes, to avoid an erroneous shell call. What seems to be the problem is HTML emails which for some reason confuses the “single quote replacement” feature, I believe.

So, does anyone have any clues as to why html email isn’t being parsed correctly?
Any better suggestions for the single quote replacement?

Here’s the script:

-- parts of this use Apple's script provided in Script menu for Mail...small parts only
set desktopPath to path to desktop
set dbPath to desktopPath & "Mail_Archive.db" as string
set fixedtext to ""
tell application "Mail"
	set theSelectedMessages to selection -- from Apple
	if (count of theSelectedMessages) is equal to 0 then -- from Apple
		display dialog "Please select a message in Mail first, then run this script again." -- from Apple
	else -- from Apple
		repeat with x from 1 to (count of theSelectedMessages) -- from Apple
			set theMessage to item x of theSelectedMessages -- from Apple
			set theSender to sender of theMessage
			set theSubject to subject of theMessage
			set theContent to content of theMessage
			set theDateSent to date sent of theMessage
			move theMessage to mailbox "Trash"
			repeat with x from 1 to (count theContent) -- this repeat screens out single quotes which would mess up the shell command syntax
				if character x of theContent = "'" then
					set fixedtext to fixedtext & "'" as string -- inserting the curly quote here
				else
					try
						set fixedtext to fixedtext & character x of theContent as string -- this is choking with html mails...
					on error errnum
						-- do nothing
						-- I added this to see if it would allow it to work with whatever was choking the script...it didn't help
					end try
				end if
			end repeat
			
			-- code to add to SQLite database
			if not my fileExists(dbPath) then
				set dbPath to POSIX path of file dbPath
				set d to space & dbPath & space
				set s to "sqlite3" & d & quote
				set s to s & "create table Mail_Archive(sender,subject,date,content);"
				set s to s & "insert into Mail_Archive values('" & theSender & "','" & theSubject & "','" & theDateSent & "','" & fixedtext & "');"
				set s to s & quote
				do shell script s
			else
				set dbPath to POSIX path of file dbPath
				set d to space & quote & dbPath & quote & space
				set s to "sqlite3" & d & quote
				set s to s & "insert into Mail_Archive values('" & theSender & "','" & theSubject & "','" & theDateSent & "','" & fixedtext & "');"
				set s to s & quote
				do shell script s
			end if
			
		end repeat
	end if
end tell


on fileExists(thepath)
	try
		alias thepath
		return true
	on error
		return false
	end try
end fileExists

TIA
Vince

Craig_Smith · November 25, 2006, 12:37am

Vince:

I don’t know if this will help you, but I had a similar issue (using mysql) and the solution I happened upon uses two escapes with the single quote as a replacement:

set end of new_word to "\\'"

For my project, it just checks every string as it goes through, and those that contain the single quote get sent to the handler to cycle through all the characters, and does the replacement above into a list (as opposed to your string variable), then concatenates all the characters into a string at the end.

Good luck,

Adam_Bell · November 25, 2006, 12:40am

Hi Vince;

Nice script. I use the loopless approach below to “clean” my text inputs. Can you give us an example of an HTML mail script that fails? Perhaps whatever causes it can just be parsed out. Let us know if Craig’s solution is the way to go for you too.

(*
****** Check Entered Data for Single Quotes ******
 Because SQLite commands are extensively single-quoted,
 any text to be stored containing single quotes must have
 them escaped. In SQLite, this is done by doubling them:
 two single quotes with no space between, not a double
 quote.
 *)
to escQuotes(RawText) -- returns escaped text
	set tid to AppleScript's text item delimiters
	set AppleScript's text item delimiters to "'"
	set p to text items of RawText
	set AppleScript's text item delimiters to "''" -- you might try Craig's "//'" instead of "''"
	set fixedText to p as text
	set AppleScript's text item delimiters to tid
	return fixedText
end escQuotes

EDIT: I use Manfred Bergman’s sqliteQuery to do quick checks on things.

Craig_Smith · November 25, 2006, 12:56am

Adam:

Great idea using the ASTID! I will try that on my project, as well.

Craig_Smith · November 25, 2006, 5:23am

I just noticed another thing, which may be useful. I don’t know if sqlite can import records from a text file, but if so, just write your data there, and instruct sqlite to bring it in that way. In that fashion, all the apostrophes, quotes, etc., are brought in intact by the software. In mysql, you can even choose your own delimiter (I typically use <, >, =, or ^) to delineate the fields. You then just instruct the program to use those delimiters. Here is the code that works for mysql:

LOAD DATA INFILE '/Users/casdvm/DVDDB2mySQL/Main02.txt' INTO TABLE main FIELDS TERMINATED BY '=' LINES TERMINATED BY '\r';

Here is what a typical looks like in the .txt file:

1-08 A Boy Named Sue=17=The Man In Black His Greatest Hits (Disc 1)=3=iTunes Music:Compilations:The Man In Black His Greatest Hits (Disc 1):1-08 A Boy Named Sue.mp3

This has 5 data fields per line; as long as your table also has 5 fields, your .txt file can have as many lines as you want, and the data is installed very fast.

Hope this helps.

vince · November 25, 2006, 3:48pm

Thanks for all the fantastic suggestions, guys. Very helpful. This was just a “rough draft” and I only tested it a few times. I originally planned on using “offset” to determine whether there were single quotes which had to be replaced prior to the text replacement repeats. Further testing, however, reveals several problems with the SQLIte shell command. It isn’t only choking on html emails, but it is choking on emails which have quotes and semicolons within their text. Since these characters are also part of the sqlite command syntax, this is messing up the unix command. Replacing them all using AS would be slow, unless someone knows of a scripting addition which would be a lot faster. I’m thinking another option might be the solution.

Craig brings up a possibly very useful alternative – import from a file. Looking at sqlite3 man in terminal, sqlite CAN import a file, however, this must be done with meta commands in the interactive mode. (not a good option for a script…) BUT, the man pages also say that you can run sqlite3 using ‘-init FILE’ and it will run commands from a text file and the commands can include metacommands. Unfortunately, the documentation is rather weak in terminal, so I am not sure what the format for the text file has to be, nor whether you can specify the delimiters… This will require some experimentation (and a plea for help from any unix/sqlite gurus). SQLite can export to a text file and then reimport the file to recreate a database, so that is a potential option which can also be explored and may reveal the data structure as well.

I have a lot of 3d modeling to do today, so I might not get to this right away… be patient! If this can be done, I think this would be a very useful script. I’m also into Supercard, and I will probably make a generic sqlite database front end using Supercard. I’d be glad to post that (as freeware) once I get this whole mess straightened out.

Thanks again. This is a great board!

Vince

Adam_Bell · November 25, 2006, 4:26pm

Vince;

For each discovered character to be escaped, you just extend the delimiter search. If you wanted to add semicolons, this would do it (not tested, but likely to work):


to escChars(RawText) -- returns escaped text -- escapes single quotes and semicolons.
	set tid to AppleScript's text item delimiters
	set AppleScript's text item delimiters to "'"
	set p to text items of RawText
	set AppleScript's text item delimiters to "''" -- you might try Craig's "\\'" -- not sure about backslash
	set firstFix to p as text
	set AppleScript's text item delimiters to ";"
	set Q to text items of firstFix
	set AppleScript's text item delimiters to "\\;" -- again forward or back slash - haven't tested.
	set fixedText to Q as text
	set AppleScript's text item delimiters to tid
	return fixedText
end escChars

EDIT: fixed as per Vince’s comment below - now compiles and works

vince · November 25, 2006, 4:53pm

Adam,

Yep, that will work, but could be pretty slow on longer emails. I might adapt your idea, and save the text to a file and then use unix to do the character substitution.

Thanks again.

Vince

Adam_Bell · November 25, 2006, 7:10pm

if you test the text item delimiter method, even for a very long file, against a do shell script sed regex approach, I’ll bet that unless the mails are extremely long and littered with characters to be escaped, the TID method wins, because there’s an overhead in running a shell script of about a 10th of a second typically. AppleScript is very good at text parsing of this type.

If you really care, use getMilliSec before and after each run to do it, e.g.

set t1 to getMilliSec
– do stuff
set t2 to getMilliSec

set ET to (t2 - t1) / 1000 – decimal seconds.

If you try it out, I’d love to know the result.

vince · December 2, 2006, 3:35pm

Adam,

I think your text item delimiter method will work great…since it checks only for the “suspect” characters, it cuts down on the script’s workload immensely. I didnt’ time it, but I don’t think there’s a need to – it will clearly be faster. Note - your script as written, wouldn’t compile. It seems that you need double backslashes for it to compile (\’ instead of ').

I tried it with a test script which seems to be doing the character substitution correctly, and returns text with the problem characters escaped, but when I run the script, I’m still getting an error.

I sent a text message to myself which had this content:

I’m going to bed; See y’all in the morning.
xx’s & oo’s
vinnie

this is what is returned in the ‘fixedtext’ variable after running the script (what ScriptDebugger is displaying in the variable’s display window – the actual variable itself seems to have \ which allows applescript to escape the escape char):

I'm going to bed; See y'all in the morning.
xx's & oo's
vinnie

Here is the shell script that is generated:

sqlite3 /Users/vangelon/Desktop/Mail_Archive.db “create table Mail_Archive(sender,subject,date,content);insert into Mail_Archive values(‘Vince Angeloni vaDeleted@deleted.com’,‘testing’,‘Saturday, December 2, 2006 9:06:46 AM’,'I'm going to bed; See y'all in the morning.
xx's & oo's
vinnie
');”

To my eye, this looks like a viable shell script, but running it via AS or in terminal, I am getting:
SQL error: unrecognized token: ""

Any clues???

Here’s the script as I have it currently modified (note that for it to function correctly, we’ll also have to screen the subject, sender for single quote, semicolon, doublequotes, etc)

set desktopPath to path to desktop
set dbPath to desktopPath & "Mail_Archive.db" as string
set fixedText to ""
tell application "Mail"
	set theSelectedMessages to selection -- from Apple
	if (count of theSelectedMessages) is equal to 0 then -- from Apple
		display dialog "Please select a message in Mail first, then run this script again." -- from Apple
	else -- from Apple
		repeat with x from 1 to (count of theSelectedMessages) -- from Apple
			set theMessage to item x of theSelectedMessages -- from Apple
			set theSender to sender of theMessage
			set theSubject to subject of theMessage
			set theContent to content of theMessage
			set theDateSent to date sent of theMessage
			move theMessage to mailbox "Trash"
			set fixedText to my escChars(theContent)
			-- code to add to SQLite database
			if not my fileExists(dbPath) then
				set dbPath to POSIX path of file dbPath
				set d to space & dbPath & space
				set s to "sqlite3" & d & quote
				set s to s & "create table Mail_Archive(sender,subject,date,content);"
				set s to s & "insert into Mail_Archive values('" & theSender & "','" & theSubject & "','" & theDateSent & "','" & fixedText & "');"
				set s to s & quote
				do shell script s
			else
				set dbPath to POSIX path of file dbPath
				set d to space & quote & dbPath & quote & space
				set s to "sqlite3" & d & quote
				set s to s & "insert into Mail_Archive values('" & theSender & "','" & theSubject & "','" & theDateSent & "','" & fixedText & "');"
				set s to s & quote
				do shell script s
			end if
			
		end repeat
	end if
end tell

on fileExists(thepath)
	try
		alias thepath
		return true
	on error
		return false
	end try
end fileExists

to escChars(RawText) -- returns escaped text -- escapes single quotes and semicolons.
	set tid to AppleScript's text item delimiters
	set AppleScript's text item delimiters to "'"
	set p to text items of RawText
	set AppleScript's text item delimiters to "\\'" -- you might try Craig's "\\'" -- not sure about backslash
	set firstFix to p as text
	set AppleScript's text item delimiters to ";"
	set Q to text items of firstFix
	set AppleScript's text item delimiters to "\\;" -- again forward or back slash - haven't tested.
	set fixedText to Q as text
	set AppleScript's text item delimiters to tid
	return fixedText
end escChars

vince · December 2, 2006, 5:50pm

To escape single quotes, SQLite expects TWO single quotes together. (‘’ - 2 single quotes). You don’t need to escape anything else, supposedly. So I eliminated everything except the single quote escapes.

However, I’ve tried it on several occasions and have gotten inconsistent results. In some cases, it works, but try it again with the same file later and it may choke. Mystifying…

Here’s the current version of the script that I am testing (rough draft):

set desktopPath to path to desktop
set dbPath to desktopPath & "Mail_Archive.db" as string
set fixedText to ""
tell application "Mail"
	set theSelectedMessages to selection -- from Apple
	if (count of theSelectedMessages) is equal to 0 then -- from Apple
		display dialog "Please select a message in Mail first, then run this script again." -- from Apple
	else -- from Apple
		repeat with x from 1 to (count of theSelectedMessages) -- from Apple
			set theMessage to item x of theSelectedMessages -- from Apple
			set theSender to sender of theMessage
			set theSubject to subject of theMessage
			set theContent to content of theMessage
			set theDateSent to date sent of theMessage
			move theMessage to mailbox "Trash"
			-- now fix/escape single quotes (need to replace single quotes with TWO single quotes for SQLite to accept!
			set theSender to my escChars(theSender)
			set theSubject to my escChars(theSubject)
			set theContent to my escChars(theContent)
			-- code to add to SQLite database
			if not my fileExists(dbPath) then
				set dbPath to POSIX path of file dbPath
				set d to space & dbPath & space
				set s to "sqlite3" & d & quote
				set s to s & "create table Mail_Archive(sender,subject,date,content);"
				set s to s & "insert into Mail_Archive values('" & theSender & "','" & theSubject & "','" & theDateSent & "','" & theContent & "');"
				set s to s & quote
				do shell script s
			else
				set dbPath to POSIX path of file dbPath
				set d to space & quote & dbPath & quote & space
				set s to "sqlite3" & d & quote
				set s to s & "insert into Mail_Archive values('" & theSender & "','" & theSubject & "','" & theDateSent & "','" & theContent & "');"
				set s to s & quote
				do shell script s
			end if
			
		end repeat
	end if
end tell

on fileExists(thepath)
	try
		alias thepath
		return true
	on error
		return false
	end try
end fileExists

to escChars(RawText) -- returns escaped text -- escapes single quotes
	set tid to AppleScript's text item delimiters
	set AppleScript's text item delimiters to "'"
	set p to text items of RawText
	set AppleScript's text item delimiters to "\\''" -- you might try Craig's "\\'" -- not sure about backslash
	set firstFix to p as text
	return firstFix
	end escChars

vince · December 2, 2006, 8:31pm

I think this is working…please test!
Vince

Two applescripts: the first one will trash the email message(s) you have selected in Mail and place into a sqlite database in your documents folder. The second one will allow you to see the data that’s been stored (should be in results window in script ed).

set UserPath to (path to documents folder from user domain as string)
set hfsFilePath to (UserPath & "Mail_Archive.db") as string
set dbPath to POSIX path of hfsFilePath -- need an hfs path to the same place  as dbpath to feed to the fileexists handler
tell application "Mail"
	set theSelectedMessages to selection -- from Apple
	if (count of theSelectedMessages) is equal to 0 then -- from Apple
		display dialog "Please select a message in Mail first, then run this script again." -- from Apple
	else -- from Apple
		repeat with x from 1 to (count of theSelectedMessages) -- from Apple
			set theMessage to item x of theSelectedMessages -- from Apple
			set theSender to sender of theMessage
			set theSubject to subject of theMessage
			set theContent to content of theMessage
			set theDateSent to date sent of theMessage
			move theMessage to mailbox "Trash"
			-- now fix/escape single quotes (need to replace single quotes with TWO single quotes for SQLite to accept!)
			set theSender to my escChars(theSender)
			set theSubject to my escChars(theSubject)
			set theContent to my escChars(theContent)
			-- code to add to SQLite database
			if not my fileExists(hfsFilePath) then
				set d to space & dbPath & space
				set s to "sqlite3" & d & quote
				set s to s & "create table Mail_Archive(sender,subject,date,content);"
				set s to s & "insert into Mail_Archive values('" & theSender & "','" & theSubject & "','" & theDateSent & "','" & theContent & "');"
				set s to s & quote
				do shell script s
			else
				set d to space & quote & dbPath & quote & space
				set s to "sqlite3" & d & quote
				set s to s & "insert into Mail_Archive values('" & theSender & "','" & theSubject & "','" & theDateSent & "','" & theContent & "');"
				set s to s & quote
				do shell script s
			end if
			
		end repeat
	end if
end tell

on fileExists(thepath) -- requires an HFS file path as input!!!
	try
		alias thepath
		return true
	on error
		return false
	end try
end fileExists

to escChars(RawText) -- returns escaped text -- escapes single quotes and semicolons.
	set tid to AppleScript's text item delimiters
	set AppleScript's text item delimiters to "'"
	set p to text items of RawText
	set AppleScript's text item delimiters to "''"
	set firstFix to p as text
	set AppleScript's text item delimiters to "\""
	set Q to text items of firstFix
	set AppleScript's text item delimiters to "\\\"" -- again forward or back slash - haven't tested.
	set fixedText to Q as text
	set AppleScript's text item delimiters to tid
	return fixedText
end escChars

and the second one…

set dbPath to "~/Documents/Mail_Archive.db"

set d to space & dbPath & space
set s to "sqlite3 -list" & d & quote
set s to s & "select * from Mail_Archive;"
set s to s & quote
do shell script s

EDITED @ 2015 hours, 2 December
The path functions weren’t working properly. This was corrected. Also the fileexists handler must be given an HFS path to work correctly. This was added. Sorry, but I think we’re finally geting there!

EDITED @ 12PM 3 December
It was choking on double quotes in the subject and content. These are now escaped and it seems to be working better.