-- 成员权限改造，主表增加维度简码字符串字段、子表增加成员序号、成员类型字段
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_EB_MEMBPERMRECORD' AND KSQL_COL_NAME IN ('FDIMSHORTNUMS')) 
 ALTER TABLE T_EB_MEMBPERMRECORD ADD (FDIMSHORTNUMS NVARCHAR(50) DEFAULT ' ' NOT NULL );


IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_EB_MEMBPERMRECORDDETAIL' AND KSQL_COL_NAME IN ('FMEMBTYPE')) 
 ALTER TABLE T_EB_MEMBPERMRECORDDETAIL ADD (FMEMBTYPE CHAR(1) DEFAULT '0' NOT NULL);

IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_EB_MEMBPERMRECORDDETAIL' AND KSQL_COL_NAME IN ('FMEMBSEQ')) 
 ALTER TABLE T_EB_MEMBPERMRECORDDETAIL ADD (FMEMBSEQ INT DEFAULT 0 NOT NULL );
 
 --权限操作日志表增加维度简码字符串字段
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_EB_MEMBPERMUPDATELOG' AND KSQL_COL_NAME IN ('FDIMSHORTNUMSTR')) 
 ALTER TABLE T_EB_MEMBPERMUPDATELOG ADD (FDIMSHORTNUMSTR NVARCHAR(50) DEFAULT ' ' NOT NULL );
 
 --删除旧的，添加字段
EXEC p_DropIdx 'IX_EB_MEMBPERMRECORDDETAIL', 'T_EB_MEMBPERMRECORDDETAIL';
 IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IX_EB_MEMBPERMRECORDDETAIL')
CREATE INDEX IX_EB_MEMBPERMRECORDDETAIL ON T_EB_MEMBPERMRECORDDETAIL (FID,FSEQ);

EXEC p_DropIdx 'IX_EB_MEMBPERMRECORD', 'T_EB_MEMBPERMRECORD';
IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IX_EB_MEMBPERMRECORD')
CREATE INDEX IX_EB_MEMBPERMRECORD ON T_EB_MEMBPERMRECORD ( FMODELID,FBIZCTRLRANGEID,FPERMGROUP,FUID,FDIMSHORTNUMS);


--权限版本表 
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_EB_MEMBPERMVERSION') 
 CREATE TABLE T_EB_MEMBPERMVERSION (
   FID BIGINT DEFAULT 0 NOT NULL,
   FUSER BIGINT DEFAULT 0 NOT NULL,
   FDIMENSION BIGINT DEFAULT 0 NOT NULL,
   FBIZRANGE BIGINT DEFAULT 0 NOT NULL,
   FVERSION BIGINT DEFAULT 0 NOT NULL,
   FDIMSHORTNUMS NVARCHAR(50) DEFAULT ' ' NOT NULL,
   FMODEL BIGINT DEFAULT 0 NOT NULL,
   FREFDIMGROUP CHAR(1) DEFAULT '0' NOT NULL,
   FADDTIME BIGINT DEFAULT 0 NOT NULL
);

--主键和索引
 EXEC P_ALTERPK 'PK_EB_MEMBPERMVERSION', 'T_EB_MEMBPERMVERSION', 'FID', '1';
 IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_EB_MEMBPERMVERSION_REF') CREATE INDEX IDX_EB_MEMBPERMVERSION_REF ON T_EB_MEMBPERMVERSION ( FMODEL,FBIZRANGE,FDIMSHORTNUMS,FREFDIMGROUP,FUSER);
 
 
 --维度组合表
 
 IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_EB_PERMDIMGROUP') 
 CREATE TABLE T_EB_PERMDIMGROUP (
   FID BIGINT DEFAULT 0 NOT NULL,
   FMODEL BIGINT DEFAULT 0 NOT NULL,
   FMODIFIERFIELD BIGINT DEFAULT 0 NOT NULL,
   FMODIFYDATEFIELD DATETIME
);

  EXEC P_ALTERPK 'PK_T_EB_PERMDIMGROUP', 'T_EB_PERMDIMGROUP', 'FID', '1';
 IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_EB_PERMDIMGROUP_MODEL') CREATE INDEX IDX_EB_PERMDIMGROUP_MODEL ON T_EB_PERMDIMGROUP ( FMODEL);
 
 
  IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_EB_PERMDIMGROUP_DIM') 
 CREATE TABLE T_EB_PERMDIMGROUP_DIM (
   FPKID BIGINT DEFAULT 0 NOT NULL,
   FENTRYID BIGINT DEFAULT 0 NOT NULL,
   FBASEDATAID BIGINT DEFAULT 0 NOT NULL
);

  EXEC P_ALTERPK 'PK_EB_PERMDIMGROUP_DIM', 'T_EB_PERMDIMGROUP_DIM', 'FPKID', '1';
 IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_EB_PERMDIMGROUP_DIM_FK') CREATE INDEX IDX_EB_PERMDIMGROUP_DIM_FK ON T_EB_PERMDIMGROUP_DIM ( FENTRYID);

 
   IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_EB_PERMDIMGROUP_ROW') 
 CREATE TABLE T_EB_PERMDIMGROUP_ROW (
   FID BIGINT DEFAULT 0 NOT NULL,
   FENTRYID BIGINT DEFAULT 0 NOT NULL,
   FSEQ INT DEFAULT 0 NOT NULL,
   FDIMGROUPNAME NVARCHAR(50) DEFAULT ' ' NOT NULL
);
 
   EXEC P_ALTERPK 'PK_EB_PERMDIMGROUP_ROW', 'T_EB_PERMDIMGROUP_ROW', 'FENTRYID', '1';
  IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_EB_PERMDIMGROUP_ROW_FK') CREATE INDEX IDX_EB_PERMDIMGROUP_ROW_FK ON T_EB_PERMDIMGROUP_ROW ( FID);
 
    IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 't_eb_permdimgroup_row_l') 
 CREATE TABLE t_eb_permdimgroup_row_l (
   FPKID NVARCHAR(36) DEFAULT ' ' NOT NULL,
   FENTRYID BIGINT DEFAULT 0 NOT NULL,
   FLOCALEID NVARCHAR(10) DEFAULT ' ' NOT NULL,
   FDIMGROUPNAME NVARCHAR(50) DEFAULT ' ' NOT NULL
);

   EXEC P_ALTERPK 'pk_eb_permdimgroup_row_l', 't_eb_permdimgroup_row_l', 'FPKID', '1';
  IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'idx_eb_permdimgroup_row_l') CREATE INDEX idx_eb_permdimgroup_row_l ON t_eb_permdimgroup_row_l ( FENTRYID,FLOCALEID);
 
 
 
 
 
 
 
 
 