diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/Pg-database.sql | 178 | ||||
| -rw-r--r-- | sql/modules/Employee.sql | 252 | ||||
| -rw-r--r-- | sql/modules/Entity.sql | 41 | ||||
| -rw-r--r-- | sql/modules/Person.sql | 52 | ||||
| -rw-r--r-- | sql/modules/admin.sql | 123 |
5 files changed, 322 insertions, 324 deletions
diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql index cc4b77dd..85c8df74 100644 --- a/sql/Pg-database.sql +++ b/sql/Pg-database.sql @@ -10,7 +10,7 @@ CREATE SEQUENCE id; CREATE TABLE entity ( id serial UNIQUE, name text check (name ~ '[[:alnum:]_]'), - entity_class integer not null, + entity_class integer references entity_class(id) not null , created date not null default current_date, PRIMARY KEY(name,entity_class)); @@ -58,70 +58,6 @@ CREATE TABLE users ( COMMENT ON TABLE users IS $$username is the actual primary key here because we do not want duplicate users$$; -create table user_connection ( - user_id int not null references users(id) on delete cascade, - dbname text not null, - host text not null default 'localhost', - port int not null default '5432' -); -/* -CREATE VIEW users_conf as - select - users.id, - loc.address1 || '\n'|| loc.address2 ||'\n' || loc.address3, - em.employeenumber, - company, - loc.country, - currency, - dateformat, - 'Pg', - u_cx.host, - u_cx.dbname, - u_cx.dbport, - users.username, - p.email, - p.fax, - 50, - p.first_name || ' ' || p.last_name, - p.number_format, - '', -- password - - -; -*/ -CREATE TABLE users_conf(id integer primary key references users(id) deferrable initially deferred, - acs text, - address text, - businessnumber text, - company text, - countrycode text, - currency text, - dateformat text, - dbdriver text default 'Pg', - dbhost text default 'localhost', - dbname text, - dboptions text, - dbpasswd text, - dbport text, - dbuser text, - email text, - fax text, - menuwidth text, - name text, - numberformat text, - password varchar(32) check(length(password) = 32), - crypted_password text, - print text, - printer text, - role text, - sid text, - signature text, - stylesheet text, - tel text, - templates text, - timeout numeric, - vclimit numeric); - COMMENT ON TABLE users_conf IS 'This is a completely dumb table that is a place holder to get usersconf into the database. Next major release will have a much more sane implementation'; COMMENT ON COLUMN users_conf.id IS 'Yes primary key with a FOREIGN KEY to users(id) is correct'; COMMENT ON COLUMN users_conf.password IS 'This means we have to get rid of the current password stuff and move to presumably md5()'; @@ -255,17 +191,56 @@ INSERT INTO salutation (id,salutation) VALUES ('6','Sir.'); SELECT SETVAL('salutation_id_seq',7); CREATE TABLE person ( - id serial PRIMARY KEY, - entity_id integer references entity(id) not null, - salutation_id integer references salutation(id), - first_name text check (first_name ~ '[[:alnum:]_]') NOT NULL, - middle_name text, - last_name text check (last_name ~ '[[:alnum:]_]') NOT NULL, - created date not null default current_date + id serial PRIMARY KEY, + entity_id integer references entity(id) not null, + salutation_id integer references salutation(id), + first_name text check (first_name ~ '[[:alnum:]_]') NOT NULL, + middle_name text, + last_name text check (last_name ~ '[[:alnum:]_]') NOT NULL, + created date not null default current_date ); COMMENT ON TABLE person IS $$ Every person, must have an entity to derive a common or display name. The correct way to get class information on a person would be person.entity_id->entity_class_to_entity.entity_id. $$; +create table entity_employee ( + + person_id integer references person(id) not null, + entity_id integer references entity(id) not null, + startdate date not null default current_date, + enddate date, + role varchar(20), + sales bool default 'f', + manager_id integer references entity(id), + employeenumber varchar(32), + dob date + PRIMARY KEY (person_id, entity_id) +); + +-- notes are from entity_note +-- ssn, iban and bic are from entity_credit_account +-- + +create view employee as + SELECT + ente.entity_id, + 3, + u.username, + ente.startdate, + ente.enddate, + en.note, + eca.ssn, + eca.iban, + eca.bic, + ente.manager_id, + ente.employeenumber, + ente.dob + FROM + entity_employee ente + JOIN + entity_credit_account eca on eca.entity_id = ente.entity_id, + entity_note en on en.entity_id = ente.entity_id, + user u on u.entity_id = ente.entity_id; + CREATE TABLE person_to_location ( location_id integer not null references location(id), person_id integer not null references person(id) ON DELETE CASCADE, @@ -473,25 +448,25 @@ CREATE TABLE pricegroup ( ); CREATE TABLE entity_credit_account ( - id serial not null unique, - entity_id int not null references entity(id) ON DELETE CASCADE, - entity_class int not null references entity_class(id) check ( entity_class in (1,2) ), - discount numeric, - discount_terms int default 0, - taxincluded bool default 'f', - creditlimit NUMERIC default 0, - terms int2 default 0, - meta_number varchar(32), - cc text, - bcc text, - business_id int, - language_code varchar(6), - pricegroup_id int references pricegroup(id), - curr char(3), - startdate date DEFAULT CURRENT_DATE, - enddate date, - threshold numeric default 0, - PRIMARY KEY(entity_id, meta_number) + id serial not null unique, + entity_id int not null references entity(id) ON DELETE CASCADE, + entity_class int not null references entity_class(id) check ( entity_class in (1,2) ), + discount numeric, + discount_terms int default 0, + taxincluded bool default 'f', + creditlimit NUMERIC default 0, + terms int2 default 0, + meta_number varchar(32), + cc text, + bcc text, + business_id int, + language_code varchar(6), + pricegroup_id int references pricegroup(id), + curr char(3), + startdate date DEFAULT CURRENT_DATE, + enddate date, + threshold numeric default 0, + PRIMARY KEY(entity_id, meta_number) ); @@ -568,24 +543,13 @@ ALTER TABLE company ADD COLUMN sic_code varchar; -- -- -create table employee ( - entity_id integer not null references entity(id) on delete cascade primary key, - entity_class_id integer references entity_class(id) not null check (entity_class_id = 3), - login text, - startdate date default current_date, - enddate date, - notes text, - role varchar(20), - sales bool default 'f', - ssn varchar(20), - iban varchar(34), - bic varchar(11), - managerid int, - employeenumber varchar(32), - dob date -); + + + + COMMENT ON TABLE employee IS $$ Is a metadata table specific to employees $$; + CREATE TABLE parts ( id serial PRIMARY KEY, partnumber text, diff --git a/sql/modules/Employee.sql b/sql/modules/Employee.sql index a934a3e5..04f33e64 100644 --- a/sql/modules/Employee.sql +++ b/sql/modules/Employee.sql @@ -1,174 +1,119 @@ -- VERSION 1.3.0 BEGIN; -CREATE OR REPLACE FUNCTION employee_save -(in_id integer, in_employeenumber varchar(32), - in_salutation int, in_first_name varchar(64), in_last_name varchar(64), - in_address1 varchar(32), in_address2 varchar(32), - in_city varchar(32), in_state varchar(32), in_zipcode varchar(10), - in_country int, in_workphone varchar(20), - in_homephone varchar(20), in_startdate date, in_enddate date, - in_notes text, in_role varchar(20), in_sales boolean, in_email text, - in_ssn varchar(20), in_dob date, in_iban varchar(34), - in_bic varchar(11), in_managerid integer) -returns int AS $$ -DECLARE - e_id int; - e entity; - loc location; - l_id int; - per person; - p_id int; -BEGIN - select * into e from entity where id = in_id and entity_class = 3; - - if found then + +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 +) +returns int AS $$ + + DECLARE + e_ent entity_employee; + e entity; + p person; + BEGIN + select * into e from entity where id = in_entity and entity_class = 3; - select l.* into loc from location l - left join person_to_location ptl on ptl.location_id = l.id - left join person p on p.id = ptl.person_id - where p.entity_id = in_id; + IF NOT FOUND THEN + RAISE EXCEPTION 'No entity found for ID %', in_id; + END IF; - select * into per from person p where p.entity_id = in_id; + select * into p from person where id = in_person; - update location - set - line_one = in_address1, - line_two = in_address2, - city_province = in_city, - mail_code = in_zipcode, - country_id = in_country - where id = loc.id; - - UPDATE employee - SET - employeenumber = in_employeenumber, - startdate = in_startdate, - enddate = in_enddate, - role = in_role, - sales = in_sales, - ssn = in_ssn, - dob = in_dob, - managerid = in_managerid - WHERE entity_id = in_id; - - update entity_note - set - note = in_note - where entity_id = in_id; - - UPDATE entity_bank_account - SET - bic = in_bic, - iban = in_iban - WHERE entity_id = in_id; - - UPDATE person - SET - salutation_id = in_salutation, - first_name = in_first_name, - last_name = in_last_name - WHERE entity_id = in_id; + IF NOT FOUND THEN + RAISE EXCEPTION 'No person found for ID %', in_id; + END IF; - UPDATE person_to_contact - set - contact = in_homephone - WHERE person_id = per.id - AND contact_class_id = 11; - - UPDATE person_to_contact - set - contact = in_workphone - WHERE person_id = per.id - AND contact_class_id = 1; - - UPDATE person_to_contact - set - contact = in_email - WHERE person_id = per.id - AND contact_class_id = 12; + -- Okay, we're good. Check to see if we update or insert. - return in_id; + select * into e_ent from entity_employee where person_id = in_person + and entity_id = in_entity; + + IF NOT FOUND THEN + -- insert. + + INSERT INTO entity_employee (person_id, entity_id, startdate, + enddate, role, sales, manager_id, employeenumber, dob) + VALUES (in_person, in_entity, in_startdate, in_enddate, in_role, + in_sales, in_managerid, in_employeenumber, in_dob); + + return in_entity; + ELSE - ELSIF NOT FOUND THEN - -- first, create a new entity - -- Then, create an employee. - - e_id := in_id; -- expect nextval entity_id to have been called. - INSERT INTO entity (id, entity_class, name) VALUES (e_id, 3, in_first_name||' '||in_last_name); - - INSERT INTO entity_bank_account (entity_id, iban, bic) - VALUES (e_id, in_iban, in_bic); - - p_id := nextval('person_id_seq'); - insert into person (id, salutation_id, first_name, last_name, entity_id) - VALUES - (p_id, in_salutation, in_first_name, in_last_name, e_id); - - if in_notes is not null then - insert into entity_note (note_class, note, ref_key, vector) - values (1, in_notes, e_id, ''); - END IF; - - insert into person_to_contact (person_id, contact_class_id, contact) - VALUES (p_id, 1, in_workphone); -- work phone # - insert into person_to_contact (person_id, contact_class_id, contact) - VALUES (p_id, 11, in_homephone); -- Home phone # - insert into person_to_contact (person_id, contact_class_id, contact) - VALUES (p_id, 12, in_email); -- email address. - - INSERT INTO employee - (employeenumber, startdate, enddate, - role, sales, ssn, - dob, managerid, entity_id, entity_class_id) - VALUES - (in_employeenumber, in_startdate, in_enddate, - in_role, in_sales, in_ssn, - in_dob, in_managerid, e_id, 3); - - l_id := nextval('location_id_seq'); - insert into location (id, location_class, line_one, line_two, city_province, country_id, mail_code) - VALUES ( - l_id, - 1, - in_address1, - in_address2, - in_city, - in_country, - in_zipcode - ); - insert into person_to_location (person_id, location_id) - VALUES (p_id, l_id); - - return e_id; - END IF; -END; -$$ LANGUAGE 'plpgsql'; + -- update + + UPDATE entity_employee + SET + startdate = in_startdate, + enddate = in_enddate, + role = in_role, + sales = in_sales, + manager_id = in_managerid + employeenumber = in_employeenumber, + dob = in_dob + WHERE + entity_id = in_entity + AND + person_id = in_person; + + return in_entity; + END IF; + END; + +$$ language 'plpgsql'; + +create view employees as + select + e.salutation, + e.first_name, + e.last_name, + ee.* + FROM entity e + JOIN entity_employees ee on e.id = ee.entity_id + where e.entity_class = 3; + -- why is this like this? CREATE OR REPLACE FUNCTION employee_get (in_id integer) -returns employee as +returns employees as $$ DECLARE - emp employee%ROWTYPE; + emp employees%ROWTYPE; BEGIN - SELECT * INTO emp FROM employees WHERE id = in_id; + SELECT + e.salutation, + e.first_name, + e.last_name, + ee.* + INTO emp + FROM employees ee + join entity e on ee.entity_id = e.id + WHERE ee.entity_id = in_id; + RETURN emp; END; $$ language plpgsql; CREATE OR REPLACE FUNCTION employee_list_managers (in_id integer) -RETURNS SETOF employee as +RETURNS SETOF employees as $$ DECLARE - emp employee%ROWTYPE; + emp employees%ROWTYPE; BEGIN FOR emp IN - SELECT * FROM employee - WHERE sales = '1' AND role='manager' - AND entity_id <> coalesce(in_id, -1) + SELECT + e.salutation, + e.first_name, + e.last_name, + ee.* + FROM entity_employee ee + JOIN entity e on e.id = ee.entity_id + WHERE ee.sales = 't'::bool AND ee.role='manager' + AND ee.entity_id <> coalesce(in_id, -1) ORDER BY name LOOP RETURN NEXT emp; @@ -196,7 +141,7 @@ CREATE OR REPLACE VIEW employee_search AS SELECT e.*, em.name AS manager, emn.note, en.name as name FROM employee e LEFT JOIN entity en on (e.entity_id = en.id) -LEFT JOIN employee m ON (e.managerid = m.entity_id) +LEFT JOIN entity_employee m ON (e.managerid = m.entity_id) LEFT JOIN entity em on (em.id = m.entity_id) LEFT JOIN entity_note emn on (emn.ref_key = em.id); @@ -228,4 +173,17 @@ BEGIN return; END; $$ language plpgsql; -COMMIT;
\ No newline at end of file + +create or replace function employee_set_location + (in_employee int, in_location int) +returns void as $$ + + INSERT INTO person_to_location (person_id,location_id) + VALUES (in_employee, in_location); + + SELECT NULL; + +$$ language 'sql'; + +COMMIT; + diff --git a/sql/modules/Entity.sql b/sql/modules/Entity.sql new file mode 100644 index 00000000..2b5aed58 --- /dev/null +++ b/sql/modules/Entity.sql @@ -0,0 +1,41 @@ +-- +BEGIN; + +CREATE OR REPLACE FUNCTION entity_save( + in_entity_id int, in_name text, in_entity_class INT +) RETURNS INT AS $$ + + DECLARE + e entity; + e_id int; + + BEGIN + + select * into e from entity where id = in_entity_id; + + IF NOT FOUND THEN + -- do the insert magic. + e_id = nextval('entity_id_seq'); + insert into entity (id, name, entity_class) values + (e_id, + in_name, + in_entity_class + ); + return e_id; + + ELSIF FOUND THEN + + update + entity + SET + name = in_name + entity_class = in_entity_class + WHERE + id = in_entity_id; + return in_entity_id; + END IF; + END; + +$$ language 'plpgsql'; + +commit;
\ No newline at end of file diff --git a/sql/modules/Person.sql b/sql/modules/Person.sql new file mode 100644 index 00000000..1ca1bb16 --- /dev/null +++ b/sql/modules/Person.sql @@ -0,0 +1,52 @@ +begin; + +CREATE OR REPLACE FUNCTION person_save + +(in_id integer, in_salutation int, +in_first_name text, in_last_name text +) +RETURNS INT AS $$ + + DECLARE + e_id int; + e entity; + loc location; + l_id int; + per person; + p_id int; + BEGIN + + select * into e from entity where id = in_id and entity_class = 3; + + IF NOT FOUND THEN + RAISE EXCEPTION 'No entity found for ID %', in_id; + END IF; + + select * into per FROM person WHERE entity_id = in_id; + + IF FOUND THEN + + -- do an update + + UPDATE person SET + salutation = in_salutation, + first_name = in_first_name, + last_name = in_last_name + WHERE + entity_id = in_id + AND + id = per.id; + + ELSE + + -- Do an insert + + INSERT INTO person (salutation, first_name, last_name) VALUES + (in_salutation, in_first_name, in_last_name); + + + END IF; + +$$ language plpgsql; + +commit;
\ No newline at end of file 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 |