mysql commands

CREATE TABLE IF NOT EXISTS `user` (
 `username` varchar(50) NOT NULL,
 `password` varchar(100) NOT NULL,
 `enabled` tinyint(1) NOT NULL,
 PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `authorities` (
 `username` varchar(50) NOT NULL,
 `authority` varchar(50) NOT NULL,
 UNIQUE KEY `ix_auth_username` (`username`,`authority`),
 CONSTRAINT `fk_authorities_users` FOREIGN KEY (`username`) REFERENCES `user` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


create table student (username varchar(50) not null primary key,firstname varchar(50) not null,lastname varchar(50) not null,email varchar(50) not null,degree_title varchar(50) default null,degree_grade float default null,preference varchar(255) default null,recommendation varchar(255) default null, foreign key (username) references user(username))ENGINE=InnoDB DEFAULT CHARSET=utf8;


create table instructor (username varchar(50) not null primary key,firstname varchar(50) not null,lastname varchar(50) not null,email varchar(50) not null, field varchar(100) not null,foreign key (username) references user(username))ENGINE=InnoDB DEFAULT CHARSET=utf8;


create table secretary (username varchar(50) not null primary key, app_pending varchar(255) default null, app_complete varchar(255) default null, foreign key (username) references user(username))ENGINE=InnoDB DEFAULT CHARSET=utf8;


alter table secretary drop column app_pending;
alter table secretary drop column app_complete;



create table application (id int not null auto_increment, state int default null, criteria boolean default null, ranking int default null, primary key(id))ENGINE=InnoDB DEFAULT CHARSET=utf8;

alter table application add master int default null;


create table master (id int not null auto_increment, title varchar(255) default null, context varchar(255) default null, criteria varchar(255) default null, field varchar(100) default null, primary key(id))ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `app_instr` (
`app_id` int NOT NULL,
`instr_id` varchar(50) NOT NULL,

PRIMARY KEY (`app_id`,`instr_id`),

KEY `FK_INSTR_idx` (`instr_id`),

CONSTRAINT `FK_APP` FOREIGN KEY (`app_id`)
REFERENCES `application` (`id`)
ON DELETE NO ACTION ON UPDATE NO ACTION,

CONSTRAINT `FK_INSTR` FOREIGN KEY (`instr_id`)
REFERENCES `instructor` (`username`)
ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO `user` (`username`, `password`, `enabled`) VALUES
   ('chris', 'test1', 1),
   ('root', 'test2', 1);
   
INSERT INTO `user` (`username`, `password`, `enabled`) VALUES
   ('spyros', 'password', 1),
   ('jim', 'password', 1),
   ('john', 'password', 1),
   ('demy', 'password', 1);
   

INSERT INTO `authorities` (`username`, `authority`) VALUES
   ('root', 'ROLE_ADMIN'),
   ('chris', 'ROLE_USER');
   
 INSERT INTO `authorities` (`username`, `authority`) VALUES
   ('spyros', 'ROLE_USER'),
   ('jim', 'ROLE_USER'),
   ('john', 'ROLE_USER'),
   ('demy', 'ROLE_USER');
   
   
   
insert into student values ('chris', 'christina', 'pap', 'test@hua.gr', 'pliroforiki', 8.6, 'cloud', 'test'); 

insert into student values ('spyros', 'spyridon', 'kar', 'kar@hua.gr', 'pliroforiki', 9, 'teaching', 'test'); 

insert into student values ('jim', 'dimitris', 'geo', 'geo@hua.gr', 'pliroforiki', 10, 'networks', 'test'); 

insert into master values (null, 'oikonomika', 'metaptuxiako sta oikonomika etc', 'vathmos panw apo 7', 'oikonomikes epistimes');

insert into application values(null, 1, 1, 12, 1);

insert into instructor values('john','johnathan', 'doe', 'doe@hua.gr', 'cloud');

insert into secretary values('demy');


update user set password='$2a$12$mLFcl.fj9QNvdQCO8.Pliet8I1TzeYNZD9gCnRRcXGpgTn6xb4Fgm';