[3733] | 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 | |
---|