294 lines
11 KiB
JavaScript
294 lines
11 KiB
JavaScript
const test = require('node:test');
|
|
const assert = require('node:assert/strict');
|
|
|
|
require('../src/common');
|
|
|
|
const { getPendingMigrations, runMigrations } = require('../src/db/migrations');
|
|
|
|
function createPool(responses) {
|
|
const queries = [];
|
|
return {
|
|
queries,
|
|
async beginTransaction() { queries.push(['START TRANSACTION']); },
|
|
async commit() { queries.push(['COMMIT']); },
|
|
async rollback() { queries.push(['ROLLBACK']); },
|
|
async query(sql, params) {
|
|
queries.push([sql, params]);
|
|
const text = String(sql);
|
|
for (const response of responses) {
|
|
if (response.match(text, params)) {
|
|
return response.result;
|
|
}
|
|
}
|
|
return [[]];
|
|
}
|
|
};
|
|
}
|
|
|
|
test('timetable rename migration does not depend on legacy timezone column', async () => {
|
|
const pool = createPool([
|
|
{
|
|
match(sql) {
|
|
return sql.includes('FROM information_schema.COLUMNS') && sql.includes('TABLE_NAME = ?') && sql.includes('COLUMN_NAME = ?');
|
|
},
|
|
result: [[{ column_count: 0 }]]
|
|
},
|
|
{
|
|
match(sql) {
|
|
return sql.includes('FROM information_schema.TABLES') && sql.includes('TABLE_NAME = ?');
|
|
},
|
|
result: [[{ table_count: 1 }]]
|
|
},
|
|
{
|
|
match(sql) {
|
|
return sql.includes('SELECT id, name, short_description,') && sql.includes("'Europe/London' AS timezone") && sql.includes('FROM i_schedule_groups');
|
|
},
|
|
result: [{}]
|
|
},
|
|
{
|
|
match(sql) {
|
|
return sql.includes('SELECT id, schedule_group_id, title, short_description, start_datetime, end_datetime, created_at, created_by, modified_at, modified_by FROM i_schedule_entries');
|
|
},
|
|
result: [{}]
|
|
},
|
|
{
|
|
match(sql) {
|
|
return sql.includes('SELECT COALESCE(MAX(id), 0) AS max_id FROM i_timetable_groups');
|
|
},
|
|
result: [[{ max_id: 1 }]]
|
|
},
|
|
{
|
|
match(sql) {
|
|
return sql.includes('SELECT COALESCE(MAX(id), 0) AS max_id FROM i_timetable_entries');
|
|
},
|
|
result: [[{ max_id: 2 }]]
|
|
}
|
|
]);
|
|
|
|
await runMigrations(pool, { currentVersion: '2.6.17' });
|
|
|
|
assert.ok(pool.queries.some(function ([sql]) {
|
|
return String(sql).includes("SELECT id, name, short_description, 'Europe/London' AS timezone") && String(sql).includes('FROM i_schedule_groups');
|
|
}));
|
|
|
|
assert.ok(!pool.queries.some(function ([sql]) {
|
|
return String(sql).includes('SELECT id, name, short_description, timezone, created_at, created_by, modified_at, modified_by') && String(sql).includes('FROM i_schedule_groups');
|
|
}));
|
|
});
|
|
|
|
test('timetable timezone migrations use timetable tables when legacy tables are gone', async () => {
|
|
const pool = createPool([
|
|
{
|
|
match(sql) {
|
|
return sql.includes('FROM information_schema.COLUMNS') && sql.includes('TABLE_NAME = ?') && sql.includes('COLUMN_NAME = ?');
|
|
},
|
|
result: [[{ column_count: 0 }]]
|
|
},
|
|
{
|
|
match(_sql, params) {
|
|
return Array.isArray(params) && params[0] === 'i_timetable_groups';
|
|
},
|
|
result: [[{ table_count: 1 }]]
|
|
},
|
|
{
|
|
match(_sql, params) {
|
|
return Array.isArray(params) && params[0] === 'i_timetable_entries';
|
|
},
|
|
result: [[{ table_count: 1 }]]
|
|
},
|
|
{
|
|
match(_sql, params) {
|
|
return Array.isArray(params) && params[0] === 'i_schedule_groups';
|
|
},
|
|
result: [[{ table_count: 0 }]]
|
|
},
|
|
{
|
|
match(_sql, params) {
|
|
return Array.isArray(params) && params[0] === 'i_schedule_entries';
|
|
},
|
|
result: [[{ table_count: 0 }]]
|
|
},
|
|
{
|
|
match(sql) {
|
|
return sql.includes('SELECT id, start_datetime, end_datetime FROM i_timetable_entries ORDER BY id ASC');
|
|
},
|
|
result: [[{ id: 1, start_datetime: '2026-08-09 09:00:00', end_datetime: '2026-08-09 10:00:00' }]]
|
|
},
|
|
{
|
|
match(sql) {
|
|
return sql.includes('SELECT COALESCE(MAX(id), 0) AS max_id FROM i_timetable_groups');
|
|
},
|
|
result: [[{ max_id: 1 }]]
|
|
},
|
|
{
|
|
match(sql) {
|
|
return sql.includes('SELECT COALESCE(MAX(id), 0) AS max_id FROM i_timetable_entries');
|
|
},
|
|
result: [[{ max_id: 1 }]]
|
|
}
|
|
]);
|
|
|
|
await runMigrations(pool, { currentVersion: '2.6.15' });
|
|
|
|
assert.ok(pool.queries.some(function ([sql]) {
|
|
return String(sql).includes('ALTER TABLE i_timetable_groups ADD COLUMN timezone');
|
|
}));
|
|
|
|
assert.ok(pool.queries.some(function ([sql]) {
|
|
return String(sql).includes('UPDATE i_timetable_groups SET timezone = ?');
|
|
}));
|
|
|
|
assert.ok(pool.queries.some(function ([sql]) {
|
|
return String(sql).includes('UPDATE i_timetable_entries SET start_datetime = ?, end_datetime = ? WHERE id = ?');
|
|
}));
|
|
|
|
assert.ok(!pool.queries.some(function ([sql]) {
|
|
return String(sql).includes('i_schedule_groups') || String(sql).includes('i_schedule_entries');
|
|
}));
|
|
});
|
|
|
|
test('2.17.0 migration adds the expiring view-as session columns', async () => {
|
|
const pool = createPool([
|
|
{
|
|
match(sql) {
|
|
return sql.includes('FROM information_schema.COLUMNS');
|
|
},
|
|
result: [[{ column_count: 0 }]]
|
|
},
|
|
{
|
|
match(sql) {
|
|
return sql.includes('FROM information_schema.TABLES');
|
|
},
|
|
result: [[{ table_count: 0 }]]
|
|
}
|
|
]);
|
|
|
|
const pending = await getPendingMigrations(pool, { currentVersion: '2.16.2' });
|
|
const viewAsMigration = pending.find(function (migration) { return migration.version === '2.17.0'; });
|
|
assert.ok(viewAsMigration);
|
|
await viewAsMigration.run(pool);
|
|
|
|
assert.ok(pool.queries.some(function ([sql]) {
|
|
return String(sql).includes('ALTER TABLE a_sessions ADD COLUMN view_as_user_id INT NULL AFTER user_id');
|
|
}));
|
|
assert.ok(pool.queries.some(function ([sql]) {
|
|
return String(sql).includes('ALTER TABLE a_sessions ADD COLUMN view_as_expires_at DATETIME NULL AFTER view_as_user_id');
|
|
}));
|
|
});
|
|
|
|
test('2.17.1 migration removes obsolete dashboard.allow grants and permission', async () => {
|
|
const pool = createPool([
|
|
{
|
|
match(sql) {
|
|
return sql.includes('FROM information_schema.COLUMNS');
|
|
},
|
|
result: [[{ column_count: 1 }]]
|
|
},
|
|
{
|
|
match(sql) {
|
|
return sql.includes('FROM information_schema.TABLES');
|
|
},
|
|
result: [[{ table_count: 0 }]]
|
|
}
|
|
]);
|
|
|
|
const pending = await getPendingMigrations(pool, { currentVersion: '2.17.0' });
|
|
const obsoletePermissionMigration = pending.find(function (migration) { return migration.version === '2.17.1'; });
|
|
assert.ok(obsoletePermissionMigration);
|
|
await obsoletePermissionMigration.run(pool);
|
|
|
|
assert.ok(pool.queries.some(function ([sql, params]) {
|
|
return String(sql).includes('DELETE role_permissions') && params[0] === 'dashboard.allow';
|
|
}));
|
|
assert.ok(pool.queries.some(function ([sql, params]) {
|
|
return String(sql).includes('DELETE FROM a_permissions') && params[0] === 'dashboard.allow';
|
|
}));
|
|
});
|
|
|
|
test('2.19.1 migration rebuilds only managed font references for all content in a transaction', async () => {
|
|
const pool = createPool([
|
|
{ match: sql => sql.includes('information_schema.COLUMNS'), result: [[{ column_count: 1 }]] },
|
|
{ match: sql => sql.includes('information_schema.TABLES'), result: [[{ table_count: 0 }]] },
|
|
{
|
|
match: (sql, params) => sql.startsWith('SELECT id, content_json') && params[0] === 0,
|
|
result: [[{ id: 117, content_json: JSON.stringify({ second_place: {
|
|
font_family: 'Arial', value: '<span style="font-family: 'Ubuntu Regular', Helvetica, sans-serif;">Second</span>',
|
|
source_id: 4, image: '/media/uploads/keep.png'
|
|
} }) }]]
|
|
},
|
|
{
|
|
match: (sql, params) => sql.startsWith('SELECT id, content_json') && params[0] === 117,
|
|
result: [[{ id: 118, content_json: { hero: { font_family: 'Acme Display' } } }]]
|
|
},
|
|
{
|
|
match: (sql, params) => sql.startsWith('SELECT id, template_id, region_key') && params[0] === 0,
|
|
result: [[{ id: 900, template_id: 42, region_key: 'hero', default_content_json: { font_family: 'Acme Display', feed_id: 8 } }]]
|
|
}
|
|
]);
|
|
|
|
const pending = await getPendingMigrations(pool, { currentVersion: '2.19.0' });
|
|
assert.deepEqual(pending.map(migration => migration.version), ['2.19.1', '2.20.0', '2.20.1']);
|
|
await pending[0].run(pool);
|
|
|
|
const deletions = pool.queries.filter(([sql]) => sql.startsWith('DELETE'));
|
|
assert.deepEqual(deletions, [['DELETE FROM c_content_references WHERE reference_type = ?', ['font-family']]]);
|
|
const inserted = pool.queries.filter(([sql]) => sql.startsWith('INSERT IGNORE INTO c_content_references'))
|
|
.flatMap(([, params]) => params[0]);
|
|
assert.deepEqual(inserted.map(row => row.slice(0, 5)), [
|
|
['slide', 117, 'second_place', 'font-family', 'ubuntu regular'],
|
|
['slide', 118, 'hero', 'font-family', 'acme display'],
|
|
['template', 42, 'hero', 'font-family', 'acme display']
|
|
]);
|
|
assert.ok(pool.queries.some(([sql, params]) => sql.startsWith('SELECT id, content_json') && params[0] === 118));
|
|
assert.ok(pool.queries.some(([sql, params]) => sql.startsWith('SELECT id, template_id, region_key') && params[0] === 900));
|
|
assert.ok(!pool.queries.some(([sql]) => /^(UPDATE|ALTER|TRUNCATE)/.test(sql)));
|
|
assert.deepEqual(pool.queries.slice(-1), [['COMMIT']]);
|
|
assert.deepEqual((await getPendingMigrations(pool, { currentVersion: '2.19.1' })).map(migration => migration.version), ['2.20.0', '2.20.1']);
|
|
});
|
|
|
|
test('2.19.1 font repair rolls back without releasing the caller-owned connection on failure', async () => {
|
|
const pool = createPool([
|
|
{ match: sql => sql.includes('information_schema.COLUMNS'), result: [[{ column_count: 1 }]] },
|
|
{ match: sql => sql.includes('information_schema.TABLES'), result: [[{ table_count: 0 }]] }
|
|
]);
|
|
const [migration] = await getPendingMigrations(pool, { currentVersion: '2.19.0' });
|
|
const query = pool.query.bind(pool);
|
|
pool.query = async function (sql, params) {
|
|
if (sql.startsWith('SELECT id, content_json')) throw new Error('read failed');
|
|
return query(sql, params);
|
|
};
|
|
|
|
await assert.rejects(migration.run(pool), /read failed/);
|
|
assert.deepEqual(pool.queries.slice(-1), [['ROLLBACK']]);
|
|
assert.ok(!pool.queries.some(([sql]) => sql === 'COMMIT'));
|
|
});
|
|
|
|
test('schema startup runs the 2.19.1 repair on its existing locked connection', async () => {
|
|
const { ensureSchema } = require('../src/db');
|
|
const connection = createPool([
|
|
{ match: sql => sql.includes('SELECT GET_LOCK'), result: [[{ lock_acquired: 1 }]] },
|
|
{ match: (sql, params) => sql.includes('information_schema.TABLES') && params[0] === 'o_app_state', result: [[{ table_count: 1 }]] },
|
|
{ match: sql => sql.startsWith('SELECT state_value'), result: [[{ state_value: '2.19.0' }]] },
|
|
{ match: sql => sql.includes('information_schema.COLUMNS'), result: [[{ column_count: 1 }]] },
|
|
{ match: sql => sql.includes('information_schema.TABLES'), result: [[{ table_count: 0 }]] }
|
|
]);
|
|
connection.release = function () { connection.queries.push(['RELEASE CONNECTION']); };
|
|
let acquisitions = 0;
|
|
|
|
await ensureSchema({
|
|
async getConnection() {
|
|
acquisitions += 1;
|
|
return connection;
|
|
}
|
|
});
|
|
|
|
assert.equal(acquisitions, 1);
|
|
const commitIndex = connection.queries.findIndex(([sql]) => sql === 'COMMIT');
|
|
const versionIndex = connection.queries.findIndex(([sql, params]) => sql.startsWith('UPDATE o_app_state') && params[0] === require('../package.json').version);
|
|
assert.ok(commitIndex > 0);
|
|
assert.ok(versionIndex > commitIndex);
|
|
assert.deepEqual(connection.queries.slice(-2), [
|
|
['SELECT RELEASE_LOCK(?)', ['pulse_signage_schema_lock']],
|
|
['RELEASE CONNECTION']
|
|
]);
|
|
}); |