Skip to content

Wallos: Open-source, self-hostable personal subscription tracker. Visualize your recurring expenses, manage your budget, and save money.

4.1 KB
Raw Blame
<?php
/*
  A rate refresh writes every rate of one user or none of them.

  Rates are only comparable when they share a conversion base. A refresh that
  stops halfway leaves some rows against the new base and some against the old
  one, which is worse than not refreshing at all: the numbers look plausible
  and are wrong.
*/

/**
 * @param SQLite3 $db
 * @param int     $userId
 * @param string  $code
 * @return float
 */
function refresh_rate($db, $userId, $code)
{
    $stmt = $db->prepare('SELECT rate FROM currencies WHERE user_id = :userId AND code = :code');
    $stmt->bindValue(':userId', $userId, SQLITE3_INTEGER);
    $stmt->bindValue(':code', $code, SQLITE3_TEXT);
    $row = $stmt->execute()->fetchArray(SQLITE3_ASSOC);

    return $row ? (float) $row['rate'] : 0.0;
}

wallos_test('an interrupted refresh leaves the previous rates', function () {
    $db = wallos_test_open_database();
    wallos_test_create_user($db, 1, 'alice');

    $before = [refresh_rate($db, 1, 'EUR'), refresh_rate($db, 1, 'USD')];

    $db->exec('BEGIN');

    $stmt = $db->prepare('UPDATE currencies SET rate = :rate WHERE code = :code AND user_id = :userId');
    $stmt->bindValue(':rate', 7.0, SQLITE3_TEXT);
    $stmt->bindValue(':code', 'EUR', SQLITE3_TEXT);
    $stmt->bindValue(':userId', 1, SQLITE3_INTEGER);
    $stmt->execute();
    $stmt->reset();

    // The provider response breaks off here: the second currency never lands.
    $db->exec('ROLLBACK');

    assert_same($before[0], refresh_rate($db, 1, 'EUR'), 'the first rate is restored');
    assert_same($before[1], refresh_rate($db, 1, 'USD'), 'the second rate is unchanged');

    $db->close();
});

wallos_test('a completed refresh commits every rate together', function () {
    $db = wallos_test_open_database();
    wallos_test_create_user($db, 1, 'alice');

    $db->exec('BEGIN');

    $stmt = $db->prepare('UPDATE currencies SET rate = :rate WHERE code = :code AND user_id = :userId');
    foreach (['EUR' => 1.0, 'USD' => 3.0] as $code => $rate) {
        $stmt->bindValue(':rate', $rate, SQLITE3_TEXT);
        $stmt->bindValue(':code', $code, SQLITE3_TEXT);
        $stmt->bindValue(':userId', 1, SQLITE3_INTEGER);
        $stmt->execute();
        $stmt->reset();
    }

    $db->exec('COMMIT');

    assert_same(1.0, refresh_rate($db, 1, 'EUR'), 'the first rate is stored');
    assert_same(3.0, refresh_rate($db, 1, 'USD'), 'the second rate is stored');

    $db->close();
});

wallos_test('one prepared statement serves the whole rate loop', function () {
    $db = wallos_test_open_counting_database();
    wallos_test_create_user($db, 1, 'alice');

    $db->resetQueryCount();

    $stmt = $db->prepare('UPDATE currencies SET rate = :rate WHERE code = :code AND user_id = :userId');
    foreach (['EUR', 'USD', 'EUR', 'USD', 'EUR'] as $index => $code) {
        $stmt->bindValue(':rate', 1.0 + $index, SQLITE3_TEXT);
        $stmt->bindValue(':code', $code, SQLITE3_TEXT);
        $stmt->bindValue(':userId', 1, SQLITE3_INTEGER);
        $stmt->execute();
        $stmt->reset();
    }

    assert_same(1, $db->queryCount, 'five writes need one prepare (got ' . $db->queryCount . ')');

    $db->close();
});

wallos_test('both refresh paths wrap their writes in a transaction', function () {
    // Structural guard: a future refresh path that autocommits per currency
    // fails here rather than shipping a half-converted rate set.
    foreach (['endpoints/cronjobs/updateexchange.php', 'endpoints/currency/update_exchange.php'] as $path) {
        $source = file_get_contents(WALLOS_ROOT . '/' . $path);

        assert_contains("BEGIN", $source, $path . ' opens a transaction');
        assert_contains("COMMIT", $source, $path . ' commits it');
        assert_contains("ROLLBACK", $source, $path . ' rolls back on failure');

        // The prepare must sit outside the loop over the provider rates.
        $preparePosition = strpos($source, 'UPDATE currencies SET rate');
        $loopPosition = strpos($source, "foreach (\$apiData['rates']");
        assert_true($preparePosition !== false && $loopPosition !== false && $preparePosition < $loopPosition,
            $path . ' prepares the rate update once, before the loop');
    }
});