db_schema.sql 5.54 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
27
28
29
30
`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,
  `value_old` text NOT NULL,
  `value_new` text NOT NULL,
  `executed` text NOT NULL
);
31
CREATE TABLE IF NOT EXISTS `courses_data` (
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `visible` INTEGER NOT NULL,
  `listed` INTEGER NOT NULL DEFAULT '1',
  `deleted` INTEGER NOT NULL DEFAULT '0',
  `title` text NOT NULL,
  `short` varchar(32) NOT NULL,
  `handle` varchar(32) NOT NULL,
  `organizer` text NOT NULL,
  `subject` varchar(32) NOT NULL,
  `credits` INTEGER NOT NULL DEFAULT '0',
  `created_by` INTEGER DEFAULT NULL,
  `time_created` datetime NOT NULL,
  `time_updated` datetime NOT NULL,
  `semester` char(6) NOT NULL,
  `settings` text NOT NULL,
  `downloadable` INTEGER NOT NULL DEFAULT '1',
  `embedinvisible` INTEGER NOT NULL DEFAULT '0',
  `description` text NOT NULL,
  `internal` text NOT NULL,
  `responsible` text NOT NULL,
  `feed_url` text NOT NULL
);
54
CREATE TABLE IF NOT EXISTS `filesizes` (
55
56
57
58
59
  `path` varchar(255) NOT NULL PRIMARY KEY,
  `size` bigint(20) NOT NULL DEFAULT '0',
  `duration` float DEFAULT NULL,
  `basename` varchar(255) NOT NULL
);
60
CREATE TABLE IF NOT EXISTS `formats` (
61
62
63
64
65
66
67
68
`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',
  `player_prio` INTEGER NOT NULL DEFAULT '0'
);
69
CREATE TABLE IF NOT EXISTS `lectures_data` (
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
`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',
  `drehplan` varchar(10) NOT NULL,
  `deleted` INTEGER NOT NULL DEFAULT '0',
  `title` text NOT NULL,
  `comment` text NOT NULL,
  `internal` text NOT NULL,
  `speaker` text NOT NULL,
  `place` text NOT NULL,
  `time` datetime NOT NULL,
  `duration` INTEGER NOT NULL DEFAULT '90',
  `time_created` datetime NOT NULL,
  `time_updated` datetime NOT NULL,
  `jumplist` text NOT NULL,
  `titlefile` varchar(255) NOT NULL
);
89
CREATE TABLE IF NOT EXISTS `places` (
90
91
92
93
94
95
  `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
);
96
97
98
99
100
101
102
103
104
CREATE TABLE IF NOT EXISTS `auth` (
	`auth_id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	`course_id` INTEGER,
	`lecture_id` INTEGER,
	`video_id` INTEGER,
	`type` varchar(10),
	`auth_user` varchar(127),
	`auth_passwd` varchar(127)
);
105
CREATE TABLE IF NOT EXISTS `site_texts` (
106
107
108
109
110
  `key` varchar(64) NOT NULL PRIMARY KEY,
  `value` text NOT NULL,
  `modified_when` datetime NOT NULL,
  `modified_by` text NOT NULL
);
111
112
113
114
115
116
CREATE TABLE IF NOT EXISTS `log` (
	`ip` varchar(64),
	`id` varchar(64),
	`time` datetime NOT NULL,
	`object` varchar(10),
	`obj_id` INTEGER,
117
	`path` varchar(255) NOT NULL
118
);
119
CREATE TABLE IF NOT EXISTS `streams` (
120
121
122
123
124
125
126
  `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
);
127
CREATE TABLE IF NOT EXISTS `stream_stats` (
128
129
130
131
132
133
134
135
136
137
`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'
);
138
CREATE TABLE IF NOT EXISTS `users` (
139
140
141
142
143
144
145
146
147
`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
);
148
CREATE TABLE IF NOT EXISTS `videos_data` (
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
`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,
  `hash` varchar(32) NOT NULL
);
166
167
168
CREATE VIEW IF NOT EXISTS `courses` AS select * from `courses_data` where (not(`courses_data`.`deleted`));
CREATE VIEW IF NOT EXISTS `lectures` AS select * from `lectures_data` where (not(`lectures_data`.`deleted`));
CREATE VIEW IF NOT EXISTS `videos` AS select * from `videos_data` where (not(`videos_data`.`deleted`));
169
COMMIT;