Last active
August 31, 2016 23:11
Star
You must be signed in to star a gist
Users and Forum migration from e107 to Joomla+Kunena
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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, '<','<'); | |
END IF; | |
#greater-than | |
IF INSTR(x, '>') | |
THEN SET TextString = REPLACE(TextString, '>','>'); | |
END IF; | |
#non-breaking space | |
IF INSTR(x, ' ') | |
THEN SET TextString = REPLACE(TextString, ' ',' '); | |
END IF; | |
#inverted exclamation mark | |
IF INSTR(x, '¡') | |
THEN SET TextString = REPLACE(TextString, '¡','¡'); | |
END IF; | |
#cent | |
IF INSTR(x, '¢') | |
THEN SET TextString = REPLACE(TextString, '¢','¢'); | |
END IF; | |
#pound | |
IF INSTR(x, '£') | |
THEN SET TextString = REPLACE(TextString, '£','£'); | |
END IF; | |
#currency | |
IF INSTR(x, '¤') | |
THEN SET TextString = REPLACE(TextString, '¤','¤'); | |
END IF; | |
#yen | |
IF INSTR(x, '¥') | |
THEN SET TextString = REPLACE(TextString, '¥','¥'); | |
END IF; | |
#broken vertical bar | |
IF INSTR(x, '¦') | |
THEN SET TextString = REPLACE(TextString, '¦','¦'); | |
END IF; | |
#section | |
IF INSTR(x, '§') | |
THEN SET TextString = REPLACE(TextString, '§','§'); | |
END IF; | |
#spacing diaeresis | |
IF INSTR(x, '¨') | |
THEN SET TextString = REPLACE(TextString, '¨','¨'); | |
END IF; | |
#copyright | |
IF INSTR(x, '©') | |
THEN SET TextString = REPLACE(TextString, '©','©'); | |
END IF; | |
#feminine ordinal indicator | |
IF INSTR(x, 'ª') | |
THEN SET TextString = REPLACE(TextString, 'ª','ª'); | |
END IF; | |
#angle quotation mark (left) | |
IF INSTR(x, '«') | |
THEN SET TextString = REPLACE(TextString, '«','«'); | |
END IF; | |
#negation | |
IF INSTR(x, '¬') | |
THEN SET TextString = REPLACE(TextString, '¬','¬'); | |
END IF; | |
#soft hyphen | |
IF INSTR(x, '­') | |
THEN SET TextString = REPLACE(TextString, '­',''); | |
END IF; | |
#registered trademark | |
IF INSTR(x, '®') | |
THEN SET TextString = REPLACE(TextString, '®','®'); | |
END IF; | |
#spacing macron | |
IF INSTR(x, '¯') | |
THEN SET TextString = REPLACE(TextString, '¯','¯'); | |
END IF; | |
#degree | |
IF INSTR(x, '°') | |
THEN SET TextString = REPLACE(TextString, '°','°'); | |
END IF; | |
#plus-or-minus | |
IF INSTR(x, '±') | |
THEN SET TextString = REPLACE(TextString, '±','±'); | |
END IF; | |
#superscript 2 | |
IF INSTR(x, '²') | |
THEN SET TextString = REPLACE(TextString, '²','²'); | |
END IF; | |
#superscript 3 | |
IF INSTR(x, '³') | |
THEN SET TextString = REPLACE(TextString, '³','³'); | |
END IF; | |
#spacing acute | |
IF INSTR(x, '´') | |
THEN SET TextString = REPLACE(TextString, '´','´'); | |
END IF; | |
#micro | |
IF INSTR(x, 'µ') | |
THEN SET TextString = REPLACE(TextString, 'µ','µ'); | |
END IF; | |
#paragraph | |
IF INSTR(x, '¶') | |
THEN SET TextString = REPLACE(TextString, '¶','¶'); | |
END IF; | |
#middle dot | |
IF INSTR(x, '·') | |
THEN SET TextString = REPLACE(TextString, '·','·'); | |
END IF; | |
#spacing cedilla | |
IF INSTR(x, '¸') | |
THEN SET TextString = REPLACE(TextString, '¸','¸'); | |
END IF; | |
#superscript 1 | |
IF INSTR(x, '¹') | |
THEN SET TextString = REPLACE(TextString, '¹','¹'); | |
END IF; | |
#masculine ordinal indicator | |
IF INSTR(x, 'º') | |
THEN SET TextString = REPLACE(TextString, 'º','º'); | |
END IF; | |
#angle quotation mark (right) | |
IF INSTR(x, '»') | |
THEN SET TextString = REPLACE(TextString, '»','»'); | |
END IF; | |
#fraction 1/4 | |
IF INSTR(x, '¼') | |
THEN SET TextString = REPLACE(TextString, '¼','¼'); | |
END IF; | |
#fraction 1/2 | |
IF INSTR(x, '½') | |
THEN SET TextString = REPLACE(TextString, '½','½'); | |
END IF; | |
#fraction 3/4 | |
IF INSTR(x, '¾') | |
THEN SET TextString = REPLACE(TextString, '¾','¾'); | |
END IF; | |
#inverted question mark | |
IF INSTR(x, '¿') | |
THEN SET TextString = REPLACE(TextString, '¿','¿'); | |
END IF; | |
#multiplication | |
IF INSTR(x, '×') | |
THEN SET TextString = REPLACE(TextString, '×','×'); | |
END IF; | |
#division | |
IF INSTR(x, '÷') | |
THEN SET TextString = REPLACE(TextString, '÷','÷'); | |
END IF; | |
#capital a, grave accent | |
IF INSTR(x, 'À') | |
THEN SET TextString = REPLACE(TextString, 'À','À'); | |
END IF; | |
#capital a, acute accent | |
IF INSTR(x, 'Á') | |
THEN SET TextString = REPLACE(TextString, 'Á','Á'); | |
END IF; | |
#capital a, circumflex accent | |
IF INSTR(x, 'Â') | |
THEN SET TextString = REPLACE(TextString, 'Â','Â'); | |
END IF; | |
#capital a, tilde | |
IF INSTR(x, 'Ã') | |
THEN SET TextString = REPLACE(TextString, 'Ã','Ã'); | |
END IF; | |
#capital a, umlaut mark | |
IF INSTR(x, 'Ä') | |
THEN SET TextString = REPLACE(TextString, 'Ä','Ä'); | |
END IF; | |
#capital a, ring | |
IF INSTR(x, 'Å') | |
THEN SET TextString = REPLACE(TextString, 'Å','Å'); | |
END IF; | |
#capital ae | |
IF INSTR(x, 'Æ') | |
THEN SET TextString = REPLACE(TextString, 'Æ','Æ'); | |
END IF; | |
#capital c, cedilla | |
IF INSTR(x, 'Ç') | |
THEN SET TextString = REPLACE(TextString, 'Ç','Ç'); | |
END IF; | |
#capital e, grave accent | |
IF INSTR(x, 'È') | |
THEN SET TextString = REPLACE(TextString, 'È','È'); | |
END IF; | |
#capital e, acute accent | |
IF INSTR(x, 'É') | |
THEN SET TextString = REPLACE(TextString, 'É','É'); | |
END IF; | |
#capital e, circumflex accent | |
IF INSTR(x, 'Ê') | |
THEN SET TextString = REPLACE(TextString, 'Ê','Ê'); | |
END IF; | |
#capital e, umlaut mark | |
IF INSTR(x, 'Ë') | |
THEN SET TextString = REPLACE(TextString, 'Ë','Ë'); | |
END IF; | |
#capital i, grave accent | |
IF INSTR(x, 'Ì') | |
THEN SET TextString = REPLACE(TextString, 'Ì','Ì'); | |
END IF; | |
#capital i, acute accent | |
IF INSTR(x, 'Í') | |
THEN SET TextString = REPLACE(TextString, 'Í','Í'); | |
END IF; | |
#capital i, circumflex accent | |
IF INSTR(x, 'Î') | |
THEN SET TextString = REPLACE(TextString, 'Î','Î'); | |
END IF; | |
#capital i, umlaut mark | |
IF INSTR(x, 'Ï') | |
THEN SET TextString = REPLACE(TextString, 'Ï','Ï'); | |
END IF; | |
#capital eth, Icelandic | |
IF INSTR(x, 'Ð') | |
THEN SET TextString = REPLACE(TextString, 'Ð','Ð'); | |
END IF; | |
#capital n, tilde | |
IF INSTR(x, 'Ñ') | |
THEN SET TextString = REPLACE(TextString, 'Ñ','Ñ'); | |
END IF; | |
#capital o, grave accent | |
IF INSTR(x, 'Ò') | |
THEN SET TextString = REPLACE(TextString, 'Ò','Ò'); | |
END IF; | |
#capital o, acute accent | |
IF INSTR(x, 'Ó') | |
THEN SET TextString = REPLACE(TextString, 'Ó','Ó'); | |
END IF; | |
#capital o, circumflex accent | |
IF INSTR(x, 'Ô') | |
THEN SET TextString = REPLACE(TextString, 'Ô','Ô'); | |
END IF; | |
#capital o, tilde | |
IF INSTR(x, 'Õ') | |
THEN SET TextString = REPLACE(TextString, 'Õ','Õ'); | |
END IF; | |
#capital o, umlaut mark | |
IF INSTR(x, 'Ö') | |
THEN SET TextString = REPLACE(TextString, 'Ö','Ö'); | |
END IF; | |
#capital o, slash | |
IF INSTR(x, 'Ø') | |
THEN SET TextString = REPLACE(TextString, 'Ø','Ø'); | |
END IF; | |
#capital u, grave accent | |
IF INSTR(x, 'Ù') | |
THEN SET TextString = REPLACE(TextString, 'Ù','Ù'); | |
END IF; | |
#capital u, acute accent | |
IF INSTR(x, 'Ú') | |
THEN SET TextString = REPLACE(TextString, 'Ú','Ú'); | |
END IF; | |
#capital u, circumflex accent | |
IF INSTR(x, 'Û') | |
THEN SET TextString = REPLACE(TextString, 'Û','Û'); | |
END IF; | |
#capital u, umlaut mark | |
IF INSTR(x, 'Ü') | |
THEN SET TextString = REPLACE(TextString, 'Ü','Ü'); | |
END IF; | |
#capital y, acute accent | |
IF INSTR(x, 'Ý') | |
THEN SET TextString = REPLACE(TextString, 'Ý','Ý'); | |
END IF; | |
#capital THORN, Icelandic | |
IF INSTR(x, 'Þ') | |
THEN SET TextString = REPLACE(TextString, 'Þ','Þ'); | |
END IF; | |
#small sharp s, German | |
IF INSTR(x, 'ß') | |
THEN SET TextString = REPLACE(TextString, 'ß','ß'); | |
END IF; | |
#small a, grave accent | |
IF INSTR(x, 'à') | |
THEN SET TextString = REPLACE(TextString, 'à','à'); | |
END IF; | |
#small a, acute accent | |
IF INSTR(x, 'á') | |
THEN SET TextString = REPLACE(TextString, 'á','á'); | |
END IF; | |
#small a, circumflex accent | |
IF INSTR(x, 'â') | |
THEN SET TextString = REPLACE(TextString, 'â','â'); | |
END IF; | |
#small a, tilde | |
IF INSTR(x, 'ã') | |
THEN SET TextString = REPLACE(TextString, 'ã','ã'); | |
END IF; | |
#small a, umlaut mark | |
IF INSTR(x, 'ä') | |
THEN SET TextString = REPLACE(TextString, 'ä','ä'); | |
END IF; | |
#small a, ring | |
IF INSTR(x, 'å') | |
THEN SET TextString = REPLACE(TextString, 'å','å'); | |
END IF; | |
#small ae | |
IF INSTR(x, 'æ') | |
THEN SET TextString = REPLACE(TextString, 'æ','æ'); | |
END IF; | |
#small c, cedilla | |
IF INSTR(x, 'ç') | |
THEN SET TextString = REPLACE(TextString, 'ç','ç'); | |
END IF; | |
#small e, grave accent | |
IF INSTR(x, 'è') | |
THEN SET TextString = REPLACE(TextString, 'è','è'); | |
END IF; | |
#small e, acute accent | |
IF INSTR(x, 'é') | |
THEN SET TextString = REPLACE(TextString, 'é','é'); | |
END IF; | |
#small e, circumflex accent | |
IF INSTR(x, 'ê') | |
THEN SET TextString = REPLACE(TextString, 'ê','ê'); | |
END IF; | |
#small e, umlaut mark | |
IF INSTR(x, 'ë') | |
THEN SET TextString = REPLACE(TextString, 'ë','ë'); | |
END IF; | |
#small i, grave accent | |
IF INSTR(x, 'ì') | |
THEN SET TextString = REPLACE(TextString, 'ì','ì'); | |
END IF; | |
#small i, acute accent | |
IF INSTR(x, 'í') | |
THEN SET TextString = REPLACE(TextString, 'í','í'); | |
END IF; | |
#small i, circumflex accent | |
IF INSTR(x, 'î') | |
THEN SET TextString = REPLACE(TextString, 'î','î'); | |
END IF; | |
#small i, umlaut mark | |
IF INSTR(x, 'ï') | |
THEN SET TextString = REPLACE(TextString, 'ï','ï'); | |
END IF; | |
#small eth, Icelandic | |
IF INSTR(x, 'ð') | |
THEN SET TextString = REPLACE(TextString, 'ð','ð'); | |
END IF; | |
#small n, tilde | |
IF INSTR(x, 'ñ') | |
THEN SET TextString = REPLACE(TextString, 'ñ','ñ'); | |
END IF; | |
#small o, grave accent | |
IF INSTR(x, 'ò') | |
THEN SET TextString = REPLACE(TextString, 'ò','ò'); | |
END IF; | |
#small o, acute accent | |
IF INSTR(x, 'ó') | |
THEN SET TextString = REPLACE(TextString, 'ó','ó'); | |
END IF; | |
#small o, circumflex accent | |
IF INSTR(x, 'ô') | |
THEN SET TextString = REPLACE(TextString, 'ô','ô'); | |
END IF; | |
#small o, tilde | |
IF INSTR(x, 'õ') | |
THEN SET TextString = REPLACE(TextString, 'õ','õ'); | |
END IF; | |
#small o, umlaut mark | |
IF INSTR(x, 'ö') | |
THEN SET TextString = REPLACE(TextString, 'ö','ö'); | |
END IF; | |
#small o, slash | |
IF INSTR(x, 'ø') | |
THEN SET TextString = REPLACE(TextString, 'ø','ø'); | |
END IF; | |
#small u, grave accent | |
IF INSTR(x, 'ù') | |
THEN SET TextString = REPLACE(TextString, 'ù','ù'); | |
END IF; | |
#small u, acute accent | |
IF INSTR(x, 'ú') | |
THEN SET TextString = REPLACE(TextString, 'ú','ú'); | |
END IF; | |
#small u, circumflex accent | |
IF INSTR(x, 'û') | |
THEN SET TextString = REPLACE(TextString, 'û','û'); | |
END IF; | |
#small u, umlaut mark | |
IF INSTR(x, 'ü') | |
THEN SET TextString = REPLACE(TextString, 'ü','ü'); | |
END IF; | |
#small y, acute accent | |
IF INSTR(x, 'ý') | |
THEN SET TextString = REPLACE(TextString, 'ý','ý'); | |
END IF; | |
#small thorn, Icelandic | |
IF INSTR(x, 'þ') | |
THEN SET TextString = REPLACE(TextString, 'þ','þ'); | |
END IF; | |
#small y, umlaut mark | |
IF INSTR(x, 'ÿ') | |
THEN SET TextString = REPLACE(TextString, 'ÿ','ÿ'); | |
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