dc/dc
dc
/
dc
2
1
Fork 0
Code Issues 7 Pull Requests 1 Actions Packages Projects Releases 27 Wiki Activity
dc/sas/sasjs/db/migrations/20210410_v3.12_release.sas

448 lines
13 KiB
SAS
Raw Permalink Blame History

/**
@file
@brief migration script to move from v3.11 to v3.12 of data controller
@details MANUAL STEP - add "AUDIT" as a new value in mpe_selectbox for the
mpe_security table (access_level column)
<h4> SAS Macros </h4>
@li mm_assigndirectlib.sas
**/
filename mc url "https://raw.githubusercontent.com/sasjs/core/main/all.sas";
%inc mc;
%let dclib=YOURDCLIB;
%let lib=&dclib;
%mm_assigndirectlib(&dclib)
proc sql;
create table &dclib..mpe_excel_config(
tx_from num,
xl_libref char(8),
xl_table char(32),
xl_column char(32),
xl_rule char(32),
xl_active num,
tx_to num not null,
constraint pk_mpe_excel_config
primary key(tx_from,xl_libref,xl_table,xl_column));
create table &lib..mpe_row_level_security(
tx_from num not null format=datetime19.3,
tx_to num not null format=datetime19.3,
RLS_RK num not null,
RLS_SCOPE char(8) not null,
RLS_GROUP char(128) not null,
RLS_LIBREF char(8) not null,
RLS_TABLE char(32) not null,
RLS_GROUP_LOGIC char(3) not null,
RLS_SUBGROUP_LOGIC char(3) not null,
RLS_SUBGROUP_ID num not null,
RLS_VARIABLE_NM varchar(32) not null,
RLS_OPERATOR_NM varchar(12) not null,
RLS_RAW_VALUE varchar(4000) not null,
RLS_ACTIVE num not null,
constraint pk_mpe_row_level_security
primary key(tx_from, RLS_RK));
proc sql;
insert into &dclib..mpe_tables
set tx_from=0
,tx_to='31DEC5999:23:59:59'dt
,libref="&dclib"
,dsn='MPE_EXCEL_CONFIG'
,num_of_approvals_required=1
,loadtype='TXTEMPORAL'
,buskey='XL_LIBREF XL_TABLE XL_COLUMN'
,notes='Configuration of the excel import rules'
,var_txfrom='TX_FROM'
,var_txto='TX_TO'
;
insert into &lib..mpe_tables
set tx_from=0
,tx_to='31DEC5999:23:59:59'dt
,libref="&lib"
,dsn='MPE_ROW_LEVEL_SECURITY'
,num_of_approvals_required=1
,loadtype='TXTEMPORAL'
,buskey='RLS_RK'
,notes='Configuration of Row Level Security'
,var_txfrom='TX_FROM'
,var_txto='TX_TO'
,rk_underlying='RLS_SCOPE RLS_GROUP RLS_LIBREF RLS_TABLE RLS_GROUP_LOGIC '
!!'RLS_SUBGROUP_LOGIC RLS_SUBGROUP_ID RLS_VARIABLE_NM RLS_OPERATOR_NM '
!!'RLS_RAW_VALUE '
,POST_EDIT_HOOK='services/hooks/mpe_row_level_security_postedit'
;
proc sql;
alter table &dclib..mpe_filteranytable drop filter_json;
proc sql;
create table &dclib..mpe_filtersource(
filter_hash char(32) not null,
filter_line num not null,
group_logic char(3) not null,
subgroup_logic char(3) not null,
subgroup_id num not null,
variable_nm varchar(32) not null,
operator_nm varchar(12) not null,
raw_value varchar(4000) not null,
processed_dttm num not null format=datetime19.,
constraint pk_mpe_filteranytable
primary key(filter_hash,filter_line));
proc sql;
select max(selectbox_rk) into: selectbox_rk from &dclib..mpe_filtersource;
insert into &lib..mpe_selectbox set
selectbox_rk=&selectbox_rk+1
,ver_from_dttm=0
,select_lib="&lib"
,select_ds="MPE_VALIDATIONS"
,base_column="RULE_TYPE"
,selectbox_value="HARDSELECT_HOOK"
,selectbox_order=7
,ver_to_dttm='31DEC5999:23:59:59'dt;
insert into &lib..mpe_selectbox set
selectbox_rk=&selectbox_rk+2
,ver_from_dttm=0
,select_lib="&lib"
,select_ds="MPE_VALIDATIONS"
,base_column="RULE_TYPE"
,selectbox_value="SOFTSELECT_HOOK"
,selectbox_order=7
,ver_to_dttm='31DEC5999:23:59:59'dt;
insert into &lib..mpe_selectbox set
selectbox_rk=&selectbox_rk+3
,ver_from_dttm=0
,select_lib="&lib"
,select_ds="MPE_ROW_LEVEL_SECURITY"
,base_column="RLS_SCOPE"
,selectbox_value="ALL"
,selectbox_order=1
,ver_to_dttm='31DEC5999:23:59:59'dt;
insert into &lib..mpe_selectbox set
selectbox_rk=&selectbox_rk+4
,ver_from_dttm=0
,select_lib="&lib"
,select_ds="MPE_ROW_LEVEL_SECURITY"
,base_column="RLS_SCOPE"
,selectbox_value="EDIT"
,selectbox_order=1
,ver_to_dttm='31DEC5999:23:59:59'dt;
insert into &lib..mpe_selectbox set
selectbox_rk=&selectbox_rk+5
,ver_from_dttm=0
,select_lib="&lib"
,select_ds="MPE_ROW_LEVEL_SECURITY"
,base_column="RLS_SCOPE"
,selectbox_value="VIEW"
,selectbox_order=1
,ver_to_dttm='31DEC5999:23:59:59'dt;
insert into &lib..mpe_selectbox set
selectbox_rk=&selectbox_rk+6
,ver_from_dttm=0
,select_lib="&lib"
,select_ds="MPE_ROW_LEVEL_SECURITY"
,base_column="RLS_GROUP_LOGIC"
,selectbox_value="AND"
,selectbox_order=1
,ver_to_dttm='31DEC5999:23:59:59'dt;
insert into &lib..mpe_selectbox set
selectbox_rk=&selectbox_rk+7
,ver_from_dttm=0
,select_lib="&lib"
,select_ds="MPE_ROW_LEVEL_SECURITY"
,base_column="RLS_GROUP_LOGIC"
,selectbox_value="OR"
,selectbox_order=2
,ver_to_dttm='31DEC5999:23:59:59'dt;
insert into &lib..mpe_selectbox set
selectbox_rk=&selectbox_rk+8
,ver_from_dttm=0
,select_lib="&lib"
,select_ds="MPE_ROW_LEVEL_SECURITY"
,base_column="RLS_SUBGROUP_LOGIC"
,selectbox_value="AND"
,selectbox_order=1
,ver_to_dttm='31DEC5999:23:59:59'dt;
insert into &lib..mpe_selectbox set
selectbox_rk=&selectbox_rk+9
,ver_from_dttm=0
,select_lib="&lib"
,select_ds="MPE_ROW_LEVEL_SECURITY"
,base_column="RLS_SUBGROUP_LOGIC"
,selectbox_value="OR"
,selectbox_order=2
,ver_to_dttm='31DEC5999:23:59:59'dt;
insert into &lib..mpe_selectbox set
selectbox_rk=&selectbox_rk+10
,ver_from_dttm=0
,select_lib="&lib"
,select_ds="MPE_ROW_LEVEL_SECURITY"
,base_column="RLS_OPERATOR_NM"
,selectbox_value="="
,selectbox_order=1
,ver_to_dttm='31DEC5999:23:59:59'dt;
insert into &lib..mpe_selectbox set
selectbox_rk=&selectbox_rk+11
,ver_from_dttm=0
,select_lib="&lib"
,select_ds="MPE_ROW_LEVEL_SECURITY"
,base_column="RLS_OPERATOR_NM"
,selectbox_value=">"
,selectbox_order=1
,ver_to_dttm='31DEC5999:23:59:59'dt;
insert into &lib..mpe_selectbox set
selectbox_rk=&selectbox_rk+12
,ver_from_dttm=0
,select_lib="&lib"
,select_ds="MPE_ROW_LEVEL_SECURITY"
,base_column="RLS_OPERATOR_NM"
,selectbox_value="<"
,selectbox_order=1
,ver_to_dttm='31DEC5999:23:59:59'dt;
insert into &lib..mpe_selectbox set
selectbox_rk=&selectbox_rk+13
,ver_from_dttm=0
,select_lib="&lib"
,select_ds="MPE_ROW_LEVEL_SECURITY"
,base_column="RLS_OPERATOR_NM"
,selectbox_value="<="
,selectbox_order=1
,ver_to_dttm='31DEC5999:23:59:59'dt;
insert into &lib..mpe_selectbox set
selectbox_rk=&selectbox_rk+14
,ver_from_dttm=0
,select_lib="&lib"
,select_ds="MPE_ROW_LEVEL_SECURITY"
,base_column="RLS_OPERATOR_NM"
,selectbox_value=">="
,selectbox_order=1
,ver_to_dttm='31DEC5999:23:59:59'dt;
insert into &lib..mpe_selectbox set
selectbox_rk=&selectbox_rk+15
,ver_from_dttm=0
,select_lib="&lib"
,select_ds="MPE_ROW_LEVEL_SECURITY"
,base_column="RLS_OPERATOR_NM"
,selectbox_value="BETWEEN"
,selectbox_order=1
,ver_to_dttm='31DEC5999:23:59:59'dt;
insert into &lib..mpe_selectbox set
selectbox_rk=&selectbox_rk+16
,ver_from_dttm=0
,select_lib="&lib"
,select_ds="MPE_ROW_LEVEL_SECURITY"
,base_column="RLS_OPERATOR_NM"
,selectbox_value="NOT IN"
,selectbox_order=1
,ver_to_dttm='31DEC5999:23:59:59'dt;
insert into &lib..mpe_selectbox set
selectbox_rk=&selectbox_rk+17
,ver_from_dttm=0
,select_lib="&lib"
,select_ds="MPE_ROW_LEVEL_SECURITY"
,base_column="RLS_OPERATOR_NM"
,selectbox_value="NE"
,selectbox_order=1
,ver_to_dttm='31DEC5999:23:59:59'dt;
insert into &lib..mpe_selectbox set
selectbox_rk=&selectbox_rk+18
,ver_from_dttm=0
,select_lib="&lib"
,select_ds="MPE_ROW_LEVEL_SECURITY"
,base_column="RLS_OPERATOR_NM"
,selectbox_value="CONTAINS"
,selectbox_order=1
,ver_to_dttm='31DEC5999:23:59:59'dt;
insert into &lib..mpe_selectbox set
selectbox_rk=&selectbox_rk+19
,ver_from_dttm=0
,select_lib="&lib"
,select_ds="MPE_ROW_LEVEL_SECURITY"
,base_column="RLS_ACTIVE"
,selectbox_value="1"
,selectbox_order=1
,ver_to_dttm='31DEC5999:23:59:59'dt;
insert into &lib..mpe_selectbox set
selectbox_rk=&selectbox_rk+20
,ver_from_dttm=0
,select_lib="&lib"
,select_ds="MPE_ROW_LEVEL_SECURITY"
,base_column="RLS_ACTIVE"
,selectbox_value="0"
,selectbox_order=2
,ver_to_dttm='31DEC5999:23:59:59'dt;
insert into &lib..MPE_VALIDATIONS set
tx_from=0
,base_lib="&lib"
,base_ds="MPE_VALIDATIONS"
,base_col="RULE_ACTIVE"
,rule_type='MINVAL'
,rule_value="0"
,rule_active=1
,tx_to='31DEC5999:23:59:59'dt;
insert into &lib..MPE_VALIDATIONS set
tx_from=0
,base_lib="&lib"
,base_ds="MPE_VALIDATIONS"
,base_col="RULE_ACTIVE"
,rule_type='MAXVAL'
,rule_value="1"
,rule_active=1
,tx_to='31DEC5999:23:59:59'dt;
insert into &lib..MPE_VALIDATIONS set
tx_from=0
,base_lib="&lib"
,base_ds="MPE_VALIDATIONS"
,base_col="BASE_LIB"
,rule_type='SOFTSELECT_HOOK'
,rule_value="services/validations/libraries_editable"
,rule_active=1
,tx_to='31DEC5999:23:59:59'dt;
insert into &lib..MPE_VALIDATIONS set
tx_from=0
,base_lib="&lib"
,base_ds="MPE_VALIDATIONS"
,base_col="BASE_DS"
,rule_type='SOFTSELECT_HOOK'
,rule_value="services/validations/tables_editable"
,rule_active=1
,tx_to='31DEC5999:23:59:59'dt;
insert into &lib..MPE_VALIDATIONS set
tx_from=0
,base_lib="&lib"
,base_ds="MPE_VALIDATIONS"
,base_col="BASE_COL"
,rule_type='SOFTSELECT_HOOK'
,rule_value="services/validations/columns_in_libds"
,rule_active=1
,tx_to='31DEC5999:23:59:59'dt;
insert into &lib..MPE_VALIDATIONS set
tx_from=0
,base_lib="&lib"
,base_ds="MPE_EXCEL_CONFIG"
,base_col="XL_LIBREF"
,rule_type='SOFTSELECT_HOOK'
,rule_value="services/validations/libraries_editable"
,rule_active=1
,tx_to='31DEC5999:23:59:59'dt;
insert into &lib..MPE_VALIDATIONS set
tx_from=0
,base_lib="&lib"
,base_ds="MPE_EXCEL_CONFIG"
,base_col="XL_TABLE"
,rule_type='SOFTSELECT_HOOK'
,rule_value="services/validations/tables_editable"
,rule_active=1
,tx_to='31DEC5999:23:59:59'dt;
insert into &lib..MPE_VALIDATIONS set
tx_from=0
,base_lib="&lib"
,base_ds="MPE_EXCEL_CONFIG"
,base_col="XL_COLUMN"
,rule_type='SOFTSELECT_HOOK'
,rule_value="services/validations/columns_in_libds"
,rule_active=1
,tx_to='31DEC5999:23:59:59'dt;
insert into &lib..MPE_VALIDATIONS set
tx_from=0
,base_lib="&lib"
,base_ds="MPE_TABLES"
,base_col="LIBREF"
,rule_type='SOFTSELECT_HOOK'
,rule_value="services/validations/libraries_all"
,rule_active=1
,tx_to='31DEC5999:23:59:59'dt;
insert into &lib..MPE_VALIDATIONS set
tx_from=0
,base_lib="&lib"
,base_ds="MPE_TABLES"
,base_col="DSN"
,rule_type='SOFTSELECT_HOOK'
,rule_value="services/validations/tables_all"
,rule_active=1
,tx_to='31DEC5999:23:59:59'dt;
insert into &lib..MPE_VALIDATIONS set
tx_from=0
,base_lib="&lib"
,base_ds="MPE_TABLES"
,base_col="VAR_TXFROM"
,rule_type='SOFTSELECT_HOOK'
,rule_value="services/validations/columns_in_libds"
,rule_active=1
,tx_to='31DEC5999:23:59:59'dt;
insert into &lib..MPE_VALIDATIONS set
tx_from=0
,base_lib="&lib"
,base_ds="MPE_TABLES"
,base_col="VAR_TXTO"
,rule_type='SOFTSELECT_HOOK'
,rule_value="services/validations/columns_in_libds"
,rule_active=1
,tx_to='31DEC5999:23:59:59'dt;
insert into &lib..MPE_VALIDATIONS set
tx_from=0
,base_lib="&lib"
,base_ds="MPE_TABLES"
,base_col="VAR_BUSFROM"
,rule_type='SOFTSELECT_HOOK'
,rule_value="services/validations/columns_in_libds"
,rule_active=1
,tx_to='31DEC5999:23:59:59'dt;
insert into &lib..MPE_VALIDATIONS set
tx_from=0
,base_lib="&lib"
,base_ds="MPE_TABLES"
,base_col="VAR_BUSTO"
,rule_type='SOFTSELECT_HOOK'
,rule_value="services/validations/columns_in_libds"
,rule_active=1
,tx_to='31DEC5999:23:59:59'dt;
insert into &lib..MPE_VALIDATIONS set
tx_from=0
,base_lib="&lib"
,base_ds="MPE_TABLES"
,base_col="VAR_PROCESSED"
,rule_type='SOFTSELECT_HOOK'
,rule_value="services/validations/columns_in_libds"
,rule_active=1
,tx_to='31DEC5999:23:59:59'dt;
insert into &lib..MPE_VALIDATIONS set
tx_from=0
,base_lib="&lib"
,base_ds="MPE_SELECTBOX"
,base_col="SELECT_LIB"
,rule_type='SOFTSELECT_HOOK'
,rule_value="services/validations/libraries_editable"
,rule_active=1
,tx_to='31DEC5999:23:59:59'dt;
insert into &lib..MPE_VALIDATIONS set
tx_from=0
,base_lib="&lib"
,base_ds="MPE_SELECTBOX"
,base_col="SELECT_DS"
,rule_type='SOFTSELECT_HOOK'
,rule_value="services/validations/tables_editable"
,rule_active=1
,tx_to='31DEC5999:23:59:59'dt;
insert into &lib..MPE_VALIDATIONS set
tx_from=0
,base_lib="&lib"
,base_ds="MPE_SELECTBOX"
,base_col="BASE_COLUMN"
,rule_type='SOFTSELECT_HOOK'
,rule_value="services/validations/columns_in_libds"
,rule_active=1
,tx_to='31DEC5999:23:59:59'dt;
Reference in New Issue View Git Blame Copy Permalink