SQL For Migrating Drupal 5 to 7

I needed to migrate the data from a Drupal 5 website to a Drupal 7 site. It’s running on MySQL. Here’s the SQL I used to do it. Note: this is NOT the preferred solution for this and might not work at all for anyone else.

In this case, drupal7 is the Drupal 7 database and drupaldb is the Drupal 5 database. The tables for the site I wanted to migrate are prefixed with lake_.

To migrate aggregator feeds:

INSERT
INTO    drupal7.lake_aggregator_feed (
            fid,
            title,
            url,
            refresh,
            checked,
            link,
            description,
            image,
            etag,
            modified,
            block)
SELECT  fid,
        title,
        url,
        refresh,
        checked,
        link,
        description,
        image,
        etag,
        modified,
        block
FROM    drupaldb.lake_aggregator_feed;

INSERT
INTO    drupal7.lake_aggregator_item (
            iid,
            fid,
            title,
            link,
            author,
            description,
            timestamp,
            guid)
SELECT  iid,
        fid,
        title,
        link,
        author,
        description,
        timestamp,
        guid
FROM    drupaldb.lake_aggregator_item;

To migrate nodes:

DELETE
FROM    drupal7.lake_node;

INSERT
INTO    drupal7.lake_node (
            nid,
            vid,
            type,
            language,
            title,
            uid,
            status,
            created,
            changed,
            comment,
            promote,
            sticky
)
SELECT  nid,
        vid,
        IF(type = "event", "date", type),
        "und",
        title,
        uid,
        status,
        created,
        changed,
        comment,
        promote,
        sticky
FROM    drupaldb.lake_node;

DELETE
FROM    drupal7.lake_node_revision;

INSERT
INTO    drupal7.lake_node_revision (
            nid,
            vid,
            uid,
            title,
            log,
            timestamp,
            status,
            comment,
            promote,
            sticky
)
SELECT  dlnr.nid,
        dlnr.vid,
        dlnr.uid,
        dlnr.title,
        dlnr.log,
        dlnr.timestamp,
        1,
        2,
        1,
        0
FROM    drupaldb.lake_node_revisions dlnr
    INNER JOIN drupaldb.lake_node dln ON dlnr.vid = dln.vid;

DELETE
FROM    drupal7.lake_taxonomy_index;

INSERT
INTO    drupal7.lake_taxonomy_index (
            nid,
            tid,
            sticky,
            created
)
SELECT  dltn.nid,
        d7lttd.tid,
        0,
        d7lnr.timestamp
FROM    ((drupaldb.lake_term_node dltn
    LEFT JOIN drupal7.lake_node_revision d7lnr          ON dltn.nid = d7lnr.nid)
    LEFT JOIN drupaldb.lake_term_data dltd              ON dltn.tid = dltd.tid)
    LEFT JOIN drupal7.lake_taxonomy_term_data d7lttd    ON dltd.name LIKE d7lttd.name;

DELETE
FROM    drupal7.lake_field_data_body;

INSERT
INTO    drupal7.lake_field_data_body (
            entity_type,
            entity_id,
            revision_id,
            bundle,
            delta,
            language,
            body_value,
            body_summary,
            body_format
)
SELECT  "node",
        dln.nid,
        MAX(dlnr.vid),
        IF(dln.type = "event", "date", dln.type),
        0,
        "und",
        dlnr.body,
        dlnr.teaser,
        dlnr.format
FROM    drupaldb.lake_node_revisions dlnr
    LEFT JOIN drupaldb.lake_node dln    ON dlnr.vid = dln.vid
GROUP BY dln.nid;
    
DELETE
FROM    drupal7.lake_field_revision_body;
    
 INSERT
 INTO   drupal7.lake_field_revision_body (
            entity_type,
            entity_id,
            revision_id,
            bundle,
            delta,
            language,
            body_value,
            body_summary,
            body_format
)
SELECT  "node",
        dlnr.nid,
        dlnr.vid,
        IF(dln.type = "event", "date", dln.type),
        0,
        "und",
        dlnr.body,
        dlnr.teaser,
        dlnr.format
FROM    drupaldb.lake_node_revisions dlnr
    LEFT JOIN drupaldb.lake_node dln    ON dlnr.vid = dln.vid;

DELETE
FROM    drupal7.lake_field_data_field_date;

INSERT
INTO    drupal7.lake_field_data_field_date (
            entity_type,
            entity_id,
            revision_id,
            bundle,
            delta,
            language,
            field_date_value,
            field_date_value2,
            field_date_rrule
)
SELECT  "node",
        dle.nid,
        dln.vid,
        IF(dln.type = "event", "date", dln.type),
        0,
        "und",
        dle.event_start,
        dle.event_end,
        dler.repeat_RRULE
FROM    ((drupaldb.lake_event dle
    LEFT JOIN drupaldb.lake_node dln                    ON dle.nid = dln.nid)
    LEFT JOIN drupaldb.lake_event_repeat_nodes dlern    ON dle.nid = dlern.nid)
    LEFT JOIN drupaldb.lake_event_repeat dler           ON dlern.rid = dler.rid;

DELETE
FROM    drupal7.lake_field_revision_field_date;

INSERT
INTO    drupal7.lake_field_revision_field_date (
            entity_type,
            entity_id,
            revision_id,
            bundle,
            delta,
            language,
            field_date_value,
            field_date_value2,
            field_date_rrule
)
SELECT  "node",
        dle.nid,
        dln.vid,
        IF(dln.type = "event", "date", dln.type),
        0,
        "und",
        dle.event_start,
        dle.event_end,
        dler.repeat_RRULE
FROM    ((drupaldb.lake_event dle
    LEFT JOIN drupaldb.lake_node dln                    ON dle.nid = dln.nid)
    LEFT JOIN drupaldb.lake_event_repeat_nodes dlern    ON dle.nid = dlern.nid)
    LEFT JOIN drupaldb.lake_event_repeat dler           ON dlern.rid = dler.rid;

DELETE
FROM    drupal7.lake_field_data_field_term;

INSERT
INTO    drupal7.lake_field_data_field_term (
            entity_type,
            entity_id,
            revision_id,
            bundle,
            delta,
            language,
            field_term_tid
)
SELECT  "node",
        dln.nid,
        dln.vid,
        IF(dln.type = "event", "date", dln.type),
        0,
        "und",
        MAX(d7lttd.tid)
FROM    (((drupaldb.lake_node dln
    LEFT JOIN drupaldb.lake_term_node dltn              ON dln.nid = dltn.nid)
    LEFT JOIN drupal7.lake_node_revision d7lnr          ON dltn.nid = d7lnr.nid)
    LEFT JOIN drupaldb.lake_term_data dltd              ON dltn.tid = dltd.tid)
    LEFT JOIN drupal7.lake_taxonomy_term_data d7lttd    ON dltd.name LIKE d7lttd.name
GROUP BY dln.nid;

DELETE
FROM    drupal7.lake_node_comment_statistics;

INSERT
INTO    drupal7.lake_node_comment_statistics (
            nid,
            cid,
            last_comment_timestamp,
            last_comment_name,
            last_comment_uid,
            comment_count
)
SELECT  nid,
        0,
        last_comment_timestamp,
        last_comment_name,
        last_comment_uid,
        comment_count
FROM    drupaldb.lake_node_comment_statistics;
Advertisements

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: