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 | |
---|