/[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

Diff of /trunk/schema.sql

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

revision 1 by dpavlin, Fri May 13 21:17:58 2005 UTC revision 45 by dpavlin, Wed May 18 13:12:54 2005 UTC
# Line 1  Line 1 
1  create table users (  create table users (
2          id      serial,          id      serial,
3          email   text not null,          email   text not null,
4          full_name text not null,          name    text default '',
5          date    timestamp default now(),          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)          primary key(id)
10  );  );
11    
12    create unique index users_email_ind on users(email);
13    
14  create table lists (  create table lists (
15          id      serial,          id      serial,
16          name    text not null,          name    text not null,
17          date    timestamp default now(),          email   text not null,
18            bounce_limit int not null default 3,
19            date    timestamp not null default now(),
20          primary key(id)          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 (  create table user_list (
27          user_id int not null references users(id),          user_id int not null references users(id) on update cascade on delete cascade,
28          list_id int not null references lists(id),          list_id int not null references lists(id) on update cascade on delete cascade,
29          date    timestamp default now(),          date    timestamp not null default now(),
30          primary key (user_id, list_id)          primary key (user_id, list_id)
31  );  );
32    
33  create table messages (  create table messages (
34          id      serial,          id      serial,
35          message text not null,          message text not null,
36          date    timestamp default now(),          date    timestamp not null default now(),
37          primary key(id)          primary key(id)
38  );  );
39    
40  insert into users (email, full_name) values ('dpavlin@rot13.org', 'DbP');  create table queue (
41  insert into users (email, full_name) values ('dpavlin@gmail.com', 'DbP');          message_id int not null references messages(id) on update cascade on delete cascade,
42  insert into users (email, full_name) values ('xxx_doesnt_exist_xxx@gmail.com', 'fake');          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) values ('Just me');  insert into lists (name, email) values ('Just me','my-alter-ego@rot13.org');
78  insert into lists (name) values ('All users');  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;  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;  insert into user_list (user_id, list_id) select users.id,2 from users;
Legend:
Removed from v.1  
changed lines
  Added in v.45
  ViewVC Help
Powered by ViewVC 1.1.26