SAP Cloud Platform SDK for service development: Create OData Service [9] UPSERT

In one of the previous blogs, we’ve already learned the basics about implementing write operations like CREATE and UPDATE.
In the present blog, which is also part of the series for beginners, we’re going to discuss one more interesting detail:

An UPDATE operation can be sent to an address that doesn’t exist… this is a new feature in V4, it is called UPSERT and worth a little wow.

Agreed?

So let’s shout it altogether:
one…. two…. three…. now: wow

ohhhhhh, I couldn’t hear anything…;-(

Note:
the UPSERT is new and in addition, it is supported by the SAP Cloud Platform SDK for service development !!!.

Can I NOW have a loud wow?

one…. two…. three…. now:

W O W !

 

Thanks, THAT was good 😉

 

So here it is, may I introduce you to:

The UPSERT

Yes, a funny name…
Nothing to get upset about…
Funny advice…

It is a merge of UPDATE and INSERT

(which is UPDATE and CREATE)

Now the serious explanation.
In case of UPSERT, we can safely quote the spec, because there are 2 lines which are short and easy to understand:

An upsert occurs when the client sends an update request to a valid URL that identifies a single entity that does not exist.
In this case the service MUST handle the request as a create entity request or fail the request altogether.

 

The spec for UPSERT can be found here:
http://docs.oasis-open.org/odata/odata/v4.0/errata03/os/complete/part1-protocol/odata-v4.0-errata03-os-part1-protocol-complete.html#_Toc453752301

BTW, I’ve mentioned that the UPSERT functionality is new in V4, so please turn to the What’s New page for OData V4 if you’re interested:
http://docs.oasis-open.org/odata/new-in-odata/v4.0/cn01/new-in-odata-v4.0-cn01.html#_Toc366145462

 

Until now, the explanation was about how the OData service should behave.

However, for the user of the OData service, the following is also interesting:

A PUT or PATCH request MUST NOT be treated as an update if an If-None-Match header is specified with a value of “*”.

(same spec, same link)

This means, the user doesn’t just send a request to any non-existing URL.
The user moreover must specify the required header.

With other words

UPDATE means:

the user sends an UPDATE request (PUT or PATCH) which points to a single resource.
This resource is specified by its key field in the URL
e.g. People(2)

UPSERT means:

the user sends an UPDATE request which points to a single resource.
This resource is specified by its key field in the URL
However, this resource doesn’t exist.
Furthermore, the user specifies a header field to express that he is intentionally sending a request to a resource which doesn’t exist
If these 2 requirements are met, then the UPDATE turns to an UPSERT.

 

CODE

We’ve been talking about what the user of our service should do.
Now that our user knows how to do an UPSERT, he will definitely do it.
At least, because of the funny name.

So it is now up- to us to -sert.

I mean, now we have to implement the UPSERT.

What do we have to do?

First of all, we have to realize that the FWK helps us.

(As usual, worth another wow)

About the FWK:
The FWK identifies, if the request is an UPSERT, or just a normal UPDATE.

Note that the user sends an UPDATE request (with PATCH or PUT).
So I assume that the FWK first checks the header.
If the If-None-Match header is NOT there, then it is a normal update
In this case, the FWK behaves normally and invokes the @Update implementation
And, BTW, in the @Update implementation it is up to us to check if the single resource exists or not

If the If-None-Match header is there and has value *, then it is an UPSERT.
Then the FWK invokes our @Read implementation for the specified key

Why the FWK calls the READ?

Because it has to identify if the specified entry already exists.
Remember: UPSERT means that the user sends an UPDATE request to a single resource which doesn’t exist

Note that this is a prerequisite: @Read has to be implemented – and it is important that the READ is correctly implemented.
Of course, it is always important to implement everything correctly.
But here the whole UPSERT functionality depends as well on the correct @Read implementation

Let’s continue.
The FWK calls the @Read implementation.

First case:
If the READ is successful and finds that resource
Then the UPSERT fails.
It is correct to fail, because the user want to do an UPSERT (indicated by the header) which means he wants to create that resource to which he’s pointing in the URL
As such, if it already exists, the creation has to fail, so the UPSERT is aborted with an error message.
That is handled by the FWK which gives an appropriate error message:
“Upsert failed as the entity already exists.”

Second case:
If the READ doesn’t find that resource and returns an error response, then the FWK checks the status code of the error:
If the status code is something not interesting, e.g. status 400, then the FWK just returns this error response as implemented by the service developer.
If the status code indicates that the entity wasn’t found, i.e. status 404 – Not Found, only then, the FWK is happy and continues with UPSERT.

I’ve said “continue with UPSERT”, but this doesn’t mean that now the UPDATE method is called.
No, no, no.
In our situation, we want to do a CREATE
As such, the FWK calls our @Create method after the (erroneous) READ

This means that UPSERT is only possible, if the @Create is implemented.
This is another prerequisite

And that’s the end of the UPSERT story:
the @Create method creates the desired entity.

Note:
The user sends a PATCH or PUT request and our @Create method takes care of handling the request.
This implies the following: the request body needs to contain all the properties that are required for a correct CREATE
This might be different from what we learned in a previous blog about PATCH, where the user is encouraged to omit properties from the request body.
However, in case of UPSERT, the user should send all properties (at least those which are required for successful CREATE)

 

Note:
Have you noticed? We’re in the CODE section, but there’s no code snippet.
Yes, that’s correct: no code required.
For UPSERT, we don’t need additional code, only the prerequisites mentioned above must be fulfilled.

Let’s remember:

Prerequisites for UPSERT

@Read must be implemented 
@Read must return error response with status 404 if entity not found
@Create must be implemented

User must send PATCH/PUT request to a resource which doesn’t exist
User must specify the header If-None-Match with value *
User must send the necessary properties in the request body

 

BTW:
For UPSERT, it is not mandatory that @Update implementation exists.

 

Note:
here’s a note which I wouldn’t want to place prominently:
I’ve mentioned that the @Read must be implemented properly and a correct error response must be sent if the entry is not found.
There’s a little bit more FWK-intelligence here: if the @Read implementation returns a success response, but the data is passed as null, or if the setData() is not set at all, then the FWK interprets that as “not found” and implicitly sets the status code 404. And as such, in that case the UPSERT will still work.

 

As usual, you can find the full source code at the end of this page.
However, as you’ve learned, there’s nothing to do, no special requirement. It just contains the basic code for READ, CREATE, UPDATE. As such, to test this, you can as well just use the existing project which we were using for blog 3

 

Examples for failing code

I’ve mentioned that the READ implementation has to be correct.
Let’s point out 2 aspects, where the UPSERT would fail, if the READ isn’t properly realized:

 

Example 1 for wrong implementation:

The READ is successful, which means it has found that resource. Expected would be that NO UPSERT is done.
Now, what if your READ implementation returns success response but implementation is not correct
If you forgot to specify setData() in your READ implementation, then you don’t return the found entity along with your success response
In that case, the framework doesn’t have a valid entry.
As such, the FWK thinks that the entry is not found.
As a consequence, the FWK proceeds with the UPSERT
But this isn’t correct, because that entry in fact does exist.

 

Example 2 for wrong implementation:

The READ returns an error response.
However, the error response is configured with a status code as 400
Reason for such (wrong) implementation could be some generic check in the READ method.
For example, you could think of checking if the key field is higher than the complete size of all People
Why not, for a READ this could make sense
And you could think of setting the status code to 400, why not, it is a bad request.
HOWEVER, the framework WANTS a status code as 404, which means NOT FOUND
And only if the READ returns a 404 , only then the framework continues with the UPSERT

 

Note:
See the end of this page for some secret note

 

Run

Below screenshot shows how to do an UPSERT operation.

We know that the URL http://<yourHost>/<yourproject>/odata/v4/DemoService/People(99) doesn’t exist, because we’ve just deployed our sample service with only 4 entries
We specify the HTTP verb PATCH, but it can be PUT as well
We specify a valid request body, i.e. some new Person data which should be created
We specify the header: If-None-Match with value *

Then we press send.
As a result, we get the status code 201, which is for successful creation (same like after successful POST)
And we get a response body with the newly created Person (same like after successful POST)

 

 

 

 

 

 

Summary

After going through this blog, you’ve learned the new OData V4 feature called UPSERT.
And you’ve also understood how it is treated by the framework and what the service developer is expected to do, in order to support this feature.
And you know how the user of the OData service can trigger an UPSERT

 

Links

Overview of blog series and link collection.

 

Appendix 1: Source code of Model file: DemoService.xml

<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
	<edmx:DataServices>
		<Schema Namespace="demo" xmlns="http://docs.oasis-open.org/odata/ns/edm">
			<EntityType Name="Person">
				<Key>
					<PropertyRef Name="PersonId" />
				</Key>
				<Property Name="PersonId" Type="Edm.Int32"/>
				<Property Name="FirstName" Type="Edm.String"/>
				<Property Name="LastName" Type="Edm.String"/>
				<Property Name="Email" Type="Edm.String"/>
				<Property Name="JobId" Type="Edm.Int32" />
			</EntityType>
			<EntityContainer Name="container">
				<EntitySet Name="People" EntityType="demo.Person"/>
			</EntityContainer>
		</Schema>
	</edmx:DataServices>
</edmx:Edmx>

Appendix 2: Source code of Java class file: ServiceImplementation.java

 

package com.example.DemoProject;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.sap.cloud.sdk.service.prov.api.operations.Create;
import com.sap.cloud.sdk.service.prov.api.operations.Query;
import com.sap.cloud.sdk.service.prov.api.operations.Read;
import com.sap.cloud.sdk.service.prov.api.operations.Update;
import com.sap.cloud.sdk.service.prov.api.request.CreateRequest;
import com.sap.cloud.sdk.service.prov.api.request.QueryRequest;
import com.sap.cloud.sdk.service.prov.api.request.ReadRequest;
import com.sap.cloud.sdk.service.prov.api.request.UpdateRequest;
import com.sap.cloud.sdk.service.prov.api.response.CreateResponse;
import com.sap.cloud.sdk.service.prov.api.response.ErrorResponse;
import com.sap.cloud.sdk.service.prov.api.response.QueryResponse;
import com.sap.cloud.sdk.service.prov.api.response.ReadResponse;
import com.sap.cloud.sdk.service.prov.api.response.UpdateResponse;

public class ServiceImplementation {
	
	private static final List<Map<String, Object>> peopleList = new ArrayList<Map<String, Object>>();

	
	@Query(serviceName="DemoService", entity="People")
	public QueryResponse getPeople(QueryRequest request) {
		List<Map<String, Object>> peopleList = getAllPeople();
		return QueryResponse.setSuccess().setDataAsMap(peopleList).response();
	}

	
	@Read(serviceName="DemoService", entity="People")
	public ReadResponse getPerson(ReadRequest request) {
		Integer id = (Integer) request.getKeys().get("PersonId");
		Map<String, Object> personMap = findPerson(id);
		
		// check if the requested person exists
		if(personMap == null) {
			return ReadResponse.setError(
					ErrorResponse.getBuilder()
					.setMessage("Person with PersonId " + id + " doesn't exist!")
					.setStatusCode(404)  
					.response());
		}
		
		return ReadResponse.setSuccess().setData(personMap).response();
	}	
	
	
	@Create(serviceName = "DemoService", entity = "People")
	public CreateResponse createPerson(CreateRequest request) {

		// extract the data for creation 
		Map<String, Object> requestBody = request.getMapData();
		Integer personId = (Integer) requestBody.get("PersonId");  
		String firstName = (String) requestBody.get("FirstName"); 
		String lastName = (String) requestBody.get("LastName"); 
		Integer jobId = (Integer) requestBody.get("JobId");
		String mail = (String) requestBody.get("Email");
		
		// do the actual creation in database
		Map<String, Object> createdPerson = createPerson(personId, firstName, lastName, mail, jobId); 
		return CreateResponse.setSuccess().setData(createdPerson).response();  // we have to set the response, because we compute one of the fields in our code
	}
	
	
	@Update(serviceName = "DemoService", entity = "People")
	public UpdateResponse updatePerson(UpdateRequest request) {
		
		// retrieve from request: which person to update
		Map<String, Object> requestBody = request.getMapData();
		
		// retrieve from database: the person to modify
		Integer personIdForUpdate = (Integer)request.getKeys().get("PersonId");
		Map<String, Object> existingPersonMap = findPerson(personIdForUpdate);
		
		// do the update
		Set<String> keySet = requestBody.keySet();
		for (Iterator<String> iterator = keySet.iterator(); iterator.hasNext();) {
			String propertyName = (String) iterator.next();
			if(propertyName.equals("PersonId")) {
				continue; // ignore key field 
			}
			
			// update the other fields
			existingPersonMap.replace(propertyName, requestBody.get(propertyName)); 
		}
		
		return UpdateResponse.setSuccess().response();
	}

	
	
	/* Dummy Database */
	
	private List<Map<String, Object>> getAllPeople(){
		
		// init the "database"
		if(peopleList.isEmpty()) {
			createPerson(1, "Anna", "Martinez", "Anna@mail.com", 1);
			createPerson(2, "Berta", "Hughes", "Berta@webmail.com", 2);
			createPerson(3, "Claudia", "Forrester", "CF@fastmail.com", 1);
			createPerson(4, "Debbie", "Huffington", "debbie@dbmail.com", 2);
		}
		
		return peopleList;
	}
	
	// used for READ operation. Go through the list and find the person with requested PersonId
	private Map<String, Object> findPerson(Integer requiredPersonId){
		List<Map<String,Object>> peopleList = getAllPeople();
		for(Map<String, Object> personMap : peopleList) {
			if(requiredPersonId.equals((Integer)personMap.get("PersonId"))) {
				return personMap;
			}
		}
		return null;
	}
	
	private Map<String, Object> createPerson(Integer personId, String firstName, String lastName, String mail, Integer jobId){
		Map<String, Object> personMap = new HashMap<String, Object>();
		
		personMap.put("PersonId", personId); 
		personMap.put("FirstName", firstName); 
		personMap.put("LastName", lastName);
		personMap.put("JobId", jobId);
		personMap.put("Email", mail);
		
		peopleList.add(personMap);
		
		return personMap;
	}
	
}

 

 

 

Note:
As I promised, here’s a secret note.
This note is a bit longer than others so it requires a separate section.
However, this section is not official, so I’m telling in brackets, please keep it as secret.
Opening bracket:
[
Currently, we have a limitation: as mentioned, in order to achieve an UPSERT, the user has to send all required properties in the request body.

But what if he doesn’t do that?

The background is:
The user sends the UPDATE request and indicates a resource which doesn’t exist. The key property value is in the URL. So the user can omit it in from the request body.
It could as well happen, that the key value in URI and in request body are different (due to laxness on user-side)
We as service developers can react and retrieve the key property value from the URL instead of request body

Example:

http://<host>/DemoProject/odata/v4/DemoService/People(123)

In this example, we should create a Person with PersonId=123

We are talking about the @Create implementation, which should work for both CREATE and UPSERT operations
So, in the code we would need to distinguish between normal CREATE and UPSERT.
In case of normal CREATE, we take the key property value from request body
In case of UPSERT, we take it from the URI
So we would need to do 2 tasks:
Check if we’re invoked for CREATE or UPSERT
In case of UPSERT, retrieve the key property value from URI
As mentioned, this is not official, so in case of questions you can only contact me directly.
Closing bracket:
]