mca blog

the announcement that Rails is adding support for PATCH has caused some gum-flapping on the Inter-Tubes and this all came up in a recent convo w/ some of my colleagues today. it turned out most of them don't have much experience in "talking HTTP" but know T-SQL really well. so i decided to illustrate the differences between HTTP's POST, PUT, and PATCH methods using simple T-SQL examples.

it seemed to resonate w/ the folks in the room so i decided to share it here. now, my T-SQL is a bit stilted, but it get ths point across.

hopefully, this illustration will spark some convos where you are and result in some deeper discussion and understanding of the HTTP protocol.

POST

HTTP.POST can be used when the client is sending data to the server and the server will decide the URI for the newly created resource.

"The POST method is used to request that the origin server accept the entity enclosed in the request as a new subordinate of the resource identified by the Request-URI in the Request-Line."

this is what most of us think of when we talk about "creating data" on a web server. translating this into T-SQL is really easy; just use an auto-incrementing primary key for INSERTS.

-- T-SQL version of POST
CREATE TABLE [dbo].[PostToDo](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Task] [nvarchar](1024) NOT NULL,
  [Completed] [nvarchar](10) NULL
) ON [PRIMARY]
 
-- Write ala POST
CREATE PROCEDURE WritePostToDo 
  @Task nvarchar(1024),
  @Completed nvarchar(10)
AS
BEGIN
  SET NOCOUNT ON;
  INSERT INTO PostToDo VALUES (@Task, @Completed)
END

-- Send some data
WritePostToDo 'Write code', 'no'
WritePostToDo 'Fix bugs', 'no'
WritePostToDo 'Deploy to production', 'no'
WritePostToDo 'Enjoy a beer', 'no'

-- Check out the results
SELECT * FROM PostToDo
1,'Write code', 'no'
2,'Fix bugs', 'no'
3,'Deploy to production', 'no'
4,'Enjoy a beer', 'no'

PUT

HTTP.PUT can be used when the client is sending data to the the server and the client is determining the URI for the newly created resource.

The PUT method requests that the enclosed entity be stored under the supplied Request-URI. If the Request-URI refers to an already existing resource, the enclosed entity SHOULD be considered as a modified version of the one residing on the origin server. If the Request-URI does not point to an existing resource, and that URI is capable of being defined as a new resource by the requesting user agent, the origin server can create the resource with that URI.

yeah, so that's kinda dense but the basics are pretty clear:

• client must supply the ID
• if the resource exists, *replace* it with the inbound data
• if it doesn't exist, create a new one (assuming you can do that)

so in T-SQL it just takes a bit more fiddling to determine if the record already exsits. if yes, do an UPDATE. if no, do an INSERT.

-- T-SQL vesrion of PUT
CREATE TABLE [dbo].[PutToDo](
    [ID] [int] NOT NULL,
    [Task] [nvarchar](1024) NOT NULL,
    [Completed] [nvarchar](10) NOT NULL
) ON [PRIMARY]

-- Write ala PUT
CREATE PROCEDURE WritePutToDo 
    @ID int,
  @Task nvarchar(1024),
  @Completed nvarchar(10)
AS
BEGIN
  SET NOCOUNT ON;

  IF(SELECT COUNT(*) FROM PutToDo WHERE ID=@ID)=0
    BEGIN
      INSERT INTO PutToDo VALUES (@ID, @Task, @Completed)
    END
  ELSE
    BEGIN
      UPDATE PutToDo
      SET Task=@Task, Completed=@Completed
      WHERE ID=@ID
    END
  --END IF
  
  SELECT * FROM PutToDo
END

-- Send some data
WritePutToDo 1, 'write code', 'no'
WritePutToDo 2, 'Fix bugs', 'no'
WritePutToDo 3, 'Deploy to production', 'no'
WritePutToDo 4, 'Enjoy a beer', 'no'

-- Check out the results
SELECT * FROM ToDo
1,'Write code', 'no'
2,'Fix bugs', 'no'
3,'Deploy to production', 'no'
4,'Enjoy a beer', 'no'

-- Send some more data
WritePutToDo 1,'write code','yes'

-- Check out the results
SELECT * FROM ToDo
1,'Write code', 'yes'
2,'Fix bugs', 'no'
3,'Deploy to production', 'no'
4,'Enjoy a beer', 'no'

PATCH

HTTP.PATCH can be used when the client is sending one or more changes to be applied by the the server.

The PATCH method requests that a set of changes described in the request entity be applied to the resource identified by the Request-URI. The set of changes is represented in a format called a "patch document"...

now we get to have some fun!

the spec clearly states this should involve a "patch document", not a typical resource write like in POST and PUT (interpret how you will...). anyway, the point is that PATCH is used to doing some kind of 'partial' update.

translating this into T-SQL is not too tough. i decided to use a 'patch document' that consists of four things:

• ID of the record
• the FIELD to patch
• the OLDVALUE of the FIELD
• the NEWVALUE of the FIELD

this allows the sproc to confirm that the field i want to modify has not already been modified by someone else since i last read in the data. that will cut down on problematic patches that might render the row invalid (again, interpret how you will...).

-- use the PutToDo Table definition

-- Write ala PATCH
CREATE PROCEDURE WritePatchToDo 
  @ID int,
  @Field nvarchar(50),
  @OldValue nvarchar(1024),
  @NewValue nvarchar(1024)
AS
BEGIN
  SET NOCOUNT ON;

  IF(SELECT COUNT(*) FROM PutToDo WHERE ID=@ID)!=0
    BEGIN
      IF(@Field='Task')
        BEGIN
          IF(SELECT Task from PutToDo WHERE ID=@ID)=@OldValue
            Update PutToDo
            SET Task=@NewValue
            WHERE ID=@ID
          --ENDIF
        END
      --ENDIF
      IF(@Field='Completed')
        BEGIN
          IF(SELECT Completed from PutToDo WHERE ID=@ID)=@OldValue
            Update PutToDo
            SET Completed=@NewValue
            WHERE ID=@ID
          --ENDIF
        END
      --ENDIF
    END
  --ENDIF
END

-- send some data 
WritePatchToDo 1, @Field='Task',
    @OldValue='write code',@NewValue='write buggy code'

-- Check out results
SELECT * FROM ToDo
1,'Write buggy code','yes'
2,'Fix bugs', 'no'
3,'Deploy to production', 'no'
4,'Enjoy a beer', 'no'

does that help?

hopefully, seeing HTTP concepts translated to another form helps folks get a handle on the differences between these HTTP methods. T-SQL made sense to the group i was talking to at the time. i wonder if there are other "translations" that would be helpful, too.

maybe you can show me?