phpMyFAQの業務導入顛末記（その3）～データ移行編（改訂版）～ #access

以前こちらで、Excelシートにストックしていたナレッジ情報から、ゴリゴリと力技で移行データを作成する方法を記述していました。
ただ、その後Accessを使って比較的簡単に移行データを出力できるツールを作成したので、その内容について記します。

1. 入力データ

1) 本文情報

phpMyFAQに移行したいデータを、下記レイアウトのExcelシートに展開しておきます。
各項目とも、セル内改行や半角カンマなどのゴミデータがない状態にしておきます。

ID	【必須】1からの連番
タイトル	【必須】
本文	【必須】
キーワード	あれば
投稿者
投稿者メルアド
備考
親カテゴリー	ないときは"-"
子カテゴリー	ないときは"-"
タグ

2) カテゴリー定義

1)の親カテゴリー、子カテゴリーから抽出し、それぞれユニークになるよう内容を整理します。
類似の内容や不要文字は、この段階で削除しておきます。

2. 出力データ

基本的なphpMyFAQの動作に必要なテーブルは次の８つです。
最後の２つのtag関連のデータは指定しなくてもOKですが、タグを指定すると関連データを素早く抽出できたり・・・と格段に使い勝手がよくなります。
・faqdata：記事の本文の情報を格納
・faqdata_user：各記事の作成ユーザーの情報を格納
・faqcategories：記事のカテゴリーとその親子関係を格納
・faqcategory_user：各カテゴリーの作成ユーザーの情報を格納
・faqcategory_group：各カテゴリーの作成グループの情報を格納
・faqcategoryrelations：各記事とカテゴリーの関連を格納
・faqtags：指定したタグの情報を格納
・faqdata_tags：各記事とタグの関連を格納

3. 各移行データの作成

1) ワークテーブル作成

最初に、Accessに次の２つのワークテーブルを作成します。
○faqdata_tmp

id	長整数型
thema	長いテキスト
content	長いテキスト
keyword	長いテキスト
author	長いテキスト
email	短いテキスト(255)
notes	長いテキスト
親カテゴリー	短いテキスト(255)
子カテゴリー	短いテキスト(255)
タグ	短いテキスト(255)

○faqcategories_tmp

id	長整数型
parent_id	長整数型
category_name	短いテキスト(255)
description	短いテキスト(255)

2) faqdata作成

入力のExcelデータをコピー⇒faqdata_tmpテーブルに貼り付けます。
その後、以下のクエリーを定義します。

faqdata作成

SELECT
    id,
    "ja" AS lang,
    [id] + 1000 AS solution_id,
    0 AS revision_id,
    "yes" AS active,
    0 AS sticky,
    keyword AS keywords,
    Replace([thema], ",", "") AS 式1, 
    fPb_HTML内容作成([content]) AS 式2,  -- 平文をHTML変換：下記参照
    author,
    email,
    "y" AS comment,
    Format$(Now(), "yyyymmddhhnnss") AS 式3,
    "" AS links_state,
    0 AS links_check_date,
    "00000000000000" AS date_start,
    "99991231235959" AS date_end,
    Now() AS created,
    notes
FROM faqdata_tmp;

次のユーザー定義関数も定義します。
本文内容に、文字装飾用のHTMLタグを付与する関数です。

fPb_HTML内容作成

Public Function fPb_HTML内容作成( _
    pVl_content As Variant _
    ) As String

    If Nz(pVl_content, "") = "" Then
        fPb_HTML内容作成 = ""
        Exit Function
    End If

    Dim 文字列 As String
    ' ゴミデータを除去
    文字列 = Replace(pVl_content, vbCrLf, "<br />")  ' CRLF
    文字列 = Replace(文字列, vbLf, "<br />")          ' LF
    文字列 = Replace(文字列, Chr(34), "")             ' ダブルクォーテーション
    文字列 = Replace(文字列, ",", "")                 ' カンマ
    fPb_HTML内容作成 = "<!DOCTYPE html>" & vbCrLf
    fPb_HTML内容作成 = fPb_HTML内容作成 & "<html>" & vbCrLf
    fPb_HTML内容作成 = fPb_HTML内容作成 & "<head></head>" & vbCrLf
    fPb_HTML内容作成 = fPb_HTML内容作成 & "<body>" & vbCrLf
    fPb_HTML内容作成 = fPb_HTML内容作成 & "<p>" & 文字列 & "</p>" & vbCrLf
    fPb_HTML内容作成 = fPb_HTML内容作成 & "</body>" & vbCrLf
    fPb_HTML内容作成 = fPb_HTML内容作成 & "</html>"

End Function

3) faqdata_user作成

次のクエリーを定義します。
user_id = -1はadminユーザーのようです。

SELECT
    id AS record_id,
    -1 AS user_id
FROM faqdata_tmp
ORDER BY id;

4) faqcategories作成

移行元データに記された親カテゴリー・子カテゴリーの内容を、faqcategory_tmpテーブルに入力します。
数が多かったり、継続的にカテゴリーの追加があるようであれば、Accessフォームで入力画面を作るといいかもしれません。

（親カテゴリーの時）
・id：1～の連番
・parent_id：0
・category_name：親カテゴリー名
・description：備考や説明（任意）

（子カテゴリーの時）
・id：1～の連番（親含めて全体で重複なし）
・parent_id：親カテゴリーのid
・category_name：子カテゴリー名
・description：備考や説明（任意）

その後、次のクエリーを定義します。

SELECT
    id,
    "ja" AS lang,
    parent_id,
    category_name,
    description,
    1 AS user_id,
    0 AS group_id,
    1 AS active,
    "" AS image,
    1 AS show_home
FROM faqcategories_tmp;

5) faqcategory_user作成

次のクエリーを定義します。

SELECT
    id AS category_id,
    -1 AS user_id
FROM faqcategories_tmp
WHERE ID <> 0
ORDER BY id;

6) faqcategory_group作成

次のクエリーを定義します。

SELECT
    id AS category_id,
    -1 AS user_id
FROM faqcategories_tmp
WHERE ID <> 0
ORDER BY id;

7) faqcategoryrelations作成

記事とカテゴリーの紐づけを抽出しますが、親子関係があるため少々煩雑です。

カテゴリーキー1

SELECT
    id AS record_id,
    [親カテゴリー] & [子カテゴリー] AS category
FROM faqdata_tmp;

カテゴリーキー2

-- 親カテゴリー
SELECT
    faqcategories_子.id AS category_id,
    faqcategories_親.category_name & faqcategories_子.category_name AS category
FROM faqcategories_親
INNER JOIN faqcategories AS faqcategories_子
ON faqcategories_親.id = faqcategories_子.id
WHERE faqcategories_親 <> 0
UNION
-- 子カテゴリー
SELECT
    id AS category_id,
    category_name & "-" AS category
FROM faqcategories_tmp
WHERE parent_id = 0
AND id <> 0
ORDER BY category_id;

記事とfaqcategoriesとの紐づけ⇒faqcategoryrelationsのデータ

SELECT
    [カテゴリーキー2].category_id,
    "ja" AS category_lang,
    [カテゴリーキー1].record_id,
    "ja" AS record_lang
FROM [カテゴリーキー2]
RIGHT JOIN [カテゴリーキー1]
ON [カテゴリーキー2].category = [カテゴリーキー1].category;

8) faqtags作成

記事に定義したタグの情報を格納します。

①タグ情報抽出：記事からユニークなタグの値を抽出

タグ情報抽出

SELECT DISTINCT タグ
FROM faqdata_tmp
WHERE タグ Is Not Null
ORDER BY タグ;

②faqtags作成

idを採番してタグ情報を登録します。

SELECT
    DCount("*", "タグ情報抽出","[タグ] <= `" & [タグ] & "'") AS tagging_id,
    Replace([タグ], Chr(10), "") AS tagging_name
FROM [タグ情報抽出];

9) faqdata_tags作成

記事とタグ情報の関連を登録します。

SELECT
    id AS record_id,
    faqtags作成.tagging_id
FROM faqdata_tmp
INNER JOIN faqtags作成
ON faqdata_tmp.[タグ] = faqtags作成.tagging_name;

4. phpMyFAQへのデータ取り込み

以上で作成した各クエリーを使い、次のようにVBAでカンマ区切りのテキストファイルにエクスポートします。
phpMyFAQのデータベースは文字コードがUTF-8なので、次のように指定します。

    DoCmd.TransferText acExportDelim, , "faqdata作成", "C:\faqdata.csv", False, , 65001

phpMyAdminなどのデータベース管理ツールを使い、出力したCSVファイルをインポートします。