1 | |
---|
2 | -- This database update provides new tables for the Principal, for |
---|
3 | -- a consistent dav_resource which a principal, collection or calendar_item |
---|
4 | -- all inherit from. |
---|
5 | |
---|
6 | BEGIN; |
---|
7 | SELECT check_db_revision(1,2,1); |
---|
8 | |
---|
9 | -- Only needs SELECT access by website. |
---|
10 | CREATE TABLE principal_type ( |
---|
11 | principal_type_id SERIAL PRIMARY KEY, |
---|
12 | principal_type_desc TEXT |
---|
13 | ); |
---|
14 | |
---|
15 | -- web needs SELECT,INSERT,UPDATE,DELETE |
---|
16 | CREATE TABLE principal ( |
---|
17 | principal_id SERIAL PRIMARY KEY, |
---|
18 | type_id INT8 NOT NULL REFERENCES principal_type(principal_type_id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE, |
---|
19 | user_no INT8 NULL REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE, |
---|
20 | displayname TEXT, |
---|
21 | active BOOLEAN |
---|
22 | ); |
---|
23 | |
---|
24 | -- Allowing identification of group members. |
---|
25 | CREATE TABLE group_member ( |
---|
26 | group_id INT8 REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE, |
---|
27 | member_id INT8 REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE |
---|
28 | ); |
---|
29 | CREATE UNIQUE INDEX group_member_pk ON group_member(group_id,member_id); |
---|
30 | CREATE INDEX group_member_sk ON group_member(member_id); |
---|
31 | |
---|
32 | |
---|
33 | -- Only needs SELECT access by website. dav_resource_type will be 'principal', 'collection', 'CalDAV:calendar' and so forth. |
---|
34 | CREATE TABLE dav_resource_type ( |
---|
35 | resource_type_id SERIAL PRIMARY KEY, |
---|
36 | dav_resource_type TEXT, |
---|
37 | resource_type_desc TEXT |
---|
38 | ); |
---|
39 | |
---|
40 | CREATE TABLE dav_resource ( |
---|
41 | dav_id INT8 PRIMARY KEY DEFAULT nextval('dav_id_seq'), |
---|
42 | dav_name TEXT, |
---|
43 | resource_type_id INT8 REFERENCES dav_resource_type(resource_type_id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE, |
---|
44 | owner_id INT8 REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE |
---|
45 | ); |
---|
46 | |
---|
47 | |
---|
48 | CREATE TABLE privilege ( |
---|
49 | granted_to_id INT8 REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE, |
---|
50 | resource_id INT8 REFERENCES dav_resource(dav_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE, |
---|
51 | granted_by_id INT8 REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE, |
---|
52 | can_read BOOLEAN, |
---|
53 | can_write BOOLEAN, |
---|
54 | can_write_properties BOOLEAN, |
---|
55 | can_write_content BOOLEAN, |
---|
56 | can_unlock BOOLEAN, |
---|
57 | can_read_acl BOOLEAN, |
---|
58 | can_read_current_user_privilege_set BOOLEAN, |
---|
59 | can_write_acl BOOLEAN, |
---|
60 | can_bind BOOLEAN, |
---|
61 | can_unbind BOOLEAN, |
---|
62 | can_read_free_busy BOOLEAN, |
---|
63 | PRIMARY KEY (granted_to_id, resource_id) |
---|
64 | ); |
---|
65 | |
---|
66 | SELECT new_db_revision(1,2,2, 'Fevrier' ); |
---|
67 | |
---|
68 | COMMIT; |
---|
69 | ROLLBACK; |
---|
70 | |
---|