db_schema.sql

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 NOT NULL,
  `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 ''
);
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