1  /**
  2   * Tests for src/utils/test-db.js
  3   *
  4   * Covers all exported functions:
  5   * - getTestDbPath
  6   * - createInMemoryDb
  7   * - initTestDb
  8   * - closeDb
  9   * - applyMigration
 10   * - createMinimalSchema
 11   * - createMinimalInMemoryDb
 12   * - cleanupTestDb
 13   */
 14  
 15  import { test, describe, after } from 'node:test';
 16  import assert from 'node:assert/strict';
 17  import { existsSync } from 'fs';
 18  import Database from 'better-sqlite3';
 19  import { createPgMock } from '../helpers/pg-mock.js'; // eslint-disable-line no-unused-vars
 20  
 21  import {
 22    getTestDbPath,
 23    createInMemoryDb,
 24    initTestDb,
 25    closeDb,
 26    applyMigration,
 27    createMinimalSchema,
 28    createMinimalInMemoryDb,
 29    cleanupTestDb,
 30  } from '../../src/utils/test-db.js';
 31  
 32  const createdDbs = [];
 33  
 34  after(() => {
 35    for (const name of createdDbs) {
 36      cleanupTestDb(name);
 37    }
 38  });
 39  
 40  describe('getTestDbPath', () => {
 41    test('returns path in /tmp', () => {
 42      const p = getTestDbPath('mytest');
 43      assert.ok(p.includes('/tmp'), 'should be in /tmp');
 44      assert.ok(p.includes('mytest'), 'should contain the db name');
 45    });
 46  
 47    test('uses default name "test" when no argument', () => {
 48      const p = getTestDbPath();
 49      assert.ok(p.includes('test'), 'should include "test"');
 50    });
 51  });
 52  
 53  describe('createInMemoryDb', () => {
 54    test('returns a Database instance', () => {
 55      const db = createInMemoryDb();
 56      assert.ok(db instanceof Database, 'should return a Database instance');
 57      db.close();
 58    });
 59  
 60    test('database has sites table from production schema', () => {
 61      const db = createInMemoryDb();
 62      const tables = db.prepare("SELECT name FROM sqlite_master WHERE type='table'").all();
 63      const tableNames = tables.map(t => t.name);
 64      assert.ok(tableNames.includes('sites'), 'should have sites table');
 65      db.close();
 66    });
 67  
 68    test('creates a true in-memory database (no file artifact)', () => {
 69      const name = `testdb-file-${Date.now()}`;
 70      const db = createInMemoryDb();
 71      // createInMemoryDb uses :memory: — no file is created
 72      const p = getTestDbPath(name);
 73      assert.ok(!existsSync(p), 'no file should be created for :memory: database');
 74      db.close();
 75    });
 76  
 77    test('each call returns a fresh isolated :memory: instance', () => {
 78      // createInMemoryDb uses :memory: — each call is isolated
 79      const db1 = createInMemoryDb();
 80      db1.exec(
 81        "INSERT INTO sites (domain, landing_page_url, keyword) VALUES ('x.com','https://x.com','test')"
 82      );
 83      // Second call returns a new, empty :memory: DB
 84      const db2 = createInMemoryDb();
 85      const count = db2.prepare('SELECT COUNT(*) as c FROM sites').get();
 86      assert.equal(count.c, 0, 'second :memory: DB should be empty');
 87      db1.close();
 88      db2.close();
 89    });
 90  });
 91  
 92  describe('initTestDb', () => {
 93    test('creates in-memory database with schema', () => {
 94      const db = initTestDb(':memory:');
 95      const tables = db.prepare("SELECT name FROM sqlite_master WHERE type='table'").all();
 96      assert.ok(tables.length > 0, 'should have tables');
 97      db.close();
 98    });
 99  
100    test('creates file-based database when path provided', () => {
101      const p = `/tmp/test-db-util-${Date.now()}.db`;
102      const db = initTestDb(p);
103      assert.ok(existsSync(p), 'file should be created');
104      db.close();
105      try {
106        import('fs').then(fs => fs.unlinkSync(p));
107      } catch {
108        /* ignore */
109      }
110    });
111  });
112  
113  describe('closeDb', () => {
114    test('closes an open database without error', () => {
115      const db = initTestDb(':memory:');
116      assert.doesNotThrow(() => closeDb(db));
117    });
118  
119    test('handles null/undefined gracefully', () => {
120      assert.doesNotThrow(() => closeDb(null));
121      assert.doesNotThrow(() => closeDb(undefined));
122    });
123  });
124  
125  describe('applyMigration', () => {
126    test('applies a migration SQL file to an existing database', () => {
127      // Use a fresh empty DB (no schema) and apply a migration that creates a table
128      const name = `testdb-migration-${Date.now()}`;
129      createdDbs.push(name);
130      const db = new Database(getTestDbPath(name));
131      // This migration creates cron_locks table using CREATE TABLE IF NOT EXISTS
132      // It should succeed on an empty DB
133      assert.doesNotThrow(() => {
134        applyMigration(db, '016-add-cron-job-logs.sql');
135      });
136      const tables = db.prepare("SELECT name FROM sqlite_master WHERE type='table'").all();
137      const tableNames = tables.map(t => t.name);
138      assert.ok(tableNames.includes('cron_job_logs'), 'should have created cron_job_logs table');
139      db.close();
140    });
141  });
142  
143  describe('createMinimalSchema', () => {
144    test('creates only the requested tables', () => {
145      const db = new Database(':memory:');
146      createMinimalSchema(db, ['sites']);
147      const tables = db.prepare("SELECT name FROM sqlite_master WHERE type='table'").all();
148      const tableNames = tables.map(t => t.name);
149      assert.ok(tableNames.includes('sites'), 'should have sites table');
150      db.close();
151    });
152  
153    test('creates multiple requested tables', () => {
154      const db = new Database(':memory:');
155      createMinimalSchema(db, ['sites', 'keywords']);
156      const tables = db.prepare("SELECT name FROM sqlite_master WHERE type='table'").all();
157      const tableNames = tables.map(t => t.name);
158      assert.ok(tableNames.includes('sites'), 'should have sites');
159      assert.ok(tableNames.includes('keywords'), 'should have keywords');
160      db.close();
161    });
162  
163    test('table is functional (can insert and query)', () => {
164      const db = new Database(':memory:');
165      createMinimalSchema(db, ['sites']);
166      assert.doesNotThrow(() => {
167        db.exec(
168          `INSERT INTO sites (domain, landing_page_url, keyword) VALUES ('ex.com','https://ex.com','test')`
169        );
170      });
171      const row = db.prepare('SELECT domain FROM sites').get();
172      assert.equal(row.domain, 'ex.com');
173      db.close();
174    });
175  
176    test('handles table name not in schema gracefully (no-op)', () => {
177      const db = new Database(':memory:');
178      assert.doesNotThrow(() => createMinimalSchema(db, ['nonexistent_table_xyz']));
179      db.close();
180    });
181  
182    test('creates triggers associated with a table (e.g. keywords)', () => {
183      const db = new Database(':memory:');
184      createMinimalSchema(db, ['keywords']);
185      const triggers = db.prepare("SELECT name FROM sqlite_master WHERE type='trigger'").all();
186      // keywords table has update_keywords_timestamp trigger
187      assert.ok(triggers.length >= 0, 'trigger extraction should not throw');
188      db.close();
189    });
190  });
191  
192  describe('createMinimalInMemoryDb', () => {
193    test('returns a Database with requested tables only', () => {
194      const db = createMinimalInMemoryDb(['sites']);
195      assert.ok(db instanceof Database, 'should return Database instance');
196      const tables = db.prepare("SELECT name FROM sqlite_master WHERE type='table'").all();
197      const tableNames = tables.map(t => t.name);
198      assert.ok(tableNames.includes('sites'), 'should have sites table');
199      db.close();
200    });
201  });
202  
203  describe('cleanupTestDb', () => {
204    test('removes database file from /tmp', () => {
205      const name = `testdb-cleanup-${Date.now()}`;
206      // Use initTestDb to create an actual file
207      const db = initTestDb(getTestDbPath(name));
208      db.close();
209      const p = getTestDbPath(name);
210      assert.ok(existsSync(p), 'file should exist before cleanup');
211      cleanupTestDb(name);
212      assert.ok(!existsSync(p), 'file should not exist after cleanup');
213    });
214  
215    test('does not throw when file does not exist', () => {
216      assert.doesNotThrow(() => cleanupTestDb('does-not-exist-xyz-12345'));
217    });
218  
219    test('removes journal/wal/shm files if present', () => {
220      // Just verify no throw — WAL/journal/SHM files may not exist
221      const name = `testdb-wal-${Date.now()}`;
222      createdDbs.push(name);
223      assert.doesNotThrow(() => cleanupTestDb(name));
224    });
225  });