irc/irc.sql at master · SpiderBall/irc · GitHub

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
DROP DATABASE IF EXISTS irc_db;
CREATE DATABASE irc_db;
\c irc_db

CREATE TABLE users (
	id serial,
	username varchar(30),
	password varchar(30),
	PRIMARY KEY (id)
);

CREATE TABLE rooms (
	id serial UNIQUE,
	roomname varchar(30),
/*	user_in_room varchar(30), */
	PRIMARY KEY (id)
);

CREATE TABLE subscriptions (
	id serial,
	room_id int NOT NULL,
	user_id int NOT NULL,
	PRIMARY KEY (id)
);

CREATE TABLE messages (
	message_id serial,
	original_poster_id int NOT NULL,
	message_content VARCHAR(250),
	room_id int NOT NULL,
	PRIMARY KEY (message_id)
);

INSERT INTO users (id, username, password)
VALUES
(DEFAULT, 'SpiderBall', 'sb'),
(DEFAULT, 'MidnaPeach', 'mp'),
(DEFAULT, 'lz', 'lz');

INSERT INTO rooms (id, roomname)
VALUES
(DEFAULT, 'General'),
(DEFAULT, 'Happy'),
(DEFAULT, 'Sad'),
(DEFAULT, 'Hungry');


INSERT INTO subscriptions (id, room_id, user_id)
VALUES
(DEFAULT, 1, 1),
(DEFAULT, 1, 2),
(DEFAULT, 1, 3),
(DEFAULT, 2, 1),
(DEFAULT, 3, 2),
(DEFAULT, 4, 1),
(DEFAULT, 4, 2),
(DEFAULT, 3, 3);
/* sb is subscribed to happy hungry and general
   mp is subscribed to general sad and hungry
   lz is subscribed to general and sad */