forked from
tangled.org/core
Monorepo for Tangled
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 create index if not exists idx_repos_aturi on repos (at_uri);
73 create index if not exists idx_repos_db_updated_at on repos (db_updated_at desc);
74 create index if not exists idx_hosts_db_updated_at on hosts (db_updated_at desc);
75
76 create or replace function set_updated_at()
77 returns trigger as $$
78 begin
79 new.db_updated_at = now();
80 return new;
81 end;
82 $$ language plpgsql;
83
84 drop trigger if exists repos_set_updated_at on repos;
85 create trigger repos_set_updated_at
86 before update on repos
87 for each row
88 execute function set_updated_at();
89
90 drop trigger if exists hosts_set_updated_at on hosts;
91 create trigger hosts_set_updated_at
92 before update on hosts
93 for each row
94 execute function set_updated_at();
95 `)
96 if err != nil {
97 return nil, fmt.Errorf("initializing db schema: %w", err)
98 }
99
100 if err := RunMigrations(ctx, conn, log.FromContext(ctx), Migrations); err != nil {
101 return nil, fmt.Errorf("running migrations: %w", err)
102 }
103
104 return db, nil
105}