uSCimT3H

1CREATE TABLE IF NOT EXISTS `node_convert` (
2  `nid` int(11) NOT NULL,
3  `created_at` int(11) NOT NULL,
4  `updated_at` int(11) NOT NULL,
5  `lang` varchar(5) character set utf8 NOT NULL,
6  `title` varchar(255) character set utf8 NOT NULL,
7  `url` varchar(255) character set utf8 NOT NULL,
8  `body` longtext character set utf8 NOT NULL,
9  `teaser` text character set utf8 NOT NULL,
10  `new_nid` int(11) NOT NULL,
11  `uid` int(11) NOT NULL,
12  `last_comment_timestamp` int(11) NOT NULL,
13  `last_comment_uid` int(11) NOT NULL,
14  `comment_count` int(11) NOT NULL
15) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
16
17
18INSERT INTO node_convert(nid, title, lang, created_at, updated_at, teaser, body, uid) 
19  SELECT node.nid, node.title, IF(node.language = '', 'sv', node.language), node.created, node.changed, 
20         fd_preface.textual_data AS preface, fd_body.textual_data AS body, node.uid
21    FROM node
22      INNER JOIN flexinode_data AS fd_preface ON fd_preface.nid = node.nid AND fd_preface.field_id = 41
23      INNER JOIN flexinode_data AS fd_body ON fd_body.nid = node.nid AND fd_body.field_id = 42
24    WHERE node.type = 'flexinode-7'
25      AND status = 1;
26--      AND node.nid = 1566;
27      
28UPDATE node_convert SET url = (SELECT dst FROM url_alias
29                                 WHERE src = CONCAT('node/', node_convert.nid)
30                                   AND dst LIKE 'nyheter/%' LIMIT 1);
31                         
32CREATE TABLE `comments_convert` (
33  `cid` int(10) NOT NULL,
34  `nid` int(10) NOT NULL default '0',
35  `uid` int(10) NOT NULL default '0',
36  `subject` varchar(64) character set latin1 NOT NULL default '',
37  `comment` longtext character set latin1 NOT NULL,
38  `hostname` varchar(128) character set latin1 NOT NULL default '',
39  `timestamp` int(11) NOT NULL default '0',
40  `score` mediumint(9) NOT NULL default '0',
41  `status` tinyint(3) unsigned NOT NULL default '0',
42  `thread` varchar(255) character set latin1 NOT NULL default '',
43  `name` varchar(60) character set latin1 default NULL,
44  `mail` varchar(64) character set latin1 default NULL,
45  `homepage` varchar(255) character set latin1 default NULL,
46  PRIMARY KEY  (`cid`),
47  KEY `lid` (`nid`)
48) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
49
50INSERT INTO comments_convert(nid, cid, uid, subject, comment, timestamp, thread, score, status, name, mail, homepage, hostname)
51  SELECT nid, cid, uid, subject, comment, timestamp, thread, score, status, name, mail, homepage, hostname
52    FROM comments
53  WHERE nid IN((SELECT nid FROM node_convert))
54    AND status = 0
55  ORDER BY cid, thread ASC;
56  
57CREATE TABLE `users_convert` (
58  `uid` int(10) unsigned NOT NULL default '0',
59  `name` varchar(60) NOT NULL default '',
60  `pass` varchar(32) NOT NULL default '',
61  `mail` varchar(64) default NULL,
62  `signature` varchar(255) NOT NULL default '',
63  `created` int(11) NOT NULL default '0',
64  `access` int(11) NOT NULL default '0',
65  `language` varchar(12) NOT NULL default '',
66  `picture` varchar(255) NOT NULL default '',
67  `init` varchar(64) default NULL,
68  `data` longtext,
69  `login` int(11) NOT NULL default '0',
70  `new_uid` int(11) DEFAULT NULL,
71  PRIMARY KEY  (`uid`),
72  UNIQUE KEY `name` (`name`),
73  KEY `changed` (`access`)
74) ENGINE=MyISAM DEFAULT CHARSET=utf8;
75
76INSERT INTO users_convert (uid, name, pass, mail, signature, created, access, language, init)
77  SELECT uid, name, pass, mail, signature, created, access, language, init
78    FROM users
79  WHERE status = 1 
80    AND (login != 0 OR uid = 0)
81
82-- Just if, somehow, the user hasn't been put in the users table and has a comment
83INSERT INTO users_convert (uid, name, pass, mail, signature, created, access, language, init)
84  SELECT u.uid, u.name, u.pass, u.mail, u.signature, u.created, u.access, u.language, u.init
85    FROM users AS u 
86      INNER JOIN comments_convert AS c ON c.uid = u.uid
87      LEFT OUTER JOIN users_convert AS uc ON uc.uid = c.uid
88  WHERE uc.uid IS NULL;
89  
90UPDATE node_convert AS nc, node_comment_statistics AS ncs 
91  SET nc.last_comment_timestamp = ncs.last_comment_timestamp,
92      nc.last_comment_uid = ncs.last_comment_uid, nc.comment_count = ncs.comment_count
93WHERE ncs.nid = nc.nid;