db_schema.sql 10.5 KB
Newer Older
1
BEGIN TRANSACTION;
2
CREATE TABLE IF NOT EXISTS `accesslog` (
3
4
5
6
7
8
9
10
11
  `logfile` varchar(255) NOT NULL,
  `vhost` varchar(30) DEFAULT NULL,
  `path` varchar(255) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `interval` INTEGER NOT NULL DEFAULT '0',
  `count` INTEGER NOT NULL DEFAULT '0',
  `volume` bigint(20) NOT NULL DEFAULT '0',
	PRIMARY KEY (logfile, path, timestamp)
);
12
CREATE TABLE IF NOT EXISTS `areas` (
13
14
15
16
17
18
  `area` varchar(20) NOT NULL PRIMARY KEY,
  `abbreviation` varchar(10) NOT NULL,
  `default` INTEGER NOT NULL DEFAULT '0',
  `rank` INTEGER DEFAULT NULL,
  `coordinates` varchar(30) NOT NULL
);
19
CREATE TABLE IF NOT EXISTS `changelog` (
20
21
22
23
24
25
26
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `when` datetime NOT NULL,
  `who` INTEGER NOT NULL,
  `table` varchar(255) NOT NULL,
  `id_key` varchar(64) NOT NULL,
  `id_value` text NOT NULL,
  `field` varchar(255) NOT NULL,
27
  `value_old` text,
28
29
30
  `value_new` text NOT NULL,
  `executed` text NOT NULL
);
31
32
33
34
35
36
37
38
39
40
41
42
CREATE TABLE IF NOT EXISTS `chapters` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `lecture_id` INTEGER NOT NULL,
	`time` INTEGER NOT NULL,
	`text` text NOT NULL,
	`visible` INTEGER NOT NULL DEFAULT 0,
	`deleted` INTEGER NOT NULL DEFAULT 0,
	`time_created` datetime NOT NULL,
	`time_updated` datetime NOT NULL,
	`created_by` INTEGER DEFAULT NULL,
	`submitted_by` varchar(32) DEFAULT NULL
);
43
CREATE TABLE IF NOT EXISTS `courses_data` (
44
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
Julian Rother's avatar
Julian Rother committed
45
  `visible` INTEGER NOT NULL DEFAULT '0',
46
47
  `listed` INTEGER NOT NULL DEFAULT '1',
  `deleted` INTEGER NOT NULL DEFAULT '0',
48
  `title` text NOT NULL DEFAULT '',
Julian Rother's avatar
Julian Rother committed
49
50
51
52
  `short` varchar(32) NOT NULL DEFAULT '',
  `handle` varchar(32) NOT NULL DEFAULT '',
  `organizer` text NOT NULL DEFAULT '',
  `subject` varchar(32) NOT NULL DEFAULT '',
53
54
55
56
  `credits` INTEGER NOT NULL DEFAULT '0',
  `created_by` INTEGER DEFAULT NULL,
  `time_created` datetime NOT NULL,
  `time_updated` datetime NOT NULL,
Julian Rother's avatar
Julian Rother committed
57
58
  `semester` char(6) NOT NULL DEFAULT '',
  `settings` text NOT NULL DEFAULT '',
59
60
  `downloadable` INTEGER NOT NULL DEFAULT '1',
  `embedinvisible` INTEGER NOT NULL DEFAULT '0',
Julian Rother's avatar
Julian Rother committed
61
62
63
  `description` text NOT NULL DEFAULT '',
  `internal` text NOT NULL DEFAULT '',
  `responsible` text NOT NULL DEFAULT '',
64
  `feed_url` text NOT NULL DEFAULT '',
65
  `external` INTEGER NOT NULL DEFAULT 0,
66
67
68
69
  `coursechapters` INTEGER NOT NULL DEFAULT 0,
  `autopublish` INTEGER NOT NULL DEFAULT 0,
  `autovisible` INTEGER NOT NULL DEFAULT 0,
	`profile` varchar(64) NOT NULL DEFAULT 'default'
70
);
71
CREATE TABLE IF NOT EXISTS `filesizes` (
72
73
74
75
76
  `path` varchar(255) NOT NULL PRIMARY KEY,
  `size` bigint(20) NOT NULL DEFAULT '0',
  `duration` float DEFAULT NULL,
  `basename` varchar(255) NOT NULL
);
77
CREATE TABLE IF NOT EXISTS `formats` (
78
79
80
81
82
83
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `description` text NOT NULL,
  `keywords` text NOT NULL,
  `resolution` varchar(16) NOT NULL,
  `aspect` varchar(16) NOT NULL,
  `prio` INTEGER NOT NULL DEFAULT '0',
84
  `player_prio` INTEGER NOT NULL DEFAULT '0',
85
	`mimetype` varchar(32) NOT NULL,
Julian Rother's avatar
Julian Rother committed
86
87
  `options` text,
	`suffix` varchar(32)
88
);
89
CREATE TABLE IF NOT EXISTS `lectures_data` (
90
91
92
93
94
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `course_id` INTEGER NOT NULL,
  `visible` INTEGER NOT NULL DEFAULT '1',
  `timed_release` datetime DEFAULT NULL,
  `use_timed_release` INTEGER NOT NULL DEFAULT '0',
95
  `drehplan` varchar(10) NOT NULL DEFAULT '',
96
  `deleted` INTEGER NOT NULL DEFAULT '0',
97
98
99
100
101
  `title` text NOT NULL DEFAULT '',
  `comment` text NOT NULL DEFAULT '',
  `internal` text NOT NULL DEFAULT '',
  `speaker` text NOT NULL DEFAULT '',
  `place` text NOT NULL DEFAULT '',
102
103
104
105
  `time` datetime NOT NULL,
  `duration` INTEGER NOT NULL DEFAULT '90',
  `time_created` datetime NOT NULL,
  `time_updated` datetime NOT NULL,
106
  `jumplist` text NOT NULL DEFAULT '',
107
  `titlefile` varchar(255) NOT NULL DEFAULT '',
108
  `live` INTEGER NOT NULL DEFAULT 0,
109
110
  `norecording` INTEGER NOT NULL DEFAULT 0,
	`profile` varchar(64)
111
);
112
CREATE TABLE IF NOT EXISTS `places` (
113
114
115
116
117
118
  `place` varchar(20) NOT NULL PRIMARY KEY,
  `name` text NOT NULL,
  `area` varchar(20) NOT NULL,
  `campus_room` varchar(20) NOT NULL,
  `campus_name` varchar(30) NOT NULL
);
119
120
CREATE TABLE IF NOT EXISTS `perm` (
	`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
121
	`deleted` INTEGER NOT NULL DEFAULT '0',
122
123
124
	`course_id` INTEGER,
	`lecture_id` INTEGER,
	`video_id` INTEGER,
125
126
127
	`type` varchar(10),
	`param1` varchar(127),
	`param2` varchar(127),
128
129
130
	`time_created` datetime NOT NULL,
	`time_updated` datetime NOT NULL,
	`created_by` INTEGER DEFAULT NULL
131
);
132
CREATE TABLE IF NOT EXISTS `site_texts` (
133
134
135
136
137
  `key` varchar(64) NOT NULL PRIMARY KEY,
  `value` text NOT NULL,
  `modified_when` datetime NOT NULL,
  `modified_by` text NOT NULL
);
138
139
140
141
CREATE TABLE IF NOT EXISTS `sources` (
  `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `lecture_id` INTEGER NOT NULL,
  `path` text NOT NULL,
Julian Rother's avatar
Julian Rother committed
142
143
144
  `type` text NOT NULL,
  `hash` varchar(32) NOT NULL,
	`time_created` datetime NOT NULL
145
);
146
CREATE TABLE IF NOT EXISTS `log` (
147
	`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
148
	`time` datetime NOT NULL,
Julian Rother's avatar
Julian Rother committed
149
	`date` datetime NOT NULL,
Julian Rother's avatar
Julian Rother committed
150
151
	`source` INTEGER,
	`video` INTEGER
152
);
153
154
155
156
157
158
159
160
CREATE TABLE IF NOT EXISTS `hlslog` (
	`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	`time` datetime NOT NULL,
	`source` INTEGER,
	`lecture` INTEGER,
	`handle` varchar(32),
	`format` varchar(16)
);
161
162
163
164
165
166
167
CREATE TABLE IF NOT EXISTS `logcache` (
	`req` varchar(64),
	`param` varchar(64),
	`trace` varchar(64),
	`date` datetime NOT NULL,
	`value` INTEGER
);
168
169
170
171
172
CREATE TABLE IF NOT EXISTS `profiles` (
	`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `name` varchar(64) NOT NULL,
	`format` INTEGER NOT NULL
);
173
CREATE TABLE IF NOT EXISTS `streams` (
174
175
176
177
178
179
180
  `handle` varchar(32) NOT NULL PRIMARY KEY,
  `active` INTEGER NOT NULL,
  `visible` INTEGER NOT NULL,
  `lecture_id` INTEGER NOT NULL,
  `description` text NOT NULL,
  `poster` text NOT NULL
);
181
CREATE TABLE IF NOT EXISTS `stream_stats` (
182
183
184
185
186
187
188
189
190
191
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `handle` varchar(32) NOT NULL,
  `lecture` INTEGER NOT NULL,
  `time` datetime NOT NULL,
  `rtmp_h264` INTEGER NOT NULL DEFAULT '0',
  `http_webm` INTEGER NOT NULL DEFAULT '0',
  `hls_1080p` INTEGER NOT NULL DEFAULT '0',
  `hls_720p` INTEGER NOT NULL DEFAULT '0',
  `hls_360p` INTEGER NOT NULL DEFAULT '0'
);
Andreas Valder's avatar
Andreas Valder committed
192
193
194
195
196
197
198
199
CREATE TABLE IF NOT EXISTS `import_campus` (
	`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	`course_id` INTEGER NOT NULL,
	`last_checked` datetime NOT NULL,
	`type` varchar(255),
	`url` varchar(2084),
	`changed` INTEGER NOT NULL DEFAULT '0' 
);
200
CREATE TABLE IF NOT EXISTS `users` (
201
202
203
204
205
206
207
208
209
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `name` varchar(32) NOT NULL,
  `realname` text NOT NULL,
  `level` INTEGER NOT NULL DEFAULT '0',
  `fsacc` varchar(32) NOT NULL,
  `last_login` datetime DEFAULT NULL,
  `calendar_key` varchar(40) NOT NULL,
  `rfc6238` varchar(20) NOT NULL
);
210
CREATE TABLE IF NOT EXISTS `videos_data` (
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `lecture_id` INTEGER NOT NULL,
  `visible` INTEGER NOT NULL DEFAULT '1',
  `deleted` INTEGER NOT NULL DEFAULT '0',
  `downloadable` INTEGER NOT NULL DEFAULT '1',
  `title` text NOT NULL,
  `comment` text NOT NULL,
  `internal` text NOT NULL,
  `path` text NOT NULL,
  `file_modified` datetime NOT NULL,
  `time_created` datetime NOT NULL,
  `time_updated` datetime NOT NULL,
  `created_by` INTEGER NOT NULL,
  `file_size` bigINTEGER NOT NULL DEFAULT '-1',
  `video_format` INTEGER NOT NULL,
226
227
  `hash` varchar(32) NOT NULL,
  `source` INTEGER
228
);
229
230
CREATE TABLE IF NOT EXISTS `announcements` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
231
	`extid` varchar(128) UNIQUE,
232
	`text` text NOT NULL DEFAULT '',
233
234
235
	`level` INTEGER NOT NULL DEFAULT 0,
	`visible` INTEGER NOT NULL DEFAULT 0,
	`deleted` INTEGER NOT NULL DEFAULT 0,
236
237
  `time_publish` datetime DEFAULT '',
  `time_expire` datetime DEFAULT '',
238
239
240
241
  `time_created` datetime NOT NULL,
  `time_updated` datetime NOT NULL,
  `created_by` INTEGER NOT NULL
);
242
243
CREATE TABLE IF NOT EXISTS `featured` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
244
	`title` text NOT NULL DEFAULT '',
245
246
	`text` text NOT NULL DEFAULT '',
	`internal` text NOT NULL DEFAULT '',
247
248
	`type` varchar(32) NOT NULL DEFAULT '',
	`param` text NOT NULL DEFAULT '',
Julian Rother's avatar
Julian Rother committed
249
	`param2` text NOT NULL DEFAULT '',
250
	`order` INTEGER DEFAULT NULL,
251
252
253
254
255
256
	`visible` INTEGER NOT NULL DEFAULT 0,
	`deleted` INTEGER NOT NULL DEFAULT 0,
  `time_created` datetime NOT NULL,
  `time_updated` datetime NOT NULL,
  `created_by` INTEGER NOT NULL
);
257
258
259
260
261
262
263
264
CREATE TABLE IF NOT EXISTS `sortlog` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `when` datetime NOT NULL,
  `path` text NOT NULL,
  `lecture_id` INTEGER NOT NULL,
  `video_id` INTEGER NOT NULL
);

265
266
267
268
269
270
271
272
273
274
275
CREATE TABLE IF NOT EXISTS `sorterrorlog_data` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `when` datetime NOT NULL,
  `path` text NOT NULL,
  `course_id` INTEGER NOT NULL,
  `matches` text NOT NULL Default '',
  `deleted` INTEGER NOT NULL DEFAULT '0',
  `time_updated` datetime NOT NULL,
  `time_created` datetime NOT NULL
);

Andreas Valder's avatar
Andreas Valder committed
276
277
278
279
280
281
282
283
284
285
286
CREATE TABLE IF NOT EXISTS `worker` (
`hostname` text NOT NULL PRIMARY KEY,
  `last_ping` datetime NOT NULL
);

CREATE TABLE IF NOT EXISTS `jobs` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `type` text NOT NULL,
  `priority` INTEGER NOT NULL DEFAULT 0,
  `state` text NOT NULL DEFAULT 'ready',
  `time_finished` datetime DEFAULT '',
Andreas Valder's avatar
Andreas Valder committed
287
  `time_scheduled` datetime DEFAULT '',
Andreas Valder's avatar
Andreas Valder committed
288
289
290
291
292
293
294
  `time_created` datetime NOT NULL,
  `last_ping` datetime NOT NULL DEFAULT '',
  `worker` text DEFAULT NULL,
  `data` text NOT NULL DEFAULT '{}',
  `status` text NOT NULL DEFAULT '{}'
);

295
CREATE VIEW IF NOT EXISTS `courses` AS select * from `courses_data` where (not(`courses_data`.`deleted`));
296
297
CREATE VIEW IF NOT EXISTS `lectures` AS select `lectures_data`.* from `lectures_data` join `courses_data` on (`courses_data`.`id` = `course_id`) where (not(`lectures_data`.`deleted` or `courses_data`.`deleted`));
CREATE VIEW IF NOT EXISTS `videos` AS select `videos_data`.* from `videos_data` join `lectures_data` on (`lectures_data`.`id` = `lecture_id`) join `courses_data` on (`courses_data`.`id` = `course_id`) where (not(`videos_data`.`deleted` or `lectures_data`.`deleted` or `courses_data`.`deleted`));
298
CREATE VIEW IF NOT EXISTS `sorterrorlog` AS select * from `sorterrorlog_data` where (not(`sorterrorlog_data`.`deleted`));
299
CREATE TRIGGER IF NOT EXISTS featured_unique_order AFTER INSERT ON featured FOR EACH ROW BEGIN UPDATE featured SET `order` = (SELECT MAX(`order`) FROM featured)+1 WHERE id = NEW.id; END;
300
CREATE TRIGGER IF NOT EXISTS courses_unique_handle BEFORE UPDATE OF handle ON courses_data FOR EACH ROW WHEN NEW.handle IN (SELECT handle FROM courses WHERE NOT deleted) BEGIN SELECT RAISE(ROLLBACK, "Handle bereits in Verwendung"); END;
301
COMMIT;