directus

1
import { createInspector } from '@directus/schema';
2
import type { RelationMeta } from '@directus/types';
3
import type { Knex } from 'knex';
4
import { useLogger } from '../../logger.js';
5
import { getDefaultIndexName } from '../../utils/get-default-index-name.js';
6

7
export async function up(knex: Knex): Promise<void> {
8
	const logger = useLogger();
9

10
	const inspector = createInspector(knex);
11

12
	const foreignKeys = await inspector.foreignKeys();
13

14
	const relations = await knex
15
		.select<RelationMeta[]>('id', 'many_collection', 'many_field', 'one_collection')
16
		.from('directus_relations');
17

18
	const constraintsToAdd = relations.filter((relation) => {
19
		const exists = !!foreignKeys.find(
20
			(fk) => fk.table === relation?.many_collection && fk.column === relation?.many_field,
21
		);
22

23
		return exists === false;
24
	});
25

26
	const corruptedRelations: number[] = [];
27

28
	for (const constraint of constraintsToAdd) {
29
		if (!constraint.one_collection) continue;
30

31
		if (
32
			(await inspector.hasTable(constraint.many_collection)) === false ||
33
			(await inspector.hasTable(constraint.one_collection)) === false
34
		) {
35
			logger.warn(
36
				`Ignoring ${constraint.many_collection}.${constraint.many_field}<->${constraint.one_collection}. Tables don't exist.`,
37
			);
38

39
			corruptedRelations.push(constraint.id);
40
			continue;
41
		}
42

43
		const currentPrimaryKeyField = await inspector.primary(constraint.many_collection);
44
		const relatedPrimaryKeyField = await inspector.primary(constraint.one_collection);
45

46
		if (constraint.many_field === currentPrimaryKeyField) {
47
			logger.warn(
48
				`Illegal relationship ${constraint.many_collection}.${constraint.many_field}<->${constraint.one_collection} encountered. Many field equals collections primary key.`,
49
			);
50

51
			corruptedRelations.push(constraint.id);
52
			continue;
53
		}
54

55
		if (!currentPrimaryKeyField || !relatedPrimaryKeyField) continue;
56

57
		const rowsWithIllegalFKValues = await knex
58
			.select(`main.${currentPrimaryKeyField}`)
59
			.from({ main: constraint.many_collection })
60
			.leftJoin(
61
				{ related: constraint.one_collection },
62
				`main.${constraint.many_field}`,
63
				`related.${relatedPrimaryKeyField}`,
64
			)
65
			.whereNull(`related.${relatedPrimaryKeyField}`);
66

67
		if (rowsWithIllegalFKValues.length > 0) {
68
			const ids: (string | number)[] = rowsWithIllegalFKValues.map<string | number>(
69
				(row) => row[currentPrimaryKeyField],
70
			);
71

72
			try {
73
				await knex(constraint.many_collection)
74
					.update({ [constraint.many_field]: null })
75
					.whereIn(currentPrimaryKeyField, ids);
76
			} catch (err: any) {
77
				logger.error(
78
					`${constraint.many_collection}.${constraint.many_field} contains illegal foreign keys which couldn't be set to NULL. Please fix these references and rerun this migration to complete the upgrade.`,
79
				);
80

81
				if (ids.length < 25) {
82
					logger.error(`Items with illegal foreign keys: ${ids.join(', ')}`);
83
				} else {
84
					logger.error(`Items with illegal foreign keys: ${ids.slice(0, 25).join(', ')} and ${ids.length} others`);
85
				}
86

87
				throw 'Migration aborted';
88
			}
89
		}
90

91
		// MySQL doesn't accept FKs from `int` to `int unsigned`. `knex` defaults `.increments()`
92
		// to `unsigned`, but defaults `.integer()` to `int`. This means that created m2o fields
93
		// have the wrong type. This step will force the m2o `int` field into `unsigned`, but only
94
		// if both types are integers, and only if we go from `int` to `int unsigned`.
95
		const columnInfo = await inspector.columnInfo(constraint.many_collection, constraint.many_field);
96
		const relatedColumnInfo = await inspector.columnInfo(constraint.one_collection!, relatedPrimaryKeyField);
97

98
		try {
99
			await knex.schema.alterTable(constraint.many_collection, (table) => {
100
				if (
101
					columnInfo.data_type !== relatedColumnInfo.data_type &&
102
					columnInfo.data_type === 'int' &&
103
					relatedColumnInfo.data_type === 'int unsigned'
104
				) {
105
					table.specificType(constraint.many_field, 'int unsigned').alter();
106
				}
107

108
				const indexName = getDefaultIndexName('foreign', constraint.many_collection, constraint.many_field);
109

110
				const builder = table
111
					.foreign(constraint.many_field, indexName)
112
					.references(relatedPrimaryKeyField)
113
					.inTable(constraint.one_collection!);
114

115
				// Can't reliably have circular cascade
116
				if (constraint.many_collection !== constraint.one_collection) {
117
					builder.onDelete('SET NULL');
118
				}
119
			});
120
		} catch (err: any) {
121
			logger.warn(
122
				`Couldn't add foreign key constraint for ${constraint.many_collection}.${constraint.many_field}<->${constraint.one_collection}`,
123
			);
124

125
			logger.warn(err);
126
		}
127
	}
128

129
	if (corruptedRelations.length > 0) {
130
		logger.warn(
131
			`Encountered one or more corrupted relationships. Please check the following rows in "directus_relations": ${corruptedRelations.join(
132
				', ',
133
			)}`,
134
		);
135
	}
136
}
137

138
export async function down(knex: Knex): Promise<void> {
139
	const logger = useLogger();
140

141
	const relations = await knex
142
		.select<RelationMeta[]>('many_collection', 'many_field', 'one_collection')
143
		.from('directus_relations');
144

145
	for (const relation of relations) {
146
		if (!relation.one_collection) continue;
147

148
		try {
149
			await knex.schema.alterTable(relation.many_collection, (table) => {
150
				table.dropForeign([relation.many_field]);
151
			});
152
		} catch (err: any) {
153
			logger.warn(
154
				`Couldn't drop foreign key constraint for ${relation.many_collection}.${relation.many_field}<->${relation.one_collection}`,
155
			);
156

157
			logger.warn(err);
158
		}
159
	}
160
}
161