Add pg_jekyll script

4 files changed, 63 insertions, 4 deletions

diff --git a/Gemfile b/Gemfile
index 0a9fffa..b979a21 100644
--- a/Gemfile
+++ b/Gemfile
@@ -3,4 +3,5 @@
 source "https://rubygems.org"
 
 gem 'rubyzip', '~> 2.4'
+gem 'pg', '~> 1.6'
 gem "term-ansicolor", "~> 1.11"
diff --git a/Gemfile.lock b/Gemfile.lock
index 3663415..ffdbf82 100644
--- a/Gemfile.lock
+++ b/Gemfile.lock
@@ -1,12 +1,14 @@
 GEM
   remote: https://rubygems.org/
   specs:
-    bigdecimal (3.2.2)
+    bigdecimal (3.2.3)
+    pg (1.6.2)
+    pg (1.6.2-x86_64-linux)
     rubyzip (2.4.1)
     sync (0.5.0)
     term-ansicolor (1.11.2)
       tins (~> 1.0)
-    tins (1.38.0)
+    tins (1.43.0)
       bigdecimal
       sync
 
@@ -15,8 +17,9 @@ PLATFORMS
   x86_64-linux
 
 DEPENDENCIES
+  pg (~> 1.6)
   rubyzip (~> 2.4)
   term-ansicolor (~> 1.11)
 
 BUNDLED WITH
-   2.6.7
+   2.6.9
diff --git a/README.md b/README.md
index 5a2a4a2..b30ff91 100644
--- a/README.md
+++ b/README.md
@@ -13,7 +13,8 @@ The repository contains a collection of random scripts and short programs.
 3. [read\_logs.rb](#read_logsrb)
 4. [cross\_toolchain.rb](#cross_toolchainrb)
 5. [rename.rb](#renamerb)
-6. [tea-cleaner](#tea-cleaner)
+6. [pg\_jekyll.rb](#pg_jekyllrb)
+7. [tea-cleaner](#tea-cleaner)
 
 ## 7digital.rb
 
@@ -80,6 +81,17 @@ Changes the extension of all files matching a pattern in the given directory.
 Call the `rename.rb` without arguments to see the usage information and an
 example.
 
+## pg\_jekyll.rb
+
+This script was used to migrate blog posts from a PostgreSQL database to a
+static site generator in Jekyll-like format. `pg_jekyll.rb` cannot be used as
+is: the query and columns should be adjusted for a concrete case, but it can
+be a starting point and example for such a migration. Also it expects
+environment variables `USER` and `DBNAME` to be present in the environment.
+
+Pages are written into the `posts/` directory. A column named `slug` is used
+for the name with `.html` extension.
+
 ## tea-cleaner
 
 `tea-cleaner` tries to detect spam accounts on a gitea instance and can remove
diff --git a/bin/pg_jekyll.rb b/bin/pg_jekyll.rb
new file mode 100755
index 0000000..ed2ac3b
--- /dev/null
+++ b/bin/pg_jekyll.rb
@@ -0,0 +1,43 @@
+#!/usr/bin/env ruby
+# This Source Code Form is subject to the terms of the Mozilla Public License,
+# v. 2.0. If a copy of the MPL was not distributed with this file, You can
+# obtain one at https://mozilla.org/MPL/2.0/. -}
+
+# frozen_string_literal: true
+
+require 'pg'
+
+connection = PG.connect(user: ENV['user'], dbname: ENV['DBNAME'])
+query = <<~SQL
+  SELECT articles.*, categories.name
+  FROM articles
+  INNER JOIN categories on categories.id = articles.category_id
+SQL
+connection.exec query do |result|
+  result.each do |row|
+    post_contents = <<~POST
+      ---
+      layout: post
+    POST
+
+    date = row['created_at'].gsub /\.[[:digit:]]+$/, '' # Remove milliseconds from the value.
+    post_contents << "date: #{date}\n"
+    category = row['name']
+    post_contents << "tags: #{category}\n"
+
+    if row['title'].include? '"'
+      post_contents << "title: |\n  #{row['title']}\n"
+    else
+      post_contents << "title: #{row['title']}\n"
+    end
+
+    post_contents << "teaser:\n  #{row['teaser']}\n" unless row['teaser'].nil?
+    post_contents << "---\n"
+    post_contents << "#{row['description'].delete("\r")}\n"
+
+    post_path = (Pathname.new('posts') + row['slug']).sub_ext('.html')
+
+    post_path.dirname.mkpath
+    post_path.write post_contents
+  end
+end