drupal2openpublish-drupal.sql

CREATE TABLE IF NOT EXISTS `node_convert` (
  `nid` int(11) NOT NULL,
  `created_at` int(11) NOT NULL,
  `updated_at` int(11) NOT NULL,
  `lang` varchar(5) character set utf8 NOT NULL,
  `title` varchar(255) character set utf8 NOT NULL,
  `url` varchar(255) character set utf8 NOT NULL,
  `body` longtext character set utf8 NOT NULL,
  `teaser` text character set utf8 NOT NULL,
  `new_nid` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  `last_comment_timestamp` int(11) NOT NULL,
  `last_comment_uid` int(11) NOT NULL,
  `comment_count` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO node_convert(nid, title, lang, created_at, updated_at, teaser, body, uid) 
  SELECT node.nid, node.title, IF(node.language = '', 'sv', node.language), node.created, node.changed, 
         fd_preface.textual_data AS preface, fd_body.textual_data AS body, node.uid
    FROM node
      INNER JOIN flexinode_data AS fd_preface ON fd_preface.nid = node.nid AND fd_preface.field_id = 41
      INNER JOIN flexinode_data AS fd_body ON fd_body.nid = node.nid AND fd_body.field_id = 42
    WHERE node.type = 'flexinode-7'
      AND status = 1;
--      AND node.nid = 1566;
      
UPDATE node_convert SET url = (SELECT dst FROM url_alias
                                 WHERE src = CONCAT('node/', node_convert.nid)
                                   AND dst LIKE 'nyheter/%' LIMIT 1);
                         
CREATE TABLE `comments_convert` (
  `cid` int(10) NOT NULL,
  `nid` int(10) NOT NULL default '0',
  `uid` int(10) NOT NULL default '0',
  `subject` varchar(64) character set latin1 NOT NULL default '',
  `comment` longtext character set latin1 NOT NULL,
  `hostname` varchar(128) character set latin1 NOT NULL default '',
  `timestamp` int(11) NOT NULL default '0',
  `score` mediumint(9) NOT NULL default '0',
  `status` tinyint(3) unsigned NOT NULL default '0',
  `thread` varchar(255) character set latin1 NOT NULL default '',
  `name` varchar(60) character set latin1 default NULL,
  `mail` varchar(64) character set latin1 default NULL,
  `homepage` varchar(255) character set latin1 default NULL,
  PRIMARY KEY  (`cid`),
  KEY `lid` (`nid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

INSERT INTO comments_convert(nid, cid, uid, subject, comment, timestamp, thread, score, status, name, mail, homepage, hostname)
  SELECT nid, cid, uid, subject, comment, timestamp, thread, score, status, name, mail, homepage, hostname
    FROM comments
  WHERE nid IN((SELECT nid FROM node_convert))
    AND status = 0
  ORDER BY cid, thread ASC;
  
CREATE TABLE `users_convert` (
  `uid` int(10) unsigned NOT NULL default '0',
  `name` varchar(60) NOT NULL default '',
  `pass` varchar(32) NOT NULL default '',
  `mail` varchar(64) default NULL,
  `signature` varchar(255) NOT NULL default '',
  `created` int(11) NOT NULL default '0',
  `access` int(11) NOT NULL default '0',
  `language` varchar(12) NOT NULL default '',
  `picture` varchar(255) NOT NULL default '',
  `init` varchar(64) default NULL,
  `data` longtext,
  `login` int(11) NOT NULL default '0',
  `new_uid` int(11) DEFAULT NULL,
  PRIMARY KEY  (`uid`),
  UNIQUE KEY `name` (`name`),
  KEY `changed` (`access`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO users_convert (uid, name, pass, mail, signature, created, access, language, init)
  SELECT uid, name, pass, mail, signature, created, access, language, init
    FROM users
  WHERE status = 1 
    AND (login != 0 OR uid = 0)

-- Just if, somehow, the user hasn't been put in the users table and has a comment
INSERT INTO users_convert (uid, name, pass, mail, signature, created, access, language, init)
  SELECT u.uid, u.name, u.pass, u.mail, u.signature, u.created, u.access, u.language, u.init
    FROM users AS u 
      INNER JOIN comments_convert AS c ON c.uid = u.uid
      LEFT OUTER JOIN users_convert AS uc ON uc.uid = c.uid
  WHERE uc.uid IS NULL;
  
UPDATE node_convert AS nc, node_comment_statistics AS ncs 
  SET nc.last_comment_timestamp = ncs.last_comment_timestamp,
      nc.last_comment_uid = ncs.last_comment_uid, nc.comment_count = ncs.comment_count
WHERE ncs.nid = nc.nid;

drupal2openpublish-openpublish.sql

INSERT INTO users (name, pass, mail, signature, created, access, language, init)
  SELECT u.name, u.pass, u.mail, u.signature, u.created, u.access, u.language, u.init
    FROM piratpartiet.users AS u
  WHERE lower(u.name) NOT IN(SELECT lower(name) FROM users)
  
UPDATE piratpartiet.users_convert AS uc SET new_uid = (SELECT uid FROM users AS u WHERE u.name = uc.name);

INSERT INTO openpublish.node (vid, type, language, title, uid, created, changed, comment, promote, status)
  SELECT nc.created_at, 'nyheter' AS type, nc.lang, nc.title, u.new_uid, nc.created_at, nc.updated_at, 2, 1, 1
    FROM piratpartiet.node_convert AS nc
      INNER JOIN piratpartiet.users_convert AS u ON u.uid = nc.uid
      LEFT OUTER JOIN openpublish.node AS n ON n.title = nc.title
    WHERE n.title IS NULL;
    
UPDATE node SET vid = nid WHERE vid > 200000;
UPDATE piratpartiet.node_convert AS nc SET new_nid = (SELECT nid FROM node WHERE title = nc.title AND created = nc.created_at);
DELETE FROM piratpartiet.node_convert WHERE new_nid = 0;

INSERT INTO url_alias (src, dst, language)
  SELECT CONCAT('node/', new_nid), url, lang 
    FROM piratpartiet.node_convert AS nc
      LEFT OUTER JOIN url_alias AS ua ON ua.dst = url
  WHERE ua.dst IS NULL;
  
INSERT INTO node_revisions (vid, nid, uid, title, body, teaser, timestamp, format)
  SELECT n.new_nid AS vid, n.new_nid, u.new_uid, n.title, n.body, n.teaser, n.updated_at, 2
    FROM piratpartiet.node_convert AS n
      INNER JOIN piratpartiet.users_convert AS u ON u.uid = n.uid
      LEFT OUTER JOIN openpublish.node_revisions AS nr ON nr.nid = n.new_nid
  WHERE nr.nid IS NULL;
  
INSERT INTO content_field_teaser (vid, nid, field_teaser_value, field_teaser_format)
  SELECT n.new_nid AS vid, n.new_nid, n.teaser, 1 
    FROM piratpartiet.node_convert AS n
      LEFT OUTER JOIN content_field_teaser AS cft ON cft.nid = n.new_nid
  WHERE cft.nid IS NULL;
  
INSERT INTO comments (nid, uid, subject, comment, timestamp, thread, status, name, mail, homepage, hostname)
  SELECT n.new_nid, u.new_uid, cc.subject, cc.comment, cc.timestamp, cc.thread, cc.status, cc.name, cc.mail, cc.homepage, cc.hostname
    FROM piratpartiet.comments_convert AS cc
      INNER JOIN piratpartiet.node_convert AS n ON n.nid = cc.nid
      INNER JOIN piratpartiet.users_convert AS u ON u.uid = cc.uid
      LEFT OUTER JOIN comments AS c ON c.nid = n.new_nid AND c.timestamp = cc.timestamp AND cc.hostname = c.hostname
  WHERE c.nid IS NULL;

INSERT INTO node_comment_statistics (nid, last_comment_timestamp, last_comment_uid, comment_count)
  SELECT nc.new_nid, nc.last_comment_timestamp, u.new_uid, nc.comment_count
    FROM piratpartiet.node_convert AS nc
      INNER JOIN piratpartiet.users_convert AS u ON u.uid = nc.uid
      LEFT OUTER JOIN openpublish.node_comment_statistics AS ncs ON ncs.nid = nc.new_nid
  WHERE ncs.nid IS NULL;

mysql-conversion-log.txt

mysql> INSERT INTO users (name, pass, mail, signature, created, access, language, init)
    ->   SELECT u.name, u.pass, u.mail, u.signature, u.created, u.access, u.language, u.init
    ->     FROM piratpartiet.users AS u
    ->   WHERE lower(u.name) NOT IN(SELECT lower(name) FROM users);
Query OK, 4793 rows affected (0.36 sec)
Records: 4793  Duplicates: 0  Warnings: 0

mysql> UPDATE piratpartiet.users_convert AS uc SET new_uid = (SELECT uid FROM users AS u WHERE u.name = uc.name);
Query OK, 2505 rows affected (0.12 sec)
Rows matched: 2505  Changed: 2505  Warnings: 0

mysql> INSERT INTO openpublish.node (vid, type, language, title, uid, created, changed, comment, promote, status)
    ->   SELECT nc.created_at, 'nyheter' AS type, nc.lang, nc.title, u.new_uid,nc.created_at, nc.updated_at, 2, 1, 1
    ->     FROM piratpartiet.node_convert AS nc
    ->       INNER JOIN piratpartiet.users_convert AS u ON u.uid = nc.uid
    ->       LEFT OUTER JOIN openpublish.node AS n ON n.title = nc.title
    ->     WHERE n.title IS NULL;
Query OK, 586 rows affected (0.13 sec)
Records: 586  Duplicates: 0  Warnings: 0

mysql> UPDATE node SET vid = nid WHERE vid > 200000;
Query OK, 586 rows affected (0.02 sec)
Rows matched: 586  Changed: 586  Warnings: 0

mysql> UPDATE piratpartiet.node_convert AS nc SET new_nid = (SELECT nid FROM node WHERE title = nc.title AND created = nc.created_at);
Query OK, 596 rows affected, 1 warning (0.03 sec)
Rows matched: 597  Changed: 596  Warnings: 1

mysql> DELETE FROM piratpartiet.node_convert WHERE new_nid = 0;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO url_alias (src, dst, language)
    ->   SELECT CONCAT('node/', new_nid), url, lang
    ->     FROM piratpartiet.node_convert AS nc
    ->       LEFT OUTER JOIN url_alias AS ua ON ua.dst = url
    ->   WHERE ua.dst IS NULL;
Query OK, 596 rows affected (0.06 sec)
Records: 596  Duplicates: 0  Warnings: 0

mysql> INSERT INTO node_revisions (vid, nid, uid, title, body, teaser, timestamp, format)
    ->   SELECT n.new_nid AS vid, n.new_nid, u.new_uid, n.title, n.body, n.teaser, n.updated_at, 2
    ->     FROM piratpartiet.node_convert AS n
    ->       INNER JOIN piratpartiet.users_convert AS u ON u.uid = n.uid
    ->       LEFT OUTER JOIN openpublish.node_revisions AS nr ON nr.nid = n.new_nid
    ->   WHERE nr.nid IS NULL;
Query OK, 586 rows affected, 1 warning (0.04 sec)
Records: 586  Duplicates: 0  Warnings: 0

mysql> INSERT INTO content_field_teaser (vid, nid, field_teaser_value, field_teaser_format)
    ->   SELECT n.new_nid AS vid, n.new_nid, n.teaser, 1
    ->     FROM piratpartiet.node_convert AS n
    ->       LEFT OUTER JOIN content_field_teaser AS cft ON cft.nid = n.new_nid
    ->   WHERE cft.nid IS NULL;
Query OK, 596 rows affected (0.02 sec)
Records: 596  Duplicates: 0  Warnings: 0

mysql> INSERT INTO comments (nid, uid, subject, comment, timestamp, thread, status, name, mail, homepage, hostname)
    ->   SELECT n.new_nid, u.new_uid, cc.subject, cc.comment, cc.timestamp, cc.thread, cc.status, cc.name, cc.mail, cc.homepage, cc.hostname
    ->     FROM piratpartiet.comments_convert AS cc
    ->       INNER JOIN piratpartiet.node_convert AS n ON n.nid = cc.nid
    ->       INNER JOIN piratpartiet.users_convert AS u ON u.uid = cc.uid
    ->       LEFT OUTER JOIN comments AS c ON c.nid = n.new_nid AND c.timestamp = cc.timestamp AND cc.hostname = c.hostname
    ->   WHERE c.nid IS NULL;
Query OK, 14419 rows affected (0.93 sec)
Records: 14419  Duplicates: 0  Warnings: 0

mysql> INSERT INTO node_comment_statistics (nid, last_comment_timestamp, last_comment_uid, comment_count)
    ->   SELECT nc.new_nid, nc.last_comment_timestamp, u.new_uid, nc.comment_count
    ->     FROM piratpartiet.node_convert AS nc
    ->       INNER JOIN piratpartiet.users_convert AS u ON u.uid = nc.uid
    ->       LEFT OUTER JOIN openpublish.node_comment_statistics AS ncs ON ncs.nid = nc.new_nid
    ->   WHERE ncs.nid IS NULL;
Query OK, 586 rows affected (0.02 sec)
Records: 586  Duplicates: 0  Warnings: 0