Issue #23162:improve performance of getlocationid with index--test before merge

diff --git a/foundation-database/manifest.js b/foundation-database/manifest.js
index d729a19..292bdf9 100644 (file)
--- a/foundation-database/manifest.js
+++ b/foundation-database/manifest.js
@@ -991,6 +991,7 @@
     "public/trigger_functions/womatl.sql",
 
     "public/tables/metric.sql",
+    "public/tables/location.sql",
     "public/views/address.sql",
     "public/views/apmemo.sql",
     "public/views/armemo.sql",

diff --git a/foundation-database/public/functions/getlocationid.sql b/foundation-database/public/functions/getlocationid.sql
index 2694ce8..7ba6dd6 100644 (file)
--- a/foundation-database/public/functions/getlocationid.sql
+++ b/foundation-database/public/functions/getlocationid.sql
@@ -1,9 +1,8 @@
-CREATE OR REPLACE FUNCTION getLocationId(text,text) RETURNS INTEGER AS '
+CREATE OR REPLACE FUNCTION getLocationId(pWarehouse text,
+                                         pLocation text) RETURNS INTEGER AS $$
 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
 -- See www.xtuple.com/CPAL for the full text of the software license.
 DECLARE
-  pWarehouse ALIAS FOR $1;
-  pLocation ALIAS FOR $2;
   _returnVal INTEGER;
 BEGIN
   IF (pLocation IS NULL) THEN
@@ -12,14 +11,15 @@ BEGIN
 
   SELECT location_id INTO _returnVal
   FROM location
-  WHERE ((location_warehous_id=getWarehousId(pWarehouse,''ACTIVE''))
-  AND (formatLocationname(location_id)=pLocation))
+  WHERE ((location_warehous_id=getWarehousId(pWarehouse,'ACTIVE'))
+    AND  (location_formatname=pLocation))
   LIMIT 1;
 
   IF (_returnVal IS NULL) THEN
-       RAISE EXCEPTION ''Location % not found in Warehouse %.'', pLocation, pWarehouse;
+       RAISE EXCEPTION 'Location % not found in Warehouse %.', pLocation, pWarehouse;
   END IF;
 
   RETURN _returnVal;
 END;
-' LANGUAGE 'plpgsql';
+$$ LANGUAGE 'plpgsql';
+

diff --git a/foundation-database/public/trigger_functions/location.sql b/foundation-database/public/trigger_functions/location.sql
index 99fe3b0..9c78831 100644 (file)
--- a/foundation-database/public/trigger_functions/location.sql
+++ b/foundation-database/public/trigger_functions/location.sql
@@ -1,4 +1,4 @@
-CREATE OR REPLACE FUNCTION _locationTrigger () RETURNS TRIGGER AS '
+CREATE OR REPLACE FUNCTION _locationTrigger () RETURNS TRIGGER AS $$
 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
 -- See www.xtuple.com/CPAL for the full text of the software license.
 DECLARE
@@ -9,29 +9,29 @@ BEGIN
 
   -- Checks
   -- Start with privileges
-  IF (TG_OP = ''INSERT'') THEN
-    SELECT checkPrivilege(''MaintainLocations'') INTO _check;
+  IF (TG_OP = 'INSERT') THEN
+    SELECT checkPrivilege('MaintainLocations') INTO _check;
     IF NOT (_check) THEN
-      RAISE EXCEPTION ''You do not have privileges to add new Locations.'';
+      RAISE EXCEPTION 'You do not have privileges to add new Locations.';
     END IF;
   ELSE
-    SELECT checkPrivilege(''MaintainLocations'') INTO _check;
+    SELECT checkPrivilege('MaintainLocations') INTO _check;
     IF NOT (_check) THEN
-      RAISE EXCEPTION ''You do not have privileges to alter a Location.'';
+      RAISE EXCEPTION 'You do not have privileges to alter a Location.';
     END IF;
   END IF;
 
   -- Code is required
-  IF ( (LENGTH(COALESCE(NEW.location_name,''''))=0) AND
-       (LENGTH(COALESCE(NEW.location_aisle,''''))=0) AND
-       (LENGTH(COALESCE(NEW.location_rack,''''))=0) AND
-       (LENGTH(COALESCE(NEW.location_bin,''''))=0) ) THEN
-    RAISE EXCEPTION ''You must supply a valid Location Identifier.'';
+  IF ( (LENGTH(COALESCE(NEW.location_name,''))=0) AND
+       (LENGTH(COALESCE(NEW.location_aisle,''))=0) AND
+       (LENGTH(COALESCE(NEW.location_rack,''))=0) AND
+       (LENGTH(COALESCE(NEW.location_bin,''))=0) ) THEN
+    RAISE EXCEPTION 'You must supply a valid Location Identifier.';
   END IF;
   
   -- Site is required
   IF (NEW.location_warehous_id IS NULL) THEN
-    RAISE EXCEPTION ''You must supply a valid Site.'';
+    RAISE EXCEPTION 'You must supply a valid Site.';
   END IF;
 
   -- Location Identifier must be unique
@@ -44,18 +44,37 @@ BEGIN
     AND   (location_warehous_id=NEW.location_warehous_id)
     AND   (location_id<>NEW.location_id) );
   IF (FOUND) THEN
-    RAISE EXCEPTION ''You must supply a unique Location Identifier for this Site.'';
+    RAISE EXCEPTION 'You must supply a unique Location Identifier for this Site.';
+  END IF;
+
+  -- Populate formatted name
+  IF (NEW.location_aisle IS NOT NULL) THEN
+    NEW.location_formatname := NEW.location_aisle;
+  ELSE
+    NEW.location_formatname := '';
+  END IF;
+
+  IF (NEW.location_rack IS NOT NULL) THEN
+    NEW.location_formatname := (NEW.location_formatname || NEW.location_rack);
+  END IF;
+
+  IF (NEW.location_bin IS NOT NULL) THEN
+    NEW.location_formatname := (NEW.location_formatname || NEW.location_bin);
+  END IF;
+
+  IF (NEW.location_name IS NOT NULL) THEN
+    NEW.location_formatname := (NEW.location_formatname || NEW.location_name);
   END IF;
   
   RETURN NEW;
 
 END;
-' LANGUAGE 'plpgsql';
+$$ LANGUAGE 'plpgsql';
 
 SELECT dropIfExists('TRIGGER', 'locationTrigger');
 CREATE TRIGGER locationTrigger BEFORE INSERT OR UPDATE ON location FOR EACH ROW EXECUTE PROCEDURE _locationTrigger();
 
-CREATE OR REPLACE FUNCTION _locationAfterTrigger () RETURNS TRIGGER AS '
+CREATE OR REPLACE FUNCTION _locationAfterTrigger () RETURNS TRIGGER AS $$
 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
 -- See www.xtuple.com/CPAL for the full text of the software license.
 DECLARE
@@ -64,7 +83,7 @@ DECLARE
 BEGIN
 
   -- Maintain itemsite_qtyonhand and itemsite_nnqoh when location_netable changes
-  IF (TG_OP = ''UPDATE'') THEN
+  IF (TG_OP = 'UPDATE') THEN
     IF (OLD.location_netable <> NEW.location_netable) THEN
       FOR _itemloc IN SELECT * FROM itemloc WHERE (itemloc_location_id=NEW.location_id) LOOP
         IF (NEW.location_netable) THEN
@@ -83,7 +102,8 @@ BEGIN
   RETURN NEW;
 
 END;
-' LANGUAGE 'plpgsql';
+$$ LANGUAGE 'plpgsql';
 
 SELECT dropIfExists('TRIGGER', 'locationAfterTrigger');
 CREATE TRIGGER locationAfterTrigger AFTER INSERT OR UPDATE ON location FOR EACH ROW EXECUTE PROCEDURE _locationAfterTrigger();
+