70 lines
1.8 KiB
SQL
70 lines
1.8 KiB
SQL
BEGIN TRANSACTION;
|
|
|
|
CREATE SEQUENCE PERMISSION_SEQ START 1;
|
|
|
|
CREATE TABLE PERMISSION (
|
|
ID INTEGER PRIMARY KEY DEFAULT NEXTVAL('PERMISSION_SEQ'),
|
|
NAME VARCHAR(50) UNIQUE NOT NULL,
|
|
DESCRIPTION VARCHAR(250)
|
|
);
|
|
|
|
CREATE INDEX PERMISSION_NAME_IDX ON PERMISSION (NAME);
|
|
|
|
CREATE SEQUENCE USERS_SEQ START 1;
|
|
|
|
CREATE TABLE USERS (
|
|
ID INTEGER PRIMARY KEY DEFAULT NEXTVAL('USERS_SEQ'),
|
|
LOGIN VARCHAR(25) UNIQUE NOT NULL,
|
|
PASSWORD CHAR(64) NOT NULL,
|
|
NAME VARCHAR(50) NOT NULL,
|
|
SURNAME VARCHAR(100) NOT NULL,
|
|
EMAIL VARCHAR(254)
|
|
);
|
|
|
|
CREATE INDEX USERS_LOGIN_IDX ON USERS (LOGIN);
|
|
CREATE INDEX USERS_PASSWORD_IDX ON USERS (PASSWORD);
|
|
|
|
CREATE SEQUENCE ROLE_SEQ START 1;
|
|
|
|
CREATE TABLE ROLE (
|
|
ID INTEGER PRIMARY KEY DEFAULT NEXTVAL('ROLE_SEQ'),
|
|
NAME VARCHAR(50) UNIQUE NOT NULL,
|
|
DESCRIPTION VARCHAR(250)
|
|
);
|
|
|
|
CREATE INDEX ROLE_NAME_IDX ON ROLE (NAME);
|
|
|
|
|
|
CREATE SEQUENCE SESSION_SEQ START 1;
|
|
|
|
CREATE TABLE SESSION (
|
|
ID INTEGER PRIMARY KEY DEFAULT NEXTVAL('SESSION_SEQ'),
|
|
USER_ID INTEGER NOT NULL REFERENCES USERS(ID),
|
|
TOKEN CHAR(36) NOT NULL UNIQUE,
|
|
START_TIME TIMESTAMP NOT NULL,
|
|
EXPIRATION_TIME TIMESTAMP NOT NULL,
|
|
VALID BOOLEAN NOT NULL DEFAULT TRUE
|
|
);
|
|
|
|
CREATE INDEX SESSION_USER_ID_IDX ON SESSION (USER_ID);
|
|
CREATE INDEX SESSION_TOKEN_IDX ON SESSION (TOKEN);
|
|
|
|
CREATE TABLE ROLE_PERMISSION (
|
|
ROLE_ID INTEGER NOT NULL REFERENCES ROLE(ID),
|
|
PERMISSION_ID INTEGER NOT NULL REFERENCES PERMISSION(ID),
|
|
PRIMARY KEY(ROLE_ID, PERMISSION_ID)
|
|
);
|
|
|
|
CREATE INDEX ROLE_PERMISSION_ROLE_ID_IDX ON ROLE_PERMISSION (ROLE_ID);
|
|
|
|
CREATE TABLE USER_ROLE (
|
|
USER_ID INTEGER NOT NULL REFERENCES USERS(ID),
|
|
ROLE_ID INTEGER NOT NULL REFERENCES ROLE(ID),
|
|
PRIMARY KEY(USER_ID, ROLE_ID)
|
|
);
|
|
|
|
CREATE INDEX USER_ROLE_USER_ID_IDX ON USER_ROLE (USER_ID);
|
|
|
|
|
|
|
|
COMMIT; |