~evanj/cms

ref: 991b370ac6b6208e5f2fd7dcada177d52be58d9f cms/internal/s/db/sql/00003.sql -rw-r--r-- 1.1 KiB
991b370aEvan M Jones Feat(Big): Added a few things here... 1 year, 2 months 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
32
33
34
35
36
37
38
39
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
);

-- Fix user to org.

ALTER TABLE cms_user ADD ORG_ID INTEGER NOT NULL;

-- NOTE: At this point in time we haven't supported user<->space.

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);

-- Fix space to org.

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 extras.

DROP TABLE cms_user_to_space;