geeknixta.com

Telligent Mail Templates and AvatarURL tildes

Telligent's email templating is very powerful, but leaves a lot to be desired on the documentation front. It also appears to have an annoying bug, which of course we ran into headfirst. You see, we wanted to include the user's avatar image in notification emails that our community sends out.

The Problem

Sadly, the token you're supposed to use for this, $User.AvatarUrl, is worthless; it returns a URL that begins with a "~". This might be useful if there were an email template function to handle "~"s in URLs (it refers to the root of the site) but there's nothing to say there is, and nothing to document how you might process the result with a custom function. In .Net there are helper functions Telligent provide that fix this, but not in the email template language.

I left this post on the Telligent forums, in which I point out that using that token you end up with a URL that looks like:

~/blahblah.aspx

And that using the function $global.FullPath() on the token, you end up with:

http://geeknixta.com~/blahblah.aspx

Which of course DNS doesn't know how to handle, and nor does the browser…

The hopeful solution - Markup Translations?

So I began to think… Could I use a MarkUp Translation to do this for me and remove the "~"? If so, I could just add this to my theme in the theme.config.

I started to look for the telltale ApplyMarkupTranslations() call using the amazing and indispensable .Net Reflector. In retrospect this wasn't a sensible option (I sort of suspected this to begin with, but didn't really think it through until afterwards), but I did learn a lot about what's happening in there. In particular, NVelocity is the templating engine. And incidentally, if Telligent is reading this, it would be great if we could use a variant of Markup Translations on email output!

The actual solution - SQLServer Triggers

While I was in there looking at the binaries, I did eventually trace through to find that emails end up in a SQLServer table. I took at look at the tables and only one looked promising: mg_EmailQueue.

Inspecting the contents of this table as I created a new Blog Posts, this was indeed where emails go while they wait to be sent out. So, what if I "fix" the bad data as it enters the queue? It's a hack, but it would work.

Replace on an NTEXT field

The obvious solution would be to use REPLACE() in an AFTER INSERT trigger. Trying that, you get this error:

Argument data type ntext is invalid for argument 1 of replace function.

There is a slight problem in that the email field is of type NTEXT, and you cannot run the REPLACE() Transact-SQL function on NTEXT fields. Now, realistically, any email won't be the length of 1000 novels, much less a page or two, so it's safe to use the hack below (found here, thank you Google and Sides of March) of casting to NVARCHAR(MAX) and then casting the output of REPLACE() back to NTEXT on our data. It probably doesn't perform very well, but for our community (we're licensed up to 200 people) it will do and perhaps in time Telligent will either get back to me with a better solution, or a hotfix if this is a bug (it smells like one).

The final Trigger

So here's the code for the final trigger. Our database is called _Telligent - you'll need to change that in the USE statements. You might also want to be more specific that just replacing the '=7E' with a blank string (I'm guessing the email is MIME encoded so "~" becomes "=7E").

USE [_TelligentCommunity]
GO

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TelligentHacks_TidyTildeURLs]'))
DROP TRIGGER [dbo].[TelligentHacks_TidyTildeURLs]
GO

USE [_TelligentCommunity]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[TelligentHacks_TidyTildeURLs]
ON  [dbo].[mg_EmailQueue]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

UPDATE [dbo].[mg_EmailQueue]
SET EmailXml = cast(replace(cast(mgeq.EmailXml as nvarchar(max)), '=7E', '') as ntext)
FROM Inserted JOIN [dbo].[mg_EmailQueue] as mgeq
ON Inserted.EmailID=mgeq.EmailID;

END

GO

And there you have it

It's painful, but it works and since we want to include the avatar in our email notifications, it's the only way. Again though, we learn some hard lessons:

• Telligent documentation is bad. In this case we at least had good email token doco, but no examples of using it or extending it (is it even possible?).
• Not all Telligent functionality works (I'd say this is a bona-fide bug).
• Telligent's forums are an unreliable way to get an answer.
• My luck = even the workaround needs workarounds, viz. (cast(replace(cast())).
• If Telligent change the MIME formatting, or introduce other AFTER INSERT triggers, our fix breaks. I try hard to integrate such a fix seamlessly, but we're in a closed system hooking on to scaly skin, so be prepared!