Google Group => Discourse Importer
require 'fileutils'
require 'csv'
require File.expand_path(File.dirname(__FILE__) + "/mbox.rb")
require File.expand_path(File.dirname(__FILE__) + "/base.rb")
=begin
PREREQUISITES and INFORMATION:
COOKIES:
in order to be able to extract users' email addresses correctly from the Google Group, you will need
to have access to a Manager account of the Google Group. Having logged into Google Groups with this Manager account,
export the cookies.txt from your browser (I used this Chrome extension to get the cookies.txt file:
https://chrome.google.com/webstore/detail/cookiestxt/njabckikapfpffapmjgojcnbfjonfjfg)
(Without this step, the email addresses cannot be harvested from the Google Group, and this will mess up creation
of new users on Discourse)
Once you have the cookies.txt file, the easiest way to get it into your Docker container is to upload it as an attachment
to a post in your discourse forum. You can get the URL from the post, and you need to prepend '/var/www/discourse/public' to the URL,
which will be something like '/uploads/default/original/1X/245aa0cdc6847cf59647e1c7102e253e99d40b69.txt'
INSTRUCTIONS:
**run this script from INSIDE your Discourse Docker container**
$ ssh <your-discourse-server>
$ cd /var/discourse
$ ./launcher enter app
# apt install sqlite3 libsqlite3-dev git
# gem install sqlite3
# cd /var/www/discourse/script/import_scripts
# open mbox.rb, and comment out the very last line that calls "perform"
# download this script from the internet and save as "googlegroups.rb"
# su discourse
# ruby googlegroups.rb <name-of-your-google-group-goes-here>
=end
class ImportScripts::GoogleGroups < ImportScripts::Mbox
CATEGORY_MAPPINGS = {
"default" => "googlegroup"
}
def initialize(google_group_name)
if google_group_name.blank?
raise "No google group name specified!"
end
@google_group_name = google_group_name # your google group name
@first_time = true # true: scrap all, false: scrap using -rss option
@use_cookies = false # use manager cookie to recover email
@load_users = true # use exported user list to recover email
# @copy_from = "/shared/google-group-crawler" # copy from a mounted folder
@users = [] # store users from the csv file
setup_google_group
load_users if @load_users
super()
end
def execute
scrape_google_group_to_mbox if @first_time
update_google_group_to_mbox unless @first_time
super
end
# a valid csv file called #{@google_group_name}.csv from google groups
# is expected in the /tmp folder if @load_users = true
def load_users
# 0 Email address
# 1 Nickname
# 2 Group status
# 3 Email status
# 4 Email preference
# 5 Posting permissions
# 6 Join year
# 7 Join month
# 8 Join day
# 9 Join hour
# 10 Join minute
# 11 Join second
# 12 Time zone
puts "Loading users from /tmp/#{@google_group_name}.csv"
users = []
CSV.foreach(File.path("/tmp/#{@google_group_name}.csv")) do |record|
user = {}
user["email"] = record[0]
user["nickname"] = record[1]
users << user
end
@users = users
puts
end
# cross match users to recover email
def match_user(users, email)
return email if email.nil?
i = users.index { |user| user["email"] =~ /#{email.gsub(/\.+@/,".*@")}/}
if i
users[i]
else
email
end
end
# a valid cookie file called cookies.txt from google groups
# is expected in the /tmp folder if @use_cookies = true
def setup_google_group
ENV['_GROUP'] = @google_group_name
ENV['_WGET_OPTIONS'] = "--load-cookies /tmp/cookies.txt --keep-session-cookies" if @use_cookies
puts ""
puts "Your Google Group name is #{@google_group_name}"
puts "So I'm expecting the Google Group URL to be https://groups.google.com/forum/#!forum/#{@google_group_name}"
puts "First time importing? #{@first_time.to_s}"
puts "Use /tmp/cookies.txt? #{@use_cookies.to_s}"
puts "Use /tmp/#{@google_group_name}.csv? #{@load_users.to_s}"
if @copy_from.present?
puts "Copying existing scrapper from #{@copy_from} to /tmp"
system "rm -rf /tmp/google-group-crawler"
system "cp -r #{@copy_from} /tmp"
system "cp #{@copy_from}/cookies.txt /tmp" if @use_cookies
system "cp #{@copy_from}/#{@google_group_name}.csv /tmp" if @load_users
end
system "chmod -R 777 /tmp/google-group-crawler"
end
# scrape content of the Google Group using https://github.com/icy/google-group-crawler
# do everything in /tmp/
def scrape_google_group_to_mbox
FileUtils.rm_rf("/tmp/google-group-crawler") # idempotent
puts "Clone the Google Group Crawler from icy ..."
system 'git clone https://github.com/icy/google-group-crawler /tmp/google-group-crawler'
# perform the scrape
Dir.chdir '/tmp/google-group-crawler/' do
system 'chmod +x ./crawler.sh'
puts "Start the first pass collection of topics"
system './crawler.sh -sh > wget.sh'
system 'chmod +x ./wget.sh'
puts "Iterate through topics to get messages"
system './wget.sh'
system "chmod -R 777 #{@google_group_name}"
end
end
def update_google_group_to_mbox
# perform the scrape
Dir.chdir '/tmp/google-group-crawler/' do
system 'chmod +x ./crawler.sh'
puts "Update topics"
system './crawler.sh -rss > update.sh'
system 'chmod +x ./update.sh'
puts "Iterate through topics to get messages"
system './update.sh'
system "chmod -R 777 #{@google_group_name}"
end
end
# override
def open_db
SQLite3::Database.new("/tmp/google-group-crawler/index.db")
end
# override
def all_messages
puts "Loading all messages"
files = Dir["/tmp/google-group-crawler/#{@google_group_name}/mbox/*"]
files.each_with_index do |f, idx|
raw = File.read(f)
mail = Mail.read_from_string(raw)
yield mail, f
print_status(idx, files.size)
end
end
# override
def import_categories
mappings = CATEGORY_MAPPINGS.values - ['uncategorized']
create_categories(mappings) do |c|
{id: c, name: c}
end
end
# override
def extract_name(mail)
from_name = nil
from = mail[:from]
from_email = nil
if mail.from.present?
from_email = mail.from.dup
if from_email.kind_of?(Array)
from_email = from_email.first.dup
end
from_email.gsub!(/ at /, '@')
from_email.gsub!(/ \(.*$/, '')
search = match_user(@users, from_email)
from_email = search["email"] unless search.nil?
from_name = search["nickname"] unless search.nil? or search["nickname"].blank?
end
display_names = from.try(:display_names)
if display_names.present?
from_name = display_names.first
end
if from_name.blank? && from.to_s =~ /\(([^\)]+)\)/
from_name = Regexp.last_match[1]
end
from_name = from.to_s if from_name.blank?
[from_email, from_name]
end
def find_all_topics
db = open_db
title2id = {}
rows = db.execute "
SELECT msg_id, title FROM emails AS f
WHERE datetime(f.email_date) = (
SELECT min(datetime(e.email_date)) FROM emails AS e
WHERE e.title = f.title
);
"
rows.each do |row|
title2id[row[1]] = row[0]
end
title2id
ensure
db.close
end
# override
def massage_indices
db = open_db
mappings = find_all_topics
puts "#{mappings.size} topics in total"
str_ids = (mappings.values.map {|id| "'#{id}'"}).join(',')
db.execute "UPDATE emails SET reply_to = null WHERE msg_id in (#{str_ids})"
puts "wiring up replies for these topics"
mappings.each_with_index do |kv, idx|
db.execute "UPDATE emails SET reply_to = ? WHERE title = ? AND msg_id <> ?", [kv[1], kv[0], kv[1]]
print_status(idx, mappings.size)
end
ensure
db.close
end
# override
def create_email_indices
db = open_db
db.execute "DROP TABLE IF EXISTS emails"
db.execute <<-SQL
CREATE TABLE emails (
msg_id VARCHAR(995) PRIMARY KEY,
from_email VARCHAR(255) NOT NULL,
from_name VARCHAR(255) NOT NULL,
title VARCHAR(255) NOT NULL,
reply_to VARCHAR(955) NULL,
email_date DATETIME NOT NULL,
message TEXT NOT NULL,
category VARCHAR(255) NOT NULL
);
SQL
db.execute "CREATE INDEX by_title ON emails (title)"
db.execute "CREATE INDEX by_email ON emails (from_email)"
puts "", "creating indices"
all_messages do |mail, filename|
category = CATEGORY_MAPPINGS['default'] || 'uncategorized'
msg_id = mail['Message-ID'].to_s
from_email, from_name = extract_name(mail)
title = clean_title(mail['Subject'].to_s)
reply_to = mail['In-Reply-To'].to_s
date = mail['date'].to_s
email_date = ""
if date.blank?
email_date = date
else
email_date = DateTime.parse(date).to_s
end
db.execute "INSERT OR IGNORE INTO emails
(msg_id,
from_email,
from_name,
title,
reply_to,
email_date,
message,
category)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
[msg_id, from_email, from_name, title, reply_to, email_date, mail.to_s, category]
end
ensure
db.close
end
end
ImportScripts::GoogleGroups.new(ARGV[0]).performINFO