Have you ever been asked to write a WebReport to manage tables for ADN or Attribute Extension Table Key Lookup (TKL) tables and values? I had such a task for a client a few months back. One of the key challenges was allowing users to add, delete, change values not just on items with TKL attributes but the values available in the TKL tables themselves. Using “out-of-box” WebReports and LiveReports, it’s not the easiest thing in the world to write an app that can do updates to SQL tables that is both flexible (works for any possible TKL attribute), safe (not vulnerable to SQL injection), and multiplatform (works for all manner of special characters and quotes in all major databases).
In fact, I was not at all satisfied with the “out-of-box” WR app, and felt it could be done better and more cleanly if only I had an API to manage the TKL tables that is abstracted away from the Web Report. So, I wrote a REST API for TKL tables. You have a bit of set up to do, i.e. defining any identity or logical delete fields in your tables, mapping default values for any queries, etc. but with this API, you can write a WebReport that can query TKL tables, and update the attribute values without the WR application needing to know anything about the underlying tables – all that is defined in the configuration. Also, all the underlying SQL is done using bind variables rather than literal text, which is a safety feature against SQL injection. The calling app merely states to change/add/delete this value for this named attribute.
One obvious question would be why would a client want this. Why wouldn’t the DBA merely update the TKL tables in SQL Server Manager or JDeveloper or SQL+? For this client, the answer is that the folks making decisions about TKL values were not DB admins and needed a business friendly way to manage the underlying tables that underpinned their metadata model.
I’d be curious to know if others see a use for such an API. So far, I’ve tested it on SQL Server 2012 and Oracle 11g for Content Server 10.0. I plan on releasing for other versions if there is sufficient interest.