notice-sender/trunk/schema.sql

create table users (
        id      serial,
        email   text not null,
        full_name text not null,
        date    timestamp default now(),
        primary key(id)
);

create table lists (
        id      serial,
        name    text not null,
        date    timestamp default now(),
        primary key(id)
);

create table user_list (
        user_id int not null references users(id),
        list_id int not null references lists(id),
        date    timestamp default now(),
        primary key (user_id, list_id)
);

create table messages (
        id      serial,
        message text not null,
        date    timestamp default now(),
        primary key(id)
);

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) values ('Just me');
insert into lists (name) values ('All users');

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;
1	create table users (
2	id serial,
3	email text not null,
4	full_name text not null,
5	date timestamp default now(),
6	primary key(id)
7	);
8
9	create table lists (
10	id serial,
11	name text not null,
12	date timestamp default now(),
13	primary key(id)
14	);
15
16	create table user_list (
17	user_id int not null references users(id),
18	list_id int not null references lists(id),
19	date timestamp default now(),
20	primary key (user_id, list_id)
21	);
22
23	create table messages (
24	id serial,
25	message text not null,
26	date timestamp default now(),
27	primary key(id)
28	);
29
30	insert into users (email, full_name) values ('dpavlin@rot13.org', 'DbP');
31	insert into users (email, full_name) values ('dpavlin@gmail.com', 'DbP');
32	insert into users (email, full_name) values ('xxx_doesnt_exist_xxx@gmail.com', 'fake');
33
34	insert into lists (name) values ('Just me');
35	insert into lists (name) values ('All users');
36
37	insert into user_list (user_id, list_id) select users.id,1 from users limit 1;
38	insert into user_list (user_id, list_id) select users.id,2 from users;