search

TUM-Dev / Campus-Backend

New backend written in go with gRPC as an API interface
GNU General Public License v3.0
15 stars 7 forks source link

(feat): add cache for movies #466

Closed joschahenningsen closed 1 week ago

joschahenningsen commented 1 week ago

The movies table contributes to a majority of our database traffic but is very static. This PR adds a cache to minimize database hits.

github-actions[bot] commented 1 week ago

:eyes: Found the following differences in the sql schema:

Needed get from local to auto migration state ```sql -- 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 `fk_movies_file`, 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, DROP INDEX `fk_news_sources_file`, 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, DROP INDEX `fk_news_file`, DROP INDEX `fk_news_news_source`, 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, DROP INDEX `fk_news_alerts_file`, 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 ```sql -- Modify "" schema ALTER DATABASE COLLATE utf8mb4_0900_ai_ci; -- Modify "canteen_ratings" table ALTER TABLE `canteen_ratings` DROP INDEX `cafeteria_rating_cafeteria_cafeteria_fk`, 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 `member`, 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 INDEX `dish_rating_tag_dish_rating_dishRating_fk`, DROP INDEX `dish_rating_tag_dish_rating_tag_option_dishRatingTagOption_fk`, 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 INDEX `dish_to_dish_name_tag_dish_dish_fk`, DROP FOREIGN KEY `dish_to_dish_name_tag_dish_dish_fk`; -- Modify "dishes" table ALTER TABLE `dishes` DROP INDEX `dish_cafeteria_cafeteria_fk`, DROP FOREIGN KEY `dish_cafeteria_cafeteria_fk`; -- Modify "dishes_of_the_weeks" table ALTER TABLE `dishes_of_the_weeks` DROP INDEX `dishes_of_the_week_dish_dish_fk`, 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 `cover`, 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 `file`, DROP INDEX `link`, DROP INDEX `src`, 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, DROP INDEX `news_alert_files_file_fk`, 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, DROP INDEX `icon`, 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 INDEX `location`, DROP INDEX `type`, 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 INDEX `device`, 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`; ```