20260411091000_RemoveTaskHistoryTaskCodeUniqueHotfix.cs
7.64 KB
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
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;
using Rcs.Infrastructure.DB.MsSql;
#nullable disable
namespace Rcs.Infrastructure.Migrations
{
[DbContext(typeof(AppDbContext))]
[Migration("20260411091000_RemoveTaskHistoryTaskCodeUniqueHotfix")]
public class RemoveTaskHistoryTaskCodeUniqueHotfix : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql(
"""
DO $$
DECLARE
fk_name text;
uq_name text;
idx_name text;
BEGIN
IF EXISTS (
SELECT 1
FROM information_schema.tables
WHERE table_schema = current_schema()
AND table_name = 'robot_sub_task_histories'
) THEN
FOR fk_name IN
SELECT con.conname
FROM pg_constraint con
JOIN pg_class rel ON rel.oid = con.conrelid
JOIN pg_namespace nsp ON nsp.oid = rel.relnamespace
JOIN pg_attribute att ON att.attrelid = rel.oid AND att.attnum = ANY(con.conkey)
WHERE con.contype = 'f'
AND nsp.nspname = current_schema()
AND rel.relname = 'robot_sub_task_histories'
AND att.attname = 'task_code'
LOOP
EXECUTE format('ALTER TABLE robot_sub_task_histories DROP CONSTRAINT IF EXISTS %I', fk_name);
END LOOP;
END IF;
IF EXISTS (
SELECT 1
FROM information_schema.tables
WHERE table_schema = current_schema()
AND table_name = 'robot_task_histories'
) THEN
ALTER TABLE robot_task_histories
DROP CONSTRAINT IF EXISTS ak_task_history_task_code;
FOR uq_name IN
SELECT con.conname
FROM pg_constraint con
JOIN pg_class rel ON rel.oid = con.conrelid
JOIN pg_namespace nsp ON nsp.oid = rel.relnamespace
JOIN pg_attribute att ON att.attrelid = rel.oid AND att.attnum = ANY(con.conkey)
WHERE con.contype = 'u'
AND nsp.nspname = current_schema()
AND rel.relname = 'robot_task_histories'
AND att.attname = 'task_code'
LOOP
EXECUTE format('ALTER TABLE robot_task_histories DROP CONSTRAINT IF EXISTS %I', uq_name);
END LOOP;
FOR idx_name IN
SELECT i.indexname
FROM pg_indexes i
WHERE i.schemaname = current_schema()
AND i.tablename = 'robot_task_histories'
AND i.indexdef ILIKE 'CREATE UNIQUE INDEX%'
AND i.indexdef ILIKE '%(task_code)%'
LOOP
EXECUTE format('DROP INDEX IF EXISTS %I', idx_name);
END LOOP;
END IF;
END
$$;
""");
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql(
"""
DO $$
DECLARE
fk_exists boolean;
BEGIN
IF EXISTS (
SELECT 1
FROM information_schema.tables
WHERE table_schema = current_schema()
AND table_name = 'robot_task_histories'
) THEN
IF NOT EXISTS (
SELECT 1
FROM pg_constraint con
JOIN pg_class rel ON rel.oid = con.conrelid
JOIN pg_namespace nsp ON nsp.oid = rel.relnamespace
WHERE con.contype = 'u'
AND nsp.nspname = current_schema()
AND rel.relname = 'robot_task_histories'
AND con.conname = 'ak_task_history_task_code'
) AND NOT EXISTS (
SELECT 1
FROM robot_task_histories
GROUP BY task_code
HAVING COUNT(*) > 1
) THEN
ALTER TABLE robot_task_histories
ADD CONSTRAINT ak_task_history_task_code UNIQUE (task_code);
END IF;
END IF;
IF EXISTS (
SELECT 1
FROM information_schema.tables
WHERE table_schema = current_schema()
AND table_name = 'robot_sub_task_histories'
) AND EXISTS (
SELECT 1
FROM information_schema.tables
WHERE table_schema = current_schema()
AND table_name = 'robot_task_histories'
) THEN
SELECT EXISTS (
SELECT 1
FROM pg_constraint con
JOIN pg_class rel ON rel.oid = con.conrelid
JOIN pg_namespace nsp ON nsp.oid = rel.relnamespace
WHERE con.contype = 'f'
AND nsp.nspname = current_schema()
AND rel.relname = 'robot_sub_task_histories'
AND con.conname = 'fk_sub_task_history_task_code'
) INTO fk_exists;
IF NOT fk_exists
AND EXISTS (
SELECT 1
FROM pg_constraint con
JOIN pg_class rel ON rel.oid = con.conrelid
JOIN pg_namespace nsp ON nsp.oid = rel.relnamespace
WHERE con.contype = 'u'
AND nsp.nspname = current_schema()
AND rel.relname = 'robot_task_histories'
AND con.conname = 'ak_task_history_task_code'
)
AND NOT EXISTS (
SELECT 1
FROM robot_sub_task_histories sth
LEFT JOIN robot_task_histories th ON th.task_code = sth.task_code
WHERE th.task_id IS NULL
) THEN
ALTER TABLE robot_sub_task_histories
ADD CONSTRAINT fk_sub_task_history_task_code
FOREIGN KEY (task_code)
REFERENCES robot_task_histories (task_code)
ON DELETE CASCADE;
END IF;
END IF;
END
$$;
""");
}
}
}