gitlab提交代码差异分析

sql>
– txt> discussion 相关统计
with project_discuss_info as (
select
– txt> 统计每次主题讨论中包含的小讨论数目
– A.project_id,A.commit_id,A.line_code,count(1)
A., case when A.resolved_at is null then 0 else 1 end as “C_resolved”
from
notes A
where
1 = 1
– txt> 项目编码,测试使用
– and A.project_id = 245
– txt> 非系统产生的代码修改
and A.“system” = false
– txt> 本次讨论涉及到了代码修改
and A.line_code is not null
– txt> 提交代码不为空
and (A.commit_id is not null and A.commit_id <> ‘’)
– txt> 限制文件的路径{File_Path},每个项目都不相同。
– and A.original_position like ‘%A001_test/%’
– txt> 测试使用
– and A.commit_id = ‘d170e3231ca260823ffa26246471abcf69babb82’
– txt> 对某一次提交的某一行进行讨论,可以存在多条讨论，只有所有的discussion全部resolve则代表该讨论完成。
order by A.created_at desc,project_id, A.commit_id desc,A.line_code desc
– group by project_id, A.commit_id ,A.line_code
),
project_discuss_info_all as (
select A1.project_id, A1.commit_id , A1.line_code, coalesce(count(A1.“C_resolved”),0) as “C_all_discussion”,max(A1.updated_at) as “last_update”
from project_discuss_info A1
group by A1.project_id, A1.commit_id ,A1.line_code
)
– txt> 查询表
– select * from project_discuss_info_all
-------------------------------------------------------------------
,
project_discuss_info_resolved as (
select A1.project_id, A1.commit_id , A1.line_code, coalesce(count(A1.“C_resolved”),0) as “C_has_resolved_discussion”
from project_discuss_info A1
where A1.“C_resolved”=0
group by A1.project_id, A1.commit_id ,A1.line_code , A1.“C_resolved”
)
– txt> 查询表
– select * from project_discuss_info_resolved
-------------------------------------------------------------------
,
project_info as (
select a.id
,(select D.“name”
from public.namespaces d
where d.id = a.namespace_id) as “分组|个人”
,b.username as “创建人”
,a.name as “项目名称”
,a.created_at
,a.last_activity_at
,a.description
from public.projects a
inner join public.users b
on a.creator_id = b.id
order by (select D.“name”
from public.namespaces d
where d.id = a.namespace_id)
,username asc
,a.last_activity_at
)
– txt> 查询表
– select * from project_info
-------------------------------------------------------------------
select T3., T1.*, coalesce (T2.“C_has_resolved_discussion”,0)
from project_discuss_info_all T1
left join project_discuss_info_resolved T2
on ( T1.project_id = T2.project_id
and T1.commit_id = T2.commit_id
and T1.line_code = T2.line_code
)
left join project_info T3 on (
T1.project_id = T3.id
)
order by T1.project_id,
T1.“C_all_discussion” desc,
T2.“C_has_resolved_discussion” desc
;

sql> 项目信息
select a.id
,(select D.“name”
from public.namespaces d
where d.id = a.namespace_id) as “分组|个人”
,b.username as “创建人”
,a.name as “项目名称”
,a.created_at
,a.last_activity_at
,a.description
from public.projects a
inner join public.users b
on a.creator_id = b.id
order by (select D.“name”
from public.namespaces d
where d.id = a.namespace_id)
,username asc
,a.last_activity_at

txt> 代码质量管控需求
1.代码review数统计：
*需要指定project路径，脚本统计整个project下的review数。
*对于同一问题多次讨论只需resolve一次，多次resolve计数多次。
*每次统计结果为project下resolve总数，具体版本数据需要将版本前后两次统计结果相减。
*代码review数统计时机：版本通过转测流程时。
暂由张宁提供结果数据。

2.代码行统计：
*C++代码需要开发负责人提供第三方库路径，脚本统计时排除该路径下文件。（java代码经崔世江确认第三方代码不会被统计到） 
*代码行统计目标：各产品master主干代码的更新情况。
*代码行统计结果包括：新增代码行数、修改代码行数（同一project下只有一个开发团队）
*代码行统计时机：版本通过转测流程时。

txt> gitlab代码量的统计

 txt> 获取项目列表
 utl> http://192.168.20.28/api/v4/projects
 example>
        {
        "id": 266,
        "description": "防火墙前端项目",
        "name": "db-firewall-frontend",
        "name_with_namespace": "zhangyang / db-firewall-frontend",
        "path": "db-firewall-frontend",
        "path_with_namespace": "zhangyang/db-firewall-frontend",
        "created_at": "2021-03-23T03:22:47.984Z",
        "default_branch": "master",
        "tag_list": [],
        "ssh_url_to_repo": "git@192.168.20.28:zhangyang/db-firewall-frontend.git",
        "http_url_to_repo": "http://192.168.20.28/zhangyang/db-firewall-frontend.git",
        "web_url": "http://192.168.20.28/zhangyang/db-firewall-frontend",
        "readme_url": "http://192.168.20.28/zhangyang/db-firewall-frontend/blob/master/README.md",
        "avatar_url": null,
        "star_count": 0,
        "forks_count": 0,
        "last_activity_at": "2021-03-26T00:59:11.524Z",
        "namespace": {
            "id": 156,
            "name": "zhangyang",
            "path": "zhangyang",
            "kind": "user",
            "full_path": "zhangyang",
            "parent_id": null
        },

 txt> 获取项目分支列表
 url> http://192.168.20.28/api/v4/projects/245/repository/branches
 example>
        {
            "name": "feature-3.1.0_zz",
            "commit": {
                "id": "0b611ab4b607ec939bf867d3016c7b3c88f35fd3",
                "short_id": "0b611ab4",
                "title": "更新",
                "created_at": "2021-03-19T16:26:33.000+08:00",
                "parent_ids": null,
                "message": "更新",
                "author_name": "shixurong",
                "author_email": "shixurong@everfort.cn",
                "authored_date": "2021-03-19T16:26:33.000+08:00",
                "committer_name": "shixurong",
                "committer_email": "shixurong@everfort.cn",
                "committed_date": "2021-03-19T16:26:33.000+08:00"
            },
            "merged": false,
            "protected": false,
            "developers_can_push": false,
            "developers_can_merge": false,
            "can_push": true,
            "default": false
        },

 txt> 获取提交记录
 url>  http://192.168.20.28/api/v4/projects/245/repository/commits?ref_name=master
 example> 
        {
            "id": "0da1ebf1eedbe6fbd888342ea3a02cd41e5109a4",
            "short_id": "0da1ebf1",
            "title": "Merge branch 'feature-3.1.0' into 'master'",
            "created_at": "2020-10-28T11:39:19.000Z",
            "parent_ids": [
                "e0ec40990c5bb142f88b0a278f4cc01565b5c87a",
                "24c06290ece83172a49e75fac4389fe898e46029"
            ],
            "message": "Merge branch 'feature-3.1.0' into 'master'\n\nFeature 3.1.0\n\nSee merge request product-group/db-firewall-frontend!1",
            "author_name": "luobin",
            "author_email": "luobin@everfort.cn",
            "authored_date": "2020-10-28T11:39:19.000Z",
            "committer_name": "luobin",
            "committer_email": "luobin@everfort.cn",
            "committed_date": "2020-10-28T11:39:19.000Z"
        },

 txt> 每次提交的代码量
 url>  http://192.168.20.28/api/v4/projects/245/repository/commits/76cd1f40c938e9c4785c55e8f4c264833df7b39c
 example> 
        {
            "id": "7f327322f35daa3d2cc8cd42027b9fc2c4d02822",
            "short_id": "7f327322",
            "title": "增加最后的讨论时间",
            "created_at": "2021-03-24T06:05:44.000Z",
            "parent_ids": [
                "55ca6ee5f82e5f6fade8386b7dd5349fda5bfb18"
            ],
            "message": "增加最后的讨论时间",
            "author_name": "Administrator",
            "author_email": "admin@example.com",
            "authored_date": "2021-03-24T06:05:44.000Z",
            "committer_name": "Administrator",
            "committer_email": "admin@example.com",
            "committed_date": "2021-03-24T06:05:44.000Z",
            "stats": {
                "additions": 1,txt> 增加的行
                "deletions": 1,txt> 删除的行
                "total": 2
            },
            "status": null,
            "last_pipeline": null,
            "project_id": 245
        }

• Snippet:2021-3-26 15:05
  • gitlab代码统计 | gitlab代码对比
  • 问题概述: gitlab代码统计 | gitlab代码对比
  • 方案细节

      txt> compare URL, where ref_source and ref_target can be commit SHA, tag, or branch
      url> https://${gitlab_host}/${repo_path}/compare/${ref_target}...${ref_source}
      example> http://192.168.20.28/root/test-use-project/compare/A001_branch_01...master
    
      txt> tag example 1, comparing tag v1.5.1 to master
      url> https://${gitlab_host}/${repo_path}/compare/v1.5.1...master
      example> http://192.168.20.28/root/test-use-project/compare/v1.5.1...master
      
      txt> tag example 2, comparing tag v1.5.1 to tag v1.5.2
      url> https://${gitlab_host}/${repo_path}/compare/v1.5.1...v1.5.2
      example> http://192.168.20.28/root/test-use-project/compare/v1.5.1...master
    
    
      txt> commit example 1, comparing commit SHA to master
      url> https://${gitlab_host}/${repo_path}/compare/f051a7bc...master
      example> http://192.168.20.28/root/test-use-project/compare/f051a7bc...master
    
      txt> 前面的sha需要是较老的提交，后面是较新的提交。
      txt> commit example 2, comparing commit SHA to another commit SHA
      url> https://${gitlab_host}/${repo_path}/compare/f051a7bc...7f327322
      http://192.168.20.28/root/test-use-project/compare/faa6ccf3b9e4d25a2dff52212d3103f7fe58ab2d...79d05d8ca43b2c156e7bdb183b8a2a5da7be8c9c
    

txt> postman测试，需要登录，配置token。
- Snippet:2021-3-26 16:24
- gitlab配置公钥秘钥
- windows的bat中用cmd /c xxx等价实现eval功能
- 问题概述:Gitlab配置公钥秘钥
- 方案细节
>
txt> 新建SSH key pair,默认路径是 ~/.ssh,不用输入密码,全部默认.
cmd> ssh-keygen -t rsa -C “admin@example.com” -b 4096

            txt> 私钥添加到本地全局环境,该程序在git路径下
            cmd> ssh-agent.exe -s
            info>F:\Program Files\Git\usr\bin>ssh-agent.exe -s
                 SSH_AUTH_SOCK=/tmp/ssh-dQbmwhrZJ93t/agent.32180; export SSH_AUTH_SOCK;
                 SSH_AGENT_PID=32528; export SSH_AGENT_PID;
                 echo Agent pid 32528;

            
            
            cmd> ssh-add.exe ~/.ssh/id_rsa.pub
            info> F:\Program Files\Git\usr\bin>ssh-add.exe ~/.ssh/id_rsa.pub
                  Could not open a connection to your authentication agent.
            cmd> ssh-agent.exe bash
            
            txt> 获取公钥,并将公钥粘贴到gitlab服务器.
            cmd> ~/.ssh/id_rsa.pub

txt> 统计