-
Notifications
You must be signed in to change notification settings - Fork 24
/
090-cleanup.sql
59 lines (50 loc) · 3.19 KB
/
090-cleanup.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
BEGIN transaction;
-- make datastore reference fields VARCHAR from CHAR, remove whitespace
DROP VIEW webknossos.jobs_;
ALTER TABLE webknossos.jobs DROP CONSTRAINT dataStore_ref;
ALTER TABLE webknossos.jobs RENAME COLUMN _dataStore to _dataStoreOLD;
ALTER TABLE webknossos.jobs ADD COLUMN _dataStore VARCHAR(256);
UPDATE webknossos.jobs SET _dataStore = TRIM(_dataStoreOLD);
ALTER TABLE webknossos.jobs ALTER COLUMN _dataStore SET NOT NULL;
ALTER TABLE webknossos.jobs DROP COLUMN _dataStoreOLD;
ALTER TABLE webknossos.jobs ADD CONSTRAINT dataStore_ref FOREIGN KEY(_dataStore) REFERENCES webknossos.dataStores(name) DEFERRABLE;
CREATE VIEW webknossos.jobs_ AS SELECT * FROM webknossos.jobs WHERE NOT isDeleted;
DROP VIEW webknossos.dataSets_;
ALTER TABLE webknossos.dataSets DROP CONSTRAINT dataStore_ref;
ALTER TABLE webknossos.dataSets RENAME COLUMN _dataStore to _dataStoreOLD;
ALTER TABLE webknossos.dataSets ADD COLUMN _dataStore VARCHAR(256);
UPDATE webknossos.dataSets SET _dataStore = TRIM(_dataStoreOLD);
ALTER TABLE webknossos.dataSets ALTER COLUMN _dataStore SET NOT NULL;
ALTER TABLE webknossos.dataSets DROP COLUMN _dataStoreOLD;
ALTER TABLE webknossos.dataSets ADD CONSTRAINT dataStore_ref FOREIGN KEY(_dataStore) REFERENCES webknossos.dataStores(name) DEFERRABLE;
CREATE VIEW webknossos.dataSets_ AS SELECT * FROM webknossos.dataSets WHERE NOT isDeleted;
DROP VIEW webknossos.workers_;
ALTER TABLE webknossos.workers RENAME COLUMN _dataStore to _dataStoreOLD;
ALTER TABLE webknossos.workers ADD COLUMN _dataStore VARCHAR(256);
UPDATE webknossos.workers SET _dataStore = TRIM(_dataStoreOLD);
ALTER TABLE webknossos.workers ALTER COLUMN _dataStore SET NOT NULL;
ALTER TABLE webknossos.workers DROP COLUMN _dataStoreOLD;
ALTER TABLE webknossos.workers ADD CONSTRAINT dataStore_ref FOREIGN KEY(_dataStore) REFERENCES webknossos.dataStores(name) DEFERRABLE;
CREATE VIEW webknossos.workers_ AS SELECT * FROM webknossos.workers WHERE NOT isDeleted;
-- drop objectid defaults
ALTER TABLE webknossos.annotations ALTER COLUMN _id DROP DEFAULT;
ALTER TABLE webknossos.meshes ALTER COLUMN _id DROP DEFAULT;
ALTER TABLE webknossos.publications ALTER COLUMN _id DROP DEFAULT;
ALTER TABLE webknossos.dataSets ALTER COLUMN _id DROP DEFAULT;
ALTER TABLE webknossos.projects ALTER COLUMN _id DROP DEFAULT;
ALTER TABLE webknossos.scripts ALTER COLUMN _id DROP DEFAULT;
ALTER TABLE webknossos.taskTypes ALTER COLUMN _id DROP DEFAULT;
ALTER TABLE webknossos.tasks ALTER COLUMN _id DROP DEFAULT;
ALTER TABLE webknossos.teams ALTER COLUMN _id DROP DEFAULT;
ALTER TABLE webknossos.timespans ALTER COLUMN _id DROP DEFAULT;
ALTER TABLE webknossos.organizations ALTER COLUMN _id DROP DEFAULT;
ALTER TABLE webknossos.users ALTER COLUMN _id DROP DEFAULT;
ALTER TABLE webknossos.multiUsers ALTER COLUMN _id DROP DEFAULT;
ALTER TABLE webknossos.tokens ALTER COLUMN _id DROP DEFAULT;
ALTER TABLE webknossos.workers ALTER COLUMN _id DROP DEFAULT;
ALTER TABLE webknossos.jobs ALTER COLUMN _id DROP DEFAULT;
ALTER TABLE webknossos.invites ALTER COLUMN _id DROP DEFAULT;
ALTER TABLE webknossos.annotation_privateLinks ALTER COLUMN _id DROP DEFAULT;
ALTER TABLE webknossos.shortLinks ALTER COLUMN _id DROP DEFAULT;
UPDATE webknossos.releaseInformation SET schemaVersion = 90;
COMMIT;