Blob of contradictions

Easy table + views integration using the Data Module

Fri, 10/14/2011 - 22:04 -- btmash

I was recently tasked to import data from a deprecated database table (Q&A) into a Drupal 6 site. Regarding the data that was being imported in:

  • It is a one-time import.
  • It boiled down as one flat database file
  • No new content of this type will be added again. Ever.
  • It should not appear in search results
  • They need an easy way to go through all the data.
  • It would be great if it could be filtered through and made searchable.

From this, I knew one thing for sure; have all of this working with Views would be FANTASTIC. There are many (MANY!) different approaches that we could take to getting this data in.

  1. You could create a content type with fields to import the content. This method is very flexible (can add more fields, can add additional actions, integrates with practically everything). I have blogged about using migrate in the past, and that could be a way to import all the content in.
  2. We could import the data as webform data. Semi-flexible, simple UI (easy to add some new fields). But integration to try and search through was a pain. There are some great modules to help out with this (Webform MySQL Views) though they require additional dependencies.
  3. Create my own database table(s). A clear map to import the data. Very lightweight. But I would then have to define the ability to make the content more searchable. I would need to write out the code to get it integrated with Views.

I decided early on that (1) did not seem like the best approach. Its very flexible, but I also did not need most of the functionality that a node provides (content is being brought in once, no editing, no deletion, no additional fields). I did not need the content showing up in search results, and it would be easier to manage all of the data from a couple of tables. From there, I started off going the route of creating a webform but quickly ran into the issue of what to do as far as a Views integration is concerned. The current views integration is quite minimal (take a look at issue #680386). I then came across an excellent tutorial by Johan Falk (of NodeOne) wrote a fantastic tutorial on how to integrate webform submissions with views. This method required usage of the Webform MySQL Views module with the Data module. The key in all of this is the Data module which provides baseline integrations with Views (and the ability to join with other tables for more complex relationships).

A data UI screen showing how you can add new columns, change the type, size, what the indexes are and what the column can join against

With the Data module able to drive all of the views integration that I needed, I went ahead and created the table directly using the Data UI module. The other nice piece of functionality that comes with all of this is that you can export your data tables and have them as part of a module so you don't have to worry about losing the structure. You simply implement hook_data_default() and your table is ready to get used.

A views creation page showing the option to use the newly created data table as a views source

All I had to worry about at this stage was importing the actual content (which was really easy given that there was one table and I just just have to map the appropriate column in the CSV dump to the column in the data table.