[Building Sakai] Sakai BasicSQLService - Can we add dbUpdate() Methods?

[csev]

On Jul 19, 2011, at 1:06 PM, csev wrote:

> Sakai's BasicSQL Service is missing the ability to handle UPDATE statements. 

More detail:

I need dbUpdate() to support a more RESTFUL (i.e. edit-free) pattern.

If you put data like ID and send it out to a browser and then get it back, you need to verify the ID before you just do an UPDATE.

Imagine table with 

ID Integer AutoIncrement
SITE_ID  String
DATA String

Lets say you put up a list of these things in a UI with edit buttons with an ID=parameter - then on the edit screen you make ID a hidden parameter DATA, make changes to DATA and then you hit Save

If you know if a user is access, maintain, or admin, you can do the following.

For admin they can update any item:

UPDATE table SET DATA='stuff' WHERE ID=5

For maintain they can update any item in the current site (in which they have admin):

UPDATE table SET DATA='stuff' WHERE ID=5 and SITE_ID='123456 (current context)"

I could do a SELECT and then check SITE_ID, and then do the UPDATE if the SITE_ID matches.  But ...

That should be done in a transaction to insure things don't change between the SELECT and UPDATE.  

If I get dbUpdate() and get a sense of the number of rows affected, then I can effectively combine the SELECT and UPDATE into a single command with no need for a transaction.

If I get zero rows updated, it means that the UPDATE failed because ID was bad, or SItE_ID was a mis-match or did not exist.  If I get one row updated - that is goodness.  If I get two or more rows updated, it probably means I need a uniqueness constraint on (ID, SITE_ID).

It is a way to turn the UPDATE into a free and implicit UPDATE.

/Chuck