Wallos: Open-source, self-hostable personal subscription tracker. Visualize your recurring expenses, manage your budget, and save money.
<?php
/*
The subscription queries Wallos runs on every page must use an index rather
than scanning the table.
*/
require_once WALLOS_ROOT . '/includes/upcoming_payments.php';
/**
* Returns the query plan of a statement as one string.
*
* @param SQLite3 $db
* @param string $sql
* @return string
*/
function index_plan($db, $sql)
{
$plan = '';
$result = $db->query('EXPLAIN QUERY PLAN ' . $sql);
while ($result && $row = $result->fetchArray(SQLITE3_ASSOC)) {
$plan .= $row['detail'] . ' ';
}
return trim($plan);
}
wallos_test('the migration creates the subscription indexes', function () {
$db = wallos_test_open_database();
$indexes = [];
$result = $db->query("SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='subscriptions'");
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
$indexes[] = $row['name'];
}
assert_true(in_array('idx_subscriptions_user_inactive_next_payment', $indexes, true),
'the list/calendar index exists');
assert_true(in_array('idx_subscriptions_user_notify_inactive', $indexes, true),
'the notification index exists');
$db->close();
});
wallos_test('the queries Wallos runs use an index instead of scanning', function () {
$db = wallos_test_open_database();
wallos_test_create_user($db, 1, 'alice');
$cases = [
'subscription list' => "SELECT * FROM subscriptions WHERE user_id = 1",
'active subscriptions' => "SELECT * FROM subscriptions WHERE user_id = 1 AND inactive = 0",
'notification cron' => "SELECT * FROM subscriptions WHERE user_id = 1 AND notify = 1 AND inactive = 0",
'calendar range' => "SELECT * FROM subscriptions WHERE user_id = 1 AND inactive = 0 AND next_payment BETWEEN '2026-08-01' AND '2026-08-31'",
'dashboard cancellations' => "SELECT * FROM subscriptions WHERE user_id = 1 AND inactive = 0 AND cancellation_date IS NOT NULL AND cancellation_date != '' AND cancellation_date >= date('now') AND cycle != 5 ORDER BY cancellation_date ASC",
];
foreach ($cases as $label => $sql) {
$plan = index_plan($db, $sql);
assert_contains('USING INDEX', $plan, $label . ' uses an index (plan: ' . $plan . ')');
assert_not_contains('SCAN subscriptions', $plan, $label . ' does not scan the table');
}
$db->close();
});
wallos_test('the migration can run twice', function () {
$db = wallos_test_open_database();
require WALLOS_ROOT . '/migrations/000055.php';
assert_true((bool) $db->querySingle("SELECT COUNT(*) FROM sqlite_master WHERE type='index' AND name='idx_subscriptions_user_inactive_next_payment'"),
'the index still exists after a second run');
$db->close();
});
wallos_test('the dashboard limit migration defaults existing users to three', function () {
$db = wallos_test_open_database();
$row = $db->query('SELECT upcoming_payments_limit FROM settings WHERE user_id = 1')
->fetchArray(SQLITE3_ASSOC);
assert_same(3, (int) $row['upcoming_payments_limit'], 'existing users keep the default limit');
$db->exec('UPDATE settings SET upcoming_payments_limit = 4 WHERE user_id = 1');
require WALLOS_ROOT . '/migrations/000057.php';
$row = $db->query('SELECT upcoming_payments_limit FROM settings WHERE user_id = 1')
->fetchArray(SQLITE3_ASSOC);
assert_same(3, (int) $row['upcoming_payments_limit'], 'invalid stored values are reset to the default');
require WALLOS_ROOT . '/migrations/000057.php';
assert_same(3, (int) $db->querySingle('SELECT upcoming_payments_limit FROM settings WHERE user_id = 1'),
'the dashboard limit migration is safe to run twice');
$db->close();
});
wallos_test('dashboard limit parsing accepts only supported values', function () {
assert_same(20, parse_upcoming_payments_limit('20'), 'a supported string value is accepted');
assert_same(5, parse_upcoming_payments_limit(5), 'a supported numeric value is accepted');
assert_same(null, parse_upcoming_payments_limit(0), 'zero is no longer a supported value');
assert_same(null, parse_upcoming_payments_limit(4), 'unsupported numeric values are rejected');
assert_same(null, parse_upcoming_payments_limit(true), 'boolean values are rejected');
});
wallos_test('the dashboard keeps the legacy default of three upcoming payments', function () {
$db = wallos_test_open_database();
wallos_test_create_user($db, 1, 'alice');
$stmt = $db->prepare('INSERT INTO subscriptions (user_id, name, price, currency_id, next_payment, cycle, inactive)
VALUES (1, :name, 9.99, :currencyId, :nextPayment, 3, 0)');
for ($i = 1; $i <= 5; $i++) {
$stmt->bindValue(':name', 'payment-' . $i, SQLITE3_TEXT);
$stmt->bindValue(':currencyId', wallos_test_currency_id(1, 0), SQLITE3_INTEGER);
$stmt->bindValue(':nextPayment', date('Y-m-d', strtotime('+' . $i . ' days')), SQLITE3_TEXT);
$stmt->execute();
}
assert_same(3, count(get_upcoming_payments($db, 1, 3)), 'the default limit remains three');
$db->close();
});
wallos_test('the dashboard supports the configured limits', function () {
$db = wallos_test_open_database();
wallos_test_create_user($db, 1, 'alice');
$stmt = $db->prepare('INSERT INTO subscriptions (user_id, name, price, currency_id, next_payment, cycle, inactive)
VALUES (1, :name, 9.99, :currencyId, :nextPayment, 3, 0)');
for ($i = 1; $i <= 25; $i++) {
$stmt->bindValue(':name', 'payment-' . $i, SQLITE3_TEXT);
$stmt->bindValue(':currencyId', wallos_test_currency_id(1, 0), SQLITE3_INTEGER);
$stmt->bindValue(':nextPayment', date('Y-m-d', strtotime('+' . $i . ' days')), SQLITE3_TEXT);
$stmt->execute();
}
assert_same(5, count(get_upcoming_payments($db, 1, 5)), 'the five-payment option is honored');
assert_same(10, count(get_upcoming_payments($db, 1, 10)), 'the ten-payment option is honored');
assert_same(20, count(get_upcoming_payments($db, 1, 20)), 'the twenty-payment option is honored');
assert_same(3, count(get_upcoming_payments($db, 1, 7)), 'unsupported values fall back to three');
$db->close();
});