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

Implemented antispam via feedback replyto ratelimiting #341

Closed CommanderStorm closed 7 months ago

CommanderStorm commented 7 months ago

Also adds a different field for the name of the submittee

github-actions[bot] commented 7 months ago

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

Needed get from local to auto migration state ```sql -- Create "mensaprices" table CREATE TABLE `mensaprices` ( `price` bigint NOT NULL AUTO_INCREMENT, `created` timestamp NOT NULL DEFAULT (current_timestamp()), `person` mediumtext NOT NULL, `type` mediumtext NOT NULL, `typeLong` mediumtext NOT NULL, `typeNumber` int NOT NULL, `value` decimal(10) NOT NULL, PRIMARY KEY (`price`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Modify "dish_name_tag_option" table ALTER TABLE `dish_name_tag_option` AUTO_INCREMENT 69; -- Create "roomfinder_rooms2maps" table CREATE TABLE `roomfinder_rooms2maps` ( `room_id` bigint NOT NULL, `map_id` bigint NOT NULL, PRIMARY KEY (`room_id`, `map_id`) ) CHARSET utf8mb4 COLLATE utf8mb4_general_ci; -- Create "roomfinder_buildings" table CREATE TABLE `roomfinder_buildings` ( `building_nr` varchar(8) NOT NULL COLLATE utf8mb4_unicode_ci, `utm_zone` varchar(4) NULL COLLATE utf8mb4_unicode_ci, `utm_easting` varchar(32) NULL COLLATE utf8mb4_unicode_ci, `utm_northing` varchar(32) NULL COLLATE utf8mb4_unicode_ci, `default_map_id` int NULL, PRIMARY KEY (`building_nr`) ) CHARSET utf8mb4 COLLATE utf8mb4_general_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; -- Modify "dish_name_tag_option_excluded" table ALTER TABLE `dish_name_tag_option_excluded` AUTO_INCREMENT 9; -- Modify "dish_name_tag_option_included" table ALTER TABLE `dish_name_tag_option_included` AUTO_INCREMENT 94; -- 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; -- Modify "feedback" table ALTER TABLE `feedback` MODIFY COLUMN `email_id` text NULL, MODIFY COLUMN `receiver` text NULL, MODIFY COLUMN `feedback` text NULL, MODIFY COLUMN `latitude` decimal(11,8) NULL, MODIFY COLUMN `longitude` decimal(11,8) NULL, MODIFY COLUMN `timestamp` datetime NOT NULL DEFAULT (current_timestamp()); -- 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 "roomfinder_buildings2gps" table CREATE TABLE `roomfinder_buildings2gps` ( `id` varchar(8) NOT NULL COLLATE utf8mb4_unicode_ci, `latitude` varchar(30) NULL COLLATE utf8mb4_unicode_ci, `longitude` varchar(30) NULL COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`id`) ) CHARSET utf8mb4 COLLATE utf8mb4_general_ci; -- Create "roomfinder_schedules" table CREATE TABLE `roomfinder_schedules` ( `room_id` bigint NOT NULL AUTO_INCREMENT, `start` datetime NOT NULL, `end` datetime NOT NULL, `title` varchar(64) NULL COLLATE utf8mb4_unicode_ci, `event_id` int NOT NULL, `course_code` varchar(32) NULL COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`room_id`, `start`, `end`), UNIQUE INDEX `unique` (`room_id`, `start`, `end`) ) CHARSET utf8mb4 COLLATE utf8mb4_general_ci; -- Create "roomfinder_buildings2maps" table CREATE TABLE `roomfinder_buildings2maps` ( `building_nr` varchar(8) NOT NULL COLLATE utf8mb4_unicode_ci, `map_id` bigint NOT NULL, PRIMARY KEY (`building_nr`, `map_id`) ) CHARSET utf8mb4 COLLATE utf8mb4_general_ci; -- 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; -- Modify "crontab" table ALTER TABLE `crontab` AUTO_INCREMENT 53, MODIFY COLUMN `interval` int NOT NULL DEFAULT 7200, MODIFY COLUMN `lastRun` int NOT NULL DEFAULT 0, MODIFY COLUMN `type` enum('news','mensa','roomfinder','ticketsale','alarm','fileDownload','canteenHeadCount','iosNotifications','iosActivityReset','newExamResultsHook','movie','feedbackEmail','dishNameDownload') NULL; -- Modify "canteen_head_count" table ALTER TABLE `canteen_head_count` MODIFY COLUMN `timestamp` timestamp NOT NULL DEFAULT (current_timestamp()) ON UPDATE current_timestamp(); -- Create "migrations" table CREATE TABLE `migrations` ( `id` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Modify "cafeteria_rating" table ALTER TABLE `cafeteria_rating` MODIFY COLUMN `timestamp` timestamp NOT NULL DEFAULT (current_timestamp()) ON UPDATE current_timestamp(); -- 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; -- 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 "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; -- Create "roomfinder_building2area" table CREATE TABLE `roomfinder_building2area` ( `area_id` int NOT NULL, `building_nr` varchar(8) NOT NULL COLLATE utf8mb4_unicode_ci, `campus` char(1) NULL COLLATE utf8mb4_unicode_ci, `name` varchar(32) NULL COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`building_nr`) ) CHARSET utf8mb4 COLLATE utf8mb4_general_ci; -- Create "roomfinder_rooms" table CREATE TABLE `roomfinder_rooms` ( `room_id` bigint NOT NULL AUTO_INCREMENT, `room_code` varchar(32) NULL COLLATE utf8mb4_unicode_ci, `building_nr` varchar(8) NULL COLLATE utf8mb4_unicode_ci, `arch_id` varchar(16) NULL COLLATE utf8mb4_unicode_ci, `info` varchar(64) NULL COLLATE utf8mb4_unicode_ci, `address` varchar(128) NULL COLLATE utf8mb4_unicode_ci, `purpose_id` bigint NULL, `purpose` varchar(64) NULL COLLATE utf8mb4_unicode_ci, `seats` bigint NULL, `utm_zone` varchar(4) NULL COLLATE utf8mb4_unicode_ci, `utm_easting` varchar(32) NULL COLLATE utf8mb4_unicode_ci, `utm_northing` varchar(32) NULL COLLATE utf8mb4_unicode_ci, `unit_id` bigint NULL, `default_map_id` bigint NULL, PRIMARY KEY (`room_id`) ) CHARSET utf8mb4 COLLATE utf8mb4_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 ENGINE MyISAM AUTO_INCREMENT 30; -- 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; -- Create "roomfinder_maps" table CREATE TABLE `roomfinder_maps` ( `map_id` bigint NOT NULL AUTO_INCREMENT, `description` varchar(64) NULL COLLATE utf8mb4_unicode_ci, `scale` int NOT NULL, `width` int NOT NULL, `height` int NOT NULL, PRIMARY KEY (`map_id`) ) CHARSET utf8mb4 COLLATE utf8mb4_general_ci; -- Create "chat_room" table CREATE TABLE `chat_room` ( `room` bigint NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `semester` varchar(3) NULL, PRIMARY KEY (`room`), UNIQUE INDEX `Index 2` (`semester`, `name`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci AUTO_INCREMENT 1724450; -- 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; -- Create "chat_message" table CREATE TABLE `chat_message` ( `message` bigint NOT NULL AUTO_INCREMENT, `member` bigint NOT NULL, `room` bigint NOT NULL, `text` longtext NOT NULL, `created` datetime NOT NULL, `signature` longtext NOT NULL, PRIMARY KEY (`message`), INDEX `chat_message_b3c09425` (`member`), INDEX `chat_message_ca20ebca` (`room`), CONSTRAINT `FK_chat_message_chat_room` FOREIGN KEY (`room`) REFERENCES `chat_room` (`room`) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT `chat_message_ibfk_1` FOREIGN KEY (`member`) REFERENCES `member` (`member`) ON UPDATE RESTRICT ON DELETE RESTRICT ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "chat_room2members" table CREATE TABLE `chat_room2members` ( `room2members` bigint NOT NULL AUTO_INCREMENT, `room` bigint NOT NULL, `member` bigint NOT NULL, PRIMARY KEY (`room2members`), UNIQUE INDEX `chatroom_id` (`room`, `member`), INDEX `chat_chatroom_members_29801a33` (`room`), INDEX `chat_chatroom_members_b3c09425` (`member`), CONSTRAINT `FK_chat_room2members_chat_room` FOREIGN KEY (`room`) REFERENCES `chat_room` (`room`) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT `chat_room2members_ibfk_2` FOREIGN KEY (`member`) REFERENCES `member` (`member`) ON UPDATE RESTRICT ON DELETE RESTRICT ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Modify "devices" table ALTER TABLE `devices` AUTO_INCREMENT 144352, MODIFY COLUMN `uuid` varchar(50) NOT NULL, MODIFY COLUMN `lastAccess` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp(), MODIFY COLUMN `lastApi` mediumtext NOT NULL DEFAULT '', MODIFY COLUMN `developer` enum('true','false') NOT NULL DEFAULT 'false', MODIFY COLUMN `osVersion` mediumtext NOT NULL DEFAULT '', MODIFY COLUMN `appVersion` mediumtext NOT NULL DEFAULT '', MODIFY COLUMN `counter` int NOT NULL DEFAULT 0, MODIFY COLUMN `pk` longtext NULL, MODIFY COLUMN `pkActive` enum('true','false') NOT NULL DEFAULT 'false', ADD INDEX `member` (`member`), ADD UNIQUE INDEX `uuid` (`uuid`), ADD CONSTRAINT `devices_ibfk_1` FOREIGN KEY (`member`) REFERENCES `member` (`member`) ON UPDATE RESTRICT ON DELETE RESTRICT; -- 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, `ChatRoomsActivity` int NOT NULL DEFAULT 0, `CalendarActivity` int NOT NULL DEFAULT 0, `WizNavCheckTokenActivity` int NOT NULL DEFAULT 0, `ChatActivity` 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, `RoomFinderActivity` int NOT NULL DEFAULT 0, `RoomFinderDetailsActivity` 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, `WizNavChatActivity` 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 RESTRICT ON DELETE RESTRICT ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 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_ibfk_1` FOREIGN KEY (`dish`) REFERENCES `dish` (`dish`) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT `dish2dishflags_ibfk_2` FOREIGN KEY (`flag`) REFERENCES `dishflags` (`flag`) ON UPDATE RESTRICT ON DELETE RESTRICT ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "mensa" table CREATE TABLE `mensa` ( `mensa` bigint NOT NULL AUTO_INCREMENT, `id` int NULL, `name` mediumtext NOT NULL, `address` mediumtext 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 '0000-00-00 00:00:00' ON UPDATE current_timestamp(), PRIMARY KEY (`dish2mensa`), INDEX `dish` (`dish`), INDEX `mensa` (`mensa`), CONSTRAINT `dish2mensa_ibfk_1` FOREIGN KEY (`mensa`) REFERENCES `mensa` (`mensa`) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT `dish2mensa_ibfk_2` FOREIGN KEY (`dish`) REFERENCES `dish` (`dish`) ON UPDATE RESTRICT ON DELETE RESTRICT ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Modify "dish_rating" table ALTER TABLE `dish_rating` MODIFY COLUMN `timestamp` timestamp NOT NULL DEFAULT (current_timestamp()) ON UPDATE current_timestamp(), ADD INDEX `dish_rating_dish_dish_fk` (`dishID`), DROP FOREIGN KEY `fk_dish_rating_dish`, ADD CONSTRAINT `dish_rating_dish_dish_fk` FOREIGN KEY (`dishID`) REFERENCES `dish` (`dish`) ON UPDATE RESTRICT ON DELETE RESTRICT; -- Modify "files" table ALTER TABLE `files` AUTO_INCREMENT 34761, MODIFY COLUMN `name` text NOT NULL, MODIFY COLUMN `path` text NOT NULL, MODIFY COLUMN `downloads` int NOT NULL DEFAULT 0, ADD UNIQUE INDEX `url_unique` (`url`); -- Create "ticket_group" table CREATE TABLE `ticket_group` ( `ticket_group` bigint NOT NULL AUTO_INCREMENT, `description` text CHARSET utf8mb4 NULL COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`ticket_group`) ) CHARSET utf8mb3 COLLATE utf8mb3_general_ci AUTO_INCREMENT 2; -- Modify "kino" table ALTER TABLE `kino` AUTO_INCREMENT 219, MODIFY COLUMN `trailer` text NULL, DROP INDEX `uni_kino_link`, ADD INDEX `cover` (`cover`), DROP FOREIGN KEY `fk_kino_file`, ADD CONSTRAINT `kino_ibfk_1` FOREIGN KEY (`cover`) REFERENCES `files` (`file`) ON UPDATE CASCADE ON DELETE CASCADE; -- Modify "newsSource" table ALTER TABLE `newsSource` AUTO_INCREMENT 17, MODIFY COLUMN `title` mediumtext NOT NULL, MODIFY COLUMN `url` mediumtext NULL, MODIFY COLUMN `hook` enum('newspread','impulsivHook') NULL, ADD INDEX `icon` (`icon`), DROP FOREIGN KEY `fk_newsSource_file`, ADD CONSTRAINT `newsSource_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 `newsSource` (`source`) ON UPDATE RESTRICT ON DELETE RESTRICT, ADD CONSTRAINT `news_ibfk_2` FOREIGN KEY (`file`) REFERENCES `files` (`file`) ON UPDATE CASCADE ON DELETE CASCADE; -- Create "event" table CREATE TABLE `event` ( `event` bigint NOT NULL AUTO_INCREMENT, `news` bigint NULL, `kino` bigint NULL, `file` bigint NULL, `title` varchar(100) CHARSET utf8mb4 NULL COLLATE utf8mb4_unicode_ci, `description` text CHARSET utf8mb4 NULL COLLATE utf8mb4_unicode_ci, `locality` varchar(200) CHARSET utf8mb4 NULL COLLATE utf8mb4_unicode_ci, `link` varchar(200) CHARSET utf8mb4 NULL COLLATE utf8mb4_unicode_ci, `start` datetime NULL, `end` datetime NULL, `ticket_group` bigint NULL, PRIMARY KEY (`event`), INDEX `file` (`file`), INDEX `fkEventGroup` (`ticket_group`), INDEX `fkKino` (`kino`), INDEX `fkNews` (`news`), CONSTRAINT `fkEventFile` FOREIGN KEY (`file`) REFERENCES `files` (`file`) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT `fkEventGroup` FOREIGN KEY (`ticket_group`) REFERENCES `ticket_group` (`ticket_group`) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT `fkKino` FOREIGN KEY (`kino`) REFERENCES `kino` (`kino`) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT `fkNews` FOREIGN KEY (`news`) REFERENCES `news` (`news`) ON UPDATE RESTRICT ON DELETE RESTRICT ) CHARSET utf8mb3 COLLATE utf8mb3_general_ci AUTO_INCREMENT 39; -- 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 RESTRICT ON DELETE RESTRICT, CONSTRAINT `fkLog2UsersAf` FOREIGN KEY (`user_affected`) REFERENCES `users` (`user`) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT `fkLog2UsersEx` FOREIGN KEY (`user_executed`) REFERENCES `users` (`user`) ON UPDATE RESTRICT ON DELETE RESTRICT ) 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 RESTRICT ON DELETE RESTRICT, CONSTRAINT `menu_ibfk_2` FOREIGN KEY (`parent`) REFERENCES `menu` (`menu`) ON UPDATE RESTRICT ON DELETE RESTRICT ) 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 RESTRICT ON DELETE RESTRICT ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Modify "news_alert" table ALTER TABLE `news_alert` 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_alert_file`, ADD CONSTRAINT `news_alert_files_file_fk` FOREIGN KEY (`file`) REFERENCES `files` (`file`) ON UPDATE RESTRICT 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 RESTRICT ON DELETE RESTRICT, ADD CONSTRAINT `notification_ibfk_2` FOREIGN KEY (`location`) REFERENCES `location` (`location`) ON UPDATE RESTRICT ON DELETE RESTRICT; -- 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 RESTRICT ON DELETE RESTRICT, ADD CONSTRAINT `notification_confirmation_ibfk_2` FOREIGN KEY (`device`) REFERENCES `devices` (`device`) ON UPDATE RESTRICT ON DELETE RESTRICT; -- 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 RESTRICT ON DELETE RESTRICT, CONSTRAINT `question2answer_questionAnswers_answer_fk` FOREIGN KEY (`answer`) REFERENCES `questionAnswers` (`answer`) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT `question2answer_question_question_fk` FOREIGN KEY (`question`) REFERENCES `question` (`question`) ON UPDATE RESTRICT ON DELETE RESTRICT ) 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_general_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 RESTRICT ON DELETE RESTRICT, CONSTRAINT `question2faculty_ibfk_2` FOREIGN KEY (`faculty`) REFERENCES `faculty` (`faculty`) ON UPDATE RESTRICT ON DELETE RESTRICT ) 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 RESTRICT ON DELETE RESTRICT ) 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 RESTRICT ON DELETE RESTRICT ) 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 RESTRICT ON DELETE RESTRICT, CONSTRAINT `fkRole` FOREIGN KEY (`role`) REFERENCES `roles` (`role`) ON UPDATE RESTRICT ON DELETE RESTRICT ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "ticket_admin" table CREATE TABLE `ticket_admin` ( `ticket_admin` bigint NOT NULL AUTO_INCREMENT, `key` text CHARSET utf8mb4 NULL COLLATE utf8mb4_unicode_ci, `created` timestamp NOT NULL DEFAULT (current_timestamp()), `active` bool NOT NULL DEFAULT 0, `comment` text CHARSET utf8mb4 NULL COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`ticket_admin`) ) CHARSET utf8mb3 COLLATE utf8mb3_general_ci AUTO_INCREMENT 48; -- Create "ticket_admin2group" table CREATE TABLE `ticket_admin2group` ( `ticket_admin2group` bigint NOT NULL AUTO_INCREMENT, `ticket_admin` bigint NOT NULL, `ticket_group` bigint NOT NULL, PRIMARY KEY (`ticket_admin2group`), INDEX `ticket_admin` (`ticket_admin`), INDEX `ticket_group` (`ticket_group`), CONSTRAINT `fkTicketAdmin` FOREIGN KEY (`ticket_admin`) REFERENCES `ticket_admin` (`ticket_admin`) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT `fkTicketGroup` FOREIGN KEY (`ticket_group`) REFERENCES `ticket_group` (`ticket_group`) ON UPDATE RESTRICT ON DELETE RESTRICT ) CHARSET utf8mb3 COLLATE utf8mb3_general_ci; -- Create "ticket_payment" table CREATE TABLE `ticket_payment` ( `ticket_payment` bigint NOT NULL AUTO_INCREMENT, `name` varchar(50) CHARSET utf8mb4 NULL COLLATE utf8mb4_unicode_ci, `min_amount` int NULL, `max_amount` int NULL, `config` text CHARSET utf8mb4 NULL COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`ticket_payment`) ) CHARSET utf8mb3 COLLATE utf8mb3_general_ci AUTO_INCREMENT 3; -- Create "ticket_type" table CREATE TABLE `ticket_type` ( `ticket_type` bigint NOT NULL AUTO_INCREMENT, `event` bigint NOT NULL, `ticket_payment` bigint NOT NULL, `price` double NOT NULL, `contingent` int NOT NULL, `description` varchar(100) CHARSET utf8mb4 NULL COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`ticket_type`), INDEX `event` (`event`), INDEX `ticket_payment` (`ticket_payment`), CONSTRAINT `fkEvent` FOREIGN KEY (`event`) REFERENCES `event` (`event`) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT `fkPayment` FOREIGN KEY (`ticket_payment`) REFERENCES `ticket_payment` (`ticket_payment`) ON UPDATE RESTRICT ON DELETE RESTRICT ) CHARSET utf8mb3 COLLATE utf8mb3_general_ci AUTO_INCREMENT 57; -- Create "ticket_history" table CREATE TABLE `ticket_history` ( `ticket_history` bigint NOT NULL AUTO_INCREMENT, `member` bigint NOT NULL, `ticket_payment` bigint NULL, `ticket_type` bigint NOT NULL, `purchase` datetime NULL, `redemption` datetime NULL, `created` timestamp NOT NULL DEFAULT (current_timestamp()), `code` char(128) CHARSET utf8mb4 NULL COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`ticket_history`), INDEX `member` (`member`), INDEX `ticket_payment` (`ticket_payment`), INDEX `ticket_type` (`ticket_type`), CONSTRAINT `fkMember` FOREIGN KEY (`member`) REFERENCES `member` (`member`) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT `fkTicketPayment` FOREIGN KEY (`ticket_payment`) REFERENCES `ticket_payment` (`ticket_payment`) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT `fkTicketType` FOREIGN KEY (`ticket_type`) REFERENCES `ticket_type` (`ticket_type`) ON UPDATE RESTRICT ON DELETE RESTRICT ) CHARSET utf8mb3 COLLATE utf8mb3_general_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 RESTRICT ON DELETE RESTRICT ) 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 RESTRICT ON DELETE RESTRICT, CONSTRAINT `fkUser2RolesUser` FOREIGN KEY (`user`) REFERENCES `users` (`user`) ON UPDATE RESTRICT ON DELETE RESTRICT ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; ```
Needed from auto to local migration state ```sql -- Modify "cafeteria_rating" table ALTER TABLE `cafeteria_rating` MODIFY COLUMN `timestamp` timestamp NOT NULL; -- Modify "canteen_head_count" table ALTER TABLE `canteen_head_count` MODIFY COLUMN `timestamp` timestamp NOT NULL; -- Modify "chat_message" table ALTER TABLE `chat_message` DROP FOREIGN KEY `FK_chat_message_chat_room`, DROP FOREIGN KEY `chat_message_ibfk_1`; -- Modify "chat_room2members" table ALTER TABLE `chat_room2members` DROP FOREIGN KEY `FK_chat_room2members_chat_room`, DROP FOREIGN KEY `chat_room2members_ibfk_2`; -- Modify "crontab" table ALTER TABLE `crontab` MODIFY COLUMN `interval` int NULL DEFAULT 7200, MODIFY COLUMN `lastRun` int NULL DEFAULT 0, MODIFY COLUMN `type` enum('news','mensa','movie','roomfinder','alarm','fileDownload','dishNameDownload','iosNotifications','iosActivityReset','canteenHeadCount','newExamResultsHook') NULL; -- Modify "device2stats" table ALTER TABLE `device2stats` DROP FOREIGN KEY `device2stats_ibfk_2`; -- Modify "devices" table ALTER TABLE `devices` MODIFY COLUMN `uuid` varchar(50) NULL, MODIFY COLUMN `lastAccess` timestamp NULL DEFAULT '0000-00-00 00:00:00', MODIFY COLUMN `lastApi` text NULL, MODIFY COLUMN `developer` enum('true','false') NULL DEFAULT 'false', MODIFY COLUMN `osVersion` text NULL, MODIFY COLUMN `appVersion` text NULL, MODIFY COLUMN `counter` int NULL DEFAULT 0, MODIFY COLUMN `pk` text NULL, MODIFY COLUMN `pkActive` enum('true','false') NULL DEFAULT 'false', DROP INDEX `uuid`, DROP FOREIGN KEY `devices_ibfk_1`; -- Modify "dish2dishflags" table ALTER TABLE `dish2dishflags` DROP FOREIGN KEY `dish2dishflags_ibfk_1`, DROP FOREIGN KEY `dish2dishflags_ibfk_2`; -- Modify "dish2mensa" table ALTER TABLE `dish2mensa` DROP FOREIGN KEY `dish2mensa_ibfk_1`, DROP FOREIGN KEY `dish2mensa_ibfk_2`; -- Modify "dish_rating" table ALTER TABLE `dish_rating` MODIFY COLUMN `timestamp` timestamp NOT NULL, ADD INDEX `fk_dish_rating_dish` (`dishID`), DROP FOREIGN KEY `dish_rating_dish_dish_fk`; -- Modify "event" table ALTER TABLE `event` DROP FOREIGN KEY `fkEventFile`, DROP FOREIGN KEY `fkEventGroup`, DROP FOREIGN KEY `fkKino`, DROP FOREIGN KEY `fkNews`; -- Modify "feedback" table ALTER TABLE `feedback` MODIFY COLUMN `email_id` text NOT NULL, MODIFY COLUMN `receiver` text NOT NULL, MODIFY COLUMN `feedback` text NOT NULL, MODIFY COLUMN `latitude` double NULL, MODIFY COLUMN `longitude` double NULL, MODIFY COLUMN `timestamp` timestamp NULL DEFAULT (current_timestamp()); -- Modify "files" table ALTER TABLE `files` MODIFY COLUMN `name` text NULL, MODIFY COLUMN `path` text NULL, MODIFY COLUMN `downloads` int NULL DEFAULT 0, DROP INDEX `url_unique`; -- Modify "kino" table ALTER TABLE `kino` MODIFY COLUMN `trailer` longtext NULL, ADD INDEX `fk_kino_file` (`cover`), ADD UNIQUE INDEX `uni_kino_link` (`link`), DROP FOREIGN KEY `kino_ibfk_1`; -- 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 "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 "newsSource" table ALTER TABLE `newsSource` MODIFY COLUMN `title` text NULL, MODIFY COLUMN `url` text NULL, MODIFY COLUMN `hook` char(1) NULL, ADD INDEX `fk_newsSource_file` (`icon`), DROP FOREIGN KEY `newsSource_ibfk_1`; -- Modify "news_alert" table ALTER TABLE `news_alert` 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_alert_file` (`file`), DROP FOREIGN KEY `news_alert_files_file_fk`; -- 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 "ticket_admin2group" table ALTER TABLE `ticket_admin2group` DROP FOREIGN KEY `fkTicketAdmin`, DROP FOREIGN KEY `fkTicketGroup`; -- Modify "ticket_history" table ALTER TABLE `ticket_history` DROP FOREIGN KEY `fkMember`, DROP FOREIGN KEY `fkTicketPayment`, DROP FOREIGN KEY `fkTicketType`; -- Modify "ticket_type" table ALTER TABLE `ticket_type` DROP FOREIGN KEY `fkEvent`, DROP FOREIGN KEY `fkPayment`; -- 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_rating" table ALTER TABLE `dish_rating` ADD CONSTRAINT `fk_dish_rating_dish` FOREIGN KEY (`dishID`) REFERENCES `dish` (`dish`) ON UPDATE CASCADE ON DELETE CASCADE; -- Modify "kino" table ALTER TABLE `kino` ADD CONSTRAINT `fk_kino_file` FOREIGN KEY (`cover`) REFERENCES `files` (`file`) ON UPDATE RESTRICT ON DELETE RESTRICT; -- 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 `newsSource` (`source`) ON UPDATE CASCADE ON DELETE CASCADE; -- Modify "newsSource" table ALTER TABLE `newsSource` ADD CONSTRAINT `fk_newsSource_file` FOREIGN KEY (`icon`) REFERENCES `files` (`file`) ON UPDATE CASCADE ON DELETE CASCADE; -- Modify "news_alert" table ALTER TABLE `news_alert` ADD CONSTRAINT `fk_news_alert_file` FOREIGN KEY (`file`) 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 "chat_message" table DROP TABLE `chat_message`; -- Drop "chat_room" table DROP TABLE `chat_room`; -- Drop "chat_room2members" table DROP TABLE `chat_room2members`; -- 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 "event" table DROP TABLE `event`; -- 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 "roomfinder_building2area" table DROP TABLE `roomfinder_building2area`; -- Drop "roomfinder_buildings" table DROP TABLE `roomfinder_buildings`; -- Drop "roomfinder_buildings2gps" table DROP TABLE `roomfinder_buildings2gps`; -- Drop "roomfinder_buildings2maps" table DROP TABLE `roomfinder_buildings2maps`; -- Drop "roomfinder_maps" table DROP TABLE `roomfinder_maps`; -- Drop "roomfinder_rooms" table DROP TABLE `roomfinder_rooms`; -- Drop "roomfinder_rooms2maps" table DROP TABLE `roomfinder_rooms2maps`; -- Drop "roomfinder_schedules" table DROP TABLE `roomfinder_schedules`; -- Drop "sessions" table DROP TABLE `sessions`; -- Drop "ticket_admin" table DROP TABLE `ticket_admin`; -- Drop "ticket_admin2group" table DROP TABLE `ticket_admin2group`; -- Drop "ticket_group" table DROP TABLE `ticket_group`; -- Drop "ticket_history" table DROP TABLE `ticket_history`; -- Drop "ticket_payment" table DROP TABLE `ticket_payment`; -- Drop "ticket_type" table DROP TABLE `ticket_type`; -- 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`; ```