-- lfs: squashed from 0200_lfs.sql, 0905_lfs_shared_storage.sql.
-- Which repositories may serve an oid. owner_id is denormalized so triggers can bill without a join
-- (the repo row may already be gone during a cascading delete).
CREATE TABLE lfs_objects (
repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
oid TEXT NOT NULL,
owner_id TEXT NOT NULL,
size INTEGER NOT NULL,
created_at INTEGER NOT NULL,
PRIMARY KEY (repo_id, oid)
);
CREATE INDEX lfs_objects_oid ON lfs_objects (oid);
CREATE TRIGGER lfs_objects_link AFTER INSERT ON lfs_objects BEGIN
UPDATE lfs_blobs SET refs = refs + 1, orphaned_at = NULL WHERE oid = NEW.oid;
INSERT OR IGNORE INTO lfs_account_objects (owner_id, oid, size, refs) VALUES (NEW.owner_id, NEW.oid, NEW.size, 0);
UPDATE lfs_account_objects SET refs = refs + 1 WHERE owner_id = NEW.owner_id AND oid = NEW.oid;
INSERT OR IGNORE INTO lfs_usage (owner_id) SELECT NEW.owner_id WHERE EXISTS (SELECT 1 FROM accounts WHERE id = NEW.owner_id);
UPDATE lfs_usage SET storage_bytes = storage_bytes + NEW.size
WHERE owner_id = NEW.owner_id
AND (SELECT refs FROM lfs_account_objects WHERE owner_id = NEW.owner_id AND oid = NEW.oid) = 1;
END;
CREATE TRIGGER lfs_objects_unlink AFTER DELETE ON lfs_objects BEGIN
UPDATE lfs_blobs SET refs = MAX(refs - 1, 0) WHERE oid = OLD.oid;
UPDATE lfs_blobs SET orphaned_at = CAST(unixepoch('subsec') * 1000 AS INTEGER) WHERE oid = OLD.oid AND refs = 0;
UPDATE lfs_account_objects SET refs = refs - 1 WHERE owner_id = OLD.owner_id AND oid = OLD.oid;
UPDATE lfs_usage SET storage_bytes = MAX(storage_bytes - OLD.size, 0)
WHERE owner_id = OLD.owner_id
AND (SELECT refs FROM lfs_account_objects WHERE owner_id = OLD.owner_id AND oid = OLD.oid) = 0;
DELETE FROM lfs_account_objects WHERE owner_id = OLD.owner_id AND oid = OLD.oid AND refs <= 0;
END;
-- Transfers move billing to the new owner.
CREATE TRIGGER lfs_objects_rebill AFTER UPDATE OF owner_id ON lfs_objects WHEN OLD.owner_id != NEW.owner_id BEGIN
UPDATE lfs_account_objects SET refs = refs - 1 WHERE owner_id = OLD.owner_id AND oid = OLD.oid;
UPDATE lfs_usage SET storage_bytes = MAX(storage_bytes - OLD.size, 0)
WHERE owner_id = OLD.owner_id