diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/modules/Employee.sql | 11 | ||||
| -rw-r--r-- | sql/modules/Entity.sql | 20 | ||||
| -rw-r--r-- | sql/modules/Reconciliaton.sql | 2 | ||||
| -rw-r--r-- | sql/modules/admin.sql | 49 |
4 files changed, 62 insertions, 20 deletions
diff --git a/sql/modules/Employee.sql b/sql/modules/Employee.sql index bd3445f6..78920ea6 100644 --- a/sql/modules/Employee.sql +++ b/sql/modules/Employee.sql @@ -2,7 +2,7 @@ BEGIN; -CREATE OR REPLACE FUNCTION employee_save( +CREATE OR REPLACE FUNCTION employee__save( in_person int, in_entity int, in_startdate date, in_enddate date, in_role text, in_sales boolean, in_dob date, in_managerid integer, in_employeenumber text @@ -75,7 +75,7 @@ create view employees as JOIN salutation s ON (p.salutation_id = s.id); -CREATE OR REPLACE FUNCTION employee_get +CREATE OR REPLACE FUNCTION employee__get (in_id integer) returns employees as $$ @@ -83,21 +83,16 @@ DECLARE emp employees%ROWTYPE; BEGIN SELECT - s.salutation, - p.first_name, - p.last_name, ee.* INTO emp FROM employees ee - join person p USING (entity_id) - JOIN salutation s ON (p.salutation_id = s.id) WHERE ee.entity_id = in_id; RETURN emp; END; $$ language plpgsql; -CREATE OR REPLACE FUNCTION employee_list_managers +CREATE OR REPLACE FUNCTION employee__list_managers (in_id integer) RETURNS SETOF employees as $$ diff --git a/sql/modules/Entity.sql b/sql/modules/Entity.sql index ba98c62a..9c742e4b 100644 --- a/sql/modules/Entity.sql +++ b/sql/modules/Entity.sql @@ -37,4 +37,24 @@ CREATE OR REPLACE FUNCTION entity_save( $$ language 'plpgsql'; + +CREATE OR REPLACE FUNCTION entity__get_entity ( + in_entity_id int +) RETURNS setof entity AS $$ + +declare + v_row entity; +BEGIN + SELECT * INTO v_row FROM entity WHERE id = in_entity_id; + IF NOT FOUND THEN + raise exception "Could not find entity with ID %", in_entity_id; + ELSE + return next v_row; + END IF; +END; + +$$ language plpgsql; + + + commit; diff --git a/sql/modules/Reconciliaton.sql b/sql/modules/Reconciliaton.sql index 045d5e93..bd504dbb 100644 --- a/sql/modules/Reconciliaton.sql +++ b/sql/modules/Reconciliaton.sql @@ -491,7 +491,7 @@ create type recon_accounts as ( id int ); -create or replace function reconciliation__get_accounts () returns setof recon_accounts as $$ +create or replace function reconciliation__account_list () returns setof recon_accounts as $$ SELECT coa.accno || ' ' || coa.description as name, coa.id as id diff --git a/sql/modules/admin.sql b/sql/modules/admin.sql index e2023a5f..c7dc3d9b 100644 --- a/sql/modules/admin.sql +++ b/sql/modules/admin.sql @@ -198,13 +198,13 @@ 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 users as $$ +create or replace function admin__get_user(in_user INT) returns setof users as $$ DECLARE a_user users; BEGIN - select * into a_user from users where username = in_user; + select * into a_user from users where id = in_user; IF NOT FOUND THEN RAISE EXCEPTION 'cannot find user %', in_user; END IF; @@ -215,15 +215,30 @@ create or replace function admin__get_user(in_user TEXT) returns setof users as END; $$ language plpgsql; -create or replace function admin__get_roles_for_user(in_user TEXT) returns setof lsmb_roles as $$ +create or replace function admin__get_roles_for_user(in_user_id INT) returns setof text as $$ declare - u_role lsmb_roles; + u_role text; a_user users; begin - select * into a_user from admin_get_user(in_user); + select * into a_user from admin__get_user(in_user_id); - FOR u_role IN select * from lsmb_roles lr WHERE lr.user_id = a_user.id LOOP + FOR u_role IN + select r.rolname + from + pg_roles r, + (select + m.roleid + from + pg_auth_members m, pg_roles b + where + m.member = b.oid + and + b.rolname = a_user.username + ) as ar + where + r.oid = ar.roleid + LOOP RETURN NEXT u_role; @@ -297,7 +312,7 @@ 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 $$ +create or replace function admin__is_group(in_dbname TEXT, in_group_name text) returns bool as $$ DECLARE @@ -324,7 +339,7 @@ CREATE OR REPLACE FUNCTION admin__create_group(in_group_name TEXT, in_dbname TEX stmt text; BEGIN - stmt := 'create role '|| quote_ident(quote_ident(in_dbname) || '_lsmb_' || quote_ident(in_group_name) ); + stmt := 'create role lsmb_'|| quote_ident(quote_ident(in_dbname) || '__' || quote_ident(in_group_name) ); execute stmt; return 1; END; @@ -373,7 +388,7 @@ CREATE OR REPLACE FUNCTION admin__delete_group (in_dbname TEXT, in_group_name TE if not found then return 'f'::bool; else - stmt := 'drop role ' || quote_ident(in_dbname || '_lsmb_' || in_group_name); + stmt := 'drop role lsmb_' || quote_ident(in_dbname || '__' || in_group_name); execute stmt; return 't'::bool; end if; @@ -392,7 +407,7 @@ DECLARE out_rolename RECORD; BEGIN FOR out_rolename IN SELECT rolname FROM pg_authid - WHERE oid IN (SELECT id FROM connectby( + WHERE oid IN (SELECT id FROM connectby ( '(SELECT m.member, m.roleid, r.oid FROM pg_authid r LEFT JOIN pg_auth_members m ON (r.oid = m.roleid)) a', 'oid', 'member', 'oid', '320461', '0', ',' @@ -407,7 +422,6 @@ $$ LANGUAGE PLPGSQL; -- TODO: Add admin user - --CREATE OR REPLACE FUNCTION admin_audit_log () returns int as $$ @@ -464,5 +478,18 @@ BEGIN END; $$ language plpgsql; +create or replace function admin__user_preferences (in_user int) returns setof user_preference as $$ + +declare + v_row user_preferences; +BEGIN + select * into v_row from user_preference where user_id = in_user; + + IF NOT FOUND THEN + + RAISE EXCEPTION "Could not find user preferences for id %", in_user; + ELSE + return next v_row; + END IF; commit;
\ No newline at end of file |