SF.net SVN: ledger-smb:[5718] trunk/sql/modules

[..hidden..]

Revision: 5718
          http://sourceforge.net/p/ledger-smb/code/5718
Author:   ehuelsmann
Date:     2013-04-28 10:22:37 +0000 (Sun, 28 Apr 2013)
Log Message:
-----------
Introduce functions to calculate database local role names
and use them where these calculations are now implemented
in line (without taking the role_prefix setting into account).

Modified Paths:
--------------
    trunk/sql/modules/Entity.sql
    trunk/sql/modules/Journal.sql
    trunk/sql/modules/Roles.sql
    trunk/sql/modules/Util.sql
    trunk/sql/modules/admin.sql

Modified: trunk/sql/modules/Entity.sql
===================================================================
--- trunk/sql/modules/Entity.sql	2013-04-27 15:34:56 UTC (rev 5717)
+++ trunk/sql/modules/Entity.sql	2013-04-28 10:22:37 UTC (rev 5718)
@@ -59,7 +59,7 @@
              LEFT JOIN defaults ON setting_key = 'roll_prefix'
 		WHERE active and pg_has_role(SESSION_USER, 
                                      coalesce(defaults.value, 
-                                     'lsmb_' || current_database() || '__') ||
+                                     lsmb__role_prefix() ||
                                      'contact_class_' ||
                                      lower(regexp_replace(class, ' ', '_')), 
                                      'USAGE')

Modified: trunk/sql/modules/Journal.sql
===================================================================
--- trunk/sql/modules/Journal.sql	2013-04-27 15:34:56 UTC (rev 5717)
+++ trunk/sql/modules/Journal.sql	2013-04-28 10:22:37 UTC (rev 5718)
@@ -103,7 +103,7 @@
 BEGIN
 
 -- error handling and checks before we begin
-IF (pg_has_role('lsmb_'||current_database()||'__draft_modify') IS NOT TRUE THEN
+IF (pg_has_role(lsmb_role('draft_modify')) IS NOT TRUE THEN
     RAISE EXCEPTION 'Access denied';
 END IF;
 

Modified: trunk/sql/modules/Roles.sql
===================================================================
--- trunk/sql/modules/Roles.sql	2013-04-27 15:34:56 UTC (rev 5717)
+++ trunk/sql/modules/Roles.sql	2013-04-28 10:22:37 UTC (rev 5718)
@@ -1626,19 +1626,16 @@
 $$
 DECLARE
    r_eclass entity_class;
-   roll_pfx text;
 BEGIN
 IF TG_OP = 'DELETE' THEN
    RETURN OLD;
 ELSE 
-   SELECT value INTO roll_pfx FROM defaults WHERE setting_key = 'roll_prefix';
    SELECT * INTO r_eclass from entity_class WHERE id = NEW.entity_class;
-   IF pg_has_role(SESSION_USER, coalesce(roll_pfx, 
-                                         'lsmb_' || current_database() || '__')
-                                || 'contact_class_' || lower(regexp_replace(
-                                                        r_eclass.class, 
-                                                        ' ', 
-                                                        '_')), 'USAGE')
+   IF pg_has_role(SESSION_USER,
+                  lsmb__role('contact_class_'
+                             || lower(regexp_replace(r_eclass.class,
+                                                     ' ', '_'))),
+                  'USAGE')
    THEN
       RETURN NEW;
    ELSE

Modified: trunk/sql/modules/Util.sql
===================================================================
--- trunk/sql/modules/Util.sql	2013-04-27 15:34:56 UTC (rev 5717)
+++ trunk/sql/modules/Util.sql	2013-04-28 10:22:37 UTC (rev 5718)
@@ -121,4 +121,13 @@
 LANGUAGE SQL AS
 $$ SELECT max(transdate) FROM acc_trans; $$;
 
+CREATE OR REPLACE FUNCTION lsmb__role_prefix() RETURNS text
+LANGUAGE SQL AS
+$$ select coalesce((setting_get('role_prefix')).value,
+                   'lsmb_' || current_database() || '__'); $$;
+
+CREATE OR REPLACE FUNCTION lsmb__role(global_role text) RETURNS text
+LANGUAGE SQL AS
+$$ select lsmb__role_prefix() || $1; $$;
+
 COMMIT;

Modified: trunk/sql/modules/admin.sql
===================================================================
--- trunk/sql/modules/admin.sql	2013-04-27 15:34:56 UTC (rev 5717)
+++ trunk/sql/modules/admin.sql	2013-04-28 10:22:37 UTC (rev 5718)
@@ -455,15 +455,14 @@
 CREATE OR REPLACE FUNCTION admin__create_group(in_group_name TEXT) RETURNS int as $$
     
     DECLARE
-        
         stmt text;
-        t_dbname text;
+        group_name text;
     BEGIN
-	t_dbname := current_database();
-        stmt := 'create role '|| quote_ident('lsmb_' || t_dbname || '__' || in_group_name);
+        group_name := lsmb__role(in_group_name);
+        stmt := 'create role '|| quote_ident(group_name);
         execute stmt;
         INSERT INTO lsmb_group (role_name) 
-             values (quote_literal('lsmb_' || t_dbname || '__' || in_group_name));
+             values (group_name));
         return 1;
     END;
     
@@ -569,23 +568,21 @@
 If in_drop_role is set, it drops the role too.
 $$;
 
--- Work oin progress, not for ducmenting yet.
+-- Work in progress, not for ducmenting yet.
 CREATE OR REPLACE FUNCTION admin__delete_group (in_group_name TEXT) returns bool as $$
     
     DECLARE
         stmt text;
         a_role role_view;
-        t_dbname text;
+        group_name text;
     BEGIN
-        t_dbname := current_database();
-        
+        select * into a_role from role_view where rolname = in_group_name;
 
-        select * into a_role from role_view where rolname = in_group_name;
-        
         if not found then
             return 'f'::bool;
         else
-            stmt := 'drop role lsmb_' || quote_ident(t_dbname || '__' || in_group_name);
+            group_name := lsmb__role(in_group_name);
+            stmt := 'drop role lsmb_' || quote_ident(group_name);
             execute stmt;
             return 't'::bool;
         end if;
@@ -638,17 +635,15 @@
 create or replace function admin__get_roles () returns setof pg_roles as $$
 DECLARE
     v_rol record;
-    t_dbname text;
 BEGIN
-    t_dbname := current_database();
-    FOR v_rol in 
+    FOR v_rol in
         SELECT *
-        from 
+        FROM
             pg_roles
-        where 
-            rolname ~ ('^lsmb_' || t_dbname || '__') 
-            and rolcanlogin is false
-        order by rolname ASC
+        WHERE
+            rolname ~ ('^' || lsmb__role_prefix())
+            AND NOT rolcanlogin
+        ORDER BY rolname ASC
     LOOP
         RETURN NEXT v_rol;
     END LOOP;

This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.