50 lines
1.0 KiB
SQL
Executable File
50 lines
1.0 KiB
SQL
Executable File
CREATE TABLE IF NOT EXISTS channels (
|
|
id VARCHAR(40),
|
|
detail TEXT,
|
|
private BOOL,
|
|
PRIMARY KEY(id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
email VARCHAR(40),
|
|
nickname VARCHAR(40) UNIQUE,
|
|
password VARCHAR(40),
|
|
propic TEXT,
|
|
PRIMARY KEY(email)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS allowed_users (
|
|
user_email VARCHAR(40),
|
|
channel_id VARCHAR(40),
|
|
PRIMARY KEY(user_email, channel_id),
|
|
FOREIGN KEY(user_email) REFERENCES users(email)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS messages (
|
|
id VARCHAR(40),
|
|
channel_id VARCHAR(40),
|
|
data TEXT,
|
|
author VARCHAR(40),
|
|
ts timestamp NOT NULL DEFAULT NOW(),
|
|
PRIMARY KEY(id),
|
|
FOREIGN KEY(author) REFERENCES users(email)
|
|
);
|
|
|
|
INSERT INTO
|
|
channels (id, detail, private)
|
|
VALUES
|
|
('Crypto', 'web3 without crypto?', FALSE);
|
|
|
|
INSERT INTO
|
|
channels (id, detail, private)
|
|
VALUES
|
|
('Random', 'whatever', FALSE);
|
|
|
|
INSERT INTO
|
|
channels (id, detail, private)
|
|
VALUES
|
|
(
|
|
'Brews',
|
|
'Share your best caffeine based concoction',
|
|
FALSE
|
|
); |