All files / blong-gogo/src/adapter/schema schemaProcedureBind.ts

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
import {type IMeta, library} from '@feasibleone/blong';
import {Type, type TFunction, type TSchema} from 'typebox';

function snakeToCamel(str: string): string {
    return str.replace(/([-_]\w)/g, g => g[1].toUpperCase());
}

function sqlTypeToTypebox(sqlType: string): TSchema {
    switch (sqlType.toUpperCase()) {
        case 'VARCHAR':
        case 'CHAR':
        case 'TEXT':
        case 'MEDIUMTEXT':
        case 'LONGTEXT':
        case 'TINYTEXT':
        case 'ENUM':
        case 'SET':
            return Type.String();
        case 'INT':
        case 'INTEGER':
        case 'BIGINT':
        case 'SMALLINT':
        case 'TINYINT':
        case 'MEDIUMINT':
            return Type.Integer();
        case 'DECIMAL':
        case 'FLOAT':
        case 'DOUBLE':
        case 'NUMERIC':
        case 'REAL':
            return Type.Number();
        case 'BOOLEAN':
        case 'BOOL':
        case 'BIT':
            return Type.Boolean();
        case 'DATE':
            return Type.String({format: 'date'});
        case 'DATETIME':
        case 'TIMESTAMP':
            return Type.String({format: 'date-time'});
        case 'TIME':
            return Type.String({format: 'time'});
        case 'JSON':
            return Type.Unknown();
        default:
            return Type.String();
    }
}

export default library(
    ({config}) =>
        async function schemaProcedureBind(
            namespace: string,
            schema?: string,
        ): Promise<{
            handlers: Record<
                string,
                (params: Record<string, unknown>, $meta: IMeta) => Promise<unknown>
            >;
            schemas: Record<string, TFunction>;
        }> {
            // eslint-disable-next-line @typescript-eslint/no-explicit-any
            const knex = (config as Record<string, any>)?.context?.queryBuilder;
            if (!knex) throw new Error('Knex queryBuilder not available in adapter context');

            const dbName =
                schema ?? (knex.client?.config?.connection?.database as string | undefined);

            const procedures: Array<{ROUTINE_NAME: string}> = await knex
                .select('ROUTINE_NAME')
                .from('information_schema.ROUTINES')
                .where('ROUTINE_SCHEMA', dbName)
                .where('ROUTINE_TYPE', 'PROCEDURE')
                .andWhere('ROUTINE_NAME', 'like', `${namespace}%`);

            const handlers: Record<
                string,
                (params: Record<string, unknown>, $meta: IMeta) => Promise<unknown>
            > = {};
            const schemas: Record<string, TFunction> = {};

            for (const proc of procedures) {
                const spName = proc.ROUTINE_NAME;
                const handlerName = snakeToCamel(spName);

                const parameters: Array<{
                    PARAMETER_NAME: string;
                    DATA_TYPE: string;
                    PARAMETER_MODE: string;
                }> = await knex
                    .select('PARAMETER_NAME', 'DATA_TYPE', 'PARAMETER_MODE')
                    .from('information_schema.PARAMETERS')
                    .where('SPECIFIC_SCHEMA', dbName)
                    .where('SPECIFIC_NAME', spName)
                    .whereNotNull('PARAMETER_NAME')
                    .orderBy('ORDINAL_POSITION');

                const inParams = parameters.filter(
                    p => p.PARAMETER_MODE === 'IN' || p.PARAMETER_MODE === 'INOUT',
                );

                handlers[handlerName] = async function (
                    params: Record<string, unknown>,
                ) {
                    const placeholders = inParams.map(() => '?').join(', ');
                    const values = inParams.map(p => params[snakeToCamel(p.PARAMETER_NAME)]);
                    const result = await knex.raw(
                        `CALL \`${spName}\`(${placeholders})`,
                        values,
                    );
                    return Array.isArray(result?.[0]) ? result[0][0] : result?.[0];
                };

                const paramProperties: Record<string, TSchema> = {};
                for (const p of inParams) {
                    paramProperties[snakeToCamel(p.PARAMETER_NAME)] = sqlTypeToTypebox(
                        p.DATA_TYPE,
                    );
                }

                schemas[handlerName] = Type.Function(
                    [Type.Object(paramProperties)],
                    Type.Promise(Type.Unknown()),
                );
            }

            return {handlers, schemas};
        },
);