📝 ドリル 01 — `SERIAL` + `RETURNING` で 1 ステートメント採番

問題

notes テーブルに body を 3 件 INSERT し、INSERT 直後の id を取得して 1 件ずつ次のフォーマットで出力する。

inserted id: <id>

PostgreSQL では INSERT ... RETURNING id が使えるため INSERT と id 取得が 1 ステートメント で済む。MySQL / SQLite には RETURNING が無い (※) ので、INSERT → lastInsertId() の 2 段階になる。

※ MySQL 8 / SQLite 3.35 で RETURNING がサポートされたが、ポータビリティのため本ドリルでは lastInsertId() で書く想定。

期待される出力

inserted id: 1
inserted id: 2
inserted id: 3

採点

php scripts/grade.php topics/11-db/ch12-postgres-specific/drill/01-serial-returning/

採点ランナーはデフォルトで SQLite を使うので、AUTOINCREMENT カラムへの INSERT → lastInsertId() の経路で PASS する。

PostgreSQL モード (DOJO_DB_DRIVER=pgsql) で動かしたときは tests/setup.pgsql.sql が選ばれ、SERIAL + RETURNING の経路を通る。

ヒント

• 採用ドライバは getenv('DOJO_DB_DRIVER') ?: 'sqlite' で判定する
• PostgreSQL: RETURNING id の戻り値は $stmt->fetchColumn() で 1 つの値として取れる
• SQLite / MySQL: INSERT 後に $pdo->lastInsertId() で取得する
• ループで body を 3 回 INSERT すれば id は 1, 2, 3 と採番される
• scripts/shared/db-connect.php の dojo_db_connect() を使うと PDO を driver 非依存に取得できる

なぜ PostgreSQL の RETURNING が便利か

INSERT した行の (シーケンスで採番された) id や created_at などの DB 側で自動入力された値を 1 往復 で取得できる。MySQL の lastInsertId() は AUTO_INCREMENT カラム 1 つに限定だが、RETURNING は 任意のカラム を返せる:

INSERT INTO notes (body) VALUES ('hello')
RETURNING id, created_at, edit_token;

→ 「INSERT 後に SELECT で取り直す」が不要になる。

inserted id: 1
inserted id: 2
inserted id: 3

<?php

// TODO:
// 1) 採用ドライバを取得: $driver = getenv('DOJO_DB_DRIVER') ?: 'sqlite';
// 2) scripts/shared/db-connect.php の dojo_db_connect() で PDO を取得
//    require __DIR__ . '/../../../../../scripts/shared/db-connect.php';
// 3) body に 'first', 'second', 'third' を順に INSERT し、INSERT 直後の id を出力する
//      - PostgreSQL: INSERT ... RETURNING id  (1 ステートメントで id 取得)
//      - SQLite / MySQL: INSERT  →  $pdo->lastInsertId()
// 4) 出力フォーマット:
//      inserted id: 1
//      inserted id: 2
//      inserted id: 3

<?php

require __DIR__ . '/../../../../../scripts/shared/db-connect.php';

$driver = getenv('DOJO_DB_DRIVER') ?: 'sqlite';
$pdo = dojo_db_connect();

$bodies = ['first', 'second', 'third'];

foreach ($bodies as $body) {
    if ($driver === 'pgsql') {
        // PostgreSQL: RETURNING で INSERT と id 取得を 1 ステートメントに
        $stmt = $pdo->prepare("INSERT INTO notes (body) VALUES (?) RETURNING id");
        $stmt->execute([$body]);
        $id = (int) $stmt->fetchColumn();
    } else {
        // SQLite / MySQL: INSERT → lastInsertId
        $stmt = $pdo->prepare("INSERT INTO notes (body) VALUES (?)");
        $stmt->execute([$body]);
        $id = (int) $pdo->lastInsertId();
    }
    echo "inserted id: {$id}\n";
}