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 new.delta = (select max(delta)+1 from content_field_contact_telephone where vid = new.vid);
end;
|
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.