VBulletin 3.8 to Discourse on Docker Migration Test Take Four


 
Thread Tools Search this Thread
The Lounge What is on Your Mind? VBulletin 3.8 to Discourse on Docker Migration Test Take Four
# 22  
Old 03-18-2020
Here is the working ruby import_likes function:

Code:
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

Code:
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)

# 23  
Old 03-18-2020
Code:
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);
}

# 24  
Old 03-18-2020
# 25  
Old 03-18-2020
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:

VBulletin 3.8 to Discourse on Docker Migration Test Take Four-screen-shot-2020-03-18-31944-pmjpg


VBulletin 3.8 to Discourse on Docker Migration Test Take Four-screen-shot-2020-03-18-31655-pmjpg
These 3 Users Gave Thanks to Neo For This Post:
# 26  
Old 03-18-2020
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)
# 27  
Old 03-18-2020
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):

Code:
 <?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.
# 28  
Old 03-18-2020
OK... this new code works as intended:


Code:
  #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:

Code:
# rake posts:delete_all_likes
    36980 / 105653 ( 35.0%)

Login or Register to Ask a Question

Previous Thread | Next Thread

7 More Discussions You Might Find Interesting

1. What is on Your Mind?

Under Consideration: Migrate the Forums to Discourse

Dear All, After being active on the Node-RED forum for the last few weeks, I have been very impressed with Discourse, and my eyes have been opened. https://www.discourse.org/ but not the paid /hosted offering, but using the open distribution: https://github.com/discourse/discourse ... (52 Replies)
Discussion started by: Neo
52 Replies

2. What is on Your Mind?

VBulletin 3.8 to Discourse on Docker Migration Test Take Two

OK. Like we all do, we learn a lot from tests, test migrations, and so forth. Today, I started from scratch on test migration 2, armed with a lot more knowledge, The main differences are as follows: Installed discourse plugin ruby-bbcode-to-md before starting the install Modified... (30 Replies)
Discussion started by: Neo
30 Replies

3. Docker

Docker learning Phase-I

Hello All, I had recently learnt a bit of Docker(which provides containerization process). Here are some of my learning points from it. Let us start first with very basic question: What is Docker: Docker is a platform for sysadmins and developers to DEPLOY, DEVELOP and RUN applications ... (7 Replies)
Discussion started by: RavinderSingh13
7 Replies

4. Shell Programming and Scripting

Problem in extracting yocto SDK for docker

Actually I was facing the following issue while building my Yocto SDK on Docker container sudo docker build --tag="akash/eclipse-che:6.5.0-1" --tag="akash/eclipse-che:latest" /home/akash/dockerimage.yocto.support/ Sending build context to Docker daemon 26.93MB Step 1/5 : FROM eclipse/cpp_gcc ... (3 Replies)
Discussion started by: Akash BHardwaj
3 Replies

5. AIX

AIX - FC Switch migration, SAN Migration question!

I'm New to AIX / VIOS We're doing a FC switch cutover on an ibm device, connected via SAN. How do I tell if one path to my remote disk is lost? (aix lvm) How do I tell when my link is down on my HBA port? Appreciate your help, very much! (4 Replies)
Discussion started by: BG_JrAdmin
4 Replies

6. Linux

Docker and pipework,ip with other subnet

Recently i found this for give to docker a "personal" ip ip addr del 10.1.1.133/24 dev eth0 ip link add link eth0 dev eth0m type macvlan mode bridge ip link set eth0m up ip addr add 10.1.1.133/24 dev eth0m route add default gw 10.1.1.1On container i did ... (0 Replies)
Discussion started by: Linusolaradm1
0 Replies

7. Web Development

Removing VBSEO for vbulletin – Reverting back to vbulletin URLs

Please note, this information was copied from vbseo.com, now showing a database error. This is posted for reference since vbSEO seems to be going out of business: If you ever need to uninstall vBSEO , you can use the following instructions. Make sure you carefully follow each step. Login... (37 Replies)
Discussion started by: Neo
37 Replies
Login or Register to Ask a Question