Serious updates to the Admin controller;

Creation of a User controller/object - I would like to use this to save/load roles, as well.
Creation of Person stored procs, Entity stored procs. Serious updates to the employee stored procs.

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

1 files changed, 53 insertions, 70 deletions

diff --git a/sql/modules/admin.sql b/sql/modules/admin.sql
index 5cb6773a..84b65af3 100644
--- a/sql/modules/admin.sql
+++ b/sql/modules/admin.sql
@@ -1,3 +1,12 @@
+begin;
+
+create table lsmb_roles (
+    
+    user_id integer not null references users,
+    role text not null
+    
+);
+
 CREATE OR REPLACE FUNCTION admin_add_user_to_role(in_user TEXT, in_role TEXT) returns INT AS $$
     
     declare
@@ -19,7 +28,7 @@ CREATE OR REPLACE FUNCTION admin_add_user_to_role(in_user TEXT, in_role TEXT) re
             RAISE EXCEPTION 'Cannot grant permissions to a non-existant user.';
         END IF;
         
-        stmt := 'GRANT '|| in_role ||' to '|| in_user;
+        stmt := 'GRANT '|| quote_ident(in_role) ||' to '|| quote_ident(in_user);
         
         EXECUTE stmt;
         
@@ -49,7 +58,7 @@ CREATE OR REPLACE FUNCTION admin_remove_user_from_role(in_user TEXT, in_role TEX
             RAISE EXCEPTION 'Cannot revoke permissions from a non-existant user.';
         END IF;
         
-        stmt := 'REVOKE '|| in_role ||' FROM '|| in_user;
+        stmt := 'REVOKE '|| quote_ident(in_role) ||' FROM '|| quote_ident(in_user);
         
         EXECUTE stmt;
         
@@ -79,7 +88,7 @@ CREATE OR REPLACE FUNCTION admin_add_function_to_group(in_func TEXT, in_role TEX
             RAISE EXCEPTION 'Cannot grant permissions to a non-existant user.';
         END IF;
         
-        stmt := 'GRANT EXECUTE ON FUNCTION '|| in_func ||' to '|| in_role;
+        stmt := 'GRANT EXECUTE ON FUNCTION '|| quote_ident(in_func) ||' to '|| quote_ident(in_role);
         
         EXECUTE stmt;
         
@@ -109,7 +118,7 @@ CREATE OR REPLACE FUNCTION admin_remove_function_from_group(in_func TEXT, in_rol
             RAISE EXCEPTION 'Cannot revoke permissions from a non-existant function.';
         END IF;
         
-        stmt := 'REVOKE EXECUTE ON FUNCTION '|| in_func ||' FROM '|| in_role;
+        stmt := 'REVOKE EXECUTE ON FUNCTION '|| quote_ident(in_func) ||' FROM '|| quote_ident(in_role);
         
         EXECUTE stmt;
         
@@ -147,7 +156,7 @@ CREATE OR REPLACE FUNCTION admin_add_table_to_group(in_table TEXT, in_role TEXT,
             raise exception 'Cannot add unknown permission';
         END IF;
         
-        stmt := 'GRANT '|| in_perm|| 'ON TABLE '|| in_table ||' to '|| in_role;
+        stmt := 'GRANT '|| quote_ident(in_perm) || 'ON TABLE '|| quote_ident(in_table) ||' to '|| quote_ident(in_role);
         
         EXECUTE stmt;
         
@@ -171,8 +180,6 @@ CREATE OR REPLACE FUNCTION admin_remove_table_from_group(in_table TEXT, in_role
             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' 
@@ -182,7 +189,7 @@ CREATE OR REPLACE FUNCTION admin_remove_table_from_group(in_table TEXT, in_role
             RAISE EXCEPTION 'Cannot revoke permissions from a non-existant table.';
         END IF;
         
-        stmt := 'REVOKE '|| in_role ||' FROM '|| in_user;
+        stmt := 'REVOKE '|| quote_literal(in_role) ||' FROM '|| quote_literal(in_user);
         
         EXECUTE stmt;
         
@@ -191,18 +198,19 @@ CREATE OR REPLACE FUNCTION admin_remove_table_from_group(in_table TEXT, in_role
         
 $$ language 'plpgsql';
 
-create or replace function admin_get_user(in_user TEXT) returns setof user as $$
+create or replace function admin_get_user(in_user TEXT) returns setof users as $$
     
     DECLARE
-        a_user user;
+        a_user users;
     BEGIN
         
-        select * into a_user from user where username = in_user;
+        select * into a_user from users where username = in_user;
         IF NOT FOUND THEN
             RAISE EXCEPTION 'cannot find user %', in_user;
         END IF;
         
-        return a_user;
+        return next a_user;
+        return;
     
     END;    
 $$ language plpgsql;
@@ -211,13 +219,13 @@ create or replace function admin_get_roles_for_user(in_user TEXT) returns setof
     
     declare
         u_role lsmb_roles;
-        a_user user;
+        a_user users;
     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
+        FOR u_role IN select * from lsmb_roles lr WHERE lr.user_id = a_user.id LOOP
         
-            RETURN NEXT a_role;
+            RETURN NEXT u_role;
         
         END LOOP;
         RETURN;
@@ -227,82 +235,55 @@ $$ language 'plpgsql';
 
 CREATE OR REPLACE FUNCTION admin_save_user(
     in_id int, 
+    in_entity_id INT,
     in_username text, 
-    in_password TEXT, 
-    in_dbname TEXT, 
-    in_host TEXT, 
-    in_port TEXT
+    in_password TEXT
 ) returns int AS $$
     DECLARE
     
-        a_user user;
-        v_entity_id int;
+        a_user users;
+        v_user_id int;
         p_id int;
         l_id int;
         stmt text;
     BEGIN
     
-        select * into a_user from user where id = in_id;
+        select * into a_user from users lu where lu.id = in_id;
         
         IF NOT FOUND THEN 
             -- Insert cycle
             
-            --- First, create an entity.
+            --- The entity is expected to already BE created. See admin.pm.
             
             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
-            );
-            
+                RAISE EXCEPTION 'Fatal exception: Username already exists in Postgres; not
+                    a valid lsmb user.';
+            end if;         
             -- create an actual user
-            insert into users (name, entity_id) VALUES (
+            
+            v_user_id := nextval('users_id_seq');
+            insert into users (id, name, entity_id) VALUES (
+                v_user_id,
                 in_username,
-                v_entity_id
+                in_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||$$;'$$;
+            stmt := 'CREATE USER ' || quote_ident( in_username ) || ' WITH ENCRYPTED PASSWORD ' || quote_literal (in_password);
             execute stmt;
             
-            return v_entity_id;
+            return v_user_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$ $$;
+            stmt := ' alter user '|| quote_ident(in_username) || ' with encrypted password ' || quote_literal(in_password);
             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;
@@ -341,7 +322,7 @@ CREATE OR REPLACE FUNCTION admin_create_group(in_group_name TEXT, in_dbname TEXT
         stmt text;
         
     BEGIN
-        stmt := 'create role '||in_dbname||'_lsmb_$$' || in_group_name || '$$;';
+        stmt := 'create role '|| quote_ident(quote_ident(in_dbname) || '_lsmb_' || quote_ident(in_group_name) );
         execute stmt;
         return 1;
     END;
@@ -352,17 +333,17 @@ CREATE OR REPLACE FUNCTION admin_delete_user(in_username TEXT) returns INT as $$
     
     DECLARE
         stmt text;
-        a_user user;
+        a_user users;
     BEGIN
     
         select * into a_user from users where username = in_username;
         
         IF NOT FOUND THEN
         
-            raise exception "User not found.";
+            raise exception 'User not found.';
         ELSIF FOUND THEN
     
-            stmt := $$ drop user $$ || a_user.username ||;
+            stmt := ' drop user ' || quote_ident(a_user.username);
             execute stmt;
             
             -- also gets user_connection
@@ -378,7 +359,7 @@ 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 $$
+CREATE OR REPLACE FUNCTION admin_delete_group (in_dbname TEXT, in_group_name TEXT) returns bool as $$
     
     DECLARE
         stmt text;
@@ -390,14 +371,14 @@ CREATE OR REPLACE FUNCTION admin_delete_group (in_group_name TEXT) returns bool
         if not found then
             return 'f'::bool;
         else
-            stmt := 'drop role $dbname_lsmb_$$' || in_group_name || '$$;';
+            stmt := 'drop role ' || quote_ident(in_dbname || '_lsmb_' || in_group_name);
             execute stmt;
             return 't'::bool;
         end if;
     END;
 $$ language 'plpgsql';
 
-comment on function admin_delete_group(text) IS $$ 
+comment on function admin_delete_group(text,text) IS $$ 
     Deletes the input group from the database. Not designed to be used to 
     remove a login-capable user.
 $$;
@@ -425,8 +406,10 @@ $$ LANGUAGE PLPGSQL;
 -- TODO:  Add admin user
 
 
-CREATE OR REPLACE FUNCTION admin_audit_log () returns int as $$
+--CREATE OR REPLACE FUNCTION admin_audit_log () returns int as $$
     
     
     
-$$ language plpgsql;
\ No newline at end of file
+--$$ language plpgsql;
+
+commit;
\ No newline at end of file