Monorepo for Tangled
tangled.org
1package db
2
3import (
4 "context"
5 "database/sql"
6 "fmt"
7 "time"
8
9 _ "github.com/jackc/pgx/v5/stdlib"
10 "tangled.org/core/log"
11)
12
13func Make(ctx context.Context, dbUrl string, maxConns int) (*sql.DB, error) {
14 db, err := sql.Open("pgx", dbUrl)
15 if err != nil {
16 return nil, fmt.Errorf("opening db: %w", err)
17 }
18
19 db.SetMaxOpenConns(maxConns)
20 db.SetMaxIdleConns(maxConns)
21 db.SetConnMaxIdleTime(time.Hour)
22
23 pingCtx, cancel := context.WithTimeout(ctx, 5*time.Second)
24 defer cancel()
25 if err := db.PingContext(pingCtx); err != nil {
26 db.Close()
27 return nil, fmt.Errorf("ping db: %w", err)
28 }
29
30 conn, err := db.Conn(ctx)
31 if err != nil {
32 return nil, err
33 }
34 defer conn.Close()
35
36 _, err = conn.ExecContext(ctx, `
37 create table if not exists repos (
38 did text not null,
39 rkey text not null,
40 at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo' || '/' || rkey) stored,
41 cid text,
42
43 -- record content
44 name text not null,
45 knot_domain text not null,
46
47 -- sync info
48 git_rev text not null,
49 repo_sha text not null,
50 state text not null default 'pending',
51 error_msg text,
52 retry_count integer not null default 0,
53 retry_after integer not null default 0,
54 db_created_at timestamptz not null default now(),
55 db_updated_at timestamptz not null default now(),
56
57 constraint repos_pkey primary key (did, rkey)
58 );
59
60 -- knot hosts
61 create table if not exists hosts (
62 hostname text not null,
63 no_ssl boolean not null default false,
64 status text not null default 'active',
65 last_seq bigint not null default -1,
66 db_created_at timestamptz not null default now(),
67 db_updated_at timestamptz not null default now(),
68
69 constraint hosts_pkey primary key (hostname)
70 );
71
72 -- repo language stats at HEAD
73 create table if not exists repo_head_languages (
74 repo text not null, -- repo identifier (did)
75 commit text not null, -- commit id (oid)
76 language text not null,
77 size integer not null check (size >= 0),
78
79 constraint repo_head_languages_pkey
80 primary key (repo, commit, language)
81 );
82
83 create index if not exists idx_repos_aturi on repos (at_uri);
84 create index if not exists idx_repos_db_updated_at on repos (db_updated_at desc);
85 create index if not exists idx_hosts_db_updated_at on hosts (db_updated_at desc);
86
87 create index if not exists idx_repo_head_languages_repo_commit
88 on repo_head_languages (repo, commit);
89
90 create or replace function set_updated_at()
91 returns trigger as $$
92 begin
93 new.db_updated_at = now();
94 return new;
95 end;
96 $$ language plpgsql;
97
98 drop trigger if exists repos_set_updated_at on repos;
99 create trigger repos_set_updated_at
100 before update on repos
101 for each row
102 execute function set_updated_at();
103
104 drop trigger if exists hosts_set_updated_at on hosts;
105 create trigger hosts_set_updated_at
106 before update on hosts
107 for each row
108 execute function set_updated_at();
109 `)
110 if err != nil {
111 return nil, fmt.Errorf("initializing db schema: %w", err)
112 }
113
114 if err := RunMigrations(ctx, conn, log.FromContext(ctx), Migrations); err != nil {
115 return nil, fmt.Errorf("running migrations: %w", err)
116 }
117
118 return db, nil
119}