Skip to content

Instantly share code, notes, and snippets.

@GromNaN
Last active August 31, 2016 23:11
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save GromNaN/617450d310e9b987d0427204b6bcafc1 to your computer and use it in GitHub Desktop.
Users and Forum migration from e107 to Joomla+Kunena
SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
DELIMITER $$
DROP FUNCTION IF EXISTS HTML_UnEncode$$
CREATE DEFINER=`root`@`localhost` FUNCTION `HTML_UnEncode`(x VARCHAR(16383)) RETURNS varchar(16383) CHARSET utf8mb4
BEGIN
DECLARE TextString VARCHAR(16383);
SET TextString = x;
IF INSTR(x, '[blockquote')
THEN SET TextString = REPLACE(REPLACE(TextString, '[blockquote','[quote'), '[/blockquote]','[/quote]');
END IF;
IF INSTR(x, '[link')
THEN SET TextString = REPLACE(REPLACE(TextString, '[link','[url'), '[/link', '[/url');
END IF;
IF INSTR(x, '[iframe')
THEN SET TextString = REPLACE(REPLACE(TextString, '[iframe','[video'), '[/iframe', '[/video');
END IF;
IF INSTR(x, '[img:')
THEN SET TextString = REPLACE(TextString, '[img:','[img ');
END IF;
IF INSTR(x, '{e_FILE}')
THEN SET TextString = REPLACE(TextString, '{e_FILE}','/e107_files/');
END IF;
IF INSTR(x, '\\'')
THEN SET TextString = REPLACE(TextString, '&\#092;\'','\'');
END IF;
IF INSTR(x, '$')
THEN SET TextString = REPLACE(TextString, '&\#036;','\$');
END IF;
IF INSTR(x, '%')
THEN SET TextString = REPLACE(TextString, '&\#037;','\%');
END IF;
IF INSTR(x, '&')
THEN SET TextString = REPLACE(TextString, '&\#038;','\&');
END IF;
IF INSTR(x, ''')
THEN SET TextString = REPLACE(TextString, '&\#039;','\'');
END IF;
#quotation mark
IF INSTR(x, '"')
THEN SET TextString = REPLACE(TextString, '"','"');
END IF;
#apostrophe
IF INSTR(x, ''')
THEN SET TextString = REPLACE(TextString, ''','"');
END IF;
#ampersand
IF INSTR(x, '&')
THEN SET TextString = REPLACE(TextString, '&','&');
END IF;
#less-than
IF INSTR(x, '<')
THEN SET TextString = REPLACE(TextString, '&lt;','<');
END IF;
#greater-than
IF INSTR(x, '&gt;')
THEN SET TextString = REPLACE(TextString, '&gt;','>');
END IF;
#non-breaking space
IF INSTR(x, '&nbsp;')
THEN SET TextString = REPLACE(TextString, '&nbsp;',' ');
END IF;
#inverted exclamation mark
IF INSTR(x, '&iexcl;')
THEN SET TextString = REPLACE(TextString, '&iexcl;','¡');
END IF;
#cent
IF INSTR(x, '&cent;')
THEN SET TextString = REPLACE(TextString, '&cent;','¢');
END IF;
#pound
IF INSTR(x, '&pound;')
THEN SET TextString = REPLACE(TextString, '&pound;','£');
END IF;
#currency
IF INSTR(x, '&curren;')
THEN SET TextString = REPLACE(TextString, '&curren;','¤');
END IF;
#yen
IF INSTR(x, '&yen;')
THEN SET TextString = REPLACE(TextString, '&yen;','¥');
END IF;
#broken vertical bar
IF INSTR(x, '&brvbar;')
THEN SET TextString = REPLACE(TextString, '&brvbar;','¦');
END IF;
#section
IF INSTR(x, '&sect;')
THEN SET TextString = REPLACE(TextString, '&sect;','§');
END IF;
#spacing diaeresis
IF INSTR(x, '&uml;')
THEN SET TextString = REPLACE(TextString, '&uml;','¨');
END IF;
#copyright
IF INSTR(x, '&copy;')
THEN SET TextString = REPLACE(TextString, '&copy;','©');
END IF;
#feminine ordinal indicator
IF INSTR(x, '&ordf;')
THEN SET TextString = REPLACE(TextString, '&ordf;','ª');
END IF;
#angle quotation mark (left)
IF INSTR(x, '&laquo;')
THEN SET TextString = REPLACE(TextString, '&laquo;','«');
END IF;
#negation
IF INSTR(x, '&not;')
THEN SET TextString = REPLACE(TextString, '&not;','¬');
END IF;
#soft hyphen
IF INSTR(x, '&shy;')
THEN SET TextString = REPLACE(TextString, '&shy;','­');
END IF;
#registered trademark
IF INSTR(x, '&reg;')
THEN SET TextString = REPLACE(TextString, '&reg;','®');
END IF;
#spacing macron
IF INSTR(x, '&macr;')
THEN SET TextString = REPLACE(TextString, '&macr;','¯');
END IF;
#degree
IF INSTR(x, '&deg;')
THEN SET TextString = REPLACE(TextString, '&deg;','°');
END IF;
#plus-or-minus
IF INSTR(x, '&plusmn;')
THEN SET TextString = REPLACE(TextString, '&plusmn;','±');
END IF;
#superscript 2
IF INSTR(x, '&sup2;')
THEN SET TextString = REPLACE(TextString, '&sup2;','²');
END IF;
#superscript 3
IF INSTR(x, '&sup3;')
THEN SET TextString = REPLACE(TextString, '&sup3;','³');
END IF;
#spacing acute
IF INSTR(x, '&acute;')
THEN SET TextString = REPLACE(TextString, '&acute;','´');
END IF;
#micro
IF INSTR(x, '&micro;')
THEN SET TextString = REPLACE(TextString, '&micro;','µ');
END IF;
#paragraph
IF INSTR(x, '&para;')
THEN SET TextString = REPLACE(TextString, '&para;','');
END IF;
#middle dot
IF INSTR(x, '&middot;')
THEN SET TextString = REPLACE(TextString, '&middot;','·');
END IF;
#spacing cedilla
IF INSTR(x, '&cedil;')
THEN SET TextString = REPLACE(TextString, '&cedil;','¸');
END IF;
#superscript 1
IF INSTR(x, '&sup1;')
THEN SET TextString = REPLACE(TextString, '&sup1;','¹');
END IF;
#masculine ordinal indicator
IF INSTR(x, '&ordm;')
THEN SET TextString = REPLACE(TextString, '&ordm;','º');
END IF;
#angle quotation mark (right)
IF INSTR(x, '&raquo;')
THEN SET TextString = REPLACE(TextString, '&raquo;','»');
END IF;
#fraction 1/4
IF INSTR(x, '&frac14;')
THEN SET TextString = REPLACE(TextString, '&frac14;','¼');
END IF;
#fraction 1/2
IF INSTR(x, '&frac12;')
THEN SET TextString = REPLACE(TextString, '&frac12;','½');
END IF;
#fraction 3/4
IF INSTR(x, '&frac34;')
THEN SET TextString = REPLACE(TextString, '&frac34;','¾');
END IF;
#inverted question mark
IF INSTR(x, '&iquest;')
THEN SET TextString = REPLACE(TextString, '&iquest;','¿');
END IF;
#multiplication
IF INSTR(x, '&times;')
THEN SET TextString = REPLACE(TextString, '&times;','×');
END IF;
#division
IF INSTR(x, '&divide;')
THEN SET TextString = REPLACE(TextString, '&divide;','÷');
END IF;
#capital a, grave accent
IF INSTR(x, '&Agrave;')
THEN SET TextString = REPLACE(TextString, '&Agrave;','À');
END IF;
#capital a, acute accent
IF INSTR(x, '&Aacute;')
THEN SET TextString = REPLACE(TextString, '&Aacute;','Á');
END IF;
#capital a, circumflex accent
IF INSTR(x, '&Acirc;')
THEN SET TextString = REPLACE(TextString, '&Acirc;','Â');
END IF;
#capital a, tilde
IF INSTR(x, '&Atilde;')
THEN SET TextString = REPLACE(TextString, '&Atilde;','Ã');
END IF;
#capital a, umlaut mark
IF INSTR(x, '&Auml;')
THEN SET TextString = REPLACE(TextString, '&Auml;','Ä');
END IF;
#capital a, ring
IF INSTR(x, '&Aring;')
THEN SET TextString = REPLACE(TextString, '&Aring;','Å');
END IF;
#capital ae
IF INSTR(x, '&AElig;')
THEN SET TextString = REPLACE(TextString, '&AElig;','Æ');
END IF;
#capital c, cedilla
IF INSTR(x, '&Ccedil;')
THEN SET TextString = REPLACE(TextString, '&Ccedil;','Ç');
END IF;
#capital e, grave accent
IF INSTR(x, '&Egrave;')
THEN SET TextString = REPLACE(TextString, '&Egrave;','È');
END IF;
#capital e, acute accent
IF INSTR(x, '&Eacute;')
THEN SET TextString = REPLACE(TextString, '&Eacute;','É');
END IF;
#capital e, circumflex accent
IF INSTR(x, '&Ecirc;')
THEN SET TextString = REPLACE(TextString, '&Ecirc;','Ê');
END IF;
#capital e, umlaut mark
IF INSTR(x, '&Euml;')
THEN SET TextString = REPLACE(TextString, '&Euml;','Ë');
END IF;
#capital i, grave accent
IF INSTR(x, '&Igrave;')
THEN SET TextString = REPLACE(TextString, '&Igrave;','Ì');
END IF;
#capital i, acute accent
IF INSTR(x, '&Iacute;')
THEN SET TextString = REPLACE(TextString, '&Iacute;','Í');
END IF;
#capital i, circumflex accent
IF INSTR(x, '&Icirc;')
THEN SET TextString = REPLACE(TextString, '&Icirc;','Î');
END IF;
#capital i, umlaut mark
IF INSTR(x, '&Iuml;')
THEN SET TextString = REPLACE(TextString, '&Iuml;','Ï');
END IF;
#capital eth, Icelandic
IF INSTR(x, '&ETH;')
THEN SET TextString = REPLACE(TextString, '&ETH;','Ð');
END IF;
#capital n, tilde
IF INSTR(x, '&Ntilde;')
THEN SET TextString = REPLACE(TextString, '&Ntilde;','Ñ');
END IF;
#capital o, grave accent
IF INSTR(x, '&Ograve;')
THEN SET TextString = REPLACE(TextString, '&Ograve;','Ò');
END IF;
#capital o, acute accent
IF INSTR(x, '&Oacute;')
THEN SET TextString = REPLACE(TextString, '&Oacute;','Ó');
END IF;
#capital o, circumflex accent
IF INSTR(x, '&Ocirc;')
THEN SET TextString = REPLACE(TextString, '&Ocirc;','Ô');
END IF;
#capital o, tilde
IF INSTR(x, '&Otilde;')
THEN SET TextString = REPLACE(TextString, '&Otilde;','Õ');
END IF;
#capital o, umlaut mark
IF INSTR(x, '&Ouml;')
THEN SET TextString = REPLACE(TextString, '&Ouml;','Ö');
END IF;
#capital o, slash
IF INSTR(x, '&Oslash;')
THEN SET TextString = REPLACE(TextString, '&Oslash;','Ø');
END IF;
#capital u, grave accent
IF INSTR(x, '&Ugrave;')
THEN SET TextString = REPLACE(TextString, '&Ugrave;','Ù');
END IF;
#capital u, acute accent
IF INSTR(x, '&Uacute;')
THEN SET TextString = REPLACE(TextString, '&Uacute;','Ú');
END IF;
#capital u, circumflex accent
IF INSTR(x, '&Ucirc;')
THEN SET TextString = REPLACE(TextString, '&Ucirc;','Û');
END IF;
#capital u, umlaut mark
IF INSTR(x, '&Uuml;')
THEN SET TextString = REPLACE(TextString, '&Uuml;','Ü');
END IF;
#capital y, acute accent
IF INSTR(x, '&Yacute;')
THEN SET TextString = REPLACE(TextString, '&Yacute;','Ý');
END IF;
#capital THORN, Icelandic
IF INSTR(x, '&THORN;')
THEN SET TextString = REPLACE(TextString, '&THORN;','Þ');
END IF;
#small sharp s, German
IF INSTR(x, '&szlig;')
THEN SET TextString = REPLACE(TextString, '&szlig;','ß');
END IF;
#small a, grave accent
IF INSTR(x, '&agrave;')
THEN SET TextString = REPLACE(TextString, '&agrave;','à');
END IF;
#small a, acute accent
IF INSTR(x, '&aacute;')
THEN SET TextString = REPLACE(TextString, '&aacute;','á');
END IF;
#small a, circumflex accent
IF INSTR(x, '&acirc;')
THEN SET TextString = REPLACE(TextString, '&acirc;','â');
END IF;
#small a, tilde
IF INSTR(x, '&atilde;')
THEN SET TextString = REPLACE(TextString, '&atilde;','ã');
END IF;
#small a, umlaut mark
IF INSTR(x, '&auml;')
THEN SET TextString = REPLACE(TextString, '&auml;','ä');
END IF;
#small a, ring
IF INSTR(x, '&aring;')
THEN SET TextString = REPLACE(TextString, '&aring;','å');
END IF;
#small ae
IF INSTR(x, '&aelig;')
THEN SET TextString = REPLACE(TextString, '&aelig;','æ');
END IF;
#small c, cedilla
IF INSTR(x, '&ccedil;')
THEN SET TextString = REPLACE(TextString, '&ccedil;','ç');
END IF;
#small e, grave accent
IF INSTR(x, '&egrave;')
THEN SET TextString = REPLACE(TextString, '&egrave;','è');
END IF;
#small e, acute accent
IF INSTR(x, '&eacute;')
THEN SET TextString = REPLACE(TextString, '&eacute;','é');
END IF;
#small e, circumflex accent
IF INSTR(x, '&ecirc;')
THEN SET TextString = REPLACE(TextString, '&ecirc;','ê');
END IF;
#small e, umlaut mark
IF INSTR(x, '&euml;')
THEN SET TextString = REPLACE(TextString, '&euml;','ë');
END IF;
#small i, grave accent
IF INSTR(x, '&igrave;')
THEN SET TextString = REPLACE(TextString, '&igrave;','ì');
END IF;
#small i, acute accent
IF INSTR(x, '&iacute;')
THEN SET TextString = REPLACE(TextString, '&iacute;','í');
END IF;
#small i, circumflex accent
IF INSTR(x, '&icirc;')
THEN SET TextString = REPLACE(TextString, '&icirc;','î');
END IF;
#small i, umlaut mark
IF INSTR(x, '&iuml;')
THEN SET TextString = REPLACE(TextString, '&iuml;','ï');
END IF;
#small eth, Icelandic
IF INSTR(x, '&eth;')
THEN SET TextString = REPLACE(TextString, '&eth;','ð');
END IF;
#small n, tilde
IF INSTR(x, '&ntilde;')
THEN SET TextString = REPLACE(TextString, '&ntilde;','ñ');
END IF;
#small o, grave accent
IF INSTR(x, '&ograve;')
THEN SET TextString = REPLACE(TextString, '&ograve;','ò');
END IF;
#small o, acute accent
IF INSTR(x, '&oacute;')
THEN SET TextString = REPLACE(TextString, '&oacute;','ó');
END IF;
#small o, circumflex accent
IF INSTR(x, '&ocirc;')
THEN SET TextString = REPLACE(TextString, '&ocirc;','ô');
END IF;
#small o, tilde
IF INSTR(x, '&otilde;')
THEN SET TextString = REPLACE(TextString, '&otilde;','õ');
END IF;
#small o, umlaut mark
IF INSTR(x, '&ouml;')
THEN SET TextString = REPLACE(TextString, '&ouml;','ö');
END IF;
#small o, slash
IF INSTR(x, '&oslash;')
THEN SET TextString = REPLACE(TextString, '&oslash;','ø');
END IF;
#small u, grave accent
IF INSTR(x, '&ugrave;')
THEN SET TextString = REPLACE(TextString, '&ugrave;','ù');
END IF;
#small u, acute accent
IF INSTR(x, '&uacute;')
THEN SET TextString = REPLACE(TextString, '&uacute;','ú');
END IF;
#small u, circumflex accent
IF INSTR(x, '&ucirc;')
THEN SET TextString = REPLACE(TextString, '&ucirc;','û');
END IF;
#small u, umlaut mark
IF INSTR(x, '&uuml;')
THEN SET TextString = REPLACE(TextString, '&uuml;','ü');
END IF;
#small y, acute accent
IF INSTR(x, '&yacute;')
THEN SET TextString = REPLACE(TextString, '&yacute;','ý');
END IF;
#small thorn, Icelandic
IF INSTR(x, '&thorn;')
THEN SET TextString = REPLACE(TextString, '&thorn;','þ');
END IF;
#small y, umlaut mark
IF INSTR(x, '&yuml;')
THEN SET TextString = REPLACE(TextString, '&yuml;','ÿ');
END IF;
RETURN TextString;
END$$
DELIMITER ;
-- Utilisateurs
DELETE FROM mvd_joomla2.gltb5_usergroups;
DELETE FROM gltb5_users;
DELETE FROM gltb5_comprofiler;
DELETE FROM gltb5_user_usergroup_map;
DELETE FROM gltb5_kunena_categories;
DELETE FROM gltb5_kunena_aliases;
DELETE FROM gltb5_kunena_topics;
DELETE FROM gltb5_kunena_messages;
DELETE FROM gltb5_kunena_messages_text;
-- ALTER TABLE gltb5_users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- ALTER TABLE gltb5_kunena_categories CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- ALTER TABLE gltb5_kunena_aliases CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- ALTER TABLE gltb5_kunena_topics CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- ALTER TABLE gltb5_kunena_messages CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- ALTER TABLE gltb5_kunena_messages_text CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
INSERT INTO mvd_joomla2.gltb5_usergroups
(id, parent_id, lft, rgt, title)
VALUES
(1, 0, 0, 0, 'Public'),
(2, 1, 0, 0, 'Enregistré'),
(3, 2, 0, 0, 'Auteur'),
(4, 3, 0, 0, 'Rédacteur'),
(5, 4, 0, 0, 'Validateur'),
(6, 1, 0, 0, 'Gestionnaire'),
(7, 6, 0, 0, 'Administrateur'),
(8, 1, 0, 0, 'Super Utilisateur'),
(9, 1, 0, 0, 'Invité');
INSERT INTO mvd_joomla2.gltb5_usergroups (
id,
parent_id,
lft,
rgt,
title
) SELECT
-- Ajouter 10 pour la continuité
userclass_id+15 AS id,
2 AS parent_id,
0 AS lft,
0 AS rgt,
userclass_name AS title
FROM docksuserclass_classes;
INSERT INTO gltb5_users (
id,
name,
username,
email,
password,
block,
sendEmail,
registerDate,
lastvisitDate,
activation,
requireReset,
params
) SELECT
user_id AS id,
user_name AS name,
user_loginname AS username,
user_email AS email,
user_password AS password,
user_ban AS block,
0 AS sendEmail,
FROM_UNIXTIME(user_join) AS registerDate,
FROM_UNIXTIME(user_lastvisit) AS lastVisitDate,
1 AS activation,
0 AS requireReset,
'' AS params
FROM docksuser;
INSERT INTO gltb5_user_usergroup_map (
group_id,
user_id
) SELECT
2,
user_id
FROM docksuser;
INSERT INTO gltb5_user_usergroup_map (
group_id,
user_id
) SELECT
8,
user_id
FROM docksuser WHERE user_admin = 1 OR user_name IN ('Raoul', 'Jerome Blu');
INSERT INTO gltb5_user_usergroup_map (
group_id,
user_id
) SELECT
c.userclass_id+15 AS group_id,
user_id
FROM docksuser u INNER JOIN docksuserclass_classes c
ON u.user_class = c.userclass_id
OR u.user_class LIKE CONCAT('%,', c.userclass_id, ',%')
OR u.user_class LIKE CONCAT(c.userclass_id, ',%')
OR u.user_class LIKE CONCAT('%,', c.userclass_id);
INSERT INTO gltb5_comprofiler (
id,
user_id,
firstname,
middlename,
lastname,
hits,
message_last_sent,
message_number_sent,
avatar,
avatarapproved,
canvas,
canvasapproved,
approved,
confirmed,
lastupdatedate,
registeripaddr,
cbactivation,
banned,
banneddate,
unbanneddate,
bannedby,
unbannedby,
bannedreason,
acceptedterms,
cb_genre,
cb_mvd_groupes,
cb_anniversaire,
cb_nombredenfants,
cb_association,
cb_aproposdemoi
) SELECT
u.user_id AS id,
u.user_id AS user_id,
ue.user_Prenom AS firstname,
NULL AS middlename,
ue.user_Nom AS lastname,
u.user_visits AS hits,
'0000-00-00' AS message_last_sent,
u.user_forums AS message_number_sent,
NULL AS avatar,
1 AS avatarapproved,
NULL AS canvas,
1 AS canvasapproved,
1 AS approved,
1 AS confirmed,
FROM_UNIXTIME(u.user_currentvisit) AS lastupdatedate,
u.user_ip AS registeripaddr,
'' AS cbactivation,
0 AS banned,
NULL AS banneddate,
NULL AS unbanneddate,
NULL AS bannedby,
NULL AS unbannedby,
NULL AS bannedreason,
1 AS acceptedterms,
'' AS cb_genre,
'' AS cb_mvd_groupes,
ue.user_birthday AS cb_anniversaire,
ue.user_enfants AS cb_nombredenfants,
0 AS cb_association,
u.user_signature AS cb_aproposdemoi
FROM docksuser u INNER JOIN docksuser_extended ue ON u.user_id=ue.user_extended_id;
INSERT INTO gltb5_kunena_categories
(
id,
parent_id,
name,
alias,
icon,
icon_id,
locked,
accesstype,
access,
pub_access,
pub_recurse,
admin_access,
admin_recurse,
ordering,
published,
checked_out,
checked_out_time,
review,
allow_anonymous,
post_anonymous,
hits,
description,
headerdesc,
class_sfx,
allow_polls,
topic_ordering,
numTopics,
numPosts,
last_topic_id,
last_post_id,
last_post_time,
params
) SELECT
forum_id AS id,
forum_parent AS parent_id,
HTML_UnEncode(forum_name) AS name,
forum_id AS alias,
'fa fa-list' as icon,
0 AS icon_id,
0 AS locked,
'joomla.level' AS accesstype,
1 AS access,
1 AS pub_access,
1 AS pub_recurse,
8 AS admin_access,
1 AS admin_recurse,
forum_order AS ordering,
1 AS published,
0 AS checked_out,
'0000-00-00 00:00:00' AS checked_out_time,
0 AS review,
0 AS allow_anonymous,
0 AS post_anonymous,
0 AS hits,
HTML_UnEncode(forum_description) as description,
'' AS headerdesc,
'' AS class_sfx,
1 AS allow_polls,
'lastpost' AS topic_ordering,
forum_threads AS numTopics,
forum_replies AS numPosts,
0 AS last_topic_id,
0 AS last_post_id,
0 AS last_post_time,
'{}' AS params
FROM docksforum
ORDER BY forum_id;
INSERT INTO gltb5_kunena_aliases(alias, type, item, state)
SELECT alias, 'catid', id, 0
FROM gltb5_kunena_categories;
INSERT INTO gltb5_kunena_topics
(
id,
category_id,
subject,
icon_id,
locked,
hold,
ordering,
posts,
hits,
attachments,
poll_id,
moved_id,
first_post_id,
first_post_time,
first_post_userid,
first_post_message,
first_post_guest_name,
last_post_id,
last_post_time,
last_post_userid,
last_post_message,
last_post_guest_name,
params
)
SELECT
thread_id as id,
thread_forum_id as category_id,
HTML_UnEncode(thread_name) as subject,
0 AS icon_id,
0 AS locked,
0 AS hold,
0 AS ordering,
thread_total_replies + 1 AS posts,
thread_views AS hits,
0 AS attachments,
0 AS poll_id,
0 AS moved_id,
thread_id AS first_post_id,
thread_datestamp AS first_post_time,
SUBSTRING_INDEX(thread_user, '.', 1) AS first_post_userid,
HTML_UnEncode(thread_thread) AS first_post_message,
SUBSTRING_INDEX(thread_user, '.', -1) AS first_post_guest_name,
IFNULL((SELECT thread_id FROM docksforum_t TMP WHERE TMP.thread_parent=T.thread_id ORDER BY thread_datestamp DESC LIMIT 1), thread_id) AS last_post_id,
thread_lastpost AS last_post_time,
SUBSTRING_INDEX(thread_lastuser, '.', 1) AS last_post_userid,
IFNULL((SELECT thread_thread FROM docksforum_t TMP WHERE TMP.thread_parent=T.thread_id ORDER BY thread_datestamp DESC LIMIT 1), thread_thread) AS last_post_message,
SUBSTRING_INDEX(thread_lastuser, '.', -1) AS last_post_guest_name,
'' AS params
FROM docksforum_t as T
WHERE thread_parent=0;
INSERT INTO gltb5_kunena_messages
(
id,
parent,
thread,
catid,
name,
userid,
email,
subject,
time,
ip,
topic_emoticon,
locked,
hold,
ordering,
hits,
moved
)
SELECT
thread_id AS id,
thread_parent AS parent,
IF(thread_parent > 0, thread_parent, thread_id) AS thread,
thread_forum_id AS catid,
SUBSTRING_INDEX(thread_user, '.', -1) AS name,
SUBSTRING_INDEX(thread_user, '.', 1) AS userid,
'' AS email,
HTML_UnEncode(thread_name) AS subject,
thread_datestamp AS time,
'' AS ip,
0 AS topic_emoticon,
0 AS locked,
0 AS hold,
0 AS ordering,
thread_views AS hits,
0 AS moved
FROM docksforum_t as T;
INSERT INTO gltb5_kunena_messages_text
(
mesid,
message
)
SELECT
thread_id AS mesid,
HTML_UnEncode(thread_thread) AS message
FROM docksforum_t as T;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment