cftopper.com

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:



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.

I'm going to tell you a secret, but don't tell anyone. Shhhhsss.
About 3 weeks ago on a quiet Tuesday evening I installed the latest version of subversion client TortoiseSVN on one of our website hosting servers in Dublin - I had to reboot the server after the install. This is when it all went wrong...

I usually wait maybe 2 minutes and the server is backup serving a plethora of client websites and a few small web apps. I waited and waited, repeatedly pinging the server, searching for the first sign of life.

The server was apparently lifeless. Nada. Nothing. Zip. "No reply from server"...

We called our hosting company. No I lied ... we didn't. We actually had to use their brutal support chat program and bludgeon English into a pigeon-English speaking Polish guy to request that the server be manually restarted.

I guessed that I had hit the "shutdown" option instead of the "restart" option and that the technicians would reboot the server in a jiffy and that all would be well.. so I tottered off home.

Some time later I got a frantic phone call from Dan informing me that the technicians said the server's hard drive was fried. I turned visibly white and raced back to the office for damage assessment.

Hours later, the technicians in Dublin had managed to salvage some data by plugging the hard drive into another PC and running a Linux program to do a sector by sector copy where possible. We recovered a lot of data but we had still lost a fair amount. Luckily we had most, but not all, of the website databases backed up to a PC in my office thanks to Navicat and scheduled tasks.

The technicians reinstalled a blank Windows 2003 server installation along with the data they managed to recover and Dan and I spent the entire night working feverishly to get all the websites and applications working before our clients got to work and started screaming.

In the end, the only thing we lost was a very small website. We recovered the page content from Google page cache (isn't Google great) and then gave the client a new, much better website for free to make up for the down time which was 1 day while Dan knocked the new site out.

And here's my point: disasters will happen, so plan for them.

Our New Backup Plan

We have since installed Carbonite Backup on all our servers. Everywhere. It's an absolutely bargain for only $30 a year per server for unlimited data. I thought there must be a catch at first, but no, it's a steal, easy-to-setup and works great.

That's the files sorted. But what about the databases. Well, we use MySQL on all our servers (if you use anything else you are a stupid Microsoft whore or Oracle slut, sorry) and it's difficult to restore MySQL from just the raw data files. I wanted something that could backup an entire server of MySQL databases automatically including new databases without any additional configuration - the reason we lost that one website was because each website had to be manually configured with our old database backup system and we had missed a few.

Eventually, after a serous amount of poking about ye olde internet, I found exactly what I was looking for in Auto Backup for MySQL from Swordsky SoftWare. This is the only program I could find that would run as a service and backup multiple entire servers reliably without needing additional configuration as each new website is added.

It also has a thoughtful array of options such as automatic removal of old backups after so-many days - something that I used to have to do manually in the past. The SwordSky team, whoever they are, have done a fantastic job here and I encourage you to give it a go. It's a bargain at only $100 or so too.

So there you have it, two cheap, easy-to-setup products that may one day save your ass.