-
Notifications
You must be signed in to change notification settings - Fork 23
/
120-remove-old-organization-id.sql
186 lines (152 loc) · 10.1 KB
/
120-remove-old-organization-id.sql
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
START TRANSACTION;
do $$ begin ASSERT (select schemaVersion from webknossos.releaseInformation) = 119, 'Previous schema version mismatch'; end; $$ LANGUAGE plpgsql;
-- Drop views
DROP VIEW webknossos.userInfos;
DROP VIEW webknossos.datasets_;
DROP VIEW webknossos.projects_;
DROP VIEW webknossos.taskTypes_;
DROP VIEW webknossos.teams_;
DROP VIEW webknossos.organizations_;
DROP VIEW webknossos.organizationTeams;
DROP VIEW webknossos.users_;
DROP VIEW webknossos.invites_;
DROP VIEW webknossos.credentials_;
DROP VIEW webknossos.aiModels_;
DROP VIEW webknossos.aiInferences_;
-- Drop Foreign Key Constraints
ALTER TABLE webknossos.datasets DROP CONSTRAINT organization_ref;
ALTER TABLE webknossos.teams DROP CONSTRAINT organization_ref;
ALTER TABLE webknossos.users DROP CONSTRAINT organization_ref;
ALTER TABLE webknossos.experienceDomains DROP CONSTRAINT organization_ref;
ALTER TABLE webknossos.voxelytics_runs DROP CONSTRAINT voxelytics_runs__organization_fkey;
ALTER TABLE webknossos.voxelytics_runs DROP CONSTRAINT voxelytics_runs__organization_workflow_hash_fkey;
ALTER TABLE webknossos.voxelytics_workflows DROP CONSTRAINT voxelytics_workflows__organization_fkey;
ALTER TABLE webknossos.aiModels DROP CONSTRAINT aimodels__organization_fkey;
ALTER TABLE webknossos.aiInferences DROP CONSTRAINT aiinferences__organization_fkey;
-- Update dataset table
ALTER TABLE webknossos.datasets ALTER COLUMN _organization TYPE VARCHAR(256);
UPDATE webknossos.datasets SET _organization = (SELECT name FROM webknossos.organizations WHERE _id = _organization);
-- Update project table
ALTER TABLE webknossos.projects ALTER COLUMN _organization TYPE VARCHAR(256);
UPDATE webknossos.projects SET _organization = (SELECT name FROM webknossos.organizations WHERE _id = _organization);
-- Update taskType table
ALTER TABLE webknossos.taskTypes ALTER COLUMN _organization TYPE VARCHAR(256);
UPDATE webknossos.taskTypes SET _organization = (SELECT name FROM webknossos.organizations WHERE _id = _organization);
-- Update experienceDomain table
ALTER TABLE webknossos.experienceDomains ALTER COLUMN _organization TYPE VARCHAR(256);
UPDATE webknossos.experienceDomains SET _organization = (SELECT name FROM webknossos.organizations WHERE _id = _organization);
-- Update team table
ALTER TABLE webknossos.teams ALTER COLUMN _organization TYPE VARCHAR(256);
UPDATE webknossos.teams SET _organization = (SELECT name FROM webknossos.organizations WHERE _id = _organization);
-- Update organization_usedStorage table (has no view)
ALTER TABLE webknossos.organization_usedStorage ALTER COLUMN _organization TYPE VARCHAR(256);
UPDATE webknossos.organization_usedStorage SET _organization = (SELECT name FROM webknossos.organizations WHERE _id = _organization);
-- Update user table
ALTER TABLE webknossos.users ALTER COLUMN _organization TYPE VARCHAR(256);
UPDATE webknossos.users SET _organization = (SELECT name FROM webknossos.organizations WHERE _id = _organization);
-- update invite table
ALTER TABLE webknossos.invites ALTER COLUMN _organization TYPE VARCHAR(256);
UPDATE webknossos.invites SET _organization = (SELECT name FROM webknossos.organizations WHERE _id = _organization);
-- update credential table
ALTER TABLE webknossos.credentials ALTER COLUMN _organization TYPE VARCHAR(256);
UPDATE webknossos.credentials SET _organization = (SELECT name FROM webknossos.organizations WHERE _id = _organization);
-- update aiModel table
ALTER TABLE webknossos.aiModels ALTER COLUMN _organization TYPE VARCHAR(256);
UPDATE webknossos.aiModels SET _organization = (SELECT name FROM webknossos.organizations WHERE _id = _organization);
-- update aiInference table
ALTER TABLE webknossos.aiInferences ALTER COLUMN _organization TYPE VARCHAR(256);
UPDATE webknossos.aiInferences SET _organization = (SELECT name FROM webknossos.organizations WHERE _id = _organization);
-- update voxelytics_run table
ALTER TABLE webknossos.voxelytics_runs ALTER COLUMN _organization TYPE VARCHAR(256);
UPDATE webknossos.voxelytics_runs SET _organization = (SELECT name FROM webknossos.organizations WHERE _id = _organization);
-- update voxelytics_workflows
ALTER TABLE webknossos.voxelytics_workflows ALTER COLUMN _organization TYPE VARCHAR(256);
UPDATE webknossos.voxelytics_workflows SET _organization = (SELECT name FROM webknossos.organizations WHERE _id = _organization);
-- update analyticsEvents local event only first
ALTER TABLE webknossos.analyticsEvents ALTER COLUMN _organization TYPE VARCHAR(256);
UPDATE webknossos.analyticsEvents
SET _organization = (
SELECT o.name
FROM webknossos.organizations o
WHERE o._id = webknossos.analyticsEvents._organization
)
-- preventing the update if the organization does not exist for this event.
WHERE EXISTS (
SELECT 1
FROM webknossos.organizations
WHERE _id = webknossos.analyticsEvents._organization
);
-- join events
UPDATE webknossos.analyticsEvents
SET eventProperties = CASE
WHEN EXISTS (SELECT 1 FROM webknossos.organizations WHERE _id = eventProperties->>'joined_organization_id') THEN
(SELECT jsonb_build_object('joined_organization_id', name) FROM webknossos.organizations WHERE _id = eventProperties->>'joined_organization_id')
ELSE eventProperties
END
WHERE eventType = 'join_organization';
UPDATE webknossos.analyticsEvents
SET eventProperties = CASE
WHEN (SELECT True FROM webknossos.organizations WHERE _id = eventProperties->>'dataset_organization_id') THEN
(
SELECT jsonb_set(
eventProperties::jsonb,
'{dataset_organization_id}',
to_jsonb(name)
)
FROM webknossos.organizations
WHERE _id = eventProperties->>'dataset_organization_id'
)
ELSE eventProperties
END
WHERE eventType = 'open_dataset' or eventType = 'upload_dataset';
-- update organization table
ALTER TABLE webknossos.organizations RENAME COLUMN _id TO _id_old;
ALTER TABLE webknossos.organizations RENAME COLUMN name TO _id;
ALTER TABLE webknossos.organizations RENAME COLUMN displayName TO name;
-- reusing the already existing index during new index creation
-- refer to https://gist.github.com/scaryguy/6269293?permalink_comment_id=4229697#gistcomment-4229697
ALTER TABLE webknossos.organizations RENAME CONSTRAINT organizations_pkey TO organizations_pkeyold;
CREATE UNIQUE INDEX organizations_pkey ON webknossos.organizations (_id);
ALTER TABLE webknossos.organizations DROP CONSTRAINT organizations_pkeyold;
ALTER TABLE webknossos.organizations ADD PRIMARY KEY USING INDEX organizations_pkey;
ALTER TABLE webknossos.organizations ALTER COLUMN _id_old TYPE CHAR(24);
ALTER TABLE webknossos.organizations ALTER COLUMN _id_old SET DEFAULT NULL;
ALTER TABLE webknossos.organizations ALTER COLUMN _id_old DROP NOT NULL;
ALTER TABLE webknossos.organizations ALTER COLUMN _id TYPE VARCHAR(256);
ALTER TABLE webknossos.organizations ADD CONSTRAINT validOrganizationId CHECK (_id ~* '^[A-Za-z0-9\-_. ]+$');
-- Drop old unique constraint which is no longer needed as the orga name (now id) is the primary key.
ALTER TABLE webknossos.organizations DROP CONSTRAINT organizations_name_key;
-- Re-add Foreign Key Constraints
ALTER TABLE webknossos.datasets ADD CONSTRAINT organization_ref FOREIGN KEY(_organization) REFERENCES webknossos.organizations(_id) DEFERRABLE;
ALTER TABLE webknossos.teams ADD CONSTRAINT organization_ref FOREIGN KEY(_organization) REFERENCES webknossos.organizations(_id) DEFERRABLE;
ALTER TABLE webknossos.users ADD CONSTRAINT organization_ref FOREIGN KEY(_organization) REFERENCES webknossos.organizations(_id) DEFERRABLE;
ALTER TABLE webknossos.experienceDomains ADD CONSTRAINT organization_ref FOREIGN KEY(_organization) REFERENCES webknossos.organizations(_id) DEFERRABLE;
ALTER TABLE webknossos.voxelytics_runs
ADD CONSTRAINT organization_ref FOREIGN KEY (_organization) REFERENCES webknossos.organizations(_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE,
ADD CONSTRAINT voxelytics_runs__organization_workflow_hash_fkey FOREIGN KEY (_organization, workflow_hash) REFERENCES webknossos.voxelytics_workflows(_organization, hash) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE;
ALTER TABLE webknossos.voxelytics_workflows ADD CONSTRAINT organization_ref FOREIGN KEY (_organization) REFERENCES webknossos.organizations(_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE;
ALTER TABLE webknossos.aiModels ADD CONSTRAINT organization_ref FOREIGN KEY (_organization) REFERENCES webknossos.organizations(_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE;
ALTER TABLE webknossos.aiInferences ADD CONSTRAINT organization_ref FOREIGN KEY (_organization) REFERENCES webknossos.organizations(_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE;
-- Recreate views
CREATE VIEW webknossos.datasets_ AS SELECT * FROM webknossos.datasets WHERE NOT isDeleted;
CREATE VIEW webknossos.projects_ AS SELECT * FROM webknossos.projects WHERE NOT isDeleted;
CREATE VIEW webknossos.taskTypes_ AS SELECT * FROM webknossos.taskTypes WHERE NOT isDeleted;
CREATE VIEW webknossos.teams_ AS SELECT * FROM webknossos.teams WHERE NOT isDeleted;
CREATE VIEW webknossos.organizations_ AS SELECT * FROM webknossos.organizations WHERE NOT isDeleted;
CREATE VIEW webknossos.organizationTeams AS SELECT * FROM webknossos.teams WHERE isOrganizationTeam AND NOT isDeleted;
CREATE VIEW webknossos.users_ AS SELECT * FROM webknossos.users WHERE NOT isDeleted;
CREATE VIEW webknossos.invites_ AS SELECT * FROM webknossos.invites WHERE NOT isDeleted;
CREATE VIEW webknossos.credentials_ AS SELECT * FROM webknossos.credentials WHERE NOT isDeleted;
CREATE VIEW webknossos.aiModels_ AS SELECT * FROM webknossos.aiModels WHERE NOT isDeleted;
CREATE VIEW webknossos.aiInferences_ AS SELECT * FROM webknossos.aiInferences WHERE NOT isDeleted;
CREATE VIEW webknossos.userInfos AS
SELECT
u._id AS _user, m.email, u.firstName, u.lastname, o.name AS organization_name,
u.isDeactivated, u.isDatasetManager, u.isAdmin, m.isSuperUser,
u._organization, o._id AS organization_id, u.created AS user_created,
m.created AS multiuser_created, u._multiUser, m._lastLoggedInIdentity, u.lastActivity, m.isEmailVerified
FROM webknossos.users_ u
JOIN webknossos.organizations_ o ON u._organization = o._id
JOIN webknossos.multiUsers_ m on u._multiUser = m._id;
UPDATE webknossos.releaseInformation SET schemaVersion = 120;
COMMIT TRANSACTION;