Question [Solved] Migration phpbb3 to Kunena 5.0.14

SET @@SESSION.sql_mode='ALLOW_INVALID_DATES';
...
...
SET alias = REPLACE(REPLACE(LCASE(REPLACE(CONVERT(TRIM(str) USING ascii), '?', '')),' ','-'), '&','and');
...
...
...
#quotation mark
IF INSTR( x , '"' )
THEN SET TextString = REPLACE(TextString, '"','"') ;
END IF ;

#period
IF INSTR( x , '.' )
THEN SET TextString = REPLACE(TextString, '.','.') ;
END IF ;
...
...
...

IF(p_id <> 0) THEN
SELECT
concat(alias, '-', teamno10forum.get_alias(teamno10forum.HTML_UnEncode(forum_name)))
...
...
...

BEGIN
DECLARE str text;
set str = message;

set str = replace(str, '<a class="postlink" href="', '[url=');
set str = replace(str, '">', ']');
set str = replace(str, '</a>', '[/url]');
...
...

SET tag_pos = INSTR(str, CONCAT('', tag_name));
SET pos = LOCATE(']', str, tag_pos);



SET str = REPLACE(str, pattern, CONCAT(pattern, ' post="', post_id, '"'));
...
...
...
IF(msg LIKE '%<a%>%</a>%') THEN
SET msg = teamno10forum.fix_url(msg);
END IF;

IF(msg LIKE '%[code%') THEN
SET msg = teamno10forum.fix_tag(msg, 'code');
END IF;

IF(msg LIKE '%[b%') THEN
SET msg = teamno10forum.fix_tag(msg, 'b');
END IF;

IF(msg LIKE '%[img%') THEN
SET msg = teamno10forum.fix_tag(msg, 'img');
END IF;

IF(msg LIKE '%[quote%') THEN
SET msg = teamno10forum.fix_tag(msg, 'quote');
END IF;

SET @sql = concat('ALTER TABLE ', schemaname, '.', tablename, ' ADD ', columnname, ' ', datatype, ';');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
...
...
...
CALL teamno10forum.create_missing_columns('teamno10forum', 'phpbb_users', 'user_from', 'varchar(256)');
...
...
...


DELETE FROM test_kunena.qb9ug_kunena_messages_text;
DELETE FROM test_kunena.qb9ug_kunena_messages;
DELETE FROM test_kunena.qb9ug_kunena_topics;
DELETE FROM test_kunena.qb9ug_kunena_aliases;
DELETE FROM test_kunena.qb9ug_kunena_categories;
DELETE FROM test_kunena.qb9ug_kunena_attachments;
DELETE FROM test_kunena.qb9ug_kunena_users;
DELETE FROM test_kunena.qb9ug_kunena_ranks;

DELETE FROM test_kunena.qb9ug_user_usergroup_map;
DELETE FROM test_kunena.qb9ug_users;

INSERT INTO test_kunena.qb9ug_users(name, username,password,params) VALUES ('Admin', 'mixerp','d2064d358136996bd22421584a7cb33e:trd7TvKHx6dMeoMmBVxYmg0vuXEA4199', '');
INSERT INTO test_kunena.qb9ug_user_usergroup_map(user_id, group_id) VALUES (LAST_INSERT_ID(),'8');

...
...

SELECT
forum_id,
parent_id,
REPLACE(forum_name, '&amp;', '&'),
teamno10forum.get_forum_alias(forum_id) AS alias,
1 AS icon_id,
0 AS locked,
'test_kunena.level' AS accesstype,
1 AS access,
1 AS pub_access,
1 AS pub_recurse,
8 AS admin_access,
1 AS admin_recurse,
left_id 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,
forum_desc,
'' AS headerdesc,
'' AS class_sfx,
1 AS allow_polls,
'lastpost' AS topic_ordering,
forum_topics AS forum_topics,
forum_posts AS forum_posts,
teamno10forum.get_topic_id(forum_last_post_id) AS last_topic_id,
forum_last_post_id,
forum_last_post_time,
'{}' AS params
FROM teamno10forum.phpbb_forums
ORDER BY forum_id;


INSERT INTO test_kunena.qb9ug_kunena_aliases(alias, type, item, state)
SELECT alias, 'catid', id, 0
FROM test_kunena.qb9ug_kunena_categories;
...
...
...
SELECT
attach_id AS id,
post_msg_id AS mesid,
poster_id AS userid,
md5(physical_filename) AS hash,
filesize AS size,
'media/kunena/attachments/migrated' AS folder,
mimetype AS filetype,
CONCAT(physical_filename, '.jpg') AS filename
FROM teamno10forum.phpbb_attachments;

/*
The user_id->1 means "Anonymous" user in default phpBB installation.
Kunena, on the other hand, wants user_id->0 for Guest posts.
*/

UPDATE test_kunena.qb9ug_kunena_messages SET userid=0 WHERE userid=1;

UPDATE test_kunena.qb9ug_kunena_messages
INNER JOIN teamno10forum.phpbb_posts
ON teamno10forum.phpbb_posts.post_id = test_kunena.qb9ug_kunena_messages.id
SET test_kunena.qb9ug_kunena_messages.name = teamno10forum.phpbb_posts.post_username;

/*********************************************
Data migration is complete.
**********************************************/
UPDATE test_kunena.qb9ug_kunena_categories
SET numTopics = test_kunena.count_topics(id);

UPDATE test_kunena.qb9ug_kunena_categories
SET numPosts = test_kunena.count_category_posts(id);

UPDATE test_kunena.qb9ug_kunena_topics
SET test_kunena.qb9ug_kunena_topics.posts = test_kunena.count_posts(id);

/*********************************************
Statistics recounted.
**********************************************/


UPDATE
test_kunena.qb9ug_kunena_messages_text
SET message = replace(message, '<!-- m -->', '');


/*********************************************
Convert Emoticons to Kunena
**********************************************/


UPDATE
test_kunena.qb9ug_kunena_messages_text
SET message = replace(message, '<!-- s:-) --><img src="{SMILIES_PATH}/icon_e_smile.gif" alt="" title="Smile" /><!-- s:-) -->', '');

UPDATE
test_kunena.qb9ug_kunena_messages_text
SET message = replace(message, '<!-- s:-) --><img src="{SMILIES_PATH}/icon_smile.gif" alt="" title="Smile" /><!-- s:-) -->', '');
...
...
...