如何从GitLab表创建进入ci_job_artifacts对象存储的路径？

Question

Gitlab有一个内部的PostgreSQL数据库。psql有一个名为gitlab-psql的客户端包装器，我用它连接它。这允许我通过表ci_job_artifacts查询作业工件。这个表模式如下所示，

gitlabhq_production=# \d ci_job_artifacts    
                                          Table "public.ci_job_artifacts"
      Column       |           Type           | Collation | Nullable |                   Default                    
-------------------+--------------------------+-----------+----------+----------------------------------------------
 project_id        | integer                  |           | not null | 
 file_type         | integer                  |           | not null | 
 size              | bigint                   |           |          | 
 created_at        | timestamp with time zone |           | not null | 
 updated_at        | timestamp with time zone |           | not null | 
 expire_at         | timestamp with time zone |           |          | 
 file              | character varying        |           |          | 
 file_store        | integer                  |           |          | 1
 file_sha256       | bytea                    |           |          | 
 file_format       | smallint                 |           |          | 
 file_location     | smallint                 |           |          | 
 id                | bigint                   |           | not null | nextval('ci_job_artifacts_id_seq'::regclass)
 job_id            | bigint                   |           | not null | 
 locked            | smallint                 |           |          | 2
 original_filename | text                     |           |          | 
 partition_id      | bigint                   |           | not null | 100
 accessibility     | smallint                 |           | not null | 0

如何将其转换为可以用于在GitLab对象存储中查找工件的路径。

Answer 1

除了面向客户的文档之外，源中还有一个文档详细说明了路径是如何创建的，称为"Uploads guide: Adding new uploads"目前在doc/development/uploads/working_with_uploads.md。本文档展示了GitLab中任何地方的桶结构的魔力。对于作业工件，结构是

/artifacts////

proj_id_hash is sha256 is project_id您可以在这个问题上读到更多关于这个问题的内容，“GitLab程序是什么？

为了计算其余部分，我创建了一个助手函数，它可以极大地简化这个过程，

CREATE EXTENSION pgcrypto; -- needed for `digest(bytea, text) `

CREATE OR REPLACE FUNCTION generate_job_artifact_bucket_path(project_id bigint, created_at timestamp with time zone, job_id bigint, id bigint, file text)
RETURNS text
AS $
  SELECT FORMAT(
    '%s/%s/%s/%s/%s/%s/%s',
    substring(proj_sha256 FROM 1 FOR 2),
    substring(proj_sha256 FROM 3 FOR 2),
    proj_sha256,
    to_char(created_at, 'YYYY_MM_DD'),
    job_id,
    id,
    file
  )
  FROM encode(digest(project_id::text::bytea, 'SHA256'), 'hex') AS proj_sha256
$ LANGUAGE SQL IMMUTABLE;

COMMENT ON FUNCTION generate_job_artifact_bucket_path
IS $Generates the relative bucket to `gitlab_rails['object_store']['objects']['artifacts']['bucket']` for more information see https://devops.stackexchange.com/q/17298/18965$;

现在您可以像这样查询路径，

SELECT generate_job_artifact_bucket_path(project_id, created_at, job_id, id, file)
FROM ci_job_artifacts;

或者你也可以这样做，

CREATE OR REPLACE FUNCTION to_bucket_path(t ci_job_artifacts)
RETURNS text
AS $
  SELECT FORMAT(
    '%s/%s/%s/%s/%s/%s/%s',
    substring(proj_sha256 FROM 1 FOR 2),
    substring(proj_sha256 FROM 3 FOR 2),
    proj_sha256,
    to_char(t.created_at, 'YYYY_MM_DD'),
    t.job_id,
    t.id,
    t.file
  )
  FROM encode(digest(t.project_id::text::bytea, 'SHA256'), 'hex') AS proj_sha256
$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION to_bucket_path(t ci_pipeline_artifacts)
RETURNS text
AS $
  SELECT FORMAT(
    '%s/%s/%s/pipelines/%s/artifacts/%s/%s',
    substring(proj_sha256 FROM 1 FOR 2),
    substring(proj_sha256 FROM 3 FOR 2),
    proj_sha256,
    t.pipeline_id,
    t.id,
    t.file
  )
  FROM encode(digest(t.project_id::text::bytea, 'SHA256'), 'hex') AS proj_sha256
$ LANGUAGE SQL IMMUTABLE;

这将提供一个多态约定来实现多个表的to_bucket_path。

SELECT to_bucket_path(ci_job_artifacts) FROM ci_job_artifacts;
SELECT to_bucket_path(ci_pipeline_artifacts) FROM ci_pipeline_artifacts;