# データベース設計書

## 概要

本ドキュメントは、FreeBSD ソースツリー (freebsd-src-main) に含まれるSQLiteベースのデータベーススキーマを網羅的に記載したデータベース設計書である。本プロジェクトはOSカーネル・ユーザランドのソースコードであり、アプリケーションレベルのRDBMSを中心としたシステムではないが、以下の3つのサブシステムにおいてSQLiteデータベースが利用されている。

1. **Kyua テストフレームワーク** (`contrib/kyua/store/`) -- テスト実行結果の永続化ストア。スキーマバージョン v1 から v3 まで存在し、現行は v3。
2. **WPA Supplicant / hostapd** (`contrib/wpa/`) -- EAP認証ユーザ管理、RADIUSセッション管理、EAP-SIM再認証情報の永続化。
3. **Heimdal Kerberos** (`crypto/heimdal/`) -- HDB (Heimdal Database) のSQLiteバックエンドおよびクレデンシャルキャッシュ (scache)。

## テーブル一覧

### Kyua テストストア (schema v3 -- 現行)

| テーブル名 | 対応エンティティ | 説明 |
| --- | --- | --- |
| metadata | データベースメタデータ | スキーマバージョンとタイムスタンプの履歴管理 |
| contexts | 実行コンテキスト | テスト実行時の作業ディレクトリ情報 |
| env_vars | 環境変数 | 実行コンテキストにおける環境変数 |
| metadatas | メタデータプロパティ | テストプログラム・テストケースのメタデータ (キーバリュー形式) |
| test_programs | テストプログラム | テストプログラムのパス・所属テストスイート・インターフェース情報 |
| test_cases | テストケース | テストプログラムに属する個別のテストケース |
| test_results | テスト結果 | テストケースの実行結果 (結果種別・理由・開始/終了時刻) |
| test_case_files | テストケース出力ファイル | テストケースが生成したファイル (stdout/stderr含む) |
| files | ファイル実体 | テスト中に生成されたファイルのBLOBデータ |

### WPA / hostapd EAP認証 (hostapd.eap_user_sqlite)

| テーブル名 | 対応エンティティ | 説明 |
| --- | --- | --- |
| users | EAPユーザ | EAP認証ユーザの資格情報 |
| wildcards | ワイルドカードルール | パターンマッチによるEAPメソッド割当 |
| authlog | 認証ログ | 認証イベントのログ記録 |
| pending_tc | T&C保留端末 | Terms and Conditions承認待ちの端末情報 |
| current_sessions | セッション | 現在アクティブな認証セッション |

### WPA / EAP-SIM データベース (eap_sim_db.c)

| テーブル名 | 対応エンティティ | 説明 |
| --- | --- | --- |
| pseudonyms | 擬似名マッピング | EAP-SIM/AKA永続IDと擬似名のマッピング |
| reauth | 再認証情報 | EAP-SIM/AKA再認証に必要な鍵・カウンタ情報 |

### WPA / RADIUS サーバ (radius_server.c)

| テーブル名 | 対応エンティティ | 説明 |
| --- | --- | --- |
| radius_attributes | RADIUS属性 | RADIUSリクエスト/レスポンスの属性値記録 |
| sim_provisioning | SIMプロビジョニング | SIMプロビジョニング情報 |

### Heimdal HDB SQLiteバックエンド (hdb-sqlite.c)

| テーブル名 | 対応エンティティ | 説明 |
| --- | --- | --- |
| Version | バージョン管理 | HDBスキーマバージョン番号 |
| Principal | Kerberosプリンシパル | プリンシパル名とエントリへの参照 |
| Entry | Kerberosエントリ | プリンシパルに対応するバイナリエントリデータ |

### Heimdal クレデンシャルキャッシュ (scache.c)

| テーブル名 | 対応エンティティ | 説明 |
| --- | --- | --- |
| master | マスター設定 | バージョンとデフォルトキャッシュ名 |
| caches | キャッシュ | クレデンシャルキャッシュのインスタンス |
| credentials | クレデンシャル | キャッシュに格納されたKerberosクレデンシャル |
| principals | プリンシパル | クレデンシャルに関連付けられたプリンシパル名 |

## 各テーブル定義

---

### 1. Kyua テストストア

---

#### 1.1 metadata

データベースのスキーマバージョンとタイムスタンプを管理するテーブル。行は不変であり、スキーマ更新時に新しいレコードが追加される。最も大きい schema_version を持つ行が有効なレコードとなる。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| schema_version | INTEGER | NOT NULL | スキーマバージョン番号 (PRIMARY KEY, CHECK >= 1) |
| timestamp | TIMESTAMP | NOT NULL | レコード作成時刻 (UNIX epoch, CHECK >= 0) |

#### 1.2 contexts

テスト実行時の実行コンテキストを記録するテーブル。デバッグおよび情報記録用途。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| cwd | TEXT | NOT NULL | テスト実行時のカレントワーキングディレクトリ |

備考: v3ではcontext_idカラムおよびAUTOINCREMENTが削除されている (v2からの変更)。1回の実行につき1コンテキストのみ。

#### 1.3 env_vars

実行コンテキストの環境変数を格納するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| var_name | TEXT | NOT NULL | 環境変数名 (PRIMARY KEY) |
| var_value | TEXT | NOT NULL | 環境変数値 |

#### 1.4 metadatas

テストプログラムおよびテストケースのメタデータをキーバリュー形式で格納するテーブル。metadata_id によって同一オブジェクトのプロパティ群をグルーピングする。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| metadata_id | INTEGER | NOT NULL | メタデータグループID (複合PRIMARY KEYの一部) |
| property_name | TEXT | NOT NULL | プロパティ名 (複合PRIMARY KEYの一部) |
| property_value | TEXT | NULL | プロパティ値 |

インデックス: `index_metadatas_by_id` ON metadatas (metadata_id)

#### 1.5 test_programs

テストプログラムの情報を格納するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| test_program_id | INTEGER | NOT NULL | テストプログラムID (PRIMARY KEY AUTOINCREMENT) |
| absolute_path | TEXT | NOT NULL | テストプログラムの絶対パス |
| root | TEXT | NOT NULL | テストスイートのルートパス (Kyuafileの配置場所) |
| relative_path | TEXT | NOT NULL | ルートからの相対パス |
| test_suite_name | TEXT | NOT NULL | テストスイート名 |
| metadata_id | INTEGER | NULL | metadatasテーブルへの参照 |
| interface | TEXT | NOT NULL | テストプログラムインターフェース名 |

#### 1.6 test_cases

テストケースの情報を格納するテーブル。テストプログラムに属する。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| test_case_id | INTEGER | NOT NULL | テストケースID (PRIMARY KEY AUTOINCREMENT) |
| test_program_id | INTEGER | NULL | テストプログラムへの外部キー (REFERENCES test_programs) |
| name | TEXT | NOT NULL | テストケース名 |
| metadata_id | INTEGER | NULL | metadatasテーブルへの参照 |

インデックス: `index_test_cases_by_test_programs_id` ON test_cases (test_program_id)

#### 1.7 test_results

テストケースの実行結果を格納するテーブル。テストケースと1:1の関係。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| test_case_id | INTEGER | NOT NULL | テストケースID (PRIMARY KEY, REFERENCES test_cases) |
| result_type | TEXT | NOT NULL | 結果種別 (passed, failed, skipped等) |
| result_reason | TEXT | NULL | 結果の理由 |
| start_time | TIMESTAMP | NOT NULL | テスト開始時刻 |
| end_time | TIMESTAMP | NOT NULL | テスト終了時刻 |

#### 1.8 test_case_files

テストケースが生成した出力ファイルの参照を保持するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| test_case_id | INTEGER | NOT NULL | テストケースID (REFERENCES test_cases, 複合PRIMARY KEYの一部) |
| file_name | TEXT | NOT NULL | ファイル名 (`__STDOUT__`, `__STDERR__` は予約名, 複合PRIMARY KEYの一部) |
| file_id | INTEGER | NOT NULL | ファイル実体への外部キー (REFERENCES files) |

#### 1.9 files

テスト中に生成されたファイルやログのBLOBデータを格納するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| file_id | INTEGER | NOT NULL | ファイルID (PRIMARY KEY) |
| contents | BLOB | NOT NULL | ファイル内容 |

---

### 2. WPA / hostapd EAP認証

---

#### 2.1 users

EAP認証のユーザ情報を管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| identity | TEXT | NOT NULL | ユーザ識別子 (PRIMARY KEY) |
| methods | TEXT | NULL | 許可されるEAPメソッド (例: TTLS-MSCHAPV2) |
| password | TEXT | NULL | パスワード |
| remediation | TEXT | NULL | 修復情報 |
| phase2 | INTEGER | NULL | Phase 2認証フラグ |
| t_c_timestamp | INTEGER | NULL | Terms and Conditions承認タイムスタンプ |

#### 2.2 wildcards

ワイルドカードによるEAPメソッド割当ルールを管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| identity | TEXT | NOT NULL | ワイルドカードパターン (PRIMARY KEY) |
| methods | TEXT | NULL | 許可されるEAPメソッド |

#### 2.3 authlog

認証イベントのログを記録するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| timestamp | TEXT | NULL | イベント発生時刻 |
| session | TEXT | NULL | セッション識別子 |
| nas_ip | TEXT | NULL | NAS (Network Access Server) のIPアドレス |
| username | TEXT | NULL | ユーザ名 |
| note | TEXT | NULL | 備考 |

#### 2.4 pending_tc

Terms and Conditions承認待ちの端末情報を管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| mac_addr | TEXT | NOT NULL | MACアドレス (PRIMARY KEY) |
| identity | TEXT | NULL | ユーザ識別子 |

#### 2.5 current_sessions

現在アクティブな認証セッションを管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| mac_addr | TEXT | NOT NULL | MACアドレス (PRIMARY KEY) |
| identity | TEXT | NULL | ユーザ識別子 |
| start_time | TEXT | NULL | セッション開始時刻 |
| nas | TEXT | NULL | NAS識別子 |
| hs20_t_c_filtering | BOOLEAN | NULL | Hotspot 2.0 T&Cフィルタリング有効フラグ |
| waiting_coa_ack | BOOLEAN | NULL | CoA (Change of Authorization) ACK待機フラグ |
| coa_ack_received | BOOLEAN | NULL | CoA ACK受信済みフラグ |

---

### 3. WPA / EAP-SIM データベース

---

#### 3.1 pseudonyms

EAP-SIM/AKAプロトコルにおける永続IDと擬似名のマッピングを管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| permanent | CHAR(21) | NOT NULL | 永続ID (PRIMARY KEY) |
| pseudonym | CHAR(21) | NOT NULL | 擬似名 |

#### 3.2 reauth

EAP-SIM/AKA再認証に必要な情報を管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| permanent | CHAR(21) | NOT NULL | 永続ID (PRIMARY KEY) |
| reauth_id | CHAR(21) | NOT NULL | 再認証ID |
| counter | INTEGER | NULL | 再認証カウンタ |
| mk | CHAR(40) | NULL | マスターキー |
| k_encr | CHAR(32) | NULL | 暗号化キー |
| k_aut | CHAR(64) | NULL | 認証キー |
| k_re | CHAR(64) | NULL | 再認証キー |

---

### 4. WPA / RADIUS サーバ

---

#### 4.1 radius_attributes

RADIUSリクエスト/レスポンスの属性値を記録するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| id | INTEGER | NOT NULL | レコードID (PRIMARY KEY) |
| sta | TEXT | NULL | ステーション (端末) 識別子 |
| reqtype | TEXT | NULL | リクエスト種別 |
| attr | TEXT | NULL | 属性値 |

#### 4.2 sim_provisioning

SIMプロビジョニング情報を管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| mobile_identifier_hash | TEXT | NOT NULL | モバイル識別子ハッシュ (PRIMARY KEY) |
| imsi | TEXT | NULL | IMSI (International Mobile Subscriber Identity) |
| mac_addr | TEXT | NULL | MACアドレス |
| eap_method | TEXT | NULL | EAPメソッド |
| timestamp | TEXT | NULL | プロビジョニング時刻 |

---

### 5. Heimdal HDB SQLiteバックエンド

---

#### 5.1 Version

HDBデータベースのスキーマバージョンを管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| number | REAL | NULL | スキーマバージョン番号 |

#### 5.2 Principal

Kerberosプリンシパル情報を管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| id | INTEGER | NOT NULL | プリンシパルID (PRIMARY KEY) |
| principal | TEXT | NOT NULL | プリンシパル名 (UNIQUE) |
| canonical | INTEGER | NULL | 正規プリンシパルかどうかのフラグ |
| entry | INTEGER | NULL | Entryテーブルへの参照 |

#### 5.3 Entry

Kerberosプリンシパルに対応するバイナリエントリデータを格納するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| id | INTEGER | NOT NULL | エントリID (PRIMARY KEY) |
| data | BLOB | NULL | エントリデータ (エンコード済み) |

---

### 6. Heimdal クレデンシャルキャッシュ (scache)

---

#### 6.1 master

クレデンシャルキャッシュのマスター設定を管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| oid | INTEGER | NOT NULL | レコードID (PRIMARY KEY) |
| version | INTEGER | NOT NULL | バージョン番号 |
| defaultcache | TEXT | NOT NULL | デフォルトキャッシュ名 |

#### 6.2 caches

個々のクレデンシャルキャッシュインスタンスを管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| oid | INTEGER | NOT NULL | キャッシュID (PRIMARY KEY) |
| principal | TEXT | NULL | キャッシュのプリンシパル名 |
| name | TEXT | NOT NULL | キャッシュ名 |

トリガー: `CacheDropCreds` -- caches削除時に対応するcredentialsを自動削除。

#### 6.3 credentials

キャッシュに格納されたKerberosクレデンシャルを管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| oid | INTEGER | NOT NULL | クレデンシャルID (PRIMARY KEY) |
| cid | INTEGER | NOT NULL | 所属キャッシュID (cachesテーブルへの参照) |
| kvno | INTEGER | NOT NULL | キーバージョン番号 |
| etype | INTEGER | NOT NULL | 暗号化タイプ |
| created_at | INTEGER | NOT NULL | 作成時刻 |
| cred | BLOB | NOT NULL | クレデンシャルデータ |

トリガー: `credDropPrincipal` -- credentials削除時に対応するprincipalsを自動削除。

#### 6.4 principals

クレデンシャルに関連付けられたプリンシパル情報を管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| oid | INTEGER | NOT NULL | プリンシパルID (PRIMARY KEY) |
| principal | TEXT | NOT NULL | プリンシパル名 |
| type | INTEGER | NOT NULL | プリンシパル種別 |
| credential_id | INTEGER | NOT NULL | credentialsテーブルへの参照 |

## 備考

- 全てのデータベースはSQLiteを使用しており、FreeBSDソースツリーに含まれるcontribパッケージの一部として提供されている。
- Kyuaのスキーマはv1からv3までのマイグレーションパスが提供されている (`migrate_v1_v2.sql`, `migrate_v2_v3.sql`)。v2ではmetadatasテーブルの導入とATF/plain固有テーブルの廃止が行われ、v3では複数アクション管理 (actions, context_id) の廃止により1データベース1実行の構造に簡素化された。
- WPA/hostapd関連のデータベースはC言語のソースコード内にSQL文字列として埋め込まれており、独立した `.sql` ファイルとしては存在しない (`hostapd.eap_user_sqlite` を除く)。
- Heimdal HDBおよびscacheのスキーマもCソースコード内のマクロ定義として記述されている。
- Kyuaのtest_case_filesテーブルでは `__STDOUT__` と `__STDERR__` という特別なファイル名が予約されている。
