Campus-Backend
Campus-Backend copied to clipboard
Published
20 hours ago •
TUM-Dev
TUM-Dev
Adds the missing dishrating-FKs
Open questions/TODOs:
- [ ] how should the remaining tables be joined?
- [ ] Should the file uploads log the existance of files into the
filestable? - [ ] Adapt the models to support this
:eyes: Found the following differences in the sql schema:
Needed get from local to auto migration state
-- Modify "" schema
ALTER DATABASE COLLATE utf8mb4_unicode_ci;
-- Create "mensaprices" table
CREATE TABLE `mensaprices` (
`price` bigint NOT NULL AUTO_INCREMENT,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`person` text NOT NULL,
`type` text NOT NULL,
`typeLong` text NOT NULL,
`typeNumber` int NOT NULL,
`value` decimal(10) NOT NULL,
PRIMARY KEY (`price`)
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Modify "dish_name_tag_options" table
ALTER TABLE `dish_name_tag_options` AUTO_INCREMENT 69;
-- Create "alarm_ban" table
CREATE TABLE `alarm_ban` (
`ban` bigint NOT NULL AUTO_INCREMENT,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`ip` binary(16) NOT NULL,
PRIMARY KEY (`ban`),
UNIQUE INDEX `ip` (`ip`)
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Modify "feedback" table
ALTER TABLE `feedback` MODIFY COLUMN `email_id` text NULL, MODIFY COLUMN `receiver` text NULL, MODIFY COLUMN `reply_to_email` text NULL, MODIFY COLUMN `reply_to_name` text NULL, MODIFY COLUMN `feedback` text NULL, MODIFY COLUMN `os_version` text NULL, MODIFY COLUMN `app_version` text NULL;
-- Modify "excluded_dish_name_tag_options" table
ALTER TABLE `excluded_dish_name_tag_options` AUTO_INCREMENT 9;
-- Create "wifi_measurement" table
CREATE TABLE `wifi_measurement` (
`id` bigint NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
`SSID` varchar(32) NOT NULL,
`BSSID` varchar(64) NOT NULL,
`dBm` int NULL,
`accuracyInMeters` float NOT NULL,
`latitude` double NOT NULL,
`longitude` double NOT NULL,
PRIMARY KEY (`id`)
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Create "sessions" table
CREATE TABLE `sessions` (
`session` varchar(255) NOT NULL,
`access` int unsigned NULL,
`data` text NULL,
PRIMARY KEY (`session`),
UNIQUE INDEX `session` (`session`)
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Create "published_exam_results" table
CREATE TABLE `published_exam_results` (
`date` datetime(3) NULL,
`exam_id` varchar(191) NOT NULL,
`lecture_title` longtext NULL,
`lecture_type` longtext NULL,
`lecture_sem` longtext NULL,
`published` bool NULL,
PRIMARY KEY (`exam_id`)
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Create "openinghours" table
CREATE TABLE `openinghours` (
`id` bigint NOT NULL AUTO_INCREMENT,
`category` varchar(20) NOT NULL,
`name` varchar(60) NOT NULL,
`address` varchar(140) NOT NULL,
`room` varchar(140) NULL,
`transport_station` varchar(150) NULL,
`opening_hours` varchar(300) NULL,
`infos` varchar(500) NULL,
`url` varchar(300) NOT NULL,
`language` varchar(2) NULL DEFAULT "de",
`reference_id` int NULL DEFAULT -1,
PRIMARY KEY (`id`)
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Create "migrations" table
CREATE TABLE `migrations` (
`id` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Modify "crontab" table
ALTER TABLE `crontab` AUTO_INCREMENT 54;
-- Create "alarm_log" table
CREATE TABLE `alarm_log` (
`alarm` bigint NOT NULL AUTO_INCREMENT,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`message` text NOT NULL,
`send` int NOT NULL,
`received` int NOT NULL,
`test` enum('true','false') NOT NULL DEFAULT "false",
`ip` binary(16) NOT NULL,
PRIMARY KEY (`alarm`)
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Create "barrierFree_moreInfo" table
CREATE TABLE `barrierFree_moreInfo` (
`id` bigint NOT NULL AUTO_INCREMENT,
`title` varchar(32) CHARSET utf8mb4 NULL COLLATE utf8mb4_unicode_ci,
`category` varchar(11) CHARSET utf8mb4 NULL COLLATE utf8mb4_unicode_ci,
`url` varchar(128) CHARSET utf8mb4 NULL COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (`id`)
) CHARSET utf8mb3 COLLATE utf8mb3_general_ci;
-- Create "mensaplan_mensa" table
CREATE TABLE `mensaplan_mensa` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`latitude` double NULL,
`longitude` double NULL,
`webid` int NULL,
`category` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Modify "included_dish_name_tag_options" table
ALTER TABLE `included_dish_name_tag_options` AUTO_INCREMENT 94;
-- Create "barrierFree_persons" table
CREATE TABLE `barrierFree_persons` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(40) CHARSET utf8mb4 NULL COLLATE utf8mb4_unicode_ci,
`telephone` varchar(32) CHARSET utf8mb4 NULL COLLATE utf8mb4_unicode_ci,
`email` varchar(32) CHARSET utf8mb4 NULL COLLATE utf8mb4_unicode_ci,
`faculty` varchar(32) CHARSET utf8mb4 NULL COLLATE utf8mb4_unicode_ci,
`office` varchar(16) CHARSET utf8mb4 NULL COLLATE utf8mb4_unicode_ci,
`officeHour` varchar(16) CHARSET utf8mb4 NULL COLLATE utf8mb4_unicode_ci,
`tumID` varchar(24) CHARSET utf8mb4 NULL COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (`id`)
) CHARSET utf8mb3 COLLATE utf8mb3_general_ci;
-- Modify "canteen_rating_tag_options" table
ALTER TABLE `canteen_rating_tag_options` AUTO_INCREMENT 10;
-- Create "curricula" table
CREATE TABLE `curricula` (
`curriculum` bigint NOT NULL AUTO_INCREMENT,
`category` enum('bachelor','master') NOT NULL DEFAULT "bachelor",
`name` mediumtext NOT NULL,
`url` mediumtext NOT NULL,
PRIMARY KEY (`curriculum`)
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Modify "canteen_ratings" table
ALTER TABLE `canteen_ratings` ADD INDEX `cafeteria_rating_cafeteria_cafeteria_fk` (`cafeteriaID`), ADD CONSTRAINT `cafeteria_rating_cafeteria_cafeteria_fk` FOREIGN KEY (`cafeteriaID`) REFERENCES `canteens` (`cafeteria`) ON UPDATE CASCADE ON DELETE CASCADE;
-- Create "member" table
CREATE TABLE `member` (
`member` bigint NOT NULL AUTO_INCREMENT,
`lrz_id` varchar(7) NOT NULL,
`name` varchar(150) NOT NULL,
`active_day` int NULL DEFAULT 0,
`active_day_date` date NULL,
`student_id` text NULL,
`employee_id` text NULL,
`external_id` text NULL,
PRIMARY KEY (`member`),
UNIQUE INDEX `lrz_id` (`lrz_id`)
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci AUTO_INCREMENT 104353;
-- Modify "devices" table
ALTER TABLE `devices` AUTO_INCREMENT 144352, ADD INDEX `member` (`member`), ADD UNIQUE INDEX `uuid` (`uuid`), ADD CONSTRAINT `devices_ibfk_1` FOREIGN KEY (`member`) REFERENCES `member` (`member`) ON UPDATE NO ACTION ON DELETE NO ACTION;
-- Create "device2stats" table
CREATE TABLE `device2stats` (
`device` bigint NOT NULL,
`LecturesPersonalActivity` int NOT NULL DEFAULT 0,
`CafeteriaActivity` int NOT NULL DEFAULT 0,
`WizNavStartActivity` int NOT NULL DEFAULT 0,
`NewsActivity` int NOT NULL DEFAULT 0,
`StartupActivity` int NOT NULL DEFAULT 0,
`MainActivity` int NOT NULL DEFAULT 0,
`CalendarActivity` int NOT NULL DEFAULT 0,
`WizNavCheckTokenActivity` int NOT NULL DEFAULT 0,
`CurriculaActivity` int NOT NULL DEFAULT 0,
`CurriculaDetailsActivity` int NOT NULL DEFAULT 0,
`GradeChartActivity` int NOT NULL DEFAULT 0,
`GradesActivity` int NOT NULL DEFAULT 0,
`InformationActivity` int NOT NULL DEFAULT 0,
`LecturesAppointmentsActivity` int NOT NULL DEFAULT 0,
`LecturesDetailsActivity` int NOT NULL DEFAULT 0,
`OpeningHoursDetailActivity` int NOT NULL DEFAULT 0,
`OpeningHoursListActivity` int NOT NULL DEFAULT 0,
`OrganisationActivity` int NOT NULL DEFAULT 0,
`OrganisationDetailsActivity` int NOT NULL DEFAULT 0,
`PersonsDetailsActivity` int NOT NULL DEFAULT 0,
`PersonsSearchActivity` int NOT NULL DEFAULT 0,
`PlansActivity` int NOT NULL DEFAULT 0,
`PlansDetailsActivity` int NOT NULL DEFAULT 0,
`SetupEduroamActivity` int NOT NULL DEFAULT 0,
`TransportationActivity` int NOT NULL DEFAULT 0,
`TransportationDetailsActivity` int NOT NULL DEFAULT 0,
`TuitionFeesActivity` int NOT NULL DEFAULT 0,
`UserPreferencesActivity` int NOT NULL DEFAULT 0,
`WizNavExtrasActivity` int NOT NULL DEFAULT 0,
`TuitionFeesCard` int NOT NULL DEFAULT 0,
`NextLectureCard` int NOT NULL DEFAULT 0,
`CafeteriaMenuCard` int NOT NULL DEFAULT 0,
`NewsCard1` int NOT NULL DEFAULT 0,
`NewsCard2` int NOT NULL DEFAULT 0,
`NewsCard3` int NOT NULL DEFAULT 0,
`NewsCard7` int NOT NULL DEFAULT 0,
PRIMARY KEY (`device`),
CONSTRAINT `device2stats_ibfk_2` FOREIGN KEY (`device`) REFERENCES `devices` (`device`) ON UPDATE NO ACTION ON DELETE NO ACTION
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Modify "dishes" table
ALTER TABLE `dishes` ADD INDEX `dish_cafeteria_cafeteria_fk` (`cafeteriaID`), ADD CONSTRAINT `dish_cafeteria_cafeteria_fk` FOREIGN KEY (`cafeteriaID`) REFERENCES `canteens` (`cafeteria`) ON UPDATE CASCADE ON DELETE CASCADE;
-- Create "dishflags" table
CREATE TABLE `dishflags` (
`flag` bigint NOT NULL AUTO_INCREMENT,
`short` varchar(10) NOT NULL,
`description` varchar(50) NOT NULL,
PRIMARY KEY (`flag`)
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Create "dish2dishflags" table
CREATE TABLE `dish2dishflags` (
`dish2dishflags` bigint NOT NULL AUTO_INCREMENT,
`dish` bigint NOT NULL,
`flag` bigint NOT NULL,
PRIMARY KEY (`dish2dishflags`),
UNIQUE INDEX `dish` (`dish`, `flag`),
INDEX `flag` (`flag`),
CONSTRAINT `dish2dishflags_dish_dish_fk` FOREIGN KEY (`dish`) REFERENCES `dishes` (`dish`) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `dish2dishflags_dishflags_flag_fk` FOREIGN KEY (`flag`) REFERENCES `dishflags` (`flag`) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `dish2dishflags_ibfk_1` FOREIGN KEY (`dish`) REFERENCES `dishes` (`dish`) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT `dish2dishflags_ibfk_2` FOREIGN KEY (`flag`) REFERENCES `dishflags` (`flag`) ON UPDATE NO ACTION ON DELETE NO ACTION
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Create "mensa" table
CREATE TABLE `mensa` (
`mensa` bigint NOT NULL AUTO_INCREMENT,
`id` int NULL,
`name` text NOT NULL,
`address` text NOT NULL,
`latitude` float NOT NULL DEFAULT 0.000000,
`longitude` float NOT NULL DEFAULT 0.000000,
PRIMARY KEY (`mensa`),
UNIQUE INDEX `id` (`id`)
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci AUTO_INCREMENT 17;
-- Create "dish2mensa" table
CREATE TABLE `dish2mensa` (
`dish2mensa` bigint NOT NULL AUTO_INCREMENT,
`mensa` bigint NOT NULL,
`dish` bigint NOT NULL,
`date` date NOT NULL,
`created` datetime NOT NULL,
`modifierd` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`dish2mensa`),
INDEX `dish` (`dish`),
INDEX `mensa` (`mensa`),
CONSTRAINT `dish2mensa_dish_dish_fk` FOREIGN KEY (`dish`) REFERENCES `dishes` (`dish`) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `dish2mensa_ibfk_1` FOREIGN KEY (`mensa`) REFERENCES `mensa` (`mensa`) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT `dish2mensa_ibfk_2` FOREIGN KEY (`dish`) REFERENCES `dishes` (`dish`) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT `dish2mensa_mensa_mensa_fk` FOREIGN KEY (`mensa`) REFERENCES `mensa` (`mensa`) ON UPDATE CASCADE ON DELETE CASCADE
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Modify "dish_ratings" table
ALTER TABLE `dish_ratings` DROP FOREIGN KEY `fk_dish_ratings_dish`, ADD CONSTRAINT `dish_rating_dish_dish_fk` FOREIGN KEY (`dishID`) REFERENCES `dishes` (`dish`) ON UPDATE NO ACTION ON DELETE NO ACTION;
-- Modify "dish_rating_tag_options" table
ALTER TABLE `dish_rating_tag_options` AUTO_INCREMENT 10;
-- Modify "dish_rating_tags" table
ALTER TABLE `dish_rating_tags` ADD INDEX `dish_rating_tag_dish_rating_dishRating_fk` (`parentRating`), ADD INDEX `dish_rating_tag_dish_rating_tag_option_dishRatingTagOption_fk` (`tagID`), ADD CONSTRAINT `dish_rating_tag_dish_rating_dishRating_fk` FOREIGN KEY (`parentRating`) REFERENCES `dish_ratings` (`dishRating`) ON UPDATE CASCADE ON DELETE CASCADE, ADD CONSTRAINT `dish_rating_tag_dish_rating_tag_option_dishRatingTagOption_fk` FOREIGN KEY (`tagID`) REFERENCES `dish_rating_tag_options` (`dishRatingTagOption`) ON UPDATE CASCADE ON DELETE CASCADE;
-- Modify "dish_to_dish_name_tags" table
ALTER TABLE `dish_to_dish_name_tags` ADD INDEX `dish_to_dish_name_tag_dish_dish_fk` (`dishID`), ADD CONSTRAINT `dish_to_dish_name_tag_dish_dish_fk` FOREIGN KEY (`dishID`) REFERENCES `dishes` (`dish`) ON UPDATE CASCADE ON DELETE CASCADE;
-- Modify "dishes_of_the_weeks" table
ALTER TABLE `dishes_of_the_weeks` ADD INDEX `dishes_of_the_week_dish_dish_fk` (`dishID`), ADD CONSTRAINT `dishes_of_the_week_dish_dish_fk` FOREIGN KEY (`dishID`) REFERENCES `dishes` (`dish`) ON UPDATE CASCADE ON DELETE CASCADE;
-- Create "actions" table
CREATE TABLE `actions` (
`action` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`description` mediumtext NOT NULL,
`color` varchar(6) NOT NULL,
PRIMARY KEY (`action`)
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci AUTO_INCREMENT 19;
-- Create "users" table
CREATE TABLE `users` (
`user` bigint NOT NULL AUTO_INCREMENT,
`username` varchar(7) NULL,
`firstname` varchar(100) NULL,
`surname` varchar(100) NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`deleted` int NOT NULL DEFAULT 0,
`lastActive` int NOT NULL DEFAULT 0,
`lastPage` text NULL,
`lastLogin` datetime NULL,
`tum_id_student` varchar(50) NULL COMMENT "OBFUSCATED_ID_ST",
`tum_id_employee` varchar(50) NULL COMMENT "OBFUSCATED_ID_B",
`tum_id_alumni` varchar(50) NULL COMMENT "OBFUSCATED_ID_EXT",
`tum_id_preferred` varchar(50) NULL COMMENT "OBFUSCATED_ID_BEVORZUGT",
PRIMARY KEY (`user`),
UNIQUE INDEX `username` (`username`)
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci AUTO_INCREMENT 434;
-- Create "log" table
CREATE TABLE `log` (
`log` bigint NOT NULL AUTO_INCREMENT,
`time` int NOT NULL,
`user_executed` bigint NULL,
`user_affected` bigint NULL,
`action` bigint NULL,
`comment` varchar(255) NOT NULL,
PRIMARY KEY (`log`),
INDEX `action` (`action`),
INDEX `user` (`user_executed`),
INDEX `user_affected` (`user_affected`),
CONSTRAINT `fkLog2Actions` FOREIGN KEY (`action`) REFERENCES `actions` (`action`) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT `fkLog2UsersAf` FOREIGN KEY (`user_affected`) REFERENCES `users` (`user`) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT `fkLog2UsersEx` FOREIGN KEY (`user_executed`) REFERENCES `users` (`user`) ON UPDATE NO ACTION ON DELETE NO ACTION
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Create "rights" table
CREATE TABLE `rights` (
`right` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(100) NULL,
`description` mediumtext NOT NULL,
`category` int NOT NULL DEFAULT 0,
PRIMARY KEY (`right`),
UNIQUE INDEX `Unquie` (`name`)
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci AUTO_INCREMENT 14;
-- Create "menu" table
CREATE TABLE `menu` (
`menu` bigint NOT NULL AUTO_INCREMENT,
`right` bigint NULL,
`parent` bigint NULL,
`name` varchar(255) NULL,
`path` varchar(255) NULL,
`target` enum('_blank','_self','_parent','_top') NOT NULL DEFAULT "_self",
`icon` varchar(200) NOT NULL,
`class` varchar(200) NOT NULL,
`position` int NOT NULL DEFAULT 0,
PRIMARY KEY (`menu`),
INDEX `parent` (`parent`),
INDEX `right` (`right`),
CONSTRAINT `menu_ibfk_1` FOREIGN KEY (`right`) REFERENCES `rights` (`right`) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT `menu_ibfk_2` FOREIGN KEY (`parent`) REFERENCES `menu` (`menu`) ON UPDATE NO ACTION ON DELETE NO ACTION
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci AUTO_INCREMENT 25;
-- Create "modules" table
CREATE TABLE `modules` (
`module` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(255) NULL,
`right` bigint NULL,
PRIMARY KEY (`module`),
INDEX `module_right` (`right`),
CONSTRAINT `fkMod2Rights` FOREIGN KEY (`right`) REFERENCES `rights` (`right`) ON UPDATE NO ACTION ON DELETE NO ACTION
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Modify "files" table
ALTER TABLE `files` AUTO_INCREMENT 34761;
-- Modify "movies" table
ALTER TABLE `movies` AUTO_INCREMENT 219, MODIFY COLUMN `trailer` text NULL, DROP INDEX `uni_kino_link`, ADD INDEX `cover` (`cover`), ADD UNIQUE INDEX `link` (`link`), DROP FOREIGN KEY `fk_movies_file`, ADD CONSTRAINT `movies_ibfk_1` FOREIGN KEY (`cover`) REFERENCES `files` (`file`) ON UPDATE CASCADE ON DELETE CASCADE;
-- Modify "news_sources" table
ALTER TABLE `news_sources` AUTO_INCREMENT 17, MODIFY COLUMN `title` text NOT NULL, MODIFY COLUMN `hook` enum('newspread','impulsivHook') NULL, ADD INDEX `icon` (`icon`), DROP FOREIGN KEY `fk_news_sources_file`, ADD CONSTRAINT `news_sources_ibfk_1` FOREIGN KEY (`icon`) REFERENCES `files` (`file`) ON UPDATE CASCADE ON DELETE CASCADE;
-- Modify "news" table
ALTER TABLE `news` AUTO_INCREMENT 770113, MODIFY COLUMN `date` datetime NOT NULL, MODIFY COLUMN `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, MODIFY COLUMN `title` tinytext NOT NULL, MODIFY COLUMN `description` text NOT NULL, MODIFY COLUMN `src` bigint NOT NULL, MODIFY COLUMN `link` varchar(190) NOT NULL, ADD INDEX `file` (`file`), ADD UNIQUE INDEX `link` (`link`), ADD INDEX `src` (`src`), DROP FOREIGN KEY `fk_news_file`, DROP FOREIGN KEY `fk_news_news_source`, ADD CONSTRAINT `news_ibfk_1` FOREIGN KEY (`src`) REFERENCES `news_sources` (`source`) ON UPDATE NO ACTION ON DELETE NO ACTION, ADD CONSTRAINT `news_ibfk_2` FOREIGN KEY (`file`) REFERENCES `files` (`file`) ON UPDATE CASCADE ON DELETE CASCADE;
-- Modify "news_alerts" table
ALTER TABLE `news_alerts` MODIFY COLUMN `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, MODIFY COLUMN `from` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, MODIFY COLUMN `to` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, ADD INDEX `news_alert_files_file_fk` (`file`), DROP FOREIGN KEY `fk_news_alerts_file`, ADD CONSTRAINT `news_alert_files_file_fk` FOREIGN KEY (`file`) REFERENCES `files` (`file`) ON UPDATE NO ACTION ON DELETE CASCADE;
-- Modify "notification_type" table
ALTER TABLE `notification_type` AUTO_INCREMENT 4, MODIFY COLUMN `confirmation` enum('true','false') NULL;
-- Create "location" table
CREATE TABLE `location` (
`location` bigint NOT NULL AUTO_INCREMENT,
`name` text CHARSET utf8mb4 NULL COLLATE utf8mb4_unicode_ci,
`lon` float NOT NULL,
`lat` float NOT NULL,
`radius` int NOT NULL DEFAULT 1000 COMMENT "in meters",
PRIMARY KEY (`location`)
) CHARSET utf8mb3 COLLATE utf8mb3_general_ci AUTO_INCREMENT 2;
-- Modify "notification" table
ALTER TABLE `notification` AUTO_INCREMENT 107, MODIFY COLUMN `type` bigint NOT NULL, MODIFY COLUMN `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, MODIFY COLUMN `silent` bool NOT NULL DEFAULT 0, ADD INDEX `location` (`location`), ADD INDEX `type` (`type`), ADD CONSTRAINT `notification_ibfk_1` FOREIGN KEY (`type`) REFERENCES `notification_type` (`type`) ON UPDATE NO ACTION ON DELETE NO ACTION, ADD CONSTRAINT `notification_ibfk_2` FOREIGN KEY (`location`) REFERENCES `location` (`location`) ON UPDATE NO ACTION ON DELETE NO ACTION;
-- Modify "notification_confirmation" table
ALTER TABLE `notification_confirmation` MODIFY COLUMN `sent` bool NOT NULL DEFAULT 0, MODIFY COLUMN `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, ADD INDEX `device` (`device`), ADD CONSTRAINT `notification_confirmation_ibfk_1` FOREIGN KEY (`notification`) REFERENCES `notification` (`notification`) ON UPDATE NO ACTION ON DELETE NO ACTION, ADD CONSTRAINT `notification_confirmation_ibfk_2` FOREIGN KEY (`device`) REFERENCES `devices` (`device`) ON UPDATE NO ACTION ON DELETE NO ACTION;
-- Create "questionAnswers" table
CREATE TABLE `questionAnswers` (
`answer` bigint NOT NULL AUTO_INCREMENT,
`text` text NULL,
PRIMARY KEY (`answer`)
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci AUTO_INCREMENT 3;
-- Create "question" table
CREATE TABLE `question` (
`question` bigint NOT NULL AUTO_INCREMENT,
`member` bigint NOT NULL,
`text` text NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`end` timestamp NULL,
PRIMARY KEY (`question`),
INDEX `member` (`member`)
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci AUTO_INCREMENT 282;
-- Create "question2answer" table
CREATE TABLE `question2answer` (
`question` bigint NOT NULL,
`answer` bigint NOT NULL,
`member` bigint NOT NULL,
UNIQUE INDEX `question` (`question`, `member`),
INDEX `question2answer_member_member_fk` (`member`),
INDEX `question2answer_questionAnswers_answer_fk` (`answer`),
CONSTRAINT `question2answer_member_member_fk` FOREIGN KEY (`member`) REFERENCES `member` (`member`) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT `question2answer_questionAnswers_answer_fk` FOREIGN KEY (`answer`) REFERENCES `questionAnswers` (`answer`) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT `question2answer_question_question_fk` FOREIGN KEY (`question`) REFERENCES `question` (`question`) ON UPDATE NO ACTION ON DELETE NO ACTION
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Create "faculty" table
CREATE TABLE `faculty` (
`faculty` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(150) NULL COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (`faculty`),
UNIQUE INDEX `name` (`name`)
) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci AUTO_INCREMENT 18;
-- Create "question2faculty" table
CREATE TABLE `question2faculty` (
`question` bigint NOT NULL,
`faculty` bigint NOT NULL,
PRIMARY KEY (`question`, `faculty`),
INDEX `faculty` (`faculty`),
CONSTRAINT `question2faculty_ibfk_1` FOREIGN KEY (`question`) REFERENCES `question` (`question`) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT `question2faculty_ibfk_2` FOREIGN KEY (`faculty`) REFERENCES `faculty` (`faculty`) ON UPDATE NO ACTION ON DELETE NO ACTION
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Create "recover" table
CREATE TABLE `recover` (
`recover` bigint NOT NULL AUTO_INCREMENT,
`user` bigint NOT NULL,
`created` int NOT NULL,
`hash` varchar(190) NOT NULL,
`ip` varchar(255) NOT NULL,
PRIMARY KEY (`recover`),
UNIQUE INDEX `hash` (`hash`),
INDEX `user` (`user`),
CONSTRAINT `fkRecover2User` FOREIGN KEY (`user`) REFERENCES `users` (`user`) ON UPDATE NO ACTION ON DELETE NO ACTION
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Create "reports" table
CREATE TABLE `reports` (
`report` bigint NOT NULL AUTO_INCREMENT,
`device` bigint NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`fixed` enum('true','false') NOT NULL DEFAULT "false",
`issue` int NULL,
`stacktrace` mediumtext NOT NULL,
`stacktraceGroup` text NULL,
`log` mediumtext NOT NULL,
`package` mediumtext NOT NULL,
`packageVersion` mediumtext NOT NULL,
`packageVersionCode` int NOT NULL DEFAULT -1,
`model` mediumtext NOT NULL,
`osVersion` mediumtext NOT NULL,
`networkWifi` varchar(10) NOT NULL,
`networkMobile` varchar(10) NOT NULL,
`gps` varchar(10) NOT NULL,
`screenWidth` varchar(100) NOT NULL,
`screenHeight` varchar(100) NOT NULL,
`screenOrientation` varchar(100) NOT NULL,
`screenDpi` varchar(100) NOT NULL,
PRIMARY KEY (`report`),
INDEX `device` (`device`),
CONSTRAINT `reports_ibfk_3` FOREIGN KEY (`device`) REFERENCES `devices` (`device`) ON UPDATE NO ACTION ON DELETE NO ACTION
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Create "roles" table
CREATE TABLE `roles` (
`role` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`description` mediumtext NOT NULL,
PRIMARY KEY (`role`)
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci AUTO_INCREMENT 6;
-- Create "roles2rights" table
CREATE TABLE `roles2rights` (
`role` bigint NOT NULL,
`right` bigint NOT NULL,
PRIMARY KEY (`role`, `right`),
INDEX `fkRight_idx` (`right`),
CONSTRAINT `fkRight` FOREIGN KEY (`right`) REFERENCES `rights` (`right`) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT `fkRole` FOREIGN KEY (`role`) REFERENCES `roles` (`role`) ON UPDATE NO ACTION ON DELETE NO ACTION
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Create "users2info" table
CREATE TABLE `users2info` (
`user` bigint NOT NULL,
`firstname` varchar(255) NOT NULL,
`surname` varchar(255) NOT NULL,
`lastPwChange` int NOT NULL,
`pager` int NULL DEFAULT 15,
PRIMARY KEY (`user`),
CONSTRAINT `fkUsers` FOREIGN KEY (`user`) REFERENCES `users` (`user`) ON UPDATE NO ACTION ON DELETE NO ACTION
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Create "users2roles" table
CREATE TABLE `users2roles` (
`user` bigint NOT NULL,
`role` bigint NOT NULL,
PRIMARY KEY (`user`, `role`),
INDEX `fkUser2RolesRole` (`role`),
CONSTRAINT `fkUser2RolesRole` FOREIGN KEY (`role`) REFERENCES `roles` (`role`) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT `fkUser2RolesUser` FOREIGN KEY (`user`) REFERENCES `users` (`user`) ON UPDATE NO ACTION ON DELETE NO ACTION
) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
Needed from auto to local migration state
-- Modify "" schema
ALTER DATABASE COLLATE utf8mb4_0900_ai_ci;
-- Modify "canteen_ratings" table
ALTER TABLE `canteen_ratings` DROP FOREIGN KEY `cafeteria_rating_cafeteria_cafeteria_fk`;
-- Modify "device2stats" table
ALTER TABLE `device2stats` DROP FOREIGN KEY `device2stats_ibfk_2`;
-- Modify "devices" table
ALTER TABLE `devices` DROP INDEX `uuid`, DROP FOREIGN KEY `devices_ibfk_1`;
-- Modify "dish2dishflags" table
ALTER TABLE `dish2dishflags` DROP FOREIGN KEY `dish2dishflags_dish_dish_fk`, DROP FOREIGN KEY `dish2dishflags_dishflags_flag_fk`, DROP FOREIGN KEY `dish2dishflags_ibfk_1`, DROP FOREIGN KEY `dish2dishflags_ibfk_2`;
-- Modify "dish2mensa" table
ALTER TABLE `dish2mensa` DROP FOREIGN KEY `dish2mensa_dish_dish_fk`, DROP FOREIGN KEY `dish2mensa_ibfk_1`, DROP FOREIGN KEY `dish2mensa_ibfk_2`, DROP FOREIGN KEY `dish2mensa_mensa_mensa_fk`;
-- Modify "dish_rating_tags" table
ALTER TABLE `dish_rating_tags` DROP FOREIGN KEY `dish_rating_tag_dish_rating_dishRating_fk`, DROP FOREIGN KEY `dish_rating_tag_dish_rating_tag_option_dishRatingTagOption_fk`;
-- Modify "dish_ratings" table
ALTER TABLE `dish_ratings` DROP FOREIGN KEY `dish_rating_dish_dish_fk`;
-- Modify "dish_to_dish_name_tags" table
ALTER TABLE `dish_to_dish_name_tags` DROP FOREIGN KEY `dish_to_dish_name_tag_dish_dish_fk`;
-- Modify "dishes" table
ALTER TABLE `dishes` DROP FOREIGN KEY `dish_cafeteria_cafeteria_fk`;
-- Modify "dishes_of_the_weeks" table
ALTER TABLE `dishes_of_the_weeks` DROP FOREIGN KEY `dishes_of_the_week_dish_dish_fk`;
-- Modify "feedback" table
ALTER TABLE `feedback` MODIFY COLUMN `email_id` longtext NOT NULL, MODIFY COLUMN `receiver` longtext NOT NULL, MODIFY COLUMN `reply_to_email` longtext NULL, MODIFY COLUMN `reply_to_name` longtext NULL, MODIFY COLUMN `feedback` longtext NOT NULL, MODIFY COLUMN `os_version` longtext NULL, MODIFY COLUMN `app_version` longtext NULL;
-- Modify "log" table
ALTER TABLE `log` DROP FOREIGN KEY `fkLog2Actions`, DROP FOREIGN KEY `fkLog2UsersAf`, DROP FOREIGN KEY `fkLog2UsersEx`;
-- Modify "menu" table
ALTER TABLE `menu` DROP FOREIGN KEY `menu_ibfk_1`;
-- Modify "modules" table
ALTER TABLE `modules` DROP FOREIGN KEY `fkMod2Rights`;
-- Modify "movies" table
ALTER TABLE `movies` MODIFY COLUMN `trailer` longtext NULL, DROP INDEX `link`, ADD INDEX `fk_movies_file` (`cover`), ADD UNIQUE INDEX `uni_kino_link` (`link`), DROP FOREIGN KEY `movies_ibfk_1`;
-- Modify "news" table
ALTER TABLE `news` MODIFY COLUMN `date` datetime NULL, MODIFY COLUMN `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP, MODIFY COLUMN `title` text NULL, MODIFY COLUMN `description` text NULL, MODIFY COLUMN `src` bigint NULL, MODIFY COLUMN `link` varchar(190) NULL, DROP INDEX `link`, ADD INDEX `fk_news_file` (`file`), ADD INDEX `fk_news_news_source` (`src`), DROP FOREIGN KEY `news_ibfk_1`, DROP FOREIGN KEY `news_ibfk_2`;
-- Modify "news_alerts" table
ALTER TABLE `news_alerts` MODIFY COLUMN `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP, MODIFY COLUMN `from` datetime NULL DEFAULT CURRENT_TIMESTAMP, MODIFY COLUMN `to` datetime NULL DEFAULT CURRENT_TIMESTAMP, ADD INDEX `fk_news_alerts_file` (`file`), DROP FOREIGN KEY `news_alert_files_file_fk`;
-- Modify "news_sources" table
ALTER TABLE `news_sources` MODIFY COLUMN `title` text NULL, MODIFY COLUMN `hook` char(1) NULL, ADD INDEX `fk_news_sources_file` (`icon`), DROP FOREIGN KEY `news_sources_ibfk_1`;
-- Modify "notification" table
ALTER TABLE `notification` MODIFY COLUMN `type` int NULL, MODIFY COLUMN `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP, MODIFY COLUMN `silent` tinyint NULL DEFAULT 0, DROP FOREIGN KEY `notification_ibfk_1`, DROP FOREIGN KEY `notification_ibfk_2`;
-- Modify "notification_confirmation" table
ALTER TABLE `notification_confirmation` MODIFY COLUMN `sent` tinyint NULL DEFAULT 0, MODIFY COLUMN `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP, DROP FOREIGN KEY `notification_confirmation_ibfk_1`, DROP FOREIGN KEY `notification_confirmation_ibfk_2`;
-- Modify "notification_type" table
ALTER TABLE `notification_type` MODIFY COLUMN `confirmation` enum('true','false') NULL DEFAULT "false";
-- Modify "question2answer" table
ALTER TABLE `question2answer` DROP FOREIGN KEY `question2answer_member_member_fk`, DROP FOREIGN KEY `question2answer_questionAnswers_answer_fk`, DROP FOREIGN KEY `question2answer_question_question_fk`;
-- Modify "question2faculty" table
ALTER TABLE `question2faculty` DROP FOREIGN KEY `question2faculty_ibfk_1`, DROP FOREIGN KEY `question2faculty_ibfk_2`;
-- Modify "recover" table
ALTER TABLE `recover` DROP FOREIGN KEY `fkRecover2User`;
-- Modify "reports" table
ALTER TABLE `reports` DROP FOREIGN KEY `reports_ibfk_3`;
-- Modify "roles2rights" table
ALTER TABLE `roles2rights` DROP FOREIGN KEY `fkRight`, DROP FOREIGN KEY `fkRole`;
-- Modify "users2info" table
ALTER TABLE `users2info` DROP FOREIGN KEY `fkUsers`;
-- Modify "users2roles" table
ALTER TABLE `users2roles` DROP FOREIGN KEY `fkUser2RolesRole`, DROP FOREIGN KEY `fkUser2RolesUser`;
-- Modify "dish_ratings" table
ALTER TABLE `dish_ratings` ADD CONSTRAINT `fk_dish_ratings_dish` FOREIGN KEY (`dishID`) REFERENCES `dishes` (`dish`) ON UPDATE CASCADE ON DELETE CASCADE;
-- Modify "movies" table
ALTER TABLE `movies` ADD CONSTRAINT `fk_movies_file` FOREIGN KEY (`cover`) REFERENCES `files` (`file`) ON UPDATE NO ACTION ON DELETE NO ACTION;
-- Modify "news" table
ALTER TABLE `news` ADD CONSTRAINT `fk_news_file` FOREIGN KEY (`file`) REFERENCES `files` (`file`) ON UPDATE CASCADE ON DELETE CASCADE, ADD CONSTRAINT `fk_news_news_source` FOREIGN KEY (`src`) REFERENCES `news_sources` (`source`) ON UPDATE CASCADE ON DELETE CASCADE;
-- Modify "news_alerts" table
ALTER TABLE `news_alerts` ADD CONSTRAINT `fk_news_alerts_file` FOREIGN KEY (`file`) REFERENCES `files` (`file`) ON UPDATE CASCADE ON DELETE CASCADE;
-- Modify "news_sources" table
ALTER TABLE `news_sources` ADD CONSTRAINT `fk_news_sources_file` FOREIGN KEY (`icon`) REFERENCES `files` (`file`) ON UPDATE CASCADE ON DELETE CASCADE;
-- Drop "actions" table
DROP TABLE `actions`;
-- Drop "alarm_ban" table
DROP TABLE `alarm_ban`;
-- Drop "alarm_log" table
DROP TABLE `alarm_log`;
-- Drop "barrierFree_moreInfo" table
DROP TABLE `barrierFree_moreInfo`;
-- Drop "barrierFree_persons" table
DROP TABLE `barrierFree_persons`;
-- Drop "curricula" table
DROP TABLE `curricula`;
-- Drop "device2stats" table
DROP TABLE `device2stats`;
-- Drop "dish2dishflags" table
DROP TABLE `dish2dishflags`;
-- Drop "dish2mensa" table
DROP TABLE `dish2mensa`;
-- Drop "dishflags" table
DROP TABLE `dishflags`;
-- Drop "faculty" table
DROP TABLE `faculty`;
-- Drop "location" table
DROP TABLE `location`;
-- Drop "log" table
DROP TABLE `log`;
-- Drop "member" table
DROP TABLE `member`;
-- Drop "mensa" table
DROP TABLE `mensa`;
-- Drop "mensaplan_mensa" table
DROP TABLE `mensaplan_mensa`;
-- Drop "mensaprices" table
DROP TABLE `mensaprices`;
-- Drop "menu" table
DROP TABLE `menu`;
-- Drop "migrations" table
DROP TABLE `migrations`;
-- Drop "modules" table
DROP TABLE `modules`;
-- Drop "openinghours" table
DROP TABLE `openinghours`;
-- Drop "published_exam_results" table
DROP TABLE `published_exam_results`;
-- Drop "question" table
DROP TABLE `question`;
-- Drop "question2answer" table
DROP TABLE `question2answer`;
-- Drop "question2faculty" table
DROP TABLE `question2faculty`;
-- Drop "questionAnswers" table
DROP TABLE `questionAnswers`;
-- Drop "recover" table
DROP TABLE `recover`;
-- Drop "reports" table
DROP TABLE `reports`;
-- Drop "rights" table
DROP TABLE `rights`;
-- Drop "roles" table
DROP TABLE `roles`;
-- Drop "roles2rights" table
DROP TABLE `roles2rights`;
-- Drop "sessions" table
DROP TABLE `sessions`;
-- Drop "users" table
DROP TABLE `users`;
-- Drop "users2info" table
DROP TABLE `users2info`;
-- Drop "users2roles" table
DROP TABLE `users2roles`;
-- Drop "wifi_measurement" table
DROP TABLE `wifi_measurement`;
![github-actions[bot] avatar](https://avatars.githubusercontent.com/in/15368?v=4 "Published by a pub.dev verified publisher"){kind=small}