WordPressのDBから記事データを抽出する

2020-04-30
wordpressmysqlbloggatsbyphp
    

目次

概要

WordPressにため込んだデータMarkdown化しGatsbyで作ったブログに引越しするために記事データを抽出した時のSQLの話。

かなり泥臭かったです。

WordPressのDB関連図

WordPressのDBの関連図は次のようになっています。画像は基本的なテーブルです。

wordpress scheme

実際に実行したのはこちらのテーブル。

プラグイン依存のテーブルがありますが、これは無視していきます。

+-----------------------------+
| Tables_in_bitnami_wordpress |
+-----------------------------+
| wp_aiowps_events            |
| wp_aiowps_failed_logins     |
| wp_aiowps_global_meta       |
| wp_aiowps_login_activity    |
| wp_aiowps_login_lockdown    |
| wp_aiowps_permanent_block   |
| wp_as3cf_items              |
| wp_cocoon_accesses          |
| wp_cocoon_affiliate_tags    |
| wp_cocoon_function_texts    |
| wp_cocoon_item_rankings     |
| wp_cocoon_speech_balloons   |
| wp_commentmeta              |
| wp_comments                 |
| wp_links                    |
| wp_options                  |
| wp_postmeta                 |
| wp_posts                    |
| wp_term_relationships       |
| wp_term_taxonomy            |
| wp_termmeta                 |
| wp_terms                    |
| wp_urls                     |
| wp_usermeta                 |
| wp_users                    |
+-----------------------------+

このデータベースからタイトル・記事データ・サムネイル・カテゴリ・タグを抽出していきます。

公開記事一覧の取得

WordPressのタイトル・記事内容の情報はwp_postsに格納されていますが、画像データなど様々な物が含まれているので、フィルターをかけて抽出します。

実際のクエリはこちら

SELECT 
  * 
FROM
  wp_posts
WHERE 
  post_status = 'publish'
  AND 
  post_title <> ''

取得できたデータはこちら。IDが主キーとなります。

*************************** 1. row ***************************
                   ID: 5
          post_author: 3
            post_date: 2018-01-22 12:15:57
        post_date_gmt: 2018-01-22 12:15:57
         post_content: '略: 本文本文本文本文本文本文'
           post_title: '略: タイトルタイトルタイトルタイトル'
         post_excerpt: 
          post_status: publish
       comment_status: closed
          ping_status: open
        post_password: 
            post_name: hello-world
              to_ping: 
               pinged: 
        post_modified: 2019-01-14 17:52:46
    post_modified_gmt: 2019-01-14 08:52:46
post_content_filtered: 
          post_parent: 0
                 guid: http://0.0.0.0/?p=5
           menu_order: 0
            post_type: post
       post_mime_type: 
        comment_count: 0

タグ・カテゴリの取得

タグやカテゴリは、これらを管理するwp_term_taxonomyと記事IDに紐つく連関エンティティ wp_term_relationships を利用して取得します。

wp_term_relationships

+------------------+---------------------+------+-----+---------+-------+
| Field            | Type                | Null | Key | Default | Extra |
+------------------+---------------------+------+-----+---------+-------+
| object_id        | bigint(20) unsigned | NO   | PRI | 0       |       |
| term_taxonomy_id | bigint(20) unsigned | NO   | PRI | 0       |       |
| term_order       | int(11)             | NO   |     | 0       |       |
+------------------+---------------------+------+-----+---------+-------+

wp_term_taxonomy

+------------------+---------------------+------+-----+---------+----------------+
| Field            | Type                | Null | Key | Default | Extra          |
+------------------+---------------------+------+-----+---------+----------------+
| term_taxonomy_id | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| term_id          | bigint(20) unsigned | NO   | MUL | 0       |                |
| taxonomy         | varchar(32)         | NO   | MUL |         |                |
| description      | longtext            | NO   |     | NULL    |                |
| parent           | bigint(20) unsigned | NO   |     | 0       |                |
| count            | bigint(20)          | NO   |     | 0       |                |
+------------------+---------------------+------+-----+---------+----------------+

クエリ

select 
    term_taxonomy.taxonomy,
    terms.name
from wp_posts posts
    inner join wp_term_relationships term_relationships 
    on posts.id = term_relationships.object_id 
    inner join wp_term_taxonomy term_taxonomy 
    on term_relationships.term_taxonomy_id = term_taxonomy.term_taxonomy_id 
    inner join wp_terms terms 
    on term_taxonomy.term_id = terms.term_id 
where 
    posts.ID = :id 
+----------+-----------------------+
| taxonomy | name                  |
+----------+-----------------------+
| category | Web                   |
| post_tag | HTML                  |
| post_tag | CSS                   |
| post_tag | JavaScript            |
| post_tag | canvas                |
+----------+-----------------------+

カテゴリとタグを取得できました。

サムネイルの取得

サムネイルのデータは wp_posts に格納されています。

select 
    thumbnail.guid as url
from wp_posts posts
    inner join wp_postmeta postmeta 
    on posts.ID = postmeta.post_id 
    inner join wp_posts thumbnail 
    on postmeta.meta_value = thumbnail.ID 
where 
    posts.ID = :id
    AND
    postmeta.meta_key = '_thumbnail_id'

ただし、画像の管理に関して様々なプラグインを利用しており、 結局目的の情報は取得できなかったので自分のブログに対しスクレイピングで画像を1件ずつ取得する方法にしました。

おまけ: PHPスクリプト化しました

記事データをmarkdownに吐くphpスクリプトを書きました。

<?php
define('DB_HOST', 'localhost:3306');
define('DB_NAME', '');
define('DB_USER', '');
define('DB_PASSWORD', '');
define('DB_CHARSET', 'utf8');

try {
    $dbh = new PDO(
        sprintf(
            "mysql:dbname=%s;host=%s;charset=%s;",
            DB_NAME,
            DB_HOST,
            DB_CHARSET
        ),
        DB_USER,
        DB_PASSWORD
    );
} catch (PDOException $e) {
    echo "connect fail: " . $e->getMessage() . "\n";
    exit(1);
}

$items = getItems();

$ignoreTags = ['web &amp; programming'];
$counter = 0;
$pathList = [];
foreach ($items as $row) {
    $id = $row['ID'];
    $taxonomy = getTagsById($id);
    $tags = [];
    foreach ($taxonomy as $tag) {
        $tag = mb_strtolower($tag['name']);
        if (in_array($tag, $ignoreTags)) {
            continue;
        }
        $tags[$tag] = 1;
    }
    $tags = array_keys($tags);
    $thumbnail = '';
    // note: DBから画像が取得できる場合
    // $thumbnail = getThumbnailById($id);
    // if (count($thumbnail) > 0) {
    //     $thumbnail = $thumbnail[0]['url'];
    // } else {
    //     $thumbnail = '';
    // }

    $oldPath = sprintf(
        "/%s/%s/%s/",
        substr($row['post_date'], 0, 4),
        substr($row['post_date'], 5, 2),
        $id
    );
    try {
        $content = file_get_contents('https://tech-blog.s-yoshiki.com'. $oldPath);
        if (!$content) {
            continue;
        }
        $thumbnail = saveThumbnail($content);
    } catch (Exception $e) {
        // echo($e);
        continue;
    }
    $counter++;
    $newPath = sprintf("/entry/%s", $counter);
    _mkdir("./entry/" . $counter);
    // MD file
    $result = flushFile([
        'title' => $row['post_title'],
        'date' => $row['post_date'],
        'content' => $row['post_content'],
        'thumbnail' => $thumbnail,
        'path' => $newPath,
        'tags' => json_encode($tags, JSON_UNESCAPED_UNICODE),
    ]);
    file_put_contents('./entry/' . $counter . '/index.md', $result);
    $pathList[] = [
        'fromPath' => $oldPath,
        'toPath' => $newPath,
    ];
}
file_put_contents('./entry/redirect-config.json', json_encode($pathList, JSON_UNESCAPED_UNICODE));
exit(0);


function getItems()
{
    global $dbh;
    $sql = "
    SELECT * 
    FROM
    wp_posts
    WHERE 
    post_status = 'publish'
    AND  post_title <> '' ";
    $prepare = $dbh->prepare($sql);
    $prepare->execute();
    $result = $prepare->fetchAll(PDO::FETCH_ASSOC);
    return $result;
}

function getTagsById($id)
{
    global $dbh;
    $sql = "
    select 
    term_taxonomy.taxonomy,
    terms.name
    from wp_posts posts
    inner join wp_term_relationships term_relationships on posts.id = term_relationships.object_id 
    inner join wp_term_taxonomy term_taxonomy on term_relationships.term_taxonomy_id = term_taxonomy.term_taxonomy_id 
    inner join wp_terms terms on term_taxonomy.term_id = terms.term_id 
    where 
    posts.ID = :id ";
    $prepare = $dbh->prepare($sql);
    $prepare->bindValue(':id', $id, PDO::PARAM_INT);
    $prepare->execute();
    $result = $prepare->fetchAll(PDO::FETCH_ASSOC);
    return $result;
}

function getThumbnailById($id)
{
    global $dbh;
    $sql = "
    select 
    thumbnail.guid as url
    from wp_posts posts
    inner join wp_postmeta postmeta on posts.ID = postmeta.post_id 
    inner join wp_posts thumbnail on postmeta.meta_value = thumbnail.ID 
    where 
    posts.ID = :id
    AND postmeta.meta_key = '_thumbnail_id'";
    $prepare = $dbh->prepare($sql);
    $prepare->bindValue(':id', $id, PDO::PARAM_INT);
    $prepare->execute();
    $result = $prepare->fetchAll(PDO::FETCH_ASSOC);
    return $result;
}

function get_og_image_url($content)
{
    if (preg_match_all( "<meta property=\"og:([^\"]+)\" content=\"([^\"]+)\">", $content, $ogp_list )){
        if (is_array($ogp_list) && isset($ogp_list[1])) {
            for ($i = 0; $i < count($ogp_list[1]); $i++) {
                $ogp_result[$ogp_list[1][$i]] = $ogp_list[2][$i];
            }
        }
        if (isset($ogp_result['image'])) {
           return $ogp_result['image'];
        }
    }
    return false;
}

function _mkdir($dir)
{
    if (!is_dir($dir)) {
        `mkdir -p $dir`;
    }
}

function saveThumbnail($content)
{
    $dstPath = '';
    $thumbnail = '../../../images/no-image.png';
    $ogimage = get_og_image_url($content);
    if (!$ogimage) {
        return $thumbnail;
    }
    $parsedUrl = parse_url($ogimage);
    $parsedPath = pathinfo($parsedUrl['path']);
    if ($parsedPath['extension'] === 'gif') {
        return $thumbnail;
    }
    $dstPath = './images/thumbnail/' . $parsedPath['basename'];
    $thumbnail = '../../../images/thumbnail/' . $parsedPath['basename'];
    if (file_exists($dstPath)) {
        return $thumbnail;
    }
    _mkdir('./images/thumbnail/' . $parsedPath['dirname']);
    $rawImg = file_get_contents($ogimage);
    file_put_contents($dstPath, $rawImg);
    return $thumbnail;
}

function flushFile($arg)
{
    $title = $arg['title'];
    $date = $arg['date'];
    $content = $arg['content'];
    $tags = $arg['tags'];
    $path = $arg['path'];
    $thumbnail = $arg['thumbnail'];
    return <<<EOT
---
title: "$title"
path: "$path"
date: "$date"
coverImage: "$thumbnail"
author: "XXXXX"
tags: $tags
---
$content
EOT;
}

参考にしたところ

データベース構造 - WordPress Codex 日本語版

    
s-yoshiki
s-yoshiki
githubtwitterqiita
Web作ってますが、インタラクティブなプログラミングも好きです。
JavaScript / Vue / node.js / PHP / AWS / OpenCV

関連記事

SQL整形ツールを作成した
特徴 使い方 FW/ライブラリ等 nuxt sql-formatter-plus Monaco Editor おまけ ソース SQL整形ツールを作成しました。 URLはこちらです。 SQL…

Amazon S3 と ローカルファイルのチェックサムの比較
s3apiでEtagを取得 検証 マルチアップロード時の注意点 Amazon S3 の Etagを使ってファイルの整合性チェックをする。 s3apiでEtagを取得 S3 APIを利用するとEtagを取得します。この値はmd5のハッシュ値になります。 検証 MD…

GitHub Actions で Gatsby をビルドし Amazon S3 にデプロイする
GitHub Actions について あらかじめ準備しておくもの AWS IAM ユーザを環境変数にセットする workflowの記述 ビルド バッジを利用する 終わりに 参考にしたところ Gatsbyで作った静的サイトを、GitHub Actions…

WordPressやめます Gatsbyに移行しました
これまでのWordPress運用 なぜWordPressを捨てるのか? なぜGatsbyを利用するのか? gatsbyについて WordPressから記事の救出 移行対象記事の抽出 記事の置換 Gatsbyテーマの作成 Gatsby…

PHPerkaigi 2020 資料まとめ
資料 もっと気軽にOSSに Pull Requestを出そう!/ Let's make a PR to OSS more easily PHP で JVM を実装して、 HelloWorld を出力してみる Deep Module in PHP 磯野ー、MySQL…

Macでも利用できるDBクライアント MySQL PostgreSQL Oracle など
デスクトップ系ツール TablePlus DBeaver MySQL Workbench Sequel pro Webアプリケーション phpMyAdmin Adminer 参考 Macで利用できるDB (MySQL PostgreSQL Oracle…

DockerでMySQL5.1.73を利用する。docker-composeから起動
Dockerのインストールから実行まで docker-compose から起動 詳細 Docker から MySQL 5.1.73 を利用する。 Dockerのインストールから実行まで docker imageの pull。 119MB…

phpMyAdminをDockerで起動する設定
docker-compose の設定 phpMyAdminのコンテナイメージ GitHub phpMyAdminはPHPで実装されているMySQLの管理ツールです。サーバに配置しブラウザから操作するのが特徴です。このphpMyAdminをdocker…

WordPressを静的サイトに変換するプラグインの紹介。WP2Static
静的化の目的 WP2Static StaticPress Simply Static 3つを比較して WordPressを静的化するプラグインとして、simply staticやStaticPress、WP2Static…

WordPressをAmazon S3 + CloudFront構成で月額200円で運用
システム全体の この構成のメリット・デメリット AWSの構成 静的ページを作成するWordPressプラグイン WP2Static WP Offload Media Lite WordPress on Docker環境 まとめ AmazonS…

最新の投稿

SQL整形ツールを作成した
特徴 使い方 FW/ライブラリ等 nuxt sql-formatter-plus Monaco Editor おまけ ソース SQL整形ツールを作成しました。 URLはこちらです。 SQL…

ファイルの1行目を表示 Linuxコマンド head
head コマンド例 headコマンドでファイルの 1行目もしくは指定した行数だけ表示する方法。 head 利用できるオプション コマンド例 の 1行目だけを表示 の 5行目までを表示 カレントディレクトリ以下の全てのtxtファイルの1行目を表示

Amazon S3 と ローカルファイルのチェックサムの比較
s3apiでEtagを取得 検証 マルチアップロード時の注意点 Amazon S3 の Etagを使ってファイルの整合性チェックをする。 s3apiでEtagを取得 S3 APIを利用するとEtagを取得します。この値はmd5のハッシュ値になります。 検証 MD…

github.io / gitlab.ioで公開されている質の高い技術ドキュメント
AWSによるクラウド入門 Pythonプログラミング入門 普通の人が資産運用で99点をとる方法とその考え方 2018年の段階で私が知らないこと github.io / gitlab.io で無料で公開されている興味深いドキュメントのmemo AWS…

10進数から2進数 2進数から10進数への変換 JavaScript
10進数から2進数 2進数から10進数 テスト 10進数から2進数、2進数から10進数への変換を行うJavaScriptのコードの紹介。 JSの場合、10進数から2進数への変換はメソッド。2進数から1…

JavaScriptの配列ショートハンド (AtCoder用)

JavaScriptでワーシャルフロイド法を実装
AtCoder ABC012 D問題 D - バスと避けられない運命 解説 実装 AtCoder ABC012 の D問題でワーシャルフロイド法が利用できる問題が出てきたので、 JavaScriptで実装しました。 AtCoder ABC012 D問題 D…

GitHub Actions で Gatsby をビルドし Amazon S3 にデプロイする
GitHub Actions について あらかじめ準備しておくもの AWS IAM ユーザを環境変数にセットする workflowの記述 ビルド バッジを利用する 終わりに 参考にしたところ Gatsbyで作った静的サイトを、GitHub Actions…

cloudinaryによる画像ファイルの管理 はじめてみる
目的 cloudinary について 他のサービスとの比較 料金プラン アカウントの登録 利用してみる ダッシュボード 画像の編集 APIベースでのアクセス 感想 参考 画像の管理や配信、さらには加工といった事ができるsaas型のcloud…

JavaScriptによる2分探索(バイナリサーチ) のサンプルコード
2分探索について ソース 参考 JavaScriptで2分探索(バイナリサーチ)を実装してみました。…

Tags

Dates

© 2020   404 motivation not found