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