1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84 | ystv=# SELECT id FROM members;
id
----
1
2
5
(3 rows)
ystv=# SELECT name FROM member_groups;
name
----------------------
officers
Producers
Directors
Technical Assistants
gbaz
gbar
gfoo
(7 rows)
ystv=# SELECT * FROM member_group_permissions;
member_group_name | permission_name
-------------------+-----------------
gfoo | pbaz
gfoo | pbar
(2 rows)
ystv=# SELECT * FROM member_group_members;
member_group_name | member_id
-------------------+-----------
gfoo | 5
gfoo | 1
(2 rows)
ystv=# SELECT name FROM officerships;
name
---------------------------------------------
Station Director
Studio Manager
Production Director
Commercial Director
Technical Director
Computing officer
Secretary
Treasurer
Social Secretary
Scheduling Manager
Head of Presentation
Training Manager
Advertising Manager
PR Manager
Deputy Technical Director
Network Manager
Deputy Computing officer
Web Designer
Controller of News & Current Affairs
Deputy Controller of News & Current Affairs
Controller of Drama & Entertainment
Controller of Factual Programming
ofoo
(23 rows)
ystv=# SELECT * FROM officership_permissions;
permission_name | officership_name
-----------------+------------------
pbar | ofoo
pfoo | ofoo
(2 rows)
ystv=# SELECT * FROM member_officerships;
id | member_id | officership_name | start_date | end_date | start_year | end_year
----+-----------+------------------+------------+------------+------------+----------
1 | 5 | ofoo | 2008-09-10 | 2008-12-12 | |
(1 row)
ystv=# (SELECT member_id, permission_name FROM members JOIN member_group_members ON member_group_members.member_id = members.id JOIN member_group_permissions ON member_group_permissions.member_group_name = member_group_members.member_group_name) UNION (SELECT member_id, permission_name FROM members JOIN member_officerships ON member_officerships.member_id = members.id JOIN officership_permissions ON officership_permissions.officership_name = member_officerships.officership_name);
member_id | permission_name
-----------+-----------------
1 | pbar
1 | pbaz
5 | pbar
5 | pbaz
5 | pfoo
(5 rows)
|