/[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 30 by dpavlin, Mon May 16 21:54:41 2005 UTC revision 47 by dpavlin, Tue May 24 14:02:05 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 not null default now(),          date    timestamp not null default now(),
6          active  boolean not null default true,          active  boolean not null default true,
7          bounce_count int not null default 0,          bounce_count int not null default 0,
# Line 14  create unique index users_email_ind on u Line 14  create unique index users_email_ind on u
14  create table lists (  create table lists (
15          id      serial,          id      serial,
16          name    text not null,          name    text not null,
17            from_addr       text not null default '',
18          email   text not null,          email   text not null,
19          bounce_limit int not null default 3,          bounce_limit int not null default 3,
20          date    timestamp not null default now(),          date    timestamp not null default now(),
# Line 48  create table queue ( Line 49  create table queue (
49  create table sent (  create table sent (
50          message_id int not null references messages(id) on update cascade on delete cascade,          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,          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(),          date    timestamp not null default now(),
54          primary key(message_id, user_id)          primary key(message_id, user_id)
55  );  );
56    
57    create unique index sent_hash_ind on sent(hash);
58    
59  create table received (  create table received (
60          id serial,          id serial,
61          user_id int not null references users(id) on update cascade on delete cascade,          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,          message text not null,
65          bounced boolean not null default false,          bounced boolean not null default false,
66          date    timestamp not null default now(),          date    timestamp not null default now(),
67          primary key (id)          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    
 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, email) values ('Just me','my-alter-ego@rot13.org');  
 insert into lists (name, email) values ('All users','announce@example.com');  
   
 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;  
Legend:
Removed from v.30  
changed lines
  Added in v.47
  ViewVC Help
Powered by ViewVC 1.1.26