-
Notifications
You must be signed in to change notification settings - Fork 81
/
connector.ts
218 lines (194 loc) · 10 KB
/
connector.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
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
import {z} from "zod";
import {AnyError, indent, joinLast, Logger, plural, stripIndent} from "@azimutt/utils";
import {JsValue, Millis} from "../common";
import {DatabaseUrlParsed} from "../databaseUrl"
import {
AttributeName,
AttributeRef,
AttributeType,
AttributeValue,
CatalogName,
Database,
DatabaseName,
EntityName,
EntityRef,
SchemaName
} from "../database";
// every connector should implement this interface
// similar to https://github.com/planetscale/database-js?
export interface Connector {
name: string
getSchema(application: string, url: DatabaseUrlParsed, opts: ConnectorSchemaOpts): Promise<Database>
getQueryHistory(application: string, url: DatabaseUrlParsed, opts: ConnectorQueryHistoryOpts): Promise<DatabaseQuery[]>
execute(application: string, url: DatabaseUrlParsed, query: string, parameters: any[], opts: ConnectorDefaultOpts): Promise<QueryResults>
analyze(application: string, url: DatabaseUrlParsed, query: string, parameters: any[], opts: ConnectorDefaultOpts): Promise<QueryAnalyze>
getEntityStats(application: string, url: DatabaseUrlParsed, ref: EntityRef, opts: ConnectorDefaultOpts): Promise<ConnectorEntityStats>
getAttributeStats(application: string, url: DatabaseUrlParsed, ref: AttributeRef, opts: ConnectorDefaultOpts): Promise<ConnectorAttributeStats>
}
export type ConnectorDeps = {
logger: Logger
}
export const ConnectorConfOpts = z.object({
logQueries: z.boolean().optional() // default: false, log executed queries using the provided logger
}).strict()
export type ConnectorConfOpts = z.infer<typeof ConnectorConfOpts>
export type ConnectorDefaultOpts = ConnectorDeps & ConnectorConfOpts
export const ConnectorScopeOpts = z.object({
// filters: limit the scope of the extraction
database: DatabaseName.optional(), // export only this database or database pattern (use LIKE if contains %, equality otherwise)
catalog: CatalogName.optional(), // export only this catalog or catalog pattern (use LIKE if contains %, equality otherwise)
schema: SchemaName.optional(), // export only this schema or schema pattern (use LIKE if contains %, equality otherwise)
entity: EntityName.optional(), // export only this entity or entity pattern (use LIKE if contains %, equality otherwise)
}).strict()
export type ConnectorScopeOpts = z.infer<typeof ConnectorScopeOpts>
export const ConnectorSchemaDataOpts = z.object({
// data access: get more interesting result, beware of performance
sampleSize: z.number().optional(), // default: 100, number of documents used to infer a schema (document databases, json attributes in relational db...)
inferMixedJson: z.string().optional(), // when inferring JSON, will differentiate documents on this attribute, useful when storing several documents in the same collection in Mongo or Couchbase
inferJsonAttributes: z.boolean().optional(), // will get sample values from JSON attributes to infer a schema (nested attributes)
inferPolymorphicRelations: z.boolean().optional(), // will get distinct values from the kind attribute of polymorphic relations to create relations
inferRelationsFromJoins: z.boolean().optional(), // will fetch historical queries and suggest relations based on joins
inferPii: z.boolean().optional(), // will fetch sample rows from tables to detect columns with Personal Identifiable Information
})
export type ConnectorSchemaDataOpts = z.infer<typeof ConnectorSchemaDataOpts>
export const ConnectorSchemaConfOpts = z.object({
// post analysis:
inferRelations: z.boolean().optional(), // default: false, infer relations based on attribute names
// behavior
ignoreErrors: z.boolean().optional(), // default: false, ignore errors when fetching the schema, just log them
})
export type ConnectorSchemaConfOpts = z.infer<typeof ConnectorSchemaConfOpts>
export type ConnectorSchemaOpts = ConnectorDefaultOpts & ConnectorScopeOpts & ConnectorSchemaDataOpts & ConnectorSchemaConfOpts
export const connectorSchemaOptsDefaults = {
sampleSize: 100
}
export type ConnectorQueryHistoryOpts = ConnectorDefaultOpts & {
// filters: limit the scope of the extraction
user?: string // query stats only from this user or user pattern (use LIKE if contains %, = otherwise)
database?: DatabaseName // query stats only from this database or database pattern (use LIKE if contains %, = otherwise)
}
export const QueryId = z.string()
export type QueryId = z.infer<typeof QueryId>
// TODO define more generic and meaningful structure
export const DatabaseQuery = z.object({
id: QueryId, // query id to group duplicates
user: z.string().optional(), // the user executing the query
database: DatabaseName, // the database in which the query was executed
query: z.string(),
rows: z.number(), // accumulated rows retrieved or affected by the query
plan: z.object({count: z.number(), minTime: Millis, maxTime: Millis, sumTime: Millis, meanTime: Millis, sdTime: Millis}).strict().optional(),
exec: z.object({count: z.number(), minTime: Millis, maxTime: Millis, sumTime: Millis, meanTime: Millis, sdTime: Millis}).strict().optional(),
blocks: z.object({sumRead: z.number(), sumWrite: z.number(), sumHit: z.number(), sumDirty: z.number()}).optional(), // data from tables & indexes
blocksTmp: z.object({sumRead: z.number(), sumWrite: z.number(), sumHit: z.number(), sumDirty: z.number()}).optional(), // data from temporary tables & indexes
blocksQuery: z.object({sumRead: z.number(), sumWrite: z.number()}).optional(), // data used for the query (sorts, hashes...)
}).strict().describe('DatabaseQuery')
export type DatabaseQuery = z.infer<typeof DatabaseQuery>
export const QueryResultsAttribute = z.object({
name: z.string(),
ref: AttributeRef.optional()
}).strict()
export type QueryResultsAttribute = z.infer<typeof QueryResultsAttribute>
export const QueryResults = z.object({
query: z.string(),
attributes: QueryResultsAttribute.array(),
rows: JsValue.array()
}).strict().describe('QueryResults')
export type QueryResults = z.infer<typeof QueryResults>
// TODO
export const QueryAnalyze = z.object({}).strict().describe('QueryAnalyze')
export type QueryAnalyze = z.infer<typeof QueryAnalyze>
export const ConnectorEntityStats = EntityRef.extend({
rows: z.number(),
sampleValues: z.record(AttributeValue)
}).strict().describe('ConnectorEntityStats')
export type ConnectorEntityStats = z.infer<typeof ConnectorEntityStats>
export const ConnectorAttributeStatsValue = z.object({
value: AttributeValue,
count: z.number()
}).strict()
export type ConnectorAttributeStatsValue = z.infer<typeof ConnectorAttributeStatsValue>
export const ConnectorAttributeStats = AttributeRef.extend({
type: AttributeType,
rows: z.number(),
nulls: z.number(),
cardinality: z.number(),
commonValues: ConnectorAttributeStatsValue.array()
}).strict().describe('ConnectorAttributeStats')
export type ConnectorAttributeStats = z.infer<typeof ConnectorAttributeStats>
export const connectorScopeLevels = ['database', 'catalog', 'schema', 'entity'] as const
export type ConnectorScopeNames = {database?: string, catalog?: string, schema?: string, entity?: string}
export function formatConnectorScope(names: ConnectorScopeNames, opts: ConnectorScopeOpts): string {
const filters = connectorScopeLevels
.map(key => [names[key], opts[key]])
.filter((v): v is [string, string] => !!v[0] && !!v[1])
.map(([name, opt]) => `${opt.includes('%') ? plural(name) : name} '${opt}'`)
if (filters.length > 0) {
return joinLast(filters, ', ', ' and ')
} else {
return ''
}
}
export const logQueryIfNeeded = <U>(
id: number,
name: string | undefined,
sql: string,
parameters: any[],
exec: (sql: string, parameters: any[]) => Promise<U>,
count: (res: U) => number,
{logger, logQueries}: ConnectorDefaultOpts
): Promise<U> => {
if (logQueries) {
const start = Date.now()
const queryId = `#${id}${name ? ' ' + name : ''}`
logger.log(`${queryId} exec:\n${indent(stripIndent(sql))}`)
const res = exec(sql, parameters)
res.then(
r => logger.log(`${queryId} success: ${count(r)} rows in ${Date.now() - start} ms`),
e => logger.log(`${queryId} failure: ${e} in ${Date.now() - start} ms`)
)
return res
} else {
return exec(sql, parameters)
}
}
export function isPolymorphic(attr: AttributeName, entityAttrs: AttributeName[]): boolean {
return ['type', 'class', 'kind'].some(suffix => {
if (attr.endsWith(suffix)) {
const related = attr.slice(0, -suffix.length) + 'id'
return entityAttrs.some(c => c === related)
} else if (attr.endsWith(suffix.toUpperCase())) {
const related = attr.slice(0, -suffix.length) + 'ID'
return entityAttrs.some(c => c === related)
} else if (attr.endsWith(suffix.charAt(0).toUpperCase() + suffix.slice(1))) {
const related = attr.slice(0, -suffix.length) + 'Id'
return entityAttrs.some(c => c === related)
} else {
return false
}
})
}
export function queryError(name: string | undefined, sql: string, err: AnyError): Error {
const formattedSql = stripIndent(sql)
if (typeof err === 'object' && err !== null) {
return new Error(
err.constructor.name +
('code' in err && err.code ? ` ${err.code}` : '') +
(name ? ` on query ${name}` : '') +
('message' in err && err.message ? `: ${err.message}` : '') +
`\nQuery: ${formattedSql}`)
} else if (err) {
return new Error(`Error ${JSON.stringify(err)}\n on '${formattedSql}'`)
} else {
return new Error(`Error on '${formattedSql}'`)
}
}
export function handleError<T>(msg: string, onError: T, {logger, ignoreErrors}: ConnectorSchemaOpts) {
return (err: any): Promise<T> => {
if (ignoreErrors) {
logger.warn(`${msg}. Ignoring...`)
return Promise.resolve(onError)
} else {
return Promise.reject(err)
}
}
}