system.sql

来自「Java的框架」· SQL 代码 · 共 40 行

USE PRRM;

CREATE TABLE IF NOT EXISTS USER (
    USERNAME VARCHAR(20) NOT NULL PRIMARY KEY,
    VERSION INTEGER NOT NULL,
    PASSWORD VARCHAR(255) NOT NULL,
    FIRST_NAME VARCHAR(50) NOT NULL,
    LAST_NAME VARCHAR(50) NOT NULL,
    ADDRESS VARCHAR(150),
    CITY VARCHAR(50) NOT NULL,
    PROVINCE VARCHAR(100),
    COUNTRY VARCHAR(100),
    POSTAL_CODE VARCHAR(15) NOT NULL,
    EMAIL VARCHAR(255) NOT NULL UNIQUE,
    PHONE_NUMBER VARCHAR(255),
    WEBSITE VARCHAR(255),
    PASSWORD_HINT VARCHAR(255),
    ENABLED CHAR(1),
    EXPIRED CHAR(1),
    LOCKED CHAR(1),
    CREDENTIALS_EXPIRED CHAR(1)
)		ENGINE=INNODB
;


CREATE TABLE IF NOT EXISTS ROLE (
    NAME VARCHAR(50) NOT NULL PRIMARY KEY,
    VERSION INTEGER NOT NULL,
    DESCRIPTION VARCHAR(255)
) 	ENGINE=INNODB
;


CREATE TABLE IF NOT EXISTS USER_ROLE (
    USERNAME VARCHAR(20) NOT NULL,
    ROLE_NAME VARCHAR(50) NOT NULL,
    PRIMARY KEY (USERNAME, ROLE_NAME)
) 	ENGINE=INNODB
;