[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[3263] trunk/sql
- Subject: SF.net SVN: ledger-smb:[3263] trunk/sql
- From: ..hidden..
- Date: Thu, 16 Jun 2011 12:40:47 +0000
Revision: 3263
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3263&view=rev
Author: einhverfr
Date: 2011-06-16 12:40:46 +0000 (Thu, 16 Jun 2011)
Log Message:
-----------
Menus and ACLS now work
Modified Paths:
--------------
trunk/sql/Pg-database.sql
trunk/sql/modules/Roles.sql
Added Paths:
-----------
trunk/sql/upgrade/3263-menu_generate.sql
Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql 2011-06-16 09:37:45 UTC (rev 3262)
+++ trunk/sql/Pg-database.sql 2011-06-16 12:40:46 UTC (rev 3263)
@@ -1533,7 +1533,6 @@
--
create index ar_id_key on ar (id);
create index ar_transdate_key on ar (transdate);
-create index ar_invnumber_key on ar (invnumber);
create index ar_ordnumber_key on ar (ordnumber);
create index ar_quonumber_key on ar (quonumber);
create index ar_curr_idz on ar(curr);
@@ -2713,6 +2712,7 @@
acl_type character varying,
node_id integer,
CONSTRAINT menu_acl_acl_type_check CHECK ((((acl_type)::text = 'allow'::text) OR ((acl_type)::text = 'deny'::text)))
+ PRIMARY KEY (node_id, role_name)
);
@@ -2729,6 +2729,21 @@
-- PostgreSQL database dump complete
--
+CREATE OR REPLACE FUNCTION to_args (in_base text[], in_args text[])
+RETURNS text[] AS
+$$
+SELECT CASE WHEN $2[1] IS NULL OR $2[2] IS NULL THEN $1
+ ELSE $1 || ($2[1]::text || '=' || $2[2]::text)
+ END;
+$$ language sql;
+
+CREATE AGGREGATE to_args (
+ basetype = text[],
+ sfunc = to_args,
+ stype = text[],
+ INITCOND = '{}'
+);
+
CREATE TYPE menu_item AS (
position int,
id int,
@@ -2738,6 +2753,7 @@
args varchar[]
);
+
CREATE OR REPLACE FUNCTION menu_generate() RETURNS SETOF menu_item AS
$$
DECLARE
@@ -2745,21 +2761,53 @@
arg menu_attribute%ROWTYPE;
BEGIN
FOR item IN
- SELECT n.position, n.id, c.level, n.label, c.path, '{}'
+ 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
- FOR arg IN
- SELECT *
- FROM menu_attribute
- WHERE node_id = item.id
- LOOP
- item.args := item.args ||
- (arg.attribute || '=' || arg.value)::varchar;
- END LOOP;
RETURN NEXT item;
END LOOP;
END;
@@ -2772,21 +2820,52 @@
arg menu_attribute%ROWTYPE;
begin
FOR item IN
- SELECT n.position, n.id, c.level, n.label, c.path, '{}'
+ 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
- FOR arg IN
- SELECT *
- FROM menu_attribute
- WHERE node_id = item.id
- LOOP
- item.args := item.args ||
- (arg.attribute || '=' || arg.value)::varchar;
- END LOOP;
return next item;
end loop;
end;
Modified: trunk/sql/modules/Roles.sql
===================================================================
--- trunk/sql/modules/Roles.sql 2011-06-16 09:37:45 UTC (rev 3262)
+++ trunk/sql/modules/Roles.sql 2011-06-16 12:40:46 UTC (rev 3263)
@@ -44,6 +44,8 @@
values (30, 'allow', 'lsmb_<?lsmb dbname ?>__contact_read');
INSERT INTO menu_acl (node_id, acl_type, role_name)
values (33, 'allow', 'lsmb_<?lsmb dbname ?>__contact_read');
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (49, 'allow', 'lsmb_<?lsmb dbname ?>__contact_read');
CREATE ROLE "lsmb_<?lsmb dbname ?>__contact_create"
@@ -98,6 +100,8 @@
values (30, 'allow', 'lsmb_<?lsmb dbname ?>__contact_create');
INSERT INTO menu_acl (node_id, acl_type, role_name)
values (31, 'allow', 'lsmb_<?lsmb dbname ?>__contact_create');
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (48, 'lsmb_<?lsmb dbname ?>__contact_create');
CREATE ROLE "lsmb_<?lsmb dbname ?>__contact_edit"
@@ -140,7 +144,7 @@
GRANT INSERT ON voucher TO "lsmb_<?lsmb dbname ?>__batch_create";
GRANT ALL ON voucher_id_seq TO "lsmb_<?lsmb dbname ?>__contact_create";
--- TODO add Menu ACLs
+-- No menu acls
CREATE ROLE "lsmb_<?lsmb dbname ?>__batch_post"
WITH INHERIT NOLOGIN;
@@ -151,7 +155,10 @@
GRANT UPDATE ON batch TO "lsmb_<?lsmb dbname ?>__batch_post";
GRANT UPDATE ON gl TO "lsmb_<?lsmb dbname ?>__batch_post";
--- TODO add Menu ACLs
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (206, 'allow', 'lsmb_<?lsmb dbname ?>__contact_create');
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (210, 'allow', 'lsmb_<?lsmb dbname ?>__contact_create');
-- AR
CREATE ROLE "lsmb_<?lsmb dbname ?>__ar_transaction_create"
@@ -182,7 +189,8 @@
GRANT INSERT ON acc_trans TO "lsmb_<?lsmb dbname ?>__ar_transaction_create_voucher";
GRANT ALL ON acc_trans_entry_id_seq TO "lsmb_<?lsmb dbname ?>__ar_transaction_create_voucher";
--- TODO add Menu ACLs
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (4, 'allow', 'lsmb_<?lsmb dbname ?>__ar_transaction_create');
CREATE ROLE "lsmb_<?lsmb dbname ?>__ar_invoice_create"
WITH INHERIT NOLOGIN
@@ -536,7 +544,7 @@
INSERT INTO menu_acl (node_id, acl_type, role_name)
values (16, 'allow', 'lsmb_<?lsmb dbname ?>__close_till');
INSERT INTO menu_acl (node_id, acl_type, role_name)
-values (19, 'allow', 'lsmb_<?lsmb dbname ?>__close_till');
+values (20, 'allow', 'lsmb_<?lsmb dbname ?>__close_till');
CREATE ROLE "lsmb_<?lsmb dbname ?>__list_all_open"
@@ -575,9 +583,9 @@
GRANT ALL ON cr_report_id_seq TO "lsmb_<?lsmb dbname ?>__reconciliation_enter";
INSERT INTO menu_acl (node_id, acl_type, role_name)
-values (35, 'allow', 'lsmb_<?lsmb dbname ?>_reconciliation_enter');
+values (35, 'allow', 'lsmb_<?lsmb dbname ?>__reconciliation_enter');
INSERT INTO menu_acl (node_id, acl_type, role_name)
-values (45, 'allow', 'lsmb_<?lsmb dbname ?>_reconciliation_enter');
+values (45, 'allow', 'lsmb_<?lsmb dbname ?>__reconciliation_enter');
CREATE ROLE "lsmb_<?lsmb dbname ?>__reconciliation_approve"
@@ -588,13 +596,16 @@
"lsmb_<?lsmb dbname ?>__reconciliation_approve";
INSERT INTO menu_acl (node_id, acl_type, role_name)
-values (35, 'allow', 'lsmb_<?lsmb dbname ?>_reconciliation_approve');
+values (35, 'allow', 'lsmb_<?lsmb dbname ?>__reconciliation_approve');
INSERT INTO menu_acl (node_id, acl_type, role_name)
-values (41, 'allow', 'lsmb_<?lsmb dbname ?>_reconciliation_approve');
+values (41, 'allow', 'lsmb_<?lsmb dbname ?>__reconciliation_approve');
INSERT INTO menu_acl (node_id, acl_type, role_name)
-values (44, 'allow', 'lsmb_<?lsmb dbname ?>_reconciliation_approve');
+values (44, 'allow', 'lsmb_<?lsmb dbname ?>__reconciliation_approve');
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (211, 'allow', 'lsmb_<?lsmb dbname ?>__reconciliation_approve');
+
CREATE ROLE "lsmb_<?lsmb dbname ?>__reconciliation_all"
WITH INHERIT NOLOGIN
IN ROLE "lsmb_<?lsmb dbname ?>__reconciliation_enter",
@@ -618,6 +629,14 @@
values (35, 'allow', 'lsmb_<?lsmb dbname ?>__payment_process');
INSERT INTO menu_acl (node_id, acl_type, role_name)
values (38, 'allow', 'lsmb_<?lsmb dbname ?>__payment_process');
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (43, 'allow', 'lsmb_<?lsmb dbname ?>__payment_process');
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (201, 'allow', 'lsmb_<?lsmb dbname ?>__payment_process');
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (202, 'allow', 'lsmb_<?lsmb dbname ?>__payment_process');
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (223, 'allow', 'lsmb_<?lsmb dbname ?>__payment_process');
CREATE ROLE "lsmb_<?lsmb dbname ?>__receipt_process"
@@ -636,7 +655,15 @@
INSERT INTO menu_acl (node_id, acl_type, role_name)
values (36, 'allow', 'lsmb_<?lsmb dbname ?>__receipt_process');
INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (37, 'allow', 'lsmb_<?lsmb dbname ?>__receipt_process');
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (42, 'allow', 'lsmb_<?lsmb dbname ?>__receipt_process');
+INSERT INTO menu_acl (node_id, acl_type, role_name)
values (47, 'allow', 'lsmb_<?lsmb dbname ?>__receipt_process');
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (203, 'allow', 'lsmb_<?lsmb dbname ?>__receipt_process');
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (204, 'allow', 'lsmb_<?lsmb dbname ?>__receipt_process');
CREATE ROLE "lsmb_<?lsmb dbname ?>__cash_all"
@@ -871,6 +898,10 @@
values (73, 'allow', 'lsmb_<?lsmb dbname ?>__gl_reports');
INSERT INTO menu_acl (node_id, acl_type, role_name)
values (76, 'allow', 'lsmb_<?lsmb dbname ?>__gl_reports');
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (105, 'allow', 'lsmb_<?lsmb dbname ?>__gl_reports');
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (114, 'allow', 'lsmb_<?lsmb dbname ?>__gl_reports');
CREATE ROLE "lsmb_<?lsmb dbname ?>__yearend_run"
@@ -893,9 +924,6 @@
GRANT SELECT ON batch_class TO "lsmb_<?lsmb dbname ?>__batch_list";
GRANT SELECT ON voucher TO "lsmb_<?lsmb dbname ?>__batch_list";
--- TODO: Add menu items
-
-
CREATE ROLE "lsmb_<?lsmb dbname ?>__gl_all"
WITH INHERIT NOLOGIN
IN ROLE "lsmb_<?lsmb dbname ?>__gl_transaction_create",
@@ -1045,11 +1073,18 @@
values (112, 'allow', 'lsmb_<?lsmb dbname ?>__financial_reports');
INSERT INTO menu_acl (node_id, acl_type, role_name)
values (113, 'allow', 'lsmb_<?lsmb dbname ?>__financial_reports');
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (114, 'allow', 'lsmb_<?lsmb dbname ?>__financial_reports');
-- RECURRING TRANSACTIONS
--- TO ADD WHEN THIS IS REDESIGNED
+CREATE ROLE "lsmb_<?lsmb dbname ?>__recurring"
+WITH INHERIT NOLOGIN;
+
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (115, 'allow', 'lsmb_<?lsmb dbname ?>__print_jobs_list');
+
-- BATCH PRINTING
CREATE ROLE "lsmb_<?lsmb dbname ?>__print_jobs_list"
WITH INHERIT NOLOGIN;
@@ -1085,6 +1120,19 @@
IN ROLE "lsmb_<?lsmb dbname ?>__print_jobs_list";
+--Tax Forms
+
+CREATE ROLE "lsmb_<?lsmb dbname ?>__tax_form_save"
+WITH INHERIT NOLOGIN;
+
+GRANT INSERT, UPDATE ON country_tax_form
+TO "lsmb_<?lsmb dbname ?>__tax_form_save";
+
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (218, 'allow', 'lsmb_<?lsmb dbname ?>__tax_form_save');
+
+--
+
-- SYSTEM SETTINGS
CREATE ROLE "lsmb_<?lsmb dbname ?>__system_settings_list"
WITH INHERIT NOLOGIN;
@@ -1369,6 +1417,8 @@
values (188, 'allow', 'lsmb_<?lsmb dbname ?>__template_edit');
INSERT INTO menu_acl (node_id, acl_type, role_name)
values (189, 'allow', 'lsmb_<?lsmb dbname ?>__template_edit');
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (190, 'allow', 'lsmb_<?lsmb dbname ?>__template_edit');
CREATE ROLE "lsmb_<?lsmb dbname ?>__users_manage"
WITH INHERIT NOLOGIN
@@ -1397,7 +1447,24 @@
GRANT EXECUTE ON FUNCTION admin__delete_group(text)
TO "lsmb_<?lsmb dbname ?>__users_manage";
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (220, 'allow', 'lsmb_<?lsmb dbname ?>__users_manage');
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (221, 'allow', 'lsmb_<?lsmb dbname ?>__users_manage');
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (222, 'allow', 'lsmb_<?lsmb dbname ?>__users_manage');
+CREATE ROLE "lsmb_<?lsmb dbname ?>__backup"
+WITH INHERIT NOLOGIN;
+
+-- TODO GRANT SELECT ON ALL TABLES
+
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (134, 'allow', 'lsmb_<?lsmb dbname ?>__backup');
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (135, 'allow', 'lsmb_<?lsmb dbname ?>__backup');
+
+
CREATE ROLE "lsmb_<?lsmb dbname ?>__system_admin"
WITH INHERIT NOLOGIN
IN ROLE "lsmb_<?lsmb dbname ?>__system_settings_change",
@@ -1407,6 +1474,7 @@
"lsmb_<?lsmb dbname ?>__sic_all",
"lsmb_<?lsmb dbname ?>__template_edit",
"lsmb_<?lsmb dbname ?>__users_manage",
+"lsmb_<?lsmb dbname ?>__backup",
"lsmb_<?lsmb dbname ?>__tax_form_save";
-- Manual Translation
@@ -1529,3 +1597,9 @@
TO "lsmb_<?lsmb dbname ?>__voucher_delete";
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (191, 'allow', 'public');
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (192, 'allow', 'public');
+INSERT INTO menu_acl (node_id, acl_type, role_name)
+values (193, 'allow', 'public');
Added: trunk/sql/upgrade/3263-menu_generate.sql
===================================================================
--- trunk/sql/upgrade/3263-menu_generate.sql (rev 0)
+++ trunk/sql/upgrade/3263-menu_generate.sql 2011-06-16 12:40:46 UTC (rev 3263)
@@ -0,0 +1,118 @@
+
+
+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.