Conversion of TechScreen from Drupal 6 to Drupal 7

I) Installation of Drupal 7 with theme Garland as default

II) Installation of Modules

Entity Reference (to support links from challenge to solution and vice versa)
Tagadelic and Tagadelic Taxonomy (for the tag cloud)
5 Star Voting

III) Define content nodes "challenge" and "solution" (old name of challenge was problem, but in the UI we have used challenge) with the same fields as in original nodes

IV) Database Conversion

Steps in conversion

1) New node table

nodes before 38 are at the moment used for different content
a node in Drupal 7 has more attributes and body, teaser and format have moved to node_revision and field_data_body
field_data_body needs also the type
if the goal system does not use multi-language support, all language fields must be "und" (undefined)

SELECT nid,vid,uid,title,log,timestamp FROM node WHERE nid > 37
-> export result and import into new node table

2) New node_revision

Problem in conversion of node_revisions -> field_data_body: Node type in node but not in node_revision

SELECT nid, vid, uid, title, log, timestamp, status, comment, 0, sticky FROM node WHERE node.nid > 37

Define node_revisions.entity_type
INSERT IGNORE INTO node_revisions (nid, vid, uid, title, log, timestamp, status, comment, promote, sticky)

INSERT IGNORE INTO `node_revision` (`nid`, `vid`, `uid`, `title`, `log`, `timestamp`)
UPDATE node_revisions, node SET entity_type=node.type WHERE node_revisions.nid=node.nid AND node_revisions.vid=node.vid

SELECT "node",node.type as bundle, 0, node_revisions.nid, node_revisions.vid, "en", 0, node_revisions.body as body_value, node_revisions.teaser as body_summary, node_revisions.format as body_format
FROM node_revisions, node WHERE node_revisions.nid=node.nid AND node.nid > 37

3) New field_data_body and field_revision_body
SELECT "node" as entity_type, "challenge" as bundle, "0" as `delete`, node.nid as entity_id, node.vid as revision_id, "und" as language, "0" as delta, content_type_problem.field_description_value as body_value, "" as body_summary, content_type_problem.field_description_format as body_format FROM node, content_type_problem WHERE content_type_problem.nid=node.nid AND node.nid > 37 AND type="problem"
INSERT IGNORE INTO field_data_body (entity_type, bundle, delete, entity_id, revision_id, language, delta, body_value, body_summary, body_format)

INSERT IGNORE INTO field_revision_body (entity_type, bundle, delete, entity_id, revision_id, language, delta, body_value, body_summary, body_format)

4) Change of content type
Because I have changed the content node "problem" to "challenge"

UPDATE node SET type="challenge" WHERE type="problem"
UPDATE field_data_body SET bundle="challenge" WHERE bundle="problem"
UPDATE field_revision_body SET bundle="challenge" WHERE bundle="problem"

5) Converse comments
Without converting comments the body of nodes is not known

Converting comments to comment and field_data_comment_body

SELECT cid, pid, nid, uid, subject, hostname, timestamp as created, status thread, name, mail, homepage FROM comments

SELECT "comment", "comment_node_challenge", 0, cid as entity_id, cid as revision_id, "und", 0, comments.comment as comment_body_value, format as comment_body_format FROM comments, node where node.nid=comments.nid and node.type = "problem"
SELECT "comment", "comment_node_solution", 0, cid as entity_id, cid as revision_id, "und", 0, comments.comment as comment_body_value, format as comment_body_format FROM comments, node where node.nid=comments.nid and node.type = "solution"

INSERT IGNORE INTO field_data_comment_body (entity_type, bundle, deleted, entity_id, revision_id, language, delta, comment_body_value, comment_body_format)

6) Convert the link from Solution to Challenge

Old data is in content_type_solution, new link in field problem_id (to be defined in Drupal)

SELECT "node" as entity_type, "solution" as bundle, 0, vid as entity_id, nid as revision_id, "und" as language, 0, field_store_problem_id_value as field_problem_id_value, NULL FROM content_type_solution

INSERT IGNORE INTO field_data_field_problem_id(entity_type, bundle, deleted, entity_id, revision_id, language, delta, field_problem_id_value, field_problem_id_format)
VALUES ([value-1],[value-2],[value-3],[value-4],[value-5],[value-6],[value-7],[value-8],[value-9])