Monday 25 July 2011

Generated columns and updatable views

When developing the conceptual level of a database, new structure can be added by using views and generated columns. The programming model for this in the SQL standard is theoretically complete, but in practical examples the standard machinery using triggers can seem over-complex.
Recent programming frameworks (e.g. .NET 4) have focussed instead on providing up to 4 pieces of SQL for the GET, PUT, POST and DELETE operations for any named structure. This suggests an extension to the CREATE VIEW syntax as follows:
CREATE VIEW id AS QueryExpression [UPDATE SqlStatement] [INSERT SqlStatement] [DELETE SqlStatement]
Similarly the syntax for a generated column can be extended:
GenerationRule = GENERATED ALWAYS AS '('Value')' [UPDATE '(' Assignments ')' ]
The implementation of these ideas is extremely straightfoward so they are good candidates for inclusion in the next version of Pyrrho.
More importantly, together with the following syntax extensions:
SqlStatement =| HTTP (POST|UPDATE|DELETE) url ['('Value')']
QueryExpression =| HTTP GET url .
we can see how a local database include or refer to data selected from an external database.
It is quite interesting to consider the effect of such extensions on the transaction model. One reasonable approach would be to recommend that for long transactions database designers should use Web-based transaction control as in JAX-RS. Another reasonable suggestion would be that such web requests should be tunnelled in duplex streams established for each web host referenced in the transaction, and this would be quite suitable for short transactions. For example, instead of the sequence
PUT http://myhost/mydb/url1 (+20)
PUT http://myhost/mydb/url2 (-20)
POST http://myhost/mydb/strtrans (PUT /url1 (+20) PUT /url2 (-20) COMMIT)

No comments:

Post a Comment