-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathservice-enhance-gitlab.sql.ts
140 lines (130 loc) · 7.83 KB
/
service-enhance-gitlab.sql.ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
import { dcp } from "./deps.ts";
export function initSQL(
ctx: dcp.DcpInterpolationContext,
options: dcp.InterpolationContextStateOptions & {
readonly gitLabCanonicalSchema: dcp.PostgreSqlSchema;
},
): dcp.DcpInterpolationResult {
const state = ctx.prepareState(
ctx.prepareTsModuleExecution(import.meta.url),
options,
);
const [sqr] = state.observableQR(state.schema);
const [glcsqr] = state.observableQR(options.gitLabCanonicalSchema);
// our typical naming is to have tables be singular, not plural but we're
// following GitLab conventions (theirs is plural)
const glQNView = sqr("gitlab_qualified_namespaces");
const glQPView = sqr("gitlab_qualified_projects");
const glQPRView = sqr("gitlab_qualified_project_repos");
const glQPRCloneFn = sqr("gitlab_qualified_project_repos_clone");
const glQPRBareFn = sqr("gitlab_qualified_project_repos_bare");
const glNamespacesTable = glcsqr("namespaces");
// deno-fmt-ignore
return dcp.SQL(ctx, state)`
create or replace view ${glQNView} as
select namespace.*,
qualified.level,
qualified.abs_path as qualified_path,
qualified.qualified_name
from ${glNamespacesTable} namespace
cross join lateral(
with recursive recursive_ns (id, level, path_component, abs_path, name_component, qualified_name) as (
select id, 0, path, path::text, name, name::text
from ${glNamespacesTable}
where parent_id is null
union all
select childns.id, t0.level + 1, childns.path, (t0.abs_path || '/' || childns.path)::text, childns.name, (t0.qualified_name || '::' || childns.name)::text
from ${glNamespacesTable} childns
inner join recursive_ns t0 on t0.id = childns.parent_id)
select level, abs_path::text, qualified_name::text
from recursive_ns
where id = namespace.id
) as qualified(level, abs_path, qualified_name);
comment on view ${glQNView} is 'All Gitlab namespaces with level and hierarchical qualifications';
create or replace view ${glQPView} as
select namespace.level as namespace_level,
namespace.qualified_name as qualified_namespace_name,
namespace.qualified_path as qualified_namespace_path,
namespace.level+1 as project_level,
project.*,
namespace.qualified_path || '/' || project.path as qualified_project_path,
namespace.qualified_name || '::' || project.name as qualified_project_name
from ${glcsqr("projects")} project
left join ${glQNView} namespace on project.namespace_id = namespace.id;
comment on view ${glQPView} is 'All registered GitLab projects with namespace-qualified names and logical paths with hierarchy';
create or replace view ${glQPRView} as
select qp.*,
pr.id as project_repo_id,
pr.shard_id as project_repo_gitaly_shard_id,
pr.disk_path as project_repo_gitaly_disk_path,
qp.qualified_project_path || '.git' as qualified_project_git_dir_path,
pr.disk_path || '.git' as project_repo_gitaly_disk_git_dir_rel_path
from ${glQPView} qp
left join ${glcsqr("project_repositories")} pr on pr.project_id = qp.id;
comment on view ${glQPRView} is 'All registered GitLab projects with namespace-qualified names, logical paths, and physical Gitaly repository paths';
CREATE OR REPLACE FUNCTION ${glQPRCloneFn}(gitlab_host_name text) RETURNS table(qpr ${glQPRView}, clone_ssh text, clone_https text) AS $func$
BEGIN
RETURN QUERY
select repos as qpr,
format('git@%s:%s', gitlab_host_name, repos.qualified_project_git_dir_path) as clone_ssh,
format('https://%s/%s', gitlab_host_name, repos.qualified_project_git_dir_path) as clone_https
from ${glQPRView} as repos;
END
$func$ LANGUAGE plpgsql;
comment on function ${glQPRCloneFn}(text) is 'All registered GitLab projects with namespace-qualified names, logical paths, and relative Gitaly repository paths plus cloning paths on a given host';
CREATE OR REPLACE FUNCTION ${glQPRCloneFn}(gitlab_host_name text, parent_namespace_id integer) RETURNS table(qpr ${glQPRView}, clone_ssh text, clone_https text) AS $func$
BEGIN
RETURN QUERY
select repos as qpr,
format('git@%s:%s', gitlab_host_name, repos.qualified_project_git_dir_path) as clone_ssh,
format('https://%s/%s', gitlab_host_name, repos.qualified_project_git_dir_path) as clone_https
from ${glQPRView} as repos
where namespace_id in (
with recursive descendants AS (
select parent_namespace_id AS id
union all
select ns.id
from ${glNamespacesTable} as ns
join descendants on descendants.id = ns.parent_id)
select id from descendants);
END
$func$ LANGUAGE plpgsql;
comment on function ${glQPRCloneFn}(text, integer) is 'All registered GitLab projects under a specific namespace ID with namespace-qualified names, logical paths, and relative Gitaly repository paths plus cloning paths on a given host';
CREATE OR REPLACE FUNCTION ${glQPRBareFn}(gitlab_bare_repos_home_on_disk text) RETURNS table(qpr ${glQPRView}, git_dir_abs_path text) AS $func$
BEGIN
RETURN QUERY
select repos as qpr,
format('%s/%s', gitlab_bare_repos_home_on_disk, repos.project_repo_gitaly_disk_git_dir_rel_path) as git_dir_abs_path
from ${glQPRView} as repos;
END
$func$ LANGUAGE plpgsql;
comment on function ${glQPRBareFn}(text) is 'All registered GitLab projects with namespace-qualified names, logical paths, and absolute paths to Gitaly bare Git repositories';
CREATE OR REPLACE FUNCTION ${glQPRBareFn}(gitlab_bare_repos_home_on_disk text, parent_namespace_id integer) RETURNS table(qpr ${glQPRView}, git_dir_abs_path text) AS $func$
BEGIN
RETURN QUERY
select repos as qpr,
format('%s/%s', gitlab_bare_repos_home_on_disk, repos.project_repo_gitaly_disk_git_dir_rel_path) as git_dir_abs_path
from ${glQPRView} as repos
where namespace_id in (
with recursive descendants AS (
select parent_namespace_id AS id
union all
select ns.id
from ${glNamespacesTable} as ns
join descendants on descendants.id = ns.parent_id)
select id from descendants);
END
$func$ LANGUAGE plpgsql;
comment on function ${glQPRBareFn}(text, integer) is 'All registered GitLab projects under a specific namespace ID with namespace-qualified names, logical paths, and absolute paths to Gitaly bare Git repositories';
-- Grant permission to gitlab user
GRANT USAGE ON SCHEMA ${state.schema.name} to gitlab_read_only_user;
GRANT SELECT ON ALL TABLES IN SCHEMA ${state.schema.name} to gitlab_read_only_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA ${state.schema.name} to gitlab_read_only_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA ${state.schema.name} TO gitlab_read_only_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA ${state.schema.name} GRANT SELECT ON TABLES TO gitlab_read_only_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA ${state.schema.name} GRANT SELECT ON SEQUENCES TO gitlab_read_only_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA ${state.schema.name} GRANT EXECUTE ON FUNCTIONS TO gitlab_read_only_user;
-- qualified references observed in this template:
-- ${state.qualifiedReferencesObserved.referencesObserved.map(r => `* ${r}`).join(`\n -- `)}
`;
}