従属するテーブルの連動削除

昨日触れた header - detail 構造はデータベースにおいても、当然使われる構造である。 帳票の基本情報としての header と、それに対する明細情報としての detail など、事務処理においてはお約束のような構造である。

今回は、header のレコードを削除した場合に、従属する detail のレコードを連動して削除するクエリを考えてみる。 データベース上でテーブルの整合性を取るための処理である。

まず、主体となるテーブル header は主キー key を持っているものとする。 従属するテーブル detail は主キー key, no を持っているものとする。 header と detail の key は紐づいたれコードが登録されているものとする。

header レコード削除後に、detail 側で紐づいていないレコードを削除する DELETE 文の書き方はいくつか存在するため、 何パターンか書いてみることにしよう。

1. サブクエリを使って主キーが存在しない内容を削除する

DELETE FROM detail
 WHERE key NOT IN (
    SELECT key FROM header
 );

2. 外部結合で紐づかない内容を削除する

DELETE FROM detail
 USING detail d
  LEFT OUTER JOIN header h ON d.key = h.key
 WHERE detail.key = d.key AND h.key IS NULL;

3. NOT EXISTS を使う

DELETE FROM detail
 WHERE NOT EXISTS (
    SELECT 1
      FROM header h
     WHERE detail.key = h.key
 )

実際にパフォーマンスなどのテストする場合は、トランザクションをかけてロールバックしながら何度も分析する形になると思うが、 クエリ上で行うならば、以下のように EXPLAIN ANALYZE を使えば、その結果でパフォーマンスを確認できる。

BEGIN;

EXPLAIN ANALYZE
DELETE FROM detail
 WHERE key NOT IN (
    SELECT key FROM header
 );

ROLLBACK;

数千件程度では結果としては 1 の方法がシンプルで概して速かった。2 と 3 は外で結合させている手順の分だけ時間がかかっており、 メモリに展開できる程度の小さなデータであれば 1, 3, 2 の順番で速かった。

しかし、手元にあった50万件ほどのレコードで試してみると、案の定というか、3, 2 の順で速く、1 に至っては数分待ってもレスポンスが返らない。 という結果になった。サブクエリの内容を全展開した上で、否定的な判定をする必要があることから、対象となるデータ量によっては膨大なメモリが 必要となるためだろう。そのため、想定されるレコード数によっては全く使い物にならない方法と言える。

クエリの書き方で、この処理は重そう、軽そう。というのが感覚的に解る十分な経験や実績があり、技量に自信があれば、直感で組み上げても それほど問題が起こることはないだろうが、少しでも怪しいのであれば、実際の環境を想定した条件で逐一確認すべきである。 そのためにも、データベースを扱う開発者は、テストに使える様々な規模のデータを準備しておくべきでもある。

header - detail 構造

よくあるデータ構造として、header - detail 構造が挙げられる。

概念

主体の基本情報 header と、主体に従属する詳細情報 detail の組み合わせで、 主体側:1 に対して 従属側:N の関係になる構造である。

実際には、これらを複合的に組み合わせた構造で捉える必要のあることも多いだろうが、 主体側をグループや人物として捉えても成立するし、それらに汎用的なパラメータとして詳細情報を紐づけるなど、 この単位で現実なり業務の要素をオブジェクトとして捉えるスキルがあれば、それだけで幅広く現実をデータ構造に落とし込めるようになる。 それほどまでにこの構造は世に溢れている。

具象

開発言語で捉える場合、主体側が集合要素を捉えられる何らかのコンテナ構造（Array, List, Queue, Stack, Set, Map など）を介して従属側を所有あるいは参照する形で扱うのが一般的と言えるだろう。 Java であれば、このコンテナ構造のことをコレクションと呼称しているようだ。

扱うデータ構造と必要な操作に合わせたコンテナを組み合わせることで、実装者はデータを捉えやすくなる上にパフォーマンスも上げられるため、できれば幅広く扱えたほうが良いが、 一般的な業務では List, Set, Map 辺りの汎用的な構造が最低限使えれば、それらの組み合わせ次第で（効率を気にしなければ）他は代用できるため、そこまで困ることはないが、 できる限り設計に対して明確なコンテナで捉えることが望ましい。汎用的な構造では、どういう目的でどのように管理しようとしているのか？など用途が曖昧になる場合もあるため、 説明や管理方法が簡潔になる最適なコンテナが存在するのであれば優先的に選択すべきである。

個々の値を意識しなければ扱えない構造だった場合、開発中はそこまでの問題はないだろうが、一旦その開発から離れて数ヶ月や数年経ってから保守や拡張などの作業をする際に、 意味を思い出すための時間が必要となるため、オブジェクト指向的な理想としては、実データの個々の値を意識して直接的に扱うのではなく、抽象化した概念で扱える状況の方が 中長期に渡っての保守性が安定する。構造化的に捉えるのであれば、対象データが構造を使って隔離できており、各項目に対する説明なりドキュメント整備ができていれば そこまで時間が取られることはないだろう。

PostgreSQL で日付型絞り込み検索

PostgreSQL において、テーブル t に含まれる登録日時を記録した日付型のカラム entryTime が存在しているとする。

そのテーブルに対して先々月、先月、当月に該当するレコードをそれぞれ取り出す。なんてのは業務処理のレポート出力でありがちなパターンと言える。

それぞれのクエリを書いてみよう。

先々月

SELECT *
  FROM t
 WHERE date_trunc('month', current_date) - interval '2 month' <= t.entryTime
   AND t.entryTime < date_trunc('month', current_date) - interval '1 month';

先月

SELECT *
  FROM t
 WHERE date_trunc('month', current_date) - interval '1 month' <= t.entryTime
   AND t.entryTime < date_trunc('month', current_date);

当月

SELECT *
  FROM t
 WHERE date_trunc('month', current_date) <= t.entryTime
   AND t.entryTime < date_trunc('month', current_date) + interval '1 month';

interval の指定は '-1 month' 等の書式も有効であるため、上記のクエリをシステムに組み込む際には パラメータ置き換えを使って全て同じ式でまとめて表現することも可能である。

この手の実装でよくあるミスとしては、月初から月末の 00:00:00 までの日付範囲で判定して月末の時刻を持つデータが拾えていないパターンである。 上記のクエリで示したように対象月の月初から翌月の月初未満の範囲で判定するのが漏れの発生しない手堅く確実な方法だと言える。

DOM 抽象化

XML、SVG、あと XHTML なんかは、それぞれ異なるデータ形式やマークアップ言語だが、 これらすべてのデータ構造は、DOM（Document Object Model）という共通の概念で扱うことができる。 DOM は、データの構造や内容に関係なく、一貫したインターフェイスを提供するプログラムと文書のための 標準モデルであり、これを使うことで開発者は異なるデータ形式に対して同じ方法でアクセスし、 操作することが可能になる。

次の XML に対して、JavaScript と Java で同じ DOM操作を行うコードを考えてみる。

<?xml version="1.0" encoding="UTF-8"?>
<fruits>
  <fruit id="1">
    <name>Apple</name>
    <color>Red</color>
  </fruit>
  <fruit id="2">
    <name>Banana</name>
    <color>Yellow</color>
  </fruit>
</fruits>

以下は JavaScript で xml を制御するコードである

// DOMParserを使用して、XML文字列をDOMオブジェクトに変換
const xmlString = `<上記のXMLデータ>`;
const parser = new DOMParser();
const xmlDoc = parser.parseFromString(xmlString, "text/xml");

// 最初のフルーツ要素を取得
const firstFruit = xmlDoc.getElementsByTagName("fruit")[0];

// フルーツの名前を取得
const firstName = firstFruit.getElementsByTagName("name")[0].textContent;
console.log("First fruit name: " + firstName);

// 新しいフルーツ要素を作成し、fruits要素に追加
const newFruit = xmlDoc.createElement("fruit");
newFruit.setAttribute("id", "3");
const newName = xmlDoc.createElement("name");
newName.textContent = "Orange";
const newColor = xmlDoc.createElement("color");
newColor.textContent = "Orange";
newFruit.appendChild(newName);
newFruit.appendChild(newColor);
xmlDoc.getElementsByTagName("fruits")[0].appendChild(newFruit);

以下は Java で xml を制御するコードである

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

public class Main {
  public static void main(String[] args) {
    try {
      // XML文字列をDOMオブジェクトに変換
      String xmlString = "<上記のXMLデータ>";
      DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
      DocumentBuilder builder = factory.newDocumentBuilder();
      Document xmlDoc = builder.parse(new ByteArrayInputStream(xmlString.getBytes("UTF-8")));

      // 最初のフルーツ要素を取得
      NodeList fruitNodes = xmlDoc.getElementsByTagName("fruit");
      Node firstFruit = fruitNodes.item(0);

      // フルーツの名前を取得
      NodeList firstNameList = ((Element) firstFruit).getElementsByTagName("name");
      String firstName = firstNameList.item(0).getTextContent();
      System.out.println("First fruit name: " + firstName);

      // 新しいフルーツ要素を作成し、fruits要素に追加
      Element newFruit = xmlDoc.createElement("fruit");
      newFruit.setAttribute("id", "3");
      Element newName = xmlDoc.createElement("name");
      newName.setTextContent("Orange");
      Element newColor = xmlDoc.createElement("color");
      newColor.setTextContent("Orange");
      newFruit.appendChild(newName);
      newFruit.appendChild(newColor);
      xmlDoc.getElementsByTagName("fruits").item(0).appendChild(newFruit);

    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

これらのコードは、XMLデータから最初のフルーツ要素を取得し、その名前を表示し、 新しいフルーツ要素を作成して追加する機能を実装している。 両方のコードからJavaScriptとJavaの両方でXMLデータを扱い、DOM操作を行う方法を確認できるだろう。

JavaScriptとJavaでの主な違いは、オブジェクト生成とメソッド呼び出しの方法であり、 JavaScriptでは、DOMParser と createElement を使用して新しいオブジェクトを作成し、 Javaでは DocumentBuilderFactory と DocumentBuilder を使用する。 また、要素の取得や操作に関しても、それぞれ言語に応じたメソッドが提供されている。

この例からも、DOM操作に関しては異なる言語であっても同様のアプローチを取れることが理解できるだろう。

もちろん、今の時代に DOM を意識して操作することが適切なのか？という根本的な話はあるが、 これを理解した上で、それぞれのデータ形式や利用する開発言語、実行環境に合わせた方法を選択すべきだろう。

Chrome を使ったフォルダ走査

2023-04 現在、Chromium系のブラウザは、ドロップされたフォルダに対する走査が実現可能であり、 再帰的にフォルダを走査することで、配下も含めたフォルダやファイルが制御可能なため、 フォルダ配下のファイルをまとめてアップロードすることもできる。

つまり、サーバ側のAPIを整えフォルダまで扱える機能がサーバ側に備わっていれば、 フォルダ階層も含めてファイルをアップロードできるのである。

ただし、これが実行できる状況には制限があり、サーバとの接続が localhost か https 環境のみ、 いわゆるセキュリティ的に問題のない経路でしか動作しない。

特にイントラ内での開発やテストにおいて、192.168.*.* 等に http で接続して動かない。 というパターンで動かない。という状況に陥る人が多いため注意すること。

で、今回はドロップされたフォルダを再帰的に走査する JavaScript を書いてみようと思う。

以下に HTML 一式を示す。ローカルのウェブサーバに置けば確認できるはずだ。

<!DOCTYPE html>
<html lang="ja">
<head>
  <meta charset="UTF-8">
  <title>ドロップされたフォルダの走査</title>
  <style>
    #drop-area {
      width: 100%;
      height: 200px;
      border: 2px dashed #ccc;
      text-align: center;
      line-height: 200px;
    }
  </style>
</head>
<body>
  <div id="drop-area">フォルダをここにドロップ</div>
  <ul id="file-list"></ul>
  <script>
    function handleFolder(folder) {
      const reader = folder.createReader();
      reader.readEntries(function (entries) {
        entries.forEach(function (entry) {
          if (entry.isDirectory) {
            handleFolder(entry);
          } else if (entry.isFile) {
            const listItem = document.createElement('li');
            listItem.textContent = entry.fullPath;
            document.getElementById('file-list').appendChild(listItem);
          }
        });
      });
    }

    document.getElementById('drop-area').addEventListener('dragover', function (event) {
      event.preventDefault();
    });

    document.getElementById('drop-area').addEventListener('drop', function (event) {
      event.preventDefault();
      const items = event.dataTransfer.items;
      for (let i = 0; i < items.length; i++) {
        const item = items[i];
        if (item.kind === 'file' && item.webkitGetAsEntry().isDirectory) {
          handleFolder(item.webkitGetAsEntry());
        }
      }
    });
  </script>
</body>
</html>

Chromium系ブラウザでは、webkitGetAsEntry() メソッドを使って、ファイルアイテムからディレクトリエントリを取得できるため、 フォルダである場合はフォルダ走査を行う handleFolder関数を呼び出している。

handleFolder関数は、渡されたフォルダを走査し、ディレクトリだった場合は再帰的に走査し、ファイルだった場合はファイルパスを ul要素に追加している。

実際のプロジェクトで使うには、複数ドロップされた場合やドロップ後のアップロード処理、エラー処理など様々な処理が必要となるため、 非常に複雑なものとなってしまうが、基本的なフォルダ走査を行う要素は上記のように再帰的な処理で捉えてしまえば単純なものとなる。

JavaScript XMLHttpRequest を使ったファイルアップロードの進捗状況表示

今回は XMLHttpRequestを使ってファイルをサーバにアップロードする際に、その進捗状況を把握したい場合のコードを書いてみる。

コードは当然 JavaScript である。以下に HTMLと合わせたコードを示す。

<!DOCTYPE html>
<html lang="ja">
<head>
  <meta charset="UTF-8">
  <title>ファイルアップロードの進捗状況</title>
  <style>
    #progress-bar {
      width: 100%;
      background-color: #f3f3f3;
      position: relative;
    }
    #progress-bar span {
      position: absolute;
      height: 100%;
      background-color: #4CAF50;
    }
  </style>
</head>
<body>
  <input type="file" id="file-input">
  <div id="progress-bar"><span></span></div>
  <script>
    document.getElementById('file-input').addEventListener('change', function (event) {
      const file = event.target.files[0];
      const xhr = new XMLHttpRequest();
      const formData = new FormData();

      xhr.open('POST', '/upload', true);

      xhr.upload.addEventListener('progress', function (event) {
        if (event.lengthComputable) {
          const percentage = (event.loaded / event.total) * 100;
          document.getElementById('progress-bar').querySelector('span').style.width = percentage + '%';
        }
      });

      xhr.onreadystatechange = function () {
        if (xhr.readyState === XMLHttpRequest.DONE) {
          if (xhr.status === 200) {
            alert('ファイルのアップロードに成功しました。');
          } else {
            alert('ファイルのアップロードに失敗しました。');
          }
        }
      };

      formData.append('file', file);
      xhr.send(formData);
    });
  </script>
</body>
</html>

input type="file" 要素と、進捗状況を表示する div 要素を配置し、CSS で調整しておく。

input 要素が change されたタイミングで XMLHttpRequest を使ってサーバにPOSTする。

リクエストの進捗状況を表示するには、xhr.upload オブジェクトの progress イベントを使う。

event.loadedとevent.totalプロパティを使って、アップロードの進捗率（進捗率）を計算し、その値を使って進捗バーの幅を更新している。

最後に、xhr.onreadystatechangeでリクエストの状態を監視し、リクエストが完了したら（readyState === XMLHttpRequest.DONE）、ステータスコードが200の場合はアップロード成功、それ以外の場合はアップロード失敗として単純にアラートで通知している。

実際のプロジェクトでは、エラー処理やファイル制限等が必要となるため、このようなサンプルと同じようにはいかないだろうが、進捗管理の要点は十分に理解できると思う。

年月に対する締め日範囲の算出

会計処理や月次レポートを行うシステムにおいて、対象年月（YYYYMM形式）と締め日（1～28,99:月末）から、対象年月に対する日付範囲を算出したい状況というのが稀に存在する。

今回はその局面で使えそうなコードを Java で書いてみようと思う。

public static void outputCutoffDateRange(int yyyymm, int cutoffDay) {
    int year = yyyymm / 100;
    int month = yyyymm % 100;

    YearMonth targetYearMonth = YearMonth.of(year, month);

    LocalDate startDate;
    LocalDate endDate;

    if (cutoffDay == 99) {
        endDate = targetYearMonth.atEndOfMonth();
        startDate = targetYearMonth.minusMonths(1).atEndOfMonth().plusDays(1);
    } else {
        startDate = targetYearMonth.atDay(cutoffDay).minusMonths(1).plusDays(1);
        endDate = targetYearMonth.atDay(cutoffDay);
    }

    LocalDate currentDate = startDate;
    while (!currentDate.isAfter(endDate)) {
        System.out.println(currentDate.getYear() * 10000 + currentDate.getMonthValue() * 100 + currentDate.getDayOfMonth());
        currentDate = currentDate.plusDays(1);
    }
}

単純に年月と締め日を使って開始日と終了日を算出し、開始から終了までの範囲を走査するだけである。

試しに実行してみる。それっぽい日付は出てるか？

public static void main(String[] args) {
    outputCutoffDateRange(202304, 15);
    System.out.println("---");
    outputCutoffDateRange(202303, 15);
    System.out.println("---");
    outputCutoffDateRange(202302, 99);
    System.out.println("---");
    outputCutoffDateRange(202302, 15);
}

これを基準に出力をDate型に変えたり、クラス化して Iterable で実装するなど使い方はいろいろあるかもしれない。