Enumeration support in ADQL

[tcivera]

An enumeration is a way to assign human-readable names to specific constant values (often integers). They improve query readability, make queries self-explanatory, and reduce errors, while they also can allow the underlying data to be stored efficiently as integers instead of text.

At CEFCA/OAJ, we have been using enumerations in our TAP service for years, and they are widely appreciated by our users. The syntax we use is:

enumeration_name::item_name

Examples of enumeration usage

1. Accessing elements in array columns

In our service, object magnitudes are stored in arrays, where each position corresponds to a different filter. So, for example, if an user wants to access to the magnitude of filter rSDSS without enumerations, they must write:

SELECT mag_auto[1] ...

This is less readable and forces users to remember the position of each filter, whereas with enumerations they can write a clearer query that reduces the likelihood of mistakes, such as:

SELECT mag_auto[jplus::rSDSS] ...

Where:

jplus = enumeration defining the set of filters
rSDSS = specific filter in the enumeration

2. Representing calibration methods, external catalogue names, etc.

In some of our data releases, we provide different image zero points obtained using different methods or we provide the crossmatch of our data with external catalogues. Instead of storing their names as text, we store them as integers, which is more efficient and avoids inconsistencies (e.g., slightly different strings referring to the same method or catalogue).

So, for example, if a user wants to retrieve only the zero points obtained using one of our calibration methods (the Stellar and White Dwarf Loci method), without enumerations they must write:
SELECT ZPT, ERRZPT,... FROM jplus.CalibTileImage WHERE CALIB_PROCEDURE = 8

Whereas with enumerations they can write:

SELECT ZPT, ERRZPT,... FROM jplus.CalibTileImage WHERE CALIB_PROCEDURE = calibration_method::SWDL

In conclusion, I would like this concept to be considered for inclusion in the ADQL standard, as I believe it could be useful not only in our case but also for other data centers and archives.

[msdemlei]

On Mon, Aug 04, 2025 at 02:42:05AM -0700, Tamara Civera wrote: In conclusion, I would like this concept to be considered for inclusion in the ADQL standard, as I believe it could be useful not only in our case but also for other data centers and archives.

Do you have a grammar fragment for the enumerations? [personal note: I would frankly prefer some syntax that lets the parser know that it's an enumeration (rather than, say, a column reference) right away. Perhaps :: could be a prefix operator, say? But then that's perhaps not important enough to change something your users have already learned]. More importantly: How do people/clients discover what enumerations there are and what names are in them? Should we think about symbolic names for strings, too (to ward off typos) or would that muddy the water?

[mbtaylor]

It's currently documented as a TAPRegExt languageFeature like this:

     <languageFeatures type="ivo://cefca/std/exts#extra-adql-keywords">
        <feature>
          <form>ENUMERATION</form>
          <description>Enumerations, it is a form of assign a set of names to a range of values. Writing a enumeration in a query is done by writing the enumeration name, the symbol :: and the item name of the element in the enumeration that we want to use. That is equivalent to write the associated value of the item and it is useful when accessing an concrete element in a array column or using it as a constant. See the enumerations available here: https://archive.cefca.es/catalogues/jplus-dr3/help_adql.html.</description>
        </feature>
      </languageFeatures>

which I'd say works pretty well for discoverability, at least from TOPCAT's TAP GUI (look under the ADQL tab).

On syntax, doesn't that embedded "::" do the job of syntactically distinguishing it from other language elements?

[tcivera]

Yes, exactly, thank you for the comment. As you pointed out, we currently expose the existence of enumerations through TAPRegExt, and this works quite well for making them discoverable to users. However, ideally, it would be great if the standard also allowed specifying the full definition (the enumerations available and their elements) directly in a standardized way.

Regarding the syntax, in our implementation, the parser we have developed (which translates the enumeration into the corresponding numeric value before sending the query to the database) can correctly identify enumerations thanks to the use of the :: operator, which clearly distinguishes them from other language elements.

[gmantele]

In brief, I like this idea.

Now, in details:

1. I don't have any strong opinion about the syntax (i.e. PREFIX::NAME). Postgres seems to be the only one to use the operator :: ; it is then used to cast a value into a different datatype (equivalent to CAST(... AS ...)). So I'd say we could keep this one ; at least your users won't have to change anything in the way they use these enumerations. As @mbtaylor , it is unique enough to not conflict with anything else.

2. Existing DBMS (e.g. Postgres, MySQL) already propose enumerations in a quite common way: CREATE TYPE jplus AS ENUM ('rSDSS', ...) (which won't be visible and possible in ADQL) and then MAG_AUTO['rSDSS'] < 16. But with the respect I have for these DBMS, I don't like this syntax as there is no difference between a string literal and an enumeration. At least, with the syntax you propose, it is clear an enumeration is used, whatever is its datatype.

3. I agree that enumerations should be declared. What you did in the Capabilities is already a good start. If we want to go further why not changing your current language feature in something like this?

   It could probably be improved by adding a <datatype> child, but I think the syntax used below is already an improvement that does not imply a change in TAPRegExt.

   Then, as we did for the language features declaring UDF, we could say that the <form> value should use the syntax PREFIX::NAME).

   <languageFeatures type="ivo://ivoa.net/std/TAPRegExt#features-enumeration">
       <feature>
           <form>jplus::rSDSS</form>
           <description>Optional description (and eventually datatype) here.</description>
       </feature>
       <feature>
           <form>jplus::gSDSS</form>
       </feature>
       ...
       <feature>
           <form>jplus_img_flag::ASTROMETRY_PROBLEM</form>
       </feature>
       ...
   </languageFeatures>
   

   What do you all think about this?

[msdemlei]

On Thu, Aug 28, 2025 at 02:54:36AM -0700, Grégory Mantelet wrote: Then, as we did for the language features declaring UDF, we could say that the `<form>` value should use the syntax `PREFIX::NAME`). ``` <languageFeatures type="ivo://ivoa.net/std/TAPRegExt#features-enumeration"> <feature> <form>jplus::rSDSS</form> <description>Optional description (and eventually datatype) here.</description> </feature> <feature> <form>jplus::gSDSS</form> </feature> ... <feature> <form>jplus_img_flag::ASTROMETRY_PROBLEM</form> </feature> ... </languageFeatures> ``` What do you all think about this?

I think that can work,in particular if we request one languageFeatures element per enumeration (i.e., not mix different enumerations within one element). What I'm missing is a way to explain what the whole feature is ("Designations of SDSS filters"; "Flags for image processing"). I'll touch TAPRegExt soon anyway; what if we simply added a description element to tr:LanguageFeatureList? @mbtailor: Would you have a good place to put that string in? We would also have to explain how to relate the description of the standard key (ivo://ivoa.net/std/TAPRegExt#features-enumeration, in the vstd:StandardKey element) to the description in tr:languageFeatures. That's simple in this case; it might not be as clear for "normal" use of languageFeatures. Perhaps that's a sign that we should have a different mechanism after all...

[gmantele]

I think that can work,in particular if we request one languageFeatures element per enumeration (i.e., not mix different enumerations within one element).

That's what I've wondered when writing this example. I agree that we should have a different languageFeatures for each enumeration, but only if it is not confusing for users or not too complicated for clients to interpret them

What I'm missing is a way to explain what the whole feature is ("Designations of SDSS filters"; "Flags for image processing"). I'll touch TAPRegExt soon anyway; what if we simply added a description element to tr:LanguageFeatureList?

I had the same idea while writing this example. I wanted to avoid touching TAPRegExt, but if you plan to do so, you have my support.

[mbtaylor]

Yes I agree with others that (a) enumerations seems like a good idea, (b) the :: syntax seems like a suitable choice, (c) such enumerations should be declared in one or more <languageFeatures type="ivo://ivoa.net/std/TAPRegExt#features-enumeration"> lists, and (d) the missing piece is how to declare/document an enumeration as distinct from its elements.

In relation to (d) I can think of four options (some of which were introduced in the above discussion):

1. One enumeration per languageFeatures list. This looks nicer for human readabilty of the XML, but (assuming the form element includes the prefix) the hierarchical organisation would be redundant with the PREFIX:: namespacing, so it introduces more possibilities for doing things wrong (more mistakes for validators to check, more errors for clients to choke on or work around).

2. As 1 but with an additional element/attribute documenting the usage or meaning of the enumeration (as others have suggested). Having a new description attribute or something could be done, but this doesn't feel quite right, since the other language features don't need it; it would be annoying for both service providers and consumers if this resulted in redundant chatter like:

  <languageFeatures type="ivo://ivoa.net/std/TAPRegExt#features-udf"
                    description="List of User-Defined Functions">
    ...
  </languageFeatures>

3. One enumeration per languageFeature element. Disadvantage: no per-element documentation. This would look like:

<languageFeatures type="ivo://ivoa.net/std/TAPRegExt#features-enumeration">
  <feature>
    <form>jplus::rSDSS jplus::rSDSS jplus::gSDSS jplus::iSDSS ...</form>
    <description>J-PLUS-DR3 filters</description>
  </feature>
  <feature>
    <form>calibration_method::SWDL calibration_method::GWDL</form>
    <description>Calibration method identifiers</description>
  </feature>
</languageFeatures>

4. No special measures, just have one enumeration element per languageFeature in one long languageFeatures list. I actually think this would be quite OK. Presumably(?) that's enough machine-readable information for a parser to be able to work with, and I think in most cases it would be pretty obvious to a human what's going on by looking at the PREFIX::VALUE entries.

I'd tend to favour option 4, but I'm not violently opposed to any of them.

Whichever, I can see roughly how I'd present this information in the ADQL tab of topcat's TAP window that informs users about service specifics.

[msdemlei]

On Tue, Sep 02, 2025 at 01:40:35AM -0700, Mark Taylor wrote: 4. No special measures, just have one enumeration element per `languageFeature` in one long `languageFeatures` list. I actually think this would be quite OK. Presumably(?) that's enough machine-readable information for a parser to be able to work with, and I think in most cases it would be pretty obvious to a human what's going on by looking at the `PREFIX::VALUE` entries. I'd tend to favour option 4, but I'm not violently opposed to any of them.

Fine; I'm still sort of sad that operators can't explain what a given enumeration is about and what its "scope" is (i..e, which table(s) it's supposed to be used with). But I understand Mark's concerns about new traps and pitfalls, and clearly I won't write the validator to catch all the invalid permutations of prefixes in various groups. So, unless we find a really compelling reason why we need per-enumeration documentation, I'd say we can go ahead with option 4. This would mean another TAPRegExt-defined feature type (p 8 in the current PDF). I'd be grateful if someone could draft a PR (ahem).

[gmantele]

As Markus said, I understand too the drawbacks declared by Mark about solution 1, although I like its grouping aspect.

I dislike solution 3 as it uses concatenation of prefixed values and we loose the description per element. It is less readable and is more likely error prone (e.g. forgotten separator).

I have an alternative to solution 2 (already derived from 1) to propose (let's call it 'solution 2bis'):

<languageFeatures type="ivo://ivoa.net/std/TAPRegExt#features-enumeration"
                  name="jplus"
                  description="...">
	<feature>
		<form>rSDSS</form>
		<description>Optional description (and eventually datatype) here.</description>
	</feature>
	<feature>
		<form>gSDSS</form>
	</feature>
	...
</languageFeatures>

<languageFeatures type="ivo://ivoa.net/std/TAPRegExt#features-enumeration"
                  name="jplus_img_flag"
                  description="...">
	<feature>
		<form>ASTROMETRY_PROBLEM</form>
	</feature>
	...
</languageFeatures>

The advantage is that there is no risk to get confused with prefixes (as they are no longer repeated for each value). The drawback is the addition of 2 optional attributes into the languageFeatures element: name and description. The first one would be used as prefix for enumerations and will generally be useless (for now) for other language features (although it may be useful for UDF also as a prefix like 'ivoa' or gavo).

What do you think about this alternative (2bis)?

Otherwise, I am ok-ish for solution 4 if we do not have any other nicer alternative. So far, it would be my preferred solution (after solution 2bis).

[gmantele]

Another alternative (5) would be to declare enumerations in the TAP_SCHEMA. From what I see, there is no real obligation to have them declared in TAPRegExt. What can be done is just to declare in TAPRegExt that the Enumeration feature is supported and then, the enumeration elements and there description could be found in the TAP_SCHEMA.

More concretely, it could be something like this:

• in the capabilities:

  <languageFeatures type="ivo://ivoa.net/std/TAPRegExt#features-extra">
      <feature>
          <form>ENUMERATION</form>
      </feature>
  </languageFeatures>

• in TAP_SCHEMA.enumerations:

  prefix	name	description
  jplus	rSDSS	blabla
  jplus	gSDSS	blabla
  jplus_img_flag	ASTROMETRY_PROBLEM	blabla
  ...	...	...

What do you think?

[tcivera]

The different alternatives being proposed and discussed sound very promising, and I agree with the points made about their pros and cons.

From our side, the most important requirement is that enumerations are easily discoverable by both users and clients, in a clear and unambiguous way, making it straightforward to see which enumerations exist and what elements they contain. I believe most of the proposed options could achieve this.

Option 4 could certainly work and has the advantage of simplicity. However, if it turns out to be feasible, I think option 2bis would be ideal, as Gregory pointed out: having separate enumerations improves readability and avoids the repetition of prefixes, while also keeping the structure cleaner and allowing the addition of descriptions for both enumerations and their elements.

Option 5 is also interesting, but I feel it might lose some of the immediate discoverability compared to a more compact declaration in TAPRegExt.

In any case, I am open to the different approaches.

[pdowler]

I think I missed something in this discussion.... from the original use case, how does a client discover that values from an enumeration can be used as indices in an array access?

Is there some column metadata that refers to the declared enumeration (by name)?

And/or: what if the values in a column are the terms from the enumeration? Does the reference differentiate?

And quite likely peripheral be seems comparable: a column with enum values is quite like a column with vocabulary terms... should refer to enum and refer to vocabulary be similar?

[msdemlei]

On Mon, Sep 08, 2025 at 01:55:51PM -0700, Patrick Dowler wrote: I think I missed something in this discussion.... from the original use case, how does a client discover that values from an enumeration can be used as indices in an array access?

My feeling is that linking enumerations (that conceptually are just symbolic names for integers) with the columns they are intended for, be it for comparisons ("WHERE obs_status=GAVO::broken") or for array access, is on the bad side of the cost/benefit axis. When people write queries, it is not unreasonable to hope they'll read the column descriptions that could contain such information.

And/or: what if the values in a column are the terms from the enumeration? Does the reference differentiate?

I don't quite understand this question; are you asking if having GAVO::broken *as a string* could be a column value and if that's covered by enums? If so: No, I'm sure enums should just be symbolic names for integers, just like in C.

And quite likely peripheral be seems comparable: a column with enum values is quite like a column with vocabulary terms... should refer to enum and refer to vocabulary be similar?

I'd suggest a separation of concerns: For integers, there's enums, for strings, there's vocabularies.

[gmantele]

A good question because in my mind an enumeration could be for integers but also for strings (or anything else). Then, it could be used as an array index or a special value for a column offering a limited set of, for instance, string values (e.g. flags, telescopes, surveys, ...).

In any case, users would have to look at the documentation (description) to know how to use them. In an ideal world, an attribute could be added to specify the datatype, but for the introduction of the feature, it would be OK, according to me, if it is just said in the description of the enumeration.

[msdemlei]

On Mon, Sep 15, 2025 at 01:16:10AM -0700, Grégory Mantelet wrote: gmantele left a comment (ivoa-std/ADQL#104) A good question because in my mind an enumeration could be for integers but also for strings (or anything else). Then, it could be

Hm. Then, I feel, we shouldn't call them "Enumerations" but rather "symbolic names" or perhaps, for symmetry reasons, "user defined names" (UDNs?). My preference, I think, would be to keep them enums, but that's mainly because I'm worried about the implications for the grammar. Tamara: I don't remember having seen concrete grammar rules -- have I missed them? If not, could you contribute them?