diff options
Diffstat (limited to 'db/schema.sql')
| -rw-r--r-- | db/schema.sql | 23 |
1 files changed, 21 insertions, 2 deletions
diff --git a/db/schema.sql b/db/schema.sql index fbff047fb..ae66fc28c 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -66,6 +66,7 @@ create function ms_current_timestamp() -- Who to send problems for a specific MaPit area ID to create table contacts ( + id serial primary key, area_id integer not null, category text not null default 'Other', email text not null, @@ -86,6 +87,7 @@ create unique index contacts_area_id_category_idx on contacts(area_id, category) create table contacts_history ( contacts_history_id serial not null primary key, + contact_id integer not null, area_id integer not null, category text not null default 'Other', email text not null, @@ -115,6 +117,22 @@ create trigger contacts_update_trigger after update on contacts create trigger contacts_insert_trigger after insert on contacts for each row execute procedure contacts_updated(); +-- table for sessions - needed by Catalyst::Plugin::Session::Store::DBIC +create table sessions ( + id char(72) primary key, + session_data text, + expires integer +); + +-- users table +create table users ( + id serial not null primary key, + email text not null unique, + name text, + phone text, + password text not null default '' +); + -- Problems reported by users of site create table problem ( id serial not null primary key, @@ -132,9 +150,8 @@ create table problem ( used_map boolean not null, -- User's details + user_id int references users(id) not null, name text not null, - email text not null, - phone text not null, anonymous boolean not null, -- Metadata @@ -156,6 +173,7 @@ create table problem ( send_questionnaire boolean not null default 't' ); create index problem_state_latitude_longitude_idx on problem(state, latitude, longitude); +create index problem_user_id_idx on problem ( user_id ); create table questionnaire ( id serial not null primary key, @@ -362,3 +380,4 @@ create table admin_log ( or action = 'resend'), whenedited timestamp not null default ms_current_timestamp() ); + |