[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[3410] trunk/sql
- Subject: SF.net SVN: ledger-smb:[3410] trunk/sql
- From: ..hidden..
- Date: Sat, 02 Jul 2011 03:18:22 +0000
Revision: 3410
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3410&view=rev
Author: einhverfr
Date: 2011-07-02 03:18:22 +0000 (Sat, 02 Jul 2011)
Log Message:
-----------
Correcting menu functions parent directory acl detection
Modified Paths:
--------------
trunk/sql/Pg-database.sql
Added Paths:
-----------
trunk/sql/upgrade/3410-menu-functions.sql
Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql 2011-07-01 20:45:41 UTC (rev 3409)
+++ trunk/sql/Pg-database.sql 2011-07-02 03:18:22 UTC (rev 3410)
@@ -3190,7 +3190,7 @@
ilike 'ALLOW'
THEN TRUE
END))
- and cc.path like c.path || '%')
+ and cc.path like c.path || ',%')
GROUP BY n.position, n.id, c.level, n.label, c.path, c.list_order
ORDER BY c.list_order
@@ -3255,7 +3255,7 @@
ilike 'ALLOW'
THEN TRUE
END))
- and cc.path like c.path || '%')
+ and cc.path like c.path || ',%')
GROUP BY n.position, n.id, c.level, n.label, c.path, c.list_order
ORDER BY c.list_order
LOOP
Added: trunk/sql/upgrade/3410-menu-functions.sql
===================================================================
--- trunk/sql/upgrade/3410-menu-functions.sql (rev 0)
+++ trunk/sql/upgrade/3410-menu-functions.sql 2011-07-02 03:18:22 UTC (rev 3410)
@@ -0,0 +1,117 @@
+CREATE OR REPLACE FUNCTION menu_generate() RETURNS SETOF menu_item AS
+$$
+DECLARE
+ item menu_item;
+ arg menu_attribute%ROWTYPE;
+BEGIN
+ FOR item IN
+ SELECT n.position, n.id, c.level, n.label, c.path,
+ to_args(array[ma.attribute, ma.value])
+ FROM connectby('menu_node', 'id', 'parent', 'position', '0',
+ 0, ',')
+ c(id integer, parent integer, "level" integer,
+ path text, list_order integer)
+ 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
+ WHERE pg_has_role(CASE WHEN role_name
+ ilike 'public'
+ THEN current_user
+ ELSE role_name
+ END, 'USAGE')
+ 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 connectby('menu_node', 'id', 'parent',
+ 'position', '0', 0, ',')
+ cc(id integer, parent integer,
+ "level" integer, path text,
+ list_order integer)
+ JOIN menu_node cn USING(id)
+ WHERE cn.id IN
+ (select node_id FROM menu_acl
+ WHERE pg_has_role(CASE WHEN role_name
+ ilike 'public'
+ THEN current_user
+ ELSE role_name
+ END, 'USAGE')
+ 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 like c.path || ',%')
+ GROUP BY n.position, n.id, c.level, n.label, c.path, c.list_order
+ ORDER BY c.list_order
+
+ LOOP
+ RETURN NEXT item;
+ END LOOP;
+END;
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION menu_children(in_parent_id int) RETURNS SETOF menu_item
+AS $$
+declare
+ item menu_item;
+ arg menu_attribute%ROWTYPE;
+begin
+ FOR item IN
+ SELECT n.position, n.id, c.level, n.label, c.path,
+ to_args(array[ma.attribute, ma.value])
+ FROM connectby('menu_node', 'id', 'parent', 'position',
+ in_parent_id, 1, ',')
+ c(id integer, parent integer, "level" integer,
+ path text, list_order integer)
+ 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
+ WHERE pg_has_role(CASE WHEN role_name
+ ilike 'public'
+ THEN current_user
+ ELSE role_name
+ END, 'USAGE')
+ 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 connectby('menu_node', 'id', 'parent',
+ 'position', '0', 0, ',')
+ cc(id integer, parent integer,
+ "level" integer, path text,
+ list_order integer)
+ JOIN menu_node cn USING(id)
+ WHERE cn.id IN
+ (select node_id FROM menu_acl
+ WHERE pg_has_role(CASE WHEN role_name
+ ilike 'public'
+ THEN current_user
+ ELSE role_name
+ END, 'USAGE')
+ 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 like c.path || ',%')
+ GROUP BY n.position, n.id, c.level, n.label, c.path, c.list_order
+ ORDER BY c.list_order
+ LOOP
+ return next item;
+ end loop;
+end;
+$$ language plpgsql;
+
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.