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

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

### 1. 入力データ ###
#### 1) 本文情報 ####
phpMyFAQに移行したいデータを、下記レイアウトのExcelシートに展開しておきます。
各項目とも、セル内改行や半角カンマなどのゴミデータがない状態にしておきます。
<table>
<tr>
    <td>ID</td><td>【必須】1からの連番</td>
</tr>
<tr>
    <td>タイトル</td><td>【必須】</td>
</tr>
<tr>
    <td>本文</td><td>【必須】</td>
</tr>
<tr>
    <td>キーワード</td><td>あれば</td>
</tr>
<tr>
    <td>投稿者</td><td></td>
</tr>
<tr>
    <td>投稿者メルアド</td><td></td>
</tr>
<tr>
    <td>備考</td><td></td>
</tr>
<tr>
    <td>親カテゴリー</td><td>ないときは"-"</td>
</tr>
<tr>
    <td>子カテゴリー</td><td>ないときは"-"</td>
</tr>
<tr>
    <td>タグ</td><td></td>
</tr>
</table>

#### 2) カテゴリー定義 ####
1)の親カテゴリー、子カテゴリーから抽出し、それぞれユニークになるよう内容を整理します。
類似の内容や不要文字は、この段階で削除しておきます。
***
### 2. 出力データ ###
基本的なphpMyFAQの動作に必要なテーブルは次の８つです。
最後の２つのtag関連のデータは指定しなくてもOKですが、タグを指定すると関連データを素早く抽出できたり・・・と格段に使い勝手がよくなります。
・faqdata：記事の本文の情報を格納
・faqdata_user：各記事の作成ユーザーの情報を格納
・faqcategories：記事のカテゴリーとその親子関係を格納
・faqcategory_user：各カテゴリーの作成ユーザーの情報を格納
・faqcategory_group：各カテゴリーの作成グループの情報を格納
・faqcategoryrelations：各記事とカテゴリーの関連を格納
・faqtags：指定したタグの情報を格納
・faqdata_tags：各記事とタグの関連を格納
***
### 3. 各移行データの作成 ###
#### 1) ワークテーブル作成 ####
最初に、Accessに次の２つのワークテーブルを作成します。
○faqdata_tmp
<table>
<tr>
    <td>id</td><td>長整数型</td>
</tr>
<tr>
    <td>thema</td><td>長いテキスト</td>
</tr>
<tr>
    <td>content</td><td>長いテキスト</td>
</tr>
<tr>
    <td>keyword</td><td>長いテキスト</td>
</tr>
<tr>
    <td>author</td><td>長いテキスト</td>
</tr>
<tr>
    <td>email</td><td>短いテキスト(255)</td>
</tr>
<tr>
    <td>notes</td><td>長いテキスト</td>
</tr>
<tr>
    <td>親カテゴリー</td><td>短いテキスト(255)</td>
</tr>
<tr>
    <td>子カテゴリー</td><td>短いテキスト(255)</td>
</tr>
<tr>
    <td>タグ</td><td>短いテキスト(255)</td>
</tr>
</table>
○faqcategories_tmp
<table>
<tr>
    <td>id</td><td>長整数型</td>
</tr>
<tr>
    <td>parent_id</td><td>長整数型</td>
</tr>
<tr>
    <td>category_name</td><td>短いテキスト(255)</td>
</tr>
<tr>
    <td>description</td><td>短いテキスト(255)</td>
</tr>
</table>
    
#### 2) faqdata作成 ####
入力のExcelデータをコピー⇒faqdata_tmpテーブルに貼り付けます。
その後、以下のクエリーを定義します。
``` SQL: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タグを付与する関数です。
``` VB: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ユーザーのようです。
``` SQL
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：備考や説明（任意）

その後、次のクエリーを定義します。
``` SQL
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作成 ####
次のクエリーを定義します。
``` SQL
SELECT
    id AS category_id,
    -1 AS user_id
FROM faqcategories_tmp
WHERE ID <> 0
ORDER BY id;
```
#### 6) faqcategory_group作成 ####
次のクエリーを定義します。
``` SQL
SELECT
    id AS category_id,
    -1 AS user_id
FROM faqcategories_tmp
WHERE ID <> 0
ORDER BY id;
```
#### 7) faqcategoryrelations作成 ####
記事とカテゴリーの紐づけを抽出しますが、親子関係があるため少々煩雑です。
``` SQL:カテゴリーキー1
SELECT
    id AS record_id,
    [親カテゴリー] & [子カテゴリー] AS category
FROM faqdata_tmp;
```
``` SQL:カテゴリーキー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;
```
``` SQL:記事と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作成 ####
記事に定義したタグの情報を格納します。
##### ①タグ情報抽出：記事からユニークなタグの値を抽出 #####
``` SQL:タグ情報抽出
SELECT DISTINCT タグ
FROM faqdata_tmp
WHERE タグ Is Not Null
ORDER BY タグ;
```
##### ②faqtags作成 #####
idを採番してタグ情報を登録します。
``` SQL
SELECT
    DCount("*", "タグ情報抽出","[タグ] <= `" & [タグ] & "'") AS tagging_id,
    Replace([タグ], Chr(10), "") AS tagging_name
FROM [タグ情報抽出];
```
#### 9) faqdata_tags作成 ####
記事とタグ情報の関連を登録します。
``` SQL
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なので、次のように指定します。
``` VB
    DoCmd.TransferText acExportDelim, , "faqdata作成", "C:\faqdata.csv", False, , 65001
```
phpMyAdminなどのデータベース管理ツールを使い、出力したCSVファイルをインポートします。