pathfork/init.sql at master · thearchduke/pathfork · GitHub

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
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
drop table if exists tbl_subsection;
drop table if exists tbl_section CASCADE;
drop table if exists tbl_snippet;
drop table if exists tbl_work CASCADE;
drop table if exists tbl_character;
drop table if exists tbl_setting;
drop table if exists tbl_thing;
drop table if exists tbl_user CASCADE;
drop table if exists r_works_characters;
drop table if exists r_sections_characters;
drop table if exists r_works_settings;
drop table if exists r_sections_settings;
drop table if exists r_works_things;
drop table if exists r_sections_things;
drop table if exists r_settings_things;
drop table if exists r_settings_characters;
drop table if exists r_characters_things;

create table tbl_user(
email varchar(256) primary key,
pw varchar(64) not null,
verified BOOLEAN DEFAULT FALSE
);

create table tbl_work(
work_id serial primary key,
title text not null,
blurb text,
user_email text not null,
word_count integer not null default 0,
foreign key (user_email) references tbl_user(email)
	ON DELETE CASCADE
);

create table tbl_section(
section_id serial primary key,
title text not null,
blurb text,
body text,
order integer,
is_snippet boolean default false,
work_id integer not null,
user_email text not null,
word_count integer not null default 0,
foreign key (work_id) references tbl_work(work_id)
	ON DELETE CASCADE,
foreign key (user_email) references tbl_user(email)
	ON DELETE CASCADE
);

/*
create table tbl_subsection(
subsection_id serial primary key,
title text not null,
blurb text,
body text,
section_id integer not null,
user_email text not null,
foreign key (section_id) references tbl_section(section_id),
foreign key (user_email) references tbl_user(email)
);
*/

/*
create table tbl_snippet(
snippet_id serial primary key,
title text not null,
blurb text,
body text,
work_id integer,
user_email text not null,
foreign key (work_id) references tbl_work(work_id)
	ON DELETE CASCADE,
foreign key (user_email) references tbl_user(email)
	ON DELETE CASCADE
);
*/

create table tbl_character(
character_id serial primary key,
name text not null,
blurb text,
body text,
user_email text not null,
foreign key (user_email) references tbl_user(email)
	ON DELETE CASCADE
);

create table r_sections_characters(
section_id serial not null,
character_id integer not null,
PRIMARY KEY (section_id, character_id),
FOREIGN KEY (section_id) references tbl_section(section_id)
	ON DELETE CASCADE,
FOREIGN KEY (character_id) references tbl_character(character_id)
	ON DELETE CASCADE
);

create table r_works_characters(
work_id serial not null,
character_id integer not null,
PRIMARY KEY (work_id, character_id),
FOREIGN KEY (work_id) references tbl_work(work_id)
	ON DELETE CASCADE,
FOREIGN KEY (character_id) references tbl_character(character_id)
	ON DELETE CASCADE
);

create table tbl_setting(
setting_id serial primary key,
name text not null,
blurb text,
body text,
user_email text not null,
foreign key (user_email) references tbl_user(email)
	ON DELETE CASCADE
);

create table r_sections_settings(
section_id serial not null,
setting_id serial not null,
PRIMARY KEY (section_id, setting_id),
FOREIGN KEY (section_id) references tbl_section(section_id)
	ON DELETE CASCADE,
FOREIGN KEY (setting_id) references tbl_setting(setting_id)
	ON DELETE CASCADE
);

create table r_works_settings(
work_id serial not null,
setting_id serial not null,
PRIMARY KEY (work_id, setting_id),
FOREIGN KEY (work_id) references tbl_work(work_id)
	ON DELETE CASCADE,
FOREIGN KEY (setting_id) references tbl_setting(setting_id)
	ON DELETE CASCADE
);

/*
create table tbl_thing(
thing_id serial primary key,
name text not null,
blurb text,
user_email text not null,
foreign key (user_email) references tbl_user(email)
);

create table r_sections_things(
section_id integer not null,
thing_id integer not null,
PRIMARY KEY (section_id, thing_id)
);

create table r_works_things(
work_id integer not null,
thing_id integer not null,
PRIMARY KEY (work_id, thing_id)
);

create table r_characters_things(
character_id integer not null,
thing_id integer not null,
PRIMARY KEY (character_id, thing_id)
);

create table r_settings_things(
setting_id integer not null,
thing_id integer not null,
PRIMARY KEY (setting_id, thing_id)
);
*/

create table r_settings_characters(
setting_id integer not null,
character_id integer not null,
PRIMARY KEY (setting_id, character_id),
FOREIGN KEY (setting_id) references tbl_setting(setting_id)
	ON DELETE CASCADE,
FOREIGN KEY (character_id) references tbl_character(character_id)
	ON DELETE CASCADE
);

create unique index ix_characters_works on r_works_characters (character_id, work_id);
create unique index ix_settings_works on r_works_settings (setting_id, work_id);
create unique index ix_characters_sections on r_sections_characters (character_id, section_id);
create unique index ix_settings_sections on r_sections_settings (setting_id, section_id);
/*
create unique index ix_things_sections on r_sections_things (thing_id, section_id);
create unique index ix_things_works on r_works_things (thing_id, work_id);
*/

create index ix_work_email on tbl_work (user_email);
create index ix_character_email on tbl_character (user_email);
create index ix_setting_email on tbl_setting (user_email);
/*create index ix_thing_email on tbl_thing (user_email);*/


/*
INSERT INTO tbl_user(email, pw) values ('[email protected]', 'password');
INSERT INTO tbl_work(title, user_email) values ('my first novel!', '[email protected]');
INSERT INTO tbl_section(title, user_email, work_id) values ('a title', '[email protected]', 2);
insert into tbl_section(title, work_id, user_email) values ('section 2', 1, '[email protected]');
insert into tbl_section(title, blurb, body, work_id, user_email) values ('section 3', 'In which there is discontent', 'Now is the winter of our discontent', 1, '[email protected]');
insert into tbl_character(name, user_email) values ('cornelius', '[email protected]');
insert into r_works_characters values (1, 1);
*/