OK.. it worked... forum vb "thanks" were migrated to discourse "thanks":
I think there may be some counters (like total likes given to a user) which have not updated in the migration; but maybe they will update based on an internal cron. If not, I can easily write code to update the users profile "likes" counter(s).
Here is the working ruby import_likes function:
def import_likes
puts "\nimporting likes..."
# created mysql user_actions table in vb3 using PHP script and included that table with migration dump
sql = "select acting_user_id as user_id, target_post_id as post_id, created_at from user_actions"
results = mysql_query(sql)
puts "length() method form : #{results.count}\n\n"
puts "skip loading unique id map"
existing_map = {}
PostCustomField.where(name: 'import_id').pluck(:post_id, :value).each do |post_id, import_id|
existing_map[import_id] = post_id
#puts "postcustomfield existing_map post_id: #{post_id} import_id #{import_id}\n"
end
puts "loading data into temp table"
#manually created the temp like_data table so I could check the table after session ends
#DB.exec("create temp table like_data(user_id integer, post_id integer, created_at timestamp without time zone)")
puts "like_data temp table created"
PostAction.transaction do
results.each do |result|
result["user_id"] = user_id_from_imported_user_id(result["user_id"].to_s)
result["post_id"] = existing_map[result["post_id"].to_s]
next unless result["user_id"] && result["post_id"]
puts "insert like table user_id: #{result["user_id"]} post_id #{result["post_id"]}\n"
DB.exec("INSERT INTO like_data VALUES (:user_id,:post_id,:created_at)",
user_id: result["user_id"],
post_id: result["post_id"],
created_at: result["created_at"]
)
end
end
puts "creating missing post actions"
DB.exec <<~SQL
INSERT INTO post_actions (post_id, user_id, post_action_type_id, created_at, updated_at)
SELECT l.post_id, l.user_id, 2, l.created_at, l.created_at FROM like_data l
LEFT JOIN post_actions a ON a.post_id = l.post_id AND l.user_id = a.user_id AND a.post_action_type_id = 2
WHERE a.id IS NULL
SQL
puts "creating missing user actions"
DB.exec <<~SQL
INSERT INTO user_actions (user_id, action_type, target_topic_id, target_post_id, acting_user_id, created_at, updated_at)
SELECT pa.user_id, 1, p.topic_id, p.id, pa.user_id, pa.created_at, pa.created_at
FROM post_actions pa
JOIN posts p ON p.id = pa.post_id
LEFT JOIN user_actions ua ON action_type = 1 AND ua.target_post_id = pa.post_id AND ua.user_id = pa.user_id
WHERE ua.id IS NULL AND pa.post_action_type_id = 2
SQL
# reverse action
DB.exec <<~SQL
INSERT INTO user_actions (user_id, action_type, target_topic_id, target_post_id, acting_user_id, created_at, updated_at)
SELECT p.user_id, 2, p.topic_id, p.id, pa.user_id, pa.created_at, pa.created_at
FROM post_actions pa
JOIN posts p ON p.id = pa.post_id
LEFT JOIN user_actions ua ON action_type = 2 AND ua.target_post_id = pa.post_id AND
ua.acting_user_id = pa.user_id AND ua.user_id = p.user_id
WHERE ua.id IS NULL AND pa.post_action_type_id = 2
SQL
puts "updating like counts on posts"
DB.exec <<~SQL
UPDATE posts SET like_count = coalesce(cnt,0)
FROM (
SELECT post_id, count(*) cnt
FROM post_actions
WHERE post_action_type_id = 2 AND deleted_at IS NULL
GROUP BY post_id
) x
WHERE posts.like_count <> x.cnt AND posts.id = x.post_id
SQL
puts "updating like counts on topics"
DB.exec <<-SQL
UPDATE topics SET like_count = coalesce(cnt,0)
FROM (
SELECT topic_id, sum(like_count) cnt
FROM posts
WHERE deleted_at IS NULL
GROUP BY topic_id
) x
WHERE topics.like_count <> x.cnt AND topics.id = x.topic_id
SQL
end
end
mysql table structure from vb3 used in migration:
Note: did not use fields: target_user_id
MariaDB [(none)]> connect vb3;
MariaDB [vb3]> describe user_actions;
+--------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| action_type | int(11) unsigned | NO | | NULL | |
| user_id | int(11) unsigned | NO | | NULL | |
| target_topic_id | int(11) unsigned | YES | | NULL | |
| target_post_id | int(11) unsigned | YES | | NULL | |
| target_user_id | int(11) unsigned | YES | | NULL | |
| acting_user_id | int(11) unsigned | YES | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
+--------------------+------------------+------+-----+---------+----------------+
11 rows in set (0.015 sec)
ubuntu:/var/www/includes/cron# cat thanks_to_discourse.php
<?php
/**************************************************
+-----------+------------+-----------+-----------+
| thanker | unixtime | id | thanked |
+-----------+------------+-----------+-----------+
| 1 | 1584149592 | 303045211 | 302093876 |
| 302153369 | 1584136706 | 303045214 | 302116191 |
| 302108573 | 1584128526 | 303045211 | 302093876 |
| 302153369 | 1584126659 | 303042175 | 302116191 |
| 302153369 | 1584126400 | 303045174 | 302116191 |
| 302153369 | 1584117711 | 303045184 | 1 |
| 37898 | 1584108187 | 303045175 | 1 |
| 302181242 | 1584106664 | 303045201 | 302122047 |
| 302181242 | 1584104642 | 303045074 | 302052697 |
| 302025710 | 1584103722 | 303045184 | 1 |
+-----------+------------+-----------+-----------+
**************************************************/
if(false)
{
$query = 'SELECT SUBSTRING(p.pagetext,1,128) AS snippet, p.threadid AS threadid,t.userid AS thanker,t.date AS unixtime,t.postid AS postid,p.userid AS thanked from post_thanks as t LEFT JOIN post p ON p.postid = t.postid';
}
$query = 'SELECT p.threadid AS threadid,t.userid AS thanker,t.date AS unixtime,t.postid AS postid,p.userid AS thanked from post_thanks as t LEFT JOIN post p ON p.postid = t.postid';
$allthanks = $vbulletin->db->query_read($query);
/****************************************
mysql> describe user_actions;
+-----------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| action_type | int(11) unsigned | NO | | NULL | |
| user_id | int(11) unsigned | NO | | NULL | |
| target_topic_id | int(11) unsigned | YES | | NULL | |
| target_post_id | int(11) unsigned | YES | | NULL | |
| target_user_id | int(11) unsigned | YES | | NULL | |
| acting_user_id | int(11) unsigned | YES | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
+-----------------+------------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
*************************************/
$action_code = 2; // discourse like action == 2
$target_user_id = "";
while ($action = $vbulletin->db->fetch_array($allthanks)){
$update = 'INSERT INTO user_actions (action_type, user_id, target_topic_id, target_post_id, target_user_id,acting_user_id, created_at, updated_at)'.
' VALUES ('.$action_code.','.
'"'.$action['thanked'].'",'.
'"'.$action['threadid'].'",'.
'"'.$action['postid'].'",'.
'"'.$target_user_id.'",'.
'"'.$action['thanker'].'",'.
'FROM_UNIXTIME('.$action['unixtime'].'),'.
'FROM_UNIXTIME('.$action['unixtime'].'))';
$doit = $vbulletin->db->query_write($update);
}
Seems like the "likes" migrations worked well, and most of the profile counters have updated, except mine (for some strange reason, LOL)
The discourse system gives more weight to likes to people who have higher trust status (moderators, leaders, etc), which is a different system than vb3, and don't fully understand it yet.
Also, the total number of posts are different (for a reason I don't yet understand).
For example:
btw, a nice succinct guide to the "markdown" primitives.
btw2, there's no "color-picker" in "discourse"? (I know I'm asking for too much)
Regarding PHP code to create migration table for vb thanks to discourse likes, I have modified the code to make up for a mismatch between how vb3 and discourse deals with the first post in a topic (testing now):
<?php
/**************************************************
+-----------+------------+-----------+-----------+
| thanker | unixtime | id | thanked |
+-----------+------------+-----------+-----------+
| 1 | 1584149592 | 303045211 | 302093876 |
| 302153369 | 1584136706 | 303045214 | 302116191 |
| 302108573 | 1584128526 | 303045211 | 302093876 |
| 302153369 | 1584126659 | 303042175 | 302116191 |
| 302153369 | 1584126400 | 303045174 | 302116191 |
| 302153369 | 1584117711 | 303045184 | 1 |
| 37898 | 1584108187 | 303045175 | 1 |
| 302181242 | 1584106664 | 303045201 | 302122047 |
| 302181242 | 1584104642 | 303045074 | 302052697 |
| 302025710 | 1584103722 | 303045184 | 1 |
+-----------+------------+-----------+-----------+
**************************************************/
$query = 'SELECT p.threadid AS threadid,t.userid AS thanker,t.date AS unixtime,t.postid AS postid,p.userid AS thanked from post_thanks as t LEFT JOIN post p ON p.postid = t.postid';
$allthanks = $vbulletin->db->query_read($query);
/****************************************
mysql> describe user_actions;
+-----------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| action_type | int(11) unsigned | NO | | NULL | |
| user_id | int(11) unsigned | NO | | NULL | |
| target_topic_id | int(11) unsigned | YES | | NULL | |
| target_post_id | varchar(16) | YES | | NULL | |
| target_user_id | int(11) unsigned | YES | | NULL | |
| acting_user_id | int(11) unsigned | YES | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
+-----------------+------------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
*************************************/
$action_code = 2; // discourse like action == 2
$target_user_id = "";
while ($action = $vbulletin->db->fetch_array($allthanks)) {
#code to deal with how discourse tags the first post in a topic in the post_custom_fields table
$query = 'SELECT firstpostid FROM thread WHERE threadid =' . $action['threadid'] . ' LIMIT 1';
$threadinfo = $vbulletin->db->query_first($query);
if ($threadinfo['firstpostid'] == $action['postid']) {
$action['postid'] = 'thread-' . $action['threadid'];
}
$update = 'INSERT INTO user_actions (action_type, user_id, target_topic_id, target_post_id, target_user_id,acting_user_id, created_at, updated_at)' .
' VALUES (' . $action_code . ',' .
'"' . $action['thanked'] . '",' .
'"' . $action['threadid'] . '",' .
'"' . $action['postid'] . '",' .
'"' . $target_user_id . '",' .
'"' . $action['thanker'] . '",' .
'FROM_UNIXTIME(' . $action['unixtime'] . '),' .
'FROM_UNIXTIME(' . $action['unixtime'] . '))';
$doit = $vbulletin->db->query_write($update);
}
Testing now.
OK... this new code works as intended:
#code to deal with how discourse tags the first post in a topic in the post_custom_fields table
$query = 'SELECT firstpostid FROM thread WHERE threadid =' . $action['threadid'] . ' LIMIT 1';
$threadinfo = $vbulletin->db->query_first($query);
if ($threadinfo['firstpostid'] == $action['postid']) {
$action['postid'] = 'thread-' . $action['threadid'];
}
Cleaning up the discourse database up for another test by running:
# rake posts:delete_all_likes
36980 / 105653 ( 35.0%)
There will be no color picker on Community.
Colors cause problems with themes and so when a member chooses a color which looks OK in one theme the same color may not work in another theme.
Community is theme driven optimised for mobile so we will not be providing color pickers or color BBCODE.
This way the future of the site is not constrained by the personal text color choices by members in their posts.
Hi Neo,
We're looking at migrating a big vb 3.8 forum over to discourse and your posts here are very helpful. I wonder if you ever collected all of this knowledge into a single place? I see a take 2, 3, 4 and various auxiliary posts but I'm not finding take 1 (which was probably not named take 1 at the time :). Basically I'd like to get as much benefit as possible from your previous efforts.
Thank you!
Hi @schwa
Nothing to add to my many posts on this topic which you can find from searching these forums and also our original forums (which you can also search):
Wishing you the best of luck in your vB to Discourse migration project!
Neo
