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	dpavlin	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;