File size: 1,914 Bytes
cfe1d88 330856f cfe1d88 330856f cfe1d88 330856f cfe1d88 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 | CREATE TABLE IF NOT EXISTS users (
id TEXT PRIMARY KEY,
username TEXT UNIQUE,
password_hash TEXT,
display_name TEXT,
bio TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS tweets (
id TEXT PRIMARY KEY,
user_id TEXT,
content TEXT,
quote_tweet_id TEXT,
reply_to_id TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (quote_tweet_id) REFERENCES tweets(id),
FOREIGN KEY (reply_to_id) REFERENCES tweets(id)
);
CREATE TABLE IF NOT EXISTS follows (
follower_id TEXT,
followed_id TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (follower_id, followed_id),
FOREIGN KEY (follower_id) REFERENCES users(id),
FOREIGN KEY (followed_id) REFERENCES users(id)
);
CREATE TABLE IF NOT EXISTS likes (
user_id TEXT,
tweet_id TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (user_id, tweet_id),
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (tweet_id) REFERENCES tweets(id)
);
CREATE TABLE IF NOT EXISTS bookmarks (
user_id TEXT,
tweet_id TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (user_id, tweet_id),
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (tweet_id) REFERENCES tweets(id)
);
CREATE TABLE IF NOT EXISTS retweets (
user_id TEXT,
tweet_id TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (user_id, tweet_id),
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (tweet_id) REFERENCES tweets(id)
);
CREATE TABLE IF NOT EXISTS messages (
id TEXT PRIMARY KEY,
sender_id TEXT,
receiver_id TEXT,
content TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (sender_id) REFERENCES users(id),
FOREIGN KEY (receiver_id) REFERENCES users(id)
);
|