Skip to content

Free, Open Source, Self-Hosted WhatsApp API Gateway

7.7 KB
Raw Blame
import 'reflect-metadata';
import { DataSource, Logger } from 'typeorm';
import { MessageService } from './message.service';
import { Message, MessageDirection } from './entities/message.entity';
import type { EngineRegistry } from '../../engine/engine-registry.service';
import type { MessageProjector } from '../session/message-projector.service';
import type { HookManager } from '../../core/hooks';
import type { LidMappingStoreService } from '../../engine/identity/lid-mapping-store.service';
import type { SendPacingService } from './send-pacing.service';
import type { MessageSendService } from './message-send.service';

/**
 * A chat thread is read with a chatId IN (...) filter, and SQLite here never has planner statistics.
 * Without them it walked the whole session through (sessionId, createdAt) to fill one chat's page,
 * so the plans of the statements getMessages actually issues are pinned against the entity schema.
 */
describe('message list query plans on SQLite', () => {
  let ds: DataSource;
  let service: MessageService;
  const queries: Array<{ sql: string; params?: unknown[] }> = [];

  const logger: Logger = {
    logQuery: (sql, params) => {
      queries.push({ sql, params: params as unknown[] | undefined });
    },
    logQueryError: () => undefined,
    logQuerySlow: () => undefined,
    logSchemaBuild: () => undefined,
    logMigration: () => undefined,
    log: () => undefined,
  };

  beforeEach(async () => {
    ds = new DataSource({
      type: 'better-sqlite3',
      database: ':memory:',
      entities: [Message],
      synchronize: true,
      logging: ['query'],
      logger,
    });
    await ds.initialize();
    service = new MessageService(
      ds.getRepository(Message),
      {} as EngineRegistry,
      {} as MessageProjector,
      {} as HookManager,
      {
        findLidsForPhone: () => Promise.resolve([]),
        findPhoneForLid: () => Promise.resolve(null),
      } as unknown as LidMappingStoreService,
      {} as SendPacingService,
      {} as MessageSendService,
    );
    queries.length = 0;
  });

  afterEach(async () => {
    await ds.destroy();
  });

  /** The ordered, limited statement that picks the page. */
  const pageQuery = (): { sql: string; params?: unknown[] } => {
    const page = queries.find(q => q.sql.includes('ORDER BY') && q.sql.includes('LIMIT'));
    expect(page).toBeDefined();
    return page!;
  };

  /** EXPLAIN QUERY PLAN of the page select getMessages issued. */
  const pagePlan = async (): Promise<string> => {
    const page = pageQuery();
    const rows = await ds.query<{ detail: string }[]>(`EXPLAIN QUERY PLAN ${page.sql}`, page.params);
    return rows.map(r => r.detail).join(' | ');
  };

  const countPlan = async (): Promise<string> => {
    const count = queries.find(q => q.sql.includes('COUNT('));
    expect(count).toBeDefined();
    const rows = await ds.query<{ detail: string }[]>(`EXPLAIN QUERY PLAN ${count!.sql}`, count!.params);
    return rows.map(r => r.detail).join(' | ');
  };

  it('seeks one user chat through the (sessionId, chatId, createdAt) index', async () => {
    // A user chat expands to several dialect candidates (@c.us and @s.whatsapp.net at least).
    await service.getMessages('s1', { chatId: '[email protected]', limit: 50 });

    expect(await pagePlan()).toContain('IDX_messages_sessionId_chatId_createdAt (sessionId=? AND chatId=?)');
    expect(await countPlan()).toContain('IDX_messages_sessionId_chatId_createdAt (sessionId=? AND chatId=?)');
  });

  it('reads a single-candidate chat from the same index in order, with no sort', async () => {
    await service.getMessages('s1', { chatId: '[email protected]', limit: 50 });

    const plan = await pagePlan();
    expect(plan).toContain('IDX_messages_sessionId_chatId_createdAt (sessionId=? AND chatId=?)');
    expect(plan).not.toContain('TEMP B-TREE');
  });

  it('keeps the unfiltered list on the (sessionId, createdAt) index with no sort', async () => {
    await service.getMessages('s1', { limit: 50 });

    const plan = await pagePlan();
    expect(plan).toContain('(sessionId=?)');
    expect(plan).not.toContain('chatId');
    expect(plan).not.toContain('TEMP B-TREE');
  });

  /**
   * A sort evaluates its result columns for every matching row before the limit applies, so a size
   * computed in the ordered statement read the stored payload of every row in the chat. The size is
   * read by id for the page rows only, and as a byte count, which neither dialect has to load the
   * payload for.
   */
  it('reads metadata sizes for the page rows only, outside the ordered statement', async () => {
    const repo = ds.getRepository(Message);
    await repo.save(
      ['a', 'b', 'c'].map(n =>
        repo.create({ sessionId: 's1', chatId: '[email protected]', waMessageId: n, from: 'x', to: 'y', body: n }),
      ),
    );
    queries.length = 0;

    const { messages } = await service.getMessages('s1', { chatId: '[email protected]', limit: 2 });

    expect(messages).toHaveLength(2);
    expect(pageQuery().sql).not.toMatch(/metadata/i);
    const sizes = queries.filter(q => /LENGTH\(/i.test(q.sql));
    expect(sizes).toHaveLength(1);
    expect(sizes[0].sql).toMatch(/OCTET_LENGTH\(/);
    expect(sizes[0].sql).not.toMatch(/(^|[^_])LENGTH\(/);
    expect(sizes[0].sql).not.toContain('ORDER BY');
  });
  /**
   * The page rows are read by id, from about ten ids on SQLite prefers the session or chat index for that
   * statement and visits every row of it (the same session of 300k rows took 20 ms a statement, against 0.05
   * ms by key). Every read of the page's rows has to be a primary key lookup, whatever the list is filtered by.
   */
  describe('the reads of a page by id', () => {
    const seedChat = async (): Promise<void> => {
      const repo = ds.getRepository(Message);
      await repo.save(
        Array.from({ length: 120 }, (_, i) =>
          repo.create({
            sessionId: 's1',
            chatId: '[email protected]',
            waMessageId: `w${i}`,
            from: '[email protected]',
            to: 'y',
            body: 'b',
            type: 'text',
            direction: MessageDirection.INCOMING,
            timestamp: 1000 + i,
          }),
        ),
      );
      queries.length = 0;
    };
    const plansOfReadsById = async (): Promise<string[]> => {
      const reads = queries.filter(q => /"message"\."id" IN/.test(q.sql) && !/^\s*SELECT\s+COUNT/i.test(q.sql));
      const plans: string[] = [];
      for (const read of reads) {
        const rows = await ds.query<{ detail: string }[]>(`EXPLAIN QUERY PLAN ${read.sql}`, read.params);
        plans.push(rows.map(r => r.detail).join(' | '));
      }
      return plans;
    };

    it.each([
      ['an unfiltered list', {}],
      ['one chat', { chatId: '[email protected]' }],
      [
        'every filter',
        {
          chatId: '[email protected]',
          from: '[email protected]',
          direction: MessageDirection.INCOMING,
          type: 'text',
          since: 1000000,
          until: 1200000,
          orderBy: 'timestamp' as const,
        },
      ],
    ])('looks %s up by primary key', async (_label, options) => {
      await seedChat();

      const { messages } = await service.getMessages('s1', { ...options, limit: 100, inlineMedia: false });

      expect(messages).toHaveLength(100);
      const plans = await plansOfReadsById();
      expect(plans).toHaveLength(2);
      for (const plan of plans) {
        expect(plan).toBe('SEARCH message USING INDEX sqlite_autoindex_messages_1 (id=?)');
      }
    });

    it('reads a page chunk without the list order', async () => {
      await seedChat();

      await service.getMessages('s1', { limit: 100, inlineMedia: false });

      const chunk = queries.find(q => /"message"\."id" IN/.test(q.sql) && !/OCTET_LENGTH/.test(q.sql));
      expect(chunk).toBeDefined();
      expect(chunk!.sql).not.toContain('ORDER BY');
    });
  });
});