DBに落としこんでみる - zowのプログラムな日々

今回作る静的CMSは1サイト1ファイルのSQLiteで作る。その方が管理もバックアップも楽だからだ。まずは、前回考えた構成をDBに落としこんでみる。

CREATE TABLE site (
  `name` TEXT,
  `label` TEXT,
  `title` TEXT, -- サイト タイトル
  `url` TEXT, -- サイトURL
  `category_filename_prefix` TEXT DEFAULT 'category_', -- Categoryファイル名接頭語
  `category_filename_suffix` TEXT, -- Categoryファイル名接尾語
  `tag_filename_prefix` TEXT DEFAULT 'tag_', -- Tagファイル名接頭語
  `tag_filename_suffix` TEXT, -- Tagファイル名接尾語
  `page_filename_prefix` TEXT, -- Pageファイル名接頭語
  `page_filename_suffix` TEXT, -- Pageファイル名接尾語
  `added_at` TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE pages (
  `page_id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `page_name` TEXT, -- ページ名（識別用）
  `page_filename` TEXT, -- 生成するファイル名
  `page_label` TEXT, -- ページへリンクする場合のアンカーテキスト
  `page_title` TEXT, -- ページ タイトル
  `page_disabled` INT DEFAULT 0, -- 1でdisabled
  `page_description` TEXT, -- Description記述
  `page_template` TEXT, -- ページに適用するテンプレート名記述
  `page_group_id` INT, -- 所属page_group_id記述
  `page_category` TEXT, -- カンマ区切りでカテゴリ名記述
  `page_tags` TEXT, -- カンマ区切りでタグ名記述
  `page_place_holder_1` TEXT DEFAULT 'header_top', -- 予約:ヘッダ上(header_top)
  `page_place_holder_2` TEXT DEFAULT 'header_main', -- 予約:ヘッダコンテンツ(header_main)
  `page_place_holder_3` TEXT DEFAULT 'header_bottom', -- 予約:ヘッダ下(header_bottom)
  `page_place_holder_4` TEXT DEFAULT 'main', -- 予約:メインコンテンツ(main)
  `page_place_holder_5` TEXT DEFAULT 'side1', -- 予約:サイドコンテンツ(side1)
  `page_place_holder_6` TEXT DEFAULT 'side2', -- 予約:サイドコンテンツ2(side2)
  `page_place_holder_7` TEXT DEFAULT 'footer', -- 予約:フッタコンテンツ(footer)
  `page_place_holder_8` TEXT, -- 任意
  `page_place_holder_9` TEXT, -- 任意
  `page_place_holder_10` TEXT, -- 任意
  `page_place_holder_11` TEXT, -- 任意
  `page_place_holder_12` TEXT, -- 任意
  `page_place_holder_13` TEXT, -- 任意
  `page_place_holder_14` TEXT, -- 任意
  `page_place_holder_15` TEXT, -- 任意
  `page_place_holder_16` TEXT, -- 任意
  `page_place_holder_17` TEXT, -- 任意
  `page_place_holder_18` TEXT, -- 任意
  `page_place_holder_19` TEXT, -- 任意
  `page_place_holder_20` TEXT, -- 任意
  `added_at` TEXT DEFAULT CURRENT_TIMESTAMP
);

-- menu_itemsにはpages,tags,categoriesにある全レコードを登録する。更に外部リンクも登録できるようにする
CREATE TABLE menu_items (
  `menu_item_id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `menu_item_name` TEXT, -- メニュー項目名（識別用）
  `menu_item_label` TEXT, -- メニュー表示名
  `menu_item_disabled` INT DEFAULT 0, -- 1 でdisabled
  `menu_item_type_id` INT, -- item_typeに紐づくID
  `menu_item_type` TEXT, -- 種別判別(page,tag,category,ext_link)
  `menu_item_link_to` TEXT, -- リンク先
  `menu_item_link_nofollow_flg` INT DEFAULT 0, -- 1 でnofollow
  `added_at` TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE contents (
  `content_id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `content_name` TEXT, --コンテンツ名（識別用）
  `content_label` TEXT, --コンテンツ表示名
  `content_disabled` INT DEFAULT 0, -- 1 でdisabled
  `content_type` TEXT, -- コンテンツの種類を記述(menu等)
  `content_template` TEXT, -- コンテンツ表示テンプレート
  `content_data` TEXT, -- content_typeによってdataの扱いを変える。menuの場合はmenu_items_id列記等
  `added_at` TEXT DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TEXT
);

CREATE TABLE contents_group (
  `contents_group_id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `contents_group_name` TEXT, --コンテンツグループ名（識別用）
  `contents_group_label` TEXT, --コンテンツグループ表示名
  `contents_ids` TEXT, --所属するコンテンツ（カンマ区切りでcontents_idを表示順に列記）
  `added_at` TEXT DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TEXT
);

CREATE TABLE categories (
  `category_id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `category_name` TEXT, --カテゴリ名（識別用）
  `category_filename` TEXT, --カテゴリ一覧ファイル名
  `category_label` TEXT, --カテゴリ表示名
  `category_title` TEXT, --カテゴリ一覧ページタイトル
  `category_disabled` INT DEFAULT 0, -- 1 でdisabled
  `category_description` TEXT, -- Description記述
  `category_template` TEXT, -- カテゴリ一覧ページに適用するテンプレート名記述
  `added_at` TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tags (
  `tag_id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `tag_name` TEXT, --タグ名（識別用）
  `tag_filename` TEXT, --タグ一覧ファイル名
  `tag_label` TEXT, --タグ表示名
  `tag_title` TEXT, --タグ一覧ページタイトル
  `tag_disabled` INT DEFAULT 0, -- 1 でdisabled
  `tag_description` TEXT, -- Description記述
  `tag_template` TEXT, -- タグ一覧ページに適用するテンプレート名記述
  `added_at` TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE page_group (
  `page_group_id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `page_group_name` TEXT, --ページグループ名（識別用）
  `page_group_filename` TEXT DEFAULT 'index.html', --ページグループファイル名(index.html)
  `page_group_pathname` TEXT, --ページグループ パス名
  `page_group_label` TEXT, --ページグループ表示名
  `page_group_title` TEXT, --ページグループ ページタイトル
  `page_group_disabled` INT DEFAULT 0, -- 1 でdisabled
  `page_group_description` TEXT, -- Description記述
  `page_group_template` TEXT, -- ページグループ一覧ページに適用するテンプレート名記述
  `added_at` TEXT DEFAULT CURRENT_TIMESTAMP
);

今回作る静的CMSのキモは、まさにこのSQLiteのDBなので、判りやすい様にコメントとか付けてみた。

あくまでも前回の構成から脳内で考えて作った物なので、恐らく現状ではまだ不十分だと思う。これからどんどんフィールド追加すると思う。

site：サイト全体に関連する設定等
pages：ページ情報を格納
menu_items：メニュー項目情報を格納
contents：コンテンツ情報を格納
contents_group：コンテンツグループ情報を格納
categories：カテゴリ情報を格納
tags：タグ情報を格納
page_group：ページグループ情報を格納

現状では上記8テーブルの構成になる。コンテンツの集合がコンテンツグループで、このコンテンツグループをページ情報のプレースホルダに割り当ててやる。メインコンテンツだけでなく、ページ内に配置する全てのフィールドを（サイドバーのメニューやヘッダータイトル等も）コンテンツとして扱う。なので、各ページには表示する場所に応じたプレースホルダを用意してやって、そことコンテンツグループを紐付ける感じになってる。今気づいたが、タグとカテゴリの一覧ページとページグループのインデックスページにはプレースホルダを用意していないので、ここにもじきに追加することになる。

カテゴリとタグについてはそのまんま。ページグループはページの集合になる。あとはmenu_itemsだが、ここに全てのページへのリンクを用意してやって、コンテンツとして任意の構成のメニューを作る事を考えている。

多分、このSQL見ただけじゃまだ掴めない人のほうが多いと思う。そういう人は、ページヘのリンクの集まりがメニューで、コンテンツの集合がページで、ページの集合がサイトってのを頭に入れてから見ると少しだけ判るかもしれない。

そして、今回のDBはこれで終わらない。トリガとか作ってみた。

CREATE TRIGGER tri_page_menu_add AFTER INSERT ON pages
BEGIN
  UPDATE pages SET `page_filename` = (
    SELECT
    (SELECT `page_filename_prefix` FROM site) ||
    `page_name` ||
    (SELECT `page_filename_suffix` FROM site) ||
    '.html'
    FROM pages
    WHERE ROWID = new.ROWID
  ) WHERE ROWID = new.ROWID;

  INSERT INTO menu_items (
    `menu_item_type_id`,
    `menu_item_name`,
    `menu_item_label`,
    `menu_item_title`,
    `menu_item_link_to`
  ) SELECT
      `page_id`,
      `page_name`,
      `page_label`,
      `page_title`,
      CASE
        WHEN new.`page_group_id` IS NULL
        THEN `page_filename`
        WHEN new.`page_group_id` IS NOT NULL
        THEN
          (SELECT `page_group_pathname`
          FROM page_group
          WHERE `page_group_id` = new.`page_group_id`) || '/' ||`page_filename`
      END AS filepath
    FROM pages
    WHERE ROWID = new.ROWID;

  UPDATE menu_items SET `menu_item_type` = 'page'
  WHERE ROWID = last_insert_rowid();
END;

CREATE TRIGGER tri_page_menu_del AFTER DELETE ON pages
BEGIN
  DELETE FROM menu_items
  WHERE `menu_item_type` = 'page'
  AND `menu_item_type_id` = old.`page_id`;
END;


CREATE TRIGGER tri_page_menu_disable AFTER UPDATE OF `page_disabled` ON pages
  FOR EACH ROW
    WHEN new.`page_disabled` = 1
BEGIN
  UPDATE menu_items
  SET `menu_item_disabled` = 1
  WHERE `menu_item_type` = 'page'
  AND `menu_item_type_id` = new.`page_id`;
END;

CREATE TRIGGER tri_page_menu_enable AFTER UPDATE OF `page_disabled` ON pages
  FOR EACH ROW
    WHEN new.`page_disabled` = 0
BEGIN
  UPDATE menu_items
  SET `menu_item_disabled` = 0
  WHERE `menu_item_type` = 'page'
  AND `menu_item_type_id` = new.`page_id`;
END;


CREATE TRIGGER tri_contents_update AFTER UPDATE ON contents
BEGIN
  UPDATE contents
  SET `updated_at` = CURRENT_TIMESTAMP
  WHERE `content_id` = new.'content_id';

  UPDATE contents_group
  SET `updated_at` = CURRENT_TIMESTAMP
  WHERE `content_ids` LIKE '%' ||new.`content_id` ||'%';

END;


CREATE TRIGGER tri_category_menu_add AFTER INSERT ON categories
BEGIN
  UPDATE categories SET `category_filename` = (
    SELECT
      (SELECT `category_filename_prefix` FROM site) ||
      `category_name` ||
      (SELECT `category_filename_suffix` FROM site) ||
      '.html'
    FROM categories
    WHERE ROWID = new.ROWID
  ) WHERE ROWID = new.ROWID;

  INSERT INTO menu_items (
    `menu_item_type_id`,
    `menu_item_name`,
    `menu_item_label`,
    `menu_item_title`,
    `menu_item_link_to`
  ) SELECT
      `category_id`,
      `category_name`,
      `category_label`,
      `category_title`,
      `category_filename`
    FROM categories
    WHERE ROWID = new.ROWID;

  UPDATE menu_items SET `menu_item_type` = 'category'
  WHERE ROWID = last_insert_rowid();
END;

CREATE TRIGGER tri_category_menu_del AFTER DELETE ON categories
BEGIN
  DELETE FROM menu_items
  WHERE `menu_item_type` = 'category'
  AND `menu_item_type_id` = old.`category_id`;
END;

CREATE TRIGGER tri_category_menu_disable AFTER UPDATE OF `category_disabled` ON categories
  FOR EACH ROW
    WHEN new.`category_disabled` = 1
BEGIN
  UPDATE menu_items
  SET `menu_item_disabled` = 1
  WHERE `menu_item_type` = 'category'
  AND `menu_item_type_id` = new.`category_id`;
END;

CREATE TRIGGER tri_category_menu_enable AFTER UPDATE OF `category_disabled` ON categories
  FOR EACH ROW
    WHEN new.`category_disabled` = 0
BEGIN
  UPDATE menu_items
  SET `menu_item_disabled` = 0
  WHERE `menu_item_type` = 'category'
  AND `menu_item_type_id` = new.`category_id`;
END;


CREATE TRIGGER tri_tag_menu_add AFTER INSERT ON tags
BEGIN
  UPDATE tags SET `tag_filename` = (
    SELECT
    (SELECT `tag_filename_prefix` FROM site) ||
    `tag_name` ||
    (SELECT `tag_filename_suffix` FROM site) ||
    '.html'
    FROM tags
    WHERE ROWID = new.ROWID
  ) WHERE ROWID = new.ROWID;

  INSERT INTO menu_items (
    `menu_item_type_id`,
    `menu_item_name`,
    `menu_item_label`,
    `menu_item_title`,
    `menu_item_link_to`
  ) SELECT
      `tag_id`,
      `tag_name`,
      `tag_label`,
      `tag_title`,
      `tag_filename`
    FROM tags
    WHERE ROWID = new.ROWID;

  UPDATE menu_items SET `menu_item_type` = 'tag'
  WHERE ROWID = last_insert_rowid();
END;

CREATE TRIGGER tri_tag_menu_del AFTER DELETE ON tags
BEGIN
  DELETE FROM menu_items
  WHERE `menu_item_type` = 'tag'
  AND `menu_item_type_id` = old.`tag_id`;
END;

CREATE TRIGGER tri_tag_menu_disable AFTER UPDATE OF `tag_disabled` ON tags
  FOR EACH ROW
    WHEN new.`tag_disabled` = 1
BEGIN
  UPDATE menu_items
  SET `menu_item_disabled` = 1
  WHERE `menu_item_type` = 'tag'
  AND `menu_item_type_id` = new.`tag_id`;
END;

CREATE TRIGGER tri_tag_menu_enable AFTER UPDATE OF `tag_disabled` ON tags
  FOR EACH ROW
    WHEN new.`tag_disabled` = 0
BEGIN
  UPDATE menu_items
  SET `menu_item_disabled` = 0
  WHERE `menu_item_type` = 'tag'
  AND `menu_item_type_id` = new.`tag_id`;
END;


CREATE TRIGGER tri_page_group_menu_add AFTER INSERT ON page_group
BEGIN
  UPDATE page_group SET `page_group_pathname` = (
    SELECT `page_group_name`
    FROM page_group
    WHERE ROWID = new.ROWID
  ) WHERE ROWID = new.ROWID;

  INSERT INTO menu_items (
    `menu_item_type_id`,
    `menu_item_name`,
    `menu_item_label`,
    `menu_item_title`,
    `menu_item_link_to`
  ) SELECT
      `page_group_id`,
      `page_group_name`,
      `page_group_label`,
      `page_group_title`,
      `page_group_pathname`|| '/' || `page_group_filename`
    FROM page_group
    WHERE ROWID = new.ROWID;

  UPDATE menu_items SET `menu_item_type` = 'page_group'
  WHERE ROWID = last_insert_rowid();
END;

CREATE TRIGGER tri_page_group_menu_del AFTER DELETE ON page_group
BEGIN
  DELETE FROM menu_items
  WHERE `menu_item_type` = 'page_group'
  AND `menu_item_type_id` = old.`page_group_id`;
END;

CREATE TRIGGER tri_page_group_menu_disable AFTER UPDATE OF `page_group_disabled` ON page_group
  FOR EACH ROW
    WHEN new.`page_group_disabled` = 1
BEGIN
  UPDATE menu_items
  SET `menu_item_disabled` = 1
  WHERE `menu_item_type` = 'page_group'
  AND `menu_item_type_id` = new.`page_group_id`;

  UPDATE pages
  SET `page_disabled` = 1
  WHERE `page_group_id` = new.`page_group_id`;

END;

CREATE TRIGGER tri_page_group_menu_enable AFTER UPDATE OF `page_group_disabled` ON page_group
  FOR EACH ROW
    WHEN new.`page_group_disabled` = 0
BEGIN
  UPDATE menu_items
  SET `menu_item_disabled` = 0
  WHERE `menu_item_type` = 'page_group'
  AND `menu_item_type_id` = new.`page_group_id`;

  UPDATE pages
  SET `page_disabled` = 0
  WHERE `page_group_id` = new.`page_group_id`;
END;

CREATE TRIGGER tri_page_group_update_path AFTER UPDATE OF `page_group_pathname` ON page_group
BEGIN
  UPDATE menu_items
  SET `menu_item_link_to` = new.`page_group_pathname`|| '/' || new.`page_group_filename`
  WHERE `menu_item_type` = 'page_group'
  AND `menu_item_type_id` = new.`page_group_id`;

  UPDATE menu_items
  SET `menu_item_link_to` = REPLACE(`menu_item_link_to`, old.`page_group_pathname`, new.`page_group_pathname`)
  WHERE `menu_item_type` = 'page';
END;

今回のCMSではテーブル間の連携とかはトリガを活用していこうと思う。出来る限りプログラムでの動作を減らしたい。プログラム側で書くのはシンプルなCRUDぐらいにして、SQLで出来る部分は出来る限りSQLで書くって事にしてる。普段書き慣れていないので、調べながら書いていて結構ストレスが溜まったりもするのだが、多分に意味がある事だと思うので我慢して書いている。どういう意味があるのかは勘の良い人ならピンとくるんじゃなかろうか。ヒントはjinja2とTwigかな。細かい事は次回記事で説明したいと思う。