emergencysystem.sql

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `users_roles`, `users`, `roles`;
SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE IF NOT EXISTS `users` (
  `USER_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `FIRSTNAME` varchar(255) NOT NULL,
  `LASTNAME` varchar(255) NOT NULL,
  `USERNAME` varchar(16) NOT NULL,
  `PASSWORD` varchar(22) NOT NULL,
  `EMAIL` varchar(255) NOT NULL,
  `CREATED` datetime NOT NULL,
  PRIMARY KEY (`USER_ID`),
  UNIQUE KEY `USERNAME` (`USERNAME`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE `roles` (
  `ROLE_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) NOT NULL,
  PRIMARY KEY (`ROLE_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

CREATE TABLE `users_roles` (
  `ROLE_ID` bigint(20) NOT NULL,
  `USER_ID` bigint(20) NOT NULL,
  PRIMARY KEY (`ROLE_ID`, `USER_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1;


ALTER TABLE `users_roles`
  ADD CONSTRAINT `fk_users_roles_1` FOREIGN KEY (`ROLE_ID`) REFERENCES `roles` (`ROLE_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_users_roles_2` FOREIGN KEY (`USER_ID`) REFERENCES `users` (`USER_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION;

INSERT INTO `users` (`USER_ID` ,`FIRSTNAME` ,`LASTNAME` ,`USERNAME` ,`PASSWORD` ,`EMAIL` ,`CREATED`) VALUES (NULL , 'admin', 'admin', 'admin', 'admin', 'admin@admin.it', '2014-10-01 00:00:00');
INSERT INTO `roles` (`ROLE_ID`, `NAME`) VALUES (1, 'admin');
INSERT INTO `users_roles` (`ROLE_ID`, `USER_ID`) VALUES ('1', '1');