Mirror of https://github.com/roostorg/coop
github.com/roostorg/coop
1import * as knexPkg from 'knex';
2
3import { takeLast } from './sql.js';
4
5const { knex: Knex } = knexPkg.default;
6
7describe('Sql Helpers', () => {
8 describe('takeLast', () => {
9 test('should work for simple queries', () => {
10 type User = { id: string; name: string; email: string };
11
12 const knex = Knex({ dialect: 'postgres' });
13 const users = knex<User>('users').select('id', 'name');
14
15 const result = takeLast(users, [{ column: 'id', order: 'desc' }], 2);
16
17 expect(result.toString()).toEqual(
18 'select * from (select "id", "name" from "users" order by "id" asc limit 2) as "dc2d41a9-082e-48b0-a66f-345a22696b02" order by "id" desc',
19 );
20 });
21
22 test('should work for arbitrarily complex queries', () => {
23 // We'll test this with the real query we use for backtesting.
24 // This is still only one case (notably, with no joins), but at least it
25 // uses aliases and a WHERE, so it'll give us a bit more confidence.
26 const knex = Knex({ dialect: 'postgres' });
27 type Result = {
28 orgId: string;
29 ts: string;
30 content: string;
31 correlationId: string;
32 };
33
34 const backtestResults = knex<Result>('RULE_EXECUTIONS')
35 .select({
36 orgId: 'ORG_ID',
37 ts: 'TS',
38 content: 'CONTENT',
39 correlationId: 'CORRELATION_ID',
40 })
41 .where('CORRELATION_ID', '=', '47')
42 .andWhere('TS', '>', '2019-01-01');
43
44 const result = takeLast(
45 backtestResults,
46 [{ column: 'ts', order: 'asc' }],
47 50,
48 );
49
50 expect(result.toString()).toMatchInlineSnapshot(
51 `"select * from (select "ORG_ID" as "orgId", "TS" as "ts", "CONTENT" as "content", "CORRELATION_ID" as "correlationId" from "RULE_EXECUTIONS" where "CORRELATION_ID" = '47' and "TS" > '2019-01-01' order by "ts" desc limit 50) as "dc2d41a9-082e-48b0-a66f-345a22696b02" order by "ts" asc"`,
52 );
53 });
54 });
55});