【補足を追加しました。】WordPressに記事として書かれている文章のうち、設定ファイル、プログラム例及びコマンドの出力結果などを除いた部分のおおよその総文字数をSQL文だけで求める方法の例

はじめに

以前、この記事で、WordPressに記事として書かれている文章のおおよその総文字数をSQL文だけで求める方法について書きました。

上記の記事にあるSQL文は、実際に見に来ていただいている訪問者の方が目にする文字列の総量を示すという点では意味のあるものです。しかし、本サイト(に限らず、IT技術系の内容が記述されているサイトでは割とよくあることであるとは思いますが、)ではそれらの文字列の中には、アプリケーションの設定ファイルやプログラム例、コマンドの出力結果など、説明の関係で貼り付けることが必要なものの、(非常に厳密な書き方になりますが、)自分の言葉で記述したものでない部分がそれなりの比率で存在しています。

そこで本記事では、上記の記事にあるSQL文にちょっと追加をして、WordPressに記事として書かれている文章のうち、設定ファイル、プログラム例及びコマンドの出力結果などを除いた部分のおおよその総文字数をSQL文だけで求めることを考えます。

まず、設定ファイル、プログラム例及びコマンドの出力結果などを除外する部分の動作確認。

本サイトではhighlight.jsを使って設定ファイル、プログラム例及びコマンドの出力結果などを表示していますが、その際に以下のタグを使用して表示しています。

<div class=”code”>[設定ファイル、プログラム例及びコマンドの出力結果など]</div>

そこで、上記のタグで囲まれている文字列を記事から除くことを考えます。

まず、この記事と同様にregexp_replace関数を使って、以下のように書いてみます。

MariaDB [pandanote]> select regexp_replace(post_content,'(?s)<div class=”code”>((?!</div>).)*?</div>’,”) from wp_posts where id = 997;

すると、実行結果が表示された後にWarningが出力されました。よく調べてみると記事の後半にあるコマンドの実行結果例が削除できていませんでした。

1 row in set, 1 warning (0.02 sec)
MariaDB [pandanote]> show warnings;
+———+——+——————————————————————–+
| Level   | Code | Message                                                            |
+———+——+——————————————————————–+
| Warning | 1139 | Got error ‘pcre_exec: recursion limit of 521 exceeded’ from regexp |
+———+——+——————————————————————–+
1 row in set (0.02 sec)

そこで、Google先生に聞いてみたところ、「このような警告が出力されるときには、thread_stackの値を増やすとよい。」らしいことがわかったので、まず、デフォルトの値を調べます。MariaDB 10.1.26では289Kのようです。

MariaDB [pandanote]> show variables like ‘%thread_stack%’;
+—————+——–+
| Variable_name | Value  |
+—————+——–+
| thread_stack  | 295936 |
+—————+——–+
1 row in set (0.01 sec)

これよりも大きな値にすればよいということで、mariadb-server.cnfに以下の行を追加し、MariaDBサーバを再起動します。

thread_stack=1M

少し大きすぎるような気もしますが、これでWarningは出力されなくなり、設定ファイル、プログラム例及びコマンドの出力結果などを除外することができるようになりました。

いよいよ本番。

SQL文の作成。

動作確認ができたところで、いよいよ以下のコードに設定ファイル、プログラム例及びコマンドの出力結果などを除外するためのregexp_replace関数を追加します。

	-- WordPressに記事として書かれている文章のおおよその総文字数を求めるSQL文
	-- なお、本SQL文を利用することにより得られた結果については責任は負いかねます。
	select sum(char_length(regexp_replace(regexp_replace(regexp_replace(post_content,'<.+?>',''),'&.+?;','_'),'(\s+\|\r\n\|\n)','')))
	as total from wp_posts where post_status = 'publish' and post_type = 'post';

view raw total_characters_on_wordpress.sql hosted with

by GitHub

regexp_replace関数を追加すると、以下のようなSQL文になります。上記のSQL文のpost_contentの部分に設定ファイル、プログラム例及びコマンドの出力結果などを除外した結果を代入するように変更しています。

	-- WordPressに記事として書かれている文章のうち、
	-- 設定ファイル、プログラム例及びコマンドの出力結果などを除いた部分のおおよその総文字数を求めるSQL文
	-- なお、本SQL文を利用することにより得られた結果については責任は負いかねます。
	select sum(char_length(regexp_replace(regexp_replace(regexp_replace(regexp_replace(post_content,'(?s)<div class="code">((?!</div>).)*?</div>',''),'<.+?>',''),'&.+?;','_'),'(\s+\|\r\n\|\n)','')))
	as total from wp_posts where post_status = 'publish' and post_type = 'post';

view raw total_characters_on_wordpress_excluding_examples.sql hosted with

by GitHub

実行結果の比較

まず、設定ファイル、プログラム例及びコマンドの出力結果などを除外しないで総文字数を求めた結果は以下の通りになります。

MariaDB [pandanote]> select sum(char_length(regexp_replace(regexp_replace(regexp_replace(post_content,'<.+?>’,”),’&.+?;’,’_’),'(\s+|\r\n|\n)’,”)))  as total from wp_posts where post_status = ‘publish’ and post_type = ‘post’;
+——-+
| total |
+——-+
| 96194 |
+——-+
1 row in set (0.11 sec)

次に、設定ファイル、プログラム例及びコマンドの出力結果などを除外して総文字数を求めた結果は以下の通りになります。

MariaDB [pandanote]> select sum(char_length(regexp_replace(regexp_replace(regexp_replace(regexp_replace(post_content,'(?s)<div class=”code”>((?!</div>).)*?</div>’,”),'<.+?>’,”),’&.+?;’,’_’),'(\s+|\r\n|\n)’,”)))  as total from wp_posts where post_status = ‘publish’ and post_type = ‘post’;
+——-+
| total |
+——-+
| 77421 |
+——-+
1 row in set (0.10 sec)

上記の結果より、本サイトではブラウザ等のクライアントが表示すべきものとして解釈する文字のうち自力で書いたものが約80.5%で、残りが設定ファイル、プログラム例及びコマンドの出力結果など、自力で書いたものではないことがわかります。

[2020/07/04 補足]regexp_replace関数を使うと絵文字などが”?”に変換されてしまう問題

MariaDBの文字コードをutf8mb4に指定した場合にのみサポートされる文字(絵文字等)を含む文字列を引数として指定してregexp_replace関数を実行するとutf8mb4に指定した場合にのみサポートされる文字の部分が(変換の対象とならない場合でも)”?”に変換されてしまう問題がMariaDB ServerのJiraに報告されていて、10.3の時点でも修正されていないようです(参考文献参照。なお、本Webサーバで本補足を書いた時点で使用しているバージョン(10.4)でも修正が行われていないようです)。

記事の文字数(それも概算)を数えるといった用途であれば、regexp_replace関数を使用しても良さそうですが、用途によっては別の方法での文字列の置き換えを検討する必要がありそうです。

まとめ

SQL文だけを使用して最初に設定した問題の答えを求めることができましたが、結局MariaDBのサーバの設定の変更が必要であることもわかりました。

本サイトでは各記事の公開後も任意のタイミングで記事の修正を行い、記事の質の向上を図るというWiki的な運営ポリシーを採用しています。この運営ポリシーを実践するためには、個別の記事の内容を大まかにでも把握しておく必要がありますが、記事の数が増えてくるとそもそも何を書いていたのか把握することが徐々に難しくなってきます(本サイトも固定ページを除くとこの記事が50本目になりますが、そろそろ最初の方で何を書いたのか忘れかけてきてます(苦笑))。

そこで、記事ごとまたは記事の総量として「自力でどのくらい書いたのか」についての概要を把握することで、個別の記事の内容は忘れてしまっていても、ブラッシュアップすべき記事のあたりをつけることを可能にすることで、記事の質を向上させることが容易になるのではないかと思います。

なお、本記事を除くとここまで49件の記事を書いているのですが、1件平均で約1580文字自力で書いていることになります。本サイトで記事を書き始めた当初は平均1000文字の記事を定期的に書くのは難しいと思っていたのですが、なんか書き出すと止まらないみたいです。（＾ω＾）

この記事は以上です。