[3733] | 1 | -- Some sample data to prime the database... |
---|
| 2 | -- base-data.sql should be processed before this |
---|
| 3 | |
---|
| 4 | INSERT INTO usr ( user_no, active, email_ok, updated, username, password, fullname, email ) |
---|
| 5 | VALUES( 2, TRUE, current_date, current_date, 'andrew', '**x', 'Andrew McMillan', 'andrew@catalyst.net.nz' ); |
---|
| 6 | INSERT INTO role_member (user_no, role_no) VALUES( 2, 1); |
---|
| 7 | |
---|
| 8 | |
---|
| 9 | INSERT INTO usr ( user_no, active, email_ok, updated, username, password, fullname, email ) |
---|
| 10 | VALUES( 10, TRUE, current_date, current_date, 'user1', '**user1', 'User 1', 'user1@example.net' ); |
---|
| 11 | INSERT INTO usr ( user_no, active, email_ok, updated, username, password, fullname, email ) |
---|
| 12 | VALUES( 11, TRUE, current_date, current_date, 'user2', '**user2', 'User 2', 'user2@example.net' ); |
---|
| 13 | INSERT INTO usr ( user_no, active, email_ok, updated, username, password, fullname, email ) |
---|
| 14 | VALUES( 12, TRUE, current_date, current_date, 'user3', '**user3', 'User 3', 'user3@example.net' ); |
---|
| 15 | INSERT INTO usr ( user_no, active, email_ok, updated, username, password, fullname, email ) |
---|
| 16 | VALUES( 13, TRUE, current_date, current_date, 'user4', '**user4', 'User 4', 'user4@example.net' ); |
---|
| 17 | INSERT INTO usr ( user_no, active, email_ok, updated, username, password, fullname, email ) |
---|
| 18 | VALUES( 14, TRUE, current_date, current_date, 'user5', '**user5', 'User 5', 'user5@example.net' ); |
---|
| 19 | INSERT INTO usr ( user_no, active, email_ok, updated, username, password, fullname, email ) |
---|
| 20 | VALUES( 15, TRUE, current_date, current_date, 'User Six', '**user6', 'User 6', 'user6@example.net' ); |
---|
| 21 | |
---|
| 22 | INSERT INTO usr ( user_no, active, email_ok, updated, username, password, fullname, email ) |
---|
| 23 | VALUES( 20, TRUE, current_date, current_date, 'manager1', '**manager1', 'Manager 1', 'manager1@example.net' ); |
---|
| 24 | |
---|
| 25 | INSERT INTO usr ( user_no, active, email_ok, updated, username, password, fullname, email ) |
---|
| 26 | VALUES( 30, TRUE, current_date, current_date, 'assistant1', '**assistant1', 'Assistant 1', 'assistant1@example.net' ); |
---|
| 27 | |
---|
| 28 | |
---|
| 29 | INSERT INTO usr ( user_no, active, email_ok, updated, username, password, fullname, email ) |
---|
| 30 | VALUES( 100, TRUE, current_date, current_date, 'resource1', '*salt*unpossible', 'Resource 1', 'resource1@example.net' ); |
---|
| 31 | INSERT INTO role_member (user_no, role_no) VALUES( 100, 4); |
---|
| 32 | INSERT INTO usr ( user_no, active, email_ok, updated, username, password, fullname, email ) |
---|
| 33 | VALUES( 101, TRUE, current_date, current_date, 'resource2', '*salt*unpossible', 'Resource 2', 'resource2@example.net' ); |
---|
| 34 | INSERT INTO role_member (user_no, role_no) VALUES( 101, 4); |
---|
| 35 | |
---|
| 36 | INSERT INTO usr ( user_no, active, email_ok, updated, username, password, fullname, email ) |
---|
| 37 | VALUES( 200, TRUE, current_date, current_date, 'resmgr1', '*salt*unpossible', 'Resource Managers', 'resource-managers@example.net' ); |
---|
| 38 | INSERT INTO role_member (user_no, role_no) VALUES( 200, 2); |
---|
| 39 | |
---|
| 40 | INSERT INTO usr ( user_no, active, email_ok, updated, username, password, fullname, email ) |
---|
| 41 | VALUES( 300, TRUE, current_date, current_date, 'teamclient1', '*salt*unpossible', 'Team for Client1', 'team-client1@example.net' ); |
---|
| 42 | INSERT INTO role_member (user_no, role_no) VALUES( 300, 2); |
---|
| 43 | |
---|
| 44 | SELECT setval('usr_user_no_seq', 1000); |
---|
| 45 | |
---|
| 46 | UPDATE usr SET joined = '2009-06-01', updated = '2009-06-02'; |
---|
| 47 | |
---|
| 48 | INSERT INTO collection (user_no, parent_container, dav_name, dav_etag, |
---|
| 49 | dav_displayname, is_calendar, created, modified, |
---|
| 50 | public_events_only, publicly_readable, collection_id, resourcetypes ) |
---|
| 51 | SELECT user_no, '/' || username || '/', '/' || username || '/home/', md5(username), |
---|
| 52 | username || ' home', TRUE, '2009-06-03', '2009-06-04', |
---|
| 53 | FALSE, FALSE, user_no, '<DAV::collection/><urn:ietf:params:xml:ns:caldav:calendar/>' |
---|
| 54 | FROM usr; |
---|
| 55 | |
---|
| 56 | INSERT INTO principal (type_id, user_no, displayname, default_privileges) |
---|
| 57 | SELECT 1, user_no, fullname, privilege_to_bits(ARRAY['read-free-busy','schedule-send','schedule-deliver']) FROM usr |
---|
| 58 | WHERE NOT EXISTS(SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_name = 'Group' AND role_member.user_no = usr.user_no) |
---|
| 59 | AND NOT EXISTS(SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_name = 'Resource' AND role_member.user_no = usr.user_no) |
---|
| 60 | AND NOT EXISTS(SELECT 1 FROM principal WHERE principal.user_no = usr.user_no); |
---|
| 61 | |
---|
| 62 | INSERT INTO principal (type_id, user_no, displayname, default_privileges) |
---|
| 63 | SELECT 2, user_no, fullname, privilege_to_bits(ARRAY['read','schedule-send','schedule-deliver']) FROM usr |
---|
| 64 | WHERE EXISTS(SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_name = 'Resource' AND role_member.user_no = usr.user_no) |
---|
| 65 | AND NOT EXISTS(SELECT 1 FROM principal WHERE principal.user_no = usr.user_no); |
---|
| 66 | |
---|
| 67 | INSERT INTO principal (type_id, user_no, displayname, default_privileges) |
---|
| 68 | SELECT 3, user_no, fullname, privilege_to_bits(ARRAY['read-free-busy','schedule-send','schedule-deliver']) FROM usr |
---|
| 69 | WHERE EXISTS(SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_name = 'Group' AND role_member.user_no = usr.user_no) |
---|
| 70 | AND NOT EXISTS(SELECT 1 FROM principal WHERE principal.user_no = usr.user_no); |
---|
| 71 | |
---|
| 72 | SELECT setval('dav_id_seq', 1000); |
---|
| 73 | |
---|
| 74 | -- Set the insert sequence to the next number, with a minimum of 1000 |
---|
| 75 | SELECT setval('relationship_type_rt_id_seq', (SELECT 10 UNION SELECT rt_id FROM relationship_type ORDER BY 1 DESC LIMIT 1) ); |
---|
| 76 | |
---|
| 77 | -- The resources for meetings |
---|
| 78 | INSERT INTO relationship ( from_user, to_user, rt_id ) VALUES( 200, 100, 1 ); |
---|
| 79 | INSERT INTO relationship ( from_user, to_user, rt_id ) VALUES( 200, 101, 1 ); |
---|
| 80 | |
---|
| 81 | -- The people who administer meetings |
---|
| 82 | INSERT INTO relationship ( from_user, to_user, rt_id ) VALUES( 10, 200, 1 ); |
---|
| 83 | INSERT INTO relationship ( from_user, to_user, rt_id ) VALUES( 11, 200, 1 ); |
---|
| 84 | INSERT INTO relationship ( from_user, to_user, rt_id ) VALUES( 30, 200, 1 ); |
---|
| 85 | |
---|
| 86 | -- Between a PA and their Manager |
---|
| 87 | INSERT INTO relationship ( from_user, to_user, rt_id ) VALUES( 30, 20, 2 ); |
---|
| 88 | INSERT INTO relationship ( from_user, to_user, rt_id ) VALUES( 30, 10, 2 ); |
---|
| 89 | |
---|
| 90 | |
---|
| 91 | -- Between a team |
---|
| 92 | INSERT INTO relationship ( from_user, to_user, rt_id ) VALUES( 20, 300, 3 ); |
---|
| 93 | INSERT INTO relationship ( from_user, to_user, rt_id ) VALUES( 10, 300, 3 ); |
---|
| 94 | INSERT INTO relationship ( from_user, to_user, rt_id ) VALUES( 30, 300, 3 ); |
---|
| 95 | INSERT INTO relationship ( from_user, to_user, rt_id ) VALUES( 300, 20, 3 ); |
---|
| 96 | INSERT INTO relationship ( from_user, to_user, rt_id ) VALUES( 300, 10, 3 ); |
---|
| 97 | INSERT INTO relationship ( from_user, to_user, rt_id ) VALUES( 300, 30, 3 ); |
---|
| 98 | |
---|
| 99 | -- Granting explicit free/busy permission |
---|
| 100 | INSERT INTO relationship ( from_user, to_user, rt_id ) VALUES( 11, 10, 4 ); |
---|
| 101 | INSERT INTO relationship ( from_user, to_user, rt_id ) VALUES( 10, 11, 4 ); |
---|
| 102 | |
---|
| 103 | |
---|
| 104 | UPDATE relationship r SET confers = (SELECT bit_confers FROM relationship_type rt WHERE rt.rt_id=r.rt_id); |
---|
| 105 | |
---|
| 106 | INSERT INTO group_member ( group_id, member_id) |
---|
| 107 | SELECT g.principal_id, m.principal_id |
---|
| 108 | FROM relationship JOIN principal g ON(to_user=g.user_no AND g.type_id = 3) -- Group |
---|
| 109 | JOIN principal m ON(from_user=m.user_no AND m.type_id IN (1,2)); -- Person | Resource |
---|
| 110 | |
---|
| 111 | INSERT INTO grants ( by_principal, to_principal, privileges, is_group ) |
---|
| 112 | SELECT pby.principal_id AS by_principal, pto.principal_id AS to_principal, |
---|
| 113 | confers AS privileges, pto.type_id > 2 AS is_group |
---|
| 114 | FROM relationship r JOIN usr f ON(f.user_no=r.from_user) |
---|
| 115 | JOIN usr t ON(t.user_no=r.to_user) |
---|
| 116 | JOIN principal pby ON(t.user_no=pby.user_no) |
---|
| 117 | JOIN principal pto ON(pto.user_no=f.user_no) |
---|
| 118 | WHERE rt_id < 4 AND pby.type_id < 3; |
---|