PDQ/lib/jobs.php

<?php
require_once __DIR__ . '/db.php';

// Apply a single-field change to a job. If the column is audited
// (status or ack) and the value changed, append a job_history row.
function apply_job_change(array $job, string $col, $new, string $actor): void {
    $pdo = db();
    $old = $job[$col];
    if ((string) $old === (string) $new) return;

    // Use BEGIN IMMEDIATE so the write lock is acquired up front (PDO's
    // beginTransaction uses BEGIN DEFERRED, which can short-circuit
    // SQLite's busy handler). Application-level retry on SQLITE_BUSY
    // is a backstop in case the PDO timeout isn't honoured.
    $attempts = 0;
    while (true) {
        $attempts++;
        try {
            $pdo->exec('BEGIN IMMEDIATE');
            break;
        } catch (PDOException $e) {
            if ($attempts >= 50 || !is_busy_error($e)) throw $e;
            usleep(100000); // 100 ms
        }
    }
    try {
        $stmt = $pdo->prepare("UPDATE jobs SET $col = ?, updated_at = datetime('now') WHERE id = ?");
        $stmt->execute([$new, $job['id']]);

        // Every field change is audited — the log page reads this table.
        $hist = $pdo->prepare(
            'INSERT INTO job_history(job_id, field, old_value, new_value, actor)
             VALUES (?, ?, ?, ?, ?)'
        );
        $hist->execute([$job['id'], $col, (string) $old, (string) $new, $actor]);

        $pdo->exec('COMMIT');
    } catch (Throwable $e) {
        $pdo->exec('ROLLBACK');
        throw $e;
    }
}

function is_busy_error(PDOException $e): bool {
    return stripos($e->getMessage(), 'database is locked') !== false
        || stripos($e->getMessage(), 'database table is locked') !== false;
}

// Parse the same M-D / M-D-Y forms the original PDQUpdates.php accepted.
// Returns ISO 'YYYY-MM-DD' or null (which clears the date).
function parse_due_date(string $raw): ?string {
    $raw = trim(str_replace('/', '-', $raw));
    if ($raw === '') return null;
    $parts = explode('-', $raw);
    $today = getdate();
    if (count($parts) === 3) {
        [$m, $d, $y] = $parts;
    } elseif (count($parts) === 2) {
        [$m, $d] = $parts;
        $y = $today['year'];
    } else {
        return null;
    }
    $m = (int) $m; $d = (int) $d; $y = (int) $y;
    if ($y < 100) $y += 2000;
    if (!checkdate($m, $d, $y)) return null;
    return sprintf('%04d-%02d-%02d', $y, $m, $d);
}