第192回 欠番を1件ずつ展開しない:PythonとSQLiteで作る欠番範囲レポート

第191回では、最初の未来イベントを受信した時刻から、欠番待ちの経過時間を測りました。今回はSQLiteの保留イベントを調べ、現在確認できる欠番を連番の範囲へまとめます。

適用済み連番が10で、連番1000000だけが保留されていても、11から999999までを1件ずつ生成する必要はありません。開始値と終了値を持つ一つの範囲として出力します。

作成するCLIは読み取り専用です。再送依頼、保留イベントの適用や削除、欠番の読み飛ばしは行いません。保留済みの最大連番より先は、まだ作られていない可能性があるため欠番とみなしません。

欠番と判定できる範囲

送信側が商品ごとに1から始まる連続した整数を発行し、受信側が連続適用できた最後の値を保存していることが前提です。意図的に連番を飛ばす設計には利用できません。

適用済み連番 保留連番 判定
10 12、14、15 11~11、13~13が欠番
10 1000000 11~999999が一つの欠番範囲
10 保留なし 欠番なし。11が未作成か未着かは判断できない
10 11 適用可能な行が残っているため不整合
10 10以下 適用済みの行が保留されているため不整合

計算には、次に必要な連番を示すカーソルを使います。適用済み連番が10なら初期値は11です。昇順に読んだ保留連番がカーソルより大きければ、カーソルから保留連番の直前までを欠番とします。その後、カーソルを保留連番の次へ進めます。

存在しない連番を順番に生成しないため、処理量は連番の飛び幅ではなく、読み取る保留行数と出力する範囲数で決まります。

今回の実行契約

項目 仕様
動作環境 Python 3.11、標準ライブラリのsqlite3が利用できる環境
連番 商品ごとに1から始まり、1ずつ増える整数。上限は掲載スキーマで2147483647
初期状態 inventory_stateに商品行がなければ、適用済み連番を0とする
データベース mode=roで開き、query_onlyも有効にする
読み取り 一つの読み取りトランザクション内で状態と保留行を確認する
上限 保留行100000件、欠番範囲1000件。超過時は途中結果を出さない
標準出力 正常時は結果を1行JSONで出力する
標準エラー出力 引数、データ不整合、SQLiteエラーを1行JSONで出力する
終了コード 意味
0 確認できる欠番範囲がない
3 一つ以上の欠番範囲を出力した
4 保留状態の不整合、または実装上限の超過
2 引数またはデータベースパスの誤り
1 データベースを開けない、必要な表がないなどのSQLiteエラー

終了コード3は異常終了ではありません。欠番が見つかったことを呼び出し元へ伝えるための値です。

検証用データベースを作る

次のsetup_demo.pyは、通常例のgap-demo.dbと、不整合例のgap-drain-demo.dbを作ります。既存ファイルがある場合は上書きせずに停止します。

表は再現用の最小構成です。既存システムで表名や列名が異なる場合は、後述する読み取りSQLを実際のスキーマへ合わせてください。

from pathlib import Path
import sqlite3

SCHEMA = (
    '''
    CREATE TABLE inventory_state (
        sku TEXT PRIMARY KEY
            CHECK (length(sku) BETWEEN 1 AND 100),
        quantity INTEGER NOT NULL
            CHECK (typeof(quantity) = 'integer' AND quantity >= 0),
        last_sequence INTEGER NOT NULL
            CHECK (typeof(last_sequence) = 'integer'
                   AND last_sequence BETWEEN 0 AND 2147483647)
    ) WITHOUT ROWID
    ''',
    '''
    CREATE TABLE reorder_buffer (
        sku TEXT NOT NULL
            CHECK (length(sku) BETWEEN 1 AND 100),
        sequence INTEGER NOT NULL
            CHECK (typeof(sequence) = 'integer'
                   AND sequence BETWEEN 1 AND 2147483647),
        event_id TEXT NOT NULL UNIQUE,
        quantity INTEGER NOT NULL
            CHECK (typeof(quantity) = 'integer' AND quantity >= 0),
        received_at TEXT NOT NULL
            CHECK (length(received_at) = 20),
        PRIMARY KEY (sku, sequence)
    ) WITHOUT ROWID
    ''',
)

def create_database(path, state_rows, buffer_rows):
    connection = sqlite3.connect(path, isolation_level=None)
    try:
        connection.execute('BEGIN IMMEDIATE')
        for statement in SCHEMA:
            connection.execute(statement)
        connection.executemany(
            'INSERT INTO inventory_state '
            '(sku, quantity, last_sequence) VALUES (?, ?, ?)',
            state_rows,
        )
        connection.executemany(
            'INSERT INTO reorder_buffer '
            '(sku, sequence, event_id, quantity, received_at) '
            'VALUES (?, ?, ?, ?, ?)',
            buffer_rows,
        )
        connection.execute('COMMIT')
    except BaseException:
        if connection.in_transaction:
            connection.execute('ROLLBACK')
        raise
    finally:
        connection.close()

valid_path = Path('gap-demo.db')
error_path = Path('gap-drain-demo.db')

for path in (valid_path, error_path):
    if path.exists():
        raise SystemExit(f'already exists: {path}')

create_database(
    valid_path,
    [
        ('SKU-A', 8, 10),
        ('SKU-B', 4, 20),
    ],
    [
        ('SKU-A', 12, 'event-a12', 9, '2026-11-19T00:00:00Z'),
        ('SKU-A', 14, 'event-a14', 7, '2026-11-19T00:01:00Z'),
        ('SKU-A', 15, 'event-a15', 6, '2026-11-19T00:02:00Z'),
        ('SKU-B', 23, 'event-b23', 5, '2026-11-19T00:03:00Z'),
    ],
)

create_database(
    error_path,
    [('SKU-C', 3, 30)],
    [('SKU-C', 31, 'event-c31', 4, '2026-11-19T00:04:00Z')],
)

print('created gap-demo.db and gap-drain-demo.db')

各データベースでは、表の作成とテスト行の追加を一つのトランザクションで確定します。received_atは前回までのスキーマとつなぐための列で、今回の範囲計算には使いません。

欠番範囲を作る読み取り専用CLI

次をgap_ranges.pyとして保存します。結果を組み立て終わるまで標準出力へ書かないため、上限超過や不整合が見つかった場合に途中のレポートは残りません。

#!/usr/bin/env python3
import argparse
import json
import sqlite3
import sys
from pathlib import Path

MAX_BUFFER_ROWS = 100_000
MAX_GAP_RANGES = 1_000
MAX_SEQUENCE = 2_147_483_647

class DataError(Exception):
    pass

def write_json(payload, stream):
    print(
        json.dumps(
            payload,
            ensure_ascii=False,
            separators=(',', ':'),
        ),
        file=stream,
    )

class JsonArgumentParser(argparse.ArgumentParser):
    def error(self, message):
        write_json(
            {
                'status': 'error',
                'error': 'argument_error',
                'detail': message,
            },
            sys.stderr,
        )
        raise SystemExit(2)

def validate_sku(value):
    if type(value) is not str or not 1 <= len(value) <= 100:
        raise DataError('sku must be text with 1 to 100 characters')
    return value

def validate_integer(value, field, low, high):
    if type(value) is not int or value < low or value > high:
        raise DataError(
            f'{field} must be an integer in [{low}, {high}]'
        )
    return value

def add_range(ranges, sku, start, end, next_sequence):
    if len(ranges) >= MAX_GAP_RANGES:
        raise DataError(
            f'gap range count exceeds {MAX_GAP_RANGES}'
        )
    ranges.append(
        {
            'sku': sku,
            'missing_from': start,
            'missing_to': end,
            'next_buffered_sequence': next_sequence,
        }
    )

def build_report(rows):
    ranges = []
    current_sku = None
    current_last = None
    cursor_sequence = None
    previous_sequence = None
    sku_count = 0

    for row in rows:
        sku = validate_sku(row['sku'])
        sequence = validate_integer(
            row['sequence'],
            f'{sku}.sequence',
            1,
            MAX_SEQUENCE,
        )
        last_applied = validate_integer(
            row['last_sequence'],
            f'{sku}.last_sequence',
            0,
            MAX_SEQUENCE,
        )

        if sku != current_sku:
            current_sku = sku
            current_last = last_applied
            cursor_sequence = current_last + 1
            previous_sequence = None
            sku_count += 1
        elif last_applied != current_last:
            raise DataError(
                f'{sku}: last_sequence changed inside one snapshot'
            )

        if (
            previous_sequence is not None
            and sequence <= previous_sequence
        ):
            raise DataError(
                f'{sku}: buffered sequences are not strictly increasing'
            )

        if sequence <= current_last:
            raise DataError(
                f'{sku}: buffered sequence {sequence} is already applied'
            )

        if previous_sequence is None and sequence == cursor_sequence:
            raise DataError(
                f'{sku}: sequence {sequence} is ready to apply; '
                'drain the buffer first'
            )

        if sequence > cursor_sequence:
            add_range(
                ranges,
                sku,
                cursor_sequence,
                sequence - 1,
                sequence,
            )

        cursor_sequence = sequence + 1
        previous_sequence = sequence

    return {
        'status': 'gaps_found' if ranges else 'no_gaps',
        'scanned_buffer_rows': len(rows),
        'scanned_skus': sku_count,
        'range_count': len(ranges),
        'ranges': ranges,
    }

def rollback_quietly(connection):
    if connection is None or not connection.in_transaction:
        return
    try:
        connection.execute('ROLLBACK')
    except sqlite3.Error:
        pass

def main():
    parser = JsonArgumentParser(
        description='Report sequence gaps as inclusive ranges.'
    )
    parser.add_argument('--db', required=True)
    args = parser.parse_args()

    try:
        database_path = Path(args.db).resolve(strict=True)
    except (OSError, RuntimeError) as exc:
        write_json(
            {
                'status': 'error',
                'error': 'input_error',
                'detail': str(exc),
            },
            sys.stderr,
        )
        return 2

    if not database_path.is_file():
        write_json(
            {
                'status': 'error',
                'error': 'input_error',
                'detail': 'database path is not a regular file',
            },
            sys.stderr,
        )
        return 2

    connection = None
    try:
        connection = sqlite3.connect(
            database_path.as_uri() + '?mode=ro',
            uri=True,
            timeout=5.0,
            isolation_level=None,
        )
        connection.row_factory = sqlite3.Row
        connection.execute('PRAGMA query_only = ON')
        connection.execute('PRAGMA busy_timeout = 5000')
        connection.execute('BEGIN')

        database_cursor = connection.execute(
            '''
            SELECT
                b.sku AS sku,
                b.sequence AS sequence,
                CASE
                    WHEN s.sku IS NULL THEN 0
                    ELSE s.last_sequence
                END AS last_sequence
            FROM reorder_buffer AS b
            LEFT JOIN inventory_state AS s
                ON s.sku = b.sku
            ORDER BY b.sku, b.sequence
            '''
        )
        rows = database_cursor.fetchmany(MAX_BUFFER_ROWS + 1)
        database_cursor.close()

        if len(rows) > MAX_BUFFER_ROWS:
            raise DataError(
                f'buffer row count exceeds {MAX_BUFFER_ROWS}'
            )

        report = build_report(rows)
        connection.execute('COMMIT')
    except DataError as exc:
        rollback_quietly(connection)
        write_json(
            {
                'status': 'error',
                'error': 'data_error',
                'detail': str(exc),
            },
            sys.stderr,
        )
        return 4
    except sqlite3.Error as exc:
        rollback_quietly(connection)
        write_json(
            {
                'status': 'error',
                'error': 'database_error',
                'detail': str(exc),
            },
            sys.stderr,
        )
        return 1
    finally:
        if connection is not None:
            connection.close()

    write_json(report, sys.stdout)
    return 3 if report['range_count'] else 0

if __name__ == '__main__':
    raise SystemExit(main())

scanned_skusが数えるのは、保留表に行があった商品だけです。保留行のない商品について、次の連番が未着か未作成かをこのCLIだけで判断することはできません。

通常系を実行する

通常のPOSIXシェルでは、次の順番で実行します。終了コードはCLIの直後に保存します。

python3 setup_demo.py
python3 gap_ranges.py --db gap-demo.db
code=$?
echo exit=$code

出力は次のとおりです。

created gap-demo.db and gap-drain-demo.db
{"status":"gaps_found","scanned_buffer_rows":4,"scanned_skus":2,"range_count":3,"ranges":[{"sku":"SKU-A","missing_from":11,"missing_to":11,"next_buffered_sequence":12},{"sku":"SKU-A","missing_from":13,"missing_to":13,"next_buffered_sequence":14},{"sku":"SKU-B","missing_from":21,"missing_to":22,"next_buffered_sequence":23}]}
exit=3

SKU-Aでは11と13が欠けています。連番15は14に続いているため、新しい範囲を作りません。SKU-Bでは21~22を一つの範囲にまとめます。

next_buffered_sequenceは、欠番範囲より後のイベントが保留されていることを示す値です。再送元の存在や再送APIの仕様を示すものではありません。

適用可能な行が残る異常系

gap-drain-demo.dbには、適用済み連番30の直後に当たる31が保留されています。第190回の並べ替えバッファが正常に処理を終えていれば、この行は適用され、保留表から削除されるはずです。

python3 gap_ranges.py --db gap-drain-demo.db
code=$?
echo exit=$code

この場合は標準出力へレポートを出さず、標準エラー出力へ次のJSONを出します。

{"status":"error","error":"data_error","detail":"SKU-C: sequence 31 is ready to apply; drain the buffer first"}
exit=4

連番31はすでに受信済みです。再送対象にはせず、受信処理の停止やトランザクション失敗を先に調べます。

運用へ組み込む際の注意

  • 出力するのは、実行時のSQLiteスナップショットに存在しない連番です。送信元で失われたことまでは証明しません。
  • 第191回の経過時間判定とは役割が異なります。待ち時間が期限を超えた商品を確認した後、その時点の欠番範囲を調べる使い方ができます。
  • CLI終了後に欠番が到着すれば、出力は古くなります。外部へ依頼する直前に再確認し、依頼側でも重複を許容できる契約が必要です。
  • 最大保留連番より後は出力しません。まだ作られていないイベントを欠番と誤認しないためです。
  • 再送依頼の永続化、配送、再試行、完了記録は実装していません。範囲指定を受け付けない送信元へ個別の連番を送る場合は、相手側の件数上限を確認して別に設計します。
  • 状態データベースが過去の内容へ戻ると、適用済みイベントを欠番と誤認する可能性があります。バックアップ復元後の整合性確認は別途必要です。

欠番を閉区間へまとめれば、大きな飛び番も巨大なリストへ展開せずに扱えます。検証用データベースでは、単独欠番、連続欠番、適用可能な保留行を区別して確認できます。