directus

1
import {
2
	ContainsNullValuesError,
3
	InvalidForeignKeyError,
4
	NotNullViolationError,
5
	RecordNotUniqueError,
6
	ValueOutOfRangeError,
7
	ValueTooLongError,
8
} from '@directus/errors';
9

10
import getDatabase from '../../index.js';
11
import type { MSSQLError } from './types.js';
12

13
enum MSSQLErrorCodes {
14
	FOREIGN_KEY_VIOLATION = 547,
15
	NOT_NULL_VIOLATION = 515,
16
	NUMERIC_VALUE_OUT_OF_RANGE = 220,
17
	UNIQUE_VIOLATION = 2601, // or 2627
18
	VALUE_LIMIT_VIOLATION = 2628,
19
}
20

21
export async function extractError(error: MSSQLError): Promise<MSSQLError | Error> {
22
	switch (error.number) {
23
		case MSSQLErrorCodes.UNIQUE_VIOLATION:
24
		case 2627:
25
			return await uniqueViolation(error);
26
		case MSSQLErrorCodes.NUMERIC_VALUE_OUT_OF_RANGE:
27
			return numericValueOutOfRange(error);
28
		case MSSQLErrorCodes.VALUE_LIMIT_VIOLATION:
29
			return valueLimitViolation(error);
30
		case MSSQLErrorCodes.NOT_NULL_VIOLATION:
31
			return notNullViolation(error);
32
		case MSSQLErrorCodes.FOREIGN_KEY_VIOLATION:
33
			return foreignKeyViolation(error);
34
	}
35

36
	return error;
37
}
38

39
async function uniqueViolation(error: MSSQLError) {
40
	/**
41
	 * NOTE:
42
	 * SQL Server doesn't return the name of the offending column when a unique constraint is thrown:
43
	 *
44
	 * insert into [articles] ([unique]) values (@p0)
45
	 * - Violation of UNIQUE KEY constraint 'UQ__articles__5A062640242004EB'.
46
	 * Cannot insert duplicate key in object 'dbo.articles'. The duplicate key value is (rijk).
47
	 *
48
	 * While it's not ideal, the best next thing we can do is extract the column name from
49
	 * information_schema when this happens
50
	 */
51

52
	const betweenQuotes = /'([^']+)'/g;
53
	const betweenParens = /\(([^)]+)\)/g;
54

55
	const quoteMatches = error.message.match(betweenQuotes);
56
	const parenMatches = error.message.match(betweenParens);
57

58
	if (!quoteMatches || !parenMatches) return error;
59

60
	const keyName = quoteMatches[1]!.slice(1, -1);
61

62
	let collection = quoteMatches[0]!.slice(1, -1);
63
	let field: string | null = null;
64

65
	if (keyName) {
66
		const database = getDatabase();
67

68
		const constraintUsage = await database
69
			.select('sys.columns.name as field', database.raw('OBJECT_NAME(??) as collection', ['sys.columns.object_id']))
70
			.from('sys.indexes')
71
			.innerJoin('sys.index_columns', (join) => {
72
				join
73
					.on('sys.indexes.object_id', '=', 'sys.index_columns.object_id')
74
					.andOn('sys.indexes.index_id', '=', 'sys.index_columns.index_id');
75
			})
76
			.innerJoin('sys.columns', (join) => {
77
				join
78
					.on('sys.index_columns.object_id', '=', 'sys.columns.object_id')
79
					.andOn('sys.index_columns.column_id', '=', 'sys.columns.column_id');
80
			})
81
			.where('sys.indexes.name', '=', keyName)
82
			.first();
83

84
		collection = constraintUsage?.collection;
85
		field = constraintUsage?.field;
86
	}
87

88
	return new RecordNotUniqueError({
89
		collection,
90
		field,
91
	});
92
}
93

94
function numericValueOutOfRange(error: MSSQLError) {
95
	const betweenBrackets = /\[([^\]]+)\]/g;
96

97
	const bracketMatches = error.message.match(betweenBrackets);
98

99
	if (!bracketMatches) return error;
100

101
	const collection = bracketMatches[0].slice(1, -1);
102

103
	/**
104
	 * NOTE
105
	 * MS SQL Doesn't return the offending column name in the error, nor any other identifying information
106
	 * we can use to extract the column name :(
107
	 *
108
	 * insert into [test1] ([small]) values (@p0)
109
	 * - Arithmetic overflow error for data type tinyint, value = 50000.
110
	 */
111

112
	const field = null;
113

114
	return new ValueOutOfRangeError({
115
		collection,
116
		field,
117
	});
118
}
119

120
function valueLimitViolation(error: MSSQLError) {
121
	const betweenBrackets = /\[([^\]]+)\]/g;
122
	const betweenQuotes = /'([^']+)'/g;
123

124
	const bracketMatches = error.message.match(betweenBrackets);
125
	const quoteMatches = error.message.match(betweenQuotes);
126

127
	if (!bracketMatches || !quoteMatches) return error;
128

129
	const collection = bracketMatches[0].slice(1, -1);
130
	const field = quoteMatches[1]!.slice(1, -1);
131

132
	return new ValueTooLongError({
133
		collection,
134
		field,
135
	});
136
}
137

138
function notNullViolation(error: MSSQLError) {
139
	const betweenBrackets = /\[([^\]]+)\]/g;
140
	const betweenQuotes = /'([^']+)'/g;
141

142
	const bracketMatches = error.message.match(betweenBrackets);
143
	const quoteMatches = error.message.match(betweenQuotes);
144

145
	if (!bracketMatches || !quoteMatches) return error;
146

147
	const collection = bracketMatches[0].slice(1, -1);
148
	const field = quoteMatches[0].slice(1, -1);
149

150
	if (error.message.includes('Cannot insert the value NULL into column')) {
151
		return new ContainsNullValuesError({ collection, field });
152
	}
153

154
	return new NotNullViolationError({
155
		collection,
156
		field,
157
	});
158
}
159

160
function foreignKeyViolation(error: MSSQLError) {
161
	const betweenUnderscores = /__(.+)__/g;
162
	const betweenParens = /\(([^)]+)\)/g;
163

164
	// NOTE:
165
	// Seeing that MS SQL doesn't return the offending column name, we have to extract it from the
166
	// foreign key constraint name as generated by the database. This'll probably fail if you have
167
	// custom names for whatever reason.
168

169
	const underscoreMatches = error.message.match(betweenUnderscores);
170
	const parenMatches = error.message.match(betweenParens);
171

172
	if (!underscoreMatches || !parenMatches) return error;
173

174
	const underscoreParts = underscoreMatches[0].split('__');
175

176
	const collection = underscoreParts[1]!;
177
	const field = underscoreParts[2]!;
178

179
	return new InvalidForeignKeyError({
180
		collection,
181
		field,
182
	});
183
}
184