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

Unique `dish.{name, cafeteriaID}` #325

Closed CommanderStorm closed 8 months ago

CommanderStorm commented 8 months ago

During https://github.com/TUM-Dev/Campus-Backend/pull/314#pullrequestreview-1924563994 you noted that updating the type is likely a better idea. This PR implements this change.

Note that because dish-names are currently not unique, many duplicates in the database exist. The following SQL query should, as far as I can tell, update all FK-relationships.

Now to the interesting question:

Should dish_rating.dishID be a FK to dish.dish

```sql alter table dish_rating add constraint dish_rating_dish_dish_fk foreign key (dishID) references dish (dish) on delete cascade; # because dishes already have a cafeteria, storing this agiain is not nessesary alter table dish_rating drop column cafeteriaID; ``` 

With relabling as (SELECT COUNT(*), name, cafeteriaID, min(dish) as group_dish_id
                   FROM dish
                   GROUP BY name, cafeteriaID
                   HAVING COUNT(*) > 1
                   ORDER BY COUNT(*) desc),
     dishes_to_relabel as (SELECT DISTINCT dish as old_id, group_dish_id as new_id
                           FROM relabling r,
                                dish d
                           WHERE r.name = d.name
                             and r.cafeteriaID = d.cafeteriaID
                             and r.group_dish_id != d.dish)

update dish2dishflags d2f
set dish = (SELECT r.new_id
            from dishes_to_relabel r
            WHERE r.old_id = d2f.dish);
update dish2mensa d2m
set dish = (SELECT r.new_id
            from dishes_to_relabel r
            WHERE r.old_id = d2m.dish);

# dish_rating does NOT have a FK-set => intentional?
update dish_rating dr
set dr.dishID = (SELECT r.new_id
                 from dishes_to_relabel r
                 WHERE r.old_id = dr.dishID);
update dish_to_dish_name_tag dt
set dt.dishID = (SELECT r.new_id
                 from dishes_to_relabel r
                 WHERE r.old_id = dt.dishID);
update dishes_of_the_week dw
set dw.dishID = (SELECT r.new_id
                 from dishes_to_relabel r
                 WHERE r.old_id = dw.dishID);

update dish d
set d.dish = (SELECT r.new_id
                 from dishes_to_relabel r
                 WHERE r.old_id = d.dish);
github-actions[bot] commented 8 months ago

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

Needed get from local to auto migration state ```sql -- 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` COLLATE utf8mb4_general_ci, MODIFY COLUMN `cron` bigint NOT NULL AUTO_INCREMENT, 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, ADD PRIMARY KEY (`cron`), DROP INDEX `cron`; -- Modify "device2stats" table ALTER TABLE `device2stats` DROP FOREIGN KEY `device2stats_ibfk_2`; -- Modify "devices" table ALTER TABLE `devices` COLLATE utf8mb4_general_ci, MODIFY COLUMN `device` bigint NOT NULL AUTO_INCREMENT, MODIFY COLUMN `member` bigint NULL, 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', MODIFY COLUMN `gcmToken` text NULL, MODIFY COLUMN `gcmStatus` varchar(200) NULL, MODIFY COLUMN `confirmationKey` varchar(35) NULL, DROP INDEX `uuid`, DROP FOREIGN KEY `devices_ibfk_1`; -- Modify "dish" table ALTER TABLE `dish` COLLATE utf8mb4_general_ci, MODIFY COLUMN `dish` bigint NOT NULL AUTO_INCREMENT, MODIFY COLUMN `name` text NOT NULL, MODIFY COLUMN `type` text NOT NULL; -- 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 `dishID` bigint 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 `id` bigint NOT NULL AUTO_INCREMENT, MODIFY COLUMN `email_id` text NOT NULL, MODIFY COLUMN `receiver` text NOT NULL, MODIFY COLUMN `reply_to` text 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` COLLATE utf8mb4_general_ci, MODIFY COLUMN `file` bigint NOT NULL AUTO_INCREMENT, MODIFY COLUMN `name` text NULL, MODIFY COLUMN `path` text NULL, MODIFY COLUMN `downloads` int NULL DEFAULT 0, MODIFY COLUMN `url` varchar(191) NULL; -- Modify "kino" table ALTER TABLE `kino` COLLATE utf8mb4_general_ci, MODIFY COLUMN `kino` bigint NOT NULL AUTO_INCREMENT, MODIFY COLUMN `title` text NOT NULL, MODIFY COLUMN `year` varchar(4) NULL, MODIFY COLUMN `runtime` varchar(40) NULL, MODIFY COLUMN `genre` varchar(100) NULL, MODIFY COLUMN `director` text NULL, MODIFY COLUMN `actors` text NULL, MODIFY COLUMN `rating` varchar(4) NULL, MODIFY COLUMN `description` text NOT NULL, MODIFY COLUMN `cover` bigint NULL, MODIFY COLUMN `trailer` longtext NULL, MODIFY COLUMN `link` varchar(190) NOT NULL, MODIFY COLUMN `location` varchar(191) 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` COLLATE utf8mb4_general_ci, MODIFY COLUMN `news` bigint NOT NULL AUTO_INCREMENT, 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, MODIFY COLUMN `image` text NULL, MODIFY COLUMN `file` bigint 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` COLLATE utf8mb4_general_ci, MODIFY COLUMN `source` bigint NOT NULL AUTO_INCREMENT, MODIFY COLUMN `title` text NULL, MODIFY COLUMN `url` text NULL, MODIFY COLUMN `icon` bigint NOT 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 `news_alert` bigint NOT NULL AUTO_INCREMENT, MODIFY COLUMN `file` bigint NOT NULL, 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 `FK_File`, ADD INDEX `fk_news_alert_file` (`file`); -- Modify "notification" table ALTER TABLE `notification` DROP FOREIGN KEY `notification_ibfk_1`, DROP FOREIGN KEY `notification_ibfk_2`; -- Modify "notification_confirmation" table ALTER TABLE `notification_confirmation` DROP FOREIGN KEY `notification_confirmation_ibfk_1`, DROP FOREIGN KEY `notification_confirmation_ibfk_2`; -- 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 "update_note" table ALTER TABLE `update_note` MODIFY COLUMN `version_code` bigint NOT NULL AUTO_INCREMENT; -- Modify "users2info" table ALTER TABLE `users2info` DROP FOREIGN KEY `fkUsers`; -- Modify "users2roles" table ALTER TABLE `users2roles` DROP FOREIGN KEY `fkUser2RolesRole`, DROP FOREIGN KEY `fkUser2RolesUser`; -- Create "notifications" table CREATE TABLE `notifications` ( `notification` bigint NOT NULL AUTO_INCREMENT, `type` int NULL, `location` bigint NULL, `title` text NULL, `description` text NULL, `created` timestamp NULL DEFAULT (current_timestamp()), `signature` text NULL, `silent` tinyint NULL DEFAULT 0, PRIMARY KEY (`notification`) ) CHARSET utf8mb4 COLLATE utf8mb4_general_ci; -- Create "notification_confirmations" table CREATE TABLE `notification_confirmations` ( `notification` bigint NOT NULL, `device` bigint NOT NULL, `sent` tinyint NULL DEFAULT 0, `created` timestamp NULL DEFAULT (current_timestamp()), `received` timestamp NULL, PRIMARY KEY (`notification`, `device`) ) CHARSET utf8mb4 COLLATE utf8mb4_general_ci; -- Create "notification_types" table CREATE TABLE `notification_types` ( `type` bigint NOT NULL AUTO_INCREMENT, `name` text NULL, `confirmation` enum('true','false') NULL DEFAULT 'false', PRIMARY KEY (`type`) ) CHARSET utf8mb4 COLLATE utf8mb4_general_ci; -- 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 "notification" table DROP TABLE `notification`; -- Drop "notification_confirmation" table DROP TABLE `notification_confirmation`; -- Drop "notification_type" table DROP TABLE `notification_type`; -- 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`; ```
Needed from auto to local migration state ```sql -- 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` COLLATE utf8mb4_general_ci, MODIFY COLUMN `cron` bigint NOT NULL AUTO_INCREMENT, 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, ADD PRIMARY KEY (`cron`), DROP INDEX `cron`; -- Modify "device2stats" table ALTER TABLE `device2stats` DROP FOREIGN KEY `device2stats_ibfk_2`; -- Modify "devices" table ALTER TABLE `devices` COLLATE utf8mb4_general_ci, MODIFY COLUMN `device` bigint NOT NULL AUTO_INCREMENT, MODIFY COLUMN `member` bigint NULL, 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', MODIFY COLUMN `gcmToken` text NULL, MODIFY COLUMN `gcmStatus` varchar(200) NULL, MODIFY COLUMN `confirmationKey` varchar(35) NULL, DROP INDEX `uuid`, DROP FOREIGN KEY `devices_ibfk_1`; -- Modify "dish" table ALTER TABLE `dish` COLLATE utf8mb4_general_ci, MODIFY COLUMN `dish` bigint NOT NULL AUTO_INCREMENT, MODIFY COLUMN `name` text NOT NULL, MODIFY COLUMN `type` text NOT NULL; -- 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 `dishID` bigint 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 `id` bigint NOT NULL AUTO_INCREMENT, MODIFY COLUMN `email_id` text NOT NULL, MODIFY COLUMN `receiver` text NOT NULL, MODIFY COLUMN `reply_to` text 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` COLLATE utf8mb4_general_ci, MODIFY COLUMN `file` bigint NOT NULL AUTO_INCREMENT, MODIFY COLUMN `name` text NULL, MODIFY COLUMN `path` text NULL, MODIFY COLUMN `downloads` int NULL DEFAULT 0, MODIFY COLUMN `url` varchar(191) NULL; -- Modify "kino" table ALTER TABLE `kino` COLLATE utf8mb4_general_ci, MODIFY COLUMN `kino` bigint NOT NULL AUTO_INCREMENT, MODIFY COLUMN `title` text NOT NULL, MODIFY COLUMN `year` varchar(4) NULL, MODIFY COLUMN `runtime` varchar(40) NULL, MODIFY COLUMN `genre` varchar(100) NULL, MODIFY COLUMN `director` text NULL, MODIFY COLUMN `actors` text NULL, MODIFY COLUMN `rating` varchar(4) NULL, MODIFY COLUMN `description` text NOT NULL, MODIFY COLUMN `cover` bigint NULL, MODIFY COLUMN `trailer` longtext NULL, MODIFY COLUMN `link` varchar(190) NOT NULL, MODIFY COLUMN `location` varchar(191) 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` COLLATE utf8mb4_general_ci, MODIFY COLUMN `news` bigint NOT NULL AUTO_INCREMENT, 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, MODIFY COLUMN `image` text NULL, MODIFY COLUMN `file` bigint 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` COLLATE utf8mb4_general_ci, MODIFY COLUMN `source` bigint NOT NULL AUTO_INCREMENT, MODIFY COLUMN `title` text NULL, MODIFY COLUMN `url` text NULL, MODIFY COLUMN `icon` bigint NOT 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 `news_alert` bigint NOT NULL AUTO_INCREMENT, MODIFY COLUMN `file` bigint NOT NULL, 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 `FK_File`, ADD INDEX `fk_news_alert_file` (`file`); -- Modify "notification" table ALTER TABLE `notification` DROP FOREIGN KEY `notification_ibfk_1`, DROP FOREIGN KEY `notification_ibfk_2`; -- Modify "notification_confirmation" table ALTER TABLE `notification_confirmation` DROP FOREIGN KEY `notification_confirmation_ibfk_1`, DROP FOREIGN KEY `notification_confirmation_ibfk_2`; -- 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 "update_note" table ALTER TABLE `update_note` MODIFY COLUMN `version_code` bigint NOT NULL AUTO_INCREMENT; -- Modify "users2info" table ALTER TABLE `users2info` DROP FOREIGN KEY `fkUsers`; -- Modify "users2roles" table ALTER TABLE `users2roles` DROP FOREIGN KEY `fkUser2RolesRole`, DROP FOREIGN KEY `fkUser2RolesUser`; -- Create "notifications" table CREATE TABLE `notifications` ( `notification` bigint NOT NULL AUTO_INCREMENT, `type` int NULL, `location` bigint NULL, `title` text NULL, `description` text NULL, `created` timestamp NULL DEFAULT (current_timestamp()), `signature` text NULL, `silent` tinyint NULL DEFAULT 0, PRIMARY KEY (`notification`) ) CHARSET utf8mb4 COLLATE utf8mb4_general_ci; -- Create "notification_confirmations" table CREATE TABLE `notification_confirmations` ( `notification` bigint NOT NULL, `device` bigint NOT NULL, `sent` tinyint NULL DEFAULT 0, `created` timestamp NULL DEFAULT (current_timestamp()), `received` timestamp NULL, PRIMARY KEY (`notification`, `device`) ) CHARSET utf8mb4 COLLATE utf8mb4_general_ci; -- Create "notification_types" table CREATE TABLE `notification_types` ( `type` bigint NOT NULL AUTO_INCREMENT, `name` text NULL, `confirmation` enum('true','false') NULL DEFAULT 'false', PRIMARY KEY (`type`) ) CHARSET utf8mb4 COLLATE utf8mb4_general_ci; -- 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 "notification" table DROP TABLE `notification`; -- Drop "notification_confirmation" table DROP TABLE `notification_confirmation`; -- Drop "notification_type" table DROP TABLE `notification_type`; -- 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`; ```