LTER Home | Intranet | LNO

Handling data in Drupal

Submitted by isangil on Fri, 10/08/2010 - 08:39.
We expend a good chunk of one day exposing climate data to Drupal Views for a small request that came from Bob Waide and James Brunt.

We succeeded to do this 100% for a single codebase, single database Drupal instance. Success did not come without having to go through some hoops. Sometimes the tasks explained below could be done in 15 minutes or less, but sometimes in first attempts, we had to bump into issues.

Let me sum-up the process.

First we made a table named ltersitesclimate, that has 8 columns. Table was made using a CREATE....SELECT mysql statement out of a external mysql database, call the DB "small". We joined the desired fields from the tables "ltersites" and "climate", including lat-lons and usual meteo station metrics, such as temperature, precipitation, relative humidity, evaporation, drought index, etc.

We still needed the georef:elevation and ecosystem type fields per Bob's request, but those were not so accessible, so we went on with what we had ready (in the spirit of beating the clock)

Then we exposed the new table "ltersitesclimate" to Drupal @ intranet-two (hosted at

Temperate hosts a drupal multi-site instance -- the same code-base, database is shared -- the usual Drupal table prefix differentiates the Drupal instances.

We used mysqldump to download a SQL script for the creation & population of "ltersitesclimate". Something like this: %mysqldump -u uname -p small ltersitesclimate > sitesclimate.sql

We ran the mysql shell prompt in (where the intranet2 mysql is hosted) to execute (mysql> source sitesclimate.sql) the mysqldump-generated script into the temperate Drupal database (tempertate_lternet_edu). Now the table and climate data content is in the proper Drupal database.

I installed drush - Using "drush", we included the necessary modules (Data, Schema, CTools..) to finish up - sidenote: drush now enables us to cut down on Drupal related maintenance tasks.

Using the Data module ($drupalRoot/admin/build/data/adopt) We were able to "adopt" the table from the list of adoptable tables. There was a substantial list of adoptable tables, as this Drupal instances sees the rest of the shared drupal instances tables as "adoptable" tables.

Upon data adoption, the "data" module is supposed to generate a simple view that allows you to inspect the data table contents - this view can be cloned for your peruse (styled as google maps, or spreadsheet, or a chart -- you follow the potential?)

However, the associated view was NOT successfully created, the error message seems to indicate that the actual query used the instance prefix which was not present -- (table is prefix-less, "ltersitesclimate", instead of "prefix_ltersitesclimate").

Upon some research, we found a bug issue that explained this unexpected behavior. It seems strange, as Marsh claims he did a similar procedure just 2 days ago on a multi-site install. The bug may have other legs.

We tried a suggested patch and then renaming the table w/ prefix, but the issue wouldnt really go away. Similar problem was found in a non-multi-site Drupal instance, at which point confusion settled.

Get out of the box. tic-toc-tic-toc

Workaround -- create a table first using the Data module ( and then the procedure work in a single-code single database Drupal. Boom!

I have a few hints as for what could be wrong with the most parsimonious approach, including the lack of PKs in the table we were adopting, and the prefix issue, of course.

But we ought to see how this works for multi-sites instances, as this module is the gateway to dynamic "data" charts, and maps.

see table too here

sites_climate.jpg74.23 KB
Submitted by isangil on Fri, 10/08/2010 - 08:39.