importing-from-kunena-3-to-discourse-2

kunena3.rb

 require "mysql2"

 require File.expand_path(File.dirname(__FILE__) + "/base.rb")

# If you change this script's functionality, please consider making a note here:
# https://meta.discourse.org/t/importing-from-kunena-3/	

# Before running this script, paste these lines into your shell,
# then use arrow keys to edit the values	
=begin
export DB_HOST="localhost"
export DB_NAME="forums"
export DB_USER="root"

export DB_PW="notreallymypassword"
export KUNENA_PREFIX="qhzr6_"
export JOOMLA_PREFIX_USERS="qhzr6_"
export KUNENA_PREFIX_USERS="qhzr6_kunena_"
#export IMAGE_PREFIX="https://suitecrm.com/suitecrm/media/kunena/attachments"
export IMAGE_PREFIX="/uploads/migrated/kunena/attachments"
export PARENT_FIELD="parent_id"
=end

class ImportScripts::Kunena < ImportScripts::Base

  DB_HOST ||= ENV['DB_HOST'] || "localhost"
  DB_NAME ||= ENV['DB_NAME'] || "kunena"
  DB_USER ||= ENV['DB_USER'] || "kunena"
  DB_PW   ||= ENV['DB_PW'] || "kunena"
  KUNENA_PREFIX ||= ENV['KUNENA_PREFIX'] || "jos_" # "iff_" sometimes
  JOOMLA_PREFIX_USERS ||= ENV['JOOMLA_PREFIX_USERS'] || "jos_" # "iff_" sometimes
  KUNENA_PREFIX_USERS ||= ENV['KUNENA_PREFIX_USERS'] || "jos_" # "iff_" sometimes
  IMAGE_PREFIX ||= ENV['IMAGE_PREFIX'] || "http://EXAMPLE.com/media/kunena/attachments"
  PARENT_FIELD ||= ENV['PARENT_FIELD'] || "parent_id" # "parent" in some versions

#    (?:.*)([\/])(\d*.)-(.*)?(\w*)[=](\d+)#(\d+)          post/\2/\5/\6
#    (?:.*)([\/])(?'topicid'\d*.)-(.[^\?]*)(?'parm'\?(\w*)[=](?'start'\d+))?(?:#|\/)?(?'postid'\d+)?     post/${topicid}/${postid}/${start}
#    (?:.*)([\/])(?'topicid'\d*.)-(.[^\/#\?]*)(?'parm'\?(\w*)[=](?'start'\d+))?(?:#\|\/unread|\/reply|\/edit\/|\/)?(?'postid'\d+)?
#    post.${topicid}.${postid}.${start}
#  NORM0 = '/(?:.*)(\/)(?<topicid>\d*.)-(.[^\/#\?]*)(?<parm>\?(\w*)[=](?<start>\d+))?(?:#)?(?<postid>\d+)?/normalized.\k<topicid>.\k<postid>'
  NORM1 = '/(?:.*)(\/)(?<topicid>\d*.)-(.[^\/#\?]*)(?<parm>\?(\w*)[=](?<start>\d+))?(?:\/)?(?<postid>\d+)?/normalized.\k<topicid>.\k<postid>'
  NORM2 = '/(?:.*)(\/)(?<topicid>\d*.)-(.[^\/#\?]*)(?<parm>\?(\w*)[=](?<start>\d+))?(?:\/unread)?(?<postid>\d+)?/normalized.\k<topicid>.\k<postid>'
  NORM3 = '/(?:.*)(\/)(?<topicid>\d*.)-(.[^\/#\?]*)(?<parm>\?(\w*)[=](?<start>\d+))?(?:\/reply)?(?<postid>\d+)?/normalized.\k<topicid>.\k<postid>'
  NORM4 = '/(?:.*)(\/)(?<topicid>\d*.)-(.[^\/#\?]*)(?<parm>\?(\w*)[=](?<start>\d+))?(?:\/edit\/)?(?<postid>\d+)?/normalized.\k<topicid>.\k<postid>'

  NORM = '/(?:.*)(\/)(?<topicid>\d*.)-(.[^\/#\?]*)(?<parm>\?(\w*)[=](?<start>\d+))?(?:\/)?(\D+(\/)?)?(?<postid>\d+)?(?:\/)?/normalized.\k<topicid>.\k<postid>'

  def initialize
    @mytopics = {}
    @topics_map = {}
    @posts_map = {}
    puts "Hello 1"
    super
    puts "Hello 2"

    Permalink.destroy_all # I want a clean slate
    my_add_normalizations

    @users = {}
    puts DB_USER
    puts PARENT_FIELD
    @client = Mysql2::Client.new(
      host: DB_HOST,
      username: DB_USER,
      password: DB_PW,
      database: DB_NAME
    )
  end

  def my_add_normalizations
    normalizations = SiteSetting.permalink_normalizations
    normalizations = normalizations.blank? ? [] : normalizations.split('|')
    add_normalization(normalizations, NORM)
#    add_normalization(normalizations, NORM1)
#    add_normalization(normalizations, NORM2)
#    add_normalization(normalizations, NORM3)
#    add_normalization(normalizations, NORM4)
 #   add_normalization(normalizations, NORM5)

    SiteSetting.permalink_normalizations = normalizations.join('|')
    #abort("Script intentionally aborted.")
  end

  def execute
    #pg_conn = PG.connect(db_name: 'discourse_development')
    #conn = MiniSql::Connection.get(pg_conn)
    puts "Hello 3"
    #create_import_topic_id("1", "333")

    import_watched
    abort("Script intentionally aborted.")

    parse_users

    puts "creating users"

    create_users(@users) do |id, user|
      { id: id,
        email: user[:email],
        username: user[:username],
        created_at: user[:created_at],
        bio_raw: user[:bio],
        moderator: user[:moderator] ? true : false,
        admin: user[:admin] ? true : false,
        suspended_at: user[:suspended] ? Time.zone.now : nil,
        suspended_till: user[:suspended] ? 100.years.from_now : nil,
        password: user['password_hash'] }
    end
    @users = nil

    create_categories(@client.query("SELECT id, #{PARENT_FIELD} as parent_id, name, description, ordering FROM  #{KUNENA_PREFIX_USERS}categories WHERE published=1 ORDER BY #{PARENT_FIELD}, id;")) do |c|
      h = { id: c['id'], name: c['name'], description: c['description'], position: c['ordering'].to_i }
      if c['parent_id'].to_i > 0
        h[:parent_category_id] = category_id_from_imported_category_id(c['parent_id'])
      end
      h
    end

    import_posts

    begin
      create_admin(email: 'CHANGE@ME.COM', username: UserNameSuggester.suggest('CHAMGEME'))
    rescue => e
      puts '', "Failed to create admin user"
      puts e.message
    end
  end

  def parse_users
    # Need to merge data from joomla with kunena

    puts "fetching Joomla users data from mysql"
    results = @client.query("
       SELECT id, username, email, registerDate, password, block
           FROM #{JOOMLA_PREFIX_USERS}users u
           JOIN #{KUNENA_PREFIX_USERS}users ku
           ON u.id = ku.userid
           WHERE (ku.posts > 0) or ((u.block = 0)) and (left((u.lastvisitDate),4)='2018' OR left((u.lastvisitDate),4)='2019')
           ;
              ", cache_rows: false)
    results.each do |u|
      next unless u['id'].to_i > (0) && u['username'].present? && u['email'].present?
      next if u['username'] == 'SMD19' #ignore 1000+ dummy users in our database
      #username = u['username'].gsub(' ', '_').gsub(/[^A-Za-z0-9_]/, '')[0, User.username_length.end]
      #if username.length < User.username_length.first
      #  username = username * User.username_length.first
      #end
      @users[u['id'].to_i] = {
          id: u['id'].to_i,
          #username: username,
          username: u['username'],
          email: u['email'],
          created_at: u['registerDate'],
          password_hash: u['password']
      }
    end

    puts "fetching Kunena user data from mysql"
    results = @client.query("
         SELECT userid, signature, moderator, banned
         FROM #{KUNENA_PREFIX_USERS}users ku
         JOIN #{JOOMLA_PREFIX_USERS}users u
         ON u.id = ku.userid
         WHERE (ku.posts > 0) or ((u.block = 0)) and (left((u.lastvisitDate),4)='2018' OR left((u.lastvisitDate),4)='2019')
       ;", cache_rows: false)
    results.each do |u|
      next unless u['userid'].to_i > 0
      user = @users[u['userid'].to_i]
      if user
        user[:bio] = u['signature']
        user[:moderator] = (u['moderator'].to_i == 1)
        user[:suspended] = u['banned'].present?
      end
    end
  end

  # Get the Discourse Topic id based on the id of the source topic
  def topic_id_from_imported_topic_id(import_id)
    @topics_map[import_id] || @topics_map[import_id.to_s]
  end

  def import_posts
    puts '', "creating topics and posts"

    total_count = @client.query("
         SELECT COUNT(*) count
         FROM #{KUNENA_PREFIX_USERS}messages m
         WHERE m.hold = 0
      ;").first['count']

    batch_size = 1000

    batches(batch_size) do |offset|
      results = @client.query("
        SELECT m.id id,
               m.thread thread,
               m.parent parent,
               m.catid catid,
               m.userid userid,
               m.subject subject,
               m.time time,
               t.message message
        FROM  #{KUNENA_PREFIX_USERS}messages m,
              #{KUNENA_PREFIX_USERS}messages_text t
        WHERE (m.id = t.mesid) AND
              (m.hold = 0)
        ORDER BY m.id
        LIMIT #{batch_size}
        OFFSET #{offset}
      ;", cache_rows: false)

      break if results.size < 1

      next if all_records_exist? :posts, results.map { |p| p['id'].to_i }

      perms = Hash.new
      interrupt = false # debugger is no good at breaking execution, so set this to true with "evaluate expression", to force abort

      create_posts(results, total: total_count, offset: offset) do |m|
        skip = false
        mapped = {}

        mapped[:id] = m['id']
        mapped[:user_id] = user_id_from_imported_user_id(m['userid']) || -1

        id = m['userid']
#        mapped[:raw] = m["message"].gsub(/\[code\](.+?)\[\/code\]/, "```\n\\1\n```\n")
        mapped[:raw] = m["message"].gsub(/([\s\S]*?)\[code\]([\s\S]*?)\[\/code\]([\s\S]*?)/, "\\1\n[code]\n\\2\n[/code]\n\\3")
        mapped[:raw] = mapped[:raw].gsub(/([\s\S]*)\[quote(="[\w]+")?(.*)\]([\s\S]*)\[\/quote\]([\s\S]*)/, "\\1\n[quote\\2]\n\\4\n[/quote]\n\\5")
        (mapped[:raw] = mapped[:raw].gsub(/(?:\[ol\])?(\s*\[li\](.+)\[\/li\]\s*)(?:\[\/ol\])?/, "1. \\2\n")) unless mapped[:raw].match(/\[ul\]/)
        (mapped[:raw] = mapped[:raw].gsub(/(?:\[ul\])?(\s*\[li\](.+)\[\/li\]\s*)(?:\[\/ul\])?/, "- \\2\n"))  unless mapped[:raw].match(/\[ol\]/)
        mapped[:raw] = mapped[:raw].gsub(/\[attachment=[0-9]+\](.+?)\[\/attachment\]/, "\n\[img\]#{IMAGE_PREFIX}/#{id}/\\1\[/img\]")
        mapped[:created_at] = Time.zone.at(m['time'])

        #          ![2019-09-13_201119|127x68](upload://gv0zOyOxXetBDHmAYhVO2DtLzHp.png)

        if interrupt then abort('Interrupt forced.') end

        thread = @topics_map[(m['thread'])]
        parent = topic_lookup_from_imported_post_id(m['parent'])

        if thread                    #m['parent'] == 0
          # topic already exists
          mapped[:topic_id] = thread
          if parent
            mapped[:reply_to_post_number] = parent[:post_number] if parent[:post_number] > 1
          else
            #puts "Parent post #{m['parent']} doesn't exist. Skipping #{m["id"]}: #{m["subject"][0..40]}"
            #skip = true
          end
        else
          # new topic created
          mapped[:category] = category_id_from_imported_category_id(m['catid'])
          mapped[:title] = m['subject']
        end

        skip ? nil : mapped
      end
      @topics
    end
  end

  protected

  def add_normalization(normalizations, normalization)
    normalizations << normalization unless normalizations.include?(normalization)
  end

  def permalink_exists(url)
    Permalink.find_by(url: url)
  end

end

def import_watched
   puts "fetching watched topic/user pairs"
   sql =  <<~SQL
    SELECT discourse_user_id, discourse_topic_id
    FROM joomla_watched jw
    WHERE
       discourse_user_id IS NOT NULL AND
       discourse_topic_id IS NOT NULL
    ;
  SQL

  DB.query(sql).each do |watched_pairs|
    # if watched_pairs
    #ret  = TopicUser.notification_level_change(watched_pairs.discourse_user_id,
    #                                          watched_pairs.discourse_topic_id,
    #                                           TopicUser.notification_levels[:watching],
    #                                           TopicUser.notification_reasons[:user_interacted])

    ret = TopicUser.change(watched_pairs.discourse_user_id.to_i,
                           watched_pairs.discourse_topic_id.to_i,
                           notification_level: TopicUser.notification_levels[:watching].to_i)

    puts "Watching: user " + watched_pairs.discourse_user_id.to_s + ", topic " + watched_pairs.discourse_topic_id.to_s #, ret.to_s
    #@topics_map[kunena_topic_id] = discourse_topic_id
    # set_topic_notification_level
    # post_args[:topic].notify_watch!(user2)
    # TopicUser.notification_levels[:watching]
    #
  end

 end

 ImportScripts::Kunena.new.perform