Motivation

In the ADQL-entry screen that is part of the TAP interface, we already provide UI support for a schema browser, allowing the insertion of table and column names into the ADQL text.

It would also be useful to provide UI support for the identification of JOINs supported by the schema of the published tables.

Experience in Rubin DP0.1 already has shown quite clearly the high demand for JOIN queries based on the DP0.1 schema. This is likely to continue into DP0.2 and it would be good to provide some additional support.

There is (or will be) a separate ticket requesting a full query-form support for JOINs. That is quite a challenging problem, so in the present ticket I propose a simpler UI that supports interactive generation of JOINs in the existing ADQL entry screen, based on key and key-column data in TAP_SCHEMA.

This ticket would require parsing the relevant TAP_SCHEMA data, so it would also be a first step toward the implementation of the more challenging fully-form-based JOIN screen envisioned.

It would be a powerful tool for expert users to use to discover the availability of JOINs directly from the Portal, rather than merely from a separate manual describing the data products.

TAP_SCHEMA data explicitly describes the connections between tables that can be made on a TAP service via traditional key-equality JOINs. This data supports both simple and composite foreign keys.

Proposal

In the existing schema browser in the ADQL-entry screen, there are "Table" nodes. Under the table nodes, lists of columns appear.

After implementing this feature, for any table for which TAP_SCHEMA.keys reports an available JOIN, a new "JOINs available" node would appear under that table, preceding all the columns entries and on the same level. Underneath that node would appear the names of all the "target" tables to which a JOIN could be made. Clicking on a "target" table name would insert only its name at the current insertion point in the ADQL entry field.

Underneath a target-table node the following entries would appear: first, a "(jump to table)" entry, which when clicked would move the schema browser to the main entry for that table; next, one entry for each key entry in TAP_SCHEMA.keys that connects the source and target tables. Generally there would only be one such key, but the TAP_SCHEMA schema allows for more than one. The key's key_id from TAP_SCHEMA would appear as the key's node string, with a mouse-over for the key description. Clicking on the key name would insert the text "INNER JOIN target_table_name ON source_key = target_key" at the current insertion point in the ADQL entry field, as determined from the information in TAP_SCHEMA.key_columns that is linked to the selected key.

Validation that the insertion point is in the correct place in the ADQL for this text is beyond the scope of this ticket. ADQL-parsing is NOT a requirement of this ticket. (For information's sake, the INNER JOIN should immediately follow a FROM clause for the SELECT.)

For tables with no documented JOINs in TAP_SCHEMA, there would be no visible changes in appearance or behavior of the ADQL-entry screen.

For Rubin this capability would be usable for DP0.2, either initially or as an upgrade during 2022.

To have it "go live" would require verifying that the Felis-processing code is capable of filling out the TAP_SCHEMA.keys and TAP_SCHEMA.key_columns tables.