schema.sql

use ssas;

create table `user` (`id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,`name` VARCHAR(254) NOT NULL,`address` VARCHAR(254),`email` VARCHAR(254) NOT NULL,`password` VARCHAR(254) NOT NULL,`salt` VARCHAR(254) NOT NULL);
create unique index `idx_email` on `user` (`email`);
create table `friend` (`user1` INTEGER NOT NULL,`user2` INTEGER NOT NULL,`relationship` VARCHAR(254) DEFAULT 'FRIENDSHIP' NOT NULL);
alter table `friend` add constraint `pk_friend` primary key(`user1`,`user2`);
create table `admin` (`user` INTEGER NOT NULL PRIMARY KEY);
create table `session` (`session_key` VARCHAR(254) NOT NULL PRIMARY KEY,`user` INTEGER,`creation` TIMESTAMP NOT NULL);
create index `idx_session` on `session` (`creation`);
create table `friend_request` (`from_user` INTEGER NOT NULL,`to_user` INTEGER NOT NULL,`relationship` VARCHAR(254) DEFAULT 'FRIENDSHIP' NOT NULL);
alter table `friend_request` add constraint `pk_friend_request` primary key(`from_user`,`to_user`);
create table `email_confirmation` (`guid` VARCHAR(254) NOT NULL PRIMARY KEY,`user` INTEGER NOT NULL);
create table `hobby` (`id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,`name` VARCHAR(254) NOT NULL);
create unique index `idx_name` on `hobby` (`name`);
create table `user_hobby` (`user` INTEGER NOT NULL,`hobby` INTEGER NOT NULL);
alter table `user_hobby` add constraint `pk_friend_request` primary key(`user`,`hobby`);
create table `formkey` (`form_key` VARCHAR(254) NOT NULL PRIMARY KEY,`session` VARCHAR(254) NOT NULL,`creation` TIMESTAMP NOT NULL);
create index `idx_formkey` on `formkey` (`creation`);
alter table `friend` add constraint `fk_friend_user1` foreign key(`user1`) references `user`(`id`) on update NO ACTION on delete CASCADE;
alter table `friend` add constraint `fk_friend_user2` foreign key(`user2`) references `user`(`id`) on update NO ACTION on delete CASCADE;
alter table `admin` add constraint `fk_admin_user` foreign key(`user`) references `user`(`id`) on update NO ACTION on delete CASCADE;
alter table `session` add constraint `fk_session_user` foreign key(`user`) references `user`(`id`) on update NO ACTION on delete CASCADE;
alter table `friend_request` add constraint `fk_friend_request_user1` foreign key(`from_user`) references `user`(`id`) on update NO ACTION on delete CASCADE;
alter table `friend_request` add constraint `fk_friend_request_user2` foreign key(`to_user`) references `user`(`id`) on update NO ACTION on delete CASCADE;
alter table `email_confirmation` add constraint `fk_email_confirmation_user` foreign key(`user`) references `user`(`id`) on update NO ACTION on delete CASCADE;
alter table `user_hobby` add constraint `fk_user_hobby_user` foreign key(`user`) references `user`(`id`) on update NO ACTION on delete CASCADE;
alter table `user_hobby` add constraint `fk_user_hobby_hobby` foreign key(`hobby`) references `hobby`(`id`) on update NO ACTION on delete CASCADE;
alter table `formkey` add constraint `fk_formkey_session` foreign key(`session`) references `session`(`session_key`) on update NO ACTION on delete CASCADE;