選單許可權表sqlite和mysql

得好好活發表於2024-04-23
SQLiteMySql

sqlite


CREATE TABLE sys_user (
                          id TEXT PRIMARY KEY,
                          name TEXT NOT NULL,
                          phone TEXT  ,
                          password TEXT NOT NULL,
                          create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                          is_delete INTEGER NOT NULL  DEFAULT 0,
                          remark TEXT
);

CREATE TABLE sys_role (
                          id TEXT PRIMARY KEY,
                          name TEXT NOT NULL,
                          remark TEXT,
                          create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE sys_menu (
                          id TEXT PRIMARY KEY,
                          menu TEXT NOT NULL,
                          url TEXT  ,
                          sort INTEGER  ,
                          style TEXT,
                          create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                          is_delete INTEGER NOT NULL DEFAULT 0,
                          remark TEXT
);

CREATE TABLE sys_permission (
                                id TEXT PRIMARY KEY,
                                name TEXT  ,
                                url TEXT,
                                remark TEXT,
                                create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                                is_delete INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE sys_role_user
(
    role_id TEXT,
    user_id TEXT
);

CREATE TABLE sys_role_menu
(
    role_id TEXT,
    menu_id TEXT
);
CREATE TABLE sys_role_permission
(
    role_id TEXT,
    permission_id TEXT
);

mysql 型別：

CREATE TABLE sys_user
(
    id          varchar(100) PRIMARY KEY,
    name        varchar(100) NOT NULL,
    phone       varchar(100),
    password    varchar(100) NOT NULL,
    create_time DATETIME              DEFAULT CURRENT_TIMESTAMP,
    is_delete   INTEGER      NOT NULL DEFAULT 0,
    remark      varchar(100)
);

CREATE TABLE sys_role
(
    id          varchar(100) PRIMARY KEY,
    name        varchar(100) NOT NULL,
    remark      varchar(100),
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE sys_menu
(
    id          varchar(100) PRIMARY KEY,
    menu        varchar(100) NOT NULL,
    url         varchar(100),
    sort        INTEGER,
    style       varchar(100),
    create_time DATETIME              DEFAULT CURRENT_TIMESTAMP,
    is_delete   INTEGER      NOT NULL DEFAULT 0,
    remark      varchar(100)
);

CREATE TABLE sys_permission
(
    id          varchar(100) PRIMARY KEY,
    name        varchar(100),
    url         varchar(100),
    remark      varchar(100),
    create_time DATETIME         DEFAULT CURRENT_TIMESTAMP,
    is_delete   INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE sys_role_user
(
    role_id varchar(100),
    user_id varchar(100),
    PRIMARY KEY (role_id, user_id),
);

CREATE TABLE sys_role_menu
(
    role_id varchar(100),
    menu_id varchar(100),
    PRIMARY KEY (role_id, menu_id),
);

CREATE TABLE sys_role_permission
(
    role_id       varchar(100),
    permission_id varchar(100),
    PRIMARY KEY (role_id, permission_id),
);

相關文章