0

Gitlab has an internal PostgreSQL database. There is a client wrapper for psql called gitlab-psql that I connect to it with. This allows me to query job artifacts through the table ci_job_artifacts. This table schema looks like this,

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

How do I convert this to a path that I can use to find the artifact in GitLab Object Storage.

1 Answer 1

0

Aside from customer-facing documentation there is a document in the source that details how the path is created called "Uploads guide: Adding new uploads" currently at doc/development/uploads/working_with_uploads.md. This document shows the magic sauce for the bucket structure everywhere in GitLab. Specifically for the job artifacts, the structure is

/artifacts/<proj_id_hash>/<date>/<job_id>/<artifact_id>

The proj_id_hash is the sha256 is the project_id you can read more about that on this question, "What is the GitLab proj_id_hash for the bucket structure in the object store?"

To calculate the rest, I've create a helper function which should greatly ease this process,

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$$;

Now you can query for the path like this,

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

Alternatively you can do something like this,

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;

This will provide a polymorphic convention to implement a to_bucket_path for multiple tables..

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.