RegTAP.tex

\documentclass[11pt,a4paper]{ivoa}
\input tthdefs
\input gitmeta

\tolerance=6000
\hbadness=6000

\usepackage[utf8]{inputenc}
\usepackage{longtable}
\usepackage{listings}
\usepackage{todonotes}
\lstloadlanguages{XML,SQL}
\lstset{flexiblecolumns=true}

\definecolor{rtcolor}{rgb}{0.15,0.4,0.3}
\definecolor{tapcolor}{rgb}{0.4,0.1,0.1}

\iftth
\def\rtent#1{\texttt{\color{rtcolor}\verb|#1|}}
\else
\makeatletter
\renewcommand\l@subsection{\@dottedtocline{2}{1.8em}{3em}}
\def\makeunderscoreletter{\catcode`\_=12}
\def\makeunderscoresubscript{\catcode`\_=8}
\def\rtent{\makeunderscoreletter\relax\rt@nt}
\def\rt@nt#1{\texttt{\color{rtcolor} #1}\makeunderscoresubscript{}}
\makeatother
\fi
\newcommand{\tapent}[1]{\texttt{\color{tapcolor} #1}}

\ivoagroup{Registry}

\author[http://www.ivoa.net/cgi-bin/twiki/bin/view/IVOA/MarkusDemleitner]{Markus Demleitner}
\author[http://www.ivoa.net/cgi-bin/twiki/bin/view/IVOA/PaulHarrison]{Paul Harrison}
\author[http://www.ivoa.net/cgi-bin/twiki/bin/view/IVOA/MarcoMolinaro]{Marco Molinaro}
\author[http://www.ivoa.net/cgi-bin/twiki/bin/view/IVOA/GretchenGreene]{Gretchen Greene}
\author[http://www.ivoa.net/cgi-bin/twiki/bin/view/IVOA/TheresaDower]{Theresa Dower}
\author[http://wiki.ivoa.net/twiki/bin/view/IVOA/MenelaosPerdikeas]{Menelaos Perdikeas}


\editor{Markus Demleitner}

\previousversion[https://www.ivoa.net/documents/RegTAP/20240124]{PR-1.2-20240124}
\previousversion[https://www.ivoa.net/documents/RegTAP/20220519]{WD-1.2-20220519}
\previousversion[https://ivoa.net/documents/RegTAP/20191011]{REC-1.1}
\previousversion[https://ivoa.net/documents/RegTAP/20190911]{PR-20190911}
\previousversion[https://ivoa.net/documents/RegTAP/20190529]{PR-20190529}
\previousversion[https://ivoa.net/documents/RegTAP/20190326]{PR-20190326}
\previousversion[https://ivoa.net/documents/RegTAP/20180731]{PR-20180731}
\previousversion[https://ivoa.net/documents/RegTAP/20171206]{WD-20171206}
\previousversion[https://ivoa.net/documents/RegTAP/20141208]{REC-1.0}

\title{IVOA Registry Relational Schema}

\begin{document}

\begin{abstract}
Registries provide a mechanism with which VO applications can
discover and select resources -- first and foremost data and
services -- that are relevant for a particular scientific problem.
This specification defines  an interface for searching this resource
metadata based on the IVOA's TAP protocol.  It specifies a set of tables
that comprise a useful subset of the information contained in the
registry records, as well as the table's data content in terms of the
XML VOResource data model.  The general design of the system is geared
towards allowing easy authoring of queries.
\end{abstract}


\section{Introduction}

\label{intro}

In the Virtual Observatory (VO), registries provide a means for
discovering useful resources, i.e., data and services.  Individual
publishers offer the descriptions for their resources (``resource
records'') in publishing registries.  As of March 2024, there are
almost 29000 such resource records active within the VO, originating
from about 50 publishing registries.

The protocol spoken by these
publishing registries, OAI-PMH \citep{std:OAIPMH}, only allows restricting queries by
modification date and identifier and is hence not suitable for data discovery.
Even if it were, data discovery would at least be fairly time consuming if
each client had to query dozens or, potentially, hundreds of
publishing registries.

To enable efficient data discovery nevertheless, there are services
(``searchable registries'') harvesting the
resource records from the publishing registries and offering rich query
facilities to Registry clients.
Version 1.0 of the IVOA Registry
Interfaces specification \citep{2009ivoa.spec.1104B} defined, among other aspects of
the VO registry system, a standard interface for such services.
Built on SOAP and an early draft of an XML-based query language,
this first attempt was quickly obsoleted by parallel
developments in the VO.  It was then decided to have searchable
registries specified outside of Registry Interfaces.

This document provides one such specification, based in particular on TAP \citep{2010ivoa.spec.0327D}
and ADQL \citep{2023ivoa.spec.1215M}.  It follows the model of ObsCore
\citep{2017ivoa.spec.0509L} of defining a representation of a data model
within a relational database.  In this case, the data model is a
simplification of the VO's resource metadata interchange representation,
the VOResource XML format \citep{2018ivoa.spec.0625P}.  The simplification
yields a schema with 18 tables.  For each table, \tapent{TAP\_SCHEMA} metadata is given together
with rules for how to fill these tables from VOResource-serialized
metadata records as well as conditions on foreign keys and
recommendations on indexes.

The resulting set of tables has a modest size by today's standards,
but is still non-trivial.  The largest table, \makeunderscoreletter\rtent{table_column},
has about a million rows at the time of writing.

The architecture laid out here allows client applications to perform ``canned''
queries on behalf of their users as well as complex queries formulated
directly by advanced users, using the same TAP clients they employ to
query astronomical data servers.


\subsection{Terminology and Syntactic Conventions}

\label{terms}

The set of tables and their metadata specified here, together with
the mapping from VOResource (``ingestion rules'') is collectively
called ``relational registry schema'' or ``relational registry'' for
short, with a standard schema name of \tapent{rr}.

The specificiation additionally talks about how to embed these into TAP
services, gives additional user defined functions, talks about
discovering compliant services, etc.  Since all this is tightly coupled
to the ``relational registry'' as defined above, we do not
introduce a new term for it.  Hence, the entire standard is now known as
``IVOA registry relational schema''.

Historically, we intended to follow the ObsCore/ObsTAP model and
talked about RegTAP.  As changing this acronym is technically painful
(e.g., identifiers and URLs would need to be adapted), we kept it even after
the distinction between the schema and its mapping on the one hand and
its combination with a TAP service on the other went away.  This
means that the official acronym for ``IVOA registry relational schema'' is
RegTAP.  This aesthetic defect seems preferable to causing actual
incompatibilities.

Since RegTAP mentions concepts from several different but related
domains, we try to give typographic hints as to the nature of entities
discussed:

\begin{itemize}
\item Names of tables, columns, and functions of the relational registry
are written in \rtent{green typewriter}.

\item Names coming from generic TAP are written in \tapent{brown
typewriter}.

\item VOResource concepts are written in \vorent{caps and small caps}
(where small caps correspond to lowercase letters in element names of
the XML serialisation).

\item XML literals (like tag, attribute or XSD type names or special
values) are written in \xmlel{cursive typewriter}.

\end{itemize}


\subsection{The Relational Registry within the VO Architecture}

\label{rolewithinivoa}


\begin{figure}[th]
\begin{center}
\includegraphics[width=0.95\textwidth]{role_diagram.pdf}
\end{center}

\caption{IVOA Architecture
diagram with the IVOA Registry Relational Specification (shown as
``RegTAP'') and the related standards.}
\end{figure}

This specification directly relates to other VO standards in the
following ways:


\begin{bigdescription}
\item[VOResource, v1.1 \citep{2018ivoa.spec.0625P}] This standard
sets the foundation for a formal definition of the data
model for resource records via its schema definition.  This document
refers to concepts laid down there via xpaths \citep{std:XPATH}.  Since
its version 1.1, RegTAP
incorporates the concepts from VOResource 1.1 but can represent
VOResource 1.0 instances (within the limits laid out below) as well.
\item[VODataService, v1.2 \citep{2021ivoa.spec.1102D}] VODataService
de\-scribes several concepts and resource types extending
VOResource's data model, including
tablesets, data services and data
collections.  These concepts and types are reflected in the database
schema.  Again xpaths link this specification and VODataService.
\item[Other Registry Extensions]Registry extensions are VO standards
defining how particular resources (e.g., Standards) or capabilities
(e.g., IVOA defined interfaces) are described.  Most aspects
introduced by them are reflected in the \rtent{res_detail} table using
xpaths into the registry documents.
The present standard should not in general need updates
for registry extension updates.  For completeness, we note the
versions current as of this specification: SimpleDALRegExt 1.2
\citep{2022ivoa.spec.0222D},
StandardsRegExt 1.0 \citep{2012ivoa.spec.0508H}, TAPRegExt 1.0
\citep{2012ivoa.spec.0827D}, Registry Interfaces 1.1
\citep{2018ivoa.spec.0723D}
\item[TAP, v1.1 \citep{2019ivoa.spec.0927D}]
The queries against the schema defined in the present document, and the results of
these queries, will usually be transported using the Table Access
Protocol TAP.  It also allows discovering
local additions to the registry relations via TAP's metadata publishing
mechanisms.
\item[IVOA Identifiers, v2.0 \citep{2016ivoa.spec.0523D}]IVOA identifiers are
essentially the primary keys within the VO
registry; as such, they are actual primary keys of the central table of
the relational registry. Also, the notion of an authority as laid down
in IVOA Identifiers plays an important role as publishing registries can
be viewed as a realization of a set of authorities.

\end{bigdescription}

This standard also relates to other IVOA standards:


\begin{bigdescription}
\item[ADQL 2.1 \citep{2023ivoa.spec.1215M}] The rules for ingestion are designed to allow
easy queries given the constraints of the IVOA Astronomical Data Query
Language.  Also, we give some functions that extend ADQL using the
language's built-in facility for user-defined functions.
\end{bigdescription}


\section{Design Considerations}

\label{design}

In the design of the tables, the goal has been to preserve as much of
VOResource and its extensions, including the element names, as
possible.

An overriding consideration has been, however, to make natural joins
between the tables behave usefully, i.e., to actually combine rows
relevant to the same entity (resource, table, capability, etc.).
To disambiguate column names that name the same concept on different
entities (name, description, etc.) and would therefore interfere with
the natural join, a shortened tag for the source object
is prepended to the name.  Thus, a \vorent{description} element within
a resource ends up in a column named
\rtent{res_description}, whereas the same element from a
\vorent{capability} becomes \rtent{cap_description}.

We further renamed some columns and most tables
with respect to their VOResource
counterparts to avoid clashes with reserved words in popular database
management systems.  The alternatives would have been to either recommend
quoting them or burden ADQL translation layers with the task of
automatically converting them to delimited identifiers.  Both
alternatives seemed more confusing and less robust than the renaming
proposed here.

Furthermore, camel-case identifiers have been converted to
underscore-separated ones (thus, \vorent{standardID}  becomes
\rtent{standard_id}) to have all-lowercase column names; this saves
potential headache if users choose to reference the columns using SQL
delimited identifiers.  Dashes in VOResource attribute names are
converted to underscores, too, with the exception of
\vorent{ivo-id}, which is just rendered \rtent{ivoid}.

Another design goal of this specification has been that different registries
operating on the same set of registry records will return identical responses
for most queries; hence, we try to avoid relying on features left not
defined by ADQL (e.g., the case sensitivity of string matches).  However,
with a view to non-uniform support for information retrieval-type
queries in database systems, the \rtent{ivo_hasword} user defined
function is not fully specified here; queries employing it may yield
different results on different implementations, even if they operate on
the same set of resource records.


\section{Primary Keys}

\label{primarykeys}

The primary key in the Registry as an abstract concept is a resource
record's IVOID.  Hence, for all tables having primary keys at all, the
\rtent{ivoid} column is part of its primary key.  This
specification does not require implementations to actually declare
primary keys in the underlying database, and no aspect of user-visible
behavior depends on such explicit declarations; in particular, this
specification makes no requirements on the contents of
\tapent{tap\_schema.keys}.

We nevertheless make recommendations on explicit primary keys, as
we expect definitions according to our recommendations will enhance
robustness of services.

In several RegTAP tables -- \rtent{capability},
\rtent{res_schema}, \rtent{res_table}, and
\rtent{interface} -- artificial primary keys are necessary, as
in VOResource XML sibling elements are not otherwise distinguished.  To
allow such artificial primary keys, a column is added to each table, the
name of which ends in \texttt{\_index} (\rtent{cap_index},
\rtent{schema_index}, \rtent{table_index}, and
\rtent{intf_index}).

The type and content of these \texttt{X\_index} columns is
implementation-defined, and clients must not make assumptions on their
content except that the pair \rtent{ivoid}, \texttt{X\_index} is a primary
key for the relation (plus, of course, that references from other tables
correctly resolve).  In the tables of columns given below, the
\texttt{X\_index} columns have ``(key)'' given for type.  Implementors
have to insert whatever ADQL type is appropriate for their
choice or \texttt{X\_index} implementation.

Obvious implementations for \texttt{X\_index} include having
\texttt{X\_index} enumerate the sibling elements or using some sort
of UUID.


\section{Notes on string handling}

\label{stringnorm}

In the interest of consistent behavior between different RegTAP
implementations regardless of their technology choices, this section
establishes some rules on the treatment of strings -- both those
obtained from attributes and those obtained from element
content -- during ingestion from VOResource XML to database
tables.



\subsection{Whitespace Normalization}

\label{whitenorm}

Most string-valued items in VOResource and extensions are of type
\texttt{xs:to\-ken}, with the clear intent that whitespace in them is
to be normalized in the sense of that XML schema type (i.e., all
whitespace is just a single blank, and there is no leading or trailing
whitespace).  For the few exceptions
that actually are directly derived from xs:string (e.g.,
\vorent{vstd:EndorsedVersion}, \vorent{vs:Waveband}) it does not
appear that the intent regarding whitespace is different.

In order to provide reliable querying and simple rules for ingestors
even when these do not employ schema-aware XML parsers, this standard
requires that during ingestion, leading and trailing whitespace MUST be
removed from all strings; in particular, there are no strings consisting
exclusively of whitespace in RegTAP.  The treatment of internal
whitespace is implementation-defined. This reflects the expectation
that, wherever multi-word items are queried, whitespace-ignoring
constraints will be used (e.g., LIKE-based regular expressions or the
\rtent{ivo_hasword} user defined function defined below).


\subsection{NULL/Empty String Normalization}

\label{nullnorm}

While empty strings and NULL values are not usually well
distinguished in VO practice -- as reflected in the conventional
TABLEDATA and BINARY serializations of VOTable -- , the distinction
must be strictly maintained in the database tables to ensure
reproduceable queries across different RegTAP implementations.

Ingestors therefore MUST turn empty strings (which, by section \ref{whitenorm}, include strings consisting of whitespace
only in VOResource's XML serialization) into NULL values in the
database.  Clients expressing constraints on the presence (or absence)
of some information must therefore do so using SQL's \texttt{IS NOT NULL}
(or \texttt{IS NULL}) operators.


\subsection{Case Normalization}

\label{casenorm}

ADQL 2.0 has no operators for case-insensitive matching of strings
(ILIKE, required by this version of RegTAP, was only defined in ADQL
2.1).
Mainly for this reason, RegTAP 1.0 required most columns
containing values not usually intended for display to be
converted to lower case on ingestion.  This also somewhat reduces the
likelihood that matches are missed because of different capitalisation,
since queries disregarding capitalisation variations will yield empty
(rather than partial) results.

In the table descriptions below, there are
explicit requirements on case normalization near the end of each
section.  This is particularly important when the entities to be
compared are defined to be case-insensitive (e.g., UCDs, IVOIDs).
Client software that can inspect user-provided arguments (e.g., when
filling template queries) should also convert the respective fields to
lower case.

This conversion MUST cover all ASCII letters, i.e., A through Z.
The conversion SHOULD take place according to
algorithm R2 in section 3.13, ``Default Case Algorithms'' of the Unicode
Standard
\citep{std:UNICODE}.  In practice, non-ASCII characters are not expected
to occur in columns for which lowercasing is required.

Analogously, case-insensitive comparisons as required by some of the
user-defined functions for the relational registry MUST compare
the ASCII letters without regard for case.  They SHOULD compare according
to D144 in the Unicode Standard.

Columns intended for presentation are not case-normalised.  When
matching against these, queries should use case-insensitive matching
using ADQL 2.1's ILIKE or, equivalently, the \verb|ivo_nocasematch| user
defined function required by RegTAP.


\subsection{Non-ASCII Characters}

\label{utfreq}

Neither TAP nor ADQL mention non-ASCII in service parameters -- in
particular the queries -- or returned values.  For RegTAP, that is
unfortunate, as several columns will contain relevant non-ASCII
characters.  Columns for which extra care is necessary include all
descriptions, \rtent{res_title} and \rtent{creator_seq} in
\rtent{rr.resource}, as well as \rtent{role_name} and
\rtent{street_address} in \rtent{rr.res_role}.

RegTAP implementations SHOULD be able to faithfully represent all
characters defined in the latest version of the Unicode standard
\citep{std:UNICODE} at
any given time and allow querying using them (having support for UTF-8
in the database should cover this requirement) for at least the fields
mentioned above.

On VOResource ingestion, non-ASCII characters that a service cannot
faithfully store MUST be replaced by a question mark character (``?'').

RegTAP services MUST interpret incoming ADQL as encoded in UTF-8,
again replacing unsupported characters with question marks.

We leave character replacement on result generation unspecified, as
best-effort representations (e.g., ``Angstrom'' instead of ``Ångström'')
should not impact interoperability but significantly improve user
experience over consistent downgrading.  In VOTable output,
implementations SHOULD support full Unicode in at least the fields
enumerated above.  Clients are advised to retrieve results in VOTable or
other encoding-aware formats.

Note that at least up to VOTable 1.5, non-ASCII in char-typed fields, while
supported by most clients in TABLEDATA serialization, is technically
illegal; it is essentially undefined in other serializations.  To
produce standards-compliant VOTables, columns containing non-ASCII must
be of type unicodeChar.

\subsection{Vocabulary considerations}

\label{sect:vocab-use}

Since version 1.1, VOResource employs RDF vocabularies to control terms
used in several places; in version 1.2, this concerns
\vorent{content/con\-tentLevel}, \vorent{content/type},
\vorent{content/subject},
\vorent{date/role}, \vorent{content/relationship/relationshipType}.
These vocabularies are available from the IVOA vocabulary
repository\footnote{\url{https://www.ivoa.net/rdf}} as specified by
Vocabularies in the VO, Version 2 \citep{2023ivoa.spec.0206D}.  The
relevant vocabulary URIs are given in the VOResource specification and
\xmlel{xs:documentation} elements in the schema file.

For RegTAP, these vocabulary resources are important because the
VOResource relationship types and date roles contain some deprecated
terms kept for compatibility with VOResource 1.0, together with guidance
what to use instead.  In order to simplify the usage of
vocabulary-controlled RegTAP columns, services MUST translate such deprecated
terms when the vocabularies give replacements (i.e., appear as subjects
of \emph{ivoasem:useInstead} triples).

Since the vocabularies are expected to develop independently of their
originating standards, RegTAP service operators furthermore SHOULD regularly
revisit IVOA vocabularies to see if further translations must be done.

In VO practice, many resource records still use subject identifiers that
are not taken from the IVOA UAT\footnote{{http://www.ivoa.net/rdf/uat}}.
Where only the lexical form of the identifier is wrong, RegTAP operators
are free to correct the syntax; otherwise, subject identifiers should be
ingested as given by the data providers even if they are not drawn from
the UAT.


\section{QNames in VOResource attributes}

\label{qnameatts}

VOResource and its extensions make use of XML QNames in attribute
values, most prominently in \texttt{xsi:type}.  The standard
representation of these QNames in XML instance documents makes use of an
abbreviated notation employing prefixes declared using the xmlns mechanism
as discussed in \citet{std:XMLNS}.  Within an ADQL-exposed database, no
standard mechanism exists that could provide a similar mapping of URLs
and abbreviations.  The correct way to handle this problem would thus be
to have full QNames in the database (e.g.,
\verb|{http://www.ivoa.net/xml/ConeSearch/v1.0}ConeSearch| for the
canonical \vorent{cs:ConeSearch}).  This, of course, would make for
excessively tedious and error-prone querying.

For various reasons, VOResource authors have always been encouraged
to use a set of ``standard'' prefixes.  This allows an easy and, to users,
unsurprising exit from the problem of the missing xmlns declarations:
For the representation of QNames within the database, these recommended
prefixes are mandatory in RegTAP. Future VOResource extensions define their
mandatory prefixes themselves.

As described in the IVOA endorsed Note ``XML schema versioning
policies'' \citep{2018ivoa.spec.0529H}, minor-version updates to XML
schemas do not change the namespace URIs.  Before the adoption of that
note, some schemas introduced namespace URIs that did change on minor
versions.  For consistency, and because there should not really be
discovery use cases based on minor versions of XML schemas, all
namespace URIs for the same major version of a standard have the same
canonical prefix -- e.g., the schema URIs for both SSAP namespaces that
SimpleDALRegExt has defined are mapped to \texttt{ssap:}.

For reference, table~\ref{tab:prefixmap}
lists the XML namespace URIs and their canonical prefixes
for schemata widely used in the VO Registry.

\begin{table}
\begin{tabular}{ll}
\sptablerule
cs&http://www.ivoa.net/xml/ConeSearch/v1.0\\
dc&http://purl.org/dc/elements/1.1/\\
oai&http://www.openarchives.org/OAI/2.0/\\
ri&http://www.ivoa.net/xml/RegistryInterface/v1.0\\
sia&http://www.ivoa.net/xml/SIA/v1.0\\
sia&http://www.ivoa.net/xml/SIA/v1.1\\
slap&http://www.ivoa.net/xml/SLAP/v1.0\\
ssap&http://www.ivoa.net/xml/SSA/v1.0\\
ssap&http://www.ivoa.net/xml/SSA/v1.1\\
tr&http://www.ivoa.net/xml/TAPRegExt/v1.0\\
vg&http://www.ivoa.net/xml/VORegistry/v1.0\\
vr&http://www.ivoa.net/xml/VOResource/v1.0\\
vs&http://www.ivoa.net/xml/VODataService/v1.0\\
vs&http://www.ivoa.net/xml/VODataService/v1.1\\
vstd&http://www.ivoa.net/xml/StandardsRegExt/v1.0\\
xsi&http://www.w3.org/2001/XMLSchema-instance\\
\sptablerule
\end{tabular}
\caption{The canonical prefix mapping in the VO Registry as of the
publication of this specification.}
\label{tab:prefixmap}
\end{table}


\section{Xpaths}

\label{vorutypes}

This specification piggybacks on top of the well-established
VOResource standard.  This means that it does not define a full data model,
but rather something like a reasonably query-friendly view of a partial
representation of one.  The link between the actual data model, i.e.,
VOResource and its extensions as defined by the XML Schema documents, and
the fields within this database schema, is provided by
xpaths, which are here slightly abbreviated for both brevity and
generality.

All xpaths given in this specification are assumed to be relative to
the enclosing \vorent{vr:Resource} element; these are called
``resource xpaths'' in the following.  If resource xpaths are to be
applied to an OAI-PMH response, the Xpath expression
\texttt{*/*/*/oai:metadata/ri:Resource} must be prepended to it,
with the canonical prefixes from section \ref{qnameatts} implied.  The resource xpaths themselves
largely do not need explicit namespaces since VOResource elements are by
default unqualified.  Elements and attributes from non-VOResource
schemata in such resource xpaths have the canonical namespace prefixes, which in this
specification only applies to several \texttt{xsi:type} attribute
names.

Some tables draw data from several different VOResource elements.
For those, we have introduced an extended syntax with additional
metacharacters \verb$($, \verb$)$, and \verb$|$,
where the vertical bar denotes an
alternative and the parentheses grouping.  For instance, our notation
\texttt{/(tableset/schema/|)table/} corresponds to the two xpaths
\texttt{/table} and \texttt{/tableset/schema/table}.

Within the Virtual Observatory, the link between data models and
concrete data representations is usually made using utypes.
Since VOResource is directly modelled
in XML Schema, the choice of XPath as the bridging formalism is
compelling, though, and utypes themselves are not necessary for the
operation of a TAP service containing the relational registry.
TAP, however, offers fields for utypes in its \tapent{TAP\_SCHEMA}.  Since they
are not otherwise required, this specification takes the liberty of
using them to denote the xpaths.

In the metadata for tables and columns below, the utypes given are
obtained from the xpaths by simply prepending them with
\texttt{xpath:}.  To avoid repetition, we allow relative xpaths:
when the xpath in a column utype does not start with a slash, it is
understood that it must be concatenated with the table utype to obtain
the full xpath.

For illustration, if a table has a utype of
$$\texttt{xpath:/capability/interface/}$$ and a column within this table
has a utype of $$\texttt{xpath:accessURL/@use},$$ the resulting resource
xpath would come out to be
$$\texttt{/capability/interface/accessURL/@use};$$ to match this in an
OAI-PMH response, the XPath would be
$$\texttt{\small
*/*/*/oai:metadata/ri:Resource/capability/interface/accessURL/@use}.$$


While clients MUST NOT rely on these utypes in either
\tapent{TAP\_SCHEMA} or the
metadata delivered with TAP replies, service operators SHOULD provide them, in
particular when there are local extensions to the relational registry in their
services.  Giving xpaths for extra columns and tables helps human
interpretation of them at least when the defining schema files are
available.

Resource xpaths are also used in the \rtent{res_detail} table (section
\ref{table_res_detail}).  These are normal xpaths
(although again understood relative to the enclosing Resource element),
which, in particular, means that they are case sensitive.  On the other
hand, to clients they are simply opaque strings, i.e., clients cannot
just search for any xpaths into VOResource within \rtent{res_detail}.

Non-normatively, we give an XSLT
sheet\footnote{\auxiliaryurl{makeutypes.xslt}} producing resource xpaths
for suitable VOResource extensions. It is, however, not fully general,
as it will only notice direct subclasses of VOResource's
\vorent{Resource}, \vorent{Capability}, and \vorent{Interface} classes.
If extensions derive from other extensions' subclasses of these classes,
the stylesheet would need to be amended.

\section{Discovering Relational Registries}

\label{registration}

The relational registry can be part of any TAP service.  The presence
of the tables discussed here is indicated by declaring support for the
data model \texttt{Registry 1.2} with the IVOID
$$\texttt{ivo://ivoa.net/std/regtap\#1.2}$$ in the service's
capabilities as governed by TAPRegExt \citep{2012ivoa.spec.0827D}.  Technically, this
entails adding


\begin{verbatim}
<dataModel ivo-id="ivo://ivoa.net/std/regtap#1.2"
  >Registry 1.2</dataModel>
\end{verbatim}

as a child of the capability element with the type
\vorent{tr:TableAccess}.

A client that knows the access URL of one TAP service containing
a relational
registry can thus discover all other services exposing one. The
``Find all TAP endpoints offering the
relational registry'' example (sect.~\ref{ex:find-regtap})
shows a query that does
this.

Services implementing this data model that do not (strive to) offer
the full data content of the VO registry (like domain-specific
registries or experimental systems) MUST NOT declare the above data
model in order to not invite clients expecting the VO registry to send
queries to it.

Section 5.2 of Registry Interfaces 1.1 additionally requires full RegTAP
services to register a \xmlel{vg:Registry}-typed record with a (possibly
auxiliary) TAP capability.  This record is being used by the RofR, and
it opens up a migration path to a data-based discovery
pattern\footnote{This would look for schema utypes and appears
desirable to enable multiple instances of a data model within one TAP
service; it is expected that the recommended discovery pattern
in RegTAP 1.3 will be updated accordingly.}.




\section{RegTAP Tables}

\label{vortables}

All tables making up the RegTAP schema are in the \tapent{rr} schema.
In both \tapent{TAP\_SCHEMA} and the VODataService tableset, the
\tapent{rr} schema
MUST be associated with a \tapent{utype} matching the data model
identifier given in sect.~\ref{registration}, i.e.,
$$\texttt{ivo://ivoa.net/std/regtap\#1.2}.$$

In the following table descriptions, the names of tables
(cf.~Table \ref{table:dm}) and columns
are normative and MUST be used as given, and all-lowercase.  The utypes
given in the table descriptions are formed as discussed
in section~\ref{vorutypes} and are subject to the requirements given
there.  All columns defined in
this document MUST have a 1 in the \tapent{std} column of the
\tapent{TAP\_SCHEMA.table\_columns} table.  Unless otherwise
specified, all values of ucd and unit in
\tapent{TAP\_SCHEMA.table\_columns} are NULL for columns defined here.
Descriptions are not normative (as given, they usually are taken from
the schema files of VOResource and its extensions with slight
redaction).  Registry operators MAY provide additional columns in their
tables, but they MUST provide all columns given in this
specification.

Many of the columns specified below are defined as having a ``string''
data type.  This is to be translated into arrays of \texttt{char} or
\texttt{unicodeChar} on VOTable output depending on the service
operators' decisions as to the representation of non-ASCII data in the
database.  For requirements and recommendations regarding national
characters in RegTAP, see Sect.~\ref{utfreq}.  The length of these
arrays is not defined by this standard, where no artificial
length limits should be imposed by implementations.

Some of the types are given as ``datatype+xtype''.  In these cases, the
xtype MUST be given in VOTable output, and the serialisation rules from
DALI \citep{2017ivoa.spec.0517D} apply.

All table descriptions start out with brief remarks on the
relationship of the table to the VOResource XML data model.  Then, the
columns are described in a selection of \tapent{TAP\_SCHEMA} metadata. For each
table, recommendations on explicit primary and foreign keys as well as
indexed columns are given, where it is understood that primary and
foreign keys are already indexed in order to allow efficient joins;
these parts are not normative, but operators should ensure decent
performance for queries assuming the presence of the given indexes and
relationships.  Finally, miscellaneous normative requirements, typically
on case normalization, are given.


\begin{figure}

\includegraphics[width=\textwidth]{schema.pdf}
\caption{A sketch of the
Relational Registry schema.
Only the columns considered
most interesting for client use are shown.  Arrows indicate foreign
key-like relationships.}
\end{figure}


% GENERATED: gettables.sh

\begin{table}[t]
\small
\hbox to\hsize{\hss
\begin{tabular}{p{0.35\textwidth}p{0.64\textwidth}}
\sptablerule
\textbf{Name and UType}&\textbf{Description}\\
\sptablerule
rr.alt\_identifier\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/(curation/creator/|)altIdentifier}&
 An alternate identifier associated with this record.\\
rr.capability\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/capability/}&
 Pieces of behaviour of a resource.\\
rr.interface\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/capability/interface/}&
 Information on access modes of a capability.\\
rr.intf\_param\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/capability/interface/param/}&
 Input parameters for services.\\
rr.relationship\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/content/relationship/}&
 Relationships between resources (like mirroring, derivation, serving
a data collection).\\
rr.res\_date\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/curation/}&
 A date associated with an event in the life cycle of the resource.\\
rr.res\_detail\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily }&
 XPath-value pairs for members of resource or capability and their
derivations that are less used and/or from VOResource extensions.\\
rr.res\_role\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily }&
 Entities (persons or organizations) operating on resources: creators,
contacts, publishers, contributors.\\
rr.res\_schema\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/tableset/schema/}&
 Sets of tables related to resources.\\
rr.res\_subject\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/content/}&
 Topics, object types, or other descriptive keywords about the
resource.\\
rr.res\_table\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/(tableset/schema/|)table/}&
 (Relational) tables that are part of schemata or resources.\\
rr.resource\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/}&
 The resources (like services, data collections, organizations)
present in this registry.\\
rr.stc\_spatial\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/coverage/spatial}&
 The spatial coverage of resources.\\
rr.stc\_spectral\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/coverage/spectral}&
 The spectral coverage of resources, given as one or more intervals.\\
rr.stc\_temporal\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/coverage/temporal}&
 The temporal coverage of resources, given as one or more intervals.\\
rr.table\_column\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/(tableset/schema/|)/table/column/}&
 Metadata on columns of a resource's tables.\\
rr.tap\_table\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily }&
 TAP-queriable tables.\\
rr.validation\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/(capability/|)validationLevel}&
Validation levels for resources and capabilities.\\

\sptablerule
\end{tabular}\hss}
\caption{The tables making up the TAP data model \texttt{Registry 1.2}}
\label{table:dm}
\end{table}

% /GENERATED


\subsection{The resource Table}

\label{table_resource}

The \rtent{rr.resource} table contains most atomic members of
\rtent{vr:Resource} that have a 1:1 relationship to the resource
itself.  Members of derived types are, in general, handled through
the \rtent{res_detail}
table even if 1:1 (see \ref{table_res_detail}).  The
\rtent{content_level}, \rtent{content_type}, and \rtent{waveband}
members are 1:n but still appear
here.  If there are multiple values, they are concatenated with hash
characters (\#).  Use the \rtent{ivo_hashlist_has} ADQL extension
function to check for the presence of a single value.  This convention
saves on the number of tables while not complicating common queries significantly.

In VOResource documents, multiple \vorent{rights} elements are allowed
on a single record.  This is mainly for compatiblity with DataCite, and
multiple \vorent{rights} elements are discouraged by the VOResource
specification at least for use within the VO.  RegTAP uses that freedom
to include \rtent{rights} and \rtent{rights_uri} columns in
\rtent{rr.resource} directly.  These columns must be populated,
respectively, with the content and the value of the rightsURI attribute
of the \emph{first} \vorent{rights} element within a resource record
(falling back to NULL).  RegTAP services may provide all \vorent{rights}
and \vorent{rightsURI} values through \rtent{rr.res_detail} (see
sect.~\ref{table_res_detail}).

A local addition is the \rtent{creator_seq} column.  It contains
all content of the \vorent{name} elements below a resource element
\vorent{curation} child's \vorent{creator} children, concatenated with a
sequence of semicolon and blank characters (``\mbox{\texttt{; }}''). The
individual parts must be concatenated preserving the sequence of the XML
elements.  The resulting string is primarily intended for display
purposes (``author list'') and is hence not case-normalized.  It was
added since the equivalent of an author list is expected to be a
metadatum that is displayed fairly frequently, but also since the
sequence of author names is generally considered significant.  The
\rtent{res_role} table, on the other hand, does not allow recovering
the input sequence of the rows belonging to one resource.

The \rtent{res_type} column reflects the lower-cased value of
the \vorent{ri:Resource} element's \texttt{xsi:type} attribute,
where the canonical prefixes (cf.~sect.~\ref{qnameatts})
are used.  While custom or experimental
VOResource extensions may lead to more or less arbitrary strings in that
column, VOResource and its IVOA-recommended extensions at the time of
writing define the following values for \rtent{res_type}:


\begin{description}
\item[vg:authority]A naming authority; as described in the IVOA
Identifiers specification \citep{2016ivoa.spec.0523D}, these records
are used to guarantee global uniqueness of IVOIDs.
\item[vg:registry]A registry.  This can be a publishing registry (which
have at least one capability element of type \xmlel{vg:Harvest}), or a
searchable registry (like a RegTAP service).  See Registry Interfaces
1.1 on how to apply this resource type.
\item[vr:organisation]The main purpose of an organisation as a registered resource is to
be referenced by IVOID as a publisher of other resources.
\item[vr:resource]Any entity or component of a VO application that is describable and
identifiable by an IVOA identifier; while it is technically possible to
publish such records, the authors of such records should probably be
asked to use a more specific type.
\item[vr:service]A resource that can be invoked by a client to perform some action on
its behalf.
\item[vs:catalogservice]A service that interacts with one or more
specified tables.
\item[vs:catalogresource] A resource accessible through collective services
(which would typically be declared through auxiliary capabilities) or non-IVOA protocols
(typical example: A set of tables accessible within a larger TAP
service).
\item[vs:dataservice]A service for accessing astronomical data; publishers choosing
this over \vorent{vs:CatalogService} probably intend to communicate
that the resource does not have an intrinsically tabular structure.
\item[vs:dataresource] A non-tabular resource accessible through collective
services (which would typically be declared through auxiliary
capabilities) or non-IVOA protocols.
\item[vs:datacollection] A resource type intended by VODataService
version 1.1 to be used for data-only resources.  Data providers should
use \vorent{vs:CatalogResource} or \vorent{vs:DataResource} instead.
\item[vstd:standard]A description of a standard specification.
\end{description}

The \vorent{status} attribute of \vorent{vr:Resource} is
considered an implementation detail of the XML serialization and is not
reflected here.  Neither \vorent{inactive} nor \vorent{deleted}
records may be kept in the \rtent{resource} table.  Since all
other tables in the relational registry should keep a foreign key on the
\rtent{ivoid} column, this implies that only metadata on
\vorent{active} records
is being kept in the relational registry. In other words, users can
expect a resource to exist and work if they find it in a relational
registry.


% GENERATED: maketable.sh rr.resource

\begin{inlinetable}
\renewcommand*{\arraystretch}{1.2}
\small
\begin{tabular}{p{0.28\textwidth}p{0.2\textwidth}p{0.66\textwidth}}
\sptablerule
\multicolumn{3}{l}{\textit{Column names, utypes, datatypes, and descriptions for the rr.resource table}}\\
\sptablerule

\baselineskip=9pt\relax ivoid\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:identifier}&
\footnotesize string&
Unambiguous reference to the resource conforming to the IVOA standard for identifiers.\\

\baselineskip=9pt\relax res\_type\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:@xsi:type}&
\footnotesize string&
Resource type (something like vg:authority, vs:catalogservice, etc).\\

\baselineskip=9pt\relax created\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:@created}&
\footnotesize character[19]\hfil\break+timestamp&
The UTC date and time this resource metadata description was created.\\

\baselineskip=9pt\relax short\_name\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:shortName}&
\footnotesize string&
A short name or abbreviation given to something, for presentation in space-constrained fields (up to 16 characters).\\

\baselineskip=9pt\relax res\_title\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:title}&
\footnotesize string&
The full name given to the resource.\\

\baselineskip=9pt\relax updated\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:@updated}&
\footnotesize character[19]\hfil\break+timestamp&
The UTC date this resource metadata description was last updated.\\

\baselineskip=9pt\relax content\_level\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:content/contentLevel}&
\footnotesize string&
A hash-separated list of content levels specifying the intended audience.\\

\baselineskip=9pt\relax res\_description\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:content/description}&
\footnotesize string&
An account of the nature of the resource.\\

\baselineskip=9pt\relax reference\_url\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:content/referenceURL}&
\footnotesize string&
URL pointing to a human-readable document describing this resource.\\

\baselineskip=9pt\relax creator\_seq\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:curation/creator/name}&
\footnotesize string&
The creator(s) of the resource in the order given by the resource record author, separated by semicolons.\\

\baselineskip=9pt\relax content\_type\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:content/type}&
\footnotesize string&
A hash-separated list of natures or genres of the content of the resource.\\

\baselineskip=9pt\relax source\_format\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:content/source/@format}&
\footnotesize string&
The format of source\_value. This, in particular, can be ``bibcode''.\\

\baselineskip=9pt\relax source\_value\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:content/source}&
\footnotesize string&
A bibliographic reference from which the present resource is derived or extracted.\\

\baselineskip=9pt\relax res\_version\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:curation/version}&
\footnotesize string&
Label associated with creation or availablilty of a version of a resource.\\

\baselineskip=9pt\relax region\_of\_regard\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:coverage/regionOfRegard}&
\footnotesize real&
A single numeric value representing the angle, given in decimal degrees, by which a positional query against this resource should be ``blurred'' in order to get an appropriate match.\\

\baselineskip=9pt\relax waveband\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:coverage/waveband}&
\footnotesize string&
A hash-separated list of regions of the electro-magnetic spectrum that the resource's spectral coverage overlaps with.\\

\baselineskip=9pt\relax rights\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/rights}&
\footnotesize string&
A statement of usage conditions (license, attribution, embargo, etc).\\

\baselineskip=9pt\relax rights\_uri\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/rights/@rightsURI}&
\footnotesize string&
A URI identifying a license the data is made available under.\\

\sptablerule
\end{tabular}
\end{inlinetable}


% /GENERATED


This table should have the \rtent{ivoid} column explicitly set
as its primary key.

The following columns MUST be lowercased during ingestion:
\rtent{ivoid}, \rtent{res_type}, \rtent{content_level},
\rtent{content_type}, \rtent{source_format},
\rtent{waveband}.
Clients are advised to query the \rtent{res_description} and
\rtent{res_title}  columns
using the the \rtent{ivo_hasword} function, and to use
\rtent{ivo_hashlist_has} on \rtent{content_level},
\rtent{content_type}, and
\rtent{waveband}.

The row for \rtent{region_of_regard} in
\tapent{TAP\_SCHEMA.columns} MUST have \texttt{deg} in its
\tapent{unit} column.

When querying \rtent{content_type} and \rtent{content_level},
note that resource record authors should restrict themselves to terms
from the vocabularies at
\url{http://ivoa.net/rdf/voresource/content_type} and
\url{http://ivoa.net/rdf/voresource/content_level}, respectively

The content of incoming \vorent{content/type} and \vorent{content/level}
elements must be normalized according to the rules laid down in
sect.~\ref{sect:vocab-use} before further processing.


\subsection{The res\_role Table}

\label{table_res_role}

This table subsumes the contact, publisher, contributor,
and creator members of the
VOResource data model.  They have been combined into a single table to
reduce the total number of tables, and also in anticipation of a unified
data model for such entities in future versions of VOResource.

The actual role is given in the \rtent{base_role} column, which
can be one of \rtent{contact}, \rtent{publisher}, \rtent{contributor}, or
\rtent{creator}.  Depending on this value, here are the xpaths
for the table fields (we have abbreviated
\vorent{/curation/publisher}
as cp, \vorent{/curation/contact} as co, \vorent{/curation/creator}
as cc,
and \vorent{/curation/contributor} as cb):

\vspace{5pt}
\hbox to\hsize{\hss\small
\noindent\begin{tabular}{lllll}
\sptablerule
\textbf{base\_role value}&
\textbf{contact}&
\textbf{publisher}&
\textbf{creator}&
\textbf{contributor}\\
\sptablerule
role\_name&co/name&cp&cc/name&cb\\
role\_ivoid&co/name/@ivo-id&cp/@ivo-id&cc/name/@ivo-id&cb/@ivo-id\\
address&co/address&N/A&N/A&N/A\\
email&co/email&N/A&N/A&N/A\\
telephone&co/telephone&N/A&N/A&N/A\\
logo&co/logo&N/A&cc/logo&N/A\\
\sptablerule
\end{tabular}\hss}
\vskip5pt

Not all columns are available for each role type in VOResource.  For
example, contacts have no logo, and creators no telephone members.  Unavailable
metadata (marked with N/A in the above table) MUST be represented with NULL
values in the corresponding columns.

When matching against \rtent{role_name}, please be aware that despite
the admonitions in section 3.1.2 of VOResource 1.1 (which recommends a
format like Last, F.~for person names), as of this writing the wide
majority of role names in the VO Registry are not in this format.  Hence, name
matching in RegTAP at this point should be very lenient.


% GENERATED: maketable.sh rr.res_role

\begin{inlinetable}
\renewcommand*{\arraystretch}{1.2}
\small
\begin{tabular}{p{0.28\textwidth}p{0.2\textwidth}p{0.66\textwidth}}
\sptablerule
\multicolumn{3}{l}{\textit{Column names, utypes, datatypes, and descriptions for the rr.res\_role table}}\\
\sptablerule

\baselineskip=9pt\relax ivoid\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/identifier}&
\footnotesize string&
The parent resource.\\

\baselineskip=9pt\relax role\_name\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily }&
\footnotesize string&
The real-world name or title of a person or organization.\\

\baselineskip=9pt\relax role\_ivoid\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily }&
\footnotesize string&
An IVOA identifier of a person or organization.\\

\baselineskip=9pt\relax street\_address\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily }&
\footnotesize string&
A mailing address for a person or organization.\\

\baselineskip=9pt\relax email\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily }&
\footnotesize string&
An email address the entity can be reached at.\\

\baselineskip=9pt\relax telephone\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily }&
\footnotesize string&
A telephone number the entity can be reached at.\\

\baselineskip=9pt\relax logo\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily }&
\footnotesize string&
URL pointing to a graphical logo, which may be used to help identify the entity.\\

\baselineskip=9pt\relax base\_role\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily }&
\footnotesize string&
The role played by this entity; this is one of contact, publisher, contributor, or creator.\\

\sptablerule
\end{tabular}
\end{inlinetable}


% /GENERATED



The \rtent{ivoid} column should be an explicit foreign key into
the \rtent{resource} table.  It is recommended to maintain indexes
on at least the \rtent{role_name} column, ideally in a way that
supports regular expressions.

The following columns MUST be lowercased during ingestion:
\rtent{ivoid}, \rtent{role_ivoid},
\rtent{base_role}.
Clients are advised to query the remaining columns, in particular
\rtent{role_name},
case-insensitively, e.g., using \verb|ivo_nocasematch|.


\subsection{The res\_subject Table}

\label{table_res_subject}

Since subject queries are expected to be frequent and perform relatively
complex checks (e.g., resulting from thesaurus queries in the clients), the
subjects are kept in a separate table rather than being hash-joined like other
string-like 1:n members of resource.


% GENERATED: maketable.sh rr.res_subject

\begin{inlinetable}
\renewcommand*{\arraystretch}{1.2}
\small
\begin{tabular}{p{0.28\textwidth}p{0.2\textwidth}p{0.66\textwidth}}
\sptablerule
\multicolumn{3}{l}{\textit{Column names, utypes, datatypes, and descriptions for the rr.res\_subject table}}\\
\sptablerule

\baselineskip=9pt\relax ivoid\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/identifier}&
\footnotesize string&
The parent resource.\\

\baselineskip=9pt\relax res\_subject\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:subject}&
\footnotesize string&
Topics, object types, or other descriptive keywords about the resource.\\

\sptablerule
\end{tabular}
\end{inlinetable}


% /GENERATED



The \rtent{ivoid}  column should be an explicit foreign key into
\rtent{resource}.  It is recommended to index the
\rtent{res_subject} column, preferably in a way that allows to process
case-insensitive and pattern queries using the index.

The \rtent{ivoid} column MUST be lowercased during
ingestion.  Clients are advised to query the \rtent{res_subject} column
case-insensitively, e.g., using \verb|ivo_nocasematch|.

The content of incoming \vorent{subject}
elements may be normalized according to the rules laid down in
sect.~\ref{sect:vocab-use}.



\subsection{The capability Table}

\label{table_capability}

The capability table describes a resource's modes of interaction; it only
contains the members of the base type \vorent{vr:Capability}.
Members of derived types are kept in the \rtent{res_detail} table
(see \ref{table_res_detail}).

The table has a
\rtent{cap_index} to disambiguate multiple
capabilities on a single resource.  See section \ref{primarykeys} for details.


% GENERATED: maketable.sh rr.capability

\begin{inlinetable}
\renewcommand*{\arraystretch}{1.2}
\small
\begin{tabular}{p{0.28\textwidth}p{0.2\textwidth}p{0.66\textwidth}}
\sptablerule
\multicolumn{3}{l}{\textit{Column names, utypes, datatypes, and descriptions for the rr.capability table}}\\
\sptablerule

\baselineskip=9pt\relax ivoid\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/identifier}&
\footnotesize string&
The parent resource.\\

\baselineskip=9pt\relax cap\_index\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily }&
\footnotesize (key)&
An arbitrary identifier of this capability within the resource.\\

\baselineskip=9pt\relax cap\_type\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:@xsi:type}&
\footnotesize string&
The type of capability covered here. If looking for endpoints implementing a certain standard, you should not use this column but rather match against standard\_id.\\

\baselineskip=9pt\relax cap\_description\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:description}&
\footnotesize string&
A human-readable description of what this capability provides as part of the over-all service.\\

\baselineskip=9pt\relax standard\_id\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:@standardID}&
\footnotesize string&
A URI for a standard this capability conforms to.\\

\sptablerule
\end{tabular}
\end{inlinetable}


% /GENERATED



This table should have an explicit primary key made up of
\rtent{ivoid} and \rtent{cap_index}.
The \rtent{ivoid} column should be
an explicit foreign key into \rtent{resource}.
It is recommended to maintain indexes on at least the
\rtent{cap_type} and \rtent{standard_id} columns.

The following columns MUST be lowercased during ingestion:
\rtent{ivoid}, \rtent{cap_type}, \rtent{standard_id}.
Clients are advised to query the \rtent{cap_description} column
using the \rtent{ivo_hasword} function.



\subsection{The res\_schema Table}

\label{table_res_schema}

The \rtent{res_schema} table corresponds to VODataService's
\vorent{schema} element.  It has been renamed to avoid clashes with
the SQL reserved word \texttt{SCHEMA}.

The table has a column \rtent{schema_index} to disambiguate
multiple schema elements on a single resource.  See section \ref{primarykeys} for details.


% GENERATED: maketable.sh rr.res_schema

\begin{inlinetable}
\renewcommand*{\arraystretch}{1.2}
\small
\begin{tabular}{p{0.28\textwidth}p{0.2\textwidth}p{0.66\textwidth}}
\sptablerule
\multicolumn{3}{l}{\textit{Column names, utypes, datatypes, and descriptions for the rr.res\_schema table}}\\
\sptablerule

\baselineskip=9pt\relax ivoid\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/identifier}&
\footnotesize string&
The parent resource.\\

\baselineskip=9pt\relax schema\_index\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily }&
\footnotesize (key)&
An arbitrary identifier for the res\_schema rows belonging to a resource.\\

\baselineskip=9pt\relax schema\_description\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:description}&
\footnotesize string&
A free text description of the tableset explaining in general how all of the tables are related.\\

\baselineskip=9pt\relax schema\_name\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:name }&
\footnotesize string&
A name for the set of tables.\\

\baselineskip=9pt\relax schema\_title\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:title}&
\footnotesize string&
A descriptive, human-interpretable name for the table set.\\

\baselineskip=9pt\relax schema\_utype\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:utype}&
\footnotesize string&
An identifier for a concept in a data model that the data in this schema as a whole represent.\\

\sptablerule
\end{tabular}
\end{inlinetable}


% /GENERATED



This table should have an explicit primary key made up of
\rtent{ivoid} and \rtent{schema_index}.  The
\rtent{ivoid}  column should be an explicit foreign key into
\rtent{resource}.

The following columns MUST be lowercased during ingestion:
\rtent{ivoid}, \rtent{schema_name}, \rtent{schema_utype}.
Clients are advised to query the \rtent{schema_description}
and \rtent{schema_title} columns
using the the \rtent{ivo_hasword} function.



\subsection{The res\_table Table}

\label{table_res_table}

The \rtent{res_table} table models VODataService's
\vorent{table} element.  It has been renamed to avoid name clashes
with the SQL reserved word \texttt{TABLE}.

The table contains a column \rtent{table_index} to disambiguate
multiple tables on a single resource.  See section \ref{primarykeys} for details.  Note that if the sibling
count is used as implementation of \rtent{table_index}, the count
must be per resource and \emph{not} per schema, as
\rtent{table_index} MUST be unique within a resource.


% GENERATED: maketable.sh rr.res_table

\begin{inlinetable}
\renewcommand*{\arraystretch}{1.2}
\small
\begin{tabular}{p{0.28\textwidth}p{0.2\textwidth}p{0.66\textwidth}}
\sptablerule
\multicolumn{3}{l}{\textit{Column names, utypes, datatypes, and descriptions for the rr.res\_table table}}\\
\sptablerule

\baselineskip=9pt\relax ivoid\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/identifier}&
\footnotesize string&
The parent resource.\\

\baselineskip=9pt\relax schema\_index\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily }&
\footnotesize (key)&
Index of the schema this table belongs to, if it belongs to a schema (otherwise NULL).\\

\baselineskip=9pt\relax table\_description\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:description}&
\footnotesize string&
A free-text description of the table's contents.\\

\baselineskip=9pt\relax table\_name\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:name}&
\footnotesize string&
The fully qualified name of the table. As per VODataService, this includes all catalog or schema prefixes needed to distinguish it in a query, and it comes with SQL delimiters where necessary.\\

\baselineskip=9pt\relax table\_index\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily }&
\footnotesize (key)&
An arbitrary identifier for the tables belonging to a resource.\\

\baselineskip=9pt\relax table\_title\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:title}&
\footnotesize string&
A descriptive, human-interpretable name for the table.\\

\baselineskip=9pt\relax table\_type\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:@type}&
\footnotesize string&
A name for the role this table plays. Recognized values include "output", indicating this table is output from a query; "base\_table", indicating a table whose records represent the main subjects of its schema; and "view", indicating that the table represents a useful combination or subset of other tables. Other values are allowed.\\

\baselineskip=9pt\relax table\_utype\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:utype}&
\footnotesize string&
An identifier for a concept in a data model that the data in this table as a whole represent.\\

\sptablerule
\end{tabular}
\end{inlinetable}


% /GENERATED



This table should have an explicit primary key made up of
\rtent{ivoid} and \rtent{table_index}.  The
\rtent{ivoid} column should be an explicit
foreign key into \rtent{resource}.  It is recommended to
maintain an index on at least the \rtent{table_description}
column, ideally one suited for queries with \rtent{ivo_hasword}.  Since
\rtent{table_utype} is used in data discovery, it should also be indexed.

The following columns MUST be lowercased during ingestion:
\rtent{ivoid}, \rtent{table_type},
\rtent{table_utype}.
Clients are advised to query the \rtent{table_description}
and \rtent{table_title}  columns
using the the \rtent{ivo_hasword} function.



\subsection{The table\_column Table}

\label{table_table_column}

The \rtent{table_column}  table models the content of VODataService's
\vorent{column} element.  The table has been renamed to avoid
a name clash with the SQL reserved word \texttt{COLUMN}.

Since it is expected that queries for column properties will be
fairly common in advanced queries, it is the column table that has the
unprefixed versions of common member names (name,  ucd,
utype, etc).

The \rtent{flag} column contains a concatenation of all values
of a \vorent{column} element's \vorent{flag} children, separated
by hash characters.  Use the \rtent{ivo_hashlist_has} function in
queries against \rtent{flag}.

The \rtent{table_column} table also includes information from
VODataService's data type concept.  VODataService 1.1 includes several type
systems (VOTable, ADQL, Simple).  The
\rtent{type_system} column contains the value of the column's
\vorent{datatype} child, with the VODataService XML prefix fixed
to vs; hence, this column will contain one of NULL,
\texttt{vs:taptype},
\texttt{vs:simpledatatype}, and \texttt{vs:votabletype}.  Modern
resource records should always use \texttt{vs:votabletype}, but
column declarations using the other type systems are still present in
the VO.


% GENERATED: maketable.sh rr.table_column

\begin{inlinetable}
\renewcommand*{\arraystretch}{1.2}
\small
\begin{tabular}{p{0.28\textwidth}p{0.2\textwidth}p{0.66\textwidth}}
\sptablerule
\multicolumn{3}{l}{\textit{Column names, utypes, datatypes, and descriptions for the rr.table\_column table}}\\
\sptablerule

\baselineskip=9pt\relax ivoid\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/identifier}&
\footnotesize string&
The parent resource.\\

\baselineskip=9pt\relax table\_index\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily }&
\footnotesize (key)&
Index of the table this column belongs to.\\

\baselineskip=9pt\relax name\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:name}&
\footnotesize string&
The name of the column.\\

\baselineskip=9pt\relax ucd\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:ucd}&
\footnotesize string&
A unified content descriptor that describes the scientific content of the column.\\

\baselineskip=9pt\relax unit\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:unit}&
\footnotesize string&
The unit associated with all values in the column.\\

\baselineskip=9pt\relax utype\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:utype}&
\footnotesize string&
An identifier for a role in a data model that the data in this column represents.\\

\baselineskip=9pt\relax std\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:@std}&
\footnotesize integer&
If 1, the meaning and use of this column is reserved and defined by a standard model. If 0, it represents a database-specific column that effectively extends beyond the standard.\\

\baselineskip=9pt\relax datatype\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:dataType}&
\footnotesize string&
The type of the data contained in the column.\\

\baselineskip=9pt\relax extended\_schema\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:dataType/@extendedSchema}&
\footnotesize string&
An identifier for the schema that the value given by the extended attribute is drawn from.\\

\baselineskip=9pt\relax extended\_type\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:dataType/@extendedType}&
\footnotesize string&
A custom type for the values this column contains.\\

\baselineskip=9pt\relax arraysize\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:dataType/@arraysize}&
\footnotesize string&
The shape of the array that constitutes the value, e.g., 4, *, 4*, 5x4, or 5x*, as specified by VOTable.\\

\baselineskip=9pt\relax delim\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:dataType/@delim}&
\footnotesize string&
The string that is used to delimit elements of an array value when arraysize is not '1'.\\

\baselineskip=9pt\relax type\_system\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:dataType/@xsi:type}&
\footnotesize string&
The type system used, as a QName with a canonical prefix; this will ususally be one of vs:simpledatatype, vs:votabletype, and vs:taptype.\\

\baselineskip=9pt\relax flag\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:flag}&
\footnotesize string&
Hash-separated keywords representing traits of the column. Recognized values include "indexed", "primary", and "nullable".\\

\baselineskip=9pt\relax column\_description\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:description}&
\footnotesize string&
A free-text description of the column's contents.\\

\sptablerule
\end{tabular}
\end{inlinetable}


% /GENERATED



The pair \rtent{ivoid}, \rtent{table_index} should be an
explicit foreign key into \rtent{res_table}.  It is recommended to
maintain indexes on at least the \rtent{column_description},
\rtent{name}, \rtent{ucd}, and \rtent{utype} columns,
where the index on \rtent{column_description} should ideally be able
to handle queries using \rtent{ivo_hasword}.

The following columns MUST be lowercased during ingestion:
\rtent{ivoid}, \rtent{name}, \rtent{ucd},
\rtent{utype}, \rtent{datatype}, \rtent{type_system}.
The boolean value of the column's \rtent{std} attribute must be
converted to 0 (False), 1 (True), or NULL (not given) on ingestion.
Clients are advised to query the \rtent{description}
column using the \rtent{ivo_hasword} function, and to query
the \rtent{flag} column using the \rtent{ivo_hashlist_has}
function.



\subsection{The interface Table}

\label{table_interface}

The \rtent{interface} table subsumes both the
\vorent{vr:Interface} and \vorent{vr:access\-URL} types from
VOResource.  The integration of \vorent{access\-URL}  into
the \rtent{interface}  table means that an interface in the
relational registry can only have one access URL, where in VOResource it
can have many.  VOResource 1.1 deprecated that capability (that was
never really used in practice anyway) and replaced it with
\vorent{mirrorURL}.
In the unlikely case multiple \vorent{accessURL} are defined in a single
interface nevertheless, implementation behavior for a RegTAP service is
undefined.

The table contains a column \rtent{intf_index} to disambiguate
multiple interfaces of one resource. See section \ref{primarykeys} for details.

In VOResource, interfaces can have zero or more \vorent{securityMethod}
children to convey support for authentication and authorization methods.
Apart from an identifier for an authentication method -- usually taken
from the SSO document \citet{2017ivoa.spec.0524T} --, no actual content
has been specified so far for these elements.  Also, there are as of now
no actual discovery cases employing this information except ``filter out
services requiring authentication''.  Hence, RegTAP 1.2 does not attempt
to map \vorent{securityMethod} except through the
\rtent{authenticated_only} column which is required to be 0 when there
is no \vorent{securityMethod} or at least one \vorent{securityMethod}
without a \vorent{standardId} on an
interface, 1 otherwise.

Clients not prepared to authenticate to services should always include a
\verb|authenticated_only=0| condition when retrieving access URLs
from RegTAP 1.2 services, as it is conceivable that a future VO will
contain many services requiring authentication and users should not have
to try out which of them they can actually use.

The \rtent{query_type} column is a hash-joined list (analogous
to \rtent{waveband} in the resource table), as
the XML schema allows listing up to two request methods.

The \rtent{mirror_url} column is used to keep all mirror URLs in one
field, again separating values with hash characters.  This design was
chosen over a native array since arrays of variable-length strings are
not supported by VOTable, and emulating them is a major implementation
liability.  It was chosen over a separate database table implementing
the 1:n relation because the hash -- a fragment identifier in URIs, and
access fragments are meaningless for access URLs -- happens to be a safe and
convenient separator for the datatype, and thus there is no semantic
cost attached to using an array emulation that is simpler on both client
and server.  Note that contrary to \rtent{query_type} and similar
hash-joined lists of enumerated values, \emph{no} case normalisation
may take place in \rtent{mirror_url}.

This table only contains interface elements from within capabilities.
Interface elements in StandardsRegExt records are ignored in the
relational registry,
and they must not be inserted in this table, since doing so would disturb
the foreign key from interface into capability.  In other words,
the relational registry requires every interface to have a parent capability.

Analogous to \rtent{resource.res_type}, the
\rtent{intf_type} column contains type names; VOResource extensions
can define new types here, but at the time of writing, the following
types are mentioned in IVOA-recommended schemata:


\begin{description}
\item[vs:paramhttp]A service invoked via an HTTP query, usually with some form of
structured parameters. This type is used for interfaces speaking
``simple'' IVOA protocols.
\item[vr:webbrowser]A (form-based) interface intended to be accessed interactively by a
user via a web browser.
\item[vg:oaihttp]A standard OAI PMH interface using HTTP queries with form-urlencoded
parameters.
\item[vg:oaisoap]A standard OAI PMH interface using a SOAP Web Service
interface.
\item[vr:webservice]A Web Service that is describable by a WSDL document.

\end{description}


% GENERATED: maketable.sh rr.interface

\begin{inlinetable}
\renewcommand*{\arraystretch}{1.2}
\small
\begin{tabular}{p{0.28\textwidth}p{0.2\textwidth}p{0.66\textwidth}}
\sptablerule
\multicolumn{3}{l}{\textit{Column names, utypes, datatypes, and descriptions for the rr.interface table}}\\
\sptablerule

\baselineskip=9pt\relax ivoid\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/identifier}&
\footnotesize string&
The parent resource.\\

\baselineskip=9pt\relax cap\_index\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily }&
\footnotesize (key)&
The index of the parent capability.\\

\baselineskip=9pt\relax intf\_index\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily }&
\footnotesize (key)&
An arbitrary identifier for the interfaces of a resource.\\

\baselineskip=9pt\relax intf\_type\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:@xsi:type}&
\footnotesize string&
The type of the interface (vr:webbrowser, vs:paramhttp, etc).\\

\baselineskip=9pt\relax intf\_role\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:@role}&
\footnotesize string&
An identifier for the role the interface plays in the particular capability. If the value is equal to "std" or begins with "std:", then the interface refers to a standard interface defined by the standard referred to by the capability's standardID attribute.\\

\baselineskip=9pt\relax std\_version\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:@version}&
\footnotesize string&
The version of a standard interface specification that this interface complies with. When the interface is provided in the context of a Capability element, then the standard being refered to is the one identified by the Capability's standardID element.\\

\baselineskip=9pt\relax query\_type\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:queryType}&
\footnotesize string&
Hash-joined list of expected HTTP method (get or post) supported by the service.\\

\baselineskip=9pt\relax result\_type\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:resultType}&
\footnotesize string&
The MIME type of a document returned in the HTTP response.\\

\baselineskip=9pt\relax wsdl\_url\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:wsdlURL}&
\footnotesize string&
The location of the WSDL that describes this Web Service. If NULL, the location can be assumed to be the accessURL with '?wsdl' appended.\\

\baselineskip=9pt\relax url\_use\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:accessURL/@use}&
\footnotesize string&
A flag indicating whether this should be interpreted as a base URL ('base'), a full URL ('full'), or a URL to a directory that will produce a listing of files ('dir').\\

\baselineskip=9pt\relax access\_url\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:accessURL}&
\footnotesize string&
The URL at which the interface is found.\\

\baselineskip=9pt\relax mirror\_url\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:mirrorURL}&
\footnotesize string&
Secondary access URLs of this interface, separated by hash characters.\\

\baselineskip=9pt\relax authenticated\_only\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily }&
\footnotesize integer&
A flag for whether an interface is available for anonymous use (=0) or only authenticated clients are served (=1).\\

\sptablerule
\end{tabular}
\end{inlinetable}


% /GENERATED

This table should have the pair \rtent{ivoid}, \rtent{cap_index}
as an explicit foreign key into
\rtent{capability}, and the pair \rtent{ivoid}, and
\rtent{intf_index} as an explicit primary key. Additionally, it
is recommended to maintain an index on at least the
\rtent{intf_type} column.

The following columns MUST be lowercased during ingestion:
\rtent{ivoid}, \rtent{intf_type}, \rtent{intf_role},
\rtent{std_version}, \rtent{query_type},
\rtent{result_type}, \rtent{url_use},
Clients are advised to query \rtent{query_type} using the the
\rtent{ivo_hashlist_has} function.



\subsection{The intf\_param Table}

\label{table_intf_param}

The \rtent{intf_param} table keeps information on the parameters
available on interfaces.  It is therefore closely related to
\rtent{table_column}, but the differences between the two are
significant enough to warrant a separation between the two tables.
Since the names of common column attributes are used where applicable in
both tables (e.g., name, ucd, etc), the two tables cannot be (naturally)
joined.


% GENERATED: maketable.sh rr.intf_param

\begin{inlinetable}
\renewcommand*{\arraystretch}{1.2}
\small
\begin{tabular}{p{0.28\textwidth}p{0.2\textwidth}p{0.66\textwidth}}
\sptablerule
\multicolumn{3}{l}{\textit{Column names, utypes, datatypes, and descriptions for the rr.intf\_param table}}\\
\sptablerule

\baselineskip=9pt\relax ivoid\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/identifier}&
\footnotesize string&
The parent resource.\\

\baselineskip=9pt\relax intf\_index\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily }&
\footnotesize (key)&
The index of the interface this parameter belongs to.\\

\baselineskip=9pt\relax name\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:name}&
\footnotesize string&
The name of the parameter.\\

\baselineskip=9pt\relax ucd\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:ucd}&
\footnotesize string&
A unified content descriptor that describes the scientific content of the parameter.\\

\baselineskip=9pt\relax unit\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:unit}&
\footnotesize string&
The unit associated with all values in the parameter.\\

\baselineskip=9pt\relax utype\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:utype}&
\footnotesize string&
An identifier for a role in a data model that the data in this parameter represents.\\

\baselineskip=9pt\relax std\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:@std}&
\footnotesize integer&
If 1, the meaning and use of this parameter is reserved and defined by a standard model. If 0, it represents a database-specific parameter that effectively extends beyond the standard.\\

\baselineskip=9pt\relax datatype\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:dataType}&
\footnotesize string&
The type of the data contained in the parameter.\\

\baselineskip=9pt\relax extended\_schema\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:dataType/@extendedSchema}&
\footnotesize string&
An identifier for the schema that the value given by the extended attribute is drawn from.\\

\baselineskip=9pt\relax extended\_type\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:dataType/@extendedType}&
\footnotesize string&
A custom type for the values this parameter contains.\\

\baselineskip=9pt\relax arraysize\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:dataType/@arraysize}&
\footnotesize string&
The shape of the array that constitutes the value, e.g., 4, *, 4*, 5x4, or 5x*, as specified by VOTable.\\

\baselineskip=9pt\relax delim\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:dataType/@delim}&
\footnotesize string&
The string that is used to delimit elements of an array value when arraysize is not '1'.\\

\baselineskip=9pt\relax param\_use\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:@use}&
\footnotesize string&
An indication of whether this parameter is required to be provided for the application or service to work properly (one of required, optional, ignored, or NULL).\\

\baselineskip=9pt\relax param\_description\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:description}&
\footnotesize string&
A free-text description of the parameter's contents.\\

\sptablerule
\end{tabular}
\end{inlinetable}


% /GENERATED



The pair \rtent{ivoid}, \rtent{intf_index} should be an explicit
foreign key into \rtent{interface}.

The remaining requirements and conventions are as per
section \ref{table_table_column}
where applicable, and \rtent{param_description} taking the role
of \rtent{column_description}.



\subsection{The relationship Table}

\label{table_relationship}

The relationship element is a slight denormalization of the
\vorent{vr:Relation\-ship} type: whereas in VOResource, a single
relationship element can take several IVOIDs, in the relational model,
the pairs are stored directly.  It is straightforward to translate
between the two representations in the database ingestor.


% GENERATED: maketable.sh rr.relationship

\begin{inlinetable}
\renewcommand*{\arraystretch}{1.2}
\small
\begin{tabular}{p{0.28\textwidth}p{0.2\textwidth}p{0.66\textwidth}}
\sptablerule
\multicolumn{3}{l}{\textit{Column names, utypes, datatypes, and descriptions for the rr.relationship table}}\\
\sptablerule

\baselineskip=9pt\relax ivoid\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/identifier}&
\footnotesize string&
The parent resource.\\

\baselineskip=9pt\relax relationship\_type\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:relationshipType}&
\footnotesize string&
The type of the relationship; these terms are drawn from a controlled vocabulary and are DataCite-compatible.\\

\baselineskip=9pt\relax related\_id\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:relatedResource/@ivo-id}&
\footnotesize string&
The IVOA identifier for the resource referred to.\\

\baselineskip=9pt\relax related\_name\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:relatedResource}&
\footnotesize string&
The name of resource that this resource is related to.\\

\sptablerule
\end{tabular}
\end{inlinetable}


% /GENERATED



The \rtent{ivoid} column should be an explicit foreign key into the
\rtent{resource} table.  You should index at least the
\rtent{related_id} column.

The following columns MUST be lowercased during ingestion:
\rtent{ivoid}, \rtent{relationship_type},
\rtent{related_id}.

The content of incoming \vorent{relationshipType}
elements must be normalized according to the rules laid down in
sect.~\ref{sect:vocab-use} before lowercasing.


\subsection{The validation Table}

\label{table_validation}

The \rtent{validation} table subsumes the
\vorent{vr:validationLevel}-typed members of both \vorent{vr:Resource}
and \vorent{vr:Capability}.

If the \rtent{cap_index} column is \texttt{NULL}, the
validation comprises the entire resource.  Otherwise, only the
referenced capability has been validated.

While it is recommended that harvesters only accept resource records
from their originating registries, it is valuable to gather validation
results from various sources.  Hence, harvesters for the relational
registry may choose to obtain validation data from the OAI-PMH endpoints
of various registries by not harvesting just for the \emph{ivo\_managed} set and
generate \rtent{rr.validation} rows from these records.  This can
trigger potentially problematic behavior when the original registry
updates  its resource record in that naive implementations will lose all
third-party validation rows; this may actually be the correct behavior,
since an update of the registry record might very well indicate
validation-relevant changes in the underlying services.  Implementations
are free to handle or ignore validation results as they see fit, and
they may add validation results of their own.

The validation levels are defined in \citet{2007ivoa.spec.0302H} and
currently range from 0 (description stored in a registry) to
4 (inspected by a human to be technically and scientifically
correct).


% GENERATED: maketable.sh rr.validation

\begin{inlinetable}
\renewcommand*{\arraystretch}{1.2}
\small
\begin{tabular}{p{0.28\textwidth}p{0.2\textwidth}p{0.66\textwidth}}
\sptablerule
\multicolumn{3}{l}{\textit{Column names, utypes, datatypes, and descriptions for the rr.validation table}}\\
\sptablerule

\baselineskip=9pt\relax ivoid\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/identifier}&
\footnotesize string&
The parent resource.\\

\baselineskip=9pt\relax validated\_by\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:validationLevel/@validatedBy}&
\footnotesize string&
The IVOA ID of the registry or organisation that assigned the validation level.\\

\baselineskip=9pt\relax val\_level\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:validationLevel}&
\footnotesize integer&
A numeric grade describing the quality of the resource description, when applicable, to be used to indicate the confidence an end-user can put in the resource as part of a VO application or research study.\\

\baselineskip=9pt\relax cap\_index\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily }&
\footnotesize (key)&
If non-NULL, the validation only refers to the capability referenced here.\\

\sptablerule
\end{tabular}
\end{inlinetable}


% /GENERATED



The \rtent{ivoid} column should be an explicit foreign key into
\rtent{resource}.  Note, however, that \rtent{ivoid},
\rtent{cap_index} is \emph{not} a foreign key into \rtent{capability}
since \rtent{cap_index} may be \texttt{NULL} (in case the validation
addresses the entire resource).

The following columns MUST be lowercased during ingestion:
\rtent{ivoid}, \rtent{validated_by}.



\subsection{The res\_date Table}

\label{table_res_date}

The \rtent{res_date} table contains information gathered from
\vorent{vr:Curation}'s date children.


% GENERATED: maketable.sh rr.res_date

\begin{inlinetable}
\renewcommand*{\arraystretch}{1.2}
\small
\begin{tabular}{p{0.28\textwidth}p{0.2\textwidth}p{0.66\textwidth}}
\sptablerule
\multicolumn{3}{l}{\textit{Column names, utypes, datatypes, and descriptions for the rr.res\_date table}}\\
\sptablerule

\baselineskip=9pt\relax ivoid\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/identifier}&
\footnotesize string&
The parent resource.\\

\baselineskip=9pt\relax date\_value\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:date}&
\footnotesize character[19]\hfil\break+timestamp&
A date associated with an event in the life cycle of the resource.\\

\baselineskip=9pt\relax value\_role\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:date/@role}&
\footnotesize string&
A string indicating what the date refers to, e.g., created, availability, updated. This value is generally drawn from a controlled vocabulary.\\

\sptablerule
\end{tabular}
\end{inlinetable}


% /GENERATED



The \rtent{ivoid} column should be an explicit foreign key into
\rtent{resource}.

The following columns MUST be lowercased during ingestion:
\rtent{ivoid}, \rtent{value_role}.

The content of incoming \vorent{date/@role}
attributes must be normalized according to the rules laid down in
sect.~\ref{sect:vocab-use} before lowercasing.


\subsection{The res\_detail Table}

\label{table_res_detail}

The \rtent{res_detail} table is the relational registry's primary means for
extensibility as well as a fallback for less-used simple
metadata.  Conceptually, it stores triples of resource entity
references, resource xpaths,
and values, where resource entities can be resource records themselves
or capabilities.  Thus, metadata with values that are either
string-valued or sets of strings can be represented in this table.

As long as the metadata that needs to be represented in the
relational registry for new VOResource extensions is simple enough, no changes to the schema defined
here will be necessary as these are introduced.  Instead, the extension itself simply defines
new xpaths to be added in \rtent{res_detail}.

Some complex metadata -- \vorent{tr:languageFeature} or
\vorent{vstd:key} being examples -- cannot be kept in this table.
If a representation of such information in the relational registry is
required, this standard will need to be changed.

Appendix \ref{d_u_list} gives a list
of resource xpaths from the registry extensions
that were recommendations at the time of writing.
For the resource xpaths marked with an exclamation mark there,
xpath/value pairs MUST be generated whenever the corresponding
metadata items are given in a resource record.
For the remaining resource xpaths, these pairs should be provided if
convenient; they mostly concern test queries and other curation-type
information that, while unlikely to be useful to normal users, may be
relevant to curation-type clients that, e.g., ascertain the continued
operation of services.

Some detail values must be interpreted case-insensitively; this
concerns, in particular, IVOID like the TAP data model type.  For other
rows -- the test queries are immediate examples -- , changing the case
will likely break the data.  In order to avoid having to give and
implement case normalization rules by detail xpath, no case normalization
is done on detail values at all, and users and clients will have to use
\verb|ivo_nocasematch| when locating
case-insensitive values.  For the resource xpaths given in Appendix \ref{d_u_list}, this concerns all items with xpaths ending
in \texttt{@ivo-id}.

Individual
ingestors
MAY choose to expose additional metadata using other xpaths, provided
they are formed according to the rules in
section \ref{vorutypes} (this rule is intended
to minimize the risk of later clashes).

In addition to the metadata listed in this specification,
metadata defined in future
IVOA-approved VOResource extensions MUST or SHOULD be present in
\rtent{res_detail} as the extensions require it.


% GENERATED: maketable.sh rr.res_detail

\begin{inlinetable}
\renewcommand*{\arraystretch}{1.2}
\small
\begin{tabular}{p{0.28\textwidth}p{0.2\textwidth}p{0.66\textwidth}}
\sptablerule
\multicolumn{3}{l}{\textit{Column names, utypes, datatypes, and descriptions for the rr.res\_detail table}}\\
\sptablerule

\baselineskip=9pt\relax ivoid\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/identifier}&
\footnotesize string&
The parent resource.\\

\baselineskip=9pt\relax cap\_index\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily }&
\footnotesize (key)&
The index of the parent capability; if NULL the xpath-value pair describes a member of the entire resource.\\

\baselineskip=9pt\relax detail\_xpath\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily }&
\footnotesize string&
The xpath of the data item.\\

\baselineskip=9pt\relax detail\_value\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily }&
\footnotesize string&
(Atomic) value of the member.\\

\sptablerule
\end{tabular}
\end{inlinetable}


% /GENERATED



The \rtent{ivoid} column should be an explicit foreign key into
\rtent{resource}.  It is recommended to maintain indexes on
at least the columns
\rtent{detail_xpath} and \rtent{detail_value}, where the
index on \rtent{detail_value} should ideally work for both direct
comparisons and searches using \verb|ivo_nocasematch|.

The following column MUST be lowercased during ingestion:
\rtent{ivoid}.  Clients are advised to
use \verb|ivo_nocasematch| to search in
\rtent{detail_value} if the values are to be compared
case-insensitively (e.g., all IVOIDs).

\subsection{The alt\_identifier Table}
\label{table_alt_identifier}

Since its version 1.1, VOResource allows the annotation of various
elements (initially, the record itself and creators) with alternate
identifiers (the \vorent{alt\-Iden\-ti\-fier} element).  Examples of these are
DOIs, ORCIDs, and bibcodes.

Considering that that the typical query against the alternate
identifiers can be expected to be of the type ``records having to do
with \emph{identifier}'' and since the identifiers
are stored in URI form and hence identifiers of different types
cannot clash, RegTAP does not keep track
where an alternate identifier was encountered.  Instead, the
\rtent{alt_identifier} table just links IVOIDs and alternate
identifiers:


% GENERATED: maketable.sh rr.alt_identifier

\begin{inlinetable}
\renewcommand*{\arraystretch}{1.2}
\small
\begin{tabular}{p{0.28\textwidth}p{0.2\textwidth}p{0.66\textwidth}}
\sptablerule
\multicolumn{3}{l}{\textit{Column names, utypes, datatypes, and descriptions for the rr.alt\_identifier table}}\\
\sptablerule

\baselineskip=9pt\relax ivoid\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/identifier}&
\footnotesize string&
The parent resource.\\

\baselineskip=9pt\relax alt\_identifier\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily }&
\footnotesize string&
An identifier for the resource or an entity related to the resource in URI form.\\

\sptablerule
\end{tabular}
\end{inlinetable}


% /GENERATED

The \rtent{ivoid} column should be an explicit foreign key into
\rtent{resource}.  It is recommended to maintain an index on
the \rtent{alt_identifier} column.

\subsection{The stc\_spatial Table}
\label{table_stc_spatial}

Since VODataService 1.2, registry records can represent their resource's
spatial coverage using spatial MOCs \citep{2022ivoa.spec.0727F}.  The
\rtent{stc_spatial} table is a direct reflection of this metadata:

% GENERATED: maketable.sh rr.stc_spatial

\begin{inlinetable}
\renewcommand*{\arraystretch}{1.2}
\small
\begin{tabular}{p{0.28\textwidth}p{0.2\textwidth}p{0.66\textwidth}}
\sptablerule
\multicolumn{3}{l}{\textit{Column names, utypes, datatypes, and descriptions for the rr.stc\_spatial table}}\\
\sptablerule

\baselineskip=9pt\relax ivoid\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/identifier}&
\footnotesize string&
The parent resource.\\

\baselineskip=9pt\relax coverage\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:.}&
\footnotesize string\hfil\break+moc&
A geometry representing the area a resource contains data for; this should be tight at least with a resolution of degrees.\\

\baselineskip=9pt\relax ref\_system\_name\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:@frame}&
\footnotesize string&
The reference frame coverage is written in. This is currently reserved and fixed to NULL. Clients should always add a constraint to NULL for this to avoid matching non-celestial resources later.\\

\sptablerule
\end{tabular}
\end{inlinetable}


% /GENERATED

The \rtent{ivoid} column should be an explicit foreign key into
\rtent{resource}.

The details of how the MOC-valued coverage is entered and retrieved
will be given in version 1.2 of DALI \citep{2017ivoa.spec.0517D}.
Implementations MUST
evaluate the ADQL CONTAINS and INTERSECTS predicates with
coverage as one argument and ADQL CIRCLEs and POLYGONs as the other, and
they must support CONTAINS with an ADQL POINT in the first argument.
There are no expectations that the predicates are computed exactly, but
implementations should strive to limit the number of false positives;
clients are advised that on services supporting MOC literals, it is
probably much faster and more exact to use MOC-MOC comparisons to query
\rtent{coverage}.

\subsection{The stc\_temporal Table}
\label{table_stc_temporal}

Since VODataService 1.2, registry records can represent their resource's
temporal coverage as a union of time intervals.  The
\rtent{stc_temporal} table is a direct reflection of this metadata:

% GENERATED: maketable.sh rr.stc_temporal

\begin{inlinetable}
\renewcommand*{\arraystretch}{1.2}
\small
\begin{tabular}{p{0.28\textwidth}p{0.2\textwidth}p{0.66\textwidth}}
\sptablerule
\multicolumn{3}{l}{\textit{Column names, utypes, datatypes, and descriptions for the rr.stc\_temporal table}}\\
\sptablerule

\baselineskip=9pt\relax ivoid\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/identifier}&
\footnotesize string&
The parent resource.\\

\baselineskip=9pt\relax time\_start\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:.}&
\footnotesize real&
Lower limit of a time interval covered by the resource in MJD.\\

\baselineskip=9pt\relax time\_end\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:.}&
\footnotesize real&
Upper limit of a time interval covered by the resource in MJD.\\

\sptablerule
\end{tabular}
\end{inlinetable}


% /GENERATED

The \rtent{ivoid} column should be an explicit foreign key into
\rtent{resource}.

Clients are advised that the \verb|ivo_interval_overlaps| user defined
function is available to conveniently compare a user-specified interval
of interest to \rtent{time_start} $\cdots$ \rtent{time_end}.

Since VODataService temporal coverage is given in MJD,
the rows for \rtent{time_start} and \rtent{time_end} in
\tapent{TAP\_SCHEMA.columns} MUST have the appropriate VOUnits
\citep{2023ivoa.spec.1215G} string,
\texttt{d}, in their
\tapent{unit} column.


\subsection{The stc\_spectral Table}
\label{table_stc_spectral}

Since VODataService 1.2, registry records can represent their resource's
spectral coverage as a union of energy intervals.  The
\rtent{stc_spectral} table is a direct reflection of this metadata:

% GENERATED: maketable.sh rr.stc_spectral

\begin{inlinetable}
\renewcommand*{\arraystretch}{1.2}
\small
\begin{tabular}{p{0.28\textwidth}p{0.2\textwidth}p{0.66\textwidth}}
\sptablerule
\multicolumn{3}{l}{\textit{Column names, utypes, datatypes, and descriptions for the rr.stc\_spectral table}}\\
\sptablerule

\baselineskip=9pt\relax ivoid\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:/identifier}&
\footnotesize string&
The parent resource.\\

\baselineskip=9pt\relax spectral\_start\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:.}&
\footnotesize real&
Lower limit (in Joules) of messenger energy interval covered by the resource (for the solar system barycenter).\\

\baselineskip=9pt\relax spectral\_end\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:.}&
\footnotesize real&
Upper limit (in Joules) of messenger energy interval covered by the resource (for the solar system barycenter).\\

\sptablerule
\end{tabular}
\end{inlinetable}


% /GENERATED

The \rtent{ivoid} column should be an explicit foreign key into
\rtent{resource}.

Clients are advised that the \verb|ivo_interval_overlaps| user defined
function is available to conveniently compare a user-specified intervals
of interest to \rtent{spectral_start} $\cdots$ \rtent{spectral_end}.

Since VODataService spectral coverage gives energies in Joules,
the rows for \rtent{spectral_start} and \rtent{spectral_end} in
\tapent{TAP\_SCHEMA.columns} MUST have the appropriate VOUnits
string, \texttt{J}, in their
\tapent{unit} column.


\subsection{The tap\_table View}
\label{table_tap_table}

Several Registry clients need to easily obtain metadata on tables
queriable through TAP.  Since the Registry data model gives services
some freedom registering these -- they can occur in tablesets of TAP
services, in tablesets of records having auxiliary TAP capabilities
\citep{2019ivoa.spec.0520D}, or both -- it is hard to write ADQL
producing such a relation.  Hence, starting with version 1.2 of RegTAP,
implementing
services must provide a view encapsulating a query yielding all tables
in \rtent{rr.res_table}

\begin{enumerate}
\item that are accessible through a TAP service
\item and are not declared as \emph{output} tables (which by definition
cannot be queried)
\item exactly once for each actual table (i.e., there cannot be two rows
in the view having the same (\rtent{svcid}, \rtent{table_name}))
\item with references to both a full metadata record and the record of
the TAP service publishing the resource.\label{cond:fullmeta}
\end{enumerate}

Condition~\ref{cond:fullmeta} requires an explanation: A given table can
be both in the tableset of the TAP service (that will in general have
very little additional information on the table) and in the tableset of
a specific resource (which will contain rich metadata on the table).  In
the latter case, \rtent{tap_table} must reference the specific resource
as the full metadata record (the \rtent{resid} column).  Tables only
present in their TAP services' tableset will have identical
\rtent{resid} and \rtent{svcid}.

The \rtent{tap_table} view has the following columns:


% GENERATED: maketable.sh rr.tap_table

\begin{inlinetable}
\renewcommand*{\arraystretch}{1.2}
\small
\begin{tabular}{p{0.28\textwidth}p{0.2\textwidth}p{0.66\textwidth}}
\sptablerule
\multicolumn{3}{l}{\textit{Column names, utypes, datatypes, and descriptions for the rr.tap\_table table}}\\
\sptablerule

\baselineskip=9pt\relax resid\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily }&
\footnotesize string&
IVOA identifier of the resource this table was taken from (where there is a dedicated resource containing this table in its tableset, that resource is preferred over a TAP service).\\

\baselineskip=9pt\relax svcid\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily }&
\footnotesize string&
IVOA identifier of the TAP service making this table queriable.\\

\baselineskip=9pt\relax table\_name\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:name}&
\footnotesize string&
The fully qualified name of the table. As per VODataService, this includes all catalog or schema prefixes needed to distinguish it in a query, and it comes with SQL delimiters where necessary.\\

\baselineskip=9pt\relax table\_title\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:title}&
\footnotesize string&
A descriptive, human-interpretable name for the table.\\

\baselineskip=9pt\relax table\_description\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:description}&
\footnotesize string&
A free-text description of the table's contents.\\

\baselineskip=9pt\relax table\_utype\hfil\break
\makebox[0pt][l]{\scriptsize\ttfamily xpath:utype}&
\footnotesize string&
An identifier for a concept in a data model that the data in this table as a whole represent.\\

\sptablerule
\end{tabular}
\end{inlinetable}


% /GENERATED

Since \rtent{rr.tap_table} is (at least conceptually; this specification
does not forbid making it a materialised view or a physical table) a
view, it inherits the properties of the contributing tables.  This means
that \rtent{table_title} and \rtent{table_description} should be queried
using \texttt{ivo\_hasword}, and that \rtent{table_utype} should have an
index.  By construction, (\rtent{svcid}, \rtent{table_name}) is suitable
as a primary key of the relation.

Appendix~\ref{app:tap-table-viewdef} gives a standard SQL query that
will produce the view specified here from other RegTAP tables.


\section{RegTAP Requirements on TAP services}

Since RegTAP 1.2, implementing services MUST implement at least version
2.1 of ADQL.

Since RegTAP deals with text much more intensively than is usual for the
astrophysical data that TAP and ADQL were designed for and some query
patterns uncommon in astrophysics significantly help writing RegTAP
queries, TAP services implementing RegTAP MUST implement some ADQL
extensions, partly specified as ADQL optional features, partly in ADQL
User Defined Functions (UDFs).


\subsection{ADQL Optional Features Required for RegTAP}

TAP Servers implementing the
\texttt{ivo://ivoa.net/std/regtap\#1.2} data model MUST implement the
following optional features defined in ADQL 2.1 \citep{2023ivoa.spec.1215M}:

\begin{bigdescription}
\item[COALESCE] Primarily in order to make the use of
\verb|ivo_string_agg| predictable in the presence of NULL values in
columns like \rtent{standard_id}, RegTAP services MUST provide the
COALESCE feature in
\nolinkurl{ivo://ivoa.net/std/tapregext#features-adql-conditional}.

\item[ILIKE] As a standard alternative to \verb|ivo_nocasematch| as
employed by RegTAP earlier than 1.2, RegTAP services MUST provide the
ILIKE feature in
\nolinkurl{ivo://ivoa.net/std/tapregext#features-adql-string}.

\item[WITH] To let clients more clearly structure their queries, RegTAP
services MUST implement common table expressions as per the WITH feature
in \nolinkurl{ivo://ivoa.net/std/tapregext#features-adql-common-table}.
\end{bigdescription}


\subsection{User Defined Functions Required for RegTAP}
\label{adqludf}

TAP Servers implementing the
\texttt{ivo://ivoa.net/std/regtap\#1.2} data model MUST
implement the following User Defined Functions in their ADQL language,
given here
with signatures written as recommended in TAPRegExt \citep{2012ivoa.spec.0827D}:


\begin{bigdescription}
\term[\small\texttt{ivo\_nocasematch(value VARCHAR(*), pat VARCHAR(*))->INTEGER}]
The function returns 1 if \texttt{pat}  matches
\texttt{value}, 0 otherwise.
\texttt{pat}  is defined as for the SQL LIKE operator, but the
match is performed case-insensitively.  Clients that only talk to RegTAP
1.2 and later should prefer the ILIKE operator.

\term[\small\texttt{ivo\_hasword(haystack VARCHAR(*), needle VARCHAR(*)) -> INTEGER}]The function takes two strings and returns 1 if the second is
contained in the first one in a ``word'' sense, i.e., delimited by
non-letter characters or the beginning or end of the string, where case
is ignored.  It returns 0 otherwise.
Additionally, servers MAY employ techniques to improve recall, in
particular stemming.  Registry clients must hence expect different results
from different servers when using \rtent{ivo_hasword}; for such
queries trying them on multiple registries may improve recall.
\term[\small\texttt{ivo\_hashlist\_has(hashlist VARCHAR(*), item VARCHAR(*)) -> INTEGER}]The function takes two strings; the first is a list of words not
containing the hash sign (\#), concatenated by hash signs, the second is
a word not containing the hash sign.  It returns 1 if, compared
case-insensitively, the second argument is in the list of words encoded in
the first argument, 0 otherwise.  The behavior for second
arguments containing a hash sign is undefined.
\term[\small\texttt{ivo\_string\_agg(expr VARCHAR(*), delim VARCHAR(*)) -> VARCHAR(*)}]An aggregate function returning all values of
\texttt{expr} within a GROUP concatenated with
\texttt{delim}.  NULLs in the aggregate do not contribute, an empty
aggregate yields an empty string.

\term[\small\texttt{ivo\_interval\_overlaps(l1 T, h1 T, l2 T, h2 T) -> INTEGER}]
The function returns 1 if the interval [l1...h1] overlaps with
the interval [l2...h2].  For the purposes of this function,
the case l1=h2 or l2=h1 is treated as overlap.  The function
returns 0 for non-overlapping intervals.  The function must be
available for both integers and floating point numbers; on most systems,
this will mean that \verb|T| is \verb|NUMERIC|.

\end{bigdescription}

Reference implementations of the functions for the PostgreSQL
database system are given in Appendix \ref{appPGDefs}.  As required for
UDFs with an \emph{ivo\_} prefix, these functions are also listed in the
Catalogue of ADQL User Defined Functions \citep{2023ivoa.spec.1117C}.



\section{Common Queries to the Relational Registry}

\label{sample_queries}

This section contains sample queries to the relational registry,
mostly contributed as use cases by various members of the IVOA Registry
working group.  They are intended as an aid in designing relational
registry queries, in particular for users new to the data model.

When locating access URLs for capabilities of standard services, these
sample queries limit the matches to interfaces declared with
\vorent{role} equal to \verb|std|.
This filters out \vorent{WebBrowser} interfaces that some data providers
add in SCS or SSAP capabilities (a practice not recommended).
Future standards might require more specific strings starting with
\verb|std:| in this place; discovery for those needs to be adapted
accordingly.

In RegTAP 1.0, this filtering was effected by constraining the interface
type to \vorent{vs:ParamHTTP}.  As discussed in that specification, this adopted
existing discovery patterns and worked around missing metadata in
VOResource records.   This workaround is no
longer necessary, and future standards should be free to use other
interface types rather than \vorent{vs:ParamHTTP}.

Note that it still is possible that a single resource will return
multiple access URLs with the query patterns given here.  Clients can
assume that all access URLs returned in this way correspond to their
constraints.  Therefore, it is legal to randomly pick one of those.

Service standards can give discovery patterns different from the ones
shown here if their particular use cases require them.

\subsection{TAP accessURLs}
\textbf{Problem:} Find all TAP services; return their accessURLs

As the capability type is in
\rtent{rr.capability}, whereas the access URL can be
found from
\rtent{rr.interface}, this requires
a (natural) join.

Clients communicating with a RegTAP 1.1 or later service should request the
\rtent{authenticated_only} column.  If this is 1, the service
requires some sort of authentication and should only presented to users
if a client has the necessary infrastructure for the authentication
system.

Hence, clients only interested in services not requiring authentication should
use

%CHECK_HERE
\begin{lstlisting}[language=SQL,flexiblecolumns=true]
SELECT ivoid, access_url
FROM rr.capability
NATURAL JOIN rr.interface
WHERE standard_id like 'ivo://ivoa.net/std/tap%'
  AND intf_role='std'
  AND authenticated_only=0
\end{lstlisting}

Analogous considerations apply to the other example queries

Other \rtent{standard_id}s relevant here include:


\begin{itemize}

\item \texttt{ivo://ivoa.net/std/registry} for OAI-PMH services,{}

\item \texttt{ivo://ivoa.net/std/sia} for SIA services,{}

\item \texttt{ivo://ivoa.net/std/conesearch} for SCS services,
and{}

\item \texttt{ivo://ivoa.net/std/ssa} for SSA services.{}

\end{itemize}

\subsection{Image Services with Spirals}

\textbf{Problem:} Find all SIA services that might have spiral
galaxies

This is somewhat tricky since it is probably hard to image a part
of the sky guaranteed not to have some, possibly distant, spiral galaxy
in it.  However, translating the intention into ``find all SIA services
that mention spiral in either the subject (from
\rtent{rr.res_subject}), the description, or the
title (which are in
\rtent{rr.resource})'',
the query would become:


%CHECK_HERE
\begin{lstlisting}[language=SQL,flexiblecolumns=true]
SELECT ivoid, access_url
FROM rr.capability
  NATURAL JOIN rr.resource
  NATURAL JOIN rr.interface
  NATURAL JOIN rr.res_subject
WHERE standard_id like 'ivo://ivoa.net/std/sia%'
  AND intf_role='std'
  AND (
    res_subject ILIKE '%spiral%'
    OR 1=ivo_hasword(res_description, 'spiral')
    OR 1=ivo_hasword(res_title, 'spiral'))
\end{lstlisting}


\subsection{Infrared Image Services}

\textbf{Problem:} Find all SIA services that provide infrared
images

The waveband information in
\rtent{rr.resource}
comes in hash-separated atoms (which can be
radio, millimeter, infrared, optical, uv, euv, x-ray, or gamma-ray).
For matching those, use the \rtent{ivo_hashlist_has} function as
below.  The access URL and the service standard come from
\rtent{rr.interface} and
\rtent{rr.capability}, respectively.


%CHECK_HERE
\begin{lstlisting}[language=SQL,flexiblecolumns=true]
SELECT ivoid, access_url
FROM rr.capability
  NATURAL JOIN rr.resource
  NATURAL JOIN rr.interface
WHERE standard_id LIKE 'ivo://ivoa.net/std/sia%'
  AND intf_role='std'
  AND 1=ivo_hashlist_has(waveband, 'infrared')
\end{lstlisting}

\subsection{Catalogs with Redshifts}
\textbf{Problem:} Find all searchable catalogs (i.e., cone search
services) that provide a column containing redshifts

Metadata on columns exposed by a service are contained in
\rtent{rr.table_column}.  Again, this table can be
naturally joined with
\rtent{rr.capability} and
\rtent{rr.interface}.

%CHECK_HERE
\begin{lstlisting}[language=SQL,flexiblecolumns=true]
SELECT ivoid, access_url
FROM rr.capability
  NATURAL JOIN rr.table_column
  NATURAL JOIN rr.interface
WHERE standard_id LIKE 'ivo://ivoa.net/std/conesearch%'
  AND intf_role='std'
  AND ucd='src.redshift'
\end{lstlisting}

Sometimes you want to match a whole set of ucds.  Frequently the
simple regular expressions of SQL will help, as in
\texttt{AND ucd LIKE 'pos.parallax\%'}.  When that is not enough,
use boolean OR expressions.

\subsection{Names from an Authority}

\textbf{Problem:} Find all the resources published by a certain
authority

An ``authority'' within the VO is something that hands out identifiers.
You can tell what authority a record came from by looking at the ``host
part'' of the IVO identifier, most naturally obtained from
\rtent{rr.resource}.  Since ADQL cannot actually parse
URIs, we make do with simple string matching:


%CHECK_HERE
\begin{lstlisting}[language=SQL,flexiblecolumns=true]
SELECT ivoid
FROM rr.resource
WHERE ivoid LIKE 'ivo://org.gavo.dc%'
\end{lstlisting}

\subsection{Records Published by X}

\textbf{Problem:} What registry records are there from a given
publisher?

This uses the
\rtent{rr.res_role}
 table both to
match names (in this case, a publisher that has ``gavo'' in its name) and
to ascertain the named entity actually publishes the resource (rather
than, e.g., just being the contact in case of trouble).  The result is a
list of ivoids in this case.  You could join this with any other
table in the relational registry to find out more about these
services.


%CHECK_HERE
\begin{lstlisting}[language=SQL,flexiblecolumns=true]
SELECT ivoid
FROM rr.res_role
WHERE 1=ivo_nocasematch(role_name, '%gavo%')
  AND base_role='publisher'
\end{lstlisting}

or, if the publisher actually gives its ivo-id in the registry
records,


%CHECK_HERE
\begin{lstlisting}[language=SQL,flexiblecolumns=true]
SELECT ivoid
FROM rr.res_role
WHERE role_ivoid='ivo://ned.ipac/ned'
  AND base_role='publisher'
\end{lstlisting}

\subsection{Records from Registry}

\textbf{Problem:} What registry records are
there originating from registry X?

This is mainly a query interesting for registry maintainers.  Still,
it is a nice example for joining with the
\rtent{rr.res_detail} table, in this case to
first get a list of all authorities managed by the CDS registry.


%CHECK_HERE
\begin{lstlisting}[language=SQL,flexiblecolumns=true]
SELECT ivoid FROM rr.resource
RIGHT OUTER JOIN (
  SELECT 'ivo://' || detail_value || '%' AS pat
  FROM rr.res_detail
  WHERE detail_xpath='/managedAuthority'
    AND ivoid='ivo://cds.vizier/registry')
  AS authpatterns
ON 1=ivo_nocasematch(resource.ivoid, authpatterns.pat)
\end{lstlisting}

\subsection{Locate RegTAP services}
\label{ex:find-regtap}

\textbf{Problem:} Find all TAP endpoints offering the relational
registry

This is the discovery query for RegTAP services themselves;  note how
this combines
\rtent{rr.res_detail} pairs with
\rtent{rr.capability}
and
\rtent{rr.interface} to locate the desired protocol
endpoints.  As clients should not usally be concerned with minor
versions of protocols unless  they rely on additions made in later
versions, this query will return endpoints supporting ``version 1'' rather
than exactly version 1.2.


%CHECK_HERE
\begin{lstlisting}[language=SQL,flexiblecolumns=true]
SELECT access_url
FROM rr.interface
NATURAL JOIN rr.capability
NATURAL JOIN rr.res_detail
WHERE standard_id LIKE 'ivo://ivoa.net/std/tap%'
  AND intf_role='std'
  AND detail_xpath='/capability/dataModel/@ivo-id'
  AND 1=ivo_nocasematch(detail_value,
    'ivo://ivoa.net/std/regtap#1.%')
  AND authenticated_only=0
\end{lstlisting}

Also note the remarks on the likely evolution of data model query
patterns in sect.~\ref{registration}.

\subsection{TAP with Physics}

\textbf{Problem:} Find all TAP services
exposing a table with certain features

``Certain features'' could be ``have some word in their description
and having a column with a certain UCD''.  Either way, this kind of query
fairly invariably combines the usual
\rtent{rr.capability} and
\rtent{rr.interface}
 for service location with
\rtent{rr.table_column}
 for the column metadata
and
\rtent{rr.res_table} for properties of tables.


%CHECK_HERE
\begin{lstlisting}[language=SQL,flexiblecolumns=true]
SELECT ivoid,
  name, ucd, column_description,
  access_url
FROM rr.capability
  NATURAL JOIN rr.interface
  NATURAL JOIN rr.table_column
  NATURAL JOIN rr.res_table
WHERE standard_id LIKE 'ivo://ivoa.net/std/tap%'
  AND intf_role='std'
  AND 1=ivo_hasword(table_description, 'quasar')
  AND ucd='phot.mag;em.opt.v'
\end{lstlisting}

\subsection{Theoretical SSA}

\textbf{Problem:} Find all SSAP services that
provide theoretical spectra.

The metadata required to solve this problem is found in the SSAP
registry extension and is thus kept in
\rtent{rr.res_detail}:


%CHECK_HERE
\begin{lstlisting}[language=SQL,flexiblecolumns=true]
SELECT access_url
FROM rr.res_detail
  NATURAL JOIN rr.capability
  NATURAL JOIN rr.interface
WHERE detail_xpath='/capability/dataSource'
  AND intf_role='std'
  AND standard_id LIKE 'ivo://ivoa.net/std/ssa%'
  AND detail_value='theory'
\end{lstlisting}


\subsection{Find Contact Persons}

\textbf{Problem:} The service at
\texttt{http://dc.zah.uni-heidelberg.de/tap} is down, who can
fix it?

This uses the \rtent{rr.res_role} table and returns all information on
it based on the IVOID of a service that in turn was obtained from
\rtent{rr.interface}.  You could restrict to the actual technical
contact person by requiring \texttt{base\_role='contact'}.


%CHECK_HERE
\begin{lstlisting}[language=SQL,flexiblecolumns=true]
SELECT DISTINCT base_role, role_name, email
FROM rr.res_role
  NATURAL JOIN rr.interface
WHERE access_url='http://dc.zah.uni-heidelberg.de/tap'
\end{lstlisting}

\subsection{Related Capabilities}

\textbf{Problem:} Get the capabilities of all services serving a
specific resource (typically, a data collection).

In the VO, data providers can register data collections either as such
or with ``auxiliary capabilities'' that are fully described elsewhere; a
practice for doing that is discussed in an Endorsed Note on discovering
data collections within services \citep{2019ivoa.spec.0520D}.

When following this pattern, data collections records should provide an
\emph{isServedBy} relationship to the resources providing the access
services for the data collction (like a TAP or a SIAP service).

While the access URLs can typically be established from the auxiliary
capabilities themselves, several use cases require finding out more
about the publishing service.  To locate its metadata, inspect
\rtent{rr.relationship} and use it to select records
from
\rtent{rr.capability}; this requires an explicit join condition, as in
this case the capabilities are for the \emph{related} record, not for
the originally matched one.

%CHECK_HERE
\begin{lstlisting}[language=SQL,flexiblecolumns=true]
SELECT *
FROM rr.relationship AS a
  JOIN rr.capability AS b
    ON (a.related_id=b.ivoid)
WHERE
  relationship_type='isservedby'
  AND a.ivoid='ivo://cds.vizier/j/a+a/649/a25'
\end{lstlisting}


\subsection{Constraints on Space, Time, and Spectrum}

\textbf{Problem:} Give me resources that cover M 101 ($\alpha=210.80$,
$\delta=54.35$, Diameter about $0.3^\circ$) in the mid-infrared around
$5\,\mu\textrm{m}$ in August 2010.

Without further database support, clients need to manually convert the
spectral coordinate to energy ($hc/\lambda \approx 3.97\times
10^{-20}\,\textrm{J}$) and time (August 1st, 2010 starts MJD 55409.0) to
the quantities RegTAP expects.

This would yield a query like (the explicit MOC conversion is a common
device to speed the query up; without it, the database would convert the
circle once for each coverage, to the respective order):

%CHECK_HERE
\begin{lstlisting}[language=SQL,flexiblecolumns=true]
SELECT ivoid
FROM rr.stc_spatial
  NATURAL JOIN rr.stc_spectral
  NATURAL JOIN rr.stc_temporal
WHERE
  1=CONTAINS(MOC(8, CIRCLE(210.80, 54.35, 0.3)), coverage)
  AND 1=ivo_interval_overlaps(time_start, time_end, 55409, 55440)
  AND 3.97e-20 between spectral_start and spectral_end
\end{lstlisting}

In particular when more complex geometries are desired, clients will
want to pass in MOCs directly.  Conversely, RegTAP services may provide
the additional user-defined functions that allow specifying temporal and
spectral constraints in different, perhaps human-friendlier ways.  For
instance, once support for the relevant UDFs is established using the
TAP capabilities, the above query
could also be written as (the MOC given is the circle above at order 8):

%CHECK_HERE
\begin{lstlisting}[language=SQL,basicstyle=\footnotesize]
SELECT ivoid
FROM rr.stc_spatial
  NATURAL JOIN rr.stc_spectral
  NATURAL JOIN rr.stc_temporal
WHERE
  1=CONTAINS(
    MOC('8/182947 182950 182952-182953 182955-182956 8/'),
    coverage)
  AND 1=ivo_interval_overlaps(
    time_start, time_end,
    gavo_to_mjd('2010-08-01'), gavo_to_mjd('2010-08-31'))
  AND gavo_specconv(5e-6, 'm', 'J') between spectral_start and spectral_end
\end{lstlisting}

\subsection{Reliably Doing Arrays of Strings}

In particular libraries and user interfaces often want to retrieve
something like ``all access options'' for a set of resources; this would
mean at least access URLs and their standard identifiers.  Since RegTAP
does not require support for string arrays, this is not entirely
trivial, since with the traditional workaround, \verb|ivo_string_agg|,
NULLs do not appear at all; that way, re-relating, say, access URLs and
standard ids (some of which may be NULL) becomes impossible for a
client.

The solution to this problem is to turn NULLs to empty strings using
\verb|COALESCE|.  The following example also illustrates how to use CTEs
to structure complex queries of this sort, in this case by pre-selecting
ivoids of interest and then using that pre-selected list in a NATURAL
JOIN to restrict the query to, in this case, resources matching a
certain keyword.  This is sometimes simpler than putting the condition
in a WHERE clause, in particular when a table may contribute more than
one output row.

%CHECK_HERE
\begin{lstlisting}[language=SQL,basicstyle=\footnotesize]
WITH candidates AS (
SELECT ivoid
FROM rr.res_subject
WHERE res_subject='solar-system-planets')
SELECT
  ivoid,
  ivo_string_agg(COALESCE(access_url, ''), '<sep>') AS access_urls,
  ivo_string_agg(COALESCE(standard_id, ''), '<sep>') AS standard_ids
FROM
  rr.capability
  NATURAL JOIN rr.interface
  NATURAL JOIN candidates
GROUP BY ivoid
\end{lstlisting}



\appendix

\section{XPaths for res\_detail}

\label{d_u_list}

This appendix defines the \rtent{res_detail}
table (see section \ref{table_res_detail} for
details) by giving
xpaths for which xpath/value pairs MUST (where marked with an
exclamation mark) or SHOULD be given if the
corresponding data is present in the resource records.  This list is
normative for metadata defined in IVOA recommendations current as of the
publication of this document (see section \ref{rolewithinivoa}).
As laid down in section \ref{table_res_detail},
new VOResource extensions or new
versions of existing VOResource extensions may amend this list.

In case there are conflicts between this list and xpaths derived
from schema files using the rules given in section \ref{vorutypes}, the conflict must be considered due to an
editorial oversight in the preparation of this list, and the xpaths from the
schema files are normative.  Errata to this list will be issued in such
cases.

The xpaths are sufficient for locating the respective metadata as per
section \ref{vorutypes}.  With the explanations we
give the canonical prefixes for the XML namespaces from which the items
originate, which is where further information can be found.


\begin{description}
\item[/accessURL (!)]For legacy VODataService vs:DataCollection-typed
records, this is
the URL that can be used to download the data contained.  Do
\emph{not} enter accessURLs from interface elements into res\_detail (vs).
\item[/capability/executionDuration/hard]The hard run time limit, given in seconds (tr).
\item[/capability/complianceLevel]The category indicating the level to which this instance complies with the SSA standard (ssap).
\item[/capability/creationType (!)]The category that describes the process used to produce the dataset; one of archival, cutout, filtered, mosaic, projection, specialExtraction, catalogExtraction (ssap).
\item[/capability/dataModel (!)]The short, human-readable name of a data model supported by a TAP service; for most applications, clients should rather constrain /capability/dataModel/@ivo-id (tr).
\item[/capability/dataModel/@ivo-id (!)]The IVOID of the data model supported by a TAP service (tr).
\item[/capability/dataSource (!)]The category specifying where the data originally came from; one of survey, pointed, custom, theory, artificial (ssap).
\item[/capability/defaultMaxRecords (!)]The largest number of records that the service will return when the MAXREC parameter is not specified in the query input (ssap).
\item[/capability/executionDuration/default]The run time limit for newly-created jobs, given in seconds (tr).
\item[/capability/imageServiceType (!)]The class of image service: Cutout, Mosaic, Atlas, Pointed (sia).
\item[/capability/inter\-face/securityMethod/@standardID (!)]A standard
identifier for an authentication method supported on an interface (vr).
\item[/capability/interface/testQueryString] A query string that can be
used to validate one of the interfaces of a capability (vr).
\item[/capability/language/name (!)]A short, human-readable name of a language understood by the TAP service (tr).
\item[/capability/language/version/@ivo-id (!)]The IVOID of a language supported by a TAP service (tr).
\item[/capability/maxAperture]The largest aperture that can be supported upon request via the APERTURE input parameter by a service that supports the special extraction creation method (ssap).
\item[/capability/maxFileSize (!)]The maximum image file size in bytes (sia).
\item[/capability/maxImageExtent/lat]The maximum size in the latitude (Dec.) direction (sia).
\item[/capability/maxImageExtent/long]The maximum size in the longitude (R.A.) direction (sia).
\item[/capability/maxImageSize/lat]The maximum image size in the latitude (Dec.) direction in pixels (sia-1.0).
\item[/capability/maxImageSize/long]The maximum image size in the longitude (R.A.) direction in pixels (sia-1.0).
\item[/capability/maxImageSize]A measure of the largest image the service can produce given as the maximum number of pixels along the first or second axes. (sia).
\item[/capability/maxQueryRegionSize/lat]The maximum size in the latitude (Dec.) direction (sia).
\item[/capability/maxQueryRegionSize/long]The maximum size in the longitude (R.A.) direction (sia).
\item[/capability/maxRecords (!)]The largest number of items (records, rows, etc.) that the service will return (cs, sia, vg, ssap).
\item[/capability/maxSearchRadius (!)]The largest search radius, in degrees, that will be accepted by the service without returning an error condition. Not providing this element or specifying a value of 180 indicates that there is no restriction. (ssap)
\item[/capability/maxSR (!)]The largest search radius of a cone search service (cs).
\item[/capability/outputFormat/@ivo-id (!)]An IVOID of an output format (tr).
\item[/capability/outputFormat/alias]A short, mnemonic identifier for a service's output format (tr).
\item[/capability/outputFormat/mime (!)]The MIME type of an output format (tr).
\item[/capability/outputLimit/default]The maximal output size for newly-created jobs (tr).
\item[/capability/outputLimit/default/@unit]The unit (rows/bytes) in which the service's default output limit is given (tr).
\item[/capability/outputLimit/hard]The hard limit of a service's output size (tr).
\item[/capability/outputLimit/hard/@unit]The unit of this service's hard output limit (tr).
\item[/capability/retentionPeriod/default]The default time between job creation and removal on this service, given in seconds (tr).
\item[/capability/retentionPeriod/hard]The hard limit for the retention time of jobs on this services (tr).
\item[/capability/supportedFrame (!)]The STC name for a world coordinate system frame supported by this service (ssap).
\item[/capability/testQuery/catalog]The catalog to query (cs).
\item[/capability/testQuery/dec]Declination in a test query (cs)
\item[/capability/testQuery/extras]Any extra (non-standard) parameters that must be provided (apart from what is part of base URL given by the accessURL element; cs, sia).
\item[/capability/testQuery/pos/lat]The Declination of the center of the search position in decimal degrees (ssap, sia).
\item[/capability/testQuery/pos/long]The Right Ascension of the center of the search position in decimal degrees (ssap, sia).
\item[/capability/testQuery/pos/refframe]A coordinate system reference frame name for a test query. If not provided, ICRS is assumed (ssap).
\item[/capability/testQuery/queryDataCmd]Fully specified test query formatted as an URL argument list in the syntax specified by the SSA standard. The list must exclude the REQUEST argument (ssap).
\item[/capability/testQuery/ra]Right ascension in a test query (cs).
\item[/capability/testQuery/size]The size of the search radius in an SSA search query (ssap).
\item[/capability/testQuery/size/lat]Region size for a SIA test query in declination (sia).
\item[/capability/testQuery/size/long]Region size for a SIA test query in RA (sia).
\item[/capability/testQuery/sr]Search radius of a cone search service's test query (cs).
\item[/capability/testQuery/verb]Verbosity of a service's test query (cs, sia).
\item[/capability/uploadLimit/default]An advisory size above which user agents should reconfirm uploads to this service (tr).
\item[/capability/uploadLimit/default/@unit]The unit of the limit specified (tr).
\item[/capability/uploadLimit/hard]Hard limit for the size of uploads on this service (tr).
\item[/capability/uploadLimit/hard/@unit]The unit of the limit specified (tr).
\item[/capability/uploadMethod/@ivo-id]The IVOID of an upload method supported by the service (tr).
\item[/capability/verbosity (!)]\texttt{true} if the service supports the VERB keyword; \texttt{false}, otherwise (cs).
\item[/coverage/footprint (!)]A URL of a footprint service for retrieving precise and up-to-date description of coverage (vs).
\item[/coverage/footprint/@ivo-id (!)]The URI form of the IVOA identifier for the service describing the capability refered to by this element (vs).
\item[/deprecated (!)]A sentinel that all versions of the referenced standard are deprecated. The value is a human-readable explanation for the designation (vstd).
\item[/endorsedVersion (!)]A version of a standard that is recommended for use (vstd).
\item[/facility (!)]The observatory or facility used to collect the data contained or managed by this resource (vs).
\item[/format (!)]The physical or digital manifestation of the information supported by a (DataCollection) resource.  MIME types should be used for network-retrievable, digital data, non-MIME type values are used for media that cannot be retrieved over the network (vs).
\item[/format/@isMIMEType]If \texttt{true}, then an accompanying \texttt{/format} item is a MIME Type. Within \rtent{res_detail}, this does not work for services that give more than one format; since furthermore the literal of \vorent{vs:Format} allows a good guess whether or not it is a MIME type, this does not appear a dramatic limitation (vs).
\item[/full]If \verb|true|, the registry attempts to collect all resource records known to the IVOA (vg).
\item[/instrument (!)]The instrument used to collect the data contained or managed by a resource (vr).
\item[/instrument/@ivo-id (!)]IVOID of the instrument used to collect the data contained or managed by a resource (vr).
\item[/managedAuthority (!)]An authority identifier managed by a registry (vg).
\item[/managingOrg (!)]The organization that manages or owns this authority (vg).
\item[/rights]Free-text information on usage conditions for a resource;
clients should generally use the \rtent{rights} column in
\rtent{rr.resource} (vr).
\item[/rights/@rightsURI]A formal identifier for a license a
resource is made available under; clients should generally use the
\rtent{rights_uri} column in \rtent{rr.resource} (vr).
\item[/schema/@namespace (!)]An identifier for a schema described by a standard (vstd).

\end{description}

Note that the representation of StandardsRegExt's
\vorent{status}  and \vorent{use}
attributes as well as its \vorent{key} would require sequences of
complex objects, which is impossible using \rtent{res_detail}.
Hence, the respective metadata is not queriable
within the relational registry. Similarly, complex TAPRegExt metadata on
languages, user defined functions, and the like cannot be
represented in this table.  Since these pieces of metadata do not seem
relevant to resource discovery and are geared towards other uses of the
respective VOResource extensions, a more complex model does not seem
justifiable just so they can be exposed.



\section{The Extra UDFs in
PL/pgSQL}

\label{appPGDefs}

What follows are (non-normative)
implementations of four of the User Defined Functions
specificed in section \ref{adqludf} in the SQL dialect
of PostgreSQL (e.g., \citet{doc:Postgres92}).

Note that PostgreSQL cannot use fulltext indexes on the respective
columns if the functions are defined in this way for (fairly subtle)
reasons connected with NULL value handling.  While workarounds are
conceivable, they come with potentially unwelcome side effects, at least
as of PostgreSQL 9.x.  It is therefore recommended to replace
expressions involving the functions given here with simple boolean
expressions in the ADQL translation layer whenever possible.


\begin{lstlisting}[language=SQL,basicstyle=\footnotesize]
  CREATE OR REPLACE FUNCTION
    ivo_hasword(haystack TEXT, needle TEXT)
  RETURNS INTEGER AS $func$
    SELECT CASE WHEN to_tsvector($1) @@ plainto_tsquery($2)
      THEN 1
      ELSE 0
    END
  $func$ LANGUAGE SQL;

  CREATE OR REPLACE FUNCTION
    ivo_hashlist_has(hashlist TEXT, item TEXT)
  RETURNS INTEGER AS $func$
    -- postgres can't RE-escape a user string; hence, we'll have
    -- to work on the hashlist (this assumes hashlist is already
    -- lowercased).
    SELECT CASE WHEN lower($2) = ANY(string_to_array($1, '#'))
      THEN 1
      ELSE 0
    END
  $func$ LANGUAGE SQL;

  CREATE OR REPLACE FUNCTION
    ivo_nocasematch(value TEXT, pattern TEXT)
  RETURNS INTEGER AS $func$
    SELECT CASE WHEN $1 ILIKE $2
      THEN 1
      ELSE 0
    END
  $func$ LANGUAGE SQL;

  CREATE OR REPLACE FUNCTION
    ivo_interval_overlaps(l1 NUMERIC, h1 NUMERIC,
      l2 NUMERIC, h2 NUMERIC)
  RETURNS BOOLEAN AS $func$
    SELECT h1>=l2 AND h2>=l1 AND l1<=h1 AND l2<=h2
  $func$ LANGUAGE SQL STABLE;

  -- ivo_string_agg directly corresponds to string_agg; this translation
  -- should be done in the ADQL translator.
\end{lstlisting}

\section{A View Definition for tap\_table (non-normative)}
\label{app:tap-table-viewdef}

While RegTAP operators are free to implement \rtent{tap_table} as
convenient on their platform, here is a standard SQL query that produces
a result compliant to the constraints in Sect.~\ref{table_tap_table}
assuming 2024 Registry conventions:

\begin{lstlisting}[basicstyle=\footnotesize,language=SQL]
WITH
  fromres AS (
    -- tables coming in through relationships; only those declaring
    -- an auxiliary capability *and* a relationship will be considered
    -- The GROUP BY and MIN hack is necessary since multiple of these
    -- may declare the same table (e.g., ivoa.obscore for data collections
    -- published through obscore).
    SELECT
    	MIN(tabcap.ivoid) as resid,
    	related_id as svcid,
    	table_name,
      MIN(table_title) as table_title,
      MIN(table_description) as table_description,
      MIN(table_utype) as table_utype
    FROM rr.res_table as tab
    NATURAL JOIN rr.capability as tabcap
    NATURAL JOIN rr.relationship
    JOIN rr.capability AS svccap
      ON (svccap.ivoid=related_id)
    WHERE
      (table_type!='output' OR table_type IS NULL)
      AND svccap.standard_id='ivo://ivoa.net/std/tap'
      AND tabcap.standard_id='ivo://ivoa.net/std/tap#aux'
      AND relationship_type='isservedby'
    GROUP BY related_id, table_name),

  fromtap AS (
    -- tables directly attached to the TAP service
    SELECT rt.ivoid as resid, ivoid as svcid,
      table_name, table_title,
      table_description, table_utype
    FROM rr.res_table AS rt
    NATURAL JOIN rr.capability
    WHERE
      (table_type!='output' OR table_type IS NULL)
      AND standard_id='ivo://ivoa.net/std/tap'
      AND NOT EXISTS (SELECT 1 FROM fromres as fr
        WHERE rt.ivoid=fr.svcid
        AND rt.table_name=fr.table_name))

-- using WITH here to allow for a lateral union
SELECT * FROM fromtap UNION ALL
SELECT * FROM fromres) q)
\end{lstlisting}


\section{Changes from Previous Versions}

\subsection{Changes from PR-1.2-20240124}

\begin{itemize}
\item Removed hedging language from sect.~4.5, ``Vocabulary
Considerations'', since Vocabularies in the VO 2 is now a REC.

\item Consequently, removed Appendix D (``Mandatory translations'').
Ingestors should take these directly from the vocabulary (e.g., via
desise).

\item Now explicitly requiring ADQL 2.1 or later on the underlying TAP
service.
\end{itemize}

\subsection{Changes from WD-1.2-20220519}

\begin{itemize}
\item Names in \verb|rr.res_table| are no longer lowercased (this
picks up RegTAP 1.1 erratum 1)
\item Several editorial changes like slightly improved column
descriptions.
\item No longer mentioning VODataService 1.0 tablesets in the running
text, but still mentioning its traditional namespace; the tablesets no
longer exist in the registry (but perhaps some vs:ParamHTTP types), and
so let's not reference apocryphic documents.
\end{itemize}

\subsection{Changes from REC-1.1}

\begin{itemize}
\item Adding \rtent{stc_spatial}, \rtent{stc_temporal}, and
\rtent{stc_spectral} tables and a sample query illustrating their use.
\item Adding a \rtent{tap_table} view of TAP-queriable tables.
\item Requiring ADQL COALESCE, ILIKE, and WITH constructs.
\item Requiring an \texttt{ivo\_interval\_overlaps} ADQL User Defined
Function.
\item Including VODataService 1.2 resource types.
\item \rtent{table_name} is no longer case-folded (RegTAP 1.1 Erratum 1).
\item Now recommending an index on \rtent{res_table.table_utype} (for
discovering EPN-TAP, LineTAP, ObsLocTAP\dots).
\end{itemize}

\subsection{Changes from REC-1.0}

\begin{itemize}
\item Added the \rtent{alt_identifier} table.
\item Added \rtent{rights_uri} to \rtent{resource}.
In \rtent{rights}, we now only take data from the first rights element
as hash-joining is not reliable with free text.
This technically might constitute an API change, but since we don't
believe rights has (properly) been used anywhere, we still believe we are
within the limits of a minor change.
\item Added an xpath \texttt{/capability/interface/testQueryString} for
use in
\rtent{res_detail} to cover VOResource 1.1's \vorent{testQueryString}
interface child.  Note that this is not really enough to feed
validators, as a capability can have multiple interfaces and
\rtent{res_detail} only tells apart capabilitities.  Running a
validator off a RegTAP service really requires an extra table.
\item Added a \rtent{mirror_url} column to \rtent{rr.interface}.
\item Made type information in the schema tables more generic; we now
have string, integer, real, and string+timestamp.
\item Added a column \rtent{authenticated_only} in \rtent{interface}
that is true when the interface cannot be used without authentication.
Added this to the recommended discovery patterns.
\item Recommending that when discovering standard services clients
should (again) constrain \rtent{intf_role} to \verb|std| rather than
\rtent{intf_type} to \verb|vs:ParamHTTP|.  An investigation on 2019-09-01 showed that the
workaround from RegTAP 1.0 is no longer necessary.
\item Now requiring that services map deprecated vocabulary terms to
preferred ones.
\item Now requiring the data model URI as the utype of the \rtent{rr} schema.
\item No longer claiming that RegTAP services do not use the
\xmlel{vg:registry} resource type any more, instead referring to RI 1.1.
\item Dropping the appendix with recommended string sizes.
\item Replaced inline XSLT utype maker with a link to an external
resource.
\item Updated example queries to match standard ids as recommended by
Identifiers 2.0; also included RegTAP 1.0 erratum 1, and repaired the
bad order of arguments in ivo\_hashlist\_has in query 10.3.
\end{itemize}

\subsection{Changes from PR-2014-10-30}

\begin{itemize}
\item No changes to specification content (only minor typo fixes).
\end{itemize}


\subsection{Changes from PR-20140627}

\begin{itemize}
\item Removed reference to a future STC extension.
\item Migrated to ivoatex.
\end{itemize}

\subsection{Changes from PR-20140227}

\label{changes-20140227}

\begin{itemize}

\item Added a \texttt{/full} details xpath from VORegistry (this had
  been forgotten due to limitations in the makeutypes stylesheet).{}

\item Added a \texttt{/capability/interface/securityMethod/@standardID}
  details xpath from vr:Interface.{}

\item Added requirement to implement the \rtent{ivo_string_agg}
  user defined function.{}

\item Added a section specifying the treatment of non-ASCII characters
  in RegTAP columns.{}

\item New rules on string normalization: strings must be
  whitespace-stripped, empty strings must be mapped to NULL.{}

\item Dropped requirements that the \texttt{\_index} columns are
  integers (let alone small integers); added a section discussing in
  what sense they are implementation defined.{}

\item Dropped adql: prefixes on TAP\_SCHEMA.columns datatypes.{}

\item Now declaring a precedence of xpaths generated by rules over the
  list in Appendix \ref{d_u_list}.{}

\item Clarified translation of column/@std and param/@std.{}

\item Now recommending to constrain on \rtent{intf_type}
  (rather than \rtent{intf_role}, as before) when locating standard
  interfaces.{}

\item Redactional changes from RFC (e.g., in column descriptions, some
  clarifications, typo fixes).{}


\end{itemize}

\subsection{Changes from WD-20131203}

\label{changes-20131203}


\begin{itemize}

\item To match our usage with what will later be in the standards
  record, changed the data model identifier to
  \url{ivo://ivoa.net/std/regtap\#1.0}.

\item Fixed a typo in a column name: schema.schemaname is now schema.schema\_name
  as in the prose.{}

\item Recovered
  \texttt{/capability/uploadMethod/@ivo-id} res\_detail keys that was
  accidentally lost in a previous version.{}

\item Clarification of nomenclature.{}

\end{itemize}

\subsection{Changes from WD-20130909}

\label{changes-20130909}


\begin{itemize}

\item Updates for REC of SimpleDALRegExt, which contains versions 1.1 of
  both the sia and the ssap XML schemas; this means there are now additional
  namespace URIs to take into accound, as well as new res\_detail xpaths
  \texttt{/capability/maxSearchRadius},
  \texttt{/capability/maxImageSize}, and
  \texttt{/capability/testQuery/pos/refframe}.{}

\item Reinstated makeutypes.xslt script; it's useful even with the new
  xpaths.{}

\end{itemize}

\subsection{Changes from WD-20130411}

\label{changes-20130411}


\begin{itemize}

\item The final utype reform: most of our ex-utype strings aren't called utypes
  any more, they're fairly plain xpaths.  Consequently,
  \rtent{res_detail.detail_utype} has been renamed
  \rtent{detail_xpath}.{}

\item Renamed some columns and the subject table to relieve the need of quoting
  in MS SQL Server (or, in the case or \rtent{use_param}, maintain
  consistency after the renaming):\\

\begin{tabular}{lll}

\textbf{Old}&
\textbf{New}\\
resource.version&resource.res\_version\\
res\_role.address&res\_role.street\_address\\
subject.*&res\_subject.*\\
res\_subject.res\_subject&res\_subject.res\_subject\\
table\_column.description&table\_column.column\_description\\
intf\_param.description&intf\_param.param\_description\\
intf\_param.use\_param&intf\_param.param\_use\\
validation.level&validation.val\_level\\

\end{tabular}

\item rr.intf\_param grew the arraysize and delim columns that before
    accidentally were only present in rr.table\_column.{}

\item Added warnings about having to match case-insensitively in
  res\_detail.detail\_value for IVOID-valued rows.{}

\item Restored the foreign key from interface to capability.  Mandating
  ignoring interface elements from StandardsRegExt records really is the
  lesser evil.{}

\item \rtent{resource.region_of_regard} now must have unit metadata
  declared.{}

\item We now explicitely deprecate multiple access URLs per interface
  and announce that single access URLs will be enforced in future
  VOResource versions.{}

\end{itemize}

\subsection{Changes from WD-20130305}

\label{changes-20130305}

\begin{itemize}

\item intf\_index is now required to be unique within a resource, not a
capability; this is because StandardsRegExt has interfaces outside
of capabilities.  In consequence, the intf\_param no longer has a
cap\_index column, and its foreign key is just ivoid and intf\_index.{}

\item Added handling for the StandardsRegExt schema element.{}

\item The list of res\_detail utypes was moved to an appendix since
it was too long to be included in the running text.{}

\item Redaction for WD publication.{}

\end{itemize}

\subsection{Changes from WD-20121112}

\label{changes-20121112}

\begin{itemize}

\item Adapted all utypes to better match future VO-DML utypes.{}

\item footprint, data\_url, facility, and instrument are no longer in rr.resource
but are instead kept in rr.res\_detail rows.{}

\item For VOResource compliance, intf\_param has no flag column any more.{}

\item res\_role.base\_utype is renamed to res\_role.base\_role and no longer
pretends to be a utype fragment; also, the content is now a simple
word..{}

\item intf\_param.use is now called intf\_param.use\_param to avoid possible
clashes with reserved SQL words.{}

\item Removed all material on STC coverage.{}

\item Added an appendix recommending field sizes.{}

\end{itemize}


\bibliography{ivoatex/ivoabib,ivoatex/docrepo,local}

\end{document}