a-blog cmsのメンテナンスでよく使うSQL

以前、a-blog cms案件のリニューアルで使えるSQLコマンド をご紹介しましたが、CSVインポートでやらかしてしまった場合などに使える、より実践的なSQL文をメモします。

より実践的である分、危険度の高い操作となりますので、本記事を参考にSQLを発行する場合はバックアップを取得の上自己責任でお願いいたします。

エントリーを削除する

CSVインポートに失敗したとき、エントリー管理から記事を削除することになりますが、ゴミ箱に残ったりIDの連番が進んでしまうのが気になります（entry-200.html があるときに100件インポートしたら、次の記事はentry-301.htmlから始まる）。そこでDBから直接エントリーを削除することができます。次の例はidが1705以降のエントリーを削除する例です。

DELETE FROM `acms_entry` WHERE `entry_id` >= 1705;
DELETE FROM `acms_field` WHERE `field_eid` >= 1705;
DELETE FROM `acms_column` WHERE `column_eid` >= 1705;
DELETE FROM `acms_fulltext` WHERE `fulltext_eid` >= 1705;
UPDATE `acms_sequence` SET `sequence_entry_id` = 1704;

重複するユニットを削除する

CSVインポートなどでユニットを二重に投入してしまった際に使用します。

重複するユニットを抽出する

本文が重複しているユニットをグループ化し、最小のidと最大のidを抽出し重複を確認します。

SELECT MIN(`column_id`), MAX(`column_id`) FROM `acms_column` 
  GROUP BY `column_field_1` 
  HAVING count(*) > 1
  ORDER BY MIN(`column_id`)

重複するユニットを削除する

本文が重複しているユニットをグループ化し、最小のidを削除します。 SELECT * を DELETE に書き換えて使用します。関係のない記事のユニットまで削除しないよう、column_id の範囲を指定しています。

SELECT * FROM `acms_column` 
WHERE `column_id` BETWEEN 2956 AND 3209
AND `column_id` IN (
    SELECT min_id FROM (
        SELECT MIN(`column_id`) AS min_id FROM `acms_column` 
        GROUP BY `column_field_1` 
        HAVING count(*) > 1
    ) AS tmp
);

カスタムフィールドのないエントリーを操作する

お客様から記事を優先表示してほしいと要望があった場合に、カスタムフィールドでのソートを行いますが、カスタムフィールドがなかった場合にソートができません。エントリーの「まとめて操作」で追加可能ですが、管理画面のカスタマイズが必要なので、SQLで対応すると早いかもしれません。

カスタムフィールドのないエントリーを抽出

index_entry_pickup というカスタムフィールドのないエントリーを抽出します。

SELECT `entry_id`, `entry_status`, `entry_title`, `entry_datetime`
FROM `acms_entry`
WHERE `entry_status` = 'open' AND NOT EXISTS (
 SELECT *
 FROM `acms_field`
 WHERE `acms_field`.`field_key` = 'index_entry_pickup' AND `acms_entry`.`entry_id` = `acms_field`.`field_eid`
)
ORDER BY `entry_datetime` DESC

カスタムフィールドのないエントリーに、カスタムフィールドを追加する

「0」の値をもった index_entry_pickup というカスタムフィールドに新たに追加します。

INSERT INTO `acms_field` (`field_key`, `field_value`, `field_sort`, `field_search`, `field_eid`, `field_blog_id`)
SELECT 'index_entry_pickup', '0', 1, 'on', `entry_id`, 1
FROM `acms_entry`
WHERE `entry_status` = 'open' AND NOT EXISTS (
 SELECT *
 FROM `acms_field`
 WHERE `acms_field`.`field_key` = 'index_entry_pickup' AND `acms_entry`.`entry_id` = `acms_field`.`field_eid`
)

特定のカスタムフィールドがあるエントリーに、カスタムフィールドがない場合にカスタムフィールドを追加する

PICKUP というカスタムフィールドがあるエントリーに、「0」の値をもった index_entry_pickup というカスタムフィールドを新たに追加します。

INSERT INTO `acms_field` (`field_key`, `field_value`, `field_sort`, `field_search`, `field_eid`, `field_blog_id`)
SELECT 'index_entry_pickup', '0', 1, 'on', `entry_id`, 1
FROM `acms_entry`
WHERE `entry_status` = 'open' 

AND EXISTS (
 SELECT *
 FROM `acms_field`
 WHERE `acms_field`.`field_key` = 'PICKUP' AND `acms_entry`.`entry_id` = `acms_field`.`field_eid`
)

AND NOT EXISTS (
 SELECT *
 FROM `acms_field`
 WHERE `acms_field`.`field_key` = 'index_entry_pickup' AND `acms_entry`.`entry_id` = `acms_field`.`field_eid`
)

カスタムフィールドのあるエントリーを操作する

カスタムフィールドが特定の値でないエントリーを抽出

entry_approval というカスタムフィールドの値が「manager-approval」でないエントリーを抽出します。

SELECT `entry_id`, `entry_status`, `entry_title`, `entry_datetime`, `field_key`, `field_value`
FROM `acms_entry`
INNER JOIN `acms_field` ON `acms_field`.`field_eid` = `acms_entry`.`entry_id`
WHERE `acms_entry`.`entry_status` = 'open' AND `acms_field`.`field_key` = 'entry_approval' AND `field_value` != 'manager-approval'
ORDER BY `entry_datetime`

カスタムフィールドの値を置換

employee_division というカスタムフィールドが「新規事業部」の場合、「イノベーション推進部」に一斉置換します。

UPDATE `acms_field` SET `field_value` = '新規事業部' WHERE `field_key` = 'employee_division' AND `field_value` = 'イノベーション推進部'

REPLACE句を使い employee_introduction というカスタムフィールドに含まれる「新規事業部」を、「イノベーション推進部」に一斉置換します。

UPDATE `acms_field` SET field_value=REPLACE (field_value,'新規事業部','イノベーション推進部')  WHERE `field_key` = 'employee_introduction';

以上、他にもありましたら随時追記していきたいと思います。

---

いいね!