I am in the middle of designing a next generation database access class for one of the programs I am building. We use PostgreSQL exclusively.

Our current approach is procedural (largely inspired believe it or not by RESTful web services, but paying attention to discoverability as well). The database defines (rather loosely) an API which the application discovers. The API is designed to be both readily useful and readable for computers and DBA's.

You can read about our current approach at http://ledgersmbdev.blogspot.com/2011/10/introduction-to-soda.html but the basic point is that we look up names only of data and generate procedural calls that are easily mapped to methods. Our Perl classes support a call interface like:

 $recon_report->exec_method({ funcname => 'cash_recon__approve' });

This would re-use object properties to fill in the expected arguments for cash_recon. This allows the API to be changed to a small extent without breaking the basic contract with the application, and thus allows the worst of the stored procedure woes to be avoided. The above might generate a sql query like:

 SELECT * FROM cash_recon__approve(134);

The function definition in PostgreSQL would look like:

 CREATE OR REPLACE FUNCTION cash_recon__approve(in_id int) returns ...

One of the big problems with this approach though is that it is very had to manage large numbers of function names in this way, so I am looking for a more object-like interface around the database. We currently manage this by using double underscores, but this creates about as many problems as it solves. It is slightly better than using nonstandard characters and requiring every function name to be quoted when a dba types it or in test cases though.

The new interface I am looking at would be different. Instead of identifying the type by the portion before the underscore it would be the type used by the first argument. The equivalent to the above would be the much more annoying looking:

SELECT * FROM approve('(35,65,1111111,f,f,2011-10-06,"2011-10-07 13:11:24.324195",186,chris,f,,,,f)'::cash_recon);

The function definition here would look something like:

CREATE OR REPLACE FUNCTION approve(obj cash_recon) RETURNS ....

Such an interface would also be discoverable but it would rely on discovery. It is not as likely that individuals could read the call and understand exactly what it did right away. The major advantage however is that it would allow complex data structures to be passed in, and this would be necessary for procedures that expect to be able to determine whether a transaction is balanced before writing it to the database so there are cases where this is clearly preferable. It requires though that test cases would need to be dynamically generated from the database. Manually written test cases would likely be quite brittle.

So the example above strikes me as a case where the existing stored procedure interface is probably preferable, while posting a journal entry strikes me as one where the approach under development is significantly preferable.

So my first question is, given that these two API's have different strengths and weaknesses, does it raise any red flags to support both and assume that stored procedures may be written to either spec? Or is it generally better to have a smaller spec with only one API approach supported. In other words should the old API be deprecated or used alongside the new one? I am leaning towards the latter but would like to get feedback.

The second question is, assuming we do support both, is it better to discover the form of the API at run-time or to require a different call for the old vs the new style? I am leaning here towards discovery because it allows the functions in the db to be rewritten and remain call-compatible if need be.



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.