Google API Translation at work
Over the weekend, I wrote a resuable script that converts snippets of English text to 20 other languages using the new
Google translate Ajax API for our
Teamwork Project Manager software.
The script was hacked together quickly and isn't the best most structred code in the world but it works well - it cycles through any strings in the languages table that haven't been converted yet and one by one, converts them to a foreign language. Every time, the script has 20 results, it saves the changes to the database.
Watching it at work is pretty cool with strings coming back in Arabic, Chinese, Russian etc.
The strings are stored in the following table MySQL table:
CREATE TABLE `languagestrings` (
`languageStringId` int(10) unsigned NOT NULL auto_increment,
`languageStringRef` varchar(255) collate utf8_bin NOT NULL,
`languageCode` char(2) character set utf8 NOT NULL,
`languageString` text character set utf8 NOT NULL,
`languageStringUsageCount` int(10) NOT NULL default '0',
PRIMARY KEY (`languageStringId`),
UNIQUE KEY `langStringRef` (`languageStringRef`,`languageCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
And translating the strings is done with the function
translate( ref, english_default, bUpdateNow, language ) which works like so:
#APPLICATION.teamworkpm.translate( "Your Projects", "Your Projects", false, "ES" )# gives "Su Proyectos".
Here is the translate function code:
<cffunction name="translate" returnType="string" output="No">
<cfargument name="ref" type="string" required="yes">
<cfargument name="englishDefault" type="string" required="Yes">
<cfargument name="updateNow" type="boolean" default="no">
<cfargument name="lang" type="string" default="#SESSION.lang#">
<cfset var getLangString = "">
<cfset var insLangString = "">
<!--- Ensure there is a structure for the currency language code --->
<cfif NOT StructKeyExists( APPLICATION.langStrings, ARGUMENTS.lang )>
<cfset APPLICATION.langStrings[ ARGUMENTS.lang ] = StructNew()>
</cfif>
<cfif NOT StructKeyExists( APPLICATION.langStrings[ ARGUMENTS.lang ], ref )>
<cfset ARGUMENTS.updateNow = true>
</cfif>
<cfif ARGUMENTS.updateNow>
<!--- Check to see if the value is in the database --->
<cfquery name="getLangString" datasource="teamworkpm_master">
SELECT languageString
FROM languagestrings
WHERE languageStringRef = <cfqueryparam value="#ref#" cfsqltype="CF_SQL_VARCHAR">
AND languageCode = <cfqueryparam value="#ARGUMENTS.lang#" cfsqltype="CF_SQL_VARCHAR">
</cfquery>
<cfif getLangString.recordCount>
<cfset APPLICATION.langStrings[ ARGUMENTS.lang ][ ref ] = getLangString.languageString>
<cfquery name="updateLangStringCount" datasource="teamworkpm_master">
UPDATE languagestrings
SET languageStringUsageCount = languageStringUsageCount + 1
WHERE languageStringRef = <cfqueryparam value="#Left(ref,80)#" cfsqltype="CF_SQL_VARCHAR">
AND languageCode = <cfqueryparam value="#ARGUMENTS.lang#" cfsqltype="CF_SQL_VARCHAR">
</cfquery>
<cfelse>
<!--- Store the value in the field --->
<cfset APPLICATION.langStrings[ ARGUMENTS.lang ][ ref ] = ARGUMENTS.englishDefault>
<!--- Insert the default english value into the database --->
<cfquery name="insLangString" datasource="teamworkpm_master">
INSERT INTO languagestrings( languageStringRef, languageCode,
languageString, languageStringUsageCount )
VALUES( <cfqueryparam value="#ARGUMENTS.ref#" cfsqltype="CF_SQL_VARCHAR">,
<cfqueryparam value="#ARGUMENTS.lang#" cfsqltype="CF_SQL_VARCHAR">,
<cfqueryparam value="#ARGUMENTS.englishDefault#" cfsqltype="CF_SQL_VARCHAR">, 1 )
ON DUPLICATE KEY UPDATE languageString =
<cfqueryparam value="#ARGUMENTS.englishDefault#" cfsqltype="CF_SQL_VARCHAR">
</cfquery>
</cfif>
</cfif>
<cfreturn TRIM(APPLICATION.langStrings[ ARGUMENTS.lang ][ ref ])>
</cffunction>
You can see that if the string has already been looked up, the result is returned immediately so that this function is nice and fast.
If a string doesn't exist for a given language, the default english version is inserted for that language, so that it can be translated later.
The languageStringUsageCount is updated whenever a string has to be looked up, usually after a server reboot. This will be useful when we want to strip the database of strings that are no longer used in a few months.
The "update now" argument allows programmers to quickly change the value of english strings directly from the code base during development.
Hopefully this will help you get your own multi-language systems up and running in ColdFusion.
Tags:
Comments [10]
The Googlebot is retarded.
.
Adam, our graphic designer has put together the temporary showcase website design for Digital Crew and its not too bad at all for a temporary site. I'm looking forward to putting it up. Here is a tiny preview.
I've spent the last 2 weeks working on the website for the new National Maritime College of Ireland (NMCI). It was a bit of a struggle to meet a tight deadline for their launch but we did it.
One neat feature is this ability for the user to type [include:subnav] in the WYSIWYG and the website 
Peter Coppinger aka Topper is a neurotic web monster who spends most of his chaotic life developing ColdFusion web applications when not drinking himself into a stupor and scheming his plans for world dominance.
