流水线监控

监听新 projId，自动补全内容，获取从 20240101 到当前的所有流水线信息

监听功能未知原因不好用，先不做了，改手动遍历了

拿到 project_id 后，获取数据表中最新的 pipeline 的 Id，然后比对接口中的 ID 进行填充

如果没有 pipeline 的 id，直接从接口中获取 20240101 到当前的流水线信息

先从数据中获取用户信息填充，随后在根据填充完的用户信息获取全部的 userid 的列表，再写 pipeline 表

流水线成功提醒

接收Gitlab 的 WebHooks请求，Secret 令牌 实际上为 Egg 的 API_KEY

获取对应的MR信息，MR 的发起者也需要发送通知

组织卡片信息，给 Commit的用户以及 可能的 MR发起者发送通知

处理在中间Stage需要提醒的情况

在stage全部成功的情况下，按finish_at时间排序，找到最后一个stage，如果stage的状态是成功，且该build的id没有发送过通知，则发送通知

流水线通知只是在Pipeline纬度上，所以某个stage的变化不会触发通知

在流水线为running时，如果需要监听Stage，且该stage全部的job有非结束状态时即created、pending、running、manual、scheduled时

加入数据库监控列表，每10s检查一次，如果stage状态全部成功的时候则发送通知，并删除监控

在流水线结束，即状态为failed、canceled、skipped、success，的时候，删除监控，并在状态为success的时候，发送通知

数据库表信息

数据库地址

project 信息

{
  id: 'aaa',
  project_id: 131366,
  description: "场景复现平台-展示设备（移动、音箱、小爱建议、车载、手表等设备）上小爱执行结果及相关处理流程",
  name: "ai-scene-review-fe",
  path_with_namespace: "miai-fe/fe/ai-scene-review-fe",
  web_url: "https://git.n.xiaomi.com/miai-fe/fe/ai-scene-review-fe",
  avatar_url: null,
  has_new_cicd: false,
}

pipeline 信息

{
  id: 'bbb',
  project_id: 'aaa',
  user_id: 'ccc',
  pipeline_id: 7646046,
  ref: "preview",
  status: "success",
  web_url: "https://git.n.xiaomi.com/miai-fe/fe/ai-scene-review-fe/-/pipelines/7646046",
  started_at: "2024-03-01T16:47:40.192+08:00",
  finished_at: "2024-03-01T16:49:30.624+08:00",
  duration: 100,
  queued_duration: 6,
}

user 信息

{
  id: 'ccc',
  user_id: 10011,
  username: "zhaoyingbo",
  name: "赵英博",
  avatar_url: "https://git.n.xiaomi.com/uploads/-/system/user/avatar/10011/avatar.png",
  web_url: "https://git.n.xiaomi.com/zhaoyingbo"
}

每周每个项目按分支的运行时长统计 SQL statisticsPerProj

SELECT
  (ROW_NUMBER() OVER()) as id,
  strftime('%Y-%W', datetime(pip.started_at)) AS week,
  p.name AS name,
  ROUND(AVG(pip.duration/60.0), 1) AS duration,
  pip.ref
FROM project p
JOIN pipeline pip ON p.id = pip.project_id
GROUP BY name, week, pip.ref;

每周流水线运行统计 SQL statisticsPerWeek

SELECT
  (ROW_NUMBER() OVER()) as id,
  strftime('%Y-%W', datetime(started_at)) AS week,
  COUNT(*) AS total_count,
  SUM(CASE WHEN status = 'success' THEN 0 ELSE 1 END) AS failed_count,
  SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS success_count,
  ROUND(SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS success_rate,
  ROUND(AVG(duration/60.0), 1) AS duration
FROM pipeline
GROUP BY week;

GPT

我有一个 sqlite 数据库，表如下 project 表

{
  id: 'aaa',
  project_id: 131366,
  description: "场景复现平台-展示设备（移动、音箱、小爱建议、车载、手表等设备）上小爱执行结果及相关处理流程",
  name: "ai-scene-review-fe",
  path_with_namespace: "miai-fe/fe/ai-scene-review-fe",
  web_url: "https://git.n.xiaomi.com/miai-fe/fe/ai-scene-review-fe",
  avatar_url: null,
  has_new_cicd: false,
}

pipeline 表

{
  id: 'bbb',
  project_id: 'aaa',
  user_id: 'ccc',
  pipeline_id: 7646046,
  ref: "preview",
  status: "success",
  web_url: "https://git.n.xiaomi.com/miai-fe/fe/ai-scene-review-fe/-/pipelines/7646046",
  started_at: "2024-03-01T16:47:40.192+08:00",
  finished_at: "2024-03-01T16:49:30.624+08:00",
  duration: 100,
  queued_duration: 6,
}

user 表

{
  id: 'ccc',
  user_id: 10011,
  username: "zhaoyingbo",
  name: "赵英博",
  avatar_url: "https://git.n.xiaomi.com/uploads/-/system/user/avatar/10011/avatar.png",
  web_url: "https://git.n.xiaomi.com/zhaoyingbo"
}

我想按天展示每个项目的 pipeline 按 ref 区分的平均 duration，如何创建视图

机器人

CICD 统计总结

卡片 ID：ctp_AAyVLS6Q37cL

JSON 示例

{
  "total_count": "29", // OK
  "group_table": [
    {
      "project_name": "ai-ak-fe",
      "project_ref": "master",
      "project_duration": "1.4",
      "project_duration_rate": "<font color='green'>↓12%</font>"
    },
    {
      "project_name": "ai-class-schedule-fe",
      "project_ref": "preview",
      "project_duration": "3.2",
      "project_duration_rate": "<font color='red'>↑5%</font>"
    },
    {
      "project_name": "ai-scene-review-fe",
      "project_ref": "staging",
      "project_duration": "5.4",
      "project_duration_rate": "<font color='green'>↓6%</font>"
    }
  ],
  "weekly_count_rate": "<font color='red'>较上周 ↑5%</font>", // OK
  "weekly_duration_rate": "<font color='green'>较上周 ↓12%</font>", // OK
  "weekly_success_rate": "<font color='red'>较上周 ↑12%</font>", // OK
  "duration": "0.9", // OK
  "success_rate": "100", // OK
  "has_new_cicd_count": "15", // OK
  "without_new_cicd_count": "20" // OK
}

流水线成功通知

卡片 ID：ctp_AA36QafWyob2

JSON 示例

{
  "project": "cloudml-visuals/fe/test",
  "project_link": "https://git.n.xiaomi.com/cloudml-visuals/fe/test",
  "pipeline": "8815519",
  "pipeline_link": "https://git.n.xiaomi.com/cloudml-visuals/fe/cloud-ml-fe/-/pipelines",
  "ref": "preview",
  "ref_link": "https://git.n.xiaomi.com/cloudml-visuals/fe/cloud-ml-fe/-/commits/preview",
  "commit_user": "赵英博",
  "duration": "1m35s",
  "participant": "赵英博、吴婷",
  "commit_message": "chore: Add .gitlab-ci.yml for printing \"Hello, world!\"",
  "mr": "cloudml-visuals/fe/cloud-ml-fe!374",
  "mr_link": "https://git.n.xiaomi.com/cloudml-visuals/fe/cloud-ml-fe/-/merge_requests/374"
}