You pretty much have the view trick. Its a set of dynamically
generated rules for views created to allow inserts into the view which do cascaded inserts into the parent tables.
It's not an entirely general solution to invoking arbitrary
plsql but on the other hand it makes building up
content types from metadata quite easy. The end
result is anyone creating a content revision only has to do
a normal insert statement.
Its all in acs-content-repository/sql/{postgresql,oracle}/content-type.sql
and in particular content_type__create_type, content_type__create_attribute, content_type__refresh_view.
In addition the cms system has code for building forms and
performing the necessary dml for adding and editing content.
It needs a little extension work since it does not support
multiple forms per content type but it does work reasonably well already. So if you actually supply all the metadata thats needed you don't end up writing any dml let alone plsql.
Some of the work I am doing now will expose that API so that
packages using the CR but not CMS will actually be able to
use the cms automatic form generation/dml but not the full blown CMS api.
I looked at query-writer but I didn't really understand it
completely. It would be useful if you had more sample code
somewhere (although I guess since the metadata is all in
the DB that's not so straightforward as it sounds right?)