目的

Whenever you have too many projects and too many repositories configured in the instance, having a better picture of how many of each you have can become challenging.

The purpose of this article is to help anybody that needs to retrieve a list of projects and repositories, along with personal repositories, directly from the database used by Bitbucket Server.

ソリューション

Connect to the database used by Bitbucket Server using the database vendor supplied tool or maybe using a client like DBVisualizer and run the following SQL query:

SELECT 
       prj.name AS "Project / User Name"
     , prj.project_key AS "Project / User Key"
     , rep.name AS "Repository Name"
     , rep.slug AS "Repository Slug"
FROM repository rep 
INNER JOIN project prj ON rep.project_id = prj.id
ORDER BY prj.name, rep.name

Sample result:

Project / User Name  Project / User Key  Repository Name  Repository Slug  
-------------------  ------------------  ---------------  ---------------  
Project One          PROJ                Temp1            temp1            
Project Two          PT                  GitHook          githook          
~internal1           ~INTERNAL1          PersonalRepo     personalrepo     

Where

• Project Project One has one repository, named Temp1.
• Project Project Two has one repository, named GitHook.
• User internal1 has a personal repository, named PersonalRepo.

If you would like to find out when a project or repository was created, you can use the queries below for Postgres and MSSQL databases:

• Postgres 

  select
  	p."name" ,
  	p.project_key ,
  	a."ACTION" ,
  	cast(to_timestamp(a."ENTITY_TIMESTAMP" / 1000) as date) creation_date ,
  	a."USER_NAME"
  from
  	"AO_C77861_AUDIT_ENTITY" a ,
  	"project" p
  where
  	a."PRIMARY_RESOURCE_ID" = cast(p.id as varchar(255))
  	and "ACTION" in ('Project created', 'Repository created')
  	and cast(to_timestamp(a."ENTITY_TIMESTAMP" / 1000) as date) > '2023-12-31';

• MS SQL 

  MS SQL

  
  

  
  

  
  

  SELECT
        a.PRIMARY_RESOURCE_ID
      , a.ID
      , p.name
      , p.project_key
      , a.USER_ID
      , a.USER_NAME
      , a.ACTION
      , CAST(DATEADD(SECOND, a.ENTITY_TIMESTAMP / 1000, '1970/1/1') AS DATE) creation_date
  FROM AO_C77861_AUDIT_ENTITY a
      JOIN project p ON a.PRIMARY_RESOURCE_ID = p.id
  WHERE
      CAST(DATEADD(SECOND, a.ENTITY_TIMESTAMP / 1000, '1970/1/1') AS DATE) > '2023-12-30'
      AND a.[ACTION] in ('Project created', 'Repository created')
  ORDER BY a.ENTITY_TIMESTAMP DESC;