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