We received a report from one TeamworkPM customer that the sorting for Czech language on our people page was incorrect.
It took me 30 minutes to track down the problem so I hope this will safe some of you a bit of frustration.

Problem #1 - Merging of accented and non-accented characters

Say we had 2 users - "Candy" and "Čatelyn" (note the accent on Č), MySQL was returning only one row when we ran the following code

SELECT DISTINCT   ucase( left( userFirstName, 1 ) ) AS letter
FROM users u
ORDER BY  letter

was returning just 1 row with "C".
We wanted it to return "C" and "Č".

The fix:

I was scratching my head but it's obvious in hindsight – we just needed to change the collation on the userFirstname field from utf8_unicode_ci to utf8_bin.

Problem #2 - Sort order incorrect

That was one problem fixed. It turns out that MySQL was still putting names that start with accented characters at the end of the list so we had:

Anya
Candy
Mary
Čatelyn

instead of

Anya
Candy
Čatelyn
Mary

To fix this I just sorted using the following:

SELECT DISTINCT   ucase( left( userFirstName, 1 ) ) AS letter
FROM users u
ORDER BY  letter collate utf8_general_ci, letter

Hope this helps somebody.

We support over 26 languages in TeamworkPM. Today our backup script failed for one customer because the generated file name had bizarre characters in it. To fix this I amended our safeFileName() function to just use alpha-numeric characters. But if all characters are ripped out and we are left with just the extension (eg. ".zip"), then we fall back to a time-stamp. Here's the code:

<cffunction name="SafeFileName" returntype="string" output="No">

<cfargument name="fileName" required="Yes" type="string">

<cfset var result = ARGUMENTS.fileName>

<cfset result = trim( reReplace( result , "[\ \,]", "_", "ALL" ) )>

<cfset result = reReplace( result , "[^a-zA-Z0-9\.\_]", "", "ALL" ) >

<cfif Len( result ) IS 4 AND Left( result, 1 ) IS ".">

<cfset result = dateFormat(now(),"ddmmyyyy") & timeFormat(now(),"hhmmss") & result>

</cfif>

<cfreturn result>

</cffunction>

I'm working on a referral program for TeamworkPM and as part of this we are going to provide a link for our resellers to post on Twitter with a shortened URL that contains a link to our main website along with a "ref" code.

I've google around and found an excellent library for creating Bit.ly URLs with ColdFusion by Matt Gifford.

It was a breeze to use, I just created a free account with Bit.ly, grabbed my API key and I was generating shortened URLs.

Now the only thing I've done to make the process more efficient for our TeamworkPM site is to cache the result of the shortened URL in memcached:

<cffunction name="shortenURL" returntype="string">

<cfargument name="url" required="Yes">

<cfset var bitlyAPIUsername = "teamworkpm">

<cfset var bitlyAPIKey = "---- you key here ---">

<!--- Retrieve from Mem cache --->

<cfset var memCacheKey = "shortURL#Hash( ARGUMENTS.url )#">

<cfset var memCacheLookup = APPLICATION.twutil.teamworkPMCache.getInstallationCache( key="#memCacheKey#" )>

<cfif memCacheLookup.cacheIsOK>

<cfreturn memCacheLookup.value>

</cfif>

<cfset objBitly = createObject( "component", "cfcs.utility.com.coldfumonkeh.bitly.bitly" ).init(

username = bitlyAPIUsername,

apikey = bitlyAPIKey,

parse = true

)>

<cfset bitLyResponse = objBitly.shorten( longURL = ARGUMENTS.url )>

<cfif structKeyExists( bitLyResponse.response, "status_code" ) AND bitLyResponse.response.status_code.XmlText IS "200"

AND structKeyExists( bitLyResponse.response, "data" )

AND structKeyExists( bitLyResponse.response.data, "url" )>

<!--- Store value in memcache --->

<cfset APPLICATION.twutil.teamworkPMCache.setInstallationCache( key=memCacheKey, value=bitLyResponse.response.data.url.XmlText )>

<cfreturn bitLyResponse.response.data.url.XmlText>

</cfif>

<cfreturn "">

</cffunction>

Thanks Matt. Now back to the referral scheme... hoping to be done version 1 today.

The other day, I posted a quick tip on using INSERT IGNORE to avoid duplicate keys. Well this is embarrassing but I take it all back, do not use that tip. Why? Well I should have read the docs properly - using IGNORE instructs MySQL to ignores all errors which is less that ideal. D'oh!

Instead I've found that using "REPLACE" instead of INSERT works great for MySQL. eg.

REPLACE INTO subscribed_projectmessage_users( projectmessageId, userId )
VALUES( #getProjectMessageId.projectmessageId#, #notifyUserId# )

Hey Guys,

I was just setting up a new workhorse server for TeamworkPM and I needed to set-up 19 datasources. This could have been quote tedious but a little bit of googling and I found the answer in a cf-talk thread:

All you have to do is copy the files neo-datasource.xml and neo-drivers.xml from C:\ColdFusion9\lib to your new server and you are in business.

Worked perfectly for me anyhow.

Hey guys, this morning I wanted to know if a request was coming from an Ajax call or it a user was browsing the URL directly. This was important because in TeamworkPM, our calendar was displaying a bit of javascript on the screen when the user tried adding an event and his session was timed out.

It actually took me a while to figure this out but there is a standard header used by both Prototype.js and JQuery that can help. The header is 'X-Requested-With': 'XMLHttpRequest'.

So to determine if a request in ColdFusion comes from Ajax, just use something like:

<cfset headers = GetHttpRequestData().headers>
<cfif structKeyExists( headers, "X-Requested-With" ) AND headers[ "X-Requested-With" ] EQ "XMLHttpRequest">
<!--- It's AJAX Baby! --->
</cfif>

Hope that saves somebody out there some googling.

I didn't know this until minutes ago, when you are inserting and you think it might be there already I have been using something like
ON DUPLICATE KEY UPDATE A = A

Well, you could just use INSERT IGNORE and if the records exists, it's silently skipped.

One of my pet usability gripes that just about every login page on the Internet commits is that when you click a "Forgot your password" link, the email address you just entered seconds ago must be re-entered on the new form that appears. Just copy over the email address or username the user previously entered.

The biggest thing turning me off Firefox right now is that just about every time I start it and I'm busy in the middle of something I get that stupid fucking "Update Now" screen. Couldn't they just do that AFTER firefox is loaded - have a little icon indicating that updates are available when you are good and ready for them. It drives me nuts. No wonder I'm using Chrome more and more. Rant Over.

While fixing a very minor problem on the calendar in TeamworkPM today, I ran into a javascript hick-up. I have a HREF element object assigned to a variable and I simply wanted to simply get it's full HTML. Not it's innerHTML for which I would just use .innerHTML().

So if I had go on, I wanted the string "go oo", not just "go on".

So I tried the seemingly obvious:

var html = $( myDomObject ).outerHTML;

But this works in IE and Safari.

Some intensive googling later and I was very surprised to find that there is no easy way - There is also no handy function in prototype.js for this which also surprised me.

I asked the other guys and they didn't have a clue either. In all these years of javascript programming, none of us have ever had this requirement before.

The solution was to create a simple little function that will either use the built-in outerHTML() that IE and Safari support or instead will place a clone of the element within a new div and then get the innerHTML of the div; the code is:

function getHTML(element) {

if ("outerHTML" in document.body) {

return element.outerHTML;

}

var tmp = new Element("div").update(element.cloneNode(true));

var html = tmp.innerHTML;

delete tmp;

return html;

}

And I thought I was a javascript Ninja. That's the thing about this job, you are always learning.