~evanj/cms

ref: b8638dfa72e1e2a3c40350a8c866c86ae8868fd4 cms/internal/s/db/sql/00001.sql -rw-r--r-- 3.1 KiB
b8638dfa — Evan J TMP(c/user/user.go): Adding temp Chief signups. TODO: Remove. 1 year, 1 month 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
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
CREATE TABLE cms_migrate (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	NAME varchar(256) UNIQUE NOT NULL,
  DATE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE cms_user (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	NAME varchar(256) UNIQUE NOT NULL,
	HASH varchar(256) NOT NULL
);

CREATE TABLE cms_space (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	NAME varchar(256) NOT NULL,
	DESCRIPTION varchar(256) NOT NULL
);

CREATE TABLE cms_user_to_space (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	USER_ID INTEGER NOT NULL,
	SPACE_ID INTEGER NOT NULL,
	FOREIGN KEY(USER_ID) REFERENCES cms_user(ID),
	FOREIGN KEY(SPACE_ID) REFERENCES cms_space(ID) ON DELETE CASCADE
);

CREATE TABLE cms_contenttype (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	NAME varchar(256) NOT NULL,
	SPACE_ID INTEGER NOT NULL,
	CONSTRAINT FG FOREIGN KEY(SPACE_ID) REFERENCES cms_space(ID) ON DELETE CASCADE,
	CONSTRAINT UNIQUEPERCONN UNIQUE(SPACE_ID, NAME)
);

CREATE TABLE cms_valuetype (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	VALUE varchar(256) UNIQUE NOT NULL
);

CREATE TABLE cms_contenttype_to_valuetype (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	NAME varchar(256) NOT NULL,
	CONTENTTYPE_ID INTEGER NOT NULL,
	VALUETYPE_ID INTEGER NOT NULL,
	FOREIGN KEY(CONTENTTYPE_ID) REFERENCES cms_contenttype(ID) ON DELETE CASCADE,
	FOREIGN KEY(VALUETYPE_ID) REFERENCES cms_valuetype(ID)
);

CREATE TABLE cms_content (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	CONTENTTYPE_ID INTEGER NOT NULL,
	FOREIGN KEY(CONTENTTYPE_ID) REFERENCES cms_contenttype(ID) ON DELETE CASCADE
);

CREATE TABLE cms_value (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	CONTENT_ID INTEGER NOT NULL,
	CONTENTTYPE_TO_VALUETYPE_ID INTEGER NOT NULL, 
	VALUE_ID INTEGER NOT NULL, -- Should be a foreign key but impossible to make it for two+ tables.
	FOREIGN KEY(CONTENT_ID) REFERENCES cms_content(ID) ON DELETE CASCADE,
	FOREIGN KEY(CONTENTTYPE_TO_VALUETYPE_ID) REFERENCES cms_contenttype_to_valuetype(ID) ON DELETE CASCADE
);

CREATE TABLE cms_value_string_small ( 
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	VALUE VARCHAR(256) NOT NULL
);

CREATE TABLE cms_value_string_big (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	VALUE TEXT NOT NULL
);

CREATE TABLE cms_value_date (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	VALUE DATE NOT NULL
);

-- TODO: Reconsider these ON DELETE CASCADES after this point.

CREATE TABLE cms_value_reference (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	VALUE INTEGER NOT NULL,
	FOREIGN KEY(VALUE) REFERENCES cms_content(ID) ON DELETE CASCADE
);

CREATE TABLE cms_value_reference_list (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT
);

CREATE TABLE cms_value_reference_list_values (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	VALUE_ID INTEGER NOT NULL,
	CONTENT_ID INTEGER NOT NULL,
	FOREIGN KEY(VALUE_ID) REFERENCES cms_value_reference_list(ID) ON DELETE CASCADE,
	FOREIGN KEY(CONTENT_ID) REFERENCES cms_content(ID) ON DELETE CASCADE
);

CREATE TABLE cms_hooks (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	URL varchar(256) NOT NULL,
	SPACE_ID INTEGER NOT NULL,
	FOREIGN KEY(SPACE_ID) REFERENCES cms_space(ID) ON DELETE CASCADE,
	CONSTRAINT UNIQUEPERCONN UNIQUE(SPACE_ID, URL)
);