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
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
|
set client_min_messages = 'warning';
BEGIN;
DROP TYPE IF EXISTS menu_item CASCADE;
CREATE TYPE menu_item AS (
position int,
id int,
level int,
label varchar,
path varchar,
parent int,
args text[]
);
CREATE OR REPLACE FUNCTION menu_generate() RETURNS SETOF menu_item AS
$$
WITH RECURSIVE tree (path, id, parent, level, positions)
AS (select id::text as path, id, parent,
0 as level, position::text
FROM menu_node where parent is null
UNION
select path || ',' || n.id::text, n.id,
n.parent,
t.level + 1,
t.positions || ',' || n.position
FROM menu_node n
JOIN tree t ON t.id = n.parent)
SELECT n.position, n.id, c.level, n.label, c.path, n.parent,
to_args(array[ma.attribute, ma.value])
FROM tree c
JOIN menu_node n USING(id)
JOIN menu_attribute ma ON (n.id = ma.node_id)
WHERE n.id IN (select node_id
FROM menu_acl acl
LEFT JOIN pg_roles pr on pr.rolname = acl.role_name
WHERE CASE WHEN role_name
ilike 'public'
THEN true
WHEN rolname IS NULL
THEN FALSE
ELSE pg_has_role(rolname,
'USAGE')
END
GROUP BY node_id
HAVING bool_and(CASE WHEN acl_type ilike 'DENY'
THEN FALSE
WHEN acl_type ilike 'ALLOW'
THEN TRUE
END))
or exists (select cn.id, cc.path
FROM tree cc
JOIN menu_node cn USING(id)
WHERE cn.id IN
(select node_id
FROM menu_acl acl
LEFT JOIN pg_roles pr
on pr.rolname = acl.role_name
WHERE CASE WHEN rolname
ilike 'public'
THEN true
WHEN rolname IS NULL
THEN FALSE
ELSE pg_has_role(rolname,
'USAGE')
END
GROUP BY node_id
HAVING bool_and(CASE WHEN acl_type
ilike 'DENY'
THEN false
WHEN acl_type
ilike 'ALLOW'
THEN TRUE
END))
and cc.path::text
like c.path::text || ',%')
GROUP BY n.position, n.id, c.level, n.label, c.path, c.positions,
n.parent
ORDER BY string_to_array(c.positions, ',')::int[]
$$ language sql;
COMMENT ON FUNCTION menu_generate() IS
$$
This function returns the complete menu tree. It is used to generate nested
menus for the web interface.
$$;
CREATE OR REPLACE FUNCTION menu_children(in_parent_id int) RETURNS SETOF menu_item
AS $$
SELECT * FROM menu_generate() where parent = $1;
$$ language sql;
COMMENT ON FUNCTION menu_children(int) IS
$$ This function returns all menu items which are children of in_parent_id
(the only input parameter).
It is thus similar to menu_generate() but it only returns the menu items
associated with nodes directly descendant from the parent. It is used for
menues for frameless browsers.$$;
CREATE OR REPLACE FUNCTION
menu_insert(in_parent_id int, in_position int, in_label text)
returns int
AS $$
DECLARE
new_id int;
BEGIN
UPDATE menu_node
SET position = position * -1
WHERE parent = in_parent_id
AND position >= in_position;
INSERT INTO menu_node (parent, position, label)
VALUES (in_parent_id, in_position, in_label);
SELECT INTO new_id currval('menu_node_id_seq');
UPDATE menu_node
SET position = (position * -1) + 1
WHERE parent = in_parent_id
AND position < 0;
RETURN new_id;
END;
$$ language plpgsql;
comment on function menu_insert(int, int, text) is $$
This function inserts menu items at arbitrary positions. The arguments are, in
order: parent, position, label. The return value is the id number of the menu
item created. $$;
DROP VIEW IF EXISTS menu_friendly;
CREATE VIEW menu_friendly AS
WITH RECURSIVE tree (path, id, parent, level, positions)
AS (select id::text as path, id, parent,
0 as level, position::text
FROM menu_node where parent is null
UNION
select path || ',' || n.id::text, n.id,
n.parent,
t.level + 1,
t.positions || ',' || n.position
FROM menu_node n
JOIN tree t ON t.id = n.parent)
SELECT t."level", t.path,
(repeat(' '::text, (2 * t."level")) || (n.label)::text) AS label,
n.id, n."position"
FROM tree t
JOIN menu_node n USING (id)
ORDER BY string_to_array(t.positions, ',')::int[];
COMMENT ON VIEW menu_friendly IS
$$ A nice human-readable view for investigating the menu tree. Does not
show menu attributes or acls.$$;
update defaults set value = 'yes' where setting_key = 'module_load_ok';
COMMIT;
|
