Search code examples
sqlgitlabinvantive-sql

Query repository file contents from GitLab

I want retrieve the commit id of a file readmeTest.txt through Invantive SQL like so:

select * from repository_files(29, file-path, 'master')

But for this to work I need a project-id, file-path and a ref.

I know my project-id (I got it from select * from projects) and my ref (master branch) but I don’t know where I can find the path to the file I want to retrieve information of.

So where can I find the value of file-path and ref?

This is my repository directory tree, where I can see the files exist:

Directory tree

Directory tree

Solution

  • You need to join several entities in GitLab to get the information you need.

    The fields from your repository_files table function and their meaning:

    • project-id can be found as id in the projects entity, as you already knew;
    • ref-name can be found as name in repositories;
    • ref is the name of a branch, a tag or a commit, so let's assume you want the master for now.

    Giving this information, you need the below query to get all repository files and their content in a project (I narrowed it down to a single project for now):

    select pjt.name project_name
,      rpe.name repository_name
,      rpf.content file
from   projects pjt
join   repositories(pjt.id) rpe
on     1=1
and    rpe.name like '%.%'
join   repository_files(pjt.id, rpe.name, 'master') rpf
on     1=1
where  pjt.id = 1