-
Notifications
You must be signed in to change notification settings - Fork 0
/
vanilla2_to_smf.sql
executable file
·226 lines (195 loc) · 8.88 KB
/
vanilla2_to_smf.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
/* ATTENTION: You don't need to run or use this file! The convert.php script does everything for you! */
/******************************************************************************/
---~ name: "Vanilla2"
/******************************************************************************/
---~ version: "SMF 2.0"
---~ settings: "/conf/config-defaults.php", "/conf/config.php"
---~ defines: APPLICATION
---~ globals: Configuration
---~ from_prefix: "`{$Configuration['Database']['Name']}`.{$Configuration['Database']['DatabasePrefix']}"
---~ table_test: "{$from_prefix}User"
/******************************************************************************/
--- Converting members...
/******************************************************************************/
TRUNCATE {$to_prefix}members;
---* {$to_prefix}members
---{
$ignore = true;
$row['date_registered'] = strtotime($row['date_registered']);
//no regdate? lets create one..
if(empty($row['date_registered']))
$row['date_registered'] = mktime();
$row['last_login'] = strtotime($row['last_login']);
//no last_login
if(empty($row['last_login']))
$row['last_login'] = mktime();
$row['real_name'] = trim($row['real_name']) == '' ? $row['member_name'] : $row['real_name'];
---}
SELECT
m.UserID AS id_member, m.Name as member_name, m.DateFirstVisit AS date_registered,
(IFNULL(m.CountDiscussions, 0) + IFNULL(m.CountComments, 0)) AS posts, m.DateLastActive AS last_login,
m.admin AS id_group, m.Password AS passwd, m.Name AS real_name, m.Email AS email_address,
CASE m.ShowEmail WHEN 1 THEN 0 ELSE 1 END as hide_email, '' AS avatar,
'' AS member_ip, '' AS member_ip2, '' AS password_salt,
'' AS lngfile, '' AS buddy_list, '' AS pm_ignore_list, '' AS message_labels,
'' AS personal_text, '' AS time_format, '' AS usertitle, '' AS secret_question,
'' AS secret_answer, '' AS validation_code, '' AS additional_groups, '' AS smiley_set
FROM {$from_prefix}User AS m;
---*
/******************************************************************************/
--- Converting boards...
/******************************************************************************/
TRUNCATE {$to_prefix}boards;
---* {$to_prefix}boards
SELECT
c.CategoryID AS id_board, 1 AS id_cat, 0 AS id_parent, 0 AS num_posts,
0 AS num_topics, '-1,0' AS member_groups, SUBSTRING(c.Name, 1, 255) AS name,
SUBSTRING(c.Description, 1, 65534) AS description, c.Sort AS board_order
FROM {$from_prefix}Category AS c;
---*
/******************************************************************************/
--- Converting topics...
/******************************************************************************/
TRUNCATE {$to_prefix}topics;
TRUNCATE {$to_prefix}log_topics;
TRUNCATE {$to_prefix}log_boards;
TRUNCATE {$to_prefix}log_mark_read;
TRUNCATE {$to_prefix}messages;
---* {$to_prefix}topics
---{
$no_add = true;
$ignore = true;
$row['poster_time'] = strtotime($row['poster_time']);
$row['modified_time'] = is_null($row['modified_time']) ? 0 : strtotime($row['modified_time']);
$row['modified_name'] = is_null($row['modified_name']) ? '' : $row['modified_name'];
convert_insert('messages',
array(
'id_topic', 'id_board', 'poster_time', 'id_member', 'subject', 'poster_name', 'poster_email', 'poster_ip', 'modified_time', 'modified_name', 'body'),
array(
$row['id_topic'], $row['id_board'], $row['poster_time'], $row['id_member_started'], $row['subject'], $row['poster_name'], $row['poster_email'], "", $row['modified_time'], $row['modified_name'], $row['body'])
);
$rows[] = array(
'id_topic' => $row['id_topic'],
'is_sticky' => $row['is_sticky'],
'id_board' => $row['id_board'],
'id_member_started' => $row['id_member_started'],
'id_first_msg' => $row['modified_time'],
'id_last_msg' => $row['modified_time'],
'id_member_updated' => $row['id_member_started'],
'num_replies' => $row['num_replies'],
'locked' => $row['locked'],
);
---}
SELECT
t.DiscussionID AS id_topic, t.Announce AS is_sticky, t.CategoryID AS id_board, t.InsertUserID AS id_member_started, t.LastCommentUserID AS id_member_updated, t.body, t.CountComments AS num_replies, t.Closed AS locked, IFNULL(fm.Name, 'Guest') AS poster_name, IFNULL(fm.Email, 'Unknown') AS poster_email, t.DateInserted AS poster_time, t.name AS subject, fm.name AS modified_name, t.DateUpdated AS modified_time
FROM {$from_prefix}Discussion AS t
LEFT JOIN {$from_prefix}User AS fm ON (t.InsertUserID = fm.UserID);
---*
/******************************************************************************/
--- Converting posts...
/******************************************************************************/
---* {$to_prefix}messages 200
---{
if(empty($row['id_board']))
$row['id_board'] = 1;
$row['poster_time'] = strtotime($row['poster_time']);
$row['modified_time'] = is_null($row['modified_time']) ? 0 : strtotime($row['modified_time']);
$row['modified_name'] = is_null($row['modified_name']) ? '' : $row['modified_name'];
---}
SELECT
p.DiscussionID AS id_topic, t.CategoryID AS id_board,
p.DateInserted AS poster_time, p.InsertUserID AS id_member, t.Name AS subject,
IFNULL(m.Name, 'Guest') AS poster_name, IFNULL(m.Email, 'Unknown') AS poster_email, '' AS poster_ip,
p.DateUpdated AS modified_time, m2.Name AS modified_name, p.Body AS body
FROM {$from_prefix}Comment AS p
LEFT JOIN {$from_prefix}Discussion AS t ON (t.DiscussionID = p.DiscussionID)
LEFT JOIN {$from_prefix}User AS m ON (m.UserID = p.InsertUserID)
LEFT JOIN {$from_prefix}User AS m2 ON (m2.UserID = p.UpdateUserID)
WHERE p.CommentID > 0;
---*
/******************************************************************************/
--- Converting personal messages (step 1)...
/******************************************************************************/
TRUNCATE {$to_prefix}personal_messages;
---* {$to_prefix}personal_messages
---{
$row['msgtime'] = strtotime($row['msgtime']);
---}
SELECT
cm.MessageID AS id_pm, cm.InsertUserID AS id_member_from, cm.DateInserted AS msgtime,
IFNULL(uc.Deleted, 0) AS deleted_by_sender, c.FirstMessageID AS id_pm_head,
SUBSTRING(IFNULL(u.Name, "Guest"), 1, 255) AS from_name, "(No Subject)" AS subject,
SUBSTRING(cm.body, 1, 65534) AS body
FROM {$from_prefix}Conversation AS c
LEFT JOIN {$from_prefix}ConversationMessage AS cm ON (c.ConversationID = cm.ConversationID)
LEFT JOIN {$from_prefix}User AS u ON (c.InsertUserID = u.UserID)
LEFT JOIN {$from_prefix}UserConversation AS uc ON (c.InsertUserID = uc.UserID AND c.ConversationID = uc.ConversationID)
GROUP BY cm.MessageID;
---*
/******************************************************************************/
--- Converting personal messages (step 2)...
/******************************************************************************/
TRUNCATE {$to_prefix}pm_recipients;
---* {$to_prefix}pm_recipients
---{
$no_add = true;
$keys = array('id_pm', 'id_member', 'labels', 'is_read');
$invited_members = @unserialize($row['Contributors']);
if (is_array($invited_members) && !empty($invited_members))
{
foreach ($invited_members as $invited => $id)
{
if (!empty($invited) && $row['msg_author_id'] != $id)
{
// Check if this should be marked as deleted or as is_read
$result = convert_query("
SELECT IFNULL(DateLastViewed, 0) AS is_read, deleted
FROM {$from_prefix}UserConversation
WHERE UserID = {$id} AND ConversationID = {$row['ConversationID']}");
list ($is_read, $is_deleted) = convert_fetch_row($result);
convert_free_result($result);
$rows[] = array(
'id_pm' => $row['id_pm'],
'id_member' => $id,
'labels' => $row['labels'],
'is_read' => is_numeric($is_read) ? $is_read : strtotime($row['DateInserted']) < strtotime($is_read) ? 1 : 0,
'deleted' => $is_deleted,
);
}
}
}
---}
SELECT cm.MessageID AS id_pm, '-1' AS labels, c.ConversationID, cm.DateInserted, c.Contributors, cm.InsertUserID AS msg_author_id
FROM {$from_prefix}ConversationMessage AS cm
LEFT JOIN {$from_prefix}Conversation AS c ON (c.ConversationID = cm.ConversationID)
GROUP BY cm.MessageID;
---*
/******************************************************************************/
--- Converting avatars...
/******************************************************************************/
DELETE FROM {$to_prefix}attachments WHERE id_msg = 0;
---* {$to_prefix}attachments
---{
$no_add = true;
$upload_dir = (!isset($upload_dir)) ? $_POST['path_from'] . '/uploads/' : $upload_dir;
$row['Photo'] = str_replace('userpics/', 'userpics/p', $row['Photo']);
$row['filename'] = str_replace('userpics/', '', $row['Photo']);
$file_hash = getAttachmentFilename($row['filename'], $id_attach, null, true);
$physical_filename = $id_attach . '_' . $file_hash;
if (strlen($physical_filename) > 255)
return;
if (!@copy($upload_dir . $row['Photo'], $attachmentUploadDir . '/' . $physical_filename))
return;
$rows[] = array(
'id_attach' => $id_attach,
'size' => filesize($attachmentUploadDir . '/' . $physical_filename),
'filename' => $row['filename'],
'file_hash' => $file_hash,
'id_member' => $row['id_member'],
);
$id_attach++;
---}
SELECT Photo, UserID AS id_member
FROM {$from_prefix}User
WHERE Photo IS NOT NULL;
---*