Well, it looks like I’m almost there. Thanks a lot to Jay for the guidance.
Thanks, this was key, it was actually as simple as copying the permalink part of the Drupal import script itself and changing it to run on posts instead of topics:
## I added permalinks for each Drupal comment (reply) link: /comment/DIGITS#comment-DIGITS
Post.find_each do |post|
begin
pcf = post.custom_fields
if pcf && pcf['import_id']
cid = pcf['import_id'][/cid:(\d+)/, 1]
slug = "/comment/#{cid}" # The #comment-DIGITS part breaks the permalink and isn't needed
Permalink.create(url: slug, post_id: post.id)
end
rescue => e
puts e.message
puts "Permalink creation failed for cid #{post.id}"
end
end
I was stuck for a while with my original attempt that included the relative page #comment-DIGITS part of the original Drupal link, which completely breaks the permalink in Discourse. then I realized that of course the # part of a link doesn’t actually get passed to the webserver and was only needed for Drupal to make it scroll to the part of the page where the specific comment was located. So it works fine without that in Discourse even if coming from an external web page with an old /comment/YYYYYY#comment-YYYYY link, it simply looks like this in Discourse: /comment/YYYYYY → /t/topic-title-words/123456/X and the URL bar shows like: /t/topic-title-words/123456/X#comment-YYYYYY , it doesn’t appear to care about the bogus #comment-YYYYYY part.
For some forums I suspect that the stock Drupal importer postprocess_posts function might actually be enough. It should be noted that it needs to be adjusted for each forum, there’s a rather sloppy hard-coded regexp replace for site.com → community.site.com. But after adjusting that it does a good job of rewriting internal forum links for nodes → topics as well as comments → replies. But I do have a fair number of external websites linking to individual comments (replies) on my forum and it’s worth conserving those. Plus Google indexes most of the 1.7M /comment-YYYYYY URLs and it would probably hurt my ranking if those all disappeared. I hope it won’t cause any problems for Discourse to have ~2M permalinks though?
Thanks a lot, I lifted that function almost without modifications, just had to adjust a few column names. Works great.
def suspend_users
puts '', "updating banned users"
banned = 0
failed = 0
total = mysql_query("SELECT COUNT(*) AS count FROM users WHERE status = 0").first['count']
system_user = Discourse.system_user
mysql_query("SELECT name username, mail email FROM users WHERE status = 0").each do |b|
user = User.find_by_email(b['email'])
if user
user.suspended_at = Time.now
user.suspended_till = 200.years.from_now
if user.save
StaffActionLogger.new(system_user).log_user_suspend(user, "banned during initial import")
banned += 1
else
puts "Failed to suspend user #{user.username}. #{user.errors.try(:full_messages).try(:inspect)}"
failed += 1
end
else
puts "Not found: #{b['email']}"
failed += 1
end
print_status banned + failed, total
end
end
Also worked! I did have to deal with Drupal’s diffuse DB schema and LEFT JOIN profile_value location ON users.uid = location.uid to correlate another table that contains the profile data, but very cool that it’s so easy to add on the Discourse side of things. It’s worth noting that this process runs about 50% slower than stock, I suspect it’s due to the LEFT JOIN. But I can live with it, as I only have about 80K users.
This was fairly hard, once again due to Drupal’s disjointed database schema. I ended up using jforum.rb as the basis with a little help from the Vanilla importer too. The original script was rather paranoid with checking at every single variable pass to make sure the avatar filename isn’t null, so I removed most of those checks to make the code less messy. The worst that can happen is that the script could crash, but with the SQL query I used I don’t think even that could go wrong.
def import_users
puts "", "importing users"
user_count = mysql_query("SELECT count(uid) count FROM users").first["count"]
last_user_id = -1
batches(BATCH_SIZE) do |offset|
users = mysql_query(<<-SQL
SELECT users.uid,
name username,
mail email,
created,
picture,
location.value location
FROM users
LEFT JOIN profile_value location ON users.uid = location.uid
WHERE users.uid > #{last_user_id}
ORDER BY uid
LIMIT #{BATCH_SIZE}
SQL
).to_a
break if users.empty?
last_user_id = users[-1]["uid"]
users.reject! { |u| @lookup.user_already_imported?(u["uid"]) }
create_users(users, total: user_count, offset: offset) do |row|
if row['picture'] > 0
q = mysql_query("SELECT filename FROM file_managed WHERE fid = #{row['picture']};").first
avatar = q["filename"]
end
email = row["email"].presence || fake_email
email = fake_email if !EmailAddressValidator.valid_value?(email)
username = @htmlentities.decode(row["username"]).strip
{
id: row["uid"],
name: username,
email: email,
location: row["location"],
created_at: Time.zone.at(row["created"]),
post_create_action: proc do |user|
import_avatar(user, avatar)
end
}
end
end
end
def import_avatar(user, avatar_source)
return if avatar_source.blank?
path = File.join(ATTACHMENT_DIR, avatar_source)
@uploader.create_avatar(user, path)
end
After your paid help with the SQL query I ended up trying to hack it into the script for Discuz, IPboard, and Xenforo. I kept getting hitting dead ends with each one, I got closest with the Discuz model which appears to have a very similar database schema, but I couldn’t get past a bug with the @first_post_id_by_topic_id instance variable. After tons of trial and error I finally realized that it was improperly initialized at the beginning of the Discuz script (I tried to put it in the same location in the Drupal script) and this finally fixed it:
def initialize
super
@first_post_id_by_topic_id = {}
@htmlentities = HTMLEntities.new
@client = Mysql2::Client.new(
host: "172.17.0.3",
username: "user",
password: "pass",
database: DRUPAL_DB
)
end
…
def import_private_messages
puts '', 'creating private messages'
pm_indexes = 'pm_index'
pm_messages = 'pm_message'
total_count = mysql_query("SELECT count(*) count FROM #{pm_indexes}").first['count']
batches(BATCH_SIZE) do |offset|
results = mysql_query("
SELECT pi.mid id, thread_id, pi.recipient to_user_id, pi.deleted deleted, pm.author user_id, pm.subject subject, pm.body message, pm.format format, pm.timestamp created_at FROM pm_index pi LEFT JOIN pm_message pm ON pi.mid=pm.mid WHERE deleted = 0
LIMIT #{BATCH_SIZE}
OFFSET #{offset};")
break if results.size < 1
# next if all_records_exist? :posts, results.map {|m| "pm:#{m['id']}"}
create_posts(results, total: total_count, offset: offset) do |m|
skip = false
mapped = {}
mapped[:id] = "pm:#{m['id']}"
mapped[:user_id] = user_id_from_imported_user_id(m['user_id']) || -1
mapped[:raw] = preprocess_raw(m['message'],m['format'])
mapped[:created_at] = Time.zone.at(m['created_at'])
thread_id = "pm_#{m['thread_id']}"
if is_first_pm(m['id'], m['thread_id'])
# find the title from list table
# pm_thread = mysql_query("
# SELECT thread_id, subject
# FROM #{table_name 'ucenter_pm_lists'}
# WHERE plid = #{m['thread_id']};").first
mapped[:title] = m['subject']
mapped[:archetype] = Archetype.private_message
# Find the users who are part of this private message.
import_user_ids = mysql_query("
SELECT thread_id plid, recipient user_id
FROM pm_index
WHERE thread_id = #{m['thread_id']};
").map { |r| r['user_id'] }.uniq
mapped[:target_usernames] = import_user_ids.map! do |import_user_id|
import_user_id.to_s == m['user_id'].to_s ? nil : User.find_by(id: user_id_from_imported_user_id(import_user_id)).try(:username)
end.compact
if mapped[:target_usernames].empty? # pm with yourself?
skip = true
puts "Skipping pm:#{m['id']} due to no target"
else
@first_post_id_by_topic_id[thread_id] = mapped[:id]
end
else
parent = topic_lookup_from_imported_post_id(@first_post_id_by_topic_id[thread_id])
if parent
mapped[:topic_id] = parent[:topic_id]
else
puts "Parent post pm thread:#{thread_id} doesn't exist. Skipping #{m["id"]}: #{m["message"][0..40]}"
skip = true
end
end
skip ? nil : mapped
end
end
end
# search for first pm id for the series of pm
def is_first_pm(pm_id, thread_id)
result = mysql_query("
SELECT mid id
FROM pm_index
WHERE thread_id = #{thread_id}
ORDER BY id")
result.first['id'].to_s == pm_id.to_s
end
Oh, and for most of these queries it also requires running this in the MySQL container to disable a strict mode SQL sanity check:
mysql -u root -ppass -e "SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));"
Another thing I realized was missing were a few thousand Drupal nodes of type poll . I first tried to just include WHERE type = 'forum' OR type = 'poll' in the import_topics function, but there is some seriously janky stuff going on in the original Drupal database that causes it to miss many of them. So I ended up copying the import_topics into a new import_polls function:
def import_poll_topics
puts '', "importing poll topics"
polls = mysql_query(<<-SQL
SELECT n.nid nid, n.title title, n.uid uid, n.created created, n.sticky sticky, taxonomy_index.tid tid, node_counter.totalcount views
FROM node n
LEFT JOIN taxonomy_index ON n.nid = taxonomy_index.nid
LEFT JOIN node_counter ON n.nid = node_counter.nid
WHERE n.type = 'poll'
AND n.status = 1
SQL
).to_a
create_posts(polls) do |topic|
{
id: "nid:#{topic['nid']}",
user_id: user_id_from_imported_user_id(topic['uid']) || -1,
category: category_id_from_imported_category_id(topic['tid']),
raw: "### You can see the archived poll results on the Wayback Machine:\n**https://web.archive.org/web/1234567890/http://myforum.com/node/#{topic['nid']}**",
created_at: Time.zone.at(topic['created']),
pinned_at: topic['sticky'].to_i == 1 ? Time.zone.at(topic['created']) : nil,
title: topic['title'].try(:strip),
views: topic['views'],
custom_fields: { import_id: "nid:#{topic['nid']}" }
}
end
end
I don’t care too much about importing the actual poll results, and it would require re-coding the entire algorithm that Drupal uses to tally up all the votes and eliminates duplicates. I mainly just want to import the followup comments in the poll thread. But just in case anyone wants to see the original poll results I made it write out a direct link to the original forum node in the Wayback Machine.
So the code is not at all elegant and probably isn’t very efficient, but for a one-shot deal that should get the job done.
Sorry for the walls of code, let me know if that irritates anyone and I can move them to a pastebin URL.