DROP TABLE IF EXISTS role;
CREATE TABLE role
(
id text NOT NULL,
name text NOT NULL,
PRIMARY KEY (id)
)
WITH (oids = false);
COMMENT ON TABLE role IS '角色表';
COMMENT ON COLUMN role.name IS '角色名称';
DROP TABLE IF EXISTS menu;
CREATE TABLE menu
(
id text NOT NULL,
name text NOT NULL,
parent text,
priority smallint,
url text,
authcode smallint,
PRIMARY KEY (id)
)
WITH (oids = false);
COMMENT ON TABLE menu IS '菜单表';
COMMENT ON COLUMN menu.id IS '菜单ID';
COMMENT ON COLUMN menu.name IS '菜单名称';
COMMENT ON COLUMN menu.parent IS '父级菜单ID';
COMMENT ON COLUMN menu.priority IS '显示优先级';
COMMENT ON COLUMN menu.url IS '页面地址';
COMMENT ON COLUMN menu.authcode IS '用户控制码,每一位表示页面上一个功能(通常为按钮)是否可用,比如0b01表示第一个功能可用,0b11表示第一和第二个功能可用,而0b10表示第二个功能可用,第一个功能不可用';
DROP TABLE IF EXISTS rolemenu;
CREATE TABLE rolemenu
(
roleid text NOT NULL,
menuid text NOT NULL,
CONSTRAINT pk_rolemenu PRIMARY KEY (roleid, menuid)
)
WITH (oids = false);
COMMENT ON TABLE rolemenu IS '角色菜单关联表';
COMMENT ON COLUMN rolemenu.roleid IS '角色ID role.id';
COMMENT ON COLUMN rolemenu.menuid IS '菜单ID menu.id';
DROP TABLE IF EXISTS userole;
CREATE TABLE userole
(
userid text NOT NULL,
roleid text NOT NULL,
CONSTRAINT pk_userole PRIMARY KEY (userid, roleid)
)
WITH (oids = false);
COMMENT ON TABLE userole IS '用户角色关联表';
COMMENT ON COLUMN userole.userid IS '用户ID user.id';
COMMENT ON COLUMN userole.roleid IS '角色ID role.id';