directus

1
import { createInspector } from '@directus/schema';
2
import type { Knex } from 'knex';
3
import { useLogger } from '../../logger.js';
4

5
/**
6
 * Things to keep in mind:
7
 *
8
 * - Can't have circular constraints (field -> parent_field)
9
 * - Can't have two constraints from/to the same table (user_created/user_modified -> users)
10
 *
11
 * The following updates are all the times we can rely on the DB to do the cascade. The rest will
12
 * have to be handled in the API. I don't make the rules.
13
 */
14

15
const updates = [
16
	{
17
		table: 'directus_files',
18
		constraints: [
19
			{
20
				column: 'folder',
21
				references: 'directus_folders.id',
22
				on_delete: 'SET NULL',
23
			},
24
		],
25
	},
26
	{
27
		table: 'directus_permissions',
28
		constraints: [
29
			{
30
				column: 'role',
31
				references: 'directus_roles.id',
32
				on_delete: 'CASCADE',
33
			},
34
		],
35
	},
36
	{
37
		table: 'directus_presets',
38
		constraints: [
39
			{
40
				column: 'user',
41
				references: 'directus_users.id',
42
				on_delete: 'CASCADE',
43
			},
44
			{
45
				column: 'role',
46
				references: 'directus_roles.id',
47
				on_delete: 'CASCADE',
48
			},
49
		],
50
	},
51
	{
52
		table: 'directus_revisions',
53
		constraints: [
54
			{
55
				column: 'activity',
56
				references: 'directus_activity.id',
57
				on_delete: 'CASCADE',
58
			},
59
		],
60
	},
61
	{
62
		table: 'directus_sessions',
63
		constraints: [
64
			{
65
				column: 'user',
66
				references: 'directus_users.id',
67
				on_delete: 'CASCADE',
68
			},
69
		],
70
	},
71
	{
72
		table: 'directus_users',
73
		constraints: [
74
			{
75
				column: 'role',
76
				references: 'directus_roles.id',
77
				on_delete: 'SET NULL',
78
			},
79
		],
80
	},
81
];
82

83
export async function up(knex: Knex): Promise<void> {
84
	const logger = useLogger();
85

86
	const inspector = createInspector(knex);
87

88
	const foreignKeys = await inspector.foreignKeys();
89

90
	for (const update of updates) {
91
		for (const constraint of update.constraints) {
92
			const existingForeignKey = foreignKeys.find(
93
				(fk: any) =>
94
					fk.table === update.table &&
95
					fk.column === constraint.column &&
96
					fk.foreign_key_table === constraint.references.split('.')[0] &&
97
					fk.foreign_key_column === constraint.references.split('.')[1],
98
			);
99

100
			try {
101
				await knex.schema.alterTable(update.table, (table) => {
102
					table.dropForeign([constraint.column], existingForeignKey?.constraint_name || undefined);
103
				});
104
			} catch (err: any) {
105
				logger.warn(`Couldn't drop foreign key ${update.table}.${constraint.column}->${constraint.references}`);
106
				logger.warn(err);
107
			}
108

109
			/**
110
			 * MySQL won't delete the index when you drop the foreign key constraint. Gotta make
111
			 * sure to clean those up as well
112
			 */
113
			if (knex.client.constructor.name === 'Client_MySQL') {
114
				try {
115
					await knex.schema.alterTable(update.table, (table) => {
116
						// Knex uses a default convention for index names: `table_column_type`
117
						table.dropIndex([constraint.column], `${update.table}_${constraint.column}_foreign`);
118
					});
119
				} catch (err: any) {
120
					logger.warn(
121
						`Couldn't clean up index for foreign key ${update.table}.${constraint.column}->${constraint.references}`,
122
					);
123

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

128
			try {
129
				await knex.schema.alterTable(update.table, (table) => {
130
					table.foreign(constraint.column).references(constraint.references).onDelete(constraint.on_delete);
131
				});
132
			} catch (err: any) {
133
				logger.warn(`Couldn't add foreign key to ${update.table}.${constraint.column}->${constraint.references}`);
134
				logger.warn(err);
135
			}
136
		}
137
	}
138
}
139

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

143
	for (const update of updates) {
144
		for (const constraint of update.constraints) {
145
			try {
146
				await knex.schema.alterTable(update.table, (table) => {
147
					table.dropForeign([constraint.column]);
148
				});
149
			} catch (err: any) {
150
				logger.warn(`Couldn't drop foreign key ${update.table}.${constraint.column}->${constraint.references}`);
151
				logger.warn(err);
152
			}
153

154
			/**
155
			 * MySQL won't delete the index when you drop the foreign key constraint. Gotta make
156
			 * sure to clean those up as well
157
			 */
158
			if (knex.client.constructor.name === 'Client_MySQL') {
159
				try {
160
					await knex.schema.alterTable(update.table, (table) => {
161
						// Knex uses a default convention for index names: `table_column_type`
162
						table.dropIndex([constraint.column], `${update.table}_${constraint.column}_foreign`);
163
					});
164
				} catch (err: any) {
165
					logger.warn(
166
						`Couldn't clean up index for foreign key ${update.table}.${constraint.column}->${constraint.references}`,
167
					);
168

169
					logger.warn(err);
170
				}
171
			}
172

173
			try {
174
				await knex.schema.alterTable(update.table, (table) => {
175
					table.foreign(constraint.column).references(constraint.references);
176
				});
177
			} catch (err: any) {
178
				logger.warn(`Couldn't add foreign key to ${update.table}.${constraint.column}->${constraint.references}`);
179
				logger.warn(err);
180
			}
181
		}
182
	}
183
}
184