shithub Public

Watch 1 Fork 0 Star 0
MySQL · 3467 bytes Raw Blame History
1 -- SPDX-License-Identifier: AGPL-3.0-or-later
2 --
3 -- GitHub organization imports. One parent row tracks discovery/progress;
4 -- one child row tracks each GitHub repository to create and fetch.
5
6 -- +goose Up
7
8 CREATE TABLE org_github_imports (
9 id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
10 org_id bigint NOT NULL REFERENCES orgs(id) ON DELETE CASCADE,
11 source_host text NOT NULL DEFAULT 'github.com',
12 source_org text NOT NULL,
13 requested_by_user_id bigint REFERENCES users(id) ON DELETE SET NULL,
14 status text NOT NULL DEFAULT 'queued',
15 include_private boolean NOT NULL DEFAULT false,
16 token_present boolean NOT NULL DEFAULT false,
17 token_ciphertext bytea,
18 token_nonce bytea,
19 total_count integer NOT NULL DEFAULT 0,
20 last_error text,
21 started_at timestamptz,
22 completed_at timestamptz,
23 created_at timestamptz NOT NULL DEFAULT now(),
24 updated_at timestamptz NOT NULL DEFAULT now(),
25 CHECK (source_host = 'github.com'),
26 CHECK (source_org <> ''),
27 CHECK (length(source_org) <= 100),
28 CHECK (status IN ('queued', 'discovering', 'importing', 'completed', 'failed')),
29 CHECK (total_count >= 0),
30 CHECK ((token_ciphertext IS NULL) = (token_nonce IS NULL)),
31 CHECK ((token_ciphertext IS NULL) OR token_present)
32 );
33
34 CREATE TABLE org_github_import_repos (
35 id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
36 import_id bigint NOT NULL REFERENCES org_github_imports(id) ON DELETE CASCADE,
37 github_id bigint,
38 source_full_name text NOT NULL,
39 source_name text NOT NULL,
40 target_name text NOT NULL,
41 clone_url text NOT NULL,
42 description text NOT NULL DEFAULT '',
43 default_branch text NOT NULL DEFAULT '',
44 target_visibility repo_visibility NOT NULL DEFAULT 'public',
45 is_private boolean NOT NULL DEFAULT false,
46 is_fork boolean NOT NULL DEFAULT false,
47 status text NOT NULL DEFAULT 'queued',
48 repo_id bigint REFERENCES repos(id) ON DELETE SET NULL,
49 last_error text,
50 started_at timestamptz,
51 completed_at timestamptz,
52 created_at timestamptz NOT NULL DEFAULT now(),
53 updated_at timestamptz NOT NULL DEFAULT now(),
54 CHECK (source_full_name <> ''),
55 CHECK (source_name <> ''),
56 CHECK (target_name <> ''),
57 CHECK (clone_url <> ''),
58 CHECK (length(clone_url) <= 2048),
59 CHECK (status IN ('queued', 'importing', 'imported', 'skipped', 'failed'))
60 );
61
62 CREATE UNIQUE INDEX org_github_import_repos_import_target_idx
63 ON org_github_import_repos(import_id, target_name);
64
65 CREATE INDEX org_github_imports_org_created_idx
66 ON org_github_imports(org_id, created_at DESC);
67
68 CREATE INDEX org_github_import_repos_import_status_idx
69 ON org_github_import_repos(import_id, status);
70
71 CREATE TRIGGER org_github_imports_set_updated_at
72 BEFORE UPDATE ON org_github_imports
73 FOR EACH ROW EXECUTE FUNCTION tg_set_updated_at();
74
75 CREATE TRIGGER org_github_import_repos_set_updated_at
76 BEFORE UPDATE ON org_github_import_repos
77 FOR EACH ROW EXECUTE FUNCTION tg_set_updated_at();
78
79
80 -- +goose Down
81
82 DROP TRIGGER IF EXISTS org_github_import_repos_set_updated_at ON org_github_import_repos;
83 DROP TRIGGER IF EXISTS org_github_imports_set_updated_at ON org_github_imports;
84 DROP INDEX IF EXISTS org_github_import_repos_import_status_idx;
85 DROP INDEX IF EXISTS org_github_imports_org_created_idx;
86 DROP INDEX IF EXISTS org_github_import_repos_import_target_idx;
87 DROP TABLE IF EXISTS org_github_import_repos;
88 DROP TABLE IF EXISTS org_github_imports;
89