Concrete CMS を業務で使っていると、どうしても一括で特定の文字列を変換を頼まれたりします。
Concrete CMS には c5:exec という素晴らしい CLI 実行方法やジョブというバッチもあるので、場合によっては PHP でコマンドやジョブを書いて実行するときもあります。
しかし、中には MySQL の SQL を走らせるだけで完結する場合もあります。
いろいろ案件とかで、必要に迫られて実施してきた SQL クエリが溜まってきて、自分でも必要になる時があるので、まとめてみました。
MySQL クライアントに SSH や Sequal Pro などのソフトからログインし、Concrete CMS のデータベースに入った状態で SQL Query を直接叩いて実行する想定です。
SSH ターミナルなどで
mysql -h [MySQL DB のアドレス] -u [MySQL ユーザー名] --password="[MySQL パスワード]" [MySQL データベース名]
でログインしてから、MySQL のコンソールで下記の SQL 実行してもらうという想定です。
お断り
Conrete CMS の履歴管理・バージョン管理などの機能を無視して、過去のバージョンのデータも書き換えるので、必ずサイトデータをバックアップし、失敗しても復旧できるような体制を作ってから実施してください。
一括変換 SQL は無保証・ちょっと危険 なやり方なので気をつけて実行してください。
たとえば、以下の手順がおすすめです。
- 事前に MySQL のバックアップを取得
- 開発環境を用意してそちらで予め実行する
- 万が一のときの復旧方法を開発環境でリハーサルする
- 最後に本番で実施する
Concrete CMS のバックアップ取得には、僕が作成したバックアップシェルスクリプトをお使いください。
concrete5 Backup Shell Script
https://github.com/concrete5-community/concrete5-backup-shell
調査系
特定の文字列 (http://example.com) が入っているブロックが何個あるかカウントする
例：SSL 対応した時に、http:// なリンクが大量に記事の中にあるので面倒だから一斉置換したい。そのために対象のブロック数を知りたい。
# 基本形
SELECT count(*) FROM [テーブル名] WHERE content LIKE '%[特定文字列]%';
LIKE で % を希望する文字列の前後にいれることで、レコードの中の全文から抽出検索 (?) します。
置換前後に走らせて数が変化しているかどうか調べます。
# 記事ブロック
SELECT count(*) FROM btContentLocal WHERE content LIKE '%http://example.com%';
# テキスト属性
SELECT count(*) FROM atDefault WHERE value LIKE '%http://example.com%';
「http://example.com」を希望する文字列に書き換えて実施してみてください。
特定の文字列 (http://example.com) が入っているブロックが入っているページはどこか一覧を取得する
JOIN を使っても良いのですが
SELECT cPath FROM PagePaths
WHERE cID
IN (SELECT cID FROM CollectionVersionBlocks WHERE bID
IN (SELECT bID FROM btContentLocal WHERE content LIKE '%http://example.com%')
);
一括置換
##ユーザー系一括置換
ユーザーのメールアドレスを強制変更する
# 基本形
UPDATE Users SET uEmail=REPLACE(uEmail, "original", "example");
本番サーバーから開発サーバーにデータを移行する時、ユーザーデータを匿名化して開発サイトからのメールの誤送信や個人情報保護を行うためのコマンドです。
メールアドレス一斉置換
# ユーザーID@example.com に変更する
UPDATE Users SET uEmail=CONCAT(uName, '@example.com');
# メールアドレスが「@concrete5.co.jp」で終わる以外のユーザーは全員 ユーザーID@example.com に変更する
UPDATE Users SET uEmail=CONCAT(uName, '@example.com') WHERE uEmail NOT LIKE "%@concrete5.co.jp";
ユーザー名一斉置換
# uID=1 のスーパー管理者以外は、ユーザーIDを登録順の数字だけに一括変換する
UPDATE Users SET uName=uID WHERE uID NOT LIKE "1";
# メールアドレスが「@concrete5.co.jp」で終わる以外のユーザー全員は、ユーザー名を、登録順の数字に置き換える
UPDATE Users SET uEmail=CONCAT(uID, '@example.com') WHERE uEmail NOT LIKE "%@concrete5.co.jp";
ユーザー属性の場合
なお、追加で登録したユーザー属性の情報はここでは匿名化しません。実は前述の concrete5 Backup Shell Scriptの中に Batch Modify Users という concrete5 のジョブがあってそこのジョブファイルを属性のハンドルと上書きするデータを指示することでユーザー属性を匿名化出来ます。
コンテンツ一斉置換
テキスト属性
例：テキスト属性を変換する場合
UPDATE atDefault SET value = REPLACE(value, 'http://example.com', 'http://example.net');
記事ブロック & HTML ブロック
実は、記事ブロックと HTML ブロックは同じテーブルに保存されているので、これで一括変換されてしまいます。
UPDATE btContentLocal SET content = REPLACE(content, 'http://example.com', 'http://example.net');