Bulk update in client-server application

Question

Sometimes when coding a client-server application, the client needs to make a bulk update. For example: "mark all pending orders as dispatched". To implement these I typically write specific controller methods, which perform the required database operation.

It crossed my mind that theoretically this could be done without specific controller methods. In a simple approach, the client could iterate through the pending orders (which it can already access) and mark each as dispatched (again, it already has a call to do this). The problem with that approach is that it causes many network round-trips, increasing latency.

But this made me wonder, is there some kind of generic client-server query/update language? I would hesitate to allow SQL from client to server, unless I could create some very tight controls to prevent SQL injection. But in principle I want to client to tell the server to "UPDATE orders SET status='dispatched' WHERE status='pending'" - and the server to action this, while enforcing any security controls applicable to the current user.

I figured this probably already has a solution, I just don't know the name of it - hence this question. Any help appreciated.

Edit: on reflection, maybe this is a bad idea. When I explicitly code a controller, I can ensure the database has appropriate indexes to make the update efficient. If the query only exists on the front-end, then ensuring appropriate indexes are in place creates an uncomfortable front/back dependency.

Answer 1

flask-restless supports this behaviour with it's multiple patch feature.

PUT /api/person

Sets specified attributes on every instance of Person which meets the search criteria described in the q parameter.

I still don't know the name for this general technique, but this does what I need.

Answer 2

Look into the SQL COPY command. It can take data as input and insert it into a table. So, SQL injection would not be an issue. You would just have to require a data-format for the file, or build an interface for the user to specify it.

• Adam Zuckerman: This answer doesn't address the question: how can I perform a bulk update from the client?
• paj28: Hi, thanks for the answer, but I'm afraid I have to echo Adam's comment - this doesn't answer my question.
• ginkgoMZD: I think you want to clarify your question. Are you just looking for a name of a concept? I question the need for a name of this concept as a software architecture. After re-reading your question and comments... I think you are just talking about SQL in general. SQL is set-based. Maybe that is the phrase you are looking for. "Here is a set, explicitly or by these characteristics, now do x with it". Every implementation of SQL is client-server architecture.
• ginkgoMZD: It sounds like what you want to do is to have a "bulk operation" mode in your client application that does not trigger an asynchronous update but rather acumulates item ID's. When the user confirms... you send a list of ID's to the server and do an UPDATE orders set status='dispatched' where ID IN (...<insert delimited list of ID's here> ).