Importing Data Into Drupal CCK

Posted on: 20 March 2008

I've now got a bunch of structured data in a relational database, and need to get it into Drupal. I've created my content types using CCK. Fortunately, there's a great overview here which highlights some of the core Drupal tables I need to populate which aren't immediately apparent.

Everything is pretty straightforward, apart from populating deltas for repeating fields in CCK.

An example will help here.

Suppose I have a content type of person, who can have multiple phone numbers. CCK lets me add a field of type text, which is defined as a repeating field. To the end user, this looks as though you can simply enter as many phone numbers as you like for this person.

In the database, CCK creates a table called content_type_person, which holds simple fields (i.e. fields where there can only be one value - things like first name, surname). It also creates a separate table for repeating fields, so phone numbers will be stored in content_field_contact_telephone. This new table is keyed on vid and nid (i.e. the current version of the node this person belongs to), and delta. Delta is a zero-based autoincrementing field, which increments for each new value for this nid.

Again, an example will help.

I create a new person, which is internally given nid 1 and vid 1, and added to content-type-person. If I add three phone numbers, these are added to content_field_telephone with nid and vid of 1, and deltas of 0, 1 and 2. A second person with nid 2 would get deltas 0, 1 and 2 again:

nid     vid     delta     phone
1 1 0 01234 567890
1 1 1 01234 567891
1 1 2 01234 567892
2 2 0 01234 567893
2 2 1 01234 567894
2 2 2 01234 567895

I've got hundreds of people, and several phone numbers for each. I can create the content node OK and get the right nid and vid, but how do I then insert all these into the database, and get nice zero-based incrementing delta values?

The obvious answer (coming from a procedural programmer's perspective) is to write a cursor. However, I have an instinctive dislike of cursors, and felt that there must be a better set-based alternative. And there is - MySQL 5.02 introduced triggers, and this is the key.

Some experimentation and testing led to this trigger on the content_field_contact_telephone table:
delimiter |
create trigger generate_deltas before insert on content_field_contact_telephone
for each row begin
    set = (select max(delta)+1 from content_field_contact_telephone where vid = new.vid);

Bingo! Specify your insert values for nid, vid and phone, and you get nice neat auto-incremented delta values for each nid/vid. Just remember to take the trigger off again once you've finished bulk loading - I'm not sure what impact this would have on the main CCK content handling, but I suspect it wouldn't be pleasant! Even better, load this data into a staging table to generate the deltas, check them over, and then transfer them to your main Drupal tables.