SF.net SVN: ledger-smb:[2382] trunk/sql/modules

[..hidden..]

Revision: 2382
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2382&view=rev
Author:   einhverfr
Date:     2008-11-07 21:17:08 +0000 (Fri, 07 Nov 2008)

Log Message:
-----------
Adding edit_drafts permission
Correcting logic in location_save
Correcting data type contact_list
Correcting some logic regarding how addresses are stored when they are modified.
Adding relevant test cases.

Modified Paths:
--------------
    trunk/sql/modules/Company.sql
    trunk/sql/modules/Location.sql
    trunk/sql/modules/Roles.sql
    trunk/sql/modules/test/Company.sql

Modified: trunk/sql/modules/Company.sql
===================================================================
--- trunk/sql/modules/Company.sql	2008-10-28 22:20:27 UTC (rev 2381)
+++ trunk/sql/modules/Company.sql	2008-11-07 21:17:08 UTC (rev 2382)
@@ -463,6 +463,7 @@
 CREATE TYPE contact_list AS (
 	class text,
 	class_id int,
+	description text,
 	contact text
 );
 
@@ -633,7 +634,7 @@
 		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 @@
 		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)

Modified: trunk/sql/modules/Location.sql
===================================================================
--- trunk/sql/modules/Location.sql	2008-10-28 22:20:27 UTC (rev 2381)
+++ trunk/sql/modules/Location.sql	2008-11-07 21:17:08 UTC (rev 2382)
@@ -37,6 +37,14 @@
 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 @@
 	        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 @@
 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 

Modified: trunk/sql/modules/Roles.sql
===================================================================
--- trunk/sql/modules/Roles.sql	2008-10-28 22:20:27 UTC (rev 2381)
+++ trunk/sql/modules/Roles.sql	2008-11-07 21:17:08 UTC (rev 2382)
@@ -1400,6 +1400,8 @@
 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.

Modified: trunk/sql/modules/test/Company.sql
===================================================================
--- trunk/sql/modules/test/Company.sql	2008-10-28 22:20:27 UTC (rev 2381)
+++ trunk/sql/modules/test/Company.sql	2008-11-07 21:17:08 UTC (rev 2382)
@@ -21,10 +21,26 @@
 	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;
 
 SELECT (select count(*) from test_result where success is true) 


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.