sql 2017-11-21 system manage user role right test

---------------------------------------------------------------------------------------------------
-- test case
-- 
-- @author zengwenfeng
-- @date 2017-11-21
---------------------------------------------------------------------------------------------------

-- CREATE TABLE
CREATE TABLE T_TEACHER
(
  id                    VARCHAR2(32) NOT NULL,
  sex                   VARCHAR2(50),
  name                  VARCHAR2(200),
  code                  VARCHAR2(50),
  birthday              DATE
)
TABLESPACE DATA_PT
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- ADD COMMENTS TO THE TABLE 
COMMENT ON TABLE T_TEACHER IS 'teacher info';

-- ADD COMMENTS TO THE COLUMNS 
COMMENT ON COLUMN T_TEACHER.id IS 'ID';
COMMENT ON COLUMN T_TEACHER.sex IS '性别';
COMMENT ON COLUMN T_TEACHER.name IS '姓名';
COMMENT ON COLUMN T_TEACHER.code IS '学号';
COMMENT ON COLUMN T_TEACHER.birthday IS '出生日期';

-- CREATE/RECREATE PRIMARY, UNIQUE AND FOREIGN KEY CONSTRAINTS 
ALTER TABLE T_TEACHER ADD CONSTRAINT PK_T_TEACHER primary key (ID)
  USING INDEX 
  TABLESPACE DATA_PT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

---------------------------------------------------------------------------------------------------
SELECT * FROM T_TEACHER;
SELECT * FROM T_PERMISSION;
SELECT * FROM T_MENU WHERE NAME LIKE '%教师%' OR ID = 'e9544dfb-40ee-49d8-9e87-ce521493fc07';


---------------------------------------------------------------------------------------------------
SELECT * FROM T_USER WHERE ACCOUNT = 'admin'; --id = 0
SELECT * FROM T_ROLE WHERE ID = '0';
SELECT * FROM T_USER_ROLE WHERE USER_ID = '0' AND role_id = '0';


---------------------------------------------------------------------------------------------------

SELECT * FROM T_ROLE_PERMISSION WHERE permISSION_CODE = 'e9544dfb-40ee-49d8-9e87-ce521493fc07';
DELETE FROM T_ROLE_PERMISSION WHERE permISSION_CODE = 'e9544dfb-40ee-49d8-9e87-ce521493fc07';

---------------------------------------------------------------------------------------------------
INSERT INTO T_ROLE_PERMISSION VALUES ('0', 'e9544dfb-40ee-49d8-9e87-ce521493fc07');