[3733] | 1 | -- Define an updateable view for dav_principal which conbines the AWL usr |
---|
| 2 | -- record 1:1 with the principal table |
---|
| 3 | |
---|
| 4 | |
---|
| 5 | DROP VIEW dav_principal CASCADE; |
---|
| 6 | CREATE OR REPLACE VIEW dav_principal AS |
---|
| 7 | SELECT user_no, usr.active AS user_active, joined AS created, updated AS modified, |
---|
| 8 | username, password, fullname, email, |
---|
| 9 | email_ok, date_format_type, locale, |
---|
| 10 | principal_id, type_id, displayname, default_privileges, |
---|
| 11 | TRUE AS is_principal, |
---|
| 12 | FALSE AS is_calendar, |
---|
| 13 | principal_id AS collection_id, |
---|
| 14 | FALSE AS is_addressbook, |
---|
| 15 | '/' || username || '/' AS dav_name, |
---|
| 16 | '<DAV::collection/><DAV::principal/>'::text AS resourcetypes |
---|
| 17 | FROM usr JOIN principal USING(user_no); |
---|
| 18 | |
---|
| 19 | CREATE or REPLACE RULE dav_principal_insert AS ON INSERT TO dav_principal |
---|
| 20 | DO INSTEAD |
---|
| 21 | ( |
---|
| 22 | INSERT INTO usr ( user_no, active, joined, updated, username, password, fullname, email, email_ok, date_format_type, locale ) |
---|
| 23 | VALUES( |
---|
| 24 | COALESCE( NEW.user_no, nextval('usr_user_no_seq')), |
---|
| 25 | COALESCE( NEW.user_active, TRUE), |
---|
| 26 | current_timestamp, |
---|
| 27 | current_timestamp, |
---|
| 28 | NEW.username, NEW.password, |
---|
| 29 | COALESCE( NEW.fullname, NEW.displayname ), |
---|
| 30 | NEW.email, NEW.email_ok, |
---|
| 31 | COALESCE( NEW.date_format_type, 'E'), |
---|
| 32 | NEW.locale |
---|
| 33 | ); |
---|
| 34 | INSERT INTO principal ( user_no, principal_id, type_id, displayname, default_privileges ) |
---|
| 35 | VALUES( |
---|
| 36 | COALESCE( NEW.user_no, currval('usr_user_no_seq')), |
---|
| 37 | COALESCE( NEW.principal_id, nextval('dav_id_seq')), |
---|
| 38 | NEW.type_id, |
---|
| 39 | COALESCE( NEW.displayname, NEW.fullname ), |
---|
| 40 | COALESCE( NEW.default_privileges, 0::BIT(24)) |
---|
| 41 | ); |
---|
| 42 | ); |
---|
| 43 | |
---|
| 44 | |
---|
| 45 | CREATE or REPLACE RULE dav_principal_update AS ON UPDATE TO dav_principal |
---|
| 46 | DO INSTEAD |
---|
| 47 | ( |
---|
| 48 | UPDATE usr |
---|
| 49 | SET |
---|
| 50 | user_no=NEW.user_no, |
---|
| 51 | active=NEW.user_active, |
---|
| 52 | updated=current_timestamp, |
---|
| 53 | username=NEW.username, |
---|
| 54 | password=NEW.password, |
---|
| 55 | fullname=NEW.fullname, |
---|
| 56 | email=NEW.email, |
---|
| 57 | email_ok=NEW.email_ok, |
---|
| 58 | date_format_type=NEW.date_format_type, |
---|
| 59 | locale=NEW.locale |
---|
| 60 | WHERE user_no=OLD.user_no; |
---|
| 61 | |
---|
| 62 | UPDATE principal |
---|
| 63 | SET |
---|
| 64 | principal_id = NEW.principal_id, |
---|
| 65 | type_id = NEW.type_id, |
---|
| 66 | displayname = NEW.displayname, |
---|
| 67 | default_privileges = NEW.default_privileges |
---|
| 68 | WHERE principal_id=OLD.principal_id; |
---|
| 69 | ); |
---|
| 70 | |
---|
| 71 | CREATE or REPLACE RULE dav_principal_delete AS ON DELETE TO dav_principal |
---|
| 72 | DO INSTEAD |
---|
| 73 | ( |
---|
| 74 | DELETE FROM usr WHERE user_no=OLD.user_no; |
---|
| 75 | DELETE FROM principal WHERE principal_id=OLD.principal_id; |
---|
| 76 | ); |
---|
| 77 | |
---|