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

Table name cleanup #419

Closed CommanderStorm closed 2 months ago

CommanderStorm commented 2 months ago

Resolves https://github.com/TUM-Dev/Campus-Backend/pull/214

github-actions[bot] commented 2 months 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` 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; -- Create "migrations" table CREATE TABLE `migrations` ( `id` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_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; -- Create "update_note" table CREATE TABLE `update_note` ( `version_code` bigint NOT NULL AUTO_INCREMENT, `version_name` text NULL, `message` text NULL, PRIMARY KEY (`version_code`) ) 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; -- 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 "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; -- 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 "dishes_of_the_week" table CREATE TABLE `dishes_of_the_week` ( `dishesOfTheWeek` bigint NOT NULL AUTO_INCREMENT, `year` int NOT NULL, `week` int NOT NULL, `day` int NOT NULL, `dishID` bigint NOT NULL, PRIMARY KEY (`dishesOfTheWeek`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "dish_to_dish_name_tag" table CREATE TABLE `dish_to_dish_name_tag` ( `dishToDishNameTag` bigint NOT NULL AUTO_INCREMENT, `dishID` bigint NOT NULL, `nameTagID` bigint NOT NULL, PRIMARY KEY (`dishToDishNameTag`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 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 "dish_rating_tag_option" table CREATE TABLE `dish_rating_tag_option` ( `dishRatingTagOption` bigint NOT NULL AUTO_INCREMENT, `DE` text NOT NULL DEFAULT (_utf8mb4'de'), `EN` text NOT NULL DEFAULT (_utf8mb4'en'), PRIMARY KEY (`dishRatingTagOption`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci AUTO_INCREMENT 10; -- 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 "cafeteria" table CREATE TABLE `cafeteria` ( `cafeteria` bigint NOT NULL AUTO_INCREMENT, `name` mediumtext NOT NULL, `address` text NOT NULL, `latitude` float NOT NULL, `longitude` float NOT NULL, PRIMARY KEY (`cafeteria`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "cafeteria_rating" table CREATE TABLE `cafeteria_rating` ( `cafeteriaRating` bigint NOT NULL AUTO_INCREMENT, `points` int NOT NULL, `comment` text NULL, `cafeteriaID` bigint NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `image` text NULL, PRIMARY KEY (`cafeteriaRating`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "cafeteria_rating_tag" table CREATE TABLE `cafeteria_rating_tag` ( `CafeteriaRatingTag` bigint NOT NULL AUTO_INCREMENT, `correspondingRating` bigint NOT NULL, `points` int NOT NULL, `tagID` bigint NOT NULL, PRIMARY KEY (`CafeteriaRatingTag`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "cafeteria_rating_tag_option" table CREATE TABLE `cafeteria_rating_tag_option` ( `cafeteriaRatingTagOption` bigint NOT NULL AUTO_INCREMENT, `DE` varchar(191) NOT NULL DEFAULT (_utf8mb4'de'), `EN` varchar(191) NOT NULL DEFAULT (_utf8mb4'en'), PRIMARY KEY (`cafeteriaRatingTagOption`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci AUTO_INCREMENT 10; -- Create "canteen_head_count" table CREATE TABLE `canteen_head_count` ( `canteen_id` varchar(64) NOT NULL, `count` int NOT NULL, `max_count` int NOT NULL, `percent` float NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`canteen_id`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "crontab" table CREATE TABLE `crontab` ( `cron` bigint NOT NULL AUTO_INCREMENT, `interval` int NOT NULL DEFAULT 7200, `lastRun` int NOT NULL DEFAULT 0, `type` enum('news','mensa','chat','kino','roomfinder','ticketsale','alarm','fileDownload','canteenHeadCount') NULL, `id` int NULL, PRIMARY KEY (`cron`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 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 "dish_rating_tag" table CREATE TABLE `dish_rating_tag` ( `dishRatingTag` bigint NOT NULL AUTO_INCREMENT, `parentRating` bigint NOT NULL, `points` int NOT NULL, `tagID` bigint NOT NULL, PRIMARY KEY (`dishRatingTag`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "dish_name_tag_option_included" table CREATE TABLE `dish_name_tag_option_included` ( `dishNameTagOptionIncluded` bigint NOT NULL AUTO_INCREMENT, `nameTagID` bigint NOT NULL, `expression` text NULL, PRIMARY KEY (`dishNameTagOptionIncluded`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci AUTO_INCREMENT 94; -- 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; -- Modify "crontabs" table ALTER TABLE `crontabs` AUTO_INCREMENT 11, MODIFY COLUMN `type` enum('news','mensa','movie','alarm','fileDownload','dishNameDownload','iosNotifications','iosActivityReset','canteenHeadCount','newExamResultsHook','scrapeStudentClubs','feedbackEmail') NULL; -- Create "dish_name_tag" table CREATE TABLE `dish_name_tag` ( `DishNameTag` bigint NOT NULL AUTO_INCREMENT, `correspondingRating` bigint NOT NULL, `points` int NOT NULL, `tagNameID` bigint NOT NULL, PRIMARY KEY (`DishNameTag`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "dish_name_tag_option" table CREATE TABLE `dish_name_tag_option` ( `dishNameTagOption` bigint NOT NULL AUTO_INCREMENT, `DE` text NOT NULL, `EN` text NOT NULL, PRIMARY KEY (`dishNameTagOption`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci AUTO_INCREMENT 69; -- Create "dish_name_tag_option_excluded" table CREATE TABLE `dish_name_tag_option_excluded` ( `dishNameTagOptionExcluded` bigint NOT NULL AUTO_INCREMENT, `nameTagID` bigint NOT NULL, `expression` text NULL, PRIMARY KEY (`dishNameTagOptionExcluded`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci AUTO_INCREMENT 9; -- Modify "devices" table ALTER TABLE `devices` AUTO_INCREMENT 144352, MODIFY COLUMN `uuid` varchar(50) NOT NULL, MODIFY COLUMN `lastAccess` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, MODIFY COLUMN `lastApi` mediumtext NOT NULL DEFAULT (_utf8mb4''), MODIFY COLUMN `developer` enum('true','false') NOT NULL DEFAULT "false", MODIFY COLUMN `osVersion` mediumtext NOT NULL DEFAULT (_utf8mb4''), MODIFY COLUMN `appVersion` mediumtext NOT NULL DEFAULT (_utf8mb4''), 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 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; -- Create "dish" table CREATE TABLE `dish` ( `dish` bigint NOT NULL AUTO_INCREMENT, `name` text NOT NULL, `type` text NOT NULL, `cafeteriaID` bigint NOT NULL, PRIMARY KEY (`dish`), UNIQUE INDEX `dish_name_cafeteriaID_uindex` (`name` (255), `cafeteriaID`) ) 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 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` 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 CURRENT_TIMESTAMP 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 NO ACTION ON DELETE NO ACTION, CONSTRAINT `dish2mensa_ibfk_2` FOREIGN KEY (`dish`) REFERENCES `dish` (`dish`) ON UPDATE NO ACTION ON DELETE NO ACTION ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "dish_rating" table CREATE TABLE `dish_rating` ( `dishRating` bigint NOT NULL AUTO_INCREMENT, `points` int NOT NULL, `dishID` bigint NOT NULL, `comment` text NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `image` text NULL, PRIMARY KEY (`dishRating`), INDEX `dish_rating_dish_dish_fk` (`dishID`), CONSTRAINT `dish_rating_dish_dish_fk` FOREIGN KEY (`dishID`) REFERENCES `dish` (`dish`) ON UPDATE NO ACTION ON DELETE NO ACTION ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 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; -- Create "kino" table CREATE TABLE `kino` ( `kino` bigint NOT NULL AUTO_INCREMENT, `date` datetime NOT NULL, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `title` text NOT NULL, `year` varchar(4) NULL, `runtime` varchar(40) NULL, `genre` varchar(100) NULL, `director` text NULL, `actors` text NULL, `rating` varchar(4) NULL, `description` text NOT NULL, `cover` bigint NULL, `trailer` text NULL, `link` varchar(190) NOT NULL, `location` varchar(191) NULL, PRIMARY KEY (`kino`), INDEX `cover` (`cover`), UNIQUE INDEX `link` (`link`), CONSTRAINT `kino_ibfk_1` FOREIGN KEY (`cover`) REFERENCES `files` (`file`) ON UPDATE CASCADE ON DELETE CASCADE ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci AUTO_INCREMENT 219; -- 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; -- Create "newsSource" table CREATE TABLE `newsSource` ( `source` bigint NOT NULL AUTO_INCREMENT, `title` mediumtext NOT NULL, `url` mediumtext NULL, `icon` bigint NOT NULL, `hook` enum('newspread','impulsivHook') NULL, PRIMARY KEY (`source`), INDEX `icon` (`icon`), CONSTRAINT `newsSource_ibfk_1` FOREIGN KEY (`icon`) REFERENCES `files` (`file`) ON UPDATE CASCADE ON DELETE CASCADE ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci AUTO_INCREMENT 17; -- 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 NO ACTION ON DELETE NO ACTION, ADD CONSTRAINT `news_ibfk_2` FOREIGN KEY (`file`) REFERENCES `files` (`file`) ON UPDATE CASCADE ON DELETE CASCADE; -- Create "news_alert" table CREATE TABLE `news_alert` ( `news_alert` bigint NOT NULL AUTO_INCREMENT, `file` bigint NOT NULL, `name` varchar(100) NULL, `link` text NULL, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `from` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `to` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`news_alert`), INDEX `news_alert_files_file_fk` (`file`), CONSTRAINT `news_alert_files_file_fk` FOREIGN KEY (`file`) REFERENCES `files` (`file`) ON UPDATE NO ACTION ON DELETE CASCADE ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 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; -- Drop "canteen_rating_tags" table DROP TABLE `canteen_rating_tags`; -- Drop "canteen_ratings" table DROP TABLE `canteen_ratings`; -- Drop "canteens" table DROP TABLE `canteens`; -- Drop "dish_name_tag_options" table DROP TABLE `dish_name_tag_options`; -- Drop "dish_name_tags" table DROP TABLE `dish_name_tags`; -- Drop "dish_rating_tag_options" table DROP TABLE `dish_rating_tag_options`; -- Drop "dish_rating_tags" table DROP TABLE `dish_rating_tags`; -- Drop "dish_to_dish_name_tags" table DROP TABLE `dish_to_dish_name_tags`; -- Drop "dishes_of_the_weeks" table DROP TABLE `dishes_of_the_weeks`; -- Drop "excluded_dish_name_tag_options" table DROP TABLE `excluded_dish_name_tag_options`; -- Drop "included_dish_name_tag_options" table DROP TABLE `included_dish_name_tag_options`; -- Drop "movies" table DROP TABLE `movies`; -- Drop "canteen_rating_tag_options" table DROP TABLE `canteen_rating_tag_options`; -- Drop "news_alerts" table DROP TABLE `news_alerts`; -- Drop "canteen_head_counts" table DROP TABLE `canteen_head_counts`; -- Drop "update_notes" table DROP TABLE `update_notes`; -- Drop "dish_ratings" table DROP TABLE `dish_ratings`; -- Drop "dishes" table DROP TABLE `dishes`; -- Drop "news_sources" table DROP TABLE `news_sources`; ```
Needed from auto to local migration state ```sql -- Modify "" schema ALTER DATABASE COLLATE utf8mb4_0900_ai_ci; -- Modify "crontabs" table ALTER TABLE `crontabs` MODIFY COLUMN `type` enum('news','mensa','movie','roomfinder','alarm','fileDownload','dishNameDownload','iosNotifications','iosActivityReset','canteenHeadCount','newExamResultsHook','scrapeStudentClubs') 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 CURRENT_TIMESTAMP, 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` DROP FOREIGN KEY `dish_rating_dish_dish_fk`; -- 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; -- Modify "kino" table ALTER TABLE `kino` 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` DROP FOREIGN KEY `newsSource_ibfk_1`; -- Modify "news_alert" table ALTER TABLE `news_alert` 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 "users2info" table ALTER TABLE `users2info` DROP FOREIGN KEY `fkUsers`; -- Modify "users2roles" table ALTER TABLE `users2roles` DROP FOREIGN KEY `fkUser2RolesRole`, DROP FOREIGN KEY `fkUser2RolesUser`; -- Create "canteen_head_counts" table CREATE TABLE `canteen_head_counts` ( `canteen_id` varchar(64) NOT NULL, `count` int NOT NULL, `max_count` int NOT NULL, `percent` float NOT NULL, `timestamp` timestamp NOT NULL, PRIMARY KEY (`canteen_id`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "canteen_rating_tag_options" table CREATE TABLE `canteen_rating_tag_options` ( `cafeteriaRatingTagOption` bigint NOT NULL AUTO_INCREMENT, `DE` varchar(191) NOT NULL DEFAULT (_utf8mb4'de'), `EN` varchar(191) NOT NULL DEFAULT (_utf8mb4'en'), PRIMARY KEY (`cafeteriaRatingTagOption`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "canteen_rating_tags" table CREATE TABLE `canteen_rating_tags` ( `CanteenRatingTag` bigint NOT NULL AUTO_INCREMENT, `correspondingRating` bigint NOT NULL, `points` int NOT NULL, `tagID` bigint NOT NULL, PRIMARY KEY (`CanteenRatingTag`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "canteen_ratings" table CREATE TABLE `canteen_ratings` ( `cafeteriaRating` bigint NOT NULL AUTO_INCREMENT, `points` int NOT NULL, `comment` text NULL, `cafeteriaID` bigint NOT NULL, `timestamp` timestamp NOT NULL, `image` text NULL, PRIMARY KEY (`cafeteriaRating`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "canteens" table CREATE TABLE `canteens` ( `cafeteria` bigint NOT NULL AUTO_INCREMENT, `name` mediumtext NOT NULL, `address` text NOT NULL, `latitude` float NOT NULL, `longitude` float NOT NULL, PRIMARY KEY (`cafeteria`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "dish_name_tag_options" table CREATE TABLE `dish_name_tag_options` ( `dishNameTagOption` bigint NOT NULL AUTO_INCREMENT, `DE` text NOT NULL, `EN` text NOT NULL, PRIMARY KEY (`dishNameTagOption`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "dish_name_tags" table CREATE TABLE `dish_name_tags` ( `DishNameTag` bigint NOT NULL AUTO_INCREMENT, `correspondingRating` bigint NOT NULL, `points` int NOT NULL, `tagNameID` bigint NOT NULL, PRIMARY KEY (`DishNameTag`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "dish_rating_tag_options" table CREATE TABLE `dish_rating_tag_options` ( `dishRatingTagOption` bigint NOT NULL AUTO_INCREMENT, `DE` text NOT NULL DEFAULT (_utf8mb4'de'), `EN` text NOT NULL DEFAULT (_utf8mb4'en'), PRIMARY KEY (`dishRatingTagOption`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "dish_rating_tags" table CREATE TABLE `dish_rating_tags` ( `dishRatingTag` bigint NOT NULL AUTO_INCREMENT, `parentRating` bigint NOT NULL, `points` int NOT NULL, `tagID` bigint NOT NULL, PRIMARY KEY (`dishRatingTag`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "dish_ratings" table CREATE TABLE `dish_ratings` ( `dishRating` bigint NOT NULL AUTO_INCREMENT, `points` int NOT NULL, `dishID` bigint NOT NULL, `comment` text NULL, `timestamp` timestamp NOT NULL, `image` text NULL, PRIMARY KEY (`dishRating`), INDEX `fk_dish_ratings_dish` (`dishID`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "dish_to_dish_name_tags" table CREATE TABLE `dish_to_dish_name_tags` ( `dishToDishNameTag` bigint NOT NULL AUTO_INCREMENT, `dishID` bigint NOT NULL, `nameTagID` bigint NOT NULL, PRIMARY KEY (`dishToDishNameTag`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "dishes" table CREATE TABLE `dishes` ( `dish` bigint NOT NULL AUTO_INCREMENT, `name` text NOT NULL, `type` text NOT NULL, `cafeteriaID` bigint NOT NULL, PRIMARY KEY (`dish`), UNIQUE INDEX `dish_name_cafeteriaID_uindex` (`name` (255), `cafeteriaID`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "dishes_of_the_weeks" table CREATE TABLE `dishes_of_the_weeks` ( `dishesOfTheWeek` bigint NOT NULL AUTO_INCREMENT, `year` int NOT NULL, `week` int NOT NULL, `day` int NOT NULL, `dishID` bigint NOT NULL, PRIMARY KEY (`dishesOfTheWeek`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "excluded_dish_name_tag_options" table CREATE TABLE `excluded_dish_name_tag_options` ( `dishNameTagOptionExcluded` bigint NOT NULL AUTO_INCREMENT, `nameTagID` bigint NOT NULL, `expression` text NULL, PRIMARY KEY (`dishNameTagOptionExcluded`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "included_dish_name_tag_options" table CREATE TABLE `included_dish_name_tag_options` ( `dishNameTagOptionIncluded` bigint NOT NULL AUTO_INCREMENT, `nameTagID` bigint NOT NULL, `expression` text NULL, PRIMARY KEY (`dishNameTagOptionIncluded`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "movies" table CREATE TABLE `movies` ( `kino` bigint NOT NULL AUTO_INCREMENT, `date` datetime NOT NULL, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `title` text NOT NULL, `year` varchar(4) NULL, `runtime` varchar(40) NULL, `genre` varchar(100) NULL, `director` text NULL, `actors` text NULL, `rating` varchar(4) NULL, `description` text NOT NULL, `trailer` longtext NULL, `cover` bigint NULL, `link` varchar(190) NOT NULL, `location` varchar(191) NULL, PRIMARY KEY (`kino`), INDEX `fk_movies_file` (`cover`), UNIQUE INDEX `uni_movies_link` (`link`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "news_alerts" table CREATE TABLE `news_alerts` ( `news_alert` bigint NOT NULL AUTO_INCREMENT, `file` bigint NOT NULL, `name` varchar(100) NULL, `link` text NULL, `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `from` datetime NULL DEFAULT CURRENT_TIMESTAMP, `to` datetime NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`news_alert`), INDEX `fk_news_alerts_file` (`file`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "news_sources" table CREATE TABLE `news_sources` ( `source` bigint NOT NULL AUTO_INCREMENT, `title` text NULL, `url` text NULL, `icon` bigint NOT NULL, `hook` char(1) NULL, PRIMARY KEY (`source`), INDEX `fk_news_sources_file` (`icon`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create "update_notes" table CREATE TABLE `update_notes` ( `version_code` bigint NOT NULL AUTO_INCREMENT, `version_name` text NULL, `message` text NULL, PRIMARY KEY (`version_code`) ) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 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 "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_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 "cafeteria" table DROP TABLE `cafeteria`; -- Drop "cafeteria_rating" table DROP TABLE `cafeteria_rating`; -- Drop "cafeteria_rating_tag" table DROP TABLE `cafeteria_rating_tag`; -- Drop "cafeteria_rating_tag_option" table DROP TABLE `cafeteria_rating_tag_option`; -- Drop "canteen_head_count" table DROP TABLE `canteen_head_count`; -- Drop "crontab" table DROP TABLE `crontab`; -- Drop "curricula" table DROP TABLE `curricula`; -- Drop "device2stats" table DROP TABLE `device2stats`; -- Drop "dish" table DROP TABLE `dish`; -- Drop "dish2dishflags" table DROP TABLE `dish2dishflags`; -- Drop "dish2mensa" table DROP TABLE `dish2mensa`; -- Drop "dish_name_tag" table DROP TABLE `dish_name_tag`; -- Drop "dish_name_tag_option" table DROP TABLE `dish_name_tag_option`; -- Drop "dish_name_tag_option_excluded" table DROP TABLE `dish_name_tag_option_excluded`; -- Drop "dish_name_tag_option_included" table DROP TABLE `dish_name_tag_option_included`; -- Drop "dish_rating" table DROP TABLE `dish_rating`; -- Drop "dish_rating_tag" table DROP TABLE `dish_rating_tag`; -- Drop "dish_rating_tag_option" table DROP TABLE `dish_rating_tag_option`; -- Drop "dish_to_dish_name_tag" table DROP TABLE `dish_to_dish_name_tag`; -- Drop "dishes_of_the_week" table DROP TABLE `dishes_of_the_week`; -- Drop "dishflags" table DROP TABLE `dishflags`; -- Drop "faculty" table DROP TABLE `faculty`; -- Drop "kino" table DROP TABLE `kino`; -- 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 "newsSource" table DROP TABLE `newsSource`; -- Drop "news_alert" table DROP TABLE `news_alert`; -- 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 "update_note" table DROP TABLE `update_note`; -- 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`; ```