From 9533a34322c5ec154860c42af98e0d4e8d44d945 Mon Sep 17 00:00:00 2001 From: ilotterytea Date: Mon, 13 Oct 2025 00:17:36 +0500 Subject: feat: save words into the database --- database.sql | 32 ++++++++++++++++++++++++++++++++ 1 file changed, 32 insertions(+) create mode 100644 database.sql (limited to 'database.sql') diff --git a/database.sql b/database.sql new file mode 100644 index 0000000..e453239 --- /dev/null +++ b/database.sql @@ -0,0 +1,32 @@ +CREATE TABLE IF NOT EXISTS channels ( + id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, + alias_id INTEGER NOT NULL UNIQUE, + alias_name TEXT NOT NULL UNIQUE, + joined_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP, + opted_out_at TIMESTAMP +); + +CREATE TABLE IF NOT EXISTS users ( + id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, + alias_id INTEGER NOT NULL UNIQUE, + alias_name TEXT NOT NULL UNIQUE, + joined_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP, + opted_out_at TIMESTAMP +); + +CREATE TABLE IF NOT EXISTS words ( + id BIGINT PRIMARY KEY AUTO_INCREMENT, + name TEXT NOT NULL UNIQUE +); + +CREATE TABLE IF NOT EXISTS channel_words ( + id BIGINT PRIMARY KEY AUTO_INCREMENT, + channel_id INTEGER NOT NULL REFERENCES channels(id) ON DELETE CASCADE, + user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, + word_id BIGINT NOT NULL REFERENCES words(id) ON DELETE CASCADE, + usage_count BIGINT NOT NULL DEFAULT 0, + first_used_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP, + last_used_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP, + + UNIQUE (channel_id, user_id, word_id) +); \ No newline at end of file -- cgit v1.2.3