Keeping the API happy - SQL utilities cleanup

With IRIS 2021.1, we released a significant revision our SQL utilities API at %SYSTEM.SQL. Yes, that's a while ago now, but last week a customer asked a few questions about this and then @Tom Woodfin applied gentle mental pressure ;-) to make me describe the rationale of these changes in a little more detail on the Developer Community. So here we go!

By early 2020, the %SYSTEM.SQL API had grown from a helpful class wrapper around a key routines to a big pile of not-so-coherent entry points for diverse SQL-related functionalities. That shouldn't be a surprise, as the IRIS SQL engine (and actually the Caché SQL engine before that) had grown massively in terms of capabilities and usability. As we care a lot about backwards compatibility, almost every change to the API was a net addition, a new method or an extension of a method's signature. In some cases this goal of backwards compatibility meant an intent to simplify things by removing a method argument (for something that was now automated) got watered down into deprecating the argument in the class ref, but leaving it in place as to not upset existing code using it. In other words, the API began to resemble a famous Italian dish that is not pizza.

If by now you think: "oh boy, you've made such a mess, that would never happen to us!" - you can stop reading here. 

... I think you're still there :-)

It's probably fair to say this kind of organic growth is unavoidable and in a way not necessarily a bad thing, as it means you've made progress and you care about your users as you don't destroy their code by adapting the API between every release. But at some point you have to take out the big broom and clean up. And that's what we did in 2020.3 for the IRIS SQL utilities.

Sorting things out

The first problem we wanted to tackle was the sheer quantity of methods, which were way too many for a single API to be navigable. So we started by dividing the class up into a number of smaller, more focused API classes in the %SYSTEM.SQL package, which meant that we could retain the nice $SYSTEM shorthand syntax.

• %SYSTEM.SQL.Functions – in case you didn’t know, %SYSTEM.SQL had ObjectScript equivalents to all of the simple scalar functions in IRIS SQL, such as %SYSTEM.SQL.ABS().
• %SYSTEM.SQL.PTools – the Performance Analysis Toolkit is a group of utilities for deep analysis of individual query performance. This advanced toolkit is primarily used by InterSystems Support and experienced customers, but it’s a powerful and well-documented API so worth a look if you need to figure out that one darned query
• %SYSTEM.SQL.Schema – checking for table existence, importing or exporting DDL statements, and other functions to look into or alter schema objects are now here.
• %SYSTEM.SQL.Security – this groups ObjectScript entry points for the GRANT and REVOKE commands
• %SYSTEM.SQL.Statement – as you probably are aware, InterSystems IRIS includes comprehensive management and caching for SQL statements. This class groups methods for managing the contents of the Statement Index, such as importing and exporting plans, as well as freezing and thawing them if you’d like to force use of a particular one.
• %SYSTEM.SQL.Stats.Table – This is where you collect table stats and optionally override, import or export them. It’s one level deeper than the others, as there are other elements we’d like to collect and manage statistics about in the future.
• %SYSTEM.SQL.Util – Not everything fits in a clean themed bucket, so this is where we put the leftovers: functionality we wanted to retain from %SYSTEM.SQL, but didn’t find a more suitable home for. Only a very small number of extremely common tasks, such as %SYSTEM.SQL.Explain(), were left in the top-level class.

Of course we still care about existing users with code calling the pre-existing entry points, so we’ve left all the original methods in the %SYSTEM.SQLclass and marked them as internal and deprecated using method keywords, leaving a note about the new, preferred location of the functionality.

One advantage of this approach with new classes is that we didn’t have to worry about the new getting in the way of the existing. In most of our REST APIs (such as /api/deepsee/ and /api/iknow/*), we baked a version number into the APIs URL itself (e.g. /api/iknow/v1/namespace/domains) to take care of such API changes. We briefly played with that idea here too, but thought it would look really awkward in a class name and not quite invite users to adopt it over the existing-yet-deprecated base entry point. The new entry points are also a tat longer, but at least the extra token makes sense with respect to what you’re trying to accomplish. Implicitly, this means we’re somewhat optimistically assuming we’ll never have to change these APIs again, so let’s take a look at the other main change we made: reviewing method signatures.

Deep cleaning

As described in the introduction, we weren’t just looking at a sprawling number of methods, some had a sprawling method signature by themselves. Good examples of those were the TuneTable() and Export() methods where a fair number of flags had been added over the years. Usually, the most interesting arguments are the newer ones at the end, and I regularly saw even the most seasoned developers count the commas before setting that 1 or 0 to override the (hopefully decent) default. That’s clearly an area we could improve, and so we did.

The first question to ask is obviously whether each argument was worth keeping. Some had been overtaken by time and new functionality, and could be safely left out. Then, we put the most important, mandatory ones at the start and bundled all of the optional flags in a new qualifiers argument, similar in nature the qualifiers used in several %SYSTEM.OBJ methods. We support dynamic object or legacy format. The former can be passed in as a %DynamicObject instance, or in JSON string format, such as { “silent”: 1, “logFile”: “/tmp/xyz.log" }. The latter uses the same format as in %SYSTEM.OBJusing slashes, in which the previous qualifiers could be expressed as “/silent /logFile=/tmp/xyz.log”. This mechanism has been a great way to offer easy-to-use, easy-to-document and easy-to-evolve API methods.

Next to changing the argument list, there was some more low-hanging fruit while we were at it, such as a more standardized use of %Status return values and ByRef parameters. And last but not least, changing the method names to be more uniform and self-explaining, often facilitated by the move to a reasonably-named subpackage. Did you know without looking whether the old %SYSTEM.SQL.Export() method exported data or schema information? I rest my case!

Forward thinking

Are we done? Definitely not. I’m very grateful for the time the team spent on reviewing this change’s specification, implementation and testing, but by the time we released this with 2020.3, we already identified a few cases where we could have gone a little further or made things even simpler. We’ll continue to look out for such opportunities and, now that the vast majority of this first big cleanup has been implemented, we can pragmatically push smaller changes that fit the guidelines described above.

If you’re looking for a reason why on earth you previously could call %SYSTEM.SQL.FreezePlans(1, 3, “MyTable”) and now are recommended to use %SYSTEM.SQL.Statement.FreezeRelation(“MyTable”), I hope this was helpful. If you were looking for general API evolution advice, I’m hoping this has at least not felt as a waste of your time :-) and I’m happy to hear your thoughts on how we did it and how you’d do it differently. There is absolutely no rocket science in the above. We took a pragmatic approach, trying to make things easier to use without changing methods for the heck of it and trying to be economical on development and test time. We only hope eventually it’ll also save you time in learning and using the API, and especially save the time you spend on counting commas!