setup-mssql.sql

CREATE TABLE PLM_ROLE (
  ID BIGINT NOT NULL,
  LABEL varchar(255) NOT NULL,
  CONSTRAINT plm_role_pk PRIMARY KEY (ID),
  CONSTRAINT uniq_plm_role_label UNIQUE (LABEL)
);

CREATE TABLE PLM_USER (
  ID BIGINT NOT NULL,
  ID_ROLE BIGINT NOT NULL,
  CREATION_DATE DATETIME NOT NULL,
  FIRST_NAME varchar(255) NOT NULL,
  LAST_NAME varchar(255) NOT NULL,
  EMAIL varchar(255) NOT NULL,
  USER_NAME varchar(255) NOT NULL,
  PASSWORD varchar(255) NOT NULL,
  CONSTRAINT plm_user_pk PRIMARY KEY (ID),
  CONSTRAINT uniq_plm_user_email UNIQUE (EMAIL),
  CONSTRAINT uniq_plm_user_username UNIQUE (USER_NAME),
  CONSTRAINT plm_user_role FOREIGN KEY (ID_ROLE) REFERENCES PLM_ROLE (ID)
);

CREATE TABLE PLM_ROLE_PERMISSION (
  ID_ROLE BIGINT NOT NULL,
  PERMISSION varchar(255) NOT NULL,
  CONSTRAINT plm_role_permission_pk PRIMARY KEY (ID_ROLE, PERMISSION),
  CONSTRAINT plm_role_permission_role FOREIGN KEY (ID_ROLE) REFERENCES PLM_ROLE (ID)
);


INSERT INTO PLM_ROLE VALUES(1, 'Administrator');
INSERT INTO PLM_USER VALUES(1, 1, GETDATE(), 'Admin', 'Admin', 'admin@admin', 'admin', '$2a$11$FfgtfoHeNo/m9jGj9D5rTO0zDDI4LkMXnXHai744Ee32P3CHoBVqm');
INSERT INTO PLM_ROLE_PERMISSION VALUES(1, 'MANAGE_USERS');
INSERT INTO PLM_ROLE_PERMISSION VALUES(1, 'MANAGE_ROLES');
INSERT INTO PLM_ROLE_PERMISSION VALUES(1, 'MANAGE_SYSTEM');

GO