Every now and then, a single CMS post may vanish or a forum user's posts are accidentally deleted. No worries, right? That's why we make backups. If you have an active site with lots of users posting content, loading a backup is probably not practical. So, load up the latest incremental backup and select the records you need, and do it easily with MySQL's SELECT INTO OUTFILE and LOAD DATA INFILE.

As the MySQL documentation says, these commands are meant to work in pairs. Here's a simple example of restoring all fields for a specific collection of records.

SELECT * INTO OUTFILE '/tmp/data-out.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM my_table
  WHERE id = N;

Move the CSV file to your production server and load it. Be sure to specify which fields you selected.

LOAD DATA INFILE '/tmp/data-out.csv' INTO TABLE my_table 
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  (field1, field2, field3, field4);

Each of these commands is capable of more. See the MySQL docs for complete details.

I'm beginning to build a Drupal-powered web site for a customer. A goal for this project is to provide translation of pages, events, and announcements for Spanish-speaking site visitors. Although Google's Translation service is great, automated translation tools remain less than perfect. Fortunately, the customer has several dedicated bilingual volunteers who will translate as much of the site's content as possible.

In this post, I'll walk you through the steps to configure a Drupal 7 site so that it may be translated to other languages.

It's Not Just Translation

If you're new to building multilingual web sites, I recommend reading the Wikipedia entries on internationalization and localization. Building a multilingual web site involves much more than translating content from one language to another. Translation must be done with an awareness of a language's idioms as spoken within a geographic region. If you're a native English speaker in the United States you probably know that "a lift" means something quite different to the English. Idiomatic differences also exist in Spanish spoken in Mexico, Latin America, South America, and, of course, Spain. Localizing translations is one of the biggest challenges for machine translation and the primary reason we chose to manually translate our content rather than slapping the Google Translation Widget on our pages.

Localization, Translation, and Drupal

Localizing and translating a web site is a complicated task, but Drupal does a remarkably good job of simplifying the process. If you're interested in the technical details and decisions that went into how Drupal supports internationalization, have a look at Gabor Hojtsy's series, "Drupal 7's new multilingual systems compilation."

Karen Stevenson wrote a wonderful article, "Localized and Multi-Lingual Content in Drupal 7", which summarizes Gabor's series. In her article, Karen discusses the two site components requiring translation, the interface and the site's content. Text in Drupal's interface, including the login form and navigation menu, has already been translated into many of the world's languages which saves you, I, and the rest of the Drupal community a ton of time.

Karen also breaks down the two content translation options available in Drupal 7, full-node translation and entity translation, which is new in Drupal 7. After some consideration, I decided that entity translation was the best choice for the site I'm building. So, with this introductory information in mind, here are the steps I used to configure multilingual support in a Drupal 7 site.

1.0 Define Languages and Translate the Interface

The foundation for multilingual support in Drupal 7 is the Locale module which ships with the default distribution. Enable locale, then define the languages your site will support. In this article, I'll be using Drush to install and enable modules. If you're new to Drush, have a look at my introductory article, or download and enable modules through Drupal's administration interface.

1.1 Enable the Locale Module, Add Languages, Configure Language Switching

drush @my-site pm-enable locale

With locale enabled, you can now add the languages your site will support.

1. Go to Administration > Configuration > Languages (under "Regional and Language")
2. Click "Add language"
3. Select a predefined language, in my case, Spanish (Español)
4. Click "Add Language"

Add as many languages as you need. You can also add custom language translations here.  

  

After adding languages, you'll need to configure how users' language preference are detected and how they'll switch between languages.

1. Select the "Detection and Selection" tab (Administration > Configuration > Languages)
2. Check one or more methods to detect the language to display (I enabled all, although I won't use all initially)
3. Save the settings

You can rearrange the priority of detection methods, set which part of the URL to use in detection (es.mysite.com vs. mysite.com/es/), and set a request/session variable name to use to switch languages.

1.2 Load an Interface Language Translation

If you're localizing for one of the predefined languages listed on the Add Language form, most, if not all, interface translation work has already been done for you. Most of the core Drupal interface, including default menus and blocks, has already been translated by the Drupal community. All you need to do is download a translation file and load it.

1. Visit localize.drupal.org and download an available interface translation for Drupal 7.
2. Go to Administration > Configuration > Translate Interface.
3. Select the "Import" tab.
4. Choose the language file you downloaded (in my case, drupal-7.9.es.po).
5. Be sure the "Import into" menu selection corresponds to the imported file.
6. Choose whether to replace existing string translations, or just import new ones. Yes, this means that if a translated string isn't appropriate for your site, you can override imported values. Just be careful not to overwrite your changes when updating translations.
7. Click Import.

Once complete, you'll be shown an import results showing how many strings were updated and added. You'll also see the percentage of the translation complete for that language. Since it's one of the most spoken languages on the planet, Spanish is a well maintained Drupal interface translation which is typically at 100% complete.

You may now visit your site and append the language code (http://mysite.com/es) to the URL to see the fruits of your labor, so far. Next, let's enable content translation.

2.0 Enable Content Translation

As Karen points out in her article, content translation in Drupal 7 can now be configured at the node or field levels. She notes the debate among Drupal developers about the merits of each method. For this site, since I'm new to developing multilingual sites, I've decided to follow Karen's recommendation and use the newer field translation model.

Since the translation debate was not settled before Drupal 7 shipped, field translation is not part of core. I'll need to install several contributed modules to enable field translation.

• Entity Translation - Enables translation of node fields, with the exception of titles.
• Title - Enables translation of node titles.
• Entity - Title module dependency.

Here are the Drush commands used to download and install these.

drush @my-site dl entity_translation title entity 

drush @my-site en entity_translation title entity

After installation, define which content types (Blog posts, Basic Pages, Articles, etc.) can be translated.

1. Go to Administration > Structure > Content Types
2. Edit an existing content type, i.e. "Basic page"
3. Select the "Publishing options" tab
4. Choose "Enabled, with entity translation"
5. Save the changes

Next, convert the Title field to a regular field using the Title module, and set which fields are translatable.

1. Go to Administration > Structure > Content types
2. Select "manage fields" for the content type you enabled translation for, i.e. "Basic page"
3. Click "replace" for the Title field
4. Check "Replace title with a field instance" and save
5. Select the "Manage Display" tab
6. Change the Title field's Label and Format to <Hidden> and Save
7. Select the "Manage Fields" tab
8. Edit the Body field
9. Select the "Field Settings" tab
10. Check "Users may translate this field" and save the field settings
11. Repeat steps 8 through 10 for other translatable fields

Now give it a try. You'll now see a Translation tab next to the standard View and Edit node tabs.

On this tab you'll now see an "add translation" link next to each language you've enabled. Clicking this will display a slightly altered add/edit node form to create a translated version of the original. The translation form will contain the original languages values for reference during translation.

The translation form provides controls to set published state, whether the translation requires an update, and a translated URL alias.

After saving the translation, click on it's title in the translation list to view it. We're almost there. We still need to translate the site's title, slogan, and main menu. We also need to provide a way for visitors to switch languages.

3.0 Switching Between Languages

Next, let's provide a language switcher for users. The Locale module provides a simple block with enabled languages listed. If your site's translated to several languages, this list will take up a lot of real estate. The Language Dropdown module converts the language switcher list to an attractive dropdown menu.

drush @my-site dl lang_dropdown

drush @my-site en lang_dropdown

Next, enable the language switcher block.

1. Visit Administration > Structure > Blocks
2. Enable and place Language switcher dropdown (user interface text)

You should now see a fully-functional language switcher menu like this. If you'd like, you can also download and install the Language Icons module which will add flags for each language in the menu.

4.0 Menu Item, Block, Variable, and Contact Form Translation

What about translating menu items and blocks? Let's not forget about the site's name, slogan, and contact form. To tackle these tasks, you'll need the i18n module and the slew of module's it, and it's sub-modules, depend upon.

Install i18n, Translate Menu Items

1. Download i18n
   

   drush @my-site dl i18n
   

2. Enable i18n_menu, which will also enable several additional required modules.
   

   drush @my-site en i18n_menu
   
   

   The following extensions will be enabled: i18n_menu, i18n_translation, i18n_string, i18n_block, variable, i18n
   Do you really want to continue? (y/n): y
   i18n_translation was enabled successfully.            [ok]
   i18n_menu was enabled successfully.                   [ok]
   i18n_string was enabled successfully.                 [ok]
   i18n_block was enabled successfully.                  [ok]
   variable was enabled successfully.                    [ok]
   i18n was enabled successfully.                        [ok]
   Refreshed 16 strings for the enabled modules.         [status]
   

3. Go to Administration > Structure > Menus
4. Click "edit menu" for the Main menu
5. Under "Multilingual options", set the Translation mode to "Translate and Localize"
6. Save the menu
7. Edit a menu item from the "List Links" tab
8. Select the "Translate" tab
9. Translate the item and save

Repeat for each menu item.

Note that translation of the Home tab requires that it's default language be changed to English. Once this is done, a translation for the Home tab may be added. You'll see both "Home" and the translated version of Home on menu lists, but only the tab appropriate for a selected language will be displayed to end-users.

Translate Site Variables

The Internationalization module, along with the Variables module, provide support for translating site variable values. These include, but are not limited to, site name, slogan

1. Enable the i18n Variable Translation module and it's required modules, Variable store and Variable realm
2. Visit Administration > Configuration > Multilingual Settings
3. Click on the "Variables" tab
4. Select variable to be translated
5. Save

This list also includes the RSS feed description, user-related email messages (welcome, password reset, etc.).

Now, to translate the site's name and slogan.

1. Go to Administration > Configuration > Site Information
2. Select the language to translate variable values to
3. Translate variables
4. Save

Now you'll see the translated site name and slogan in your banner!

Enable Block Translation

When i18n_menu was enabled above, i18n_block was automatically enabled which, as the name suggests, provides support for block translation. Just create or edit a block and set which languages it can translated to.

You may receive the following error when attempting to translate blocks:

"The string blocks:block:1:body for textgroup blocks is not allowed for translation because of its text format."

No worries, you just need to set which text formats are translatable.

1. Visit Administration > Multilingual settings
2. Select the "Strings" tab
3. Select the Translatable text formats to allow, i.e. Filtered HTML, Full HTML, Plain text

Translate Contact Form Categories and Auto-Reply Messages

This is another relatively straight forward process. Enabling the Contact Translation module allows for the translation of Contact form categories and auto-response messages.

1. Enable the Contact and Contact Translation modules
2. Go to Administration > Structure > Contact Form
3. Edit or Add a category
4. Click the "Translate" tab
5. Translate the Category and Auto-reply values

After adding a few categories and translating them, your contact form should look something like this.

You'll probably want to create a language-specific URL path for special pages, like the contact page (Administration > Configuration > URL aliases).

6.0 Set Date Formats

Don't forget to set locale-appropriate date formats. IBM provides a nice locale quick reference which includes various locales' date formats.

I'll set the following date formats for es-MX at Administration > Configuration > Date and Time.

• Short: 28/11/2011
• Medium: 28 de noviembre de 2011
• Long: lunes 28 de noviembre de 2011

And here's how the date displays for articles and blog posts.

Additional Considerations

There are a few things I haven't touched upon, at least not yet. The Internationalization module makes it relatively easy to tackle these additional translation tasks.

• If you set a node as your site's homepage, you'll need to provide translated nodes for each languages homepage.
• If you've enabled node comments, you'll need to consider how you want to display comments made in various languages. Have a look at the Filter comments per language section of the Comment settings tab.
• Taxonomy
• Views, Internationalization Views
• URLs without accents, Transliteration
• Tracking and managing your site's translation is a lot easier is you can get a bird's eye view.
• i18n update
• l10n_install

The Drupal article on Basic Internationalization Setup provides additional detail and I highly recommend that everyone review it. You may want to also review the i18n module's documentation.

Drupal is a great foundation for so many types of web sites. The Drupal community continuously improves its core and the growing list of add-on modules to extend functionality and improve security. If you manage several Drupal sites, all of this extending and improving can make keeping Drupal up-to-date a time-consuming task. Here are a few notes about how I maintain my Drupal and a few shell scripts I've created to streamline the process.

Drupal Multisite and Subversion

I use Drupal's multisite feature and Subversion to create and manage almost every site I develop.

Here's the basic process:

1. Download core and module updates tarballs
2. Unpack the update over a local SVN working copy
3. Test and commit the updates locally
4. Run SVN update for the remote production copy

About a year ago, I wrote a few shell scripts to streamline this process for Drupal 6 and recently added support for Drupal 7.

Drupal Core and Module Update Scripts

Before using the update Drupal core script, be sure that you've set your Subversion repository to ignore local files that you don't want to add (i.e. settings.php, symlinks, etc.). If you don't, the script may add and commit files you don't want in your repo. With that out of the way, here are the scripts and a what they do.

drupal_update.sh does the following:

1. Moves the core update package to tmp and unpacks it.
2. Removes the .htaccess and sites/example.sites.php files.
3. Zips the modified core package and unpacks over the local install.
4. SVN adds any new files.
5. Pauses and prompts you to run update.php locally and test.
6. Checks in the modified and new files upon confirmation.
7. Prompts to run SVN update against the remote installation.

drupal_module_update.sh does the following:

1. Moves the module update package to either sites/all/modules or sites/yourdomain.com/modules and unpacks it.
2. SVN adds any new files.
3. Pauses and prompts you to run update.php locally and test.
4. Checks in modified and new files upon confirmation. 
5. Prompts to run SVN update against the remote installation.

I still have several features I'll be adding, including the use of drush to run database updates (update.php) and the removal of deprecated files. See the Roadmap for a full to do list.

Get the scripts, Configure 'em, and Use

1. Clone the Git repo or Download (tar.gz or zip)
2. Remove the .default extension froma drupal_info.sh
3. Edit drupal_info.sh, add local and remote path information for your Drupal 6 and 7 instance. Add SSH server and username for remote server.
4. Download Drupal core/module updates to $DownloadsPath, the following to update a Drupal core:
   

   % drupal_update.sh

   or, update a shared or site-specific module with:
   

   % drupal_module_update.sh
   

Roadmap

• Delete deprecated package files from SVN via svn remove.
• Provide drush integration to initiate update.php, etc.
• Cleanup and release a "create new multisite instance" script.
• Add theme update/install support.

Change Log

2011-02-11 version 0.8

• Added support for Drupal 7.
• Auto-detect version.
• Automatically add new files for Drupal core updates.
• Fix include of drupal_info.sh to allow aliased script execution to work.

Thanks to @andrew_k for posting an awesomely simple curl command to dump bookmarks for a del.icio.us account.

My wife and I both use del.icio.us. The following is a simple shell script I'm running periodically during the day to keep a backup of our bookmarks. I'll keep running this up until Yahoo! pulls the plug or until I find a suitable alternative.

If you need to dump a single account, just hardcode your login and password into the curl URL and run the command as crontab entry directly.

 

#!/bin/bash
#
# Script: backup_delicious.sh
# Description: Backup delicious bookmarks to an XML file
# Location: /usr/local/bin
# Author: Chad Kieffer, ckieffer at gmail dot com
#
# Sample cron entry:
# 0 9,12,15,18,21 * * * '/usr/local/bin/backup_delicious.sh'
USER=(
  'user1'
  'user2'
)
PASSWORD=(
  'user1password'
  'user2password'
)
for (( i = 0 ; i < ${#USER[@]} ; i++ ))
  do
    /usr/bin/curl https://${USER[$i]}:${PASSWORD[$i]}@api.del.icio.us/v1/posts/all > /path/to/backup/directory/bookmarks_${USER[$i]}.xml
done
exit 0

Ever since giving up last year and jumping into the social network fray I've been looking for a good status update viewer and aggregator. I'm trying to keep up with updates from a growing list of family, friends, and peers on Twitter, Facebook, and occasionally Flickr. I'm using Ping.fm to cross post status updates. I've tried the dedicated clients and plugins, but none worked for me quite as well as EventBox.

The Twitterfox and Facebook Firefox plugins work well enough but I'd like a more unified interface. Twitterfox can only show a limited number of updates after not checking updates for a day.

I tried the Adobe Air applications, including Twhirl and Tweetdeck . These are both great dedicated Twitter clients. The multipane approach of Tweetdeck and ability to group those you follow into panes is powerful. Both provide Growl-like notification panes. My biggest beefs with these clients are their inability to up font size and Air application's use of the system's default HTML editor to view posted URLs rather than the system's default browser. When I click on posted links, they open in Textmate rather than Firefox.

AlertThingy is another Adobe Air client with a multipane UI similar to Tweetdeck. AlertThingy is a bit more appealing because in addition to Twitter updates, it can also display your Facebook, Flickr, and updates from several other services. Currently, however, AlertThingy doesn't clearly show read vs. unread items and it doesn't seem to reliably retain changes in settings from session to session.

I finally decided to purchase EventBox after giving it a try. EventBox is an OS X-only client that supports display of Facebook, Twitter, Flickr, and Reddit status, comment, and photo updates. It also includes a generic RSS reader plus support for Google Reader. All of this plus the promise of additional service support convinced me to shell out $15 for a license.

EventBox isn't perfect, but it provides more of what I'm looking for with many of the benefits that come with an OS X native application. The interface is clean and intuitive and provides service-specific views as well as an aggregated unread and recent item  views. Unread updates are faded and appear clearly different from unread items. I can control update intervals for each service and turn off new item alerts when I need to tune out distractions and get things done.

EventBox also provides an Apple Menu item with an unread count and a summary list of recent updates. Photo viewing for Facebook and Flickr is nice and you can even upload images and edit image metadata directly in EventBox.

EventBox doesn't provide font size controls but overall, this is the only real issue I have with it. I hope that they add support for Ping.fm, LinkedIn, and Delicious. I also hope that thet add support for the Newsgator feed reader, although I might decide to switch to Google Reader. Sync of unread items between multiple installs would be nice to have too. Oh, and they'd really create a stir with an iPhone app!

I'm looking forward to seeing where The Cosmic Machine takes EventBox next. Download the free two week trial and see what you think.