Creation of next-gen admin.pl, admin.pm, and admin.sql systems.

Admin.pm defines the new controller
admin.pl is the direct view-backing software.

POD is not completed, will be provided shortly.

Full UI templates provided for the Reconciliation, Vendor, Customer, and Employee as well. 
UI/  is being delineated by subdirectories for Admin, Vendor, Customer and Employee, to date.

Minor whitespace cleanup.

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1557 4979c152-3d1c-0410-bac9-87ea11338e46

1 files changed, 403 insertions, 0 deletions

diff --git a/sql/modules/admin.sql b/sql/modules/admin.sql
new file mode 100644
index 00000000..103c10c1
--- /dev/null
+++ b/sql/modules/admin.sql
@@ -0,0 +1,403 @@
+CREATE OR REPLACE FUNCTION admin_add_user_to_role(in_user TEXT, in_role TEXT) returns INT AS $$
+    
+    declare
+        stmt TEXT;
+        a_role name;
+        a_user name;
+    BEGIN
+    
+        -- Issue the grant
+        select rolname into a_role from pg_roles where rolname = in_role;
+        
+        IF NOT FOUND THEN
+            RAISE EXCEPTION 'Cannot grant permissions of a non-existant role.';
+        END IF;
+        
+        select rolname into a_user from pg_roles where rolname = in_user;
+        
+        IF NOT FOUND THEN
+            RAISE EXCEPTION 'Cannot grant permissions to a non-existant user.';
+        END IF;
+        
+        stmt := 'GRANT '|| in_role ||' to '|| in_user;
+        
+        EXECUTE stmt;
+        
+        return 1;
+    END;
+    
+$$ language 'plpgsql';
+
+CREATE OR REPLACE FUNCTION admin_remove_user_from_role(in_user TEXT, in_role TEXT) returns INT AS $$
+    
+    declare
+        stmt TEXT;
+        a_role name;
+        a_user name;
+    BEGIN
+    
+        -- Issue the grant
+        select rolname into a_role from pg_roles where rolname = in_role;
+        
+        IF NOT FOUND THEN
+            RAISE EXCEPTION 'Cannot revoke permissions of a non-existant role.';
+        END IF;
+        
+        select rolname into a_user from pg_roles where rolname = in_user;
+        
+        IF NOT FOUND THEN
+            RAISE EXCEPTION 'Cannot revoke permissions from a non-existant user.';
+        END IF;
+        
+        stmt := 'REVOKE '|| in_role ||' FROM '|| in_user;
+        
+        EXECUTE stmt;
+        
+        return 1;    
+    END;
+    
+$$ language 'plpgsql';
+
+CREATE OR REPLACE FUNCTION admin_add_function_to_group(in_func TEXT, in_role TEXT) returns INT AS $$
+    
+    declare
+        stmt TEXT;
+        a_role name;
+        a_user name;
+    BEGIN
+    
+        -- Issue the grant
+        select rolname into a_role from pg_roles where rolname = in_role;
+        
+        IF NOT FOUND THEN
+            RAISE EXCEPTION 'Cannot grant permissions of a non-existant role.';
+        END IF;
+        
+        select rolname into a_user from pg_roles where rolname = in_user;
+        
+        IF NOT FOUND THEN
+            RAISE EXCEPTION 'Cannot grant permissions to a non-existant user.';
+        END IF;
+        
+        stmt := 'GRANT EXECUTE ON FUNCTION '|| in_func ||' to '|| in_role;
+        
+        EXECUTE stmt;
+        
+        return 1;
+    END;
+    
+$$ language 'plpgsql';
+
+CREATE OR REPLACE FUNCTION admin_remove_function_from_group(in_func TEXT, in_role TEXT) returns INT AS $$
+    
+    declare
+        stmt TEXT;
+        a_role name;
+        a_user name;
+    BEGIN
+    
+        -- Issue the grant
+        select rolname into a_role from pg_roles where rolname = in_role;
+        
+        IF NOT FOUND THEN
+            RAISE EXCEPTION 'Cannot revoke permissions of a non-existant role.';
+        END IF;
+        
+        select rolname into a_user from pg_roles where rolname = in_user;
+        
+        IF NOT FOUND THEN
+            RAISE EXCEPTION 'Cannot revoke permissions from a non-existant function.';
+        END IF;
+        
+        stmt := 'REVOKE EXECUTE ON FUNCTION '|| in_func ||' FROM '|| in_role;
+        
+        EXECUTE stmt;
+        
+        return 1;    
+    END;
+    
+    
+$$ language 'plpgsql';
+
+CREATE OR REPLACE FUNCTION admin_add_table_to_group(in_table TEXT, in_role TEXT, in_perm TEXT) returns INT AS $$
+    
+    declare
+        stmt TEXT;
+        a_role name;
+        a_user name;
+    BEGIN
+    
+        -- Issue the grant
+        select rolname into a_role from pg_roles where rolname = in_role;
+        
+        IF NOT FOUND THEN
+            RAISE EXCEPTION 'Cannot grant permissions of a non-existant role.';
+        END IF;
+        
+        select table_name into a_table from information_schema.tables 
+        where table_schema NOT IN ('information_schema','pg_catalog','pg_toast') 
+        and table_type='BASE TABLE' 
+        and table_name = in_table;
+        
+        IF NOT FOUND THEN
+            RAISE EXCEPTION 'Cannot grant permissions to a non-existant table.';
+        END IF;
+        
+        if lower(in_perm) not in ('select','insert','update','delete') THEN
+            raise exception 'Cannot add unknown permission';
+        END IF;
+        
+        stmt := 'GRANT '|| in_perm|| 'ON TABLE '|| in_table ||' to '|| in_role;
+        
+        EXECUTE stmt;
+        
+        return 1;
+    END;
+    
+$$ language 'plpgsql';
+
+CREATE OR REPLACE FUNCTION admin_remove_table_from_group(in_table TEXT, in_role TEXT) returns INT AS $$
+    
+    declare
+        stmt TEXT;
+        a_role name;
+        a_table text;
+    BEGIN
+    
+        -- Issue the grant
+        select rolname into a_role from pg_roles where rolname = in_role;
+        
+        IF NOT FOUND THEN
+            RAISE EXCEPTION 'Cannot revoke permissions of a non-existant role.';
+        END IF;
+        
+        SELECT table_schema, table_name from 
+        
+        select table_name into a_table from information_schema.tables 
+        where table_schema NOT IN ('information_schema','pg_catalog','pg_toast') 
+        and table_type='BASE TABLE' 
+        and table_name = in_table;
+        
+        IF NOT FOUND THEN
+            RAISE EXCEPTION 'Cannot revoke permissions from a non-existant table.';
+        END IF;
+        
+        stmt := 'REVOKE '|| in_role ||' FROM '|| in_user;
+        
+        EXECUTE stmt;
+        
+        return 1;    
+    END;
+        
+$$ language 'plpgsql';
+
+create or replace function admin_get_user(in_user TEXT) returns setof user as $$
+    
+    DECLARE
+        a_user user;
+    BEGIN
+        
+        select * into a_user from user where username = in_user;
+        IF NOT FOUND THEN
+            RAISE EXCEPTION 'cannot find user %', in_user;
+        END IF;
+        
+        return a_user;
+    
+    END;    
+$$ language plpgsql;
+
+create or replace function admin_get_roles_for_user(in_user TEXT) returns setof lsmb_roles as $$
+    
+    declare
+        u_role lsmb_roles;
+        a_user user;
+    begin
+        select * into a_user from admin_get_user(in_user);
+        
+        FOR u_role IN select * from lsmb_roles WHERE user = a_user.id LOOP
+        
+            RETURN NEXT a_role;
+        
+        END LOOP;
+        RETURN;
+    end;
+    
+$$ language 'plpgsql';
+
+CREATE OR REPLACE FUNCTION admin_save_user(
+    in_id int, 
+    in_username text, 
+    in_password TEXT, 
+    in_dbname TEXT, 
+    in_host TEXT, 
+    in_port TEXT
+) returns int AS $$
+    DECLARE
+    
+        a_user user;
+        v_entity_id int;
+        p_id int;
+        l_id int;
+        stmt text;
+    BEGIN
+    
+        select * into a_user from user where id = in_id;
+        
+        IF NOT FOUND THEN 
+            -- Insert cycle
+            
+            --- First, create an entity.
+            
+            if admin_is_user(in_username) then
+                
+                -- uhm, this is bad.
+                RAISE EXCEPTION 
+                    "Fatal exception: Username already exists in Postgres; not
+                    a valid lsmb user.";
+            end if;
+            
+            v_entity_id := nextval('entity_id_seq');
+                
+            INSERT INTO entity (id, name, entity_class) VALUES (
+                v_entity_id,
+                in_first_name || ' ' || in_last_name,
+                3
+            );
+            
+            -- create an actual user
+            insert into users (name, entity_id) VALUES (
+                in_username,
+                v_entity_id
+            );
+            
+            insert into user_connection (entity_id, database, host, port) 
+                VALUES (
+                    v_entity_id,
+                    in_database,
+                    in_host,
+                    in_port                    
+                );
+            
+            -- Finally, issue the create user statement
+            
+            stmt := $$CREATE USER $$||in_username||$$WITH ENCRYPTED PASSWORD '$$||in_password||$$;'$$;
+            execute stmt;
+            
+            return v_entity_id;
+
+        ELSIF FOUND THEN
+            
+            -- update cycle
+            
+            -- Only update if it's changed. Wewt.
+            UPDATE entity SET name = in_first_name || ' ' || in_last_name 
+            WHERE entity_id = a_user.entity_id and 
+            name <> in_first_name || ' ' || in_last_name;
+            
+            stmt := $$ alter user $$ || in_username || $$ with encrypted password $1$$$ || in_password || $$$1$ $$;
+            execute stmt;
+            
+            update user_connection set database = in_database, host = in_host, port = in_port
+            where database <> in_database
+            OR host <> in_host
+            OR port <> in_port;
+            
+            return a_user.id;
+        
+        END IF;
+    
+    END;
+$$ language 'plpgsql';
+
+create view role_view as 
+    select * from pg_auth_members m join pg_authid a ON (m.roleid = a.oid);
+        
+
+create or replace function admin_is_group(in_group_name text) returns bool as $$
+    
+    DECLARE
+        
+        existant_role role_view;
+        stmt text;
+        
+    BEGIN
+        select * into role_view from role_view where rolname = in_group_name;
+        
+        if not found then
+            return 'f'::bool;
+            
+        else
+            return 't'::bool;
+        end if;            
+    END;
+    
+$$ language 'plpgsql';
+
+CREATE OR REPLACE FUNCTION admin_create_group(in_group_name TEXT, in_dbname TEXT) RETURNS int as $$
+    
+    DECLARE
+        
+        stmt text;
+        
+    BEGIN
+        stmt := 'create role '||in_dbname||'_lsmb_$$' || in_group_name || '$$;';
+        execute stmt;
+        return 1;
+    END;
+    
+$$ language 'plpgsql';
+
+CREATE OR REPLACE FUNCTION admin_delete_user(in_username TEXT) returns INT as $$
+    
+    DECLARE
+        stmt text;
+        a_user user;
+    BEGIN
+    
+        select * into a_user from users where username = in_username;
+        
+        IF NOT FOUND THEN
+        
+            raise exception "User not found.";
+        ELSIF FOUND THEN
+    
+            stmt := $$ drop user $$ || a_user.username ||;
+            execute stmt;
+            
+            -- also gets user_connection
+            delete from users where id = a_user.id; 
+            delete from entity where id = a_user.entity_id;
+                                        
+        END IF;   
+    END;
+    
+$$ language 'plpgsql';
+
+comment on function admin_delete_user(text) is $$ 
+    Drops the provided user, as well as deletes the entity and user configuration data.
+$$;
+
+CREATE OR REPLACE FUNCTION admin_delete_group (in_group_name TEXT) returns bool as $$
+    
+    DECLARE
+        stmt text;
+        a_role role_view;
+    BEGIN
+        
+        select * into a_role from role_view where rolname = in_group_name;
+        
+        if not found then
+            return 'f'::bool;
+        else
+            stmt := 'drop role $dbname_lsmb_$$' || in_group_name || '$$;';
+            execute stmt;
+            return 't'::bool;
+        end if;
+    END;
+$$ language 'plpgsql';
+
+comment on function admin_delete_group(text) IS $$ 
+    Deletes the input group from the database. Not designed to be used to 
+    remove a login-capable user.
+$$;
\ No newline at end of file