0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@Nasmac(Nasmac)

# 桐をWebで蘇らせる ― DataDrawers開発記-第9回

0
Posted at

第9回: データソース管理とビュー作成 - 複数DB対応への第一歩

データソース機能の実装

実務では、複数のSQL Serverインスタンスや異なるデータベースに接続する必要がある。

例えば:

開発環境: localhost:1433
本番環境: prod-server:1433
テスト環境: test-server:1433

それぞれに異なる接続情報（サーバー、ポート、データベース名、認証情報）が必要だ。

データソース管理の設計

DataDrawersでは、データソースという概念を導入し、複数の接続先を管理できるようにした。

// prisma/schema.prisma
model DataSource {
  id          String   @id @default(cuid())
  name        String   // "開発環境"、"本番環境"など
  type        String   // "mssql", "mysql", "postgres"
  host        String
  port        Int
  database    String
  username    String
  password    String   // 実装では暗号化して保存
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
}

データソースの登録

ユーザーは、ダイアログから新しいデータソースを登録できる:

┌─────────────────────────────────────┐
│ データソースを追加                    │
├─────────────────────────────────────┤
│ 名前: [開発環境___________________] │
│ 種類: [SQL Server ▼]                │
│ ホスト: [localhost_______________]  │
│ ポート: [1433____________________]  │
│ DB名: [StudentDB________________]   │
│ ユーザー: [sa___________________]   │
│ パスワード: [••••••••••••••••••] │
│                                     │
│ [接続テスト] [キャンセル] [保存]     │
└─────────────────────────────────────┘

接続テストボタンで、実際に接続できるか確認できる:

// app/api/datasources/test/route.ts
export async function POST(request: NextRequest) {
  const { type, host, port, database, username, password } = await request.json();

  try {
    if (type === 'mssql') {
      const pool = await sql.connect({
        server: host,
        port: port,
        database: database,
        user: username,
        password: password,
        options: { encrypt: true, trustServerCertificate: true }
      });

      await pool.request().query('SELECT 1');
      await pool.close();

      return NextResponse.json({ success: true, message: '接続成功' });
    }
  } catch (error) {
    return NextResponse.json({
      success: false,
      error: error instanceof Error ? error.message : '接続失敗'
    });
  }
}

データソースの切り替え

登録したデータソースは、ヘッダーのドロップダウンで即座に切り替えられる:

┌──────────────────────────────────────────┐
│ DataDrawers   [開発環境 ▼] [設定] [ヘルプ] │
│                 ├ 開発環境 (現在)        │
│                 ├ 本番環境              │
│                 └ テスト環境            │
└──────────────────────────────────────────┘

データソースを切り替えると、インポート済みのテーブル一覧が自動的に更新される。各テーブルはどのデータソースからインポートされたかを記録している:

// prisma/schema.prisma
model ImportedTable {
  id            String      @id
  sqlTableName  String
  displayName   String
  objectType    String
  columns       String      // JSON
  importedAt    DateTime
  lastSyncAt    DateTime?
  customColumns String?     // JSON
  dataSourceId  String?     // データソースID
  dataSource    DataSource? @relation(fields: [dataSourceId], references: [id])
}

これにより、「このテーブルは開発環境からインポートした」という情報を保持できる。

マルチDB対応の意義

データソース機能により、以下が実現できる:

  1. 環境の切り替え
    • 開発→本番へワンクリックで切り替え
    • それぞれの環境で独立してデータ管理
  2. 複数プロジェクトの管理
    • プロジェクトA、プロジェクトBのデータを同じツールで管理
    • データソースを切り替えるだけで操作対象を変更
  3. 将来のMySQL/PostgreSQL対応
    • データソースの type フィールドで種類を管理
    • アダプターパターンで実装を切り替え

桐の「補集合」とは

桐（Kiri）には、独特の機能がいくつかある。その中でも特に便利なのが補集合だ。

例えば、こんなケース:

全生徒1,000人のうち、面談済みの生徒が800人いる。 未面談の200人のリストを作りたい。

普通のデータベースソフトなら:

  1. 全生徒のリストを開く
  2. フィルタで「面談済み = はい」を適用 → 800人が表示される
  3. ...ここで詰まる。「未面談の200人」をどうやって抽出する？

桐なら:

  1. 全生徒のリストを開く
  2. フィルタで「面談済み = はい」を適用 → 800人が表示される
  3. 「補集合」ボタンをクリック → 未面談の200人が表示される

これだけ。

補集合とは、「選択した行以外」を取得する機能。桐では当たり前のように使われていたが、他のデータベースソフトではあまり見かけない。

補集合の実装（SQLite版）

DataDrawersでも、この機能を実装した。ただし、IndexedDBからSQLiteに移行したため、実装方法が変わった。

IndexedDB版（旧）

IndexedDB版では、ブラウザに読み込まれたデータ内で補集合を計算していた:

// 旧実装（IndexedDB版）
const allData = await db.tableData.where('tableId').equals(tableId).toArray();
const currentIds = currentFilteredData.map(row => row.id);
const complementData = allData.filter(row => !currentIds.includes(row.id));

問題点: ブラウザに読み込んだデータが10万件だった場合、残りの114万件が計算対象外になる。

SQLite版（新）

SQLite版では、サーバー側のSQLiteデータベース全体から補集合を計算する:

// components/toolbar.tsx
const handleComplementSet = async () => {
  if (filterSteps.length === 0 || !currentTable) return;

  setIsCalculatingComplement(true);

  try {
    // 現在のフィルタ結果のIDを取得
    const lastStep = filterSteps[filterSteps.length - 1];
    const idField = lastStep.column;
    const currentIds = currentFilteredData.map(row => row[idField]);

    console.log('[補集合] 現在のフィルタ結果:', currentIds.length, '件');

    // SQLite APIを呼び出して補集合を計算
    const response = await fetch(`/api/tables/${encodeURIComponent(currentTable)}/complement`, {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({
        columnName: idField,
        currentIds: currentIds,
        previousFilterIds: null  // 全データから計算
      })
    });

    const result = await response.json();

    if (result.success) {
      console.log('[補集合] 補集合のID数:', result.complementIds.length);

      // 最後のステップを削除してから補集合ステップを追加
      removeFilterStepFromCurrentTable(filterSteps[filterSteps.length - 1].id);

      addFilterStepToCurrentTable({
        id: generateId(),
        column: idField,
        operator: 'IN',
        value: result.complementIds,
        resultCount: result.complementIds.length,
        timestamp: new Date()
      });
    }
  } catch (error) {
    console.error('[補集合] エラー:', error);
  } finally {
    setIsCalculatingComplement(false);
  }
};

APIルート側の処理

// app/api/tables/[tableId]/complement/route.ts
export async function POST(request: NextRequest, { params }: { params: { tableId: string } }) {
  const { columnName, currentIds } = await request.json();

  // 現在のIDをSetに変換（高速検索用）
  const currentIdSet = new Set(currentIds.map(id => String(id)));

  // SQLiteテーブルの全データから補集合を計算
  const query = `SELECT "${columnName}" FROM "${tableId}"`;
  const allRows: any[] = await prisma.$queryRawUnsafe(query);

  console.log('[API complement] テーブル総件数:', allRows.length);

  // 補集合を計算（現在のIDに含まれないもの）
  const complementIds = allRows
    .map(row => row[columnName])
    .filter(value => value !== null && value !== undefined && !currentIdSet.has(String(value)));

  // 重複を除去
  const uniqueComplementIds = [...new Set(complementIds)];

  console.log('[API complement] 補集合件数:', uniqueComplementIds.length);

  return NextResponse.json({
    success: true,
    complementIds: uniqueComplementIds,
    stats: {
      baseDataCount: allRows.length,
      currentCount: currentIds.length,
      complementCount: uniqueComplementIds.length
    }
  });
}

実行例

124万件のテーブルで補集合を計算した例:

[補集合] 現在のフィルタ結果: 1,150,000 件
[API complement] テーブル総件数: 1,240,703
[API complement] 補集合件数: 90,703
[補集合] 補集合のID数: 90,703

所要時間: 約3秒

124万件のデータから、Set を使った高速検索で補集合を計算している。

補集合の実用例

ケース1: 未提出者リスト

1. 全生徒: 1,000人
2. 提出済みで絞り込み → 850人
3. 補集合 → 未提出: 150人 ✅

ケース2: 未登録データの抽出

1. 全レコード: 500,000件
2. カテゴリ = 'A' OR 'B' OR 'C' で絞り込み → 480,000件
3. 補集合 → カテゴリ未設定: 20,000件 ✅

ケース3: 複数条件での絞り込み後の補集合

1. 全データ: 1,240,703件
2. 学年 = 1年 → 300,000件
3. 在籍 = '在籍中' → 280,000件
4. 補集合 → 1年生の退学者・休学者: 20,000件 ✅

このように、「〇〇以外」を抽出したいケースは実務で頻繁にある。桐の補集合機能は、この操作を直感的にできるようにした画期的な機能だった。

併合機能の現状

もう一つの桐の核心機能が**併合（マージ）**だ。

併合とは

他のテーブルから、特定のカラムのデータを転記する機能。

例:

【生徒マスタ】
ID | 氏名   | 学年
---+--------+-----
1  | 田中太郎 | 1
2  | 佐藤花子 | 2

【出席データ】（併合前）
ID | student_id | student_name | 出席日
---+------------+--------------+--------
1  | 1          | null         | 2026-01-10
2  | 2          | null         | 2026-01-11

【出席データ】（併合後）
ID | student_id | student_name | 出席日
---+------------+--------------+--------
1  | 1          | 田中太郎      | 2026-01-10  ← 生徒マスタから転記
2  | 2          | 佐藤花子      | 2026-01-11  ← 生徒マスタから転記

SQLでいう JOIN + UPDATE の組み合わせだが、桐では専用のUIで簡単に設定できる。

現在の状態: ⚠️ 一時無効

併合機能は、当初 IndexedDB（Dexie.js）を前提に実装していた:

// components/merge-dialog.tsx (旧実装)
import { useLiveQuery } from 'dexie-react-hooks';
import { db } from '@/lib/db'; // Dexie

const importedTables = useLiveQuery(() => db.importedTables.toArray(), []);

しかし、SQLiteへの移行により、以下の理由で一時的に無効化している:

  1. データ取得方法の変更
    • IndexedDB: useLiveQuery() でリアクティブに取得
    • SQLite: API経由で fetch() が必要
  2. 併合処理の実行方法
    • IndexedDB: ブラウザ内で JOIN してデータを更新
    • SQLite: サーバー側で SQL の JOIN + UPDATE を実行すべき
  3. トランザクション管理
    • IndexedDB: Dexie のトランザクション
    • SQLite: Prisma のトランザクション

再実装の方針

SQLite版の併合機能は、以下のように実装予定:

// app/api/tables/[tableId]/merge/route.ts (予定)
export async function POST(request: NextRequest) {
  const { sourceTableId, targetTableId, joinConditions, transferColumns, updateMode } = await request.json();

  await prisma.$transaction(async (tx) => {
    // 1. ソーステーブルとターゲットテーブルを JOIN
    // 2. 転記カラムのデータを取得
    // 3. ターゲットテーブルを UPDATE

    const query = `
      UPDATE "${targetTableId}"
      SET ${transferColumns.map(col => `"${col.target}" = source."${col.source}"`).join(', ')}
      FROM (
        SELECT * FROM "${sourceTableId}"
      ) AS source
      WHERE ${joinConditions.map(cond => `"${targetTableId}"."${cond.target}" = source."${cond.source}"`).join(' AND ')}
    `;

    await tx.$executeRawUnsafe(query);
  });
}

現在は基本機能の完成を優先しているため、併合機能の再実装は後回しになっている。

ビュー作成機能

併合と並んで実装を完了したのが、ビュー作成機能だ。

ビュー作成とは

複数のテーブルを JOIN して、仮想的な統合テーブルを作成する機能。SQLの知識がなくても、直感的な操作で複雑な結合クエリを作成できる。

例:

【生徒テーブル】+ 【出席テーブル】+ 【成績テーブル】
  ↓ JOIN
【生徒の出席・成績統合ビュー】

ビジュアルエディタによる直感的な操作

DataDrawersでは、React Flowを使用したビジュアルエディタを実装した:

┌──────────────┐
│  生徒        │──┐
│  • id        │  │ JOIN (id = student_id)
│  • 氏名      │  │
└──────────────┘  │
                  ├─→ 統合ビュー
┌──────────────┐  │
│  出席        │──┘
│  • student_id│  │ JOIN (student_id = student_id)
│  • 出席日    │  │
└──────────────┘  │
                  ├─→ さらに結合
┌──────────────┐  │
│  成績        │──┘
│  • student_id│
│  • 科目      │
│  • 点数      │
└──────────────┘

3つ以上のテーブルも対応: 実務では生徒・出席・成績・クラブ活動など、4〜5つのテーブルを結合することも珍しくない。DataDrawersは無制限にテーブルを追加できる。

ビュー作成の主な機能

  1. ドラッグ&ドロップでテーブル追加
    • サイドバーからテーブルをキャンバスにドラッグ
    • 自動的にカラム一覧が表示される
  2. 視覚的な結合設定
    • テーブル間を線で結んで JOIN 条件を設定
    • INNER / LEFT / RIGHT / FULL JOIN を選択可能
    • 複数の結合条件（AND条件）にも対応
  3. カラム選択
    • チェックボックスで表示カラムを選択
    • エイリアス（表示名）の設定
    • カラムの並び順を自由に変更
  4. WHERE条件ビルダー
    • 絞り込み条件を GUI で設定
    • AND / OR の複雑な条件にも対応
    • リアルタイムでSQL生成
  5. ORDER BY設定
    • 並び順を指定
    • 複数カラムでのソートに対応
  6. リアルタイムプレビュー
    • ビュー定義を変更すると即座に結果が表示される
    • 最初の100件をプレビュー表示

実装例

// ビュー定義の構造
interface ViewDefinition {
  id: string;
  name: string;
  tables: Array<{
    tableId: string;
    alias: string;  // t1, t2, t3...
  }>;
  joins: Array<{
    leftTable: string;
    rightTable: string;
    type: 'INNER' | 'LEFT' | 'RIGHT' | 'FULL';
    conditions: Array<{
      leftColumn: string;
      operator: '=' | '!=' | '>' | '<' | '>=' | '<=';
      rightColumn: string;
    }>;
  }>;
  columns: Array<{
    tableAlias: string;
    columnName: string;
    displayName?: string;
  }>;
  filters?: FilterStep[];
  orderBy?: Array<{
    tableAlias: string;
    column: string;
    direction: 'ASC' | 'DESC';
  }>;
}

ビュー実行エンジン

ビュー定義から動的にSQLを生成し、SQLiteで実行:

// lib/view-executor.ts
export async function executeView(viewId: string, limit: number = 100000): Promise<any[]> {
  // 1. ビュー定義を取得
  const viewDef = await getViewDefinition(viewId);

  // 2. SQLを動的生成
  const sql = generateViewSQL(viewDef, limit);

  // 3. SQLiteで実行
  const result = await prisma.$queryRawUnsafe(sql);

  return result;
}

生成されるSQLの例:

SELECT
  t1.id AS "t1_id",
  t1.氏名 AS "t1_氏名",
  t2.出席日 AS "t2_出席日",
  t3.点数 AS "t3_点数"
FROM table_1234 AS t1
INNER JOIN table_5678 AS t2 ON t1.id = t2.student_id
LEFT JOIN table_9012 AS t3 ON t1.id = t3.student_id
WHERE t1.学年 = 1 AND t2.出席日 >= '2026-01-01'
ORDER BY t1.id ASC, t2.出席日 DESC
LIMIT 100000;

ビューの保存と再利用

作成したビューは保存され、通常のテーブルと同じように操作できる:

  • データ表示
  • 絞り込み
  • ソート
  • エクスポート

ビューは仮想的なテーブルなので、元のテーブルが更新されると、ビューの結果も自動的に更新される。

補集合機能の価値

補集合は、一見地味な機能だが、実務では非常に重要だ。

従来のSQLでは:

-- 未面談者を抽出
SELECT * FROM 生徒
WHERE id NOT IN (
  SELECT student_id FROM 面談記録
);

このクエリを書くには:

  • SQLの知識が必要
  • サブクエリの理解が必要
  • パフォーマンスの考慮が必要

DataDrawersでは:

  1. 面談済みで絞り込み
  2. 補集合ボタンをクリック

これだけ。

SQLを知らない事務職員でも、直感的に操作できる。これが桐の設計思想であり、DataDrawersが目指すユーザー体験だ。

次回、DataDrawersの現状と今後の展望についてまとめる。

参考リンク

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?