notice-sender/trunk/schema.sql

create table users (
        id      serial,
        email   text not null,
        name    text default '',
        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 unique index users_email_ind on users(email);

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

create unique index lists_name_ind on lists(name);
create unique index lists_email_ind on lists(email);

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 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,
        hash    text not null,
        date    timestamp not null default now(),
        primary key(message_id, user_id)
);

create unique index sent_hash_ind on sent(hash);

create table received (
        id serial,
        user_id int references users(id),
        list_id int not null references lists(id),
        message_id int references messages(id),
        message text not null,
        bounced boolean not null default false,
        date    timestamp not null default now(),
        primary key (id)
);

create index received_user_id_int on received(user_id);
create index received_list_id_int on received(list_id);

1	dpavlin	1	create table users (
2			id serial,
3			email text not null,
4	dpavlin	45	name text default '',
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	dpavlin	30	create unique index users_email_ind on users(email);
13
14	dpavlin	1	create table lists (
15			id serial,
16			name text not null,
17	dpavlin	47	from_addr text not null default '',
18	dpavlin	30	email text not null,
19	dpavlin	4	bounce_limit int not null default 3,
20			date timestamp not null default now(),
21	dpavlin	1	primary key(id)
22			);
23
24	dpavlin	30	create unique index lists_name_ind on lists(name);
25			create unique index lists_email_ind on lists(email);
26	dpavlin	5
27	dpavlin	1	create table user_list (
28	dpavlin	4	user_id int not null references users(id) on update cascade on delete cascade,
29			list_id int not null references lists(id) on update cascade on delete cascade,
30			date timestamp not null default now(),
31	dpavlin	1	primary key (user_id, list_id)
32			);
33
34			create table messages (
35			id serial,
36			message text not null,
37	dpavlin	4	date timestamp not null default now(),
38	dpavlin	1	primary key(id)
39			);
40
41	dpavlin	11	create table queue (
42	dpavlin	4	message_id int not null references messages(id) on update cascade on delete cascade,
43	dpavlin	11	list_id int not null references lists(id) on update cascade on delete cascade,
44	dpavlin	4	date timestamp not null default now(),
45	dpavlin	11	all_sent boolean default false,
46	dpavlin	4	primary key(message_id, list_id)
47			);
48
49			create table sent (
50			message_id int not null references messages(id) on update cascade on delete cascade,
51			user_id int not null references users(id) on update cascade on delete cascade,
52	dpavlin	36	hash text not null,
53	dpavlin	4	date timestamp not null default now(),
54			primary key(message_id, user_id)
55			);
56
57	dpavlin	36	create unique index sent_hash_ind on sent(hash);
58
59	dpavlin	4	create table received (
60	dpavlin	11	id serial,
61	dpavlin	47	user_id int references users(id),
62	dpavlin	36	list_id int not null references lists(id),
63			message_id int references messages(id),
64	dpavlin	4	message text not null,
65			bounced boolean not null default false,
66			date timestamp not null default now(),
67	dpavlin	11	primary key (id)
68	dpavlin	4	);
69
70	dpavlin	36	create index received_user_id_int on received(user_id);
71			create index received_list_id_int on received(list_id);
72	dpavlin	4