Something went wrong. Try again.
This repository has no description
Something went wrong. Try again.
14 kB · 475 lines
Go
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476package db
import ( "context" "database/sql" "fmt" "log/slog" "os" "strings" "sync"
securejoin "github.com/cyphar/filepath-securejoin" _ "github.com/mattn/go-sqlite3" "tangled.org/core/log" "tangled.org/core/orm")
type DB struct { db *sql.DB logger *slog.Logger
// uidAssignMu serialises GetOrAssignOwnerUID across goroutines so that // concurrent callers don't race on the uid_counter read-modify-write. uidAssignMu sync.Mutex}
type DBTX interface { QueryRow(query string, args ...any) *sql.Row Query(query string, args ...any) (*sql.Rows, error) Exec(query string, args ...any) (sql.Result, error)}
func (d *DB) BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error) { return d.db.BeginTx(ctx, opts)}
func (d *DB) Exec(query string, args ...any) (sql.Result, error) { return d.db.Exec(query, args...)}
func (d *DB) QueryRow(query string, args ...any) *sql.Row { return d.db.QueryRow(query, args...)}
func (d *DB) Query(query string, args ...any) (*sql.Rows, error) { return d.db.Query(query, args...)}
func Setup(ctx context.Context, dbPath string) (*DB, error) { // https://github.com/mattn/go-sqlite3#connection-string opts := []string{ "_foreign_keys=1", "_journal_mode=WAL", "_synchronous=NORMAL", "_auto_vacuum=incremental", "_busy_timeout=5000", }
logger := log.FromContext(ctx) logger = log.SubLogger(logger, "db")
db, err := sql.Open("sqlite3", dbPath+"?"+strings.Join(opts, "&")) if err != nil { return nil, err }
conn, err := db.Conn(ctx) if err != nil { return nil, err } defer conn.Close()
_, err = conn.ExecContext(ctx, ` create table if not exists known_dids ( did text primary key );
create table if not exists public_keys ( id integer primary key autoincrement, did text not null, key text not null, created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')), unique(did, key), foreign key (did) references known_dids(did) on delete cascade );
create table if not exists _jetstream ( id integer primary key autoincrement, last_time_us integer not null );
create table if not exists events ( rkey text not null, nsid text not null, event text not null, -- json created integer not null default (strftime('%s', 'now')), primary key (rkey, nsid) );
create table if not exists repo_keys ( repo_did text primary key, signing_key blob not null, created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')) );
create table if not exists migrations ( id integer primary key autoincrement, name text unique ); `) if err != nil { return nil, err }
if err := orm.RunMigration(conn, logger, "add-owner-did-to-repo-keys", func(tx *sql.Tx) error { _, mErr := tx.ExecContext(ctx, `ALTER TABLE repo_keys ADD COLUMN owner_did TEXT`) return mErr }); err != nil { return nil, err }
if err := orm.RunMigration(conn, logger, "add-repo-name-to-repo-keys", func(tx *sql.Tx) error { _, mErr := tx.ExecContext(ctx, `ALTER TABLE repo_keys ADD COLUMN repo_name TEXT`) return mErr }); err != nil { return nil, err }
if err := orm.RunMigration(conn, logger, "add-unique-owner-repo-on-repo-keys", func(tx *sql.Tx) error { _, mErr := tx.ExecContext(ctx, `CREATE UNIQUE INDEX IF NOT EXISTS idx_repo_keys_owner_repo ON repo_keys(owner_did, repo_name)`) return mErr }); err != nil { return nil, err }
if err := orm.RunMigration(conn, logger, "add-key-type-and-nullable-signing-key", func(tx *sql.Tx) error { _, mErr := tx.ExecContext(ctx, ` create table repo_keys_new ( repo_did text primary key, signing_key blob, created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')), owner_did text, repo_name text, at_uri text, key_type text not null default 'k256' ); insert into repo_keys_new (repo_did, signing_key, created_at, owner_did, repo_name, key_type) select repo_did, signing_key, created_at, owner_did, repo_name, 'k256' from repo_keys; drop table repo_keys; alter table repo_keys_new rename to repo_keys; create unique index if not exists idx_repo_keys_owner_repo on repo_keys(owner_did, repo_name); `) return mErr }); err != nil { return nil, err }
if err := orm.RunMigration(conn, logger, "add-repo-aliases", func(tx *sql.Tx) error { _, mErr := tx.ExecContext(ctx, ` create table if not exists repo_aliases ( owner_did text not null, rkey text not null, repo_did text not null, rev text not null, primary key (owner_did, rkey) ); create index if not exists idx_repo_aliases_repo_did on repo_aliases(repo_did);
insert or ignore into repo_aliases (owner_did, rkey, repo_did, rev) select owner_did, repo_name, repo_did, '1_' || created_at from repo_keys where owner_did is not null and repo_name is not null and repo_did is not null; `) return mErr }); err != nil { return nil, err }
if err := orm.RunMigration(conn, logger, "drop-at-uri-from-repo-keys", func(tx *sql.Tx) error { _, mErr := tx.ExecContext(ctx, ` create table repo_keys_new ( repo_did text primary key, signing_key blob, created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')), owner_did text, repo_name text, key_type text not null default 'k256' ); insert into repo_keys_new (repo_did, signing_key, created_at, owner_did, repo_name, key_type) select repo_did, signing_key, created_at, owner_did, repo_name, key_type from repo_keys; drop table repo_keys; alter table repo_keys_new rename to repo_keys; create unique index if not exists idx_repo_keys_owner_repo on repo_keys(owner_did, repo_name); `) return mErr }); err != nil { return nil, err }
if err := orm.RunMigration(conn, logger, "create-knot-members", func(tx *sql.Tx) error { _, mErr := tx.ExecContext(ctx, ` create table if not exists knot_members ( id integer primary key autoincrement, did text not null, rkey text not null, subject text not null, created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')), unique (did, rkey) ); `) return mErr }); err != nil { return nil, err }
if err := orm.RunMigration(conn, logger, "add-isolated-at-to-repo-keys", func(tx *sql.Tx) error { _, mErr := tx.ExecContext(ctx, `ALTER TABLE repo_keys ADD COLUMN isolated_at DATETIME`) return mErr }); err != nil { return nil, err }
if err := orm.RunMigration(conn, logger, "add-owner-uid-tables", func(tx *sql.Tx) error { _, mErr := tx.ExecContext(ctx, ` CREATE TABLE IF NOT EXISTS owner_uid_assignments ( owner_did TEXT PRIMARY KEY, uid INTEGER NOT NULL UNIQUE, created_at DATETIME DEFAULT CURRENT_TIMESTAMP );
CREATE TABLE IF NOT EXISTS uid_counter ( next_uid INTEGER NOT NULL DEFAULT 100000 ); `) if mErr != nil { return mErr } // Seed the counter only if the table is empty. _, mErr = tx.ExecContext(ctx, ` INSERT INTO uid_counter (next_uid) SELECT 100000 WHERE NOT EXISTS (SELECT 1 FROM uid_counter) `) return mErr }); err != nil { return nil, err }
if err := orm.RunMigration(conn, logger, "knot-members-nullable-rkey", func(tx *sql.Tx) error { _, mErr := tx.ExecContext(ctx, ` create table knot_members_new ( id integer primary key autoincrement, did text not null, rkey text, subject text not null, created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')), unique (did, rkey) ); insert into knot_members_new (id, did, rkey, subject, created) select id, did, rkey, subject, created from knot_members; drop table knot_members; alter table knot_members_new rename to knot_members; `) return mErr }); err != nil { return nil, err }
if err := orm.RunMigration(conn, logger, "create-collaborators", func(tx *sql.Tx) error { _, mErr := tx.ExecContext(ctx, ` create table if not exists collaborators ( id integer primary key autoincrement, repo_did text not null, subject_did text not null, added_by_did text not null, created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')), unique (repo_did, subject_did) ); create index if not exists idx_collaborators_repo_id on collaborators(repo_did, id); `) return mErr }); err != nil { return nil, err }
if err := orm.RunMigration(conn, logger, "knot-members-direct-subject-unique", func(tx *sql.Tx) error { _, mErr := tx.ExecContext(ctx, ` create unique index if not exists idx_knot_members_direct_subject on knot_members(subject) where rkey is null; create index if not exists idx_knot_members_subject on knot_members(subject); `) return mErr }); err != nil { return nil, err }
if err := orm.RunMigration(conn, logger, "add-events-created-index", func(tx *sql.Tx) error { _, mErr := tx.ExecContext(ctx, `create index if not exists idx_events_created on events(created)`) return mErr }); err != nil { return nil, err }
if err := orm.RunMigration(conn, logger, "add-rkey-to-public-keys", func(tx *sql.Tx) error { _, mErr := tx.ExecContext(ctx, `ALTER TABLE public_keys ADD COLUMN rkey TEXT`) return mErr }); err != nil { return nil, err }
if err := orm.RunMigration(conn, logger, "enforce-global-key-uniqueness", func(tx *sql.Tx) error { res, mErr := tx.ExecContext(ctx, `delete from public_keys where id not in (select min(id) from public_keys group by key)`) if mErr != nil { return mErr } if n, rErr := res.RowsAffected(); rErr == nil && n > 0 { logger.Warn("dropped duplicate public keys to enforce global key uniqueness", "deleted", n) } _, mErr = tx.ExecContext(ctx, `create unique index if not exists idx_public_keys_key on public_keys(key)`) return mErr }); err != nil { return nil, err }
return &DB{ db: db, logger: logger, }, nil}
func (d *DB) StoreRepoKey(repoDid string, signingKey []byte, ownerDid, repoName string) error { return d.storeRepoKeyRow(repoDid, signingKey, ownerDid, repoName, "k256")}
func (d *DB) StoreRepoDidWeb(repoDid, ownerDid, repoName string) error { return d.storeRepoKeyRow(repoDid, nil, ownerDid, repoName, "web")}
func (d *DB) storeRepoKeyRow(repoDid string, signingKey []byte, ownerDid, repoName, keyType string) error { tx, err := d.db.Begin() if err != nil { return err } defer tx.Rollback()
if _, err := tx.Exec( `INSERT INTO repo_keys (repo_did, signing_key, owner_did, repo_name, key_type) VALUES (?, ?, ?, ?, ?)`, repoDid, signingKey, ownerDid, repoName, keyType, ); err != nil { return err }
if _, err := tx.Exec( `INSERT INTO repo_aliases (owner_did, rkey, repo_did, rev) VALUES (?, ?, ?, '0_' || strftime('%Y-%m-%dT%H:%M:%SZ', 'now')) ON CONFLICT(owner_did, rkey) DO NOTHING`, ownerDid, repoName, repoDid, ); err != nil { return err }
return tx.Commit()}
func (d *DB) DeleteRepoKey(repoDid string) error { tx, err := d.db.Begin() if err != nil { return err } defer tx.Rollback()
if _, err := tx.Exec(`DELETE FROM repo_aliases WHERE repo_did = ?`, repoDid); err != nil { return err }
if _, err := tx.Exec(`DELETE FROM repo_keys WHERE repo_did = ?`, repoDid); err != nil { return err }
if _, err := tx.Exec(`DELETE FROM collaborators WHERE repo_did = ?`, repoDid); err != nil { return err }
return tx.Commit()}
func (d *DB) RepoDidExists(repoDid string) (bool, error) { var count int err := d.db.QueryRow(`SELECT count(1) FROM repo_keys WHERE repo_did = ?`, repoDid).Scan(&count) return count > 0, err}
func (d *DB) ListRepoDids() ([]string, error) { rows, err := d.db.Query(`SELECT repo_did FROM repo_keys`) if err != nil { return nil, err } defer rows.Close()
dids := []string{} for rows.Next() { var did string if err := rows.Scan(&did); err != nil { return nil, err } dids = append(dids, did) } return dids, rows.Err()}
func (d *DB) GetRepoDid(ownerDid, rkey string) (string, error) { var repoDid string err := d.db.QueryRow( `SELECT repo_did FROM repo_aliases WHERE owner_did = ? AND rkey = ?`, ownerDid, rkey, ).Scan(&repoDid) return repoDid, err}
func (d *DB) GetRepoDidByName(ownerDid, repoName string) (string, error) { var repoDid string err := d.db.QueryRow( `SELECT repo_did FROM repo_keys WHERE owner_did = ? AND repo_name = ?`, ownerDid, repoName, ).Scan(&repoDid) return repoDid, err}
func (d *DB) GetRepoKeyOwner(repoDid string) (string, string, error) { return GetRepoKeyOwner(d.db, repoDid)}
func GetRepoKeyOwner(q DBTX, repoDid string) (ownerDid string, repoName string, err error) { err = q.QueryRow( `SELECT owner_did, rkey FROM repo_aliases WHERE repo_did = ? ORDER BY rev DESC LIMIT 1`, repoDid, ).Scan(&ownerDid, &repoName) if err != nil { return } if ownerDid == "" || repoName == "" { err = fmt.Errorf("repo_aliases row for %s has empty owner_did or rkey", repoDid) return } return}
func (d *DB) ResolveRepoDIDOnDisk(scanPath, repoDid string) (repoPath, ownerDid, repoName string, err error) { ownerDid, repoName, err = d.GetRepoKeyOwner(repoDid) if err != nil { return }
didPath, joinErr := securejoin.SecureJoin(scanPath, repoDid) if joinErr != nil { err = fmt.Errorf("securejoin failed for repo DID path %s: %w", repoDid, joinErr) return }
if _, statErr := os.Stat(didPath); statErr != nil { err = fmt.Errorf("repo DID directory not found on disk: %s", didPath) return }
repoPath = didPath return}