1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
|
-- These are changes needed to the schema to support moving over to DBIx::Class
begin;
-- 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 ''
);
--- add PK to contacts table
ALTER TABLE contacts
ADD COLUMN id SERIAL PRIMARY KEY;
AlTER TABLE contacts_history
ADD COLUMN contact_id integer;
update contacts_history
set contact_id = (
select id
from contacts
where contacts_history.category = contacts.category
and contacts_history.area_id = contacts.area_id
);
-- Note any categories that have been deleted will cause the following
-- line to fail, and they'll need to be deleted manually first.
AlTER TABLE contacts_history
alter COLUMN contact_id SET NOT NULL;
create or replace function contacts_updated()
returns trigger as '
begin
insert into contacts_history (contact_id, area_id, category, email, editor, whenedited, note, confirmed, deleted) values (new.id, new.area_id, new.category, new.email, new.editor, new.whenedited, new.note, new.confirmed, new.deleted);
return new;
end;
' language 'plpgsql';
--- add pk and lowercase check to abuse
drop index abuse_email_idx;
update abuse set email = lower(email);
alter table abuse add check( lower(email) = email );
alter table abuse add primary key(email);
commit;
|
