diff options
| -rw-r--r-- | sql/modules/Company.sql | 9 | ||||
| -rw-r--r-- | sql/modules/Location.sql | 14 | ||||
| -rw-r--r-- | sql/modules/Roles.sql | 2 | ||||
| -rw-r--r-- | sql/modules/test/Company.sql | 18 |
4 files changed, 37 insertions, 6 deletions
diff --git a/sql/modules/Company.sql b/sql/modules/Company.sql index 500a7188..c77d37ca 100644 --- a/sql/modules/Company.sql +++ b/sql/modules/Company.sql @@ -463,6 +463,7 @@ $$ LANGUAGE PLPGSQL; CREATE TYPE contact_list AS ( class text, class_id int, + description text, contact text ); @@ -633,7 +634,7 @@ create or replace function _entity_location_save( AND location_class = in_location_class AND location_id = in_location_id; - SELECT location_save(in_line_one, in_line_two, in_line_three, in_city, + SELECT location_save(NULL, in_line_one, in_line_two, in_line_three, in_city, in_state, in_mail_code, in_country_code) INTO l_id; @@ -664,9 +665,11 @@ create or replace function eca__location_save( AND location_class = in_location_class AND location_id = in_location_id; - SELECT location_save(in_location_id, in_line_one, in_line_two, in_line_three, in_city, + -- don't pass the in_location_id through because that is not safe. + SELECT location_save(NULL, in_line_one, in_line_two, in_line_three, + in_city, in_state, in_mail_code, in_country_code) - INTO l_id; + INTO l_id; INSERT INTO eca_to_location (credit_id, location_class, location_id) diff --git a/sql/modules/Location.sql b/sql/modules/Location.sql index cd86dea1..363fa52e 100644 --- a/sql/modules/Location.sql +++ b/sql/modules/Location.sql @@ -37,6 +37,14 @@ DECLARE BEGIN IF in_location_id IS NULL THEN + SELECT id INTO location_id FROM location + WHERE line_one = in_address1 AND line_two = in_address2 + AND line_three = in_address3 AND in_city = city + AND in_state = state AND in_zipcode = mail_code + AND in_country = country_id + LIMIT 1; + + IF NOT FOUND THEN -- Straight insert. location_id = nextval('location_id_seq'); INSERT INTO location ( @@ -58,10 +66,12 @@ BEGIN in_zipcode, in_country ); + END IF; return location_id; ELSE + RAISE NOTICE 'Overwriting location id %', in_location_id; -- Test it. - SELECT * INTO location_row WHERE id = in_location_id; + SELECT * INTO location_row FROM location WHERE id = in_location_id; IF NOT FOUND THEN -- Tricky users are lying to us. RAISE EXCEPTION 'location_save called with nonexistant location ID %', in_location_id; @@ -87,7 +97,7 @@ $$ LANGUAGE PLPGSQL; COMMENT ON function location_save (in_location_id int, in_address1 text, in_address2 text, in_address3 text, in_city text, in_state text, in_zipcode text, in_country int) IS -$$ Note that this does NOT override the data in the database. +$$ Note that this does NOT override the data in the database unless in_location_id is specified. Instead we search for locations matching the desired specifications and if none are found, we insert one. Either way, the return value of the location can be used for mapping to other things. This is necessary because locations are diff --git a/sql/modules/Roles.sql b/sql/modules/Roles.sql index ca8e2533..9ef3d4c7 100644 --- a/sql/modules/Roles.sql +++ b/sql/modules/Roles.sql @@ -1400,6 +1400,8 @@ GRANT ALL ON transactions, entity_employee, customer, vendor TO public; GRANT ALL ON pending_job, payments_queue TO PUBLIC; GRANT ALL ON pending_job_id_seq TO public; --TODO, lock recurring, pending_job, payment_queue down more +-- Roles with no db permissions: +CREATE ROLE "lsmb_<?lsmb dbname ?>__draft_edit" WITH INHERIT NOLOGIN; -- CT: The following grant is required for now, but will hopefully become less -- important when we get to 1.4 and can more sensibly lock things down. diff --git a/sql/modules/test/Company.sql b/sql/modules/test/Company.sql index fa95b32a..7634b7ea 100644 --- a/sql/modules/test/Company.sql +++ b/sql/modules/test/Company.sql @@ -21,9 +21,25 @@ SELECT 'eca_location_save', IS NOT NULL; INSERT INTO test_result (test_name, success) -SELECT 'list_locations', count(*) > 0 +SELECT 'eca_location_save returns same id with same args and no in_location_id', + eca__location_save(currval('entity_credit_account_id_seq')::int, NULL, 1, 'Test2', 'Test', + '', 'Test', 'Test', '12345', 25) = + eca__location_save(currval('entity_credit_account_id_seq')::int, NULL, 2, 'Test2', 'Test', + '', 'Test', 'Test', '12345', 25); + +INSERT INTO test_result (test_name, success) +SELECT 'list_locations', count(*) = 3 FROM eca__list_locations(currval('entity_credit_account_id_seq')::int); +INSERT INTO test_result(test_name, success) +SELECT 'saving eca contact', + eca__save_contact(currval('entity_credit_account_id_seq')::int, + 1, 'test_d', 'test_c', NULL, NULL) IS NOT NULL; + +INSERT INTO test_result(test_name, success) +SELECT 'Contact found correctly', count(*) = 1 +FROM eca__list_contacts(currval('entity_credit_account_id_seq')::int) +WHERE contact = 'test_c'; SELECT * FROM test_result; |