chore: 整理数据初始化脚本
diff --git a/deploy-manifests/k8s-rancher/open-platform/1.1.init-poa-service.sql b/deploy-manifests/k8s-rancher/open-platform/1.1.init-poa-service.sql
index 875dbcf..66cda66 100644
--- a/deploy-manifests/k8s-rancher/open-platform/1.1.init-poa-service.sql
+++ b/deploy-manifests/k8s-rancher/open-platform/1.1.init-poa-service.sql
@@ -1,13 +1,17 @@
-- 1.1.init-poa-service.sql
-/*
+-- 将以下 sql 生成的 insert 语句，在 developer_center 下进行执行
+
+-- SERVICE 记录
SELECT CONCAT(
'INSERT INTO \`TB_DEV_SERVICE\` (ID,DELETED, NAME, DESCRIPTION, ORIGIN, SYSTEM_ID) VALUES',
'(\'', ID, '\',\'0\',\'', NAME, '\',\'', DESCRIPTION, '\',\'', ORIGIN, '\',\'','1','\');') INFO
-FROM platform_openapi_test.SERVICE
+FROM platform_openapi.SERVICE
order by ID
;
-*/
+
+/*
+DELETE FROM TB_DEV_SERVICE;
INSERT INTO `TB_DEV_SERVICE` (ID,DELETED, NAME, DESCRIPTION, ORIGIN, SYSTEM_ID) VALUES('admincenter','0','管理中心','管理中心','http://admin-center-poa-svc.admin-center.svc.cluster.local:8080','1');
INSERT INTO `TB_DEV_SERVICE` (ID,DELETED, NAME, DESCRIPTION, ORIGIN, SYSTEM_ID) VALUES('authz','0','授权服务','授权服务','http://user-authorization-poa-svc.user-authorization-service.svc.cluster.local:8080','1');
@@ -16,48 +20,51 @@
INSERT INTO `TB_DEV_SERVICE` (ID,DELETED, NAME, DESCRIPTION, ORIGIN, SYSTEM_ID) VALUES('messagecenter','0','消息服务','消息服务','http://message-service.paas.nwpu.edu.cn/center/api','1');
INSERT INTO `TB_DEV_SERVICE` (ID,DELETED, NAME, DESCRIPTION, ORIGIN, SYSTEM_ID) VALUES('user','0','用户服务','用户服务','http://user-data-service-poa-svc.user-data-service.svc.cluster.local:8080','1');
INSERT INTO `TB_DEV_SERVICE` (ID,DELETED, NAME, DESCRIPTION, ORIGIN, SYSTEM_ID) VALUES('userThirdparty','0','用户服务（第三方应用）','用户服务（第三方应用）','http://user-data-service-poa-svc.user-data-service.svc.cluster.local:8080','1');
-
-
-/*
-SELECT CONCAT(
- 'INSERT INTO \`TB_DEV_SERVICE_SPEC\` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT) VALUES',
- '(\'', SERVICE_ID,'_',EDIT_VERSION, '\',\'0\',\'', SERVICE_ID, '\',\'', API_VERSION, '\',\'', EDIT_VERSION, '\',\'','','\', 0);') as INSERT_SQL
-FROM platform_openapi_test.API_SPEC
-order by SERVICE_ID, API_VERSION
-;
*/
-INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT) VALUES('admincenter_1602519341813','0','admincenter','v1','1602519341813','', 2);
-INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT) VALUES('authz_1609773572799','0','authz','v1','1609773572799','', 1);
-INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT) VALUES('communicate_1609773573086','0','communicate','v1','1609773573086','', 2);
-INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT) VALUES('echo_1575287118791','0','echo','v1','1575287118791','', 4);
-INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT) VALUES('echo_1575432281641','0','echo','v1','1575432281641','', 4);
-INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT) VALUES('echo_1575524533071','0','echo','v1','1575524533071','', 4);
-INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT) VALUES('messagecenter_1597927983542','0','messagecenter','v1','1597927983542','', 3);
-INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT) VALUES('user_1609773572180','0','user','v1','1609773572180','', 7);
-INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT) VALUES('userThirdparty_1609773572595','0','userThirdparty','v1','1609773572595','', 1);
-
+-- OAS 文档 记录
+SELECT CONCAT(
+ 'INSERT INTO \`TB_DEV_SERVICE_SPEC\` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT, PUBLISHED, ENABLED, USE_STATUS) VALUES',
+ '(\'', SERVICE_ID,'_',EDIT_VERSION, '\',\'0\',\'', SERVICE_ID, '\',\'', API_VERSION, '\',\'', EDIT_VERSION, '\',\'','','\', 0, 1, 1, 0);') as INSERT_SQL
+FROM platform_openapi.API_SPEC
+order by SERVICE_ID, API_VERSION
+;
/*
+INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT, PUBLISHED, ENABLED, USE_STATUS) VALUES('admincenter_1618843667044','0','admincenter','v1','1618843667044','', 0, 1, 1, 0);
+INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT, PUBLISHED, ENABLED, USE_STATUS) VALUES('authz_1618847916306','0','authz','v1','1618847916306','', 0, 1, 1, 0);
+INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT, PUBLISHED, ENABLED, USE_STATUS) VALUES('communicate_1609773934184','0','communicate','v1','1609773934184','', 0, 1, 1, 0);
+INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT, PUBLISHED, ENABLED, USE_STATUS) VALUES('echo_1575287118791','0','echo','v1','1575287118791','', 0, 1, 1, 0);
+INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT, PUBLISHED, ENABLED, USE_STATUS) VALUES('echo_1575432281641','0','echo','v1','1575432281641','', 0, 1, 1, 0);
+INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT, PUBLISHED, ENABLED, USE_STATUS) VALUES('echo_1575524533071','0','echo','v1','1575524533071','', 0, 1, 1, 0);
+INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT, PUBLISHED, ENABLED, USE_STATUS) VALUES('messagecenter_1615306817363','0','messagecenter','v1','1615306817363','', 0, 1, 1, 0);
+INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT, PUBLISHED, ENABLED, USE_STATUS) VALUES('messagecenter_1615364096757','0','messagecenter','v1','1615364096757','', 0, 1, 1, 0);
+INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT, PUBLISHED, ENABLED, USE_STATUS) VALUES('user_1618846743727','0','user','v1','1618846743727','', 0, 1, 1, 0);
+INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT, PUBLISHED, ENABLED, USE_STATUS) VALUES('userThirdparty_1618846743994','0','userThirdparty','v1','1618846743994','', 0, 1, 1, 0);
+INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT, PUBLISHED, ENABLED, USE_STATUS) VALUES('userTransout_1618846744130','0','userTransout','v1','1618846744130','', 0, 1, 1, 0);
+*/
+
+
+-- Scope 记录
SELECT CONCAT(
'INSERT INTO \`TB_DEV_SERVICE_SCOPE\` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES',
'(\'', SERVICE_ID,'_',SCOPE, '\',\'0\',\'', SERVICE_ID, '\',\'', API_VERSION, '\',\'', SCOPE, '\',\'','','\', 1, 1);') as INSERT_SQL
from (
select SERVICE_ID, API_VERSION, EDIT_VERSION, SCOPE
- from platform_openapi_test.API
+ from platform_openapi.API
where EDIT_VERSION in
(
select max(EDIT_VERSION) as EDIT_VERSION
- from platform_openapi_test.API
+ from platform_openapi.API
group by SERVICE_ID, API_VERSION, SCOPE
)
group by SERVICE_ID, API_VERSION, EDIT_VERSION, SCOPE
) API_TMP
order by SERVICE_ID, API_VERSION, EDIT_VERSION, SCOPE
;
-*/
+/*
INSERT INTO `TB_DEV_SERVICE_SCOPE` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES('admincenter:v1:readAdminCenterPermission','0','admincenter','v1','admincenter:v1:readAdminCenterPermission','读取权限', 1, 1);
INSERT INTO `TB_DEV_SERVICE_SCOPE` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES('admincenter:v1:readMenu','0','admincenter','v1','admincenter:v1:readMenu','读取后台菜单', 1, 1);
INSERT INTO `TB_DEV_SERVICE_SCOPE` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES('authz:v1:readRole','0','authz','v1','authz:v1:readRole','读取角色', 1, 1);
@@ -78,5 +85,18 @@
INSERT INTO `TB_DEV_SERVICE_SCOPE` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES('user:v1:readUserSecret','0','user','v1','user:v1:readUserSecret','读取用户私密信息', 1, 1);
INSERT INTO `TB_DEV_SERVICE_SCOPE` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES('user:v1:writePost','0','user','v1','user:v1:writePost','写入岗位', 1, 1);
INSERT INTO `TB_DEV_SERVICE_SCOPE` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES('userThirdparty:v1:adminAppGroupWrite','0','userThirdparty','v1','userThirdparty:v1:adminAppGroupWrite','写入应用的用户组（普通）', 1, 1);
+*/
+
+
+-- 统计 各 SERVICE 的 Scope 数，更新 TB_DEV_SERVICE_SPEC 的 SCOPE_COUNT 字段
+UPDATE TB_DEV_SERVICE_SPEC sp, (
+ SELECT SERVICE_ID, API_VERSION, count(1) as SCOPE_COUNT
+ FROM TB_DEV_SERVICE_SCOPE
+ GROUP BY SERVICE_ID, API_VERSION
+) sc
+SET sp.SCOPE_COUNT=sc.SCOPE_COUNT
+WHERE sp.SERVICE_ID=sc.SERVICE_ID
+ AND sp.API_VERSION=sc.API_VERSION
+;