Amongst other things in my life, I'm writing a framework in PHP to manage a slew of common problems I come up against in every project I tackle. The framework is currently very data-centric, with the largest component (named Data) being part-ORM and part-DDD in it's approach.
The ORM-ness of this component heavily abstracts away queries to the point that the developer doesn't need to know any SQL or even know SQL terminology. The main drive behind this comes from my experience as a DBA/BIDev, where I groaned over many of the poorly constructed queries by SQL amateurs that convoluted things rather than took the simplest most direct approach. As an example, the following code would r
SELECT ... FOR UPDATE from the
user table where
username is 'jennifer':
$userFilter = $userRepository->createBlank(); $userFilter->username()->identifyValue('jennifer'); $userFilter->lockForUpdate(); $user = $userRepository->retrieve($userFilter);
I test my framework against several applications that I'm involved with, meaning I can mock live-fire scenarios to an extent. In recent tests, I came up against the following error:
SQLSTATE[0A000]: Feature not supported: 7 ERROR: FOR UPDATE is not allowed with window functions Failed query is: SELECT * FROM "myView" WHERE "ID"=:fi_ID__ix0 FOR UPDATE;
It turns out that the view
myView contains a column that is generated by an OLAP/window function (i.e.
ROW_NUMBER() OVER(PARTITION BY...), which means that the view cannot be selected for update. The error was generated by PostgreSQL, is entirely legitimate, and is naturally in response to this invalid operation.
What I'm wondering is, what are the common/accepted ways that frameworks handle third-party problems like this? Throw the error message verbatim and let the developer figure it out, or try to value-add to the message by suggesting common problems or avenues of investigation?