/[notice-sender]/trunk/schema.sql
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Contents of /trunk/schema.sql

Parent Directory Parent Directory | Revision Log Revision Log


Revision 45 - (show annotations)
Wed May 18 13:12:54 2005 UTC (18 years, 10 months ago) by dpavlin
File size: 2512 byte(s)
added delete_member

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 email text not null,
18 bounce_limit int not null default 3,
19 date timestamp not null default now(),
20 primary key(id)
21 );
22
23 create unique index lists_name_ind on lists(name);
24 create unique index lists_email_ind on lists(email);
25
26 create table user_list (
27 user_id int not null references users(id) on update cascade on delete cascade,
28 list_id int not null references lists(id) on update cascade on delete cascade,
29 date timestamp not null default now(),
30 primary key (user_id, list_id)
31 );
32
33 create table messages (
34 id serial,
35 message text not null,
36 date timestamp not null default now(),
37 primary key(id)
38 );
39
40 create table queue (
41 message_id int not null references messages(id) on update cascade on delete cascade,
42 list_id int not null references lists(id) on update cascade on delete cascade,
43 date timestamp not null default now(),
44 all_sent boolean default false,
45 primary key(message_id, list_id)
46 );
47
48 create table sent (
49 message_id int not null references messages(id) on update cascade on delete cascade,
50 user_id int not null references users(id) on update cascade on delete cascade,
51 hash text not null,
52 date timestamp not null default now(),
53 primary key(message_id, user_id)
54 );
55
56 create unique index sent_hash_ind on sent(hash);
57
58 create table received (
59 id serial,
60 user_id int not null references users(id),
61 list_id int not null references lists(id),
62 message_id int references messages(id),
63 message text not null,
64 bounced boolean not null default false,
65 date timestamp not null default now(),
66 primary key (id)
67 );
68
69 create index received_user_id_int on received(user_id);
70 create index received_list_id_int on received(list_id);
71
72
73 insert into users (email, name) values ('dpavlin@rot13.org', 'DbP');
74 insert into users (email, name) values ('dpavlin@gmail.com', 'DbP');
75 insert into users (email, name) values ('xxx_doesnt_exist_xxx@gmail.com', 'fake');
76
77 insert into lists (name, email) values ('Just me','my-alter-ego@rot13.org');
78 insert into lists (name, email) values ('All users','announce@example.com');
79
80 insert into user_list (user_id, list_id) select users.id,1 from users limit 1;
81 insert into user_list (user_id, list_id) select users.id,2 from users;

  ViewVC Help
Powered by ViewVC 1.1.26