Something went wrong on our end
Select Git revision
-
Andreas Valder authoredAndreas Valder authored
Code owners
Assign users and groups as approvers for specific file changes. Learn more.
db_schema.sql NaN GiB
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS `accesslog` (
`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)
);
CREATE TABLE IF NOT EXISTS `areas` (
`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
);
CREATE TABLE IF NOT EXISTS `changelog` (
`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,
`value_new` text NOT NULL,
`executed` text NOT NULL
);
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
);
CREATE TABLE IF NOT EXISTS `courses_data` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`visible` INTEGER NOT NULL DEFAULT '0',
`listed` INTEGER NOT NULL DEFAULT '1',
`deleted` INTEGER NOT NULL DEFAULT '0',
`title` text NOT NULL DEFAULT '',
`short` varchar(32) NOT NULL DEFAULT '',
`handle` varchar(32) NOT NULL DEFAULT '',
`organizer` text NOT NULL DEFAULT '',
`subject` varchar(32) NOT NULL DEFAULT '',
`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 DEFAULT '',
`settings` text NOT NULL DEFAULT '',
`downloadable` INTEGER NOT NULL DEFAULT '1',
`embedinvisible` INTEGER NOT NULL DEFAULT '0',
`description` text NOT NULL DEFAULT '',
`internal` text NOT NULL DEFAULT '',
`responsible` text NOT NULL DEFAULT '',
`feed_url` text NOT NULL DEFAULT '',
`external` INTEGER NOT NULL DEFAULT 0,
`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'
);
CREATE TABLE IF NOT EXISTS `filesizes` (
`path` varchar(255) NOT NULL PRIMARY KEY,
`size` bigint(20) NOT NULL DEFAULT '0',
`duration` float DEFAULT NULL,
`basename` varchar(255) NOT NULL
);
CREATE TABLE IF NOT EXISTS `formats` (
`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',
`mimetype` varchar(32) NOT NULL,
`options` text
);
CREATE TABLE IF NOT EXISTS `lectures_data` (
`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 DEFAULT '',
`deleted` INTEGER NOT NULL DEFAULT '0',
`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 '',
`time` datetime NOT NULL,
`duration` INTEGER NOT NULL DEFAULT '90',
`time_created` datetime NOT NULL,
`time_updated` datetime NOT NULL,
`jumplist` text NOT NULL DEFAULT '',
`titlefile` varchar(255) NOT NULL DEFAULT '',
`live` INTEGER NOT NULL DEFAULT 0,
`norecording` INTEGER NOT NULL DEFAULT 0,
`profile` varchar(64)
);
CREATE TABLE IF NOT EXISTS `places` (
`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
);
CREATE TABLE IF NOT EXISTS `perm` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`deleted` INTEGER NOT NULL DEFAULT '0',
`course_id` INTEGER,
`lecture_id` INTEGER,
`video_id` INTEGER,
`type` varchar(10),
`param1` varchar(127),
`param2` varchar(127),
`time_created` datetime NOT NULL,
`time_updated` datetime NOT NULL,
`created_by` INTEGER DEFAULT NULL
);
CREATE TABLE IF NOT EXISTS `site_texts` (
`key` varchar(64) NOT NULL PRIMARY KEY,
`value` text NOT NULL,
`modified_when` datetime NOT NULL,
`modified_by` text NOT NULL
);
CREATE TABLE IF NOT EXISTS `sources` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`lecture_id` INTEGER NOT NULL,
`path` text NOT NULL,
`profile` text NOT NULL,
`hash` varchar(32) NOT NULL
);
CREATE TABLE IF NOT EXISTS `log` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`time` datetime NOT NULL,
`date` datetime NOT NULL,
`source` INTEGER,
`video` INTEGER
);
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)
);
CREATE TABLE IF NOT EXISTS `logcache` (
`req` varchar(64),
`param` varchar(64),
`trace` varchar(64),
`date` datetime NOT NULL,
`value` INTEGER
);
CREATE TABLE IF NOT EXISTS `profiles` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`name` varchar(64) NOT NULL,
`format` INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS `streams` (
`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
);
CREATE TABLE IF NOT EXISTS `stream_stats` (
`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'
);
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'
);
CREATE TABLE IF NOT EXISTS `users` (
`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
);
CREATE TABLE IF NOT EXISTS `videos_data` (
`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,
`source` INTEGER
);
CREATE TABLE IF NOT EXISTS `announcements` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`extid` varchar(128) UNIQUE,
`text` text NOT NULL DEFAULT '',
`level` INTEGER NOT NULL DEFAULT 0,
`visible` INTEGER NOT NULL DEFAULT 0,
`deleted` INTEGER NOT NULL DEFAULT 0,
`time_publish` datetime DEFAULT '',
`time_expire` datetime DEFAULT '',
`time_created` datetime NOT NULL,
`time_updated` datetime NOT NULL,
`created_by` INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS `featured` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`title` text NOT NULL DEFAULT '',
`text` text NOT NULL DEFAULT '',
`internal` text NOT NULL DEFAULT '',
`type` varchar(32) NOT NULL DEFAULT '',
`param` text NOT NULL DEFAULT '',
`param2` text NOT NULL DEFAULT '',
`order` INTEGER DEFAULT 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 NOT NULL
);
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
);
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
);
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 '',
`time_scheduled` datetime DEFAULT '',
`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 '{}'
);
CREATE VIEW IF NOT EXISTS `courses` AS select * from `courses_data` where (not(`courses_data`.`deleted`));
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`));
CREATE VIEW IF NOT EXISTS `sorterrorlog` AS select * from `sorterrorlog_data` where (not(`sorterrorlog_data`.`deleted`));
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;
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;
COMMIT;