Skip to content

Instantly share code, notes, and snippets.

@stammy
Last active March 27, 2022 09:39
Show Gist options
  • Save stammy/790971 to your computer and use it in GitHub Desktop.
Save stammy/790971 to your computer and use it in GitHub Desktop.
Import a WordPress database and generate markdown files for Jekyll
# View my Jekyll blog http://paulstamatiou.com and my jekyll migration post http://paulstamatiou.com/how-to-wordpress-to-jekyll/
#
#
# based on the import script by icebreaker, which is based on mojombo's
# https://github.com/mojombo/jekyll/blob/master/lib/jekyll/migrators/wordpress.rb
# https://gist.github.com/303570
# edited to rewrite image URLs to use my CloudFront URL
require 'rubygems'
require 'sequel'
require 'fileutils'
# $ export DB=my_wpdb
# $ export USER=dbuser
# $ export PASS=dbpass
# $ ruby -r './lib/jekyll/migrators/wordpress' -e 'Jekyll::WordPress.process( "#{ENV["DB"]}", "#{ENV["USER"]}", "#{ENV["PASS"]}")'
# NOTE: This converter requires Sequel and the MySQL gems.
# The MySQL gem can be difficult to install on OS X. Once you have MySQL
# installed, running the following commands should work:
# $ sudo gem install sequel
# $ sudo gem install mysql -- --with-mysql-config=/usr/local/mysql/bin/mysql_config
module Jekyll
module WordPress
# Reads a MySQL database via Sequel and creates a post file for each
# post in wp_posts that has post_status = 'publish'.
# This restriction is made because 'draft' posts are not guaranteed to
# have valid dates.
QUERY = "select post_title, post_name, post_date, post_content, post_excerpt, ID, guid from wp_posts where post_status = 'publish' and post_type = 'post'"
# Fetch all tags for a given POST ID
TAGS_QUERY = "select tm.term_id,tm.name from wp_term_relationships tr
inner join wp_term_taxonomy tt on tr.term_taxonomy_id = tt.term_taxonomy_id
inner join wp_terms tm on tm.term_id=tt.term_id
where tr.object_id=%d and tt.taxonomy = 'post_tag'";
def self.process(dbname = '', user='', pass='', host = 'localhost', domain = '')
db = Sequel.mysql(dbname, :user => user, :password => pass, :host => host)
FileUtils.mkdir_p "_posts"
db[QUERY].each do |post|
# Get required fields and construct Jekyll compatible name
title = post[:post_title]
slug = post[:post_name]
date = post[:post_date]
content = post[:post_content]
name = "%02d-%02d-%02d-%s.markdown" % [date.year, date.month, date.day, slug]
# Get associated taxonomy terms (tags)
# We replace + with nothing and transform to lower case
# TODO: figure out what other characters would fuck up YAML
tags = []
db[TAGS_QUERY % post[:ID]].each do |tag|
tags << tag[:name].to_s.gsub('+','').downcase
end
# Process content to rewrite some URLs
if domain
content = self.transformUrls(domain,content)
end
# Get the relevant fields as a hash, delete empty fields and convert
# to YAML for the header
data = {
'layout' => 'post',
'title' => title.to_s,
'excerpt' => post[:post_excerpt].to_s,
'tags' => tags
}.delete_if { |k,v| v.nil? || v == ''}.to_yaml
# Write out the data and content to file
File.open("_posts/#{name}", "w") do |f|
f.puts data
f.puts "---"
f.puts content
end
end
end
# Process the content and replace URLs pointing to wp-content/uploads/
# with CloudFront CNAME'd URL turbo.paulstamatiou.com/uploads/
def self.transformUrls(domain,content)
baseurl = "%s/wp-content/uploads/" % domain
return content.gsub(baseurl,"turbo.paulstamatiou.com/uploads/")
end
end
end
@RandomDSdevel
Copy link

RandomDSdevel commented Dec 24, 2017

@stammy:

With respect to the lines 18–22 of this Gist as quoted here:

⋮

# NOTE: This converter requires Sequel and the MySQL gems.
# The MySQL gem can be difficult to install on OS X. Once you have MySQL
# installed, running the following commands should work:
# $ sudo gem install sequel
# $ sudo gem install mysql -- --with-mysql-config=/usr/local/mysql/bin/mysql_config

⋮

the instructions continued within it no longer seem to work. Specifically, the last line, number 22, does not seem to lend itself well to successful invocation. With Ruby v2.4.3 and MySQL v5.7.20 (both installed by Homebrew,) attempting to execute said command — or, rather, a variation thereupon in the form of sudo gem install --no-user-install mysql -- --with-mysql-config="/usr/local/bin/mysql_config" — results in the following tirade of warnings and errors:

Building native extensions with: '--with-mysql-config=/usr/local/bin/mysql_config'
This could take a while...
ERROR:  Error installing mysql:
	ERROR: Failed to build gem native extension.

    current directory: /usr/local/lib/ruby/gems/2.4.0/gems/mysql-2.9.1/ext/mysql_api
/usr/local/opt/ruby/bin/ruby -r ./siteconf20171223-6350-8qcaqp.rb extconf.rb --with-mysql-config=/usr/local/bin/mysql_config
checking for mysql_ssl_set()... yes
checking for rb_str_set_len()... yes
checking for rb_thread_start_timer()... no
checking for mysql.h... yes
creating Makefile

current directory: /usr/local/lib/ruby/gems/2.4.0/gems/mysql-2.9.1/ext/mysql_api
make "DESTDIR=" clean

current directory: /usr/local/lib/ruby/gems/2.4.0/gems/mysql-2.9.1/ext/mysql_api
make "DESTDIR="
compiling mysql.c
mysql.c:588:9: warning: implicit conversion loses integer precision: 'my_ulonglong' (aka 'unsigned long long') to 'unsigned int' [-Wshorten-64-to-32]
    n = mysql_num_rows(res);
      ~ ^~~~~~~~~~~~~~~~~~~
mysql.c:633:9: warning: implicit conversion loses integer precision: 'my_ulonglong' (aka 'unsigned long long') to 'unsigned int' [-Wshorten-64-to-32]
    n = mysql_num_rows(res);
      ~ ^~~~~~~~~~~~~~~~~~~
mysql.c:1068:73: warning: implicit conversion loses integer precision: 'unsigned long' to 'int' [-Wshorten-64-to-32]
                int len = strlen(fields[i].table)+strlen(fields[i].name)+1;
                    ~~~   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^~
mysql.c:1320:74: error: use of undeclared identifier 'rb_cFixnum'
        else if (argv[i] == rb_cNumeric || argv[i] == rb_cInteger || argv[i] == rb_cFixnum)
                                                                                ^
mysql.c:1661:9: warning: implicit conversion loses integer precision: 'unsigned long' to 'int' [-Wshorten-64-to-32]
    n = mysql_stmt_param_count(s->stmt);
      ~ ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql.c:1775:5: warning: division by zero is undefined [-Wdivision-by-zero]
    rb_scan_args(argh, argue, "08", &year, &month, &day, &hour, &minute, &second, &neg, &second_part);
    ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2151:9: note: expanded from macro 'rb_scan_args'
        rb_scan_args0(argh,argvp,fat,\
        ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2324:9: note: expanded from macro 'rb_scan_args0'
                     (rb_scan_args_verify(fat, vary), vars))
                      ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2209:11: note: expanded from macro 'rb_scan_args_verify'
        verify = rb_scan_args_verify_count(fat, vary); \
                 ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
note: (skipping 4 expansions in backtrace; use -fmacro-backtrace-limit=0 to see all)
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2175:6: note: expanded from macro 'rb_scan_args_count_hash'
     rb_scan_args_count_block(fat, off, varc, vari) : \
     ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2170:6: note: expanded from macro 'rb_scan_args_count_block'
     rb_scan_args_count_end(fat, off, varc, vari) : \
     ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2166:12: note: expanded from macro 'rb_scan_args_count_end'
    ((vari)/(!fmt[ofs] || rb_scan_args_bad_format(fit)))
           ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql.c:1775:5: warning: division by zero is undefined [-Wdivision-by-zero]
    rb_scan_args(argh, argue, "08", &year, &month, &day, &hour, &minute, &second, &neg, &second_part);
    ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2151:9: note: expanded from macro 'rb_scan_args'
        rb_scan_args0(argh,argvp,fat,\
        ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2324:9: note: expanded from macro 'rb_scan_args0'
                     (rb_scan_args_verify(fat, vary), vars))
                      ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2209:11: note: expanded from macro 'rb_scan_args_verify'
        verify = rb_scan_args_verify_count(fat, vary); \
                 ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
note: (skipping 4 expansions in backtrace; use -fmacro-backtrace-limit=0 to see all)
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2175:6: note: expanded from macro 'rb_scan_args_count_hash'
     rb_scan_args_count_block(fat, off, varc, vari) : \
     ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2171:6: note: expanded from macro 'rb_scan_args_count_block'
     rb_scan_args_count_end(fat, ofs+1, vary, vari+1))
     ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2166:12: note: expanded from macro 'rb_scan_args_count_end'
    ((vari)/(!fmt[ofs] || rb_scan_args_bad_format(fit)))
           ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql.c:1775:5: warning: division by zero is undefined [-Wdivision-by-zero]
    rb_scan_args(argh, argue, "08", &year, &month, &day, &hour, &minute, &second, &neg, &second_part);
    ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2151:9: note: expanded from macro 'rb_scan_args'
        rb_scan_args0(argh,argvp,fat,\
        ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2324:9: note: expanded from macro 'rb_scan_args0'
                     (rb_scan_args_verify(fat, vary), vars))
                      ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2209:11: note: expanded from macro 'rb_scan_args_verify'
        verify = rb_scan_args_verify_count(fat, vary); \
                 ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
note: (skipping 4 expansions in backtrace; use -fmacro-backtrace-limit=0 to see all)
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2176:6: note: expanded from macro 'rb_scan_args_count_hash'
     rb_scan_args_count_block(fat, ofs+1, vary, vari+1))
     ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2170:6: note: expanded from macro 'rb_scan_args_count_block'
     rb_scan_args_count_end(fat, off, varc, vari) : \
     ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2166:12: note: expanded from macro 'rb_scan_args_count_end'
    ((vari)/(!fmt[ofs] || rb_scan_args_bad_format(fit)))
           ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql.c:1775:5: warning: division by zero is undefined [-Wdivision-by-zero]
    rb_scan_args(argh, argue, "08", &year, &month, &day, &hour, &minute, &second, &neg, &second_part);
    ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2151:9: note: expanded from macro 'rb_scan_args'
        rb_scan_args0(argh,argvp,fat,\
        ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2324:9: note: expanded from macro 'rb_scan_args0'
                     (rb_scan_args_verify(fat, vary), vars))
                      ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2209:11: note: expanded from macro 'rb_scan_args_verify'
        verify = rb_scan_args_verify_count(fat, vary); \
                 ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
note: (skipping 4 expansions in backtrace; use -fmacro-backtrace-limit=0 to see all)
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2175:6: note: expanded from macro 'rb_scan_args_count_hash'
     rb_scan_args_count_block(fat, off, varc, vari) : \
     ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2170:6: note: expanded from macro 'rb_scan_args_count_block'
     rb_scan_args_count_end(fat, off, varc, vari) : \
     ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2166:12: note: expanded from macro 'rb_scan_args_count_end'
    ((vari)/(!fmt[ofs] || rb_scan_args_bad_format(fmt)))
           ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql.c:1775:5: warning: division by zero is undefined [-Wdivision-by-zero]
    rb_scan_args(argh, argue, "08", &year, &month, &day, &hour, &minute, &second, &neg, &second_part);
    ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2151:9: note: expanded from macro 'rb_scan_args'
        rb_scan_args0(argh,argvp,fat,\
        ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2324:9: note: expanded from macro 'rb_scan_args0'
                     (rb_scan_args_verify(fat, vary), vars))
                      ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2209:11: note: expanded from macro 'rb_scan_args_verify'
        verify = rb_scan_args_verify_count(fat, vary); \
                 ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
note: (skipping 4 expansions in backtrace; use -fmacro-backtrace-limit=0 to see all)
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2175:6: note: expanded from macro 'rb_scan_args_count_hash'
     rb_scan_args_count_block(fat, off, varc, vari) : \
     ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2170:6: note: expanded from macro 'rb_scan_args_count_block'
     rb_scan_args_count_end(fat, off, varc, vari) : \
     ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2166:12: note: expanded from macro 'rb_scan_args_count_end'
    ((vari)/(!fmt[ofs] || rb_scan_args_bad_format(fit)))
           ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql.c:1775:5: warning: division by zero is undefined [-Wdivision-by-zero]
    rb_scan_args(argh, argue, "08", &year, &month, &day, &hour, &minute, &second, &neg, &second_part);
    ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2151:9: note: expanded from macro 'rb_scan_args'
        rb_scan_args0(argh,argvp,fat,\
        ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2324:9: note: expanded from macro 'rb_scan_args0'
                     (rb_scan_args_verify(fat, vary), vars))
                      ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2209:11: note: expanded from macro 'rb_scan_args_verify'
        verify = rb_scan_args_verify_count(fat, vary); \
                 ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
note: (skipping 5 expansions in backtrace; use -fmacro-backtrace-limit=0 to see all)
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2175:6: note: expanded from macro 'rb_scan_args_count_hash'
     rb_scan_args_count_block(fat, off, varc, vari) : \
     ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2170:6: note: expanded from macro 'rb_scan_args_count_block'
     rb_scan_args_count_end(fat, off, varc, vari) : \
     ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/usr/local/Cellar/ruby/2.4.3_1/include/ruby-2.4.0/ruby/ruby.h:2166:12: note: expanded from macro 'rb_scan_args_count_end'
    ((vari)/(!fmt[ofs] || rb_scan_args_bad_format(fit)))
           ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
10 warnings and 1 error generated.
make: *** [mysql.o] Error 1

make failed, exit code 2

Gem files will remain installed in /usr/local/lib/ruby/gems/2.4.0/gems/mysql-2.9.1 for inspection.
Results logged to /usr/local/lib/ruby/gems/2.4.0/extensions/x86_64-darwin-15/2.4.0/mysql-2.9.1/gem_make.out

The same is also true with respect to the equivalent lines of @harperreed's migration script, which you reference in the blog post with which this Gist goes. I suspect that this is due to changes in Ruby and MySQL that have not been taken into account within the Ruby mysql Gem's codebase, as its author stopped maintaining it a couple years ago.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment