MAS 模块化应用支撑平台 ​

DROP TABLE IF EXISTS "t_api_manage";
CREATE TABLE "t_api_manage" (
	"c_id" serial8 NOT NULL,
	"c_last_modifier" int8,
	"c_last_modify_time" TIMESTAMP ( 6 ),
	"c_creator" int8,
	"c_create_time" TIMESTAMP ( 6 ),
	"c_sort_number" int4,
	"c_tenant_id" int8,
	"c_type" VARCHAR ( 10 ) COLLATE "pg_catalog"."default",
	"c_module" VARCHAR ( 50 ) COLLATE "pg_catalog"."default",
	"c_path" VARCHAR ( 255 ) COLLATE "pg_catalog"."default",
	"c_remark" VARCHAR ( 500 ) COLLATE "pg_catalog"."default",
	CONSTRAINT "t_api_manage_pkey" PRIMARY KEY ( "c_id" )
) WITH ( OIDS = TRUE );
COMMENT ON COLUMN "t_api_manage"."c_id" IS '主键';
COMMENT ON COLUMN "t_api_manage"."c_last_modifier" IS '修改人';
COMMENT ON COLUMN "t_api_manage"."c_last_modify_time" IS '修改时间';
COMMENT ON COLUMN "t_api_manage"."c_creator" IS '创建人';
COMMENT ON COLUMN "t_api_manage"."c_create_time" IS '创建时间';
COMMENT ON COLUMN "t_api_manage"."c_sort_number" IS '排序号';
COMMENT ON COLUMN "t_api_manage"."c_tenant_id" IS '租户ID';
COMMENT ON COLUMN "t_api_manage"."c_type" IS '类型';
COMMENT ON COLUMN "t_api_manage"."c_module" IS '模块';
COMMENT ON COLUMN "t_api_manage"."c_path" IS '路径';
COMMENT ON COLUMN "t_api_manage"."c_remark" IS '描述';

--初始化mas-wps预览放行接口：
INSERT INTO t_api_manage(c_last_modifier,c_last_modify_time,c_creator,c_create_time,c_sort_number,c_type,c_module,c_path,c_remark) VALUES (1, '2023-09-15 15:17:53.895', 1, '2023-09-15 15:17:53.895', 1, '', 'mas-wps', '/mas-wps/v2/3rd/**', 'mas-wps中台接口');
INSERT INTO t_api_manage(c_last_modifier,c_last_modify_time,c_creator,c_create_time,c_sort_number,c_type,c_module,c_path,c_remark) VALUES (1, '2023-09-15 15:18:16.785', 1, '2023-09-15 15:18:16.785', 2, '', 'mas-wps', '/mas-wps/v1/3rd/**', 'mas-wps中台接口');
INSERT INTO t_api_manage(c_last_modifier,c_last_modify_time,c_creator,c_create_time,c_sort_number,c_type,c_module,c_path,c_remark)  VALUES (1, '2023-09-15 15:18:40.948', 1, '2023-09-15 15:18:40.948',3, '', 'mas-wps', '/mas-wps/download/attach', 'mas-wps中台回调下载接口');
INSERT INTO t_api_manage(c_last_modifier,c_last_modify_time,c_creator,c_create_time,c_sort_number,c_type,c_module,c_path,c_remark)  VALUES (1, '2023-09-15 15:18:59.719', 1, '2023-09-15 15:18:59.719', 4, '', 'mas-wps', '/mas-wps/download/gz', 'mas-wps中台回调稿纸接口');
INSERT INTO t_api_manage(c_last_modifier,c_last_modify_time,c_creator,c_create_time,c_sort_number,c_type,c_module,c_path,c_remark)  VALUES (1, '2023-09-15 15:18:59.719', 1, '2023-09-15 15:18:59.719', 5, '', 'mas-file', '/mas-file/file/download4wpsproxy', 'mas-file中台回调下载文件接口');
INSERT INTO t_api_manage(c_last_modifier,c_last_modify_time,c_creator,c_create_time,c_sort_number,c_type,c_module,c_path,c_remark)  VALUES (1, '2023-09-15 15:18:59.719', 1, '2023-09-15 15:18:59.719', 6, '', 'mas-file', '/mas-file/file/fileinfo4wpsproxy', 'mas-file中台回调获取文件信息接口');
INSERT INTO t_api_manage(c_last_modifier,c_last_modify_time,c_creator,c_create_time,c_sort_number,c_type,c_module,c_path,c_remark)  VALUES (1, '2023-09-15 15:18:59.719', 1, '2023-09-15 15:18:59.719', 7, '', 'mas-file', '/mas-file/file/downloadByOsskey4wpsproxy', 'mas-file中台回调oss下载文件接口');
INSERT INTO t_api_manage(c_last_modifier,c_last_modify_time,c_creator,c_create_time,c_sort_number,c_type,c_module,c_path,c_remark)  VALUES (1, '2023-09-15 15:18:59.719', 1, '2023-09-15 15:18:59.719', 8, '', 'mas-file', '/mas-file/file/fileinfoByOsskey4wpsproxy', 'mas-file中台回调获取oss文件信息接口');

--初始化登录认证接口放行
INSERT INTO "t_api_manage"( "c_last_modifier", "c_last_modify_time", "c_creator", "c_create_time", "c_sort_number", "c_tenant_id", "c_type", "c_module", "c_path", "c_remark") VALUES (1, '15-APR-24 20:06:56.809', 1, '15-APR-24 20:06:56.809', 1, NULL, '', 'mas-file', '/mas-file/file/downloadByReferenceId', '文件下载');
INSERT INTO "t_api_manage"( "c_last_modifier", "c_last_modify_time", "c_creator", "c_create_time", "c_sort_number", "c_tenant_id", "c_type", "c_module", "c_path", "c_remark") VALUES (NULL, NULL, NULL, NULL, NULL, NULL, '', 'fbp', '/fbp/app/lc-userpwdlogin/**', 'fbp账号密码登录退出接口');
INSERT INTO "t_api_manage"( "c_last_modifier", "c_last_modify_time", "c_creator", "c_create_time", "c_sort_number", "c_tenant_id", "c_type", "c_module", "c_path", "c_remark") VALUES (NULL, NULL, NULL, NULL, NULL, NULL, '', 'fbp', '/fbp/app/lc-zzdlogin/**', '浙政钉登录');
INSERT INTO "t_api_manage"( "c_last_modifier", "c_last_modify_time", "c_creator", "c_create_time", "c_sort_number", "c_tenant_id", "c_type", "c_module", "c_path", "c_remark") VALUES (NULL, NULL, NULL, NULL, NULL, NULL, '', 'fbp', '/fbp/app/lc-qrlogin/**', 'PC扫码登录');
INSERT INTO "t_api_manage"( "c_last_modifier", "c_last_modify_time", "c_creator", "c_create_time", "c_sort_number", "c_tenant_id", "c_type", "c_module", "c_path", "c_remark") VALUES (NULL, NULL, NULL, NULL, NULL, NULL, '', 'fbp', '/fbp/app/lc-fbpsso/**', 'fbp单点登录');
INSERT INTO "t_api_manage"( "c_last_modifier", "c_last_modify_time", "c_creator", "c_create_time", "c_sort_number", "c_tenant_id", "c_type", "c_module", "c_path", "c_remark") VALUES (NULL, NULL, NULL, NULL, NULL, NULL, '', 'fbp', '/fbp/app/boot/1.0.0/getConfig', '获取站点配置');
INSERT INTO "t_api_manage"( "c_last_modifier", "c_last_modify_time", "c_creator", "c_create_time", "c_sort_number", "c_tenant_id", "c_type", "c_module", "c_path", "c_remark") VALUES (NULL, NULL, NULL, NULL, NULL, NULL, '', 'fbp', '/fbp/app/lc-usernamelogin/**', '用户名单点登录');
INSERT INTO "t_api_manage"( "c_last_modifier", "c_last_modify_time", "c_creator", "c_create_time", "c_sort_number", "c_tenant_id", "c_type", "c_module", "c_path", "c_remark") VALUES (NULL, NULL, NULL, NULL, NULL, NULL, '', 'fbp', '/fbp/app/lc-fbpsso/**', 'FBP单点登录（含扫码预览）');
INSERT INTO "t_api_manage"( "c_last_modifier", "c_last_modify_time", "c_creator", "c_create_time", "c_sort_number", "c_tenant_id", "c_type", "c_module", "c_path", "c_remark") VALUES (NULL, NULL, NULL, NULL, NULL, NULL, '', 'fbp', '/fbp/app/lc-ythlogin/**', '一体化单点登录');

ALTER TABLE cos_portal_component ADD COLUMN c_ref_id VARCHAR(200);
COMMENT ON COLUMN "cos_portal_component"."c_ref_id" IS '应用文件id';

ALTER TABLE cos_portal_component ADD COLUMN c_setting text;
COMMENT ON COLUMN "cos_portal_component"."c_setting" IS '应用配置';

ALTER TABLE cos_site_component_grant ADD COLUMN c_setting text;
COMMENT ON COLUMN "cos_site_component_grant"."c_setting" IS '应用配置';


-- ----------------------------
-- Table structure for cos_admin_site_group_info
-- ----------------------------
DROP TABLE IF EXISTS "cos_admin_site_group_info";
CREATE TABLE "cos_admin_site_group_info" (
  "c_id" serial8 NOT NULL,
  "c_last_modifier" int8,
  "c_last_modify_time" timestamp(6),
  "c_creator" int8,
  "c_create_time" timestamp(6),
  "c_sort_number" int4,
  "c_tenant_id" int8,
  "c_tenant_code" varchar(32) ,
  "c_code" varchar(32) ,
  "c_name" varchar(32) ,
  "c_activate_time" timestamp(6),
  "c_invalid_time" timestamp(6),
  CONSTRAINT "cos_admin_site_group_info_pkey" PRIMARY KEY ("c_id")
);
COMMENT ON COLUMN "cos_admin_site_group_info"."c_id" IS '主键';
COMMENT ON COLUMN "cos_admin_site_group_info"."c_last_modifier" IS '修改人';
COMMENT ON COLUMN "cos_admin_site_group_info"."c_last_modify_time" IS '修改时间';
COMMENT ON COLUMN "cos_admin_site_group_info"."c_creator" IS '创建人';
COMMENT ON COLUMN "cos_admin_site_group_info"."c_create_time" IS '创建时间';
COMMENT ON COLUMN "cos_admin_site_group_info"."c_sort_number" IS '排序号';
COMMENT ON COLUMN "cos_admin_site_group_info"."c_tenant_id" IS '租户ID';
COMMENT ON COLUMN "cos_admin_site_group_info"."c_tenant_code" IS '租户代码';
COMMENT ON COLUMN "cos_admin_site_group_info"."c_code" IS '站群代码';
COMMENT ON COLUMN "cos_admin_site_group_info"."c_name" IS '站群名称';
COMMENT ON COLUMN "cos_admin_site_group_info"."c_activate_time" IS '生效时间';
COMMENT ON COLUMN "cos_admin_site_group_info"."c_invalid_time" IS '失效时间';

-- 调整表结构
ALTER TABLE cos_admin_site_info ADD COLUMN c_site_group_id int8;
ALTER TABLE cos_admin_site_info ADD COLUMN c_site_group_code varchar(50);
ALTER TABLE cos_admin_site_info ADD COLUMN c_is_manage_site bool;

ALTER TABLE cos_admin_account_info ADD COLUMN c_site_group_id int8;
ALTER TABLE cos_admin_account_tenant ADD COLUMN c_site_group_id int8;
ALTER TABLE cos_admin_site_info ALTER COLUMN c_contact type varchar(50);

--账号表新增字段
ALTER TABLE cos_admin_account_info ADD COLUMN c_identity_code VARCHAR(100);
COMMENT ON COLUMN "cos_admin_account_info"."c_identity_code" IS '身份编码';

ALTER TABLE cos_admin_account_info ADD COLUMN c_user_code VARCHAR(100);
COMMENT ON COLUMN "cos_admin_account_info"."c_user_code" IS '用户编码';

ALTER TABLE cos_admin_account_info ADD COLUMN c_identity_desc VARCHAR(500);
COMMENT ON COLUMN "cos_admin_account_info"."c_identity_desc" IS '身份描述信息';

ALTER TABLE cos_admin_role_info ADD COLUMN c_site_group_id int8;
COMMENT ON COLUMN "cos_admin_role_info"."c_site_group_id" IS '站群id';

ALTER TABLE cos_admin_role_mapping ADD COLUMN c_site_group_id int8;
COMMENT ON COLUMN "cos_admin_role_mapping"."c_site_group_id" IS '站群id';

--升级平台管理租户为站群，其它租户等升级完后用升级应用进行升级
update cos_admin_site_info set c_is_manage_site = false;
--初始化平台站群
INSERT INTO "cos_admin_site_group_info"("c_last_modifier", "c_last_modify_time", "c_creator", "c_create_time", "c_sort_number", "c_tenant_id", "c_tenant_code", "c_code", "c_name", "c_activate_time", "c_invalid_time") VALUES (1, '11-JUL-24 16:59:10.659737', 3, '11-JUL-24 16:59:10.659737', 1, 1, 'sys', 'sys_sg', '默认站群', NULL, NULL);
--初始化平台站群管理站点
INSERT INTO "cos_admin_site_info"("c_last_modifier", "c_last_modify_time", "c_creator", "c_create_time", "c_sort_number", "c_site_type", "c_title", "c_theme", "c_logo", "c_code", "c_name", "c_contact", "c_mobile", "c_email", "c_activate_time", "c_invalid_time", "c_site_domain", "c_inited", "c_tenant_id", "c_site_setting", "c_tenant_code", "c_app_key", "c_app_secret", "c_manage_sites", "c_site_group_id", "c_is_manage_site", "c_site_group_code") VALUES (1, '16-JUL-24 11:25:36.263', 1, '11-JUL-24 16:59:10', 1, 0, NULL, NULL, NULL, 'sys_sg_manage', '默认站点', 'sys_sg_manage_admin', NULL, NULL, NULL, NULL, NULL, NULL, 1, '{"appKey":"","appSecret":"","systemName":"平台管理站","shortName":"平台管理站"}', 'sys', '', '', NULL, (select c_id from cos_admin_site_group_info where c_code = 'sys_sg'), 't', 'sys_sg');
--初始化平台站群管理站点账号
INSERT INTO "cos_admin_account_info"("c_granted_apis", "c_account_expired", "c_account_locked", "c_credentials_expired", "c_last_modifier", "c_last_modify_time", "c_creator", "c_create_time", "c_sort_number", "c_tenant_id", "c_login_name", "c_login_dingding", "c_login_phone", "c_login_email", "c_status", "c_password_policy", "c_password", "c_mobile_device", "c_allow_mobile_access", "c_last_login_time", "c_last_login_client_ip", "c_last_login_failed", "c_default_tenant", "c_account_source", "c_account_type", "c_full_name", "c_tag", "c_theme") VALUES ('/**', '0', '0', '0', 1, now(), NULL, NULL, 1, (select c_id from cos_admin_tenant_info where c_code = 'sys'), 'sys_sg_manage_admin', NULL, NULL, NULL, '1', NULL, '$2a$10$7B1jfrju3iOGFd8F5dfZzuCgReqJBqasvSPyO0oYtNCP3n2h69Q8C', NULL, NULL, NULL, NULL, NULL, 'sys', NULL, 11, '平台站群管理员', NULL, NULL);
--将账号关联站群
update cos_admin_account_info set c_site_group_id = (select c_id from cos_admin_site_group_info where c_code = 'sys_sg') where c_tenant_id = (select c_id from cos_admin_tenant_info where c_code = 'sys');
--将站群管理账号授权到管理站点
INSERT INTO "cos_admin_account_tenant"("c_last_modifier", "c_last_modify_time", "c_creator", "c_create_time", "c_sort_number", "c_tenant_id", "c_account_id", "c_site_id", "c_theme") VALUES (1, '15-AUG-23 13:24:52.813', '1', '15-AUG-23 13:24:52.813', 1, (select c_id from cos_admin_tenant_info where c_code = 'sys'), (select c_id from cos_admin_account_info  where c_tenant_id = (select c_id from cos_admin_tenant_info where c_code = 'sys') and c_login_name = 'sys_sg_manage_admin'), (select c_id  from cos_admin_site_info where c_code = 'sys_sg_manage'), NULL);
--将管理平台账号关联站群
update cos_admin_account_tenant set c_site_group_id = (select c_id from cos_admin_site_group_info where c_code ='sys_sg') where c_tenant_id = (select c_id from cos_admin_tenant_info where c_code = 'sys');
--将平台租户下的站点关联进站群
update cos_admin_site_info set c_site_group_id = (select c_id from cos_admin_site_group_info where c_code = 'sys_sg'),c_site_group_code = 'sys_sg' where c_tenant_code = 'sys';
-- 将平台租户下的角色关联到平台站群
update cos_admin_role_info set c_site_group_id = (select c_id from cos_admin_site_group_info where c_code = 'sys_sg') where c_tenant_id = (select c_id from cos_admin_tenant_info where c_code = 'sys');
-- 清理平台站点初始化菜单数据，以便上架后的应用菜单能出来
delete from cos_admin_init_menu where c_site_id = (select c_id from cos_admin_site_info where c_code = 'sys')

ALTER TABLE "cos_quartz_job"
ADD COLUMN "c_job_params" varchar(4000),
ADD COLUMN "c_method_url" varchar(2000);

-----------------------------------------------------------------------------------
 -- 低代码fbp开发的应用如果有DSC配置，在应用初始化进去后需要将配置数据初始化进去,否则运行报错
 -----------------------------------------------------------------------------------
-- lc-userpwdlogin 登录应用
update cos_portal_component SET c_setting = '[{"code":"datasource","name":"数据源配置","config":[{"catalog":"1","tenantId":3,"project":"userpwdlogin","id":143,"type":"1","value":"mas","version":"v1.0.0","key":"ds1","desc":"mas平台"},{"catalog":"1","tenantId":3,"project":"userpwdlogin","id":135,"type":"1","value":"locas","version":"v1.0.0","key":"ds2","desc":"FBP平台"}]},{"code":"cache","name":"缓存配置","config":[{"catalog":"2","tenantId":3,"project":"userpwdlogin","id":160,"type":"1","value":"sys_cache_locas","version":"v1.0.0","key":"ch1","desc":"系统缓存"}]}]' WHERE c_code = 'lc-userpwdlogin';
-- lc-syscachemng 缓存应用
update cos_portal_component SET c_setting = '[{"code":"datasource","name":"数据源配置","config":[{"catalog":"1","tenantId":3,"project":"syscachemng","id":148,"type":"1","value":"mas","version":"v1.0.0","key":"ds1","desc":"mas平台数据源"}]},{"code":"cache","name":"缓存配置","config":[{"catalog":"2","tenantId":3,"project":"syscachemng","id":149,"type":"1","value":"sys_cache_locas","version":"v1.0.0","key":"ch1","desc":"系统缓存"}]}]' WHERE c_code = 'lc-syscachemng';

-- 将5个新应用授权给平台租户sys
insert into cos_portal_component_grant(c_tenant_id,c_app_code) values((select c_id from cos_admin_tenant_info where c_code = 'sys'),'mas-explorer');
insert into cos_portal_component_grant(c_tenant_id,c_app_code) values((select c_id from cos_admin_tenant_info where c_code = 'sys'),'lc-sm2');
insert into cos_portal_component_grant(c_tenant_id,c_app_code) values((select c_id from cos_admin_tenant_info where c_code = 'sys'),'lc-syscachemng');
insert into cos_portal_component_grant(c_tenant_id,c_app_code) values((select c_id from cos_admin_tenant_info where c_code = 'sys'),'lc-userpwdlogin');
insert into cos_portal_component_grant(c_tenant_id,c_app_code) values((select c_id from cos_admin_tenant_info where c_code = 'sys'),'mas-ops-sitegroup');

-- 将应用授权给给sys站点
insert into cos_site_component_grant(c_tenant_id,c_site_id,c_app_code) values((select c_id from cos_admin_tenant_info where c_code = 'sys'),(select c_id from cos_admin_site_info where c_code = 'sys'),'mas-explorer');
insert into cos_site_component_grant(c_tenant_id,c_site_id,c_app_code) values((select c_id from cos_admin_tenant_info where c_code = 'sys'),(select c_id from cos_admin_site_info where c_code = 'sys'),'lc-userpwdlogin');
insert into cos_site_component_grant(c_tenant_id,c_site_id,c_app_code) values((select c_id from cos_admin_tenant_info where c_code = 'sys'),(select c_id from cos_admin_site_info where c_code = 'sys'),'lc-sm2');
insert into cos_site_component_grant(c_tenant_id,c_site_id,c_app_code) values((select c_id from cos_admin_tenant_info where c_code = 'sys'),(select c_id from cos_admin_site_info where c_code = 'sys'),'mas-ops-sitegroup');
insert into cos_site_component_grant(c_tenant_id,c_site_id,c_app_code) values((select c_id from cos_admin_tenant_info where c_code = 'sys'),(select c_id from cos_admin_site_info where c_code = 'sys'),'lc-syscachemng');