notice-sender/trunk/schema.sql

create table users (
        id      serial,
        email   text not null,
        full_name text not null,
        date    timestamp not null default now(),
        active  boolean not null default true,
        bounce_count int not null default 0,
        ext_id  text,
        primary key(id)
);

create table lists (
        id      serial,
        name    text not null,
        bounce_limit int not null default 3,
        date    timestamp not null default now(),
        primary key(id)
);

create index lists_name_ind on lists(name);

create table user_list (
        user_id int not null references users(id) on update cascade on delete cascade,
        list_id int not null references lists(id) on update cascade on delete cascade,
        date    timestamp not null default now(),
        primary key (user_id, list_id)
);

create table messages (
        id      serial,
        message text not null,
        date    timestamp not null default now(),
        primary key(id)
);

create index messages_md5_ind on messages(md5);

create table queue (
        message_id int not null references messages(id) on update cascade on delete cascade,
        list_id int not null references lists(id) on update cascade on delete cascade,
        date    timestamp not null default now(),
        all_sent boolean default false,
        primary key(message_id, list_id)
);

create table sent (
        message_id int not null references messages(id) on update cascade on delete cascade,
        user_id int not null references users(id) on update cascade on delete cascade,
        date    timestamp not null default now(),
        primary key(message_id, user_id)
);

create table received (
        id serial,
        user_id int not null references users(id) on update cascade on delete cascade,
        message text not null,
        bounced boolean not null default false,
        date    timestamp not null default now(),
        primary key (id)
);


insert into users (email, full_name) values ('dpavlin@rot13.org', 'DbP');
insert into users (email, full_name) values ('dpavlin@gmail.com', 'DbP');
insert into users (email, full_name) values ('xxx_doesnt_exist_xxx@gmail.com', 'fake');

insert into lists (name) values ('Just me');
insert into lists (name) values ('All users');

insert into user_list (user_id, list_id) select users.id,1 from users limit 1;
insert into user_list (user_id, list_id) select users.id,2 from users;
1	dpavlin	1	create table users (
2			id serial,
3			email text not null,
4			full_name text not null,
5	dpavlin	4	date timestamp not null default now(),
6			active boolean not null default true,
7			bounce_count int not null default 0,
8			ext_id text,
9	dpavlin	1	primary key(id)
10			);
11
12			create table lists (
13			id serial,
14			name text not null,
15	dpavlin	4	bounce_limit int not null default 3,
16			date timestamp not null default now(),
17	dpavlin	1	primary key(id)
18			);
19
20	dpavlin	5	create index lists_name_ind on lists(name);
21
22	dpavlin	1	create table user_list (
23	dpavlin	4	user_id int not null references users(id) on update cascade on delete cascade,
24			list_id int not null references lists(id) on update cascade on delete cascade,
25			date timestamp not null default now(),
26	dpavlin	1	primary key (user_id, list_id)
27			);
28
29			create table messages (
30			id serial,
31			message text not null,
32	dpavlin	4	date timestamp not null default now(),
33	dpavlin	1	primary key(id)
34			);
35
36	dpavlin	5	create index messages_md5_ind on messages(md5);
37
38	dpavlin	11	create table queue (
39	dpavlin	4	message_id int not null references messages(id) on update cascade on delete cascade,
40	dpavlin	11	list_id int not null references lists(id) on update cascade on delete cascade,
41	dpavlin	4	date timestamp not null default now(),
42	dpavlin	11	all_sent boolean default false,
43	dpavlin	4	primary key(message_id, list_id)
44			);
45
46			create table sent (
47			message_id int not null references messages(id) on update cascade on delete cascade,
48			user_id int not null references users(id) on update cascade on delete cascade,
49			date timestamp not null default now(),
50			primary key(message_id, user_id)
51			);
52
53			create table received (
54	dpavlin	11	id serial,
55			user_id int not null references users(id) on update cascade on delete cascade,
56	dpavlin	4	message text not null,
57			bounced boolean not null default false,
58			date timestamp not null default now(),
59	dpavlin	11	primary key (id)
60	dpavlin	4	);
61
62
63	dpavlin	1	insert into users (email, full_name) values ('dpavlin@rot13.org', 'DbP');
64			insert into users (email, full_name) values ('dpavlin@gmail.com', 'DbP');
65			insert into users (email, full_name) values ('xxx_doesnt_exist_xxx@gmail.com', 'fake');
66
67			insert into lists (name) values ('Just me');
68			insert into lists (name) values ('All users');
69
70			insert into user_list (user_id, list_id) select users.id,1 from users limit 1;
71			insert into user_list (user_id, list_id) select users.id,2 from users;