Something went wrong. Try again.
Monorepo for Tangled
Something went wrong. Try again.
9.1 kB · 333 lines
Go
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334package db
import ( "context" "database/sql" "fmt" "log/slog" "os" "strings"
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}
type DBTX interface { QueryRow(query string, args ...any) *sql.Row 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 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 }
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 }
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) 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}