~evanj/cms

ref: 4e6befbb576c2e14bcbb5b05e127ae40c9c6073e cms/internal/s/db/sql/00003.sql -rw-r--r-- 987 bytes
4e6befbbEvan J Fix(hook service): Hook service was broke for list of hooks. Encapsulate 1 year, 22 days ago
                                                                                
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
CREATE TABLE cms_org (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	BOGUS_USER_ID INTEGER NOT NULL,
	CONSTRAINT BOGUS_USER_ID_FK FOREIGN KEY(BOGUS_USER_ID) REFERENCES cms_user(ID) ON DELETE CASCADE
);

ALTER TABLE cms_user ADD ORG_ID INTEGER NOT NULL;

INSERT INTO cms_org (BOGUS_USER_ID)
SELECT cms_user.ID FROM cms_user;

UPDATE cms_user 
JOIN cms_org ON cms_user.ID=cms_org.BOGUS_USER_ID
SET cms_user.ORG_ID=cms_org.ID;

ALTER TABLE cms_org DROP FOREIGN KEY BOGUS_USER_ID_FK;
ALTER TABLE cms_org DROP COLUMN BOGUS_USER_ID;

ALTER TABLE cms_user ADD CONSTRAINT CMS_USER_ORG_ID_FK FOREIGN KEY(ORG_ID) REFERENCES cms_org(ID);

ALTER TABLE cms_space ADD ORG_ID INTEGER NOT NULL;

UPDATE cms_space
JOIN cms_user_to_space ON SPACE_ID=cms_space.ID 
JOIN cms_user ON USER_ID=cms_user.ID
JOIN cms_org ON cms_user.ORG_ID=cms_org.ID
SET cms_space.ORG_ID=cms_org.ID;

ALTER TABLE cms_space ADD CONSTRAINT CMS_SPACE_ORG_ID_FK FOREIGN KEY(ORG_ID) REFERENCES cms_org(ID);

DROP TABLE cms_user_to_space;