smartclickhouse/test/test.nonci.ts

import { expect, tap } from '@git.zone/tstest/tapbundle';
import * as smartclickhouse from '../ts/index.js';

let testClickhouseDb: smartclickhouse.SmartClickHouseDb;

// ============================================================
// Connection
// ============================================================

tap.test('should create a SmartClickHouseDb instance', async () => {
  testClickhouseDb = new smartclickhouse.SmartClickHouseDb({
    url: 'http://localhost:8123',
    database: 'test_smartclickhouse',
    unref: true,
  });
});

tap.test('should start the clickhouse db', async () => {
  await testClickhouseDb.start(true);
});

// ============================================================
// Backward-compatible TimeDataTable tests
// ============================================================

let timeTable: smartclickhouse.TimeDataTable;

tap.test('should create a TimeDataTable via getTable()', async () => {
  timeTable = await testClickhouseDb.getTable('analytics');
  expect(timeTable).toBeInstanceOf(smartclickhouse.TimeDataTable);
});

tap.test('should insert data via addData()', async () => {
  for (let i = 0; i < 50; i++) {
    await timeTable.addData({
      timestamp: Date.now(),
      message: `hello this is a message ${i}`,
      wow: 'hey',
      deep: {
        so: 'hello',
        myArray: ['array1', 'array2'],
      },
    });
  }
});

tap.test('should retrieve the last 10 entries', async () => {
  const entries = await timeTable.getLastEntries(10);
  expect(entries.length).toEqual(10);
});

tap.test('should retrieve entries newer than a specific timestamp', async () => {
  const timestamp = Date.now() - 60000;
  const entries = await timeTable.getEntriesNewerThan(timestamp);
  expect(entries.length).toBeGreaterThan(0);
});

tap.test('should retrieve entries between two timestamps', async () => {
  const startTimestamp = Date.now() - 120000;
  const endTimestamp = Date.now() + 5000;
  const entries = await timeTable.getEntriesBetween(startTimestamp, endTimestamp);
  expect(entries.length).toBeGreaterThan(0);
});

tap.test('should delete the time data table', async () => {
  await timeTable.delete();
  const result = await testClickhouseDb.clickhouseHttpClient.queryPromise(`
    SHOW TABLES FROM ${testClickhouseDb.options.database} LIKE '${timeTable.options.tableName}'
  `);
  expect(result.length).toEqual(0);
});

// ============================================================
// New typed ClickhouseTable API
// ============================================================

interface ILogEntry {
  timestamp: number;
  level: string;
  message: string;
  service: string;
  duration: number;
}

let logTable: smartclickhouse.ClickhouseTable<ILogEntry>;

tap.test('should create a typed table via createTable()', async () => {
  logTable = await testClickhouseDb.createTable<ILogEntry>({
    tableName: 'logs',
    orderBy: 'timestamp',
    columns: [
      { name: 'timestamp', type: "DateTime64(3, 'Europe/Berlin')" },
      { name: 'level', type: 'String' },
      { name: 'message', type: 'String' },
      { name: 'service', type: 'String' },
      { name: 'duration', type: 'Float64' },
    ],
    ttl: { column: 'timestamp', interval: '30 DAY' },
  });
  expect(logTable).toBeInstanceOf(smartclickhouse.ClickhouseTable);
});

tap.test('should insert a single row', async () => {
  await logTable.insert({
    timestamp: Date.now(),
    level: 'info',
    message: 'Server started',
    service: 'api',
    duration: 0,
  });
});

tap.test('should insert many rows', async () => {
  const rows: ILogEntry[] = [];
  for (let i = 0; i < 100; i++) {
    rows.push({
      timestamp: Date.now(),
      level: i % 10 === 0 ? 'error' : 'info',
      message: `Log message ${i}`,
      service: i % 2 === 0 ? 'api' : 'worker',
      duration: Math.random() * 2000,
    });
  }
  await logTable.insertMany(rows);
});

tap.test('should query with builder - basic where', async () => {
  const errors = await logTable.query()
    .where('level', '=', 'error')
    .orderBy('timestamp', 'DESC')
    .toArray();
  expect(errors.length).toBeGreaterThan(0);
  for (const entry of errors) {
    expect(entry.level).toEqual('error');
  }
});

tap.test('should query with builder - limit and offset', async () => {
  const result = await logTable.query()
    .orderBy('timestamp', 'DESC')
    .limit(5)
    .toArray();
  expect(result.length).toEqual(5);
});

tap.test('should query with builder - multiple conditions', async () => {
  const result = await logTable.query()
    .where('service', '=', 'api')
    .and('level', '=', 'info')
    .orderBy('timestamp', 'DESC')
    .limit(10)
    .toArray();
  for (const entry of result) {
    expect(entry.service).toEqual('api');
    expect(entry.level).toEqual('info');
  }
});

tap.test('should query with builder - IN operator', async () => {
  const result = await logTable.query()
    .where('level', 'IN', ['error', 'info'])
    .limit(10)
    .toArray();
  expect(result.length).toBeGreaterThan(0);
});

tap.test('should query first()', async () => {
  const entry = await logTable.query()
    .orderBy('timestamp', 'DESC')
    .first();
  expect(entry).toBeTruthy();
  expect(entry.level).toBeTruthy();
});

tap.test('should query count()', async () => {
  const count = await logTable.query().count();
  expect(count).toBeGreaterThan(100);
});

tap.test('should get row count', async () => {
  const count = await logTable.getRowCount();
  expect(count).toBeGreaterThan(100);
});

tap.test('should generate SQL with toSQL()', async () => {
  const sql = logTable.query()
    .where('level', '=', 'error')
    .orderBy('timestamp', 'DESC')
    .limit(10)
    .toSQL();
  expect(sql).toInclude('WHERE');
  expect(sql).toInclude('level');
  expect(sql).toInclude('ORDER BY');
  expect(sql).toInclude('LIMIT');
  console.log('Generated SQL:', sql);
});

tap.test('should execute result set operations', async () => {
  const resultSet = await logTable.query()
    .orderBy('timestamp', 'DESC')
    .limit(20)
    .execute();

  expect(resultSet.isEmpty()).toBeFalse();
  expect(resultSet.rowCount).toEqual(20);
  expect(resultSet.first()).toBeTruthy();
  expect(resultSet.last()).toBeTruthy();

  const filtered = resultSet.filter((row) => row.level === 'error');
  expect(filtered.rows.length).toBeLessThanOrEqual(20);

  const services = resultSet.map((row) => row.service);
  expect(services.length).toEqual(20);
});

// ============================================================
// UPDATE (mutation)
// ============================================================

tap.test('should update rows via mutation', async () => {
  // First, insert a specific row to update
  await logTable.insert({
    timestamp: Date.now(),
    level: 'warning',
    message: 'Deprecated API call',
    service: 'api',
    duration: 50,
  });

  // Update it
  await logTable.update(
    { level: 'warn' },
    (q) => q.where('level', '=', 'warning'),
  );

  // Verify: no more 'warning' level entries
  const warnings = await logTable.query()
    .where('level', '=', 'warning')
    .toArray();
  expect(warnings.length).toEqual(0);

  // Verify: 'warn' entries exist
  const warns = await logTable.query()
    .where('level', '=', 'warn')
    .toArray();
  expect(warns.length).toBeGreaterThan(0);
});

// ============================================================
// DELETE (targeted)
// ============================================================

tap.test('should delete rows with targeted where clause', async () => {
  const countBefore = await logTable.query()
    .where('level', '=', 'warn')
    .count();
  expect(countBefore).toBeGreaterThan(0);

  await logTable.deleteWhere(
    (q) => q.where('level', '=', 'warn'),
  );

  const countAfter = await logTable.query()
    .where('level', '=', 'warn')
    .count();
  expect(countAfter).toEqual(0);
});

// ============================================================
// Auto-schema evolution on typed table
// ============================================================

tap.test('should auto-evolve schema when inserting new fields', async () => {
  const flexTable = await testClickhouseDb.createTable<any>({
    tableName: 'flex_data',
    orderBy: 'timestamp' as any,
    autoSchemaEvolution: true,
  });

  await flexTable.insert({
    timestamp: Date.now(),
    message: 'first insert',
  });

  // Insert with a new field — should trigger schema evolution
  await flexTable.insert({
    timestamp: Date.now(),
    message: 'second insert',
    newField: 'surprise!',
    count: 42,
  });

  const columns = await flexTable.updateColumns();
  const columnNames = columns.map((c) => c.name);
  expect(columnNames).toContain('newField');
  expect(columnNames).toContain('count');

  await flexTable.drop();
});

// ============================================================
// Raw query on db
// ============================================================

tap.test('should execute raw query via db.query()', async () => {
  const result = await testClickhouseDb.query<{ cnt: string }>(
    `SELECT count() as cnt FROM ${testClickhouseDb.options.database}.logs FORMAT JSONEachRow`
  );
  expect(result.length).toEqual(1);
  expect(parseInt(result[0].cnt, 10)).toBeGreaterThan(0);
});

// ============================================================
// Cleanup
// ============================================================

tap.test('should drop the logs table', async () => {
  await logTable.drop();
});

export default tap.start();