Latest Post
DrupalCon San Francisco: sponsorship and session proposals
In case you haven’t heard, DrupalCon San Francisco is fast approaching. The conference, from April 19th through the 21st, promises to be the largest Drupal gathering of its kind, and we’re excited to be able to sponsor and attend.We also have several session proposal that are currently open for voting. Here is a quick overview of the sessions our team members have proposed:
Drupal for Learning: A Case Study
Presented by Justin Toupin, Brad Bowman and Jon Clark
Track: Under the HoodThis January, we launched a Drupal-based redesign of Poynter’s News University, the world’s leading journalism education website. In this case study, we’ll cover the design and technical challenges we faced, as outlined in Justin’s recent post. We’ll also touch on what we’re pushing forward in new online learning projects.
You should vote for the Drupal for Learning: A Case Study session here.
Enhancing Drupal Interfaces Javascript and Flash
Presented by Scott Reynen and Ken Woodworth
Track: Design, Theme, and UsabilitySimple well-stuctured Drupal markup is accessible on nearly all existing web browsing devices, and very likely devices that don’t even exist yet. JavaScript and Flash, on the other hand, can provide much more compelling user interfaces. We will discuss using the best of both worlds in Drupal, how to get all the interface niceties of JavaScript and Flash without sacrificing the broad accessibility of HTML.
We’ll take a look at two specific case studies of Drupal sites with interfaces that work well without JavaScript or Flash, but work even better with both: the United Nations Development Programme’s adaptationlearning.net and Richardson Sports, a leading sports apparel manufacturer.
You should vote for the Enhancing Drupal Interfaces Javascript and Flash session here.
Efficient Workflow for Design and Development Projects
Presented by Justin Toupin and Jon Clark
Track: Providing Professional Drupal ServicesWe’ll take a look at how we moves Drupal projects from concept, to design, to production, using Drupal as the technical backbone for much of the process. We’ll discuss how we communicate with clients and manage expectations. We’ll also touch on lessons learned and things we do differently after 10 years of doing business.
You should vote for the Efficient Workflow for Design and Development Projects session here.
Defining and Pitching Your Process
Presented by Jon Clark
Track: Providing Professional Drupal ServicesEffectively communicating how you work is a powerful way to convince clients to choose you. Before they make a decision, potential clients want to know what working with you will be like, and need to be confident that it will not only be effective, but that they’ll be comfortable in the process. This session will offer insights into thinking through your own work process, how to communicate that to decision makers, and the key role this will play in setting client expectations for the project.
You should vote for the Defining and Pitching Your Process session here.
Best Practices for Managing a Drupal Firm
Panel members: Glenn Hilton, Dave Terry, Jeff Walpole, Jennifer Hols, Eric Gundersen, Ben Finklea, Liza Kindred, Jon Clark
Track: Providing Professional Drupal ServicesIn this panel discussion, we will look beyond the technology side of Drupal and dig into the business end of what it takes to run a successful Drupal agency. We’ll touch on critical subject matter topics in areas like sales, recruiting, accounting, human resources, and how to effectively market your services. The panel members represent Drupal shops that include ImageX, Mediacurrent Media, Phase2 Technology, Development Seed, Volacci and Lullabot.
You should vote for the Best Practices for Managing a Drupal Firm panel discussion here.
Recent Posts
IE6Funeral.com
Yesterday we announced a local party celebrating Google’s soon-to-be discontinued support for IE6. Now, thousands of Twitter posts later, with exposure on a few really big websites, we’ve obviously struck a chord (and maybe in some cases, a nerve) with internet lovers everywhere.
The Premise
Needless to say, IE6Funeral.com received more attention than anticipated. If you haven’t seen it already, check out Twitter, Techcrunch, or The Washington Post. The premise is simple: Google will soon be discontinuing support for IE6 on at least some of its online services. We should commemorate the occasion.
The Funeral
In the days ahead, we’ll be working to address the overwhelming response to the
partyfuneral invitation. What we know so far is there will definitely be a casket, a body, and from all appearances, mourners a-plenty.The Reality
Back in reality, we still develop websites that support IE6, along with every other browser. The extent to which we focus on IE6 is assessed on a project-by-project basis, and grounded in research on the relevant audiences and content.
While Google discontinuing support for IE6 has very little impact on the way we work, it does mark an important step in the evolution of the interwebs. It’s also a great excuse to get together with our local web design community.
In lieu of attending the party, please send flowers. And at the very least, please leave your remembrance.
Drupal for Journalism Educators: Poynter's News University Goes Live
Last week, Poynter’s NewsU.org, the world’s leading journalism education website, went live with a complete redesign. The website was designed and developed by Aten and built entirely in Drupal. It is the result of almost a year of strategy, design, and development, in collaboration with the incredible team of web producers and content experts at NewsU.
It’s an exciting step forward for NewsU, and an exciting case study in using Drupal as a learning platform. The website features hundreds of courses spread across several course types, each with its own needs for design, behavior, reporting, and 3rd-party integration.
A few of challenges we tackled with NewsU included:
- Creating an effective UI for allowing NewsU to promote — and end users to find — courses within an extensive catalog of almost two hundred training modules.
- More clearly demonstrating the relationship between NewsU and its parent organization, the Poynter Institute.
- Managing permissions and enrollment across hundreds of courses for more than 130,000 users.
- Importing massive data sets into Drupal from legacy sources including MySQL and SQL Server.
- Integrating with SCORM learning modules, Adobe Connect, and other 3rd-party services.
- Developing and implementing design and technical standards for creating (as well as retro-fitting) courses across the entire website.
We’re extremely pleased with the outcome of the process. It is rewarding to work with organizations that have such relevance in the current world climate. As journalism continues to evolve and adapt in the age of the Internet, the need for quality, relevant training will need to adapt with it. So far, Drupal has proved a capable platform for handling the unique needs of an online learning experience. We’re looking forward to helping expand and refine both NewsU and Drupal as a learning platform.
Check out the redesign at NewsU.org.
Look out for an upcoming case study on the design and development process, as well as a soon-to-be announced session proposal for DrupalCon San Francisco. You can learn a bit more about the redesign process at NewsU Next, see what people are saying on Twitter, or ready the official press release.
Progressive Enhancement in Practice
It's one thing to talk about the importance of progressive enhancement. But how does it actually work in practice? Let's take a look at a specific example from a recently launched site, the Adaptation Learning Mechanism.
One of the key functions of this site is to find content by geographic location. The content is related to locations primarily with the location module. A hierarchical taxonomy also places content in broader regions, associated with location's broadest specificity, country, via taxonomy synonyms and a little nodeapi glue.
Views exposes all of this as filters on a list of content and we have a functional search by location.
Two things to note about this: 1) it works and 2) it could be improved. That's exactly how an HTML-only interface should be. People who aren't using JavaScript don't expect a JavaScript-like experience, but they do expect the site to work. So we're in good shape, but there's still a lot of room for improvement here. Both the region and the country lists are rather long, so it can be difficult to find the right place. Also, it's possible to enter impossible locations, e.g. a country of Cuba and a region of Western Asia. Finally, it would be nice if we could see the search results without a full page refresh.
We can improve all of this with JavaScript. For the first issue, we add a text field and set it to autocomplete with region and country names. Whenever a country or region name is typed, the corresponding
optionis automatically selected. Though they're still driving the search results, we hide both the region and countryselects, because we've replaced them with an easier method of accomplishing the same thing.Alas, we've actually removed a bit of functionality here; though auto-completes are better than
selects for searching, they're actually worse for browsing. So we improve the browse-ability with a link to a page with a browse-able list of regions and countries.For the second issue, we update the region
selectwhenever the countryselectchanges, selecting the associated region. So if you have Western Asia selected and you choose Cuba, it will change Western Asia to Caribbean. The last issue, speeding up the response time, is actually the easiest to address. We simply turn on AJAX in Views.
We've now enhanced the experience with JavaScript, but it could still be even better. People don't always think of locations as names; we often think of them as points on a map. Though Morocco is next to Egypt in an alphabetic list of countries in Northern Africa, the two are spatially very far apart. Flash would be useful here. After checking for Flash availability, we replace the link to browse locations with a Flash map for doing that. When areas of the map are selected, we select the same areas on the hidden
selects. And from there, it works just as if someone had used theselects directly.We also change the auto-complete field to update the Flash map in addition to the
selects, so the map always reflects the actual location selection.
That's all easier said than done, of course, but that's the basic approach.
- HTML-only: works
- HTML+JavaScript: works better
- HTML+JavaScript+Flash: works best
The key step here is incredibly simple: HTML-only works. That's not hard to do, but it requires a consistent focus. It's really easy to start thinking about the really cool Flash map and completely forget about the HTML-only alternative. So this is a friendly reminder: don't do that.
Data Imports with Migrate and Table Wizard
Many of the Drupal projects we’re working on currently have large imports of legacy data. The specific subject of this post is a user import I’ve recently completed for NewsU.org, which is a project of the Poynter Institute and funded by the Knight Foundation. In this post I’ll be talking about the process I used to migrate their 110,000+ users from SQL Server to Drupal, but many of the concepts apply regardless of the source database and the type of content you are dealing with. If you’d like to read the less nerdy bits about the NewsU project, you can visit the project blog at next.newsu.org.
The Process
Let me begin by talking a bit about the process I’ve been using. My plan of attack has several steps:
- Load database backup into a SQL server instance
- For this I’ve been using Amazon EC2 instances (thanks to greggles suggestion). Many of the database backups I’ve been working with exceed the 2 gigabyte limit present in the developer version of SQL server. Left with a choice, we’ve been paying $1.50 an hour to use an appropriate Windows server with SQL Server 2008 on the EC2 platform, rather than spending thousands of dollars on a SQL Server license.
- Use the MySQL Migration Toolkit to create a MySQL schema and retrieve the data from SQL Server.
- The MySQL Migration Toolkit will do a good bit of the heavy lifting involved when mapping your SQL Server schema to something appropriate for MySQL, but it will often still take a lot of manual massaging of the create table statements it provides to get something that will work for you.
- Get the data into your Drupal database.
- This is fairly straightforward. The result of the MySQL Migration Toolkit step is typically two MySQL import files. One of these is for the table structure, and one is for the data. Import them as you would any other MySQL database dump.
- Field mappings and import process.
- This is the most involved of all steps. Use the Migrate + Table Wizard modules to Views-enable the required tables you have imported, perform field mappings with the migrate module, then import the data.
The Export
In the interest of time, I’m going to skip over the process of spawning a EC2 server with SQL Server and restoring a backup on it. Instead, I’m going to start at the point at which I already have SQL Server set up with the source database loaded. Once that is complete I can load up the MySQL Migration Toolkit and get to work.
The first step is to input the database credentials for my source database …
… and target database.
Once it has connection credentials the Migration Toolkit will examine what tables and stored procedures are available to import.
In this case, because I only need a snapshot of this data to perform an import from, I’m only interested in the table data. I’m not interested in maintaining data integrity over time, or doing any of the dozens of other things the stored procedures might do. I just need the data exactly how it exists at this particular moment in time to base this import on. After selecting which tables I want to import the Migration Toolkit will automatically take a shot at making an applicable MySQL create table statement.
I also have the opportunity to run the create table statements against my target database so I immediately spot any errors in what is automatically generated by the Migration Toolkit. This typically takes a couple rounds of trial and error to get something that will work. Once I have something that works, I can proceed to exporting the data. There’s not a whole lot to this, except to let the Migration Toolkit crunch away for a while, selecting data from SQL Server and transforming it into MySQL insert statements.
From there, I perform an import of the database dump into MySQL (or, if you transferred the objects online rather than saved database exports in the previous steps, it will already be there).
The Import
At this point I can start taking advantage of the tools which Migrate and Table Wizard provide. I’ll start by providing some background information on the modules, and what each is used for.
- Table Wizard
- The table wizard module provides an interface for exposing any table in your database to Views. Furthermore, you can define relationships between tables by tagging various columns as foreign keys. Although in this case I’m only using it to expose the data to Views for data import purposes, it has a wide range of use outside of data migrations. If you haven’t tried it yet, go get it. Now. Really.
- Migrate
- Migrate provides a mechanism for mapping Views data to Drupal objects. Once you have whatever data you are importing exposed to Views, Migrate will allow you to map data in the Views fields to various properties on the object. If that sounds a bit abstract, it’s because it is. Out of the box it provides support for importing data to users, content types, taxonomies, profiles and more. Furthermore it provides hooks for you to declare your own destination objects.
The next step is to Views-enable the table I’d like to import from. To do that I’ll navigate to the Table Wizard section under Administer -> Content and select the table I’d like to expose to Views.
Once this is done, I can go in and view the range of values represented by the data, comment on the purpose of various fields, or use Views to display the content of the table.
Now is where the real fun begins. Now that I’ve imported my data to MySQL, and used Table Wizard to Views-enable it, I can leverage the migrate module to map values in the various database columns to my Drupal users. First, a bit of background on that. For profiles on this site, we needed a range of field types which weren’t provided by the core profile module. For this reason we decided to use content profile. The catch is that from a Drupal perspective, both profile and user information were combined in the legacy database. I need to be able to simultaneously map values from the legacy database to both Drupal users and content profile fields. Luckily the migration module has built-in support for the content profile module and automatically adds available mappings from profile nodes into the user import. Migrate will also be smart enough to map and save data for the Drupal user before anything happens on the profile node, ensuring a Drupal uid is in place and the profile node can be correctly associated with the Drupal user. With this in mind I can head over to Admin -> Content -> Migrate and create a new migration set.
The migration set will allow me to map column values from my views into the appropriate Drupal fields. It should be noted that anything being mapped here is treated as a raw value, without any additional processing done to it. Typically if I need to do any additional processing, like transforming a SQL Date field into a Unix timestamp, I’ll just leave it out of the mapping here and process it in the hooks which migrate provides.
If I have data which needs additional processing in some form, hope is not lost. Migrate exposes both the Drupal objects I’m working with as well as the Views data via hooks, so I can perform any additional processing I need. Here’s a toned down example of what I’m using for user profiles. In this case I’m pulling data from a handful of tables with many to one relationships with the legacy user data, as well as preserving legacy course enrollment data (which are organic groups in the new Drupal site).
<?php /** * Implementation of hook_migrate_prepare_node(). * * Provides customizations for the user profile node before it gets sent off for saving. */ function newsu_user_import_migrate_prepare_node(&$node, $tblinfo, $row) { // …. $node->taxonomy = array(); // Determine user 'role'. $roles = db_query(" SELECT t.tid FROM {tableRole} tr INNER JOIN {tablelLinkRoleToUser} l ON l.intRoleID_fk = tr.autRoleID INNER JOIN {term_data} t ON t.name = tr.strRole WHERE l.intUserID_fk = %d", $row->autUserID ); while ($role = db_fetch_object($roles)) { $node->taxonomy[] = $role->tid; } // Determine user 'medium'. $mediums = db_query(" SELECT t.tid FROM {tableMedium} tr INNER JOIN {tableLinkkMediumToUser} l ON l.intMediumID_fk = tr.autMediumID INNER JOIN {term_data} t ON t.name = tr.strMedium WHERE l.intUserID_fk = %d", $row->autUserID ); while ($medium = db_fetch_object($mediums)) { $node->taxonomy[] = $medium->tid; } // Add in course enrollment. $user_courses = db_query("SELECT * from {LEGACY_COURSE_ROSTER} cr WHERE cr.USER_ID = '%s'", $row->userLegacy_strUsername); while ($user_course = db_fetch_object($user_courses)) { if ($course_nid = newsu_user_import_get_course_nid($user_course->COURSE_ID)) { if ($course_nid && $node->uid) { og_save_subscription($course_nid, $node->uid, array('is_active' => 1)); } } } // …. return $errors; } ?>
Naturally, there’s lots of other processing I might (and do!) need, but this should give you the basic idea of how the process works. Once I have both my mappings in place and my code for additional processing I can start the data import using Migrate. Before I do that though, I’ll want to keep in mind that Drupal loves sending emails to users when accounts are created, I’m going to want to make sure I disable that to prevent thousands of users from getting emails pointing them to my development informing them their accounts have been created. I’ll do this by using the a href=”http://drupal.org/project/devel”>devel module and setting SMTP to log only on the devel settings page. Once that’s done I can start the import. To do this I’ll head over to Admin -> Content -> Migrate -> Process and run the user import process. Again, and again, and again and.. well, you get the point. The cycle of running a sample import, verifying the data, making any adjustments needed, and running it again can be a very time consuming process. However, it’s integral to making sure the import is working as planned.
Updates, Updates, Updates
So far I’ve covered the process of initially importing the data, which might be useful as we develop the site, but what about a couple months from now when the site is ready to go live? How do I handle changes to old accounts, and import new users? Well, it turns out the solution to that is pretty easy. There are actually two ways I can go about that. The first is quite simple. I just use data from the view row in the migrate hook I am implementing to figure out if an object of that type already exists. In the case of this user import, here’s the code I’m using to figure out if the user I’m importing is already in the system.
<?php /** * Implementation of hook_migrate_prepare_user(). */ function newsu_user_import_migrate_prepare_user(&$account, $tblinfo, $row) { if ($uid = db_result(db_query("SELECT uid FROM {users} WHERE name = '%s'", $row->userLegacy_strUsername))) { $account['uid'] = $uid; } // …. } ?>
In the event the user has already been imported, easy enough, I just set the uid on the $account array to equal their existing Drupal uid and the call to user_save will perform an update, rather than adding a user.
The second possibility requires a bit more knowledge about how Migrate works. Every content set which gets created by Migrate gets an entry in the migrate_content_sets table, which contains a unique migration content set id, or mcsid. Migrate also creates a table for each content set, in the form of migrate_map_<mcsid>. This table contains two columns, a sourceid which stored the primary key from the legacy data, and the destid which stored the new id of the Drupal Object. Migrate does this for two reasons. The first is that when doing an import on a content set Migrate needs to keep track of what it has processed and what it hasn’t. This table provides an easy way for Migrate to track this information. The second reason is that there is also the possibility that a later import of related data will need to preserve a relationship which was present, and the migrate_map_<mcsid> table provides an easy mechanism for doing that. In either case, when using this method just run a full import once. You can then copy the data from migrate_map_<mcsid> into a new table, truncate the original (to make migrate think it hasn’t imported your data set yet) and run queries to get Drupal id’s against your copy, attempting to match against the legacy data’s source id. This is useful for situations where the primary key in the legacy data is your only source for ensuring the uniqueness of content, like article content where titles and other fields cannot be counted on to be unique.
Wrapping it up
If you’re still with me, I’m proud of you. That pretty much wraps things up, at this point I’ve covered exporting data from SQL server to MySQL, setting up field mappings using Migrate and Table Wizard, writing additional code to process values which can’t be directly mapped, and managing updates to data that’s previously been imported. Thanks should also go to Mike Ryan and Moshe Weitzman of Cyrve for their work on Migrate and Table Wizard.
