create or replace table actions ( action int auto_increment primary key, name varchar(50) not null, description mediumtext not null, color varchar(6) not null ) collate = utf8mb4_unicode_ci auto_increment = 19; create or replace table alarm_ban ( ban int auto_increment primary key, created timestamp /* mariadb-5.3 */ default current_timestamp() not null, ip binary(16) not null, constraint ip unique (ip) ) collate = utf8mb4_unicode_ci; create or replace table alarm_log ( alarm int auto_increment primary key, created timestamp /* mariadb-5.3 */ default current_timestamp() not null, message text not null, send int not null, received int not null, test enum ('true', 'false') default 'false' not null, ip binary(16) not null ) collate = utf8mb4_unicode_ci; create or replace table barrierFree_moreInfo ( id int(11) unsigned auto_increment primary key, title varchar(32) null, category varchar(11) null, url varchar(128) null ) charset = utf8 auto_increment = 11; create or replace table barrierFree_persons ( id int(11) unsigned auto_increment primary key, name varchar(40) null, telephone varchar(32) null, email varchar(32) null, faculty varchar(32) null, office varchar(16) null, officeHour varchar(16) null, tumID varchar(24) null ) charset = utf8 auto_increment = 19; create or replace table card_type ( card_type int auto_increment primary key, title varchar(255) null ) auto_increment = 2; create or replace table chat_room ( room int auto_increment primary key, name varchar(100) not null, semester varchar(3) null, constraint `Index 2` unique (semester, name) ) collate = utf8mb4_unicode_ci auto_increment = 1724450; create or replace table crontab ( cron int auto_increment, `interval` int default 7200 not null, lastRun int default 0 not null, type enum ('news', 'mensa', 'chat', 'kino', 'roomfinder', 'ticketsale', 'alarm', 'fileDownload') null, id int null, constraint cron unique (cron) ) collate = utf8mb4_unicode_ci auto_increment = 44; create or replace table curricula ( curriculum int auto_increment primary key, category enum ('bachelor', 'master') default 'bachelor' not null, name mediumtext not null, url mediumtext not null ) collate = utf8mb4_unicode_ci auto_increment = 16; create or replace table dish ( dish int auto_increment primary key, name varchar(150) not null, type varchar(20) not null ) collate = utf8mb4_unicode_ci; create or replace table dishflags ( flag int auto_increment primary key, short varchar(10) not null, description varchar(50) not null ) collate = utf8mb4_unicode_ci; create or replace table dish2dishflags ( dish2dishflags int auto_increment primary key, dish int not null, flag int not null, constraint dish unique (dish, flag), constraint dish2dishflags_ibfk_1 foreign key (dish) references dish (dish) on update cascade on delete cascade, constraint dish2dishflags_ibfk_2 foreign key (flag) references dishflags (flag) on update cascade on delete cascade ) collate = utf8mb4_unicode_ci; create or replace index flag on dish2dishflags (flag); create or replace table faculty ( faculty int auto_increment primary key, name varchar(150) not null, constraint name unique (name) ) charset = utf8mb4 auto_increment = 18; create or replace table feedback ( id int auto_increment primary key, email_id text charset utf8 null, receiver text charset utf8 null, reply_to text charset utf8 null, feedback text charset utf8 null, image_count int null, latitude decimal(11, 8) null, longitude decimal(11, 8) null, timestamp datetime /* mariadb-5.3 */ default current_timestamp() null ) auto_increment = 293; create or replace table files ( file int auto_increment primary key, name mediumtext not null, path mediumtext not null, downloads int default 0 not null, url varchar(191) null, downloaded tinyint(1) default 1 null ) collate = utf8mb4_unicode_ci auto_increment = 34761; create or replace table kino ( kino int auto_increment primary key, date datetime /* mariadb-5.3 */ not null, created timestamp /* mariadb-5.3 */ default current_timestamp() not null, title text not null, year varchar(4) not null, runtime varchar(40) not null, genre varchar(100) not null, director text not null, actors text not null, rating varchar(4) not null, description text not null, cover int null, trailer text null, link varchar(190) not null, constraint link unique (link), constraint kino_ibfk_1 foreign key (cover) references files (file) on update cascade on delete set null ) collate = utf8mb4_unicode_ci auto_increment = 219; create or replace index cover on kino (cover); create or replace table lecture ( lecture int auto_increment primary key, title varchar(255) null ); create or replace table location ( location int auto_increment primary key, name text not null, lon float(10, 6) not null, lat float(10, 6) not null, radius int default 1000 not null comment 'in meters' ) charset = utf8 auto_increment = 2; create or replace table member ( member int auto_increment primary key, lrz_id varchar(7) not null, name varchar(150) not null, active_day int default 0 null, active_day_date date null, student_id text null, employee_id text null, external_id text null, constraint lrz_id unique (lrz_id) ) collate = utf8mb4_unicode_ci auto_increment = 104353; create or replace table card ( card int auto_increment primary key, member int null, lecture int null, card_type int null, title varchar(255) null, front_text varchar(2000) null, front_image varchar(2000) null, back_text varchar(2000) null, back_image varchar(2000) null, can_shift tinyint(1) default 0 null, created_at date not null, updated_at date not null, duplicate_card int null, aggr_rating float default 0 null, constraint card_ibfk_1 foreign key (member) references member (member) on delete set null, constraint card_ibfk_2 foreign key (lecture) references lecture (lecture) on delete set null, constraint card_ibfk_3 foreign key (card_type) references card_type (card_type) on delete set null, constraint card_ibfk_4 foreign key (duplicate_card) references card (card) on delete set null ); create or replace index card_type on card (card_type); create or replace index duplicate_card on card (duplicate_card); create or replace index lecture on card (lecture); create or replace index member on card (member); create or replace table card_box ( card_box int auto_increment primary key, member int null, title varchar(255) null, duration int not null, constraint card_box_ibfk_1 foreign key (member) references member (member) on delete cascade ) auto_increment = 6; create or replace index member on card_box (member); create or replace table card_comment ( card_comment int auto_increment primary key, member int null, card int not null, rating int default 0 null, created_at date not null, constraint card_comment_ibfk_1 foreign key (member) references member (member) on delete set null, constraint card_comment_ibfk_2 foreign key (card) references card (card) on delete cascade ); create or replace index card on card_comment (card); create or replace index member on card_comment (member); create or replace table card_option ( card_option int auto_increment primary key, card int not null, text varchar(2000) default '' null, is_correct_answer tinyint(1) default 0 null, sort_order int default 0 not null, image varchar(2000) null, constraint card_option_ibfk_1 foreign key (card) references card (card) on delete cascade ); create or replace index card on card_option (card); create or replace table chat_message ( message int auto_increment primary key, member int not null, room int not null, text longtext not null, created datetime /* mariadb-5.3 */ not null, signature longtext not null, constraint FK_chat_message_chat_room foreign key (room) references chat_room (room) on update cascade on delete cascade, constraint chat_message_ibfk_1 foreign key (member) references member (member) on update cascade on delete cascade ) collate = utf8mb4_unicode_ci auto_increment = 1977; create or replace index chat_message_b3c09425 on chat_message (member); create or replace index chat_message_ca20ebca on chat_message (room); create or replace table chat_room2members ( room2members int auto_increment primary key, room int not null, member int not null, constraint chatroom_id unique (room, member), constraint FK_chat_room2members_chat_room foreign key (room) references chat_room (room) on update cascade on delete cascade, constraint chat_room2members_ibfk_2 foreign key (member) references member (member) on update cascade on delete cascade ) collate = utf8mb4_unicode_ci auto_increment = 63377426; create or replace index chat_chatroom_members_29801a33 on chat_room2members (room); create or replace index chat_chatroom_members_b3c09425 on chat_room2members (member); create or replace table devices ( device int auto_increment primary key, member int null, uuid varchar(50) not null, created timestamp /* mariadb-5.3 */ null, lastAccess timestamp /* mariadb-5.3 */ default '0000-00-00 00:00:00' not null on update current_timestamp(), lastApi mediumtext default '' not null, developer enum ('true', 'false') default 'false' not null, osVersion mediumtext default '' not null, appVersion mediumtext default '' not null, counter int default 0 not null, pk longtext null, pkActive enum ('true', 'false') default 'false' not null, gcmToken text null, gcmStatus varchar(200) null, confirmationKey varchar(35) null, keyCreated datetime null, keyConfirmed datetime null, constraint uuid unique (uuid), constraint devices_ibfk_1 foreign key (member) references member (member) on update cascade on delete cascade ) collate = utf8mb4_unicode_ci auto_increment = 144352; create or replace table device2stats ( device int default 0 not null primary key, LecturesPersonalActivity int default 0 not null, CafeteriaActivity int default 0 not null, WizNavStartActivity int default 0 not null, NewsActivity int default 0 not null, StartupActivity int default 0 not null, MainActivity int default 0 not null, ChatRoomsActivity int default 0 not null, CalendarActivity int default 0 not null, WizNavCheckTokenActivity int default 0 not null, ChatActivity int default 0 not null, CurriculaActivity int default 0 not null, CurriculaDetailsActivity int default 0 not null, GradeChartActivity int default 0 not null, GradesActivity int default 0 not null, InformationActivity int default 0 not null, LecturesAppointmentsActivity int default 0 not null, LecturesDetailsActivity int default 0 not null, OpeningHoursDetailActivity int default 0 not null, OpeningHoursListActivity int default 0 not null, OrganisationActivity int default 0 not null, OrganisationDetailsActivity int default 0 not null, PersonsDetailsActivity int default 0 not null, PersonsSearchActivity int default 0 not null, PlansActivity int default 0 not null, PlansDetailsActivity int default 0 not null, RoomFinderActivity int default 0 not null, RoomFinderDetailsActivity int default 0 not null, SetupEduroamActivity int default 0 not null, TransportationActivity int default 0 not null, TransportationDetailsActivity int default 0 not null, TuitionFeesActivity int default 0 not null, UserPreferencesActivity int default 0 not null, WizNavExtrasActivity int default 0 not null, WizNavChatActivity int default 0 not null, TuitionFeesCard int default 0 not null, NextLectureCard int default 0 not null, CafeteriaMenuCard int default 0 not null, NewsCard1 int default 0 not null, NewsCard2 int default 0 not null, NewsCard3 int default 0 not null, NewsCard7 int default 0 not null, constraint device2stats_ibfk_2 foreign key (device) references devices (device) on update cascade on delete cascade ) collate = utf8mb4_unicode_ci; create or replace index member on devices (member); create or replace table members_card ( member int not null, card int not null, card_box int null, last_answered_active_day int null, primary key (member, card), constraint members_card_ibfk_1 foreign key (member) references member (member) on delete cascade, constraint members_card_ibfk_2 foreign key (card) references card (card) on delete cascade, constraint members_card_ibfk_3 foreign key (card_box) references card_box (card_box) on delete set null ); create or replace index card on members_card (card); create or replace index card_box on members_card (card_box); create or replace table members_card_answer_history ( members_card_answer_history int auto_increment primary key, member int not null, card int null, card_box int null, answer varchar(2000) null, answer_score float(10, 2) default 0.00 null, created_at date not null, constraint members_card_answer_history_ibfk_1 foreign key (member) references member (member) on delete cascade, constraint members_card_answer_history_ibfk_2 foreign key (card) references card (card) on delete set null, constraint members_card_answer_history_ibfk_3 foreign key (card_box) references card_box (card_box) on delete set null ); create or replace index card on members_card_answer_history (card); create or replace index card_box on members_card_answer_history (card_box); create or replace index member on members_card_answer_history (member); create or replace table mensa ( mensa int auto_increment primary key, id int null, name mediumtext not null, address mediumtext not null, latitude float(10, 6) default 0.000000 not null, longitude float(10, 6) default 0.000000 not null, constraint id unique (id) ) collate = utf8mb4_unicode_ci auto_increment = 17; create or replace table dish2mensa ( dish2mensa int auto_increment primary key, mensa int not null, dish int not null, date date not null, created datetime /* mariadb-5.3 */ not null, modifierd timestamp /* mariadb-5.3 */ default '0000-00-00 00:00:00' not null on update current_timestamp(), constraint dish2mensa_ibfk_1 foreign key (mensa) references mensa (mensa) on update cascade on delete cascade, constraint dish2mensa_ibfk_2 foreign key (dish) references dish (dish) on update cascade on delete cascade ) collate = utf8mb4_unicode_ci; create or replace index dish on dish2mensa (dish); create or replace index mensa on dish2mensa (mensa); create or replace table mensaplan_mensa ( id int auto_increment primary key, name varchar(100) not null, latitude double null, longitude double null, webid int null, category varchar(50) not null ) engine = MyISAM collate = utf8mb4_unicode_ci auto_increment = 30; create or replace table mensaprices ( price int auto_increment primary key, created timestamp /* mariadb-5.3 */ default current_timestamp() not null, person mediumtext not null, type mediumtext not null, typeLong mediumtext not null, typeNumber int not null, value decimal not null ) collate = utf8mb4_unicode_ci; create or replace table migrations ( id varchar(255) not null primary key ); create or replace table newsSource ( source int auto_increment primary key, title mediumtext not null, url mediumtext null, icon int null, hook enum ('newspread', 'impulsivHook') null, constraint newsSource_ibfk_1 foreign key (icon) references files (file) on update cascade on delete set null ) collate = utf8mb4_unicode_ci auto_increment = 17; create or replace table news ( news int auto_increment primary key, date datetime /* mariadb-5.3 */ not null, created timestamp /* mariadb-5.3 */ default current_timestamp() not null, title tinytext not null, description text not null, src int not null, link varchar(190) not null, image text null, file int null, constraint link unique (link), constraint news_ibfk_1 foreign key (src) references newsSource (source) on update cascade on delete cascade, constraint news_ibfk_2 foreign key (file) references files (file) on update cascade on delete set null ) collate = utf8mb4_unicode_ci auto_increment = 770113; create or replace index file on news (file); create or replace index src on news (src); create or replace index icon on newsSource (icon); create or replace table news_alert ( news_alert int auto_increment primary key, file int null, name varchar(100) null, link text null, created timestamp /* mariadb-5.3 */ default current_timestamp() not null, `from` datetime /* mariadb-5.3 */ default current_timestamp() not null, `to` datetime /* mariadb-5.3 */ default current_timestamp() not null, constraint FK_File unique (file) ) charset = utf8mb4 auto_increment = 7; create or replace table notification_type ( type int auto_increment primary key, name text not null, confirmation enum ('true', 'false') default 'false' not null ) charset = utf8 auto_increment = 4; create or replace table notification ( notification int auto_increment primary key, type int not null, location int null, title text not null, description text not null, created timestamp /* mariadb-5.3 */ default current_timestamp() not null, signature text null, silent tinyint(1) default 0 not null, constraint notification_ibfk_1 foreign key (type) references notification_type (type) on update cascade on delete cascade, constraint notification_ibfk_2 foreign key (location) references location (location) on update cascade on delete set null ) charset = utf8 auto_increment = 107; create or replace index location on notification (location); create or replace index type on notification (type); create or replace table notification_confirmation ( notification int not null, device int not null, sent tinyint(1) default 0 not null, created timestamp /* mariadb-5.3 */ default current_timestamp() not null, received timestamp /* mariadb-5.3 */ null, primary key (notification, device), constraint notification_confirmation_ibfk_1 foreign key (notification) references notification (notification), constraint notification_confirmation_ibfk_2 foreign key (device) references devices (device) ) charset = utf8; create or replace index device on notification_confirmation (device); create or replace table openinghours ( id int auto_increment primary key, 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) default 'de' null, reference_id int default -1 null ) collate = utf8mb4_unicode_ci auto_increment = 113; create or replace table question ( question int auto_increment primary key, member int not null, text text not null, created timestamp /* mariadb-5.3 */ default current_timestamp() not null, end timestamp /* mariadb-5.3 */ null ) auto_increment = 282; create or replace index member on question (member); create or replace table question2answer ( question int not null, answer int not null, member int not null, constraint question unique (question, member) ); create or replace table question2faculty ( question int not null, faculty int not null, primary key (question, faculty), constraint question2faculty_ibfk_1 foreign key (question) references question (question) on update cascade on delete cascade, constraint question2faculty_ibfk_2 foreign key (faculty) references faculty (faculty) on update cascade on delete cascade ); create or replace index faculty on question2faculty (faculty); create or replace table questionAnswers ( answer int auto_increment primary key, text text not null ) auto_increment = 3; create or replace table reports ( report int auto_increment primary key, device int null, created timestamp /* mariadb-5.3 */ default current_timestamp() not null, fixed enum ('true', 'false') default 'false' not null, issue int null, stacktrace mediumtext not null, stacktraceGroup text null, log mediumtext not null, package mediumtext not null, packageVersion mediumtext not null, packageVersionCode int default -1 not null, 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, constraint reports_ibfk_3 foreign key (device) references devices (device) on update cascade on delete set null ) collate = utf8mb4_unicode_ci; create or replace index device on reports (device); create or replace table rights ( `right` int auto_increment primary key, name varchar(100) null, description mediumtext not null, category int default 0 not null, constraint Unquie unique (name) ) collate = utf8mb4_unicode_ci auto_increment = 14; create or replace table menu ( menu int auto_increment primary key, `right` int null, parent int null, name varchar(255) null, path varchar(255) null, target enum ('_blank', '_self', '_parent', '_top') default '_self' not null, icon varchar(200) not null, class varchar(200) not null, position int default 0 not null, constraint menu_ibfk_1 foreign key (`right`) references rights (`right`) on update cascade on delete set null, constraint menu_ibfk_2 foreign key (parent) references menu (menu) on update cascade on delete set null ) collate = utf8mb4_unicode_ci auto_increment = 25; create or replace index parent on menu (parent); create or replace index `right` on menu (`right`); create or replace table modules ( module int auto_increment primary key, name varchar(255) null, `right` int null, constraint fkMod2Rights foreign key (`right`) references rights (`right`) on update cascade on delete set null ) collate = utf8mb4_unicode_ci auto_increment = 31; create or replace index module_right on modules (`right`); create or replace table roles ( role int auto_increment primary key, name varchar(50) not null, description mediumtext not null ) collate = utf8mb4_unicode_ci auto_increment = 6; create or replace table roles2rights ( role int not null, `right` int not null, primary key (role, `right`), constraint fkRight foreign key (`right`) references rights (`right`) on delete cascade, constraint fkRole foreign key (role) references roles (role) on delete cascade ) collate = utf8mb4_unicode_ci; create or replace index fkRight_idx on roles2rights (`right`); create or replace table roomfinder_building2area ( area_id int not null, building_nr varchar(8) not null primary key, campus char not null, name varchar(32) not null ) charset = utf8mb4; grant select on table roomfinder_building2area to 'tca-students'; create or replace table roomfinder_buildings ( building_nr varchar(8) not null primary key, utm_zone varchar(4) null, utm_easting varchar(32) null, utm_northing varchar(32) null, default_map_id int null ) charset = utf8mb4; grant select on table roomfinder_buildings to 'tca-students'; create or replace table roomfinder_buildings2gps ( id varchar(8) default '' not null primary key, latitude varchar(30) null, longitude varchar(30) null ) charset = utf8mb4; create or replace table roomfinder_buildings2maps ( building_nr varchar(8) not null, map_id int not null, primary key (building_nr, map_id) ) charset = utf8mb4; grant select on table roomfinder_buildings2maps to 'tca-students'; create or replace table roomfinder_maps ( map_id int not null primary key, description varchar(64) not null, scale int not null, width int not null, height int not null ) charset = utf8mb4; grant select on table roomfinder_maps to 'tca-students'; create or replace table roomfinder_rooms ( room_id int not null primary key, room_code varchar(32) null, building_nr varchar(8) null, arch_id varchar(16) null, info varchar(64) null, address varchar(128) null, purpose_id int null, purpose varchar(64) null, seats int null, utm_zone varchar(4) null, utm_easting varchar(32) null, utm_northing varchar(32) null, unit_id int null, default_map_id int null ) charset = utf8mb4; create or replace fulltext index search_index on roomfinder_rooms (info, address, room_code); grant select on table roomfinder_rooms to 'tca-students'; create or replace table roomfinder_rooms2maps ( room_id int not null, map_id int not null, primary key (room_id, map_id) ) charset = utf8mb4; create or replace table roomfinder_schedules ( room_id int not null, start datetime /* mariadb-5.3 */ not null, end datetime /* mariadb-5.3 */ not null, title varchar(64) not null, event_id int not null, course_code varchar(32) null, constraint `unique` unique (room_id, start, end) ) charset = utf8mb4; grant select on table roomfinder_schedules to 'tca-students'; create or replace table sessions ( session varchar(255) charset utf8 not null primary key, access int unsigned null, data text null, constraint session unique (session) ) collate = utf8mb4_unicode_ci; create or replace table tag ( tag int auto_increment primary key, title varchar(255) null ); create or replace table card2tag ( tag int not null, card int not null, primary key (tag, card), constraint card2tag_ibfk_1 foreign key (tag) references tag (tag) on delete cascade, constraint card2tag_ibfk_2 foreign key (card) references card (card) on delete cascade ); create or replace index card on card2tag (card); create or replace table ticket_admin ( ticket_admin int auto_increment primary key, `key` text not null, created timestamp /* mariadb-5.3 */ default current_timestamp() not null, active tinyint(1) default 0 not null, comment text null ) charset = utf8 auto_increment = 48; create or replace table ticket_group ( ticket_group int auto_increment primary key, description text not null ) charset = utf8 auto_increment = 2; create or replace table event ( event int auto_increment primary key, news int null, kino int null, file int null, title varchar(100) not null, description text not null, locality varchar(200) not null, link varchar(200) null, start datetime /* mariadb-5.3 */ null, end datetime /* mariadb-5.3 */ null, ticket_group int default 1 null, constraint fkEventFile foreign key (file) references files (file) on update cascade on delete set null, constraint fkEventGroup foreign key (ticket_group) references ticket_group (ticket_group), constraint fkKino foreign key (kino) references kino (kino) on update cascade on delete set null, constraint fkNews foreign key (news) references news (news) on update cascade on delete set null ) charset = utf8 auto_increment = 39; create or replace index file on event (file); create or replace fulltext index searchTitle on event (title); create or replace table ticket_admin2group ( ticket_admin2group int auto_increment primary key, ticket_admin int not null, ticket_group int not null, constraint fkTicketAdmin foreign key (ticket_admin) references ticket_admin (ticket_admin) on update cascade on delete cascade, constraint fkTicketGroup foreign key (ticket_group) references ticket_group (ticket_group) on update cascade on delete cascade ) charset = utf8 auto_increment = 10; create or replace index ticket_admin on ticket_admin2group (ticket_admin); create or replace index ticket_group on ticket_admin2group (ticket_group); create or replace table ticket_payment ( ticket_payment int auto_increment primary key, name varchar(50) not null, min_amount int null, max_amount int null, config text not null ) charset = utf8 auto_increment = 3; create or replace table ticket_type ( ticket_type int auto_increment primary key, event int not null, ticket_payment int not null, price double not null, contingent int not null, description varchar(100) not null, constraint fkEvent foreign key (event) references event (event) on update cascade on delete cascade, constraint fkPayment foreign key (ticket_payment) references ticket_payment (ticket_payment) on update cascade ) charset = utf8 auto_increment = 57; create or replace table ticket_history ( ticket_history int auto_increment primary key, member int not null, ticket_payment int null, ticket_type int not null, purchase datetime /* mariadb-5.3 */ null, redemption datetime /* mariadb-5.3 */ null, created timestamp /* mariadb-5.3 */ default current_timestamp() not null, code char(128) not null, constraint fkMember foreign key (member) references member (member) on update cascade on delete cascade, constraint fkTicketPayment foreign key (ticket_payment) references ticket_payment (ticket_payment) on update cascade, constraint fkTicketType foreign key (ticket_type) references ticket_type (ticket_type) on update cascade ) charset = utf8 auto_increment = 776; create or replace index member on ticket_history (member); create or replace index ticket_payment on ticket_history (ticket_payment); create or replace index ticket_type on ticket_history (ticket_type); create or replace index event on ticket_type (event); create or replace index ticket_payment on ticket_type (ticket_payment); create or replace table update_note ( version_code int not null primary key, version_name text null, message text null ); create or replace table users ( user int auto_increment primary key, username varchar(7) null, firstname varchar(100) null, surname varchar(100) null, created timestamp /* mariadb-5.3 */ default current_timestamp() not null, deleted int default 0 not null, lastActive int default 0 not null, 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', constraint username unique (username) ) collate = utf8mb4_unicode_ci auto_increment = 434; create or replace table log ( log int auto_increment primary key, time int not null, user_executed int null, user_affected int null, action int null, comment varchar(255) not null, constraint fkLog2Actions foreign key (action) references actions (action) on update set null on delete set null, constraint fkLog2UsersAf foreign key (user_affected) references users (user) on update set null on delete set null, constraint fkLog2UsersEx foreign key (user_executed) references users (user) on update set null on delete set null ) collate = utf8mb4_unicode_ci; create or replace index action on log (action); create or replace index user on log (user_executed); create or replace index user_affected on log (user_affected); create or replace table recover ( recover int auto_increment primary key, user int not null, created int not null, hash varchar(190) not null, ip varchar(255) not null, constraint hash unique (hash), constraint fkRecover2User foreign key (user) references users (user) on delete cascade ) collate = utf8mb4_unicode_ci; create or replace index user on recover (user); create or replace table users2info ( user int not null primary key, firstname varchar(255) not null, surname varchar(255) not null, lastPwChange int not null, pager int default 15 null, constraint fkUsers foreign key (user) references users (user) on update cascade on delete cascade ) collate = utf8mb4_unicode_ci; create or replace table users2roles ( user int not null, role int not null, primary key (user, role), constraint fkUser2RolesRole foreign key (role) references roles (role) on update cascade on delete cascade, constraint fkUser2RolesUser foreign key (user) references users (user) on update cascade on delete cascade ) collate = utf8mb4_unicode_ci; create or replace table wifi_measurement ( id int(20) unsigned auto_increment primary key, 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 ) collate = utf8mb4_unicode_ci;