Repository: SFDO-Tooling/CumulusCI
Pull request #3361 (2022-09-16 01:40:42)

Support updates and upserts with blank dates represented by strings
A dataset like this:

```
BEGIN TRANSACTION;
CREATE TABLE "Contact" (
	id INTEGER NOT NULL, 
	"FirstName" VARCHAR(255), 
	"LastName" VARCHAR(255), 
	"Birthdate" VARCHAR(255), 
	"External_ID__c" VARCHAR(255), 
	"IsPersonAccount" VARCHAR(255), 
	PRIMARY KEY (id)
);
INSERT INTO "Contact" VALUES(1,'AAA','BBBB','','C2','False');
COMMIT;
```

Previously generated an error like this:

```
                    WARNING  Error on record with id 1: JSON_PARSER_ERROR: Cannot deserialize   utils.py:141
                             instance of date from VALUE_STRING value  or request may be                    
                             missing a required field at [Source: N/A; line: -1, column:                    
                             -1][line:-1, column:-1] ()                                                     
```


prescod: Fixes #3283


Patch:
diff --git a/cumulusci/tasks/bulkdata/step.py b/cumulusci/tasks/bulkdata/step.py
index 0356b6cea..0d3fda60a 100644
--- a/cumulusci/tasks/bulkdata/step.py
+++ b/cumulusci/tasks/bulkdata/step.py
@@ -466,29 +466,31 @@ class RestApiDmlOperation(BaseDmlOperation):
             self.api_options["batch_size"], MAX_REST_BATCH_SIZE
         )
 
-    def load_records(self, records):
-        def _convert(rec):
-            result = dict(zip(self.fields, rec))
-            for boolean_field in self.boolean_fields:
-                try:
-                    result[boolean_field] = process_bool_arg(
-                        result[boolean_field] or False
-                    )
-                except TypeError as e:
-                    raise BulkDataException(e)
-
-            # Remove empty fields (different semantics in REST API)
-            # We do this for insert only - on update, any fields set to `null`
-            # are meant to be blanked out.
-            if self.operation is DataOperationType.INSERT:
-                result = {
-                    k: result[k]
-                    for k in result
-                    if result[k] is not None and result[k] != ""
-                }
+    def _record_to_json(self, rec):
+        result = dict(zip(self.fields, rec))
+        for boolean_field in self.boolean_fields:
+            try:
+                result[boolean_field] = process_bool_arg(result[boolean_field] or False)
+            except TypeError as e:
+                raise BulkDataException(e)
+
+        # Remove empty fields (different semantics in REST API)
+        # We do this for insert only - on update, any fields set to `null`
+        # are meant to be blanked out.
+        if self.operation is DataOperationType.INSERT:
+            result = {
+                k: result[k]
+                for k in result
+                if result[k] is not None and result[k] != ""
+            }
+        elif self.operation in (DataOperationType.UPDATE, DataOperationType.UPSERT):
+            result = {k: (result[k] if result[k] != "" else None) for k in result}
+
+        result["attributes"] = {"type": self.sobject}
+        return result
 
-            result["attributes"] = {"type": self.sobject}
-            return result
+    def load_records(self, records):
+        """Load, update, upsert or delete records into the org"""
 
         self.results = []
         method = {
@@ -501,7 +503,9 @@ class RestApiDmlOperation(BaseDmlOperation):
         update_key = self.api_options.get("update_key")
         for chunk in iterate_in_chunks(self.api_options.get("batch_size"), records):
             if self.operation is DataOperationType.DELETE:
-                url_string = "?ids=" + ",".join(_convert(rec)["Id"] for rec in chunk)
+                url_string = "?ids=" + ",".join(
+                    self._record_to_json(rec)["Id"] for rec in chunk
+                )
                 json = None
             else:
                 if update_key:
@@ -509,7 +513,10 @@ class RestApiDmlOperation(BaseDmlOperation):
                     url_string = f"/{self.sobject}/{update_key}"
                 else:
                     url_string = ""
-                json = {"allOrNone": False, "records": [_convert(rec) for rec in chunk]}
+                json = {
+                    "allOrNone": False,
+                    "records": [self._record_to_json(rec) for rec in chunk],
+                }
 
             self.results.extend(
                 self.sf.restful(
@@ -607,6 +614,7 @@ def get_dml_operation(
     or if the operation is HARD_DELETE, which is only available for Bulk)."""
 
     context.logger.debug(f"Creating {operation} Operation for {sobject} using {api}")
+    assert isinstance(operation, DataOperationType)
 
     # REST Collections requires 42.0.
     api_version = float(context.sf.sf_version)


Test patch:
diff --git a/cumulusci/tasks/bulkdata/tests/test_step.py b/cumulusci/tasks/bulkdata/tests/test_step.py
index caa23d271..274ef6aec 100644
--- a/cumulusci/tasks/bulkdata/tests/test_step.py
+++ b/cumulusci/tasks/bulkdata/tests/test_step.py
@@ -1386,3 +1386,69 @@ class TestGetOperationFunctions:
                 api=42,
                 volume=1,
             )
+
+    def test_cleanup_date_strings__insert(self):
+        """Empty date strings should be removed from INSERT operations"""
+        context = mock.Mock()
+        context.sf.sf_version = "42.0"
+        context.sf.Test__c.describe = lambda: {
+            "name": "Test__c",
+            "fields": [
+                {"name": "Birthdate", "type": "date"},
+                {"name": "IsHappy", "type": "boolean"},
+                {"name": "Name", "type": "string"},
+            ],
+        }
+
+        step = get_dml_operation(
+            sobject="Test__c",
+            operation=DataOperationType.INSERT,
+            fields=["Birthdate", "IsHappy", "Name"],
+            api_options={},
+            context=context,
+            api=DataApi.REST,
+            volume=1,
+        )
+        json_out = step._record_to_json(["", "", "Bill"])
+        assert json_out == {
+            "IsHappy": False,
+            "Name": "Bill",
+            "attributes": {"type": "Test__c"},
+        }, json_out
+        # Empty dates (and other fields) should be filtered out of INSERTs
+        assert "BirthDate" not in json_out  # just for emphasis
+
+    @pytest.mark.parametrize(
+        "operation", ((DataOperationType.UPSERT, DataOperationType.UPDATE))
+    )
+    def test_cleanup_date_strings__upsert_update(self, operation):
+        """Empty date strings should be NULLED for UPSERT and UPDATE operations"""
+        context = mock.Mock()
+        context.sf.sf_version = "42.0"
+        context.sf.Test__c.describe = lambda: {
+            "name": "Test__c",
+            "fields": [
+                {"name": "Birthdate", "type": "date"},
+                {"name": "IsHappy", "type": "boolean"},
+                {"name": "Name", "type": "string"},
+            ],
+        }
+
+        step = get_dml_operation(
+            sobject="Test__c",
+            operation=operation,
+            fields=["Birthdate", "IsHappy", "Name"],
+            api_options={},
+            context=context,
+            api=DataApi.REST,
+            volume=1,
+        )
+        # Empty dates (and other fields) should be NULLED in UPSERTs
+        # Booleans become False for backwards-compatibility reasons.
+        json_out = step._record_to_json(["", "", "Bill"])
+        assert json_out == {
+            "Birthdate": None,
+            "IsHappy": False,
+            "Name": "Bill",
+            "attributes": {"type": "Test__c"},
+        }, json_out