directus

1
import type { Knex } from 'knex';
2
import type { FnHelperOptions } from '../types.js';
3
import { FnHelper } from '../types.js';
4

5
const parseLocaltime = (columnType?: string) => {
6
	if (columnType === 'timestamp') {
7
		return '';
8
	}
9

10
	return `, 'localtime'`;
11
};
12

13
export class FnHelperSQLite extends FnHelper {
14
	year(table: string, column: string, options?: FnHelperOptions): Knex.Raw {
15
		return this.knex.raw(`CAST(strftime('%Y', ??.?? / 1000, 'unixepoch'${parseLocaltime(options?.type)}) AS INTEGER)`, [
16
			table,
17
			column,
18
		]);
19
	}
20

21
	month(table: string, column: string, options?: FnHelperOptions): Knex.Raw {
22
		return this.knex.raw(`CAST(strftime('%m', ??.?? / 1000, 'unixepoch'${parseLocaltime(options?.type)}) AS INTEGER)`, [
23
			table,
24
			column,
25
		]);
26
	}
27

28
	week(table: string, column: string, options?: FnHelperOptions): Knex.Raw {
29
		return this.knex.raw(`CAST(strftime('%W', ??.?? / 1000, 'unixepoch'${parseLocaltime(options?.type)}) AS INTEGER)`, [
30
			table,
31
			column,
32
		]);
33
	}
34

35
	day(table: string, column: string, options?: FnHelperOptions): Knex.Raw {
36
		return this.knex.raw(`CAST(strftime('%d', ??.?? / 1000, 'unixepoch'${parseLocaltime(options?.type)}) AS INTEGER)`, [
37
			table,
38
			column,
39
		]);
40
	}
41

42
	weekday(table: string, column: string, options?: FnHelperOptions): Knex.Raw {
43
		return this.knex.raw(`CAST(strftime('%w', ??.?? / 1000, 'unixepoch'${parseLocaltime(options?.type)}) AS INTEGER)`, [
44
			table,
45
			column,
46
		]);
47
	}
48

49
	hour(table: string, column: string, options?: FnHelperOptions): Knex.Raw {
50
		return this.knex.raw(`CAST(strftime('%H', ??.?? / 1000, 'unixepoch'${parseLocaltime(options?.type)}) AS INTEGER)`, [
51
			table,
52
			column,
53
		]);
54
	}
55

56
	minute(table: string, column: string, options?: FnHelperOptions): Knex.Raw {
57
		return this.knex.raw(`CAST(strftime('%M', ??.?? / 1000, 'unixepoch'${parseLocaltime(options?.type)}) AS INTEGER)`, [
58
			table,
59
			column,
60
		]);
61
	}
62

63
	second(table: string, column: string, options?: FnHelperOptions): Knex.Raw {
64
		return this.knex.raw(`CAST(strftime('%S', ??.?? / 1000, 'unixepoch'${parseLocaltime(options?.type)}) AS INTEGER)`, [
65
			table,
66
			column,
67
		]);
68
	}
69

70
	count(table: string, column: string, options?: FnHelperOptions): Knex.Raw<any> {
71
		const collectionName = options?.originalCollectionName || table;
72
		const type = this.schema.collections?.[collectionName]?.fields?.[column]?.type ?? 'unknown';
73

74
		if (type === 'json') {
75
			return this.knex.raw(`json_array_length(??.??, '$')`, [table, column]);
76
		}
77

78
		if (type === 'alias') {
79
			return this._relationalCount(table, column, options);
80
		}
81

82
		throw new Error(`Couldn't extract type from ${table}.${column}`);
83
	}
84
}
85