From 1af6b91342f8e8ee61eccf6673b5e0db6d4e7002 Mon Sep 17 00:00:00 2001 From: ilotterytea Date: Sat, 10 May 2025 22:55:31 +0500 Subject: feat: badges table --- database.sql | 11 ++++++++++- 1 file changed, 10 insertions(+), 1 deletion(-) (limited to 'database.sql') diff --git a/database.sql b/database.sql index e995320..36beec8 100644 --- a/database.sql +++ b/database.sql @@ -81,13 +81,19 @@ CREATE TABLE IF NOT EXISTS reports ( response_message TEXT ); +CREATE TABLE IF NOT EXISTS badges ( + id CHAR(32) NOT NULL PRIMARY KEY DEFAULT REPLACE(UUID(),'-',''), + uploaded_by CHAR(32) REFERENCES users(id), + created_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP +); + CREATE TABLE IF NOT EXISTS roles ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, priority INTEGER NOT NULL DEFAULT 0, name TEXT NOT NULL, foreground_color TEXT NOT NULL DEFAULT '000,000,000', background_color TEXT NOT NULL DEFAULT 'solid:255,255,255', - badge_id INTEGER NOT NULL DEFAULT 0, + badge_id CHAR(32) REFERENCES badges(id), -- permissions permission_upload BOOLEAN NOT NULL DEFAULT true, @@ -153,6 +159,8 @@ END$$ DELIMITER ; -- NULLIFY EMOTE AUTHORS ON USER DELETION +DROP TRIGGER IF EXISTS user_deletion; + DELIMITER $$ CREATE TRIGGER IF NOT EXISTS user_deletion BEFORE DELETE ON users @@ -161,6 +169,7 @@ BEGIN UPDATE emotes SET uploaded_by = NULL WHERE uploaded_by = OLD.id; UPDATE emote_set_contents SET added_by = NULL WHERE added_by = OLD.id; UPDATE reports SET resolved_by = NULL WHERE resolved_by = OLD.id; + UPDATE badges SET uploaded_by = NULL WHERE uploaded_by = OLD.id; END$$ DELIMITER ; -- cgit v1.2.3