annotate SQL/postgres/2009103100.sql @ 32:f8b3ac77e951

improved label deletion handling
author Charlie Root
date Tue, 13 Mar 2018 13:01:49 -0400
parents 1e000243b222
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
0
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
1 -- Updates from version 0.3.1
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
2
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
3 DROP INDEX identities_user_id_idx;
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
4 CREATE INDEX identities_user_id_idx ON identities (user_id, del);
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
5
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
6 ALTER TABLE identities ADD changed timestamp with time zone DEFAULT now() NOT NULL;
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
7
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
8 CREATE SEQUENCE contactgroups_ids
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
9 INCREMENT BY 1
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
10 NO MAXVALUE
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
11 NO MINVALUE
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
12 CACHE 1;
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
13
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
14 CREATE TABLE contactgroups (
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
15 contactgroup_id integer DEFAULT nextval('contactgroups_ids'::text) PRIMARY KEY,
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
16 user_id integer NOT NULL
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
17 REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
18 changed timestamp with time zone DEFAULT now() NOT NULL,
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
19 del smallint NOT NULL DEFAULT 0,
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
20 name varchar(128) NOT NULL DEFAULT ''
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
21 );
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
22
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
23 CREATE INDEX contactgroups_user_id_idx ON contactgroups (user_id, del);
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
24
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
25 CREATE TABLE contactgroupmembers (
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
26 contactgroup_id integer NOT NULL
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
27 REFERENCES contactgroups(contactgroup_id) ON DELETE CASCADE ON UPDATE CASCADE,
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
28 contact_id integer NOT NULL
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
29 REFERENCES contacts(contact_id) ON DELETE CASCADE ON UPDATE CASCADE,
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
30 created timestamp with time zone DEFAULT now() NOT NULL,
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
31 PRIMARY KEY (contactgroup_id, contact_id)
1e000243b222 vanilla 1.3.3 distro, I hope
Charlie Root
parents:
diff changeset
32 );